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,12*salary+100 FROM employees;
SELECT last_name,12*(salary+100) FROM employees;
SELECT last_name,job_id,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,department_id
FROM employees
WHERE department_id = 90 ;
SELECT last_name,department_id
FROM employees
WHERE last_name = 'Whalen' ;
SELECT last_name,salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500 ;
SELECT employee_id,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,salary
FROM employees
WHERE salary >=10000
AND job_id LIKE '%MAN%' ;
SELECT employee_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,department_id,hire_date
FROM employees
ORDER BY hire_date ;
SELECT last_name,hire_date
FROM employees
ORDER BY hire_date DESC ;
SELECT employee_id,salary*12 annsal
FROM employees
ORDER BY annsal ;
SELECT last_name,salary
FROM employees
ORDER BY department_id,salary DESC;
SELECT employee_id,department_id
FROM employees
WHERE employee_id = &employee_num ;
SELECT last_name,salary*12
FROM employees
WHERE job_id = '&job_title' ;
SELECT employee_id,&column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;
SELECT employee_id,&&column_name
FROM employees
ORDER BY &column_name ;
DEFINE employee_num = 200
SELECT employee_id,department_id
FROM employees
WHERE employee_id = &employee_num ;
UNDEFINE employee_num
SET VERIFY ON
SELECT employee_id,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,'*')
TRIM TRIM('H' FROM 'HelloWorld')
REPLACE REPLACE('JACK and JUE','J','BL')
SELECT employee_id,department_id
FROM employees
WHERE last_name = 'higgins';
no rows selected
SELECT employee_id,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,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,8),'_US'))
FROM employees
WHERE department_id = 60;
NVL (expr1,expr2)
NVL2 (expr1,expr2,expr3)
NULLIF (expr1,expr2)
COALESCE (expr1,...,exprn)
NVL(commission_pct,0)
NVL(hire_date,'01-JAN-97')
NVL(job_id,'No Job Yet')
SELECT last_name,NVL(commission_pct,
(salary*12) + (salary*12*NVL(commission_pct,0)) AN_SAL
FROM employees;
SELECT last_name,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,-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,
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,
DECODE(job_id,'IT_PROG',1.10*salary,
'ST_CLERK',1.15*salary,
'SA_REP',1.20*salary,
salary)
REVISED_SALARY
FROM employees;
原文链接：https://www.f2er.com/oracle/212102.html