【可视化工具操作数据库，MySQL基础】

一、如何使用终端操作数据库

点击查看

二、如何使用可视化工具操作数据库

1、可视化数据库操作工具：

• Navicat
• SQLyog

新建数据库时：

• 表名：自拟

• 引擎选：InnoDB

• 字符集选：utf8

• 排序规则选：utf8_general_ci

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

unsigned：

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

zerofill：

• 0填充
• 不足的位数，使用0来填充，int(3): 5—005

自增：

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

==非空：==not null

• 假设设置为not null，如果不给他赋值，就会报错！
• NULL，如果不填写，默认就是null

默认：

• 设置默认的值
• gender，默认值为 男，如果不指定该列的值，则会有默认值！

拓展：

/*每个表，都必须存在以下五个字段！表示一个记录存在意义！
id			主键
'version'	乐观锁
is_delete	伪删除
gmt_creat	创建时间
gmt_update	修改时间
*/

可视化工具中使用命令行建表：

-- 创建表格注意点：使用英文()，表的名称 和 字段 尽量使用'' 括起来
-- 字符串使用单引号括起来！
-- 所有的语句后面加 ,(英文符号) 最后一个字段不用加
-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键
CREATE TABLE IF NOT EXISTS 'student'(
	'id' INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    'name' VARCHAR(25) NOT NULL DEFAULT '匿名' COMMENT '姓名'
    
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

格式：[ ]内表示可选

CREAT TABLE [IF NOT EXISTS] '表明'(
	'字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    '字段名' 列类型 [属性] [索引] [注释],
    ······
    '字段名' 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释];

可视化工具中查看SQL语句：

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

关于数据库引擎（数据表的类型）：

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

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

常规使用操作：

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

在物理空间存在的位置：

• 所有的数据库文件都存在data目录下，本质还是文件的存储！

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

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

设置数据表的字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码~（不支持中文！）

MySQL的默认编码是Latin1，不支持中文

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

character-set-server=utf8

3、DML语言

• 添加

语法：insert into 表名([字段名1,字段名2,...])values('值1','值2',...)[,('值1','值2',...)...];

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应
3. 同时可以插入多条数据，values后面的值需要使用逗号隔开即可：values(),()...

• 修改

语法：update 表名 set 'colnum_name' ='value' [,'colnum_name1' ='value1'...] where[条件]

条件：where子句 运算符

注意事项：

1. colnum_name 是数据库的列，尽量带上’ ’
2. 条件，筛选的条件，如果没有指定，则会修改所有的列
3. value，是一个具体的值，也可以是一个变量
4. 多个设置的属性之间，使用英文逗号隔开

操作符	含义	范围	结果
=	等于	1=2	false
<> 或 !=	不等于	1<>2	true
> >=	大于 大于等于
< <=	小于 小于等于
between …and…	在某个范围内	[1,2]
and &&	我和你	5>1 and 1>2	false
or ||	我或你	5>1 or 1>2	true
not ！	非	not a	取反

• 删除

语法：delete from 表名[where 条件]

-- 删除数据 （避免这样写，会全部删除）
delete from 'student'

-- 删除指定数据
delete from 'student' where id = 1;

truncate命令

用法：truncate 'student' -- 清空student表

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

delete与truncate 区别：

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

了解：delete删除的问题，重启数据库

• InnoDB：自增列会从1开始（存在内存当中的，断电即失）
• MyISAM：继续从上一个自增量开始（存在文件中的，不会丢失）

4、DQL查询数据（重点）

data query language

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

SELECT 语法

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

指定查询字段

语法：select 字段 ... from 表

-- 查询全部的学生
select *from student 
-- 查询指定字段
select 'StudentNo','StudentName' from student
--别名
select 'StudentNo' as 学号,'StudentName' as 学生姓名 from student as s

别名，给结果起一个名字as ,可以给字段起别名，也可以给表起别名

函数concat(a,b) 连接a和b

select concat('姓名：', StudentName) as 新名字 from student

• 去重 distinct

select distinct 'StudentNo' from result

作用：去除select语句查询出来的结果中重复的数据，重复的只显示一条。

• 查询系统版本（函数）

select version()

• 计算结果（表达式）

select 100*2-1 as 计算结果

eg: select 'StudentNo','StudentResult' +1 as '提分后' from result

• 查询自增的步长（变量）

select @@auto_increment_increment

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

用法：select 表达式 from 表

模糊查询：比较运算符

运算符	语法	描述
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…其中的某一个之中，结果为真

-- 模糊查询
-- *********like**************
-- like结合 %（代表0到任意个字符）	_（代表一个字符）
-- 查询姓刘的同学
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)

-- *********null  not null**************
-- 查询地址为空的学生
select 'StudentNo','StudentName' from 'student' where address='' or address is null
-- 查询地址不为空的学生
select 'StudentNo','StudentName' from 'student' where address='' or address is not null

联表查询（JOIN）

left join right join inner join

JOIN对比

-- 联表查询
-- 查询参加了考试的学生（学号，姓名，科目编号，分数）
select * from student
selsct * from result
/* 思路
1. 分析需求，分析查询的字段来自那些表，（连接查询）
2. 确定使用哪种连接查询？ 7种
确定交叉点（两个表中哪个数据是相同的）
判断的条件：学生表中的 studentNo = 成绩表中的studentNo
*/
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 s
right join result r
on s.studentNo = r.studentNo

--left join
select s.studentNo,studentName,subjectNo,studentResult 
from student s
left join result r
on s.studentNo = r.studentNo

-- 查询缺考的同学
select s.studentNo,studentName,subjectNo,studentResult 
from student s
left join result r
on s.studentNo = r.studentNo
where studentResult is NULL

right join与left join的区别：

操作	描述
inner join	如果表中至少有一个匹配，就返回行
right join	会从右表中返回所有的值，即使左表中没有匹配
left join	会从左表中返回所有的值，即使右表中没有匹配

join（连接的表） on （判断条件）连接查询

where 等值查询

-- 三表联查 student，result，subject
-- 学号，姓名，科目名，分数
select studentNo,studentName,SubjectName,StudentResult
from student s
right join result r 
on r.studentNo = s.studentNo
inner join subject sub
on r.SubjectNo = sub.SubjectNo

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

自连接（了解）

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

父类

categoryid	categoryName
2	信息技术
3	软件开发
5	美术设计

子类

pid	categoryid	categoryName
3	4	数据库
2	8	办公信息
3	6	web开发
5	7	ps技术

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

父类	子类
信息技术	办公信息
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息
select a.categoryName as '父栏目', b.categoryName as '子栏目'
from category as a,category as b
where a.categoryid = b.pid

子查询

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

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

where(select * from)

-- 1、查询 数据库结构-1 的所有考试结果，降序排序
-- 方式一：使用联表查询
select studentName,r.subjectNo,studentResult 
from result r
inner join subject sub
on r.subjectNo = sub.subjectNo
where SubjectName = '数据库结构-1'
order by studentResult DESC
-- 方式二：使用子查询
select studentName,subjectNo,studentResult 
from result
where subjectNo = (
    select subjectNo from subject
    where SubjectName = '数据库结构-1'
)

查询小结：

顺序很重要

select 去重 要查询的字段 from 表（注意：表和字段可以取别名）
xxx join 要连接的表 on 等值判断
where（具体的值，子查询语句）
Group By（通过哪个字段来分组）
Having（过分组后的信息，条件和where是一样的，位置不同）
Order By…（通过哪个字段排序）[升序/降序]
Limit startindex，pagesize

业务层面：

查询：跨表，跨数据库······

5、MySQL函数

常用函数

-- 数学运算
select ABS(-8) -- 绝对值
select CEILING(3.4) -- 向上取整
select FLOOR(3.4) -- 向下取整
select RAND() --  返回一个0-1之间的随机数

-- 字符串函数
select CHAR_LENGTH('长风破浪会有时') -- 字符串长度
select CONCAT('A','B','C') -- 拼接字符串
select INSERT('12345',1,2,'abc') -- 查询，替换
SELECT LOWER('XiaoEr')-- 小写字母
SELECT UPPER('XiaoEr') -- 小写字母

-- 时间和日期
select CURRENT_DATE()-- 获取当前日期
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()

聚合函数（常用）

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

select COUNT(studentName) from student; --count(字段)，会忽略所有的null值
select COUNT(*) from student; --count(*)，不会忽略所有的null值，本质 计算行数
select COUNT(1) from student; --count(1)，不会忽略所有的null值

select SUM(sutdentResult) as 总和 from result
select AVG(sutdentResult) as 平均分 from result

-- 练习：查询不同课程的平均分，最高分，最低分，平均分大于80分
-- 核心：根据不同的课程分组
select SubjectName,AVG(studentResult) as 平均分,MAX(studentResult) as 最高分,MIX(studentResult) as 最低分
from result r
inner join subject sub
on r.subjectNo = sub.subJectNo
group by r.subjectNo -- 通过什么字段来分组
having 平均分>80 -- 过滤分组的记录必须满足的次要条件

分页limit 和排序order by

排序：升序ASC，降序DESC

order by 通过哪个字段排序，怎么排

-- 查询的结果根据成绩升序 排序
select s.studentNo,studentName,subjectNo,studentResult 
from student s
right join result r
on s.studentNo = r.studentNo
order by studentResult ASC

分页

可以缓解数据库压力，给人好的体验

-- 分页，每页只显示5条数据
-- 语法：limit 起始值，页面的大小
-- limit 0,5	1~5	第一页
-- limit 1,5	2~6
-- limit 5,5	6~10第二页
-- 第N页 limit (n-1)*pageSize,pageSize
-- 【pageSize：页面大小，(n-1)*pageSize：页面起始值，n：当前页】
-- 【总页数 = 数据总数/页面大小】
select s.studentNo,studentName,subjectNo,studentResult 
from student s
right join result r
on s.studentNo = r.studentNo
order by studentResult ASC
limit 0,5

分组和过滤

-- 练习：查询不同课程的平均分，最高分，最低分，平均分大于80分
-- 核心：根据不同的课程分组
select SubjectName,AVG(studentResult) as 平均分,MAX(studentResult) as 最高分,MIX(studentResult) as 最低分
from result r
inner join subject sub
on r.subjectNo = sub.subJectNo
group by r.subjectNo -- 通过什么字段来分组
having 平均分>80 -- 过滤分组的记录必须满足的次要条件

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

MD5码以512位分组来处理输入的信息，且每一分组又被划分为16个32位子分组，经过了一系列的处理后，算法的输出由四个32位分组组成，将这四个32位分组级联后将生成一个128位散列值。

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

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、事务

点击参考博客链接

在 MySQL 中，事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性。

比如我们的银行转账：

-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';

-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

在实际项目中，假设只有一条 SQL 语句执行成功，而另外一条执行失败了，就会出现数据前后不一致。

因此，在执行多条有关联 SQL 语句时，事务可能会要求这些 SQL 语句要么同时执行成功，要么就都执行失败。

如何控制事务 - COMMIT / ROLLBACK

在 MySQL 中，事务的自动提交状态默认是开启的。

-- 查询事务的自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

自动提交的作用：当我们执行一条 SQL 语句的时候，其产生的效果就会立即体现出来，且不能回滚。

什么是回滚？举个例子：

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

可以看到，在执行插入语句后数据立刻生效，原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是，撤销执行过的所有 SQL 语句，使其回滚到最后一次提交数据时的状态。

在 MySQL 中使用 ROLLBACK 执行回滚：

-- 回滚到最后一次提交
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

由于所有执行过的 SQL 语句都已经被提交过了，所以数据并没有发生回滚。那如何让数据可以发生回滚？

-- 关闭自动提交
SET AUTOCOMMIT = 0;

-- 查询自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

将自动提交关闭后，测试数据回滚：

INSERT INTO user VALUES (2, 'b', 1000);

-- 关闭 AUTOCOMMIT 后，数据的变化是在一张虚拟的临时数据表中展示，
-- 发生变化的数据并没有真正插入到数据表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

-- 数据表中的真实数据其实还是：
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

-- 由于数据还没有真正提交，可以使用回滚
ROLLBACK;

-- 再次查询
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

那如何将虚拟的数据真正提交到数据库中？使用 COMMIT :

INSERT INTO user VALUES (2, 'b', 1000);
-- 手动提交数据（持久性），
-- 将数据真正提交到数据库中，执行后不能再回滚提交过的数据。
COMMIT;

-- 提交后测试回滚
ROLLBACK;

-- 再次查询（回滚无效了）
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

总结

1. 自动提交

   • 查看自动提交状态：SELECT @@AUTOCOMMIT ；

   • 设置自动提交状态：SET AUTOCOMMIT = 1 。

2. 手动提交

   • @@AUTOCOMMIT = 0 时，使用 COMMIT 命令提交事务，结束事务。

3. 事务回滚

   @@AUTOCOMMIT = 0 时，使用 ROLLBACK 命令回滚事务。

事务的实际应用，让我们再回到银行转账项目：

-- 转账
UPDATE user set money = money - 100 WHERE name = 'a';

-- 到账
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

这时假设在转账时发生了意外，就可以使用 ROLLBACK 回滚到最后一次提交的状态：

-- 假设转账发生了意外，需要回滚。
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

这时我们又回到了发生意外之前的状态，也就是说，事务给我们提供了一个可以反悔的机会。假设数据没有发生意外，这时可以手动将数据真正提交到数据表中：COMMIT 。

手动开启事务 - BEGIN / START TRANSACTION

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后，此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件，使其可以发生回滚：

-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手动开启的事务没有开启自动提交，
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 测试回滚
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

仍然使用 COMMIT 提交数据，提交后无法再发生本次事务的回滚。

BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 提交数据
COMMIT;

-- 测试回滚（无效，因为表的数据已经被提交）
ROLLBACK;

事务开启：

1. 修改默认提交 set autocommit=0;
2. begin;
3. start transaction;

事务手动提交：

• commit;

事务手动回滚：

• rollback;

事务的 ACID 特征与使用

（atomicity、consistency、isolation、durability）

事务的四大特征：

• A 原子性：事务是最小的单位，不可以再分割，表示两个步骤一起成功，或者一起失败，不能只发生其中一个动作；
• C 一致性：事务要求同一事务中的 SQL 语句，必须保证同时成功或者失败，表示事务完成后，符合逻辑运算；
• I 隔离性：事务1 和 事务2 之间是具有隔离性的；
• D 持久性：事务一旦结束 ( COMMIT ) ，就不可以再返回了 ( ROLLBACK )，表示事务结束后的数据不随外界原因导致数据丢失 。

事务的隔离性

事务的隔离性可分为四种 ( 性能从低到高 ) ：

1. READ UNCOMMITTED ( 读取未提交 )

   如果有多个事务，那么任意事务都可以看见其他事务的未提交数据。

2. READ COMMITTED ( 读取已提交 )

   只能读取到其他事务已经提交的数据。

3. REPEATABLE READ ( 可被重复读 )

   如果有多个连接都开启了事务，那么事务之间不能共享数据记录，否则只能共享已提交的记录。

4. SERIALIZABLE ( 串行化 )

   所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作。

查看当前数据库的默认隔离级别：

-- MySQL 8.x, 
-- GLOBAL表示系统级别，不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的默认隔离级别，可以重复读。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别：使用set

-- 设置系统隔离级别，LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询系统隔离级别，发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

脏读

测试 READ UNCOMMITTED ( 读取未提交 ) 的隔离性：

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子：
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 然后淘宝店在另一方查询结果，发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的，而该操作的结果是可以被其他事务（另一方的淘宝店）看见的，因此淘宝店的查询结果是正确的，淘宝店确认到账。但就在这时，如果小明在它所处的事务上又执行了 ROLLBACK 命令，会发生什么？

-- 小明所处的事务
ROLLBACK;

-- 此时无论对方是谁，如果再去查询结果就会发现：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

这就是所谓的脏读，一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

读取已提交

把隔离级别设置为 READ COMMITTED ：

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

这样，再有新的事务连接进来时，它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说，它们看到的还是未提交的数据，例如：

-- 正在操作数据事务（当前事务）
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 虽然隔离级别被设置为了 READ COMMITTED，但在当前事务中，
-- 它看到的仍然是数据表中临时改变数据，而不是真正提交过的数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+


-- 假设此时在远程开启了一个新事务，连接到数据库。
$ mysql -u root -p12345612

-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

但是这样还有问题，那就是假设一个事务在操作数据时，其他事务干扰了这个事务的数据。例如：

-- 小张在查询数据的时候发现：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

-- 在小张求表的 money 平均值之前，小王做了一个操作：
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- 此时表的真实数据是：
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

-- 这时小张再求平均值的时候，就会出现计算不相符合的情况：
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据，但还是会出现问题，就是在读取同一个表的数据时，可能会发生前后不一致的情况。这被称为不可重复读现象 ( READ COMMITTED ) 。

(可以想一个极端情况来理解：你刚SELECT完，立马计算AVG，在你输入代码的时候有人提交新数据改变了表，这就是不可重复性。)

幻读

将隔离级别设置为 REPEATABLE READ ( 可被重复读取 ) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

测试 REPEATABLE READ ，假设在两个不同的连接上分别执行 START TRANSACTION :

-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

-- 小王 - 北京
START TRANSACTION;

-- 小张 - 成都
COMMIT;

当前事务开启后，没提交之前，查询不到，提交后可以被查询到。但是，在提交之前其他事务被开启了，那么在这条事务线上，就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ，在小王这边，都不会查询到小张的事务记录，而是只会查询到自己所处事务的记录：

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ，那么在他的这条新事务的线上，跟其他事务是没有联系的，也就是说，此时如果其他事务正在操作数据，它是不知道的。

然而事实是，在真实的数据表中，小张已经插入了一条数据。但是小王此时并不知道，也插入了同一条数据，会发生什么呢？

INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

报错了，操作被告知已存在主键为 6 的字段。这种现象也被称为幻读，一个事务提交的数据，不能被其他事务读取到。

事务a和事务b同时操作一张表，事务a提交的数据，也不能被事务b读到，就可以造成幻读。

串行化

顾名思义，就是所有事务的写入操作全都是串行化的。什么意思？把隔离级别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

还是拿小张和小王来举例：

-- 小张 - 成都
START TRANSACTION;

-- 小王 - 北京
START TRANSACTION;

-- 开启事务之前先查询表，准备操作数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

-- 发现没有 7 号王小花，于是插入一条数据：
INSERT INTO user VALUES (7, '王小花', 1000);

此时会发生什么呢？由于现在的隔离级别是 SERIALIZABLE ( 串行化 ) ，串行化的意思就是：假设把所有的事务都放在一个串行的队列中，那么所有的事务都会按照固定顺序执行，执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释，小王在插入数据时，会出现等待状态，直到小张执行 COMMIT 结束它所处的事务，或者出现等待超时。

问题：串行化性能特差！

性能比较：(隔离级别越高，性能越差)

read uncommitted > read committed > repeatable read （默认级别）>serializable

资料来源：一天学会 MySQL 数据库_哔哩哔哩_bilibili

事务总结

-- 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 保存点名  -- 撤销保存点

7、索引

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

索引的分类

• 主键索引（primary key)
  • 唯一的标识，主键不可重复，只能有一个列作为主键
• 唯一索引（unique key)
  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识 唯一索引
• 常规索引（key/index）
  • 默认的，inedx/key关键字来设置
• 全文索引（fulltext）
  • 在特定的数据库引擎下才有，MyISAM
  • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建完毕后,增加索引

-- 显示所有的索引信息
SHOW INDEX FROM student

-- 增加一个全文索引(索引名)列名
ALTER TABLE school.student ADD FULLTEXT INDEX 'studentName'('studentName');

-- EXPLAIN 分析sq1执行的状况
EXPLAIN SELECT * FROM student;--非全文索引
EXPLAIN SELECT * FROM student WHERE MATCH(studentName) AGAINST('刘');

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

CodingLabs - MySQL索引背后的数据结构及算法原理

索引原则

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

索引的数据结构

Hash类型的索引

Btree：InnoB

8、权限管理和备份

了解

9、规范数据库设计

当数据库比较复杂时，我们需要规范设计

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库插入和删除都会很麻烦、异常【屏蔽使用物理外键】
• 程序的性能差

良好的数据库设计

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

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

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

设计数据库的步骤：（个人博客）

• 收集信息，分析需求
  • 用户表（登录注销，个人信息，写博客，创建分类）
  • 分类表（文章分类，谁创建）
  • 评论表
  • 友链表（友链信息）
  • 自定义表（系统信息，某个关键的字，或一些主字段）key：value
• 标识实体（把需求落地到每个字段）
• 标识实体之间的关系
  • 写博客：user—>blog
  • 创建分类：user—>category
  • 关注：user—>user
  • 友链：link
  • 评论：user—>user-blog

资料来源：【狂神说Java】MySQL最新教程通俗易懂】

数据库的三大设计范式

（1）1NF

只要字段值还可以继续拆分，就不满足第一范式。

范式设计得越详细，对某些实际操作可能会更好，但并非都有好处，需要对项目的实际情况进行设定。

（2）2NF

在满足第一范式的前提下，其他列都必须完全依赖于主键列。如果出现不完全依赖，只可能发生在联合主键的情况下：

-- 订单表
CREATE TABLE myorder (
    product_id INT,
    customer_id INT,
    product_name VARCHAR(20),
    customer_name VARCHAR(20),
    PRIMARY KEY (product_id, customer_id)
);

实际上，在这张订单表中，product_name 只依赖于 product_id ，customer_name 只依赖于 customer_id 。也就是说，product_name 和 customer_id 是没用关系的，customer_name 和 product_id 也是没有关系的。

这就不满足第二范式：其他列都必须完全依赖于主键列！

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20)
);

拆分之后，myorder 表中的 product_id 和 customer_id 完全依赖于 order_id 主键，而 product 和 customer 表中的其他字段又完全依赖于主键。满足了第二范式的设计！

（3）3NF

在满足第二范式的前提下，除了主键列之外，其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT,
    customer_phone VARCHAR(15)
);

表中的 customer_phone 有可能依赖于 order_id 、 customer_id 两列，也就不满足了第三范式的设计：其他列之间不能有传递依赖关系。

CREATE TABLE myorder (
    order_id INT PRIMARY KEY,
    product_id INT,
    customer_id INT
);

CREATE TABLE customer (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    phone VARCHAR(15)
);

修改后就不存在其他列之间的传递依赖关系，其他列都只依赖于主键列，满足了第三范式的设计！

三、如何在编程语言中操作数据库

• JDBC : sun发布的 一个 java程序和数据库之间通信的 规范（接口）
• 各大数据库厂商去实现JDBC规范（实现类），这些实现类打成压缩包，就是所谓的jar包

1 JDBC常规操作：

1)JDBC连接MySQL数据库

package com.atguigu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

//目标：和数据库建立连接
public class Demo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.添加jar
        //2.加载驱动
        //org.gjt.mm.mysql.Driver
        Class.forName("com.mysql.cj.jdbc.Driver");
        //3.通过驱动管理器获取连接对象
        //3-1.准备URL
        String url = "jdbc:mysql://localhost:3306/fruitdb";
        //3-2.准备用户名
        String user = "root";
        //3-3.准备密码
        String pwd = "123456";
        Connection conn = DriverManager.getConnection(url,user,pwd);

        System.out.println("conn = " + conn);
    }
}

2)JDBC对数据库进行增、删、改

package com.atguigu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class Demo02 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.通过驱动管理器获取连接对象
        //url 表示 和 数据库通信的地址
        //如果url中需要带参数，则需要使用?进行连接
        //如果需要带多个参数，则从第二个参数开始使用&连接
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useSSL=false&useUnicode=true&characterEncoding=utf-8","root","123456");
        //3.编写SQL语句
        //id , fname , price , fcount , remark
        String sql = "insert into t_fruit values(0,?,?,?,?)";
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.填充参数
        psmt.setString(1,"草莓");
        psmt.setInt(2,10);
        psmt.setInt(3,30);
        psmt.setString(4,"草莓很好吃");
        //6.执行更新(增删改），返回影响行数
        int count = psmt.executeUpdate();
        System.out.println(count > 0 ? "添加成功！" : "添加失败！");
        //7.释放资源（关闭连接 , 先关闭psmt，后关闭conn）
        psmt.close();
        conn.close();
    }
}

修改和删除：

package com.atguigu.jdbc;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

//JDBC - 修改和删除
public class Demo03 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Fruit fruit = new Fruit(33,"猕猴桃","猕猴桃是水果之王");

        Class.forName("org.gjt.mm.mysql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        String sql = "update t_fruit set fname = ? , remark = ? where fid = ? " ;
        PreparedStatement psmt = conn.prepareStatement(sql);
        psmt.setString(1,fruit.getFname());
        psmt.setString(2,fruit.getRemark());
        psmt.setInt(3,fruit.getFid());

        int count = psmt.executeUpdate();

        System.out.println(count > 0 ? "修改成功！" : "修改失败！");

        psmt.close();
        conn.close();
    }
}

//JDBC - 修改和删除
public class Demo04 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        String sql = "delete from t_fruit where fid = ? " ;
        PreparedStatement psmt = conn.prepareStatement(sql);
        psmt.setInt(1,6);
        int count = psmt.executeUpdate();

        System.out.println(count > 0 ? "删除成功！" : "删除失败！");

        psmt.close();
        conn.close();
    }
}

3)JDBC对数据库进行查询操作

查询所有的库存：

package com.atguigu.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//JDBC - 查询所有的库存
public class Demo05 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        //3.编写SQL语句
        String sql = "select * from t_fruit" ;
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.执行查询，返回结果集
        ResultSet rs = psmt.executeQuery();
        //6.解析结果集
        List<Fruit> fruitList = new ArrayList<>();
        while(rs.next()){
            //1表示读取当前行的第一列的数据
            //getInt ， 因为这一列是int类型，所以使用getInt
            //getInt(结果集的列名)
            //int fid = rs.getInt("fid");
            int fid = rs.getInt(1);
            String fname = rs.getString("fname");
            int price = rs.getInt(3);
            int fcount = rs.getInt(4);
            String remark = rs.getString(5);

            Fruit fruit = new Fruit(fid , fname , price , fcount , remark );
            fruitList.add(fruit);
        }
        //7.释放资源
        rs.close();
        psmt.close();
        conn.close();

        fruitList.forEach(System.out::println);
    }
}

查询指定fid的库存记录：

package com.atguigu.jdbc;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

//JDBC - 查询指定fid的库存记录
public class Demo06 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        //url ->    jdbc:mysql://ip:port/dbname?参数列表
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        //3.编写SQL语句
        String sql = "select * from t_fruit where fid = ? " ;
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.填充参数
        psmt.setInt(1,33);
        //6.执行查询，返回结果集
        ResultSet rs = psmt.executeQuery();
        //6.解析结果集

        if(rs.next()){
            int fid = rs.getInt(1);
            String fname = rs.getString("fname");
            int price = rs.getInt(3);
            int fcount = rs.getInt(4);
            String remark = rs.getString(5);
            Fruit fruit = new Fruit(fid , fname , price , fcount , remark );
            System.out.println(fruit);
        }
        //7.释放资源
        rs.close();
        psmt.close();
        conn.close();
    }
}

4) 添加操作时获取自增列主键值

package com.atguigu.jdbc;

import java.sql.*;

//JDBC - 查询指定fid的库存记录
public class Demo07 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Class.forName("org.gjt.mm.mysql.Driver");
        //url ->    jdbc:mysql://ip:port/dbname?参数列表
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/fruitdb?useUnicode=true&characterEncoding=utf-8&useSSL=false","root","123456");
        //3.编写SQL语句
        String sql = "select count(*) from t_fruit" ;
        //4.创建预处理命令对象
        PreparedStatement psmt = conn.prepareStatement(sql);
        //5.执行查询，返回结果集
        ResultSet rs = psmt.executeQuery();
        //6.解析结果集
        if(rs.next()){
            int count = rs.getInt(1);
            System.out.println("总记录条数："+count);
        }
        //7.释放资源
        rs.close();
        psmt.close();
        conn.close();
    }
}

5)批处理