昨日回顾
# 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.28\data\DESKTOP-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)
#
# 特性:(****************)
# 原子性(Atomicity),原子意为最小的粒子,即不能再分的事务,要么全部执行,要么全部取消(就像上面的银行例子)
# 一致性(Consistency):指事务发生前和发生后,数据的总额依然匹配
# 隔离性(Isolation):简单点说,某个事务的操作对其他事务不可见的
# 持久性(Durability):当事务完成后,其影响应该保留下来,不能撤消,只能通过“补偿性事务”来抵消之前的错误
#
# 存储引擎:(**************)
#
# 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 triggers\G
# *************************** 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;