MySQL基础知识点
- 一、存取数据的演变史
- 二、数据库软件应用史
- 三、数据库的本质
- 四、数据库的分类
- 五、MySQL简介
- 六、MySQL基本使用
- 七、将MySQL服务制作成系统服务
- 八、密码相关操作
- 九、SQL与NoSQL
- 十、数据库重要概念
- 十一、基本SQL语句
- 十二、字符编码与配置文件
- 十三、数据库存储引擎
- 十四、创建表的完整语法
- 十五、字段类型之整型
- 十六、严格模式
- 十七、字段类型之浮点型
- 十八、字段类型之字符类型
- 十九、数字的含义
- 二十、字段类型之枚举（enum）与集合集合（set）
- 二十一、字段类型之日期类型
- 二十二、主题:字段约束条件
- 二十三、SQL语句查询关键字
- 二十四、前期数据准备
- 二十五、编写SQL语句的小技巧
- 二十六、查询关键字之where筛选
- 二十七、查询关键字之group by分组
- 二十八、查询关键字之having过滤
- 二十九、查询关键字之distinct去重
- 三十、查询关键字之order by排序
- 三十一、查询关键字之limit分页
- 三十二、查询关键字之regexp正则表达式
- 三十三、多表查询的思路
- 三十四、多表查询的两种方法
- 三十五、小知识点补充说明
- 三十六、可视化软件Navicat
- 三十七、多表查询练习题
- 三十八、python操作MySQL
- 三十九、pymysql补充说明
- 四十、SQL注入问题
- 四十一、视图
- 四十二、触发器
- 四十三、事务
- 四十四、存储过程
- 四十五、函数
- 四十六、流程控制
- 四十七、索引相关概念
- 四十八、索引数据结构
- 四十九、慢查询优化
- 五十、测试索引
- 五十一、数据库三大范式
一、存取数据的演变史
1、文本文件
使用文本文件存储数据的时候路径不固定数据格式不统一，一旦更换设备就容易出现无法使用的情况。
文件路径不固定:C:\aaa.txt D:\bbb.txt E:\ccc.txt
数据格式不统一:jason|123 jason$123 jason 123
2、软件开发目录规范
规定了数据应该保存在db目录下>>>:路径偏向统一。
db/user.txt
db/userinfo.txt
db/jason.json
db/jason（比如使用pocket模块存储对象）
但是数据格式还是没有得到统一，有文本、json格式、对象等数据存储格式。
3、数据库服务(重点)
特点
统一路径，统一操作方式
降低学习成本，提高开发效率
1.数据库管理软件的由来
基于我们之前所学，数据要想永久保存，都是保存于文件中，毫无疑问，一个文件仅仅只能存在于某一台机器上。
如果我们暂且忽略直接基于文件来存取数据的效率问题，并且假设程序所有的组件都运行在一台机器上，那么用文件存取数据，并没有问题。
很不幸，这些假设都是你自己意淫出来的，上述假设存在以下几个问题。。。。。。
①程序所有的组件就不可能运行在一台机器上
- 因为这台机器一旦挂掉则意味着整个软件的崩溃，并且程序的执行效率依赖于承载它的硬件，而一台机器机器的性能总归是有限的，受限于目前的硬件水平，就一台机器的性能垂直进行扩展是有极限的。
- 于是我们只能通过水平扩展来增强我们系统的整体性能，这就需要我们将程序的各个组件分布于多台机器去执行。
②数据安全问题
- 根据1的描述，我们将程序的各个组件分布到各台机器，但需知各组件仍然是一个整体，言外之意，所有组件的数据还是要共享的。但每台机器上的组件都只能操作本机的文件，这就导致了数据必然不一致。
- 于是我们想到了将数据与应用程序分离：把文件存放于一台机器，然后将多台机器通过网络去访问这台机器上的文件（用socket实现），即共享这台机器上的文件,共享则意味着竞争，会发生数据不安全，需要加锁处理。。。。
③并发
根据2的描述，我们必须写一个socket服务端来管理这台机器（数据库服务器）上的文件，然后写一个socket客户端，完成如下功能：
- 1.远程连接（支持并发）
- 2.打开文件
- 3.读写（加锁）
- 4.关闭文件
总结
我们在编写任何程序之前，都需要事先写好基于网络操作一台主机上文件的程序（socket服务端与客户端程序），于是有人将此类程序写成一个专门的处理软件，这就是mysql等数据库管理软件的由来，但mysql解决的不仅仅是数据共享的问题，还有查询效率，安全性等一系列问题，总之，把程序员从数据管理中解脱出来，专注于自己的程序逻辑的编写。
二、数据库软件应用史
1.单机游戏
数据存储于各个计算机的本地 无法共享
2.网络游戏
数据存储于网络中 可以共享(数据库服务)
数据库服务集群:提升数据的安全性
三、数据库的本质
1.站在底层原理的角度
数据库指的是操作数据的进程(一堆代码)
2.站在实际应用的角度
数据库指的是可视化操作界面(一些软件)
ps:以后不做特殊说明的情况下讲数据库其实指的是数据库软件
数据库软件本质也是CS架构的程序。这意味着所有的程序员其实都有资格编写一款数据库软件，只不过我们短时间内没这个水平。
四、数据库的分类
1.关系型数据库
特征1:拥有固定的表结构(字段名 字段类型)
id name pwd
特征2:数据之间可以建立数据库层面关系
用户表数据
豪车表数据
豪宅表数据
常见的关系型数据库：
MySQL、Oracle（中文叫甲骨文）、MariaDB、PostgreSQL、sql server、sqlite、db2、access
1.MySQL:开源免费，使用最广，性价比贼高。
主要用于大型门户，例如搜狗、新浪等，它主要的优势就是开放源代码。
因为开放源代码，这个数据库是免费的，他现在是甲骨文公司的产品。
2.Oracle:收费，使用成本较高。但是安全性也是最高的。
主要用于银行、铁路、飞机场等。该数据库功能强大，软件费用高。也是甲骨文公司的产品。
3.PostgreSQL:开源免费并且支持二次开发，兼容性极高。
4.MariaDB:跟MySQL是一个作者，开源免费。
MySQL被甲骨文收购后，MySQL 的原创人员有拉出另外一个分支，命名MariaDB 。该数据库被维基百科，Facebook 甚至 Google等技术巨头使用。
MariaDB 是一种可为 MySQL 提供插件替换功能的数据库服务器。开发人员的首要关注点是安全性，在每个版本发布时，开发人员还会合并所有 MySQL 的安全修补程序，并在需要时对其进行增强。
5.sqlite:小型数据库，主要用于本地测试
2.非关系型数据库
特征1:没有固定的表结构 数据存储采用K:V键值对的形式
{'name':'jason'}
{'username':'kevin','pwd':123}
特征2:数据之间无法建立数据库层面的关系
- 虽然不能建立数据库层面的关系，但是我们可以自己编写代码建立逻辑层面的关系。
redis、mongoDB、memcache
1.redis:目前最火、使用频率最高的非关系型数据库(缓存数据库)
虽然缓存数据库是基于内存做数据存取但是拥有持久化的功能，也就是有自动保存和写日志的功能。
2.mongoDB:文档型数据库 最像关系型数据库的非关系型数据库
主要用在爬虫以及大数据领域
3.memcache:已经被redis淘汰
虽然数据库软件有很多 但是操作方式大差不差 学会了一个几乎就可以学会所有
其中以MySQL最为典型
五、MySQL简介
1.版本问题
8.0:最新版
5.7:使用频率较高
5.6:学习推荐使用
ps:站在开发的角度使用哪个版本学习都没有关系，但是新版的软件封装了一些功能，不需要我们自己操作了，对于学习者来说应该亲自实践一下更好。
2、MySQL软件下载及安装见博客
MySQL软件安装教程（windows系统）：
https://www.cnblogs.com/zhihuanzzh/p/16915579.html
3.主要目录介绍
bin目录
存放启动文件
mysqld.exe(服务端) mysql.exe(客户端)
data目录
存放核心数据
my-default.ini
默认的配置文件
readme
软件说明
六、MySQL基本使用
当我们刚下载完MySQL的安装包解压到指定目录后其实已经可以使用MySQL了。只是需要开两个cmd窗口才能使用MySQL服务
打开步骤讲解：
1、先打开cmd窗口（cmd建议使用管理员身份打开）
2、切换到mysql的bin目录下先启动服务端
mysqld
3、保持窗口不关闭 重新打开一个新的cmd窗口
4、切换到mysql的bin目录下启动客户端
mysql
ps：直接使用mysql命令默认是游客模式 权限和功能都很少
使用用户名登陆的指令
mysql -u用户名 -p密码
管理员默认没有密码，连续回车即可（有密码就输入）
mysql -uroot -p密码
有些同学的电脑在启动服务端的时候就会报错，不要慌。拷贝报错信息，然后百度搜索：
mysql启动报错（后面粘贴错误信息）
七、将MySQL服务制作成系统服务
1、把bin目录添加到环境变量
先清空之前打开的cmd窗口,一定要把之前用cmd启动的服务端关闭(ctrl+c)
1.右键计算机，点击属性
2.点击高级系统设置
3.在弹出的窗口中点击环境变量
4.双击第二个内容框（系统变量）中的path
5.在弹出的新窗口中点击新建，然后输入bin文件夹的路径
ps：将MySQL的bin目录路径追加到环境变量中时，win7需要用；分割。
2、将MySQL服务制作成windows系统服务（相当于把MySQL服务制作成系统的守护进程）
将MySQL服务制作成windows系统服务后就不需要使用cmd开启MySQL服务端了。
1.如何查看系统服务
方式一：
同时按住ESC键+SHIFT键+CTRL键打开任务管理器，然后点击侧边栏中最后一个选项就可以查看系统服务
方式二：
同时按住win键和R键，输入services.msc，然后按回车，就能打开服务界面
2.制作MySQL的Windows服务
步骤一：以管理员身份打开cmd窗口
步骤二：输入指令
mysqld --install
3.首次添加MySQL服务后并不会自动启动，需要认为操作一下
打开方式一：选中MySQL服务右键，然后点击启动
打开方式二：使用cmd输入代码启动
开启后我们可以去服务窗口查看状态
3.如何卸载
步骤一：先关闭服务端
打开cmd，输入指令：net stop mysql
步骤二：移除系统服务
接着输入指令：mysqld --remove
八、密码相关操作
1.修改密码
方式1:mysqladmin
输入下列指令：
mysqladmin -u用户名 -p原密码 password 新密码
方式2:直接修改存储用户数据的表
如何查看存储用户数据的表
select * from mysql.user\G
这里如果不在末尾跟上\G读取的内容会出现格式错乱，跟上\G后可以一行行展示文件内容。
方式3:冷门操作 有些版本可能还不支持
set password=password('新密码') # 修改当前登录用户的密码
2.忘记密码的解决方法
方式1:卸载重新装
方式2:把data目录删除 拷贝同桌的目录
这里就相当于用你同桌的信息登陆
方式3:小把戏操作
1.关闭正常的服务端
net stop mysql
2.以跳过授权表的方式重启服务端(不校验密码)
mysqld --skip-grant-table
3.以管理员身份进入然后修改mysql.user表数据即可
mysql -uroot -p
update mysql.user set password=password('123') where Host='localhost' and User='root';
最后一行的代码的作用是设置root账号的密码为123
4.关闭服务端 然后以正常方式启动即可
九、SQL与NoSQL
数据库服务端是可以服务多种类型的客户端，客户端可以是自己开发的，也可以是python代码编写，也可以是java代码编写，但是这样就导致了数据库服务端操作会变得复杂。
SQL和NoSQL就相当于是数据库服务端规定了客户端操作服务端的数据时需要使用的语言。
SQL（操作关系型数据库的语言）
SQL结构化查询语言是一种数据库操作的非过程式编程语言，用于存取数据以及查询、更新和管理关系型数据库系统，一般脚本文件后缀为.sql。
NoSQL（操作非关系型数据库的语言）
NoSQL泛指非关系型数据库，主要用于针对超大规模和高并发的社交SNS类型网站的解决方案
ps:要想跟数据库交互就必须使用数据库指定的语言。
SQL有时候也指代关系型数据库。
NoSQL有时候也指代非关系型数据库。
SQL 和 NoSQL对比
复杂查询SQL数据库比较擅长；
SQL数据库不适合分层次的数据存储，NoSQL可以很好的实现数据的分层次存储，更适合大数据；
对于要求数据严格一致性的应用中，通常使用SQL，因为他稳定，能够保证数据操作的原子性和一致性；
而NoSQL对事务的处理能力有限，一般保证最终一致性；
SQL厂商支持，而NoSQL是社区支持；
性能对比中，NoSQL明显优于SQL数据库，一般NoSQL都充分的利用系统的内存资源；
NoSQL数据库开发方便，不用考虑数据关系和格式。
十、数据库重要概念
什么是数据库服务器
运行有DBMS服务端的计算机，该计算机对内存和硬盘要求都相对较高
什么是数据库管理系统（DataBase Management System 简称DBMS）
管理数据的套接字软件，CS架构
什么是库（DataBase，简称DB）
文件夹
什么是表
文件
什么是记录
一组数据构成一条记录，相当于文件中的一行内容，如1,jason,male,18
什么是数据（Data）
事物的状态
举例
强调:小白阶段为了更加方便的理解，做了以下比喻，本质其实有一点点的区别。
库 就相当于是 文件夹
表 就相当于是 文件夹里面的文件
记录 就相当于是 文件夹里面的文件中的一行行数据
验证指令
1.查看所有的库名称
show databases;
2.查看所有的表名称
show tables;
3.查看所有的记录
select * from mysql.user;
十一、基本SQL语句
注意事项
1.sql语句必须以分号结尾
2.sql语句编写错误之后不用担心 可以直接执行报错即可
基于库的增删改查指令
1.创建库
create database 库名;
库文件会创建在MySQL文件夹内的data文件夹内。
2.查看库
show databases; 查看所有的库名称
其中上图中放的information_schema存在于内存中
show create database 库名; 查看指定库信息
3.编辑库
alter database 库名 charset='utf8';
修改字符编码类型（默认情况是latinl——拉丁文）
4.删除库
drop database 库名;
基于表的增删改查
操作表之前需要先创建库库
create database db1;
然后切换操作库
use db1;
1.创建表
create table 表名(字段名 字段类型,字段名 字段类型);
2.查看表
show tables; 查看库下所有的表名称
show create table 表名; 查看指定表信息
describe 表名; 查看表结构
desc 表名;
ps:如果想跨库操作其他表 只需要在表名前加库名即可
desc mysql.user;
3.编辑表
alter table 表名 rename 新表名;
4.删除表
drop table 表名;
基于记录的增删改查
1.插入数据
insert into 表名 values(数据值1,数据值2);
有几个字段名插入几个值
2.查询数据
select * from 表名;
查询表中所有的数据
3.编辑数据
update 表名 set 字段名=新数据 where 筛选条件;
4.删除数据
delete from 表名;
后面不写where筛选的话就会删除表中的所有数据。
delete from 表名 where id=2;
十二、字符编码与配置文件
昨天我们讲解到MySQL文件夹内的my-default.ini文件是配置文件，这里我们讲一些简单的配置设置。
ps：修改配置文件后需要重新启动MySQL服务才能生效。
mysql配置文件的作用
1.影响服务端的启动（mysqld）
2.影响客户端的连接
1.\s——查看MySQL相关信息
登陆MySQL后使用\s命令后就可以查看当前用户、版本、编码、端口号。
MySQL5.6（5.6版本字符编码类型是拉丁文和utf8）及之前的版本编码需要人为统一 之后的版本已经全部默认统一。如果想要永久修改编码配置，需要操作配置文件。
2.配置文件修改默认字符编码类型
默认的配置文件是my-default.ini
但是我们在使用的时候不建议直接修改默认的配置文件，理想的操作应该是拷贝上述文件并重命名为my.ini，然后再my.ini中修改配置。
配置文件打开后分成两部分内容，一部分是注释，一部分是配置代码。注释部分在每一行的开头有井号（#）。
ps：配置文件中的注释可以有中文，但是配置代码中不能出现中文。
配置代码
直接拷贝字符编码相关配置即可无需记忆
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
ps:
1.utf8mb4能够存储表情 功能更强大
2.utf8与utf-8是有区别的 MySQL中只有utf8
修改了配置文件中关于[mysqld]的配置 需要重启服务端
3.利用配置文件免输入账号信息登陆
将管理员登录的账号密码直接写在配置文件中，之后使用mysql登录即可。
代码如下：
[mysql]
user='root'
password=123
十三、数据库存储引擎
1、存储引擎
数据库针对数据采取的多种存取方式。存储引擎是数据库底层软件组件，数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据库操作。不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能。使用不同的存储引擎，还可以获得特定的功能。现在许多不同的数据库管理系统都支持多种不同的数据引擎。MySQL的核心就是存储引擎。
2、查看存储引擎的语句
show engines;
3、需要了解的四个存储引擎
1.MyISAM
MySQL5.5（包括5.5版本）之前默认使用的的存储引擎。
存取数据的速度快，但是功能较少，安全性较低。
2.InnoDB
MySQL5.5之后默认使用的的存储引擎。
支持事务、行锁、外键等操作，虽然存取速度没有MyISAM快，但是安全性更高。
3.Memory
基于内存存取数据，仅用于临时表数据存取。
4.BlackHole
任何写入进去的数据都会立刻丢失。
4、了解不同存储引擎底层文件个数
用各个存储引擎创建不同的表，然后去查看文件个数（如果不是5.6版本可能文件个数会不一样）。
create database db2;
use db2;
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=memory;
create table t4(id int) engine=blackhole;
1.innodb有两个文件
.frm 表结构
.ibd 表数据(表索引也放一起了)
2.myisam有三个文件
.frm 表结构
.MYD 表数据
.MYI 表索引
3.memory只有一个文件
.frm 表结构
4.blackhole只有一个文件
.frm 表结构
创建了表之后我们加入数据
insert into t1 values(1);
insert into t2 values(1);
insert into t3 values(1);
insert into t4 values(1);
ps:MySQL默认忽略大小写
十四、创建表的完整语法
语法
create table 表名(
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件,
字段名 字段类型(数字) 约束条件
);
1.字段名和字段类型是必须的
2.数字和约束条件是可选的
3.约束条件也可以写多个 空格隔开即可
4.最后一行结尾不能加逗号
常见约束
not null 非空
default 默认值
auto_increment 自增长
primary key 主键 非空且唯一
ps:编写SQL语句报错之后不要慌，仔细查看提示，他会告知语句的什么位置疑似有错误，通过提示可以很快解决问题。
十五、字段类型之整型
概念
整型主要用于存储整数值，主要有以下几个字段类型：
这里的范围是根据字节大小计算得到的（一个字节等于八位二进制）。
整型经常被用到，比如 tinyint、smallint、int、bigint 。默认是有符号的，符号会占用一个字节（bytes），若只需存储无符号值，可增加 unsigned 属性。
int(M)中的 M 代表最大显示宽度，并不是说 int(1) 就不能存储数值10了，不管设定了显示宽度是多少个字符，int 都是占用4个字节，即int(5)和int(10)可存储的范围一样。
存储字节越小，占用空间越小。所以本着最小化存储的原则，我们要尽量选择合适的整型，例如：存储一些状态值或人的年龄可以用 tinyint ；主键列，无负数，建议使用 int unsigned 或者 bigint unsigned，预估字段数字取值会超过 42 亿，使用 bigint 类型。
验证整型默认是否携带正负号
需要先在配置文件中删除下列信息：
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
不删除的情况下就属于严格模式。
输入下列代码创建表（用上tinyint字段类型）并添加数据值
create table t5(id tinyint);
insert into t5 values(-129),(128);
结果是-128和127，也就意味着默认情况下是自带正负号的。
我们也可以用unsigned取消正负号
create table t6(id tinyint unsigned);
insert into t6 values(-129),(128),(1000);
取消正负号后最小就是0，最大255.
十六、严格模式
当我们在使用数据库存储数据的时候，如果数据不符合规范，应该直接报错而不是擅自修改数据，这样会导致数据的失真(没有实际意义)。
在测试整形字段是否自带正负号的时候，就出现了失真的情况。正常都应该报错。但是我们之前不小心改了配置文件。
使用下方代码可以查看是否启动了严格模式：
show variables like '%mode%';
1.临时修改
set session sql_mode='strict_trans_tables';
在当前客户端有效
set global sql_mode='strict_trans_tables';
在当前服务端有效
修改后的结果
2.永久修改
直接修改配置文件在mysqld下方加上如下代码：
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
十七、字段类型之浮点型
概念
浮点型主要有 float，double 两个，浮点型在数据库中存放的是近似值，例如float(6,3)，如果插入一个数123.45678，实际数据库里存的是123.457。
语法简介
float(20,10)
总共存储20位数，小数点后面占10位数。
定点型字段类型有 decimal 一个，主要用于存储有精度要求的小数。
对于声明语法 DECIMAL(M,D) ，自变量的值范围如下：
M是最大位数（精度），范围是1到65。可不指定，默认值是10。
D是小数点右边的位数（小数位）。范围是0到30，并且不能大于M，可不指定，默认值是0。
例如字段 salary DECIMAL(5,2)，能够存储具有五位数字和两位小数的任何值，因此可以存储在salary列中的值的范围是从-999.99到999.99。
代码验证精确度
float(20,10)
总共存储20位数 小数点后面占10
double(20,10)
总共存储20位数 小数点后面占10
decimal(20,10)
总共存储20位数 小数点后面占10
create table t7(id float(60,20));
create table t8(id double(60,20));
create table t9(id decimal(60,20));
insert into t7 values(1.11111111111111111111);
insert into t8 values(1.11111111111111111111);
insert into t9 values(1.11111111111111111111);
我们会发现存储在表中的数据精确度不一样，float字段类型的精确度最低，其次是double，decimal精确度最高。
三者的核心区别在于精确度不同
float < double < decimal
十八、字段类型之字符类型
概念
字符串类型也经常用到，常用的几个类型如下表：
其中 char 和 varchar 是最常用到的。char 类型是定长的，MySQL 总是根据定义的字符串长度分配足够的空间。当保存 char 值时，在它们的右边填充空格以达到指定的长度，当检索到 char 值时，尾部的空格被删除掉。varchar 类型用于存储可变长字符串（即变长），存储时，如果字符没有达到定义的位数，也不会在后面补空格。
char(M) 与 varchar(M) 中的的 M 表示保存的最大字符数，单个字母、数字、中文等都是占用一个字符。char 适合存储很短的字符串，或者所有值都接近同一个长度。例如，char 非常适合存储密码的 MD5 值，因为这是一个定长的值。对于字符串很长或者所要存储的字符串长短不一的情况，varchar 更加合适。
我们在定义字段最大长度时应该按需分配，提前做好预估，能使用 varchar 类型就尽量不使用 text 类型。除非有存储长文本数据需求时，再考虑使用 text 类型。
代码查看char和varchar的实际情况
create table t10(id int, name char(4));
create table t11(id int, name varchar(4));
insert into t10 values(1, 'jason1');
insert into t11 values(1, 'jason2');
ps:char_length()获取字段存储的数据长度
默认情况下MySQL针对char的存储会自动填充空格和删除空格
因此我们需要使用set global进行设置，在设置的时候需要把之前设置的指令也写上，否则会之前的指令就不生效了。
set global sql_mode='strict_trans_tables,pad_char_to_full_length';
接着使用
select char_length(name) from t10;
select char_length(name) from t11;
就能看到字符的长度变成了4和1
char和varchar对比
char
优势:整存整取 速度快
劣势:浪费存储空间
varchar
优势:节省存储空间
劣势:存取数据的速度较char慢
比如在存储下方的字符时，char字段类型使用空格填充，varchar就需要使用报头，记录每一次记录的字符的长度，而报头需要占用一个字节
jacktonyjasonkevintomjerry
1bytes+jack1bytes+tony1bytes+jason1bytes+kevin1bytes+tom1bytes+jerry
十九、数字的含义
- 数字在很多地方都是用来表示限制存储数据的长度，但是在整型中数字却不是用来限制存储长度，是用来设置展示长度的（即打印长度），默认情况下（也就是括号内不写数字）int括号内的参数是11。
create table t12(id int(3)); 不是用来限制长度
insert into t12 values(12345);
create table t13(id int(5) zerofill); 而是用来控制展示的长度
insert into t13 values(123),(123456789);
create table t14(id int);
"""以后写整型无需添加数字"""
二十、字段类型之枚举（enum）与集合集合（set）
enum枚举是多选一，像python布尔类型,
set集合是多选一或多
枚举
多选一
create table t15(
id int,
name varchar(32),
gender enum('male','female','others')
);
insert into t15 values(1,'tony','猛男');
insert into t15 values(2,'jason','male');
insert into t15 values(3,'kevin','others');
意思就是在给表添加记录的时候，枚举类型的值只能从创建时设定的值中选一个，否则都会报错。
集合
多选多(多选一)
create table t16(
id int,
name varchar(16),
hobbies set('basketabll','football','doublecolorball')
);
insert into t16 values(1,'jason','study');
insert into t16 values(2,'tony','doublecolorball');
insert into t16 values(3,'kevin','doublecolorball,football');
集合的选择条件是让我们在给定的数据值中选择一个或多个，如果一个也没有就会报错。
二十一、字段类型之日期类型
概念
MySQL支持的日期和时间类型有 YEAR 、TIME 、DATE 、DATETIME 、TIMESTAMP，几种类型比较如下：
涉及到日期和时间字段类型选择时，根据存储需求选择合适的类型即可。
datetime 年月日时分秒
date 年月日
time 时分秒
year 年
关于 DATETIME 与 TIMESTAMP 两种类型如何选用，可以按照存储需求来，比如要求存储范围更广，则推荐使用 DATETIME ，如果只是存储当前时间戳，则可以使用 TIMESTAMP 类型。不过值得注意的是，TIMESTAMP 字段数据会随着系统时区而改变但 DATETIME 字段数据不会。总体来说 DATETIME 使用范围更广。
实操代码
create table t17(
id int,
name varchar(32),
register_time datetime,
birthday date,
study_time time,
work_time year
);
insert into t17 values(1,'jason','2000-11-11 11:11:11','1998-01-21','11:11:11','2000');
ps:以后涉及到日期相关字段一般都是系统自动获取 无需我们可以操作
二十二、主题:字段约束条件
- 什么是字段约束
简而言之，字段约束就是将字段的内容定一个规则，我们要按照规则办事，常见的字段约束有下面几个。
- 字段约束的作用
1、保证数据的完整性
描述：我们有时候填表会发现有些是必填项，这里就是not null的作用，他要求这个表格不能为空，获取我们完整的信息。
2、保证数据的有效性
描述：在这里我们假设一个个场景，要是张三的电话号码是123×××××××45，那么李四的电话号码绝对不会和张三一摸一样，这里就体现出unique的作用了
- 常用的约束条件作用概述
| 约束条件 | 作用 |
|---|---|
| unsigned | 去掉正负号 |
| zerofill | 字段数据长度不够用0填充 |
| not null | 让字段数据不能为空 |
| default | 设置字段默认值 |
| unique | 设置字段数据唯一 |
| primary key | 主键，不能为空且唯一 |
一、无符号、零填充
无正负符号
存储记录的时候取消正负号，这时候就只能存储0~max的数据值。
关键字：unsigned
id int unsigned
字段数据长度不够用0填充
昨天学习后我们了解到，int字段括号内数字的作用是控制输出的长度，这里用上zerofill之后，如果输入333就会把前面空着的两个位置用0填充。
关键字：zerofill
id int(5) zerofill
二、非空
关键字：not null
作用描述：当我们默认情况下创建字段的时候，是可以输入null让值为空的，但是加上not null之后就不能空着不输入该字段的值了，但是我们输入''，这样的空信息却是可以在not null条件下输入进去的，并且在查看的时候该字段在该条记录对应的值就是空的。
create table t1(
id int,
name varchar(16)
);
insert into t1(id) values(1);
insert into t1(name) values('jason');
insert into t1(name,id) values('kevin',2);
我们发现默认情况下，也就是不设置字段约束的情况下，字段值是可以为空的，结果如下：
ps:所有字段类型不加约束条件的情况下默认都可以为空
create table t2(
id int,
name varchar(16) not null
);
insert into t2(id) values(1);
insert into t2(name) values('jason');
insert into t2 values(1,'');
insert into t2 values(2,null);
这里我们发现添加了字段约束后我们添加记录的时候如果出现空的值就会报错。
三、默认值
关键字：default 默认值
作用描述：当我们设置了默认值字段约束的时候，可以在后面设置默认值，在添加记录的时候如果输入了值，就会使用输入的值，否则就会用默认值填充(可以跟python中的关键字参数类比记忆)。
create table t3(
id int default 666,
name varchar(16) default '匿名'
);
insert into t3(id) values(1);
insert into t3(name) values('jason');
insert into t3 values(2,'kevin');
四、唯一值
关键字：unique
作用描述：分成单列唯一和多列唯一两种使用方式，单列唯一就很好理解，一个字段内的所有数据值不能重复。多列唯一的意思是设置多列唯一的几个字段中的数据组合起来不能相同。
单列唯一
'''单列唯一'''
create table t4(
id int unique,
name varchar(32) unique
);
insert into t4 values(1,'jason'),(2,'jason');
这里我们就是用两个相同的数据值测试唯一性。结果如下：
多列唯一
'''联合唯一'''
create table t5(
id int,
ip varchar(32),
port int,
unique(ip,port)
);
insert into t5 values(1,'127.0.0.1',8080),(2,'127.0.0.1',8081),(3,'127.0.0.2',8080);
insert into t5 values(4,'127.0.0.1',8080);
这里我们在添加第四条记录的时候会报错，原因就是我们的第四条记录跟第一条记录的ip和port的值组合起来之后重复了。
五、主键
关键字：primary key
作用描述：单从约束层面上而言主键相当于not null + unique(非空且唯一)
ps：主键分成单列主键和联合主键，联合主键跟多列唯一用法一样，但是用的较少。
代码验证主键作用
create table t6(
id int primary key,
name varchar(32)
);
insert into t6(name) values('jason');
insert into t6 values(1,'kevin');
insert into t6 values(1,'jerry');
结果如下：
InnoDB存储引擎规定一张表必须有且只有一个主键
InnoDB存储引擎规定了所有的表都必须有且只有一个主键(主键是组织数据的重要条件并且主键可以加快数据的查询速度)，因此当我们没有添加主键的时候InnoDB存储引擎会自动设置一个字段当作主键，有以下两种设置方式：
方式一：
当表中没有主键也没有其他非空切唯一的字段的情况下。
InnoDB会采用一个隐藏的字段作为表的主键，隐藏意味着无法使用，基于该表的数据查询只能一行行查找，速度很慢。
方式二：
当表中没有主键但是有其他非空且唯一的字段，那么会从上往下将第一个该字段自动升级为主键。
create table t7(
id int,
age int not null unique,
phone bigint not null unique,
birth int not null unique,
height int not null unique
);
ps：我们在创建表的时候应该有一个字段用来标识数据的唯一性，并且该字段通常情况下就是'id'(编号)字段。
id nid sid pid gid uid
0create 0table userinfo(
uid int primary key,
);
六、自增
功能简介
关键字：auto_increment
作用描述：在设置了自增的字段下，我们如果不输入对应的数据值，他会自动给值，同时也会在下一次记录数据值的时候自动把上一次的值加一当作结果绑定给下一次的记录，前提是这一次记录数据值的时候也没有给定值。
特殊情况1
把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，后续添加记录的时候如果没有给定id 的值，就会从200开始自增。
特殊情况2
把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，如果再次添加一条记录id的值也是200的话，就会报错。
特殊情况3
把表的id字段设置成自增，然后在添加第一条记录，我们把id的值设置成200，接着再添加几条数据值，让他自增，接着我们删除id为200的记录，我们就可以在添加记录的时候重新添加一条id字段值为200的记录。
ps：该约束条件不能单独出现，并且一张表中只能出现一次，主要就是配合主键一起用。
create table t8(
id int primary key,
name varchar(32)
);
create table t9(
id int primary key auto_increment,
name varchar(32)
);
insert into t8 values(200,'zzh');
insert into t8(name) values('jason');
结果如下：
特性描述
自增特性
自增不会因为数据的删除而回退 永远自增往前
如果自己设置了更大的数 则之后按照更大的往前自增
如果想重置某张表的主键值 可以使用
truncate t9;
清空表数据并重置主键。
七、外键前戏
1、外键的定义
外键是某个表中的一列，它包含在另一个表的主键中。
外键也是索引的一种，是通过一张表中的一列指向另一张表中的主键，来对两张表进行关联。
一张表可以有一个外键，也可以存在多个外键，与多张表进行关联。
2、外键的作用
外键的主要作用是保证数据的一致性和完整性，并且减少数据冗余。
主要体现在以下两个方面：
阻止执行
从表插入新行，其外键值不是主表的主键值便阻止插入。
从表修改外键值，新值不是主表的主键值便阻止修改。
主表删除行，其主键值在从表里存在便阻止删除(要想删除，必须先删除从表的相关行)。
主表修改主键值，旧值在从表里存在便阻止修改(要想修改，必须先删除从表的相关行)。
级联执行
这里需要提前设置
on update cascade
on delete cascade
关联主表和从表
主表删除行，连带从表的相关行一起删除。
主表修改主键值，连带从表相关行的外键值一起修改。
3、外键创建限制
父表必须已经存在于数据库中，或者是当前正在创建的表。
如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为父表定义主键。
外键中列的数目必须和父表的主键中列的数目相同。
两个表必须是 InnoDB 表，MyISAM 表暂时不支持外键。
外键列必须建立了索引，MySQL 4.1.2 以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显式建立。
外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如 int 和tinyint 可以，而 int 和 char 则不可以；
4、例子引入
我们需要一张员工表
id name age dep_name（部门名称） dep_desc（部门功能）
出现的问题：
1.表语义不明确(到底是员工表还是部门表)
2.存取数据过于冗余(浪费存储空间)，如部门名称和功能重复出现。
3.数据的扩展性极差（修改的时候会遇到很的问题，比较繁琐）
解决方案
将上述表一分为二
员工表：id name age
部门表：id dep_name dep_desc
上述的三个问题全部解决，但是员工跟部门之间没有了关系，接着我们引出外键来解决这个问题：
外键字段:用于标识数据与数据之间关系的字段。
八 、表关系的判断
表关系、数据关系其实意思是一样的，只是知识说法上有区分。
关系总共有四种：
- 一对多
- 多对多
- 一对一
- 没有关系
关系的判断可以采用'换位思考'原则
九、一对多关系
以员工表和部门表为例
1.先站在员工表的角度
问:一名员工能否对应多个部门
答:不可以
2.再站在部门表的角度
问:一个部门能否对应多名员工
答:可以
结论:一个可以一个不可以 那么关系就是'一对多'
针对'一对多'关系 外键字段建在'多'的一方
十、外键字段的建立
因为外键字段需要引入其他表中的字段，所以我们应该先创建没有外键的表，再创建含有外键的表。
小技巧:先定义出含有普通字段的表 之后再考虑外键字段的添加
create table emp(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep(id)
);
create table dep(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
1.创建表的时候一定要先创建被关联表
2.录入表数据的时候一定要先录入被关联表
3.修改数据的时候外键字段无法修改和删除
针对3有简化措施>>>:级联更新级联删除
create table emp1(
id int primary key auto_increment,
name varchar(32),
age int,
dep_id int,
foreign key(dep_id) references dep1(id)
on update cascade
on delete cascade
);
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
这里我们可以看到外键的key值是MUL，意为复合约束条件
- 外键其实是强耦合，不符合解耦合的特性，会导致维护难度和成本变高。
- 所以很多时候 实际项目中当表较多的情况 我们可能不会使用外键 而是使用代码建立逻辑层面的关系
十一、多对多关系
以书籍表与作者表为例
1.先站在书籍表的角度
问:一本书能否对应多个作者
答:可以
2.再站在作者表的角度
问:一个作者能否对应多本书
答:可以
结论:两个都可以，关系就是'多对多'
针对'多对多'关系不能在表中直接创建，需要新建第三张关系表。
create table book(
id int primary key auto_increment,
title varchar(32),
price float(5,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
phone bigint
);
create table book2author(
id int primary key auto_increment,
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade
);
十二、一对一关系
以用户表与用户详情表为例
1.先站在用户表的角度
问:一个用户能否对应多个用户详情
答:不可以
2.再站在用户详情表的角度
问:一个用户详情能否对应多个用户
答:不可以
结论:两个都可以 关系就是'一对一'或者没有关系
针对'一对一'外键字段建在任何一方都可以 但是推荐建在查询频率较高的表中
create table user(
id int primary key auto_increment,
name varchar(32),
detail_id int unique,
foreign key(detail_id) references userdetail(id)
on update cascade
on delete cascade
);
create table userdetail(
id int primary key auto_increment,
phone bigint
);
二十三、SQL语句查询关键字
关键词：select和from
作用描述：from的作用是指定需要查询信息的表，select的作用是指定需要查询的字段信息，根据字段信息获取内容
select
指定需要查询的字段信息
select * 查询所有字段的信息
select name 查询name字段的信息
select char_length(name) = 4
支持对字段做处理，比如这里是查找name字段中长度为4的记录
from
指定需要查询的表信息
from mysql.user
from t1
SQL语句中关键字的执行顺序和编写顺序并不是一致的 可能会错乱
eg:
select id,name from userinfo;
-
比如这里就是先执行from查找到表中的信息，然后再根据select跟后面的字段名称查找对应的id和name信息。
-
对应关键字的编写顺序和执行顺序我们没必要过多的在意，熟练之后会非常自然的编写，我们只需要把注意力放在每个关键字的功能上即可。
二十四、前期数据准备
- 为后面的查询准备的数据值，cv复制执行即可
create table emp(
id int primary key auto_increment,
name varchar(20) not null,
gender enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,gender,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','浦东第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
二十五、编写SQL语句的小技巧
- 针对select后面的字段名可以先用*占位往后写 最后再回来修改
select * from 表名称
- 在实际应用中select后面很少直接写* 因为*表示所有，当表中字段和数据都特别多的情况下非常浪费数据库资源（也就是说记录特别多的时候就会显示不下，造成数据显示混乱，但是可以用\G来分行查看）。
- SQL语句的编写类似于代码的编写，不是一蹴而就的，也需要反反复复的修修补补。
二十六、查询关键字之where筛选
关键词：where
使用描述：where后面跟上条件，根据条件查找信息
1.查询id大于等于3小于等于6的数据
select * from emp where id >= 3 and id <= 6; 支持逻辑运算符（如<>表示不等于等）
select * from emp where id between 3 and 6;
2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000); 支持成员运算
3.查询id小于3大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
4.查询员工姓名中包含字母o的员工姓名与薪资
因为查找条件不够精确的查询，我们称之为——模糊查询。
模糊查询的关键字：
- like
模糊查询的常用符号 ：
- %:匹配任意个数的任意字符
- _:匹配单个个数的任意字符
eg:
%o% o jason owen loo wwoww
%o o asdasdo asdo
_o_ aox wob iok
o_ oi ok ol
select * from emp where name like '%o%';
5.查询员工姓名是由四个字符组成的员工姓名与其薪资
select * from emp where name like '____';
select name,salary from emp where name like '____';
select * from emp where char_length(name) = 4;
select name,salary from emp where char_length(name)=4;
6.查询岗位描述为空的员工名与岗位名，针对null不能用等号，只能用is
select * from emp where post_comment=NULL; 不可以
select * from emp where post_comment is NULL; 可以
在MySQL中也有很多内置方法，我们可以通过查看帮助手册学习。同时我们可以使用如下方法，查看不同方法名的简介：
help 方法名
二十七、查询关键字之group by分组
group by介绍
关键词：group by
使用描述：group by 后面接上条件，根据条件进行筛选，同时除了5.6版本，别的版本需要把select后面的* 号改成固定的字段。
分组:按照指定的条件将单个单个的数据组成一个个整体
eg:
将班级学生按照性别分组
将全国人民按照民族分组
将全世界的人按照肤色分组
分组的目的是为了更好的统计相关数据
eg:
每个班级的男女比例
每个民族的总占比
每个部门的平均薪资
聚合函数
专门用于分组之后的数据统计
| 名称 | 含义 |
|---|---|
| avg | 表示求指定列的平均值 |
| min | 表示求指定列的最小值 |
| max | 表示求指定列的最大值 |
| sum | 表示求指定列的和 |
| count | 表示求指定列的总行数 |
例子
1.将员工数据按照部门分组
select * from emp group by post;
- 小知识点
输入上面的代码的时候，MySQL5.6默认不会报错，MySQL5.7及8.0默认都会直接报错。
原因是分组之后，select后面默认只能直接填写分组的依据，不能再写其他字段。
5.6版本如果需要修改成跟其他版本一样的话需要修改配置：
set global sql_mode='strict_trans_tables,only_full_group_by'
接着使用下方代码即可正确查询
select post from emp group by post;
select age from emp group by age;
ps：分组之后默认的最小单位就应该是组，而不应该再是组内的单个数据单个字段。
2.获取每个部门的最高工资
要不要分组我们完全可以从题目的需求中分析出来，尤其是出现关键字：每个、平均等词的时候。
select post,max(salary) from emp group by post;
针对sql语句执行之后的结果 我们是可以修改字段名称的 关键字as 也可以省略
select post as '部门',max(salary) as '最高薪资' from emp group by post;
3.一次获取部门薪资相关统计
select post,max(salary) '最高薪',min(salary) '最低薪',avg(salary) '平均薪资',sum(salary) '月支出' from emp group by post;
4.统计每个部门的人数
select post,count(id) from emp group by post;
5.统计每个部门的部门名称以及部门下的员工姓名
分组以外的字段无法直接填写，因此我们需要借助于方法。
group_concat作用：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
select post,name from emp group by post;
这里只能看到每个部门中的一个名字
select post,group_concat(name) from emp group by post;
使用了group_concat方法后就可以获得每个部门的所有名字
group_concat方法还可以获取多个字段的值。
select post,group_concat(name,age) from emp group by post;
group_concat方法获取的字段值可以拼接。
select post,group_concat(name,'|',age) from emp group by post;
select post,group_concat(name,'_NB') from emp group by post;
select post,group_concat('DSB_',name,'_NB') from emp group by post;
二十八、查询关键字之having过滤
关键词：having
作用描述：
- having与where本质是一样的 都是用来对数据做筛选
- 只不过where用在分组之前(首次筛选)，having用在分组之后(二次筛选)
题目：统计各部门年龄在30岁以上的员工平均工资 并且保留大于10000的数据
'''
稍微复杂一点的SQL 跟写代码几乎一样 也需要提前想好大致思路
每条SQL的结果可以直接看成就是一张表 基于该表如果还想继续操作则直接在产生该表的SQL语句上添加即可
'''
步骤1:先筛选出所有年龄大于30岁的员工数据
select * from emp where age > 30;
步骤2:再对筛选出来的数据按照部门分组并统计平均薪资
select post,avg(salary) from emp where age > 30 group by post;
步骤3:针对分组统计之后的结果做二次筛选
select post,avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;
二十九、查询关键字之distinct去重
关键词：distinct
作用描述：类似python中的集合的去重作用。
ps：去重有一个必须的条件，也是很容易被忽略的条件——数据必须一模一样才可以去重
select distinct id,age from emp;
当我们的去重关键字后跟上多个字段后，去重的对象就会变成这些字段组合起来之后的结果，如果组合起来的结果出现了重复，才会去重。
关键字后跟上单个字段的时候就会针对单个字段内的值进行去重。
select distinct age from emp;
select distinct age,post from emp;
三十、查询关键字之order by排序
概念讲解
关键词：order by
作用描述：关键词后面跟上字段名称，会根据字段的值进行排序，默认情况下是升序，字段名称后面跟上asc也是升序，字段名称后面跟上desc就会变成降序。
举例：
1.可以是单个字段排序
select * from emp order by age; 默认升序
select * from emp order by age asc; 默认升序(asc可以省略)
select * from emp order by age desc; 降序
2.也可以是多个字段排序
select * from emp order by age,salary desc;
先按照年龄升序排 相同的情况下再按照薪资降序排
统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
1.先筛选出所有年龄大于10岁的员工
select * from emp where age > 10;
2.再对他们按照部门分组统计平均薪资
select post,avg(salary) from emp where age > 10 group by post;
3.针对分组的结果做二次筛选
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000;
4.最后按照指定字段排序
select post,avg(salary) from emp where age > 10 group by post having avg(salary)>1000 order by avg(salary);
"""
当一条SQL语句中很多地方都需要使用聚合函数计算之后的结果 我们可以节省操作(主要是节省了底层运行效率 代码看不出来)
select post,avg(salary) as avg_salary from emp where age > 10 group by post having avg_salary>1000 order by avg_salary;
"""
三十一、查询关键字之limit分页
关键词：limit
作用描述：当表中数据特别多的情况下 我们很少会一次性获取所有的数据，limit的作用就是控制一次性显示多少跳记录。后面跟的数字就是规定显示几条数据。
ps：很多网站也是做了分页处理 一次性只能看一点点
select * from emp limit 5; 直接限制展示的条数
select * from emp limit 5,5; 从第5条开始往后读取5条
查询工资最高的人的详细信息
'''千万不要惯性思维 一看到工资最高就想着用分组聚合'''
select * from emp order by salary desc limit 1;
三十二、查询关键字之regexp正则表达式
关键词：regexp
作用描述：关键词后面接上正则表达式进行筛选。
SQL语句的模糊匹配如果用不习惯，也可以自己写正则批量查询。
ps：8.0之前的MySQL无法理解?运算符。
select * from emp where name regexp '^j.*?(n|y)$';
select * from emp where name regexp '^j.*(n|y)$';
三十三、多表查询的思路
表数据准备
create table dep(
id int primary key auto_increment,
name varchar(16)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'财务');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
多表查询
select * from emp,dep;
会将两张表中所有的数据对应一遍
这个现象我们也称之为'笛卡尔积' 无脑的对应没有意义，应该将有关系的数据对应到一起才合理。
笛卡尔积概念
首先，先简单解释一下笛卡尔积。现在，我们有两个集合A和B。
A = {0,1} B = {2,3,4}
集合 A×B 和 B×A的结果集就可以分别表示为以下这种形式：
A×B = {（0，2），（1，2），（0，3），（1，3），（0，4），（1，4）}；
B×A = {（2，0），（2，1），（3，0），（3，1），（4，0），（4，1）}；
可以得出A×B和B×A的笛卡尔积，但总体思路为用
以上A×B和B×A的结果就可以叫做两个集合相乘的‘笛卡尔积’。
从以上的数据分析我们可以得出以下两点结论：
1，两个集合相乘，不满足交换率，既 A×B ≠ B×A;
2，A集合和B集合相乘，包含了集合A中元素和集合B中元素相结合的所有的可能性。既两个集合相乘得到的新集合的元素个数是 A集合的元素个数 × B集合的元素个数;
基于笛卡尔积可以将部门编号与部门id相同的数据筛选出来
涉及到两张及以上的表时 字段很容易冲突 我们需要在字段前面加上表名来指定
select * from emp,dep where emp.dep_id=dep.id;
基于上述的操作就可以将多张表合并到一起然后一次性获取更多的数据
三十四、多表查询的两种方法
方式1:连表操作
inner join(内连接)
select * from emp inner join dep on emp.dep_id=dep.id;
只连接两张表中公有的数据部分
left join(左连接)
select * from emp left join dep on emp.dep_id=dep.id;
以左表为基准 展示左表所有的数据 如果没有对应项则用NULL填充
right join（右连接）
select * from emp right join dep on emp.dep_id=dep.id;
以右表为基准 展示右表所有的数据 如果没有对应项则用NULL填充
union（全连接）
select * from emp left join dep on emp.dep_id=dep.id
union
select * from emp right join dep on emp.dep_id=dep.id;
以左右表为基准 展示所有的数据 各自没有的全部NULL填充
学会了连表操作之后也就可以连接N多张表
思路:将拼接之后的表起别名当成一张表再去与其他表拼接，再起别名当一张表，再去与其他表拼接，其次往复即可。
方式2:子查询
将一条SQL语句用括号括起来当成另外一条SQL语句的查询条件
题目:求姓名是jason的员工部门名称
ps：子查询类似于我们日常生活中解决问题的方式>>>:分步操作
步骤1:先根据jason获取部门编号
select dep_id from emp where name='jason';
步骤2:再根据部门编号获取部门名称
select name from dep where id=200;
步骤3：整合上面的代码
select name from dep where id=(select dep_id from emp where name='jason');
很多时候多表查询需要结合实际情况判断用哪种，更多时候甚至是相互配合使用。
三十五、小知识点补充说明
1.concat与concat_ws
之前我们学习到group_concat()可以获得分组字段以外的字段数据，并支持拼接。
concat和concat_ws也是用于获取字段数据，并支持拼接。
concat用于分组之前的字段拼接操作
select concat(name,'$',sex) from emp;
concat_ws拼接多个字段并且中间的连接符一致
select concat_ws('|',name,sex,age,dep_id) from emp;
2.exists
sql1 exists (sql2);
select * from dep where exists (select * from emp where emp.id>1);
sql2有结果的情况下才会执行sql1，否则不执行sql1，返回空数据。
3.表相关SQL补充
alter table 表名 rename 新表名; # 修改表名
alter table 表名 add 字段名 字段类型(数字) 约束条件; # 添加字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已有字段;
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;# 修改字段
alter table 表名 change 旧字段名 新字段名 字段类型(数字) 约束条件;
alter table 表名 modify 字段名 新字段类型(数字) 约束条件;
alter table 表名 drop 字段名; # 删除字段
三十六、可视化软件Navicat
1、Navicat简介
Navicat 是一套快速、可靠并价格适宜的数据库管理工具, 适用于三种平台: Windows、mac OS 及 Linux。 可以用来对本机或远程的 MySQL、SQL Server、SQLite、Oracle 等数据库进行管理及开发。 专门为简化数据库的操作而设计。 它的设计符合数据库管理员、开发人员及中小企业的需求。 Navicat 是一个数据库图形化客户端软件, 让你可以以安全并且简单的方式对数据库进行操作。
能够操作数据库的第三方可视化软件有很多 其中针对MySQL最出名的就是Navicat
ps：Navicat是一个第三方数据库客户端，提供简单快捷的操作界面，本质还是使用sql语言对数据库进行操作。
2、安装教程
正版教程
官网地址：http://www.navicat.com.cn/
步骤一：
进入官网点击上方的产品
步骤二：
如果想要先使用可以点击免费使用，如果想要直接购买的可以点击购买，试用满十四天后就需要收费了。
步骤三：
参考破解版的安装教程
破解版教程
文件获取和简单教程可以参考这两个网站。
https://www.jb51.net/database/803226.html
https://www.jb51.net/softjc/823874.html
步骤一：
获取文件，下载后解压。然后双击运行navicat161_premium_cs_x64.exe
步骤二：
点击下一步，并同意规定，然后选择安装路径（安装路径后面要用到）
步骤三：
然后一路下一步一路到安装成功。
步骤四：
解压NavicatCracker.rar，然后打开解压得到的文件夹中的NavicatCracker v16.0.7.0 .exe
步骤五：
出现窗口后点击patch边上的两个点，把目录选择到navicat的安装目录
步骤六：
接着点击patch，如果成功了会有一个如下的弹窗，然后点击是
步骤七：
接着断网，然后点击generate获得激活码
步骤八：
然后打开navicat软件，点击注册，在激活码处输入获得的激活码
步骤九：
点击激活后会出现一个弹窗，点击手动激活，这时候会再出现一个弹窗
步骤十：
把新出现的弹窗中的请求码复制，然后粘贴到request code的窗口中
步骤十一：
点击Generate Activation Code按钮，这时候会在Activation Code中生成一串字符，把他复制然后粘贴到请求码的窗口下方
步骤十二：
点击激活就可以永久激活，但是不支持更新。
3、常用操作
1.连接数据库
步骤一：
打开软件后点击左上角的连接然后点击MySQL
步骤二：
输入用户名和密码，然后点击测试连接，试试看能不能连上，如果可以就点击确定创建连接
步骤三：
接下去会出现如下界面，这就表示连接数据库成功了
2.创建库
前置条件：连接数据库
步骤一：
选中连接，右键点击新建数据库，这时候会出现一个弹窗
步骤二：
填写数据库名称和字符集（就是字符编码类型，建议选utf8或utf8mb4），排序规则不要动，让他空着
步骤三：
点击确定就可以在左边看到新创的数据库了
3.创建表
步骤一：
双击一个数据库
步骤二：
点击表，然后右键点击新建表
步骤三：
这时候右边窗口会出现创建表的窗口，我们在内部可以更简单的创建表，创建表的时候需要设置上主键（使用tab可以换到下一行）
步骤四：
设置好了之后点击保存就可以创建出这个表了，我们可以在左边查看到表的信息
4.往表中添加记录
在左边双击表，就能开始添加表的记录
5.设置外键
需要在创建表的时候就进行设置
ps：还有很多实用的功能，可以自行探索，比如编写sql代码会出现提示可以用Tab补全，类似pycharm。
4.SQL语句注释语法
方式一：--
在一行代码的开头加上两个横杠就可以将其注释
注释快捷键就是用这种方式，使用ctrl+？是将代码注释，使用ctrl+shift+？是解开注释
方式二：#
在一行代码的开头加上一个井号就可以将其注释
方式三：\**\
这是sql代码中的多行注释
5.运行、转储SQL文件
在左侧选中一个数据库，然后右键点击运行/转储SQL文件就可以导入或是到处数据库文件
三十七、多表查询练习题
提示：
- 编写复杂的SQL不要想着一口气写完
- 一定要先明确思路 然后一步步写一步步查一步步补
题目：
1、查询所有的课程的名称以及对应的任课老师姓名
4、查询平均成绩大于八十分的同学的姓名和平均成绩
7、查询没有报李平老师课的学生姓名
8、查询没有同时选修物理课程和体育课程的学生姓名
9、查询挂科超过两门(包括两门)的学生姓名和班级
-- 1、查询所有的课程的名称以及对应的任课老师姓名
# 1.先确定需要用到几张表 课程表 分数表
# 2.预览表中的数据 做到心中有数
-- select * from course;
-- select * from teacher;
# 3.确定多表查询的思路 连表 子查询 混合操作
-- SELECT
-- teacher.tname,
-- course.cname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
# 1.先确定需要用到几张表 学生表 分数表
# 2.预览表中的数据
-- select * from student;
-- select * from score;
# 3.根据已知条件80分 选择切入点 分数表
# 求每个学生的平均成绩 按照student_id分组 然后avg求num即可
-- select student_id,avg(num) as avg_num from score group by student_id having avg_num>80;
# 4.确定最终的结果需要几张表 需要两张表 采用连表更加合适
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN (
-- SELECT
-- student_id,
-- avg(num) AS avg_num
-- FROM
-- score
-- GROUP BY
-- student_id
-- HAVING
-- avg_num > 80
-- ) AS t1 ON student.sid = t1.student_id;
-- 7、查询没有报李平老师课的学生姓名
# 1.先确定需要用到几张表 老师表 课程表 分数表 学生表
# 2.预览每张表的数据
# 3.确定思路 思路1:正向筛选 思路2:筛选所有报了李平老师课程的学生id 然后取反即可
# 步骤1 先获取李平老师教授的课程id
-- select tid from teacher where tname = '李平老师';
-- select cid from course where teacher_id = (select tid from teacher where tname = '李平老师');
# 步骤2 根据课程id筛选出所有报了李平老师的学生id
-- select distinct student_id from score where course_id in (select cid from course where teacher_id = (select tid from teacher where tname = '李平老师'))
# 步骤3 根据学生id去学生表中取反获取学生姓名
-- SELECT
-- sname
-- FROM
-- student
-- WHERE
-- sid NOT IN (
-- SELECT DISTINCT
-- student_id
-- FROM
-- score
-- WHERE
-- course_id IN (
-- SELECT
-- cid
-- FROM
-- course
-- WHERE
-- teacher_id = (
-- SELECT
-- tid
-- FROM
-- teacher
-- WHERE
-- tname = '李平老师'
-- )
-- )
-- )
-- 8、查询没有同时选修物理课程和体育课程的学生姓名(报了两门或者一门不报的都不算)
# 1.先确定需要的表 学生表 分数表 课程表
# 2.预览表数据
# 3.根据给出的条件确定起手的表
# 4.根据物理和体育筛选课程id
-- select cid from course where cname in ('物理','体育');
# 5.根据课程id筛选出所有跟物理 体育相关的学生id
-- select * from score where course_id in (select cid from course where cname in ('物理','体育'))
# 6.统计每个学生报了的课程数 筛选出等于1的
-- select student_id from score where course_id in (select cid from course where cname in ('物理','体育'))
-- group by student_id
-- having count(course_id) = 1;
# 7.子查询获取学生姓名即可
-- SELECT
-- sname
-- FROM
-- student
-- WHERE
-- sid IN (
-- SELECT
-- student_id
-- FROM
-- score
-- WHERE
-- course_id IN (
-- SELECT
-- cid
-- FROM
-- course
-- WHERE
-- cname IN ('物理', '体育')
-- )
-- GROUP BY
-- student_id
-- HAVING
-- count(course_id) = 1
-- )
-- 9、查询挂科超过两门(包括两门)的学生姓名和班级
# 1.先确定涉及到的表 分数表 学生表 班级表
# 2.预览表数据
-- select * from class
# 3.根据条件确定以分数表作为起手条件
# 步骤1 先筛选掉大于60的数据
-- select * from score where num < 60;
# 步骤2 统计每个学生挂科的次数
-- select student_id,count(course_id) from score where num < 60 group by student_id;
# 步骤3 筛选次数大于等于2的数据
-- select student_id from score where num < 60 group by student_id having count(course_id) >= 2;
# 步骤4 连接班级表与学生表 然后基于学生id筛选即可
SELECT
student.sname,
class.caption
FROM
student
INNER JOIN class ON student.class_id = class.cid
WHERE
student.sid IN (
SELECT
student_id
FROM
score
WHERE
num < 60
GROUP BY
student_id
HAVING
count(course_id) >= 2
);
三十八、python操作MySQL
需要用到一个第三方模块——pymysql，需要导入，通过pymysql模块就可以充当客户端存储数据了。
部分方法简介
| 参数 | 说明 |
|---|---|
| dsn | 数据源名称，给出该参数表示数据库依赖 |
| host=None | 数据库连接地址 |
| user=None | 数据库用户名 |
| password=‘’ | 数据库用户密码 |
| database=None | 要连接的数据库名称 |
| port=3306 | 端口号，默认为3306 |
| charset=‘’ | 要连接的数据库的字符编码（可以在终端登陆mysql后使用 \s 查看，如下图） |
| connect_timeout=10 | 连接数据库的超时时间，默认为10 |
| port=3306 | 端口号，默认为3306 |
代码实操
pymysql模块
pip3 install pymysql
import pymysql
# 1.连接MySQL服务端
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='976863429',
db='infor-1',
charset='utf8mb4',
autocommit=True # 针对增 删 改 自动确认
)
# 2.产生游标对象
# cursor = conn.cursor() # 括号内不填写额外参数 数据是元组 指定性不强 [(),()]
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # [{},{}]
# 3.编写SQL语句
sql = 'select * from teacher;' # 查询是可以
# sql = 'insert into teacher(tname) values("Jason老师");' # 插入不可以
# sql = 'update teacher set tname="tony老师" where tid=7;' # 修改不可以
# sql = 'delete from teacher where tid=7;' # 删除不可以
# sql = 'select * from score;'
# 4.发送SQL语句
affect_rows = cursor.execute(sql) # execute也有返回值 接收的是SQL语句影响的行数
print(affect_rows)
# conn.commit() # 针对 增 删 改 需要二次确认
# 5.获取SQL语句执行之后的结果
res1 = cursor.fetchall()
# res2 = cursor.fetchall()
# res3 = cursor.fetchall()
print(res1)
# print(res2)
# print(res3)
# res1 = cursor.fetchone()
# print(res1)
# res2 = cursor.fetchone()
# print(res2)
# res2 = cursor.fetchmany(2)
# print(res2)
# res1 = cursor.fetchall()
# print(res1)
# cursor.scroll(1,'relative') # 基于当前位置往后移动
# cursor.scroll(-1, 'relative') # 基于当前位置往后移动
# cursor.scroll(0,'absolute')
# res1 = cursor.fetchall()
# print(res1)
三十九、pymysql补充说明
1.获取数据
fetchall() 获取所有的结果
fetchone() 获取结果集的第一个数据
fetchmany() 获取指定数量的结果集
ps:注意三者都有类似于文件光标移动的特性
cursor.scroll(-1, 'relative') # 基于末尾移动
cursor.scroll(1,'relative') # 基于当前位置往后移动
cursor.scroll(0,'absolute') # 基于数据的开头往后移动
2.增删改查
autocommit=True # 针对增 删 改 自动确认(直接配置)
conn.commit() # 针对 增 删 改 需要二次确认(代码确认)
四十、SQL注入问题
SQL注入问题引入
我们在pycharm中使用pymysql模块执行mysql代码的时候会发现用占位符传输信息的时候若是按下方的代码输入内容，会出现下面的两个怪象。
sql = " insert into userinfo(name,pwd) values(%s,%s)" % (sign_name,sign_pwd)
sign_name = 'jason' -- kasdjksajd'
sign_pwd = ''
sign_name = 'xyz' or 1=1 -- aksdjasldj'
sign_pwd = ''
怪象1:输对用户名就可以登录成功
怪象2:不需要对的用户名和密码也可以登录成功
这时候pycharm会在报错信息中返回一个sql语句，内容如下：
select * from userinfo where name='jason' -- kasdjksajd' and pwd=''
select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd=''
讲解：
通过上面的语句，我们可以发现语句的中间出现了两个'--'，而这个--符号的意思是注释的作用，因此就相当于执行了--符号前面的语句，后面的内容成了注释内容，这种情况就叫做SQL注入。
SQL注入概念和解决方案
- 概念
SQL注入:利用特殊符号的组合产生特殊的含义，从而避开正常的业务逻辑
- 解决方案
针对上述的SQL注入问题，核心在于手动拼接了关键数据，交给execute处理即可
sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql, (username, password))
补充说明
我们也可以使用executemany来一次性处理多个数据，把多个数据填入下方的小括号的即可。
executemany(sql,[(),(),(),()...])
四十一、视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次直接使用，通常用于连表操作得到的结果。语句如下：
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
注意事项
1.视图的表只能用来查询不能做其他增删改操作
2.视图尽量少用，会跟真正的表产生混淆，从而干扰操作者（使用show tables查看表名称或是进行其他操作的时候跟普通的表没有区别）。
四十二、触发器
定义
触发器是由事件来触发某个操作。当数据库执行这些事件时，就会激活触发器来执行相应的操作。这些事件称为触发条件，在MySQL中，有UPDATE，INSERT，和DELETE（即对表进行增、删、改）。
主要有六种情况:增前、增后、删前、删后、改前、改后（BEFORE INSERT、BEFORE DELETE、BEFORE UPDATE、AFTER INSERT、AFTER DELETE、AFTER UPDATE）。
代码
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
1.触发器命名有一定的规律
tri_before_insert_t1
tri_after_delete_t2
tri_after_update_t2
讲解：
这里我们发现第一部分名称就是tirgger关键字的前三个字母，第二部分名称是出发的状态，这部分名称由两部分组成，最后的一部分名称我们可以自定义。
2.临时修改SQL语句的结束符
- 因为有些操作中需要使用分号，这时候就会出现冲突。
- 这里我们用delimiter关键字修改结束符号，通过这种方式达到目的。
触发器实际应用
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #no代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象，如果新纪录中success的值为no就会把数据也记录到errlog表中
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ; # 结束之后记得再改回来，不然后面结束符就都是$$了
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('kevin','0755','ls -l /etc',NOW(),'yes'),
('kevin','0755','cat /etc/passwd',NOW(),'no'),
('kevin','0755','useradd xxx',NOW(),'no'),
('kevin','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
select * from errlog;
# 查看所有的触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;
四十三、事务
MySQL提供两种事务型存储引擎InnoDB和NDB cluster及第三方XtraDB、PBXT
事务的四大特性(ACID)
- A:原子性
指事务是一个不可再分割的工作单位，事务中的操作要么都发生，要么都不发生。
特点：
1、事务是一个完整的操作，事务的各元素是不可分的。
2、事务中的所有元素必须作为一个整体提交或回滚。
3、如果事务中的任何元素失败，则整个事务将失败。
举例：
比如转账的时候，如果对方接收失败了，钱应该退回自己的账户中。让双方的数据都回到转账前的状态。
- C:一致性
指在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。一致性与原子性是密切相关的。
特点：
1、当事务完成时，数据必须处于一致状态。
2、在事务开始前，数据库中存储的数据处于一致状态。
3、在正在进行的事务中，数据可能处于不一致的状态。
4、当事务成功完成时，数据必须再次回到已知的一致状态。
举例：
对银行转帐事务，不管事务成功还是失败，应该保证事务结束后表中A和B的存款总额跟事务执行前一致。
- I:隔离性
指在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间（多个事务之间彼此不干扰）。
特点：
1、对数据进行修改的所有并发事务是彼此隔离的，表明事务必须是独立的，它不应以任何方式依赖于或影响其他事务。
2、修改数据的事务可在另一个使用相同数据的事务开始之前访问
3、这些数据，或者在另一个使用相同数据的事务结束之后访问这些数据
- D:持久性
持久性也称永久性，指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，并不会被回滚。
特点：
1、指不管系统是否发生故障，事务处理的结果都是永久的。
2、一旦事务被提交，事务的效果会被永久地保留在数据库中。
代码
transaction是事务功能的关键字
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('kevin',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
事务相关关键字
事务(transaction)
回退(rollback)
提交(commit)
保留点(savepoint)
为了支持回退部分事务处理，必须能在事务处理块中合适的位置放置占位符，这样如果需要回退可以回退到某个占位符(保留点)
创建占位符可以使用savepoint
savepoint sp01;
回退到占位符地址
rollback to sp01;
保留点在执行rollback或者commit之后自动释放
事务之间的相互影响
1、脏读：一个事务读取了另一个事务未提交的数据，而这个数据是有可能回滚的。
2、不可重复读：一个事务内两个相同的查询却返回了不同数据。这是由于查询时系统中其他事务修改的提交而引起的。
3、幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有没有修改的数据行，就好象发生了幻觉一样。
4、丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
设置隔离级别语句：
set transaction isolation level 级别
查询全局事务隔离级别
show global variables like '%isolation%';
SELECT @@global.tx_isolation;
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较
例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
username
create_version
delete_version
jason 1
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
username
create_version
delete_version
jason 1
2
jason01 2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
username
create_version
delete_version
jason01 2
3
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
四十四、存储过程
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合,调用存储过程可以简化应用开发人员的很多工作,减少数据在数据库和应用服务器之间的传输,对于提高数据处理的效率是有好处的。
可以看成是python中的自定义函数。
# 无参函数
delimiter $$
create procedure p1()
begin
select * from cmd;
end $$
delimiter ;
# 调用
call p1()
# 有参函数
delimiter $$
create procedure p2(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select * from cmd where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
"""
查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;
"""
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
# 1、直接在mysql中调用
set @res=10 # res的值是用来判断存储过程是否被执行成功的依据，所以需要先定义一个变量@res存储10
call p1(2,4,10); # 报错
call p1(2,4,@res);
# 查看结果
select @res; # 执行成功，@res变量值发生了变化
# 2、在python程序中调用
pymysql链接mysql
产生的游表cursor.callproc('p1',(2,4,10)) # 内部原理：@_p1_0=2,@_p1_1=4,@_p1_2=10;
cursor.excute('select @_p1_2;')
四十五、函数
可以看成是python中的内置函数
ps:可以通过help 函数名 查看帮助信息!
1.移除指定字符
Trim、LTrim、RTrim
2.大小写转换
Lower、Upper
3.获取左右起始指定个数字符
Left、Right
4.返回读音相似值(对英文效果)
Soundex
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff计算两个日期差值
四十六、流程控制
# 分支结构
declare i int default 0;
IF i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
# 循环结构
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT num ;
SET num = num + 1 ;
END WHILE ;
四十七、索引相关概念
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容
2）让获取的数据更有目的性，从而提高数据库检索数据的性能
MySQL索引主要有两种结构：B+Tree索引和Hash索引。
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构
* primary key
* unique key
* index key
1.上述的三个key都可以加快数据查询
2.primary key和unique key除了可以加快查询本身还自带限制条件而index key很单一就是用来加快数据查询
3.外键不属于索引键的范围 是用来建立关系的 与加快查询无关
索引加快查询的本质
id int primary key auto_increment,
name varchar(32) unique,
province varchar(32)
age int
phone bigint
select name from userinfo where phone=18818888888; # 一页页的翻
select name from userinfo where id=99999; # 按照目录确定页数找
索引可以加快数据查询 但是会降低增删的速度
通常情况下我们频繁使用某些字段查询数据
为了提升查询的速度可以将该字段建立索引
聚集索引(primary key)
主键、主键索引
辅助索引(unique,index)
除主键意外的都是辅助索引，辅助索引其实本质上也是用的聚集索引的数据来查询的（用的主键的数据）
覆盖索引
select name from user where name='jason';
所谓覆盖索引就是条件中的字段名和查看的字段名称相同
非覆盖索引
select age from user where name='jason';
四十八、索引数据结构
索引的底层其实是树结构>>>:树是计算机底层的数据结构
树有很多中类型
二叉树、b树、b+树、B*树......
二叉树
二叉树里面还可以细分成很多领域，我们简单的了解即可。
二叉树的特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。
B树（B-Tree）
MySQL的数据是存储在磁盘文件中的，查询数据时需要先把磁盘中的数据加载到内存中，磁盘IO操作非常耗时，所以我们优化的重点就是尽量减少磁盘IO操作，所以，我们应当尽量减少从磁盘中读取数据的次数。另外，从磁盘中读取数据时，都是按照磁盘块来读取的，并不是一条一条的读。如果我们能把尽量多的数据放进磁盘块中，那一次磁盘读取操作就会读取更多数据，那我们查找数据的时间也会大幅度降低。
如果我们用树这种数据结构作为索引的数据结构，那我们每查找一次数据就需要从磁盘中读取一个节点，也就是我们说的一个磁盘块。我们都知道平衡二叉树可是每个节点只存储一个键值和数据的。那说明什么？说明每个磁盘块仅仅存储一个键值和数据！那如果我们要存储海量的数据呢？
可以想象到二叉树的节点将会非常多，高度也会极其高，我们查找数据时也会进行很多次磁盘IO，我们查找数据的效率将会极低！
为了解决平衡二叉树的这个弊端，B树应运而生， B树是一种多叉平衡查找树，主要的特点是：
1、叶子节点都在同一层，叶子节点没有指针连接
2、B树的节点中存储着多个元素，每个内节点有多个分叉
3、节点中的元素包含键值和数据，节点中的键值从大到小排列
4、所有的节点都可以存放完整的数据
下面模拟下查找key为27的data的过程：
存在的一些问题：
- B树中每个节点中包含key值以及data值，而每一个节点的存储空间是有限的(MySQL默认16K)，如果data中存放的数据较大时，将会导致每个节点能存储的key的数量很小，所以当数据量很多，且每行数据量很大的时候，同样会导致树的高度变得很高，增大查询时的磁盘IO次数，进而影响查询效率。
- 不支持范围查询的快速查找，而在实际的应用中，数据库范围查询的频率非常高，以下的一种情况是我查找10和35之间的数据，查找到15之后，需要回到根节点重新遍历查找，需要从根节点进行多次遍历，查询效率有待提高。
B+\B*树
只有叶子节点才会存放真正的数据 其他节点只存放索引数据
B+树
B+叶子节点增加了指向其他叶子节点的指针
对比B树和B+树，我们发现二者主要存在以下几点不同的地方：
- 数据都存放在叶子节点中
- 非叶子节点只存储键值信息，不再存储数据
- 所有叶子节点之间都有一个指针，指向下一个叶子节点,而且叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表
等值查询
下面模拟下查找key为9的data的过程：
范围查询
下面模拟下查找key的范围为9到26这个范围的data的过程：
从上面的结果，我们可以知道B+树作为索引结构带来的好处：
- 磁盘IO次数更少
- 数据遍历更为方便
- 查询性能更稳定
由于B+树优秀的结构特性，在MySQL中，存储引擎MyISAM和InnoDB的索引就采用了B+树的数据结构。
B*树
B*叶子节点和枝节点都有指向其他节点的指针
ps：
- 辅助索引在查询数据的时候最会还是需要借助于聚集索引
- 辅助索引叶子节点存放的是数据的主键值
- 有时候就算采用索引字段查询数据 也可能不会走索引!!!
索引失效的几种情况
1.前导模糊查询不能利用索引(like '%XX'或者like '%XX%')
'A%'就可以正常使用索引
2.如果mysql估计使用全表扫描要比使用索引快,则不使用索引
3.OR前后存在非索引的列，索引失效
如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
4.普通索引的不等于不会走索引；如果是主键，则还是会走索引；如果是主键或索引是整数类型，则还是会走索引
5.组合索引最左前缀
如果组合索引为：(name,email)
name and email -- 使用索引
name -- 使用索引
email -- 不使用索引
6.is null可以使用索引，is not null无法使用索引
最好在设计表时设置NOT NULL约束，比如将INT类型的默认值设为0，将字符串默认值设为''。
7.计算、函数导致索引失效另外一种情况
#使用到了索引
explain select * from student_info where name like 'li%';
#未使用索引，花费时间更久
explain select * from student_info where LEFT(name,2)='li';
扩展：
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
#不会使用name的索引
explain select * from student_info where name=123;
#使用到索引
explain select * from student_info where name='123';
如上，name字段是VARCAHR类型的，但是比较的值是INT类型的，name的值会被隐式的转换为INT类型再比较，中间相当于有一个将字符串转为INT类型的函数。这也相当于是函数导致的索引失效。
8.字符集不统一
统一使用utf8mb4( 5.5.3 版本以上支持 ) 兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。。
ps：最好能记三个左右的特殊情况
四十九、慢查询优化
我们使用explain方法来查看语句的索引扫描类型。
1、Explain是什么？
- 主键自动建立唯一索引。
- 频繁作为查询条件的字段应该创建索引。
- 查询中与其他表关联的字段，外键关系建立索引。
- 单键/组合索引的选择问题，组合索引性价比更高。
- 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。
- 查询中统计或者分组字段。
2、Explain能干什么？
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引可以被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
3、怎么用
- explain + SQL
- 执行计划包含的信息
+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+-----+---------+------+------+-------+
4、常见的索引扫描类型:
1）index
2）range
3）ref
4）eq_ref
5）const
6）system
7）null
索引扫描类型详解
-
生产中,mysql在使用全表扫描时的性能是极其差的，所以MySQL尽量避免出现全表扫描
-
从上到下，性能从最差到最好，我们认为至少要达到range级别
index：Full Index Scan，index与ALL区别为index类型只遍历索引树。
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<,>查询。
| mysql> alter table city add index idx_city(population); | |
|---|---|
| mysql> explain select * from city where population>30000000; |
ref：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行。
| mysql> alter table city drop key idx_code; | |
|---|---|
| mysql> explain select * from city where countrycode='chn'; | |
| mysql> explain select * from city where countrycode in ('CHN','USA'); | |
| mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA'; |
eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件A
| join B | |
|---|---|
| on A.sid=B.sid |
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量
mysql> explain select * from city where id=1000;
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。
mysql> explain select * from city where id=1000000000000000000000000000;
Extra（扩展）
Using temporary
Using filesort 使用了默认的文件排序（如果使用了索引，会避免这类排序）
Using join buffer
如果出现Using filesort请检查order by ,group by ,distinct,join 条件列上没有索引
mysql> explain select * from city where countrycode='CHN' order by population;
当order by语句中出现Using filesort，那就尽量让排序值在where条件中出现
| mysql> explain select * from city where population>30000000 order by population; | |
|---|---|
| mysql> select * from city where population=2870300 order by population; |
key_len: 越小越好
- 前缀索引去控制
rows: 越小越好
五十、测试索引
准备
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,'jason','male',concat('jason',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
# 由于这里要加三百万调记录要很久
#3. 查看存储过程
show create procedure auto_insert1\G
#4. 调用存储过程
call auto_insert1();
# 表没有任何索引的情况下
select * from s1 where id=30000;
# 避免打印带来的时间损耗
select count(id) from s1 where id = 30000;
select count(id) from s1 where id = 1;
# 给id做一个主键
alter table s1 add primary key(id); # 速度很慢
select count(id) from s1 where id = 1; # 速度相较于未建索引之前两者差着数量级
select count(id) from s1 where name = 'jason' # 速度仍然很慢
"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快
select count(id) from s1 where id > 1; # 速度相较于id = 1慢了很多
select count(id) from s1 where id >1 and id < 3;
select count(id) from s1 where id > 1 and id < 10000;
select count(id) from s1 where id != 3;
alter table s1 drop primary key; # 删除主键 单独再来研究name字段
select count(id) from s1 where name = 'jason'; # 又慢了
create index idx_name on s1(name); # 给s1表的name字段创建索引
select count(id) from s1 where name = 'jason' # 仍然很慢！！！
"""
再来看b+树的原理，数据需要区分度比较高，而我们这张表全是jason，根本无法区分
那这个树其实就建成了“一根棍子”
"""
select count(id) from s1 where name = 'xxx';
# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
select count(id) from s1 where name like 'xxx';
select count(id) from s1 where name like 'xxx%';
select count(id) from s1 where name like '%xxx'; # 慢 最左匹配特性
# 区分度低的字段不能建索引
drop index idx_name on s1;
# 给id字段建普通的索引
create index idx_id on s1(id);
select count(id) from s1 where id = 3; # 快了
select count(id) from s1 where id*12 = 3; # 慢了 索引的字段一定不要参与计算
drop index idx_id on s1;
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx';
# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
create index idx_name on s1(name);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 并没有加速
drop index idx_name on s1;
# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度
create index idx_id on s1(id);
select count(id) from s1 where name='jason' and gender = 'male' and id = 3 and email = 'xxx'; # 快了 先通过id已经讲数据快速锁定成了一条了
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 慢了 基于id查出来的数据仍然很多，然后还要去比较其他字段
drop index idx_id on s1
create index idx_email on s1(email);
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 快 通过email字段一剑封喉
1、联合索引
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx';
# 如果上述四个字段区分度都很高，那给谁建都能加速查询
# 给email加然而不用email字段
select count(id) from s1 where name='jason' and gender = 'male' and id > 3;
# 给name加然而不用name字段
select count(id) from s1 where gender = 'male' and id > 3;
# 给gender加然而不用gender字段
select count(id) from s1 where id > 3;
# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
create index idx_all on s1(email,name,gender,id); # 最左匹配原则，区分度高的往左放
select count(id) from s1 where name='jason' and gender = 'male' and id > 3 and email = 'xxx'; # 速度变快
总结:上面这些操作，你感兴趣可以敲一敲，不感兴趣你就可以不用敲了，权当看个乐呵。理论掌握了就行了
慢查询日志
设定一个时间检测所有超出该时间的sql语句，然后针对性的进行优化！
2、全文检索
使用Mysql全文检索fulltext的先决条件
表的类型必须是MyISAM
建立全文检索的字段类型必须是char,varchar,text
MySQL的全文检索功能MYISAM存储引擎支持而InnoDB存储引擎不支持
一般在创建表的时候启用全文检索功能
create table t1(
id int primary key auto_increment,
content text
fulltext(content)
)engine=MyISAM;
# match括号内的值必须是fulltext括号中定义的(单个或者多个)
select content from t1 where match(content) against('jason')
'''上述语句可以用like实现但是查询出来的结果顺序不同 全文检索会以文本匹配的良好程度排序数据再返回效果更佳'''
# 查询扩展
select note_text from productnotes where Math(note_text) Against('jason' with query expansion);
"""
返回除jason外以及其他jason所在行相关文本内容行数据
eg:
jason is handsome and cool,every one want to be cool,tony want to be more handsome;
二三句虽然没有jason关键字 但是含有jason所在行的cool和handsome
"""
# 布尔文本搜索
即使没有定义fulltext也可以使用，但是这种方式非常缓慢性能低下
select note_text from productnotes where Match(note_text) Against('jason' in boolean mode);
# 注意事项
1.三个及三个以下字符的词视为短词，全文检索直接忽略且从索引中排除
2.MySQL自身自带一个非用词列表，表内词默认均被忽略(可以修改该列表)
3.出现频率高于50%的词自动作为非用词忽略，该规则不适用于布尔搜索
4.针对待搜索的文本内容不能少于三行，否则检索不返回任何结果
5.单引号默认忽略
3、插入数据
数据库经常被多个用户访问，insert操作可能会很耗时(特别是有很多索引需要更新的时候)而且还可能降低等待处理的select语句性能
如果数据检索是最重要的(一般都是)，则可以通过在insert与into之间添加关键字low_priority指示MySQL降低insert语句优先级
insert low_priority into
insert还可以将一条select语句的结果插入表中即数据导入:insert select
eg:想从custnew表中合并数据到customers表中
insert into customers(contact,email) select contact,email from custnew;
4、更新数据
如果使用update语句更新多列值，并且在更新这些列中的一列或者多列出现一个错误会导致整个update操作被取消，如果想发生错误也能继续执行没有错误的更新操作可以采用
update ignore custmoers ...
"""
update ignore set name='jason1',id='a' where id=1;
name字段正常修改
update set name='jason2',id='h' where id=1;
全部更新失败
"""
5、删除数据
- delete语句从表中删除数据，甚至可以是所有数据但是不会删除表本身
- 并且如果想从表中删除所有的行不要使用delete可以使用truncate速度更快并且会重置主键值(实际是删除原来的表并重新创建一个表而不是逐行删除表中的数据)
6、主键
查看当前表主键自增到的值(表当前主键值减一)
select last_insert_id();
7、外键
MySQL存储引擎可以混用，但是外键不能跨引擎即使用一个引擎的表不能引用具有使用不同引擎表的外键
8、重命名表
rename关键字可以修改一个或者多个表名
rename table customer1 to customer2;
rename table back_cust to b_cust,
back_cust1 to b_cust1,
back_cust2 to b_cust2;
9、安全管理
1.创建用户
create user 用户名 identified by '密码';
"""修改密码"""
set password for 用户名 = Password('新密码');
set password = Password('新密码'); # 针对当前登录用户
2.重命名
rename user 新用户名 to 旧用户名;
3.删除用户
drop user 用户名;
4.查看用户访问权限
show grants for 用户名;
5.授予访问权限
grant select on db1.* to 用户名;
# 授予用户对db1数据库下所有表使用select权限
6.撤销权限
revoke select on db1.* from 用户名;
"""
整个服务器
grant all/revoke all
整个数据库
on db.*
特定的表
on db.t1
"""
10、隔离级别
在SQL标准中定义了四种隔离级别，每一种级别都规定了一个事务中所做的修改
InnoDB支持所有隔离级别
set transaction isolation level 级别
1.read uncommitted(未提交读)
事务中的修改即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，这一现象也称之为"脏读"
2.read committed(提交读)
大多数数据库系统默认的隔离级别
一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的，这种级别也叫做"不可重复读"
3.repeatable read(可重复读) # MySQL默认隔离级别
能够解决"脏读"问题，但是无法解决"幻读"
所谓幻读指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题
4.serializable(可串行读)
强制事务串行执行，很少使用该级别
11、锁
读锁(共享锁)
多个用户同一时刻可以同时读取同一个资源互不干扰
写锁(排他锁)
一个写锁会阻塞其他的写锁和读锁
死锁
1.多个事务试图以不同的顺序锁定资源时就可能会产生死锁
2.多个事务同时锁定同一个资源时也会产生死锁
# Innodb通过将持有最少行级排他锁的事务回滚
12、事务日志
事务日志可以帮助提高事务的效率
存储引擎在修改表的数据时只需要修改其内存拷贝再把该修改记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘
事务日志采用的是追加方式因此写日志操作是磁盘上一小块区域内的顺序IO而不像随机IO需要次哦按的多个地方移动磁头所以采用事务日志的方式相对来说要快的多
事务日志持久之后内存中被修改的数据再后台可以慢慢刷回磁盘，目前大多数存储引擎都是这样实现的，通常称之为"预写式日志"修改数据需要写两次磁盘
13、MVCC多版本控制
MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较
例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
username create_version delete_version
jason 1
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
username create_version delete_version
jason 1 2
jason01 2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
username create_version delete_version
jason01 2 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""
14、转换表的引擎
主要有三种方式，并各有优缺点!
# 1.alter table
alter table t1 engine=InnoDB;
"""
适用于任何存储引擎 但是需要执行很长时间 MySQL会按行将数据从原表赋值到一张新的表中，在复制期间可能会消耗系统所有的IO能力，同时原表会加读锁
"""
# 2.导入导出
"""
使用mysqldump工具将数据导出到文件，然后修改文件中相应的SQL语句
1.引擎选项
2.表名
"""
# 3.insert ... select
"""
综合了第一种方案的高效和第二种方案的安全
1.先创建一张新的表
2.利用insert ... select语法导数据
数据量不大这样做非常合适 数据量大可以考虑分批处理 针对每一段数据执行事务提交操作避免产生过多的undo
"""
ps:上述操作可以使用pt-online-schema-change(基于facebook的在线schema变更技术)工具，简单方便的执行上述过程
五十一、数据库三大范式
1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线；也就是说：每列的值具有原子性，不可再分割。
2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得，满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键，那么主键以外的列必须完全依赖于主键；如果表是复合主键，那么主键以外的列必须完全依赖于主键，不能仅依赖主键的一部分。
3、第三范式(3NF)是在第二范式的基础上建立起来的，即满足第三范式必须要先满足第二范式。第三范式(3NF)要求：表中的非主键列必须和主键直接相关而不能间接相关；也就是说：非主键列之间不能相关依赖。
