目录
初识数据库
数据库演变过程
#随意存到一个文件中的数据格式也是千差万别的，取决于我们的自己
# 小李，|分割
jason|123|NB
# 小王，-分割
egon-123-DBJ
# 小红，~分割
tank~123~hecha
#所以使用数据库统一储存的格式
软件开发目录规范
#软件开发目录规范,限制了储存数据的具体位置
bin #启动文件
conf #配置文件
core #项目主代码
db #存放数据
lib #公共方法
readme #使用说明
#假设上诉是一个单机游戏，那么每个人的记录只会在自己的计算机保存，注册登录的帐号也只能在自己的计算机有效
#如何将单机编程联网
将数据库保存部分统一起来，所有人在一个地方操作
数据库本质
#本质就是一款基于网络通信的应用程序，那其实每个人都可以开发一款数据库软件，因为它仅仅是一个基于网络通信的应用程序
#关系型数据库
MySql、oracle、db2、access、sql server
#什么是关系型
1 数据之间有关系或约束
2 存储数据的表现形式通常是以表格存储，每个字段还会有存储类型限制
name password hobby
jason 123 学习
egon 123 女教练
tank 123 吃生蚝
#非关系型数据库(缓存数据库)
redis、mongodb、memcahe
#什么是非关系型
存储数据通常都是以k,v键值对的形式
MySql
#任何基于网络通信的应用程序底层用的都是socket协议
#服务端
基于scoket通信
收发信息
SQL语句
#客户端
基于scoket通信
收发消息
SQL语句
#MySql不单单支持自己的客户端app还支持其他编程语言来充当客户端操作
#如何解决语言的障碍？
1 让服务器兼容所有的语言（精通万国语言）
2 采用统一的语言（SQL语句）
重要概念介绍
库比喻成文件夹
表比喻成文件
记录比喻成文件内一行行的数据
表头就是表格的第一行字段
字段就是每一纵的名称
MySql安装
#在IT界一般不会轻易使用最新版本的软件，因为新版本可能会出现各种问题，使用旧版本更稳定
#MySql有很多版本（5.6，5.7，8.0....）目前企业用的比较多还是5.6左右
MySql服务端与客户端
#服务端
mysqld.exe
#客户端
mysql.exe
#注意，在前期配置mysql的时候，cmd尽量使用管理员运行，普通用户有一些命令是无法执行的
启动
#cmd（管理员）先切换到mysqld所在的bin目录下，输入mysqld即可
#重新打开一个cmd窗口
#第一次一管理员身份进入是没有密码的，直接回车即可
#完整命令 mysql -h 127.0.0.1 -P 3306 -uroot -p
#简写命令 mysql -uroot -p
常见的软件默认端口号
MySQL 3306
redis 6379
mongodb 27017
django 8000
flask 5000
vue 8080
SQL语句初识
#mysql中的sql语句是以分号作为结束的标志
#基本命令
show databases; #查看所有的库名
#当你发现输入的命令不对，可以用c取消
#客户端退出
exit
quit
#当你在连接服务端的时候，发现只输入mysql也能连接数据库，但只是游客身份
环境变量配置及系统服务制作
#cmd查看当前具体进程
tasklist
tasklist |findstr mysqld #查找mysqld进程号
#杀死具体进程
taskkill /F /PID 进程的pid号
#查看当前计算机的服务
services.msc
#每次启动mysqld需要先切换到文件路径下才能操作太过繁琐，将mysqld所在的文件添加到系统环境变量中
#还需要手动启动mysql服务端也不好，把它做成服务（开机自启）
mysqld --install
mysqld --remove #移除mysql系统服务
设置密码
#无需进入mysql，cmd终端输入即可
mysqladmin -uroot -p原密码 password 新密码
如：mysqladmin -uroot -p123 password 123456
破解密码
'''
可以将mysql获取用户名和密码效验的功能看成是一个装饰器，装饰在客户端请求访问的功能上，
我们将该装饰器移除，那么mysql服务端就不会校检用户名和密码了
'''
#先关闭当前mysql服务端
mysqld --skip-grant-tables
#直接以无密码的方式连接
mysql -uroot -p 直接回车
#修改密码
update mysql.user set password=password(123456) where user='root' and host='localhost';
#刷新数据到硬盘
flush privileges;
#关闭当前服务端，然后以正常效验授权表的显示启动
统一编码
#mysql的默认配置文件，程序启动会先加载配置文件中的配置才会真正的启动
my-default.ini #ini结尾的一般都是配置文件
#在D:Mysqlmysql-5.6.48-winx64（程序根目录）自己创建一个my.ini的配置文件
# 修改配置文件后一定要重启服务才能生效
# 统一编码的配置，无需掌握，直接拷贝即可
# 偷懒可以将管理员的用户名和密码也添加到配置文件中
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
[client]
default-character-set=utf8
[mysql]
user="root"
password=123456
default-character-set=utf8
基本sql语句
DML:
数据操作语言，它是对表记录的操作(增，删，改)
DCL:
数据控制语言
*一个项目创建一个用户！一个项目对应的数据库只有一个~
*这个用户只能对这个数据库有权限，其他数据库你就操作不了了！
DQL:
数据查询语言，查询不会修改数据库记录！
DDL:
数据定义语言。这些语句定义了不同的数据段、
数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括create、drop、alter
等。
针对库的增删查改（文件夹）
#增
create database db1;
create database db1 charset='gbk'; #指定gbk编码
#删
drop database db1;
#查
show databases; #查所有
show create database db1; #查单个
#改
alter database db1 charset='utf8' #改编码格式
针对表的增删查改(文件)
#在操作表（文件）的时候，需要指定所在的库（文件夹）
#查看当前所在哭的名字
select database();
#切换库
use db1;
#增
create table t1(id int,name char(4));
#删
drop table t1;
#查
show tables; #查看当前库下的所有表名
show create table t1; #查看单个表
describe t1; #查看表结构，简写 desc t1;
#改
alter table t1 modify name char(16); #改字段name的类型char为16长度
#也可以用绝对路径的形式操作不同的库
create table db2.t1(id int); #在db2库下的创建t1表
针对数据的增删改查(一行行的数据)
#一定要先有库，有表才能操作记录
#增
insert into t1 values(1,'jsson'); #插入一条ID为1，name为jsoin的数据
insert into t1 values(1,'jsson'),(2,'egon'),(3,'tank'); #插三条
#删
delete from t1 where id>1; #删除id大于1的数据
delete from t1 where name='jason'; #删除name=jason的数据
delete from t1; #清空表
#查
select * from t1; #查看t1表的所有数据，当数据量特别大的时候不建议使用
#改
update t1 set name='DSB' where id>1; #把id大于1的name更新为DSB
储存引擎
日常生活中文件格式有很多种，并且针对不同的文件格式会有对应不同的储存方式和处理机制（txt,pdf,word,mp4......）,针对不同的数据应该有不同的处理机制来储存，存储引擎就是不同的处理机制
MySql主要的储存引擎
#innodb
是mysql5.5版本之后默认的储存引擎，储存数据更加安全
#myisam
是mysql5.5版本之前默认的储存引擎，速度比innodb更快，但是我们更加注重的是数据的安全
#memory
内存引擎（数据全部放在内存中）断电数据丢失
#blackhole
无论存什么，都立刻消失（黑洞）
#查看所有的储存引擎
show engines;
# 不同的存储引擎在存储表的时候 异同点
create table t1(id int) engine=innodb;
create table t2(id int) engine=myisam;
create table t3(id int) engine=blackhole;
create table t4(id int) engine=memory;
创建表的完整语法
#语法
create table t1(
字段名1 类型（宽度） 约束条件，
字段名2 类型（宽度） 约束条件，
字段名3 类型（宽度） 约束条件，
)
#注意
1 在同一张表中字段名不能重复
2 宽度和约束条件是可选的（可写可不写），而字段名和子弹类是必填的
约束条件还支持多个，字段名1 类型（宽度） 约束条件1 约束条件2，
3 结尾不能有逗号
create table t6(id int,name char,); #报错
#宽度
一般情况下指的是对储存数据的限制
create table t7(name char); 默认宽度是1
insert into t7 values('jason');
insert into t7 values(null); 关键字NULL
针对不同版本的会出现不同的效果，5.6版本，默认没有开启严格模式，规定只能存一个字符，你给了多个字符，会自动帮你截取
5.7版本及以上或者开启了严格模式，那么规定只能存几个，一旦超出范围立即报错， Data too long for ....
#那么严格模式到的开不开呢
mysql5.7之后的版本默认都是开启严格模式的，
#使用数据库的准则
能尽量少的让数据库干活就尽量少，不要给数据库在家额外的压力
#约束提交 null not null不能插入null
create table t8(id int,name char not null); #name字段不能为null
#宽度和约束条件到底是什么关系
宽度是用来限制数据的储存
约束条件是在宽度的基础上增加额外的约束
基本数据类型
整形
#分类，看下图
INT #大整数值
TINYINT #小整数值
SMALLINT #大整数值
MEDUIMINT #大整数值
BIGINT #极大整数值
#作用
储存年龄、等级、id、号码等等
#以TINYINT为例
是否有符号，默认情况下是带符号的
超出会怎么样，超出ianzhi只存最大可接受值
create table t9(id tinyint);
insert into t9 values(-129),(256); #结果是-128，127 #只接受最大值
#约束条件之unsigned无符号
create table t10(id tinyint unsigned);
create table t11(id int);
#int默认也是带有符号的
#整形默认情况下都是都有符号的
#针对整型括号的宽度到底是干嘛的
create table t12(id int(8));
insert into t12 values(123456789);
特例：
只有整形括号里面的数字不是表示限制位数，如果没有8位，那么用空格填充至8位，
如果超出了8位，那么有几位就存几位，但是还是要遵循最大范围
#总结
针对整数字段，括号内无序指定宽度，以为他默认的宽度已经足够显示所有的数据了
严格模式
#mysql5.7之后的版本默认都是开启严格模式的
针对不同版本的会出现不同的效果，5.6版本，默认没有开启严格模式，规定只能存一个字符，你给了多个字符，会自动帮你截取
5.7版本及以上或者开启了严格模式，那么规定只能存几个，一旦超出范围立即报错， Data too long for ....
#查看严格模式
show variables like "%mode";
模糊匹配/查询
关键字 like
%:匹配任意多个字符
_:匹配任意单个字符
#修改严格模式
set session #只在当前窗口有效
set global #全局有效
set global spl_mode = 'STRICT_TRANS_TABLES'; #修改完后，重新进入服务端即可
浮点型
#分类
FLOAT
DOUBLE
DECIMAL
#作用
身高、体重、薪资
#储存限制
float(255,30) #总共255位，小数部分占30位
double(255,30) # 总共255位 小数部分占30位
decimal(65,30) # 总共65位 小数部分占30位
# 精确度验证
create table t15(id float(255,30));
create table t16(id double(255,30));
create table t17(id decimal(65,30));
insert into t15 values(1.111111111111111111111111111111);
insert into t16 values(1.111111111111111111111111111111);
insert into t17 values(1.111111111111111111111111111111);
float < double < decimal
# 要结合实际应用场景 三者都能使用
字符类型
#分类
char #定长，char(4)数据超过4个字符直接报错，不够4个字符空格补全
varchar #变长，varchar(4)数据超过4个字符直接报错，不够有几个存几个
# 介绍一个小方法 char_length统计字段长度
create table t18(name char(4));
create table t19(name varchar(4));
insert into t18 values('a');
insert into t19 values('a');
select char_length(name) from t18;
select char_length(name) from t19;
"""
首先可以肯定的是 char硬盘上存的绝对是真正的数据 带有空格的
但是在显示的时候MySQL会自动将多余的空格剔除
"""
# 再次修改sql_mode，让MySQL不要做自动剔除操作
set global sql_mode = 'STRICT_TRANS_TABLES,PAD_CHAR_TO_FULL_LENGTH';
char与varchar对比
#char
缺点:浪费空间
优点:存取都很简单
直接按照固定的字符存取数据即可
jason egon alex wusir tank
存按照五个字符存 取也直接按照五个字符取
#varchar
优点:节省空间
缺点:存取较为麻烦
1bytes+jason 1bytes+egon 1bytes+alex 1bytes+tank
存的时候需要制作报头
取的时候也需要先读取报头 之后才能读取真实数据
#以前基本上都是用的char,其实现在用varchar的也挺多
时间类型
#分类
date #年月日 2020-5-4
datetime #年月日时分秒 2020-5-4 11:11:11
time #时分秒11:11:11
Year #年2020
#创建表和插入数据
create table student(
id int,
name varchar(16),
born_year year,
birth date,
study_time time,
reg_time datetime
);
insert into student values(1,'egon','1880','1880-11-11','11:11:11','2020-11-11 11:11:11');
枚举与集合类型
#分类
枚举（enum） #多选一
集合（set） #多选多
#集体使用
create table user(
id int,
name char(16),
gender enum('male','female','others')
);
insert into user values(1,'jason','male'); 正常
insert into user values(2,'egon','xxxxooo'); 报错
# 枚举字段 后期在存数据的时候只能从枚举里面选择一个存储
create table teacher(
id int,
name char(16),
gender enum('male','female','others'),
hobby set('read','DBJ','hecha')
);
insert into teacher values(1,'jason','male','read'); 正常
insert into teacher values(2,'egon','female','DBJ,hecha'); 正常
insert into teacher values(3,'tank','others','生蚝'); 报错
# 集合可以只写一个 但是不能写没有列举的
总结(重点记忆)
字段类型
严格模式
约束条件
not null
zerofill
unsigned
约束条件
default默认值
# 补充知识点 插入数据的时候可以指定字段
create table t1(
id int,
name char(16)
);
insert into t1(name,id) values('jason',1);
create table t2(
id int,
name char(16),
gender enum('male','female','others') default 'male'
);
insert into t2(id,name) values(1,'jason');
insert into t2 values(2,'egon','female');
unique唯一
# 单列唯一
create table t3(
id int unique,
name char(16)
);
insert into t3 values(1,'jason'),(1,'egon');
insert into t3 values(1,'jason'),(2,'egon');
# 联合唯一
"""
ip和port
单个都可以重复 但是加载一起必须是唯一的
"""
create table t4(
id int,
ip char(16),
port int,
unique(ip,port)
);
insert into t4 values(1,'127.0.0.1',8080);
insert into t4 values(2,'127.0.0.1',8081);
insert into t4 values(3,'127.0.0.2',8080);
insert into t4 values(4,'127.0.0.1',8080); 报错
primary key唯一
1.单单从约束效果上来看primary key等价于not null + unique
非空且唯一！！！
create table t5(id int primary key);
insert into t5 values(null); 报错
insert into t5 values(1),(1); 报错
insert into t5 values(1),(2);
2.它除了有约束效果之外 它还是Innodb存储引擎组织数据的依据
Innodb存储引擎在创建表的时候必须要有primary key
因为它类似于书的目录 能够帮助提示查询效率并且也是建表的依据
# 1 一张表中有且只有一个主键 如果你没有设置主键 那么会从上往下搜索直到遇到一个非空且唯一的字段将它自动升级为主键
create table t6(
id int,
name char(16),
age int not null unique,
addr char(32) not null unique
);
# 2 如果表中没有主键也没有其他任何的非空且唯一字段 那么Innodb会采用自己内部提供的一个隐藏字段作为主键，隐藏意味着你无法使用到它 就无法提示查询速度
# 3 一张表中通常都应该有一个主键字段 并且通常将id/uid/sid字段作为主键
# 单个字段主键
create table t5(
id int primary key
name char(16)
);
# 联合主键(多个字段联合起来作为表的主键 本质还是一个主键)
create table t7(
ip char(16),
port int,
primary key(ip,port)
);
"""
也意味着 以后我们在创建表的时候id字段一定要加primary key
"""
auto_increment自增
# 当编号特别多的时候 人为的去维护太麻烦
create table t8(
id int primary key auto_increment,
name char(16)
);
insert into t8(name) values('jason'),('egon'),('kevin');
# 注意auto_increment通常都是加在主键上的 不能给普通字段加
create table t9(
id int primary key auto_increment,
name char(16),
cid int auto_increment
);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
结论
以后在创建表的id(数据的唯一标识id、uid、sid)字段的时候
id int primary key auto_increment
补充
delete from t1 删除表中数据后 主键的自增不会停止
truncate t1 清空表数据并且重置主键
表与表之间建关系
#定义一张员工表,表中有很多字段
id name gender dep_name dep_desc
1 该表的组织结构不是很清晰(可忽视)
2 浪费硬盘空间(可忽视)
3 数据的扩展性极差(无法忽视的)
# 如何优化？
上述问题就类似于你将所有的代码都写在了一个py文件中
将员工表拆分,员工表和部门表
外键
外键就是用来帮助我们建立表与表之间关系的
foreign key
表关系
表与表之间最多只有四种关系
一对多关系
在MySQL的关系中没有多对一一说
一对多 多对一 都叫一对多！！！
多对多关系
一对一关系
没有关系
一对多关系
#判断表与表之间关系的时候 前期不熟悉的情况下 一定要按照我给你的建议，换位思考，分别站在两张表的角度考虑
#员工表与部门表为例
先站在员工表
思考一个员工能否对应多个部门(一条员工数据能否对应多条部门数据)
不能！！！
(不能直接得出结论 一定要两张表都考虑完全)
再站在部门表
思考一个部门能否对应多个员工(一个部门数据能否对应多条员工数据)
能！！！
得出结论
员工表与部门表示单向的一对多
所以表关系就是一对多
#foreign key
1 一对多表关系，外键字段建在多的一方
2 在创建表的时候，一定要先建被关联表
3 在录入数据的时候，也必须先录入被关联表
# SQL语句建立表关系
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
# 修改dep表里面的id字段
update dep set id=200 where id=2; 不行
# 删除dep表里面的数据
delete from dep; 不行
# 1 先删除教学部对应的员工数据 之后再删除部门
操作太过繁琐
# 2 真正做到数据之间有关系
更新就同步更新
删除就同步删除
"""
级联更新 >>> 同步更新
级联删除 >>> 同步删除
"""
create table dep(
id int primary key auto_increment,
dep_name char(16),
dep_desc char(32)
);
create table emp(
id int primary key auto_increment,
name char(16),
gender enum('male','female','others') default 'male',
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
insert into dep(dep_name,dep_desc) values('sb教学部','教书育人'),('外交部','多人外交'),('nb技术部','技术能力有限部门');
insert into emp(name,dep_id) values('jason',2),('egon',1),('tank',1),('kevin',3);
多对多关系
#图书表和作者表
create table book(
id int primary key auto_increment,
title varchar(32),
price int,
author_id int,
foreign key(author_id) references author(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
book_id int,
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
"""
按照上述的方式创建 一个都别想成功！！！
其实我们只是想记录书籍和作者的关系
针对多对多字段表关系 不能在两张原有的表中创建外键
需要你单独再开设一张 专门用来存储两张表数据之间的关系
"""
create table book(
id int primary key auto_increment,
title varchar(32),
price int
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(author_id) references author(id)
on update cascade # 同步更新
on delete cascade, # 同步删除
foreign key(book_id) references book(id)
on update cascade # 同步更新
on delete cascade # 同步删除
);
一对一关系
#id name age addr phone hobby email........
#如果一个表的字段特别多 每次查询又不是所有的字段都能用得到,将表一分为二
用户表
用户表
id name age
用户详情表
id addr phone hobby email........
站在用户表
一个用户能否对应多个用户详情 不能！！！
站在详情表
一个详情能否属于多个用户 不能！！！
结论:单向的一对多都不成立 那么这个时候两者之间的表关系
就是一对一
或者没有关系(好判断)
客户表和学生表
在你们报名之前你们是客户端
报名之后是学生(期间有一些客户不会报名)
#一对一 外键字段建在任意一方都可以 但是推荐你建在查询频率比较高的表中
create table authordetail(
id int primary key auto_increment,
phone int,
addr varchar(64)
);
create table author(
id int primary key auto_increment,
name varchar(32),
age int,
authordetail_id int unique,
foreign key(authordetail_id) references authordetail(id)
on update cascade # 同步更新
on delete cascade # 同步删除
)
修改表（了解）
# MySQL对大小写是不敏感的
#1 修改表名
alter table 表名 rename 新表名;
#2 增加字段
alter table 表名 add 字段名 字段类型(宽度) 约束条件;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 first;
alter table 表名 add 字段名 字段类型(宽度) 约束条件 after 字段名;
#3 删除字段
alter table 表名 drop 字段名;
#4 修改字段
alter table 表名 modify 字段名 字段类型(宽度) 约束条件;
alter table 表名 change 旧字段名 新字段名 字段类型(宽度) 约束条件;
复制表（了解）
#我们sql语句查询的结果其实也是一张虚拟表
create table 表名 select * from 旧表; 不能复制主键 外键 ...
create table new_dep2 select * from dep where id>3;
前期表准备
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex 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,sex,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);
# 当表字段特别多 展示的时候错乱 可以使用G分行展示
select * from empG;
# 个别同学的电脑在插入中文的时候还是会出现乱码或者空白的现象,你可以将字符编码统一设置成GBK
几个重要的关键字的执行顺序
# 书写顺序
select id,name from emp where id > 3;
# 执行顺序
from
where
select
#虽然执行顺序和书写顺序不一致,你在写sql语句的时候可能不知道怎么写,你就按照书写顺序的方式写sql
select * 先用*号占位
之后去补全后面的sql语句
最后将*号替换后你想要的具体字段
where筛选条件
# 作用:是对整体数据的一个筛选操作
#1.查询id大于等于3小于等于6的数据
select id,name,age from emp where id>=3 and id<=6;
select id,name 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.查询员工姓名中包含字母o的员工的姓名和薪资
"""
模糊查询
like
% 匹配任意多个字符
_ 匹配任意单个字符
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是由四个字符组成的 姓名和薪资 char_length()
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
# 5.查询id小于3或者id大于6的数据
select * from emp where id not between 3 and 6;
# 6.查询薪资不在20000,18000,17000范围的数据
select * from emp where salary not in (20000,18000,17000);
# 7.查询岗位描述为空的员工姓名和岗位名 针对null不用等号 用is
select name,post from emp where post_comment = NULL;
select name,post from emp where post_comment is NULL;
group by分组
# 分组实际应用场景,分组应用场景非常的多
男女比例
部门平均薪资
部门秃头率
国家之间数据统计
# 1 按照部门分组
select * from emp group by post;
"""
分组之后 最小可操作单位应该是组 还不再是组内的单个数据
上述命令在你没有设置严格模式的时候是可正常执行的 返回的是分组之后 每个组的第一条数据 但是这不符合分组的规范:分组之后不应该考虑单个数据 而应该以组为操作单位(分组之后 没办法直接获取组内单个数据)
如果设置了严格模式 那么上述命令会直接报错
"""
set global sql_mode = 'strict_trans_tables,only_full_group_by';
设置严格模式之后,分组,默认只能拿到分组的依据
select post from emp group by post;
按照什么分组就只能拿到分组 其他字段不能直接获取 需要借助于一些方法(聚合函数)
#什么时候需要分组啊？？？
关键字
每个 平均 最高 最低
聚合函数
max
min
sum
count
avg
# 1.获取每个部门的最高薪资
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
# as可以给字段起别名,也可以直接省略不写,但是不推荐,因为省略的话语意不明确,容易错乱
# 2.获取每个部门的最低薪资
select post,min(salary) from emp group by post;
# 3.获取每个部门的平均薪资
select post,avg(salary) from emp group by post;
# 4.获取每个部门的工资总和
select post,sum(salary) from emp group by post;
# 5.获取每个部门的人数
select post,count(id) from emp group by post; # 常用 符合逻辑
select post,count(salary) from emp group by post;
select post,count(age) from emp group by post;
select post,count(post_comment) from emp group by post; null不行
# 6.查询分组之后的部门名称和每个部门下所有的员工姓名
# group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作
select post,group_concat(name) from emp group by post;
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# concat不分组的时候用
select concat('NAME:',name),concat('SAL:',salary) from emp;
# 补充 as语法不单单可以给字段起别名 还可以给表临时起别名
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; 报错
select t1.id,t1.name from emp as t1;
# 查询每个人的年薪 12薪
select name,salary*12 from emp;
分组注意事项
# 关键字where和group by同时出现的时候group by必须在where的后面
where先对整体数据进行过滤之后再分组操作
where筛选条件不能使用聚合函数
select id,name,age from emp where max(salary) > 3000;
select max(salary) from emp; # 不分组 默认整体就是一组
# 统计各部门年龄在30岁以上的员工平均薪资
1 先求所有年龄大于30岁的员工
select * from emp where age>30;
2 再对结果进行分组
select * from emp where age>30 group by post;
select post,avg(salary) from emp where age>30 group by post;
having分组之后的筛选条件
#having的语法根where是一致的,只不过having是在分组之后进行的过滤操作,即having是可以直接使用聚合函数的
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) > 10000
;
distinct去重
#一定要注意 必须是完全一样的数据才可以去重！！！
#一定不要将逐渐忽视了,有逐渐存在的情况下,是不可能去重的
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
ORM 对象关系映射 让不懂SQL语句的人也能够非常牛逼的操作数据库
表 类
一条条的数据 对象
字段对应的值 对象的属性
你再写类 就意味着在创建表
用类生成对象 就意味着再创建数据
对象点属性 就是在获取数据字段对应的值
目的就是减轻python程序员的压力 只需要会python面向对象的知识点就可以操作MySQL
select distinct id,age from emp;
select distinct age from emp;
order by排序
select * from emp order by salary;
select * from emp order by salary asc;
select * from emp order by salary desc;
#order by默认是升序 asc 该asc可以省略不写
#也可以修改为降序 desc
select * from emp order by age desc,salary asc;
# 先按照age降序排 如果碰到age相同 则再按照salary升序排
# 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary) > 1000
order by avg(salary) desc
;
limit限制展示条数
select * from emp;
"""针对数据过多的情况 我们通常都是做分页处理"""
select * from emp limit 3; # 只展示三条数据
select * from emp limit 0,5; #第一个参数是起始位置,第二个参数是展示条数
select * from emp limit 5,5;
正则
select * from emp where name regexp '^j.*(n|y)$';
多表操作
前期表准备
#建表
create table dep(
id int,
name varchar(20)
);
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,'运营');
insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
表查询
select * from dep,emp; # 结果 笛卡尔积
"""
了解即可 不知道也没关系
"""
select * from emp,dep where emp.dep_id = dep.id;
"""
MySQL也知道 你在后面查询数据过程中 肯定会经常用到拼表操作
所以特地给你开设了对应的方法
inner join 内连接
left join 左连接
right join 右连接
union 全连接
"""
# 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;
子查询
#表的查询结果可以作为其他表的查询条件,也可以通过起别名的方式把它作为一个张虚拟表根其他表关联
#多表查询就两种方式
先拼接表再查询
子查询 一步一步来
Navicat软件
一开始学习python的时候,下载python解释器然后直接在终端书写
pycharm能够更加方便快捷的帮助你书写python代码
excel word pdf
我们在终端操作MySQL,也没有自动提示也无法保存等等,不方便开发
Navicat内部封装了所有的操作数据库的命令
用户在使用它的时候只需要鼠标点点即可完成操作,无需书写sql语句
安装
#直接百度搜索,有破解版的也有非破解
#非破解的有试用期,你如果不嫌麻烦,你就用使用,到期之后重新装再使用 或者破解一下也很简单
https://www.cr173.com/soft/126934.html
#下载完成后是一个压缩包 直接解压 然后点击安装 有提醒直接点击next即可
#navicat能够充当多个数据库的客户端
#navicat图形化界面有时候反应速度较慢 你可以选择刷新或者关闭当前窗口再次打开即可
#当你有一些需求该软件无法满足的时候 你就自己动手写sql
提示
1 MySQL是不区分大小写的
验证码忽略大小写,内部统一转大写或者小写比较即可
upper
lower
2 MySQL建议所有的关键字写大写
3 MySQL中的注释 有两种
--
#
4 在navicat中如何快速的注释和解注释
ctrl + ？ 加注释
ctrl + ？ 基于上述操作再来一次就是解开注释
如果你的navicat版本不一致还有可能是
ctrl + shift + ？解开注释
练习题
-- 1、查询所有的课程的名称以及对应的任课老师姓名
-- SELECT
-- course.cname,
-- teacher.tname
-- FROM
-- course
-- INNER JOIN teacher ON course.teacher_id = teacher.tid;
-- 4、查询平均成绩大于八十分的同学的姓名和平均成绩
-- SELECT
-- student.sname,
-- t1.avg_num
-- FROM
-- student
-- INNER JOIN (
-- SELECT
-- score.student_id,
-- avg( num ) AS avg_num
-- FROM
-- score
-- INNER JOIN student ON score.student_id = student.sid
-- GROUP BY
-- score.student_id
-- HAVING
-- AVG( num ) > 80
-- ) AS t1 ON student.sid = t1.student_id;
-- 7、 查询没有报李平老师课的学生姓名
# 分步操作
# 1 先找到李平老师教授的课程id
# 2 再找所有报了李平老师课程的学生id
# 3 之后去学生表里面取反 就可以获取到没有报李平老师课程的学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- sid NOT IN (
-- SELECT DISTINCT
-- score.student_id
-- FROM
-- score
-- WHERE
-- score.course_id IN ( SELECT course.cid FROM teacher INNER JOIN course ON teacher.tid = course.teacher_id WHERE teacher.tname = '李平老师' )
-- );
-- 8、 查询没有同时选修物理课程和体育课程的学生姓名
-- (只要选了一门的 选了两门和没有选的都不要)
# 1 先查物理和体育课程的id
# 2 再去获取所有选了物理和体育的学生数据
# 3 按照学生分组 利用聚合函数count筛选出只选了一门的学生id
# 4 依旧id获取学生姓名
-- SELECT
-- student.sname
-- FROM
-- student
-- WHERE
-- student.sid IN (
-- SELECT
-- score.student_id
-- FROM
-- score
-- WHERE
-- score.course_id IN ( SELECT course.cid FROM course WHERE course.cname IN ( '物理', '体育' ) )
-- GROUP BY
-- score.student_id
-- HAVING
-- COUNT( score.course_id ) = 1
-- );
-- 9、 查询挂科超过两门(包括两门)的学生姓名和班级
# 1 先筛选出所有分数小于60的数据
# 2 按照学生分组 对数据进行计数获取大于等于2的数据
SELECT
class.caption,
student.sname
FROM
class
INNER JOIN student ON class.cid = student.class_id
WHERE
student.sid IN (
SELECT
score.student_id
FROM
score
WHERE
score.num < 60 GROUP BY score.student_id HAVING COUNT( score.course_id ) >= 2
);
pymysql模块
#支持python代码操作数据库MySQL
pip3 install pymysql
sql注入
# 利用一些语法的特性,书写一些特点的语句实现固定的语法
MySQL利用的是MySQL的注释语法
select * from user where name='jason' -- jhsadklsajdkla' and password=''
select * from user where name='xxx' or 1=1 -- sakjdkljakldjasl' and password=''
# 日常生活中很多软件在注册的时候都不能含有特殊符号,因为怕你构造出特定的语句入侵数据库,不安全
# 敏感的数据不要自己做拼接 交给execute帮你拼接即可
# 结合数据库完成一个用户的登录功能？
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
password = '123456',
database = 'day48',
charset = 'utf8' # 编码千万不要加-
)
# 链接数据库
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('>>>:')
password = input('>>>:')
sql = "select * from user where name=%s and password=%s"
# 不要手动拼接数据 先用%s占位 之后将需要拼接的数据直接交给execute方法即可
print(sql)
rows = cursor.execute(sql,(username,password)) # 自动识别sql里面的%s用后面元组里面的数据替换
if rows:
print('登录成功')
print(cursor.fetchall())
else:
print('用户名密码错误')
视图（了解）
什么是视图
视图就是通过查询得到一张虚拟表，然后保存下来，下次可以直接使用
其实视图也是表
为什么要用视图
如果要频繁的操作一张虚拟表(拼表组成的)，你就可以制作成视图 后续直接操作
如何操作
# 固定语法
create view 表名 as 虚拟表的查询sql语句
# 具体操作
create view teacher2course as
select * from teacher INNER JOIN course
on teacher.tid = course.teacher_id;
注意
1 创建视图在硬盘上只会有表结构 没有表数据(数据还是来自于之前的表)
2 视图一般只用来查询 里面的数据不要继续修改 可能会影响真正的表
视图到底使用频率高不高呢？
总结
不高，当你创建了很多视图之后 会造成表的不好维护
视图了解即可 基本不用！！！
触发器（了解）
在满足对表数据进行增、删、改的情况下，自动触发的功能
使用触发器可以帮助我们实现监控、日志...
触发器可以在六种情况下自动触发 增前 增后 删前删后 改前改后
基本语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名
for each row
begin
sql语句
end
# 具体使用 针对触发器的名字 我们通常需要做到见名知意
# 针对增
create trigger tri_before_insert_t1 before insert on t1
for each row
begin
sql语句
end
create trigger tri_after_insert_t1 after insert on t1
for each row
begin
sql语句
end
"""针对删除和修改 书写格式一致"""
ps:修改MySQL默认的语句结束符 只作用于当前窗口
delimiter $$ 将默认的结束符号由;改为$$
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') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
"""
当cmd表中的记录succes字段是no那么就触发触发器的执行去errlog表中插入数据
NEW指代的就是一条条数据对象
"""
delimiter $$
create trigger tri_after_insert_cmd after insert on cmd
for each row
begin
if NEW.success = 'no' then
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end if;
end $$
delimiter ;
# 朝cmd表插入数据
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('jason','0755','ls -l /etc',NOW(),'yes'),
('jason','0755','cat /etc/passwd',NOW(),'no'),
('jason','0755','useradd xxx',NOW(),'no'),
('jason','0755','ps aux',NOW(),'yes');
# 删除触发器
drop trigger tri_after_insert_cmd;
事务
什么是事务
开启一个事务可以包含多条sql语句 这些sql语句要么同时成功
要么一个都别想成功 称之为事务的原子性
事务的作用
保证了对数据操作的安全性
还钱的例子：
egon用银行卡给我的支付宝转账1000
1 将egon银行卡账户的数据减1000块
2 将jason支付宝账户的数据加1000块
你在操作多条数据的时候可能会出现某几条操作不成功的情况
事务的四大特性
ACID
A:原子性
一个事务是一个不可分割的单位，事务中包含的诸多操作
要么同时成功要么同时失败
C:一致性
事务必须是使数据库从一个一致性的状态变到另外一个一致性的状态
一致性跟原子性是密切相关的
I:隔离性
一个事务的执行不能被其他事务干扰
（即一个事务内部的操作及使用到的数据对并发的其他事务是隔离的，并发执行的事务之间也是互相不干扰的）
D:持久性
也叫"永久性"
一个事务一旦提交成功执行成功 那么它对数据库中数据的修改应该是永久的
接下来的其他操作或者故障不应该对其有任何的影响
如何使用事务？
# 事务相关的关键字
# 1 开启事务
start transaction;
# 2 回滚(回到事务执行之前的状态)
rollback;
# 3 确认(确认之后就无法回滚了)
commit;
"""模拟转账功能"""
create table user(
id int primary key auto_increment,
name char(16),
balance int
);
insert into user(name,balance) values
('jason',1000),
('egon',1000),
('tank',1000);
# 1 先开启事务
start transaction;
# 2 多条sql语句
update user set balance=900 where name='jason';
update user set balance=1010 where name='egon';
update user set balance=1090 where name='tank';
#总结
当你想让多条sql语句保持一致性 要么同时成功要么同时失败
你就应该考虑使用事务
储存过程
存储过程就类似于python中的自定义函数
它的内部包含了一系列可以执行的sql语句，存储过程存放于MySQL服务端中，你可以直接通过调用存储过程触发内部sql语句的执行
基本使用
create procedure 存储过程的名字(形参1,形参2,...)
begin
sql代码
end
# 调用
call 存储过程的名字();
三种开发模式
第一种
应用程序:程序员写代码开发
MySQL:提前编写好存储过程，供应用程序调用
好处:开发效率提升了 执行效率也上去了
缺点:考虑到认为元素、跨部门沟通的问题 后续的存储过程的扩展性差
第二种
应用程序:程序员写代码开发之外 设计到数据库操作也自己动手写
优点:扩展性很高
缺点:
开发效率降低
编写sql语句太过繁琐 而且后续还需要考虑sql优化的问题
第三种
应用程序:程序员写代码开发之外 设计到数据库操作也自己动手写
优点:扩展性很高
缺点:
开发效率降低
编写sql语句太过繁琐 而且后续还需要考虑sql优化的问题
第一种基本不用。一般都是第三种，出现效率问题再动手写sql
储存过程具体演示
delimiter $$
create procedure p1(
in m int, # 只进不出 m不能返回出去
in n int,
out res int # 该形参可以返回出去
)
begin
select tname from teacher where tid>m and tid<n;
set res=666; # 将res变量修改 用来标识当前的存储过程代码确实执行了
end $$
delimiter ;
# 针对形参res 不能直接传数据 应该传一个变量名
# 定义变量
set @ret = 10;
# 查看变量对应的值
select @ret;
在pymysql模块中如何调用储存过程呢？
import pymysql
conn = pymysql.connect(
host = '127.0.0.1',
port = 3306,
user = 'root',
passwd = '123456',
db = 'day48',
charset = 'utf8',
autocommit = True
)
cursor = conn.cursor(pymysql.cursors.DictCursor)
# 调用存储过程
cursor.callproc('p1',(1,5,10))
"""
@_p1_0=1
@_p1_1=5
@_p1_2=10
"""
# print(cursor.fetchall())
cursor.execute('select @_p1_2;')
print(cursor.fetchall())
知识扩展
函数
跟存储过程是有区别的，存储过程是自定义函数，函数就类似于是内置函数
('jason','0755','ls -l /etc',NOW(),'yes')
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');
流程控制(了解)
# if判断
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
# while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
索引
ps:数据都是存在与硬盘上的，查询数据不可避免的需要进行IO操作
索引:就是一种数据结构，类似于书的目录。意味着以后在查询数据的应该先找目录再找数据，而不是一页一页的翻书，从而提升查询速度降低IO操作
索引在MySQL中也叫“键”,是存储引擎用于快速查找记录的一种数据结构
- primary key
- unique key
- index key
注意foreign key不是用来加速查询用的，不在我们的而研究范围之内
上面的三种key，前面两种除了可以增加查询速度之外各自还具有约束条件，而最后一种index key没有任何的约束条件，只是用来帮助你快速查询数据
本质
通过不断的缩小想要的数据范围筛选出最终的结果，同时将随机事件(一页一页的翻)
变成顺序事件(先找目录、找数据)
也就是说有了索引机制，我们可以总是用一种固定的方式查找数据
一张表中可以有多个索引(多个目录)
索引虽然能够帮助你加快查询速度但是也有缺点
"""
1 当表中有大量数据存在的前提下 创建索引速度会很慢
2 在索引创建完毕之后 对表的查询性能会大幅度的提升 但是写的性能也会大幅度的降低
"""
索引不要随意的创建！！！
b+树
"""
只有叶子节点存放的是真实的数据 其他节点存放的是虚拟数据 仅仅是用来指路的
树的层级越高查询数据所需要经历的步骤就越多(树有几层查询数据就需要几步)
一个磁盘块存储是有限制的
为什么建议你将id字段作为索引
占得空间少 一个磁盘块能够存储的数据多
那么久降低了树的高度 从而减少查询次数
"""
聚集索引(primary key)
"""
聚集索引指的就是主键
Innodb 只有两个文件 直接将主键存放在了idb表中
MyIsam 三个文件 单独将索引存在一个文件
"""
辅助索引(unique,index)
查询数据的时候不可能一直使用到主键，也有可能会用到name,password等其他字段
那么这个时候你是没有办法利用聚集索引。这个时候你就可以根据情况给其他字段设置辅助索引(也是一个b+树）
"""
叶子节点存放的是数据对应的主键值
先按照辅助索引拿到数据的主键值
之后还是需要去主键的聚集索引里面查询数据
"""
覆盖索引
在辅助索引的叶子节点就已经拿到了需要的数据
# 给name设置辅助索引
select name from user where name='jason';
# 非覆盖索引
select age from user where name='jason';
测试索引是否有效的代码
感兴趣就自己试一试 不感兴趣直接忽略
**准备**
```mysql
#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_insert1G
#4. 调用存储过程
call auto_insert1();
```
``` mysql
# 表没有任何索引的情况下
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字段一剑封喉
```
#### 联合索引
```mysql
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语句，然后针对性的进行优化！
Mysql的四种隔离级别
SQL标准定义了4类隔离级别，包括了一些具体规则，用来限定事务内外的哪些改变是可见的，哪些是不可见的。低级别的隔离级一般支持更高的并发处理，并拥有更低的系统开销。
Read Uncommitted（读取未提交内容）
在该隔离级别，所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用，因为它的性能也不比其他级别好多少。读取未提交的数据，也被称之为脏读（Dirty Read）。
Read Committed（读取提交内容）
这是大多数数据库系统的默认隔离级别（但不是MySQL默认的）。它满足了隔离的简单定义：一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读（Nonrepeatable Read），因为同一事务的其他实例在该实例处理其间可能会有新的commit，所以同一select可能返回不同结果。
Repeatable Read（可重读）
这是MySQL的默认事务隔离级别，它确保同一事务的多个实例在并发读取数据时，会看到同样的数据行。不过理论上，这会导致另一个棘手的问题：幻读 （Phantom Read）。简单的说，幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。
Serializable（可串行化）
这是最高的隔离级别，它通过强制事务排序，使之不可能相互冲突，从而解决幻读问题。简言之，它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争。
出现问题
这四种隔离级别采取不同的锁类型来实现，若读取的是同一个数据的话，就容易发生问题。例如：
脏读(Drity Read)：某个事务已更新一份数据，另一个事务在此时读取了同一份数据，由于某些原因，前一个RollBack了操作，则后一个事务所读取的数据就会是不正确的。
不可重复读(Non-repeatable read):在一个事务的两次查询之中数据不一致，这可能是两次查询过程中间插入了一个事务更新的原有的数据。
幻读(Phantom Read):在一个事务的两次查询中数据笔数不一致，例如有一个事务查询了几列(Row)数据，而另一个事务却在此时插入了新的几列数据，先前的事务在接下来的查询中，就会发现有几列数据是它先前所没有的。
在MySQL中，实现了这四种隔离级别，分别有可能产生问题如下所示：
测试Mysql的隔离级别
下面，将利用MySQL的客户端程序，我们分别来测试一下这几种隔离级别。
测试数据库为demo，表为test；表结构：
两个命令行客户端分别为A，B；不断改变A的隔离级别，在B端修改数据。
（一）、将A的隔离级别设置为read uncommitted(未提交读)
set session transaction isolation level read uncommitted;
查看隔离级别是否设置成功
select @@transaction_isolation （mysql版本 8.0 以后）
select @@tx_isolation （mysql版本 8.0 之前）
查看mysql版本
> status
A：启动事务，此时数据为初始状态
start transaction;
B：启动事务，更新数据，但不提交
start transaction;
A：再次读取数据，发现数据已经被修改了，这就是所谓的“脏读”
B：回滚事务
rollback;
A：再次读数据，发现数据变回初始状态
经过上面的实验可以得出结论，事务B更新了一条记录，但是没有提交，此时事务A可以查询出未提交记录。造成脏读现象。未提交读是最低的隔离级别。
（二）、将客户端A的事务隔离级别设置为read committed(已提交读)
set session transaction isolation level read committed;
A：启动事务，此时数据为初始状态
B：启动事务，更新数据，但不提交
A：再次读数据，发现数据未被修改
B：提交事务
A：再次读取数据，发现数据已发生变化，说明B提交的修改被事务中的A读到了，这就是所谓的“不可重复读”
经过上面的实验可以得出结论，已提交读隔离级别解决了脏读的问题，但是出现了不可重复读的问题，即事务A在两次查询的数据不一致，因为在两次查询之间事务B更新了一条数据。已提交读只允许读取已提交的记录，但不要求可重复读。
(三)、将A的隔离级别设置为repeatable read(可重复读)
A：启动事务，此时数据为初始状态
B：启动事务，更新数据，但不提交
A：再次读取数据，发现数据未被修改
B：提交事务
A：再次读取数据，发现数据依然未发生变化，这说明这次可以重复读了
B：插入一条新的数据，并提交
A：再次读取数据，发现数据依然未发生变化，虽然可以重复读了，但是却发现读的不是最新数据，这就是所谓的“幻读”
A：提交本次事务，再次读取数据，发现读取正常了
由以上的实验可以得出结论，可重复读隔离级别只允许读取已提交记录，而且在一个事务两次读取一个记录期间，其他事务部的更新该记录。但该事务不要求与其他事务可串行化。例如，当一个事务可以找到由一个已提交事务更新的记录，但是可能产生幻读问题(注意是可能，因为数据库对隔离级别的实现有所差别)。像以上的实验，就没有出现数据幻读的问题
(四)、*将A的隔离级别设置为*可串行化(Serializable)
A：启动事务，此时数据为初始状态
B：发现B此时进入了等待状态，原因是因为A的事务尚未提交，只能等待（此时，B可能会发生等待超时）
A：提交事务
B：发现插入成功
serializable完全锁定字段，若一个事务来查询同一份数据就必须等待，直到前一个事务完成并解除锁定为止。是完整的隔离级别，会锁定对应的数据表格，因而会有效率的问题。