MySQL复习之索引，视图，触发器，常用函数，存储过程和函数，数据备份和还原

今日目录：

1、索引的引入

2、索引的优缺点

3、索引的实例

4、索引的分类

5、创建索引

6、删除索引

一、索引的引入

索引定义：索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度；类似于图书的目录，方便快速定位，寻找指定的内容；

二、索引的优缺点

优点：提高查询数据的速度；

缺点：创建和维护索引的时间增加了；

三、索引的实例

四、索引的分类

1、普通索引

这类索引可以创建在任何数据类型中；

2、唯一性索引

使用 UNIQUE 参数可以设置，在创建唯一性索引时，限制该索引的值必须是唯一的；

3、全文索引

使用 FULLTEXT 参数可以设置，全文索引只能创建在 CHAR，VARCHAR，TEXT 类型的字段上。主要作用就是提高查询较大字符串类型的速度；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；

4、单列索引

在表中可以给单个字段创建索引，单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引；

5、多列索引

多列索引是在表的多个字段上创建一个索引；

6、空间索引

使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；

五、创建索引

5.1、创建表示创建索引

CREATE TABLE 表名 (属性名 数据类型 [完整性约束条件],

属性名 数据类型 [完整性约束条件],

....

属性名 数据类型

[UNIQUE | FULLTEXT | SPATIAL ] INDEX| KEY

[别名] (属性名 1 [(长度)] [ASC | DESC])

);

5.2、在已经存在的表上创建索引

CREATE [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 ON 表名 (属性名 [(长度)] [ASC | DESC])；

5.3、用 ALTER TABLE 语句来创建索引

ALTER TABLE 表名 ADD [ UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (属性名 [(长度)] [ ASC | DESC])；

六、删除索引

DROP INDEX 索引名 ON 表名 ；

今日目录：

1、视图的引入

2、视图的作用

3、创建视图

4、查看视图

5、修改视图

6、更新视图

7、删除视图

一、视图的引入

1、视图是一种虚拟的表，是从数据库中一个或者多个表中导出来的表。

2、数据库中只存放了视图的定义，而并没有存放视图中的数据，这些数据存放在原来的表中。

3、使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。

二、视图的作用

1、使操作简便化；

2、增加数据的安全性；

3、提高表的逻辑独立性；

三、创建视图

CREATE [ALGORITHM ={ UNDEFIEND | MERGE | TEMPTABLE }] VIEW 视图名 [ ( 属性清单) ] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

ALGORITHM 是可选参数，表示视图选择的算法；

“视图名”参数表示要创建的视图的名称；

“属性清单”是可选参数，其指定了视图中各种属性的名词，默认情况下与 SELECT 语句中查询的属性相同；

SELECT 语句参数是一个完整的查询语句，标识从某个表查出某些满足条件的记录，将这些记录导入视图中；

WITH CHECK OPTION 是可选参数，表似乎更新视图时要保证在该视图的权限范围之内；

ALGORITHM 包括 3 个选项 UNDEFINED、MERGE 和 TEMPTABLE。

其中，UNDEFINED 选项表示 MySQL 将自动选择所要使用的算法；MERGE 选项表示将使用视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分；TEMPTABLE 选项表示将视图的结果存入临时表，然后使用临时表执行语句；CASCADED是可选参数，表示更新视图时要满足所有相关视图和表的条件，该参数为默认值；LOCAL 表示更新视图时，要满足该视图本身的定义条件即可；

3.1、在单表上创建视图

3.2、在多表上创建视图

四、查看视图

4.1、DESCRIBE 语句查看视图基本信息

4.2、SHOW TABLE STATUS 语句查看视图基本信息

4.3、SHOW CREATE VIEW 语句查看视图详细信息

4.3、在 views 表中查看视图详细信息

五、修改视图

5.1、CREATE OR REPLACE VIEW 语句修改视图

CREATE OR REPLACE [ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }] VIEW 视图名 [( 属性清单 )] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

5.2、ALTER 语句修改视图

ALTER [ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }] VIEW 视图名 [( 属性清单 )] AS SELECT 语句 [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

六、更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。超出了范围，就不能更新。

6.1、 插入(INSERT)

6.2、 更新(UPDATE)

6.3、 删除(DELETE)

七、删除视图

删除视图是指删除数据库中已存在的视图。删除视图时，只能删除视图的定义，不会删除数据；

DROP VIEW [ IF EXISTS ] 视图名列表 [ RESTRICT | CASCADE ]

今日目录：

1、触发器的引入

2、创建与使用触发器

3、查看触发器

4、删除触发器

一、触发器的引入

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

二、创建与使用触发器

2.1、创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE |AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句

2.2、创建多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE |AFTER 触发事件 ON 表名 FOR EACH ROW BEGIN 执行语句列表

END

三、查看触发器

3.1、SHOW TRIGGERS 语句查看触发器信息

3.2、在 triggers 表中查看触发器信息

四、删除触发器

DROP TRIGGER 触发器名；

今日目录：

1、日期和时间函数

2、字符串函数

3、数字函数

4、加密函数

一、日期和时间函数

1、CURDATE() 返回当前日期；

2、CURTIME() 返回当前时间；

3、MONTH(d) 返回日期 d 中的月份值，范围是 1~12

二、字符串函数

1，CHAR_LENGTH(s) 计算字符串 s 的字符数；

2，UPPER(s) 把所有字母变成大写字母；

3，LOWER(s) 把所有字母变成小写字母；

三、数字函数

1，ABS(x) 求绝对值

2，SQRT(x) 求平方根

3，MOD(x,y) 求余

四、加密函数

1，PASSWORD(str) 一般对用户的密码加密 不可逆

2，MD5(str) 普通加密 不可逆

3，ENCODE(str，pswd_str) 加密函数，结果是一个二进制数，必须使用 BLOB 类型的字段来保存它；

4，DECODE(crypt_str，pswd_str) 解密函数；

今日目录：

1、存储过程和函数的引入

2、创建存储过程和函数

3、调用存储过程和函数

4、查看存储过程和函数

5、修改存储过程和函数

6、删除存储过程和函数

一、存储过程和函数的引入

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输；

存储函数与存储过程的区别

一、 存储函数有且只有一个返回值，而存储过程不能有返回值。

二、 函数只能有输入参数，而且不能带in, 而存储过程可以有多个in,out,inout参数。

三、 存储过程中的语句功能更强大，存储过程可以实现很复杂的业务逻辑，而函数有很多限制，如不能在函数中使用insert,update,delete,create等语句；存储函数只完成查询的工作，可接受输入参数并返回一个结果，也就是函数实现的功能针对性比较强。

四、 存储过程可以调用存储函数。但函数不能调用存储过程。

五、 存储过程一般是作为一个独立的部分来执行(call调用)。而函数可以作为查询语句的一个部分来调用。

二、创建存储过程和函数

2.1、创建存储过程

create procedure sp_name()

begin

.........

end

2.2、 创建存储函数

create function 函数([函数参数[,….]]) Returns 返回类型

Begin

Return (返回的数据)

end;

三、调用存储过程和函数

3.1、调用存储过程

CALL sp_name( [parameter[,...]] )

3.2 调用存储函数

fun_name( [parameter[,...]] )

四、查看存储过程和函数

4.1 SHOW STATUS 语句查看存储过程和函数的状态

SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ‘pattern’] ;

4.2 SHOW CREATE 语句查看存储过程的函数的定义

SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;

五、修改存储过程和函数

ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]

characteristic :

{ CONTAINS SQL } NO SQL | READS SQL DATA| MODIFIES SQL DATA}

| SQL SECURITY { DEFINER | INVOKER }

| COMMENT ‘string’

六、删除存储过程和函数

DROP sp_name ;

今日目录：

1、数据备份

2、数据还原

一、数据备份

1.1、使用 mysqldump 命令备份

mysqldump -u username -p dbname table1 table2 ... > BackupName.sql

dbname 参数表示数据库的名称；table1 和 table2 参数表示表的名称，没有该参数时将备份整个数据库；BackupName.sql 参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常以 sql 作为后缀。

1.2 使用 sqlyog 图形工具备份

二、数据还原

2.1、使用 mysql 命令还原

Mysql -u root -p [dbname]

dbname 参数表示数据库名称。该参数是可选参数，可以指定数据库名，也可以不指定。指定数据库名时，表

示还原该数据库下的表。不指定数据库名时，表示还原特定的一个数据库。而备份文件中有创建数据库的语句。

2.2、 使用 sqlyog 图形工具还原

添加小编微信可以获取免费资源，二维码

要用到的工具，关注公众号直接免费获取：