MySQL基础（三）表关系及数据的增删改查

这一部分主要使用SQL中的DML，数据库操作语言（data manipulation language），对数据库数据进行增、删、改、查操作，作为前提，先介绍关系型数据库中的表关系。

关系型数据库（RDBMS）

MySQL作为关系型数据库，数据是存储在表中的，假如设计一个订单系统，可能包含以下信息：

• 供应商（Vendors）
• 产品目录（Products）
• 客户列表（Customers）
• 订单（Orders）
• 可能还会为产品打上一些标签（比如，新品，进口商品，本地特产等）

每个表中都有个特殊的列（字段），称为主键（Primary Key），其值用来唯一标识表中的一行数据（记录）。表中还可以有称为外键（Foreign Key）的列，引用同一个表或不同表中某行的主键。因此，通过外键，表与表之间是有关联关系的，这也是建立关系型数据库模型的基础。

下面我们试着设计一下这个订单系统的表结构，就拿其中的供应商（Vendors）和产品目录（Products）来说：

供应商表（Vendors）：

列	说明	主键/外键
vendor_id	供应商唯一ID，	PK
vend_name	供应商名字
vend_address	供应商地址

产品目录表（Products）：

列	说明	主键/外键
prod_id	产品唯一ID	PK
prod_name	产品名字
prod_price	产品价格
vendor_id	供应商ID（关联Vendors表的vendor_id列）	FK

Products表存放产品记录，每个产品都有prod_name, prod_price，并且有唯一的prod_id作为主键。除此之外，还有一个外键vendor_id，引用Vendors表中的vend_id，通过这种方式，为每个产品指定了供应商。我们不需要在产品表中再添加更多的列来存储其供应商的具体信息，以后直接根据这个外键，去Vendors表中查询即可。

从中我们可以看出，关系型数据库在存储数据时是很高效的，避免了数据冗余。而且，关系型数据库的可伸缩性远比非关系型数据库要好。比如，对数据库中供应商名字，地址等信息的修改，可以只更新Vendors表中的记录，相关表中的记录不用改动。

但是另一方方面，将数据分别存放在不同的表中有时是很复杂的，我们需要查询多个关联的表，才能拿到我们想要的数据。因此，在设计表结构，以及编写SQL语句时，需要很好地理这种关联关系，知道什么信息存在什么表中。

表关系种类

一对多

比如上面的供应商和产品，一个供应商可能提供多个产品，而一个产品只能属于一个供应商，在多的一边（产品表）通过Foreign Key 描述这种关系。

一对一

比如，一张订单对应一张订单详情；一篇文章摘要，对应一篇具体的文章内容。一对一关系的Foreign Key建在关系的哪边都行。

多对多

这个是相对复杂的一种，但是也很好理解。还是上面的例子，比如给产品打标签，一个产品可能有多个标签（”进口“， ”新品“），而一个标签下可以对应多个产品（进口商品可能有很多）。

特别要注意的是，如果要描述多对多关系，需要借助第三张表，这个表称为关系表，这张表实际存储时，看起来是这样的：

id (PK)	prod_id (FK)	tag_id (FK)
1	1	1
2	1	2
3	2	1
4	3	1

除了自增的主键id，它还有两个外键，一个prod_id关联产品表，一个tag_id关联标签表。

插入数据（INSERT INTO）

这里将以教学管理为例，来创建数据库，创建表，插入数据。最后一部分的综合练习，将基于接下来要创建的数据。

创建表并定义约束关系

首先，创建一个数据库，就命名为school吧

CREATE DATABASE school CHARSET utf8;

关于表创建和约束的更多详细，请参考MySQL基础（二）数据库、表的创建及操作

下面我们我们将创建5张表，分别是：

• 班级表（Class）
• 课程表（Course）
• 成绩表（Score）
• 学生表（Student）
• 教师表（Teacher）

其中班级表和课程表是一对多关系；班级表和学生表是一对多关系；教师表和课程表是一对多关系；学生表和课程表是多对多关系。

-- -------------------------
-- 创建班级表
-- -------------------------
CREATE TABLE Class
(
  cls_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
  caption       char(30)     NOT NULL 
);

-- -------------------------
-- 创建课程表
-- -------------------------
CREATE TABLE Course
(
  crs_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
  crs_name      char(30)     NOT NULL ,
  teacher_id    int          NOT NULL  -- FK 
);

-- -------------------------
-- 创建成绩表
-- -------------------------
CREATE TABLE Score
(
  scr_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT ,  -- PK
  num           tinyint      NOT NULL ,
  student_id    int          NOT NULL ,  -- FK
  course_id     int          NOT NULL    -- FK
);

-- -------------------------
-- 创建学生表
-- -------------------------
CREATE TABLE Student
(
  std_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
  gender        enum('女', '男')   NOT NULL ,
  std_name      char(30)     NOT NULL ,
  class_id      int          NOT NULL  -- FK
);

-- -------------------------
-- 创建教师表
-- -------------------------
CREATE TABLE Teacher
(
  tea_id        int          NOT NULL PRIMARY KEY AUTO_INCREMENT , -- PK
  tea_name      char(30)     NOT NULL 
);

-- -------------------------
-- 定义外键
-- -------------------------
ALTER TABLE Course
ADD CONSTRAINT FK_Course_Teacher
FOREIGN KEY (teacher_id) REFERENCES Teacher (tea_id);

ALTER TABLE Score
ADD CONSTRAINT FK_Score_Student
FOREIGN KEY (student_id) REFERENCES Student (std_id);
ALTER TABLE Score
ADD CONSTRAINT FK_Score_Course
FOREIGN KEY (course_id) REFERENCES Course (crs_id);

ALTER TABLE Student
ADD CONSTRAINT FK_Student_Class
FOREIGN KEY (class_id) REFERENCES Class (cls_id);

定义外键的方式

注意：定义外键的数据类型一定要和关联表主键的数据类型一致！

建表时定义

CREATE TABLE Course
(
  crs_id        int          PRIMARY KEY AUTO_INCREMENT , 
  crs_name      char(30)     NOT NULL ,
  teacher_id    int          NOT NULL ,
  CONSTRAINT FK_Course_Teacher  -- 外键名
  FOREIGN KEY (teacher_id)  -- 外键字段
  REFERENCES Teacher (tea_id)  -- 关联表主键字段
);

建表后定义

CREATE TABLE Course
(
  crs_id        int          PRIMARY KEY AUTO_INCREMENT , 
  crs_name      char(30)     NOT NULL ,
  teacher_id    int          NOT NULL 
);

ALTER TABLE Course
ADD CONSTRAINT FK_Course_Teacher  -- 外键名
Foreign KEY (teacher_id) REFERENCES Teacher (tea_id);  -- 外键字段和关联表主键字段

删除外键

格式

ALTER TABLE 表名
DROP FOREIGN KEY 外键名；

示例

ALTER TABLE Course
DROP FOREIGN KEY FK_Course_Teacher;

插入数据

插入单条数据

格式

INSERT INTO 表名 [(fld1, fld2, ..., fldn)] 
VALUES (val1, val2, ..., valn);

注意：表名后的字段名可以省略，但建议加上，VALUES的值将依次匹配字段名，而不一定非要按照表中字段的实际次序；有默认值的字段或自增字段可不提供值。另外，对于自增id，如果插入数据时指定了非连续的值，比如表记录最后一行id是3，插入数据时指定id是9，那么以后再插入数据时，id将从9开始自增。

插入多条数据

格式

INSERT INTO 表名 [(fld1, fld2, ..., fldn)] 
VALUES (val1, val2, ..., valn),
       (val1, val2, ..., valn),
        -- ...
       (val1, val2, ..., valn);

示例

下面，我们将为上面创建的那5张表填充数据：

-- --------------------------
-- 插入班级数据
-- --------------------------
INSERT INTO Class (caption)
VALUES ('三年二班'), ('三年三班'), ('一年二班'), ('二年九班');

-- --------------------------
-- 插入教师数据
-- --------------------------
INSERT INTO Teacher (tea_name)
VALUES ('张磊老师'), ('李平老师'), ('刘海燕老师'), ('朱云海老师'), ('李杰老师');

-- -------------------------
-- 插入课程数据
-- -------------------------
INSERT INTO Course (crs_name, teacher_id)
VALUES ('生物', 1), ('物理', 2), ('体育', 3), ('美术', 2);

-- -------------------------
-- 插入学生数据
-- -------------------------
INSERT INTO Student (gender, std_name, class_id)
VALUES ('男', '理解', 1), ('女', '钢蛋', 1), ('男', '张三', 1), ('男', '张一', 1),
       ('女', '张二', 1), ('男', '张四', 1), ('女', '铁锤', 2), ('男', '李三', 2), 
       ('男', '李一', 2), ('女