视图、存储过程、触发器

一、视图

1、视图的定义

　　视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影。

　　同一张表可以创建多个视图

　　创建视图的语法：　　

create view view_name as select 语句

　　说明：

　　（1）视图名跟表名是一个级别的名字，隶属于数据库；

　　（2）该语句的含义可以理解为：就是将该select命名为该名字（视图名）；

　　（3）视图也可以设定自己的字段名，而不是select语句本身的字段名——通常不设置；

　　（4）视图的使用，几乎跟表一样！

2、视图的作用

2.1、可以简化查询

　　案例一：查询平均价格前3高的类目

　　传统的sql语句写法：select cat_id,avg(price) as pj from goods group by cat_id order by pj desc limit 3;

　　下面创建一个视图，简化查询。

　　语法：create view goods_v as select cat_id,avg(price) as pj from goods group by cat_id;

　　查询平均价格前3高的栏目，我们只需查询视图即可，如下图：

　　案例二：查询出商品表，以及所在的类目名称

　　传统写法：select a.id as goods_id,goods_name,cat_name,price from goods as a left join category as b on a.cat_id=b.id;

　　创建视图：create view goods_v2 as select a.id as goods_id,goods_name,cat_name,price from goods as a left join category as b on a.cat_id=b.id;

　　查询视图：select * from goods_v2;

2.2、可以进行权限控制

　　把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据，比如某张表，用户表为例，2个网站搞合作，可以查询对方网站的用户，需要向对方开放用户表的权限，但是呢，又不想开放用户表中的密码字段。

　　再比如一个goods表，两个网站搞合作，可以相互查询对方的商品表，比如进货价格字段不能让对方查看。

　　第一步：创建一个视图，视图中不能包含in_price字段。

　　语法： create view goods_v1 as select id,goods_name,shop_price from goods;

　　第二步：创建一个用户，授予查询权限，只能操作goods_v1表（视图）。

　　语法：grant select on db1.goods_v1 to 'xiaotian'@'%' identified by '1234456';

3、查询视图

　　语法：select * from 视图名 [where 条件]

　　视图和表一样，可以添加where 条件！

4、修改视图

　　alter view view_name as select XXXX;

5、删除视图

　　drop view 视图名称;

6、查看视图结构

　　和表一样的，语法：desc 视图名称;

7、查看所有视图

　　和表一样，语法：show tables;

　　注意：没有show views语句！

 

8、视图与表的关系

　　视图是表的查询结果，自然表的数据改变了，影响视图的结果。

8.1、视图的数据与表的数据一一对应时，可以修改。

　　可见，对视图的修改，也会影响到数据库表的修改。

8.2、视图增删改也会影响表，但是视图并不是总是能增删改的。

　　注意：视图的数据与表的数据一一对应时，可以修改。

8.3、对于视图insert还应注意，视图必须包含表中没有默认值的列。

　　以上对user_v1的操作，就类似于，如下sql语句的操作，原因就是age字段没有默认值，我们insert语句中没有包含age字段：

create table user(
    id int not null,
    name varchar(32) not null,
    age tinyint not null
)engine myisam charset utf8;

insert into user values(5,'xiaoshuang');

　　注意：一般来说，视图只是用来查询的，不应该执行增删改的操作。

9、视图算法（algorithm= merge/temptable/undefined）

　　merge：当引用视图时，引用视图的语句与定义视图的语句合并（默认）。

　　temptable：当引用视图时，根据视图的创建语句建立一个临时表。

　　undefined：未定义，自动让系统帮你选。

　　merge：意味着，视图只是一个语句规则，当查询视图时，把查询视图的语句（比如where那些）与创建时的语句where子句等合并，分析，形成一条 select语句。

　　temptable：是根据创建语句瞬间创建一张临时表，然后查询视图的语句，从该临时表查数据。

　　比如如下视图的算法为merge：

　　# 在创建视图时的语句：where shop_price>1000;

　　# 查询视图时，where shop_price<3000;

　　# 那么查此视图时，真正发生的是where (select where) and (view where)

　　# where shop_price <3000 and shop_price >1000;

　　# 分析出最终语句还是去查goods表

二、SQL编程

1、变量声明

1.1、会话变量

　　定义形式：set @变量名 = 值；

　　说明：

　　1，第一次给其赋值，就算定义了。

　　2，它可以在编程环境和非编程环境中使用。

　　3，使用的任何场合也都带该"@"符号。

1.2、普通变量

　　定义形式：declare 变量名 类型 【default 默认值】；

　　说明：

　　1、它必须先声明（即定义），此时也可以赋值。

　　2、赋值跟会话变量一样： set 变量名 = 值。

　　3、它只能在编程环境中使用！！！

　　说明：什么是编程环境？

　　存储过程，函数，触发器就是编程环境。

1.3、变量赋值形式

　　语法一：

　　　　set 变量名 = 表达式;                 # 此语法中的变量必须先使用declare声明

　　语法二：

　　　　set @变量名=表达式;            # 此方式可以无需declare语法声明，而是直接赋值。

　　语法三：

　　　select @变量名:=表达式;           # 此语句会给该变量赋值，同时还会作为一个select语句输出'结果集'。

　　语法四：

　　　　select 表达式 into @变量名;          # 此语句虽然看起来是select语句，但其实并不输出'结果集'，而是给变量赋值。

 

2、运算符

2.1、算术运算符

　　+、-、*、/、%

　　注意：mysql没有++和—运算符

2.2、关系运算符

　　>、>=、<、<=、=（等于）、<>（不等于）、!=(不等于)

2.3、逻辑运算符

　　and（与）、or（或）、not（非）

3、语句块包含符

　　所谓语句块包含符，在js以及绝大部分的其他语言中，都是大括号：{}

　　它用在很多场合：if，switch，for，function

　　而mysql编程中的语句块包含符是：

4、if判断

　　MySQL支持两种判断，第一个是if判断，第二个 case判断。

　　单分支

if 条件 then
    // 代码
end if;

　　双分支

if 条件 then
    // 代码1
else
    // 代码2
end if;

　　多分支

if 条件 then
    // 代码1
elseif 条件 then
    // 代码2
else
    // 代码3
end if;

　　案例：接收4个数字，如果输入1则输出春天，2=》夏天，3=》秋天，4 =》冬天，其他数字=》出错

　　我们使用存储过程来体验if语句的用法，

　　create procedure 存储过程名(参数,参数,…)

　　begin

    　　// 代码

　　end

　　注意：通常情况下，"；"表示SQL语句结束，同时向服务器提交并执行。但是存储过程中有很多SQL语句，每一句都要以分号隔开，这时候我们就需要使用其他符号来代替向服务器提交的命令。通过delimiter命令更改语句结束符。

　　具体的语句如下：

　　调用语法：call 存储过程的名称(参数)

5、case判断

case 变量
when 值 then 语句;
when 值 then 语句;
else 语句;
end case ;

　　案例：接收4个数字，如果输入1则输出春天，2=》夏天，3=》秋天，4 =》冬天，其他数字=》出错

　　具体语法：

　　调用：

 

6、循环

　　MySQL支持的循环有loop、while、repeat循环。

6.1、loop循环

标签名:loop
    leave 标签名;       -- 退出循环
end loop;

6.2、while循环

[标签:]while 条件 do
    // 代码
end while;

6.3、repeat循环

repeat
    // 代码
until 条件 end repeat;

三、存储过程

1、概念

　　存储过程(procedure)：类似于函数，就是把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现。

　　在封装的语句体里面，可以用if/else，case，while等控制结构，可以进行sql编程。

　　查看现有的存储过程：

　　show procedure status

2、存储过程的优点

　　存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

（1）存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般SQL语句每执行一次就编译一次，所以使用存储过程可提高数据库执行速度。

（2）当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时)，可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。

（3）存储过程可以重复使用，可减少数据库开发人员的工作量。

（4）安全性高，可设定只有某些用户才具有对指定存储过程的使用权。

3、创建存储过程

create procedure 存储过程名(参数,参数,…)
begin
    // 代码
end

　　存储过程的参数分为输入参数(in)、输出参数(out)、输入输出参数(inout)，默认是输入参数。

　　如果存储过程中就一条语句，begin和end是可以省略的。

　　说明：

　　（1）存储过程中，可有各种编程元素：变量，流程控制，函数调用；

　　（2）还可以有：增删改查等各种mysql语句；

　　（3）其中select（或show，或desc）会作为存储过程执行后的"结果集"返回；

　　（4）形参可以设定数据的"进出方向"；

　　案例一：查询一个表里面某些语句

　　调用结果：

 

　　案例二（体会参数）：输入一个字符串，如果等于h则取出价格大于1000的商品，输入其他的值，则输出小于1000的商品。

　　调用结果：

4、调用存储过程

　　语法：call 存储过程名称()

　　mysql_query("call 存储过程名称()")

5、删除存储过程

　　语法：drop procedure [if exists] 存储过程名

6、创建复杂的存储过程

　　案例一：体会循环，计算1到n的和

　　案例二：带输出参数的存储过程

　　案例三：带有输入输出参数的存储过程

 

7、declare声明局部变量

　　在编程环境中使用。

　　用户变量只要在前面加一个@符即可：

　　set @name='李白';

　　select @name;

　　通俗理解术语之间的区别：

　　　　用户定义的变量就叫用户变量。这样理解的话，会话变量和全局变量都可以是用户定义的变量。只是他们是对当前客户端生效还是对所有客户端生效的区别了。所以，用户变量包括了会话变量和全局变量。

　　　　局部变量与用户变量的区分在于两点：

　　　　　　1）用户变量是以”@”开头的。局部变量没有这个符号。

　　　　　　2）定义变量不同。用户变量使用set语句，局部变量使用declare语句定义。

　　　　　　3）作用范围。局部变量只在begin-end语句块之间有效。在begin-end语句块运行完之后，局部变量就消失了。

　　变量包括局部变量和用户变量。用户变量包括会话变量和全局变量。

8、系统变量

　　MySQL启动的时候就存在的变量，以@@开头的都是系统变量。

四、函数

1、自定义函数

　　它跟js中的函数几乎一样：需要先定义，然后调用（使用）。

　　只是规定，这个函数，必须要返回数据——即要有返回值。

1.1、定义语法

create function 函数名(参数 参数的类型) returns 返回值类型
begin
    // 代码
end

　　说明：

　　（1）函数内部可以有各种编程语言的元素：变量，流程控制，函数调用；

　　（2）函数内部可以有增删改等语句！

　　（3）但：函数内部不可以有select（或show或desc）这种返回结果集的语句！

1.2、调用

　　跟系统函数调用一样：任何需要数据的位置，都可以调用该函数。

　　案例一：返回两个数的和

　　案例二：定义一个函数，返回1到n的和

　　注意：创建的函数，是隶属于数据库的，只能在创建函数的数据库中使用。

2、系统函数

2.1、数字类

mysql> select rand();        -- 返回0到1间的随机数
mysql>select * from goods order by rand() limit 2;        -- 随机取出2件商品
mysql>select floor(3.9)；        -- 输出3  地板数
mysql>select ceil(3.1)；         -- 输出4  天花板数
mysql>select round(3.5)；        -- 输出4  四舍五入

2.2、大小写转换

mysql> select ucase('I am a girl!')      -- 转成大写
mysql> select lcase('I am a girl!')      -- 转成小写

2.3、截取字符串

mysql> select left('abcde',3)            -- 从左边截取
mysql> select right('abcde',3)           -- 从右边截取
mysql> select substring('abcde',2,3)     -- 从第二个位置开始，截取3个，注意：位置从1开始
mysql> select concat(10,':锄禾日当午')     -- 字符串相连

mysql> select coalesce(null,123);
coalesce(str1,str2)：   -- 如果第str1为null，就显示str2
mysql> select stuname,stusex,coalesce(writtenexam,'缺考'), coalesce(labexam,'缺考') from stuinfo natural left join stumarks
mysql> select length('锄禾日当午') -- 输出10 显示字节的个数
mysql> select char_length('锄禾日当午') -- 输出5 显示字符的个数
mysql> select length(trim(' abc ')) -- trim用来去字符串两边空格
mysql> select replace('abc','bc','pache')-- 将bc替换成pache

2.4、时间类

mysql> select unix_timestamp()    -- 时间戳

mysql> select from_unixtime(unix_timestamp())    -- 将时间戳转成日期格式

mysql> select curdate()   -- 返回今天的时间日期

mysql> select now()      -- 取出当前时间

mysql> select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now())        -- 年月日时分秒

mysql>select datediff(now(),'1997-7-1')    -- 两个日期相距多少天

mysql> select concat(10,if(10%2=0,'偶数','奇数'))    -- if(表达式,值1,值2)：类似于三元运算符

　　案例一：比如一个电影网站，求出今天添加的电影；在添加电影时，有一个添加的时间戳。

　　思路：

　　curdate()求出今天的日期

　　把添加的时间戳，转换成日期

　　案例二：比如一个电影网站，求出昨天添加的电影；在添加电影时，有一个添加的时间戳。

　　思路：把添加的时间戳，转换成日期，和昨天的日期比较。

　　问题：如何求出昨天的日期？

　　date_sub和date_add函数

　　基本用法：date_sub(时间日期时间,interval 数字 时间单位)

　　说明：

　　（1）时间单位：可以是year month day hour minute second

　　（2）数字：可以是正数和负数

　　比如：取出昨天的日期

select date_sub(curdate(),interval 1 day)
或
select date_add(curdate(),interval -1 day)

　　比如：取出前天的日期

select date_sub(curdate(),interval 2 day)
或
select date_add(curdate(),interval -2 day)

　　比如：取出后天的日期

select date_sub(curdate(),interval -1 day)
或
select date_add(curdate(),interval 1 day)

　　回归案例：比如一个电影网站，求出9天前添加的电影；在添加电影时，有一个添加的时间戳。

五、触发器

1、简介

（1）触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。

（2）触发器必须定义在特定的表上。

（3）自动执行，不能直接调用。

　　作用：监视某种情况并触发某种操作。

　　触发器的思路：

　　监视it_order表，如果it_order表里面有增删改的操作，则自动触发it_goods里面里面增删改的操作。

　　比如新添加一个订单，则it_goods表，就自动减少对应商品的库存。

　　比如取消一个订单，则it_goods表，就自动增加对应商品的库存减少的库存。

2、触发器四要素

3、创建触发器

　　语法：

create trigger trigger_name
after/before insert/update/delete on 表名
for each row
begin
sql语句：（触发的语句一句或多句）
end

　　案例一：第一个触发器，购买一个馒头，减少1个库存

　　分析：

　　　　监视的地点： it_order表。

　　　　监视的事件： it_order表的insert操作。

　　　　触发的时间： it_order表的insert之后。

　　　　触发的事件：it_goods表减少库存的操作。

　　测试效果如下：

　　上面我们创建的触发器t1是有问题，我们购买任何商品都是减少馒头的库存。

　　案例二：购买商品，减少对应库存

create trigger t2
after insert on it_order
for each row
begin
update it_goods set goods_number=goods_number-new.much where id=new.goods_id;
end$　

　　注意：如果在触发器中引用行的值。

　　对于insert 而言，新增的行用new来表示，行中的每一列的值，用 new.列名 来表示。

　　测试效果如下: