1 视图与临时表

临时表

//1.0 仅仅复制表的结构
CREATE TABLE IF NOT EXISTS new_user  LIKE user;
//2.0 仅复制数据
insert into userdata(userId) select userId from users;
//3.0 复制表的结构+数据
CREATE TABLE IF NOT EXISTS new_user SELECT * FROM user;

//创建临时表newuser，该临时表的表结构和现有数据库user表一致
CREATE TEMPORARY TABLE IF NOT EXISTS newuser LIKE user;

//创建一个临时表newuser，该临时表的表结构需要重新定义，如果已经存在则先删除.如果已经存在newuser临时表，则drop掉
drop TEMPORARY TABLE if EXISTS newuser;
CREATE TEMPORARY TABLE newuser(
    userid int PRIMARY KEY UNIQUE AUTO_INCREMENT,
    nickname varchar(20)
);
insert into newuser(nickname) values('fly'),('wu');
select * from newuser;

视图

视图是什么：视图（view）是一种虚拟存在的表，是一个逻辑表，本身并不包含数据，作为一个select语句保存在数据字典中的。通过视图，可以展现物理表的部分数据；视图数据来自定义视图的查询中使用的表，使用视图动态生成。是mysql5.1版本出现的新特性。

视图的作用：

简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。如职员薪资表等。
数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

创建修改/使用/删除/查看视图

//1.0 创建/修改基本语法
create or replace view viewname as 查询语句;
//示例：创建一个查看员工工资的视图，该视图屏蔽掉了关键的员工薪资信息
create or replace view view_getuseremployee as select u.id,u.name,e.totalmoney from user u inner join employee e on u.id=e.userid;
//2.0 使用视图
select * from view_getuseremployee;
//视图也可以像普通表一样使用
select v.*,ud.username from view_getuseremployee v inner join userdata ud on v.userid=ud.userid;
//3.0 删除视图
语法：drop view 视图名,视图名,...;
DROP VIEW emp_v1,emp_v2,myv3;
//4.0 查看视图
查看视图的列结构：desc viewnaem;
查看视图的内容和字符集等: SHOW CREATE VIEW viewname;

视图和普通表的对比

	创建关键字	是否实际占用物理空间	使用
表	create table	保存了实际的data数据	增删改查
视图	create view	只是保存了sql语句	增删改查，但是一般只用于select查询操作。增删改的话，则有限制，且修改也是直接对物理表的修改，一般不推荐。

~~对视图进行数据DML操作（不推荐）~~
特别强调：因为视图本身没有数据，因此对视图进行的DML操作最终都体现在基表中。另：视图一般只用于查询，不建议进行DML操作。

示例
mysql> create view v_student as select * from student;
mysql> select * from v_student;
+--------+--------+------+
| 学号    | name   | sex  |
+--------+--------+------+
|      1 | 张三    | M    |
|      2 | 李四    | F    |
|      5 | 王五    | NULL |
+--------+--------+------+
mysql> update v_student set name='钱六' where 学号='1';
mysql> select * from student;
+--------+--------+------+
| 学号    | name   | sex  |
+--------+--------+------+
|      1 | 钱六    | M    |
|      2 | 李四    | F    |
|      5 | 王五    | NULL |
+--------+--------+------+

扩展：有下列内容之一，视图不能做DML操作(不能将对视图的DML修改反馈到基表，则不能修改)

select子句中包含distinct
select子句中包含组函数
select语句中包含group by子句
select语句中包含order by子句
select语句中包含union 、union all等集合运算符
where子句中包含相关子查询
from子句中包含多个表
如果视图中有计算列，则不能更新
如果基表中有某个具有非空约束的列未出现在视图定义中，则不能做insert操作
2 存储过程和自定义函数
存储过程
基本概念
含义：一组预先编译好的SQL语句的集合，理解成批处理语句。
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

存储过程的创建/调用/参数列表/备注
参考：MySQL存储过程

//1.0 创建语法
CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
    SQL语句
END
如果存储过程体仅只有一句话，则begin/end可以忽略。存储过程体中的每条sql语句的结尾要求必须加句号。存储过程的结尾可以使用delimiter重新设置。
////关于参数列表的使用说明
参数模式    参数名        参数类型
in/out/inout    nickname    varchar(20)
//2.0 调用
CALL 存储过程名(实参列表);
//3.0 删除存储过程
drop procedure if exists procname;
//4.0 显示存储过程的信息
SHOW CREATE PROCEDURE myp 8;

案例演示（参数分别为in/out/inout）

#1.0 存储过程的结尾可以使用 delimiter 重新设置
#存储过程含义：根据传入的userid值，返回得到其对应的name，并且同时
delimiter $
#2.0 判断是否存在存储过程getnamebyuserid,如果已经存在则删除之
drop procedure if exists getnamebyuserid;
#3.0 创建存储过程，参数列表为一个in参数，一个out参数(参数也可以设置为空)
CREATE PROCEDURE getnamebyuserid(IN userid int,OUT username VARCHAR(20))
BEGIN
    #4.0 开始为out参数赋值
    #note:此时可以直接用此方法更改参数:set userid=10。
    #因为此时的userid和username参数已经和局部变量一样可以被修改值
    #局部变量的定义/赋值/查询
    select name from `user` u where u.id=userid into username;
    #5.0 可以直接使用select语句
    select * from userdata where userid=userid;
END $
delimiter ;
#5.0 实际开始调用,@username无需set初始化
call getnamebyuserid(1,@username);
select @username;

#1.0 存储过程的结尾可以使用 delimiter 重新设置
delimiter $
存储过程含义：传入value1和value2两个值，最终value1和value2都翻倍并返回
#2.0 判断是否存在存储过程getnamebyuserid,如果已经存在则删除之
drop procedure if exists myproctest;
#3.0 创建存储过程，参数列表为一个in参数，一个out参数(参数也可以设置为空)
drop procedure if exists getnamebyuserid;
CREATE PROCEDURE myp8(INOUT value1 INT ,INOUT value2 INT)
BEGIN
    #局部变量的定义/赋值/查询
    declare sum int default 0;
    SET value1=value1*2;
    SET value2=value2*2;
    SET sum=value1+value2;
    #4.0 可以直接使用select语句
    SELECT sum;
END $
delimiter ;
#5.0 实际开始调用, @value1和@value2需要传入也需要传出，所需要set初始化
SET @value1=10;
SET @value2=20;
CALL myproctest(@value1,@value2);
SELECT @value1,@value2;

自定义函数

基本概念
含义：一组预先编译好的SQL语句的集合，理解成批处理语句
1、提高代码的重用性
2、简化操作
3、减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
创建/调用/查看/删除

//1.0 创建函数
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
    函数体
END
参数列表：其中参数列表包括参数名和参数类型，可是有参，也可无参，
返回值：函数体必须要有return语句，如果没有则会报错，建议一般将return放到函数的最后。
//2.0 调用函数
SELECT 函数名(参数列表)
#函数赋值
#在其他函数内部使用
SELECT myfunction(1,2) into return_str;
//3.0 删除函数
DROP FUNCTION if exists myfunction; 
//4.0 查看函数
SHOW CREATE FUNCTION myfunction;

标准案例演示

delimiter $
#1.0 是否存在指定函数，如果存在则drop掉
drop FUNCTION if exists myfunciton;
#2.0 参数可为空参数列表，也可为带参列表
CREATE FUNCTION myfunciton(num1 FLOAT,num2 FLOAT) RETURNS FLOAT
BEGIN
    #3.0 局部变量必须放置在begin/end第一行
    DECLARE SUM FLOAT DEFAULT 0;
    SET SUM=num1+num2;
    #note:不能在方法体内使用select * from user语句，否则会提示
#[Err] 1415 - Not allowed to return a result set from a function
    #4.0 必须要有return语句，且仅能返回一个值
    RETURN SUM;    
END $
#调用
SELECT myfunciton(1,2)$
#在其他函数内部使用
SELECT myfunction(1,2) into return_str;

存储过程和函数的区别

存储过程：返回值不受限（可以有0个返回，也可以有多个返回），适合做批量插入、批量更新。
函数：必须有且仅有1个返回，适合做处理数据后返回一个结果。

扩展：综合案例

该典型案例结合了存储过程+用户变量(定义+赋值)+临时表+while循环结构+if判断结构

delimiter $
#1.0 存储过程判断，如果存在则drop
DROP PROCEDURE if exists proctest;
#2.0 创建存储过程
CREATE PROCEDURE proctest()
BEGIN
    #3.0 临时表判断，如果存在则drop
    drop TEMPORARY TABLE if EXISTS newuser;
    #4.0 创建临时表
    CREATE TEMPORARY TABLE newuser(
        userid int PRIMARY KEY UNIQUE AUTO_INCREMENT,
        nickname varchar(20)
    );
    #5.0 为临时表赋值
    insert into newuser(nickname) values('fly'),('wu'),('hello');
    #6.0 定义用户变量
    set @userid=0,@nickname='';
    #7.0 定义循环结构，注意whileA是labelName，用于在循环体里面使用leave/iterate进行控制
    whileA:while(exists(select * from newuser))  DO
        select userid,nickname into @userid,@nickname from newuser limit 0,1;
        #8.0 if结构+iterate/leave关键字管控，此处就需要用到whileA这个标签名
        if(@userid=2)
            then ITERATE whileA;
            select @userid,@nickname;
        end if;
        select @userid,@nickname;
        delete from newuser where userid=@userid;
    #9.0 结束当前循环
    end while whileA;
    delete from newuser;
    #10.0 drop掉临时表
drop TEMPORARY TABLE newuser;
END $
delimiter ;
#手动调用下存储过程
call proctest();

10 数据库之MySQL

10 其他数据库对象：视图&存储过程

1 视图与临时表

临时表

视图

2 存储过程和自定义函数

存储过程

自定义函数

存储过程和函数的区别

扩展：综合案例