Mysql平台下的SQL交互操作(2)

五、索引
1.索引分类：
可分为
普通索引(INDEX/KEY):是最基本的索引类型；
唯一性索引(UNIQUE):索引列值必须是唯一的，但可以是空值；
主键索引(PRIMARY KEY):索引值不能为空，其余同上；
聚簇索引:一个表只能有一个该索引，它的索引顺序就是数据存储的物理存储数据，这样可以使索引值相近的元组所存储的物理位置也很进。
全文索引(FULLTEXT):只能创建在数据类型为VARCHAR或TEXT的列上。
也可分为
单列索引：一个索引只包含表中的一列。
组合索引：在表的多个列上创建的一个索引，遵循左前缀法制。
2.查看索引：
语法格式：
SHOW INDEX/INDEXS/KEYS FROM/IN tb_name (FROM/IN db_name);
例：查看tb_student表中所有索引的信息：

mysql> SHOW INDEX FROM tb_student\G;
*************************** 1. row ***************************
        Table: tb_student
   Non_unique: 0#表明是唯一性索引，否则值为1
     Key_name: id
 Seq_in_index: 1
  Column_name: id
    Collation: A#表示以升序索引
  Cardinality: 7
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.12 sec)

3.创建索引：
1).CREATE TABLE语句：在创建表的同时创建该表的索引。
语法格式：
CREATE TABLE tb_name(colname data_type)
(CONSTRAINT index_name) (UNIQUE) (INDEX/KEY)
(index_name(index_col_name(length))) (ASC/DESC)
例：建立一张表tb_student1的同时在name上建立普通索引

mysql> CREATE TABLE tb_student1
    -> (
    -> id INT(10) NOT NULL,
    -> name VARCHAR(15) NOT NULL,
    -> sex VARCHAR(5),
    -> birthday DATE,
    -> nation VARCHAR(15),
    -> INDEX(name)
    -> )ENGINE=InnoDB;
Query OK, 0 rows affected (0.12 sec)

mysql> SHOW INDEX FROM tb_student1\G;
*************************** 1. row ***************************
        Table: tb_student1
   Non_unique: 1
     Key_name: name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)

2).CREATE INDEX语句：在一个已存在的表上创建索引。
语法格式：
CREATE (UNIQUE) INDEX index_name
ON tb_name(col_name(length) (ASC/DESC))
例：在tb_student表中建立一个按name字段值前三个字符降序的索引

mysql> CREATE INDEX index_name ON tb_student(name(3) DESC);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

3).ALTER TABLE语句：与CREATE TABLE语句类似。
语法格式：
ALTER TABLE tb_name ADD (UNIQUE/FULLTEXT) (INDEX/KEY) index_name (col_name(length) (ASC/DESC))
例：在tb_course表的coursename列上创建一个普通索引

mysql> ALTER TABLE tb_course ADD INDEX index_coursename(coursename);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

4.删除索引：
语法格式：
DROP INDEX index_name ON tb_name
或
ALTER TABLE tb_name DROP INDEX index_name;

六、视图(具体概念略)
1.创建视图 ：
语法格式：
CREATE(OR REPLACE)
VIEW view_name (column_list)
AS SELECT_statement
(WITH (CASCADED/LOCAL) CHECK OPTION)
其中，OR REPLACE子句用于替换数据库中已有的同名视图，但需要在该视图上具有DROP权限；view_name (column_list)为指定的视图名称和列名称；SELECT_statement用于指定创建视图的SELECT语句；WITH (CASCADED/LOCAL) CHECK OPTION用于指定在可更新视图上的修改需要符合上一句的条件，其中中有两个参数，默认第一个参数CASCADED表示对所有视图进行检查，第二个表示只对定义的视图进行检查。
例：创建视图v_student,要求该视图中包含tb_student表中所有性别为boy的信息，并且要求今后对该视图的修改都必须符合性别为boy这个条件。

mysql> CREATE OR REPLACE VIEW v_student
    -> AS
    -> SELECT * FROM tb_student WHERE sex='boy'
    -> WITH CHECK OPTION;
Query OK, 0 rows affected (0.36 sec)

2.修改视图定义：
语法格式：
ALTER VIEW view_name(column_list)
AS SELECT_statement
(WITH (CASCADED/LOCAL) CHECK OPTION)
例：修改上一张视图的定义，要求满足性别为boy的同时国籍为US。

mysql> ALTER VIEW v_student
    -> AS SELECT * FROM tb_student
    -> WHERE sex='boy' AND nation='US'
    -> WITH CHECK OPTION;
Query OK, 0 rows affected (0.02 sec)

3.查看视图定义
语法格式：
SHOW CREATE VIEW view_name;
例：查看v_student视图的定义

mysql> SHOW CREATE VIEW v_student\G;
*************************** 1. row ***************************
                View: v_student
         Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_student` AS select `tb_student`.`id` AS `id`,`tb_student`.`name` AS `name`,`tb_student`.`sex` AS `sex`,`tb_student`.`birthday` AS `birthday`,`tb_student`.`nation` AS `nation` from `tb_student` where ((`tb_student`.`sex` = 'boy') and (`tb_student`.`nation` = 'US')) WITH CASCADED CHECK OPTION
character_set_client: utf8
collation_connection: utf8_general_ci
1 row in set (0.37 sec)

4.更新视图数据：
包括INSERT、UPDATE、DELETE三个语句，用法与更新表数据相同，另外需满足该视图中的行和基础表中的行之间有一对一的关系这一条件。
5.查询视图数据
例：查询视图v_student中id为100003的学生的姓名及生日

mysql> SELECT name,birthday FROM v_student
    -> WHERE id='100003';
+------+------------+
| name | birthday   |
+------+------------+
| Jack | 1981-06-24 |
+------+------------+
1 row in set (0.00 sec)

5.删除视图：
语法格式：
DROP VIEW (IF EXISTS) view_name(,view_name2)…
可见，能同时删除多个视图。
6.使用视图的注意事项：
创建视图必须有足够的访问权限；
对于可创建的视图数目没有限制；
视图可以嵌套；
ORDER BY子句可以用在视图中，但如果该视图中的SELECT语句中也有ORDER BY子句，那么视图中的ORDER BY子句会被覆盖掉；
视图不能索引，也不能有关联的触发器、默认值；
视图可以和表一起用。

七、触发器(TRIGGER)
1.概念：是一个被指定关联到一个表的数据库对象，当对一个表的特定事件出现时，它将会被激活。
2.创建触发器:
语法格式：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tb_name FOR EACH ROW trigger_body
其中，trigger_name为创建的触发器名称；trigger_time为触发时刻，有BEFORE和AFTER两个选项，用于表示触发器是在激活它的语句之前或之后触发；trigger_event语句用于指定激活触发器的语句种类，分为INSERT、UPDATE、DELETE三种触发器；FOR EACH ROW声明用来指定对于受触发事件影响的每一行都要激活触发器的动作；trigger_body为触发器动作的主体，包含触发器激活时将要执行的Mysql语句。
例：在tb_student表中创建触发器insert_trigger，用于每次向表中插入一行数据时将学生变量str的值设置为"one student added!"。
首先创建触发器，并查看变量str中的原值

mysql> CREATE TRIGGER insert_trigger AFTER INSERT
    -> ON tb_student FOR EACH ROW SET@str='one student added!';
Query OK, 0 rows affected (0.37 sec)

mysql> SELECT @str;
+------+
| @str |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

然后向表tb_student中插入一行数据并再次查看变量str的值

mysql> INSERT INTO tb_student
    -> VALUES(NULL,'Geoge','boy','1983-04-19','US');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT @str;
+--------------------+
| @str               |
+--------------------+
| one student added! |
+--------------------+
1 row in set (0.00 sec)

可以看到触发器被激活。
3.删除触发器：
语法格式：
DROP TRIGGER (IF EXISTS) (db_name.)trigger_name;
注意：当删除一个表的同时也会自动删除该表上的触发器；另外触发器不能更新或覆盖，只能删除后重新创建达到修改的目的。

八、事件
1.概念：需要在指定的时刻才执行的某些特定任务就是事件。
2.创建事件：
语法格式：
GREATE EVENT (IF NOT EXISTS) event_name
ON SCHEDULE schedule (ENABLE/DISABLE/DISABLE ON SLAVE)
DO event_body
其中schedule的语法格式为：
AT timestamp (+INTERVAL interval)…
/EVERY interval (STARTS timestamp (+INTERVAL interval)…)
(ENDS timestamp (+INTERVAL interval)…)
interval的语法格式为:
YEAR/QUARTER/MONTH/DAY/HOUR/MINUTE/WEEK/SECOND/YEAR_MONTH/…
其中schedule为时间调度，用于指定事件何时发生或每隔多久发生一次，对应AT和EVERY子句；event_body部分用于指定事件启动时所要执行的代码，如果包含多条语句可以使用BEGIN…END复合结构；ENABLE/DISABLE/DISABLE ON SLAVE可选项表示事件是活动的或关闭的或在从机中是关闭的，而默认是活动的。
例：创建一个事件event_insert用于每天向表tb_student中插入一条数据，该事件开始与明天并且在今年年末结束。
在这之前要了解DELIMITER 语句的用法，该语句指定了SQL一条语句的结束符号，比如：

mysql> DELIMITER $$
mysql> SELECT * FROM tb_student;
    -> $$
+--------+--------+------+------------+--------+
| id     | name   | sex  | birthday   | nation |
+--------+--------+------+------------+--------+
| 100001 | Bob    | boy  | 1980-03-15 | US     |
| 100002 | LiMing | boy  | 1982-11-16 | China  |
| 100003 | Jack   | boy  | 1981-06-24 | US     |
| 100004 | Rose   | girl | 1982-09-07 | UK     |
| 100005 | Tony   | boy  | 1983-04-17 | UK     |
| 100006 | Obama  | boy  | 1982-02-03 | US     |
| 100008 | Peiqi  | girl | NULL       | NULL   |
| 100009 | Geoge  | boy  | 1983-04-19 | US     |
+--------+--------+------+------------+--------+
8 rows in set (0.00 sec)

可以看出DELIMITER语句将结束符号改为了$$,若想改回分号结尾只需

mysql> DELIMITER ;

利用DELIMITER语句可以有效控制复合语句的结束位置。接下来开始创建这个事件

mysql> DELIMITER $$
mysql> CREATE EVENT IF NOT EXISTS event_insert
    -> ON SCHEDULE EVERY 1 DAY
    -> STARTS CURDATE()+INTERVAL 1 DAY
    -> ENDS '2019-12-31'
    -> DO
    -> BEGIN
    -> IF YEAR(CURDATE()) < 2020 THEN
    -> INSERT INTO tb_student
    -> VALUES(NULL,'New','boy','1982-09-25','UK');
    -> END IF;
    -> END $$
Query OK, 0 rows affected (0.35 sec)

3.修改事件：
语法格式：
ALTER EVENT event_name
(ON SCHEDULE schedule)#指定时间调度
(RENAME TO new_event_name)#重命名事件
(ENABLE/DISABLE/DISABLE ON SLAVE)#事件的关闭与开启
(DO event_body)
例1：临时关闭刚才创建的事件

Query OK, 0 rows affected (0.01 sec)

例2：将这个事件改名为event_insert2

mysql> ALTER EVENT event_insert RENAME TO event_insert2;
Query OK, 0 rows affected (0.00 sec)

4.删除事件：
语法格式：
DROP EVENT (IF EXISTS) event_name

九、存储过程
1.概念：是一组为了完成某些特定功能的SQL语句集，实质为一段存放在数据库中的代码。
2.优点：
可增强SQL语言的功能和灵活性；良好的封装性；高性能；可减少网络流量；可作为一种安全机制来确保数据库的安全性和数据的完整性。
3.创建存储过程：
语法格式：
CREATE PROCEDURE sp_name(proc_parameter(,…))
(characteristic) routine_body#存储过程的主体部分
其中,proc_parameter的格式为：
(IN/OUT/INOUT) param_name type#表示存储过程的参数(分为输入、输出、输入输出)列表
characteristic的格式为：
COMMENT ‘string’#用于对存储过程的描述
/LANGUAGE SQL#指明该过程的编写语言为SQL语言
/(NOT) DETERMINISTIC#表明该过程对同样的参数产生(不相同)相同的结果
/(CONTAINS SQL/NO SQL/READS SQL DATA/MOVIFIES SQL DATA)#表示过程是否包含读或写的SQL语句
/SQL SECURITY (DEFINER/INVOKER)#指定该过程的许可我为用户或调用者
例：创建一个存储过程用于实现给定表tb_student中一个学生的学号即可修改表tb_student中该学生的性别为一个指定的性别。

mysql> DELIMITER $$
mysql> CREATE PROCEDURE sp_update_sex(IN sid CHAR(10),IN ssex CHAR(5))
    -> BEGIN
    -> UPDATE tb_student SET sex=ssex WHERE id=sid;
    -> END $$
Query OK, 0 rows affected (0.18 sec)

4.存储过程体(routine_body):
常用以下语法元素(篇幅过长不详细介绍它们的用法)：
局部变量DECLARE
SET语句
SELECT … INTO语句
流程控制语句
游标
例：创建一个存储过程用于计算表tb_student中数据行的行数。

mysql> DELIMITER $$
mysql> CREATE PROCEDURE sp_count_rows(OUT ROWS INT)
    -> BEGIN
    -> DECLARE sid CHAR;
    -> DECLARE FOUND BOOLEAN DEFAULT TRUE;
    -> DECLARE cur CURSOR FOR SELECT id FROM tb_student;
    -> DECLARE CONTINUE HANDLER FOR NOT FOUND
    -> SET FOUND=FALSE;
    -> SET ROWS=0;
    -> OPEN cur;
    -> FETCH cur INTO sid;
    -> WHILE FOUND DO
    -> SET ROWS=ROWS+1;
    -> FETCH cur INTO sid;
    -> END WHILE;
    -> CLOSE cur;
    -> END $$
Query OK, 0 rows affected (0.00 sec)

然后调用该存储过程：

mysql> CALL sp_count_rows(@rows);
Query OK, 0 rows affected (0.00 sec)

最后查看调用存储过程后的结果：

mysql> SELECT @rows;
+-------+
| @rows |
+-------+
|     8 |
+-------+
1 row in set (0.00 sec)

5.调用存储过程：
语法格式：
CALL sp_name(parameter);
括号内表示可能要使用的参数
6.删除存储过程：
语法格式：
DROP PROCEDURE FUNCTION (IF EXISTS) sp_name;

十、存储函数
1.存储函数与存储过程之间的区别：
存储函数不能拥有输出参数；可以直接调用存储函数，而调用存储过程需要CALL语句；存储函数中必须包含一条RETURN语句，而存储过程中没有这样的语句。
2.创建存储函数：
语法格式：
CREATE FUNCTION sp_name(param_name type(,…))
RETURNS type#声明存储函数返回值的数据类型
routine_body#存储函数的主体部分
例：创建这样一个存储函数，要求该函数能根据课程号返回课程名称，若没有该课程号则返回“There is no course!"

mysql> CREATE FUNCTION sp_search(cid CHAR(10))
    -> RETURNS CHAR(15)
    -> DETERMINISTIC
    -> BEGIN
    -> DECLARE CNAME CHAR(15);
    -> SELECT coursename INTO CNAME FROM tb_course WHERE courseid=cid;
    -> IF CNAME IS NULL THEN
    -> RETURN(SELECT 'There is no course!');
    -> ELSE RETURN(SELECT coursename FROM tb_course WHERE courseid=cid);
    -> END IF;
    -> END $$
Query OK, 0 rows affected (0.36 sec)

3.调用存储函数：
语法格式：
SELECT sp_name(func_parameter(,…))
例：调用刚刚创建的函数：

mysql> SELECT sp_search('2002');
+-------------------+
| sp_search('2002') |
+-------------------+
| Math              |
+-------------------+
1 row in set (0.00 sec)

4.删除存储函数：
语法格式：
DROP FUNCTION (IF EXISTS) sp_name;