《SQL语法大全及实例(共54页).doc》由会员分享，可在线阅读，更多相关《SQL语法大全及实例(共54页).doc（54页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
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
2、emp; -如果别名中有空格，需要用双引号 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这个部门的所有员工 selec
4、t * from emp 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和150
5、0之间的员工信息 select * 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的员工信息 selec
6、t * from emp 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 f
7、rom emp where 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;
8、-descent -也可以是多个字段组合排列。例如：员工信息按照部门正序排列，并且按照姓名倒叙排列 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%; -截取子字符串，比如求H
9、ello的一部分 select 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.65
10、2, -1) from dual; - -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(*)
12、from emp; -求总的行树，（可以指定具体的字段）但如果字段有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
13、group by deptno, 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(sa
14、l) from emp); -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)
16、 from emp where 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 = d
17、ept.deptno; -要求每位雇员的薪水等级 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 n
18、ot null ); - -在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,然
19、后把结果当成一张表，再用emp和这张结果表做连接，以此求得每个部门最高薪水的人员名称 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 enam
20、e, sal from emp 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
21、 emp e join salgrade 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条件的记录，然后再排序 -(错误的写法)例如，当我们要求薪
22、水最高的前5个人时，最直接的想法可以这样写： 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
23、sal desc ) where 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 = 最高薪水的记录.
24、 select deptno 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 = (第一步结果)的记录.
25、即avg_sal =最高薪水的记录. 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) f
26、rom emp group 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 g
27、roup by deptno 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) -第二
28、步：最低的等级值 select 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)
29、 avg_sal from 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)
31、; -查看一下 desc 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(sal) avg_sal,deptno from emp group by deptno ) t where t.avg_sal=s.losal and rownum=1 order by deptno) - -求部门经理人中平均薪水最低的部门名称 (思考题) -