python ------SQL

数据库基本概念

数据库概念：是以⼀定格式进⾏组织的数据的集合（具有特殊格式 的数据⽂件的集合。）

数据库的作用：存储数据

数据库的特点：
持久化存储
读写速度极高
保证数据的有效性
对程序支持性非常好，容易扩展

数据库的分类： 关系型数据库 和 非关系型数据库
关系型数据库（RDBMS）：
是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据，本质上使用一张 二维表 来表示关系。关系型数据库的主要产品：MySQL 、 Oracle(银行、电信）、 SQLite （轻量级数据库，移动平台）、Microsoft SQL Server(微软）
非关系型数据库：
又称为NoSQL（Not Only SQL）,强调Key-Value存储和文档数据库的优点。非关系型数据库的主要产品：mongoDB 、 redis

数据库管理系统（DBMS）： 管理数据库，包括以下三部分：

• 数据库文件集合
• 数据库服务端
• 数据库客户端

数据库客户端通过SQL语句告诉服务器，客户端想要做什么；服务器端和数据文件一般都在同一台机器上，直接可以读写数据文件。

SQL(Structured Query Language)结构化查询语言：
SQL语句的作用是实现数据库客户端和服务端之间的通信，它是一种用来操作RDBMS的数据库语言。
SQL语句主要分为：

• DQL：数据查询语⾔，⽤于对数据进⾏查询，如select
• DML：数据操作语⾔，对数据进⾏增加、修改、删除，如insert、udpate、delete
• TPL：事务处理语⾔，对事务进⾏处理，包括begin transaction、commit、rollback
• DCL：数据控制语⾔，进⾏授权与权限回收，如grant、revoke
• DDL：数据定义语⾔，进⾏数据库、表的管理等，如create、drop

关系型数据库中核⼼元素：

• 数据行（记录）
• 数据列（属性、字段）
• 数据表（数据行的集合）
• 数据库（数据表的集合）

所以pipeline为：创建数据库，在数据库中创建表，向表中添加记录

MySQL环境搭建（linux）

服务器端：一般服务器已经安装好了，需要管理员权限sudo apt-get install mysql-server,一般也是启动服务的sudo service mysql start,我们只需要用命令查看一下进程中是否存在mysql服务ps -ajx |grep mysql,-a 显示所有用户进程 -j 任务格式显示进程 -x 显示无控制终端进程
客户端：图形化界⾯客户端navicat，linux安装很多教程，主要是下载一个可靠的.tar软件
linux 连接不上 可以转战windows（MySQL + navicat）安装教程参考：MySQL + navicat破解

数据库完整性和约束

数据库完整性约束⽤于保证数据的正确性。
关系模型中有四类完整性约束：实体完整性、域完整性、参照完整性和用户定义完整性

实体完整性

用于保证操作的数据（记录）非空、唯一且不重复。即实体完整性要求每个关系表有且仅有一个主键，每个主键值必须唯一，而且不允许为“空”或重复。
主键索引，不重复不可以为空
唯一索引，不重复可以为空

域完整性

指数据库表中的 列必须满足某种特定的数据类型或约束。如 default 、 not null定义都属于域完整性的范畴。

参照完整性

属于表间规则。如删除父表的某记录后，子表的相应记录也应该删除。foreign key

用户定义完整性

是对数据表中字段属性的约束，用户定义完整性规则也称域完整性规则，

常见的约束介绍

数据类型

MySQL中定义数据字段的类型对数据库的优化是很重要的。选择合适的数据类型保存数据，好处：1.节省存储空间 2.提升查找效率

数值

整型类型

应⽤场景：
保存⼈的年龄（1-100） ：tinyint
保存某个状态值（0、1）：tinyint
⼩型项⽬的编号：int

浮点型

float(M,D) ，只保证6位有效数字的准确性
double(M,D)，只保证16位有效数字的准确性
M 代表总数字的位数，最大值是255；D代表其中小数的位数。

定点数

decimal(M,D),M 代表总的数字位数，D代表其中的小数位
应用场景： 用在需要精确的小数时，比如价格。

字符串（char/varchar/text）

char 和 varchar 的区别：
char：定长字符串，指的是在创建表时，char字段占⽤硬盘空间的⼤⼩就已经固定了。
varchar：变长字符串，指的是字段占⽤硬盘空间的⼤⼩并不是固定的，⽽是由内容决定的， 等于内容的⻓度+1个字节(字符串结束’\0’)。
text:与char和varchar不同的是，text不可以有默认值，其最⼤⻓度是2的16次⽅-1

选择字符串类型的原则
经常变化的字段⽤varchar
知道固定⻓度的⽤char
尽量⽤varchar
超过255字符的只能⽤varchar或者text
能⽤varchar的地⽅不⽤text

枚举类型

枚举类型enum，在定义字段时就预告规定好固定的⼏个值，然后插⼊记录时值只能这⼏个固定好 的值中选择⼀个。
如：day enum(‘星期一’，‘星期二’，‘星期三’，‘星期四’，‘星期五’，‘星期六’，‘星期日’)

应用场景：当值是⼏个固定可选时，⽐如：性别、星期、⽉份、表示状态时(⽐如:是、否)

时间类型

登录退出数据库 以及相应操作

首先确保已经启动mysql服务：net start mysql(以管理员身份运行）
打开终端，运行命令：mysql -uroot -p，回车后输入密码即可连接成功。

因此以下语法是在mysql版本为8.0.21下执行

退出登录：
quit 和 exit

数据库操作：
查看所有数据库：show databases;
查看当前使用的数据库：select database();
创建数据库：create database 数据库名 charset=utf8;
使用数据库：use 数据库名;
删除数据库：drop database 数据库名;

数据库的备份与恢复

退出备份（导出）：mysqldump -uroot -p 数据库名 > python.sql ;
创建数据库并恢复（导入）：mysql -uroot -p 新数据库名 < python.sql ;

表结构的创建

创建学生表：
create table students(
id int unsigned primary key auto_increment not null,
name varchar(20) not null,
age tinyint unsigned default 0,
height decimal(5,2),
gender enum(“男”,"女 ",“妖”)
cls_id int unsigned default 0
);

查看当前数据库中所有表：show tables;
查看表结构：desc 表名;
重命名表的名称：rename table 原表名 to 新表名;

表结构的修改

添加字段：alter table 表名 add 列名 类型;

如： alter table students add birthday datetime;

重命名字段：alter table 表名 change 原名 新名 类型及约束;

如： alter table students change birthday birth datetime not null;

修改字段类型：alter table 表名 modify 列名 类型及约束;

如： alter table students modify birthday date ;

删除字段：alter table 表名 drop 列名;

如： alter table students drop birthday ;

删除表：drop table 表名 ;

如： drop table students;

表数据操作-增删改查（curd）

查询select

# 查询所有列

select * from 表名；
例：select * from classes;

# 查询指定列
select 列1,列2,....from  表名;
列：select id, name from classes;

# 定条件查询
select * from classes where id=2;

# 使用as为字段指定别名 （或者给表起别名）
select id as '编号'，name as '姓名' from classes;

# 消除重复行（distinct）
select distinct gender from students;

优先级由⾼到低的顺序为：
⼩括号，not，⽐较运算符，逻辑运算符 and⽐or先运算，如果同时出现并希望先算or，需要结合()使⽤

where 之比较运算

1. 等于: = 2. ⼤于: > 3. ⼤于等于: >= 4. ⼩于: < 5. ⼩于等于: <= 6. 不等于: != 或 <>

# 查询姓名是“⻩蓉”的学⽣
select * from students where name = " ⻩蓉 ";
# 查询年龄大于等于18岁的学生
select * from students where age >= 18;

where条件之逻辑运算

and or not

# 18岁以上的女性
select * from students where age > 18 and gender = '女';
# 查询年龄大于等于18岁的学生
select * from students where age >= 18;

where之模糊查询（like）

% 表示多个任意字符
_ 表示一个任意字符

# 查询姓名中有  “小” 所有的名字
select * from students where name like '%小%';

# 查询姓名中 姓  “刘”   所有的名字
select * from students where name like '刘%';

# 查询有2个字的名字
select * from students where name like'__';

where之范围查询(in/ between …and)

范围查询分为连续范围查询和⾮连续范围查询
in 表示在⼀个⾮连续的范围内(相当于多个值的or 关系） not in不在
between … and …表示在⼀个连续的范围内（是一种and关系） not between

# 查询 编号是1或3或8的学生
select * from students where id in(1,3,8)';

# 查询年龄在18到25之间的学生
select * from students where age>=18 and age<=25;  等价（where age between 18 and 25）

where条件之空值判断(is null)

1. null 与 ‘’是不同的
2. is null / is not null

# 查询 没有填写身高的学生（也就是为Null）
select * from students where height is null;
# 查询填写了身⾼的男⽣
select * from students where gender='男' and height is not null;

order by 排序(asc 默认升序，desc)

# 查询年龄为18到35之间的男性，按照年龄从小的到大排序
select * from students where gender='男' and  age between 18 and 35 order by age asc;

# 查询年龄在18到24之间的女性，身高从高到矮排序，如果身高相同的情况下按照年龄从大到小排序
select * from students  where gender= '女'  and age between 18 and 24 order by height desc,age desc;

聚合函数(组函数）

聚合函数会对当前所在表当做一个组进行统计。

# 查询学生总数
select count(*) form students;

# 查询男生有多少人
select count(*) form students where gender= '男'；

# 查询最大的年龄
select max(age) from students;

# 计算所有人的年龄总和
select sum(age) from students;

# 计算所有人的年龄的平均值
select round(avg(age),2)  from  students;# round(数值，保留的小数位数）四舍五入

group by 分组

select 后的字段只能出现以下两种情况：

• group by 后出现过
• 在聚合函数中出现过
• group by + 聚合函数

# 计算每种性别中的人数
select gender,count(*) from students group by gender;

# 查询每种性别中年龄的平均值
select gender,avg(age) from students group by gender;

#查询男女性别中年龄的最大值
select gender,max(age) from students where gender='男' or gender='女' group by gender;

group by + group_concat()
group_concat(字段名)根据分组结果，使⽤group_concat()来放置每⼀个分组中某字段的集合（统计每个分组某字段值的集合）

# 查询每种性别的姓名
select gender,group_concat(name) from students group by gender;

group by + having()
过滤分组结果，having作⽤和where类似，但having只能⽤于group by ⽽where是⽤来过滤表数据

# 查询平均年龄超过30岁的性别、以及名字
select gender,avg(age),group_concat(name) from students group by gender having avg(age) > 30;

# 查询每种性别中的人数多于2的性别和名字
select gender,count(*) c ,group_concat(name) from students group by gender having c >2;

group by + with rollup
with rollup的作⽤是：在最后新增⼀⾏，来记录当前表中该字段对应的操作结果，⼀般是汇总结 果

增加insert

# 全列插入
insert into 表名 values (...);
insert into classes values(0,"python23",55);

# 部分列插入 
insert into 表名 (列1,...) values(值1,...);
insert into classes(id,num)  values(0,15);

# 全列多行插入
insert into 表名 values(...),(...)...;
insert into classes values(0,"python23",55),(0,"python24",57),(0,"python25",59);

# 部分列多⾏插⼊
insert into 表名(列1,...) values(值1,...),(值1,...)...;
insert into classes(id,num)  values(0,15),(0,33),(0,22);

注：主键列是⾃动增⻓，但是在全列插⼊时需要占位，通常使⽤空值(0或者null) ; 字段默认值 default 来占位，插⼊成功后以实际数据为准

修改update(逻辑删除）

update  表名  set 列1=值1,列2=值2....where 条件
update classes set name="python78",num=55  where id=2;

删除delete（物理删除）

delete from  表名  where 条件
delete from classes  where id=2;

limit 限制记录

可以使⽤limit限制取出记录的数量，但 limit 要写在sql语句的最后。
语法： limit 起始记录，记录数（起始记录是指从第几条记录开始取，第一条记录的下标是0，默认是从0开始）

#从下标为5的记录开始取 2 条数据
select * from students limit 5,2;
# 一般取最新的记录（倒序）
select * from students order by id desc limit 0,2;

标准的SQL书写格式： select 字段1，字段1，.... from 表名 [where 条件] [group by 字段名] [order by 字段名 排序规则] [having 条件] [limit 起始位置，数量]

分页查询

使用limit控制每页显示多少数据

连接查询

当查询结果的列来源于多张表时，需要将多张表连接成⼀个⼤的数据集进⾏汇总显 示。
注意： 能够使⽤连接的前提是 多表之间有字段上的关联。

语法：select * from 表1 inner或left或right join 表2 where（on） 表1.列 运算符 表2.列

内连接查询
查询的结果为两个表匹配到的数据,默认是笛卡尔积

# 查询对应班级的学生以及班级信息
select * from students inner join classes where students.cls_id = classes.id;
# 按照要求显示姓名和课程名称
select students.name, classes.name from students inner join classes where students.cls_id = classes.id;
#将上一条语句起别名
select s.name,c.name from students s inner join classes c where s.cls_id = c.id;
#也可以这样写
select s.name, c.name from students s,classes c where s.cls_id = c.id;

自连接
表中的某一列，关联了这个表中的另外一列，如某个市属于某个县,一种特殊的内连接。

# 创建areas表（省没有所属的省份，，填null；城市所属的省份pid，填写省对应的编号id；）
creat table areas(
aid int primary key,
atitle varchar(20),
pid int
);
# 从sql文件中导入数据
source areas.sql;
#查询一共有多少个省
select count(*) from areas where pid is null;

# 查询省的名称为“山西省”的所有城市
select * from areas city inner join areas provice on city.pid= provice.aid  where provice.atitle='山西省';

右（外）连接查询
查询的结果为两个表匹配到的数据和右表特有的数据，对于左表中不存在的数据使⽤null填充

左（外）连接查询
查询的结果为两个表匹配到的数据和左表特有的数据，对于右表中不存在的数据使⽤null填充

子查询
分类：
标量子查询： 返回的结果是一个数据（一行一列）
列子查询： 返回的结构是一列（一列多行）
行子查询：返回的结构是一行（一行多列）

#查询出高于平均升高的信息
select * from students where height >  (select avg(height) from students);

# 查询学生的班级号对应于学生的学号id
select * from students where id in (select id from classes);

SQL 操作应用

解决的问题：goods表不再存储分类的名称，改为分类的id

#第一步，创建goods_cates表
creat table goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
#将good表中的cate_name 插入
insert into goods_cate(name)  (select cate_name from goods group by cate_name);
# 更新goods表 cate_name 为 goods_cates.id
update  goods inner join goods_cate where goods.cate_name=goods_cates.name  set goods.cate_name=goods.id;
# 修改cate_name 为cate_id
alter table goods change cate_name cate_id int unsinged not null;

外键

一个表的主键在另外一个表中出现，在另外一个表中称为外键。
作用： 表间的数据插入、更新的时候的一种约束

创建外键：

• 已经存在的表建立外键
  alter table 表名 add foreign key(当前表的字段） references 表名（字段）

alter table goods add foreign key(cate_id) references goods_cates(id);

• 在创建数据表的时候设置外键约束

create table goods_test(
id int primary key auto_increment,
name varchar(150) not null,
cate_id int unsigned not null,
brand_id int unsigned not null,
foreign key (cate_id) references goods_cates(id),
foreign key (brand_id) references goods_brands(id)
);

取消外键约束：

1. 首先查看外键名
   show create table goods_test;
2. alter table goods_test drop foreign key 外键名
   注： 使用外键约束会极大的降低表跟新的效率，所以在追求读写效率优先的场景下一般很少使用外键。

视图

视图： 是把复杂SQL语句的功能封装起来的一个虚表，数据来自于各实际存在的表，仅仅支持查询，

视图是对若干基本表的引用，一张虚表，只查询语句执行结果的字段类型和约束，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）

作用： ⽅便操作，特别是查询操作，减少复杂的SQL语句，增强可读性；

创建视图： create view 视图名 as select 语句; (视图名建议以v_开头）

查看视图： show tables;

使用视图： 视图的用途就是查询 select * from v_goods_info;

删除视图： drop view 视图名称

存储过程和函数

存储过程和函数是 事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的
存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

# 创建存储过程
create procedure procedure_name()
begin
	SQL语句
	# declare 定义局部变量
	# set/  select  into赋值
	# 传递参数  IN 输入 OUT 输出
	
end; # 可以用delimiter来声明SQL语句的分隔符
# 调用存储过程
call procedure_name();
# 删除存储过程
drop procedure sp_name;

# 创建存储函数
create function function_name()
returns type
begin
	SQL语句
end;

DELIMITER $ 
create procedure pro_test12() 
begin 
	DECLARE id int(11); 
	DECLARE name varchar(50); 
	DECLARE age int(11); 
	DECLARE salary int(11); 
	DECLARE has_data int default 1; 
	
	DECLARE emp_result CURSOR FOR select * from emp; 
	DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0; 
	open emp_result; 
	repeat 
		fetch emp_result into id , name , age , salary; 
		select concat('id为',id, ', name 为' ,name , ', age为 ' ,age , ', 薪水为: ', salary); 
		until has_data = 0 
	end repeat; 
	close emp_result; 
end$ 
DELIMITER ;

游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE，其语法分别如下。

触发器

触发器是与表有关的数据库对象，指在 insert/update/delete 之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。

使用别名 OLD 和 NEW 来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发，不支持语句级触发。

创建 update 型触发器，完成更新数据时的日志记录 :

DELIMITER $
create trigger emp_logs_update_trigger 
after update 
on emp 
for each row 
begin 
	insert into emp_logs(id,operation,operate_time,operate_id,operate_params)values(null,'update',now(),new.id,concat('修		          				改前(id:',old.id,', name:',old.name,', age:',old.age,', salary:',old.salary,') , 修改后(id',new.id, 'name:',new.name,', age:',new.age,', salary:',new.salary,')')); 
end $ 
DELIMITER ;

事务（Transaction）

是指 作为一个基本工作单元执行的一些列SQL语句的操作，要么完全地执行，要么完全地不执行

事物的四大特性ACID：
原子性（Atomicity）： 由DBMS的事务管理子系统来实现
⼀个事务必须被视为⼀个不可分割的最⼩⼯作单元，整个事务中的所有操作要么全部提交成功， 要么全部失败回滚，对于⼀个事务来说，不可能只执⾏其中的⼀部分操作，这就是事务的原⼦性

一致性（Consistency）： 由DBMS的完整性子系统执行测试任务
（加500减也是500）数据库总是从⼀个⼀致性的状态转换到另⼀个⼀致性的状态。

隔离性（Isolation）： 由DBMS的并发控制子系统实现
通常来说，⼀个事务所做的修改在最终提交以前，对其他事务是不可⻅的。

持久性（Durability）： 由DBMS的恢复管理子系统实现
⼀旦事务提交，则其所做的修改会永久保存到数据库。

原⼦性强调事务中的多个操作时⼀个整体
⼀致性强调数据库中不会保存不⼀致状态
隔离性强调数据库中事务之间相互不可⻅
持久性强调数据库能永久保存数据，⼀旦提交就不可撤销

事务使用：

数据库存储引擎是数据库底层软件组织，不同的 存储引擎 提供不同的存储机制、索引技巧、锁定⽔平 等功能，使⽤不同的存储引擎，还可以 获得特定的功能。
查看表引擎：show engines;

表的引擎类型必须是innodb类型才可以使⽤事务，这是ubuntu 中 mysql表的默认引擎

开启事务： begin；（start transaction；
操作数据库：insert update delete
提交 事务：commit;（提交后就不可以rollback）
回滚事务：rollback;（没有提交可以回滚）
并发事务处理带来的问题： 丢失更新(lost update)、脏读(dirty reads)、不可重复读(nonrepeatable reads)、幻读(phantom reads)
事务隔离级别：
为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。由低到高依次为：Read uncommitted、Read committed、Repeatable read、Serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：show variables like 'tx_isolation';

数据库三范式(规范）

• 第⼀范式（1NF）：强调的是列的原⼦性，即列不能够再分成其他⼏列
• 第⼆范式（2NF）：满⾜ 1NF，另外包含两部分内容，⼀是表必须有⼀个主键； ⼆是⾮主键字段 必须完全依赖于主键，⽽不能只依赖于主键的⼀部分。(解决方法：拆分表）
• 第三范式（3NF）：满⾜ 2NF，另外⾮主键列必须直接依赖于主键，不能存在传 递依赖。即不能存在：⾮主键列 A 依赖于⾮主键列 B，⾮主键列 B 依赖于主键的情 况。(解决方法：拆分表）

总结：
范式就是设计数据库的通⽤规范。
1NF强调字段是最⼩单元，不可再分
2NF强调在1NF基础上必须要有主键和⾮主键字段必须完全依赖于主键，也就是说 不能部分 依赖
3NF强调在2NF基础上 ⾮主键字段必须直接依赖于主键，也就是说不能传递依赖(间接依赖)。

E-R模型及表间关系

E-R图即实体-联系图(Entity Relationship Diagram)，是指提供了表示实体型、属性和联系的⽅法，⽤ 来描述现实世界的概念模型。

E-R图⽤ 实体、联系和属性这3个概念来描述现实问题。 有以下三种元素:
1 实体型(Entity)：具有相同属性的实体具有相同的特征和性质，⽤实体名及其属性名集合来抽象 和刻画同类实体;在E-R图中⽤ 矩形 表示，矩形框内写明实体名；⽐如 电商购物系统中⽤户、购物 ⻋、订单等都是实体。
2 属性(Attribute)：实体所具有的某⼀特性，⼀个实体可由若⼲个属性来刻画。在E-R图中⽤ 椭圆 形表示，并⽤⽆向边将其与相应的实体连接起来；⽐如⽤户的ID、⽤户名、密码、昵称、身份证 号码 都是属性。
3 联系(Relationship)： 实体彼此之间相互连接的⽅式称为联系，也称为关系。联系可分为以下 3 种类型:⼀对⼀、⼀对多、多对多

python中操作MySQL步骤

========================简单的连接数据库并查询表的数据==========================
# 导入模块
from pymysql import connect
# 建立连接对象 pymysql.connect
conn = connect(host='localhost', user='root', password='12345678', database='python')
# 创建游标对象
cur = conn.cursor()
# 使用游标对象执行SQL语句
# execute 返回值是影响的行数，如果是查询语句，此处返回值为总记录数
result = cur.execute("select * from classes")
print("查询到:%s条数据" % result)
# 获取执行的结果（一行）并打印
# result_one = cur.fetchone()# 元组
# print(result_one)
# 所有数据
result_all = cur.fetchall()
print(result_all)
for i in result_all:
    print(i)
# 关闭游标对象
cur.close()
# 关闭连接对象
conn.close()
==========================简单的连接数据库并增删改查表的数据=============================
from pymysql import connect
conn = connect(host='localhost', user='root', password='12345678', database='python')
cur = conn.cursor()
# sql = "insert into classes values(null,'python27',41)" #增
# sql = "delete from classes where id=7" #删
sql = "update classes set num=24 where id=8" # 改
result = cur.execute(sql)
conn.commit() # 提交刚刚执行的SQL,将修改提交到数据库，保存修改 ************************************
print("影响的行数:", result)
cur.fetchall()
cur.close()
conn.close()

SQL防注入

SQL注⼊ 产⽣原因:
后台将⽤户提交的带有恶意的数据和SQL进⾏字符串⽅式的拼接，从 ⽽影响了SQL语句的语义，最终产⽣数据泄露的现象。

防注入的思路(sql语句的参数化）：

1. sql中需要变化的地方，可以用占位符 %s %d…
   注意：SQL可以出现多个占位符，后续列表中元素的个数要与之对应
2. 把参数封装到列表中
3. 把列表传递给execute(sql,列表）

from pymysql import connect
conn = connect(host='localhost', user='root', password='12345678', database='python')
cur = conn.cursor()
input_name = input("请输入要查询的班级：")
# 被注入过程的分析：
# input_name = ' or 1 or '
# "select * from classes where name= '%s' " % input_name 将%s替换为输入内容
# "select * from classes where name= '' or 1 or '' " % input_name 永远为真

# 防止注入（传参数）
# 1.构建参数列表params 2.把列表传递给 execute(sql,params)
params = [input_name]
# sql = "select * from classes where name= '%s' " % input_name
# 修改sql语句
sql = "select * from classes where name= %s "
# 修改execute()
result = cur.execute(sql,params)
print("影响的行数:", result)
result_all = cur.fetchall()
print(result_all)
cur.close()
conn.close()

索引

1.索引 是⼀种特殊的⽂件(InnoDB数据表上的索引是表空间的⼀个组成部分)，它们包含着对数据表⾥所 有记录的位置信息。

查看表中已有的索引： show index from 表名；

创建索引： create index 索引名称 on 表名（字段名称（长度）） ；

• 如果指定字段是字符串，需要指定⻓度，建议⻓度与定义字段时的⻓度⼀致 字段类型
• 如果不是字符串，可以不填写⻓度部分

删除索引：drop index 索引名称 on 表名;

从上面这个图看出创建索引后，查找快了许多（索引可以明显提高某些字段的查询效率，）！！！！！
注意：
索引虽好但不要贪杯

• 建立太多的索引将会影响更新和插入的速度，因为它需要同样更新每个索引文件
• 建立索引会占用磁盘空间

2.避免索引失效

1）.全职匹配，对索引中所有列都指定具体值，这种情况下，索引生效，执行效率高
2）.最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引；违法最左前缀法则 ， 索引失效；如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效。
3）.范围(> <)查询右边的列，不能使用索引,也就是或使用范围属性后面的索引失效
4）.不要在索引列上进行运算操作， 索引将失效。
5）.字符串不加单引号，造成索引失效。
6）.尽量使用覆盖索引(只访问索引的查询，索引列完全包含查询列），避免select *。
使用explain 中extra字段的内容：

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表 查询数据

7）.用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
8）. 以%开头的Like模糊查询，索引失效。
如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效，可以通过覆盖索引来解决。
9）. 如果MySQL评估使用索引比全表更慢，则不使用索引。如，数据库中某一个属性所占的比例很大，就全表扫描，速度快。
10）. is NULL ， is NOT NULL 有时索引失效。根据数据库中的数据量决定的。
11）. in 走索引， not in 索引失效。
12）. 单列索引和复合索引。尽量使用复合索引，而少使用单列索引 。因为创建单列索引后，数据库会选择一个最优的索引（辨识度最高索引）来使用，并不会使用全部索引。

3.查看索引使用情况
show status like 'Handler_read%';
show global status like 'Handler_read%';

用户管理

查看所有用户

所有⽤户及权限信息存储在mysql数据库的user表中

以管理员身份运行cmd后，启动mysql服务器，登录root（超级管理员）
使用 show databases; 可以看到系统默认有四个数据库（information_schema/ mysql/performance_schema/ sys）
使用 use mysql; 进入mysql数据库
使用 show tables; 查看mysql数据库中的所有表
使用 desc user; 查看mysql数据库中的user表，可以看到Host、 User、authentication_string 允许访问的主机、用户名、密码（用md5加密）字段

如果在⽣产环境下操作数据库时也是全部直接使⽤root账户连接，这就和悬崖边跳舞差不多。所以
创建特定的账户，授予这个账户特定的操作权限， 然后连接进⾏操作 ⽐如常规的crud 才是正道。

创建账户、授权

• 需要使⽤实例级账户登录后操作，以root为例
• 创建用户
  create user '用户名'@'主机' identified by ‘密码’;
• 授权( 常⽤权限主要包括：create、alter、drop、insert、update、delete、select,如果分配所有权限，可以使⽤all privileges)
  grant 权限 on 数据库.表名 to '用户名' @'主机名';

1. create user 'wenmei'@'localhost' identified by '123456';(只能在localhost访问）
   create user 'wenmei'@'%' identified by '123456';(任意电脑访问）
   
2. grant select on python.* to 'wenmei'@'localhost';(只用select权限，python数据库的所有表)
   grant all privileges on python.* to 'wenmei'@'localhost';(所有权限)
   
3. (刷新一下权限）flush privileges;

4. 查看用户有哪些权限： show  grants  for wenmei@localhost;

修改权限

grant 权限名称 on 数据库 to 账户@主机 with grant option;
 如： grant upadate,insert on python to wenmei@localhost with grant option;

修改密码

使⽤root登录，修改mysql数据库的user表 wenmei的密码
alter user 'wenmei'@'localhost' identified by '123' ;
flush privileges; 修改完成需要刷新权限

在不知道密码的情况下(忘记密码），修改密码：（密码置空+修改密码）

• 第一步，停止MySQL服务
  
  输入命令net stop mysql（ # linux service mysqld stop ），停止mysql服务，再用 netstat -an|findstr "3306" 查看mysql服务是否开启
  

• 第二步：在mysql8系统下，用mysqld --console --skip-grant-tables --shared-memory可以无密码启动服务
  

• 第三步: 服务启动后，以空密码登入系统 mysql.exe -u root，然后执行sql命令将root用户密码设置为空

use mysql;
update user set authentication_string='' where user='root'；

• 第四步：重启mysql，net start mysql
• 第五步：使用空的密码登录mysql -uroot -p 直接回车就可以登录成功
• 第六步：修改密码为mysql

alter user 'root'@'%' identified by 'mysql';
flush privileges;

• 第七步：退出（exit）验证修改是否成功 1.使用空密码，无法登录 2.使用密码mysql登录

注意：

编辑MySQL 服务器配置文件：（linux :/etc/mysql/mysql.conf.d/mysqld.cnf）windows下的my.ini
加入一行 skip-grant-tables #跳过数据库权限验证 或者运行这个命令mysqld –skip-grant-tables
这些方法在MySQL8.0版本不适用，遇到了如下报错：
mysql: [ERROR] unknown option ‘–skip-grant-tables’.

当然也可以参考网上 利用–init-file参数解决

删除账户

使用root登录，use mysql;使用mysql数据库，然后查看有哪些用户`select host,user from user;`
drop user '用户名'@'主机'；

Innodb 和 MyISAM存储引擎的区别

	Innodb	MyISAM
存储文件	.frm 表定义文件 .ibd数据文件和索引文件	.frm 表定义文件 .myd数据文件 .myi索引文件
锁	表锁、行锁	表锁
事务	支持	不支持
CRDU	读写	读多
count	扫表	专门存储的地方
索引结构	B+ Tree	B+ Tree
	聚集索引	非聚集索引

表级锁：偏向MyISAM存储引擎，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。（读锁 、写锁）

行级锁：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。（共享锁S读锁 、排他锁X写锁）

• 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数
  据，但是只能读不能修改。
• 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他
  锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行
  读取和修改。
  对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。
  防止无索引行锁升级为表锁(如果不通过索引条件检索数据，那么InnoDB将对表中的所有记录加锁，实际效果跟表锁一样，如本来name字段是索引，但是 varchar类型少了引号，作为数组类型使用，索引失效，最终行锁变为表锁）

MySQL性能分析篇

1.首先需要使用【慢查询日志】功能，去获取所有查询时间比较长的SQL语句
2.其次【查看执行计划】查看有问题的SQL的执行计划
3.最后可以使用【show profiles】查看有问题的SQL的性能使用情况

优化SQL步骤

1. 查看SQL执行频率(一般以查询为主）
   show [session | global] status like 'Com___________';(session级 当前连接 global级 自数据库上次启动至今的统计结果，如果不写，默认使用的参数是session）
show status like 'Innodb_rows_%';
2. 定位低效率执行SQL
   · 慢查询日志(在查询结束以后才记录）
   · show processlist:实时地查看SQL的执行情况，包括线程的状态、是否锁表等.列表中有两个值，command 显示当前连接的执行的命令 包括休眠sleep，查询 query，连接connect；state列 显示使用当前连接的sql语句的状态，描述的是语句执行中的某一个状态，如 copying to tmp table、sorting result、sending data等
3. explain 分析执行计划
   可以通过explain 或者 desc命令获取MySQL如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序。
   
   （1）id字段，表示的是查询中执行select子句或者是操作表的顺序。
   id 相同表示加载表的顺序是从上的到下；id不同id值越大，优先级越高，越先被执行；id有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行，在所有的组中，id的值越大，优先级越高，越先执行。
   （2）select_type字段,以下表从上到下 效率逐渐减慢。
   
   （3）type字段,以下表从上到下 效率逐渐减慢。一般来说，我们需要保证查询至少达到range级别，最好达到ref。
   
   （4）key字段
   （5）extra字段，优化前两个（建立索引），保持最后一个
   
4. show profile 分析SQL
   Mysql 从5.0.37版本开始增加了对show profiles 和 show profile 语句的支持。show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。
   a.查看当前版本是否支持profile, select @@have _profiling;
   b.查看是否开启profile, select @@profiling;
   c.开启profile, set profiling=1;
   d.执行show profiles指令， 来查看SQL语句执行的耗时：
   e.执行show profiles for query query_id语句可以查到改SQL执行过程中每个线程的状态和消耗时间：一般sending data 耗时多
   f.在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等
   明细类型类查看MySQL在使用什么资源上耗费了过高的时间。如，选择查看CPU的耗费时间，show profile cpu for query 6
5. trace 分析优化器执行计划
   MySQL5.6 提供了对SQL的跟踪trace，通过trace文件能进一步了解为什么优化器选择A计划，而不是选择B计划。
   执行SQL语句：select * from tb_item where id < 4;
   最后，使用select * from information_schema.optimaizer_trace\G 就可以知道MySQL是如何执行SQL的；

SQL优化（数据库的优化）

（1）大批量插入数据 （提高导入的效率）

• 使用主键顺序插入数据 ， load data local infile '/root/sql1.log' into tabel 'tb_user' fields terminated by ',' lines terminated by '\n';
• 关闭唯一性校验 ，在导入数据之前执行set unique_checks=0，关闭唯一性校验，在导入结束后执行set unique_checks=1，恢复唯一性校验，可以提高导入的效率。
• 手动提交事务，如果应用使用自动提交的方式，建议在导入前执行 set autocommit=0，关闭自动提交，导入结束后再执行 set autocommit=1，打开自动提交，也可以提高导入的效率。

（2） 优化insert语句

• 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户
  端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

insert into tb_test values(1,'Tom'); 
insert into tb_test values(2,'Cat'); 
insert into tb_test values(3,'Jerry');
改为：
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

• 使用手动提交事务 start transaction commit
• 数据有序插入

（3） 优化order by 语句
FileSort 排序：通过对返回数据进行排序，所有不是通过索引直接返回排序结果的排序
using index：通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高
order by 的顺序和索引顺序相同，order by的字段都是升序或者都是降序。查找有索引的属性值，代替所有。
可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效
率。

（4） 优化group by 语句
group by 主要是多了排序之后的分组操作。，加上order by null和使用索引进行优化

（5） 优化嵌套查询
使用多表连接（join）代替子查询，连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的
查询工作
（6） 优化or条件
对于包含or的查询子句，如果要利用索引，则or之间的每个条件列都必须用到索引 ， 而且不能使用到复合索
引； 如果没有索引，则应该考虑增加索引。另外建议使用union替换or
（7） 优化分页查询
一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，
此时需要MySQL 排序 前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非
常大 。
优化一：在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容
优化二：适用于主键自增的表，可以把limit查询转换成某个位置的查询。
（8） 使用SQL提示(都放在查询语句中表名的后面）

use index（索引名）
ignore index（索引名）
force index（索引名）

SQL优化（应用优化）

在实际生产环境中，由于数据库本身的性能局限，就必须要对前台的应用进行一些优化，来降低数据库的访问压力。
（1）使用连接池
对于访问数据库来说，建立连接的代价是比较昂贵的，因为我们频繁的创建关闭连接，是比较耗费资源的，我们有
必要建立 数据库连接池，以提高访问的性能。
（2）减少对MySQL的访问

• 避免对数据进行重复检索（能够一次连接获取到结果的，就不用两次连接）

• 增加cache层
  在应用中，我们可以在应用中增加 缓存 层来达到减轻数据库负担的目的。
  （3）负载均衡
  它的机制就是利用某种均衡算法，将固定的负载量分布到不同的服务器上， 以此来降低单台服务器的负载，达到优化的效果。

• 利用MySQL复制分流查询
  通过MySQL的主从复制(复制是指将主数据库的DDL 和 DML 操作通过二进制日志传到从库服务器中，然后在从库上对这些日志重新执行（也叫重做），从而使得从库和主库的数据保持同步)，实现读写分离，使增删改操作走主节点，查询操作走从节点，从而可以降低单台服务器的读写压力。
  
  复制分为三步：Master 主库在事务提交时，会把数据变更作为时间 Events 记录在二进制日志文件 Binlog 中；主库推送二进制日志文件 Binlog 中的日志事件到从库的中继日志 Relay Log ；slave重做中继日志中的事件，将改变反映它自己的数据。

• 采用分布式数据库架构
  分布式数据库架构适合大数据量、负载高的情况，它有良好的拓展性和高可用性。通过在多台服务器之间分布数据，可以实现在多台服务器之间的负载均衡，提高访问效率。

MySQL中查询缓存优化

开启Mysql的查询缓存，当执行完全相同的SQL语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存。
查看当前的MySQL数据库是否支持查询缓存 :show variables like 'have_query_cache';
查看当前的MySQL数据库是否开启了查询缓存 :show variables like 'query_cache_type';
查看查询缓存的占用大小 :show variables like 'query_cache_size';
查看查询缓存的状态变量 :show status like 'Qcache%';
开启查询缓存： 在 /usr/my.cnf 配置中（linux系统下），增加以下配置 ：query_cache_type=1，重启服务
query_cache_type该参数的可取值有三个：

查询缓存select选项：(SQL_CACHE, SQL_NO_CACHE)

SELECT SQL_CACHE id, name FROM customer;
SELECT SQL_NO_CACHE id, name FROM customer;

查询缓存失效的情况：
（1）SQL 语句不一致的情况， 要想命中查询缓存，查询的SQL语句必须一致。
（2）当查询语句中有一些不确定的函数时，则不会缓存。如 ： now() , current_date() , curdate() , curtime() , rand() ,
uuid() , user() , database()
（3）不使用任何表查询语句。
（4）查询 mysql， information_schema或 performance_schema 系统数据库中的表时，不会走查询缓存。
（5）在存储的函数，触发器或事件的主体内执行的查询。
（6）如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用 MERGE 映射到已更改表的表的查询。一个表可以被许多类型的语句，如被改变 INSERT， UPDATE， DELETE， TRUNCATE TABLE， ALTER TABLE， DROP TABLE，或 DROP DATABASE 。

MySQL内存管理及优化

内存优化原则：
1） 将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序预留足够内存。
2） MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的
内存给操作系统做IO缓存。
3） 排序区、连接区等缓存是分配给每个数据库会话（session）专用的，其默认值的设置要根据最大连接数合理
分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致物理内存耗尽。

InnoDB内存优化，innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。innodb_buffer_pool_size变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffer_pool_size 的值越大，缓存命中率越高，访问InnoDB表需要的磁盘I/O 就越少，性能也就越高。

MySQL并发参数调整

从实现上来说，MySQL Server 是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务器资源，提高数据库的并发性能。在Mysql中，控制并发连接和线程的主要参数包括 ：

max_connections（控制允许连接到MySQL数据库的最大数量）
back_log（控制MySQL监听TCP端口时设置的积压请求栈大小，MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源）
thread_cache_size（为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用）
table_open_cahce（控制所有SQL语句执行线程可打开表缓存的数量， 而在执行SQL语句时，每一个SQL执行线程至少要打
开 1 个表缓存）
innodb_lock_wait_timeout（设置InnoDB 事务等待行锁的时间，）。

MySQL锁问题

锁是计算机协调多个进程或线程并发访问某一资源的机制（避免争抢）。

由上表可见：
1） 对MyISAM 表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；
2） 对MyISAM 表的写操作，则会阻塞其他用户对同一表的读和写操作；
简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁，则既会阻塞读，又会阻塞写。
此外，MyISAM 的读写锁调度是写优先，这也是MyISAM不适合做写为主的表的存储引擎的原因。因为写锁后，其
他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。
查看锁的争用情况： show open tables; show status like 'Table_locks%;'

间隙锁危害

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据进行加锁； 对于键值在条件范围内但并不存在的记录，叫做 “间隙（GAP）” ， InnoDB也会对这个 “间隙” 加锁，这种锁机制就是所谓的 间隙锁（Next-Key锁） 。

MySQL日志

（1）错误日志
该日志是默认开启的，默认的日志文件名为hostname.err(hostname是主机名）
show variables like 'log_error'; 查看日志位置
tail -f root/xxxx.err'; 查看日志内容

（2）二进制日志(Binlog)
记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。

二进制日志，默认情况下是没有开启的，需要到MySQL的配置文件(/usr/my.cnf)中开启，并配置MySQL日志的格式(日志格式有三种，分别为：statement【记录的是sql语句，借助mysqlbinlog工具查看】，row【记录的是每一行的数据变更借助mysqlbinlog -vv 查看】，mixed【混合了statement和row两种格式，是目前mysql默认的日志格式】。

删除日志的四种方式：Reset Master、purge master logs to ''mysqlbin.xxx删除xxx编号之前的所有日志、purge master logs before 'yyyy-mm-dd-hh' 删除日志为“yyyy-mm-dd-hh”之前产生的所有日志、在配置文件中设置日志的过期天数，–expire_logs_days=#

（3）查询日志
查询日志中记录了客户端的所有操作语句，而二进制日志不包含查询数据的SQL语句。
默认情况下， 查询日志是未开启的。如果需要开启查询日志，可以设置以下配置 ：

general_log = 1 # 该选项用来开启查询日志
general_log_file=file_name #设置日志的文件名，如果没有指定，默认的文件名为host_name.log

（4）慢查询日志

慢查询日志记录了所有执行时间 超过 参数 long_query_time 设置值并且扫描记录数不小于min_examined_row_limit 的所有的SQL语句的日志。long_query_time 默认为 10 秒，最小为 0， 精度可以到微秒
慢查询日志默认是关闭的 。可以通过两个参数来控制慢查询日志 ：

slow_query_log=1 # 该参数用来控制慢查询日志是否开启，
slow_query_log_file=slow_query.log # 该参数用来指定慢查询日志的文件名
long_query_time=10 # 该选项用来配置查询的时间

正则表达式使用

优化总结

分页优化： 可以专门建立一个表来存储count的个数，当增加数据的时候更改表中的数据，每次不需要count操作，但是针对有条件的count失效；对于limit的优化，可以使用子查询，使用id主键进行查询固定的页数和个数，与其他的操作进行拼接。
应用优化：