网页设计企业网站素材库/怎么优化电脑系统

mysql:视图、触发器、事务、存储过程、函数

一、视图

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

create table dep(
id int primary key auto_increment,
name char(32)
)engine=innodb default charset utf8;mysql> select * from dep;
+----+--------+
| id | name   |
+----+--------+
|  1 | 外交部 |
|  2 | 销售   |
|  3 | 拆迁部 |
+----+--------+
3 rows in set (0.00 sec)create table user(
id int primary key auto_increment,
name char(32),
dep_id int,
foreign key(dep_id) references dep(id)
on delete cascade
on update cascade
)engine=innodb default charset utf8;
#
mysql> select * from user;
+----+---------+--------+
| id | name    | dep_id |
+----+---------+--------+
|  1 | egon    |      1 |
|  2 | alex    |      3 |
|  3 | yuanhao |      2 |
+----+---------+--------+
3 rows in set (0.00 sec)#建立视图
CREATE VIEW user_dep AS SELECTUSER.id uid,USER. NAME uname,dep.id depid,dep.NAME depname
FROMUSER
LEFT JOIN dep on dep_id = dep.id;mysql> select * from user_dep;
+-----+---------+-------+---------+
| uid | uname   | depid | depname |
+-----+---------+-------+---------+
|   1 | egon    |     1 | 外交部  |
|   3 | yuanhao |     2 | 销售    |
|   2 | alex    |     3 | 拆迁部  |
+-----+---------+-------+---------+
3 rows in set (0.00 sec)

二、使用视图

#先创建一个视图
mysql> create view course_view as select * from user;
Query OK, 0 rows affected (0.05 sec)
#多了一个刚刚建立的视图
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| course_view   |
| dep           |
| user          |
| user_dep      |
+---------------+
4 rows in set (0.00 sec)
#查看视图数据
mysql> select * from course_view;
+----+---------+--------+
| id | name    | dep_id |
+----+---------+--------+
|  1 | egon    |      1 |
|  2 | alex    |      3 |
|  3 | yuanhao |      2 |
+----+---------+--------+
3 rows in set (0.00 sec)mysql> update course_view set name='李平';    #更新视图里面的数据
Query OK, 3 rows affected (0.09 sec)
Rows matched: 3  Changed: 3  Warnings: 0
#查看刚刚改的视图
mysql> select * from course_view;
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 李平 |      1 |
|  2 | 李平 |      3 |
|  3 | 李平 |      2 |
+----+------+--------+
3 rows in set (0.03 sec)mysql> insert into course_view values(5,'张三',2);
Query OK, 1 row affected (0.05 sec)mysql> select * from user;              #原始表的记录也跟着改了
+----+------+--------+
| id | name | dep_id |
+----+------+--------+
|  1 | 李平 |      1 |
|  2 | 李平 |      3 |
|  3 | 李平 |      2 |
|  5 | 张三 |      2 |
+----+------+--------+
4 rows in set (0.00 sec)

三、修改视图

 alter view user_dep as select * from  user where id=3;       #修改为只有id=3的字段mysql> select * from user_dep;                 #在查看视图，发现就只有id=3的了
+----+---------+--------+
| id | name    | dep_id |
+----+---------+--------+
|  3 | yuanhao |      2 |
+----+---------+--------+
1 row in set (0.03 sec)

四、删除视图

语法：DROP VIEW 视图名称DROP VIEW teacher_view

二、触发器

使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询

一 创建触发器

# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN...
END# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN...
END# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN...
END# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN...
END# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN...
END# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN...
END

#创建触发器的语法
CREATETRIGGER trigger_nametrigger_time trigger_eventON tbl_name FOR EACH ROWtrigger_bodytrigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

第一步创建表
mysql> create table cmd_log(-> id int primary key auto_increment,-> cmd_name char(64),-> sub_time datetime,            #提交时间-> user_name char(32),         -> is_success enum('yes','no')    #0代表执行失败-> );
Query OK, 0 rows affected (0.28 sec)mysql> create table err_log(-> id int primary key auto_increment,-> cname char(64),-> stime datetime-> );
Query OK, 0 rows affected (0.27 sec)
#创建触发器
mysql> delimiter //
mysql> CREATE->     TRIGGER tri_after_insert_cmd_log->     after insert->     ON cmd_log FOR EACH ROW-> BEGIN->     if new.is_success = 'no' then            #等值判断如果登陆on则插入到err_log的表里面->         insert into err_log(cname,stime) values(new.cmd_name,new.sub_time);->     end if;     #if语句必须end if;结束-> END //            #END表示语句结束
mysql> delimiter ;
Query OK, 0 rows affected (0.17 sec)#往表中插入数据触发触发器，根据if的条件决定是否插入错误日志
mysql> insert into cmd_log(cmd_name,sub_time,user_name,is_success) values    -> ('ls -l /etc | grep *.conf',now(),'root','no'), #NEW.id,NEW.cmd_name,NEW.sub_time   -> ('ps aux |grep mysqld',now(),'root','yes'),-> ('cat /etc/passwd |grep root',now(),'root','yes'),-> ('netstat -tunalp |grep 3306',now(),'egon','no');
Query OK, 4 rows affected (0.11 sec)
Records: 4  Duplicates: 0  Warnings: 0#查询错误日志的表，
mysql> select * from err_log;
+----+----------------------------+---------------------+
| id | cname                      | stime               |
+----+----------------------------+---------------------+
|  1 | ls -l /etc | grep *.conf   | 2017-09-18 19:33:06 |        #发现果然有两张表记录了插入的数据内容，插入的时间
|  2 | netstat -tunalp |grep 3306 | 2017-09-18 19:33:06 |
+----+----------------------------+---------------------+
2 rows in set (0.00 sec)

二、删除触发器

drop trigger tri_after_insert_cmd;

三、事务

#事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
create table user1(
id int primary key auto_increment,
name char(32),
balance int
);insert into user1(name,balance) values
('庞文废',200),
('萧pt',200),
('八哥',200);start transaction;
update user1 set balance=100 where name='ss';
update user1 set balance=210 where name='pt';
update user1 set balance=290 where name='bage';
rollback; #如果任意一条sql出现异常都应该回滚到初始状态
commit; #如果所有的sql都正常，应该执行commitstart transaction;
update user1 set balance=100 where name='ss';
update user1 set balance=210 where name='pt';
updssssssate user1 set balance=290 where name='bage';
rollback; #如果任意一条sql出现异常都应该回滚到初始状态
commit; #如果所有的sql都正常，应该执行commit

四、存储过程

一 介绍

存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql

使用存储过程的优点：

#使用存储过程的优点#1 程序与数据实现解耦#2 减少网络传输的数据量

使用存储过程的缺点：#1. 执行效率低#2. 程序员扩展功能不方便

补充：程序与数据库结合使用的三种方式

二、创建存储过程的方式

#方式一：MySQL：存储过程程序：调用存储过程#方式二：MySQL：程序：纯SQL语句#方式三：MySQL:程序：类和对象，即ORM（本质还是纯SQL语句）

三、创建简单存储的过程

#===============================================
#创建无参存储过程
delimiter //
create procedure p1()
beginselect * from test;insert into test(username,dep_id) values('wsb',2);
end //
delimiter ;#调用存储过程
call p1(); #在mysql中调用
cursor.callproc('p1') #在python中通过pymysql模块调用

四、创建有参存储的过程

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

#创建有参存储过程之in的使用
delimiter //
create procedure p2(in m int,in n int,
)
beginselect * from test where id between m and n;
end //
delimiter ;#调用存储过程
call p2(3,7); #在mysql中调用
cursor.callproc('p2',args=(3,7)) #在python中通过pymysql模块调用#在python中
res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall()) #可以拿到的是返回值

#创建有参存储过程之inout的使用delimiter //
create procedure p4(inout m int
)
beginselect * from test where id > m;set m=1;
end //
delimiter ;#在mysql中
set @x=2;
call p4(@x);
select @x;delimiter //
create procedure p5(inout m int
)
beginselect * from test11111111 where id > m;set m=1;
end //
delimiter ;set @x=2;
call p5(@x);
select @x;

#===============================================
#创建有参存储过程之out的使用
delimiter //
create procedure p3(in m int,in n int,out res int
)
beginselect * from test where id between m and n;set res=1;
end //
delimiter ;#调用存储过程
#在mysql中
set @x=11111111111
call p3(3,7,@x); #在mysql中调用, 查看结果：select @x;#在python中
res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果
cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall()) #可以拿到的是返回值

#====================捕捉异常+事务===========================
delimiter //
create PROCEDURE p6(OUT p_return_code tinyint
)
BEGINDECLARE exit handler for sqlexceptionBEGIN-- ERRORset p_return_code = 1;rollback;END;DECLARE exit handler for sqlwarningBEGIN-- WARNINGset p_return_code = 2;rollback;END;START TRANSACTION;insert into test(username,dep_id) values('egon',1);DELETE from tb1111111; #执行失败COMMIT;-- SUCCESSset p_return_code = 0; #0代表执行成功END //
delimiter ;#用python模拟
try:START TRANSACTION;DELETE from tb1; #执行失败insert into blog(name,sub_time) values('yyy',now());COMMIT;set p_return_code = 0; #0代表执行成功
except sqlexception:set p_return_code = 1;rollback;
except sqlwaring:set p_return_code = 2;rollback;mysql> show procedure status like 'p1%'; #查看某一类存储过程

四、执行存储过程

import pymysqlconn=pymysql.connect(host='localhost',user='root',password='',database='day48',charset='utf8')
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)res=cursor.callproc('p3',args=(3,7,123)) #@_p3_0=3,@_p3_1=7,@_p3_2=123conn.commit()
print(cursor.fetchall()) #只是拿到存储过程中select的查询结果cursor.execute('select @_p3_0,@_p3_1,@_p3_2')
print(cursor.fetchall())
cursor.close()
conn.close()

五、删除存储过程

drop procedure proc_name;

五、函数

CHAR_LENGTH(str)返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。CONCAT(str1,str2,...)字符串拼接如有任何一个参数为NULL ，则返回值为 NULL。CONCAT_WS(separator,str1,str2,...)字符串拼接（自定义连接符）CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。CONV(N,from_base,to_base)进制转换例如：SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示FORMAT(X,D)将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若  D 为 0, 则返回结果不带有小数点，或不含小数部分。例如：SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'INSERT(str,pos,len,newstr)在str的指定位置插入字符串pos：要替换位置其实位置len：替换的长度newstr：新字符串特别的：如果pos超过原字符串长度，则返回原字符串如果len超过原字符串长度，则由新字符串完全替换INSTR(str,substr)返回字符串 str 中子字符串的第一个出现位置。LEFT(str,len)返回字符串str 从开始的len位置的子序列字符。LOWER(str)变小写UPPER(str)变大写LTRIM(str)返回字符串 str ，其引导空格字符被删除。RTRIM(str)返回字符串 str ，结尾空格字符被删去。SUBSTRING(str,pos,len)获取字符串子序列LOCATE(substr,str,pos)获取子序列索引位置REPEAT(str,count)返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。若 count <= 0,则返回一个空字符串。若str 或 count 为 NULL，则返回 NULL 。REPLACE(str,from_str,to_str)返回字符串str 以及所有被字符串to_str替代的字符串from_str 。REVERSE(str)返回字符串 str ，顺序和字符顺序相反。RIGHT(str,len)从字符串str 开始，返回从后边开始len个字符组成的子序列SPACE(N)返回一个由N空格组成的字符串。SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。mysql> SELECT SUBSTRING('Quadratically',5);-> 'ratically'mysql> SELECT SUBSTRING('foobarbar' FROM 4);-> 'barbar'mysql> SELECT SUBSTRING('Quadratically',5,6);-> 'ratica'mysql> SELECT SUBSTRING('Sakila', -3);-> 'ila'mysql> SELECT SUBSTRING('Sakila', -5, 3);-> 'aki'mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);-> 'ki'TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。mysql> SELECT TRIM('  bar   ');-> 'bar'mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx');-> 'barxxx'mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx');-> 'bar'mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz');-> 'barx'部分内置函数

#需要掌握的函数
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');
# 提取sub_time字段的值，按照格式输出出来
select date_format(sub_time,'%Y-%m'),count(1) from blog group by date_format(sub_time,'%Y-%m');#结果
+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03                       |        2 |
| 2016-07                       |        4 |
| 2017-03                       |        3 |
+-------------------------------+----------+
3 rows in set (0.00 sec)

一 自定义函数

#自定义函数
mysql> select f1(1,2)-> ;
+---------+
| f1(1,2) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)mysql> select f1(1,2) into @res;
Query OK, 1 row affected (0.00 sec)mysql> select @res;
+------+
| @res |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

自定义函数

#自定义函数
mysql> select f1(1,2)-> ;
+---------+
| f1(1,2) |
+---------+
|       3 |
+---------+
1 row in set (0.00 sec)mysql> select f1(1,2) into @res;
Query OK, 1 row affected (0.00 sec)mysql> select @res;
+------+
| @res |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

删除函数

drop function func_name;

执行函数

# 获取返回值
select UPPER('egon') into @res;
SELECT @res;# 在查询中使用
select f1(11,nid) ,name from tb2;

六、流程控制

一、条件语句

delimiter //
CREATE PROCEDURE proc_if ()
BEGINdeclare i int default 0;if i = 1 THENSELECT 1;ELSEIF i = 2 THENSELECT 2;ELSESELECT 7;END IF;END //
delimiter ;

二、循环语句

delimiter //
CREATE PROCEDURE proc_while ()
BEGINDECLARE num INT ;SET num = 0 ;WHILE num < 10 DOSELECTnum ;SET num = num + 1 ;END WHILE ;END //
delimiter ;

#repeat循环
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGINDECLARE i INT ;SET i = 0 ;repeatselect i;set i = i + 1;until i >= 5end repeat;END //
delimiter ;

#loop
BEGINdeclare i int default 0;loop_label: loopset i=i+1;if i<8 theniterate loop_label;end if;if i>=10 thenleave loop_label;end if;select i;end loop loop_label;END

<wiz_tmp_tag id="wiz-table-range-border" contenteditable="false" style="display: none;">

来自为知笔记(Wiz)