MySql总结

大家好,我是一名入门的菜鸟,如果你不经意间翻开了我的文章,谢谢您,您的支持是我前进的动力,让我们一起加油!!
由于不是名牌大学,只是一个普普通通的专科生,所以,我想通过自己的努力来获得我想要的,我不会放弃我的梦想,我也曾幻想着我成功的时候在朋友边吹嘘,也曾想象到我失败时候潦倒的样子,幻想始终是幻想,我会努力的,加油,你一定能行

---

1 三大范式

1. 第一范式原子性：表中的每一列都是原子的，不可拆分成更小的列
2. 第二范式：在满足第一范式的基础上，表中的每一列都依赖于主键，一张表只描述一件事情，不存在局部依赖的关系
3. 第三范式：在满足第二范式的基础上，不存在间接依赖，都是直接依赖于主键

---

注意：范式越高，拆分的表也就越多，业务逻辑也就复杂，本来差一张表就能解决的，但遵循了三打范式的规范，就需要查很多表，在现实开发中，要进行性能与范式的取舍，当性能与范式发生冲突时，优先考虑性能

2 表与表之间的关系概述

表关系的概念

我们设计表不仅要遵守三打范式，也要结合实际生活，两者结合到一块，我们创建的表才更能贴合实际，每张表背后都对应一个实体对象，我们在设计表的时候，难免不会考虑表与表之间的关系

表与表之间的三种关系	记录之间的对应关系
一对多(A表1方和B表多方)	最常用的关系，A表中一条记录对应B表中多条记录 B表中一条记录对象A表中一条记录
一对一(A表1方和B表1方)	A表中一条记录对应B表中一条记录
多对多	A表中一条记录对应B表中多条记录 B表中一条记录对应A表中多条记录 实现过程中需要有第三张关系表

1. 一对一：特殊的1对多的关系，多方的外键添加唯一约束（unique）
   从表的主键又是外键(常用)
2. 一对多：在多方(从表)添加1列做为外键，对应(1方)主表中主键。如果要创建1对多的关系，只需要在从表中添加外键即可
3. 多对多：创建一张中间表，表中有两个从表的主键，中间表中至少有两个外键，通过外键建立多对多的关系

3 表连接：笛卡尔积和内连接

• 内连接：隐式内连接 显示内连接
  – 隐式内连接：select 列名 from 左表 ,右表 where 主表.主键 = 从表.外键; -- as省略
  – 显示内连接：select 列名 from 左表 inner join 右表 on 主表.主键 = 从表.外键
• 外连接：左连接和右链接
  – 左连接：select * from 左表 left join 右表 on 主表.主键=从表.外键
  – 右连接：select * from 左表 right join 右表 on 主表.主键=从表.外键

4 子查询

概念：

1. 查询结果作为另一个查询的条件
2. 子查询是存在查询的嵌套，内部的查询称为子查询，外部的查询就是父查询
3. 子查询必须使用括号

子查询结果的三种情况：

1. 单行单列，只有一个值

   

2. 多行单列，有多个值组成

   

3. 多行多列，是一张虚拟表，可以做为表再次查询

   

子查询的结果有三种情况：

1. 单行单列，表示一个值
2. 多行单列，表示多个值
3. 多行多行，表示一张虚拟表

5 索引

作用：

• 设置索引，查数据查的更快

索引分类：

1. 单列索引：对某一列创建索引
2. 多列索引：同时对多列创建索引

-- 创建单列索引
create index 索引名 on 表名(列名)
-- 显示索引信息
show index from 表名
-- 删除索引
drop index 索引名 on 表名
-- 创建复合索引
create index 索引名 on 表名(列名1,列名2...)

索引的使用原则和不足

使用原则：

1. 要在数据量大的表中创建索引才有意义
2. 要在经常查询的字段上使用索引
3. 如果一张表中增删改频率非常高，很少有查询操作，建议不使用索引

索引失效的几种情况

1. 查询条件中有or关键字，即使有索引也不起作用，除非对or中的每个查询都创建了索引
2. 模糊查询like，查询以 %×× 开头的不使用索引，以 xxx% 结尾会使用索引
3. 使用了比较运算符 <>和!=
4. mysql如果发现不使用索引更快，则不使用索引

6 函数

字符串函数

-- 字符串函数
-- 返回字符串的长度，所有的函数以玫瑰红显示
select char_length('Hello');   -- 5个

-- 用于拼接一个或多个字符串
select concat('I ', 'Love ', 'Java');

-- 将字符串转成小写
select lower('HELLO WORLD');

-- 将字符串转成大写
select upper('hello world');

-- 取子字符串：字符串, 开始位置(从1开始)，长度
select substr('Hello World', 7, 3);

-- 去掉前后的空格
select trim(' Hello Java ');

字符串函数	说明
CHAR_LENGTH(s)	获取字符串长度
CONCAT(s1,s2…sn)	拼接字符串
LOWER(s)	转成小写
UPPER(s)	转成大写
SUBSTR(s, start, length)	取子字符串，从1开始计数
TRIM(s)	去掉前后的空格

数学函数

-- 返回[0,1) 的随机小数，包头不包尾
select rand();

-- 四舍五入保留几位小数
select round(1.2345,3);

-- 返回一个列表中最小值
select least(30,4,28,10,9);

-- 返回一个列表中最大值
select greatest(30,4,28,10,9);

数学函数	说明
RAND()	随机得到[0,1)的数
ROUND(小数,保留几位)	四舍五入保留几位
LEAST(expr1, expr2, expr3, …)	得到列表中最小值
GREATEST(expr1, expr2, expr3, …)	得到列表中最大值

日期函数

-- 日期函数
-- 计算某个时间加多少天以后得到一个新的日期
select adddate('2019-02-28', 7);
select adddate('2019-02-28', -7);

-- 返回当前日期
select curdate();

-- 两个日期之间相差多少天，用前面的减后面的
select datediff('2001-1-1', '2001-1-5');

-- 计算你到现在活了多少天
select datediff(curdate(), '1999-11-11');

-- 得到当前的日期和时间
select now();

日期函数	说明
ADDDATE(d,n)	日期上加多少天得到新的日期
CURDATE()	得到现在的日期
DATEDIFF(d1,d2)	计算2个日期相差的天数
NOW()	得到现在的日期和时间

7 事务

概念：

• 如果一个业务中使用了多条SQL语句，必须使用事务，事务就是把这些SQL语句整合到一块，只有每条SQL语句都执行都成功了，才会进行事务提交，否则就会进行事务回滚，回到没有执行前的状态

事务就是要么所有的SQL语句全部执行成功，要么全部执行失败。

事务的四大特性

事务特性	含义
原子性（Atomicity）	一个事务所有的SQL语句是一个整体，不可分割。要么全部成功，要么全部失败。
一致性（Consistency）	对数据库中数据的操作状态在事务执行前后必须是一致的， 如：转账前2个人的总金额与转账后2个人的总金额是一致的
隔离性（Isolation）	每个表中同时有多个事务在执行，事务与事务之间是隔离的，不能相距影响
持久性（Durability）	如果一个事务执行成功，对数据库的影响是持久的，服务器关闭也是存在的。

事务：手动提交

1. 手动提交事务
2. 自动提交事务(默认)，默认情况下mysql认为每条DML语句(增删改)都是一个单独的事务，所以上面转账的案例是做为2个事务在执行

手动提交

功能	SQL语句
开启事务	start transaction
提交事务	commit
回滚事务	rollback

事务：自动提交

• 查询当前事务自动提交状态：select @@autocommit(1是自动提交默认就是1)
• 设置当前事务自动提交状态：set @@autocommit(设置为0，关闭自动提交)

事务的回滚点和执行原理图：

事务操作小结

事务的操作	MySQL操作事务的语句
开启事务	start transaction
提交事务	commit
回滚事务	rollback
设置回滚点	savepoint 名字
回到回滚点	rollback to 名字
查询事务的自动提交情况	select @@autocommit
设置事务的手动提交方式	set @@autocommit

8 并发访问

什么是事务的并发访问？

• 在同一时间，有多个用户开启了事务，同时访问了一个表中的数据，成为并发访问

会存在并发访问问题（三个问题）：

并发访问的问题	含义
脏读	一个事务读取到了另一个事务没有提交的数据
不可重复读	正常来说，一个事务多次读取同一条记录结果应该是一致的， 如果出现多次读取不一致的情况，称为不可重复读。通常是 因为一个事务在读取数据，另一个读取更新了这条记录导致。
幻读	一个事务在统计或查询的时候，2次出现查询的记录数不同。 通常是因为一个事务在统计，另一个事务插入或删除了记录导致。

因为会出现上面的三种并发访问的问题，所以我们需要避免这些问题的发生，
避免的方式就是设置事务的隔离级别。

隔离级别越高，并发出现的问题就越少。在关系型数据库中一共有四种隔离级别。

四种隔离级别

四种隔离级别起的作用

1. Read uncommitted (读未提交)： 隔离级别最低，三种并发访问的问题都是存在
2. Read committed (读已提交)：解决了脏读的问题，Oracle和SQL Server默认是这种隔离级别
3. Repeatable read (可重复读)：这是MySQL默认的隔离级别，可以解决脏读和不可重复读的问题。
4. Serializable (串行化)：所有的事务都有串行的方式执行，不存在并发的事务。