MySQL学习之第五章-高级部分

五、高级部分

1、索引

  1. 含义

    索引（index）是帮助MySQL高效获取数据的一种有序的数据结构。拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

  2. 特点

    优势：

    • 类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本
    • 通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗

    劣势：

    • 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空间的
    • 虽然索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息

  3. 分类及创建：

    创建索引的SQL语句：

    CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

/*
1.unique|fulltext为可选参数，分别表示唯一索引、全文索引
2.index和key为同义词，两者作用相同，用来指定创建索引
3.col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4.index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5.length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6.asc或desc指定升序或降序的索引值存储
*/

-- 删除索引
DROP INDEX index_name ON table_name

    1. 普通索引

      ​ 是最基本的索引，它没有任何限制。它有以下几种创建方式：

      -- 直接创建索引
CREATE INDEX index_name ON table(column(length))

-- 修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))

-- 创建表时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))
)

    2. 唯一索引

      ​ 索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

      -- 直接创建唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length))

-- 修改表结构的方式添加索引
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))

-- 创建表的时候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

    3. 主键索引

      ​ 是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引

      CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

    4. 组合索引

      ​ 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合

      -- 修改表结构添加组合索引
ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

    5. 全文索引

      ​ 主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。

      -- 直接创建全文索引
CREATE FULLTEXT INDEX index_content ON table_name(column)

-- 修改表结构添加全文索引
ALTER TABLE table_name ADD FULLTEXT index_content(column)

-- 创建表的时添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

  4. 使用索引时，有以下一些技巧和注意事项：

    1. 索引不会包含有null值的列
      只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

    2. 使用短索引
      对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    3. 索引列排序
      查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

    4. like语句操作
      一般情况下不推荐使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

    5. 不要在列上进行运算
      这将导致索引失效而进行全表扫描，例如

      SELECT * FROM table_name WHERE YEAR(column_name)<2017;

    6. 不使用not in和<>操作

2、视图

  1. 含义

    MySQL在5.1以后推出了视图（VIEW），本身是一个虚拟表，它的数据来自于表，通过执行时动态生成

  2. 特点

    • 简化sql语句
    • 提高了sql的重用性
    • 保护基表的数据，提高了安全性

  3. 语法

    -- 创建
CREATE VIEW 视图名
AS
查询语句;

-- 修改
#方式一：
CREATE OR REPLACE VIEW 视图名
AS
查询语句;

#方式二：
ALTER VIEW 视图名
AS
查询语句;

-- 删除
DROP VIEW 视图1,视图2,...;


-- 查看视图
#方式一：
DESC 视图名;

#方式二：
SHOW CREATE VIEW 视图名;

  4. 注意事项

    视图一般用于查询的，而不是更新的，所以具备以下特点的视图都不允许更新：

    • 包含分组函数、group by、distinct、having、union、join
    • 常量视图
    • where后的子查询用到了from中的表
    • 用到了不可更新的视图

3、存储过程

1.语法

  1. 含义

    存储过程，类似于Java中的方法，它是一组预先编译好的SQL语句的集合，理解成批处理语句

  2. 特点

    • 简化sql语句
    • 提高了sql的重用性
    • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率

  3. SQL语法

    -- 创建
#标准格式如下：
DELIMITER $$
CREATE PROCEDURE 存储过程名(参数模式 参数名 参数类型,...)
BEGIN
	存储过程体（一组合法的SQL语句）
END $$
DELIMITER ;

#参数模式in：参数类型是输入的
#参数模式out：参数类型是输出的
#参数模式inout：参数类型既可以输入也可以输出

#调用in模式的参数： CALL sp1('Hello,World');
#调用out模式的参数： SET @name; CALL sp1(@name); SELECT @name;
#调用inout模式的参数： SET @name=值; CALL sp1(@name); SELECT @name;


-- 先删除，再创建
-- 先删除，再创建
-- 先删除，再创建


-- 删除
DROP PROCEDURE 存储过程名;

-- 查看 
SHOW CREATE PROCEDURE 存储过程名;

2.变量

  1. 系统变量

    1. 全局变量

      ​ 服务器层面上的，必须拥有super权限才能为系统变量赋值，作用域为整个服务器，也就是针对于所有连接（会话）有效，但不能跨重启。

      1、查看所有系统变量
SHOW GLOBAL VARIABLES;

2、查看满足条件的部分系统变量
SHOW GLOBAL VARIABLES LIKE '%char%';

3、查看指定的系统变量的值
SELECT @@global 系统变量名;

4、为某个系统变量赋值
方式一： SET GLOBAL 系统变量名=值;
方式二： SET @@global 系统变量名=值;

    2. 会话变量

      ​ 服务器为每一个连接的客户端都提供了系统变量，作用域为当前的连接（会话）

      1、查看所有系统变量
SHOW 【 SESSION 】 VARIABLES;

2、查看满足条件的部分系统变量
SHOW 【 SESSION 】 VARIABLES LIKE '%char%';

3、查看指定的系统变量的值
SELECT 【 SESSION 】系统变量名;

4、为某个系统变量赋值
SET 【 SESSION 】系统变量名=值;

  2. 自定义变量

    1. 用户变量

      ​ 针对于当前连接（会话）生效，可以在begin end里面，也可以放在外面

      1、声明并赋值
SET @变量名=值; 或
SET @变量名:=值; 或
SELECT @变量名:=值;

2、更新值
方式一：
	SET @变量名=值; 或
	SET @变量名:=值; 或
	SELECT @变量名:=值;
方式二：
	SELECT xxx INTO @变量名 FROM 表;
	
3、查看值
SELECT @变量名;

    2. 局部变量

      ​ 仅仅在定义它的begin end中有效的变量，只能放在begin end中，而且只能放在第一句

      1、声明并赋值
DECLARE 变量名 类型 【 DEFAULT 值 】;

2、更新值
方式一：
	SET 变量名=值; 或
	SET 变量名:=值; 或
	SELECT @变量名:=值;
方式二：
	SELECT xxx INTO 变量名 FROM 表;
	
3、查看值
SELECT 变量名;

3.分支结构

  1. if结构

    ​ 只能放在begin end中

    IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
ELSEIF 条件3 THEN 语句3;
....
【 ELSE 语句n; 】
END IF;


-- 示例
#创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER $$
CREATE FUNCTION test_if(score FLOAT) RETURNS CHAR
BEGIN
	DECLARE ch CHAR DEFAULT 'A';
	IF score>90 THEN SET ch='A';
	ELSEIF score>80 THEN SET ch='B';
	ELSEIF score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END IF;
	RETURN ch;
END $$
DELIMITER ;

#函数调用
SELECT test_if(87)$
  2. case结构

    ​ 只能放在begin end中

    语法1：
CASE 表达式或字段
WHEN 值1 THEN 语句1;
WHEN 值2 THEN 语句2;
...
【 ELSE 语句n; 】
END 【 CASE 】;

语法2：
CASE 
WHEN 条件1 THEN 语句1;
WHEN 条件2 THEN 语句2;
...
【 ELSE 语句n; 】
END 【 CASE 】;

-- 示例
#创建函数，实现传入成绩，如果成绩>90,返回A，如果成绩>80,返回B，如果成绩>60,返回C，否则返回D
DELIMITER $$
CREATE FUNCTION test_case (score FLOAT) RETURNS CHAR 
BEGIN
	DECLARE ch CHAR DEFAULT 'A' ;
	
	CASE 
	WHEN score>90 THEN SET ch='A';
	WHEN score>80 THEN SET ch='B';
	WHEN score>60 THEN SET ch='C';
	ELSE SET ch='D';
	END CASE;
	
	RETURN ch;
END $$
DELIMITER ;

#函数调用
SELECT test_case(56);

  3. 循环结构

    名称 特点 位置
    while 先判断后执行 begin end中
    repeat 先执行后判断 begin end中
    loop 没有条件的死循环 begin end中 
    -- while循环语法
Label:while loop_condition
do
	loop_list
End while label;

-- 示例=========
#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
#删除过程
DROP PROCEDURE IF EXISTS test_while;

#定义过程
DELIMITER $$
CREATE PROCEDURE test_while(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:WHILE i<=insertCount DO
		#LEAVE代表Java中的break关键字；ITERATE代表Java中的continue关键字
		IF i>20 THEN LEAVE a;
		END IF;
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		SET i=i+1;
	END WHILE a;
END $$
DELIMITER ;

#调用过程
CALL test_while(100);


-- ===========================================================
-- repeat循环语法
Label:repeat
	loop_list
Until end_condition
end repeat label;

-- 示例=========
#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
#删除过程
DROP PROCEDURE IF EXISTS test_repeat;

#定义过程
DELIMITER $$
CREATE PROCEDURE test_repeat(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:REPEAT 
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		SET i=i+1;
	UNTIL i>20		
	END REPEAT a;
END $$
DELIMITER ;

#调用过程
CALL test_repeat(100);


-- ===========================================================
-- loop循环语法
Label:loop
	loop_list
End loop label;

-- 示例=====
#批量插入，根据次数插入到admin表中多条记录，如果次数>20则停止
#删除过程
DROP PROCEDURE IF EXISTS test_loop;

#定义过程
DELIMITER $$
CREATE PROCEDURE test_loop(IN insertCount INT)
BEGIN
	DECLARE i INT DEFAULT 1;
	a:LOOP 
		#LEAVE代表Java中的break关键字；ITERATE代表Java中的continue关键字
		IF i>20 THEN LEAVE a;
		END IF;
		INSERT INTO admin(username,`password`) VALUES(CONCAT('xiaohua',i),'0000');
		SET i=i+1;
	END LOOP a;
END $$
DELIMITER ;

#调用过程
CALL test_loop(100);

4、用户定义函数

1.含义

函数存储着一系列SQL语句，调用函数就是一次性执行这些语句。所以函数可以降低语句重复。前面也讲到了MySQL自定义的函数，用户也可以自定义函数，其实函数就是一个有返回值的存储过程。

2. SQL语法

-- 创建
DELIMITER $$
CREATE FUNCTION 函数名(参数名 参数类型) 
RETURNS 返回类型
BEGIN
	函数过程体（一组合法的SQL语句）
END $$
DELIMITER ;

-- 修改
-- 先删除，再创建
-- 先删除，再创建
-- 先删除，再创建

-- 删除
DROP FUNCTION 函数名；

-- 查看
SHOW CREATE FUNCTION 函数名;

-- 调用
SELECT 函数名(实参列表);

函数和存储过程的区别：

  • 一般来说，存储过程实现的功能要复杂一点，而函数实现的功能针对性比较强。
  • 函数只能返回一个变量；而存储过程可以返回多个。存储过程的参数可以有IN,OUT,INOUT三种类型，而函数只能有IN类，如果只有一个返回值，用存储函数，否则，一般用存储过程。。存储过程声明时不需要返回类型，而函数声明时需要描述返回类型，且函数体中必须包含一个有效的RETURN语句。
  • 存储过程一般是作为一个独立的部分来执行（ EXECUTE 语句执行），而函数可以作为SQL语句的一个部分来调用。

5、触发器

1.含义

MySQL包含对触发器的支持。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。

2.语法

在MySQL中，触发器语法及示例如下：

-- 创建
DELIMITER $$
CREATE TRIGGER 触发器名称 
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名称 
【 FOR EACH ROW 】--行级触发器
BEGIN
	触发器过程体（一组合法的SQL语句）
END $$
DELIMITER ;

-- 修改
-- 先删除，再创建
-- 先删除，再创建
-- 先删除，再创建

-- 删除
DROP TRIGGER 触发器名称;

-- 查看
SHOW TRIGGERS;


/*
假设系统中有两个表：
班级表 class(班级号 classID, 班内学生数 stuCount)
学生表 student(学号 stuID, 所属班级号 classID)
要创建触发器来使班级表中的班内学生数随着学生的添加自动更新，代码如下：
*/
DELIMITER $$
create trigger tri_stuInsert after insert
on student for each row
begin
declare c int;
set c = (select stuCount from class where classID = new.classID);
update class set stuCount = c + 1 where classID = new.classID;
end $$
DELIMITER ;

6、事件

1.含义

MySQL在5.1以后推出了事件调度器（Event Scheduler），与triggers的事件触发不同，event类似与linux crontab计划任务，用于时间触发

2.特点

  • 事件是一组SQL集合，简单说就是mysql中的定时器，时间到了就执行
  • 事件由一个特定的线程来管理的，也就是所谓的事件调度器，但是事件不能直接调用，通过单独或调用存储过程使用，在某一特定的时间点，触发相关的SQL语句或存储过程

3.语法

MySQL事件调度器event_scheduler负责调用事件，它默认是关闭的。这个调度器不断地监视一个事件是否要调用， 要创建事件，必须打开调度器。

#开启事件调度器（任意一个都可以）
SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1; 

#关闭事件调度器（任意一个都可以）
SET GLOBAL event_scheduler = OFF; 
SET @@global.event_scheduler = OFF; 
SET GLOBAL event_scheduler = 0; 
SET @@global.event_scheduler = 0;


DELIMITER $
CREATE EVENT 事件名称
ON SCHEDULE 执行时间和频率
-- 执行时间和频率有两种形式AT和EVERY
-- 	使用 AT     关键字只会执行一次，格式如下：
-- 	AT CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
-- 	使用 EVERY  关键字指定时间间隔，格式如下：
-- 	EVERY 1 [HOUR|MONTH|WEEK|DAY|MINUTE|...]

-- 可选项，使用 STARTS 关键字指定开始时间，格式如下：
-- STARTS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'
-- 可选项，使用 ENDS   关键字指定结束时间，格式如下：
-- ENDS CURRENT_TIMESTAMP 或者 'YYYY-MM-DD HH:MM.SS'

-- [ON COMPLETION [NOT] PRESERVE] 
-- 可选项，设定event的生命
--		ON COMPLETION NOT PRESERVE ：即计划任务执行完毕后自动drop该事件（默认）
--		ON COMPLETION PRESERVE     ：即计划任务执行完毕后不会drop该事件

-- [ENABLE | DISABLE] 
-- 可选项，设定event的状态
--		ENABLE	：表示系统尝试执行这个事件（默认）
--		DISABLE	：表示系统尝试关闭这个事件

-- [COMMENT 'comment'] 
-- 可选项，设定event的备注
DO
BEGIN
        /* 在这里写SQL语句或者调用存储过程 */
END$
DELIMITER ;


-- 修改
-- 先删除，再创建
-- 先删除，再创建
-- 先删除，再创建

-- 删除
DROP EVENT 事件名称;

-- 查看
SHOW EVENTS;

-- 调用
#启用事件
ALTER EVENT 事件名称 ENABLE;

#禁用事件
ALTER EVENT 事件名称 DISABLE;

4.注意事项

  1. 默认创建事件存储在当前库中，也可显示指定事件创建在哪个库中
  2. 通过show events只能查看当前库中创建的事件
  3. 事件执行完即释放，如立即执行事件，执行完后，事件便自动删除，多次调用事件或等待执行事件可以查看到
  4. 如果两个事件需要在同一时刻调用，mysql会确定调用他们的顺序，如果要指定顺序，需要确保一个事件至少在另一个事件1秒后执行
  5. 对于递归调度的事件，结束日期不能在开始日期之前
  6. select可以包含在一个事件中，然而他的结果消失了，就好像没执行过
posted @ 2021-06-01 21:49  XQ-Yang  阅读(56)  评论(0)  编辑  收藏  举报