MySQL知识梳理

做最幸福的

已于 2022-03-11 19:44:47 修改

阅读量84

点赞数

分类专栏：笔记文章标签： mysql 数据库面试

于 2021-09-21 15:08:11 首次发布

本文链接：https://blog.csdn.net/qq_44794280/article/details/120316598

版权

笔记专栏收录该内容

31 篇文章 1 订阅

订阅专栏

1.修改表常用语法

– 增加一列：alter table 表 add 字段类型约束；

– 修改列的类型约束：alter table 表modify 字段类型约束；

– 修改列的名称，类型，约束：alter table 表 change 旧列新列类型约束；

– 删除一列：alter table 表名 drop 列名；

– 修改表名：rename table 旧表名 to 新表明；

小结：都是以alter table 表名打头

增加列 add
修改列的类型、约束 modify
修改列的名字类型约束 change
删除列 drop

2.聚合函数

聚合函数	作用
max(列名)	求这一列的最大值
min(列名)	求这一列的最小值
avg(列名)	求这一列的平均值
count(列名)	统计这一列有多少条记录
sum(列名)	对这一列求总和

注意: 聚合函数会忽略空值NULL

我们发现对于NULL的记录不会统计，建议如果统计个数则不要使用有可能为null的列，但如果需要把NULL也统计进去呢？我们可以通过 IFNULL(列名，默认值) 函数来解决这个问题. 如果列不为空，返回这列的值。如果为NULL，则返回默认值。
count(具体字段名)和count(*)，count(具体字段名统计表中一行行的数据，count(具体字段名)统计表中该字段不为null的数据)

3.分组查询

SELECT 字段1,字段2... FROM 表名  [where 条件] GROUP BY 列 [HAVING 条件];

注意事项

单独分组没有意义, 返回每一组的第一条记录

分组的目的一般为了做统计使用, 所以经常和聚合函数一起使用

分组查询如果不查询出分组字段的值,就无法得知结果属于那组

在分组里面, 如果select后面的列没有出现在group by后面 ,展示这个组的这个列的第一个数据
where和having的区别【面试】

子名	作用
where 子句	1) 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。2) where后面不可以使用聚合函数
having字句	1) having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。2) having后面可以使用聚合函数

重点结论:

1.from where group by select order by
2.为什么分组函数不能直接使用在where后面?select ename,sal
from emp where sal>min(sal);该语句会报错,因为分组函数在使用的时候必须先分组之后才能使用.where执行的时候，还没有分组，所以where后面不能出现分组函数。
3.重点中的重点结论:在一条select语句当中，如果有group by语句的话,select后面只能跟:参加分组的字段,以及分组函数,其他的一律不能跟.

4.分页查询

语法

select ... from .... limit 起始行数,查询的记录条数.

LIMIT a,b;
a起始行数，从0开始计数，如果省略，默认就是0; a=(当前页码-1)*b;
b：返回的行数

4.外键

新建表时增加外键：
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字段名) REFERENCES 主表名(主键字段名)
关键字解释：
CONSTRAINT – 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES – 主表名(主键字段名) 表示参照主表中的某个字段
已有表增加外键：
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名称] FOREIGN KEY (外键字段名) REFERENCES 主表(主键字段名);

删除外键

alter table 表 drop foreign key 外键名称;
外键级联
ON UPDATE CASCADE – 级联更新，主键发生更新时，外键也会更新
ON DELETE CASCADE – 级联删除，主键发生删除时，外键也会删除
5.内连接

内连接的特点(查的是什么东西)

内连接查询的是公共部分,满足连接条件(主外键关系)的部分

使用内连接的关键点
- 使用主外键关系做为条件来去除无用信息. 抓住主外键的关系,用主外键作为连接条件 b表里面的外键 = a表里面的主键
- 显示内连接里面的,on只能用主外键关联作为条件,如果还有其它条件,后面加where
语法

-- 隐式(不出现inner)
select * from a,b where a.主键=b.外键 and 其它条件

-- 显示(出现inner)
select * from a [inner] join b on a.主键=b.外键 where 其它条件

6.外连接

1.外连接概述

 我们发现内连接查询出来的是公共部分. 如果要保证某张表的全部数据情况下进行连接查询. 那么就要使用外连接查询了.  外连接分为左外连接和右外连接。

2.左外连接

以join左边的表为主表,展示主表的所有数据,根据条件查询连接右边表的数据,若满足条件则展示,若不满足则以null显示.

可以理解为：在内连接的基础上保证左边表的数据全部显示

语法

select [字段][*] from a left [outer] join b on 条件

3.右外连接

以join右边的表为主表,展示右边表的所有数据,根据条件查询join左边表的数据,若满足则展示,若不满足则以null显示

可以理解为：在内连接的基础上保证右边表的数据全部显示

语法

select 字段 from a right [outer] join b on 条件

总结：

语法

select * from a left [outer] join b on 连接条件  --左外连接
select * from a right [outer] join b on 连接条件 --右外连接

内连接和外连接的区别
内连接: 查询的是公共部分,满足连接条件的部分

左外连接: 以左边表为主表, 查询出左边表的所有的数据. 再通过连接条件匹配出右边表的数据, 如果满足连接条件, 展示右边表的数据; 如果不满足, 右边的数据通过null代替

-------------------

右外连接: 以右边表为主表, 查询出右边表的所有的数据. 再通过连接条件匹配出左边表的数据, 如果满足连接条件, 展示左边表的数据; 如果不满足, 左边的数据通过null代替

7.子查询

1.什么是子查询？

直观一点: 一个查询语句里面至少包含2个select

2.子查询的三种结果

子查询结果的三种情况：
1. 子查询的结果是一个值的时候
  子查询结果只要是单个值，肯定在WHERE后面作为条件
  SELECT 查询字段 FROM 表 WHERE 字段[= > < <>]（子查询）;
2. 子查询结果是单列多行的时候
  子查询结果只要是单列，肯定在WHERE后面作为条件
  子查询结果是单列多行，结果集类似于一个数组，父查询使用IN运算符
  SELECT 查询字段 FROM 表 WHERE 字段 IN （子查询）;
3. 子查询的结果是多行多列
  子查询结果只要是多行多列，肯定在FROM后面作为表
  SELECT 查询字段 FROM （子查询）表别名 WHERE 条件;
  子查询作为表需要取别名，否则这张表没用名称无法访问表中的字段

8.事务

1.什么是事务

事务指逻辑上的一组操作，组成这组操作的单元要么全部成功，要么全部失败。

2.事物的作用

保证一组操作全部成功或者失败。

3.小结

事务是逻辑上的一组操作, 组成这组操作的单元要么全部成功, 要么全部失败
作用: 保证全部成功或者全部失败

9.MySQL进行事务管理

1.自动事务(mysql默认)

一条sql语句就是一个事务

2.手动开启事务

方式一: 手动开启事务的方式【掌握】

start transaction;开启事务

commit；提交

rollback；回滚

3.回滚点【了解】

1什么是回滚点

在某些成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功，可以在当前成功的位置设置一个回滚点。可以供后续失败操作返回到该位置，而不是返回所有操作，这个点称之为回滚点。

总结：设置回滚点可以让我们在失败的时候回到回滚点，而不是回到事务开启的时候。

start transaction;
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
-- 以上sql语句没有问题
savepoint abc;
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
-- 出现异常,回滚到abc回滚点位置
rollback to abc;
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
update account set money = money - 100 where name = 'zs'; -- zs账户-100
commit;

4.注意事项

建议手动开启事务, 用一次就开启一次
开启事务之后, 要么commit, 要么rollback
一旦commit或者rollback, 当前的事务就结束了
回滚到指定的回滚点, 但是这个时候事务没有结束的

10.事务特性与隔离级别

1. 事务特性

原子性（Atomicity）原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
```
eg: zs 1000; ls 1000; 
	zs 给 ls转100
	要么都发生zs 900; ls 1100;
	要么都不发生zs 1000; ls 1000;
```
一致性（Consistency）事务前后数据的完整性必须保持一致.

eg: zs 1000; ls 1000;  一共2000
	zs 给 ls转100
	要么都发生zs 900; ls 1100; 	一共2000
	要么都不发生zs 1000; ls 1000; 一共2000

持久性（Durability）持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响。
eg: zs 1000 给小红转520, 张三提交了
隔离性（Isolation）事务的隔离性是指多个用户并发操作数据库时，一个用户的事务不能被其它用户的事务所干扰，多个并发事务之间数据要相互隔离。

2.事务隔离级别

级别	名字	隔离级别	脏读	不可重复读	幻读	数据库默认隔离级别
1	读未提交	read uncommitted	是	是	是
2	读已提交	read committed	否	是	是	Oracle
3	可重复读	repeatable read	否	否	是	MySQL
4	串行化	serializable	否	否	否

隔离级别越高，安全性越高，性能(效率)越差。

设置事务隔离级别

set session transaction isolation level  隔离级别;
eg: 设置事务隔离级别为:read uncommitted,read committed,repeatable read,serializable
set session transaction isolation level read uncommitted;

查询当前事务隔离级别

select @@tx_isolation;

11.数据库安全性问题的发生

脏读：一个事物里面读到了另外一个事物没有提交的数据: read uncommitted。
不可重复读： 在一个事物里面,同一条语句,两次查询的结果不一致。
事务隔离级别:
读未提交:可能发生脏读,可能发生不可重复读,可能发生幻读
读提交: 不可能发生脏读,可能发生不可重复读,可能发生幻读
可重复读:不可能发生脏读,不可能发生不可重复读,可能发生幻读
串行化:不可能发生脏读,不可能发生不可重复读,不可能发生幻读

        ----------------------------------

12.MySQL三范式

1．第一范式(确保每列保持原子性)

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。
2．第二范式(确保表中的每列都和主键相关)

第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
3．第三范式(确保每列都和主键列直接相关,而不是间接相关)

第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。