OracleSQL语法大全

1、1. 对于日期型数据, 做*, / 运算不合法2. 包含空值的数学表达式的值都为空值3. 别名使用双引号!4. oracle 中连接字符串使用"|", 而不是java 中的"+"5. 日期和字符只能在单引号中出现. 输出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: 在单引号中的值区分大小写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. 两边都加上"(+" 符号, 会发生语法错误!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. 利用子查询创建表myemp, 该表中包含employees 表的employ

36、ee_id(id, last_name(name, salary(sal, email 字段1. 创建表的同时复制employees 对应的记录create table myempasselect employee_id id, last_name name, salary sal, email from employees2. 创建表的同时不包含employees 中的记录, 即创建一个空表create table myempasselect employee_id id, last_name name, salary sal, email from employees where 1 =

37、252. 对现有的表进行修改操作1. 添加一个新列ALTER TABLE myemp ADD(age number(32. 修改现有列的类型ALTER TABLE myemp MODIFY(name varchar2(30;3. 修改现有列的名字ALTER TABLE myemp RENAME COLUMN sal TO salary;4. 删除现有的列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 员工的信息: 使其工资变为所在部门中的最高工资, job 变为公司中平均工资最低的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