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;
