mysql快速复习整理笔记（二）

yun

Mysql视图

视图是虚表，它只存储结构的定义，而不存储数据

视图只有在被使用时，才从基表中搜寻数据，展现给用户。

视图的优点：
1、        简单
2、        安全：防止未授权用户访问敏感数据
3、        数据独立：降低数据库的复杂度
4、        视图中数据的更新就是表数据的更新。

语法：create view 视图名 as select 列名，列名，… from 表名；

不可更新视图：
如果视图中包含以下任何一种，是不可更新的：
1、        聚合函数（sum() min() count()等等）
2、        Distinct、group by、having、union或union all等
3、        Join
4、        Where字句的不可更新
5、        Where字句中的子查询
6、        引用from字句中的表
7、        Algorithm=temptable

修改视图：
Alter view 视图名 as select 列名，列名，… from 表名；

删除视图：
Drop view 视图名，视图名；

Mysql存储过程和函数

存储过程定义：就是事先进行编译存储到数据中的一段sql语句集合

优点：1、sql语句每执行一次，就会进行编译一次，所以存储过程可以提高数据库执行速度
          2、简化复杂操作，可结合事务的封装       
          3、复用性
          4、安全性高，可指定存储过程的使用权

语法：create procedure 过程名（参数列表）
                        过程体

Delimiter命令：由于受到语法的限制，分号代表语句的结束，但如果语句我还没有写完，这是可以使用delimiter分界符。即：自定义结束符。

编写存储过程的简单示例：
Delimiter //
Create procedure 过程名（参数列表）
Begin
有效的sql语句
End //
Delimiter ；（最后再把结束符改过来）

调用存储过程：
Call 存储过程（@实际参数）

函数：
语法：
Delimiter //
create function 函数名(参数列表) return 返回值类型
begin
有效的sql语句
End//
Delimiter ；

调用函数：select 函数名(实际参数)

删除存储过程和函数（一次只能删除一个存储过程和函数）
Drop procedure 存储过程名；
Drop function 函数名；


变量的定义以及使用

语法：declare 变量名 […] 类型 [default值]

注意要点：变量只能定义在begin和end之间，也可以用在嵌套的块中；并且变量定义必须复合语句的开头。在任何其他语句的前面。

变量赋值：
直接赋值：set 变量名=表达式值或常量值

将查询结果赋值给变量（查询返回的结果只能有一行）
Set 列名[…] into 变量名[…] from 表名 where 条件；

用户变量：是由客户端定义的变量
声明形式：@变量名

赋值：
Set @变量名=表达式值；
Select 值 into @变量名；

Delimiter //
Create procedure proc2()
Begin
Declare xname varchar(50);
Declare xdesc varchar(100);
Set xname = ‘财务部’;
Set xdesc = ‘发钱的部门’;
Insert into dept(name,description) values(xname,xdesc);
End//
Delimiter ;


流程控制：

If语句
语法：
If 条件表达式 then
        Sql语句
        Elseif 条件表达式2 then sql语句块2
        Else sql语句块3
End if；

Case语句
语法：
Case 表达式值
When 值1 then sql语句块1
        When 值2 then 语句块2…
Else sql语句块3
End case

Loop语句及跳转
语法：
[开始标注：]loop
        语句块
        End loop[结束标注]

Leave label
用于从标注的流程控制结构中退出。它和begin..end或循环一起被使用。

Lterate label
跳过当前循环剩下的语句，直接进入下一轮循环
Lterate只可以出现在loop，repeat，和while语句内。


Mysql触发器
语法：
Delimiter //
Create trigger 触发器名
{Before|after}                #触发的时间
{insert|update|delete}                 #触发事件
On 表名 for each row                 #要触发的SQL语句
Begin
有效的sql语句
End//
Delimiter ；

使用别名old和new，能够引用与触发程序相关的表中的列。Old.col_name在更新或删除它之前，引用已有行中的一列。New.col_name则相反。

注意要点：触发器只能定义在永久表上，不能对临时表创建。

查看触发器
Show triggers；

删除触发器
Drop trigger 触发器名


Mysql用户和权限管理

Mysql权限系统的原理
通过两个阶段进行认证：
1、        对连接的用户进行身份认证
2、        对通过身份认证合法的用户赋予权限

创建用户
语法：create user 用户名 identified by 密码

给用户授权
第一种方式使用grant语句进行授权：
Grant 权限类型
        On 对象类型 to 用户 identified by 密码
        [with grant option]；

权限类型：select、insert、update、delete…
对象类型：table、function、procedure等

显示用户相应的权限：
Show grants for ‘vince’;

更改帐号的权限：
使用grant新增权限
使用revoke回收权限
语法：
Revoke 权限类型
        On 对象类型 from 用户

修改帐号密码：
Set password for 用户名=password(‘新密码‘)；


Mysql数据库备份与恢复：
数据备份：
在dos环境下进行备份：mysql –u root –p 数据库 > d:\my.sql

数据恢复：
前提：所恢复的数据库必须存在
在dos环境下进行恢复：mysql –u root –p 数据库 < d:\my.sql
在mysql环境下进行恢复：source d:\my.sql