视图
视图就是一张虚拟表,虚拟表都是通过查询得到的
使用视图我们可以把查询过程中的临时表摘出来,用视图去实现,这样以后再想操作该临时表的数据时就无需重写复杂的sql了,直接去视图中查找即可,但视图有明显地效率问题,并且视图是存放在数据库中的,如果我们程序中使用的sql过分依赖数据库中的视图,即强耦合,那就意味着扩展sql极为不便,因此并不推荐使用
强调:
1.在硬盘中虚拟表只有 .frm文件(表结构),没有 .idb文件(数据)
2.使用视图以后就无需每次都重写子查询的sql,但是这么效率并不高,还不如我们写子查询的效率高
3.视图通常用来辅助查询,不要修改视图中的数据!!!
案例表
create table course(
cid int primary key auto_increment,
cname char(20),
teacher_id int
);
create table teacher(
tid int primary key auto_increment,
tname char(20)
);
insert into course(cname, teacher_id) values
('生物',1),
('物理',2),
('体育',3),
('美术',2);
insert into teacher(tname) values
('张磊老师'),
('李平老师'),
('刘海燕老师'),
('朱云海老师'),
('李杰老师');
select * from course;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+--------+------------+
select * from teacher;
+-----+-----------------+
| tid | tname |
+-----+-----------------+
| 1 | 张磊老师 |
| 2 | 李平老师 |
| 3 | 刘海燕老师 |
| 4 | 朱云海老师 |
| 5 | 李杰老师 |
+-----+-----------------+
-
创建视图
创建一张老师与课程内连接的虚拟表
create view course_teacher as
select * from course inner join teacher on course.teacher_id = teacher.tid;
select * from course_teacher;
+-----+--------+------------+-----+-----------------+
| cid | cname | teacher_id | tid | tname |
+-----+--------+------------+-----+-----------------+
| 1 | 生物 | 1 | 1 | 张磊老师 |
| 2 | 物理 | 2 | 2 | 李平老师 |
| 3 | 体育 | 3 | 3 | 刘海燕老师 |
| 4 | 美术 | 2 | 2 | 李平老师 |
+-----+--------+------------+-----+-----------------+
-
修改视图
ALTER VIEW 视图名称 AS SQL语句
-
删除视图
DROP VIEW 视图名称
触发器
在满足对某张表数据 增删改 的情况下,自动触发的功能称之为触发器(一种监测机制)
为何要用触发器?
触发器专门针对我们对某一张表数据 增insert,删delete,改update 的行为,这类行为一旦执行,就会触发触发器的执行,即自动运行另外一段sql代码
创建触发器语法:
# 针对增
create trigger tri_after_insert_ti after insert on 表名 for each row
begin
sql代码...
end
create trigger tri_before_insert_ti before insert on 表名 for each row
begin
sql代码...
end
# 针对删
create trigger tri_after_delete_t1 after delete on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_delete_t2 before delete on 表名 for each row
begin
sql代码。。。
end
# 针对改
create trigger tri_after_update_t1 after update on 表名 for each row
begin
sql代码。。。
end
create trigger tri_after_update_t2 before update on 表名 for each row
begin
sql代码。。。
end
# 案例
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
);
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
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');
# 查询errlog表记录
select * from errlog;
# 删除触发器
drop trigger tri_after_insert_cmd;
存储过程
存储过程包含了一系列可执行的sql语句,存储过程放于MySQL中,通过调用它的名字可以执行其内部的一堆sql(像在对象里封装了一堆方法,直接用对象调用方法即可)
存储过程在哪个库下创建的只能在对应的库下面才能使用
优点
1.用于替代程序写的sql语句,实现程序与sql解耦
2.给予网络传输,传别名的数据量小,而直接传sql数据量大
缺点
1.程序员扩展功能不方便
创建存储过程 procedure
create procedure 名(参数)
begin
sql语句
end
使用存储过程
- 无参数
delimiter $$
create procedure p1()
begin
select * from user;
end $$
delimiter ;
#在mysql中调用
call p1()
call p1();
+----+---------+----------+
| id | name | password |
+----+---------+----------+
| 1 | xiongda | 123 |
| 2 | xionger | 456 |
| 3 | xxx | 789 |
+----+---------+----------+
- 有参数
in 用于传入参数使用
out 用于返回值用
inout 既可以传入也可以当做返回值
delimiter $$
create procedure p2(
in n int,
in m int,
out res int
)
begin
select * from user where id > n and id < m;
set res=0;
end $$
delimiter ;
#在mysql中调用
set @res=0; #0代表假(执行失败),1代表真(执行成功)
call p2(1, 3, @res); # 注意:返回值只能接收变量,所以要先定义变量再传入
select @res;
set @res=0;
call p2(1, 3, @res);
+----+---------+----------+
| id | name | password |
+----+---------+----------+
| 2 | xionger | 456 |
+----+---------+----------+
select @res;
+------+
| @res |
+------+
| 0 |
+------+
python中调用存储过程 callproc
python中基于pymsql调用存储过程
游标.callproc(存储过程名, (参数们))
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p2',(1, 3, 1)) # @_p2_0=1,@_p2_1=3,@_p2_2=1,
print(cursor.fetchall()) # [{'id': 2, 'name': 'xionger', 'password': '456'}]
cursor.execute('select @_p2_2;')
print(cursor.fetchall()) # [{'@_p2_2': 0}]
查看存储过程
show create procedure 名;
删除存储过程
drop function 名;
函数
注意与存储过程的区别,mysql内置的函数只能在sql语句中使用!
参考博客:http://www.cnblogs.com/linhaifeng/articles/7495918.html#_label2
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');
流程控制
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 ;
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 ;