MySQL学习⼋——视图、存储过程
⼀、mysql视图
1.1 什么是mysql视图
——虚拟表
——内容与真实的表相似，有字段有记录
——视图并不在数据库中以存储的数据形式存在
——⾏和列的数据来⾃定义视图时查询所引⽤的基表，并且在具体引⽤视图时动态⽣成
——更新视图的数据，就是更新基表的数据
——更新基表数据，视图的数据也会跟着改变
1.2 视图优点
简单——⽤户不需要关系视图中的数据如何查询获得；
—
—视图中的数据已经是过滤好的符合条件的结果集
安全 ——⽤户只看到视图中的数据
数据独⽴ ——⼀旦视图结构确定，可以屏蔽表结构对⽤户的影响
1.2 视图使⽤限制
——不能在视图上创建索引
——在视图的FROM⼦句中不能使⽤⼦查询
——以下情形中的视图是不可更新的：
-----包含以下关键字的SQL语句：聚合函数（SUM \MIN \MAX \COUNT等），DISTINCT
\GROUP BY \ HAVING \ UNION 或UNION ALL
——常量视图、JOIN \ FROM ⼀个不能更新的视图
——WHERE⼦句的⼦查询引⽤了FROM⼦句中的表
—
—使⽤了临时表
1.4 视图的基本使⽤
创建视图:
—— create view 视图名称 as SQL查询；
——create view 视图名称（字段名列表） as SQL查询；
（注意：在视图表中不定义字段名的话，默认使⽤基表的字段名，
若定义字段名的话，视图表中的字段必须和基表的字段个数相等）
查看视图:
查看当前库下所有的表的状态信息：
—— show table status;
——show table status where comment=“view”\G;
查看创建视图具体命令：
——show create view 视图名；
使⽤视图：（对视图操作即为基本操作，反之亦然）
查询记录：-select 字段名列表 from 视图名 where 条件；
插⼊记录：-insert into 视图名（字段名列表） values(字段名列表)；
更新记录：-update 视图名 set 字段名=值 where 条件；
删除记录：-delete from 视图名 where 条件；
删除视图
—— -drop view 视图名；
1.5 AS定义视图中字段名称（创建视图的完全格式）
命令格式：
-
create
[or replace] ： 替换试图
定义处理视图的⽅式：
[algorithm={undefined | merge | temptable}]
[definer = {user | surrent_user}]
[sql security {definer | invoker}]
view view_name [(column_list)]
as select_statement
[with [cascaded |local] check option]
设置字段别名：
关联查询的视图默认不允许修改视图字段的值，
视图中的字段名不可以重名所以要定义别名
- create view 视图名
as ⽤来设置别名的
select 表别名.源字段名 as 字段别名
from 源表名 表别名 left join 源表名 表别名
on 条件；
or replace 替换原先创建的同名视图，起到修改视图的作⽤
1.6 OR REPLACEX 重要选项的使⽤
——create or replace view 视图名 as select 查询；
创建时，若视图已存在，会替换已有的视图。
algorithm
—
—alogrithm = {undefined | merge temptable } ——定义处理视图的⽅式–merage ，替换⽅式：
– 视图名直接使⽤视图公式替换掉，把视图公式合并到了select中；
–temptable ，具体化⽅式 ：
– 先得到视图的执⾏结果，该结果形成⼀个中间的结果暂时存在内存中，
之后外⾯的select 语句就调⽤了这些中间结果；
–undefined ，未定义即默认模式：
– algorithm 选项的值是undenfined表⽰使⽤的是merage 替换⽅式。
with check option 当创建的视图是根据另⼀个视图定义时，对视图更新：local 和 cascaded 关键字决定检查的范围
–local（默认值） 仅检查当前视图的限制
–cascaded 同时要满⾜基表的限制
格式：mysql> create view 视图名 as
-> select x,x,x from 表/视图
-> with [cascaded | local] check option;
⼆、mysql存储过程
存储过程，相当于是MySQL语句组成的脚本
——指的是数据库中保存的⼀系列SQL命令的集合
——可以在存储过程中使⽤变量/条件判断/流程控制等
——存储过程，就是MySQL中的脚本
存储过程的有点：
——提⾼性能
——可减轻⽹络负担
——可以防⽌对表的直接访问
—
—避免重复编写SQL操作
2.1 基本使⽤：创建 查看 调⽤ 删除
创建存储过程：
语法格式：- > **delimiter //
create procedure 存储过程名(参数类型 变量名 类型)
begin
… … 功能代码
end
// 结束存储过程
delimiter ; ** （注意：delimiter 与 ； 之间有空格，改⾏要求缺少就会⼀致在脚本中）
（delimiter 关键字⽤来指定存储过程的分隔符；因为MySQL默认以“；”为分隔符，
若没有指定分隔符，编译器会把存储过程当成SQL语句进⾏处理，从⽽执⾏出错）
查看存储过程：⽅法⼀：—mysql >show procedure status\G;
⽅法⼆：—mysql > select db,name,type from mysql.proc where name=“存储过程名”；
调⽤/删除存储过程：
调⽤存储过程： call 存储过程名（）；
——存储过程没有参数时，（）可以省略
——存储过程有参数时，调⽤时必须传给参数；
删除存储过程： drop procedure 存储过程名； （注意：后⾯不⽤加括号）
2.2 存储过程调⽤类型： in out inout
格式：create procedure 存储过程名（{in | out | inout} 参数变量名 参数变量类型 ）
in 输⼊参数：作⽤是给存储过程传值，必须在调⽤存储过程中赋值，在存储过程中该参数变量的值不允许修改；默认类型是 :in
out 输出参数 该值可在存储过程内部被改变，并可返回。
inout 输⼊/输出参数 ： 调⽤时指定，并且可被改变和返回
-create procedure 名称（ 类型 参数变量名 参数变量类型 ，类型 参数变量名 参数变量类型）
注意：调⽤了⼏个变量名，最后call时候就要给与同等数量对应的变量值。
——调⽤类型之间⽤“，” 逗号间隔；
——调⽤变量时，有@符号的变量 例如@x 调⽤的是⽤户⾃定义的变量；
——没有@符号时，例如 x 调⽤的是存储过程的参数变量
ascii码表最后一位
2.3、mysql 变量类型： 会话变量 全局变量 ⽤户变量 局部变量
会话变量和全局变量 叫系统变量，使⽤set命令定义，
全局变量的修改会影响整个服务器，但是对会话变量的修改，只会影响当前的会话。
—— > select @@hostname; （系统变量⽤@@，⽤户⾃定义变量⽤@）
⽤户变量 ：在客户端连接到数据库服务的整个过程中都是有效，当前连接断开后所有⽤户变量失效。
定义 set @变量名=值；
输出 select @变量名；
局部变量 ：存储过程中的begin/end 。其有效范围仅限于该语句执⾏中，语句块执⾏完毕后，变量失效。declare 专门⽤来定义局部变量，局部变量不需要@。
mysql > show global variliables; //查看全局变量
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 usertab; 使⽤sql命令查询结果赋值
2.4、 mysql运算符号 : + - * / DIV %
—— + 加法运算
—— - 减法运算
—— * 乘法运算
—— / 除法运算
—— DIV （必须⼤写DIV）整除运算
—— % 取余
2.5 条件判断符号： （> >= < <= = != or and ! like regexp ）
数值⽐较
—— =
—— > 、 >=
—
— <、 <=
—— !=
—— between … and …
c语言数组比大小条件测试：（逻辑⽐较、范围、空、⾮空、模糊、正则）
or 、 and、 ! 逻辑或、逻辑与、逻辑⾮
in … 、 not in 在什么范围内，不在什么范围内
is null 字段的值为空
in not null 字段值不为空
like 模糊匹配
regexp 正则匹配
2.6 流程控制：
单分⽀选择结构——当“条件成⽴”时执⾏命令序列
if 顺序结构
if 条件判断 then
代码
…
end if ;
双分⽀选择架构——当“条件成⽴”时，执⾏代码1；否则执⾏代码2；if 条件判断 then
代码
mysql查看所有存储过程
…
else
代码
…
end if;
循环结构
while 条件式循环——反复测试条件，只要成⽴就执⾏命令序列
while 条件判断 do
循环体
end while;
loop 死循环——⽆条件，反复执⾏某⼀段代码
loop
循环体
end loop;
repeat 条件式循环——当条件成⽴结束循环
repeat
循环体
until 条件判断
end repeat;
循环控制参数
例⼦：准备⼀张表，把系统⽤户信息存储到db9库下的usertab表⾥，并在多有列前添加⾏号字段id，要求⾃动增长。192.168.4.51 导⼊数据
]# cp /etc/passwd /var/lib/mysql-files/
]# mysql -u root -p
mysql> show variables like “secure_file_priv”;
mysql> create database db9;
mysql> create table db9.usertab(
角权限数据库设计
-> username char(50),
-> password char(1),
-> uid int(2),
-> gid int(2),
-> comment char(100),
-> homedir char(100),
-> shell char(50)
-> );
mysql> load data infile “/var/lib/mysql-files/passwd” into table db9.usertab fields terminated by “:” lines terminated by “\n”;
mysql> alter table db9.usertab add id int(3) primary key auto_increment first;
mysql> select * from db9.usertab;
mysql> desc db9.usertab;
创建、使⽤视图的基本使⽤：
mysql> use db9;
mysql> select database(); 查看当前在那个库内；
mysql> create view v1 as select * from usertab;
mysql> show tables;
mysql> desc v1;
mysql> select * from v1;
]# ls /var/lib/mysql/db9 （v1.frm 视图⽂件，视图没有真正的数据db）
mysql> select * from v1 where username=“root”;
mysql> update v1 set username=“admin” where username=“root”; 修改视图，基表⾥的数据也会发⽣变动；mysql> select * from usertab where username=“admin”;
mysql> insert into v1 (username ,uid) values(“jerry”,988); 插⼊记录，基表也会发⽣改变
mysql> select * from v1 where username=“jerry”;
mysql> select * from usertab where username=“jerry”;
mysql> create view v2 as select uid ,shell from usertab;
mysql> desc v2;
mysql> select * from v2;
mysql> create view v3 (vuid,vshell) as select uid,shell from usertab; 视图表中的字段必须和基表的字段个数相等；mysql> select * from v3;
mysql> show tables;
查看视图：
mysql> show table status\G; 显⽰当前库所有表状态信息，comment 是view 则说明该表是视图。
mysql> show table status where comment=“view”\G; 显⽰当前库下的视图
spring aop常用注解
mysql> show create view v3\G; 查看是基于哪个基表创建的视图elementui上传文件到后端
删除视图：
mysql> drop view v3;
mysql> show create view v3\G; （Table ‘db9.v3’ doesn’t exist）视图删除后对应的⽂件也没有了。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
AS定义视图中字段名称：
mysql> create table user2 select username,uid,gid from usertab limit 3; 快速建表
mysql> create table info select username,uid,gid,homedir,shell from usertab limit 5;
mysql> show tables;
mysql> select * from user2;
mysql> select * from info;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。