视图
视图就是通过查询得到一张虚拟表（非真实存在），然后保存下来，下次可以直接使用，用户使用时直接使用【视图表名】即可获取结果集。
视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的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
用户名或密码错误
537
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
