目录

• 前言
• 4、DQL查询语句（最重点）
• • 4.1、DQL
  • 4.2、指定字段查询
  • 4.3、where 条件子句
  • 4.4、联表查询
  • 4.5、分页和排序
  • 4.6、子查询
  • 4.7、分组和过滤
  • 4.8、select 小节
• 5、MySQL函数
• • 5.1、常用函数
  • 5.2、聚合函数（常用）
  • 5.3、数据库级别的MD5加密（扩展）
• 6、事务
• • 6.1、什么是事务

前言

这是狂神说 mysql 自学笔记，仅供参考学习，持续更新

以下是视频学习链接：
https://www.bilibili.com/video/BV1NJ411J79W

4、DQL查询语句（最重点）

4.1、DQL

（Data Query Language: 数据查询语言）

• 所有的查询操作都用它 select
• 简单的查询，复杂的查询它都能做
• 数据库中最核心的语言，最重要的语句
• 使用频率最高的语句

SELECT 语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
	[left | right | inner join table_name2] -- 联合查询
	[WHERE ...] -- 指定结果需满足的条件
	[GROUP BY ...] -- 指定结果按照哪几个字段来分组
	[HAVING] -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];
	-- 指定查询的记录从哪条至哪条

注意：[ ] 括号代表可选的，{ } 括号代表必选的

4.2、指定字段查询

-- 查询所有学生    SELECT 字段 FROM 表
SELECT * FROM student

-- 查询指定字段
SELECT `studentno`,`studentname` FROM student

-- 别名，给结果取一个名字    AS   可以给字段起别名，也可以给表起别名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student AS s

-- 函数   Concat(a, b)
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM student

语法：SELECT 字段, ... FROM 表

有的时候，列名字不是那么的见名知意。我们起别名 AS 字段名 as 别名 表名 as 别名

去重 distinct

作用：去除SELECT查询出来的结果中重复的数据，重复的数据只能显示一条

-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
SELECT `studentno` FROM result -- 查询有哪些同学参加了考试
SELECT DISTINCT `studentno` FROM result -- 发现重复数据，去重

数据库的列 （表达式）

SELECT VERSION()  -- 查询系统版本（函数）
SELECT 45/2 AS 计算结果 -- 用来计算（表达式）
SELECT @@auto_increment_increment -- 查询自增的步长（变量）

-- 学员考试成绩 + 1分查看
SELECT `studentno`,`studentresult`+1 AS '提分后' FROM result

数据库中的表达式： 文本值，列，NULL，函数，计算表达式，系统变量…

select 表达式 from 表

4.3、where 条件子句

作用：检索数据中符合条件的值

搜索的条件由一个或多个表达式组成！ 结果为 布尔值

逻辑运算符

尽量使用英文字母

-- ==================== where ===================
SELECT `studentno`,`studentresult` FROM result

-- 查询考试成绩在 95~100 分之间
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentresult`>=95 AND `studentresult`<=100

-- and   &&
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentresult`>=95 && `studentresult`<=100

-- 模糊查询（区间）
SELECT `studentno`,`studentresult` FROM result 
WHERE `studentresult` BETWEEN 95 AND 100

-- 除了1000号学生之外的同学的成绩
SELECT `studentno`,`studentresult` FROM result
WHERE NOT `studentno`=1000

-- !=   not
SELECT `studentno`,`studentresult` FROM result
WHERE `studentno`!=1000

模糊查询：比较运算符

-- ==================== where ===================
-- 查询性刘的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘%'

-- 查询姓刘的同学，名字后面只有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘_'

-- 查询姓刘的同学，名字后面有两个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘__'

-- 查询名字中间有家字的同学  %家%
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%家%'

-- ================= in (具体的一个或多个值)===================
-- 查询 1001，1002，1003号学员
SELECT `studentno`,`studentname` FROM `student` 
WHERE `studentno` IN (1001,1002,1003)

-- 查询在北京的同学
SELECT `studentno`,`studentname` FROM `student` 
WHERE `address` IN ('北京')


-- =========== null   not null ==================
-- 查询地址为空的同学 null   ''
SELECT `studentno`,`studentname` FROM `student`
WHERE `address`='' OR address IS NULL

-- 查询有出生日期的同学    不为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NOT NULL

-- 查询没有出生日期的同学    为空
SELECT `studentno`,`studentname` FROM `student`
WHERE `borndate` IS NULL

4.4、联表查询

JOIN 对比

-- ================ 联表查询 join ====================

-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result


/*思路
1. 分析需求，分析查询的字段来自哪些表（连接查询）
2. 确定使用哪种连接查询?  7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno = 成绩表 studentno

*/

-- join on  连接查询
-- where    等值查询

SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
INNER JOIN result AS r
WHERE s.studentno = r.studentno

-- right join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
RIGHT JOIN result AS r
ON s.studentno = r.studentno

-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
FROM student AS s
LEFT JOIN result AS r
ON s.studentno = r.studentno

-- 思考题（查询参加了考试的同学信息：学号，学生姓名，科目名，分数）
/*思路
1. 分析需求，分析查询的字段来自哪些表（连接查询）
2. 确定使用哪种连接查询?  7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 studentno = 成绩表 studentno

*/
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN `result` AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON sub.`subjectno` = r.`subjectno`


-- 我要查询哪些数据  select ...
-- 从哪几个表中查 from 表   XXX JOIN 连接的表 ON 交叉条件
-- 假设存在一种多张查询，慢慢来，先查询两张表然后再慢慢增加

-- from a left join b  左连接
-- from a right join b  右连接

自连接 （了解）

-- ================= 自连接 =================
CREATE TABLE `category`(
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

INSERT INTO `category` (`categoryid`,`pid`,`categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

自己的表和自己的表连接，核心：一张表拆成两张一样的表即可

父类

子类

操作：查询父类对应的子类关系

-- 查询父子信息：把一张表看成两个一摸一样的表
SELECT a.`categoryName` AS '父栏目', b.`categoryName` AS '子栏目'
FROM `category` AS a, `category` AS b
WHERE a.`categoryid` = b.`pid`

4.5、分页和排序

排序

-- 排序： 升序 ASC      ，降序 DESC
-- ORDER BY 通过哪个字段排序，怎么排
-- 查询的结果根据 成绩 降序 排序

SELECT * FROM `result` 
ORDER BY `studentresult` DESC

分页

-- 100 万条数据
-- 为什么要分页？
-- 缓解数据库压力，给人更好的体验	不分页：瀑布流

-- 分页，每页只显示五条数据
-- 语法：limit 起始值, 页面的大小
-- 网页应用：当前，总的页数，页面的大小 
-- LIMIT 0, 5      1 ~ 5    第一页
-- LIMIT 1, 5      2 ~ 6
-- LIMIT 5, 5      第二页
SELECT * FROM `result` 
ORDER BY `studentresult` DESC
LIMIT 5, 5

-- 第一页 limit 0, 5	(1 - 1) * 5
-- 第二页 limit 5, 5	(2 - 1) * 5
-- 第三页 limit 10, 5   (3 - 1) * 5
-- ...
-- 第N页 limit (n - 1) * pageSize, pageSize
-- [pageSize: 页面的大小]
-- [(n - 1) * pageSize : 起始值]
-- [n: 当前页]
-- [数据总数 / 页面大小 = 总页数]

语法：limit(查询起始下标, pageSize)

4.6、子查询

where (这个值是计算出来的)

本质：在 where 语句中嵌套一个子查询语句

-- ============ where ===============

-- 1、查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一：使用连接查询
SELECT `studentno`, r.`subjectno`, `studentresult`
FROM result AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE sub.`subjectname` = '数据库结构-1'
ORDER BY r.`studentresult` DESC


-- 方式二：使用子查询
SELECT `studentno`, `subjectno`, `studentresult`
FROM `result`
WHERE `subjectno` = (
	SELECT `subjectno`
	FROM `subject` 
	WHERE `subjectname` = '数据库结构-1'
)
ORDER BY `studentresult` DESC

-- 查询课程为 高等数学-2 且分数不小于 80 分的同学的学号和姓名  联表查询
SELECT s.`studentno`, `studentname`
FROM `student` s
INNER JOIN `result` r
ON s.studentno = r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno` = r.`subjectno`
WHERE sub.`subjectname` = '高等数学-2' AND r.`studentresult` >= 80





-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`studentno`, s.`studentname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80


-- 在这个基础上增加一个科目， 高等数学-2
SELECT DISTINCT s.`studentno`, s.`studentname`
FROM `student` AS s
INNER JOIN `result` AS r
ON s.`studentno` = r.`studentno`
WHERE r.`studentresult` >= 80 AND r.`subjectno` = (
	SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-2'
)


-- 继续改造   嵌套  （由里及外）
SELECT `studentno`, `studentname` FROM `student` WHERE `studentno` IN (
	SELECT `studentno` FROM `result` WHERE `studentresult` >= 80 AND `subjectno` = (
		SELECT `subjectno` FROM `subject` WHERE `subjectname` = '高等数学-2'
	)
)

-- 练习：查询 C语言-1 前5名同学的成绩的信息  （学号，姓名，分数）
-- 使用子查询
SELECT s.`studentno`, `studentname`, `studentresult`
FROM `student` s
INNER JOIN `result` r
ON s.`studentno` = r.`studentno`
WHERE r.`subjectno` IN (
	SELECT `subjectno` FROM `subject` WHERE `subjectname` = 'C语言-1'
)
ORDER BY studentresult DESC
LIMIT 0, 5

4.7、分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于 80
-- 核心：（根据不同的课程分组）
SELECT `subjectname`, AVG(`studentresult`) AS 平均分, MAX(`studentresult`) AS 最高分, MIN(`studentresult`) AS 最低分
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno` -- 通过什么字段来分组
HAVING 平均分 > 80

group by 根据字段进行分组，此时不能使用 where ，要使用 having 进行查询

4.8、select 小节

5、MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

5.1、常用函数

-- ==================== 常用函数 ==========================
-- 数学运算
SELECT ABS(-8) -- 绝对值
SELECT CEILING(9.4) -- 向上取整
SELECT FLOOR(9.4) -- 向下取整
SELECT RAND() -- 返回一个 0~1 之间的随机数
SELECT SIGN(10) -- 判断一个数的符号  0-0  负数返回 -1，正数返回 1


-- 字符串函数
SELECT CHAR_LENGTH('天天快乐') -- 字符串长度
SELECT CONCAT('我', '爱', '你们') -- 拼接字符串
SELECT INSERT('我爱helloword',1,2,'超级爱') -- 查询，从某个位置开始替换某个长度
SELECT LOWER('WuWUWU') -- 小写字母
SELECT UPPER('awfaw') -- 大写字母
SELECT INSTR('helloword', 'o') -- 返回第一次出现的字串的位置
SELECT REPLACE('坚持就是胜利','坚持','努力') -- 替换出现的指定字符串
SELECT SUBSTR('坚持就是胜利',4,6) -- 返回指定的子字符串（源字符串，截取的位置，截取的长度）
SELECT REVERSE('坚持就是胜利') -- 反转

-- 查询姓 罗 的同学，改为姓 周
SELECT REPLACE(`studentname`,'罗', '周') FROM `student` WHERE `studentname` LIKE '罗%'

-- 时间和日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE() -- 系统时间

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统
SELECT SYSTEM_USER()
SELECT USER()
SELECT VERSION()

5.2、聚合函数（常用）

-- ============== 聚合函数 ====================
-- 都能够统计表中的数据 （想查询一个表中有多少个记录，就会使用这个count()）
SELECT COUNT(`borndate`) FROM `student` -- COUNT(字段)，会忽略所有 null 值
SELECT COUNT(*) FROM `student` -- COUNT(*)，不会忽略 null 值，本质计算行数
SELECT COUNT(1) FROM result -- COUNT(1)，不会忽略所有的 null 值 本质 计算行数

SELECT SUM(`studentresult`) AS 总和 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MAX(`studentresult`) AS 最高分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result

5.3、数据库级别的MD5加密（扩展）

什么是 MD5 ？

主要增强算法复杂度和不可逆性。

MD5 不可逆，具体的值的 md5 是一样

MD5 破解网站的原理，背后都有一个字典，MD5 加密后的值，加密前的值

-- ======== 测试 MD5 加密 =============
CREATE TABLE `testmd5`(
    `id` INT(4) NOT NULL,
    `name` VARCHAR(20) NOT NULL,
    `pwd` VARCHAR(50) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 铭文密码
INSERT INTO testmd5 VALUES(1, 'zhangsan', '123456'),(2, 'lisi', '123456'),(3, 'wangwu', '123456')

-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id = 1

UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部的密码

-- 插入的时候加密
INSERT INTO testmd5 VALUES(4, 'xiaoming', MD5(123456))

-- 如何校验：将用户穿进来的密码，进行 md5 加密，然后对比加密后的值

SELECT * FROM `testmd5` WHERE `name` = 'xiaoming' AND pwd = MD5('123456')

6、事务

6.1、什么是事务

要么都成功，要么都失败

一一一一一一一

1、SQL 执行 A 给 B 转账 A 1000 ----> 200 B 200

2、SQL 执行 B 收到 A 的钱 A 800 ----> B 400

一一一一一一一

将一组SQL放在一个批次中去执行

事务原则：ACID 原则 原子性，一致性，隔离性，持久性 （脏读，幻读……）

参考原文链接：https://blog.csdn.net/dengjili/article/details/82468576

原子性（Atomicity）

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

一致性（Consistency）

事务前后数据的完整性必须保持一致。

隔离性（Isolation）

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

持久性（Durability） – 事务提交

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

执行事务

-- =============== 事务 =====================

-- mysql 是默认开启事务自动提交的
SET autocommit = 0   -- 关闭提交
SET autocommit = 1   -- 开启提交（默认的）

-- 手动处理事务
SET autocommit = 0  -- 关闭自动提交

-- 事务开启
START TRANSACTION  -- 标记一个事务的开始，从这个之后的 sql 都在同一事务内

INSERT xx
INSERT xx

-- 提交：持久化（成功！）
COMMIT

-- 回滚：回到原来的样子（失败！）
ROLLBACK

-- 事务结束
SET autocommit = 1  -- 开启自动提交

-- 了解
SAVEPOINT 保存点名  -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT  -- 回滚到保存点
RELEASE SAVEPOINT 保存点名  -- 撤销保存点

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
    `id` INT(3) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(30) NOT NULL,
    `money` DECIMAL(9, 2) NOT NULL,
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`, `money`)
VALUES('A', 2000.00),('B', 10000.00)

-- 模拟转账：事务
SET aautocommit = 0;  -- 关闭自动提交
START TRANSACTION  -- 开启一个事务（一组事务）

UPDATE account SET money = money - 500 WHERE `name` = 'A' -- A 减500
UPDATE account SET money = money + 500 WHERE `name` = 'B' -- B 加500

COMMIT; -- 提交事务，就被持久化了！
ROLLBACK; -- 回滚

SET autocommit = 1; -- 恢复默认值