9.数据库-查-多表操作
1.外键
实际开发中，健壮的数据库一定要有很好的参照完整性，为了保证数据的完整性，这里将两表之间数据建立关系，所以要在表中添加外键约束：
1.1什么是外键？
外键是指引用另一个表的一列或多列，被引用的列应该具有主键约束或唯一性约束。外键用于建立和加强两个表数据之间的连接。
引入外键后，外键列只能插入参照列存在的值，参照列被参照的值不能直接被删除，这就保证了数据的完整性。
1.2 为表创建外键
为表添加外键的格式如下：
alter table 表名 add constraint FK_ID foreign key(外键字段名) PEFERENCES 外表表名(主键字段名);
创建 班级 和 学生 表：
CREATE TABLE class(
cid int(4) NOT NULL PRIMARY KEY,
gname VARCHAR(10)
);
CREATE TABLE student(
sid int(4) NOT NULL PRIMARY KEY,
sname VARCHAR(10)
);
然后我们为他添加外键约束：
ALTER TABLE student ADD CONSTRAINT FK_ID FOREIGN KEY(sid) REFERENCES class(cid);
你可以用show create table 表名; 语句查看主从关系！
可以看出 sid是student表的外键，并且呢 sid外键依赖于class表中的cid主键，这样两个表就通过外键关联起来了。
需要注意的地方：
（1） 建立的外键表必须是InnoDB型，不能是临时表，因为在Mysql中 InnoDB的表才支持外键。
（2） 定义外键名时，不能加引号，如：
constraint ‘FK_ID’ 或 constraint “FK_ID” 都是错误的。
多学一招：添加外键约束的参数说明：
如果主表中的数据被删除或修改，从表中对应的数据该怎么办？
很明显也应该被删除~ 否则会导致很多垃圾数据。
MYSQL可以在建立外键的时候添加 ON DELETE 或 ON UPDATE
子句来告诉数据库，怎么避免产生垃圾数据。
语法格式如下：
Alter table 表名 add constraint FK_ID foreign key(外键字段名) REFERENCES 外表表名(主键字段名);
[ ON DELETE {CASCADE | SET NULL | NO ACTION | RESTRICT } ]
添加外键约束的参数说明
|
参数名称 |
功 能 描 述 |
|
CASCADE |
删除包括与已删除键值有参照关系的所有记录 |
|
SET NULL |
修改包含与已删除健值有参照关系的所有记录，使用NULL值替换(前提是不能用于 NOT NULL 的字段！) |
|
NO ACTION |
不进行任何工作 |
|
RESTRICT |
拒绝主表删除或修改外键关联列。 |
1.3删除外键约束
按照业务逻辑需求，需要解除两个表之间的关联关系时，就需要删除外键约束，删除外键约束的语法如下：
alter table 表名 drop foreign key 外键名;
例如：删除student表的外键：
ALTER TABLE student DROP FOREIGN KEY FK_ID;
2.关联关系
1.根据实体的内容设计数据库，实体间会有各种关联关系。所以根据实体设计的数据表之前也存在着各种关联关系，MySql中有三种：
（1） 多对一
多对一是数据表最常见的一种关系。比如员工和部门之间的关系，一个部门可以有多个员工，而一个员工不能属于多个部门，也就是说部门表中的 “一行” 在员工表可有许多匹配行，但员工表中的一行在部门表中只能有一个匹配项。
所以我们讲外键时，在多对一的表关系中，应该把外键建在多的一方，否则会照成数据冗余。
（2） 多对多
比如学生与课程之间的关系，一个学生可以选择多门课程，当然一门课程可以供多个学生选择，也就是说学生表的一行在课程表中能有许多匹配项，课程表表中的一行在学生表中也有许多匹配项。
（3） 一对一
一对一在生活中最常见，比如人和身份证之间就是一对一的关系，一个人对应一张身份证，一张身份证匹配一个人。
首先我们要弄懂主从关系，从表需要主表的存在才有意义，身份证需要有人的存在才有意义，因此人是主表，身份证是从表。
要在身份证建立外键。身份证的外键必须是非空唯一的，例如身份证号码是外键，那么就该如此，所以呢 一般都是用 从表的 主键座位外键进行约束。
在实际开发中 ， 一对一并不常见，因为用这种关系存的话 一般会放在一个表中，就不用约束了。
一对一常见于几个方面:
- 分割具有很多列的表
- 由于安全原因隔离表的一部分
- 保证临时的数据，且可以毫无费力的通过删除来删除这些数据。
2.添加数据
实际开发，最常见的就是多对一关系，我们上面有一个class 和
Student 表了 我们添加约束：
ALTER TABLE student add CONSTRAINT FK_ID FOREIGN KEY(sid) REFERENCES class(cid);
此时表student 和 class 之间是多对一的关系。外键列只能插入参照列存在的值，所以如果要吧数据添加到student【添加学生】的话，要先添加class【班级】的表，你可以试一下先添加学生，会报错的.
- 删除数据
删除数据你得先删除从表的数据 才能删除主表的数据，因为他们是关联的。例如：软件1班 不开了，你得先删除软件1班的学生，才能把软件1班删除。
实际开发中不需要这样干，你可以查询到一班的学生，使他们的班级为null，软件1班没参照了，自然可以把软件1班 删除。
3.连接查询
在实际开发中，一般都是把每个实体的所有信息存放在一个表中，当两个或多个表中存在相同意义的字段时，便可以通过这些字段对不同的表进行连接查询，连接查询包括交叉查询、内连接查询、外连接查询、下面针对连接查询讲解：
- 1. 交叉查询
交叉查询返回的结果是被连接的两个表的笛卡尔积，也就是表1 符合条件的行数 * 表2符合条件的行数。例如department 有4个部门，employee有4个员工，那么交叉连接的结果就有 4*4=16条数据。
交叉连接的语法格式如下：
SELECT * FROM 表1 CROSS JOIN 表2;
其中： CROSS JOIN 用于连接两个要查询的表，通过该语句可以查询量表中全部数据组合。
先创建department【部门】表 和 employee【雇员】表：
CREATE TABLE department(
did int(4) NOT NULL PRIMARY KEY,
dname varchar(36)
);
CREATE TABLE employee(
id int(4) NOT NULL PRIMARY KEY,
name VARCHAR(36),
age int(2),
did int(4) NOT null
);
INSERT INTO department(did,dname)VALUES(1,'网络部');
INSERT INTO department(did,dname)VALUES(2,'媒体部');
INSERT INTO department(did,dname)VALUES(3,'研发部');
INSERT INTO department(did,dname)VALUES(5,'人事部');
INSERT INTO employee (id,name,age,did)VALUES(1,'王强',20,1);
INSERT INTO employee (id,name,age,did)VALUES(2,'李红',22,1);
INSERT INTO employee (id,name,age,did)VALUES(3,'赵四',20,2);
INSERT INTO employee (id,name,age,did)VALUES(4,'恒娟',20,4);
创建了两个 和 插入了数据↑
现在我们用交叉连接试一下：
SELECT * FROM department CROSS JOIN employee;
我们可以看到，结果是部门 和 雇员表的结合，实际开发不会这样，但是会结合数据条件进行目的信息查询。
- 2. 内连接
内连接（Inner Join）又叫简单连接或自然连接，是一种常见的连接查询，内连接用运算符对两个表的数据进行比较，并列出与连接条件匹配的数据行，组成新的记录，换句话说：
-内连接只有满足条件的记录才能出现查询结果中，语法格式如下：
SELECT 查询字段 FROM 表1[INNER] JOIN 表2 ON 表1.关系字段=表2关系字段;
其中：
INNER JOIN 用于连接两个表 ON来指定连接条件，其中 INNER 可以省略：
例1 在department表 和 employee表 之间用内连接查询：
SELECT employee.name,department.dname FROM department JOIN employee
ON department.did = employee.did;
从查询结果可以看出，只有 department.did 和 department.did 一样的时候，才会被显示。
例2： 还可以用WHERE条件语句来写：【其实就是 ON 换成 WHERE】
SELECT employee.name,department.dname FROM department,employee WHERE department.did = employee.did;
虽然用 ON或 用WHERE 查询出来的结果是一样的，但是用WHERE 可以 不用加 JOIN 直接在FROM 后 用逗号分隔表，然后直接WHERE 添加条件即可。
如果在一个连接查询中，涉及的两个表是同一个表，这种查询称为自连接查询，自连接查询是一个特殊的内查询。
例如：查询李红所在的部门中还有其他什么人：【别名 方法】
SELECT p1.* FROM employee p1 JOIN employee p2 ON p1.did = p2.did WHERE p2.name='李红';
如果你看不懂 ，那么尝试把 p1 去掉：
SELECT * FROM employee p1 JOIN employee p2 ON p1.did = p2.did WHERE p2.name='李红';
这题一定要懂啊！ 主要还是 他自连接会有两个表. 【自连接一定要别名来查询】
- 3. 外连接
内连接只会查询包含符合查询条件 和 连接条件的数据，但是很多时候都包含很多没必要的数据，所以： 外连接查询、分为 左连接查询 和 右连接查询：
外连接查询格式语法如下：
SELECT 所查字段 FROM 表1 LEFT | RIGHT [OUTER] JOIN 表2
ON 表1.关系字段=表2.关系字段 WHERE 条件;
语法和内连接相似，只不过使用的是 LEFT JOIN 、 RIGHT JOIN 关键字，其中关键字左边的表被称为左表，关键字右边的表被称为右表。
左、右 查询的结果是不一致的：
（1）LEFT JOIN （左连接） 返回包括左表中所有的记录和右表中符合条件的记录。
（2）RIGHT JOIN （右连接） 返回包括右表中所有的记录和左表中符合条件的记录。
- LEFT JOIN 左连接
左连接如果左表某条记录不存在，那么在右表中显示为空(NULL)
例： 在department表 和 employee表 之间使用左连接查询：
SELECT department.did,department.dname,employee.name FROM department
LEFT JOIN employee ON department.did = employee.did;
结果：
did dname name
1 网络部 王强
1 网络部 李红
2 媒体部 赵四
3 研发部 NULL
5 人事部 NULL
在结果可以看出，研发部 和 人事部 在 employee【雇员】表中 根本没人在其中。
- RIGHT JOIN 右连接
和左连接正好相反：右连接如果右表某条记录不存在，那么在右表中显示为空(NULL)
例： 在department表 和 employee表 之间使用右连接查询：
SELECT department.did,department.dname,employee.name FROM department
RIGHT JOIN employee ON department.did=employee.did;
结果：
did dname name
1 网络部 王强
1 网络部 李红
2 媒体部 赵四
NULL NULL 恒娟
可以看出 是和左连接相反的 ，但是查询的数据是：
employee【雇员】表中 只有恒娟是没在 department【部门】表中的。
4.复合条件连接查询
复合条件连接查询是在连接查询的过程中，通过添加过滤条件来限制查询结果，使得查询更加精确。
例：
在 部门表 和 雇员表中用内连接查询，并且查询结果按照年龄从小排到大：
SELECT employee.name,employee.age,department.dname FROM department JOIN employee
ON employee.did=department.did ORDER BY age ;
直接在最后加上 所以他就会从按照年龄小排到大了。
4.子查询
1.带IN关键字的子查询
带IN关键字进行子查询时，内层查询语句仅返回一个数据列，这个数据列中的值将供外层查询语句进行比较操作。
例： 查询存在年龄20岁的员工的部门 【即 20岁员工 所在的 部门】：
SELECT * FROM department WHERE did IN(SELECT did FROM employee WHERE age=20);
你可以分开看 先看括号的子查询 返回括号返回值出来 在配合外层查询。
还可以用 NOT IN 呢 ， NOT IN 这里就不多说了。
2.带EXISTS 关键字的子查询
EXISTS 后面可以是任意一个子查询，这个子查询作用相当于测试，他不产生任何数据，他只返回 TURE / FALSE ，当返回 TRUE时，外层的查询才会执行。
例： 如果 雇员表中存在年龄大于21 的员工，若存在，则打印整个表，否则不打印：
SELECT * FROM employee WHERE EXISTS(SELECT did FROM employee WHERE age > 21);
其中： 因为子查询有数据返回 所以会返回 TRUE 然后 整个表达式为真 所以打印了employee 的表。
- 3. 带ANY关键字的子查询
ANY关键字表示满足任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询的任意一个比较条件，就返回一个结果作为外层条件。
例：使用带ANY关键字的子查询 查询满足条件的部门：
SELECT * FROM department WHERE did > ANY (SELECT did FROM employee);
其实就是说 department 的 did 要是 大于 employee中的did【其中一个】，就显示。
- 4. 带ALL关键字的子查询
All关键字和ANY关键字有点相似，只不过带ALL关键字的子查询返回的结果需同时满足所有内层查询条件、
例：使用带ALL关键字的子查询，查询满足条件的部门：
SELECT * FROM department WHERE did > ALL(SELECT did FROM employee);
它的意思就是说department表中的 did ，如果大于 employee中的did【全部】就显示。
- 5. 带比较运算符的子查询
前面讲的 ANY 和 ALL 都用了 > 比较运算符，子查询还可以用其他的比较运算符，例如： < 、 >= 、 = 、 != 等…
例：用带比较运算符的子查询，查询赵四是哪个部门的员工，SQL语句如下：
SELECT * FROM department WHERE did = (SELECT did FROM employee WHERE employee.name = '赵四');
所以 自己去多尝试即可：
完。