30分钟入门Oracle sql语句

ustbwang

　　SELECT *|{[DISTINCT] column|expression [alias],...}
　　FROM    table;
　　SELECT * FROM   departments t;
　　SELECT department_id, location_id FROM   departments;
　　SELECT last_name, salary, salary + 300 FROM   employees;
　　SELECT last_name, salary, 12*salary+100 FROM   employees;
　　SELECT last_name, salary, 12*(salary+100) FROM   employees;
　　SELECT last_name, job_id, salary, commission_pct FROM   employees;
　　SELECT last_name, 12*salary*commission_pct FROM   employees;
　　SELECT last_name AS name, commission_pct comm FROM   employees;
　　SELECT last_name "Name" , salary*12 "Annual Salary" FROM   employees;
　　SELECTlast_name||job_id AS "Employees" FROM employees;
　　SELECT last_name ||' is a '||job_id
　　AS "Employee Details"
　　FROM   employees;
　　SELECT department_name ||

　　q'[, it's assigned Manager>　　|| manager_id
　　AS "Department and Manager"
　　FROM departments;
　　SELECT department_id
　　FROM   employees;
　　SELECT DISTINCT department_id
　　FROM   employees;
　　DESC[RIBE] tablename
　　DESC employees
　　SELECT *|{[DISTINCT] column|expression [alias],...}
　　FROM   table
　　[WHERE condition(s)];
　　SELECT employee_id, last_name, job_id, department_id
　　FROM   employees
　　WHERE  department_id = 90 ;
　　SELECT last_name, job_id, department_id
　　FROM   employees
　　WHERE  last_name = 'Whalen' ;
　　SELECT last_name, salary
　　FROM   employees
　　WHERE  salary BETWEEN 2500 AND 3500 ;
　　SELECT employee_id, last_name, salary, manager_id
　　FROM   employees
　　WHERE  manager_id IN (100, 101, 201) ;
　　SELECTfirst_name
　　FROM employees
　　WHEREfirst_name LIKE 'S%' ;
　　SELECT last_name
　　FROM   employees
　　WHERE  last_name LIKE '_o%' ;
　　SELECT last_name, manager_id
　　FROM   employees
　　WHERE  manager_id IS NULL ;
　　SELECT employee_id, last_name, job_id, salary
　　FROM   employees
　　WHERE  salary >=10000
　　AND    job_id LIKE '%MAN%' ;
　　SELECT employee_id, last_name, job_id, salary
　　FROM   employees
　　WHERE  salary >= 10000
　　OR     job_id LIKE '%MAN%' ;
　　SELECT last_name, job_id
　　FROM   employees
　　WHERE  job_id
　　NOT IN ('IT_PROG', 'ST_CLERK', 'SA_REP') ;
　　SELECT   last_name, job_id, department_id, hire_date
　　FROM     employees
　　ORDER BY hire_date ;
　　SELECT   last_name, job_id, department_id, hire_date
　　FROM     employees
　　ORDER BY hire_date DESC ;
　　SELECT employee_id, last_name, salary*12 annsal
　　FROM   employees
　　ORDER BY annsal ;
　　SELECT last_name, department_id, salary
　　FROM   employees
　　ORDER BY department_id, salary DESC;
　　SELECT employee_id, last_name, salary, department_id
　　FROM   employees
　　WHERE  employee_id = &employee_num ;
　　SELECT last_name, department_id, salary*12
　　FROM   employees
　　WHERE  job_id = '&job_title' ;
　　SELECT employee_id, last_name, job_id,&column_name
　　FROM   employees
　　WHERE  &condition
　　ORDER BY &order_column ;
　　SELECT   employee_id, last_name, job_id, &&column_name
　　FROM     employees
　　ORDER BY &column_name ;
　　DEFINE employee_num = 200
　　SELECT employee_id, last_name, salary, department_id
　　FROM   employees
　　WHERE  employee_id = &employee_num ;
　　UNDEFINE employee_num
　　SET VERIFY ON
　　SELECT employee_id, last_name, salary, department_id
　　FROM   employees
　　WHERE  employee_id = &employee_num;
　　LOWER  LOWER('SQL Course')
　　UPPER UPPER('SQL Course')
　　INITCAP INITCAP('SQL Course')
　　CONCAT  CONCAT('Hello', 'World')
　　SUBSTR SUBSTR('HelloWorld',1,5)
　　LENGTH LENGTH('HelloWorld')
　　INSTR INSTR('HelloWorld', 'W')
　　LPAD | RPAD LPAD(salary,10,'*')
　　RPAD(salary, 10, '*')
　　TRIM TRIM('H' FROM 'HelloWorld')
　　REPLACE REPLACE('JACK and JUE','J','BL')
　　SELECT employee_id, last_name, department_id
　　FROM   employees
　　WHERE  last_name = 'higgins';
　　no rows selected
　　SELECT employee_id, last_name, department_id
　　FROM   employees
　　WHERE  LOWER(last_name) = 'higgins';
　　SELECT employee_id, CONCAT(first_name, last_name) NAME,
　　job_id, LENGTH (last_name),
　　INSTR(last_name, 'a') "Contains 'a'?"
　　FROM   employees
　　WHERE  SUBSTR(job_id, 4) = 'REP';
　　ROUND(45.926, 2)
　　TRUNC(45.926, 2)
　　MOD(1600, 300)
　　SELECT ROUND(45.923,2), ROUND(45.923,0),
　　ROUND(45.923,-1)
　　FROM   DUAL;
　　SELECT ROUND(45.923,2), ROUND(45.923),
　　ROUND(45.923,-1)
　　FROM   DUAL;
　　SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
　　FROM   employees
　　WHERE  department_id = 90;
　　MONTHS_BETWEEN
　　MONTHS_BETWEEN ('01-SEP-95','11-JAN-94')
　　ADD_MONTHS ADD_MONTHS ('11-JAN-94',6)
　　NEXT_DAY  NEXT_DAY   ('01-SEP-95','FRIDAY')
　　LAST_DAY  LAST_DAY   ('01-FEB-95')
　　ROUND
　　TRUNC
　　ROUND(SYSDATE,'MONTH')
　　ROUND(SYSDATE ,'YEAR')
　　TRUNC(SYSDATE ,'MONTH')
　　TRUNC(SYSDATE ,'YEAR')
　　VARCHAR2 or CHAR   NUMBER
　　VARCHAR2 or CHAR  DATE
　　NUMBER   VARCHAR2
　　DATE   VARCHAR2
　　SELECT last_name,
　　TO_CHAR(hire_date, 'fmDD Month YYYY')
　　AS HIREDATE
　　FROM   employees;
　　SELECT TO_CHAR(salary, '$99,999.000') SALARY
　　FROM   employees
　　WHERE  last_name = 'Ernst';
　　SELECT last_name,
　　UPPER(CONCAT(SUBSTR (LAST_NAME, 1, 8), '_US'))
　　FROM   employees
　　WHERE  department_id = 60;
　　NVL (expr1, expr2)
　　NVL2 (expr1, expr2, expr3)
　　NULLIF (expr1, expr2)
　　COALESCE (expr1, expr2, ..., exprn)
　　NVL(commission_pct,0)
　　NVL(hire_date,'01-JAN-97')
　　NVL(job_id,'No Job Yet')
　　SELECT last_name, salary, NVL(commission_pct, 0),
　　(salary*12) + (salary*12*NVL(commission_pct, 0)) AN_SAL
　　FROM employees;
　　SELECT last_name,  salary, commission_pct,
　　NVL2(commission_pct,
　　'SAL+COMM', 'SAL') income
　　FROM   employees WHERE department_id IN (50, 80);
　　SELECT first_name, LENGTH(first_name) "expr1",
　　last_name,  LENGTH(last_name)  "expr2",
　　NULLIF(LENGTH(first_name), LENGTH(last_name)) result
　　FROM   employees;
　　SELECT last_name,
　　COALESCE(manager_id,commission_pct, -1) comm
　　FROM   employees
　　ORDER BY commission_pct;
　　CASE expr WHEN comparison_expr1 THEN return_expr1
　　[WHEN comparison_expr2 THEN return_expr2
　　WHEN comparison_exprn THEN return_exprn
　　ELSE else_expr]
　　END
　　SELECT last_name, job_id, salary,
　　CASE job_id WHEN 'IT_PROG'  THEN  1.10*salary
　　WHEN 'ST_CLERK' THEN  1.15*salary
　　WHEN 'SA_REP'   THEN  1.20*salary
　　ELSE      salary END     "REVISED_SALARY"
　　FROM   employees;
　　DECODE(col|expression, search1, result1
　　[, search2, result2,...,]
　　[, default])
　　SELECT last_name, job_id, salary,
　　DECODE(job_id, 'IT_PROG',  1.10*salary,
　　'ST_CLERK', 1.15*salary,
　　'SA_REP',   1.20*salary,
　　salary)
　　REVISED_SALARY
　　FROM   employees;