SQL语法大全及实例54页

1、select的用法 -每个员工的所有信息 select * from emp; -每个人的部门编号，姓名，薪水 select empno, ename, sal from emp; -每个人的年薪 select ename, sal*12 from emp; -计算2*3的值 select 2*3 from emp; -计算2*3的值(dual) select 2*3 from dual; -得到当前时间 select sysdate from dual; -可以给列起别名,比如求每个人的年薪 select ename, sal*12 annual_sal from emp; -如果别名中有空

2、格，需要用双引号 select ename, sal*12 annual sal from emp; -如果没有内容，则为空 select ename, sal, comm from emp; /*null*/ -当空字段参与计算，则结果是null -例如：计算每个人的全年的收入包括月薪和年终奖 select ename, sal * 12 + comm from emp; -可以将多个字符串拼在一起。比如：求每个人的薪水，格式为smith-sal-123 select ename | - | sal | - | comm from emp; -如果字符串中有单引号，需要用另外一个单引号转义，

3、比如：这样一个字符串： hes friend select ename | hes friend from emp; distinct 关键词的用法 -求有哪些个部门 select deptno from emp; select distinct deptno from emp; -可以用来修饰多个字段 -求有哪些个部门和job的组合 select deptno , job from emp; select distinct deptno , job from emp; - where关键词的用法 -可以是数值类型的等值判断。比如：求10这个部门的所有员工 select * from emp

4、where deptno = 10; -可以是字符串类型的等值判断。比如：求叫KING的这个人的信息 select * from emp where ename = KING; -也可以是不等值判断。比如：求薪水小于2000的员工信息 select * from emp where sal 2000; -字符串也可以做不等值判断，比如：求所有ename大于CBA的员工信息。 select ename from emp where ename CBA; -求部门不是10的部门 select * from emp where deptno 10; -求薪水在800和1500之间的员工信息 sele

5、ct * from emp where sal between 800 and 1500; -也可以写成 select * from emp where sal = 800 and sal = 1500; /*这样写则不可以 -select * from emp where 800 = sal 20-2月-81; - -and or not的用法 -求薪水大于1000或者部门在10这个部门的员工信息 select * from emp where sal 1000 or deptno = 10; -求薪水不是800或者不是1500或者不是3000的员工信息 select * from emp

6、where sal 800 and sal 1500 and sal 3000; -也可以这样来写 select * from emp where sal not in (800, 1500, 3000); - -like的用法 -求名字中包含ALL这三个字符的员工信息 select ename from emp where ename like %ALL%; -求名字中的第二个字母是A的员工 select ename from emp where ename like _A%; -特殊字符需要转义。比如：求员工中包含特殊字符%的员工信息 select ename from emp where

7、 ename like % escape ; - -null的用法 -求没有年终奖的员工 select ename from emp where comm is null; -求有年终奖的员工 select ename from emp where comm is not null; - -order by的用法 -员工信息按照姓名正序排列 select ename, sal from emp order by ename asc; -ascent -员工信息按照倒叙排列 select ename, sal from emp order by ename desc; -descent -也可以

8、是多个字段组合排列。例如：员工信息按照部门正序排列，并且按照姓名倒叙排列 select ename, sal, deptno from emp order by deptno asc, ename desc; - -function的用法 -把所有姓名变成小写 select lower(ename) from emp; -把所有姓名变成大写 select upper(ename) from emp; -求所有人名中包含a的员工信息不区分大小写 select ename from emp where lower(ename) like %a%; -截取子字符串，比如求Hello的一部分 sele

9、ct substr(Hello, 2) from dual; -求Hello的一部分，并指明长度 select substr(Hello, 2, 3) from dual; -求ascii码对应的字符 select chr(65) from dual; -求字符对应的ascii码 select ascii(中) from dual; -四舍五入 select round(23.652) from dual; -四舍五入小数点后面多少位 select round(23.652, 1) from dual; -四舍五入小数点前面多少位 select round(23.652, -1) from d

10、ual; - -important!日期转换函数 - -将当前日期转换成1981-03-12 12:00:00这种形式的字符串 select to_char(sysdate, YYYY-MM-DD HH24:MI:SS) from dual; -将1981-03-12 12:00:00字符串转换成日期 select to_date(1981-03-12 12:00:00, YYYY-MM-DD HH24:MI:SS) from dual; -将每个人的薪水转换成固定格式的字符串 select to_char(sal, L00,000.9999) from emp; -将固定格式的字符串转换成数

11、值 select to_number($1,250.00, $9,999.99) from dual; -null当null参与计算时候，需要要nvl这个函数 select ename, sal*12+comm from emp; select ename, sal*12+ nvl(comm, 0) from emp; - -group function组函数 -求所有人的薪水的总和，平均值，最大值，最小值 select sum(sal) , avg(sal), max(sal) , min(sal) from emp; -求总的行数 select count(*) from emp; -求总

12、的行树，（可以指定具体的字段）但如果字段有null值的时候需要小心使用 select count(comm) from emp; -也可以过滤掉重复的行之后统计行数 select count(distinct deptno) from emp; -可以指明按照哪个字段进行分组.比如；分部门统计最高薪水 select deptno, max(sal) from emp group by deptno; -也可以按照多个字段来分组统计，比如：分部门和岗位，统计最高薪水和行数 select deptno, job , max(sal), count(*) from emp group by dept

13、no, job; - -重要：出现在select列表中的字段，如果没有在组函数中，那么必须出现在group by 子句中。 - -select ename, deptno, max(sal) from emp group by deptno; -select ename, max(sal) from emp; -求薪水最高的员工姓名 select max(sal) from emp; select ename, sal from emp where sal = 5000; select ename from emp where sal = (select max(sal) from emp);

14、 -having从句的用法 -求平均薪水是2000以上的部门 select avg(sal), deptno from emp group by deptno having avg(sal) 2000; - -总结一下select语法 select from where group by having order by - - 执行顺序very important! - 首先执行where语句将原有记录过滤； - 第二执行group by 进行分组； - 第三执行having过滤分组； - 然后将select 中的字段值选出来； - 最后执行order by 进行排序； - /* 按照部门分组

15、统计，求最高薪水，平均薪水 只有薪水是1200以上的才参与统计 并且分组结果中只包括平均薪水在1500以上的部门 而且按照平均薪水倒叙排列 */ select max(sal),avg(sal), deptno from emp where sal 1200 group by deptno having avg(sal) 1500 order by avg(sal) desc; - /* 把雇员按部门分组， 求最高薪水， 部门号， 过滤掉名字中第二个字母是A的， 要求分组后的平均薪水1500， 按照部门编号倒序排列 */ select deptno, max(sal) from emp whe

16、re ename not like _A% group by deptno having avg(sal) 1500 order by deptno desc; /* very very important! */ select ename, deptno from emp; select deptno, dname from dept; -员工姓名以及员工所在部门的名字同时显示出来 select ename, dname from emp , dept; select ename, dname from emp , dept where emp.deptno = dept.deptno; -

17、要求每位雇员的薪水等级 select ename, sal, grade from emp, salgrade where emp.sal = salgrade.losal and emp.sal = salgrade.losal and emp.sal (select avg(sal) from emp); - -雇员中哪些人是经理人 -1,首先查询mgr中有哪些号码 -2,再看有哪些人员的号码在此出现 select ename from emp where empno in ( select distinct mgr from emp where mgr is not null ); -

18、-在From子句中使用子查询 - -部门平均薪水的等级 -1,首先将每个部门的平均薪水求出来 -2,然后把结果当成一张表，再用这张结果表和salgrade表做连接，以此求得薪水等级 select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal); - -每个部门最高薪水的人员名称 -1,首先将每个部门的最高薪水求出来 -2,然后把结果当成一张表，再用e

19、mp和这张结果表做连接，以此求得每个部门最高薪水的人员名称 select ename, sal, emp.deptno from emp join (select deptno, max(sal) max_sal from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno); - -哪些人的薪水在部门的平均薪水之上 -1,首先将每个部门的平均薪水求出来 -2,然后把结果当成一张表，再用emp和这张结果表做连接，以此求得哪些人的薪水在部门的平均薪水之上 select ename, sal from e

20、mp join (select deptno, avg(sal) avg_sal from emp group by deptno) t on (emp.sal t.avg_sal and emp.deptno = t.deptno); - -求部门中(所有人的)平均的薪水等级,形式如： - deptno avg_grade - 10 3.67 - 20 2.8 - 30 2.5 -1,先求每个人的薪水等级 -2,再按照部门分组,求平均数 select deptno , avg(grade) from (select ename, deptno, grade from emp e join s

21、algrade s on (e.sal between s.losal and s.hisal) ) group by deptno; - -使用伪字段:rownum，- - -用来标识每条记录的行号，行号从1开始，每次递增1 select ename from emp where rownum = 5; -oracle下rownum只能使用 = 等比较操作符， select ename from emp where rownum 5; -当rownum和order by 一起使用时，会首先选出符合rownum条件的记录，然后再排序 -(错误的写法)例如，当我们要求薪水最高的前5个人时，最直接

22、的想法可以这样写： select ename, sal from emp where rownum = 5 order by sal desc; -(正确的写法)可以这样写 select ename, sal from (select ename, sal from emp order by sal desc) where rownum = 5; - -不准用组函数(即MAX(),求薪水的最高值(面试题) -第一种解决办法: -1,先把所有薪水按照倒序排列 -2,再取第一行 select sal from ( select sal from emp order by sal desc ) wh

23、ere rownum = 1; -第二种解决办法: -1,先跨表查询自己,先求出的结果中,e1.sal不可能出现最大数 -2,然后再not in select distinct sal from emp where sal not in ( select distinct e1.sal from emp e1 join emp e2 on (e1.sal e2.sal) ); - -求平均薪水最高的部门的部门编号 -第一种解决办法: -1,先求出每个部门的平均薪水, -2,再求每个部门的平均薪水的最高值, -3,最后再求第一步结果中avg_sal = 最高薪水的记录. select deptn

24、o from (select deptno, avg(sal) avg_sal from emp group by deptno) t where avg_sal = ( select max(avg_sal) from (select deptno, avg(sal) avg_sal from emp group by deptno) ); -第二种解决办法: -1,将上面的第一步第二步合并,先求最高平均薪水,用max(avg(sal)的办法 -2,求出每个部门的平均薪水 -3,最后再求第二步结果中(即每个部门的平均薪水),avg_sal = (第一步结果)的记录.即avg_sal =最高薪

25、水的记录. select deptno from (select deptno, avg(sal) avg_sal from emp group by deptno) t where avg_sal = (select max(avg(sal) from emp group by deptno); -第三种解决办法: -1,先求出每个部门的平均薪水, -2,求最高平均薪水,用max(avg(sal)的办法 -3,再使用having语句, avg(sal) = 第二步的结果 注意：为组函数起的别名在having中不能用 select deptno, avg(sal) from emp group

26、 by deptno having avg(sal) = (select max(avg(sal) from emp group by deptno); - -求平均薪水最高的部门的部门名称 -1,部门平均最高薪水 -2,得到部门编号列表,注意用group by deptno -3,再应用having子句, having avg(sal) = (第一步的结果) -4,得到平均最高薪水的那个部门的编号 -5,再得到部门名称 select dname from dept where deptno in ( select distinct deptno from emp group by deptn

27、o having avg(sal) = ( select max(avg(sal) from emp group by deptno ) ) - -求平均薪水的等级最低的部门的部门名称 -第一步：部门平均薪水的等级,分成两个小步骤,第一小步是求部门平均薪水 select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) -第二步：最低的等级值 sele

28、ct min(grade) from (第一步的结果) -第三步：等于最低值的部门编号 -有错误,应该是grade= select deptno from (第一步的结果) where grade = (第二步的结果) -第四步：求名称 select dname from dept where deptno in (第三步的结果) select dname from dept where deptno in (select deptno from ( select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from

29、 emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) ) where grade = ( select min(grade) from ( select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) ) ) ) -也可以用视图的方式来解决

30、 -conn sys/bjsxt as sysdba -grant create table, create view, create sequence to scott -根据第一步的结果,建立一个view create view v$_dept_info as select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal); -查看一下 desc

31、v$_dept_info; -查询一下 select * from v$_dept_info; -带入view select dname from dept where deptno in ( select deptno from ( v$_dept_info ) where grade = ( select min(grade) from ( v$_dept_info ) ) ) - -为什么in的后面不能order by ？ select dname from dept where deptno in (select deptno from salgrade s,( select avg(

32、sal) avg_sal,deptno from emp group by deptno ) t where t.avg_sal=s.losal and rownum=1 order by deptno) - -求部门经理人中平均薪水最低的部门名称 (思考题) - -求比普通员工的最高薪水还要高的经理人名称 -1,求所有经理的编号 select distinct mgr from emp where mgr is not null -2,普通员工的最高薪水 select max(sal) from emp where empno not in (select distinct mgr from

33、 emp where mgr is not null); -3, select ename, sal from emp where empno in (第一步的结果) and sal (第二步的结果) -即: select ename, sal from emp where empno in (select distinct mgr from emp where mgr is not null) and sal (select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not

34、null); - -求薪水最高的前5名雇员 -1,先观察一下 select rownum , ename , sal from emp; -2,看看rownum的作用 select rownum ,ename, sal from emp where rownum = 5; -3,不是我们想要的结果 select rownum ,ename, sal from emp where rownum = 5 order by sal desc; -4,先order by,再rownum select rownum ,ename, sal from (select ename, sal from emp

35、 order by sal desc) where rownum = 5; - -求薪水最高的第6到第10名雇员(重点掌握) -这种没法实现,oracle下rownum只能使用 = 等比较操作符 -注意里面的rownum和外面的rownum的区别，外面要想访问里面的rownum，必须取得一个别名。 select ename, sal, rownum from (select ename, sal from emp order by sal desc) where rownum = 6 and rownum = 6 and r = 10; -还有一种排序方式 minus - -练习: 求最后入职

36、的5名员工 -1,每个人的入职时间 select ename, hiredate from emp order by hiredate desc -2,取前5行 select ename, hiredate from (select ename, hiredate from emp order by hiredate desc) where rownum = v$_t2.min_r and v$_t.r = v$_t2.min_r + 1 and v$_t.deptno = v$_t2.deptno); - -面试题: 比较效率 select * from emp where deptno =

37、 10 and ename like %A%; select * from emp where ename like %A% and deptno = 10; - -使用union、minus -使用union、minus可以用来实现结果集的合并和去除（可以理解为加和减），例如： select ename, empno from emp where deptno = 10 union select ename, empno from emp where deptno = 20 -相当于 select ename, empno from emp where deptno = 10 or deptno = 20; -而下面的语句 select ename, empno from emp where deptno in (10,20) minus select ename, empno from emp where sal = 1500; -求分段显示薪水的个数 select ename, sal from emp where