day 05 数据库
1.pymysql
介绍：
Python操作mysql的模块
安装：
pip install mysql
连接：
import pymysql
conn = pymysql.connect(host='主机名', user='用户名', password='密码', database='数据库名',charset='utf8');
cursor = conn.cursor() ### 返回的是元祖套元祖
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) ### 返回的是列表套字典
执行sql语句：
cursor.execute(sql)
查：
fetchall() : 获取多个， 返回 列表套字典
fetchone() : 获取一个， 返回 字典
fetchmany(size) : 获取size个数据， 返回的是 列表套字典
增删改：
conn.commit()
SQL注入：
原因：
太相信用户输入的数据
解决的方法：
# sql = "select * from user where name='%s' and password='%s'" % (user, pwd)
sql = "select * from user where name=%s and password=%s"
cursor.execute(sql, (user, pwd))
csrf攻击
2.索引
a.索引的作用？
提高查询的效率
b.类比：字典中的目录
c.底层采用的数据结构：（******************）
B+树
d.索引本质上就是一个特殊的文件， 只不过这个特殊的文件底层的数据结构是B+树
e.索引的分类：
- 主键索引
作用： 加快查询速度 + 不能重复 + 不能为空
增加：
第一种方法：（**********************）
create table user (
id int auto_increment primary key, ### 主键自增id
)
注意：auto_increment 依赖 primary key, 而primary key 不依赖auto_increment
第二种方法：
alter table user change id id int auto_increment primary key;
删除：
如果要删除带有 auto_increment的primary key的话， 需要提前删除auto_increment
alter table user change id id int primary key;
然后再删除
alter table user drop primary key;
场景：
一般都是加在 id 这一列
技术是服务于业务的
- 唯一索引
作用： 加快查询速度 + 不能重复
增加：
第一种方法：
create table user (
id int auto_increment primary key,
phone int not null default 0,
name varchar(32)
unique ix_phone(索引名) (phone(字段名))
)
第二种方法：
alter table user add unique index ix_phone (phone);
第三种方法：
create unique index ix_phone on user (phone);
删除：
alter table user drop index ix_phone;
场景：
应用在唯一值得时候，根据自己的业务去定
脱离业务谈技术就是耍流氓
- 联合唯一索引
使用方法同上
场景：
根据项目或者业务方的需求，灵活的加上联合唯一索引
例子：
create table user (
id int auto_increment primary key,
a int not null default 0,
b int not null default 0,
unique ix_ab (a,b)
)charset utf8;
insert into user (a,b) values (1,2);
insert into user (a,b) values (1,3);
insert into user (a,b) values (3,2);
mysql> insert into user (a,b) values (1,2);
ERROR 1062 (23000): Duplicate entry '1-2' for key 'ix_ab'
mysql>
mysql> insert into user (a,b) values (1,3);
Query OK, 1 row affected (0.05 sec)
- 普通索引
作用：加速查找
增加：
第一种方法：
create table user (
id int auto_increment primary key,
name varchar(32) not null default '',
index ix_name (name)
)
第二种方式：
alter table user add index ix_name (name);
第三种方法：
create index ix_name on user (name);
删除：
alter table user drop index ix_name;
- 联合(组合)索引
index(name, age)
f.索引的命中：
索引加的越多越好？
不是
不会命中索引的情况：
a. 不能在SQl语句中，进行四则运算， 会降低SQL的查询效率
b. 使用函数
select * from tb1 where reverse(email) = 'zekai';
c. 类型不一致
如果列是字符串类型，传入条件是必须用引号引起来，不然...
select * from tb1 where email = 999;
#排序条件为索引，则select字段必须也是索引字段，否则无法命中
d. order by
select name from s1 order by email desc;
当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
select email from s1 order by email desc;
特别的：如果对主键排序，则还是速度很快：
select * from tb1 order by nid desc;
e. count(1)或count(列)代替count(*)在mysql中没有差别了
f. 组合索引最左前缀
什么时候会创建联合索引？
根据公司的业务场景， 在最常用的几列上添加索引
select * from user where name='zekai' and email='zekai@qq.com';
如果遇到上述业务情况， 错误的做法：
index ix_name (name),
index ix_email(email)
正确的做法：
index ix_name_email(name, email)
如果组合索引为：ix_name_email (name,email) ************
where name='zekai' and email='xxxx' -- 命中索引
where name='zekai' -- 命中索引
where email='zekai@qq.com' -- 未命中索引
如果组合索引为：ix_name_email_age (name, email, age):
where name='zekai' and email='xxx' and age=12; ---- 命中索引
where name='zekai' and age=12; ---- 命中索引
mysql> explain select * from user where name='zekai' and email='zekai@163.com' and age=12 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: ix_name_email_age
key: ix_name_email_age
key_len: 218
ref: const,const,const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where name='zekai' and age=12 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: ix_name_email_age
key: ix_name_email_age
key_len: 62
ref: const
rows: 1
filtered: 10.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where email='zekai@qq.com' and age=12 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: index
possible_keys: NULL
key: ix_name_email_age
key_len: 218
ref: NULL
rows: 2987635
filtered: 1.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where age=12 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: index
possible_keys: NULL
key: ix_name_email_age
key_len: 218
ref: NULL
rows: 2987635
filtered: 10.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where email=12 G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: index
possible_keys: NULL
key: ix_name_email_age
key_len: 218
ref: NULL
rows: 2987635
filtered: 10.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where email='zekai@163.com' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: index
possible_keys: NULL
key: ix_name_email_age
key_len: 218
ref: NULL
rows: 2987635
filtered: 10.00
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from user where name='zekai' G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: user
partitions: NULL
type: ref
possible_keys: ix_name_email_age
key: ix_name_email_age
key_len: 62
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
mysql> tee D:/a.log
Logging to file 'D:/a.log'
explain
g.慢日志：
查询：
show variables like '%slow%';
mysql> show variables like '%slow%'
-> ;
+---------------------------+-----------------------------------------------+
| Variable_name | Value |
+---------------------------+-----------------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF ### 默认关闭慢SQl查询日志， on |
| slow_query_log_file | D:mysql-5.7.28dataDESKTOP-910UNQE-slow.log | ## 慢SQL记录的位置
+---------------------------+-----------------------------------------------+
5 rows in set, 1 warning (0.08 sec)
mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
排查慢SQL的原因：
1. 将慢SQL记录到日志中
2. 获取慢SQl，根据慢SQL来优化查询效率 （加索引或者修改索引）
今日内容：
1.作业题
2.事务
通俗的说，事务指一组操作，要么都执行成功，要么都执行失败
思考：
我去银行给朋友汇款,
我卡上有1000元,
朋友卡上1000元,
我给朋友转账100元(无手续费),
如果,我的钱刚扣,而朋友的钱又没加时,
网线断了,怎么办?
演示：
create table user (
id int auto_increment primary key,
name varchar(32) not null default '',
salary int not null default 0
)charset utf8;
insert into user (name, salary) values ('zekai', 1000);
insert into user (name, salary) values ('min', 1000);
解决的方法：
使用事务：
start transaction;
sql语句
commit/rollback;
例子：
commit成功：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary=900 where name='zekai';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 900 |
| 2 | min | 1000 |
+----+-------+--------+
2 rows in set (0.00 sec)
mysql> update user set salary=1100 where name='min';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 900 |
| 2 | min | 1100 |
+----+-------+--------+
2 rows in set (0.00 sec)
mysql> #2.提交
mysql> commit;
Query OK, 0 rows affected (0.06 sec)
rollback回滚：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql> update user set salary=800 where name='zekai';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 800 |
| 2 | min | 1100 |
+----+-------+--------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.11 sec)
mysql> select * from user;
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 900 |
| 2 | min | 1100 |
+----+-------+--------+
2 rows in set (0.00 sec)
rollback回滚，影响所有：
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> update user set salary=800 where name='zekai';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set salary=700 where name='zekai';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from user;
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 700 |
| 2 | min | 1100 |
+----+-------+--------+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from user;
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 900 |
| 2 | min | 1100 |
+----+-------+--------+
2 rows in set (0.00 sec)
【重点，背诵】事务的特性：
1.原子性(Atomicity):原子意为最小的粒子，是不能再分的事务，要么全部执行，要么全部取消
解释：
例如：军队人员军训，
军训成功的标准就是全部人员动作一致，视为成功；
军训失败的标准就是只要有一个人动作不正确，视为全体失败，要想成功只能重新再来。
2.一致性(Consistency):指的是事务发生前和发生后，数据的总额依然匹配
解释：zekai 和 min 的salary总额2000，是不会改变的。
3.隔离性(Isolation):简单点说，某个事务的操作对其他事务不可见的
解释：在事务开始后到提交事务成功前，这一段时间的所有操作，其他用户是不可知的。
4.持久性(Durability):当事务完成以后，其影响应该保留下来，不能撤销，只能通过“补偿性事务”来抵消之前的错误。
什么是事务完成以后呢？ 就是事务commit或者rollback成功以后
其影响应该保留下来，不能撤销是什么意思呢？ 就是通过从开启事务开始，到提交事务或者回滚事务结束，这个过程中对表中字段的增删改查操作应该保留下来。(start-->update-->rollback/commit;)
什么是“补偿性事务”呢？ 就是从新再开一个事物，(start-->update-->commit;)由900重新修改为800
#### 影响什么呢？
salary初始值是900，通过开启事务，更新数据，将salary修改为700，又修改为500，但是通过rollback，将500的值又回滚到了初始值900，最终这个值900是会被保留下来的。
存储引擎：（**************）
InnoDB ： 保时捷引擎
MyIsam ： 奔奔引擎
建表的时候,
create table user (
id int auto_increment primary key,
name varchar(32) not null default '',
salary int not null default 0
)engine=Innodb charset utf8;
mysql5.5以上， 默认用到就是InnoDB
两个引擎的区别：（**************）
1. Innodb支持事务，MyISAM不支持
2. InnoDB支持行锁，MyISAM支持的表锁
3.视图
项目， 有100个SQl， 其中80个SQL都是：select * from user where name='xxx';
增加视图：
create view 视图名 as SQL语句;
删除：
drop view v1;
例子：
mysql> select * from user where name='zekai';
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 900 |
+----+-------+--------+
1 row in set (0.00 sec)
mysql> create view v1 as select * from user where name='zekai';
Query OK, 0 rows affected (0.07 sec)
mysql>
mysql> show tables;
+-----------------+
| Tables_in_test3 |
+-----------------+
| user |
| v1 |
+-----------------+
2 rows in set (0.00 sec)
mysql> select * from v1;
+----+-------+--------+
| id | name | salary |
+----+-------+--------+
| 1 | zekai | 900 |
+----+-------+--------+
1 row in set (0.00 sec)
4.触发器
两张表：
订单表 库存表
场景：
当我下一个订单的时候， 订单表中需要增加一个记录， 同时库存表中需要减1
这两个操作是同时发生的， 并且前一个操作出发后一个操作
使用方法：
增加：
delimiter //
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON t2 FOR EACH ROW
BEGIN
INSERT INTO t3 (NAME) VALUES ('aa');
END //
delimiter ;
### 当向tb1表中添加一条数据的同时， 向tb2表添加一条数据
查看：
show triggersG
*************************** 1. row ***************************
Trigger: tri_before_insert_tb1
Event: INSERT
Table: t2
Statement: BEGIN
INSERT INTO t3 (NAME) VALUES ('aa');
END
Timing: BEFORE
Created: 2019-11-01 11:47:20.65
sql_mode: ONLY_FULL_GROUP_BY
Definer: root@localhost
character_set_client: gbk
collation_connection: gbk_chinese_ci
Database Collation: latin1_swedish_ci
删除：drop trigger 触发器名；
例子：
mysql> select * from t2;
Empty set (0.00 sec)
mysql> select * from t3;
Empty set (0.00 sec)
mysql> insert into t2 (name) values ('zekai');
Query OK, 1 row affected (0.06 sec)
mysql> select * from t2;
+----+-------+
| id | name |
+----+-------+
| 1 | zekai |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | aa |
+----+------+
1 row in set (0.00 sec)
5.存储过程
像 一个 SQL函数
创建：
delimiter //
create procedure p1()
BEGIN
select * from user where id=2;
END //
delimiter ;
例子：
mysql> delimiter //
mysql> create procedure p1()
-> BEGIN
-> select * from user where id=2;
-> END //
Query OK, 0 rows affected (0.10 sec)
mysql> delimiter ;
mysql> call p1();
+----+------+--------+
| id | name | salary |
+----+------+--------+
| 2 | min | 1100 |
+----+------+--------+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
删除：
drop procedure p1;
6.函数
CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
LTRIM(str)
返回字符串 str ，其引导空格字符被删除。
RTRIM(str)
返回字符串 str ，结尾空格字符被删去。
SUBSTRING(str,pos,len)
获取字符串子序列
LOCATE(substr,str,pos)
获取子序列索引位置
REPEAT(str,count)
返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。
若 count <= 0,则返回一个空字符串。
若str 或 count 为 NULL，则返回 NULL 。
REPLACE(str,from_str,to_str)
返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
RIGHT(str,len)
从字符串str 开始，返回从后边开始len个字符组成的子序列
http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/functions.html#encryption-functions
7.运维方向：
数据库的备份
为啥要备份？
将重要的数据保存下来
用法：
#语法：
# mysqldump -h 服务器 -u用户名 -p密码 数据库名 表名, 表名,.... > aaa.sql
#示例：
#单库备份
mysqldump -uroot -p123 db1 > db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql
#多库备份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql
#备份所有库
mysqldump -uroot -p123 --all-databases > all.sql
重新导入：
mysql> source D:/test3.sql;