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);
5 绑定变量查询：Oracle将已解析、已编译的SQL连同其他内容存储在共享池中，这是SGA中一个非常重要的存储结构（内存结构主要分为SGA和PGA）。而绑定变量查询在Oracle里执行时只编译一次，随后就会把这个查询计划存储在一个共享池中以便重用，即所谓的软解析。具体的使用方法如下： SELECT * FROM employees WHERE employee_id = :employee_id6 查询虚拟列：所谓的虚拟列在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;
转载于:https://www.cnblogs.com/goodyao/archive/2011/04/06/2006967.html
相关资源：数据结构—成绩单生成器