MySQL基础

1 数据库1.1 什么是数据库1.2 数据库分类1.3 MySQL简介2 操作数据库2.1 数据库基本操作2.2 数据库的数据类型2.3 数据库的字段属性2.4 创建数据库表2.5 数据库引擎2.6 修改和删除表2.7 外键3 DML 数据操纵语言3.1 添加3.2 修改3.3 删除4 DQL 数据查询语言4.1 基本查询及别名使用4.2 去重查询4.3 WHERE 条件4.4 模糊查询4.5 联表查询4.6 自连接4.7 分页与排序4.8 子查询(嵌套查询)4.9 分组查询 (过滤)5 MySQL函数5.1 常用函数5.2 聚合函数5.3 数据库级MD5加密6 事务6.1 什么是事务6.2 开启事务7 索引7.1 索引的分类7.2 测试索引7.3 索引原则8 权限管理和备份8.1 用户管理8.2 数据库备份9 数据库设计规范9.1 三大范式

1 数据库

业务级MySQL：事务 索引...

运维级MySQL：底层，架构原理，MySQL集群，负载均衡...

1.1 什么是数据库

数据库（DB ，DataBase）

作用：储存数据，管理数据

DBMS：数据库管理系统

• 数据库的管理软件

1.2 数据库分类

关系型数据库：（SQL）

• MySQL, Oracle, Sql Server, DB2, SQLite
• 通过表和表之间，列和列之间的关系数据进行存储

非关系型数据库：（NoSQL Not Only SQL）

• Redis, MongDB
• 以对象存储，通过对象的属性来决定

1.3 MySQL简介

• MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，属于 Oracle旗下产品
• 体积小、速度快、总体拥有成本低
• MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一

2 操作数据库

2.1 数据库基本操作

创建数据库

1
CREATE DATABASE [IF NOT EXISTS] test; 

IF NOT EXISTS 不存在时创建

删除数据库

xxxxxxxxxx
1
1
DROP DATABASE [IF EXISTS] test;

IF EXISTS存在时删除

使用数据库

xxxxxxxxxx
1
1
USE test

当数据库中的字段名是关键字时需要加``括起来

查看数据库

xxxxxxxxxx
1
1
SHOW DATABASES 

查看所有数据库

2.2 数据库的数据类型

数据库的列的数据类型

数值

• tinyint 1个字节
• smallint 2个字节
• mediumint 3个字节
• int 4个字节
• bigint 8个字节
• float 4个字节
• double 8个字节
• decimal 字符串类型的浮点

注：int类型的长度不是保存数据的长度，就算定义int(1)还是能存储到2^32，int(n),长度n表示最大显示宽度，显示宽度与存储大小无关，与zerofull的填充长度有关（与vaechar不同）

在INT(3)的情况下插入100，10，0，结果为：100，010，001

字符串

• char 字符串固定大小 0~255
• varchar 可变字符串 0~65535 String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1

时间和日期

• date YYYY-MM-DD 日期格式
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 时间
• timestamp 时间戳 1970.1.1到现在的毫秒 全球统一
• year 年份

null

• 没有值
• 不要使用null进行运算

2.3 数据库的字段属性

数据库的列所携带的属性

Unsigned

• 无符号整型
• 声明后该列不能为负数

zerofill

• 0填充的
• 不足的位数用0来填充
• 定义int(3)， value = 5 ，005

自增

• 自动在上一条记录基础上加1
• 通常用来设计唯一的主键，必须为整数类型
• 可以自定义主键自增的起始值和步长

非空 NULL not null

• 设置为非空不赋值就会报错

默认值 default

• 设置默认的值

在项目中推荐必须携带的字段

xxxxxxxxxx
5
1
id         主键
2
`version`  乐观锁
3
is_delete  伪删除
4
gmt_creat  创建时间
5
gmt_update 修改时间

2.4 创建数据库表

DDL 数据定义语言

creat

xxxxxxxxxx
13
1
-- 表名称和字段名使用``括起来 防止与关键字重名
2
-- 字符串使用''
3
-- PRIMARY KEY主键 建议写在后面 方便查看
4
CREATE TABLE IF NOT EXISTS `student`(
5
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
6
    `name` VARCHAR(30) NOT NULL DEFAULT '游客' COMMENT '姓名',
7
    `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
8
    `sex` VARCHAR(2) NOT NULL COMMENT '性别',
9
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
10
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
11
    `address` VARCHAR(256) DEFAULT NULL COMMENT '家庭住址',
12
    PRIMARY KEY(`id`)
13
)ENGINE=INNODB DEFAULT CHARSET=utf8;

格式：

xxxxxxxxxx
5
1
CREATE TABLE [IF NOT EXISTS] `表名`(
2
    `字段名` 列类型 [属性] [索引] [注释]，
3
    `字段名` 列类型 [属性] [索引] [注释]，
4
...
5
)[表类型] [字符集] [注释];

xxxxxxxxxx
3
1
SHOW CREATE TABLE test; -- 查看创建数据库的语句
2
SHOW CREATE TABLE student;-- 查看创建数据表的语句
3
DESC student;-- 查看表的结构

2.5 数据库引擎

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大 2倍

常规使用操作：

• MYISAM 节约空间，速度快
• INNODB 安全性高，事务处理，多表多用户操作

存储：本质文件存储

设置数据库表的字符集编码

MySQL的默认的编码设置为Latin1，不支持中文

1. 在创表的时候修改编码

xxxxxxxxxx
1
1
CHARSET=utf8

2. 在my.ini中配置默认的编码

xxxxxxxxxx
1
1
character-set-server=utf8

建议每次建表都加上编码，修改本地配置会与其他的服务端配置不同步，可能导致同样的建表不同的效果

2.6 修改和删除表

修改

xxxxxxxxxx
12
1
-- 修改表名
2
ALTER TABLE `student` RENAME AS `student1`;
3
-- 增加新字段
4
ALTER TABLE `student1` ADD age INT(3);
5
-- 修改字段属性 
6
-- MODIFY可以修改所有属性 不能修改字段名
7
ALTER TABLE `student1` MODIFY age VARCHAR(3);
8
-- 修改字段名 
9
-- CHANGE只能修改部分属性 可以修改字段名 一般用来改名
10
ALTER TABLE `student1` CHANGE age age1;
11
-- 删除字段
12
ALTER TABLE `student1` DROP age1;

删除

xxxxxxxxxx
2
1
-- 删除表
2
DROP TABLE IF EXISTS `student1`

所有的删除操作尽量加上判断

2.7 外键

外键的作用相当于约束，在表1中grade数据必须和表2存在的id数据匹配，否则无法提交修改

1 在创建表的时候添加外键

创建年级表

xxxxxxxxxx
5
1
CREATE TABLE `grade`(
2
    `grade_id` INT(10) NOT NULL AUTO_INCREMENT,
3
    `grade_name` VARCHAR(50) NOT NULL,
4
    PRIMARY KEY(`grade_id`)
5
)ENGINE=INNODB DEFAULT CHARSET=utf8;

创建学生表

xxxxxxxxxx
17
1
-- 学生表的grade_id字段引用年级表的grade_id
2
-- 定义外键
3
-- 给外键添加约束 
4
-- 约束 KEY 外键 (字段) 引用 引用的表 (字段)
5
CREATE TABLE IF NOT EXISTS `student`(
6
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
7
    `name` VARCHAR(30) NOT NULL DEFAULT '游客' COMMENT '姓名',
8
    `password` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
9
    `sex` VARCHAR(2) NOT NULL COMMENT '性别',
10
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
11
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
12
    `address` VARCHAR(256) DEFAULT NULL COMMENT '家庭住址',
13
    `grade_id` INT(10) NOT NULL,
14
    PRIMARY KEY(`id`),
15
    KEY `FK_grade_id` (`grade_id`),
16
    CONSTRAINT `FK_grade_id` FOREIGN KEY (`grade_id`) REFERENCES `grade`(`grade_id`)
17
)ENGINE=INNODB DEFAULT CHARSET=utf8; 

查看表可以看到外键

存在被引用关系的表不能直接删除 需要先删除引用表

直接在建表时添加外键容易出现依赖性问题，版本兼容性差，可以使用ALTER语句进行添加约束

2 ALTER添加外键

xxxxxxxxxx
2
1
ALTER TABLE `student1` 
2
ADD CONSTRAINT `FK_grade_id` FOREIGN KEY(`grade_id`) REFERENCES `grade`(`grade_id`);

以上为数据库级别的外键，不建议使用，数据库表多之后引用复杂兼容性差

推荐使用：在程序中实现外键

3 DML 数据操纵语言

DML语言：数据操作语言

• insert 添加
• update 更新
• delete 删除

3.1 添加

INSERT 语句

基本插入：

xxxxxxxxxx
4
1
-- 插入语句 INSERT
2
-- 语法：INSERT INTO 表名（字段1,字段2，字段3，...）VALUES (值1，值2，值3，...);
3
INSERT INTO `grade` (`grade_name`) VALUES ('大一');
4
-- 不写表的字段名就会默认顺序插入

多行插入：

xxxxxxxxxx
8
1
-- 语法：INSERT INTO 表名（字段1,字段2，字段3，...）VALUES 
2
-- (值1，值2，值3，...),   --一行
3
-- (值1，值2，值3，...),   --二行
4
-- ......;
5
INSERT INTO `grade` (`grade_name`) VALUE 
6
('大二'),
7
('大三'),
8
('大四');

插入结果：

3.2 修改

UPDATE 语句

xxxxxxxxxx
7
1
-- 修改语句 UPDATE
2
-- 语法：UPDATE 表名 SET 字段名=修改的值 WHERE [VALUE];
3
-- WHERE选择一列 一般用唯一的主键来查找列
4
-- 不加WHERE进行选择默认修改所有列的值
5
UPDATE `student1` SET `name`='小明' WHERE id=1;
6
-- 修改多个字段
7
UPDATE `student1` SET `name`='小明',`email`='12312321@qq.com' WHERE id=1;

WHERE 子句

where 是筛选的条件

操作符	含义	语句	结果
=	等于	id=6	操作第6行
<>或!=	不等于	id<>6	操作除了第6行
>	大于	id>6	操作7~infinite行
<	小于	id<6	操作1~5行
>=	大于等于	id>=6	操作6~infinite行
<=	小于等于	id<=6	操作1~6行
BETWEEN...AND...	在...之间	id BETWEEN 2 AND 6	操作2~6行
AND	与	id<=6 AND id>=2	操作2~6行
OR	或	name='小明' or name='张三'	操作名字是小明和张三的行
NOT	非	NOT id = 6	操作除了第6行

3.3 删除

DELETE 语句

xxxxxxxxxx
6
1
-- 删除语句 DELETE
2
-- 语法：DELETE FROM 表名 WHERE [VALUE];
3
DELETE  FROM `student1` WHERE id=1;
4

5
-- 必须加上判断，否则会删除全部数据
6
DELETE  FROM `student1`

TRUNCATE 语句

作用：完全清空一个数据库表，表结构，索引不改变

xxxxxxxxxx
2
1
-- 清空
2
TRUNCATE `student1`;

DELETE 和 TRUNCATE 的区别

• 相同点：都能删除数据，不会改变表结构

• 不同：

  • TRUNCATE 会重新设置自增列的计数器
  • TRUNCATE 不会影响事务

DELETE删除问题：

重启数据库之后：

• InnoDB 自增从1开始（数据存储在内存）
• MyISAM 继续上一个自增量 （数据存储在文件，后备存储）

4 DQL 数据查询语言

DQL 数据查询语言

• 所有的查询操作都使用
• 简单查询，复杂查询都能完成
• 数据库最核心的语言，使用频率最高的语言

4.1 基本查询及别名使用

SELECT 语句

SELECT语法

xxxxxxxxxx
10
1
select [all | distinct]-- 全部，去重 
2
<select_expr>, <select_expr>, ...-- 列名
3
from <table_name1>[as table_alias] -- 表名
4
[left|right|inner join table_name2 ON <on_condition>] -- 连接查询
5
[where <where_condition>]-- 查询条件
6
[group by <col_list>] -- 按字段分组
7
[having <having_condition>] -- 过滤分组的记录需要满足的次要条件
8
[order by <order_condition>] -- 按条件排序
9
[distribute by <distribute_condition> [sort by <sort_condition>] ]
10
[limit <number>] -- 指定查询记录从哪条到哪条 分页

 

xxxxxxxxxx
9
1
-- 查询表的全部信息 
2
-- SELECT * FROM 表名
3
SELECT * FROM `student`;
4

5
-- 查询一张表指定字段信息
6
SELECT `studentname`,`sex` FROM student;
7

8
-- 给表字段名赋予别名 也可以给表起别名
9
SELECT `studentname` AS 学生姓名 ,`sex` AS 性别 FROM student;

xxxxxxxxxx
2
1
-- 字符串拼接函数 Concat(a,b)
2
SELECT CONCAT('姓名:',`studentname`) AS 名字输出 FROM `student`;

4.2 去重查询

DISTINCT 关键字

去除SELECT查询出的重复数据

xxxxxxxxxx
6
1
-- 去重查询
2
SELECT * FROM `result`; -- 查询所有的成绩
3
SELECT `studentno` FROM `result` -- 查询参加考试的同学
4
-- 一个学生会参加多门考试
5
-- DISTINCT 去除成绩表中查询出的重复的学号
6
SELECT DISTINCT `studentno` FROM `result`;

4.3 WHERE 条件

检索数据库中符合条件的行

跳转到之前WHERE 的用法

简单使用：

xxxxxxxxxx
11
1
-- 查询分数在95和100之间的学生和科目
2
-- AND
3
SELECT `studentno`,`subjectno`,`studentresult` FROM `result` WHERE `studentresult` >= 95 AND `studentresult` <= 100;
4
-- BETWEEN
5
SELECT `studentno`,`subjectno`,`studentresult` FROM `result` WHERE `studentresult` BETWEEN 95 AND 100;
6

7
-- 查询除了科目4外的学生科目成绩
8
-- !=
9
SELECT `studentno`,`subjectno`,`studentresult` FROM `result` WHERE `subjectno` != 4;
10
-- NOT
11
SELECT `studentno`,`subjectno`,`studentresult` FROM `result` WHERE NOT `subjectno` = 4;

4.4 模糊查询

LIKE 运算符

SQL匹配，如果a 匹配 b 结果为真

xxxxxxxxxx
11
1
-- 查询姓张的同学
2
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张%';
3

4
-- 查询姓张的同学 且姓后面只有一个字
5
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张_';
6

7
-- 查询姓张的同学 且姓后面只有一两个字
8
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '张__';
9

10
-- 查询名字当中有"强"字的同学
11
SELECT `studentno`,`studentname` FROM `student` WHERE `studentname` LIKE '%强%';

• %代表有任意个字符
• _代表一个字符

IN 运算符

xxxxxxxxxx
2
1
-- 查询学生的ID为1001，1002，1003，1004
2
SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN (1001,1002,1003,1004);

IS NULL 和 IS NOT NULL

条件：是否为空

xxxxxxxxxx
5
1
-- 查询身份证号为空的学生
2
SELECT `studentno`,`studentname` FROM `student` WHERE `identitycard` IS NULL;
3

4
-- 查询身份证号为空的学生
5
SELECT `studentno`,`studentname` FROM `student` WHERE `identitycard` IS NOT NULL;

4.5 联表查询

JOIN ON

七种JOIN：

​x
1
-- 查询参加了考试的同学(学号，姓名，科目号，分数)
2
-- 一张表不能查询到所有的信息，需要用到学生表和成绩表
3
-- 学号是两张表中都有的元素
4

5
-- INNER JOIN 
6
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
7
FROM `student` AS s
8
INNER JOIN `result` AS r
9
WHERE s.`studentno` = r.`studentno`;
10
-- WHERE 等值查询
11
-- JOIN ON 连接查询
12

13
-- RIGHT JOIN 
14
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
15
FROM `student` AS s
16
RIGHT JOIN `result` AS r
17
ON s.`studentno` = r.`studentno`;
18
-- result表中所有studentno都会返回如果student表没有这个studentno的列，返回的result表中有而student表没有的studentno列中student表中的字段值为NULL
19
-- 注意：SELECT后面的s.`studentno`代表`student`表中的学生ID，此时存在result表中有而student表没有的studentno列时，s.`studentno`的值为NULL
20

21
-- LEFT JOIN 
22
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
23
FROM `student` AS s
24
LEFT JOIN `result` AS r
25
ON s.`studentno` = r.`studentno`;

INNER JOIN ：

RIGHT JOIN：

LEFT JOIN ：

操作	描述
INNER JOIN	只返回两个表中联结字段相等的行
RIGHT JOIN	返回包括右表中的所有记录和左表中联结字段相等的记录
LEFT JOIN	返回包括左表中的所有记录和右表中联结字段相等的记录

简单理解：

• INNER JOIN:

  • 返回的值是两张表中都有的
  • 例如：a表中有key = 1,2,3 b表中只有key = 1,2 ,此时结果只会返回key = 1,2;

• RIGHT JOIN

  • 返回的值是两张表中都有的与b(右)表中所有的
  • 例如：a表中有key = 1,2,4 b表中有key = 1,2,3 ,此时会返回key = 1,2,3;

• LEFT JOIN

  • 返回的值是两张表中都有的与a(右)表中所有的
  • 例如：a表中有key = 1,2,3,4 b表中有key = 1,2,3,5 ,此时会返回key = 1,2,3,4;

• RIGHT JOIN和LEFT JOIN 的返回值可能会存在a或b表中没有的数据，返回的表就会把这些字段的值设为NULL

通过这个返回值为NULL的情况又可以衍生出几种JOIN的模式如上图

应用：

xxxxxxxxxx
9
1
-- 查询缺考学生（全部科目缺考）
2
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
3
FROM `student` AS s
4
LEFT JOIN `result` AS r
5
ON s.`studentno` = r.`studentno`
6
WHERE r.`studentno` IS NULL;
7
-- 参考七种JOIN图
8
-- 返回值为只有左表（`student`）中存在的值
9
-- 右表（`result`）不存在，说明没有考试成绩

三张表联表查询：

xxxxxxxxxx
6
1
SELECT s.`studentno` AS '学号',`studentname` AS '学生姓名',`subjectname` AS '科目名称',`studentresult` AS '考试成绩'
2
FROM `student` AS s
3
INNER JOIN `result` AS r
4
ON s.`studentno`=r.`studentno`
5
INNER JOIN `subject` AS sub
6
ON sub.`subjectno`=r.`subjectno`

找到每张表中共同存在的字段就可以关联

4.6 自连接

相当于一张表自己跟自己进行联表查询

现有一张表：

这里PID是父类的ID，PID为1就是父栏位，子栏位的PID等于父栏位categoryid

xxxxxxxxxx
10
1
-- 联表查询JOIN ON连接查询
2
SELECT a.`categoryname` AS '父栏位',b.`categoryname` AS '子栏位'
3
FROM `category` AS a
4
INNER JOIN `category` AS b
5
ON a.`categoryid`=b.`pid`;
6

7
-- 也可以使用WHERE等值查询
8
SELECT a.`categoryname` AS '父栏位',b.`categoryname` AS '子栏位'
9
FROM `category` AS a,`category` AS b
10
WHERE a.`categoryid`=b.`pid`;

查询结果如下

本章需要先理解联表查询

4.7 分页与排序

ORDER BY 排序

xxxxxxxxxx
10
1
-- 排序 升序ASC 降序DESC
2
-- 根据成绩进行排序
3
SELECT s.`studentno` AS '学号',`studentname` AS '学生姓名',`subjectname` AS '科目名称',`studentresult` AS '考试成绩'
4
FROM `student` AS s
5
INNER JOIN `result` AS r
6
ON s.`studentno`=r.`studentno`
7
INNER JOIN `subject` AS sub
8
ON sub.`subjectno`=r.`subjectno`
9
WHERE `subjectname`='高等数学-3'
10
ORDER BY `studentresult` ASC;

LIMIT 分页

xxxxxxxxxx
13
1
-- 分页
2
-- 分页的作用：缓解数据库压力 分页展示效果
3
-- 分页 每页五条数据
4
-- LIMIT n,m n:起始值 m:页面大小
5
SELECT s.`studentno` AS '学号',`studentname` AS '学生姓名',`subjectname` AS '科目名称',`studentresult` AS '考试成绩'
6
FROM `student` AS s
7
INNER JOIN `result` AS r
8
ON s.`studentno`=r.`studentno`
9
INNER JOIN `subject` AS sub
10
ON sub.`subjectno`=r.`subjectno`
11
LIMIT 0,5;
12
-- 公式: 第n页 每页m条 LIMIT (n-1)*m,m
13
--       总页数：数据总数/页面大小 = 总页数

4.8 子查询(嵌套查询)

在WHERE语句中嵌套一个子查询语句

xxxxxxxxxx
19
1
-- 查询 高等数学-3考试的所有考试成绩(字段：学号，科目，成绩)并降序排序
2
-- 方法一: 连接查询
3
SELECT r.`studentno` AS '学号',`subjectname` AS '科目名称',`studentresult` AS '考试成绩'
4
FROM `result` AS r
5
INNER JOIN `subject` AS sub
6
ON sub.`subjectno`=r.`subjectno`
7
WHERE `subjectname`='高等数学-3'
8
ORDER BY `studentresult` DESC;
9

10
-- 方法二: 子查询
11
SELECT `studentno` AS '学号',`subjectno` AS '科目编号',`studentresult` AS '考试成绩' -- result表中存在的字段
12
FROM `result`
13
-- 现在需要知道高等数学-3的科目编号
14
-- select `subjectno` from `subject` where `subjectname`='高等数学-3';
15
WHERE `subjectno`=(
16
    SELECT `subjectno` FROM `subject` 
17
    WHERE `subjectname`='高等数学-3'
18
)
19
ORDER BY `studentresult` DESC;

4.9 分组查询 (过滤)

GROUP BY 分组查询

xxxxxxxxxx
7
1
-- 查询不同课程的平均分，最高分，最低分
2
-- 根据科目分组
3
SELECT `subjectname` AS 科目名,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
4
FROM `result` r
5
INNER JOIN `subject` sub
6
ON r.`subjectno` = sub.`subjectno`
7
GROUP BY `subjectname`;

查询结果：

HAVING 分组过滤

xxxxxxxxxx
8
1
-- 查询不同课程的平均分，最高分，最低分 且过滤平均分大于70的科目
2
-- 根据科目分组 过滤平均分大于70
3
SELECT `subjectname` AS 科目名,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分
4
FROM `result` r
5
INNER JOIN `subject` sub
6
ON r.`subjectno` = sub.`subjectno`
7
GROUP BY `subjectname`
8
HAVING 平均分>=70;

查询结果：

5 MySQL函数

所有函数

5.1 常用函数

xxxxxxxxxx
34
1
-- 数学运算
2
SELECT ABS(-1); -- 绝对值
3
SELECT CEILING(10.1); -- 向上取整
4
SELECT FLOOR(10.1); -- 向下取整
5
SELECT RAND(); -- 0-1随机数
6
SELECT SIGN(-13); -- 判断符号
7

8
-- 字符串函数
9
SELECT CHAR_LENGTH('hello,world'); -- 字符串长度
10
SELECT CONCAT('hello','world'); -- 字符串拼接
11
SELECT INSERT('hello,hello',7,11,'world'); -- 插入，替换
12
SELECT LOWER('Hello,World'); -- 转小写
13
SELECT UPPER('hello,world'); -- 转大写
14
SELECT INSTR('hello,world','w'); -- 返回第一次出现子串的索引 
15
SELECT REPLACE('hello,ww','ww','world'); -- 替换指定字符串
16
SELECT SUBSTR('hello,world',1,5); -- 返回指定子字符串
17
SELECT REVERSE('dlrow,olleh'); -- 反转字符串
18

19
-- 时间日期
20
SELECT CURRENT_DATE(); -- 获取当前日期
21
SELECT NOW(); -- 获取当前时间
22
SELECT LOCALTIME(); -- 本地时间
23
SELECT SYSDATE(); -- 系统时间
24

25
SELECT YEAR(NOW());
26
SELECT MONTH(NOW());
27
SELECT DAY(NOW());
28
SELECT HOUR(NOW());
29
SELECT MINUTE(NOW());
30

31
-- 系统
32
SELECT SYSTEM_USER();
33
SELECT USER();
34
SELECT VERSION();

5.2 聚合函数

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

xxxxxxxxxx
20
1
-- 聚合函数
2
-- COUNT
3
SELECT COUNT(studentno) FROM student; -- COUNT(字段) 会忽略null值
4
SELECT COUNT(*) FROM student; -- COUNT(*) 不会忽略null值
5
SELECT COUNT(1) FROM student; -- COUNT(1) 不会忽略null值
6
-- 有主键，COUNT(主键)效率最高
7
-- 只有一个字段，COUNT(*)效率高
8
-- 有多个列，没有主键，COUNT(1)效率高
9

10
-- SUM
11
SELECT SUM(`studentresult`) AS 总分 FROM `result`; -- 求成绩总和
12

13
-- AVG
14
SELECT AVG(`studentresult`) AS 平均分 FROM `result`; -- 求平均分
15

16
-- MAX
17
SELECT MAX(`studentresult`) AS 平均分 FROM `result`; -- 求最高分
18

19
-- MIN
20
SELECT MIN(`studentresult`) AS 平均分 FROM `result`; -- 求最低分

5.3 数据库级MD5加密

MD5不可逆，但是相同的值加密后加密值相同

xxxxxxxxxx
3
1
-- 在数据库使用MD5加密
2
UPDATE `user` SET pwd=MD5(pwd) WHERE id = 1;
3
INSERT INTO `user` VALUES(3,'小明`user`',MD5('123456'));

6 事务

6.1 什么是事务

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

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

  • 事务的几个步骤，要不一起成功要不一起失败

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

  • 事务完成后，符合逻辑运算

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

  • 事务没有提交，恢复到原状，事务已经提交，持久化到数据库

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

  • 针对多个用户同时操作，排除其他事务对本事务的影响

事务的隔离级别

脏读：一个事务读取了另一个事务的未提交数据

不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同

幻读：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致

6.2 开启事务

DCL 数据控制语言

事务处理流程：

xxxxxxxxxx
20
1
-- 事务
2
-- MySQL是默认开启事务提交的
3
SET autocommit = 0; -- 关闭
4
SET autocommit = 1; -- 开启
5

6
-- 手动处理事务
7
SET autocommit = 0; -- 关闭自动提交
8
-- 事务开启
9
START TRANSACTION -- 标记事务开始
10

11
-- 提交
12
COMMIT
13
-- 回滚
14
ROLLBACK    
15
-- 事务结束
16
SET autocommit = 1; -- 开启自动提交
17

18
SAVEPOINT savaname -- 设置保存点
19
ROLLBACK TO SAVEPOINT savename -- 回滚到保存点
20
RELEASE SAVEPOINT savename -- 撤销保存点

 

模拟事务执行：

xxxxxxxxxx
14
1
-- 模拟转账
2
-- 手动处理事务
3
SET autocommit = 0; -- 关闭自动提交
4
-- 事务开启
5
START TRANSACTION -- 事务开始(一组事务)
6
UPDATE account SET `money`=`money`-500 WHERE `name`='a'; -- a减500
7
UPDATE account SET `money`=`money`+500 WHERE `name`='b'; -- b加500
8

9
-- 提交
10
COMMIT
11
-- 回滚
12
ROLLBACK    
13
-- 事务结束
14
SET autocommit = 1; -- 开启自动提交

7 索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1 索引的分类

• 主键索引

  • 唯一标识，主键不可重复，只能有一个列为主键

• 唯一索引

  • 避免重复的列出现，多个列可以标识为唯一索引

• 常规索引

  • 默认，用key关键字设置

• 全文索引

  • 在特定数据库引擎下支持

xxxxxxxxxx
10
1
-- 索引的使用
2
-- 1.在创建表的时候增加索引
3
-- 2.创建完毕后，增加索引
4
-- 显示索引信息
5
SHOW INDEX FROM student;
6
-- 增加索引
7
ALTER TABLE `student` ADD FULLTEXT INDEX `studentName`(``student`studentname`);
8

9
-- EXPLAIN 分析SQL执行的状况
10
EXPLAIN SELECT * FROM student; -- 普通索引

7.2 测试索引

xxxxxxxxxx
21
1
-- 插入100万条数据
2
DELIMITER $$ -- 写函数前的标志
3
CREATE FUNCTION mock_data()
4
RETURNS INT
5
BEGIN
6
    DECLARE num INT DEFAULT 1000000;
7
    DECLARE I INT DEFAULT 0;
8
    
9
    WHILE i<num DO
10
        INSERT INTO `app_user`(`name`,`email`,`phone`,`gender`,`password`,`age`) VALUES
11
        (CONCAT('用户',i),'123456@qq.com'
12
        ,CONCAT('13',FLOOR(RAND()*((999999999-100000000)+100000000)))
13
        ,FLOOR(RAND()*2)
14
        ,UUID()
15
        ,FLOOR(RAND()*100));
16
        SET i = i+1;
17
    END WHILE;
18
    RETURN i;
19
END;
20
-- 执行插入
21
SELECT mock_data();

xxxxxxxxxx
10
1
-- 查询速度测试
2
SELECT * FROM `app_user` WHERE `name`= '用户99999';
3
EXPLAIN SELECT * FROM `app_user` WHERE `name`= '用户99999';-- 查询了992975行 0.5s
4

5
-- 创建索引
6
-- CREATE INDEX
7
-- 创建索引后 查询速度测试
8
CREATE INDEX id_app_user_name ON app_user(`name`);
9
SELECT * FROM `app_user` WHERE `name`= '用户99999';
10
EXPLAIN SELECT * FROM `app_user` WHERE `name`= '用户99999';-- 查询了1行 0.001s

7.3 索引原则

• 经常变动的数据不要加索引
• 小数据量不需要加索引
• 索引一般加在常用字段

索引的数据结构

• Hash： 哈希
• Btree ：b树（InnoDB默认的数据结构）

8 权限管理和备份

8.1 用户管理

SQLyog可视化管理

权限管理：

创建表，更新表，删除表，插入，更新，删除...

xxxxxxxxxx
19
1
-- 创建用户
2
CREATE USER ZJT IDENTIFIED BY '123456';
3
-- 修改密码(当前用户密码)
4
SET PASSWORD = PASSWORD('123456');
5
-- 修改密码(当前用户密码)
6
SET PASSWORD FOR ZJT = PASSWORD('123456');
7
-- 重命名
8
RENAME USER ZJT TO zjt;
9
-- 用户授权
10
-- ALL PRIVILEGES除了给其他用户授权权限
11
GRANT ALL PRIVILEGES ON *.* TO zjt;
12
-- 查询权限
13
SHOW GRANTS FOR ZJT;
14
SHOW GRANTS FOR root@localhost; 
15
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION 包括GRANT权限
16
-- 撤销权限
17
REVOKE ALL PRIVILEGES ON *.* FROM ZJT;
18
-- 删除用户
19
DROP USER ZJT;

8.2 数据库备份

• 保证重要数据不丢失
• 数据转移

MySQL备份方式

• 1.拷贝物理文件
• 2.用可视化工具导出

• 3.使用命令行导出 mysqldump

xxxxxxxxxx
1
1
 mysqldump -uroot -p123456 school student >C:\Users\12647\Desktop\student.sql

导入：

xxxxxxxxxx
1
1
source C:\Users\12647\Desktop\student.sql

9 数据库设计规范

当数据库复杂时需要设计

数据库设计要求：

• 节省内存空间
• 保证数据完整性
• 方便开发系统

数据库设计问题：

• 数据冗余，浪费空间
• 数据插入和删除麻烦，异常[不使用物理外键]

关于数据库设计

• 收集信息，分析需求

  • 用户表（用户登录注销，用户个人信息，写博客...）
  • 分类表（文章分类）
  • 文章表
  • 自定义表（系统信息，关键字，主题...）

• 标识实体（把需求落地到字段）

9.1 三大范式

为什么需要规范化

• 信息重复
• 更新异常
• 插入异常
• 删除异常

三大范式

1.第一范式（1NF）：列不可再分

• 1.每一列属性都是不可再分的属性值，确保每一列的原子性
• 2.两列的属性相近或相似或一样，尽量合并属性一样的列，确保不产生冗余数据

2.第二范式（2NF）：属性完全依赖于主键

前提：满足第一范式

• 一行数据只做一件事情
• 例：一个人订了多个房间，此时每行还包含了联系人的信息和房间号，为了记录此人的多个房间号，创建多行此联系人的数据就会导致（联系人信息）数据冗余，需要把联系人信息和联系人订的房间号拆开两张表存放

3.第三范式（3NF）：属性不依赖于其它非主属性 属性直接依赖于主键

前提：满足第二范式

• 每列数据都需要直接和主键相关
• 例：学生表里每个学生都存储了班主任的信息，此时应该把班主任的信息拆开成班主任表，学生表只保留班主任姓名（id）用于关联两张表

规范性 和 性能的问题

关联查询不得超过三张表

• 考虑商业化需求，数据库的设计性能更重要
• 在性能允许情况下尽量规范
• 故意增加冗余字段（减少表数量）
• 故意增加计算列（索引）