0 视图

0.1 视图定义

视图是有查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影。
同一张表可以创建多个视图
创建视图语法：

create view 视图名称 as select 语句

说明：

  1. 视图名跟表名是一个级别的名字，隶属于数据库；
  2. 该语句的含义可以理解为：就是将该select命名为该名字（视图名）
  3. 该视图也可以设定自己的字段名，而不是select语句本身的字段名（通常不设置）
  4. 视图的使用几乎和表一样（只不过一般都用做查询，很少增删改，增删改在后面说明）

0.2 视图的作用

  1. 可以简化查询
    案例：1.查询平均价格前3的栏目
    以前的SQL语句
    1
    select cat_id,avg(shop_price) as pj from ecs_goods group by cat_id order by pj limit 3;

应用视图的
1) 创建一个视图

1
create view ecs_goods_v1 as select cat_id,avg(shop_price) as pj from ecs_goods group by cat_id;

2) 查询平均价格前3只需要查询视图即可

1
selcet * from ecs_goods_v1 order by pj desc limit 3;

案例：2.查询出商品表，以及所在的栏目名称
以前的sql语句

1
2
select goods_id,goods_name,b.cat_name,shop_price from ecs_goods a 
left join ecs_category b on a.vat_id=b.cat_id

1) 创建视图

1
2
3
create view ecs_goods_v2 as 
select goods_id,goods_name,b.cat_name,shop_price from ecs_goods a 
left join ecs_category b on a.vat_id=b.cat_id

2) 查询视图

1
selcet * from ecs_goods_v2;

  1. 可以进行权限控制
    把表的权限封闭，但是开放相应的视图权限，视图里只开放部分数据，比如用户表为例，两个网站搞合作，可以查询对方网站的用户，需要向对方开放用户表的权限，但是又不想开放用户表中的密码字段。
    再比如一个goods表，两个网站搞合作，可以互相查询对方的商品列表，比如进货价格字段不能让对方看到
    案例
    1) 创建一个视图，视图中不能包含进价字段（in_price）
    1
    creat view goods_v1 as select id,goods_name,shop_price from goods;

2) 创建一个用户，授予查询权限，只能操作goods_v1表（视图）

1
grant select on php.goods_v1 to 'xiaoming'@'%' identified by '123456';

3) 这样就把新建的用户给合作方

0.3 视图操作

  1. 查询视图
    语法：

select * from 视图名 [where 条件]

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

  1. 修改视图

alter view 视图名称 as select ….

  1. 删除视图

drop view 视图名称

  1. 查看视图结构
    和表一样，语法：

desc 视图名称

  1. 查看所有视图
    和表一样，语法：

show tables;

注意：没有show views 语句

  1. 视图和表得关系
    表 >>——>> 视图
    表改变了视图就改变了
    视图 >>——>> 表
    修改：
    1) 如果没有计算的来的值（avg），视图改变可以影响表
    增加：
    2) 如果没有不能为空而又刚好没被视图收录的字段，视图创建可以影响创建

  2. 视图算法
    algorithm = merge/temptable/undefined

名称 算法 创建方式
merge 当引用视图时，引用视图的语句与定义视图的语句合并（默认） 意味着视图只是一个语句规则，当查询视图时，吧查询视图的语句与创建时语句where子句等合并，分析，形成一条select语句
temptable 当引用视图时，根据视图语句创建一张临时表 根据创建语句，瞬间创建一张临时表，然后查询视图的语句，从该临时表查询数据
undefined 未定义，让系统自动选择

1 SQL编程

1.1 声明变量

  1. 会话变量
    定义形式

    set @变量名 = 值;

说明：
1) 和js类似，第一次给其赋值，就算定义了
2) 它可以在变成环境和非编程环境中使用
3) 使用的任何场合都该带“@”符号

  1. 普通变量
    定义形式：

    declare 变量名 类型 【default 默认值】;

说明：
1) 他必须先声明（即定义），此时也可以赋值；
2) 赋值跟会话变量一样set 变量名 = 值；
3) 它只能在编程环境中使用！！！
(编程环境指的是：存储过程，函数，触发器)

  1. 变量赋值形式
    1) 语法1：

    set 变量名 = 表达式;

此语法中的变量必须先使用declare声明

2) 语法2:

set @变量名 = 表达式

此方式可以无需declare语法声明，而是直接赋值，类似js定义变量并赋值；

3) 语法3：

select @变量名:= 表达式；

此语句会给变量赋值，同时还会作为一个select语句输出结果

4) 语法4：

select 表达式 into @变量名;

此语句虽然看起来是select语句，但是其实并不输出结果集，而是给变量赋值

1.2运算符

  1. 算术运算符

+,-,*,/,%

注意musql没有++和--

  1. 关系运算符

< <= = <> !=

  1. 逻辑运算符

and or not

1.3语句包含模块

所谓语句包含符，在js中，以及大部分其他语言中，大都是{}
它用在很多场合if，switch，for，function
而mysql编程包含的语句符是：

begin【什么】 end【什么】

1.4条件判断

MySQL支持两种判断if判断和case判断

  1. if
    单分支语法：

if 条件 then
//代码
end if;

双分支

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

多分枝

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

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

delimiter 符号

案例：接收4个数字，如果输入1=春天，2=夏天，3=秋天，4=冬天，其他=出错
先修改;符号

delimiter $

1
2
3
4
5
6
7
8
9
10
11
12
13
14
create procedure p1 (n int)
begin 
if n=1 then
select '春天' as '季节';
elseif n=2 then
select '夏天' as '季节';
elseif n=3 then
select '秋天' as '季节';
elseif n=4 then
select '冬天' as '季节';
else
select '错误' as '季节';
end if;
end$
  1. case判断

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

上面的例子我们改变如下

1
2
3
4
5
6
7
8
9
10
create procedure p2 (n int)
begin 
case n
when 1 then select '春天' as '季节';
when 2 then select '夏天' as '季节';
when 3 then select '秋天' as '季节';
when 4 then select '冬天' as '季节';
else select '错误' as '季节';
end case;
end$

1.5循环

Mysql支持的循环有loop，while，repeat循环

  1. loop循环

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

案例：循环1到n的和

1
2
3
4
5
6
7
8
9
10
11
12
13
create procedure p3 (n int)
begin 
    declare i int default 1;
    declare s int default 0;
    aa:loop
        if i>n then
        leave aa;
        end if;
        set s=s+i;
        set i=i+1;
    end loop;
    select s;
end$
  1. while循环

标签名 while 条件 do
// 代码
end while

1
2
3
4
5
6
7
8
9
10
create procedure p4 (n int)
begin 
    declare i int default 1;
    declare s int default 0;
    while i<=n do
        set s=s+i;
        set i=i+1;
    end while;
    select s;
end$
  1. repeat循环

repeat
// 代码
until 条件 end repeat;

1
2
3
4
5
6
7
8
9
10
create procedure p5 (n int)
begin 
    declare i int default 1;
    declare s int default 0;
    repeat
        set s=s+i;
        set i=i+1;
    until i>n end repeat;
    select s;
end$

2 存储过程

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

2.1 好处

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

  2. 当对数据库进行复杂操作时（如对多个表进行增删改时），可以将此复杂操作用存储过程封装起来，与数据库提供的事务结合一起使用

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

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

2.2 存储过程操作

  1. 创建存储过程
1
2
3
4
create procedure 存储过程名称(参数1,参数2,...)
begin 
// 代码
end

存储过程的参数部分为输入参数（in），输出参数（out），输入输出参数（inout），默认是输入参数。
如果存储过程中就一条语句，begin和end是可以省略的。

说明：
1) 存储过程中，可有各种变成元素：（变量，流程控制，函数调用）；
2) 还可以有：增删改查等各种sql语句
3) 其中select（或show，或desc）会作为存储过程执行后的“结果集”返回；
4)形参可以设定数据的“进出方向”

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

1
2
3
4
create procedure p10()
begin
select * from ecs_goods;
end;

案例2：输入一个字符串，如果是h输出价格大于1000商品，其他输出小于1000商品

1
2
3
4
5
6
7
8
9
10
create procedure p11(str char(1))
begin
if str = 'h' then
select * from ecs_goods where price>1000;
else
select * from ecs_goods where price<=1000;
end if;
end;

call p11('h');
  1. 调用存储过程
    语法：

call 存储过程()

  1. 删除存过程

语法:

drop procedure [if exists] 存储过程名

  1. 创建复杂存储过程

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

1
2
3
4
5
6
create procedure p12(in n int,out res int)
begin 
set res = n*n;
end;

set @res=0;call p12(100,@res);selcet @res;

案例2:带有输入输出参数的存储过程

1
2
3
4
5
6
create procedure p13(inout n int)
begin 
set n = n*n;
end;

set @a = 20; call p13(@a);select @a;

2.3 变量

  1. 系统变量

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

查询mysql版本：

select @@version;

  1. 用户变量
    用户变量只要在前面加一个@就可以
    1
    2
    set @name='linjiad';
    select @name;

3 函数

和js中的函数几乎一样：需要先定义，然后调用。只是规定这个函数必须需要一个返回值;

3.1 定义函数

语法：

1
2
3
4
5
6
create function 函数名(参数) 
returns 返回值类型
begin
// 代码
return xx值;
end;

说明：
1) 函数内部可以有各种编程语言元素：变量，流程控制，函数调用；
2) 函数内部可以有增删改等语句
3)但：函数内部不可以有查（select或show，或desc）这种返回结果集的语句

3.2 调用

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

案例1：返回两个数的和

1
2
3
4
5
6
create function he(num1 int,num2 int) return int
begin
return num1+num2;
end;

select he(100,120);

案例2: 返回1到n的和

1
2
3
4
5
6
7
8
9
10
11
12
create function hee(n int) return int
begin
declare i int default 1;
declare s int default 0;
while i <=n do
set s= s + i;
set i= i + 1;
end while;
retuen s;
end;

select he(100,120);

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

3.3 SQL内部函数

  1. 数字类

1) select rand(); //返回0到1之间的随机数
2) select * from it_goods //随机取出两个商品
3) select floor(3.9) // 向上取整 4
4) select ceil(3.1) // 向下取整 3
5) select round(3.5) // 四舍五入 4

  1. 大小写转换

1) select ucase(‘linjiad’) //转换成大写
2) select icase(‘linjiad’)//转换成小写

  1. 字符串截取

1) select left(‘abcde’,3) //从左边截取
2) select right(‘abcde’,3) //从右边截取
3) select substring(‘abcde’,2,3) //从第二个位置开始，截取3个
4) select concat(‘abc’,’:de’) //字符串项链 10:abcde
5) coalesce(str1,str2) //返回参数中的第一个非空表达式
select stuname.stusex,coalesce(writtenexam,’缺考’),coalesce(labexam,’缺考’) from stuinfo
6) select length(‘锄禾日当午’) //显示字节个数 10
7) select char_length(‘锄禾日当午’) //显示字符个数 5
8) select length(trim(‘ abc ‘)) //trim 用来去除字符串两边空格
9) select replace(‘abc’,’bc’,’pache’) //将bc替换成pache

  1. 时间类

1) select unix_timestamp() // 时间戳
2) select from unixtime(unix_timestamp()) //将时间戳转换成日期格式
select from_unixtime(unix_timestamp(),’%Y-%m-%d’);
3) curdate (); //返回今天得日期
select curdate(); // 2018-12-07
4) select now();// 去除当前时间
select now();// 2018-12-07 15:02:59
5) select year(now()) 年, mouth(now()) 月,day(now()) 天,hour(now()) 小时。minute(now()) 分,second(now()) 秒
6) select datediff(now(),’1997-7-1’)// 两个日期相聚多少天
7) if(表达式,值1,值2) //类似三元云算法
select concat(10,if(10%2=0,’偶数’,’奇数’))
8) date_sub 和date_add //时间加减
date_sub(时间日期时间,interval 数字 时间单位)
①：时间单位：可以是year month day hour minute second
②数字：可以是正数和负数（具体看案例2）

  1. 案例
    案例1： 查询今天得电影
    ①curdate()求出今天的日期
    ②把添加的时间戳，转换成日期
    select title from dede_archives where curdate()=from_unixtime(senddate,’%Y-%m-%d’);

案例2： 求昨天添加的电影
① 去除昨天日期
select date_sub(curdate(),interval 1 day)
或者
select date_add(curdate(),interval -1 day)

4 触发器

4.1 简介

  1. 触发器是一个特殊的存储过程，它是Mysql在insert，update，delete的时候自动执行的代码块。
  2. 触发器必须定义在特定表上
  3. 自动执行，不能直接调用
    作用：监视某种情况并触发某种操作
    触发器的思路：
    监视X表，如果X表里面有增删改的操作，则自动触发Y里面增删改操作。
    比如用户表增加新用户，相应的订单表增加该用户的信息。

目前mysql不支持多个具有同一个动作，同一时间，同一事件，同一地点，的触发器。

4.2 创建触发器

  1. 监视地点（table）
  2. 监视事件（insert/update/delete）
  3. 触发时间（after/before）
  4. 触发事件（insert/update/delete）

语法：

1
2
3
4
5
6
create trigger 触发器名
after/before inster/update/delete on 表名
for each row 
begin 
// sql语句及代码（触发的语句可以是一句或多句）
end

4.3 案例

  1. 案例1：第一个触发器，购买一个订单表减少一个

1) 监视地点 it_order表
2) 监视事件 it_order表的inset操作
3) 触发时间 it_order表的inset之后
4) 触发事件 it_goods表减少库存的操作

1
2
3
4
5
6
create trigger t1
after inster on it_order
for each row 
begin 
update it_goods set goods_number=goods_number - 1 where id = 2;
end

这个t1是有问题的，我们购买任何商品都是减少id=2的对应库存

  1. 案例2: 购买商品，减少对应库存
    如何在触发器中引用行的值？
    对于insert而言，新增的行用new来表示，行中的每一列的值，用new.列名来表示。

    1
    2
    3
    4
    5
    6
    create trigger t1
    after inster on it_order
    for each row 
    begin 
    update it_goods set goods_number=goods_number - new.much where id = new.goods_id;
    end

  2. 案例3：取消订单时，减掉的库存要添加回来

删除行我们用old来表示，如果要引用里面的数据，则使用old.列名来表示

1) 监视地点 it_order表
2) 监视事件 it_order表的delete操作
3) 触发时间 it_order表的delete之后
4) 触发事件 it_goods表减少库存恢复过来

1
2
3
4
5
6
create trigger t3
after delete on it_order
for each row 
begin 
update it_goods set goods_number=goods_number + old.much where id = old.goods_id;
end
  1. 案例4：修改订单时，库存也要做对应修改（可以修改购买数量，也可以修改类型）
    如果是修改操作要引用it_order里面的值
    修改前的数据用old表示，old.列名引用被修改之前的值
    修改后的数据用new表示，new.列名引用被修改之后的值

1) 监视地点 it_order表
2) 监视事件 it_order表的update操作
3) 触发时间 it_order表的update之后
4) 触发事件 it_goods表要修改对应的库存

1
2
3
4
5
6
7
create trigger t4
after update on it_order
for each row 
begin 
update it_goods set goods_number=goods_number + old.much where id = old.goods_id;
update it_goods set goods_number=goods_number - new.much where id = new.goods_id;
end

4.4 其他操作

  1. 删除触发器

drop trigger 触发器名称

  1. 查看触发器

show triggers

4.5 befor和after

after是先完成数据的增删改，再出发，触发器中的语句晚于监视的增删改，无法影响前面的增删改动作
类似于自助餐，先付钱，在吃饭，无法影响吃多少

befor是先完成出发，再增删改，触发的语句先于监视的增删改发生，我们有机会判断修改即将发生的操作

案例：对于已下的订单，进行判断，如果订单数量>5，就认为是恶意订单，强制吧所定的商品数量改成5

1) 监视地点 it_order表
2) 监视事件 it_order表的insert操作
3) 触发时间 it_order表的insert之前
4) 触发事件 如果购买数量＞5就把购买数量改成5

1
2
3
4
5
6
7
8
create trigger t5
after insert on it_order
for each row 
begin 
if new.much>5 then
new.much=5;
end if;
end

最后更新： 2018年12月08日 10:46

原始链接： http://linjiad.github.io/2018/12/04/MySQL高级/

赏
× 请我吃糖~
打赏二维码