sql数据库查询相关操作,SQL的应用——SQL多表连查、子查询、多行子查询
1
|
**SQL多表连查** |
1
2
3
4
5
6
7
8
|
--查询员工和部门信息 select * from emp e,dept d where e.deptno=d.deptno --查询员工姓名,部门名称 select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno --查询所有员工姓名,部门名称 select e.*, d.dname from emp e,dept d where e.deptno=d.deptno --查询工资大于3000的员工姓名,工资和部门名称 select e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno and e.sal>=3000 |
1
2
3
4
|
--非等值查询 --查询公司工资等级 select * from emp e,salgrands where e.sal<=s.hisal and e.sal>s.losal select * from salagrade |
1
2
3
4
5
|
--外链接 --左外连接 select * from emp e,dept d where e.deptno=d.deptno(+) --右外连接 select * from emp e,dept d where e.deptno(+)=d.deptno |
1
2
3
4
5
6
|
--自连接 --查询员工姓名和经理姓名 select e1.ename ,e2.ename from emp e1,emp e2 where e1.mgr=e2.deptno --查询员工姓名、经理姓名和其他经理姓名 select e1.ename,e2.ename,e3.ename from emp e1,emp e2, emp e3 where e1.mgr=e2.empno and e2.empno=e3.empno |
1
|
**以上为92版仅在面试出现** |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
--SQL99 表连接 --交叉连接 cross join select * from emp e cross join dept d --自然连接 natural join select * from emp natural join dept --using select e.ename,deptno from emp e join dept d using(deptno) --on 自定义连接 select * from emp e join dept d on e.deptno=d.deptno select * from emp e1 join emp e2 on e1.mgr=e2.empno --查询员工的姓名、经理及其经理的名字 select e1.ename,e2.ename,e3.ename from emp e1 join emp e2 on e1.mgr=e2.empno join emp e3 on e2.mgr=e3.empno --SQL99 inner join 两边都合法的数据 select * from emp e inner join dept d on e.deptno=d.deptno --left join 以左表为主 select * from emp e left join dept d on e.deptno=d.deptno --right join 以右表为主 select * from emp e right join dept d on e.deptno=d.deptno --full join 全连 取两个表的所有数据 select * from emp e full join dept d on e.deptno=d.deptno ----查询员工的姓名、经理及其经理的名字和部门名字 select e1.ename, d.dname, e2.ename, d2.dname from emp e1 left join dept d on e1.deptno = d.deptno left join emp e2 on e2.mgr = e2.empno left join dept d2 on d2.deptno = d2.deptno |
1
|
**子查询** |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
--比CLARK工资高的人 select SAL from emp where ename= 'CLARK' select * from emp where sal>( select SAL from emp where ename= 'CLARK' ) --查询工资高于平均工资的雇员姓名和工资 select avg (sal) from emp select ename,sal from emp where sal>( select avg (sal) from emp ) --查询同SCOTT同部门的且工资比他低的员工的名字和工资 select deptno from emp where ename= 'SCOTT' select sal from emp where ename= 'SCOTT' select ename, sal from emp where deptno = ( select deptno from emp where ename = 'SCOTT' ) and sal < ( select sal from emp where ename = 'SCOTT' ) --查询和'SMITH','SCOTT','CLARK'同一个部门的员工姓名 select distinct deptno from emp where ename in ( 'SMITH' , 'SCOTT' , 'CLARK' ) select * from emp where deptno in ( select distinct deptno from emp where ename in ( 'SMITH' , 'SCOTT' , 'CLARK' )) --查询和'SMITH', 'SCOTT', 'CLARK'同一个部门并不包含他们三个的员工姓名 and ename not in ( 'SMITH' , 'SCOTT' , 'CLARK' ) --查询工资最高的员工名字和工资 select ename,sal from emp where sal=( select max (sal) from emp ) --查询职务和'SCOTT'相同但是比'SCOTT'雇佣时间早的雇员信息 select * from emp where job = ( select job from emp where ename = 'SCOTT' ) and hiredate < ( select hiredate from emp where ename = 'SCOTT' ) --查询工资比'SCOTT'高或者雇佣时间比'SCOTT'早的雇员编号和姓名 select empno,ename from emp where job = ( select job from emp where ename = 'SCOTT' ) and hiredate < ( select hiredate from emp where ename = 'SCOTT' ) |
1
|
**多行子查询** |
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
--查询工资低于任何一个“CLERK”的工资的雇员信息 select * from emp where sal all ( select sal from emp where job = 'SALESMAN' ) --查询部门20中职务同部门10的雇员一样的雇员信息。 select * from emp where job in ( select job from emp where deptno=10) and deptno=20 --查询在雇员中有哪些人是经理人 select * from emp where empno in ( select distinct mgr from emp where mgr is not null or mgr != '' ) --找出部门编号为20的所有员工中收入最高的职员 select ename from emp where sal = ( select max (sal) from emp where deptno = 20) and deptno = 20 </ any ( select > |