MySQL数据库-DML与DQL（操作、查询）

一、DML

1.概念

DML（Data Manipulation Language）：数据操作语言，用来对数据库中表的数据记录进行增、删、改操作。

组成：

• insert：添加数据
• update：修改数据
• delete：删除数据

2.insert语句

（1）添加单条数据：

insert [into] 表名 values (值1，值2……);    #给全部字段添加单条数据

insert [into] 表名 (字段1，字段2……) values (值1，值2……);    #给指定字段添加单条数据

（2）批量添加多条数据：

insert [into] 表名 values (值1, 值2, ...,值n), (值1, 值2, ...,值n); # 全字段

insert [into] 表名 (字段名1, 字段名2) values (值1, 值2), (值1, 值2); # 指定字段

（3）注意事项

• 插入数据时，指定的字段顺序必须与值的顺序一致
• 字符串和日期型数据必须使用单引号或双引号定界
• 如果需要插入其他特殊字符，应该采用\转义字符做前缀
• 注意插入数据的范围

（4）replace和insert语句的区别

• replace语句的功能与insert语句的功能基本相同
• 不同之处在于：使用replace语句向表插入新记录时，如果新记录的主键值或者唯一性约束的字段值 与已有记录相同，则已有记录先被删除（注意：已有记录删除时也不能违背外键约束条件），然后再 插入新记录
• 使用replace的最大好处就是可以将delete和insert合二为一(效果相当于更新)

3.update语句

（1）更新数据

 update 表名 set 字段名1 = 值1 , 字段名2 = 值2 , .... [where 条件] ;

（2）注意事项

• 在使用update更新数据时，一定要写上where条件，否则会对所有行的对应字段进行更新修改。
• 在生产环境中的数据库表，一般还有update_time字段，用于记录数据的更新时间的，所以一般修改某些 字段时会同时修改update_time字段值为当前时间。

4.delete和truncate

（1）删除数据

delete from 表名 [where 条件];

truncate table 表名;

（2）回滚：撤销之前的操作，将树恢复到之前的状态

开启事务：start transaction

回滚：rollback

示例：

mysql> start transaction; # 开启事务
Query OK, 0 rows affected (0.01 sec)

mysql> delete from student2 where id = 1001; # 删除一条记录
Query OK, 1 row affected (0.00 sec)

mysql> select * from student2; # 查看
+------+-----------+------+------------+
| id | name | sex | birthday |
+------+-----------+------+------------+
| 1002 | 薛佳颖 | 女 | 2023-07-14 |
| 1003 | 孙德胜 | 男 | 2023-07-14 |
| 1004 | 凤飞飞 | 女 | 2023-07-14 |
+------+-----------+------+------------+
3 rows in set (0.00 sec)

mysql> rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student2; # 再次查看
+------+-----------+------+------------+
| id | name | sex | birthday |
+------+-----------+------+------------+
| 1001 | 李成峰 | 男 | 2023-07-14 |
| 1002 | 薛佳颖 | 女 | 2023-07-14 |
| 1003 | 孙德胜 | 男 | 2023-07-14 |
| 1004 | 凤飞飞 | 女 | 2023-07-14 |
+------+-----------+------+------------+
4 rows in set (0.00 sec)

（3）注意事项

• delete删除记录时，若无where字句则会清空表所有记录
• delete 删除语句不能删除某一个字段的值，若需要删除某一个字段的值，可以使用update语句， 将该字段值置为NULL即可

（4）drop、truncate、delete的区别

• delete：删除数据，保留表结构，可以回滚，如果数据量大，很慢
• truncate： 删除所有数据，保留表结构，不可以回滚，一次全部删除所有数据，速度相对很快
• drop： 删除数据和表结构,删除速度最快

二、DQL

1.作用

DQL(Data Query Language) ：数据查询语言，使用select语句实现对数据表的查询。

2.DQL语法

语法构成：

select 字段列表 from 表名列表 where 条件列表 group by 分组字段列表 having 分组后条
件列表 order by 排序字段列表 limit 分页参数;

字句的执行顺序如下图：

分拆以下几个部分：

• 基本查询（不带任何条件）
• 条件查询（where）
• 聚合函数（count、max、min、avg、sum）
• 分组查询（group by）
• 排序查询（order by）
• 分页查询（limit）

3.基本查询

（1）查询所有内容

select * from 表名; # sql优化不推荐使用

（2）查询指定字段

select 字段1，字段2，字段3.....from 表名; # 字段即列名

（3）查询时给字段起别名

select 字段1 as '别名1' , 字段2 as '别名2' ... from 表名；

• as关键字可以对表或列进行重命名，使得SQL语句更加简洁易，as可以省略
• 别名中使用特殊字符,或者是强制大小写敏感,或有空格时,都可以通过为别名添加加双引号实现

（4）去重查询

select distinct 字段列表 from 表名; # 使重复的数据只显示一条

• 在对字段进行去重的时候，要保证distinct在所有字段的最前面
• 如果distinct关键字后面有多个字段时，则会对多个字段进行组合去重，只有多个字段组合起来的值是相等的 才会被去重

4.条件查询

（1）语法

select 字段列表 from 表名 where 条件列表 ;

（2）条件运算符

（3）逻辑运算符

5.聚合函数查询

作用：将一列数据作为一个整体，进行纵向计算，本质也是分组函数

• count()：统计数量
• max()：最大值
• min()：最小值
• avg()：平均值
• sum()：求和

（1）语法

select 聚合函数(字段列表) from 表名

（2）count(1)、count(*)、count(某字段)的区别

从执行结果来看：

• count(*)：所有行进行统计，包括NULL行
• count(1)：所有行进行统计，包括NULL行
• count(某字段)：对某字段中非Null进行统计

从执行效率上看：

• 如果列为主键，count(列名)效率优于count(1)
• 如果列不为主键，count(1)效率优于count(列名)
• 如果表中存在主键，count(主键列名)效率最优
• 如果表中只有一列，则count(*)效率最优
• 如果表有多列，且不存在主键，则count(1)效率优于count(*)

（3）注意事项

• 由于where子句是对逐条的行记录进行筛选，不是一组值，所以聚合函数不可用于where字句后面

6.分组查询

作用：该列具有相同值的多条记录当成一组记录处理，最后只输出一条记录

（1）语法

select 字段,聚合函数 from 表名 [ where 条件 ] group by 字段 [ having 分组后过滤条件 ];

（2）分组查询示例

• 按照班级分组：select class_id as '班级编号', round(avg(score),2) as '平均成绩' from transcript group by class_id;
• 查询平均分在90分及以上的班级：select class_id as "班级", avg(score) as "平均分" from transcript group by class_id having avg(score)>90;
• 查询每个班级的成绩平均分（不统计成绩在85分以下的学生且过滤掉平均分在90分以下的班级），以便比较不 同班级的成绩：select class_id as "班级", avg(score) as "平均分" from transcript where score>85 group by class_id having avg(score)>90;

（3）注意事项

• group by汇总分组时，通常是结合聚合函数一起使用
• select后面的所有列中，没有使用聚合函数的列必须出现在group by的后面 分组后进行组过滤

（4）where与having区别

• 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤
• 判断条件不同：where不能对聚合函数进行判断，而having可以

7.排序查询

（1）语法

select 字段1,字段2.... from 表名 order by 字段1 asc/desc, 字段2 asc/desc....

（2）分析

• 升序：asc(ascend)，默认
• 降序：desc (descend)
• 排序依据为字符编码顺序排序，也可以自定义顺序排序
• 多次排序为分组排序，会在组内部进行再次排序

8.分页查询

作用：当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力，这时就可以用 limit 关键字来限制查询结果返回的条数，可以指定查询结果从哪条记录开始显示，一共显示多少条记录。

（1）三种语法

limit 初始位置，记录数            # 从第几条记录开始显示，显示几条，注意：第一条记录的位置是0
limit 记录数                     # 从第一条记录开始显示几条记录
limit 记录数 offset 初始位置     # 从第几条记录开始显示之后的几条记录 （偏移量方式）

（2）分页查询示例

• select * from student3 limit 2,3;              # 查看第2条开始的记录显示3条（包含第2条）
• select * from student3 limit 3;                 # 查看3条记录
• select * from student3 limit 3 offset 2;    # 查看第2条开始的3条记录，同limit 2,3

三、多表关联查询

1.多表之间的关系

（1）一对一

一张表中的一条数据对应另外一张表中的一条数据，比如一个人只有一张身份证，一张身份证对 应一个人。一对一关系比较少见，因为一对一这种关系的表可以合并成一张表。

（2）一对多

一张表中的一条数据可以和另外一张表中多条数据关联，拿学生表和成绩表举例，一个学生有多 个成绩。

（3）多对多

拿学生表和科目表举例，一个学生可以选择多门课程，一个课程也可以被很多学生选择；多对多 关系实现需要借助第三张中间表。中间表至少包含两个字段，将多对多的关系，拆成一对多的关系。

2.多表关联查询

多表关联查询是使用一条SQL语句，将关联的多张表的数据查询出来

组成：

• 交叉连接查询
• 内连接查询
• 外连接查询
• 自连接查询
• 子查询

3.交叉连接查询

（1）概念

即数据没有条件地连接在一起进行展示，可以理解为一张表的每一行去和另外一张表的任意一行进行匹配，返回笛卡尔积：即A表有m行数据，B表有n行数据，则返回m*n行数据。

（2）笛卡尔积

笛卡尔乘积是指在数学中，两个集合A集合 和 B集合的所有组合情况，会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选。

（3）语法

 select * from 表1,B表2;

（4）示例：查询学生表和成绩表

由于有很多冗余数据，这里不一一截图出来了。

4.内连接查询

（1）概念

查询两个表的交叉部分

（2）语法

select 字段1,字段2... from 表1 inner join 表2 on 表1.字段=表2.字段 where 条件;

内连接查询需要找到2个表的共有字段，如上面的学生表和成绩表

（3）示例1：查询学生每门课的成绩及个人信息

（4）示例2：只查询女学生每门课的成绩及个人信息，并以成绩为倒序查看

5.外连接查询

（1）左连接

left join：以左边的表为主表，展示主表的所有数据，根据条件查询连接右边表的数据，若满足条件则展示，若不满足则以NULL显示，可以理解为：在内连接的基础上保证左边表的数据全部显示。

语法：

select 字段 from 表1 left outer join 表2 on 表1.字段=表2.字段 where 条件;

示例：左连接查询学生表和成绩表

可以看到左连接以左表为主表，全部显示，右表无对应数据的以NULL显示

（2）右连接

right join：右边的表为主表，展示右边表的所有数据，根据条件查询join左边表的数据，若满足则展示，若不满足则以NULL显示，可以理解为：在内连接的基础上保证右边表的数据全部显示。

语法：

select 字段 from 表1 right outer join 表2 on 表1.字段=表2.字段 where 条件;

示例：右连接查询学生表和成绩表

（3）union联合查询

又称为全外连接、满连接，用于将左表和右表的数据都查询出来，然后按照连接条件连接查询并去重 （不去重）

• union： 会自动压缩多个结果集合中的重复结果，对两个结果集进行并集操作，不包括重复行
• union all： 则将所有的结果全部显示出来，不管是不是重复，包括重复行

注意：

• union 和 union all 内部的 select 语句必须拥有相同数量的列
• 每条 select 语句中列的顺序必须相同

示例1：联合查询学生表和成绩表（去重）

示例2：联合查询学生表和成绩表（不去重）

6.自连接查询

（1）概念

自连接顾名思义就是自己跟自己连接，参与连接的表都是同一张表。（通过给表取别名虚拟出2张表）

（2）语法

select 字段 from 表1 a , 表1 b where 条件;

select 字段 from 表1 a [left] join 表1 b on 条件;

（3）示例1：查询每个学生的信息及班长姓名

（4）示例2：使用左连接完成查询

使用另外一个地域库，确保左表没有重复值

（5）注意事项

• 自连接会增加查询的时间和空间复杂度，因此在实际应用中应谨慎使用。
• 为了避免自连接造成的死循环，应该在where子句中加入限制条件，例如限制查询的层数

7.子查询

（1）概念

在一个 SELECT 语句中嵌入另外一个 SELECT 语句，被嵌入的 SELECT 语句称之为子查询，子查询 所在的查询称为外部查询，子查询要么充当条件，要么充当主查询的数据源。

根据子查询返回的结果可以把子查询分为以下几类：

• 行子查询：子查询结果是一个数据（单行单列）
• 列子查询：子查询结果是一列（多行单列）
• 表子查询：子查询结果是一张临时表（多行多列）

子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字，还可以包含比较运算符：= 、 !=、> 、<等

（2）示例1：列子查询

• 查询和孙德胜同一个班的学生信息

• 查询李锐同学的LINUX课程成绩

（3）示例2：列子查询

为了能够更好的实现示例，这里对学生表加入了新字段以及新数据，ps：有一对同名孙德胜的学生

• 使用in运算符查询和孙德胜年龄相同的信息，ps：只要和其中一个孙德胜的年龄相同即可

• 使用some运算符查询比孙德胜年龄大的学生信息，ps：比任意一个孙德胜年龄大即可

• 使用any运算符查询比孙德胜年龄小的学生信息，ps：比最小的孙德胜年龄小即可

（4）示例3：表子查询

• 查询LINUX和JAVA两门课的成绩信息

（5）总结

• 子查询要包含在括号内，其查询结果可以当作数据源来用
• 行子查询可以用=、、!=等各种比较符号来做判断
• 列级子查询，需要用 in
• 表级子查询，返回结果不是当作条件用，而是当作表来用需要写在from 或 join后面

四、SQL函数

1.聚合函数

2.数值型函数

示例：

3.字符串函数

4.日期和时间函数

5.流程控制函数

示例：

• IF判断

• IFNULL判断，将之前选课成绩为NULL的进行转换

• case多分支，检索三个表查看成绩并计算等级成绩