38.SQL详解之DML

SQL详解之DML

我们接着上一课中创建的学校选课系统数据库，为大家讲解 DML 的使用。DML 可以帮助将数据插入到二维表（insert操作）、从二维表删除数据（delete操作）以及更新二维表的数据（update操作）。在执行 DML 之前，我们先通过下面的use命令切换到school数据库。

USE `school`;

insert操作

顾名思义，insert是用来插入行到二维表中的，插入的方式包括：插入完整的行、插入行的一部分、插入多行、插入查询的结果。我们通过如下所示的 SQL 向学院表中添加一个学院。

INSERT INTO `tb_college` 
VALUES
    (DEFAULT, '计算机学院', '学习计算机科学与技术的地方');

其中，由于学院表的主键是一个自增字段，因此上面的 SQL 中用default表示该列使用默认值，我们也可以使用下面的方式完成同样的操作。

INSERT INTO `tb_college` (`col_name`, `col_intro`) 
VALUES 
    ('计算机学院', '学习计算机科学与技术的地方');

我们推荐大家使用下面这种做法，指定为哪些字段赋值，这样做可以不按照建表时设定的字段顺序赋值，可以按照values前面的元组中给定的字段顺序为字段赋值，但是需要注意，除了允许为null和有默认值的字段外，其他的字段都必须要一一列出并在values后面的元组中为其赋值。如果希望一次性插入多条记录，我们可以在values后面跟上多个元组来实现批量插入，代码如下所示。

INSERT INTO `tb_college` 
    (`col_name`, `col_intro`) 
VALUES 
    ('外国语学院', '学习歪果仁的语言的学院'),
    ('经济管理学院', '经世济民，治理国家；管理科学，兴国之道'),
    ('体育学院', '发展体育运动，增强人民体质');

在插入数据时，要注意主键是不能重复的，如果插入的数据与表中已有记录主键相同，那么insert操作将会产生 Duplicated Entry 的报错信息。再次提醒大家，如果insert操作省略了某些列，那么这些列要么有默认值，要么允许为null，否则也将产生错误。在业务系统中，为了让insert操作不影响其他操作（主要是后面要讲的select操作）的性能，可以在insert和into之间加一个low_priority来降低insert操作的优先级，这个做法也适用于下面要讲的delete和update操作。

假如有一张名为tb_temp的表中有a和b两个列，分别保存了学院的名称和学院的介绍，我们也可以通过查询操作获得tb_temp表的数据并插入到学院表中，如下所示，其中的select就是我们之前提到的 DQL，在下一课中会详细讲解。

INSERT INTO `tb_college`
    (`col_name`, `col_intro`)
SELECT `a`, `b` 
  FROM `tb_temp`;

delete 操作

如果需要从表中删除数据，可以使用delete操作，它可以帮助我们删除指定行或所有行，例如我们要删除编号为1的学院，就可以使用如下所示的 SQL。

DELETE
  FROM `tb_college`
 WHERE col_id=1;

注意，上面的delete操作中的where子句是用来指定条件的，只有满足条件的行会被删除。如果我们不小心写出了下面的 SQL，就会删除学院表中所有的记录，这是相当危险的，在实际工作中通常也不会这么做。

DELETE
  FROM `tb_college`;

需要说明的是，即便删除了所有的数据，delete操作不会删除表本身，也不会让 AUTO_INCREMENT 字段的值回到初始值。如果需要删除所有的数据而且让 AUTO_INCREMENT 字段回到初始值，可以使用truncate table执行截断表操作，truncate的本质是删除原来的表并重新创建一个表，它的速度其实更快，因为不需要逐行删除数据。但是请大家记住一点，用truncate table删除数据是非常危险的，因为它会删除所有的数据，而且由于原来的表已经被删除了，要想恢复误删除的数据也会变得极为困难。

update 操作

如果要修改表中的数据，可以使用update操作，它可以用来删除指定的行或所有的行。例如，我们将学生表中的“杨过”修改为“杨逍”，这里我们假设“杨过”的学号为1001，代码如下所示。

UPDATE `tb_student`
   SET `stu_name`='杨逍'
 WHERE `stu_id`=1001;

注意上面 SQL 中的where子句，我们使用学号作为条件筛选出对应的学生，然后通过前面的赋值操作将其姓名修改为“杨逍”。这里为什么不直接使用姓名作为筛选条件，那是因为学生表中可能有多个名为“杨过”的学生，如果使用 stu_name 作为筛选条件，那么我们的update操作有可能会一次更新多条数据，这显然不是我们想要看到的。还有一个需要注意的地方是update操作中的set关键字，因为 SQL 中的=并不表示赋值，而是判断相等的运算符，只有出现在set 关键字后面的=，才具备赋值的能力。

如果要同时修改学生的姓名和生日，我们可以对上面的update语句稍作修改，如下所示。

UPDATE `tb_student`
   SET `stu_name`='杨逍'
     , `stu_birth`='1975-12-29'
 WHERE `stu_id`=1001;

update语句中也可以使用查询的方式获得数据并以此来更新指定的表数据，有兴趣的读者可以自行研究。在书写update语句时，通常都会有where子句，因为实际工作中几乎不太会用到更新全表的操作，这一点大家一定要注意。

完整的数据

下面我们给出完整的向 school 数据库的五张表中插入数据的 SQL。

USE `school`;

-- 插入学院数据
INSERT INTO `tb_college` 
    (`col_name`, `col_intro`) 
VALUES 
    ('计算机学院', '计算机学院1958年设立计算机专业，1981年建立计算机科学系，1998年设立计算机学院，2005年5月，为了进一步整合教学和科研资源，学校决定，计算机学院和软件学院行政班子合并统一运作、实行教学和学生管理独立运行的模式。 学院下设三个系：计算机科学与技术系、物联网工程系、计算金融系；两个研究所：图象图形研究所、网络空间安全研究院（2015年成立）；三个教学实验中心：计算机基础教学实验中心、IBM技术中心和计算机专业实验中心。'),
    ('外国语学院', '外国语学院设有7个教学单位，6个文理兼收的本科专业；拥有1个一级学科博士授予点，3个二级学科博士授予点，5个一级学科硕士学位授权点，5个二级学科硕士学位授权点，5个硕士专业授权领域，同时还有2个硕士专业学位（MTI）专业；有教职员工210余人，其中教授、副教授80余人，教师中获得中国国内外名校博士学位和正在职攻读博士学位的教师比例占专任教师的60%以上。'),
    ('经济管理学院', '经济学院前身是创办于1905年的经济科；已故经济学家彭迪先、张与九、蒋学模、胡寄窗、陶大镛、胡代光，以及当代学者刘诗白等曾先后在此任教或学习。');

-- 插入学生数据
INSERT INTO `tb_student` 
    (`stu_id`, `stu_name`, `stu_sex`, `stu_birth`, `stu_addr`, `col_id`) 
VALUES
    (1001, '杨过', 1, '1990-3-4', '湖南长沙', 1),
    (1002, '任我行', 1, '1992-2-2', '湖南长沙', 1),
    (1033, '王语嫣', 0, '1989-12-3', '四川成都', 1),
    (1572, '岳不群', 1, '1993-7-19', '陕西咸阳', 1),
    (1378, '纪嫣然', 0, '1995-8-12', '四川绵阳', 1),
    (1954, '林平之', 1, '1994-9-20', '福建莆田', 1),
    (2035, '东方不败', 1, '1988-6-30', NULL, 2),
    (3011, '林震南', 1, '1985-12-12', '福建莆田', 3),
    (3755, '项少龙', 1, '1993-1-25', '四川成都', 3),
    (3923, '杨不悔', 0, '1985-4-17', '四川成都', 3);

-- 插入老师数据
INSERT INTO `tb_teacher` 
    (`tea_id`, `tea_name`, `tea_title`, `col_id`) 
VALUES 
    (1122, '张三丰', '教授', 1),
    (1133, '宋远桥', '副教授', 1),
    (1144, '杨逍', '副教授', 1),
    (2255, '范遥', '副教授', 2),
    (3366, '韦一笑', DEFAULT, 3);

-- 插入课程数据
INSERT INTO `tb_course` 
    (`cou_id`, `cou_name`, `cou_credit`, `tea_id`) 
VALUES 
    (1111, 'Python程序设计', 3, 1122),
    (2222, 'Web前端开发', 2, 1122),
    (3333, '操作系统', 4, 1122),
    (4444, '计算机网络', 2, 1133),
    (5555, '编译原理', 4, 1144),
    (6666, '算法和数据结构', 3, 1144),
    (7777, '经贸法语', 3, 2255),
    (8888, '成本会计', 2, 3366),
    (9999, '审计学', 3, 3366);

-- 插入选课数据
INSERT INTO `tb_record` 
    (`stu_id`, `cou_id`, `sel_date`, `score`) 
VALUES 
    (1001, 1111, '2017-09-01', 95),
    (1001, 2222, '2017-09-01', 87.5),
    (1001, 3333, '2017-09-01', 100),
    (1001, 4444, '2018-09-03', NULL),
    (1001, 6666, '2017-09-02', 100),
    (1002, 1111, '2017-09-03', 65),
    (1002, 5555, '2017-09-01', 42),
    (1033, 1111, '2017-09-03', 92.5),
    (1033, 4444, '2017-09-01', 78),
    (1033, 5555, '2017-09-01', 82.5),
    (1572, 1111, '2017-09-02', 78),
    (1378, 1111, '2017-09-05', 82),
    (1378, 7777, '2017-09-02', 65.5),
    (2035, 7777, '2018-09-03', 88),
    (2035, 9999, '2019-09-02', NULL),
    (3755, 1111, '2019-09-02', NULL),
    (3755, 8888, '2019-09-02', NULL),
    (3755, 9999, '2017-09-01', 92);

注意：上面的insert语句使用了批处理的方式来插入数据，这种做法插入数据的效率比较高。