数据分析之MySQL学习
数据分析之数据库MySQL
数据库的概念
什么是数据库？
简单地概括，数据库其实就是用来帮助存取数据的地方
数据库的演变
1.单机游戏
不需要网络，每台机器数据独立
会造成用户注册的数据只能在注册了的计算机上面使用
换了一台计算机相同的游戏却无法进行登录和继续游戏进度
2.联网游戏
需要网络，所有的游戏数据都是共享的
无论哪台计算机上面注册或者玩耍
之后去任何一台计算机上都还可以继续登录和继续游戏进度
软件开发架构
c/s架构
c:client 客户端
s:server 服务端
我们在手机和电脑上下载的软件应用其实就是一个个的客户端
而我们点开客户端之后之所以可以使用相应的功能，其实就是服务端在提供服务
"""
我们可以把客户端想想成是一个个的客人
把服务端想成是一家家的店
我们使用软件的过程其实就是去不同的店里面体验服务的过程
"""
b/s架构
b:broswer 浏览器
s:server 服务器
"""
c/s架构中如果新体验某个点的服务需要下载对应的客户端
而b/s架构中浏览器可以充当很多服务端的客户端
"""
ps:其实b/s架构本质上还是c/s架构
数据库的本质
数据库的本质其实就是一款c/s架构软件
数据库既然是软件，那也意味着程序员可以开发多个不同的数据库软件
数据库的分类
关系型数据库
有固定的表结构，并且表与表之间可以建立代码层面的关系和约束
id name age
1 wxj 18
2 jason 32
3 tom 43
# 常见的关系型数据库：MySQL、Oracle、postgresql、access、SQL server、db2、sqlite、mariadb
非关系型数据库
没有固定的表结构，数据都是以K:V键值对的形式存储
user1={'name':'wxj','password':'qwe'}
user2={'username':'jason','password':1232,'gender':'male'}
# 常见的非关系型数据库：Redis、MongoDB
SQL语句
可以作为数据库客户端的程序很多
可以是数据库自己写的客户端
也可以是其他编程语言模拟的客户端
好比一家店需要接待不同国家的客人，所面临的的语言沟通交流的问题
如何解决客户端与服务端直接交互障碍的问题?
方案1:服务端做到兼容所有国家的语言(耗资源)
方案2:统一使用规定的语言
总结:基于上述推导步骤得出，要想使用数据库必须要学会数据库规定的统一操作数据库的语言>>>:SQL语句
MySQL
MySQL本质就是一款c/s架构的软件
MySQL目前的版本大致有
5.5
5.6:目前企业使用做频繁的版本
5.7
8.0
下载地址:https://dev.mysql.com/downloads/mysql/
将压缩包解压到对应的目录下(不要是C盘就可以)
bin文件内存放都是一些启动文件，我们需要将bin文件路径添加到环境变量中
mysqld.exe MySQL服务端
mysql.exe MySQL客户端
# 要想使用MySQL，必须确保服务端先运行之后才能使用客户端
"""
提示:你们的计算机在下载完MySQL，启动mysqld的时候可能会出现报错
这个时候不要慌张，将弹出来的报错输入到百度中搜索即可得到解决方案
"""
MySQL系统服务之作
将MySQL做成系统服务，只要计算机开机，MySQL服务端自动启动(好处在于我们不需要管服务端是否运行，只需要专心的使用客户端即可)
# 查看当前计算机所有的服务:services.msc
1.将MySQL制作成系统服务(一定要用管理员身份运行cmd)
mysqld --install
2.首次需要自己启动服务
net start mysql
3.直接打开cmd认证服务
mysql
'''补充操作'''
停止服务
net stop mysql
移除系统服务
mysqld --remove
前期登录
1.mysql在没有设置用户名和密码的时候可以直接输入mysql登录
此时你相当于是游客模式(体验模式)
2.正常情况下我们应该使用用户名和密码的方式登录mysql
mysql -uroot -p
在没有设置密码的时候root账号默认的密码是空(直接回车两次即可)
3.修改密码
# 方式1(直接生效)
set password=PASSWORD('123');
# 方式2(改完需要重启服务)
update mysql.user set password=password(123456) where user="root" and host="localhost";
重要概念
库 文件夹
表 文件夹里面的文件
数据 文件里面一行行的内容
# 总结:MySQL其实就是一款可以帮助我们远程操作文件夹、文件、数据的软件而已
MySQL基本命令
# SQL语句必须以分号作为结束符(;)
1.登录(不需要加分号)
mysql -uroot -p
2.查看所有的库
show databases;
3.退出登录(加不加分号都可以)
exit;
quit;
4.查看所有的表
show tables;
5.查看表里面的数据
select * from 表名;
6.查看数据库配置信息
\s
配置文件
# MySQL默认的配置是 不能直接修改
my-default.ini
# 如果需要修改配置 我们要新建一个配置文件
my.ini
# 添加一下固定的配置信息
[mysqld] # 服务端启动之后会默认加载下面的配置
character-set-server=utf8
collation-server=utf8_general_ci
[client] # 不是mysql自己提供的客户端连接之后会自动加载的配置
default-character-set=utf8
[mysql] # mysql自己提供客户端
default-character-set=utf8
# 要想让配置文件生效，必须要重启服务端
库的增删改查基本SQL语句
# 查
show databases; # 查看所有的数据库
show create database 已经存在的库名; # 查看字符编码的信息
# 增
create database 库名;
# 改
alter database db1 charest='gbk'; # 修改数据库的编码
# 删
drop database 库名;
表的增删改查基本SQL语句
"""
注意由于表需要在库中创建，所以我们需要提前准备好库
(不要在MySQL自带的库下面操作)
"""
create database db1;
# 创建好库之后 需要指定使用哪个库完成后续的表和记录操作
select database(); # 查看当前在哪个库里面 如果都不在返回null
use 库名; # 切换到对应的库xia
# 查
show tables; # 查看某个库下面所有的表
show create table 已经存在的表名; # 查看具体表的相关信息
describe 已经存在的表名; # 查看表字段相关信息
'''上述命令可以简写为 desc 表名;'''
# 增
create table 表名(id int,name char,password int);
"""表里面有两个字段名id、name和password
并且每个字段都必须提前声明存储的数据类型
"""
# 改
alter table 表名 modify name char(4); # 修改表字段限制
alter table 表名 rename t1 t666; # 修改表名
alter table 表名 change password pwd int; # 修改字段名称
# 删
drop table 表名;
记录的增删改查
"""操作记录之前应该先确定库和表"""
参考表
create table t1(id int,name varchar(32));
# 查
select * from t1; # *表示获取表里面所有的字段对应的数据
select name from t1; # 只获取表中name字段对应的数据
'''
如果需要多个字段 逗号隔开依次书写即可
select id,name from t1;
注意在实际工作中如果一个表里面有很多数据，一般不推荐使用*号
*号是执行效率最低的，我们只在学习阶段为了省事才会频繁使用
以后工作之后尽量少用(数据量不大的时候可以稍微使用一下偷懒)
'''
# 增
insert into t1 values(1,'jason'); # 插入单条数据
insert into t1 values(2,'tony'),(3,'tom'),(4,'kevin'); # 插入多条数据
当我们需要导入一批数据的时候,但是这批数据存在唯一索引,你还不确定这些数据存在不存在的时候,我们在insert into之间加 ignore
insert ignore into t1 values(4,'mary')
# 改
update t1 set name='jasonNB' where id=1;
'''将id是1的数据的name字段对应的值修改为jasonNB'''
# 删
delete from t1 where id=3; # 将id是3的数据删除
delete from t1; # 将t1表里面所有的数据删除
存储引擎
MySQL内部针对数据可以采用不同的存储引擎来存储，每种存储引擎都有其各自的优缺点
# 查看所有的存储引擎
show engines;
"""MySQL5.6及之后的版本默认的存储引擎是InnoDB"""
MyISAM:MySQL5.5版本之前默认的存储引擎
BlackHole:任何写入的数据都会立刻消失
Memory:基于内存存取数据，断电数据立刻丢失
InnoDB:5.5版本之后MySQL默认的存储引擎，支持很多功能，并且安全性很高
"""我们在创建表的时候可以指定存储引擎"""
create table t2(id int) engine='MyISAM';
create table t3(id int) engine='BlackHole';
create table t4(id int) engine='Memory';
create table t5(id int) engine='InnoDB';
MySQL基本数据类型之整型
我们在创建表的时候需要指定表的字段名和该字段存储的数据类型(规定该字段只能存放什么样类型的数据)
# 整型
在MySQL中整型根据能够存储的数字范围大小主要分为以下几种
tinyint 1bytes
smallint 2bytes
int 4bytes
bigint 8bytes
先验证整型默认是否自带正负号
create table t6(id tinyint);
create table t7(id smallint);
create table t8(id int);
create table t9(id bigint);
# 结论:MySQL中的整型默认都是自带正负号的
'''我们可以在创建表的时候指定不带正负号'''
create table t10(id tinyint unsigned);
MySQL基本数据类型之浮点型
float
float(255,30)总共255位小数位占30位
double
double(255,30)总共255位小数位占30位
decimal
decimal(65,30)总共65位小数位占30位
create table t11(id float(255,30));
create table t12(id double(255,30));
create table t13(id decimal(65,30));
# 分别插入相同的小数
insert into t11 values(1.111111111111111111111111111111);
insert into t12 values(1.111111111111111111111111111111);
insert into t13 values(1.111111111111111111111111111111);
"""三者不同之处其实就在于精确度不一样"""
float < double < decimal
# 结合具体业务需求选择不同的类型 一般情况下针对小数我们使用float足够，但是如果涉及到精确度较高的业务则需要切换
扩展:针对精确度问题，MySQL和python其实对数字的精确度都不是很高，python是依靠模块来解决精确度问题，而MySQL可以通过变换数据类型的方式
有时候很多看似需要用数字存储的数据，其实内部存的是字符串形式
因为字符串不存在精确度问题，在取出来的时候只需要做类型转换即可
总结 针对数据到底用什么数据类型存储并不是特别严格的，只需要你结合业务逻辑合理选定即可
MySQL基本数据类型之字符类型
char(4)
最大只能够存储4个字符，超出了会直接报错，不够则还是存成四位(空格填充)
varchar(4)
最大只能够存储四个字符，超出了会直接报错，不够则有几位存几位
# 验证
create table t14(name char(4));
create table t15(name varchar(4));
insert into t14 values('jason');
insert into t15 values('jason');
"""我们在使用MySQL5.6版本的时候数据超出了限制不会报错，这一点是非常不好的，在5.7及之后的版本都做了限制直接报错"""
针对5.6及之前的版本，需要我们自己设置严格模式来完成校验
show variables like '%mode%'; # 筛选出相应的模式
严格模式
char
优点:存取数据都是按照固定的位数，速度快
缺点:浪费存储资源
char(5)
eg:jasontony tom kevin
varchar
优点:节省存储空间
缺点:存取数据的速度较于char慢
varchar(5)
数据在存取的时候都有一个数据头(用来标识真正数据的长度)
eg:1bytes+jason1bytes+tony1bytes+tom1bytes+kevin
"""
char与varchar都有各自的优点，具体使用哪一个需要结合业务需求
目前在没有任何规定的情况下更加喜欢使用varchar
"""
行锁:简单的说就是某一个人在操作这一行数据的时候其他人不能操作(同一时间只允许一个操作)
表锁:同上
数字类型与字符类型括号里面的数字区别
1.我们知道字符类型括号里面的数字用来表示存储限制
2.针对整型其实我们需要额外注意并不是用来表示存储限制
整型后面的数字是用来控制展示长度的，并且我们在定义整型字段的时候根本无需关注整型括号里面的数字，直接采用默认的就可以了
# zerofill数字不够位数用0填充
create table t1(id int(4) zerofill);
日期类型
date 年月日
datetime 年月日时分秒
time 时分秒
year 年
create table user(
id int,
name char(16),
birth date,
register_time datetime,
study_time time,
join_date year
);
# 手动模拟时间数据
insert into user values(1,'jason','2000-11-11','2020-11-11 11:11:11','11:11:11','1995');
枚举类型
其实就是多选一，比如统计人的性别，国家等
create table userinfo(
id int,
name varchar(32),
gender enum('male','female','others')
);
集合类型
其实就是多选多(包含多选一)，比如统计人的爱好，女友/男友等
create table user1(
id int,
name varchar(32),
hobbies set('basketball','football','doublecolorball')
);
创建表的完整语法
create table 表名(
字段名1 字段类型(宽度) 约束条件,
字段名2 字段类型(宽度) 约束条件,
字段名3 字段类型(宽度) 约束条件
);
1.字段名和字段类型是必须的
2.宽度和约束条件是可选的
3.约束条件可以有多个(空格隔开即可)
4.创建表的最后一个字段结尾绝对不能有逗号
字段的约束条件
unsigned 无符号
zerofill 0填充
# 非空
not null
create table t1(
id int,
name varchar(32) not null
);
# 默认值
default
create table t2(
id int,
name varchar(32),
gender enum('男','女','其他') default '男'
);
# 唯一
unique
单个字段唯一(不能有重复)
create table t3(
id int unique,
name varchar(32) unique
);
ps:扩展>>>:联合唯一(单个字段可以重复但是多个字段的组合不能重复)
create table t4(
id int,
ip varchar(32),
port int,
unique(ip,port)
);
# 主键(******)
primary key
1.在约束层面上的效果等价于not null + unique即非空且唯一
create table t5(
id int primary key,
name char(32)
);
2.InnoDB存储引擎规定了所有的表都应该有且只有一个主键字段
"""
1.如果没有主键字段但是表中含有非空且唯一的字段，那么会自动将其升级为主键
create table t6(
id int,
name char(16),
age int not null unique,
addr char(16) not null unique
);
2.如果没有主键字段并且表中也没有其他带有约束条件的字段，那么InnoDB会采用内部隐藏的一个字段作为主键，但是该字段我们无法使用到
3.我们在创建表的时候应该设置一个id字段并且id字段应该是表的主键
id、sid、nid、uid、pid...
"""
create table t7(
id/nid/sid/uid/pid/cid int primary key,
name char(32)
);
3.不会因为数据的删除贰自动重置/回退
4.如果真的想重置主键只能格式化表
delete from不可以
truncate 可以
# 自增
auto_increment
其实就是专门用来给primary key使用的
create table t8(
id int primary key auto_increment,
name char(32)
);
#########################################################
'''以后我们在创建表的时候针对主键字段固定按照下列的语句书写'''
主键字段名 int primary key auto_increment
#########################################################
# 联合主键：多个字段联合起来作为表的一个主键，本质还是一个主键(了解)
create table t18(
ip char(16),
port int,
primary key(ip,port)
);
desc t18;
插入数据的两种方式
insert into t1 values(按照字段顺序依次填值);
insert into t1(id) values(按照前面指定字段填值);
修改表
# mysql对大小写不敏感！！！
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段 # modify只能改字段数据类型完整约束，不能改字段名，但是change可以！
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
查询流程
1.客户端输入SQL语句
2.基于网络将SQL语句发送给服务端
3.服务端执行校验、审核、执行SQL语句
4.将SQL语句的结果基于网络发送给客户端
5.客户端接收并渲染成表格的形式展示给用户看
SQL语句的查询结果我们可以看成是一张虚拟表
# 了解
只复制表数据
create table t8 select * from t7;
只复制表结构
create table t9 like t7;
外键前戏
# 以创建一张员工表为例
id name age dep_name dep_desc
"""
缺点:
1.表结构不清晰
既可以看成是员工表也可以看成是部门表
2.浪费存储空间
有一些数据频繁的重复
3.可扩展性差
修改数据条目重复太多
"""
# 如何解决上述三个缺点>>>:拆表
id name age
id dep_name dep_desc
"""
拆表解决了上述的三个问题，但是却导致了数据之间没有了关系
"""
在员工表里面添加一个字段，用来记录部门的id号，从而实现员工表与部门表的关系
结论:
我们将记录表与表之间关系的字段称之为"外键"字段
反向理解:外键就是用来表示表与表之间的关系
表关系的判断准则
# 以员工表与部门表为例
1.先站在员工表的基础上
问:员工表里面的一条数据能否对应部门表里面的多条数据
翻译:一个员工能否属于多个部门
结论:不可以
2.再站在部门表的基础上
问:部门表里面的一条数据能否对应员工表里面的多条数据
翻译:一个部门能否拥有多个员工
结论:可以
"""
换位思考完成之后才可以判定表与表之间的关系
一个可以一个不可以,那么表与表之间的关系就是'一对多'
"""
# 针对一对多的表关系，维护关系的外键字段建在哪一方？？？
建在多的一方(本案例即建在员工表里面)
# SQL实现
刚开始不熟练的情况下，可以先创建基础表之后再考虑外键字段
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.无法直接更新或者删除被关联表里面的关系字段(没有外键字段的表)
本案例中就是部门表里面的id字段无法删除和更新
针对约束3其实有对应的解决方案
# 级联更新、级联删除
create table dep1(
id int primary key auto_increment,
dep_name varchar(32),
dep_desc varchar(64)
);
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
);
表关系之"多对多"
# 以图书表和作者表为例
1.先站在图书表
问:图书表里面的一条数据能否对应作者表里面的多条数据
翻译:一本书能否有多个作者
结论:可以
2.再站在作者表
问:作者表里面的一条数据能否对应图书表里面的多条数据
翻译:一名作者能否写多本书
结论:可以
"""
换位思考完成之后才可以判定表与表之间的关系
一个可以一个也可以,即双方都可以的情况下
那么表与表之间的关系就是'多对多'
"""
# 针对多对多的表关系，维护关系的外键字段建在哪一方？？？
create table book(
id int primary key auto_increment,
title varchar(32),
price float(8,2),
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),
phone 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 float(8,2)
);
create table author(
id int primary key auto_increment,
name varchar(32),
phone int
);
create table book2author(
id int primary key auto_increment,
author_id int,
book_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
# 针对多对多，两张关系表之间彼此没有直接联系，所有的关系全部依赖于第三张关系表，并且可以不绑定关系
表关系之"一对一"
# 以用户表与用户详情表为例
1.先站在用户表
问:用户表里面的一条数据能否对应用户详情表里面的多条数据
翻译:一名用户能否有多个用户详情
结论:不可以
2.再站在用户详情表
问:用户详情表里面的一条数据能否对应用户表里面的多条数据
翻译:一个用户详情能否对应多名用户
结论:不可以
"""
换位思考完成之后才可以判定表与表之间的关系
双方都是不可以的情况下，表关系只有两种
1.没有关系(用你的膝盖都能想到)
2.一对一
"""
# 针对一对一的表关系，维护关系的外键字段建在哪一方？？？
外键字段建在任意一方都可以，但是推荐你建在查询频率较高的表中
本案例中应该建在用户表中
create table user(
id int primary key auto_increment,
name varchar(32),
phone int,
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,
addr varchar(32),
phone int
);
单表查询的数据准备
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) 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);
当查询字段较多，展示错乱的情况下，我们可以采取下列措施
1.\G
select * from mysql.user\G;
2.修改select后面的*号
select Host,User,Password from mysql.user;
SQL语句的执行顺序
select * from emp where id=1;
# 书写顺序
select
from
where
# 执行顺序
from
where
select
'''
虽然select并不是第一个执行的，但是我们在书写的时候却是第一个写的
我们在书写sql语句的时候通常会直接先写select *
之后把后面的语法补全再来考虑*号是否需要换成指定的字段名称
'''
查询关键字之where筛选
# 1.查询id大于等于3小于等于6的数据
select id,name 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.查询员工姓名中包含字母o的员工姓名和薪资
"""
模糊查询
1.关键字
like
2.符合
%:匹配任意个数的任意字符
eg:
%jason% 只要文本中含有jason即可
符合条件:asdjahsjasonjashdj
jason% 只要文本的开头是jason即可
符合条件:jasonasjdjuuwq
_:匹配单个个数的任意字符
eg:
_jason_ jason前后都有一个任意字符
符合条件:ojason$
jason_ jason后面有一个任意字符
符合条件:jason*
"""
select name,salary from emp where name like '%o%';
# 4.查询员工姓名是四个字符的员工姓名和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name) = 4;
"""char_length类似于python里面的len方法，用来统计长度"""
# 5.查询id小于3或者大于6的数据
select * from emp where id not between 3 and 6;
select * from emp where id<3 or id>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;
select name,post from emp where post_comment is not NULL;
# 8.Where 查询条件
关系运算符：>（大于）
<（小于）
=（等于）
!= 或 <> （不等于）
>=（大于等于）
<=（小于等于）
区间：between A and B（A和B之间）【A,B】
AND && 和 并且
or || : 或
not： 非 取反
Is null：是空
is not null : 非空
in 在什么里面 包含
查询关键字之group by 分组
分组:将单个单个的个体，按照一定的条件组织成一个个整体
eg:按照性别分组，按照部门分组，按照国家分组
"""分组之后默认只能拿到分组的依据，不能直接获取除分组以外的数据"""
针对MySQL5.6及之前的版本，需要我们自己修改严格模式，MySQL5.7及之后的版本都无需自己设置
set global sql_mode='ONLY_FULL_GROUP_BY';
"""
聚合函数
max 最大值
min 最小值
sum 求和
count 计数
avg 平均
"""
# 1.获取每个部门的最高工资
1.应该按照部门先分组
2.再利用聚合函数查询
select post,max(salary) from emp group by post;
# 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;
补充
# 1.查询每个部门名称及部门下的员工姓名
"""1.group_concat() 可以获取非分组字段以外的数据"""
select post,group_concat(name) from emp group by post;
# 2.查询每个部门名称及部门下的员工姓名和薪资
select post,group_concat(name),group_concat(salary) from emp group by post;
select post,group_concat(name,salary) from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
select post,group_concat(name,':',salary,'|',sex) from emp group by post;
"""2.针对查询出来的数据还可以使用as自定义字段名称"""
select post,group_concat(name) as '员工姓名' from emp group by post;
select name as '姓名',post as '岗位' from emp;
# as其实也可以直接省略,但是不推荐!
"""3.针对不分组的情况，可以使用concat做拼接"""
select concat(name,":",post,":",sex) from emp;
"""4.如果多个字段拼接的符号是一样的 还可以使用concat_ws"""
select concat_ws(':',name,sex,post) from emp;
"""5.针对select后面的字段名称还可以直接做运算"""
select name,salary*12 from emp;
分组练习题
# 1.统计各部门年龄在30岁以上的员工平均工资
1.先筛选出年龄是30岁以上的数据
select * from emp where age > 30;
2.再对筛选出来的数据做分组
select * from emp where age > 30 group by post;
3.求平均薪资
select post,avg(salary) from emp where age > 30 group by post;
"""where的筛选在group by分组之前执行"""
查询关键字之having过滤
######################################################
having在功能上与where一模一样，都是对数据进行后期的筛选
但是区别在于
where用在分组之前
having用在分组之后
######################################################
# 1.统计各部门年龄在18岁以上的员工平均工资，并且保留平均工资大于10000的部门
select post,avg(salary) from emp
where age > 18 # 分组之前用where
group by post
having avg(salary) > 10000 # 分组之后用having
;
查询关键字之distinct去重
distinct去重有一个非常苛刻的条件:数据必须是完全一模一样的才可以
# 如果数据里面包含了主键，那么肯定不是重复的数据
select distinct sex,post from emp;
查询关键字之order by 排序
select * from emp order by salary; # 默认是升序
select * from emp order by salary asc; # asc就是升序的意思 默认
select * from emp order by salary desc; # 降序
"""按照单个字段排序可能会出现相同值从而无法绝对顺序，这个时候我们还可以设置第二 第三...更多的比较字段"""
# 1.先按照年龄升序排序如果比较不出来大小则再按照薪资降序排序
select * from emp order by age asc,salary desc;
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
select post,avg(salary) from emp
where age>10
group by post
having avg(salary) > 1000
order by avg(salary);
查询关键字之limit分页
"""
当表中的数据量特别大的时候，如果一次性全部查询出来可能会导致机器的故障
我们针对数据量比较大的表，查询数据可以采用limit分页展示
"""
select * from emp limit 5; # 一个数字表示数据条数
select * from emp limit 5,5; # 第一个数字表示起始位置 第二个数字表示条数
# 1.查询工资最高的人的详细信息
1.对数据按照薪资降序排序
2.在利用limit取第一条
select * from emp order by salary desc limit 1;
查询关键字之regexp正则
"""正则:使用一些特殊符号去字符串中筛选出符合条件的内容"""
select * from emp where name regexp '^j.*(n|y)$';
'^j.*(n|y)$'
匹配j开头，中间可以是任意的文本，最后必须是以字母n或者y结尾
多表查询
有时候我们需要查询的数据并不单单来源于一张表
这个时候我们就需要使用多表查询的方式
针对多表查询有以下两种查询策略
1.子查询
就是我们日常生活中解决问题的思路
分步操作
# 将一条sql语句的查询结果当做另外一条sql语句的查询条件
2.连表操作
直接将需要用到的表全部拼接到一起组成一张大表
之后直接使用单表查询语句完成数据的筛选
多表查询基本操作
#建表
create table dep(
id int primary key auto_increment,
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,'运营'),
(205,'保洁')
;
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);
子查询
# 1.查询部门是技术或者人力资源的员工信息
1.先去部门表查询技术和人力资源部门的id号
select id from dep where name in ('技术','人力资源');
2.再去员工表中根据部门id号筛选出对应的员工
select * from emp where dep_id in (200,201);
select * from emp where dep_id in (select id from dep where name in ('技术','人力资源'));
"""当一条SQL语句需要被当做另外一条SQL语句的条件时，只需要在该SQL语句前后加上括号即可"""
连表操作
"""连表其实就是按照两张表中公有的字段连接表"""
# 针对多表操作，涉及到字段名称冲突的情况我们统一采用在字段的前面加上表名
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填充
# 查询部门是技术或者人力资源的员工信息
1.先将员工表与部门表拼接起来
2.然后再基于单表查询直接获取数据
select * from emp inner join dep on emp.dep_id=dep.id where dep.name in ('技术','人力资源');
# 结论
涉及到多表查询的业务需求
子查询和连表操作都可以采用
但是有时候两个方法难易程度可能有所不同
可视化软件之Navicat
可以充当很多数据库软件的客户端
并且通过鼠标点点点就可以完成增删改查操作
Navicat有很多版本，不同的版本图标颜色也各有不同
最老版的是黄色
https://pan.baidu.com/s/1bpo5mqj
稍微新一点的是绿色
https://www.cnblogs.com/yinfei/p/11427259.html
最新的是彩色
自行百度下载即可
安装
直接双击下载好的文件里面的.exe程序文件一步步next即可
# 结论
虽然navicat让我们鼠标点点点就可以操作数据库，但是其实内部还是封装的SQL语句，因为MySQL的服务端只认识SQL语句
# 补充
最规范的情况下所有的关键字应该采用大写的形式，但是不大写也没有关系
# MySQL中的注释
1.--
2.#
# python中的注释
1.#
2.三引号
python操作MySQL
python如果需要操作MySQL数据库，需要借助于第三方模块>>>:pymysql
针对第三方模块要先基于网络下载
pip3 install pymysql
pip3 install pymysql -i 国内的源
# MySQL默认的端口号是3306
# 导入模块
import pymysql
# 连接MySQL服务端
conn = pymysql.connect(
# 服务端的ip地址
host='127.0.0.1', # 本机回环地址
# 服务端的port地址
port=3306, # MySQL默认端口号
# 用户名
user='root',
# 密码
password='123456',
# 要操作的数据库名
database='db8',
# 字符编码
charset='utf8'
)
# 产生一个可以执行命令的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 构造SQL语句
sql = 'select * from teacher;'
# 利用cursor发送给服务端执行sql语句
cursor.execute(sql)
# 获取执行之后的结果
res = cursor.fetchall()
print(res) # ((1, '张磊老师'), (2, '李平老师'), (3, '刘海燕老师'), (4, '朱云海老师'), (5, '李杰老师'))
"""
针对查询结果默认是元组的形式展示，各个数据对应的字段名称无法查看，会导致识别不准的情况
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
加上参数之后结果就会是列表套字典的形式，这是我们比较喜欢的形式
"""
pymysql详细操作
# 导入模块
import pymysql
# 连接MySQL服务端
conn = pymysql.connect(
# 服务端的ip地址
host='127.0.0.1', # 本机回环地址
# 服务端的port地址
port=3306, # MySQL默认端口号
# 用户名
user='root',
# 密码
password='123456',
# 要操作的数据库名
database='db9',
# 字符编码
charset='utf8',
# 自动确认
autocommit=True
)
# 产生一个可以执行命令的游标对象
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 获取用户的用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 构造数据库的查询语句
# sql = 'select * from userinfo where name="%s" and password="%s"'
sql = 'select * from userinfo where name=%s and password=%s'
# 执行查询语句
# print(sql% (username, password)) # 不要自己拼接关键数据
# cursor.execute(sql % (username, password))
cursor.execute(sql, (username, password)) # 自动拼接 并剔除特殊符号
# 获取结果
res = cursor.fetchall()
if res:
print('登录成功')
else:
print('用户名或密码错误')
# 构造SQL语句
# sql = 'select * from teacher;'
# sql = 'insert into userinfo(name,password) values("tom","444");' # 插入不行
# sql = 'select * from userinfo;' # 查询可以
# sql = 'update userinfo set name="jasonNB" where id=1;' # 更新不行
# sql = 'delete from userinfo where id=3;' # 删除不行
"""针对数据的增 删 改操作 默认不能直接执行 需要你二次确认"""
# 利用cursor发送给服务端执行sql语句
# affect_row = cursor.execute(sql)
# conn.commit() # 确认可以操作
# print(affect_row)
# print(cursor.fetchall())
# 获取执行之后的结果
# res = cursor.fetchall() # 查询所有
# print(res)
# res1 = cursor.fetchone() # 只获取结果的第一条
# print(res1)
# res2 = cursor.fetchmany(2) # 括号内指定要获取的条数
# print(res2)
# res1 = cursor.fetchone() # {'tid': 1, 'tname': '张磊老师'}
# res2 = cursor.fetchone() # {'tid': 2, 'tname': '李平老师'}
# res3 = cursor.fetchall() # [{'tid': 3, 'tname': '刘海燕老师'}, {'tid': 4, 'tname': '朱云海老师'}, {'tid': 5, 'tname': '李杰老师'}]
# print(res1,res2,res3)
"""fetchone many all跟文件的光标移动类似"""
# 可以人为修改移动
# res1 = cursor.fetchone()
# # cursor.scroll(1,'relative') # 相对于当前位置往后移动
# # cursor.scroll(2,'absolute') # 相对于开头往后移动
# res2 = cursor.fetchone()
# print(res1,res2)
视图
一条SQL语句的查询结果其实我们看成是一张虚拟表(在内存中)
视图:就是将虚拟表保存到硬盘的过程，之后可以直接使用
当我们需要反复的使用某一张表，并且这张表是通过复杂操作得来的，那么这个时候可以考虑奖该表制作成视图
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id;
# 视图表的数据来自于其他基础表 我们尽量不要修改视图表里面的数据
"""
视图虽然很好用，但是不推荐使用
任意造成混淆 并浪费大量的存储空间
"""
触发器
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器
为何要用触发器
触发器专门针对我们对某一张表数据增insert、删delete、改update的行为，这类行为一旦执行
就会触发触发器的执行，即自动运行另外一段sql代码
创建触发器语法
"""语法结构
create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
begin
sql语句
end
"""
# 针对插入
create trigger tri_after_insert_t1 after insert on 表名 for each row
begin
sql代码。。。
end
create trigger tri_before_insert_t2 before insert on 表名 for each row
begin
sql代码。。。
end
# 针对删除
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
sql代码。。。
end
create trigger tri_before_delete_t2 before delete on 表名 for each row
begin
sql代码。。。
end
# 针对修改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
sql代码。。。
end
create trigger tri_before_update_t2 before update on 表名 for each row
begin
sql代码。。。
end
"""
需要注意 在书写sql代码的时候结束符是; 而整个触发器的结束也需要分号;
这就会出现语法冲突 需要我们临时修改结束符号
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
);
delimiter $$ # 将mysql默认的结束符由;换成$$
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
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
('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');
# 查询errlog表记录
select * from errlog;
# 查看触发器
show triggers;
# 删除触发器
drop trigger tri_after_insert_cmd;
事务
什么是事务
开启一个事务可以包含一些sql语句，这些sql语句要么同时成功
要么一个都别想成功，称之为事务的原子性
事务的作用
保证了对数据操作的数据安全性
案例:用交行的卡操作建行ATM机给工商的账户转钱
事务应该具有4个属性：原子性、一致性、隔离性、持久性。这四个属性通常称为ACID特性。
原子性（atomicity）。一个事务是一个不可分割的工作单位，事务中包括的诸操作要么都做，要么都不做。
一致性（consistency）。事务必须是使数据库从一个一致性状态变到另一个一致性状态。一致性与原子性是密切相关的。
隔离性（isolation）。一个事务的执行不能被其他事务干扰。即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
持久性（durability）。持久性也称永久性（permanence），指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的。接下来的其他操作或故障不应该对其有任何影响。
如何用
# 先介绍事务的三个关键字 再去用表实际展示效果
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
insert into user(name,balance)
values
('jason',1000),
('egon',1000),
('tank',1000);
# 修改数据之前先开启事务操作
start transaction;
# 修改操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
# 回滚到上一个状态
rollback;
# 开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘
commit;
"""开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作"""
# 站在python代码的角度，应该实现的伪代码逻辑，
try:
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='egon'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
rollback;
else:
commit;
# 那如何检测异常？
存储过程
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql,类似于python中的自定义函数
基本使用
delimiter $$
create procedure p1()
begin
select * from user;
end $$
delimiter ;
# 调用
call p1()
三种开发模型
第一种
"""
应用程序：只需要开发应用程序的逻辑
mysql:编写好存储过程，以供应用程序调用
优点：开发效率，执行效率都高
缺点：考虑到人为因素、跨部门沟通等问题，会导致扩展性差
"""
第二种
"""
应用程序：除了开发应用程序的逻辑，还需要编写原生sql
优点：比方式1，扩展性高(非技术性的)
缺点：
1、开发效率，执行效率都不如方式1
2、编写原生sql太过于复杂，而且需要考虑到sql语句的优化问题
"""
第三种
"""
应用程序：开发应用程序的逻辑，不需要编写原生sql，基于别人编写好的框架来处理数据，ORM
优点：不用再编写纯生sql，这意味着开发效率比方式2高，同时兼容方式2扩展性高的好处
缺点：执行效率连方式2都比不过
"""
创建存储过程
# 介绍形参特点 再写具体功能
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname from teacher where tid > m and tid < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
如何用存储过程
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
# 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;')
# 3、存储过程与事务使用举例(了解)
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
update user set balance=900 where id =1;
update user123 set balance=1010 where id = 2;
update user set balance=1090 where id =3;
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功
END //
delimiter ;
函数
注意与存储过程的区别，mysql内置的函数只能在sql语句中使用！
参考博客：<http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
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 ;
END //
delimiter ;
索引与慢查询优化
知识回顾:数据都是存在硬盘上的，那查询数据不可避免的需要进行IO操作
索引就是一种数据结构，类似于书的目录。意味着以后再查数据应该先找目录再找数据，而不是用翻页的方式查询数据
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。
- primary key
- unique key
- index key
注意foreign key不是用来加速查询用的，不在我们研究范围之内,上面三种key前两种除了有加速查询的效果之外还有额外的约束条件(primary key:非空且唯一，unique key:唯一)，而index key没有任何约束功能只会帮你加速查询
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
索引的影响:
- 在表中有大量数据的前提下，创建索引速度会很慢
- 在索引创建完毕后，对表的查询性能会大幅度提升，但是写的性能会降低
b+树
https://images2017.cnblogs.com/blog/1036857/201709/1036857-20170912011123500-158121126.png
只有叶子结点存放真实数据，根和树枝节点存的仅仅是虚拟数据
查询次数由树的层级决定，层级越低次数越少
一个磁盘块儿的大小是一定的，那也就意味着能存的数据量是一定的。如何保证树的层级最低呢？一个磁盘块儿存放占用空间比较小的数据项
思考我们应该给我们一张表里面的什么字段字段建立索引能够降低树的层级高度>>> 主键id字段
聚集索引(primary key)
聚集索引其实指的就是表的主键，innodb引擎规定一张表中必须要有主键。先来回顾一下存储引擎。
myisam在建表的时候对应到硬盘有几个文件(三个)？
innodb在建表的时候对应到硬盘有几个文件(两个)？frm文件只存放表结构，不可能放索引，也就意味着innodb的索引跟数据都放在idb表数据文件中。
特点:叶子结点放的一条条完整的记录
辅助索引(unique,index)
辅助索引:查询数据的时候不可能都是用id作为筛选条件，也可能会用name，password等字段信息，那么这个时候就无法利用到聚集索引的加速查询效果。就需要给其他字段建立索引，这些索引就叫辅助索引
特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})
select name from user where name='jason';
上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据
select age from user where name='jason';
上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找
测试索引
准备
#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字段一剑封喉
联合索引
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语句，然后针对性的进行优化！
浙公网安备 33010602011771号