MySQL 高级

MySQL 高级

1.索引

1.1 概述：

​ 索引（index）是帮助MySQL进行高效获取数据的数据结构（有序）。在数据之外，数据库系统还维 护着满足特定查找算法的数据结果，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上 实现高级查找算法，这种数据结构就是索引 。

1.2 索引的优势

优势：

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

劣势：

1. 索引实际上 也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，因为索引列也是 要占用大量空间，所以它需要存储在我们的磁盘上，并消耗一定的存储空间。 

2. 虽然索引大大的提高了查询效率，但是，同时也降低了更新表的速度，比如对表进行 insert,update,delete操作时，不仅要保存数据，还要更新索引文件。 

1.3 索引的数据结构

​ 索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的，所以每种存储引擎的索引都 不一定完全相同，也不是所有存储引擎都支持所有的索引类型。

​ MySQL 目前提供了4中索引：

• BTREE 索引 ： 最常见的索引类型，大部分索引都支持 B 树索引。
• HASH 索引：只有Memory引擎支持 ， 使用场景简单 。
• R-tree 索引（空间索引）：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据 类型，通常使用较少，不做特别介绍。
• Full-text （全文索引） ：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引， InnoDB从Mysql5.6版本开始支持全文索引。

1.4 索引分类

1.Normal 普通索引 ：表示普通索引，大多数情况下都可以使用

2.Unique 唯一索引 ：表示唯一的，不允许重复的索引，如果该字段信息保证不会重复例如身份证 号用作索引时，可设置为unique 其中 primary key 是一种特殊的唯一索引

3.FullText 全文索引 ：表示全文搜索，在检索长文本的时候，效果最好，短文本建议使用Index。如 果在检索的时候数据量比较大，可以先将数据放入一个没有全局索引的表中，然后在用Create Index创建的Full Text索引，要比先为一张表建立Full Text然后在写入数据要快的很多。

4.SPATIAL 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有 4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行 扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为 NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建（了解）。

5.单值索引、组合索引：单值索引即一个索引只包含单个列，一个表可以有多个单列索引。组合索引 即一个索引包含多个列。

2.视图

2.1 视图概述

​ 视图（View）是一种虚拟存在的表。视图并不在数据库中实际存在，行和列数据来自定义视图的查 询中使用的表，并且是在使用视图时动态生成的。通俗的讲，视图就是一条SELECT语句执行后返回的 结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。

2.2 视图的特点：

• 简单：使用视图的用户完全不需要关心底层使用了哪些表、表与表之间的关联条件和筛选条件是怎 样的，对用户来说视图是已经过滤好的复合条件的结果集。
• 安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某 个列，但是通过视图就可以简单的实现。
• 数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影 响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

2.3 创建和修改视图

创建视图的语法：

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS select 语句
[WITH [CASCADED | LOCAL] CHECK OPTION]

案例：

-- 显示城市和对应的国家信息
SELECT
	t1.* ,t2.country_name
FROM
	city t1,country t2
WHERE
	t1.country_id = t2.country_id;
	
-- 将该查询结果封装到视图中
CREATE VIEW view_city_country
AS
SELECT
	t1.* ,t2.country_name
FROM
	city t1,country t2
WHERE
	t1.country_id = t2.country_id;
	
-- 查询视图
SELECT * FROM view_city_country;

-- 更新视图
UPDATE view_city_country SET city_name='西安市' WHERE city_id=1;-- 更新视图实际是更
新它所封装的基表中的数据
SELECT * FROM city;-- 基表中的数据已经被修改

修改视图的语法：

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW 视图名称 [(column_list)]
AS 查询语句
[WITH [CASCADED | LOCAL] CHECK OPTION]

2.4 查看视图

查看视图：

​ 方式1：show tables

​ 方式2：图形化界面

删除视图：

drop view 视图名称

3.存储过程 和 函数

3.1 存储过程和函数的概述

​ 存储过程和函数是 事先经过编译并存储在数据库中的一段 SQL 语句的集合，调用存储过程和函数 可以简化数数据库设计与开发的工作，减少数据在数据库和应用服务器之间的传输，因为应用服务器访 问数据库的次数越多，数据传输效率越低，有了存储过程，应用服务器只需要访问一次数据库，就可以 执行存储过程中封装的所有sql语句，对于提高数据处理的效率是有好处的。

​ 存储过程和函数的区别在于函数必须有返回值，而存储过程没有。

3.2 创建存储过程

语法：

CREATE PROCEDURE 存储过程名([存储过程需要的参数列表])
begin
-- 多条SQL语句
end;

案例：

-- 创建一个存储过程
-- 因为在命令行模式下end后要跟；表示存储过程结束，它前面的sql语句也要跟；表示该条语句结束，那么存储过程怎么写呢？
-- 我们可以用一条语句该别结束标记
DELIMITER $ -- 将结束标记修改为$

CREATE PROCEDURE pro_test()
BEGIN
SELECT 'Hello MySQL';
END$

3.3 调用存储过程

语法：

​ call 存储过程名()

CALL pro_test();

3.4 查看存储过程

语法：

-- 查询指定数据库中的所有的存储过程
select name from mysql.proc where db='数据库名';

-- 查询存储过程的状态信息
show procedure status;

-- 查询某个存储过程的定义
show create procedure 存储过程名;

案例：

-- 查询指定数据库中的所有的存储过程
SELECT NAME FROM mysql.`proc` WHERE db='demo_01';

-- 查询存储过程的状态信息
SHOW PROCEDURE STATUS;

-- 查询某个存储过程的定义
SHOW CREATE PROCEDURE pro_test;

3.5 删除存储过程

语法：

​ drop procedure 存储结构名

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

3.6 变量

• DECLARE

  通过 DECLARE 关键字可以定义一个变量，该变量的作用范围只能在BEGIN - END 块中

• 语法

  DECLARE 变量名[变量名2,变量名2...] 数据类型 [DEFAULT 默认值]
  
  

案例：

DELIMITER $

CREATE PROCEDURE pro_test2()
BEGIN
	DECLARE num INT DEFAULT 10;-- 声明变量
	SELECT CONCAT('num的值为：',num);-- concat()函数，将字符串进行拼接
END$
-- 执行存储过程
CALL pro_test2();

• SET

直接赋值使用 SET，可以赋常量或者赋表达式。

• 语法

SET 变量名 = 值或表达式

案例：

-- 变量赋值
DELIMITER $

CREATE PROCEDURE pro_test3()
BEGIN
	DECLARE num INT DEFAULT 0;
	SET num = num + 10;
	SELECT CONCAT('num的值为：',num);
END$
CALL pro_test3();

也可以通过select … into 进行变量赋值

案例：

-- select...into 赋值
DELIMITER $
CREATE PROCEDURE pro_test4()
BEGIN
	DECLARE num INT;
	SELECT COUNT(*) INTO num FROM city;
	SELECT CONCAT('共有',num,'个城市');
END$
-- 调用之后，我们给city表添加一条数据，再次执行存储过程，其值会相应的改变
CALL pro_test4();

3.7 if条件判断

• 语法结构

if
	判断条件
then
	-- 如果成立则执行此处的sql语句
	sql语句
[elseif 判断条件 then sql语句] ...
[else sql语句]
end if;

案例：

-- if 选择结构
/*
需求：根据成绩判断其所属的阶段：优秀，一般，良好
socre>=90 优秀
90>socre>=80 一般
socre<80 良好
*/
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
	DECLARE score INT DEFAULT 95; -- 成绩变量
	DECLARE description VARCHAR(10) DEFAULT ''; -- 所属分段的描述信息
	IF
		score>=90
		THEN
	SET
		description='优秀';
	ELSEIF
		score>=80 AND score<90
	THEN
		SET
		description='一般';
	ELSEIF
		score<80
	THEN
		SET
	description='良好';
  END IF;
SELECT CONCAT('该同学的成绩为：',score,',属于',description);
END$

CALL pro_test5();

3.8 传递参数

​ 上一个案例，成绩是写死在存储过程中的，这样显然是不合理的，所以接下来我们学习传递参数， 让我们在调用存储过程时可以给它传递一个成绩的值，然后让他根据传递的值显示对应的分段信息

• 语法格式：

create procedure 存储过程名([in/out/inout] 参数名 参数类型)
	...
IN : 该参数可以作为输入，也就是需要调用方传入值 , 默认
OUT: 该参数作为输出，也就是该参数可以作为返回值
INOUT: 既可以作为输入参数，也可以作为输出参数

• IN - 输入参数

案例：

-- 传递参数
-- in-输入参数
DELIMITER $
CREATE PROCEDURE pro_test6(IN score INT)
BEGIN
	DECLARE description VARCHAR(10) DEFAULT ''; -- 所属分段的描述信息
	IF
		score>=90
	  THEN
		SET
			description='优秀';
		ELSEIF
			score>=80 AND score<90
		THEN
		  SET
			description='一般';
		ELSEIF
			score<80
		THEN
		  SET
			description='良好';
		END IF;
SELECT CONCAT('该同学的成绩为：',score,',属于',description);
END$

CALL pro_test6(70);

• OUT - 输出参数

/*
需求:根据出入的成绩变量，获取当前成绩所属的分段（返回值）
*/
-- out-输入参数
DELIMITER $
CREATE PROCEDURE pro_test7(IN score INT,OUT description VARCHAR(10))
BEGIN
  IF
	score>=90
   THEN
	SET
		description='优秀';
   ELSEIF
		score>=80 AND score<90
   THEN
	SET
		description='一般';
   ELSEIF
		score<80
   THEN
	SET
   description='良好';
  END IF;
-- SELECT CONCAT('该同学的成绩为：',score,',属于',description);
END$

CALL pro_test7(90,@description); -- @description 用户会话变量
SELECT @description;

注意：

​ @变量名：这种变量叫做用户会话变量，代表这个会话过程中，它都是有效的，一旦断开连接，或会 话窗口关闭，则@变量会被全部释放。

​ @@变量名：系统变量

3.9 case结构

• 语法结构

方式一 :
CASE 所要判断的值
WHEN 是否匹配的值 THEN sql语句 -- 是否匹配的值 就相当于java中 case后面跟的值
[WHEN 是否匹配的值 THEN sql语句]
...
[ELSE sql语句] -- 相当于java中的default
END CASE;
方式二 :
CASE
WHEN 条件表达式 THEN sql语句 -- 条件表达式为真，则执行sql语句
[WHEN 条件表达式 THEN sql语句] ...
[ELSE sql语句]
END CASE;

案例：

/*
给定一个月份，计算该月所在的季度
*/
DELIMITER $
CREATE PROCEDURE pro_test8(IN mon INT)
BEGIN
DECLARE result VARCHAR(10);
	CASE
		WHEN mon>=1 AND mon<=3 THEN
			SET result='第一季度';
		WHEN mon>=4 AND mon<=6 THEN
			SET result='第二季度';
		WHEN mon>=7 AND mon<=9 THEN
			SET result='第三季度';
		WHEN mon>=10 AND mon<=12 THEN
			SET result='第四季度';
	END CASE;
	SELECT CONCAT(mon,'属于',result);
END$

CALL pro_test8(2);

3.10 while循环

当满足条件时，则执行循环

• 语法结构

while 条件 do
	sql语句
end while;
-- 条件成立，执行do后面的语句

案例：

/*
计算从1 加到 n 的和
*/
DELIMITER $
CREATE PROCEDURE pro_test9(n INT) -- 默认是in 所以in可以省略
BEGIN
DECLARE total INT DEFAULT 0;
DECLARE num INT DEFAULT 1;
WHILE num<=n DO
 SET total = total+num;
 SET num=num+1;
END WHILE;
SELECT CONCAT('从1加到',n,'的和为',total);
END$

CALL pro_test9(100);

3.11 repeat循环

当满足条件时，则退出循环

• 语法结构

REPEAT
	sql语句
UNTIL 条件
END REPEAT;
-- 条件成立 结束循环

案例

-- repeat循环
/*
计算从1 加到 n 的和
*/
DELIMITER $
CREATE PROCEDURE pro_test10(n INT) -- 默认是in 所以in可以省略
BEGIN
DECLARE total INT DEFAULT 0;
REPEAT
	SET total = total+n;
	SET n = n -1;
	UNTIL n=0 -- 这里没有;号 一定要注意
END REPEAT;
SELECT CONCAT('从1加到',n,'的和为',total);
END$

CALL pro_test10(100);

3.12 loop循环

LOOP 实现简单的循环，退出循环的条件需要使用其他的语句定义，通常可以使用 LEAVE 语句实现

• 语法结构

[别名:] LOOP
	sql语句
END LOOP [别名]
-- 如果不在 sql语句 中增加退出循环的语句，那么 LOOP 语句可以用来实现简单的死循环。

案例：

-- loop循环
/*
计算从1 加到 n 的和
*/
DELIMITER $
CREATE PROCEDURE pro_test11(n INT) -- 默认是in 所以in可以省略
BEGIN
DECLARE total INT DEFAULT 0;
s:LOOP
	SET total = total+n;
	SET n=n-1;
  IF n<0 THEN
	LEAVE s;-- 退出循环
  END IF;
END LOOP s;
SELECT total;
END$

CALL pro_test11(99);

3.13 游标

概述：

​ 游标是用来存储查询结果集的数据类型 , 在存储过程和函数中可以使用光标对结果集进行循环的处理。

​ 我们使用select语句查询出来的结果通过展示界面我们可以看到结果中的数据，但是我还想对查询出的数据进行进一步的处理，这样的话我们就需要一个容器来存储这个结果集，这个容器就是游标。

​ 光标的使用包括光标的声明、OPEN、FETCH 和 CLOSE。

声明光标：

DECLARE 游标名 CURSOR FOR select语句 ;

OPEN 光标：

OPEN 游标名 ;

FETCH 光标：

FETCH 游标名 INTO var_name [, var_name] ...-- 相当于java中的迭代器
-- fetch 游标名 相当于 iterator.next()

CLOSE 光标：

CLOSE 游标名 ;

案例：

-- 准备一张测试表和几条数据
CREATE TABLE IF NOT EXISTS student(
id INT,#学号
NAME VARCHAR(20),#姓名
age INT,#年龄
sex VARCHAR(5),#性别
address VARCHAR(100),#地址
math INT,#数学
english INT#英语
);

INSERT INTO student (id,NAME,age,sex,address,math,english) VALUES
(1,'马云',18,'男','杭州',80,80),
(2,'马化腾',19,'男','深圳',75,60),
(3,'埃隆马斯克',31,'男','美国',76,93),
(4,'扎克伯格',27,'男','美国',65,NULL),
(5,'郎平',16,'女','上海',90,98),
(6,'姚明',32,'男','上海',80,81);

-- 查询学生表中的数据，并进行逐行展示
DELIMITER $
CREATE PROCEDURE pro_test12()
BEGIN
	DECLARE id INT;#学号
	DECLARE NAME VARCHAR(20);#姓名
	DECLARE age INT;#年龄
	DECLARE sex VARCHAR(5);#性别
	DECLARE address VARCHAR(100);#地址
	DECLARE math INT;#数学
	DECLARE english INT;#英语
	DECLARE student_result CURSOR FOR SELECT * FROM student;-- 声明游标
	OPEN student_result;-- 开启游标
	FETCH student_result INTO id,NAME,age,sex,address,math,english; -- fetch一次，取一行记录
	SELECT CONCAT('id:',id,',name:',NAME,',age',age);
   CLOSE student_result;-- 关闭游标
END$

CALL pro_test12();

游标结合循环

思路1：

1. 获取num = count(*)
2. 每循环一次，num-1
3. 当num=0时，退出

-- 循环改进
/*
1. 获取num = count(*)
2. 每循环一次，num-1
3. 当num=0时，退出
*/
DELIMITER $
CREATE PROCEDURE pro_test13()
BEGIN
DECLARE id INT;#学号
DECLARE NAME VARCHAR(20);#姓名
DECLARE age INT;#年龄
DECLARE sex VARCHAR(5);#性别
DECLARE address VARCHAR(100);#地址
DECLARE math INT;#数学
DECLARE english INT;#英语
DECLARE student_result CURSOR FOR SELECT * FROM student;-- 声明游标
OPEN student_result;-- 开启游标
SET @num = (SELECT COUNT(*)FROM student);
REPEAT
SET @num=@num-1;
FETCH student_result INTO id,NAME,age,sex,address,math,english; -- fetch一次，取一行记录
SELECT CONCAT('id:',id,',name:',NAME,',age',age);
UNTIL @num=0
END REPEAT;
CLOSE student_result;-- 关闭游标
END$
CALL pro_test13();

思路2：句柄机制

1. 先声明一个状态变量，初始值为1
2. 声明句柄机制，当抓取不到数据就将该变量值设置为0
3. 判断该状态变量的值决定循环是否继续进行

3.14 存储函数

存储过程 与 函数非常相似，存储过程虽然没有返回值，但是我们可以指定out来返回结果，所以存储函 数能做的事情，存储过程也可以做。

• 语法结构

CREATE FUNCTION 函数名([参数名 数据类型 ... ])
RETURNS 返回值类型
BEGIN
...
END;