MySQL(五） 多表查询

5 多表操作

　　5.1. 外键

　　引用另一个表中的一列或者多列，被引用的列应该具有主键约束或者非空约束

　　搭建环境，在数据库test中创建主表class：

CREATE TABLE class(
    cid INT(4) NOT NULL PRIMARY KEY,
    cname VARCHAR(10)
);

　　5.1.1 添加外键约束

　　(1) 在创建表的同时为表添加外键（在从表中添加SQL语句）

　　格式：CONSTRAINT 自定义外键名 FOREIGN KEY (从表引用字段名)

　　　　    REFERENCES 主表名 (主表被引用字段名)

CREATE TABLE student(
    sid INT(4),
    sname VARCHAR(10),
    cid INT(4),
    CONSTRAINT FK_ID FOREIGN KEY (cid) REFERENCES class (cid)
);

　　

　　(2) 为已存在的表添加外键

　　搭建环境，在数据库test下创建数据表pupil（先删除数据表student）

CREATE TABLE pupil(
    sid INT(4),
    sname VARCHAR(10),
    cid INT(4)
);

　　格式：ALTER TABLE 从表名 ADD CONSTRAINT 自定义外键名

　　　　    FOREIGN KEY (从表引用字段名)

　　　　    REFERENCES 主表名 (主表被引用字段名)

ALTER TABLE pupil ADD CONSTRAINT FK_ID
FOREIGN KEY (cid)
REFERENCES class (cid);

　　

　　5.1.2 删除外键约束

　　格式：ALTER TABLE 从表名

　　　　    DROP FOREIGN KEY 外键名

ALTER TABLE pupil
DROP FOREIGN KEY FK_ID;      

　　5.2. 操作关联表

　　5.2.1 关联关系

　　一对多（班级与学生）：将外键建在多的一方。

　　多对多（课程与学生）：定义一张中间表（连接表），该表会存在两个外键，分别参照课程表和学生表

　　一对一（人与身份证）：从主从关系入手，从表需要主表的存在才有意义

　　5.2.2 添加数据

　　给从表添加数据时，它引用的字段必须为主表中被引用字段的值

　　5.2.3 删除数据

　　在具有关联关系的表中删除数据时，一定要先删除从表中的数据，然后再删除主表中的数据，否则会报错

　　5.3. 连接查询

　　下面的两张表通过相同意义的字段模拟外键约束，并没有真正的外键约束，但其特点和外键是一样的，可以通过这些字段对不同的表进行连接查询

　　搭建查询环境，在数据库test下创建数据表department和employee

CREATE TABLE department(
    did INT(4) NOT NULL PRIMARY KEY,
    dname VARCHAR(20)
);

CREATE TABLE employee(
    id INT(4) NOT NULL PRIMARY KEY,
    name VARCHAR(10),
    age INT(2),
    did INT(4) NOT NULL
);

　　在两个表中插入相关数据

INSERT INTO department 
VALUES (1,'network'),(2,'media'),(3,'development'),(5,'personnel');

　　

INSERT INTO employee
VALUES (1,'Zhou',20,1),(2,'Will',22,1),(3,'Marry',20,2),(4,'Jin',20,4);

　　

　　5.3.1 交叉连接（笛卡尔积）

　　格式：SELECT 字段名 FROM 主表CROSS JOIN 从表;

SELECT * FROM department CROSS JOIN employee;

　　相当于：

SELECT * FROM department,employee;

　　

　　5.3.2 内连接（自然连接）

　　格式：SELECT 字段名 FROM 主表

　　　　    [INNER] JOIN 从表

　　　　    ON 主表.关系字段=从表.关系字段

SELECT department.dname,employee.name 
FROM department INNER JOIN employee
ON department.did=employee.did;

　　相当于：

SELECT department.dname,employee.name 
FROM department,employee
WHERE department.did=employee.did;

　　

　　5.3.3 外连接

　　(1) 左连接：以左表为准，去右表找数据，找不到，用null补齐

　　格式：SELECT 字段名 FROM 左表

　　　　    LEFT JOIN 右表

　　　　    ON 左表.关系字段=右表.关系字段

SELECT department.dname,employee.name 
FROM department LEFT JOIN employee
ON department.did=employee.did;

　　

　　(2) 右连接：以右表为准，去左表找数据，找不到，用null补齐

　　格式：SELECT 字段名 FROM 左表

　　　　    RIGHT JOIN 右表

　　　　    ON 左表.关系字段=右表.关系字段

SELECT department.dname,employee.name 
FROM department RIGHT JOIN employee
ON department.did=employee.did;

　　

　　注意：在左连接中互换左右表的位置，同样可以得到右连接的效果

　　(3) 全连接(FULL JOIN)：MySQL不支持，可以通过左连接UNION右连接得到

　　5.3.4 子查询

　　(1) 带IN关键字的子查询

　　使用IN关键字进行子查询时，内层查询语句仅返回一个数据列，这个数据列中的值将供外层查询语句进行比较操作

　　例：查询存在20岁的员工的部门

SELECT dname FROM department
WHERE did IN
(SELECT did FROM employee WHERE age=20);

　　

　　(2) 带EXISTS关键字的子查询

　　EXISTS关键字后面的参数可以是任意一个子查询，这个子查询的作用相当于测试，它不会产生任何数据，只返回TRUE或FALSE，当返回值为TRUE时，外层查询才会执行。

　　例：查询是否存在年龄大于21岁的员工，如果存在，则查询department表中所有的记录

SELECT dname FROM department
WHERE EXISTS
(SELECT did FROM employee WHERE age>21);

　　

　　(3) 带ANY关键字的查询

　　ANY关键字表示满足其中任意一个条件，它允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询的任意一个比较条件，就返回一个结果作为外层查询条件

SELECT * FROM department 
WHERE did>ANY (SELECT did FROM employee);

　　

　　(4) 带ALL关键字的子查询

　　ALL关键字表示满足所有的条件，子查询返回的结果需同时满足所有的内层查询条件

SELECT * FROM department
WHERE did>ALL (SELECT did FROM employee);

　　

　　(5) 带比较运算符的子查询

SELECT * FROM department
WHERE did=(SELECT did FROM employee WHERE name='Marry');