MySQL数据库

MySQL

1.1 数据库简介

1.1.1 什么是数据库

数据库 DB（DataBase）

概念：存放和管理数据的仓库，软件，可以存储大量的数据。
作用：存储和管理数据

1.1.2 数据库分类

关系型数据库 ：(SQL)

关系型数据库是由多张能互相联接的二维行列表格组成的数据库。通过表和表之间，行和列之间的关系进行数据的存储。

• 常见的如：MySQL、Oracle、SQL Server、DB2、SQLlite…

非关系型数据库：(NoSQL)

去掉关系型数据库的关系型特征， 数据之间无关系，易于扩展。以对象的方式存储数据，通过对象自身的属性来决定。

分类：

1. 键值(Key-Value)存储数据库
2. 列存储数据库
3. 文档型数据库
4. 图形(Graph)数据库

• 常见的如：Redis、MongDB…

DBMS（数据库管理系统）

• 数据库的管理软件，科学有效的管理，维护和获取数据。
• MySQL软件 本质是数据库管理系统

1.2 MySQL简介

1.2.1 安装MySQL数据库

下载：https://downloads.mysql.com/archives/installer/

安装：点击安装包、 同意协议、选择自定义安装。

选择64位版本。

安装。

选择第一个配置。

选择配置类型。

设置root账号和密码。

选择标准系统。

等待安装完成。

1.2.2 配置MySQL环境变量

• 找到mysql安装目录
• 默认C:\Program Files\MySQL\MySQL Server 5.7

系统遍历

path路径

启动服务

1. 必须以管理员身份打开cmd

2. cmd下执行 net start mysql ，提示服务名无效。这是因为net start +服务名，启动的是win下注册的服务。此时，系统中并没有注册mysql到服务中。接下来将MySQL注册到win服务里面。

3. cd 切换到MySQL安装目录，输入 mysqld --install 回车，提示Service successfully install 表示成功安装
   

4. 再次启动服务
   

登录mysql，查看版本号

1.2.3 卸载MySQL数据库

https://jingyan.baidu.com/article/3d69c551611290f0ce02d77b.html

1.2.4 连接MySQL数据库

启动mysql服务，使用管理员身份启动cmd窗口

net start mysql

命令行连接数据库

mysql  -uroot  -proot  --连接数据库

常用的一些sql语句

update mysql.user set authentication_string=password('root') 
where user = 'root' and Host = 'localHost'; --修改用户密码
flush privileges; --刷新权限
-----------------------------------------------------------
--所有语句都使用；结尾
show databases; --查询所有数据库
use mybatis; --切换想要使用的数据库
show tables; --查看所有的表
describe blog; --查看某一具体的表的结构
create database school; --创建一个数据库
exit; --退出连接
-- 单行注释
/*
多行注释
*/

数据库xxx语言

1. DDL 定义
2. DML 操作
3. DQL 查询
4. DCL 控制

1.3 操作MySQL

操作数据库–>操作数据库中的表–>操作表中的数据
Mysql关键字不区分大小写

1.3.1 操作数据库

1.创建数据库 [ ]表示可选代码

create database [if not exists] school; 
--如果不存在school数据库就创建它

2.删除数据库

drop database [if exists] school;
-- 如果存在school数据库就删除它

3.使用数据库

use `User`; -- ` `tab键上方的英文符号
-- 如果表名或者字段名是一个特殊字符，为了方便阅读使用` `对其标注

4.查看数据库

show databases;
-- 查看所有的数据库

数据库的列类型

数值

• tinyint （十分小的数据 ） 1个字节
• smallint （较小的数据） 2个字节
• mediumint （中等数据） 3个字节
• int （标准的整数）4个字节 （常用）
• bigint （较大的数据） 8个字节
• float （浮点数） 4个字节
• double （浮点数） 8个字节（精度问题！）
• decimal （字符串形式的浮点数）金融计算的时候使用

字符串

• char （字符串固定大小的） 0-255
• varchar （可变字符串） 0-65535 （常用）
• tinytext （微型文本） 2⁸ -1
• text （文本串） 2¹⁶ -1 （保存大文本）

时间日期

• date (YYYY - MM - DD) ,日期格式
• time (HH : mm : ss) ,时间格式
• datatime (YYYY-MM-DD HH:mm:ss) （常用）
• timestamp (时间戳) 1970.1.1日0时0分到现在的毫秒数（较常用）
• year (年份表示)

null

• 没有值 未知
• 不要使用null进行运算，结果为null

数据库的字段属性（重点）

Unsigned：

• 无符号的整数
• 声明了的列不能为负数

Zerofill：

• 0填充的
• 不足的位数，使用0进行填充,如： int(3) 5–>005

Autoincrement：

• 自增
• 自动在上一条记录的基础上+1（默认）
• 通常用来设置唯一的主键
• 必须是整数类型
• 可以自定义主键自增的起始值及步长

NotNull：

• 非空
• 该字段的值不能为null

Default：

• 默认值
• 不为其赋值的话该字段默认的值

每一个表，都必须存在以下五个字段：（表示一个记录存在的意义，为了保证健壮性和安全性）

1. id （主键）
2. version（乐观锁）
3. is_delete (伪删除)
4. gmt_create (创建时间)
5. gmt_update (修改时间)

1.3.2 操作表

要求：创建学生表（student）

学号 int, 登录密码 varchar(20),姓名 varchar(24), 性别 varchar(2)，出生日期 datatime，家庭住址 varchar(32)，email varchar(20).

CREATE TABLE IF NOT EXISTS `student`(
	`uid` INT(9) NOT NULL AUTO_INCREMENT COMMENT '学号',  
	`password` VARCHAR(20) COMMENT '登录密码', 
	`name` VARCHAR(24) COMMENT '姓名', 
	`sex` VARCHAR(2) COMMENT '性别', 
	`birthday` DATETIME COMMENT '出生日期', 
	`address` VARCHAR(32) COMMENT '家庭地址', 
	`email` VARCHAR(20) COMMENT '邮箱',
	PRIMARY KEY (`uid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

注意点：

• 使用英文的括号() ,表的名称和字段尽量使用 ` 标注起来
• 字符串使用单引号括起来
• 所有的语句后面加,英文状态下的逗号。最好一个不用加
• Primary key 主键，一般一个表只有一个唯一的主键
• 尽量加上comment注释
• engine(表的类型) default charset(默认字符集)
• 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务

查看数据库或者表的创建SQL语句

SHOW CREATE DATABASE school; -- 查看创建数据库的SQL
SHOW CREATE TABLE student; -- 查看创建表的语句
DESC student; -- 显示表的结构

数据表的类型

数据库引擎

• INNODB 默认使用
• MYISAM 早些年使用

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

常规使用操作：

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

在物理空间存在的位置：

所有的数据库文件都在data文件夹中，一个文件夹对应一个数据库。本质还是文件的存储。

MySQL引擎在物理文件上的区别：

• INNODB:在数据库表中只有一个*.frm文件，以及上级目录下的ibdata文件
• MYISAM 对应文件
  • *.frm -表结构的定义文件
  • *.MYD -数据文件（data）
  • *.MYI -索引文件（index）

 SHOW GLOBAL VARIABLES LIKE "%datadir%"; --查看data所在的目录

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

charset=utf8

不设置的话，会是MySQL的默认编码（Latin1），不支持中文

• 可以在创建表的时候设置默认编码（推荐）
• 也可以在my.ini中配置默认的编码

character-set-server=utf8

修改删除表

所有的创建和删除操作都建议加上判断语句，避免报错

修改表

alter table student rename as student1; -- 修改表名
alter table student1 add age varchar(11); -- 增加一条字段
-- 修改表的字段（重命名，修改约束）
alter table student1 modify  age int(11); -- 修改约束
alter table student1 change age age1 int(1); -- 字段重命名
alter table student1 drop age1; -- 删除表的字段

删除表

drop table if exists student1; -- 删除表

1.4 MySQL数据管理

1.4.1 外键（了解）

创建表的时候没有外键约束，需要将student表中的gradeid字段和grade表中的字段gradeid通过外键联系起来。

alter table `student` add constraint `FK_gradeid` foreign key (`gradeid`)
references `grade` (`gradeid`)
--  alter table 表名 add constraint 约束名 foreign key (作为外键的列) references
-- 哪个表（哪个字段）

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

最佳实现

• 数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）。
• 想要使用多张表的数据，想要使用外键，使用程序实现。

1.5 DML(数据库操作语言）

数据库意义：数据存储，数据管理
DML语言：数据操作语言

• insert
• update
• delete

1.5.1 添加

插入语句

• 写插入语句，一定要保证字段和数据一 一对应。
• 如果主键自增，可以省略该字段，会一 一匹配（不能值不写主键而省略所有字段名）

insert into 表名([`字段1`，`字段2`,`字段3`...]) values ('值1','值2','值3'...)

• 插入多个字段,每组值用括号括起来，逗号隔开

insert into 表名([`字段1`，`字段2`,`字段3`...]) values
('值1','值2','值3'...),
('值1','值2','值3'...),
('值1','值2','值3'...)...

1.5.2 修改

• update 表名 set 字段名 = 新值 where 条件

update student set name = '张三' where id = 1;

• 修改多个属性，字段之间用逗号隔开

update student set `name` = '二狗'，`address`='北京' where id = 1;

注意:

• 如果更新语句不带条件的话会更新所有的数据。
• update student set name ="凉凉';

条件：

• where 子句 运算符 如：id等于某个值，大于某个值，在某个区间修改

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	大于
<	小于
>=	大于等于
<=	小于等于
between…and…	在…区间	[2,5]闭合区间
and	等价于&&	5>3 and 5>4	true
or	有一个成立即可

• 通过多个条件更新数据

update student set `sex` ='女' where `name` = '张三' and `address` ='北京';
update student set `sex` ='女' where `name` = '李四' or `id` = 2;

• 注意：column_name 是数据库的列，尽量带上`符号

1.5.3 删除

delete from 表名 where 条件 (如果没有where条件，则会删除所有数据)

delete from student where id = 1;

truncate table 表名：完全清空一个数据库表，表的结构和索引约束不会变

delete 和truncate的区别：

• 相同点：都能删除数据，都不会删除表结构
• 不同点：
  • truncate重新设置自增列，计数器清零
  • truncate不会影响事务

delete删除的一些问题：

• 重启数据库 即 net start mysql：
  • InnoDB引擎：自增列会从1开始（存储在内存中，断电即失）
  • MyISAM：继续从上一个自增列开始（存储在文件中）

1.6 DQL(数据库查询语言)

（Data Query Language 数据查询语言）

• 用于所有的查询操作 select
• 简单，复杂查询
• 数据库中最核心的语言
• 使用频率最高的语言

select 语法 各关键字顺序不能有错

1.6.1 普通语句

• 查询全部的学生

select * from student;

• 查询指定的字段

select `studentno`,`studentname` from student;

• 起别名 ，不用加引号。可以给字段起别名，也可以给表起别名

select `studentno` as 学号,`studentname` as 姓名 from student as s;

• 函数 concat(a，b)拼接字符串

select concat('姓名：',studentname) as 新姓名 from student ;

• 去重：去掉重复的数据，只显示一条。关键词 distinct

-- 查看有哪些同学参加了考试
select `studentno` from result; -- 发现结果有重复数据
select distinct `studentno` from result; -- 去重

• 其他查询功能（函数，表达式，变量）

select version(); -- 查看mysql版本（函数）
select 100*2-9 as计算结果； -- 用来计算（表达式）
select @@auto_increment_increment -- 查询自增的步长（变量）

• 查看所有同学加1分之后的成绩

select `studentno` as 学号, `studentresult`+1 as 提分后成绩 from result;

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

select 表达式 from 表

1.6.2 where条件子句

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

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

逻辑运算符

运算符	语法	描述
and &&	a and b	逻辑与
or ii	a or b	逻辑或
not !	not a	逻辑非

• 查询考试成绩在60-100之间的学生学号，科目，和成绩

SELECT `studentno`,`subjectno`,`studentresult` FROM result 
WHERE `studentresult`>=60 AND`studentresult`<=100;

-- 或者使用between and（闭区间）
SELECT `studentno`,`subjectno`,`studentresult` FROM result 
WHERE `studentresult`between 60 and 100;

• 查询除了1000学生之外的学生考试信息

select * from result where not`studentno` = 1000;

select * from result where `studentno` != 1000;

• 模糊查询（比较运算符）

运算符	语法	描述
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属于其中的某一个值，返回真

• 查询姓刘的同学 like关键字
• 通配符：% 0-任意个字符， _1个字符

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);
-- 或者使用 or也能查出来

• 查询地址为空的学生

-- 地址为空字符串 或者 null
select `studentname` from student where `address`='' or `address` is null;

1.6.3 联表查询（重点）

查询参加考试的同学（学号，姓名，科目编号，分数）

分析需求：

• 分析查询的字段属于哪两个表
• 确定使用哪种连接查询（7种）
• 确定交叉部分（这两个表中的哪个字段是相同的）
• 判断的条件 student.studentno = result.studentno

内连接

 SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
 FROM student as s
 inner join result as r
 on s.`studentno`=r.`studentno`;

右连接

 SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
 FROM student as s
 right join result as r
 on s.`studentno`=r.`studentno`;

左连接

 SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
 FROM student as s
 left join result as r
 on s.`studentno`=r.`studentno`;

三种连接的区别

• 左表中有一个学生没去参加考试，通过左连接能够查出来该学生的信息，通过右连接则查不出。

操作	描述
inner join	如果表中至少有一个匹配就返回行（显示左右两个表全部记录）
left join	会从左表中返回所有的值，即使右表中没有匹配（以左表为基准）
right join	会从右表中返回所有的值，即使左表中没有匹配（以右表为基准）

• 查询缺考的同学 ,在左表中存在，右表中不存在，使用左连接

 SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`
 FROM student as s
 left join result as r
 on s.`studentno`=r.`studentno`
 where `studentresult` is null;

on和where的区别

• join 连接的表 on 判断条件 （连接查询）
• where （等值查询）

三表查询练习

• 查询参加考试的同学信息（学号，学生姓名，科目名，分数）
• 注意：如果一个字段在两个表中都存在，要说明是哪一个表中的字段。

 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 r.subjectno = sub.subjectno; 

自连接

• 将一张表看成两张一模一样的表来查询

select a.categoryname as 父栏目, b.categoryname as 子栏目
from category as a, category as b
where a.categoryid = b.pid;

1.6.4 分页和排序

排序：order by

升序：（ASC） 降序：（DESC）

-- 查询‘C语言-1’科目的同学成绩，并按照降序排列
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM student s
INNER JOIN result r
ON s.`studentno`=r.`studentno`
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`='C语言-1'
ORDER BY `studentresult` DESC

分页：limit

• 语法：limit 起始值，页面的大小
  • 第一页：limit 0,5
  • 第二页：limit 5,5
  • 第三页：limit10,5
  • …
  • 第n页：limit (n-1)*PageSize,PageSize
    • 【PageSize:页面大小】
    • 【(n-1)*PageSize：起始值】
    • 【n：当前页】
    • 【数据总数/页面大小=页数：向上取整】

1.6.5 子查询

where(计算出来的一个值) 、where（select 语句）

• 查询C语言-1 科目的学号，科目编号，成绩，降序排列

连接查询

SELECT `studentno`,r.`subjectno`,`studentresult`
FROM result r
INNER JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`='C语言-1'
ORDER BY `studentresult` DESC

子查询

SELECT `studentno`,`subjectno`,`studentresult`
FROM result 
WHERE `subjectno`=(
SELECT `subjectno` FROM `subject`
WHERE `subjectname` ='C语言-1'
)
ORDER BY `studentresult` DESC

1.6.6 分组和过滤

group by 和 having

• HAVING语句通常与GROUP BY语句联合使用，用来过滤由GROUP BY语句返回的记录集。

• HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。

-- 查询平均分大于80的不同课程，最高分，最低分   
SELECT subjectname AS 科目名, AVG(studentresult) AS 平均分,
MAX(studentresult) AS 最高分,MIN(studentresult) AS 最低分
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY 科目名
HAVING 平均分>=80

概述

• “Group By”从字面意义上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

原始表

简单Group By

select 类别, sum(数量) as 数量之和
from A
group by 类别

• 返回结果如下表，实际上就是分类汇总。

Group By 和 Order By

select 类别, sum(数量) AS 数量之和
from A
group by 类别
order by sum(数量) desc

• 返回结果如下表

1.6.7 DQL小结

• select [distinct] 要查询的字段 from 表名 （表和字段可以取别名）
• xxx join 要连接的表 on 等值判断
• where(具体的值，子查询语句)
• group by (通过哪个字段进行分组)
• having (过滤分组后的信息，和where一样)
• order by (通过哪个字段进行排序) [desc asc]
• limit 起始索引,每页显示条数

1.7 MySQL函数

1.7.1 常用函数

数学运算

Mod(x,y); --求余函数，返回x除以y之后的余数 MOD(5,2) 1 ,5是奇数
select ABS(-8); -- 绝对值 8
select ceiling(9.4); --向上取整 10
select floor(9.4);  -- 向下取整 9
select rand(); --返回一个0-1之间的随机数 
select sign(0); -- 判断一个数的符号，正数返回1，负数返回-1，0返回0

字符串函数

select char_length('helloword');  --返回字符串的长度 9
select concat('二','狗','子'); -- 拼接字符串 二狗子
select insert('我爱你',1,2,'我不爱');  -- 查询替换，从某个位置开始，替换某个长度 我不爱你
select lower('HelloWord') ;-- 将字符串全部转为小写
select upper('HelloWord') ; -- 将字符串全部转换为大写
select instr('hellowordhello','o'); -- 返回指定字符第一次出现的索引 5
select replace('努力就会成功','就会','不一定') -- 将a替换为b 努力不一定成功
select substr('努力就会成功',3,4) -- 截取指定字符串,a是起始位置，b是截取长度 就会成功
select reverse('努力就会成功'); -- 反转字符串  功成会就力努

其他函数

select current_date(); -- 获取当前日期
select curdate(); -- 获取当前日期
select now(); -- 获取当前时间
select localtime(); -- 获取本地时间
select sysdate(); -- 返回系统时间
select year(now()); -- 返回当前的年份

系统函数

select user(); -- 返回当前用户
select system_user(); -- 返回当前用户
select version(); -- 返回版本号

1.7.2 聚合函数

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

三种COUNT函数统计数据区别

-- 会忽略NUll值,如果该列的数据是NULL，则不会计数
select count(studentname) from student;
-- 包括了所有的列，相当于行数，不会忽略NULL值，本质是计算行数
select count(*) from student;
--包括了忽略所有列，用1代表代码行,本质是计算行数，不会忽略NULL值
select count(1) from student;

1.7.3 Case when用法

https://blog.youkuaiyun.com/qq_30038111/article/details/79611167

1.7.4 数据库级别的MD5加密

什么是MD5：

• 增强算法复杂度和不可逆性
• MD5不可逆，具体的值的MD5是一样的

-- 插入数据的时候就对其加密
insert into testmd5 values(1,'zhangsan',md5('123456'));
-- 校验密码是否正确的时候，注意如果二次加密的话不能验证
select * from testmd5 where name ='zhangsan' and pwd = md5('123456');

1.8 事务

几条sql同时执行，要么都成功，要么都失败。

转账案例：

• SQL1： A给B转账 A余额减少
• SQL2： B收到转账 B余额增加

核心：将一组sql放在一个批次中执行

1.8.1 事务的ACID原则

原子性，一致性，隔离性，持久性

参考博客地址：https://blog.youkuaiyun.com/dengjili/article/details/82468576

原子性（Atomicit）

• 一组SQL要么都成功，要么都失败。
• 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性（Consistency）

• 事务前后的数据完整性要保持一致，比如转账前AB总余额是1000，转账后总余额也要是1000
• 在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性（Isolation）

• 事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
• 数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性（Durability） ----- 事务提交

• 事务未提交，恢复到原状，
• 事务一旦提交则不可逆，被持久化到数据库中。
• 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

1.8.2 隔离所导致的一些问题

事务的隔离级别
脏读

• 指一个事务读取到了另一个事务未提交的数据

不可重复读

• 在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读（幻读）

• 是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
• 一般是行影响，多了一行

1.8.3 事务的操作

mysql是默认开启自动提交事务的

set autocommit = 0; -- 关闭事务
set autocommit = 1; -- 开启事务（默认）

手动处理事务

-- 1.关闭自动提交
set autocommit = 0;
-- 事务开启
start transaction; -- 标记一个事务的开启，从这个之后的sql都在同一个事务内
select ...
select...
commit -- 提交事务
rollback -- 回滚事务
-- 最后，将自动提交开启
set autocommit = 1; 
savepoint 保存点名 -- 设置一个事务的保存点
rollback savepoint 保存点名 -- 回滚到哪个保存点
release savepoint 保存点名 -- 撤销保存点 

事务模拟

 SET autocommit = 0; -- 关闭自动提交
 
 START TRANSACTION; -- 开启一个事务（一组事务）
 UPDATE `account` SET `money`=`money`-50 WHERE `name` ='A'; -- A减50
 UPDATE `account` SET `money`=`money`+50 WHERE `name` = 'B'; -- B加50
 
 COMMIT; -- 提交事务 ， 一旦执行就不能回滚
 ROLLBACK; -- 回滚事务
 
 SET autocommit = 1; -- 恢复默认值，设置自动提交

1.9 索引

MySQL官方对索引的定义为：

• 索引（Index）是帮助MySQL高效获取数据的数据结构。

1.9.1 索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

主键索引 （primary key）

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

唯一索引 （unique key）

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

常规索引 （key/index）

• 默认的，通过key或者index设置

全文索引 （fullText）

• 快速定位数据

1.9.2 索引的使用

• 在创建表的时候给字段增加索引
• 创建完毕后，增加索引

-- 显示所有的索引信息
show index from student;

• non_unique表示是否唯一，0表示唯一，1表示不唯一

• 增加一个全文索引 （索引名）列名

-- 为`studentName`字段添加一个全文索引		
alter table student add fulltext index `studentName` (`studentName`); 

• explain 分析sql执行的状况

explain select * from student; -- 非全文索引
explain select * from student where match(studentName) against('王');

• 在MySQL 5.6.5中，TIMESTAMP和DATETIME列可以自动initializated和更新到当前的日期和时间（即当前时间戳）
• 在5.6.5之前，这仅适用于TIMESTAMP，并且每个表最多只有一个TIMESTAMP列。

 -- 创建一个app_USER表，里面插入一百万条数据，比较有无索引查询速度对比
  CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '',
`email` VARCHAR(50) NOT NULL,
`phone` VARCHAR(20) DEFAULT '',
`gender` TINYINT(4) UNSIGNED DEFAULT '0',
`password` VARCHAR(100) NOT NULL DEFAULT '',
`age` TINYINT(4) DEFAULT NULL,
`create_time` DATETIME ,
`update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP 
 ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

• 创建一个插入一百万条数据的函数

   -- 插入一百万条数据
   DELIMITER $$ -- 写函数前必须写，函数的标志
   SET GLOBAL log_bin_trust_function_creators=TRUE;
   CREATE FUNCTION fun_exemple()
   RETURNS INT DETERMINISTIC
   BEGIN
     DECLARE num INT DEFAULT 1000000;
     DECLARE i INT DEFAULT 0;
     WHILE i < num DO
	 INSERT INTO  app_user (`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES
	 (CONCAT('用户',i),'123852147@qq.com',
	 CEILING(999999999 * RAND()),CEILING(1 * RAND()),
	'45698514',CEILING(RAND()*100));
 	 SET i = i +1;   
     END WHILE;
     RETURN i;
   END;
   
SELECT fun_exemple(); -- 执行此函数 生成一百万条数据  

通过没有索引的列name进行查询

• SELECT * FROM app_user WHERE name = ‘用户90000’; – 执行耗时 : 1.396 sec

-- 为该列添加一个普通索引
-- id_表名_字段名
-- create [fulltext] index 索引名 on 表名(字段名)
create index id_app_user_name on app_user(name);

再次查询添加了普通索引之后的该列

• SELECT * FROM app_user WHERE name = ‘用户90000’ – 执行耗时 : 0.005 sec

查询行数添加索引前后对比

添加索引前：

添加索引后：

索引在小数据量的时候，作用不大，但是在大数据的时候，区别十分明显

1.9.3 索引的原则

• 索引不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般用来加在常用来查询的字段上

1.9.4 索引的数据结构

InnoDB默认数据结构是：Btree (B树)

1.10 权限管理和备份

1.10.1 权限管理

SQLyog设置

SQL命令操作

• 用户表 mysql.user
• 本质：对该表进行增删改查

-- 创建一个用户,用户名user，密码root
CREATE USER 'rm1'@'%' IDENTIFIED BY 'root';

-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456');

-- 修改指定用户密码
SET PASSWORD FOR rm1 = PASSWORD('root');

-- 重命名
RENAME USER rm1 TO rm2;

-- 用户授权，授予全部权限(全部数据库.全部表)
-- ALL PRIVILEGES除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO  'rm1'@'%';

-- 查询权限
SHOW GRANTS FOR rm2; -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost;
-- root权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 revoke 哪些权限，在哪些库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM rm2;

-- 删除用户
DROP USER rm2;

1.10.2 数据库备份

为什么要备份？

• 防止数据丢失
• 方便数据转移

MySQL数据库备份方式：

• 物理复制，直接拷贝文件夹
• 在sqlyog可视化工具中直接导出

• 使用命令行导出 mysqldump(黑窗口命令行)

# mysqldump -h 主机 -u 用户名 -p 密码 数据库名 表1 表2... > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

导入sql文件

• 在登录的情况下
• source 备份文件所在的绝对路径

source D:/a.sql
mysql -uroot -proot 库名<备份文件

1.11 数据库设计

1.11.1 为什么需要设计

• 避免数据冗余
• 保证数据库的完整性
• 方便开发

软件开发中，关于数据库的设计

• 分析需求：分析业务和需要处理的数据
• 概要设计：设计关系图E-R图

1.11.2 数据库三大范式

满足三大范式是为了规范数据库的设计

第一范式(1NF)

• 原子性：保证每一列不可再分

第二范式(2NF)

• 前提：满足第一范式
• 每张表只满足一件事情

第三范式(3NF)

• 前提：满足第一范式和第二范式
• 确保数据表中的每一列都与主键直接相关，而不是间接相关

规范性和性能的问题

关联查询的表不能超过三张表

• 考虑商业化的需求和目标 （成本，用户体验），数据库的性能更加重要
• 在符合性能的时候，需要适当地考虑下规范性
• 故意给某些表增加一些冗余字段（从多表查询变为单表查询）
• 故意增加一些计算列（从大数据量降为小数据量的查询）
  （或者增加索引，但是数据量很大的时候，索引文件也会占用较多磁盘空间）