• MySQL数据管理
• DML(数据库管理语言 重点)
• DQL(数据库查询语言 使用频率最高)
• • where条件子句
  • 联表查询
  • 分页/排序/子查询
  • MySQL常用函数
  • 聚合函数（常用）
  • 数据库级别的MD5加密
  • where和having的区别

MySQL数据管理

外键(了解)
首先先创建一个年级表，其中有一个字段grade_id作为外键约束字段：

-- 创建一个年级表
CREATE TABLE IF NOT EXISTS `grade`(
	`grade_id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级id',
	`grade_name` VARCHAR(30) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`grade_id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

方式一：在创建表时增加约束(缺点：复杂)

-- 创建一个学生表，学生表的grade_id字段引用年级表的grade_id
-- 定义外键key，给这个外键添加约束(执行引用[references])
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) DEFAULT '张三' COMMENT '姓名',
	`age` INT(11) COMMENT '年龄',
	`sex` CHAR(2) DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`grade_id` INT(11) NOT NULL COMMENT '年级id',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
	PRIMARY KEY (`id`),
	KEY `FK_grade_id` (`grade_id`),
	CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade` (`grade_id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

要删除有外键关系的表时，必须要先删除引用外键的表(从表)，再删除被引用的表(主表)。

方法二：创建表之后添加外键约束

-- 创建表时没有指定外键关系
CREATE TABLE IF NOT EXISTS `student`(
	`id` INT(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
	`name` VARCHAR(20) DEFAULT '张三' COMMENT '姓名',
	`age` INT(11) COMMENT '年龄',
	`sex` CHAR(2) DEFAULT '男' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '生日',
	`grade_id` INT(11) NOT NULL COMMENT '年级id',
	`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
	PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 使用修改语句添加外键关系
ALTER TABLE `student` ADD CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`)
-- 以上语法为：
-- ALTER TABLE 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的字段名) REFERENCES 关联外键的表名(外键约束字段名)

-- 删除外键	ALTER TABLE 表名 DROP FOREIGN KEY 外键名字
ALTER TABLE `student` DROP FOREIGN KEY `FK_grade_id`

以上的操作都是物理外键，数据库级别的外键不建议使用(避免数据过多造成困扰~)

最佳实践：

1. 数据库就是单纯的表，只用来存数据，只有行(数据)和列(字段)；
2. 要想使用外键(一个地方引用另一个地方时)，尽量使用程序实现

DML(数据库管理语言 重点)

1. 添加(insert)

-- 插入语句(添加)
-- insert into 表名 (字段名1, 字段名2, 字段名3, ...) values ('值1', '值2', '值3', ...)
-- 如果设置了主键自增，则可以忽略主键字段
INSERT INTO `grade` (`grade_name`) VALUES ('一年级1班')

-- 如果不写values前面的对应的字段名，它就会对字段进行一一匹配
INSERT INTO `grade` VALUES (NULL,'一年级2班') -- 设置NULL是为了使主键自增
INSERT INTO `student` VALUES (NULL, '张三', 22, '男', '2000-01-01', 1, '山东济南')

-- 插入多个字段(中间使用逗号隔开)
INSERT INTO `grade` (`grade_name`) VALUES ('一年级3班'), ('一年级4班')

注意事项：
字段和字段之间使用英文逗号隔开；
字段名可省略，但values后面的值必须要一一对应；
可同时插入多条数据，在values后面，每条数据使用括号括起来，使用逗号隔开

2. 修改(update)

-- 语法：UPDATE 表名 SET 字段名 = 新值 [WHERE=条件]
-- 不指定条件的情况下，会改动所有表(慎重~~~)
UPDATE `student` SET `name` = '王五' WHERE `name` = '张三'

-- 修改多个属性，逗号隔开
UPDATE `student` SET `age` = 20, `grade_id` = 2 WHERE `name` = '王五'

条件：where子句，运算符

操作符	含义	举例	结果
=	等于	5=6	false
<> 或 !	不等于	5<>6	true
>	大于	2>1	true
<	小于	2<3	true
<=	小于等于	2<=2	true
>=	大于等于	4>=5	false
BETWEEN…AND…	在某个范围内	[2,5]
AND	&&	2>1and3>4	false
OR	||	2>1and3>4	true

CURRENT_TIME	-- 函数：表示当前时间
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '王五'

3. 删除(delete & truncate)

delete命令

-- 语法
DELETE FROM 表名 [WHERE 条件]
-- 删除数据
DELETE FROM `student`	-- 清空student表（不建议使用）

-- 删除指定行
DELETE FROM `student` WHERE id = 1

truncate命令 作用：完全清空一个数据库表，表的结构和索引约束不变

-- 清空student表
TRUNCATE TABLE `student`

delete & truncate 的异同
同：都能删除数据，都不会删除表结构
异：truncate可以重新设置自增列，计时器会归零，且truncate不会影响事务；delete不会影响自增(会在原来的基础上自增)，计时器不会归零，且会影响事务

了解DELETE删除的问题，用delete执行完清空表中的数据操纵后，重启数据库，现象：

1. 如果是INNODB引擎，则自增列会重新从1开始(原因：INNODB引擎的数据库存在内存中，断电即失)
2. 如果是MyISAM引擎，则继续从上一个自增量开始(原因：MyISAM引擎的数据库存在文件中，不会丢失)

DQL(数据库查询语言 使用频率最高)

-- 起别名 AS
-- 函数 CONCAT(a, b, ...)
SELECT CONCAT('姓名','：',`name`) AS `name` FROM `student`

去重(去掉重复数据) distinct
作用：取出查询出来的重复数据，重复的数据只显示一条

SELECT DISTINCT `name` FROM `student`

数据库的列(表达式)

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

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

where条件子句

作用：检索数据中符合条件的值。
搜索的条件有一个或多个表达式组成，表达式之间用and连接，结果为布尔值。

逻辑运算符

运算符	语法	描述
and（&&）	a and b（a && b）	逻辑与，表达式两边都为真结果即真
or（||）	a or b（a || b）	逻辑或，表达式其中一个为真，结果即真
not（!）	not a（!a）	逻辑非，真为假，假为真

-- 模糊查询（区间）
SELECT * FROM `student` WHERE `age` BETWEEN 16 AND 20	--查询年龄16-20的学生

模糊查询：比较运算符

运算符	语法	描述
IS NULL	a IS NULL	如果操作符为NULL，结果为真
IS NOT NULL	a IS NOT NULL	如果操作符不为NULL，结果为真
BETWEEN	a BETWEEN b AND c	如果a在b和c之间，则结果为真
LIKE	a LIKE b	SQL匹配，如果a匹配b，则结果为真
IN	a IN(a1, a2, a3, …)	假设a在a1,a2,a3其中某一个值中，结果为真

like结合：

1. % 代表0到任意个字符
2. _ 匹配一个字符

联表查询

七种join理论：

操作	描述
inner join	查询两表的并集，不查询包含null值的数据
left join	从左表中返回所有值，即使右表中没有匹配
right join	从由表中返回所有值，即使左表中没有匹配

-- join （连接的表） on（判断的条件）	连接查询
-- where	等值查询
/*
	联表查询思路：
		1.分析需求，分析查询的字段来自哪几张表
		2.确定使用哪种连接查询
		3.确定交叉点
*/

分页/排序/子查询

SELECT 语法

SELECT [ALL | DISTINCT]
{* | 表名.* | [表1.字段名1[as 别名1] [, 表1.字段名2 [as 别名2]] [, ...]]}
FROM 表1 [as 别名1]
	[left | right | inner join 表2]	-- 联合查询
	[WHERE ...]	--指定条件
	[GROUP BY ...]	-- 指定结果按照哪个字段分组
	[HAVING] -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];	--指定查询的记录从哪条到哪条

注意：[]括号代表可选条件，{}括号代表必选条件。

排序

-- 排序	升序：ASC	降序：DESC
-- ORDER BY 通过哪个字段排序 ASC | DESC

分页
为什么要分页？缓解数据库压力，给用户更好的体验。

-- LIMIT 起始页,页面显示记录条数	第一页从0开始
-- 第一页：LIMIT 0,5
-- 第二页：LIMIT 5,5
-- 第三页：LIMIT 10,5
-- 第N页：LIMIT (n-1) * pageSize, pageSize
-- [pageSize：页面大小]
-- [n：当前页]
-- [总页数 = 数据总记录数 / 页面显示记录条数]
-- 语法：LIMIT (查询起始下标, pageSize)

子查询/嵌套查询(由里及外)：在where语句中嵌套一个子查询语句

-- 查询王五的学生信息（姓名，班级，住址）
SELECT
	s.`name`,
	g.grade_name,
	s.address 
FROM
	student s
	INNER JOIN grade g ON s.grade_id = g.grade_id 
WHERE
	s.`name` = (
	SELECT
		s.`name` 
	FROM
		student s 
WHERE
	s.`name` = '王五'
	)
-- 其中，where中条件的值是计算出来的用子查询更好一些，以上查询没必要用子查询，直接用下面的方法即可查询到想要的学生信息
SELECT
	s.`name`,
	g.grade_name,
	s.address 
FROM
	student s
	INNER JOIN grade g ON s.grade_id = g.grade_id 
WHERE
	s.`name` = '王五'

-- 由于子查询还需要再查一遍数据库，如果数据量较大则会导致查询较慢，工作中应尽量避免使用到子查询(需要用到时也不能吝啬)

MySQL常用函数

对应MySQL官网：https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html

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

-- 字符串函数
SELECT CHAR_LENGTH('获取字符串长度') char_length	--字符串长度	结果：7
SELECT CONCAT('hello', 'world') str	-- 拼接字符串	结果：helloworld
SELECT INSERT('好好学习天天向上', 1,2,'热爱')	--插入，从某个位置(第一个字符的下标从1开始)替换指定长度	结果：热爱学习天天向上
SELECT UPPER('mysql')	-- 字母全部转大写	结果：MYSQL
SELECT LOWER('MySQL')	-- 字母全部转小写	结果：mysql
SELECT INSTR('hello','o')	-- 返回第一次出现的子串的索引	结果：5
SELECT REPLACE('好好学习天天向上','好好','天天')	-- 替换出现的指定字符串	结果：天天学习天天向上
SELECT SUBSTR('好好学习天天向上', 1,4)	-- 截取指定长度的字符串（源字符串，截取位置，截取的长度）如果不指定截取的长度则截取到最后	结果：好好学习
SELECT REVERSE('好好学习天天向上')	-- 反转	结果：上向天天习学好好

-- 时间和日期函数
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()	-- 获取当前系统用户：root@localhost
SELECT USER()	-- 获取当前用户：root@localhost
SELECT VERSION()	-- 获取当前版本：8.0.25

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值
…	…

-- 统计表*有多少记录数
SELECT COUNT('grade_name') FROM grade	-- COUNT(列名)，会忽略字段对应列中的所有null值
SELECT COUNT(*) FROM grade	-- COUNT(*)，不会忽略null值，本质：计算行数
SELECT COUNT(1) FROM grade	-- COUNT(1)，不会忽略null值，本质：计算行数
/*
	效率上：
		列名为主键，COUNT(列名)比COUNT(1)快
		列名不为主键，COUNT(1)比COUNT(列名)快
		如果表没有主键，则COUNT(1)比COUNT(*)快
		如果有主键，则SELECT COUNT(主键)执行效率最优
		如果表只有一个字段，则SELECT COUNT(*)最优
*/

数据库级别的MD5加密

MD5：增强算法复杂度和不可逆性，对数据进行加密

MD5破解网址(破解简单密码)：https://www.cmd5.com/

-- 新建一个测试表，用于测试MD5加密和解密
CREATE TABLE IF NOT EXISTS `testmd5`(
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`pass` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

-- 添加数据
INSERT INTO testmd5 VALUES (NULL, '张三', '123'),(NULL, '李四', '123'),(NULL, '王五', '123')

-- 修改密码为加密密文
UPDATE testmd5 SET pass = MD5(pass) WHERE id = 1

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

-- 插入时加密
INSERT INTO testmd5 VALUES (NULL, '赵六', MD5('123'))

-- 校验：将用户传递进来的密码进行md5加密，比对加密后的值
SELECT * FROM testmd5 WHERE `name` = '张三' AND pass = MD5('123')

where和having的区别

where:

1. where是一个约束声明,使用where来约束来自数据库的数据;
2. where是在结果返回之前起作用的;
3. where中不能使用聚合函数。

having:

1. having是一个过滤声明;
2. 在查询返回结果集以后，对查询结果进行的过滤操作;
3. 在having中可以使用聚合函数。