MySQL 第六篇：索引与子查询

本篇内容由猿道教育的课程内容整理而来。
我把MySQL的内容整理成9篇博客，学完这9篇博客虽不能说能成为大神，但是应付一般中小企业的开发已经足够了，有疑问或建议的欢迎留言讨论。

子查询

子查询，从原有的查询语句中 嵌入新的查询 来得到我们想要的结果，也可称为嵌套查询。

一、where 型

1、查询课程名为“Java”的学生信息

-- 使用关联查询实现
SELECT
	s.*
FROM
	students s,
	class c
WHERE
	s.class_id = c.class_id
AND c.class_name = 'JAVA' 

-- 使用子查询实现
SELECT
	*
FROM
	STUDENTS
WHERE
	CLASS_ID = (
		SELECT
			CLASS_ID
		FROM
			CLASS
		WHERE
			CLASS_NAME = 'JAVA'
	)

2、用子查询实现查询出PHP 和 Java的学生

select * from students
where class_id IN(
SELECT
	class_id
FROM
	class
WHERE
	class_name = 'JAVA'
OR class_name = 'PHP'
)

注意：
1、IN 的元素不能超过1000个。
2、in的效率会较低。有时候会用exists代替 in。
3、所有的关联查询都可以转换为子查询。但是并不是所有的子查询都能转化成关联查询。

二、from型

把内层的查询结果集作为临时表 供外层sql再次查询。

SELECT
	s.*, c.class_name
FROM
	(
		SELECT
			*
		FROM
			students
		WHERE
			height > 170
	) s,
	class c
WHERE
	c.class_id = s.class_id

SELECT
	s.*,(select class_name from class c where c.class_id = s.class_id) 课程名称
FROM
	(
		SELECT
			*
		FROM
			students
		WHERE
			height > 170
	) s

三、子查询与inert update delete的结合使用

例：

INSERT INTO students (
	s_id,
	s_name,
	age,
	height,
	sex,
	class_id
)
VALUES
	(
		'J1609004',
		'余志坚',
		26,
		175,
		1,
		(
			SELECT
				class_id
			FROM
				class
			WHERE
				class_name = 'JAVA'
		)
	)

将学号为J1609004 的class_id更新成PHP的class_id

UPDATE students
SET class_id = (
	SELECT
		class_id
	FROM
		class
	WHERE
		class_name = 'PHP'
)
WHERE
	s_id = 'J1609004'

索引

数据库索引：数据库管理系统中的一个排序的数据结构，以协助快速查询，更新数据库表中的数据。简单来说，建立了索引后会在数据库里面单独一块区域建立一个小空间，把数据排好序，这样查找起来就快得多。

一、为表添加索引的代价：

• a.数据库的存储空间的占用
• b.插入或修改数据的时候要花费较多的时间 - 重新建立索引

二、使用索引的三大原则

• a.单表数据太少，索引反而会影响速度，数据较少的表不需要建立索引
• b.数据较多的情况下， where 后的条件、order by 、group by等过滤时，后面的字段最好加上索引，根据实际情况，选择primary key，unique index，索引不是越多越好。
• c.联合查询，子查询等多表操作时对关联字段加索引。

三、索引类型

1、单字段索引：最常用的索引，只有一个字段，并且不保证字段值的唯一性。

语法：create index 索引名 on 表名(列名)

create index index_name  on students(s_name);

2、唯一索引：可以根据唯一索引确定唯一的一条数据，用于改善性能和保证数据的完整性。

语法：create unique index 索引名 on 表名(列名)

create unique index index_id on students(s_id);

3、组合索引

语法：create index 索引名 on 表名（列名1，列名2.);

create index index_idname on students(s_name,s_id);

SELECT * FROM STUDENTS WHERE S_ID='3' and s_name='小红'

注意：
1、组合索引页可以是唯一索引。
2、当查询条件有组合查询的第一个字段（s_name）的时候就一定会用该索引去查询，无论where后面的查询条件的顺是怎样的，无论有没有其他查询条件。

4、隐含索引：数据库创建对象的时候自动穿件。主键约束 和唯一约束会自动创建索引。

补充：

• 1、在上例中，如果不存在索引，查找时将会扫描全表的s_name字段。
• 2、在一张表中可以建立多个索引，但是索引不是越多越好。
• 3、唯一索引的意思是这个字段的每个值都不一样，可以根据索引值唯一确定一条数据，而其他索引不能根据索引值确定唯一的数据。

四、修改与删除索引

修改语法：alter index 索引名 （生产系统不建议使用索引的修改，数据库执行时无法进行查询操作）
删除语法：drop index 索引名 on 表名

DROP INDEX index_name ON talbe_name;
ALTER TABLE table_name DROP INDEX index_name;

五、建立索引指导原则

1.越小的数据类型越好。
2.简单的数据类型越好，给日期做索引的 使用datetime 。
3.尽量避免使用NULL,指定列为 not null 有空值的列很难进行查询优化。可以使用0或者一个特殊的值或者空字符串来代替。

六、索引的优缺点

• 优点：加快查询速度
• 缺点：降低增 改 删的速度，增大了表的文件大小