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 图形工具还原

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

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

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180509G0XG8100?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券