创建视图

                                                               ql-day09      创建视图

把系统用户信息存储到db9库下的user表里，并在所有列前添加行号字段id(要求自动增长)

一、mysql视图

1.1什么是mysql视图

1.2视图优点

1.3视图的基本使用

创建视图

查看视图

使用视图

删除视图

1)创建视图

mysql>create table v1 select name,uid,gid from user limit 3;

mysql>create table v2 select name,uid,homedir,shell from user limit 5;

       show tables;

       create or replace view v1

       as select

       a.name as aname,     //设置别名

       b.name as bname,

       a.uid as auid,

       b.uid as buid

       from v1 a left join v2 b on a.uid=b.uid;

2)使用视图

•查询记录

–Select字段名列表

from视图名 where 条件;

•插入记录

–Insert into视图名(字段名列表)values(字段值列表);

•更新记录

–Update视图名set字段名=值where条件;

•删除记录

–Delete from视图名where条件;

注意:对视图操作即是对基本操作,反之亦然!!!

3)删除视图

•语法格式

–drop view视图名;

mysql>drop view t11;

² 设置字段别名

•命令格式

–视图中的字段名不可以重复所以要定义别名

Create view视图名

as

select表别名.源字段名as字段别名

from源表名表别名left join源表名表别名

on条件;

u 关联查询建的视图默认不允许修改视图字段的值

mysql>create view v2

       as

       select a.name as aname,b.name as bname,a.uid as auid,b.uid as      buid from user a left join info b on a.uid=b.uid;

例子：

mysql>create table a select username,uid,gid from usertab limit 3;

mysql>create table b select username,uid,homedir,shell from usertab       limit 5;

2）创建视图：

mysql>create view v1 as select a.*,b.homedir from a left join b on   a.username=b.username and a.uid=b.uid;

mysql>create view v2

       as

        select a.username as aname,b.username as bname,a.uid as   auid,b.uid as buid from a left join b on a.uid=b.uid;

mysql>create view v3 as select a1.username as aname,b1.username as   bname,a1.uid as auid,b1.uid as buid from a a1 left join b b1 on a1.uid=b1.uid;

•语法格式

–创建时,若视图已存在,会替换已有的视图

–Create or replace view视图名as select查询

mysql>create or replace view v2 as select*from t1;

//存在，没有提示，不存在就建立

3)更新字段值

mysql>update v4 set uid=4 where username="root";

WITH CHECK OPTION

•当视图是根据另一个视图定义时,对视图更新/删除/插

–LOCAL和CASCADED关键字决定了检查的范围。

–LOCAL仅检查当前视图的限制。

–CASCADED同时要满足基表的限制。

格式：

l create table a select*from user where uid<10

And uid>5;

//建值。满足当前5<值<10

l mysql>create view v1(默认值)

as

select*from a where uid<10

with check option;

    mysql>create view v2

as

select*from v1

where uid>=5 with local check option;

1)验证限制

l create table a select * from user where uid<10;

select*from a;//建值。满足当前<10

v create view va1

as

select*from a

where uid<8 with check option;//建值。满足当前<8

v create view va2

as

select*from va1

where uid>=5

with local check option;//仅检查当前视图的限制。8>值>=5

v create view va3

as

select*from va1

where uid>=5

with cascaded check option;//同时要满足基表的限制。8>值>=5

对创建的试图va1 va2 va3插入或更新记录的操作。验证限制。

v mysql>show create view v2\G；

---

 

存储过程介绍ql-day9补充篇

•什么存储过程

–数据库中保存的一系列sql命令的集合

–编写存储过程时,可以使用变量、条件判断、流程控

制等

–存储过程,就是MySQL中的脚本

存储过程优点

•存储过程优点

–提高性能

–可减轻网络负担

–可以防止对表的直接访问

–避免重复的sql操作

mysql>create procedure sy() begin select username from db3.usertab;end////将;改为了//

u 创建存储过程

•语法格式

delimiter//mysql>delimiter//

create procedure名称()

mysql>create procedure sy()

Begin begin

功能代码select username from db3.usertab;

End end

////

u 结束存储过程

delimiter;

名称

delimiter关键字声明当前段分隔符

MySQL默认以“;”为分隔符,没有声明分割符,编译器会把存储过程当成SQL语

句进行处理,则存储过程的编译过程会报错。查看存储过程

查看存储过程

•方法1

–mysql>show procedure status;

•方法2

–mysql>select db,name,type from mysql.proc whe

re name=“存储过程名";

mysql>select db,name,type from mysql.proc where name="say";

u 调用/删除存储过程

•调用存储过程存储过程没有参数时,()可以省略

–Call存储过程名();有参数时,在调用存储过程时,

•删除存储过程必须传参。

–drop procedure存储过程名;

例子：

² 创建名称为say的存储过程，功能查看db9库下user表的前10记录信息。

mysql>delimiter//

mysql>create procedure say()

begin

select*from db9.user where id<=10;

end

//

mysql>delimiter;

² 调用存储过程

mysql>call say;

mysql>call say();查看存储过程

mysql>show procedure status\G;

mysql>select db,name,type from mysql.proc

where name="say2"\G;

mysql>show create procedure say;

mysql>drop procedure say;

² 键say2

mysql>delimiter//

mysql>create procedure say9(

in user char(10))

begin

select username;

select*from db3.usertab where name=user;

end

//

mysql>delimiter;

mysql>call say2;

mysql>call say2("bin");

mysql>call say2("mysql");

mysql>insert into db9.user(name)values("root"),("root");

mysql>call say2("root");

² 定义局部变量say3

delimiter//

create procedure say3()

begin

declare x int default 11;

select x;

select sum(uid)into x from db3.usertab where uid<=100;

select x;

end

//

mysql>call say3;//因为没有参数所以可以不加（）

² 定义局部变量(续1)

mysql>delimiter//

mysql>create procedure say(

in bash char(20),in nologin char(25),out x int,out y int

)

begin

declare z int;

set z=0;

select count(username)into@x from db3.usertab where shell=bash;

select count(username)into@y from db3.usertab where shell=nologin;

set

z=@x+@y;

select z;

mysql>

end

² 定义局部变量(续2)

mysql>delimiter;

mysql>call say("/bin/bash","/sbin/nologin",@x,@y);

delimiter//

create procedure say9()

begin

declare x int(2);

declare nameuser char(10);

select x;

set x=0;

select x;

set nameuser="bob";

select nameuser;

end

//

delimiter;

call say9;

 

存储过程进阶

• 参数类型

•MySQL存储过程,共有三种参数类型IN,OUT,INOUT

Create procedure名称(

类型参数名数据类型,类型参数名数据类型

)

关键字名称描述

in输入参数作用是给存储过程传值,必须在调用存储

过程时赋值,在存储过程中该参数的值不

允许修改;默认类型是in

out输出参数该值可在存储过程内部被改变,并可返回。

inout输入/输出参数调用时指定,并且可被改变和返回

注意:此三中类型的变量在存储过程中调用时不需要加@符号!!!

• 参数类型(续1)

mysql>delimiter//

mysql>create procedure say(in username char(10))

#定义in类型的参数变量username

->begin

->select username;

->select*from user where name=username;

->end

->//

Query OK,0 rows affected(0.00 sec)

mysql>delimiter;

• 参数类型(续2)

mysql>call say(“root”);#调用存储过程时给值。

u 变量类型

•变量的种类:全局变量\会话变量\用户变量\局部变量

名称描述

会话变量会话变量和全局变量叫系统变量使用set命令定义;

全局变量全局变量的修改会影响到整个服务器,但是对会

话变量的修改,只会影响到当前的会话。

用户变量在客户端连接到数据库服务的整个过程中都是有效的。

当当前连接断开后所有用户变量失效。

定义set

@变量名=值;

输出select@变量名;

局部变量存储过程中的begin/end。其有效范围仅限于该

语句块中,语句块执行完毕后,变量失效。

declare专门用来定义局部变量。

注意:局部变量和参数变量调用时变量名前不需要加@

• 变量类型(续1)

mysql>show global variables;//查看全局变量

mysql>show session variables;//查看会话变量

mysql>set session sort_buffer_size=40000;//设置会话变量

mysql>show session variables like“sort_buffer_size”;//查看会话变量

Mysql>show global variables like“%关键字%”;//查看全局变量

mysql>set@y=3;//用户自定义变量,直接赋值

mysql>select max(uid)into@y from user;//使用sql命令查询结果赋值

• 变量类型(续2)

mysql>delimiter//

mysql>create procedure say48()

->begin

->declare x int default 9;//局部变量x

->declare y char(10);

//局部变量y

->set y="jim";

->select x;

->select y;

->end

->//

mysql>delimiter;

mysql>select@;

• 算数运算

•算数运算符号

符号描述例子

+加法运算SET@var1=2+2;4

-减法运算SET@var2=3-2;1

*乘法运算SET@var3=3*2;6

/除法运算SET@var4=10/3;3.333333333

DIV整除运算SET@var5=10 DIV 3;3

%取模SET@var6=10%3;1

mysql>set@z=1+2;select@z;

mysql>set@x=1;set@y=2;set@z=@x*@y;select@z;

mysql>set@x=1;set@y=2;set@z=@x-@y;select@z;

mysql>set@x=1;set@y=2;set@z=@x/@y;select@z;

• 算数运算(续1)

mysql>drop procedure if exists say;

mysql>delimiter//

mysql>create procedure say5(

->in bash char(20),

->in nologin char(25),

->out x int,

->out y int

->)

->begin

->declare z int;

->set z=0;

->select count(username)into@x from db3.usertab where shell=bash;

->select count(username)into@y from db3.usertab where shell=nologin;

->set z=@x+@y;

->select z;

->end

->//

mysql>delimiter;

mysql>call say("/bin/bash","/sbin/nologin",@x,@y);

• 条件判断

•数值的比较

类型用途

=等于集群工作原理

 

>、>=大于、大于或等于

<、<=小于、小于或等于

!=不等于

BETWEEN..AND..在..与..之间

• 条件判断(续1)

•逻辑比较、范围、空、非空、模糊、正则

类型用途

OR、AND、!逻辑或、逻辑与、逻辑非

IN..、NOT IN..在..范围内、不在..范围内

IS NULL字段的值为空

IS NOT NULL字段的值不为空

LIKE模糊匹配

REGEXP正则匹配

² 顺序结构

•当“条件成立”时执行命令序列

•否则,不执行任何操作

if条件测试then

代码......•当“条件成立”时执行代码1

.....•否则,执行代码2

end if;if条件测试then

代码1......

......

else

代码2......

......

end if;

² 顺序结构(续1)

mysql>drop procedure if exists say;//全部清除

mysql>delimiter//

mysql>create procedure say(in x int(1))

begin

if x<=10 then

select * from userdb.user where id<=x;

end if;

end

//

mysql>delimiter;

mysql>call say(1);#条件判断成立

² 顺序结构(续2)

mysql>drop procedure if exists say;

mysql>delimiter//

mysql>create procedure say(in x int(1))

begin

if x is null then

set@x=1;

selec * from userdb.user where id=x;

end if;

if x<=10 then

select * from userdb.user where id<=x;

end if;

end

//

mysql>delimiter;

MySQL>call say(@x);//调用未定义变量x

² 循环结构

•条件式循环

–反复测试条件,

–只要成立就执行命令序列

mysql>delimiter//

mysql>create procedure say()

->begin while条件判断do

->declare i int;循环体

->set i=1;......

->while i<=5 do end while;

->select i;

->set i=i+1;

->end while;

->end

->//

mysql>delimiter;

² 循环结构(续)

•条件式循环

–无循环条件

end loop;loop

mysql>delimiter//循环体

mysql>create procedure say2()......

->begin end loop;

->declare i int;

->set i=1;

->loop

->select i;

->set i=i+1;

->end loop;

->end

->//

mysql>delimiter;

mysql>call say2();#不按ctrl+c结束会一直输出变量i的值

² 循环结构(续2)

•条件式循环

–until条件判断,不成立时结束循

环delimiter//

mysql>create procedure say3()

->begin repeat

->declare i int;循环体

->set i=1;until条件判断

->repeat end repeat;

->select i;until

->set i=i+1;

->until i=6//此处不需要使用;

->end repeat;

->end

->//

mysql>delimiter;

mysql>call say();//测试

 

• 控制语句

•循环结构控制语句,控制循环结构的执行。

–LEAVE标签名//跳出循环

–ITERATE标签名/放弃本次循环,执行下一次循环

mysql>create procedure say()

->begin

->declare i int;

->set i=1;

->loab1:loop//定义标签名为loab1

->select i;

->set i=i+1;

->if i=3 then#i值是3时结束本次循环

->iterate loab1;

->end if;

->if i=7 then#i值是7时结束循环

->leave loab1;

->end if;

->end loop;

->end

->//

mysql>delimiter;

mysql>call say();//测试