MySQL03 DML详解

        DML可以帮助数据插入到二维表（insert操作）、从二维表删除数据（delete操作）以及更新二维表的数据（update）。

1. insert操作

        顾名思义，insert是用来插入行到二维表中的，插入的方式包括：插入行的一部分、插入多行、插入查询的结果。在上一篇中我们建立了school表，本文继续以上文的school表为例

        向学院表中添加一个学院

insert into tb_college values (default, '计算机学院','xxxxxxxx');

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

insert into `tb_college` ( `col_name` , `col_intro` ) values ( 'xxxxxxxxxxxxxx ' ) ;

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

 

insert into `tb_college`

        ( `col_name` , `col_intro` )

values

        ( ' 外国语学院 ' , ' 学习歪果仁的语⾔的学院 ' ) ,

        ( ' 经济管理学院 ' , ' 经世济⺠，治理国家；管理科学，兴国之道 ' ) ,

        ( ' 体育学院 ' , ' 发展体育运动，增强⼈⺠体质 ' ) ;

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

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

insert into `tb_college`

        ( `col_name` , `col_intro` )

select `a` , `b` from `tb_temp` ;

 2. delete操作

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

delete from `tb_college` where col_id = 1 ;

        注意：上面的delete操作中的where子句是用来指定条件的，只有满足条件的行会被删除。如果我们不小心写出了下面的sql，就会阐述学院表中的所有的记录，这是相当危险的。

delete from tb_college;

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

3. update操作

        如果要修改表中的数据，可以使用update操作，它可以用来删除指定的行或所有的行，例如，我们将学生表中的“杨过”改为“杨逍”

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语句中也可以使用查询的方式获得数据并以此来更新指定的表数据。

4. select查询操作

        查询是非常重要的操作，关系着我们能否从关系数据库中获取我们需要的数据，以下是查询的基本操作

USE school;

-- 查询所有学⽣的所有信息

SELECT stu_id,

stu_name,

stu_sex,

stu_birth,

stu_addr,

col_id

FROM tb_student;

-- 查询学⽣的学号、姓名和籍贯 ( 投影和别名 )

SELECT stu_id AS 学号 ,

stu_name AS 姓名 ,

stu_addr AS 籍贯

FROM tb_student;

-- 查询所有课程的名称及学分 ( 投影和别名 )

SELECT cou_name AS 课程名称 ,

cou_credit AS 学分

FROM tb_course;

-- 查询所有⼥学⽣的姓名和出⽣⽇期 ( 数据筛选 )

SELECT stu_name,

stu_birth

FROM tb_student

WHERE stu_sex = 0 ;

-- 查询籍贯为 “ 四川成都 ” 的⼥学⽣的姓名和出⽣⽇期 ( 数据筛选 )

SELECT stu_name,

stu_birth

FROM tb_student

WHERE stu_sex = 0

AND stu_addr = ' 四川成都 ' ;

-- 查询籍贯为 “ 四川成都 ” 或者性别是⼥的学⽣ ( 数据筛选 )

SELECT stu_name,

stu_birth

FROM tb_student

WHERE stu_sex = 0

OR stu_addr = ' 四川成都 ' ;

-- 查询所有 80 后学⽣的姓名、性别和出⽣⽇期 ( 数据筛选 )

SELECT stu_name,

stu_sex,

stu_birth

FROM tb_student

WHERE '1980-1-1' <= stu_birth

AND stu_birth <= '1989-12-31' ;

SELECT stu_name,

stu_sex,

stu_birth

FROM tb_student

WHERE stu_birth BETWEEN '1980-1-1' AND '1989-12-31' ;

-- 查询学分⼤于 2 的课程的名称和学分 ( 数据筛选 )

SELECT cou_name,

cou_credit

FROM tb_course

WHERE cou_credit > 2 ;

-- 查询学分是奇数的课程的名称和学分 ( 数据筛选 )

SELECT cou_name,

cou_credit

FROM tb_course

WHERE cou_credit MOD 2 <> 0 ;

-- 查询选择选了 1111 的课程考试成绩在 90 分以上的学⽣学号 ( 数据筛选 )

SELECT stu_id

FROM tb_record

WHERE cou_id = 1111

AND score > 90 ;

-- 查询名字叫 “ 杨过 ” 的学⽣的姓名和性别 ( 数据筛选 )

SELECT stu_name AS 姓名 ,

CASE stu_sex WHEN 1 THEN ' 男 ' ELSE ' ⼥ ' END AS 性别

FROM tb_student

WHERE stu_name = ' 杨过 ' ;

SELECT stu_name AS 姓名 ,

IF ( stu_sex, ' 男 ' , ' ⼥ ' ) AS 性别

FROM tb_student

WHERE stu_name = ' 杨过 ' ;

-- 查询姓 “ 杨 ” 的学⽣姓名和性别 ( 模糊匹配 )

-- 通配符 % 匹配零个或任意多个字符

SELECT stu_name AS 姓名 ,

CASE stu_sex WHEN 1 THEN ' 男 ' ELSE ' ⼥ ' END AS 性别

FROM tb_student

WHERE stu_name LIKE ' 杨 %' ;

-- 查询姓 “ 杨 ” 名字两个字的学⽣姓名和性别 ( 模糊匹配 )

-- 通过符 _ 匹配⼀个字符

SELECT stu_name AS 姓名 ,

CASE stu_sex WHEN 1 THEN ' 男 ' ELSE ' ⼥ ' END AS 性别

FROM tb_student

WHERE stu_name LIKE ' 杨 _' ;

-- 查询姓 “ 杨 ” 名字三个字的学⽣姓名和性别 ( 模糊匹配 )

SELECT stu_name AS 姓名 ,

CASE stu_sex WHEN 1 THEN ' 男 ' ELSE ' ⼥ ' END AS 性别

FROM tb_student

WHERE stu_name LIKE ' 杨 __' ;

-- 查询学号最后⼀位是 3 的学⽣的学号和姓名 ( 模糊匹配 )

SELECT stu_id,

stu_name

FROM tb_student

WHERE stu_id LIKE '%3' ;

-- 查询名字中有 “ 不 ” 字或 “ 嫣 ” 字的学⽣的学号和姓名 ( 模糊匹配和并集运算 )

SELECT stu_id,

stu_name

FROM tb_student

WHERE stu_name LIKE '% 不 %'

OR stu_name LIKE '% 嫣 %' ;

SELECT stu_id,

stu_name

FROM tb_student

WHERE stu_name LIKE '% 不 %'

UNION

SELECT stu_id,

stu_name

FROM tb_student

WHERE stu_name LIKE '% 嫣 %' ;

-- 查询姓 “ 杨 ” 或姓 “ 林 ” 名字三个字的学⽣的学号和姓名 ( 正则表达式模糊匹配 )

SELECT stu_id,

stu_name

FROM tb_student

WHERE stu_name REGEXP '[ 林杨 ][\\u4e00-\\u9fa5]{2}' ;

-- 查询没有录⼊籍贯的学⽣姓名 ( 空值处理 )

SELECT stu_name

FROM tb_student

WHERE TRIM ( stu_addr ) = ''

OR stu_addr is null ;

-- 查询录⼊了籍贯的学⽣姓名 ( 空值处理 )

SELECT stu_name

FROM tb_student

WHERE TRIM ( stu_addr ) <> ''

AND stu_addr is not null ;

-- 查询学⽣选课的所有⽇期 ( 去重 )

SELECT DISTINCT sel_date

FROM tb_record;

-- 查询学⽣的籍贯 ( 去重 )

SELECT DISTINCT stu_addr

FROM tb_student

WHERE TRIM ( stu_addr ) <> ''

AND stu_addr is not null ;

-- 查询男学⽣的姓名和⽣⽇按年龄从⼤到⼩排列 ( 排序 )

SELECT stu_name,

stu_birth

FROM tb_student

WHERE stu_sex = 1

ORDER BY stu_birth ASC ;

-- 补充：将上⾯的⽣⽇换算成年龄 ( ⽇期函数、数值函数 )

SELECT stu_name AS 姓名 ,

FLOOR ( DATEDIFF ( CURDATE () , stu_birth ) / 365 ) AS 年龄

FROM tb_student

WHERE stu_sex = 1

ORDER BY 年龄 DESC ;

-- 查询年龄最⼤的学⽣的出⽣⽇期 ( 聚合函数 )

SELECT MIN ( stu_birth )

FROM tb_student;

-- 查询年龄最⼩的学⽣的出⽣⽇期 ( 聚合函数 )

SELECT MAX ( stu_birth )

FROM tb_student;

-- 查询编号为 1111 的课程考试成绩的最⾼分 ( 聚合函数 )

SELECT MAX ( score )

FROM tb_record

WHERE cou_id = 1111 ;

-- 查询学号为 1001 的学⽣考试成绩的最低分、最⾼分、平均分、标准差、⽅差 ( 聚合函数 )

SELECT MIN ( score ) AS 最低分 ,

MAX ( score ) AS 最⾼分 ,

ROUND ( AVG ( score ) , 1 ) AS 平均分 ,

STDDEV ( score ) AS 标准差 ,

VARIANCE ( score ) AS ⽅差

FROM tb_record

WHERE stu_id = 1001 ;

-- 查询学号为 1001 的学⽣考试成绩的平均分，如果有 null 值， null 值算 0 分 ( 聚合函数 )

SELECT ROUND ( SUM ( score ) / COUNT ( * ) , 1 ) AS 平均分

FROM tb_record

WHERE stu_id = 1001 ;

-- 查询男⼥学⽣的⼈数 ( 分组和聚合函数 )

SELECT CASE stu_sex WHEN 1 THEN ' 男 ' ELSE ' ⼥ ' END AS 性别 ,

COUNT ( * ) AS ⼈数

FROM tb_student

GROUP BY stu_sex;

-- 查询每个学院学⽣⼈数 ( 分组和聚合函数 )

SELECT col_id AS 学院编号 ,

COUNT ( * ) AS ⼈数

FROM tb_student

GROUP BY col_id

WITH ROLLUP ;

-- 查询每个学院男⼥学⽣⼈数 ( 分组和聚合函数 )

SELECT col_id AS 学院编号 ,

CASE stu_sex WHEN 1 THEN ' 男 ' ELSE ' ⼥ ' END AS 性别 ,

COUNT ( * ) AS ⼈数

FROM tb_student

GROUP BY col_id, stu_sex;

-- 查询每个学⽣的学号和平均成绩 ( 分组和聚合函数 )

SELECT stu_id AS 学号 ,

ROUND ( AVG ( score ) , 1 ) AS 平均分

FROM tb_record

GROUP BY stu_id;

-- 查询平均成绩⼤于等于 90 分的学⽣的学号和平均成绩 ( 分组后的数据筛选 )

SELECT stu_id AS 学号 ,

ROUND ( AVG ( score ) , 1 ) AS 平均分

FROM tb_record

GROUP BY stu_id

HAVING 平均分 >= 90 ;

-- 查询 1111 、 2222 、 3333 三⻔课程平均成绩⼤于等于 90 分的学⽣的学号和平均成绩 ( 分组前后的数据筛选 )

SELECT stu_id AS 学号 ,

ROUND ( AVG ( score ) , 1 ) AS 平均分

FROM tb_record

WHERE cou_id in ( 1111 , 2222 , 3333 )

GROUP BY stu_id

HAVING 平均分 >= 90

ORDER BY 平均分 ASC ;

-- 查询年龄最⼤的学⽣的姓名 ( ⼦查询 )

SELECT stu_name

FROM tb_student

WHERE stu_birth = ( SELECT MIN ( stu_birth )

FROM tb_student ) ;

-- 查询选了两⻔以上的课程的学⽣姓名 ( ⼦查询和集合运算 )

SELECT stu_name

FROM tb_student

WHERE stu_id in ( SELECT stu_id

FROM tb_record

GROUP BY stu_id

HAVING COUNT ( * ) > 2 ) ;

-- 查询学⽣的姓名、⽣⽇和所在学院名称 ( 表连接 )

SELECT stu_name,

stu_birth,

col_name

FROM tb_student AS t1, tb_college AS t2

WHERE t1 .col_id = t2 .col_id ;

SELECT stu_name,

stu_birth,

col_name

FROM tb_student INNER JOIN tb_college

ON tb_student .col_id = tb_college .col_id ;

SELECT stu_name,

stu_birth,

col_name

FROM tb_student NATURAL JOIN tb_college;

SELECT stu_name,

stu_birth,

col_name

FROM tb_student CROSS JOIN tb_college;

-- 查询学⽣姓名、课程名称以及成绩 ( 表连接 )

SELECT stu_name,

cou_name,

score

FROM tb_student, tb_course, tb_record

WHERE tb_student .stu_id = tb_record .stu_id

AND tb_course .cou_id = tb_record .cou_id

AND score is not null ;

SELECT stu_name,

cou_name,

score

FROM tb_student

INNER JOIN tb_record

ON tb_student .stu_id = tb_record .stu_id

INNER JOIN tb_course

ON tb_course .cou_id = tb_record .cou_id

WHERE score is not null ;

SELECT stu_name,

cou_name,

score

FROM tb_student

NATURAL JOIN tb_record

NATURAL JOIN tb_course

WHERE score is not null ;

-- 补充：上⾯的查询结果取前 5 条数据 ( 分⻚查询 )

SELECT stu_name,

cou_name,

score

FROM tb_student

NATURAL JOIN tb_record

NATURAL JOIN tb_course

WHERE score is not null

ORDER BY cou_id ASC , score DESC

LIMIT 5 ;

-- 补充：上⾯的查询结果取第 6-10 条数据 ( 分⻚查询 )

SELECT stu_name,

cou_name,

score

FROM tb_student

NATURAL JOIN tb_record

NATURAL JOIN tb_course

WHERE score is not null

ORDER BY cou_id ASC , score DESC

LIMIT 5

OFFSET 5 ;

-- 补充：上⾯的查询结果取第 11-15 条数据 ( 分⻚查询 )

SELECT stu_name,

cou_name,

score

FROM tb_student

NATURAL JOIN tb_record

NATURAL JOIN tb_course

WHERE score is not null

ORDER BY cou_id ASC , score DESC

LIMIT 10 , 5 ;

-- 查询选课学⽣的姓名和平均成绩 ( ⼦查询和表连接 )

-- Error Code: 1248. Every derived table must have its own alias

SELECT stu_name,

avg_score

FROM tb_student

NATURAL JOIN ( SELECT stu_id,

ROUND ( AVG ( score ) , 1 ) AS avg_score

FROM tb_record

GROUP BY stu_id ) as tmp;

-- 查询学⽣的姓名和选课的数量 ( ⼦查询和表连接 )

SELECT stu_name,

total

FROM tb_student

NATURAL JOIN ( SELECT stu_id,

COUNT ( * ) AS total

FROM tb_record

GROUP BY stu_id ) as tmp;

-- 查询每个学⽣的姓名和选课数量 ( ⼦查询和左外连接 )

SELECT stu_name AS 姓名 ,

COALESCE ( total, 0 ) AS 选课数量

FROM tb_student AS t1

LEFT JOIN ( SELECT stu_id,

COUNT ( * ) AS total

FROM tb_record

GROUP BY stu_id ) AS t2

ON t1 .stu_id = t2 .stu_id ;

        有⼏个地⽅需要加以说明：

• MySQL⽬前的版本不⽀持全外连接，上⾯我们通过 union 操作，将左外连接和右外连接的结果求并集实现全外连接的效果。⼤家可以通过下⾯的图来加深对连表操作的认识。

•  MySQL中支持多种类型的运算符，包括：算术运算符（+、-、*、/、%）、比较运算符（= 、 <> 、 <=> 、 < 、 <= 、 > 、 >= 、 BETWEEN...AND.. .、 IN 、 IS NULL 、 IS NOT
  NULL 、 LIKE 、 RLIKE 、 REGEXP）、逻辑运算符（not、and、or、xor）和位运算符（&、|、^、~、>>、<<），我们可以在DML中使用这些运算符处理数据。
• 在查询数据时，可以在select语句及其子句（如where子句、order by子句、having子句等）中使用函数，这些函数包括字符串函数、数值函数、时间日期函数、流程函数等，如下所示

~ 常用函数 - MySQL内置函数

        - 聚合函数

            ~ 描述性统计信息 - sum() / avg() / count() / max() / min() / var_pop() / var_samp() / stddev_pop() / stddev_samp()

            ~ 其他 - group_concat() / json_arrayagg() / json_objectagg()

        - 数值函数

            ~ 绝对值和符号函数 - abs() / sign()

            ~ 取整和四舍五入 - ceiling() / floor() / round() / truncate()

            ~ 三角和反三角函数 - sin() / cos() / tan() / cot() / asin() / acos() / atan()

            ~ 指数和对数 - pow() / exp() / log() / log10() / log2()

            ~ 随机数 - rand()

            ~ 弧度和角度 - degrees() / radians()

            ~ 平方根 - sqrt()

        - 字符串函数

            ~ 长度 - char_length() / length()

            ~ 拼接 - concat()

            ~ 大小写 - lower() / upper()

            ~ 取子串 - left() / right() / substring()

            ~ 替换 - replace()

            ~ 修剪 - trim() / ltrim() / rtrim()

            ~ 定位 - instr() / locate()

            ~ 填充 - lpad() / rpad()

            ~ BASE64编解码 - from_base64() / to_base64()

            ~ 反转 - reverse()

        - 日期时间函数

            ~ 获取日期和时间 - current_timestamp() / current_date() / current_time()

            ~ 获取部分信息 - date() / time() / year() / quarter() / month() / day() / weekday() / hour() / minute() / second()

            ~ 计算时间差 - datediff() / timestampdiff()

            ~ 添加间隔 - date_add() / date_sub()

            ~ 格式化日期时间 - date_format()

        - 流程控制函数

            ~ if() / ifnull() / nullif()

        - 窗口函数

            ~ 排序 - rank() / dense_rank() / row_number() / percent_rank()

            ~ 取数 - lag() / lead() / first_value() / last_value() / nth_value()

        - 其他函数

            ~ 类型转换 - cast() / convert()

            ~ 信息获取 - current_role() / current_user() / database() / last_insert_id() / version()

            ~ 全局唯一标识符 - uuid() / uuid_to_bin() / bin_to_uuid()