为什么要进行拆表
比如说一张学生表，里面有一个字段为分数：
image.png
我们知道，一个学生可以考多门课程，有多门课程的分数，那么这时候就需要添加多个重复的数据了；而且这里并不能区分是什么课程。那么这时候就需要进行表拆分了：
image.png
进行表拆分的优点是：
- 实现项目的具体需求
- 避免大量冗余数据，提高数据的查询效率。
注意：表并不是拆分得越仔细越好，否则工作量会增加
多表查询
既然有了表拆分，那么就会有多表查询。多表查询有如下几种：
- 合并结果集；UNION 、 UNION ALL
- 连接查询
内连接 [INNER] JOIN ON
外连接 OUTER JOIN ON
左外连接 LEFT [OUTER] JOIN
右外连接 RIGHT [OUTER] JOIN
全外连接（MySQL不支持）FULL JOIN
自然连接 NATURAL JOIN - 子查询
- 自连接
1 合并结果集
- 作用：合并结果集就是把两个select语句的查询结果合并到一起！
- 合并结果集有两种方式：
UNION：去除重复记录，例如：SELECT * FROM t1 UNION SELECT * FROM t2；
UNION ALL：不去除重复记录，例如：SELECT * FROM t1 UNION ALL SELECT * FROM t2。 - 要求：被合并的两个结果：列数、列类型必须相同（不相同的时候，通过SELECT关键字去筛选需要的列）。
相关的图例如下：
image.png
例子：
create table a(
id int,
name varchar(10),
score int
);
create table b(
name varchar(10),
score int
);
insert into a values(1,'a',10),(2,'b',20),(3,'c',30);
insert into b values('a',10),('b',20),('d',40);
去除重复记录
select name,score from a union select * from b;
不去除重复记录
select name,score from a union all select * from b;
2 连接查询（重点）
连接不限于两张表，连接查询也可以是三张、四张，甚至N张表的连接查询。直接查询多张表会产生笛卡尔积，通常连接查询不可能需要整个笛卡尔积，而只是需要其中一部分，那么这时就需要使用条件来去除不需要的记录。这个条件大多数情况下都是使用主外键关系去除，这时候就需要N-1个查询条件。
两张表的连接查询一定有一个主外键关系，三张表的连接查询就一定有两个主外键关系，所以在大家不是很熟悉连接查询时，首先要学会去除无用笛卡尔积，那么就是用主外键关系作为条件来处理。如果两张表的查询，那么至少有一个主外键条件，三张表连接至少有两个主外键条件。
下面将以两个表为例进行讲解：
CREATE TABLE student(
sid INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
sex VARCHAR(10) DEFAULT '男'
);
CREATE TABLE score(
id INT,
score INT,
sid INT , -- 外键列的数据类型一定要与主键的类型一致
CONSTRAINT fk_score_sid FOREIGN KEY (sid) REFERENCES student(id)
);
2.1 内连接查询
-- 我们通常写的连接查询又叫做99查询法，核心思想就是主外键的连接：
select s.sid,s.name,c.score from student s , score c where s.sid=c.sid;
-- 也可以写成标准的内连接查询（核心就是inner join on关键字）
select s.sid,s.name,c.score from student s inner join score c on s.sid=c.sid;
注意：内连接查询中，inner可以省略
2.2 外连接查询
外连接查询分为左、右外连接查询：
-- 左外连接查询（参照student表，student有的，即使score中对应数据为NULL，也会查询得到）
select s.sid,s.name,c.score from student s left outter join score c on s.sid=c.sid;
-- 右外连接查询（参照score表）
select s.sid,s.name,c.score from student s right outter join score c on s.sid=c.sid;
注意：左右外连接查询中，outter可以省略，其中MySQL不支持全外连接查询。
2.3 自然连接查询
大家也都知道，连接查询会产生无用笛卡尔积，我们通常使用主外键关系等式来去除它。而自然连接无需你去给出主外键等式，它会自动找到这一等式，其实就是相当于内连接查询。例如：
select * from student natural join score;
3 子查询
子查询就是嵌套查询，即SELECT中包含SELECT，如果一条语句中存在两个，或两个以上SELECT，那么就是子查询语句了。
子查询出现的位置：
where后，作为条为被查询的一条件的一部分；
from后，作表；
当子查询出现在where后作为条件时，还可以使用如下关键字（用得比较少）：
any
all
子查询结果集的形式：
单行单列（用于条件）
单行多列（用于条件）
多行单列（用于条件）
多行多列（用于表）
例子，我们还是以这个数据作为例子：
员工表emp：
create table emp(
empno int,
ename varchar(50),
job varchar(50),
mgr int,
hiredate date,
sal decimal(7,2),
comm decimal(7,2),
deptno int
);
insert into emp values(7369,'smith','clerk',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'allen','salesman',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'ward','salesman',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'jones','manager',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'martin','salesman',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'blake','manager',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'clark','manager',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'scott','analyst',7566,'1987-04-19',3000,null,20);
insert into emp values(7839,'king','president',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'turner','salesman',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'adams','clerk',7788,'1987-05-23',1100,null,20);
insert into emp values(7900,'james','clerk',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'ford','analyst',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'miller','clerk',7782,'1982-01-23',1300,null,10);
部门表dept：
create table dept(
deptno int,
dname varchar(14),
loc varchar(13)
);
insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');
-- 查询工资高于scott的员工信息
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='scott');
-- 工资高于30号部门所有人的员工信息
SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno=30);
-- 查询工作和工资与scott完全相同的员工信息
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename='scott')
-- 有2个以上直接下属的员工信息
SELECT * FROM emp WHERE empno IN(SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2);
4 自连接查询
自连接查询的核心是把自己看成两张表。
-- 求7369员工编号、姓名、经理编号和经理姓名
SELECT e1.empno , e1.ename,e2.mgr,e2.ename
FROM emp e1, emp e2
WHERE e1.mgr = e2.empno AND e1.empno = 7369;
网友评论