视图触发器函数流程控制存储过程 SQL注入问题

最新推荐文章于 2022-04-27 01:13:23 发布

我的紫霞辣辣

最新推荐文章于 2022-04-27 01:13:23 发布

阅读量206

点赞数

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/Yosigo_/article/details/118498444

版权

MySQL 专栏收录该内容

22 篇文章 1 订阅

订阅专栏

视图

视图就是通过查询得到一张虚拟表（非真实存在），然后保存下来，下次可以直接使用，用户使用时直接使用【视图表名】即可获取结果集。
视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用。

使用视图的注意点：

  1.创建视图在硬盘上只会有表结构，没有表数据，数据并没有存在硬盘(数据还是来自之前的表)

  2.视图一般只用来查询，里面的数据不要进行修改，可能会影响真正的表

创建视图语法

  create view 表名 as 虚拟表的查询语句

# 创建一张数据表
select * from employee;
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
| id | name   | sex    | age | hire_date  | post         | post_comment | salary   | office | depart_id |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+
|  1 | sun    | male   |  18 | 2017-03-01 | 猥琐欲为     | NULL         |  1000.22 |    401 |         1 |
|  2 | haha   | male   |  78 | 2015-03-02 | teacher      | NULL         | 10000.31 |    401 |         1 |
|  3 | xixi   | male   |  81 | 2013-03-05 | teacher      | NULL         |  8300.31 |    401 |         1 |
|  4 | dudu   | male   |  72 | 2015-03-12 | teacher      | NULL         |  3100.00 |    401 |         1 |
|  5 | biubiu | male   |  28 | 2014-03-18 | teacher      | NULL         |  5000.00 |    401 |         1 |
|  6 | lala   | female |  58 | 2021-03-23 | teacher      | NULL         |  1020.00 |    401 |         1 |
|  7 | bubu   | male   |  38 | 2015-05-20 | teacher      | NULL         |  1501.00 |    401 |         1 |
|  8 | bobo   | male   |  55 | 2011-03-10 | teacher      | NULL         |  8222.00 |    401 |         1 |
|  9 | 哈哈   | male   |  48 | 2012-03-21 | sale         | NULL         |  3333.00 |    402 |         2 |
| 10 | 呵呵   | male   |  55 | 2013-06-20 | sale         | NULL         |  1050.00 |    402 |         2 |
| 11 | 西西   | male   |  38 | 2014-07-20 | sale         | NULL         |  1200.31 |    402 |         2 |
| 12 | 嘟嘟   | male   |  18 | 2018-12-01 | sale         | NULL         | 10011.00 |    402 |         2 |
| 13 | 萌萌   | male   |  38 | 2010-05-02 | sale         | NULL         | 10020.00 |    402 |         2 |
| 14 | 拉拉   | male   |  58 | 2005-03-25 | sale         | NULL         |  6250.00 |    402 |         2 |
| 15 | 娜娜   | male   |  18 | 2010-03-19 | operation    | NULL         | 20000.00 |    403 |         3 |
| 16 | 宝宝   | female |  18 | 2015-04-05 | operation    | NULL         | 20000.00 |    403 |         3 |
| 17 | 辣辣   | male   |  18 | 2009-03-15 | operation    | NULL         | 18220.00 |    403 |         3 |
| 18 | 蟹蟹   | male   |  18 | 2008-03-20 | operation    | NULL         | 50000.00 |    403 |         3 |
+----+--------+--------+-----+------------+--------------+--------------+----------+--------+-----------+

# 查看salary大于10000的虚拟表
select name,age,post from employee where salary > 10000;
+--------+-----+-----------+
| name   | age | post      |
+--------+-----+-----------+
| haha   |  78 | teacher   |
| 嘟嘟   |  18 | sale      |
| 萌萌   |  38 | sale      |
| 娜娜   |  18 | operation |
| 宝宝   |  18 | operation |
| 辣辣   |  18 | operation |
| 蟹蟹   |  18 | operation |
+--------+-----+-----------+

# 创建视图,将虚拟表的结果产生的表结构保存了,并没有表数据
# 每次我们使用视图的表数据,都是直接触发as后面的sql语句运行,通过查询得到的表数据
create view new_employee as select name,age,post from employee where salary > 10000;

# 查看视图数据
select * from new_employee;
+--------+-----+-----------+
| name   | age | post      |
+--------+-----+-----------+
| haha   |  78 | teacher   |
| 嘟嘟   |  18 | sale      |
| 萌萌   |  38 | sale      |
| 娜娜   |  18 | operation |
| 宝宝   |  18 | operation |
| 辣辣   |  18 | operation |
| 蟹蟹   |  18 | operation |
+--------+-----+-----------+

# 查看数据库指定目录下的视图数据,只有表结构
ls /service/mysql/data/db01
# new_employee.frm

# 修改视图表(本质就是修改视图as后面的sql语句规则)
alter view new_employee as select name,age from employee where id < 3;

select * from new_employee;
+------+-----+
| name | age |
+------+-----+
| sun  |  18 |
| haha |  78 |
+------+-----+

# 删除视图
drop view new_employee;

触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询！！！
使用触发器可以帮助我们实现监控，日志，自动处理异常等等...

触发器可以在6种情况下自动触发 增前增后 删前删后 改前改后

创建触发器基本语法结构

  create trigger 触发器的名字 before/after insert/update/delete 表名
  for each row
  begin 
  	sql语句
  end

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的！！！

当cmd表中的记录success字段是no那么就触发触发器去执行errlog表中插入的数据

# 创建表字段
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")   	 # no代表执行失败
);

create table errlog(
id int primary key auto_increment,
err_cmd char(64),
err_time datetime
);

# 创建触发器
delimiter $$				# delimiter 声明sql语句的结束符号
# create trigger 触发器名 after insert 表名,插入表数据后被动引发触发器执行
create trigger tri_after_insert_cmd after insert on cmd 
for each row		
begin
    if new.success = "no" then      # new 指代的就是cmd表中的一条条数据对象(new.success ==> cmd.success)
    insert into errlog(err_cmd,err_time) values(new.cmd,new.sub_time);		# 必须加分号
    end if;			# 必须加分号
end $$
delimiter ;			# 重新声明sql语句符号为;

# 往表cmd中插入记录，触发触发器，根据if的条件决定是否插入错误日志
insert into cmd(user,priv,cmd,sub_time,success)
values("nana","0755","is -1 /etc",now(),"yes"),
      ("nana","0755","cat /etc/password",now(),"no"),
      ("nana","0755","useradd xxx",now(),"no");

select * from cmd;
+----+------+------+-------------------+---------------------+---------+
| id | user | priv | cmd               | sub_time            | success |
+----+------+------+-------------------+---------------------+---------+
|  1 | nana | 0755 | is -l /etc        | 2021-07-05 22:18:01 | yes     |
|  2 | nana | 0755 | cat /ect/password | 2021-07-05 22:18:01 | no      |
|  3 | nana | 0755 | useradd xxx       | 2021-07-05 22:18:01 | no      |
+----+------+------+-------------------+---------------------+---------+

# 查询错误日志,发现有两条数据
select * from errlog;
+----+-------------------+---------------------+
| id | err_cmd           | err_time            |
+----+-------------------+---------------------+
|  1 | cat /ect/password | 2021-07-05 22:18:01 |
|  2 | useradd xxx       | 2021-07-05 22:18:01 |
+----+-------------------+---------------------+

# 删除触发器
drop trigger tri_after_insert_cmd;

函数

函数只能在sql语句中使用，不能独立调用！！！
若要想在begin…end…中写sql，请用存储过程！！！

内置函数

- 常见的内置函数
# 查看当前登陆的用户
select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

# 查看当前时间
select now();
+---------------------+
| now()               |
+---------------------+
| 2021-07-05 22:45:03 |
+---------------------+

# 随机生成一个小数
select rand();
+--------------------+
| rand()             |
+--------------------+
| 0.6458554832691843 |
+--------------------+

# 四舍五入取值,保留两位小数
select round(6.666,2);
+----------------+
| round(6.666,2) |
+----------------+
|           6.67 |
+----------------+

- date_format内置函数,日期格式的拼接
# 创建表格
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 * from blog;                                                                  
+----+------+---------------------+
| id | name | sub_time            |
+----+------+---------------------+
|  1 | 1    | 2015-03-01 11:31:21 |
|  2 | 2    | 2015-03-11 16:31:21 |
|  3 | 3    | 2016-07-01 10:21:31 |
|  4 | 4    | 2016-07-22 09:23:21 |
|  5 | 5    | 2016-07-23 10:11:11 |
|  6 | 6    | 2016-07-25 11:21:31 |
|  7 | 7    | 2017-03-01 15:33:21 |
|  8 | 8    | 2017-03-01 17:32:21 |
|  9 | 9    | 2017-03-01 18:31:21 |
+----+------+---------------------+

# 提取sub_time的字段数据,格式按照"年月"来进行分组
select date_format(sub_time,"%Y-%m") "时间(年-月)",count(id) from blog group by date_format(sformat(sub_time,"%Y-%m");
+-----------------+-----------+
| 时间(年-月)     | count(id) |
+-----------------+-----------+
| 2015-03         |         2 |
| 2016-07         |         4 |
| 2017-03         |         3 |
+-----------------+-----------+

自定义函数

函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能

# delimiter 声明sql语句的结束符号
delimiter $$		

# 定义一个函数f1
create function f1(
	a int,
	b int)
	returns int
	begin
	declare num int;
	set num = a+b;
	return(num);
	end$$
	
delimiter ; 

# 调用函数
select f1(2,3);
+---------+
| f1(2,3) |
+---------+
|       5 |
+---------+

# 创建一个表
create table t1(id int);

# 调用函数在表中插入数据
mysql> insert t1 values(f1(2,3));

# 查看表数据
select * from t1;
+------+
| id   |
+------+
|    5 |
+------+

# 删除自定义函数
drop function f1;

流程控制

if判断

# 定义一个if判断的存储过程
delimiter //
create procedure proc_if ()
begin
    declare i int default 0;
    if i = 1 then
        select 1;
    elseif i = 2 then
        select 2;
    else
        select 7;
    end if;
end //
delimiter ;

# 调用存储过程
call proc_if();
+---+
| 7 |
+---+
| 7 |
+---+

# 删除存储过程
drop procedure proc_if;

while循环

# 定义一个while循环的存储过程
delimiter //
create procedure proc_while ()
begin
    declare num int ;
    set num = 0 ;
    while num < 3 do   # 如果num小于3，do表示进入循环
        select
            num ;
        set num = num + 1 ;
    end while ;

end //
delimiter ;

# 调用存储过程
call proc_while();
+------+
| num  |
+------+
|    0 |
+------+

+------+
| num  |
+------+
|    1 |
+------+

+------+
| num  |
+------+
|    2 |
+------+

# 删除存储过程
drop procedure proc_while;

存储过程

存储过程常见的三种开发模型

第一种
应用程序：程序员编写代码开发，调用存储过程
mysql：DBA提前编写好存储过程，供程序员调用
  优点：开发效率提升了，执行效率也提升了 

  缺点：考虑到人为因素，跨部门沟通问题，后续的存储国过程扩展性差

第二种
应用程序：程序员编写开发代码，涉及到数据库操作也是自己编写sql语句
  优点：扩展性很高 

  缺点：开发效率低，编写sql语句太过繁琐，后续还需要考虑sql优化的问题

第三种
应用程序：只写程序代码，不写sql语句，基于别人写好的框架(ORM框架)直接调用sql语句即可
   优点：开发效率比上面两种情况都要高 

   缺点：语句扩展性差，可能会出现效率低下问题

通常情况下，我们都是用第三种开发模型，程序运行出现效率低下的问题，再手动写sql语句。

创建无参存储过程

# 定义存储过程
delimiter //
create procedure p1()
begin
    select * from blog;
    insert into blog(name,sub_time) values("nana",now());
end //
delimiter ;

# 在mysql中调用存储过程
call p1();

# 删除存储过程
drop procedure p1;

# 在python中基于pymysql调用
cursor.callproc('p1') 
print(cursor.fetchall())

创建有参存储过程

对于存储过程，可以接收参数，其参数有三类：
  in          	仅用于传入参数用
  out        	仅用于返回值用
  inout     	既可以传参又可以当作返回值

in 仅用于传参

# 定义存储过程
delimiter //
create procedure p2(
    in n1 int,
    in n2 int
)
begin    
    select * from blog where id > n1;
end //
delimiter ;

# 在mysql中调用
call p2(3,2);

# 删除存储过程
drop procedure p2;

# 在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())

out 仅用于返回值用

# 定义存储过程
delimiter //
create procedure p3(
    in n1 int,
    out res int
)
begin
    select * from blog where id > n1;
    set res = 1;
end //
delimiter ;

# 在mysql中调用存储过程
set @res=0; 			# 0代表假（执行失败），1代表真（执行成功）
call p3(3,@res);
select @res;

# 删除存储过程
drop procedure p3;

# 在python中基于pymysql调用
cursor.callproc('p3',(3,0)) 	# 0相当于set @res=0
print(cursor.fetchall()) 	# 查询select的查询结果

cursor.execute('select @_p3_0,@_p3_1;') 	# @p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())

inout 既可以传参又可以当作返回值

# 定义存储过程
delimiter //
create procedure p4(
    inout n1 int
)
begin
    select * from blog where id > n1;
    set n1 = 1;
end //
delimiter ;

# 在mysql中调用存储过程
set @x=3;
call p4(@x);
select @x;

# 删除存储过程
drop procedure p4;

# 在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall())	 # 查询select的查询结果

cursor.execute('select @_p4_0;') 
print(cursor.fetchall())

SQL注入问题

模拟sql注入问题

sql注入问题，我们可以通过特殊的符号改变sql语句的语法规则

  如下所示，我们编写一个登陆功能，需要连接数据库，校验数据库的表数据，可以使用特殊的符号改变sql语句的语法规则
  所以市场上有一些软件，在用户注册的时候，会限制用户设置密码禁止使用一些特殊字符

1. 创建远程登陆用户，并插入用户数据

# 登陆数据库
mysql -uroot -p123

# 创建mysql用户
grant all on *.* to "nana"@"%" identified by "123";

# 刷新用户表
flush privileges;

# 创建本地用户成功之后使用ip登陆做一次测试
mysql -unana -p123 -h192.168.15.51

# 创建表格
create database db01;
use db01;
create table user(id int,name varchar(16),password varchar(32));

# 插入数据
insert user values(1,"nana","123");
insert user values(2,"haha","456");

# 查看用户表数据
mysql> select * from user;
+------+------+----------+
| id   | name | password |
+------+------+----------+
|    1 | nana | 123      |
|    2 | haha | 456      |
+------+------+----------+

2. 模拟sql注入问题

# 使用pymysql模块连接登陆数据库
import pymysql

# 连接数据库
conn = pymysql.connect(
    host="195.168.15.51",
    port=3306,
    user="nana",
    password="123",
    database="db01",
    charset="utf8"
)
# 游标:相当于mysql> ,给mysql提供sql语句
cursor = conn.cursor()

inp_name = input("username>>>:").strip()
inp_passwd = input("password>>>:").strip()
sql = "select * from user where name ='%s' and password = '%s'" % (inp_name, inp_passwd)
print(sql)
# 将命令提交给mysql数据库进行校验
rows = cursor.execute(sql)
if rows:
    print("登陆成功")
else:
    print("用户名密码错误")

cursor.close()  # 关闭游标
conn.close()  # 断开连接

=================================================运行代码===========================================================
# sql语句-- 表示注释
username>>>:kkkk' or True -- '
password>>>:
select * from user where name ='kkkk' or True -- '' and password = '';
登陆成功

sql注入问题解决方案

  方法一: 日常生活中很多软件在注册的时候禁止使用特殊符号

  方法二: 使用python的execute内置函数帮忙拼接，execute默认会把特殊符号自动过滤掉，如下所示

import pymysql

# 连接数据库
conn = pymysql.connect(
    host="192.168.15.51",
    port=3306,
    user="nana",
    password="123",
    database="db01",
    charset="utf8"
)
# 游标:相当于mysql> ,给mysql提供sql语句
cursor = conn.cursor()

inp_name = input("username>>>:").strip()
inp_passwd = input("password>>>:").strip()
# 不要手动拼接数，先用%s占位，之后将需要拼接的数据直接交给execute方法即可
sql = "select * from user where name =%s and password = %s"
print(sql)
# 将命令提交给mysql数据库进行校验
# execute:自动识别sql里面的%s然后用后面元组里面的数据替换，并且会帮你把特殊符号自动过滤掉
rows = cursor.execute(sql,(inp_name,inp_passwd))
if rows:
    print("登陆成功")
else:
    print("用户名或密码错误")

cursor.close()  # 关闭游标
conn.close()  # 断开连接

==================================================运行代码==============================================================
username>>>:kkk' or True -- '
password>>>:
select * from user where name =%s and password = %s
用户名或密码错误