Mysql基础

MySQL数据库

一、基本使用

cls 清屏(windows)

ctrl + l 清屏（linux）

1、开启或关闭mysql服务

（1）windows：超级管理员权限下才能使用

​ 启动mysql：net start mysql

​ 停止mysql：net stop mysql

（2）linux：直接启动即可

​ 启动mysql：service mysql start

​ 停止mysql：service mysql stop

​ 重启mysql：service mysql restart

2、基本权限操作

登录操作

​ musql -u用户名 -p密码 -h地址ip

​ （1）登录到本地mysql，默认用户root是最高权限账户

​ mysql -u用户名 -p密码 -h地址ip

​ （2）退出mysql

​ \q 或 exit

​ （3）远程连接mysql服务器

​ mysql -uroot -p -h192.168.134.131

用户操作

​ （1）查询当前登录的用户

​ select user();

​ （2）设置密码

​ set password = password("1324")

​ （3）去除密码

​ set password = password("")

权限操作

​ ipconfig：windows查询IP命令

​ ifconfig：linux查询命令

​ （1）windows连接远程linux中的mysql

​ create user "用户名"@"192.168.134.131(网络地址)" identified by "1234(设置的登陆密码)";

​ （2）给具体某个网段下的所有ip设置账户 % 代表任意的数据0~255

​ create user "用户名"@"192.168.134.131(网络地址)" identified by "1234(设置的登录密码)"

​ （3）所有的IP都可登录

​ create user "用户名"@"%" identified by "1234(设置的登陆密码)"

"""USAGE 没有任何权限"""
mysql> show grants for "ceshi12"@"%"; 
+-------------------------------------+
| Grants for ceshi12@%                |
+-------------------------------------+
| GRANT USAGE ON *.* TO 'ceshi12'@'%' |
+-------------------------------------+
1 row in set (0.00 sec)

​ （4）grant 权限 on 数据库.表名 to "用户名"%"ip地址" identified by "密码";

"""
# 主要权限
select  查询数据库的权限
insert  插入数据库的权限
update  更新数据库的权限
delete  删除数据库的权限
* 代表所有
"""

​ 查询权限：grant select on *.* to "用户名"@"%" identified by "密码";

​ 所有权限：grant all privileges on *.* to "用户名"@"%" identified by "密码";

​ 查看所有数据库：show databases;

​ 移除权限：revoke all on *.* from "用户名"@"%";

​ 删除账户：drop user "用户名"@"%";

​ 刷新权限，立刻生效：flush privileges

3、数据库详细操作

（1）操作 [ 数据库 ] 文件夹操作

​ 增：create

​ 创建数据库：create database 数据库名 charset utf-8;

​ 删：drop

​ 删除数据库：drop database 数据库名;

​ 改：alter

​ alert database 数据库 charset gbk;

​ 查：show

​ 查看所有的数据库：show databases;

​ 查看数据库的建表语句：show create database 数据库名;

（2）操作 [ 数据库 ] 文件操作

​ 选择数据库

​ use 数据库名

​ 增：create

​ 增加表：create table 表名(字段名1 字段类型2, 字段名2 字段类型2);

​ 删：drop

​ 删除表：drop table 表名

​ 改：alter

​ modify只改数据类型：alter table 表名 modify name char(5);

​ change连字段和数据类型一起改：alter table 表名 change 字段名 要更改的字段名 字段类型;（可以只更改一项）

​ add添加字段：alter table 表 add 要添加的字段 字段类型;（可以使用after在固定的某个字段后面添加一个字段：alter table 表 add 要添加的字段 字段类型 after 字段）

​ add column添加字段：add column列（字段，关键字）：alter table 表 add column age;（添加字段可以不用写column）

​ drop column删除字段：drop column列（字段，关键字）：alter table 表 drop column age;

​ rename 更改表名：alter table 表名 rename 新表名;

​ 查：show、desc

​ 查询所有数据表：show tables;

​ 查看建表的语句：show create table 表名 # ( 加上\G 垂直显示 ,默认横向显示)

​ 查看表结构：desc 表名；

（3）操作记录（文件里面的数据）

​ 注：mysql null 相当于 python None 是一样的代表为空

​ 增：insert

​ 一次插入一条数据：insert into 表名(字段1, 字段2, ...) value(值1, 值2, ...)

​ 一次插入多条数据：insert into 表名(字段1, 字段2, ...) values(值1, 值2, ...), (值1, 值2, ...), ...

​ 注：value和values是一样的，区别是插入一条数据value的执行效率快于values的执行效率；

​ 当插入多条数据是时，values的执行效率快于value的执行效率

​ 不指定具体字段时，默认把所有字段查询一遍：insert into 表 values(值1, 值2, ...)可以使用null占位

​ 可以具体指定某个字段进行插入：insert into 表(字段) value(值)

​ 删：delete

​ 删除id为2的某条数据：delete from 表 where id = 2

​ 删除表中所有数据：delete from 表

​ 改：update

​ 更改符合条件的数据：update 表名 set 字段=值 where 条件

​ 更改所有数据：update 表名 set 字段=值

​ 查：select

​ * 代表所有：select * from 表;

​ 把要搜索的字段写在select后面：select id, name from 表；

二、数据库中常用的数据类型

1、整型

​ tinyint：1个字节 有符号（-128~127）无符号（unsigned） 小整型值

​ int：4个字节 有符号（-21亿~21亿）无符号（0~42亿） 大整型值

1.1、布尔类型

​ MySQL中的布尔类型是通过tinyint来实现的，tinyint(0) 是布尔类型的False，tinyint(1) 是布尔类型的True。

2、浮点型

​ float：（255，30）单精度，float存在四舍五入，小数位默认保留5位

​ double：（255，30）双精度，double存在四舍五入，小数位默认保留16位

​ decimal：（65，30）金钱类型，使用字符串的形式保存小数，decimal也存在四舍五入，默认保留整数

	"""存在四舍五入"""
	create table t2(f1 float(5,2) , f2 double(5,2) , f3 decimal(5,2));
	insert into t2 values(1.666666666666666666666666666,1.666666666666666666666666666,1.666666666666666666666666666);		

	"""float 小数位默认保留5位,double小数位默认保留16位,decimal 默认保留整数位,存在四舍五入"""
	create table t3(f1 float , f2 double , f3 decimal);
	insert into t3 values(1.666666666666666666666666666,1.666666666666666666666666666,1.666666666666666666666666666);		
	
	# float(5,2) 5=>代表总长度(整数+小数)  2=> 小数长度,保留2位
	create table t6(f1 float(5,2));
	insert into t4 values(12.34567);
	insert into t5 values(1234.34567);y

3、字符串

注：varchar（）开辟空间默认预留2个字节空间：像varchar（5）存储的中文实际开辟空间是5+中文（3）+2个字节的空间

​ char：固定开辟空间长度，但开辟空间速度快；牺牲空间换取时间；像手机号、身份证号等，字符长度255个

​ varchar：不固定开辟空间长度，可根据存入值开辟对应长度空间，但开辟空间速度慢；

​ 牺牲时间，换取空间；例如：小广告或评论等；字符长度21845个

​ text：文本类型，针对于很长的字符串，像小说，文章等

4、concat拼接

​ 查询当前用户：select user()

​ concat：把所有的参数拼接在一起

​ select concat(参数1, 参数2, ...)

5、枚举和集合

​ enum 枚举：从列出来的数据当中选一个（例如：性别）

​ set集合：从列出来的数据当中选多个（自动去重）

create table 表(
	id int , 
	name char(10), 
	money float(6,2) , 
	sex enum("man","woman"),
	hobby set("beat_doudou","smoke","drink","tang_head")	
	)
	
	# 正常写法
	insert into 表(id,name,money,sex,hobby) values(1,"张三",9.66666,"woman","smoke,tang_head");
	
	# 自动去重
	insert into 表(id,name,money,sex,hobby) values(1,"张三",9.66666,"woman","beat_doudou,beat_doudou,beat_doudou,beat_doudou");

6、时间数据类型

​ date：YYY—MM—DD 年月日 （结婚纪念日，节假日）

​ time：HH：MM：SS 时分秒（体育竞赛）

​ year：YYYY 年份值（历史，1882年的某某饮品）

​ datetime：YYYY—MM—DD HH—MM—SS 年月日 时分秒（登录时间，下单时间），输入null就写入null

​ timestemp：YYYYMMDDHHMMSS（时间戳）自动更新时间（不需要手动写入，修改数据的时候自动更新，

​ mysql内置函数：now 获取当前时间 select now();

create table t5(d date , t time , y year , dt datetime);
insert into t5 values("2020-06-17","09:15:30","2020","2020-06-17 09:15:30");
insert into t5 values(now(),now(),now(),now());

​

三、约束

​ 对编辑的数据进行类型限制，不满足约束条件的直接报错

​ unsigned：无符号

​ not null：不为空

​ default：设置默认值

​ unique：唯一约束，数据唯一不能重复

​ 索引：相当于字典的目录，通过索引可以加快查询速度

​ UNI 表示唯一索引，允许null空值；

​ primary key：主键，标记数据的唯一特征（唯一且不为空的数据）

​ 注：主键非空且唯一，在一个表里面，只能有一个字段是主键

​ auto_increment ：自增加一（一般配合主键使用或者unique进行自增）

​ delete：单纯的删除数据，数据id号从上一个继续自增

​ truncate：删除所有数据，id从开头开始（重置表）

​ zerofill：0 填充（配合int类型使用），int（6），位数不够，拿0来凑，少了自动扩充

​ foreign key：外键，把多张表通过一个关联字段，联合在一起

# unsigned
	create table t7(id int unsigned);
	insert into t7 values(66);
	insert into t7 values(-66); error
	
# not null
	create table t8(id int not null , name varchar(255));
	insert into t8 values(1,"xll");
	insert into t8 values(null,"xll");  error
	insert into t8(name) values("xll"); error
	
# default
	create table t9(id int not null , name varchar(255) default "pdd");
	insert into t9 values(1,null);
	insert into t9(id) values(1);

# unique
	create table t10(id int unique , name char(10) default "pdd");
	insert into t10(id) values(1);
	insert into t10(id) values(1);  # error  不能重复
	insert into t10(id) values(12);
	insert into t10(id) values(null);
	insert into t10(id) values(null);
# primary key    主键,标记数据的唯一特征(唯一且不为空的数据) [创建表: 字段 类型 约束 ...]
	"""PRI 主键 非空且唯一, 在一个表里面,只能有一个字段是主键"""
	create table t11(id int not null unique , name char(10) default "pdd");
	insert into t11 values(1,"你好");
	insert into t11 values(null,"你好啊"); error
	
	# primary key 创建主键
	create table t12(id int primary key , name char(10) default "pdd");
	insert into t12 values(1,"aaa");
	
	# 两者同时存在 (优先显示primary key 作为主键, 另一个被标记成UNI 唯一索引)
	create table t13(id int primary key , name char(10) not null unique);
	
	# 一个表里面只能有一个主键
	create table t13(id int primary key , name char(10) primary key);
	
# auto_increment 自增加一(一般配合主键使用 或者 unique进行自增)
	create table t14(id int primary key auto_increment , name char(10) default "pdd");
	insert into t14 values(1,"张三");
	insert into t14 values(2,"张三");
	insert into t14 values(null,"张三");
	insert into t14(id) values(null);
	# 使用默认值进行插入
	insert into t14 values();
	
	# delete 单纯的删除数据,数据id号从上一个继续自增
	delete from t14;
	# truncate 删除所有数据, id从头开始 (重置表)
	truncate table t14

# zerofill       0填充(配合int类型使用) , int(6) , 位数不够位,拿0来补充
	create table t15(id int(6) zerofill );
	insert into t15 values(2);
	insert into t15 values(2222);
	insert into t15 values(2222222222);

四、联合唯一约束

​ unique(字段1，字段2，字段3，...) 把多个字段拼在一起表达唯一的数据

​ MUL：代表普通索引；UNI：代表唯一索引；PRI：主键索引

​ （1）联合唯一索引（都为非空的字段desc显示的PRI，联合在一起做的主键，不是单个字段的主键）

create table t1_server(id int, server_name char(10) not null , ip char(15) not null , port int not null , unique(ip,port));			
insert into t1_server values(1,"aaa","192.168.56.31",5000);
insert into t1_server values(1,"aaa","192.168.56.31",6000);
insert into t1_server values(1,"aaa","192.168.56.40",6000);

​ （2）联合唯一索引（为空的字段，允许插入null，显示MUL）

create table t2_server(id int, server_name char(10) not null , ip char(15) , port int , unique(ip,port));			
insert into t2_server values(1,"aaa","192.168.56.31",5000);
insert into t2_server values(1,"aaa","192.168.56.31",6000);
insert into t2_server values(1,"aaa","192.168.56.40",6000);
insert into t2_server values(1,"aaa",null,null); # 注意点,可以插入多个空值;

​ （3）联合唯一索引和主键同时存在

​ primary key 是真正的主键，联合唯一索引就会变成MUL普通索引；

​ unique(id,port) 联合唯一索引

​ primary key(id,port) 联合唯一主键

​ 二者使用的方式是一样的，区别是：前者可以继续添加一个主键，而后者不能继续添加主键；

​ 主键只能是单个字段，或者联合主键，如果再加就会报错；

​ （4）foreign key：外键，把多张表通过一个关联字段，联合在一起

​ 外键的要求：关联的字段必须具有唯一属性（unique 或者 primary key）

​ 为了避免出现过多的字段，可以采用分表的形式，来提升效率，减少数据的冗余

​ （5）联级操作、联级更新（谨慎操作）

​ 联级删除：on delete cascade

​ 联级更新：on update cascade

student1
    id  name      age  address               classid
    1   wangzhen  80   北京市天安门阁楼里    1
    2   xiaolin   90   东北老革命工业基地    1
    3    wangwen  18   内蒙古呼和浩特蒙古包  2
		
class1:
    id  classname  datetime
    1   python30   2020-01-01 09:09:09
    2   python31   2020-02-01 09:09:09

# 创建class1表
create table class1(id int , classname varchar(255));
# 被关联的字段至少需要具有唯一属性
alter table class1 add unique(id);
# 创建student1学生表
create table student1(
    id int primary key auto_increment,
    name varchar(255) not null,
    age int not null,
    classid int,
    foreign key(classid) references class1(id)
);
insert into class1 values(1,"python30");
insert into class1 values(2,"python31");
insert into student1 values(null,"wz",80,2);
insert into student1 values(null,"xl", 90,1);
insert into student1 values(null,"ww", 18,2);

# 删除class1 如果这条数据在其他表里存在,直接删会报错,因为外键的关联限制
delete from class1 where id = 1;
# 先把关联的数据都删了之后,才可真正删掉这条数据
delete from student1 where id = 2;
delete from class1 where id = 1;

# 联级删除 联级更新 (谨慎操作)
"""
联级删除 on delete cascade
联级更新 on update cascade
"""
	
# 创建class2
create table class2(id int unique , classname varchar(255));
# 创建student2
create table student2(
id int primary key auto_increment,
name varchar(255) not null,
age int not null,
classid int,
foreign key(classid) references class2(id) on delete cascade on update cascade);
	
insert into class2 values(1,"python30");
insert into class2 values(2,"python31");
insert into student2 values(null,"hen",80,2);
insert into student2 values(null,"lin", 90,1);
insert into student2 values(null,"angn", 18,2);
	
# 联级删除
delete from class2 where id = 2
# 联级更新
update class2 set id =100 where classname="python30"

五、表与表之间的关系

（1）一对一：表1中的主键(唯一索引)关联表二中的主键(唯一索引)字段形成一对一的关系。

（2）一对多或多对一：一个班级里可以对应多个学生，把学生作为主动关联的表，设置一个外键，去存储班级表的关联字段中的数据

（3）多对多：一个学生可以对应多个学科，一个学科也可以被多个学生学习

xueke (表1)
id  name
1   math
2   english
3   wuli

student (表2)
id  name
1   wangwen
2   weiyilin
3   wangyingqian

# 表达多对多关系时,需要第三张关系表
relation (表3)  把xid  和 sid 设置成外键  关联xueke的id  和 student的id

xid  sid
1    1
1    2
1    3
2    1
2    2
2    3
3    1
3    2
3    3

六、存储引擎

存储数据的结构方式

查看存储引擎命令：show engines；

概念：

​ 表级锁：如果有人修改了当前表，会直接上表锁，其它人无法修改，在编辑数据的时候，速度慢，不能高并发（MyISAM特有）

​ 行级锁：如果有人修改了当前这个表中的一条记录，当前这个数据记录会上锁，其它数据仍然可以正常修改，速度快，允许更高的并发（InnoDB）

事务处理

​ 支持事务处理：如果执行sql语句，在全部成功之后，再选择提交数据，有一条失败，立刻回滚，恢复成原来的状态。

​ begin：开始事务

​ commit：提交数据

​ rollback：回滚数据

InnoDB：5.6版本后的默认存储引擎，支持事务处理，行级锁，外键，支持高并发

MyISAM：5.6版本前的默认存储引擎，支持表级锁，并发效率不高

MEMORY：把数据放在内存中，用做缓存

BLACKHOLE：黑洞，用来同步主从数据库中的数据，场景发生在服务器并发集群，用在主从数据库当中[主数据库：增删改操作，从数据库：查询操作]

E:\Lenovo wenjian\MySQL\mysql-5.7.25-winx64\ceshi
create table  myisam1(id int,name char(10)) engine = myisam;
myisam1.frm 表结构
myisam1.MYD 表数据
myisam1.MYI 表索引

create table  innodb1(id int,name char(10)) engine = innodb;
innodb1.frm 表结构
innodb1.ibd 表数据+表索引

create table  memory1(id int,name char(10)) engine = memory;
memory1.frm 表结构
# 没有表数据文件,因为把数据存放在内存中了.
create table  blackhole1(id int,name char(10)) engine = blackhole;
blackhole1.frm 表结构

七、单表查询

select . . . from . . . where . . . group by . . . having . . . order by . . . limit . . .

1、while 条件的使用：

​ 对表中数据的筛选和过滤

​ （1）判断的符号：

​ = > >= < <= != <>不等于

​ （2）拼接条件关键字：

​ and or not

​ （3）查询区间范围值：between

​ between 小值 and 大值 [小值，大值] 查询两者之间这个范围内所有数据

​ （4）查询具体某个值的范围：in

​ in(1,2,3) 指定范围

​ （5）模糊查询 like "%" "_" 通配符：

​ like "%a"：匹配以a结尾的任意长度的字符串

​ like "a%"：匹配以a开头的任意长度的字符串

​ like "%a%"：匹配含有字母a的任意长度的字符串

​ like "_a"：个数一共两个，以a结尾的字符串

​ like "a_"：个数一共两个，以a开头的字符串

​ （6）distinct 去重

​ distinct 字段
​ distinct(字段)

（1）单条件的查询：
（2）多条件的查询
（3）关键字between... and...
（4）null 关键字：进行判定的时候使用is进行判定，不要用=判定
（5）关键字 in 在 ... 之重 查询
（6）模糊查询 like "%" "_" 通配符
（7）concat(拼接；可使用as 起别名)
（8）concat_ws(拼接的符号，参数1，参数2，...)
（9）内部可使用四则运算符（+ - * /）

2、group by 子句 分组分类

group by 字段 对数据进行分类，by后面接什么字段，select就搜索什么字段

group_concat 按照分类形式进行字段的拼接

聚合函数
	count 统计总数 * 所有
	max 统计最大值
	min 统计最小值
	avg 统计平均值
	sun 统计总合

一般境况下：分组 + 聚合函数 配合使用

3、having 过滤

having 数据在分类分组之后，进行二次数据过滤，一般是配合group by 使用，分组之后再过滤

4、order by 排序

按照字段进行排序

order by asc 默认升序：从小到大

order by desc 降序：从大到小

5、limit 限制查询条数（数据分页）

limit m, n ：m代表从第几条数据开始查，n代表查询几条，m=0数据库表中的第一条数据

两个参数，参数1默认从0开始算，0是第一条，参数2是查询条数
select * from 表 limit 0,5
只查一条数据，默认从第一条
select * from 表 limit 1
找数据库当中最后一条数据
select * from 表 order by 字段 desc limit 1 
找数据库当中最后三条数据
select * from 表 order by 字段 desc limit 3 desc

6、可以使用正则表达式查询数据（不推荐使用，效率不高）

select * from 表 where 字段 regexp ".*on$"  # 以什么结尾，没有？，问号不能使用
select * from 表 where 字段 regexp "^程" 以什么开头
select * from 表 where 字段 regexp "^程.*金"

八、多表查询

1、内连接（内联查询）：两个表或者多表满足条件的所有数据查询出来（两表之间共有的数据）

（1）两表查询：
select 字段 from 表1 inner join 表2 on 必要的关联条件
（2）多表查询：
select 字段 from 表1 inner join 表2 on 必要条件1 inner join 表3 on 必要条件3 ...
（3）as 起别名
（4）where 默认实现的就是内联查询

2、外连接

(1) union: 去掉重复元素

(2) union all　　保留重复元素

（1）左连接（左联查询 left join）以左表为主，右表为辅，完整查询左表所有数据，右表没有的数据补null
	select * from employee left join  department on employee.dep_id = department.id;
（2）右链接（右联查询 right join）以右表为主，左表为辅，完整查询右表所有数据，左表没有的数据补null
	select * from employee right join  department on employee.dep_id = department.id;
（3）全连接（全连接 union）所有数据都合并起来，空缺的补null
	select * from employee left join  department on employee.dep_id = department.id
	union
	select * from employee right join  department on employee.dep_id = department.id;
	
	select * from employee left join  department on employee.dep_id = department.id
	union all
	select * from employee right join  department on employee.dep_id = department.id;

九、子查询

​ 子查询可以单独作为一个临时数据，临时的表，临时的字段，一般用在from where select 字句后面，可以通过查询出来的临时数据和另外的表联合，变成一张更大的表，再做单表查询查到想要的数据

子查询：嵌套查询

​ （1）sql语句当中又嵌套了另外一条sql语句，用（）包起来，表达一个整体

​ （2）一般应用在from子句后面表达一张表，where子句后面表达一个条件

​ （3）查询速度从快到慢：单表查询 -> 联表查询 -> 子查询

（1）普通where写法
    select 
    d.id,d.name
    from
    employee as e,department as d
    where
    e.dep_id = d.id
    group by
    d.id,d.name
    having 
    avg(e.age) > 25;

（2）inner join
    select 
    d.id,d.name
    from
    employee as e inner join department as d on e.dep_id = d.id
    group by 
    d.id,d.name
    having 
    avg(e.age) > 25;
（3）子查询
	1.先选出平均年龄大于25岁的部门id
		select dep_id from employee group by dep_id having avg(age) > 25;
	2.通过部门id,找部门的名字
		select name from department where id in (201,202);
	3.综合拼接
		select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25);
（4）
	1.找技术部门对应id
		select id from department where name = "技术";
	2.通过id找员工姓名
		select name  from employee where dep_id = 200;
	3.综合拼接
		select name,dep_id  from employee where dep_id = (select id from department where name = "技术");
	
（5）联表写法(把null的数据露出来 , 员工的部门dep_id 为null,代表这个部门没人.)
	select 
		d.id,d.name
	from 
		department as d left join employee as e on d.id = e.dep_id
	where 
		e.dep_id is null
（6）子查询
	1.先查询,员工在哪些部门 (所有员工的分类分别是 200 201 202 204)
		select dep_id from employee group by dep_id
	2.把不在部门的数据找出来
		select id  from department where id not in (200,201,202,204);
	3.综合拼接
		select id,name  from department where id not in (select dep_id from employee group by dep_id)
	

十、带EXISTS关键字查询

exists 关键字，表达存在

如果内层sql能够查询到数据，返回True，外层sql执行查询语句

如果内层sql不能查到数据，返回False，外层sql不执行查询语句

select * from employee where exists (select * from employee where id = 1) # 能
select * from employee where exists (select * from employee where id = 100) # 不能

补充：约束的添加和删除

1、添加/删除 约束 not null
	alter table 表名 modify 字段名 类型
	alert table 表名 modify 字段 类型 not null
	alert table 表名 modify 字段 类型 
2、添加/删除 unique 唯一索引
	alter table 表名 add unique(字段)
	alter table 表名 drop index 字段
3、添加/删除 primary key
	alter table 表名 add primary key(字段)
	alter table 表名 drop primary key
4、添加/删除 foreign key 外键(先通过desc表找到外键名字，然后再删)
	alter table 表 drop foreign key 字段
	alert table 外键表 add foreign key(外键字段) reference 关联表(关联字段)

十一、py中操作数据库

​ 在python中操作数据库需要使用到pymysql模块，这种形式的数据库操作主要用没有现成的框架可以操作数据库的时候，对数据库进行手动操作的模块

import pymysql
# 基本语法
# (1) 创建连接  host  user  password  database 这四个参数必须写，还有port端口参数，charset编码参数等等
conn = pymysql.connect(host='127.0.0.1', user="root", password='1234', database='ceshi', port="3306", charset="utf8")
# 注：port一般默认3306端口
# (2) 创建游标对象，该对象可以进行增删改查操作
cursor = conn.cursor()
# (3) 执行sql语句
sql = "select * from t1"
# 返回的是数据的总条数
res = cursor.execute(sql)
print(res)
# (4) 获取数据
res = cursor.fetchone() # res的返回值依具体情况而定
print(res)
# (5) 释放游标对象
cursor.close()
# (6) 关闭连接
conn.close()

事务处理

​ python中通过pymysql模块操作事务处理，必须通过commit提交数据，才会真正的更新数据，否则rollback回滚，恢复到以前状态

conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0618")
cursor = conn.cursor()
sql1 = "begin"
sql2 = "select * from employee"  # 只有查询得到了返回值，其它都是无状态0
sql3 = "update employee set emp_name = '111egon'  where id = 1 " 
sql4 = "commit"

res1 = cursor.execute(sql1)
res2 = cursor.execute(sql2)
res3 = cursor.execute(sql3)
# res4 = cursor.execute(sql4)
# print(res1,res2,res3,res4)
# print(cursor.fetchone())

cursor.close()
conn.close()

SQL注入问题

​ sql的注入问题是因为的执行漏洞，在python中通过使用mysql中的注释符间接跨过数据库表的查询条件，获得用户的访问权限。

# 不安全写法：就是直接通过格式化字符串和format替换去操作数据
import pymysql
user = input("user>>>: ").strip()
pwd = input("password>>>: ").strip()

conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
cursor = conn.cursor()

sql = "select * from usr_pwd where username='%s' and password='%s'  " % (user,pwd)
print(sql)

res = cursor.execute(sql)
print(res) # 查询数据的个数

if res:
	print("登陆成功")
else:
	print("登录失败")
	
cursor.close()
conn.close()
'''
输入类似: 字符串' or 10 = 10 -- 字符串
原理是：select * from usr_pwd where username='%s' and password='%s'  
输入：123' or 10 = 10 -- 456 字符串执行语句变成
	 select * from usr_pwd where username='123' or 10 = 10 -- 456 and password='%s' 
但是mysql中 -- 是注释，所以语句变成了：
	 select * from usr_pwd where username='123' or 10 = 10
又因为条件 or 一真即真的特点，语句会正常执行完毕，因此获得操作权限
'''


# 安全写法，也就是解决办法，在pymysql中已经封装好了。可以表面绝大多数的sql注入问题
# execute 参数1是一个sql语句,如果sql语句和里面的参数值分开执行,默认开启预处理
# execute(sql , (参数1,参数2,参数3))
import pymysql
user = input("user>>>: ").strip()
pwd = input("password>>>: ").strip()

conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
cursor = conn.cursor()
sql = "select * from usr_pwd where username=%s and password = %s"
res = cursor.execute(sql, (user,pwd))

print("登录成功" if res else "登录失败")

cursor.close()
conn.close()

python操作mysql增删改查

​ python操作pymysql的时候，默认是开启事物的，必须在增删改查之后，统一提交数据，才会对数据库产生影响，否则默认回滚，回到没有修改前的状态。

（1）pymysql中封装好了提交数据的方法：conn.commit(注：conn是创建数据库连接，名字不固定，不一定是conn)

（2）pymysql中封装好了回滚数据的方法：conn.rollback(注：同上一个一样)

（3）查询数据,默认是元组,可以设置返回字典类型：cursor=conn.cursor(cursor = pymysql.cursors.DictCursor)

（4）单条数据操作（针对数据库语句的操作）：游标对象.execute()

（5）多条数据操作（针对数据库语句的操作）：游标对象.executemany()

​ 注：插入后的返回值是第一条数据的id，插入多条语句时，默认也是返回插入的第一条数据的id，但要注意不能使用：insert into 表 value（null，"值1","值2"）这种方式，这种方式默认返回值是最后一条数据的id

（6）单条数据获取（获取数据库的查询结果）：游标对象.fetchone()

（7）多条数据获取（获取数据库的查询结果）：游标对象.fetchmany()

​ 注：默认fetchmany()是只获取一条数据，加参数是获取多条数据；

​ 无论是fetchone()还是fetchmany()，都是默认从上一条数据继续向下搜索(性质类似于迭代器)

（8）获取所有数据fetchall()基于上一条数据往下搜索(性质类似于迭代器)；默认查询所有符合条件的数据

（9）数据滚动：

• 相对滚动：基于相对位置的数据进行滚动

  使用方法：游标对象.scroll(滚动条数,mode="relative")

  滚动条数为负数时是向后滚动数据，正数是向前滚动数据

  当以表中的第一条数据为基础（相对位置）时向后滚动报错

  向前滚动数据时超出数据范围（表数据到最后一条了）也会报错

• 绝对滚动：永远基于第一条数据的位置滚动

  当以表中的第一条数据为基础（绝对位置）时向后滚动报错，智能向前滚动数据

  向前滚动数据时超出数据范围（表数据到最后一条了）也会报错

# 1.创建mysql连接
conn = pymysql.connect(host="127.0.0.1",user="root",password="",database="db0619")
# 查询数据,默认是元组,可以设置返回字典类型, pymysql.cursors.DictCursor
cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)

# 增
sql = "insert into t1(first_name , last_name ,age ,sex,money) values(%s,%s,%s,%s,%s)"
# 一次插入一条数据
res = cursor.execute(sql,("王","振",80,0,12000))
print(res)

# 一次插入多条数据
res = cursor.executemany(sql,[("尉","翼麟",18,1,12000),("谢","晨",80,0,200),("主","胜",3,0,9.9)])
print(res)


# 获取最后插入这条数据的id号(针对于单条数据执行,获取最后的id,如果多条数据的执行,以第一条数据的id为主)
print(cursor.lastrowid)
# 针对于多条数据最后的id,可以通过倒序查询,找到id号
# select id from t1 order by id desc limit 1


# 删
sql = "delete from t1 where id = %s"
res = cursor.execute(sql,(8,))
print(res)

if res:
	print("删除成功")
else:
	print("删除失败")
    
# 改
sql = "update t1 set first_name = %s where id = %s"
res = cursor.execute(sql,('王',10))
print(res)

if res:
	print("更新成功")
else:	
	print("更新失败")
    

# 查
sql = "select * from t1"
res = cursor.execute(sql)
print(res) # 总条数

# (1) 获取一条数据 fetchone
res = cursor.fetchone()
print(res) # {'id': 9, 'first_name': '王', 'last_name': '翼麟', 'age': 18, 'sex': 1, 'money': 12000.0}

# (2) 获取多条数据 fetchmany 默认从上一条数据继续向下搜索(性质类似于迭代器)
data = cursor.fetchmany() # 没有参数,默认只获取一条
data = cursor.fetchmany(3)  
print(data)

for row in data:
	first_name = row["first_name"]
	last_name = row["last_name"]
	age = row["age"]
	if row["sex"] == 0:
		sex = "女性"
	else:
		sex = "男性"
	money = row["money"]
	print("姓:{},名:{},年龄:{},性别:{},收入:{}".format(first_name,last_name,age,sex,money))


# (3) 获取所有数据 fetchall 基于上一条数据往下搜索(性质类似于迭代器)
data = cursor.fetchall()
print(data)

# (4) 自定义搜索查询的位置
sql = "select * from t1  where id >=10"
res = cursor.execute(sql)
res = cursor.fetchone()
print(res)    

# 1.相对滚动 (向后)
cursor.scroll(3,mode="relative")
res = cursor.fetchone()
print(res)

# 方向向前 (无论前后,不能超出范围)
cursor.scroll(-3,mode="relative")
res = cursor.fetchone()
print(res)

# 2.绝对滚动 , 永远基于第一条数据的位置滚动
cursor.scroll(0,mode="absolute")
print(cursor.fetchone())

cursor.scroll(1,mode="absolute")
print(cursor.fetchone())

cursor.scroll(2,mode="absolute")
print(cursor.fetchone())

# 往前滚没数据,超出范围error
cursor.scroll(-1,mode="absolute")
print(cursor.fetchone())

# 在进行增删改的时候,必须通过commit提交数据,才会对数据库进行更新,否则默认回滚.
conn.commit()
cursor.close()
conn.close()

十二、SQL语句优化

1、SQL语句优化

1、mysql的执行流程

客户端：

​ 发送连接请求，然后发送增删改查sql语句执行操作

服务端：

​ （1）连接层：提供和客户端连接的服务，在tcp协议下，提供多线程的技术，让多个用户登录到mysql中

​ 可使用命令：show processlist; 查看所有登录到mysql的用户进程

​ （2）服务器：提供各种接口（增删改查等操作）分析器组件会解析用户的sql语句；

​ 如果发现sql语句执行效率较低，会提交给优化器组件进行优化，然后执行

​ （查询缓存：把上次搜索过的数据，或者提前存储的数据直接返回，效率加快）

​ （优化器：mysql query optimizer）

​ （3）存储引擎：存储或者提取数据

​ innoDB：支持事务处理，支持行级锁，支持高并发

​ myisam：支持表级锁，不支持高并发

​ （4）日志文件：产生binlog日志（二进制文件）

创建表的时候可以在语句最后面设置引擎和编码集，还可以设置起始的id

create table ceshi_table1(
id int primary key auto_increment,
name varchar(255)
)engine = myisam auto_increment=3 charset=utf8;

2、sql卡顿原因

​ 硬盘读写数据，io延迟高，sql语句性能低，导致sql执行的时间漫长；表中的数据没有索引，并且数据量大，也会造成sql语句查询速度慢

编写：select ... from ... join on ... where ... group by ... having ... order by ... limit

解析：from ... join on ... where ... group by ... having ... select ... order by ... limit

3、索引

索引（index）概念：

​ 是一个树状的数据结构，即（B树结构，分支节点>2）

​ 相当于字典的目录，功效是加快查询速度

​ 常用树：B树（banlance-tree），二叉树，红黑树，hash树

树节点的概念：

​ 根节点（最顶级的节点）

​ 分支节点（两种状态，父节点，子节点）

​ 叶子节点（最后一层存储数据的节点）

​ 树的高度（树的层级）

B树【b-tree】理想状态下三级，任何数据最多三次查到，支持百万级别的数据查询，追求树的矮胖结构

B+树【b+tree】：在相邻的叶子节点上，加入双向链表（指针），当前叶子节点不但保存了数据，还保存了上下两个节点的地址【小范围数据中，加快查询数据】

B*树【b*或b++ tree】在相邻的分支节点（包含叶子节点）上，加入双向链表（指针），当前分支节点（包含叶子节点）不但保存了数据，还保存了上下两个节点的地址【大范围数据中，加快查询数据】

注：磁盘块 block 数据页 16k；myisam和innodb都是b+树结构

4、innodb和myisam的索引结构

（1）聚集索引【innodb存储的特点，myisam不支持】：

​ 如果有主键，自动以主键创建聚集索引的数据结构（树状结构）；

​ 如果没有主键，选择唯一键；

​ 都没有，自动生成隐藏的聚集索引，也会分出一个字段占用6个字节长整型；

​ 叶子节点上面直接存储真实数据（索引和数据捆绑在一起）

​ 分支节点存储的是索引的最小值，用来划分范围

​ 在数据量变大的时候，尽量在树层级高度不变的情况下横向发展；好处：查询次数少，提升效率，减少io阻塞

聚集索引：默认把id变成索引，形成一套B+树id的数据结构结构，数据和索引捆绑在一个文件中，找到了id等于找到了所有数据

（2）非聚集索引（辅助索引，二级索引，普通索引）

​ 先对创建索引的该字段划分区间进行排序，把索引值分布在叶子节点上；

​ 存储的是该字段的值以及对应映射的主键id（primary key），没有真实数据

​ 通过主键id，再去从其它文件中找数据

非聚集索引：如果存的是字母，根据ascii表先排序，从小到大，然后根据索引查找到对应的id（仅id），拿到id后在进行查询id所对应的数据

（3）两者区别

​ myisam和innodb使用的索引结构都是b+树，但是叶子节点存储的数据不同

​ innodb文件结构中只有frm，ibd直接把数据存在叶子节点上

​ myisam文件结构中有frm，myi，myd，叶子节点上存储的索引值，通过索引找到id，通过id查找数据

（4）性能优化：

​ 利用索引查询时，可以增快查询速度，但是增删改速度变慢，会改变树状结构

​ 追求尽量让叶子节点存储的数据类型小一点，让高度变矮，让数据页变少

2、索引

1、常用索引

单个字段索引

​ 主键索引：primary key （非空且唯一）

​ 唯一索引：unique（唯一）

​ 普通索引：index （单纯的加个索引，为了提升查询效率）

联合索引

​ primary key（字段1，字段2，...）：联合主键索引

​ unique（字段1，字段2，...）：联合唯一索引

​ index（字段1，字段2，...）：联合普通索引

2、应用场景

编号:int
姓名:varchar(255)
身份证号:char(18)
电话char(11)
地址varchar(255)
备注:text
姓: varchar(10)
名: varchar(10)


编号: 主键
姓名: 普通索引(注意在区分度高的字段上加)
身份证:unique
电话:unique
备注:全文索引 , 借助第三方软件sphinx来运行
姓和名:联合索引 , 联合在一起查,加快速度

3、不同的存储引擎支持的数据结构

innodb：支持b-tree fulltext（全文索引） 不支持hash类型索引结构

myisam：支持b-tree fulltext（全文索引） 不支持hash类型索引结构

memory：支持b-tree hash类型 不支持fulltext（全文索引）

hash类型索引：数据放内存中，通过键来获取到值，单条数据查询快，一个范围内的数据慢

b-tree：最理想的三层结构，理论上可支撑百万条数据的查询；

4、建立索引

（1）建表的时候，直接创建索引index索引名（索引字段）

create table t1()
	id int primary key,
	name char(10),
	index index_name(name)
);

（2）创建表之后，创建索引 create index 索引名 on 表名（索引字段）

create table t2(
	id int primary key,
	name char(10)
);
create index index_name on t2(name);

（3）改变字段索引 alter table 表名 add index 索引名（索引字段）

create table t3(
	id int primary key,
	name char(10)
);
alter table t3 add index index_name(name);

（4）删除索引

drop index index_name on t3;

5、正确使用索引

加索引和不加索引的速度差别巨大，加了索引之后，ibd文件变大

（1）把频繁作为搜索条件的字段作为索引，查询单条数据，如果查询的是一个大范围中的数据，不能命中索引

​ 表达范围的符号：> < >= <= != <> like between and in

select * from s1 where id > 5;  
select * from s1 where id < 5; # 表达一个小范围内的数据可以命中.

（2）选一个区分度较高的作为索引

选区分度低的字段作为索引，在查询数据的时候，先走索引建好的树状结构，再把数据搜出来
因为树状结构中有大量的重复数据，会增加树的高度，反而速度不快，冗余数据过多
默认系统会把主键或者unique标识的约束，自动创建索引，因为区分度较高，没有冗余数据
create index index_name on s1(name); # 不推荐把区分度不高的字段加索引

（3）在搜索条件中，不能让索引字段参与计算，不能命中索引

select * from s1 where id = 1000;
select * from s1 where id*3 = 3000; # id = 1000

（4）当条件中含有and，sql语句会通过优化器进行优化

• 如果有and相连，找到第一个有索引的并且树的高度最矮的字段进行优化

select count(*) from s1 where email = "xboyww1000@oldboy"
select count(*) from s1 where email = "xboyww1000@oldboy" and id = 1000;
select count(*) from s1 where email = "xboyww1000@oldboy" and name =  "xboyww";
select count(*) from s1 where email = "xboyww1000@oldboy" and name =  "xboyww" and id = 1000;
多个and条件依然成立

• 如果有or相连，没有优化，所有语句从左到右执行，让索引失去意义

select count(*) from  s1 where id = 1000 or email = "xboyww1000@oldboy";

（5）联合索引：遵循最左原则index（字段1，字段2，...）

select count(*) from s1 where first_name = "王6" and last_name="文6"  # 命中索引
select count(*) from s1 where last_name="文6" and  first_name = "王6" # 命中索引
select count(*) from s1 where last_name="文6" # 不能命中索引
select count(*) from s1 where first_name="王6" and gender="man";
select count(*) from s1 where first_name="王6" and gender="man" and name="xboyww";
# 最左前缀原则:被标记成MUL这个字段,必须存在在搜索条件中,就命中索引
first_name + .... (必须该字段存在) 联合索引会更加精确的命中想要的数据.数据结构更合理;

（6）其它

• 数据类型不匹配，不能命中索引

  select count(*) from s1 where first_name = 100;  # first_name 是字符串
  

• 使用了函数不能命中索引

  select count(*) from s1 where reverse(first_name) = "6王";  # 字符串反转函数reverse()
  

十三、索引树高度

1、表的数据量

数据量越大，树的高度就会变高，理论上三层索引树的高度最为理想，可以支持百万级别的数据量

解决：可以使用分表（横切，竖切），分库，增加缓存，解决数据量大，查询慢的问题

2、索引键值过长

该索引字段存储数据太大，每个叶子节点最大存储16K，超过这个范围会新增加叶子节点和分支节点

解决：前缀索引（截取前5个长度）

3、数据类型

char（定长） varchar（变长）从开辟空间速度来看，char快

从数据结构上来看，varchar更合理

（1）避免使用select * ，不确定表大小的时候，使用count（*）查一下数据

（2）尽量使用数据类型较小的字段做索引

（3）重复值少的，区分度高的字段索引，性别这样的字段不要做索引

（4）在多表查询时使用join，尽量少的使用子查询

十四、执行计划分析

desc/explain

执行计化：在一条sql执行之前，制定执行的方案

desc select * from s1；

1、select_type

​ simple：代表的是简单查询（单表查询，不包括子查询，union）

​ primary：sql嵌套中的主查寻（最外层）

​ subquery：sql嵌套中的子查询（最里面）

​ derived：衍生查询（把子查询结果作为一张临时表）

2、table

​ 在多表或者子查询的时候，通过table分析出出问题的表是谁

3、type

​ 显示执行计划的类型，优先级从低到高如下，优化时，至少达到range或者ref级别

​ all < index < range < ref < eq_ref < const < system

​ （1）all全表扫描(不走索引)慢查询

​ 1）在大范围内查询 > < >= <= != between and in like

​ 2）where条件中有计算，有函数

​ 3）数据类型不匹配

​ 4）拼接条件使用or

​ （2）index全索引扫描

​ 扫描整个索引树，才能获取到有多少数据，这样的索引失去意义

​ desc select count(*) from s1;

​ （3）range索引范围扫描（注意点：范围太大，不能命中索引）

​ 1）普通情况

desc select * from s1 where id < 10;      # type = range
desc select * from s1 where id < 1000000; # type = all
desc select * from s1 where id between 1 and 10;  # type => range
desc select * from s1 where id between 1 and 1000000; # type => all
desc select * from s1 where email like "%w%"; # type => all
desc select * from s1 where email like "w%";  # type => range  (去掉左边的%)

​ 2）对in或or语句进行优化

​ 优化时：union all 比 union 速度快，union在合并数据之后，多一步去重操作

desc select * from s1 where id = 1
union all
select * from s1 where id = 1;

desc select * from s1 where id = 1
union
select * from s1 where id = 1;

​ 优化or条件

desc select * from s1 where id = 10 or name = "aaaaa"

desc select * from s1 where id = 10
union all 
select * from s1 where name = 'aaaaa';

​ （4）ref普通索引查询（非唯一）

desc select * from s1 where email = "xboyww10@oldboy";
desc select * from s1 where id = 10; # 此时id设置是普通索引

​ （5）eq_ref 唯一性索引（联表）

​ 要求：应用在多表联查中，被关联的字段需要主键或者唯一键，表之间的关系为一对一并且数据条数相同

desc select student1.age from student1,class1 where student1.class_id = class1.id
alter table class1 add primary key(id);
delete from student1 where id = 3;

​ （6）const主键或唯一索引（单表）

​ 针对于primary key 和 unique 索引等值查询

desc select * from class1 where id = 1 # type => const
desc select * from class1 where id > 1 # type => range

​ （7）system

​ 6666666666666666666只有一条数据的系统表

4、possible_keys

​ 执行sql时，可能用到的索引是谁

5、key

​ 执行sql时，实际用到的索引是谁

6、key_len

​ 判断联合索引覆盖的长度（通过字节数可以判定出到底触发了哪些联合索引字段）

​ 在没有not null约束的时候，默认预留一个字节，标记是空或者非空

​ utf8通常情况下，中文1个字符占用三个字节，字母占用1个字节，极个别的生僻字占用4个字节

​ varchar 每次存储数据的时候，系统底层默认会额外预留2个字节

		  有not null(不为空)    没有not null(可为空)
tinyint    		1                    1+1
int       		4					 4+1
char(5)   		5*3				   	 5*3+1
varchar(5)		5*3 + 2				 5*3+2+1

十五、事务处理的四项特征ACID

A.原子性：

​ 同一个事务中执行多条sql语句，要么全部成功，要么直接回滚，作为一个完整的整体，不能再继续分隔的最小个体

C.一致性：

​ a，i，d 都是为了保证数据的一致性才提出来的，比如约束，键在插入数据时，必须按照要求插入，保证规则上的一致性

​ 上升到事务中，如果出现意外导致数据不统一，例如脏读，幻读，不可重读，最终要保证数据是一致的

​ 上升到主从数据库，主数据库增删改，从数据库也要进行同步改变，保证数据的一致性；

I.隔离性：

​ lock + isolation锁，来处理事务的隔离级别

​ 一个事务和另外一个事务工作过程中彼此独立隔离

D.持久性：

​ 把数据写到磁盘上，保证数据持久化存储不丢失

隔离性：隔离级别

​ 脏读：没提交的数据被读出来了

​ 不可重读：前后多次读取，结果数据内容不一样（同一个会话里，在不修改的情况下，永远只看到同样的一份数据）

​ 幻读：前后多次读取，结果数据的总量不一样

RU:读未提交：脏读，不可重读，幻读READ-UNCOMMITTED
RC:读已提交：防止脏读，会出现不可重读和幻读 READ-COMMITTED
RR:可重复读：防止脏读，不可重读，可能会出现幻读 REPEATABLE-READ
SR:可串行化：防止一切（但是会把异步并发的程序变成同步程序，不能并发，性能差）

查询当前mysql的隔离级别（默认是RR）

select @@tx_isolation;

查询是否自动提交数据

select @@autocommit；

修改mysql配置文件

找到配置文件my.ini文件修改配置

更改隔离级别

transaction_isolation = READ-UNCOMMITTED

不让系统自动提交数据

autocommit = 0

重启mysql

net start mysql
net stop mysql

脏读

READ-UNCOMMITTED
先去调账设置，重启mysql，尝试在一个窗口里通过事务，更改一条数据，开启另外一个窗口尝试读取，会出现问题

不可重复读

窗口1
begin；
update t1 set k1="abc" where id = 1;
select * from t1;
commit;
窗口2
select * from t1；数据也跟着改了，这就是不可重读

幻读

窗口1
begin；
insert into t1 values(4,'c',50);
select * from t1;
commit;
窗口2
select * from t1; 数量也跟着增加了，这就是幻读

通过二次提交commit，可以让多用户同步数据

commit;

事务应用的技术

（1）RR级别下，解决不可重读，使用mvcc技术，生成最新的mysql的系统备份（快照），然后读取快照
（2）RR级别下，解决幻读，gap间隙锁 next-lock下一键锁