下面的语句都是本人整理出的,并且都在oracle XE上测试通过。下边的函数都没有做说明,相信大家都知道怎么用了,呵呵。
1
普通的查询语句,例如:
SELECT last_name, department_id FROM employees;
2 查询视图
SELECT employee_id, last_name, job_title, department_name, country_name,
region_name FROM emp_details_view;
3 更换列名查询
SELECT employee_id "Employee ID number ", last_name "Employee last name",
first_name "Employee first name" FROM employees;
4 联接查询
自然联接:要求两个表中的公共列必须有相同的名称和结构类型,否则报错。
SELECT employee_id, last_name, first_name, department_id,
department_name, manager_id FROM employees NATURAL JOIN departments;
查询三张或以上表时用USING,前提是用来连接两张表的列必须名称相同。
SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id,
d.department_name, d.manager_id FROM employees e
JOIN departments d USING (department_id);
联接时加上WHERE 子句。
SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
d.department_name, d.manager_id, d.location_id, l.country_id FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.location_id = 1700 ;
自我联接:
SELECT e.employee_id emp_id, e.last_name emp_lastname, m.employee_id mgr_id,
m.last_name mgr_lastname
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
LEFT OUTER JOIN :左表中所有的记录即使在右表中没有联接的都会被查询出来。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
RIGHT OUTER JOIN :右表中所有的记录即使在左表中没有联接的都会被查询出来。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
FULL OUTER JOIN :左右表中的记录全部被查询出来。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
SELECT last_name, department_id FROM employees;
2 查询视图
SELECT employee_id, last_name, job_title, department_name, country_name,
region_name FROM emp_details_view;
3 更换列名查询
SELECT employee_id "Employee ID number ", last_name "Employee last name",
first_name "Employee first name" FROM employees;
4 联接查询
自然联接:要求两个表中的公共列必须有相同的名称和结构类型,否则报错。
SELECT employee_id, last_name, first_name, department_id,
department_name, manager_id FROM employees NATURAL JOIN departments;
查询三张或以上表时用USING,前提是用来连接两张表的列必须名称相同。
SELECT e.employee_id, e.last_name, e.first_name, e.manager_id, department_id,
d.department_name, d.manager_id FROM employees e
JOIN departments d USING (department_id);
联接时加上WHERE 子句。
SELECT e.employee_id, e.last_name, e.first_name, e.department_id,
d.department_name, d.manager_id, d.location_id, l.country_id FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN locations l ON d.location_id = l.location_id
WHERE l.location_id = 1700 ;
自我联接:
SELECT e.employee_id emp_id, e.last_name emp_lastname, m.employee_id mgr_id,
m.last_name mgr_lastname
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
LEFT OUTER JOIN :左表中所有的记录即使在右表中没有联接的都会被查询出来。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id);
RIGHT OUTER JOIN :右表中所有的记录即使在左表中没有联接的都会被查询出来。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
FULL OUTER JOIN :左右表中的记录全部被查询出来。
SELECT e.employee_id, e.last_name, e.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
5
绑定变量查询:Oracle将已解析、已编译的SQL连同其他内容存储在共享池中,这是SGA中一个非常重要的存储结构(内存结构主要分为SGA和PGA)。而绑定变量查询在Oracle里执行时只编译一次,随后就会把这个查询计划存储在一个共享池中以便重用,即所谓的软解析。具体的使用方法如下:
SELECT * FROM employees WHERE employee_id = :employee_id
6 查询虚拟列:所谓的虚拟列在Oracle里类似表格的列,但并非存储在表中。查询虚拟列时将返回一个值,因此它又类似与函数。Oracle里的虚拟列有:ROWNUM, SYSDATE, and USER 。eg:
SELECT SYSDATE "NOW" FROM DUAL;
SELECT USER FROM DUAL;
SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10 ;
7 带函数查询:
Using Numeric Functions:
SELECT employee_id, ROUND (salary / 30 , 2 ) "Salary per day " FROM employees;
SELECT employee_id, TRUNC(salary / 30 , 0 ) "Salary per day " FROM employees;
SELECT employee_id, MOD(employee_id, 2 ) FROM employees;
Using Character Functions:
SELECT employee_id, UPPER (last_name), LOWER (first_name) FROM employees;
SELECT employee_id, INITCAP(first_name), INITCAP(last_name) FROM employees;
SELECT employee_id, RTRIM (first_name) || ' ' || LTRIM (last_name) FROM employees;
SELECT employee_id, TRIM(last_name) || ' , ' || TRIM(first_name) FROM employees;
SELECT employee_id, RPAD(last_name, 30 , ' ' ), first_name FROM employees;
SELECT employee_id, SUBSTR(last_name, 1 , 10 ) FROM employees;
SELECT LENGTH(last_name) FROM employees;
SELECT employee_id, REPLACE (job_id, ' SH ' , ' SHIPPING ' ) FROM employees
WHERE SUBSTR(job_id, 1 , 2 ) = ' SH ' ;
Using Date Functions:
SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed"
FROM employees;
SELECT employee_id, EXTRACT( YEAR FROM hire_date) " Year Hired" FROM employees;
SELECT EXTRACT( YEAR FROM SYSDATE) || EXTRACT( MONTH FROM SYSDATE) ||
EXTRACT( DAY FROM SYSDATE) " Current Date" FROM DUAL;
SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 3 ) FROM employees;
SELECT employee_id, hire_date, LAST_DAY(hire_date) "Last day of month "
FROM employees;
SELECT SYSTIMESTAMP FROM DUAL;
Using Conversion Functions:
SELECT TO_CHAR(SYSDATE, ' DD-MON-YYYY AD ' ) "Today" FROM DUAL;
SELECT TO_CHAR(SYSDATE, ' FMMonth DD YYYY ' ) "Today" FROM DUAL;
SELECT TO_CHAR(SYSDATE, ' MM-DD-YYYY HH24:MI:SS ' ) "Now" FROM DUAL;
SELECT hire_date, TO_CHAR(hire_date, ' DS ' ) "Short Date" FROM employees;
SELECT hire_date, TO_CHAR(hire_date, ' DL ' ) " Long Date" FROM employees;
SELECT TO_CHAR(EXTRACT( YEAR FROM SYSDATE)) ||
TO_CHAR(EXTRACT( MONTH FROM SYSDATE), ' FM09 ' ) ||
TO_CHAR(EXTRACT( DAY FROM SYSDATE), ' FM09 ' ) " Current Date" FROM DUAL;
SELECT TO_CHAR( CURRENT_DATE , ' DD-MON-YYYY HH24:MI:SS ' ) " Current Date" FROM DUAL;
SELECT TO_CHAR(salary, ' $99,999.99 ' ) salary FROM employees;
SELECT TO_NUMBER( ' 1234.99 ' ) + 500 FROM DUAL;
SELECT TO_NUMBER( ' 11,200.34 ' , ' 99G999D99 ' ) + 1000 FROM DUAL;
SELECT TO_DATE( ' 27-OCT-98 ' , ' DD-MON-RR ' ) FROM DUAL;
SELECT TO_DATE( ' 28-Nov-05 14:10:10 ' , ' DD-Mon-YY HH24:MI:SS ' ) FROM DUAL;
SELECT TO_DATE( ' January 15, 2006, 12:00 A.M. ' , ' Month dd, YYYY, HH:MI A.M. ' )
FROM DUAL;
SELECT TO_TIMESTAMP( ' 10-Sep-05 14:10:10.123000 ' , ' DD-Mon-RR HH24:MI:SS.FF ' )
FROM DUAL;
Using Aggregate Functions:
SELECT COUNT ( * ) "Employee Count " FROM employees WHERE manager_id = 122 ;
SELECT COUNT ( * ) "Employee Count ", manager_id FROM employees
GROUP BY manager_id ORDER BY manager_id
SELECT COUNT (commission_pct) FROM employees;
SELECT COUNT ( DISTINCT department_id) FROM employees;
SELECT MIN (salary), MAX (salary), AVG (salary), job_id FROM employees
GROUP BY job_id ORDER BY job_id;
SELECT RANK( 2600 ) WITHIN GROUP
( ORDER BY salary DESC ) "Rank of $ 2 , 600 among clerks"
FROM employees WHERE job_id LIKE ' %CLERK ' ;
SELECT job_id, employee_id, last_name, salary, DENSE_RANK() OVER
(PARTITION BY job_id ORDER BY salary DESC ) "Salary Rank (Dense)"
FROM employees WHERE job_id = ' SH_CLERK ' ;
SELECT employee_id, salary, hire_date, STDDEV(salary)
OVER ( ORDER BY hire_date) "Std Deviation of Salary"
FROM employees WHERE job_id = ' ST_CLERK ' ;
Using NULL Value Functions:
SELECT commission_pct, NVL(commission_pct, 0 ) FROM employees;
SELECT phone_number, NVL(phone_number, ' MISSING ' ) FROM employees;
SELECT employee_id , last_name,commission_pct, salary,
NVL2(commission_pct, salary + (salary * commission_pct), salary) income
FROM employees;
Using Conditional Functions:
SELECT employee_id, hire_date , salary,
CASE WHEN hire_date < TO_DATE( ' 01-JAN-90 ' ) THEN salary * 1.20
WHEN hire_date < TO_DATE( ' 01-JAN-92 ' ) THEN salary * 1.15
WHEN hire_date < TO_DATE( ' 01-JAN-94 ' ) THEN salary * 1.10
ELSE salary * 1.05 END "Revised Salary"
FROM employees;
SELECT employee_id, job_id , salary,
DECODE(job_id, ' PU_CLERK ' , salary * 1.05 ,
' SH_CLERK ' , salary * 1.10 ,
' ST_CLERK ' , salary * 1.15 ,
salary) "Revised Salary"
FROM employees;
SELECT * FROM employees WHERE employee_id = :employee_id
6 查询虚拟列:所谓的虚拟列在Oracle里类似表格的列,但并非存储在表中。查询虚拟列时将返回一个值,因此它又类似与函数。Oracle里的虚拟列有:ROWNUM, SYSDATE, and USER 。eg:
SELECT SYSDATE "NOW" FROM DUAL;
SELECT USER FROM DUAL;
SELECT employee_id, hire_date, SYSDATE FROM employees WHERE ROWNUM < 10 ;
7 带函数查询:
Using Numeric Functions:
SELECT employee_id, ROUND (salary / 30 , 2 ) "Salary per day " FROM employees;
SELECT employee_id, TRUNC(salary / 30 , 0 ) "Salary per day " FROM employees;
SELECT employee_id, MOD(employee_id, 2 ) FROM employees;
Using Character Functions:
SELECT employee_id, UPPER (last_name), LOWER (first_name) FROM employees;
SELECT employee_id, INITCAP(first_name), INITCAP(last_name) FROM employees;
SELECT employee_id, RTRIM (first_name) || ' ' || LTRIM (last_name) FROM employees;
SELECT employee_id, TRIM(last_name) || ' , ' || TRIM(first_name) FROM employees;
SELECT employee_id, RPAD(last_name, 30 , ' ' ), first_name FROM employees;
SELECT employee_id, SUBSTR(last_name, 1 , 10 ) FROM employees;
SELECT LENGTH(last_name) FROM employees;
SELECT employee_id, REPLACE (job_id, ' SH ' , ' SHIPPING ' ) FROM employees
WHERE SUBSTR(job_id, 1 , 2 ) = ' SH ' ;
Using Date Functions:
SELECT employee_id, TRUNC(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) "Months Employed"
FROM employees;
SELECT employee_id, EXTRACT( YEAR FROM hire_date) " Year Hired" FROM employees;
SELECT EXTRACT( YEAR FROM SYSDATE) || EXTRACT( MONTH FROM SYSDATE) ||
EXTRACT( DAY FROM SYSDATE) " Current Date" FROM DUAL;
SELECT employee_id, hire_date, ADD_MONTHS(hire_date, 3 ) FROM employees;
SELECT employee_id, hire_date, LAST_DAY(hire_date) "Last day of month "
FROM employees;
SELECT SYSTIMESTAMP FROM DUAL;
Using Conversion Functions:
SELECT TO_CHAR(SYSDATE, ' DD-MON-YYYY AD ' ) "Today" FROM DUAL;
SELECT TO_CHAR(SYSDATE, ' FMMonth DD YYYY ' ) "Today" FROM DUAL;
SELECT TO_CHAR(SYSDATE, ' MM-DD-YYYY HH24:MI:SS ' ) "Now" FROM DUAL;
SELECT hire_date, TO_CHAR(hire_date, ' DS ' ) "Short Date" FROM employees;
SELECT hire_date, TO_CHAR(hire_date, ' DL ' ) " Long Date" FROM employees;
SELECT TO_CHAR(EXTRACT( YEAR FROM SYSDATE)) ||
TO_CHAR(EXTRACT( MONTH FROM SYSDATE), ' FM09 ' ) ||
TO_CHAR(EXTRACT( DAY FROM SYSDATE), ' FM09 ' ) " Current Date" FROM DUAL;
SELECT TO_CHAR( CURRENT_DATE , ' DD-MON-YYYY HH24:MI:SS ' ) " Current Date" FROM DUAL;
SELECT TO_CHAR(salary, ' $99,999.99 ' ) salary FROM employees;
SELECT TO_NUMBER( ' 1234.99 ' ) + 500 FROM DUAL;
SELECT TO_NUMBER( ' 11,200.34 ' , ' 99G999D99 ' ) + 1000 FROM DUAL;
SELECT TO_DATE( ' 27-OCT-98 ' , ' DD-MON-RR ' ) FROM DUAL;
SELECT TO_DATE( ' 28-Nov-05 14:10:10 ' , ' DD-Mon-YY HH24:MI:SS ' ) FROM DUAL;
SELECT TO_DATE( ' January 15, 2006, 12:00 A.M. ' , ' Month dd, YYYY, HH:MI A.M. ' )
FROM DUAL;
SELECT TO_TIMESTAMP( ' 10-Sep-05 14:10:10.123000 ' , ' DD-Mon-RR HH24:MI:SS.FF ' )
FROM DUAL;
Using Aggregate Functions:
SELECT COUNT ( * ) "Employee Count " FROM employees WHERE manager_id = 122 ;
SELECT COUNT ( * ) "Employee Count ", manager_id FROM employees
GROUP BY manager_id ORDER BY manager_id
SELECT COUNT (commission_pct) FROM employees;
SELECT COUNT ( DISTINCT department_id) FROM employees;
SELECT MIN (salary), MAX (salary), AVG (salary), job_id FROM employees
GROUP BY job_id ORDER BY job_id;
SELECT RANK( 2600 ) WITHIN GROUP
( ORDER BY salary DESC ) "Rank of $ 2 , 600 among clerks"
FROM employees WHERE job_id LIKE ' %CLERK ' ;
SELECT job_id, employee_id, last_name, salary, DENSE_RANK() OVER
(PARTITION BY job_id ORDER BY salary DESC ) "Salary Rank (Dense)"
FROM employees WHERE job_id = ' SH_CLERK ' ;
SELECT employee_id, salary, hire_date, STDDEV(salary)
OVER ( ORDER BY hire_date) "Std Deviation of Salary"
FROM employees WHERE job_id = ' ST_CLERK ' ;
Using NULL Value Functions:
SELECT commission_pct, NVL(commission_pct, 0 ) FROM employees;
SELECT phone_number, NVL(phone_number, ' MISSING ' ) FROM employees;
SELECT employee_id , last_name,commission_pct, salary,
NVL2(commission_pct, salary + (salary * commission_pct), salary) income
FROM employees;
Using Conditional Functions:
SELECT employee_id, hire_date , salary,
CASE WHEN hire_date < TO_DATE( ' 01-JAN-90 ' ) THEN salary * 1.20
WHEN hire_date < TO_DATE( ' 01-JAN-92 ' ) THEN salary * 1.15
WHEN hire_date < TO_DATE( ' 01-JAN-94 ' ) THEN salary * 1.10
ELSE salary * 1.05 END "Revised Salary"
FROM employees;
SELECT employee_id, job_id , salary,
DECODE(job_id, ' PU_CLERK ' , salary * 1.05 ,
' SH_CLERK ' , salary * 1.10 ,
' ST_CLERK ' , salary * 1.15 ,
salary) "Revised Salary"
FROM employees;