C++ MySQL day8

4.30

索引

定义

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是表中一列或多列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。
相当于索引

作用

• 保证数据的准确性
• 提高检索速度
• 提高系统性能

类型

• 唯一索引（UNIQUE）：不可以出现相同的值，可以有NULL值
• 普通索引（INDEX）：允许出现相同的索引内容
• 主键索引（PRIMARY KEY）：不允许出现相同的值
• 全文索引（FULLTEXT INDEX）：可以针对值中的某个单词，但效* 率确实不敢恭维
• 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一（用得少）

创建、查看、删除

点击查看代码

-- 创建索引
ALTER TABLE 表名 ADD INDEX 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 创建全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名称 (字段名1, 字段名2, ..., 字段名n);
-- 查看索引
SHOW INDEX FROM 表名;
-- 删除索引
ALTER TABLE 表名 DROP INDEX 索引名称;

注意事项

• 虽然索引大大提高了查询速度，但也会降低更新表的速度，，数据库不仅要保存数据，还要保存一下索引文件
• 建立索引会占用磁盘空间的索引文件
• 不要在列上进行运算（包括函数运算），这会忽略索引的使用
• 不建议使用like操作，用的话，like '查询内容%'可以使用索引

多表查询

笛卡尔积

内连接

内连接相当于在笛卡尔积的基础上加上了连接条件。当没有连接条件时，内连接上升为笛卡尔积，联表查询会比较费时
基本语法：

点击查看代码

SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1 [INNER] JOIN 表2 [ON 连接条件];
SELECT 字段名1, 字段名2, ..., 字段名n FROM 表1, 表2 [WHERE 关联条件 AND 查询条件];

例子：

点击查看代码

SELECT COUNT(*) FROM stu INNER JOIN score ON stu.id=score.stu_id;
SELECT COUNT(*) FROM stu, score WHERE stu.id=score.stu_id;

外连接

外连接涉及到两张表：主表和从表，要查询的信息主要来自于哪张表，哪张表就是主表。

外连接查询的结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录（NULL）

外连接分为左外连接和右外连接两种。左外连接使用LEFT JOIN关键字，LEFT JOIN左边的是主表；右外连接使用RIGHT JOIN关键字，RIGHT JOIN右边的是主表。

左外连接

点击查看代码

SELECT 字段名1, 字段名2, ..., 字段名n FROM 主表 LEFT JOIN 从表 [ON 连接条件];

实例：

点击查看代码

SELECT * FROM stu a LEFT JOIN score b ON a.id=b.stu_id WHERE score IS NULL;

右外连接

基本语法：

点击查看代码

SELECT 字段名1, 字段名2, ..., 字段名n FROM 从表 RIGHT JOIN 主表 [ON 连接条件];

例子：

点击查看代码

SELECT * FROM stu a RIGHT JOIN score b ON a.id=b.stu_id;

子查询

定义

子查询就是嵌套在其他查询中的查询

SELECT ... FROM之间

例子：查询stu表所有学生信息，并将性别按男、女、其他展示

点击查看代码

SELECT
id,
`name`,
(SELECT text FROM dict WHERE type='sex' AND value=sex) sex,
birthday,
class
FROM
stu;

FROM ... WHERE 之间

查询年龄与Java成绩都与汤辰宇的年龄与Java成绩相同的学生信息

点击查看代码

SELECT c.*, d.* FROM stu c
INNER JOIN
score d ON c.id=d.stu_id
INNER JOIN
(SELECT
TIMESTAMPDIFF(YEAR, a.birthday,NOW()) age,
b.score
FROM stu a INNER JOIN score b ON a.id=b.stu_id
WHERE a.name='汤辰宇'
AND b.course='Java') e
ON TIMESTAMPDIFF(YEAR, c.birthday,NOW())=e.age AND d.score=e.score
WHERE d.course='Java';

WHERE 之后

查询Java成绩最高的学生信息

点击查看代码

SELECT a.*, b.* FROM stu a INNER JOIN score b ON a.id=b.stu_id
WHERE b.score=(SELECT MAX(score) FROM score WHERE course='Java')
AND b.course='Java';