mysql-视图,触发器,事物,函数,存储过程
一:视图
本质:就是一个虚拟表,根据sql里的语句获取的一个动态集,并为其命名.用户使用时,只需使用名称就可调用结果集,并可把结果集当做表来用.
创建视图(view): create view 视图名 select * from person 例:create view view_person select * from person where id >10; 本质和select * from person where id >10一样 当然虚拟表也有一些好处,当你2表连接在一起,创建视图后,可以直接查看. create view view_person select * from person inner join animal on person.id=animal.person_id; 创建出来虚拟表以后,你要再想查2个表内的数据,就可以不用再写多余的代码.
注意:当你创建虚拟表的时候,你修改虚拟表中的数据时,原表的数据也会跟着改变,
我们要尽量不修改虚拟表中的记录,尤其是在多表连接的状态下,是无法修改比表中数据.
(删除视图)
drop view 视图名.
二:触发器
使用触发器,在程序运行之前后加上(增,删,改)操作,没有查询功能
基本语法: create trigger trigger_name trigger_time trigger_event on table_name for each row trigger_name是整个trigger的名称, trigger_time 是指 before 和after 2个之间的一个,指之前和之后 trigger_event指事件 insert \update\delete table_name指要trigger的table的名称 for each row是指作用在没一行
#准备表 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 // CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW BEGIN IF NEW.success = 'no' THEN #等值判断只有一个等号 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'); #查询错误日志,发现有两条 mysql> select * from errlog; +----+-----------------+---------------------+ | id | err_cmd | err_time | +----+-----------------+---------------------+ | 1 | cat /etc/passwd | 2017-09-14 22:18:48 | | 2 | useradd xxx | 2017-09-14 22:18:48 | +----+-----------------+---------------------+ 2 rows in set (0.00 sec) 复制代码
特别的:NEW表示即将插入的数据行,OLD表示即将删除的数据行。
删除触发器:drop trigger trigger_name
三:事物
事务用于将某些操作的多个SQL作为原子性操作,一旦有某一个出现错误,即可回滚到原来的状态,从而保证数据库数据完整性。
基本语法:
start transation;
body
rollback;
commit;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table user( id int primary key auto_increment, name char(32), balance int ); insert into user(name,balance) values ('wsb',1000), ('egon',1000), ('ysb',1000); #原子操作 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 update user set balance=1090 where name='ysb'; #卖家拿到90元 commit; #出现异常,回滚到初始状态 start transaction; update user set balance=900 where name='wsb'; #买支付100元 update user set balance=1010 where name='egon'; #中介拿走10元 uppdate user set balance=1090 where name='ysb'; #卖家拿到90元,出现异常没有拿到 rollback; commit; mysql> select * from user; +----+------+---------+ | id | name | balance | +----+------+---------+ | 1 | wsb | 1000 | | 2 | egon | 1000 | | 3 | ysb | 1000 | +----+------+---------+ 3 rows in set (0.00 sec)
四:存储
一 介绍
存储过程包含了一系列可执行的sql语句,存储过程存放于MySQL中,通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点:
#1. 用于替代程序写的SQL语句,实现程序与sql解耦
#2. 基于网络传输,传别名的数据量小,而直接传sql数据量大
使用存储过程的缺点:
#1. 执行效率低
#2. 程序员扩展功能不方便
补充:程序与数据库结合使用的三种方式
#方式一:
MySQL:存储过程
程序:调用存储过程
#方式二:
MySQL:
程序:纯SQL语句
#方式三:
MySQL:
程序:类和对象,即ORM(本质还是纯SQL语句)
二 创建简单存储过程(无参)
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
#在mysql中调用
call p1()
#在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())
三 创建存储过程(有参)
对于存储过程,可以接收参数,其参数有三类:
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN
select * from blog where id > n1;
END //
delimiter ;
#在mysql中调用
call p2(3,2)
#在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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;
#在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())
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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;
#在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p4_0;')
print(cursor.fetchall())
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
四 执行存储过程
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
-- 无参数
call proc_name()
-- 有参数,全in
call proc_name(1,2)
-- 有参数,有in,out,inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)
执行存储过程
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)
五 删除存储过程
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
五 函数
MySQL中提供了许多内置函数,例如:
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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'
部分内置函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
一 自定义函数
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
二 删除函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
三 执行函数
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
# 获取返回值
select UPPER('egon') into @res;
SELECT @res;
# 在查询中使用
select f1(11,nid) ,name from tb2;
六 流程控制
一 条件语句
![](https://images.cnblogs.com/OutliningIndicators/ExpandedBlockStart.gif)
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 ;
二 循环语句
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)