mysql视图与存储

mysql 视图 view
xxx.frm（表结构） 视图表 （当数据库目录只有一个这个表）
表的存储引擎是memory
create view b as select * from a （创建视图表b引用a表）
视图介绍
虚拟表
内容与真实的表相似，有字段有记录
视图并不在数据库中以存储的数据形式存在
行和列的数据来自定义视图时查询所引用的基表，并且在具体引用视图时动态生成
更新视图的数据，视图的数据也会跟着改变

视图优点
简单
用户不需要关心视图中数据如何查询获取
视图中数据已经是过滤好的符合条件的结果集
安全
用户只能看到视图中数据
数据独立
一旦视图结构确定，可以屏蔽表结构对用户的影响

视图使用限制
不能再视图上创建索引
在视图的from 字句中不能使用子查询
以下情形中视图是不可更新的
包括以下关键字的sql 语句：聚合函数（sum min max count 等） disinct group by having union 或union all
常量视图 join from 一个不能更新的视图
where字句的子查询 引用了 from 字句中的biao
使用了临时表

视图基本使用

创建视图
格式
create view 视图名称 as sql查询；
create view 视图名称（字段名称列表） as sql 查询
create database db9 （创建库）

create table db9.user(name char(30),password char(1),uid int,gid int,comment char(100),homedir char(150),shell char(35)); （创建 user表）
system cp /etc/passwd /var/lib/mysql-files/ (拷贝)

load data infile “/var/lib/mysql-files/passwd” into table user fields terminated by “:” lines terminated by “\n”; (数据导入)

alter table user add id int primary key auto_increment first （创建子增长）

创建视图
create view v1 as select name，uid，gid from user
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| name | char(30) | YES | | NULL | |
| uid | int(11) | YES | | NULL | |
| gid | int(11) | YES | | NULL | |
±------±---------±-----±----±--------±------+
create view v1(user,uid_v,gid_v) as select name,uid,gid from user; （自己指定名字）
±------±---------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±---------±-----±----±--------±------+
| user | char(30) | YES | | NULL | |
| uid_v | int(11) | YES | | NULL | |
| gid_v | int(11) | YES | | NULL | |
±------±---------±-----±----±--------±------+

show table status\G; (显示当前库下所有表状态信息)
show table status where comment=“view”\G; (查询当前库下那些是视图表)
show create view vi; （查看创建视图（vi） 具体命令）—查看视图对应的基表

使用视图
查询记录
select 字段名列表 from 视图名 where 条件
插入记录
insert into 视图名（字段名列表） values（字段值列表）；
更新记录
update 视图 set 字段名=值 where 条件
删除记录
delete from 视图名 where 条件

删除视图
drop view 视图名 （只能删除视图）

创建视图的完全格式
命令格式
create table t1 select name,uid,shell from user limit 3;
create table t2 select name,uid,shell,password from user limit 6;
select *from t1 left join t2 on t1.name=t2.name;
select from t1 right join t2 on t1.name=t2.name
select * from t1,t2 where t1.name=“root” and t2.name=“root”;
±-----±-----±----------±-----±-----±----------±---------+
| name | uid | shell | name | uid | shell | password |
±-----±-----±----------±-----±-----±----------±---------+
| root | 1000 | /bin/bash | root | 1000 | /bin/bash | x |
±-----±-----±----------±-----±-----±----------±---------+
创建视图必须给字段取别名
create view v2 as select t1.name as a,t1.uid as b,t1.shell as c,t2. from t1,t2 where t1.name=“root” and t2.name=“root”;
select * from v2;
±-----±-----±----------±-----±-----±----------±---------+
| a | b | c | name | uid | shell | password |
±-----±-----±----------±-----±-----±----------±---------+
| root | 1000 | /bin/bash | root | 1000 | /bin/bash | x |
±-----±-----±----------±-----±-----±----------±---------+
create view v3(a,b,c,d,e,f,g) as select * from t1,t2 where t1.name=“root” and t2.name=“root”;
select * from v3;
±-----±-----±----------±-----±-----±----------±-----+
| a | b | c | d | e | f | g |
±-----±-----±----------±-----±-----±----------±-----+
| root | 1000 | /bin/bash | root | 1000 | /bin/bash | x |
±-----±-----±----------±-----±-----±----------±-----+

查询时给表定义取别名名称(a 是 user表的别名 b是 info 表别名)
create view 视图名 as select 表别名.源字段 as 字段名 from 源表名 表别名 left join 源表名 表别名 on 条件

create or replace view v3 as select * from user （视图覆盖，原先v3
被覆盖）

ALGORITHM=UNDEFINED （未定义默认算法，为merge）
create ALGORITHM=TEMPTABLE view v8 as select * from user （算法为具体化方式）关于算法：
算法未定义时，值为UNDEFIBED 默认为 merge替换方式
temptables 具体化方式
具体化方式：先执行select基表 ，再执行查询视图 两次 速度慢
merge 替换方式只查询视图，速度块。

视图限制
with check option’ 对视图做操作受限制
cascaded （默认） 要满足视图本身的 ，同时有要满足基表限制
local 只要满足视图本身限制即可
create table user2 select name,uid,gid,shell from user where uid >=5 and uid<=50;
create view v11 as select * from user where uid<=50 with local check option; (但当基表uid小于50，修改后不会在视图表显示 视图表受自己限制不能改uid 大于50的)
update v11 set uid=51 where name=“nysc”; 报错 （超了）

create view v13 as select * from user3 where uid>=30 and uid<=80;
create view v14 as select * from v13 where uid>=50 with check option ;
update v14 set uid=100 where name=“sshd”; （会报错，因为uid受 v13，v14.两者影响，且v14 修改uid 范围在 50—80 间）

mysql 存储过程
存储过程，相当于是mysql语句组成的脚本
–指的是数据库中保存的一系列SQL命令的集合
–可以在存储过程中使用变量，条件判断，流程控制等
存储过程优点：
–提高性能
–可减轻网络负担
–可以防止对表的直接访问
–避免重复编写SQL操作

创建存储过程
delimiter //
create procedure 名称（）
begin
代码
end
// 结束存储过程
delimiter；（将分隔符改回来）

delimiter //
create procedure p1()
begin
select count() from db9.user;
end
//
delimiter ;
调用存储过程
call db9.p1(); (查看)
±---------+
| count() |
±---------+
| 41 |
±---------+
查看存储过程
方法1. show procedure status
方法2. select db,name,type,body from mysql.proc where name=“p1”; （p1 存储过程）
±----±-----±----------±-----------------------------------------+
| db | name | type | body |
±----±-----±----------±-----------------------------------------+
| db9 | p1 | PROCEDURE | begin
select count() from db9.user;
end |
±----±-----±----------±-----------------------------------------+
select body from mysql.proc where name=“p1”;
±-----------------------------------------+
| body |
±-----------------------------------------+
| begin
select count() from db9.user;
end |
±-----------------------------------------+
删除存储过程
drop procedure 存储过程名
drop procedure db9.p1；

变量类型

show global variables //查看全局变量
show session variables //查看会话变量
set session sort_buffer_size = 40000 //设置会话变量
show session variables like “sort_buffer_size”; (//查看会话变量)
自定义变量
select name into @a from db9.user where name=“root”;
select @a;
±-----+
| @a |
±-----+
| root |
±-----+
set @b=“jin”; ====> select @b;
set @k=9,@j=10;
select @k,@j;
±-----±-----+
| @k | @j |
±-----±-----+
| 9 | 10 |
±-----±-----+

局部变量 （declare）
delimiter //
create procedure db9.p2()
begin
declare j int default 9;
declare k char(3);
set k=“bob”;
select j,k;
end
//
delimiter ；
call db9.p2();
±-----±-----+
| j | k |
±-----±-----+
| 9 | bob |
±-----±-----+

参数类型

存储过程参数 类型分为 in out inout
in 类型参数 给存储过程 传递数据
out类型参数 接受从存储过程输出的数据
inout类型参数 是从前二者的结合
实例一、
delimiter //
create procedure db9.p5( in x char(40) )
begin
select name from user where name=x;
end
//
delimiter ;
call db9.p5(“root”); 调用时没给值会报错
实例二、
delimiter //
create procedure db9.p6( in shellname char(30),out num int)
begin
select count(name) into num from db9.user where shell=shellname;
select num;
end
//
delimiter ;
call db9.p6("/bin/bash",@x); (注out inout 都要用变量占位，无论变量x是否赋值)
±-----+
| num |
±-----+
| 2 |
±-----+
实例三、
delimiter //
create procedure db9.p7( inout x char(30) )
begin
select name,shell from user where shell=x;
select count(*) into x from db9.user;
select x;
end
//
delimiter ;
set @name="/bin/bash"
call db9.p7(@name);
±-----±----------+
| name | shell |
±-----±----------+
| root | /bin/bash |
| lisi | /bin/bash |
±-----±----------+
2 rows in set (0.01 sec)
±-----+
| x |
±-----+
| 41 |
±-----+

算数运算

set @k = 5；
set @y = 7；
set @z=@k+@y
set @x=3;set @y=3;set @z=@x*@y;select @z;
±-----+
| @z |
±-----+
| 9 |
±-----+
delimiter //
create procedure db9.p4()
begin
declare x int;
declare y int;
declare z int;
select count(shell) into x from db9.user where shell="/bin/bash";
select count(shell) into y from db9.user where shell="/sbin/nologin";
set z=x+y;
select x,y,z;
end
//
delimiter ;
call db9.p3();
±-----±-----±-----+
| x | y | z |
±-----±-----±-----+
| 2 | 35 | 37 |
±-----±-----±-----+
流程控制
shell 流程控制 if case for while
数据库 流程控制 if for
条件判断 数值比较 字符比较 范围 空 非空 逻辑 模糊匹配 正则

if 单分支
if 条件判断 then
代码
end if；
循环结构
delimiter //
create procedure db9.p20()
begin
if 1 <= 2 then
select user();
end if;
select * from db9.user where id=1;
end
//
delimiter ;
call db9.p20();
±---------------+
| user() |
±---------------+
| root@localhost |
±---------------+
1 row in set (0.00 sec)

±—±-----±---------±-----±-----±--------±--------±----------+
| id | name | password | uid | gid | comment | homedir | shell |
±—±-----±---------±-----±-----±--------±--------±----------+
| 1 | root | x | 1000 | 0 | root | /root | /bin/bash |
±—±-----±---------±-----±-----±--------±--------±----------+

if双分支结构
if 条件判断 then
代码
else
代码
end if；
delimiter //
create procedure db9.p21( in line int)
begin
if line < 10 then
select * from db9.user where id <=line;
else
select * from db9.user where id >=line;
end if;
end
//
delimiter ;
call db9.p21(2);
±—±-----±---------±-----±-----±--------±--------±--------------+
| id | name | password | uid | gid | comment | homedir | shell |
±—±-----±---------±-----±-----±--------±--------±--------------+
| 1 | root | x | 1000 | 0 | root | /root | /bin/bash |
| 2 | bin | x | 1 | 1 | bin | /bin | /sbin/nologin |
±—±-----±---------±-----±-----±--------±--------±--------------+
call db9.p21(40);
±—±------±---------±-----±-----±-------------±---------------±-----------+
| id | name | password | uid | gid | comment | homedir | shell |
±—±------±---------±-----±-----±-------------±---------------±-----------+
| 40 | lisi | x | 1000 | 1000 | lisi | /home/lisi | /bin/bash |
| 41 | mysql | x | 27 | 27 | MySQL Server | /var/lib/mysql | /bin/false |
±—±------±---------±-----±-----±-------------±---------------±-----------+
循环结构
while 条件判断 do
循环体
end while;
delimiter //
create procedure db9.p22()
begin
declare x int default 1;
declare y int default 1;
while x <= 6 do
select y;
set x = x + 1;
end while;
end
//
delimiter ;
call db9.p22();
结果输出六个1
loop死循环（无条件、反复执行某一段代码）
loop
循环体
end loop；
delimiter //
create procedure db9.p23()
begin
declare y int default 1;
loop
select y;
end loop;
end
//
delimiter ;
repeat 条件式循环 （当条件成立时结束循环）
repeat
循环体
until 条件判断
end repeat;
delimiter //
create procedure db9.p24()
begin
declare y int default 1;
repeat
select y;
set y = y + 1;
until y = 7
end repeat;
end
//
delimiter ;
call db9.p24();
输出 （1、2、3、4、5、6）

delimiter //
create procedure db9.p25( in nline int )
begin
if nline is not null then
select * from db9.user where id = nline;
else
select * from db9.user where id = 1;
end if;
end
//
delimiter ;
call db9.p25(@x); (@X 不定以会输出 id=1)
call db9.p25(3); （会输出第三行）

输出db9库里user表中 uid号 是偶数的用户名 及对应的uid号 并统计uid 号 是偶数的用户用户个数
delimiter //
create procedure db9.p26()
begin
declare x int;
declare y int default 1;
declare z int;
declare e int default 0;
select count(*) into x from db9.user;
while y <= x do
select uid into z from db9.user where id = y;
if z % 2 = 0 then
select name,uid from db9.user where id = y;
set e = e + 1;
end if;
set y = y + 1;
end while;
select e;
end
//
delimiter ;
call db9.p26(); —【输出用户与uid 为偶数的】
±-----±-----+
| name | uid |
±-----±-----+
| root | 1000 |
±-----±-----+