常用SQL命令汇总（三）-索引、视图、触发器

一、索引

1.索引定义

  索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度，建立索引查询速度有数量级提升 ；

类似于图书的目录，方便快速定位，寻找指定的内容；

2.索引的优缺点

• 优点：提高查询数据的速度；
• 缺点：创建和维护索引的时间增加了；

3.索引分类

1，普通索引

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

2，唯一性索引

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

3，全文索引

使用 FULLTEXT 参数可以设置，全文索引只能创建在 CHAR，VARCHAR，TEXT 类型的字段上。主要作用

就是提高查询较大字符串类型的速度；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；

4，单列索引

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

5，多列索引

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

6，空间索引

使用 SPATIAL 参数可以设置空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数

据的效率；只有 MyISAM 引擎支持该索引，Mysql 默认引擎不支持；

4.创建索引

• 创建表的时候创建索引

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

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

                              ....

                              属性名 数据类型

                              [UNIQUE | FULLTEXT | SPATIAL ]  INDEX| KEY

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

);

• 1，创建普通索引

CREATE TABLE mytable(

id INT PRIMARY KEY AUTO_INCREMENT,

bookId INT,

bookName VARCHAR(20) NOT NULL,

price DECIMAL(6,2),

INDEX(bookId)   //创建普通索引

//UNIQUE INDEX (bookId)   //创建唯一性索引

//UNIQUE INDEX bokId(bookId)   //创建唯一性索引并起别名为bokId

//UNIQUE INDEX bokId(bookId，id)   //创建多列索引并起别名为bokId

);

• 2，创建唯一性索引
• 3，创建全文索引（mysql默认搜索引擎不支持）
• 4，创建单列索引 
• 5，创建多列索引
• 6，创建空间索引（不支持）
• 在已经存在的表上创建索引

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

如：

CREATE INDEX bookId ON mytable(bookId); //创建普通索引
CREATE UNIQUE INDEX bookId ON mytable(bookId); //创建唯一索引
CREATE UNIQUE INDEX bookId_price ON mytable(bookId,price); //创建多列索引

• 用 ALTER TABLE 语句来创建索引

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

如：
ALTER TABLE mytable ADD INDEX index_bookId(bookId); //创建普通索引
ALTER TABLE mytable ADD UNIQUE INDEX index_bookId(bookId); //创建唯一索引
ALTER TABLE mytable ADD UNIQUE INDEX index_bookId_price(bookId,price); //创建多列索引

5.删除索引

DROP INDEX 索引名 ON 表名 ； 
如：
drop index index_bookId_price on mytable;

二、视图

第一节：视图的引入

• 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 表示更新视图时，要 满足该视图本身的定义条件即可；

如：

创建视图：
create view v1 as select * from mytable where bookId >30;
查询使用：
select * from v1;

 

第四节：查看视图

• 4.1 DESCRIBE 视图名称； //查看视图字段属性信息
• 4.2 SHOW TABLE STATUS LIKE ‘视图名称’；// 查看视图基本信息，因为视图是虚表，故查询结果字段都为空；此sql也可以查询普通表的基本信息；后面没有like指定视图时表示查询库中所有表和视图的基本信息
• 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 ]；

create or replace  view v1 as select * from mytable where bookId < 30;

• 5.2 ALTER 语句修改视图

ALTER  [ ALGORITHM ={ UNDEFINED | MERGE | TEMPTABLE }]

             VIEW 视图名 [( 属性清单 )]

             AS SELECT 语句

             [ WITH [ CASCADED | LOCAL ] CHECK OPTION ]；

 

alter view v1 as select * from mytable;

第六节：更新视图

更新视图是指通过视图来插入（INSERT）、更新（UPDATE）和删除（DELETE）表中的数据。因为视图是一个虚

拟的表，其中没有数据。通过视图更新时，都是转换基本表来更新。更新视图时，只能更新权限范围内的数据。

超出了范围，就不能更新。

6.1 插入(INSERT)     

CREATE VIEW v1(b,p) AS select bookId,bookName from mytable;
INSERT into v1 values(44,'think in java');
--插入视图，再次查看视图v1时会看到插入的数据，视图操作实际是对表的操作，可以看到mytable表中增加了一条数据，但是其他没有插入的字段则是显示默认值

6.2 更新(UPDATE)

update v1 set b=8,p='设计模式' where b=44

6.3 删除(DELETE)

delete from v1 where b=44;

第七节：删除视图

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

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

如： DROP VIEW  IF EXISTS  v1;

三、触发器

第一节：触发器的引入

触发器（TRIGGER）是由事件来触发某个操作。这些事件包括 INSERT 语句、UPDATE 语句和 DELETE 语句。

当数据库系统执行这些事件时，就会激活触发器执行相应的操作。

第二节：创建与使用触发器

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

CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件

                              ON 表名  FOR EACH ROW 事件发生时要执行的语句

如：CREATE TRIGGER trig_book AFTER INSERT

             ON t_book FOR EACH ROW

             UPDATE t_bookType SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id;

• 表示t_book表中国执行插入操作时触发t_bookType的更新操作，将bookNum加1
• 注：new是中间量，表示插入（更新）时的那条数据，如果是删除时用old，表示删除的那条数据

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

CREATE TRIGGER 触发器名  BEFORE | AFTER 触发事件

                              ON 表名  FOR  EACH  ROW  

                              BEGIN  

                                   执行语句列表  

                              END

如：

DELIMITER |

CREATE TRIGGER trig_book2 AFTER DELETE

    ON t_book FOR EACH ROW

    BEGIN

       UPDATE t_bookType SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;

       INSERT INTO t_log VALUES(NULL,NOW(),'在book表里删除了一条数据');

       DELETE FROM t_test WHERE old.bookTypeId=t_test.id;

    END

|

DELIMITER ;

• 注：DELIMITER | 包裹起来避免触发多条sql时遇到分号中断执行的情况

第三节：查看触发器

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

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

第四节：删除触发器

DROP TRIGGER 触发器名；