MySQL（5）条件查询 | 单行函数 | 事务详解

一、select 的 的 5 个子句

1 、where 条件查询

从原表中的记录中进行筛选。

2 、group by 分组查询

很多情况下，用户都需要进行一些汇总操作，比如统计整个公司的人数或者统计每一个部门的人数等。

聚合函数

• AVG(【DISTINCT】 expr) 返回 expr 的平均值
• COUNT（【DISTINCT】 expr）返回 expr 的非 NULL 值的数目
• MIN(【DISTINCT】 expr)返回 expr 的最小值
• MAX(【DISTINCT】 expr)返回 expr 的最大值
• SUM（【DISTINCT】 expr）返回 expr 的总和

#AVG(【DISTINCT】 expr) 返回 expr 的平均值
SELECT AVG(basic_salary) FROM t_salary;

COUNT（【DISTINCT】 expr）返回 expr 的非 NULL 值的数目

#统计员工总人数
SELECT COUNT(*) FROM t_employee;#count(*)统计的是记录数

#统计员工表的员工所在部门数
SELECT COUNT(dept_id) FROM t_employee;#统计的是非 NULL 值
SELECT COUNT(DISTINCT dept_id) FROM t_employee;#统计的是非 NULL 值，并且去重

MIN(【DISTINCT】 expr)返回 expr 的最小值

#查询最低基本工资值
SELECT MIN(basic_salary) FROM t_salary;
#MAX(【DISTINCT】 expr)返回 expr 的最大值

#查询最高基本工资值
SELECT MAX(basic_salary) FROM t_salary;

#查询最高基本工资与最低基本工资的差值
SELECT MAX(basic_salary)-MIN(basic_salary) FROM t_salary;

SUM（【DISTINCT】 expr）返回 expr 的总和

#查询基本工资总和
SELECT SUM(basic_salary) FROM t_salary;

group by + 聚合函数

#统计每个部门的人数
SELECT dept_id,COUNT(*) FROM t_employee
GROUP BY dept_id;

#统计每个部门的平均基本工资
SELECT emp.dept_id,AVG(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

#统计每个部门的年龄最大者
SELECT dept_id,MIN(birthday) FROM t_employee GROUP BY dept_id;

#统计每个部门基本工资最高者
SELECT emp.dept_id,MAX(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

#统计每个部门基本工资之和
SELECT emp.dept_id,SUM(s.basic_salary )
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id;

注意：

用 count(*)，count(1)，谁好呢?
其实对于 MyISAM 引擎的表，没有区别的。
这种引擎内部有一计数器在维护着行数。
InnoDB 的表,用 count(*)直接读行数,效率很低,因为 innodb 真的要去数一遍。

关于 mysql 的 group by 的特殊：
注意：在 SELECT 列表中所有未包含在组函数中的列都应该是包含在 GROUP BY 子句中的，换句话说，SELECT 列表中最好不要出现 GROUP BY 子句中没有的列。

3 、having 筛选

having 与 where 类似,可筛选数据
having 与 where 不同点：

• where 针对表中的列发挥作用,查询数据；having 针对查询结果中的列发挥作用,筛选数据
• where 后面不能写分组函数，而 having 后面可以使用分组函数
• having 只用于 group by 分组统计语句

#按照部门统计员工人数，仅显示部门人数少于 3 人的
SELECT dept_id,COUNT(*) AS c
FROM t_employee
WHERE dept_id IS NOT NULL
GROUP BY dept_id
HAVING c <3;

#查询每个部门的平均工资，并且仅显示平均工资高于 10000
SELECT emp.dept_id,AVG(s.basic_salary ) AS avg_salary
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid AND dept_id IS NOT NULL
GROUP BY emp.dept_id
HAVING avg_salary >10000;

4 、order by 排序

• 按一个或多个字段对查询结果进行排序
  用法：order by col1,col2,col3…
  说明：先按 col1 排序如果 col1 相同就按照 col2 排序，依次类推col1，col2，col3 可以是 select 后面的字段也可以不是

• 默认是升序，也可以在字段后面加 asc 显示说明是升序，desc 为降序
  例如：order by click_count desc;
  如果两个字段排序不一样，例如：
  order by 字段 1 asc ,字段 2 desc;

• order by 后面除了跟 1 个或多个字段，还可以写表达式，函数，别名等。

#查询员工基本工资，按照基本工资升序排列，如果工资相同，按照 eid 升序排列
SELECT t_employee.eid,basic_salary FROM t_employee INNER JOIN t_salary
ON t_employee.eid = t_salary.eid
ORDER BY basic_salary,eid;

#查询员工基本工资，按照基本工资降序排列，如果工资相同，按照 eid 排列
SELECT t_employee.eid,basic_salary FROM t_employee INNER JOIN t_salary
ON t_employee.eid = t_salary.eid
ORDER BY basic_salary DESC,eid;

#统计每个部门的平均基本工资，并按照平均工资降序排列
SELECT emp.dept_id,AVG(s.basic_salary)
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id
ORDER BY AVG(s.basic_salary) DESC;

5 、limit 分页

limit m,n
m 表示从下标为 m 的记录开始查询，第一条记录下标为 0；
n 表示取出 n 条出来，如果从 m 开始不够 n 条了，就有几条取几条。
m=(page-1)*n，（page 页码，n 表示每页显示的条数）

如果第一页 limit 0,n
如果第二页 limit n,n
依次类推，得出公式 limit (page-1)*n , n

#查询员工信息，每页显示 5 条，第二页
SELECT * FROM t_employee LIMIT 5,5;

#统计每个部门的平均基本工资，并显示前三名
SELECT emp.dept_id,AVG(s.basic_salary)
FROM t_employee AS emp,t_salary AS s
WHERE emp.eid = s.eid
GROUP BY emp.dept_id
ORDER BY AVG(s.basic_salary) DESC
LIMIT 0,3;

二、单行函数

MySQL 数据库提供了很多函数包括：

• 数学函数；
• 字符串函数；
• 日期和时间函数；
• 条件判断函数；流程控制函数；
• 系统信息函数；
• 加密函数；
• 格式化函数；

1、数学函数

2、字符串函数

3、日期时间函数

（1）DATE_ADD(datetime,INTERVAL expr type)

（2）DATE_FORMAT(datetime ,fmt)和 STR_TO_DATE(str, fmt)

三、事务

DCL 用来控制数据库的访问，包括如下 SQL 语句：

• GRANT：授予访问权限
• REVOKE：撤销访问权限
• COMMIT：提交事务处理
• ROLLBACK：事务处理回退
• SAVEPOINT：设置保存点
• LOCK：对数据库的特定部分进行锁定

思考：我去银行给朋友汇款,我卡上有 1000 元，朋友卡上 500 元，我给朋友转账 50 元(无手续费)，如果我的钱刚扣，而朋友的钱又没加时，网线断了，怎么办?

1、事务的 ACID 特性

• 原子性(Atomicity)：原子意为最小的粒子，或者说不能再分的事物。数据库事务的不可再分的原则即为原子性。
  组成事务的所有查询必须：要么全部执行，要么全部取消（就像上面的银行例子）。
• 一致性(Consistency)：指数据的规则,在事务前/后应保持一致。
• 隔离性(Isolation)：简单点说，某个事务的操作对其他事务不可见的。
• 持久性(Durability)：当事务提交完成后，其影响应该保留下来，不能撤消。

2、 事务的用法

• 开启事务(start transaction)
• 执行 sql 操作(普通 sql 操作)
• 提交/回滚(commit/rollback)

注意:

• 建表的时候，选择 Innodb 引擎才支持事务。
• 默认情况下，MySQL 是自动提交事务，每次执行一个 SQL 语句时，如果执行成功，就会向数据库自动提交，而不能回滚。如果某一组操作需要在一个事务中，那么需要使用 start transaction，一旦 rollback 或 commit 就结束当次事务，之后的操作又自动提交。
• 如果需要在当前会话的整个过程中都取消自动提交事务，进行手动提交事务，就需要设置 set autocommit = false; 或set autocommit = 0；那样的话每一句 SQL 都需要手动 commit 提交才会真正生效。rollback 或 commit 之前的所有操作都视为一个事务，之后的操作视为另一个事务，还需要手动提交或回滚。
• 和 Oracle 一样，DDL 语句是不能回滚的，并且部分的 DDL 语句会造成隐式的提交，因此最好事务中不要涉及DDL 语句。

开启手动处理事务模式
set autocommit = false;
开始事务（推荐）
start transaction;
#查看当前表的数据
select * from t_stu_detail;

#删除整张表的数据
delete from t_stu_detail;

#查询该表数据，发现显示删除后的结果
select * from t_stu_detail;

回滚
rollback
#查看当前表的数据，发现又回来了
select * from t_stu_detail;

#删除整张表的数据
delete from t_stu_detail;

提交事务
commit;
#查看当前表的数据，发现真删除了
select * from t_stu_detail;

#插入一条记录
INSERT INTO t_stu_detail VALUES (1, '123456789012345678', '1990-01-21', '12345678901','a@163.com', '北七家');

保存还原点 1
savepoint point1;
#插入一条记录
INSERT INTO t_stu_detail VALUES
(2, '123456789012345677', '1990-02-21', '12345678902', 'b@163.com', '北七家');

保存还原点 2
savepoint point2;
#查看当前效果
select * from t_stu_detail;

回滚到某个还原点
rollback to point1;

#提交事务
commit;

#清空表
truncate t_stu_detail;
#回滚，对于 truncate 无法回滚
rollback;

#修改表结构
alter table t_stu_detail add description varchar(50);
#回滚，对于修改表结构的语句无法回滚
rollback;

3、数据库的隔离级别

对于同时运行的多个事务（ 多线程并发）, 当这些事务访问数据库中相同的数据时, 如果没有采取必要的隔离机制,就会导致各种并发问题: （问题的本质就是线程安全问题，共享数据的问题）

• 脏读：对于两个事务 T1, T2, T1 读取了已经被 T2 更新但还 没有被 提交的字段. 之后, 若 T2 回滚, T1读取的内容就是临时且无效的。
• 不可重复读：对于两个事务 T1, T2, T1 读取了一个字段，然后 T2 更新并提交了该字段.。之后, T1再次读取同一个字段, 值就不同了。
• 幻读：对于两个事务 T1, T2, T1 从一个表中读取了一个字段，然后 T2 在该表中 插入、删除了一些新的行.。之后，如果 T1 再次读取同一个表, 就会 多出、少了几行。

数据库事务的隔离性： 数据库系统必须具有隔离并发运行各个事务的能力，使它们不会相互影响，避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别。 数据库规定了多种事务隔离级别，不同隔离级别对应不同的干扰程度, 隔离级别越高，数据一致性就越好，但并发性越弱。

Oracle 支持的 2 种事务隔离级别：READ COMMITED，SERIALIZABLE。
Oracle 默认的事务隔离级别为: READ COMMITED。

Mysql 支持 4 中事务隔离级别。Mysql 默认的事务隔离级别为: REPEATABLE-READ。
每启动一个 mysql 程序, 就会获得一个单独的数据库连接。每个数据库连接都有一个变量@@tx_isolation, 表示当前的事务隔离级别。

• 查看当前的隔离级别: SELECT @@tx_isolation;
• 查看全局的隔离级别：select @@global.tx_isolation;
• 设置当前 mySQL 连接的隔离级别: set tx_isolation =‘repeatable-read’;
• 设置数据库系统的全局的隔离级别: set global tx_isolation =‘read-committed’;
  、

4、示例演示

1 ）脏读

脏读：进入餐厅发现“梦中情人”旁边座位已经有“帅哥”坐那儿了，正郁闷，打完饭，发现那个位置是空着的，又欣喜若狂，其实刚刚那个“帅哥”只是临时过去打个招呼。

2 ）不可重复读

不可重复读：在图书馆门口，发现自己占的位置旁边有位“美女”，等刷完卡，兴冲冲的走到那儿，发现已经变成一“如花”了。

3 ）幻读

大学考前画重点，老师说“第一章 xxxxxx 概念”，你赶紧找，“天啊，在哪儿啊”，等你画完，就听老师说：“第四章 xxxxx”，中间那些你都没听到。

4 ）序列化

5、 锁

（1 ）行级锁

InnoDB 的前三个隔离级别是行级锁。

（2 ）表级锁

InnoDB 事务隔离级别是 序列化，将会发生整张表的锁。

---

如果有收获！！！ 希望老铁们来个三连，点赞、收藏、转发
创作不易，别忘点个赞，可以让更多的人看到这篇文章，顺便鼓励我写出更好的博客