数据库终章
MySQL终章
- SQL注入问题
- 视图
- 触发器
- 存储过程
- 流程控制
- 内置函数
- 索引与慢查询优化
SQL注入
SQL(SQL injection)注入问题是早期利用SQL语言中的非法拼接和更改条件或操作数据,是发生在 Web 程序中数据库层的安全漏洞，是网站存在最多也是最简单的漏洞。
主要原因是程序对用户输入数据的合法性没有判断和处理，导致攻击者可以在 Web 应用程序中事先定义好的 SQL 语句中添加额外的 SQL 语句，
在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步获取到数据信息。
- 在python中如何方法SQL注入问题？
"""
数据库中提前准备账户密码表
mysql> select * from userinfo;
+----+--------+-----+
| id | name | pwd |
+----+--------+-----+
| 1 | wesley | 123 |
+----+--------+-----+
"""
import pymysql
conn = pymysql.connect(
host='121.4.86.62',
port=3306,
user='root',
password='Videojet2029@',
database='db7',
charset='utf8mb4',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username = input('name>>: ')
password = input('pass>>: ')
# 构建sql语句
sql = " select * from userinfo where name='%s' and pwd='%s' " % (username, password)
print(sql)
cursor.execute(sql)
res = cursor.fetchall()
if res:
print('登录成功')
print(res)
else:
print('用户名或密码错误')
"""
上述代码存在的问题
sql = " select * from userinfo where name='%s' and pwd='%s' " % (username, password)
如果这一行代码，在前端使用SQL注入将会被恶意拼接
注入: wesley' -- hahsdklflaksdfhk
恶意拼接: select * from userinfo where name='wesley' -- hahsdklflaksdfhk' and pwd=''
所以针对这个问题，需要针对性的过滤一些特殊字符，在python中pymysql的execute()方法会默认将特殊字符过滤
需要更改代码中的SQL拼接，更改为如下所示
sql = "select * from userinfo where name=%s and pwd=%s"
cursor.execute(sql, (username, password))
SQL再次注入效果
name>>: wesley' -- hahshdfhash
pass>>:
select * from userinfo where name=%s and pwd=%s
此时已经无法恶意拼接了
"""
- 视图
视图指的是将使用率高的结果表保存在下来供下次继续使用
- 视图的表只能用来查询，不能做增删改操作，一般情况下视图是不变的
# 如何创建视图
create view teacher2course as
select * from teacher inner join course on teacher.tid = course.teacher_id
1. create view # 创建视图关键字
2. teacher2course # 视图名称
3. 其他查询的SQL语句
- 触发器
触发器指，达到某个条件后自动触发执行，在mysql数据库中针对于表的增加，删除，更新能够自动触发
主要有六种触发机制
- 增加前
在增加数据前触发触发器 - 增加后
在增加数据后触发触发器 - 删除前
在删除数据前触发触发器 - 删除后
在删除数据后触发触发器 - 更新前
在更新数据前触发触发器 - 更新后
在更新数据后触发触发器
- 触发器的命名
见名知意
1. tri_before_insert_t1 # 触发器在插入t1数据前触发
2. tri_after_delete_t2 # 触发器在删除t2数据之后触发
3. tri_after_update_t2 # 触发器在更新t2数据后触发
注意： 由于触发器中可能需要使用;来区分行，这里就和mysql的结束符产生了冲突，所以需要临时使用 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
);
----------上述语句先定义2张表备用----------
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
('wesley', '0755', 'll /etc', NOW(), 'yes'),
('wesley','0755','cat /etc/passwd',NOW(), 'no'),
('wesley','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd | err_time |
+----+-----------------+---------------------+
| 1 | cat /etc/passwd | 2022-11-29 18:37:13 |
+----+-----------------+---------------------+
1 row in set (0.02 sec)
# 查看所有的触发器
mysql> show triggers;
| Trigger| Event | Table | Statement| Timing | Created| sql_mode| Definer | character_set_client | collation_connection | Database Collation |
+----------------------+--------+-------+-----------------
| tri_after_insert_cmd | INSERT | cmd | begin
if NEW.success = 'no' then # 新记录都会被MySQL封装成NEW对象
insert into errlog(err_cmd, err_time) values(NEW.cmd, NEW.sub_time);
end if;
end | AFTER | 2022-11-29 18:30:48.10 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@% | utf8mb4 | utf8mb4_general_ci | utf8mb4_general_ci |
+----------------------+--------+-------
# 删除触发器
mysql> drop trigger tri_after_insert_cmd;
Query OK, 0 rows affected (0.02 sec)
- Mysql事务
MySQL 事务主要用于处理操作量大，复杂度高的数据，比如需要删除一个人员的基本资料，也要删除该人员相关的信息，邮箱，文章等，这些数据库操作语句就构成了一个事物
- 在MySQL中只有使用了InnoDB的数据库引擎的数据库或表才支持事务
- 事务处理可以用来维护数据库的完整性，保证成批的SQL语句全部执行或不执行
- 事务是用来管理 insert,update,delete 语句
- 事务特性（ACID）
-
原子性（Atomicity 或称不可分割性）
原子性：一个事物（transaction）中的所有操作，要么全部完成，要不全部不完成，不会结束再之间的某个环节，事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样 -
一致性（Consistency）
一致性：在事务开始和事务结束以后，数据库的完整性没有被破坏，这表示写入的资料必须完全符合所有的预设规则，包含资料的精准度，窜联性以及后续数据库可以自发性的完成预定工作 -
隔离性（lsolation 又称独立性）
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时有于交叉执行而导致的数据不一致，事务隔离分为不同的级别，每一种级别都规定了事务中所做的修改，set transaction isolation level 级别-
读未提交(Read uncommitted)
事务中的修改即使没有提交，对其他事物也都是可见的，事务可以出去未提交的实际，这一现象也被称为“脏读” -
读提交(read committd)
多数数据库系统的默认隔离级别，一个事务从开始提交之前所作的任何修改对其他事务来说都是不可见的，这种级别也叫做“不可重复读” -
可重复读(repeatable read)
可重复读是MYSQL默认隔离级别，可以解决脏读的问题，但是无法解决幻读，幻读指的是，当某个事物在读取某个范围内的记录时，另一个事务又在改范围内插入了新的记录，当之前的事务再次读取该范围内的记录会产生幻行，InnoDB和XtraDB通过多版本并发控制(MVCC)及间隙锁策略解决该问题 -
串行化(Serializable)
强制事务串行执行，很少使用该级别
-
-
持久性（Durability）
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失
在MySQL默认设置下，事务都是自动提交的，即执行SQL语句后就会马上执行COMMIT操作，所以要显式的开启一个事务需要使用BEGIN或者START TRANSACTION 或者执行 SET AUTOCOMMIT = 0来禁止使用当前会话的自动提交
- MySQL 事务处理的两种方式
- 用BEGIN，ROLLBACK，COMMIT实现
- BEGIN 开始一个事务（ start transaction）
- ROLLBACK 事务回滚
- COMMIT 事务确认
- 直接使用SET 来改变MySQL的自动提交模式
- SET AUTOCOMMIT=0 进制自动提交
- SET AUTOCOMMIT=1 开启自动提交
- 事务特性验证
-- 创建用户表，需求是wesley转给tank 100元 jason收取手续费10元
create table user(
id int primary key auto_increment,
name char(32),
balance int
);
-- 导入初始金额
insert into user(name, balance) values ('wesley',1000),('jason',1000),('tank',1000);
-- 原表
mysql> select * from user;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | wesley | 1000 |
| 2 | jason | 1000 |
| 3 | tank | 1000 |
+----+--------+---------+
3 rows in set (0.02 sec)
-- 开启事务
mysql> start transaction;
1054 - Unknown column 'wesley' in 'where clause'
mysql> update user set balance=900 where name = 'wesley';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set balance=1010 where name = 'jason';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update user set balance=1090 where name = 'tank';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
-- 修改之后的数据
mysql> select * from user;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | wesley | 900 |
| 2 | jason | 1010 |
| 3 | tank | 1090 |
+----+--------+---------+
3 rows in set (0.02 sec)
-- 回滚事务后再查看
mysql> rollback;
Query OK, 0 rows affected (0.02 sec)
-- 在使用事务时，只要没有commit提交事务，数据都只是在内存中存在
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
-- 回滚事务后，事务回滚到之前的状态
mysql> select * from user;
+----+--------+---------+
| id | name | balance |
+----+--------+---------+
| 1 | wesley | 1000 |
| 2 | jason | 1000 |
| 3 | tank | 1000 |
+----+--------+---------+
3 rows in set (0.02 sec)
- MVCC
MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作，其他两个不兼容(read uncommitted:总是读取最新 serializable:所有的行都加锁)
InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现MVCC
一个列保存了行的创建时间
一个列保存了行的过期时间(或删除时间) # 本质是系统版本号
每开始一个新的事务版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号用来和查询到的每行记录版本号进行比较
例如
刚插入第一条数据的时候，我们默认事务id为1，实际是这样存储的
username create_version delete_version
jason 1
可以看到，我们在content列插入了kobe这条数据，在create_version这列存储了1，1是这次插入操作的事务id。
然后我们将jason修改为jason01，实际存储是这样的
username create_version delete_version
jason 1 2
jason01 2
可以看到，update的时候，会先将之前的数据delete_version标记为当前新的事务id，也就是2，然后将新数据写入，将新数据的create_version标记为新的事务id
当我们删除数据的时候，实际存储是这样的
username create_version delete_version
jason01 2 3
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""
- 存储过程
存储过程是事先经过编译并存储在数据库中的一段SQL 语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。 存储过程思想上很简单，就是数据库SQL 语言层面的代码封装与重用
特点
- 封装
- 复用
- 可以接收参数，也可以返回数据减少网络交互，效率提升
=COPY=START=
-- 创建语法
create procedure 存储过程名称( [ 参数列表 ] )
BEGIN
SQL 语句
END;
-- NOTE: 在命令行中，执行创建存储过程的SQL时，需要通过关键字delimiter 指定SQL语句的结束符。默认是 分号作为结束符。
-- delimiter $ ，则 $ 符作为结束符。
调用
CALL 名称 ( [参数])
# 无参函数
delimiter $$
create procedure p1()
begin
select * from cmd;
end $$
delimiter ;
# 调用
call p1()
# 有参函数
delimiter $$
create procedure p2(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select * from cmd where id > m and id < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 针对res需要先提前定义
set @res=10; 定义
select @res; 查看
call p1(1,5,@res) 调用
select @res 查看
"""
查看存储过程具体信息
show create procedure pro1;
查看所有存储过程
show procedure status;
删除存储过程
drop procedure pro1;
"""
# 大前提:存储过程在哪个库下面创建的只能在对应的库下面才能使用！！！
# 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;')
- 函数
可以看成是python中的内置函数
"ps:可以通过help 函数名 查看帮助信息!"
# 1.移除指定字符
Trim、LTrim、RTrim
# 2.大小写转换
Lower、Upper
# 3.获取左右起始指定个数字符
Left、Right
# 4.返回读音相似值(对英文效果)
Soundex
"""
eg:客户表中有一个顾客登记的用户名为J.Lee
但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
where Soundex(name)=Soundex('J.Lie')
"""
# 5.日期格式:date_format
'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
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');
1.where Date(sub_time) = '2015-03-01'
2.where Year(sub_time)=2016 AND Month(sub_time)=07;
# 更多日期处理相关函数
adddate 增加一个日期
addtime 增加一个时间
datediff计算两个日期差值
- 流程控制
# 分支结构
declare i int default 0;
IF i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
# 循环结构
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT num ;
SET num = num + 1 ;
END WHILE ;
- 索引相关概念
- 索引相当于是书的目录，可以更快的找到找到自己想要的内容
索引在Mysql中也叫做建，是存储引擎用于快速找到记录的一种数据结构
- primary key
- unique key
- index key
primary key和unique key除了可以加快查询本身还自带限制条件而index key很单一就是用来加快数据查询
-- 通过代码看查询本质
id int primary key auto_increment,
name varchar(32) unique,
province varchar(32)
age int
phone bigint
-- 遍历查找
select name from userinfo where phone=1888888
-- 通过B+Tree查找
select name from userinfo where id=9999
/*
索引可以加快数据查询 但是会降低增删的速度
通常情况下我们频繁使用某些字段查询数据
为了提升查询的速度可以将该字段建立索引
*/
聚集索引(primary key)
主键、主键索引
辅助索引(unique,index)
除主键意外的都是辅助索引
覆盖索引(需要查询的字段值与查找字段同一位置)
select name from user where name='jason';
非覆盖索引(需要查询的字段值与查找字段不在位置)
select age from user where name='jason';
- 索引数据结构
索引底层其实是树结构>>>:树是计算机底层的数据结构
树有很多中类型
二叉树、b树、b+树、B*树......
二叉树
二叉树里面还可以细分成很多领域 我们简单的了解即可
二叉意味着每个节点最大只能分两个子节点
B树
所有的节点都可以存放完整的数据
B+\*树
只有叶子节点才会存放真正的数据 其他节点只存放索引数据
B+叶子节点增加了指向其他叶子节点的指针
B*叶子节点和枝节点都有指向其他节点的指针
辅助索引在查询数据的时候最会还是需要借助于聚集索引
辅助索引叶子节点存放的是数据的主键值
有时候就算采用索引字段查询数据 也可能不会走索引!!!
最好能记三个左右的特殊情况
- 慢查询优化
explain # 通过在sql语句前面加上改关键字，可以看到下列的执行级别，一般在range级别或之上的级别都是正常的sql，在写sql时候尽量避免index级别
1）index 尽量避免
2）range
3）ref
4）eq_ref
5）const
6）system
7）null
- 测试索引
#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();
-- 开始验证
mysql> desc s1;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | char(6) | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.02 sec)
mysql> select count(id) from s1 where id = 30000;
+-----------+
| count(id) |
+-----------+
| 1 |
+-----------+
1 row in set (1.21 sec)
mysql> select count(id) from s1 where id = 1;
+-----------+
| count(id) |
+-----------+
| 1 |
+-----------+
1 row in set (1.27 sec)
mysql>
mysql> alter table s1 add primary key(id);
Query OK, 0 rows affected (28.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select count(id) from s1 where id =1;
+-----------+
| count(id) |
+-----------+
| 1 |
+-----------+
1 row in set (0.02 sec)
mysql> select count(id) from s1 where name = 'jason';
+-----------+
| count(id) |
+-----------+
| 2999999 |
+-----------+
1 row in set (0.87 sec)
"""
范围问题
"""
# 并不是加了索引，以后查询的时候按照这个字段速度就一定快
mysql> select count(id) from s1 where id >1;
+-----------+
| count(id) |
+-----------+
| 2999998 |
+-----------+
1 row in set (0.74 sec)
mysql> select count(id) from s1 where id >1 and id <3;
+-----------+
| count(id) |
+-----------+
| 1 |
+-----------+
1 row in set (0.02 sec)
mysql> select count(id) from s1 where id >1 and id <10000;
+-----------+
| count(id) |
+-----------+
| 9998 |
+-----------+
1 row in set (0.02 sec)
mysql> select count(id) from s1 where id != 3;
+-----------+
| count(id) |
+-----------+
| 2999998 |
+-----------+
1 row in set (0.73 sec)
mysql> alter table s1 drop primary key;
Query OK, 2999999 rows affected (15.62 sec)
Records: 2999999 Duplicates: 0 Warnings: 0
mysql> select count(id) from s1 where name = 'jason';
+-----------+
| count(id) |
+-----------+
| 2999999 |
+-----------+
1 row in set (1.52 sec)
浙公网安备 33010602011771号