SQL注入问题
# 1.关于sql的注入问题：
"""利用特殊符号的组合产生特殊的含义，改变正确的业务逻辑"""
1. select * from userinfo where name='jason' --kasfsdf' and pwd=''
2. select * from userinfo where name='xyz' or 1=1 -- aksdjasldj' and pwd=''
* 针对上述的SQL注入问题 核心在于手动拼接了关键数据 交给execute处理即可
sql = " select * from userinfo where name=%s and pwd=%s "
cursor.execute(sql, (username, password))
# 2.数据库实现用户登录：
import pymysql
conn = pymysql.connect(
host='127.0.0.1',
port=3306,
user='root',
password='123',
database='data1',
charset='utf8',
autocommit=True
)
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 1.获取用户名和密码
username = input('username>>>:').strip()
password = input('password>>>:').strip()
# 2.构造查询SQL语句
# sql = " select * from userinfo where name='%s' and pwd='%s' " % (username, password)
# sql = " select * from userinfo where name=%s and pwd=%s "
sql = " insert into userinfo(name,pwd) values(%s,%s)"
print(sql)
# 3.发送给服务端执行SQL语句
# cursor.execute(sql, (username, password))
cursor.executemany(sql, [('oscar', '123'), ('maria', '333'), ('lili', '567')])
# 4.获取执行结果
res = cursor.fetchall()
if res:
print('登录成功')
print(res)
else:
print('用户名或密码错误')
视图
# 视图其实就是一张被查询出来的表，这张表是虚拟的，无法进行删除
1.视图通常用作查询，尽量不要修改里面的数据
2.视图只有表结构文件，没有表数据文件
3.视图尽量少用，会和真正的表产生混淆，干扰到操作者
"""
关键字： view
模板： create view 视图名 as SQL语句 # 尽量少用
例： create view teacher2course as select * from teacher inner join course on teacher.tid = course.teacher_id
"""
触发器
# 1.触发器会在达到某个(针对数据的增删改查)条件后自动触发
"""
关键字：
trigger
语法结构：
create trigger 触发器的名字 before/after
insert/update/delete on 表名 for each row
begin
sql语句
end
"""
* 在使用触发器之前，需要先对结束符进行修改，因为触发器会用到;结束符
"""
delimiter 要修改的结束符 # 结束符修改后仅限临时使用，结束后要修改回来
"""
# 2.触发器实例
1.创建一个cmd表，记录每次执行插入的数据
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代表执行失败
);
2.创建一个errlog表，记录每次执行错误的数据
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
3.使用触发器自动触发针对表数据的增改删
delimiter $$ # 修改结束符
create trigger tri_after_insert_cmd after insert on cmd for each row
begin
if NEW.success = 'no' then # NEW是MYSQL里面封装的数据对象
insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
end $$
delemiter ; # 结束之后改回来结束符
4.往cmd中插入数据，触发触发器，依据if条件判断，是否插入错误日志数据
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('kevin','0755','ls -l /etc',NOW(),'yes'),
('kevin','0755','cat /etc/passwd',NOW(),'no'),
('kevin','0755','useradd xxx',NOW(),'no'),
('kevin','0755','ps aux',NOW(),'yes');
5.查询错误日志表errlog的记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd; # 第一次给触发器起名要做到见名知意
事务
# 1.事务的四大特性(ACID)
A:原子性 ：必须保证事务的各项操作同时成功或同时失败
C:一致性 ：是数据库从一个一致性状态变到另一个一致性状态
I :隔离性 ：多个用户并发访问数据库，每一个开启的用户事务之间彼此不干扰
Ｄ:持久性 ：指一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，数据库发生问题不会影响
# 2.事务的创建
关键字：
start transaction
创建事务操作： 模拟银行转账
- 1.创建一个用户表
create table user(id int primary key auto_increment,
name char(32),
balance int
);
- 2.插入用户数据
insert into user(name,balance) values
('jason',1000),
('kevin',1000),
('tank',1000);
- 3.开启事务操作
start transaction;
- 4.修改数据操作
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
- 5.使用rollback返回到上一次状态(模拟转账转错了，追回金额)
rollback;
"""
关于为什么可以回退金额：
因为，开启事务后，我们操作的数据并没有保存到硬盘，
所以为了数据能够刷到硬盘需要使用commit操作
"""
- 6.将修改后的数据刷到硬盘中
commit;
"""
如果是python底层的代码，逻辑应该是这样的：
try:
update user set balance=900 where name='jason'; #买支付100元
update user set balance=1010 where name='kevin'; #中介拿走10元
update user set balance=1090 where name='tank'; #卖家拿到90元
except 异常:
rollback; # 回滚
else:
commit; # 保存
"""
* 知识补充：
'''
MYSQL中提供两种事务存储引擎InnoDB(重要)与NDB cluster，及三方的XtraDB，PBXT。
事务处理相关关键词汇：
- 1.事务 transaction
- 2.回滚 rollback
- 3.提交 commit
- 4.保留点 savepoint
"""
为了支持回退部分事务处理，而出现的方法
(该方法违反了事务的原则，且有一些数据库不支持保留点。)
使用: 在事务处理块的合适位置放置占位符,有需要可以回退到某个占位符
创建占位符： savepoint sp01; # sp01是自己创建占位符
回滚占位符： rollback to sp01;
'''
事务的隔离级别
# 1.数据库中定义了四种事务的隔离级别，隔离级别规定了能对事务做修改权限。
- 1. read uncommitted (未提交读)
可以读取到事务里修改了但未提交的数据，也被称为"脏读",其他事务对脏读是可见的
- 2. read committed (提交读)
能读取到提交后的数据，但是读取不了提交之前的所作修改的数据，也叫做"不可重复读"
提交读是大部分数据库默认的隔离级别
- 3. repeatable read (可重复读)
可以解决脏读，无法解决幻读，幻读指的是事务读取某个范围的记录时，插入的新的记录，
事务再次读取会产生幻行，InnoDB要通过多版本并发控制(MVCC)及间隙锁策略解决问题
- 4. serializable (可串行读)
强制事务串行执行，很少使用该级别
- MVCC只能在read committed(提交读)、repeatable read(可重复读)两种隔离级别下工作
- InnoDB的MVCC通过在每行记录后面保存两个隐藏的列来实现
"""
由此当我们查询一条记录的时候，只有满足以下两个条件的记录才会被显示出来：
1.当前事务id要大于或者等于当前行的create_version值，这表示在事务开始前这行数据已经存在了。
2.当前事务id要小于delete_version值，这表示在事务开始之后这行记录才被删除。
"""
""" 1. InnoDB支持所有的隔离级别 语法： set transaction isolation level 级别 2. 事务的日志 记录事务修改存储记录，将其持久到硬盘上的事务日志中 """
存储过程
# 存储过程可以看成python中的自定义函数
存储过程的基本使用：
模板：
delimiter 临时结束符
create procedure 名字(参数，参数) # 加的参数要给出名字和数据类型
begin
sql语句；
end 临时结束符
delimiter ；
实操：
# 1. 针对res需要提前定义出来
set @res=10; # 定义
select @res; # 查看
# 2. 存储res
delimiter $$
create procedure p1(
in m int, # in表示这个参数必须只能是传入不能被返回出去
in n int,
out res int # out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
)
begin
select tname from teacher where tid > m and tid < n;
set res=0; # 用来标志存储过程是否执行
end $$
delimiter ;
# 3. 查看res的值
call p1(1,5,@res); # 调用
select @res; # 查看
内置函数
# 可以通过使用(help 函数名) 查看帮助信息
-1.移除指定字符
Trim,LTrim,RTrim
-2.大小写转换
Lower,Upper
-3.获取左右起始指定个数字符
Left,Right
-4.返回读音相似值(对英文效果)
Soundex
例： jason，搜索所有与其读音相似的
where Soundex(name)=Soundex('jason')
-5.日期格式
date_format
# 相关日期函数
adddate 增加一个日期
addtime 增加一个时间
datediff 计算两个日期差值
例：
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 计算两个日期差值
流程控制
# 1.if条件
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN # THEN = :
SELECT 1;
ELSEIF i = 2 THEN # ELSEIF = elif
SELECT 2;
ELSE
SELECT 7;
END IF; # 声明结束if
END //
delimiter ;
# 2.while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE ;
END //
delimiter ;
索引相关与慢查询优化
# 1.索引
-1.在mysql中实现索引的方式叫做键，作为一种数据结构，大致有三种：
primary key 主键
unique key 唯一键
index key 索引键，无任何意义
-2.区别
primary key,unique key 加快了数据查询，但是有额外限制
index key 没有额外限制，加快数据查询
-3.索引的存在可以加快数据的查询 但是会减慢数据的增删
# 2.索引数据结构
-1. 索引底层其实是树结构>>>:树是计算机底层的数据结构
-2.树有很多中类型： 二叉树、b树、b+树、B*树......
-3.二叉树
二叉树里面还可以细分成很多领域 我们简单的了解即可
二叉意味着每个节点最大只能分两个子节点
-4.B树
所有的节点都可以存放完整的数据
-5.B+\*树
只有叶子节点才会存放真正的数据 其他节点只存放索引数据
B+叶子节点增加了指向其他叶子节点的指针
B*叶子节点和枝节点都有指向其他节点的指针
"""
辅助索引在查询数据的时候最会还是需要借助于聚集索引
辅助索引叶子节点存放的是数据的主键值
有时候就算采用索引字段查询数据 也可能不会走索引
最好能记三个左右的特殊情况
"""
# 3.慢查询优化
关键字： explain
语法： explain select 字段名 from 表名 where 筛选条件;
"""
1.explain在不同版本mysql数据库中存在微小的区别：
在5.6版本中使用explain不会显示partitions,filtered。
-1. 第一次变种:
explain extended 可以在mysql5.6显示filtered字段。
rows*filtered/100 可以估算将要与explain中前一个表进行连接的行数。
复制代码
-2. 第二次变种:
explain partitions 可以在mysql5.6显示partitions字段。
可以显示查询将访问的分区。
'''实际的这两个字段也并不重要'''
2.show warnings;
在explain操作结束后，对mysql内部优化后的结果按照展示的结果执行。
3.查询数据的方式 : 全表查询
在explain语句结果中type的值为ALL
什么时候出现全表查询？
-1. 业务需要获取所有的数据
-2. 不执行索引导致要全表扫描
* 2.1 没有索引
* 2.2 索引创建有问题
* 2.3 语句有问题
4.mysql内要避免全表扫描，因为在进行全表扫描时会降低mysq的性能
"""
# 4.常见的索引扫描类型
-1. index : 在index下还有一个all，区别是index类型的只遍历索引树
-2. range : 索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行
-3. ref : 使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配某个单独值的记录行
-4. eq_ref: 类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
-5. const : 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问
-6. system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问
-7. null : MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成
从上到下，性能越强，我们一般写的sql语句需要到达range级别以上
二叉树
B树
B+树
B*树
标签：事务,name,where,查询,索引,MYSQL,数据 From： https://www.cnblogs.com/juzijunjun/p/16936590.html