OracleSQL語法大全

1、1. 對于日期型數據, 做*, / 運算不合法2. 包含空值的數學表達式的值都為空值3. 別名使用雙引號!4. oracle 中連接字符串使用"|", 而不是java 中的"+"5. 日期和字符只能在單引號中出現(xiàn). 輸出last_names email is emailselect last_name | ' s email is ' | emailfrom employees6. distinct 關鍵字, 以下語法錯誤select last_name, distinct department_idfrom employees7. WH

2、ERE 子句緊隨FROM 子句8. 查詢last_name 為'King' 的員工信息錯誤1: King 沒有加上單引號select first_name, last_namefrom employeeswhere last_name = King錯誤2: 在單引號中的值區(qū)分大小寫select first_name, last_namefrom employeeswhere last_name = 'king'正確select first_name, last_namefrom employeeswhere last_name = 'King'9

3、. 查詢1998-4-24 來公司的員工有哪些?注意: 日期必須要放在單引號中, 且必須是指定的格式select last_name, hire_datefrom employeeswhere hire_date = '24-APR -98'10. 查詢工資在5000 - 10000 之間的員工信息.1. 使用ANDselect *from employeeswhere salary >= 5000 and salary <= 100002. 使用BETWEEN . AND ., 注意: 包含邊界!select *from employeeswhere salary

4、 between 5000 and 1000011. 查詢工資等于6000, 7000, 8000, 9000, 10000 的員工信息1. 使用ORselect *from employeeswhere salary = 6000 or salary = 7000 or salary = 8000 or salary = 9000 or salary = 100002. 使用INselect *from employeeswhere salary in (6000, 7000, 8000, 9000, 1000012. 查詢LAST_NAME 中有'o' 字符的所有員工信息.

5、select *from employeeswhere last_name like '%o%'13. 查詢LAST_NAME 中第二個字符是'o' 的所有員工信息.select *from employeeswhere last_name like '_o%'14. 查詢LAST_NAME 中含有'_' 字符的所有員工信息1. 準備工作:update employeesset last_name = 'Jones_Tom'where employee_id = 1952. 使用escape 說明轉義字符.sele

6、ct *from employeeswhere last_name like '%_%' escape ''15. 查詢COMMISSION_PCT 字段為空的所有員工信息select last_name, commission_pctfrom employeeswhere commission_pct is null16. 查詢COMMISSION_PCT 字段不為空的所有員工信息select last_name, commission_pctfrom employeeswhere commission_pct is not null17. ORDER BY:

7、1. 若查詢中有表達式運算, 一般使用別名排序2. 按多個列排序: 先按第一列排序, 若第一列中有相同的, 再按第二列排序.18. 打印出"2009年10月14日9:25:40" 格式的日期和時間.select to_char(sysdate, 'YYYY"年"MM"月"DD"日" HH:MI:SS'from dual注意: 使用雙引號向日期中添加字符19. 格式化數字: 1234567.89 為1,234,567.89select to_char(1234567.89, '999,999,

8、999.99'from dual20. 字符串轉為數字時1. 若字符串中沒有特殊字符, 可以進行隱式轉換:select '1234567.89' + 100from dual2. 若字符串中有特殊字符, 例如'1,234,567.89', 則無法進行隱式轉換, 需要使用to_number( 來完成select to_number('1,234,567.89', '999,999,999.99' + 100from dual21. 對于把日期作為查詢條件的查詢, 一般都使用to_date( 把一個字符串轉為日期, 這樣可以不

9、必關注日期格式select last_name, hire_datefrom employeeswhere hire_date = to_date('1998-5-23', 'yyyy-mm-dd'22. 轉換函數: to_char(, to_number(, to_date(23. 查詢每個月倒數第2 天入職的員工的信息.select last_name, hire_datefrom employeeswhere hire_date = last_day(hire_date - 224. 計算公司員工的年薪-錯誤寫法: 因為空值計算的結果還是空值select

10、last_name, salary * 12 * (1 + commission_pct year_salfrom employees-正確寫法select last_name, salary * 12 * (1 + nvl(commission_pct, 0 year_salfrom employees25. 查詢部門號為10, 20, 30 的員工信息, 若部門號為10, 則打印其工資的1.1 倍, 20 號部門, 則打印其工資的1.2 倍, 30 號部門打印其工資的1.3 倍數-使用case-when-then-else-endselect last_name, department_i

11、d, salary, case department_id when 10 then salary * 1.1when 20 then salary * 1.2when 30 then salary * 1.3end new_salfrom employeeswhere department_id in (10, 20, 30-使用decodeselect last_name, department_id, salary, decode(department_id, 10, salary * 1.1,20, salary * 1.2,30, salary * 1.3new_salfrom em

12、ployeeswhere department_id in (10, 20, 3026. 多表連接查詢時, 若兩個表有同名的列, 必須使用表的別名對列名進行引用, 否則出錯!27. 查詢出公司員工的last_name, department_name, cityselect last_name, department_name, cityfrom departments d, employees e, locations lwhere d.department_id = e.department_id and d.location_id = l.location_id28. 查詢出last_n

13、ame 為'Chen' 的manager 的信息. (員工的manager_id 是某員工的employee_id0. 例如: 老張的員工號為: "1001", 我的員工號為: "1002",我的manager_id 為"1001" - 我是manager 是"老張"1. 通過兩條sql 查詢:select manager_idfrom employeeswhere lower(last_name = 'chen' -返回的結果為108select *from employeeswh

14、ere employee_id = 1082. 通過一條sql 查詢(自連接:select m.*from employees e, employees mwhere e.manager_id = m.employee_id and e.last_name = 'Chen'3. 通過一條sql 查詢(子查詢:select *from employeeswhere employee_id = (select manager_idfrom employeeswhere last_name = 'Chen'29. 查詢每個員工的last_name 和GRADE_LEV

15、EL(在JOB_GRADES 表中. - 非等值連接select last_name, salary, grade_level, lowest_sal, highest_salfrom employees e, job_grades jwhere e.salary >= j.lowest_sal and e.salary <= j.highest_sal30. 左外連接和右外連接select last_name, e.department_id, department_namefrom employees e, departments dwhere e.department_id

16、= d.department_id(+select last_name, d.department_id, department_namefrom employees e, departments dwhere e.department_id(+ = d.department_id理解"(+" 的位置: 以左外連接為例, 因為左表需要返回更多的記錄,右表就需要"加上" 更多的記錄, 所以在右表的鏈接條件上加上"(+"注意: 1. 兩邊都加上"(+" 符號, 會發(fā)生語法錯誤!2. 這種語法為Oracle 所獨有, 不

17、能在其它數據庫中使用.31. SQL 99 鏈接Employees 表和Departments 表1.select *from employees join departmentsusing(department_id缺點: 要求兩個表中必須有一樣的列名.2.select *from employees e join departments don e.department_id = d.department_id3.多表鏈接select e.last_name, d.department_name, l.cityfrom employees e join departments don e.

18、department_id = d.department_idjoin locations lon d.location_id = l.location_id32. SQL 99 的左外連接, 右外連接, 滿外連接1.select last_name, department_namefrom employees e left join departments don e.department_id = d.department_id2.select last_name, department_namefrom employees e right join departments don e.d

19、epartment_id = d.department_id3.select last_name, department_namefrom employees e full join departments don e.department_id = d.department_id33. 查詢employees 表中有多少個部門select count(distinct department_idfrom employees34. 查詢全公司獎金基數的平均值(沒有獎金的人按0 計算select avg(nvl(commission_pct, 0from employees35. 查詢各個部門的

20、平均工資-錯誤: avg(salary 返回公司平均工資, 只有一個值; 而department_id 有多個值, 無法匹配返回select department_id, avg(salaryfrom employees*在SELECT 列表中所有未包含在組函數中的列都應該包含在GROUP BY 子句中-正確: 按department_id 進行分組select department_id, avg(salaryfrom employeesgroup by department_id36. Toronto 這個城市的員工的平均工資select avg(salary, cityfrom empl

21、oyees e join departments don e.department_id = d.department_idjoin locations lon d.location_id = l.location_idwhere city = 'Toronto'group by city37. (有員工的城市各個城市的平均工資select avg(salary, cityfrom employees e join departments don e.department_id = d.department_idjoin locations lon d.location_id

22、= l.location_idgroup by city38. 查詢平均工資高于8000 的部門id 和它的平均工資.-錯誤: 先過濾, 后分組. 但在過濾時, 還沒有分組, 所以無法使用組函數的結果select department_id, avg(salary avg_salfrom employeeswhere avg_sal > 8000group by department_id* 不能在WHERE 子句中使用組函數! 可以在HAVING 子句中使用組函數select department_id, avg(salary avg_salfrom employeesgroup by

23、 department_idhaving avg(salary > 800039. 查詢平均工資高于6000 的job_title 有哪些select job_titlefrom jobs j join employees eon j.job_id = e.job_idgroup by job_titlehaving avg(salary > 600040. 誰的工資比Abel 高?1. 寫兩條SQL 語句.SELECT salaryFROM employeesWHERE last_name = 'Abel'-返回值為11000SELECT last_name, s

24、alaryFROM employeesWHERE salary > 110002. 使用子查詢- 一條SQL 語句SELECT last_name, salaryFROM employeesWHERE salary > (SELECT salaryFROM employeesWHERE last_name = 'Abel'41. 子查詢注意:1. 子查詢要包含在括號內2. 將子查詢放在比較條件的右側42. 查詢工資最低的員工信息: last_name, salarySELECT last_name, salaryFROM employeesWHERE salary

25、= (SELECT min(salaryFROM employees43. 查詢平均工資最低的部門信息SELECT *FROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary = (SELECT min(avg(salaryFROM employeesGROUP BY department_id44. 查詢平均工資最高的job 信息1. 按job_id 分組, 查詢最高的平均工資SELECT max(avg(salaryFROM

26、 employeesGROUP BY job_id2. 查詢出平均工資等于1 的job_idSELECT job_idFROM employeesGROUP BY job_idHAVING avg(salary = (SELECT max(avg(salaryFROM employeesGROUP BY job_id3. 查詢出2 對應的job 信息SELECT *FROM jobsWHERE job_id = (SELECT job_idFROM employeesGROUP BY job_idHAVING avg(salary = (SELECT max(avg(salaryFROM em

27、ployeesGROUP BY job_id45. 查詢平均工資高于公司平均工資的部門有哪些?1. 查詢出公司的平均工資SELECT avg(salaryFROM employees2. 查詢平均工資高于1 的部門IDSELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary > (SELECT avg(salaryFROM employees46. 查詢出公司中所有manager 的詳細信息.1. 查詢出所有的manager_idSELECT distinct manager_idFROM empl

28、oyeess2. 查詢出employee_id 為1 查詢結果的那些員工的信息SELECT employee_id, last_nameFROM employeesWHERE employee_id in (SELECT distinct manager_idFROM employees47. 各個部門中最高工資中最低的那個部門的最低工資是多少1. 查詢出各個部門的最高工資SELECT max(salaryFROM employeesGROUP BY department_id2. 查詢出1 對應的查詢結果的最低值: 各個部門中最低的最高工資(無法查詢對應的department_idSELEC

29、T min(max(salaryFROM employeesGROUP BY department_id3. 查詢出2 所對應的部門id 是多少: 各個部門中最高工資等于2 的那個部門的idSELECT department_idFROM employeesGROUP BY department_idHAVING max(salary = (SELECT min(max(salaryFROM employeesGROUP BY department_id4. 查詢出3 所在部門的最低工資SELECT min(salaryFROM employeesWHERE department_id = (

30、SELECT department_idFROM employeesGROUP BY department_idHAVING max(salary = (SELECT min(max(salaryFROM employeesGROUP BY department_id48. 查詢平均工資最高的部門的manager 的詳細信息: last_name, department_id, email, salary1. 各個部門中, 查詢平均工資最高的平均工資是多少SELECT max(avg(salaryFROM employeesGROUP BY department_id2. 各個部門中, 平均工

31、資等于1 的那個部門的部門號是多少SELECT department_idFROM employeesGROUP BY department_idHAVING avg(salary = (SELECT max(avg(salaryFROM employeesGROUP BY department_id3. 查詢出2 對應的部門的manager_idSELECT manager_idFROM departmentsWHERE department_id = (SELECT department_idFROM employeesGROUP BY department_idHAVING avg(sa

32、lary = (SELECT max(avg(salaryFROM employeesGROUP BY department_id4. 查詢出employee_id 為3 查詢的manager_id 的員工的last_name, department_id, email, salarySELECT last_name, department_id, email, salaryFROM employeesWHERE employee_id = (SELECT manager_idFROM departmentsWHERE department_id = (SELECT department_id

33、FROM employeesGROUP BY department_idHAVING avg(salary = (SELECT max(avg(salaryFROM employeesGROUP BY department_id49. 查詢1999 年來公司的人所有員工的最高工資的那個員工的信息.1. 查詢出1999 年來公司的所有的員工的salarySELECT salaryFROM employeesWHERE to_char(hire_date, 'yyyy' = '1999'2. 查詢出1 對應的結果的最大值SELECT max(salaryFROM e

34、mployeesWHERE to_char(hire_date, 'yyyy' = '1999'3. 查詢工資等于2 對應的結果且1999 年入職的員工信息SELECT *FROM employeesWHERE to_char(hire_date, 'yyyy' = '1999' AND salary = (SELECT max(salaryFROM employeesWHERE to_char(hire_date, 'yyyy' = '1999'50. 多行子查詢的any 和allselect d

35、epartment_idfrom employeesgroup by department_idhaving avg(salary >= any (-所有部門的平均工資select avg(salaryfrom employeesgroup by department_idany 和任意一個值比較, 所以其條件最為寬松, 所以實際上只需和平均工資最低的比較, 返回所有值而all 是和全部的值比較, 條件最為苛刻, 所以實際上返回的只需和平均工資最高的比較, 所以返回平均工資最高的department_id51. 利用子查詢創(chuàng)建表myemp, 該表中包含employees 表的employ

36、ee_id(id, last_name(name, salary(sal, email 字段1. 創(chuàng)建表的同時復制employees 對應的記錄create table myempasselect employee_id id, last_name name, salary sal, email from employees2. 創(chuàng)建表的同時不包含employees 中的記錄, 即創(chuàng)建一個空表create table myempasselect employee_id id, last_name name, salary sal, email from employees where 1 =

37、252. 對現(xiàn)有的表進行修改操作1. 添加一個新列ALTER TABLE myemp ADD(age number(32. 修改現(xiàn)有列的類型ALTER TABLE myemp MODIFY(name varchar2(30;3. 修改現(xiàn)有列的名字ALTER TABLE myemp RENAME COLUMN sal TO salary;4. 刪除現(xiàn)有的列ALTER TABLE myemp DROP COLUMN age;53. 情空表, 不能回滾!54. 復制employees 表中100 號部門的信息到emp 中新建emp 表, 其表結構為:Name Type Nullable Defaul

38、t Comments- - - - -NAME VARCHAR2(25EMAIL VARCHAR2(25SAL NUMBER(8,2 YHIRE_DATE DATE Ycreate table emp as select last_name name, email, salary sal, hire_date from employees where 1 = 2;insert into emp select last_name name, email, salary sal, hire_date from employees where department_id = 100;55. 更改10

39、8 員工的信息: 使其工資變?yōu)樗诓块T中的最高工資, job 變?yōu)楣局衅骄べY最低的job1. 搭建骨架update employees set salary = (, job_id = (where employee_id = 108;2. 所在部門中的最高工資select max(salaryfrom employeeswhere department_id = (select department_idfrom employeeswhere employee_id = 1083. 公司中平均工資最低的jobselect job_idfrom employeesgroup by job_

40、idhaving avg(salary = (select min(avg(salaryfrom employeesgroup by job_id4. 填充update employees set salary = (select max(salaryfrom employeeswhere department_id = (select department_idfrom employeeswhere employee_id = 108, job_id = (select job_idfrom employeesgroup by job_idhaving avg(salary = (selec

41、t min(avg(salaryfrom employeesgroup by job_idwhere employee_id = 108;56. 刪除108 號員工所在部門中工資最低的那個員工.1. 查詢108 員工所在的部門idselect department_idfrom employeeswhere employee_id = 108;2. 查詢1 部門中的最低工資select min(salaryfrom employeeswhere department_id = (select department_idfrom employeeswhere employee_id = 108;

42、3. 刪除1 部門中工資為2 的員工信息delete from employees where salary = (select min(salaryfrom employeeswhere department_id = (select department_idfrom employeeswhere employee_id = 108and department_id = (select department_idfrom employeeswhere employee_id = 10857. 定義非空約束1. 非空約束只能定義在行級.2. 不指定約束名create table emp2 (

43、name varchar2(30 not null, age number(3;3. 指定約束名create table emp3(name varchar2(30 constraint name_not_null not null, age number(3;58. 唯一約束1. 行級定義. 不指定約束名create table emp2 (name varchar2(30 unique, age number(3;. 指定約束名create table emp3 (name varchar2(30 constraint name_uq unique, age number(3;2. 表級定

44、義: 必須指定約束名. 指定約束名create table emp3 (name varchar2(30, age number(3 constraint name_uq unique(name;59. 外鍵約束1. 行級定義. 不指定約束名create table emp2(emp_id number(6,name varchar2(25,dept_id number(4 references dept2(dept_id. 指定約束名create table emp3(emp_id number(6,name varchar2(25,dept_id number(4 constraint dept_fk3 reference