一、多表查询
1.显示部门号为10的部门名,员工名和工资
mysql> select ENAME,SAL,DNAME from EMP,DEPT where EMP.DEPTNO=DEPT.DEPTNO and DEPT.DEPTNO=10;
+--------+---------+------------+
| ENAME | SAL | DNAME |
+--------+---------+------------+
| CLARK | 2450.00 | ACCOUNTING |
| KING | 5000.00 | ACCOUNTING |
| MILLER | 1300.00 | ACCOUNTING |
+--------+---------+------------+
3 rows in set (0.00 sec)
2.显示各个员工的姓名,工资,及工资级别
mysql> select ENAME,SAL,GRADE from EMP,SALGRADE where SAL between LOSAL and HISAL;
+--------+---------+-------+
| ENAME | SAL | GRADE |
+--------+---------+-------+
| SMITH | 800.00 | 1 |
| ALLEN | 1600.00 | 3 |
| WARD | 1250.00 | 2 |
| JONES | 2975.00 | 4 |
| MARTIN | 1250.00 | 2 |
| BLAKE | 2850.00 | 4 |
| CLARK | 2450.00 | 4 |
| SCOTT | 3000.00 | 4 |
| KING | 5000.00 | 5 |
| TURNER | 1500.00 | 3 |
| ADAMS | 1100.00 | 1 |
| JAMES | 950.00 | 1 |
| FORD | 3000.00 | 4 |
| MILLER | 1300.00 | 2 |
+--------+---------+-------+
14 rows in set (0.00 sec)
二、自连接
在同一张表上面查询
1.显示员工FORD的上级领导的编号和姓名
(1)子查询:
mysql> select MGR from EMP where ENAME='FORD';
+------+
| MGR |
+------+
| 7566 |
+------+
1 row in set (0.00 sec)
mysql> select ENAME,EMPNO from EMP where empno=(select MGR from EMP where ENAME='FORD');
+-------+-------+
| ENAME | EMPNO |
+-------+-------+
| JONES | 7566 |
+-------+-------+
1 row in set (0.00 sec)
(2)多表查询(自查询):需要用到重名
mysql> select leader.empno,leader.ename from EMP leader, EMP worker where leader.empno = worker.mgr andd worker.ename='FORD';
+-------+-------+
| empno | ename |
+-------+-------+
| 7566 | JONES |
+-------+-------+
1 row in set (0.00 sec)
三、子查询
子查询可以出现的两个地方:
- where子句中,作为筛选条件使用
- from子句中,用来和特定的表做笛卡尔积
子查询是指嵌入在其他sql语句中的select语句,也叫嵌套查询
1.单行子查询:返回一行记录的子查询
(1)显示SMITH同一部门的员工
mysql> select * from EMP where deptno=(select deptno from EMP where ename='SMITH');
+-------+-------+---------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+---------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 |
+-------+-------+---------+------+------------+---------+------+--------+
5 rows in set (0.00 sec)
2.多行子查询
(1)in关键字
查询和10号部门的工作岗位相同的雇员的名字,岗位,工资,部门号,但是不包含10自己的
mysql> select ename,job,sal,deptno from EMP where job in (select distinct job from EMP where deptno=10)) and deptno<>10;
+-------+---------+---------+--------+
| ename | job | sal | deptno |
+-------+---------+---------+--------+
| SMITH | CLERK | 800.00 | 20 |
| JONES | MANAGER | 2975.00 | 20 |
| BLAKE | MANAGER | 2850.00 | 30 |
| ADAMS | CLERK | 1100.00 | 20 |
| JAMES | CLERK | 950.00 | 30 |
+-------+---------+---------+--------+
5 rows in set (0.00 sec)
(2)all关键字
显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号
mysql> select deptno,sal from EMP where deptno=30;
+--------+---------+
| deptno | sal |
+--------+---------+
| 30 | 1600.00 |
| 30 | 1250.00 |
| 30 | 1250.00 |
| 30 | 2850.00 |
| 30 | 1500.00 |
| 30 | 950.00 |
+--------+---------+
6 rows in set (0.00 sec)
mysql> select distinct sal from EMP where deptno=30;
+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+
5 rows in set (0.00 sec)
mysql> select ename,sal,deptno from EMP where sal > all(select distinct sal from EMP where deptno=30);
+-------+---------+--------+
| ename | sal | deptno |
+-------+---------+--------+
| JONES | 2975.00 | 20 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| FORD | 3000.00 | 20 |
+-------+---------+--------+
4 rows in set (0.00 sec)
(3)any关键字
显示工资比部门30的任意员工的工资高的员工的姓名、工资和部门号(包含自己部门的员工)
mysql> select distinct sal from EMP where deptno=30;
+---------+
| sal |
+---------+
| 1600.00 |
| 1250.00 |
| 2850.00 |
| 1500.00 |
| 950.00 |
+---------+
5 rows in set (0.00 sec)
mysql> select ename,sal,deptno from EMP where sal > any(select distinct sal from EMP where deptno=30);
+--------+---------+--------+
| ename | sal | deptno |
+--------+---------+--------+
| ALLEN | 1600.00 | 30 |
| WARD | 1250.00 | 30 |
| JONES | 2975.00 | 20 |
| MARTIN | 1250.00 | 30 |
| BLAKE | 2850.00 | 30 |
| CLARK | 2450.00 | 10 |
| SCOTT | 3000.00 | 20 |
| KING | 5000.00 | 10 |
| TURNER | 1500.00 | 30 |
| ADAMS | 1100.00 | 20 |
| FORD | 3000.00 | 20 |
| MILLER | 1300.00 | 10 |
+--------+---------+--------+
12 rows in set (0.00 sec)
all与any的区别:all是所有都比较,any是比较其中一个
3.多列子查询
单行子查询是指子查询只返回单列,单行数据;多行子查询是指返回单列多行数据,都是针对单列而言的,而多列子查询则是指查询返回多个列数据的子查询语句
查询和SMITH的部门和岗位完全相同的所有雇员,不含SMITH本人
mysql> select * from EMP where (deptno,job)=(select deptno,job from EMP where ename='SMITH');
+-------+-------+-------+------+------------+---------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-------+------+------------+---------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
+-------+-------+-------+------+------------+---------+------+--------+
2 rows in set (0.00 sec)
mysql> select * from EMP where (deptno,job)=(select deptno,job from EMP where ename='SMITH') and ename<=> 'SMITH';
+-------+-------+-------+------+------------+--------+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
+-------+-------+-------+------+------------+--------+------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
+-------+-------+-------+------+------------+--------+------+--------+
1 row in set (0.00 sec)
4.from中使用的子查询:把一个子查询当成一个临时表使用
显示每个高于自己部门平均工资的员工的姓名、部门、工资、平均工资
mysql> select ename,deptno,sal,avg(sal) from EMP,(select avg(sal) asal,deptno dep from EMP group by depptno) tmp where EMP.sal > tmp.asal and EMP.deptno=tmp.dep;
+-------+--------+---------+-------------+
| ename | deptno | sal | avg(sal) |
+-------+--------+---------+-------------+
| KING | 10 | 5000.00 | 3070.833333 |
+-------+--------+---------+-------------+
1 row in set (0.00 sec)
查找每个部门工资最高的人的姓名、工资、部门、最高工资
mysql> select ename,deptno,sal,_max from EMP,(select deptno dt,max(sal) _max from EMP group by dt) max__tb where EMP.deptno=max_tb.dt and EMP.sal=max_tb._max;
+-------+--------+---------+---------+
| ename | deptno | sal | _max |
+-------+--------+---------+---------+
| BLAKE | 30 | 2850.00 | 2850.00 |
| SCOTT | 20 | 3000.00 | 3000.00 |
| KING | 10 | 5000.00 | 5000.00 |
| FORD | 20 | 3000.00 | 3000.00 |
+-------+--------+---------+---------+
4 rows in set (0.00 sec)
显示每个部门的信息(部门名,编号,地址)和人员数量
mysql> select *from DEPT,(select deptno dt,count(*) 总数 from EMP group by dt) total where DEPT.deptno==total.dt;
+--------+------------+----------+------+--------+
| DEPTNO | DNAME | LOC | dt | 总数 |
+--------+------------+----------+------+--------+
| 10 | ACCOUNTING | NEW YORK | 10 | 3 |
| 20 | RESEARCH | DALLAS | 20 | 5 |
| 30 | SALES | CHICAGO | 30 | 6 |
+--------+------------+----------+------+--------+
3 rows in set (0.00 sec)
mysql> select dname,deptno,loc,总数 from DEPT,(select deptno dt,count(*) 总数 from EMP group by dt) tottal where DEPT.deptno=total.dt;
+------------+--------+----------+--------+
| dname | deptno | loc | 总数 |
+------------+--------+----------+--------+
| ACCOUNTING | 10 | NEW YORK | 3 |
| RESEARCH | 20 | DALLAS | 5 |
| SALES | 30 | CHICAGO | 6 |
+------------+--------+----------+--------+
3 rows in set (0.00 sec)
5.合并查询
(1)union:作用于取两个结果集的并集,且会自动去掉重复行(左侧查询的列与右侧查询的列数要一样)
将工资大于2500或职位是MANAGER的人找出来
mysql> select ename,sal,job from EMP where sal > 2500;
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
+-------+---------+-----------+
5 rows in set (0.00 sec)
mysql> select ename,sal,job from EMP where job='MANAGER';
+-------+---------+---------+
| ename | sal | job |
+-------+---------+---------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+---------+
3 rows in set (0.00 sec)
mysql> select ename,sal,job from EMP where sal > 2500
-> union
-> select ename,sal,job,from EMP where job='MANAGER';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from EMP where job='MANAGER'' at line 3
mysql> select ename,sal,job from EMP where sal > 2500 union select ename,sal,job from EMP where job='MAANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
6 rows in set (0.00 sec)
(2)union all:不做去重
mysql> select ename,sal,job from EMP where sal > 2500 union all select ename,sal,job from EMP where jobb='MANAGER';
+-------+---------+-----------+
| ename | sal | job |
+-------+---------+-----------+
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| SCOTT | 3000.00 | ANALYST |
| KING | 5000.00 | PRESIDENT |
| FORD | 3000.00 | ANALYST |
| JONES | 2975.00 | MANAGER |
| BLAKE | 2850.00 | MANAGER |
| CLARK | 2450.00 | MANAGER |
+-------+---------+-----------+
8 rows in set (0.00 sec)