一、多表查询
1、笛卡尔积
A表中每一条数据都和B表中所有数据组成新的数据
语法:
select * from t_class,t_stu;
select * from t_class cross join t_stu;
2、等值连接(内连接)——外间关联获取数据
语法:
select * from t_class c,t_stu s where s.c_id=c.c_id;
select * from t_class c inner join t_stu s on s.c_id=c.c_id;
注:如果某个列c_id在两个表中都有,使用时必须明确写出是哪个表的列c_id, 没有歧义的列不需要加表名
练习:(1)获取2班所有学生信息
(2)显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金
(3)显示所有姓名中含有A字符的员工姓名,工作地点
select * from t_class INNER JOIN t_stu where t_class.c_id = t_stu.c_id;
+------+--------+------+--------+-------+-------+---------+------+
| c_id | c_name | s_id | s_name | s_sex | s_age | s_money | c_id |
+------+--------+------+--------+-------+-------+---------+------+
| 20 | 一班 | 1 | 张三 | 男 | 20 | 4000 | 20 |
| 21 | 二班 | 3 | 王五 | 女 | 22 | 6000 | 21 |
| 21 | 二班 | 4 | 小六 | 女 | 22 | 7000 | 21 |
| 21 | 二班 | 5 | 小明 | 女 | 24 | 8000 | 21 |
+------+--------+------+--------+-------+-------+---------+------+
select ename,loc,comm from emp,dept where emp.deptno = dept.deptno and loc ='CHICAGO'and comm is not null;
+--------+---------+---------+
| ename | loc | comm |
+--------+---------+---------+
| aLLEN | CHICAGO | 300.00 |
| WARD | CHICAGO | 500.00 |
| MARTIN | CHICAGO | 1400.00 |
| TURNER | CHICAGO | 0.00 |
+--------+---------+---------+
select ename,loc from emp,dept where emp.deptno = dept.deptno and ename like '%A%';
3、非等值连接
注:先根据题意确定字段在哪个表中,将多表进行笛卡尔积变成一张大表,在大表上分析数据写连接条件,再根据题意写筛选条件
(创建工资级别表salgrade,包含字段工资级别grade主键,最低工资losal,最高工资hisal)
select * from emp,salgrade where sal between losal and hisal;
select * from emp,salgrade where sal>=losal and sal<=hisal;
select * from emp inner join salgrade on sal between losal and hisal;
select * from emp inner join salgrade on sal>=losal and sal<=hisal;
+-------+------------+-----------+------+------------+---------+---------+--------+-------+-------+-------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno | GRADE | LOSAL | HISAL |
+-------+------------+-----------+------+------------+---------+---------+--------+-------+-------+-------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | 1 | 700 | 1200 |
| 7499 | aLLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | 3 | 1401 | 2000 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | 2 | 1201 | 1400 |
| 7566 | J_ONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | 2 | 1201 | 1400 |
| 7691 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | 4 | 2001 | 3000 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 20 | 5 | 3001 | 9999 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | 3 | 1401 | 2000 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | 1 | 700 | 1200 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | 1 | 700 | 1200 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | 4 | 2001 | 3000 |
| 7934 | MILLER_1_2 | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | 2 | 1201 | 1400 |
+-------+------------+-----------+------+------------+---------+---------+--------+-------+-------+-------+
4、左连接
语法:
select * from a left join b on a.fid = b.id
a表中所有数据都保留,如果b表中有数据则组成新的记录,如果b表中没有数据与之对应,则b表中字段部分设置为null;
练习:(1)查看每个班级的学员信息,要求显示所有班级信息
(2)查询学员和班级信息,要求显示所有学生信息
(3)统计每个班级人数
select * from t_class left join t_stu on t_class.c_id = t_class.c_id;
select * from t_stu left join t_class on t_stu.c_id = t_class.c_id;
select c_name,count(s_id) from t_class left join t_stu on t_class.c_id = t_stu.c_id group by c_name;
5、右连接
语法:
select * from a right join b on a.fid = b.id
b表中所有数据都保留,如果a表中有数据则组成新的记录,如果a表中没有数据与之对应,则a表中字段部分设置为null;
练习:(1)查找班级人数为0的班级
(2)获取没有员工的部门
select c_name,count(s_id)
-> from t_stu s right join t_class c on c.c_id=s.c_id
-> group by c_name having count(s_id)=0;
+--------+-------------+
| c_name | count(s_id) |
+--------+-------------+
| 三班 | 0 |
+--------+-------------+
select d.* from emp right join dept d on d.deptno = emp.deptno where empno is null;
--子查询
select * from dept where deptno not in(select DISTINCT e.deptno from emp e ,dept d where e.deptno=d.deptno );
+--------+------------+--------+
| deptno | dname | loc |
+--------+------------+--------+
| 3 | 人力资源部 | C区 |
| 4 | 办公部 | D区 |
| 5 | 市场 | NULL |
| 6 | 销售 | |
| 40 | OPERATIONS | BOSTON |
+--------+------------+--------+
6、自身连接
查找的信息都在同一张表中,同一表中的数据自己和自己关联
例子:查找员工姓名以及员工直接领导姓名
select e.ename,l.ename from emp e,emp l where e.mgr = l.empno;
select e.ename,l.ename from emp e inner join emp l on e.mgr = l.empno;
+------------+--------+
| ename | ename |
+------------+--------+
| SMITH | FORD |
| J_ONES | KING |
| BLAKE | KING |
| CLARK | KING |
| SCOTT | J_ONES |
| ADAMS | SCOTT |
| FORD | J_ONES |
| MILLER_1_2 | CLARK |
+------------+--------+
7、完全连接:两个表中的所有数据都保留
查询班级和学生信息,要求显示所有班级和所有学生信息
SQL99写法(MySQL不支持,oracle支持)
select * from emp e full outer join dept d on e.deptno=d.deptno;
select * from student,class where student.id = class.id;
+----+-------+------+-----+-------+------+
| id | sname | sex | cid | cname | id |
+----+-------+------+-----+-------+------+
| 1 | 小明 | 男 | 1 | 一班 | 1 |
| 2 | 小红 | 女 | 2 | 二班 | 2 |
| 3 | 小蓝 | | 3 | | 3 |
+----+-------+------+-----+-------+------+
8、多于两个表的连接
1.分析要查询的列都来自于哪些表,构成FROM子句;
2.分析这些表之间的关联关系,如果表之间没有直接关联关系,而是通过另一个中间表关联,则也要在FROM子句中补充中间关联表;
3.接下来在WHERE子句中补充表之间的关联关系,通常N个表,至少要有N-1个关联关系;
4.分析是否还有其它限制条件,补充到WHERE子句的表关联关系之后,作为限制条件;
5.根据用户想要显示的信息,补充SELECT子句。
6.分析是否有分组要求
7.分析是否有排序要求,如果排序要求中还涉及到其它表,则也要进行第2步补充排序字段所在的表,并且添加表之间的关联关系;
8.看是否需要子查询
9.如果查询字段过于长,可以起别名
练习:(1)查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
(2)成绩大等于80分的所有学生姓名,课程名称和任课老师的名字
--工作,员工姓名,员工编号,经理姓名,经理编号字段来自表dept(loc),员工表,员工表自连接为经理表
select e.ename,e.empno,l.ename,l.empno from emp e,emp l,dept d where l.deptno = e.deptno and e.mgr = l.empno and loc in('NEW YORK','CHICAGO');
+--------+-------+--------+-------+
| ename | empno | ename | empno |
+--------+-------+--------+-------+
| SMITH | 7369 | FORD | 7902 |
| SMITH | 7369 | FORD | 7902 |
| J_ONES | 7566 | KING | 7839 |
| J_ONES | 7566 | KING | 7839 |
| CLARK | 7782 | KING | 7839 |
| CLARK | 7782 | KING | 7839 |
| SCOTT | 7788 | J_ONES | 7566 |
| SCOTT | 7788 | J_ONES | 7566 |
| ADAMS | 7876 | SCOTT | 7788 |
| ADAMS | 7876 | SCOTT | 7788 |
| FORD | 7902 | J_ONES | 7566 |
| FORD | 7902 | J_ONES | 7566 |
+--------+-------+--------+-------+
select sname,cname,tname from score,student,teacher,course where course.tno = teacher.tno and student.sno = score.sno and score.cno = course.cno and score.degree > 80;
select sname,cname,tname
-> from student stu inner join score sc on stu.sno=sc.sno
-> inner join course c on sc.cno=c.cno
-> inner join teacher t on c.tno=t.tno
-> where degree>80;
二、子查询(重点)
(1)语法:
SELECT select_list
FROM table
WHERE expr operator (SELECT select_list FROM table);
(括号内的查询叫做子查询,也叫内部查询,先于主查询执行,子查询的结果被主查询(外部查询)使用)
(2)expr operator包括比较运算符
单行运算符:>、=、>=、<、<>、<=
多行运算符: IN、ANY、ALL
(3)子查询可以嵌套where、from、having子句中
(4)子查询要用括号括起来,将子查询放在比较运算符的右边,对于单行子查询要使用单行运算符,对于多行子查询要使用多行运算符
练习:(1)查询工资比Jones工资高的员工信息
(2)查询工资最低的员工姓名
(3)获取所有员工姓名和所有员工最大工资
(4)查找ACCOUNTING部门的所有员工(除了子查询可以使用等值连接)
(5)查找部门平均工资大于部门10的平均工资
--1
select * from emp where sal > (select sal from emp where ename= 'J_ONES');
--2
select ename from emp where sal = (select min(sal) from emp);
--3
select (select max(sal) from emp) maxsal,ename from emp;
select ename,t.maxsal from emp ,(select max(sal) maxsal from emp) t;
--4
select * from emp where deptno = (select deptno from dept where dname ='ACCOUNTING' );
+-------+------------+-------+------+------------+---------+------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+------------+-------+------+------------+---------+------+--------+
| 7934 | MILLER_1_2 | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
+-------+------------+-------+------+------------+---------+------+--------+
--5
select deptno,avg(sal) from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno = 10);
+--------+-------------+
| deptno | avg(sal) |
+--------+-------------+
| 1 | 3000.000000 |
| 2 | 3000.000000 |
| 20 | 2617.857143 |
| 30 | 1566.666667 |
+--------+-------------+
1、单行子查询
子查询只返回一行,使用单行运算符(= > < >= <= <>(不等于))
练习:(1)显示和雇员7369从事相同工作并且工资大于雇员7876的雇员的姓名和工作
(2)查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称
(3)查询部门人数大于所有部门平均人数的的部门编号,部门名称,部门人数
所有部门平均人数:总人数/部门数=(select count(*) from emp)/(select count(*) from dept)
--1
select ename,job from emp where job = (select job from emp where empno = 7369 ) and sal > (select sal from emp where empno = 7876 );
+------------+-------+
| ename | job |
+------------+-------+
| MILLER_1_2 | CLERK |
+------------+-------+
--2
select ename,sal,dname from emp,dept where emp.deptno = dept.deptno and sal > (select sal from emp where ename = 'smith') and loc = 'chicago';
+--------+---------+-------+
| ename | sal | dname |
+--------+---------+-------+
| aLLEN | 1600.00 | SALES |
| WARD | 1250.00 | SALES |
| MARTIN | 1250.00 | SALES |
| BLAKE | 2850.00 | SALES |
| TURNER | 1500.00 | SALES |
| JAMES | 950.00 | SALES |
+--------+---------+-------+
--3
select e.deptno,dname,count(*) from emp e,dept d where e.deptno = d.deptno group by e.deptno having count(*)>(select count(*) from emp)/(select count(*) from dept);
+--------+----------+----------+
| deptno | dname | count(*) |
+--------+----------+----------+
| 20 | RESEARCH | 7 |
| 30 | SALES | 7 |
+--------+----------+----------+
2、多行子查询
子查询返回记录的条数可以是一条或多条,和多行子查询进行比较时,需要使用多行操作符,多行操作符包括:IN ANY ALL
(1)IN操作符:判断是否与子查询的任意一个返回值相同(作用相当于or)
练习:(1)查找和SMITH、WARD相同职位的人
(2)查询是领导的员工姓名,工资
--错误示例,查询结果超过一行
select * from emp where job = (select job from emp where ename in('smith','ward'));
ERROR 1242 (21000): Subquery returns more than 1 row
--正确演示
mysql> select * from emp where job in(select job from emp where ename in('smith','ward'));
+-------+------------+----------+------+------------+---------+---------+--------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
+-------+------------+----------+------+------------+---------+---------+--------+
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 |
| 7499 | aLLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 |
| 7934 | MILLER_1_2 | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 |
| 7956 | ABCD | CLERK | 7788 | 1979-02-23 | 6000.00 | 500.00 | 30 |
+-------+------------+----------+------+------------+---------+---------+--------+
(2)ANY操作符:表示和子查询的任意一行结果进行比较,有一个满足条件即可。
<ANY:表示小于子查询结果集中的任意一个,即小于最大值就可以
> ANY:表示大于子查询结果集中的任意一个,即大于最小值就可以
= ANY:表示等于子查询结果中的任意一个,即等于谁都可以,相当于IN
练习:(1)查询是领导的员工姓名,工资
(2)查询部门编号不为10,且工资比10部门任意一名员工工资高的员工编号,姓名,职位,工资
--1
select empno,ename,sal
-> from emp where empno =any(select distinct mgr from emp);
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7566 | J_ONES | 2975.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7839 | KING | 5000.00 |
| 7902 | FORD | 3000.00 |
+-------+--------+---------+
--2
select empno,ename,job,sal
from emp
where deptno!=10 and sal >any(select sal from emp where deptno=10);
select empno,ename,job,sal
from emp
where deptno!=10 and sal > (select min(sal) from emp where deptno=10);
(3)All操作符:表示和子查询的所有行结果进行比较,每一行必须都满足条件
< ALL:表示小于子查询结果集中的所有行,即小于最小值。
> ALL:表示大于子查询结果集中的所有行,即大于最大值。
= ALL :表示等于子查询结果集中的所有行,即等于所有值,通常无意义
练习:(1)查询部门编号不为20,且工资比20部门所有员工工资高的员工编号,姓名,职位,工资
(2)查询部门编号不为10,且工资比10部门所有员工工资低的员工编号,姓名,职位,工资
--1
select empno,ename,job,sal from emp where deptno != 20 and sal > all(select sal from emp where deptno = 20);
+-------+-------+-------+---------+
| empno | ename | job | sal |
+-------+-------+-------+---------+
| 7956 | ABCD | CLERK | 6000.00 |
+-------+-------+-------+---------+
select empno, ename, job, sal
-> from emp
-> where deptno != 20
-> and sal > (select max(sal) from emp where deptno = 20);
select empno,ename,job,sal from emp where deptno !=10 and sal < (select min(sal) from emp where deptno =10);
+-------+--------+----------+---------+
| empno | ename | job | sal |
+-------+--------+----------+---------+
| 7369 | SMITH | CLERK | 800.00 |
| 7521 | WARD | SALESMAN | 1250.00 |
| 7654 | MARTIN | SALESMAN | 1250.00 |
| 7876 | ADAMS | CLERK | 1100.00 |
| 7900 | JAMES | CLERK | 950.00 |
+-------+--------+----------+---------+
5 rows in set (0.00 sec)
mysql> select empno,ename,job,sal
-> from emp
-> where deptno!=10 and sal<all(select sal from emp where deptno=10);
+-------+--------+----------+---------+
| empno | ename | job | sal |
+-------+--------+----------+---------+
| 7369 | SMITH | CLERK | 800.00 |
| 7521 | WARD | SALESMAN | 1250.00 |
| 7654 | MARTIN | SALESMAN | 1250.00 |
| 7876 | ADAMS | CLERK | 1100.00 |
| 7900 | JAMES | CLERK | 950.00 |
+-------+--------+----------+---------+
3、子查询中的空值
not in 的集合中不能有null
子查询的结果中有一条空值,这条空值导致主查询没有记录返回。这是因为所有的条件和空值比较结果都是空值。因此无论什么时候只要空值有可能成为子查询结果集合中的一部分,就不能使用NOT IN 运算符
例子:查询不是领导的员工姓名
select ename from emp where empno not in(select distinct mgr from emp where mgr is not null);
总结:
null值不能是not in()后面中的值
null值不能参与算术运算+、-、*、/、%