SQL92联合查询
---多表联合查询： --当需要获取的数据分布在多张中，考虑使用联合查询 --SQL92方式 --SQL99方式 ------------------------------------------- --SQL92方式 --笛卡尔积:将多个表的数据进行一一对应，所得到结果为多表的笛卡尔积。 --结果的数量为所有表的数量的乘积。 select * from emp,dept where emp.deptno=dept.deptno --等值连接筛选 --概念：先做表的笛卡尔积，然后筛选，筛选条件为等值筛选。 --注意：条件为字段的值相同来进行筛选，字段的名字可以不同 --查询员工姓名，工作，薪资，部门名称 select * from emp,dept where emp.deptno=dept.deptno --可以直接在select子句中使用字段直接获取数据，但是效率比较低。建议字段前加上表名 --注意：如果是公共字段，则必须声明表名 select ename,job,sal,dname from emp,dept where emp.deptno=dept.deptno--等值连接筛选 select emp.ename,emp.job,emp.sal,dept.dname,emp.deptno from emp,dept where emp.deptno=dept.deptno select e.ename,e.job,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and sal>2000--给表使用别名 --不等值连接 --查询员工姓名，工作，工资，工资等级 select * from emp e,salgrade s where e.sal>=s.losal and e.sal<=s.hisal --自连接: --查询员工姓名，工作，薪资，及上级领导姓名 select e1.ename,e1.job,e1.sal,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno --外连接 --左外连接:加在右边，显示左边对应字段没有值的数据 --查询员工姓名，工作，薪资，部门名称及没有部门的员工信息 select * from emp e,dept d where e.deptno=d.deptno(+) --右外连接:加在做边，显示右边边对应字段没有值的数据 --查询员工姓名，工作，薪资，部门名称及没有员工信息的部门 select * from emp e,dept d where e.deptno(+)=d.deptno
SQL92联合查询
---多表联合查询： --当需要获取的数据分布在多张中，考虑使用联合查询 --SQL92方式 --SQL99方式 ---------------------------------------------------------------- --SQL99多表查询 --注意1：依然可以给表添加别名 --注意2：如果使用on或者usering关键对结果进行筛选，必须使用inner join作用表与表的连接，其中inner可以省略 --注意3:外连接的 outer关键字可以省略不写 --注意4：依然可以继续使用分组，having ，排序等 --笛卡尔积:使用cross join 关键字 ---select 内容 from 表名 cross join select * from emp cross join dept --筛选 --查询员工姓名，工作，薪资，部门名称 --自然连接：使用关键字 natural join --使用：select 内容 from 表名 natural join 表名 --特点1：底层先笛卡尔积，然后按照所有的同名同值字段自动进行等值筛选。 --问题1：如果只想按照部分字段结果筛选怎么办？ --问题2：如果想按照字段名不同，但是值相同进行等值筛选怎么办？ select * from emp natural join dept --解决1：使用using关键字 --作用1：指明使用指定的字段对联合查询的结果进行等值筛选 --注意：指明的字段必须是两表的同名同值字段 --使用:select 内容 from 表名 inner join 表名 using(字段名，字段名,....) select * from emp inner join dept using(deptno) --解决2：使用on关键字进行自定义连接条件筛选(等值筛选，不等值筛选) --注意：普通筛选条件使用where进行筛选，不要使用on进行。好处：SQL语句的阅读性变强。 --使用：select 内容 from 表名 inner join 表名 on 连接条件 where 普通筛选条件 select * from emp inner join dept on emp.deptno=dept.deptno where sal>2000 --外连接： --左外连接:select 内容 from 表名 left outer join 表名 on 连接条件 --查询员工姓名，工作，薪资，部门名称及没有部门的员工信息 select * from emp e left outer join dept d on e.deptno=d.deptno --右外连接:select 内容 from 表名 right outer join 表名 on 连接条件 --查询员工姓名，工作，薪资，部门名称及没有员工的部门信息 select * from emp e right outer join dept d on e.deptno=d.deptno --全外连接：select 内容 from 表名 full outer join 表名 on 连接条件 select * from emp e full outer join dept d on e.deptno=d.deptno --自连接: --查询员工及其上级领导姓名 select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno
SQL92&SQL99实现三表联合查询
--SQL92&SQL99实现三表联合查询 --创建city表：使用图形操作即可 --给city表添加测试数据 insert into city values(1,'商丘','历史闻名古都'); insert into city values(2,'邯郸','历史闻名古都'); insert into city values(3,'洛阳','历史闻名古都'); insert into city values(4,'开封','历史闻名古都'); --将部门表中的loc字段设置为城市表的城市编号 update dept set loc='1' where deptno=50; update dept set loc='2' where deptno=40; update dept set loc='3' where deptno=30; update dept set loc='4' where deptno=20; update dept set loc='4' where deptno=10; --完成三表联合查询 --SQL92实现：查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金 --特点：易于书写，难于阅读 --缺点：92的SQL语句结构不清晰 --用法： --select 内容 (别名，连接符，去除重复，oracle函数，逻辑运算) --from 表名1，表名2，表名3... --where 条件(连接条件，普通筛选条件，where子句关键字) --group by 分组字段 --having 多行函数筛选 --order by 排序字段 select e.*,d.dname,c.cname from emp e,dept d,city c where (e.deptno=d.deptno and d.loc=c.cid and sal>2000) or (e.deptno=d.deptno and d.loc=c.cid and comm is not null) order by e.sal --SQL99实现:查询员工信息及部门名称及所在城市名称并且员工的工资大于2000或者有奖金 --特点：难于书写，易于阅读 --使用： --select 内容 from 表名1 -- inner join 表名2 -- on 连接条件 --inner join 表名3 --on 连接条件 --where 普通筛选条件 --group by 分组 --having 多行函数筛选 --order by 排序 select * from emp e inner join dept d on e.deptno = d.deptno inner join city c on d.loc =c.cid where e.sal>2000 or e.comm is not null order by e.sal
子查询
单行子查询和多行子查询
1 --单表查询： 2 --当需要的数据在一张表中，考虑使用单表查询 3 4 --多表联合查询： 5 --当需要查询的数据分布在多张表中，考虑使用多表联合 6 7 --子查询学习： 8 --使用时机：当查询的筛选条件不明确时，考虑使用子查询。 9 --单行子查询 10 --多行子查询 11 ---------------------------------------------------------------- 12 --单行子查询: 13 --使用时机：筛选条件不明确需要执行一次查询，并且查询结果一个字段并值只有一个 14 --注意：where子句中允许出现查询语句，该查询语句称为子查询 15 --使用：select 内容 from 表名 where 字段名 比较运算符 子查询语句 16 17 --查询所有比雇员“CLARK”工资高的员工信息 18 select * from emp where sal>(select sal from emp where ename ='CLARK') 19 20 --查询工资高于平均工资的员工的名字和工资 21 select ename,sal from emp where sal>(select avg(sal) from emp ) 22 23 --查询和soctt属于同一部门且工资比他低的员工资料 24 select * from emp 25 where deptno=(select deptno from emp where ename='SCOTT') 26 and sal<(select sal from emp where ename='SCOTT') 27 28 --查询工资最高的员工资料 29 select * from emp where sal=(select max(sal) from emp) 30 31 --查询职务和scott相同，雇佣时间早的员工信息 32 select * from emp 33 where job=(select job from emp where ename='SCOTT') 34 and hiredate <(select hiredate from emp where ename='SCOTT') 35 36 --查询工资比scott高或者雇佣时间早的员工编号和名字 37 select empno,ename from emp 38 where job=(select job from emp where ename='SCOTT') 39 or hiredate <(select hiredate from emp where ename='SCOTT') 40 41 ---------------------------------------------------------------------------- 42 43 ----多行子查询： 44 --使用:子查询的结果只有一个字段但是字段有n个值，考虑使用多行子查询，其实就是使用关键字 45 --关键字1:any 任意 46 --select 内容 from 表名 where 字段名 比较运算符 any 子查询语句 47 --关键字2：all 所有 48 --select 内容 from 表名 where 字段名 比较运算符 all 子查询语句 49 --关键字3：in 表示任意存在,相当于 = any 50 --select 内容 from 表名 where 字段名 in 子查询语句 51 --select 内容 from 表名 where 字段名 not in 子查询语句 52 53 --查询工资高于任意一个CLERK的所有员工信息 54 select * from emp where sal> (select min(sal) from emp where job='CLERK') 55 select * from emp where sal> any (select sal from emp where job='CLERK') 56 57 --查询工资高于所有SALESMAN的员工信息 58 select * from emp where sal> (select max(sal) from emp where job='SALESMAN') 59 select * from emp where sal> all (select sal from emp where job='SALESMAN') 60 61 --查询部门20中同部门10的雇员工作一样的雇员信息 62 select job from emp where deptno=10 63 select *from emp where (job='MANAGER' or job='PRESIDENT' or job='CLERK') and deptno=20 64 select * from emp where job in (select job from emp where deptno=10) and deptno=20 65 select * from emp where job = any (select job from emp where deptno=10) and deptno=20
练习
1 --1.列出所有雇员的姓名及其直接上级的姓名 2 select e1.ename,e2.ename from emp e1 3 inner join emp e2 4 on e1.mgr=e2.empno 5 --2.列出部门名称和这些部门的雇员,同时列出那些没有雇员的部门; 6 select * from emp e 7 right join dept d 8 on e.deptno=d.deptno 9 --3.显示所有部门在"洛阳"(dept表 loc字段)的员工姓名 10 select * from emp e 11 inner join dept d 12 on e.deptno=d.deptno 13 inner join city c 14 on d.loc=c.cid 15 where c.cname='洛阳' 16 --4.显示员工"WARD"的姓名，部门名称 17 select e.ename,d.dname from emp e 18 inner join dept d 19 on e.deptno=d.deptno 20 where e.ename='WARD' 21 --5.显示员工姓名，部门名称，工资，工资级别(salgrade表 grade字段),要求工资级别大于4级 22 select * from emp e 23 inner join dept d 24 on e.deptno=d.deptno 25 inner join salgrade s 26 on e.sal> =s.losal and e.sal<=s.hisal 27 where s.grade>4 28 --6.显示员工"KING"和"FORD"管理的员工姓名及其经理姓名 29 select * from emp e1 30 full join emp e2 31 on e1.mgr=e2.empno 32 inner join emp e3 33 on e1.empno=e3.mgr 34 where e1.ename='KING' or e1.ename='FORD' 35 --7.显示员工名，参加工作时间，经理名，参加工作时间：参加工作时间比他的经理早 36 select e1.ename,e1.hiredate,e2.ename,e2.hiredate from emp e1 37 inner join emp e2 38 on e1.mgr=e2.empno 39 where e1.hiredate<e2.hiredate 40 --8、求出部门名称中，带'S'字符的部门员工的工资总和 、部门人数 41 select d.dname, sum(e.sal),count(*) from emp e 42 inner join dept d 43 on e.deptno=d.deptno 44 where d.dname like '%S%' 45 group by d.dname 46 47 --9、列出所有员工的年工资，按年薪从低到高排序 48 select ename,(sal+nvl(comm,0))*12 年薪 from emp order by 年薪 49 --10、列出各种工作的最低工资以及从事此工作的雇员姓名 50 select * from emp e ,(select min(sal) msal,job from emp group by job) s where e.job=s.job and e.sal=s.msal 51 --11、列出所有部门的详细信息和部门人数 52 select * from dept d ,(select d.deptno, count(empno) from emp e,dept d where e.deptno(+)=d.deptno group by d.deptno) s 53 where d.deptno=s.deptno 54 --12、列出所有员工的姓名、部门名称和工资 55 select e.ename,d.dname,e.sal from emp e 56 inner join dept d 57 on e.deptno=d.deptno 58 --13、查询员工信息： 员工的名字 老板的名字 59 select e.ename,m.ename from emp e,(select * from emp where mgr is null) m 60 --14、要求查询出每一个雇员的编号、姓名、工资、部门名称、工资在所在公司的工资等级 61 --15、按照职位分组，求出每个职位的最高工资、最低工资以及平均工资 62 select job,max(sal),min(sal),avg(sal) from emp group by job 63 --16、统计平均工资大于2000的部门的详细信息 64 select * from dept d,(select deptno, avg(sal) asal from emp group by deptno) s 65 where d.deptno=s.deptno and s.asal>2000