本文共 9683 字,大约阅读时间需要 32 分钟。
视图介绍
优点
限制
1.包含以下关键字的SQL语句: 聚合函数(sum\min\max\count等)、distinct、group by、having、union或union all 2.常量视图、join、from 一个不能更新的视图 3.where子句的子查询引用from子句中的表 4.使用了临时表 |
环境准备:
]# cp /etc/passwd /var/lib/mysql-files/
mysql> load data infile "/var/lib/mysql-files/passwd" into table fields terminated by ":" lines terminated by "\n";
创建视图格式
create view 视图名称 [字段名称] as SQL查询;
mysql> create view db9.v1 as select name,uid from db9.user;
mysql> create view db9.v2(a,b,c) as select name,uid,shell from db9.user;
mysql> show tables;
msyql> desc db9.v2; desc db9.v1;
删除视图
drop view 视图;
查看视图
show table status where comment="view"\G;
*************************** 4. row *************************** Name: v2 ............ Comment: VIEW
show create view v2 (视图)\G; #查看创建视图命令
*************************** 1. row ***************************
View: v2 Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `user`.`name` AS `a`,`user`.`uid` AS `b`,`user`.`shell` AS `c` from `user` character_set_client: utf8 collation_connection: utf8_general_ci
1.创建视图时,定义视图别名
2.查询时定义别名
强制覆盖原来的创建新表
- create or replace view 视图名 as select 查询;
mysql> create or replace view v4 as select uid,gid,name from user;
视图算法
限制视图操作(约束)
mysql> create view v7 as select * from user where gid<=500 with local check option; #限制视图v7的表只能修改gid到500内
mysql> select * from v7;
mysql> create view v8 as select * from v7 where gid>=100 with cascaded check option; #限制视图v8的表不能gid小于100且又要满足v7的
mysql> update
介绍
优点
基本使用
mysql> delimiter // 防止写功能代码时还没end就结束了
mysql> create procedure 库 . 名称() begin 功能代码; end
msyql> delimiter ; 创建完了记得改回来功能代码才能执行
mysql> delimiter // mysql> create procedure db9.p1() -> begin -> select count(*) from db9.user where shell="/bin/bash"; -> select count(*) from db9.user where shell="/bin/bash"; -> end -> //Query OK, 0 rows affected (0.00 sec)mysql> delimiter ; mysql> select db,name from mysql.proc where type="procedure" and name="p1"; +-----+------+ | db | name | +-----+------+ | db9 | p1 | +-----+------+mysql> select body from mysql.proc where type="procedure" and name="p1"\G *************************** 1. row *************************** body: begin select count(*) from db9.user where shell="/bin/bash"; select count(*) from db9.user where shell="/bin/bash"; end |
call 库 . 名称();
mysql> call db9.p1() ;
+----------+
| count(*) | +----------+ | 2 | +----------+ +----------+ | count(*) | +----------+ | 2 | +----------+
mysql> desc mysql.proc;
mysql> select db,name from mysql.proc where type="PROCEDURE";
mysql> select db,name,body from mysql.proc where type="procedure" and name="存储过程名";
mysql> select db,name,body from mysql.proc where name="ps_setup_save" and type="procedure"\G ;
drop procedure 库 . 名称();
会话变量:连接[当前会话]断开就失效,
show session variables #查看 | set session
mysql> show session variables ;
mysql> set session sort_buffer_size=4000;
mysql> show session variables like "session sort_buffer_size";
全局变量:服务定的变量[临时修改]
show global varianles like(过滤) "%变量%"; 输出全局变量的值:select @@变量名 ;
mysql> show global variables;
mysql> show global variables like "%hostname%";
mysql> select @@hostname; mysql> show global variables like "%version%"; mysql> select @@version; mysql> show global variables like "%hostname%";
用户变量:自定义变量 [临时修改]
定义:set @变量名 = 值; 输出值:select select @变量,@变量,多个 ;
mysql> set @x = 99; mysql> set @name="bob"; mysql> set @name="bob";
+------+-------+ | @x | @name | +------+-------+ | 99 | bob | +------+-------+
局部变量:只能写在存储过程里定义
declare 变量 类型; set 变量名 = 值; | 输出值: select 变量名;
mysql> delimiter // mysql> create procedure db9.p2() begin -> declare x int; -> declare y int; -> set x = 11; -> set y = 99; -> select x,y; -> end -> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ; mysql> call db9.p2(); +------+------+ | x | y | +------+------+ | 11 | 99 | +------+------+
使用查询结果给变量
select count(name) into @变量名 from 库.表;
mysql> select count(name) into @x from db9.user;
mysql> delimiter //
mysql> create procedure db9.p3() begin declare x int; -> declare y int; -> select count(name) into x from db9.user where gid<=1000; -> select count(name) into y from db9.user where gid>1000; -> select x,y; -> end -> //mysql> delimiter ;
mysql> call db9.p3;
in类型 给存储过程传值 格式: in 变量名 数值类型
out类型 接收存储过程处理结果 格式:out 变量名 数值类型
inout类型 2者结合 格式:inout 变量名 数值类型
delimiter // create procedure 库 . 存储过程名(参数类型) begin 执行代码; end // delimiter ; call 库 . 存储过程名(给值) |
mysql> delimiter //
mysql> create procedure db9.p9(in names char(10)) -> begin -> select * from db9.user where name=names; -> end -> //
mysql> delimiter ;
mysql> call db9.p9("root");
mysql> delimiter // mysql> create procedure db9.p10(in x int, in y int, out z int) -> begin -> set z = x + y; -> select z; -> end -> // mysql> delimiter ; mysql> call db9.p10(20,30,@q); @q用户变量,随便写 +------+ | z | +------+ | 50 | +------+ |
mysql> delimiter // mysql> create procedure db9.p11(in uidnum int, in shells char(50), out x int) -> begin -> declare i int; -> declare j int; -> select count(shell) into j from db9.user where shell = shells; -> select count(uid) into i from db9.user where uid <= uidnum; -> set x = i + j; #使用局部变量 -> select x; -> end -> //
mysql> delimiter ;mysql> call db9.p11(11,"/bin/bash",@p);
mysql> select @p; #输出用户变量
mysql> delimiter // mysql> create procedure db9.p12(inout x char(30)) -> begin -> select name from db9.user where name=x; ->select count(*) into x from db9.user; ->select x; ->end ->// mysql>delimiter ; mysql> set @i="root"; #定义用户变量给参数 mysql> call db9.p12(@i); mysql> select @i; |
mysql> delimiter //
mysql> create procedure db9.p4()
begin declare x int; declare y int; declare z int;
select count(name) into x from db9.user where gid <=1000 ;
select count(name) into y from db9.user where gid > 1000 ;
set z=x+y; select x, y, z;
end
//
mysql> delimiter ;mysql> call db9.p4();
+------+------+------+ | x | y | z | +------+------+------+ | 41 | 1 | 42 | +------+------+------+
数值比较符号 = 、> 、>=、 <、 <=、 !=、 between..and
字符比较符号
空、非空 is null 、 is not null
范围内比较 in..、not in..
逻辑比较 or、and
正则匹配 regexp
模糊匹配 like
if 条件测试 then 代码... ..... end if; | if 条件测试 then 代码1.. ..0 else 代码2 .. end if; |
mysql> alter table db9.user add id int primary key auto_increment first;
mysql> delimiter //mysql> create procedure db9.p13( in baba int) -> begin -> if baba > 10 then -> select * from db9.user where id >= baba; -> else -> select * from db9.user where id <= baba; -> end if; -> end -> //
mysql> delimiter ;mysql> call db9.p13(20);
while 条件 do
循环体
...
end while;
mysql> delimiter // mysql> create procedure db9.p15 () -> begin -> declare x int; -> set x = 1; -> while x <= 10 do #条件不成立则退出循环 -> select x; -> set x = x + 1; -> end while; -> end -> //
mysql> delimiter ; mysql> call db9.p15();
loop
循环体
...
end loop;
delimiter //
create procedure db9.p16()
begin
loop
select * from db9.user limit 1;
end loop;
end
//
delimiter ;
repeat
循环体
...
until 条件判断
end repeat;
mysql> delimiter // mysql> create procedure db9.p17() -> begin -> declare x int; -> set x = 10; -> repeat -> select x; -> set x = x + 1; -> until x > 20 #先执行命令序列,再条件判断,为真则结束循环 -> end repeat; -> end -> // mysql> delimiter ;mysql> call db9.p17(); |
leave 结束循环(跳出循环)
delimiter //
create procedure db9.p19() begin abc:while 1 = 1 do #定义标签名为abc select user from mysql.user where user ="root"; leave abc; #结束标签abc的循环 end while; end // delimiter ; call db9.p19();iterate 结束本次循环,并开始下次循环
delimiter //
create procedure db9.p21()begin declare x int; set x = 1; #定义局部变量plj:repeat #定义标签名plj if x = 3 or x = 5 then set x = x + 1; #先执行命令序列再做条件判断,如果循环x等于3,5则+1并结束本茨循环不select输出;iterate plj;else #以上不成立就select继续循环并+1 select x;end if; #结束if判断 set x = x + 1;until x > 10 #条件大于10结束循环end repeat;end
//
delimiter ;
例子:
mysql>delimiter //mysql>create procedure db9.p22(in line_num int) begin if line_num is not null then select user,host from mysql.user limit line_num; else select user,host from mysql.user limit 1; end if; end // mysql>delimiter ;
mysql> call db9.p22(4);mysql> call db9.p22(@z); #@z变量为空值
转载地址:http://seiqi.baihongyu.com/