30分鐘入門Oracle sql語句

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;

SELECT last_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 Id: ]' 

       || 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) ;

SELECT first_name

FROM employees

WHERE first_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, search2, result1 

        [, search3, 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;