一对一

user 表叫主表，使用<font style="color:rgb(37, 41, 51);">外键</font>引用它的 id_card 表是从表。

/* 主表 */
CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` VARCHAR(45) NOT NULL COMMENT '名字',
  PRIMARY KEY (`id`)
);

/* 从表 */
CREATE TABLE `id_card` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `card_name` varchar(45) NOT NULL COMMENT '身份证号',
  `user_id` int DEFAULT NULL COMMENT '用户 id',
  PRIMARY KEY (`id`),
  INDEX `card_id_idx` (`user_id`),
  CONSTRAINT `user_id` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`)
  ON UPDATE CASCADE; -- 设置更新时的级联操作
)  CHARSET=utf8mb4

<font style="color:rgb(37, 41, 51);">PRIMARY KEY</font> 是指定 id 为主键。

<font style="color:rgb(37, 41, 51);">INDEX</font> 是建立索引，索引名是 card_id_idex，这个是用于加速 user_id 的访问的。

<font style="color:rgb(37, 41, 51);">CONSTRINT user_id FOREIGN KEY</font> 是给 user_id 添加一个外键约束，然后 user_id <font style="color:rgb(37, 41, 51);">REFERENCES user id</font> 则是指定 user_id 引用这 user 表的 id 列。

JOIN

JOIN 语句用于将两个或更多的表联接在一起。

ON条件不仅限于测试相等关系，还可以包含任何有效的 SQL 比较运算符（如<,>,<=,>=,<>,!=）。

join 前

join 后

JOIN 有 3 种 类型

<font style="color:rgb(37, 41, 51);">INNER JOIN</font> 是只返回两个表中能关联上的数据。（默认使用 JOIN 就是该类型）

SELECT * FROM user JOIN id_card ON user.id = id_card.user_id;

<font style="color:rgb(37, 41, 51);">LEFT JOIN</font> 返回左表中的所有行, 即使右表中没有匹配的行也会返回。如果在右表中没有匹配的行，则结果是 NULL。

<font style="color:rgb(37, 41, 51);">RIGHT JOIN</font> 返回右表中的所有行，即使左表中没有匹配的行也会返回。如果在左表中没有匹配的行，则结果是 NULL。

<font style="color:rgb(37, 41, 51);">FULL JOIN</font> 如果有匹配的值，则返回左表和右表中的行，如果没有匹配的值则返回 NULL。

在 FROM 后的是左表，JOIN 后的表是右表。

级联方式

有以下 4 个可选值，定义从表在主表更新或删除时的动作：

• <font style="color:rgb(37, 41, 51);">CASCADE</font>： 主表主键更新，从表关联记录的外键跟着更新，主表记录删除，从表关联记录删除
• <font style="color:rgb(37, 41, 51);">SET NULL</font>：主表主键更新或者主表记录删除，从表关联记录的外键设置为 null
• <font style="color:rgb(37, 41, 51);">RESTRICT</font>：只有没有从表的关联记录时，才允许删除主表记录或者更新主表记录的主键 id
• <font style="color:rgb(37, 41, 51);">NO ACTION</font>： 同 RESTRICT，只是 sql 标准里分了 4 种，但 mysql 里 NO ACTION 等同于 RESTRICT。

一对多

一个部门有多个员工，员工只属于一个部门；

一个订单有多个商品，而商品只属于一个订单；

一个作者可以写多篇文章，而每篇文章只属于一个作者；

数据库建模，其实依旧两个表就可以实现。

比如一个部门表，一个员工表，部门表里的部门就是一，而员工表里的员工就是多。员工表添加一个外键约束，即 department_id 列引用 部门 的 id 列即可。

关联查询依旧使用 JOIN ON即可：

select * from department
    join employee on department.id = employee.department_id
    where department.id = 5

多对多

一个用户可以有多个角色，一个角色可能多个用户都有。

一篇文章可以有多个标签，一个标签可以多篇文章都有。

一个学生可以选修多门课程，一门课程可以被多个学生选修。

多对多我们一般这样设计，比如文章一个表、标签一个表，这两个表都不保存外键，然后添加一个中间表来保存双方的外键。

CREATE TABLE `article` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `title` VARCHAR(50) NOT NULL,
 `content` TEXT NOT NULL,
 PRIMARY KEY (`id`)
) CHARSET=utf8mb4;

CREATE TABLE `tag` (
 `id` INT NOT NULL AUTO_INCREMENT,
 `name` VARCHAR(50) NOT NULL,
 PRIMARY KEY (`id`)
);

创建中间表

有两个主键，即复合主键，且删除和更新的级联方式必须为 CASCADE，因为它就是用来保存关系的，如果关联的记录都没了，这个关系也就没存在的意义了。

CREATE TABLE `article_tag` (
  `article_id` int NOT NULL,
  `tag_id` int NOT NULL,
  PRIMARY KEY (`article_id`,`tag_id`),
  KEY `tag_id_idx` (`tag_id`),
  CONSTRAINT `article_id` FOREIGN KEY (`article_id`) REFERENCES `article` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tag_id` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

关联查询

多个 JOIN 连接多个表即可：这样查询出的就是 id 为 1 的 article 的所有标签：FROM 与 JOIN 后都可以重命名表名不用 as 敲个空格就是

SELECT t.name AS 标签名, a.title AS 文章标题
    FROM article a
    JOIN article_tag at ON a.id = at.article_id
    JOIN tag t ON t.id = at.tag_id
    WHERE a.id = 1

子查询

select 中使用

查询分数最高分的学生：

SELECT name, class FROM student WHERE score = (SELECT MAX(score) FROM student);

查询成绩高于全校平均成绩的学生记录：

SELECT * FROM student WHERE score > (SELECT AVG(score) FROM student);

**<font style="color:rgb(37, 41, 51);"> EXISTS、NOT EXISTS</font>**：当子查询有返回结果的时候成立，没有返回结果的时候不成立。

/* 查询有员工的部门 */
SELECT name FROM department
    WHERE EXISTS (
        SELECT * FROM employee WHERE department.id = employee.department_id
    );

/* 查询没有员工的部门 */
SELECT name FROM department
    WHERE NOT EXISTS (
            SELECT * FROM employee WHERE department.id = employee.department_id
    );

insert 中使用

product 产品表里的分类和平均价格查出来插入这个 avg_price_by_category 表

INSERT INTO avg_price_by_category (category, avg_price)
    SELECT category, AVG(price) FROM product GROUP BY category;

update 中使用

把技术部所有人的 name 前加上 “技术-”，就可以这么写：

UPDATE employee SET name = CONCAT('技术-', name)
    WHERE department_id = (
        SELECT id FROM department WHERE name = '技术部'
    );

delete 中使用

删除技术部所有的员工。

DELETE FROM employee WHERE department_id = (
    SELECT id FROM department WHERE name = '技术部'
);