从零开始学习MySQL(四) – 联表查询与索引
|
学习笔记
|
35

第四章 联表查询

第一节 表与表之间的关系

1. 表与表之间的关系

数据表是用来描述实体信息的，比如可以使用数据表来描述学生信息，也可以用数据表来描述班级信息，这样就会存在学生表和班级表。而学生和班级显然存在着一种关系：

这种关系在数据库中体现就称之为表与表之间的关系。数据库通过主外键关联关系来体现表与表之间的关联关系

2. 主外键关联关系

如图所示，此时学生表和班级表并没有任何关系，然而实际上学生和班级是存在归属关系。可以在学生表中添加一个字段，表明该学生所属班级，该字段值使用的是班级表中的主键，在学生表中称之为外键。这样学生表中的所属班级（外键）与班级表中的编号（主键）就产生关联关系，这种关联关系称为主外键关联关系。

3. 主外键关联关系的对应

CREATE TABLE IF NOT EXISTS clss(
    id INT(20) AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学号',
    name VARCHAR(30) NOT NULL COMMENT '班级名称',
    grade VARCHAR(30) NOT NULL COMMENT '年级',
)ENGINE=InnoDB CHARSET=UTF8 COMMENT '班级表';

CREATE TABLE IF NOT EXISTS stu(
    number BIGINT(20) AUTO_INCREMENT NOT NULL COMMENT '学号',
    name VARCHAR(30) NOT NULL COMMENT '姓名',
    sex VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
    age TINYINT(3) UNSIGNED DEFAULT 0 COMMENT '年龄',
    class_id INT(11) NOT NULL COMMENT '所属班级',
    -- 指定number为主键
    PRIMARY KEY(number),
    -- 字段class_id与cls表中的number字段相关联
    FOREIGN KEY(class_id) REFERENCES class(id)
)ENGINE=InnoDB CHARSET=UTF8 COMMENT '学生表';

4. 约束

4.1 主键约束

  • 添加主键约束，保证数据的唯一性

    ALTER TABLE 表名 ADD PRIMARY KEY(字段名1,字段名2, ..., 字段名n);

    示例：

    ALTER TABLE stu ADD PRIMARY KEY(number);

  • 删除主键约束

    ALTER TABLE 表名 DROP PRIMARY KEY;

    注：若主键自增则无法直接删除主键约束

    示例：

    ALTER TABLE stu DROP PRIMARY KEY;

4.2 外键约束

  • 添加外键约束

    ALTER TABLE1 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (表名1的字段名) REFERENCES 表名2(表名2的字段名);

    注： MyISAM不支持外键索引

    示例：

    ALTER TABLE stu ADD CONSTRAINT fk_class_id FOREIGN KEY (class_id) REFERENCES class(id);

  • 删除外键约束

    ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

    注：因外键可以多个，故需要声明外键名称

    示例：

    ALTER TABLE stu DROP FOREIGN KEY fk_class_id;

4.3 唯一约束

  • 为字段添加唯一约束

    ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE(字段名1, 字段名2, ..., 字段名n);

    示例：

    ALTER TABLE stu ADD CONSTRAINT un_name UNIQUE(name);

  • 删除字段的唯一约束

    ALTER TABLE 表名 DROP KEY 约束名称;

    示例：

    ALTER TABLE stu ADD CONSTRAINT un_name UNIQUE(name);

4.4 非空约束

  • 为字段添加非空约束

    ALTER TABLE 表名 MODIFY 字段名 列类型 NOT NULL;

  • 删除字段的非空约束

    ALTER TABLE 表名 MODIFY 字段名 列类型 NULL;

4.5 默认值约束

  • 为字段添加默认值

    ALTER TABLE 表名 ALTER 字段名 SET DEFAULT 默认值;

  • 删除字段的非空约束

    ALTER TABLE 表名 ALTER 字段名 DROP DEFAULT;

4.6 自增约束

  • 为字段添加自增约束

    ALTER TABLE 表名 MODIFY 字段名 列类型 AUTO_INCREMENT;

  • 删除字段的自增约束

    ALTER TABLE 表名 MODIFY 字段名 列类型;

第二节 索引

1. 什么是索引

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

对比书籍目录理解

2. 索引的作用

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

3. 索引的类型

  • 唯一索引（UNIQUE）：不可以出现相同的值，可以有NULL值
  • 普通索引（INDEX）：允许出现相同的索引内容
  • 主键索引（PRIMARY KEY）：不允许出现相同的值
  • 全文索引（FULLTEXT INDEX）：可以针对值中的某个单词，但效率确实不敢恭维
  • 组合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一。注意组合索引在进行匹配时，遵循最左原则。

4. 索引的创建、查看、删除

  • 创建索引

    ALTER TABLE 表名 ADD INDEX 索引名称 (字段名1, 字段名2, ..., 字段名n);

  • 创建全文索引

    ALTER TABLE 表名 ADD FULLTEXT 索引名称 (字段名1, 字段名2, ..., 字段名n);

    注：5.56版本之前的 InnoDB 不支持全文索引

  • 查看索引

    SHOW INDEX FROM 表名;

  • 删除索引

    ALTER TABLE 表名 DROP INNDEX 索引名称;

  • 使用全文索引示例

    -- 创建表单
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
body TEXT,
FULLTEXT (body));

-- 插入数据
INSERT INTO articles VALUES
(NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'),
(NULL,'How To Use MySQL Efficiently', 'After you went through a ...'),
(NULL,'Optimising MySQL','In this tutorial we will show ...'),
(NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
(NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'),
(NULL,'MySQL Security', 'When configured properly, MySQL ...');

    查询语句

    SELECT * FROM articles WHERE MATCH (body) AGAINST ('database');

    其查询结果等同于

    SELECT * FROM articles WHERE body like '%database%';

    注：注意全文索引如何定义字段的，match中就必须是哪些字段，against中定义需要模糊匹配的字符串，用作查找的字符串实际上是被分词之后的结果，如果进行模糊匹配的不是一个词语，那么会查找失败，但是它的效率远高于下面的这种写法

  • 查看索引的执行情况

    可以使用EXPLAIN语句（它可以用于分析select语句的执行计划，也就是MySQL到底是如何在执行某条select语句的）来分析查询语句到底有没有通过索引进行匹配。

    EXPLAIN SELECT * FROM student WHERE name = '枫阿雨';

    得到的结果如下：

    • select_type：查询类型，上面的就是简单查询（SIMPLE）
    • table：查询的表
    • type：MySQL决定如何查找对应的记录，效率从高到低：system > const > eq_ref > ref > range > index > all
    • possible_keys：执行查询时可能会用到的索引
    • key：实际使用的索引
    • key_len：Mysql在索引里使用的字节数，字段的最大可能长度
    • rows：扫描的行数
    • extra：附加说明

5. 使用索引的注意事项

  • 虽然索引大大提高了查询速度，但也会降低更新表的速度，比如对表进行INSERT、UPDATE和DELETE操作，此时，数据库不仅要保存数据，还要保存一下索引文件
  • 建立索引会占用磁盘空间的索引文件。如果索引创建过多（尤其是在字段多、数据量大的表上创建索引），就会导致索引文件过大，这样反而会降低数据库性能。因此，索引要建立在经常进行查询操作的字段上
  • 不要在列上进行运算（包括函数运算），这会忽略索引的使用
  • 不建议使用LIKE操作，如果非使用不可，注意正确的使用方式。LIKE '%查询内容%' 不会使用索引，而LIKE '查询内容%'可以使用索引
  • 避免使用IS NULL、NOT IN、<>、!=、OR操作，这些操作都会忽略索引而进行全表扫描

6. 索引底层原理 *

既然我们要通过索引来快速查找内容，那么如何设计索引就是我们的重点内容，因为索引是存储在硬盘上的，跟我们之前使用的HashMap之类的不同，它们都是在内存中的，但是硬盘的读取速度远小于内存的速度，每一次IO操作都会耗费大量的时间，我们也不可能把整个磁盘上的索引全部导入内存，因此我们需要考虑尽可能多的减少IO次数，索引的实现可以依靠两种数据结构，一种是我们在JavaSE阶段已经学习过的Hash表，还有一种就是B-Tree。

我们首先来看看哈希表，实际上就是计算Hash值来快速定位：

点击查看源网页

通过对Key进行散列值计算，我们可以直接得到对应数据的存放位置，它的查询效率能够达到O(1)，但是它也存在一定的缺陷：

  • Hash索引仅仅能满足“=”，“in”查询条件，不能使用范围查询。
  • Hash碰撞问题。
  • 不能用部分索引键来搜索，因为组合索引在计算哈希值的时候是一起计算的。

那么，既然要解决这些问题，我们还有一种方案就是使用类似于二叉树那样的数据结构来存储索引，但是这样相比使用Hash索引，会牺牲一定的读取速度。

但是这里并没有使用二叉树，而是使用了B-Tree，它是专门为磁盘数据读取设计的一种度为n的查找树：

  • 树中每个结点最多含有m个孩子（m >= 2）
  • 除根结点和叶子结点外，其它每个结点至少有[ceil(m / 2)]个孩子。
  • 若根结点不是叶子结点，则至少有2个孩子。
  • 所有叶子结点都出现在同一层。

  • 每个非终端结点中包含有n个键值信息： (P1，K1，P2，K2，P3，......，Kn，Pn+1)。其中：

    1. Ki (i=1...n)为键值，且键值按顺序升序排序K(i-1)< Ki。
    2. Pi为指向子树根的结点，且指针P(i)指向的子树中所有结点的键值均小于Ki，但都大于K(i-1)。
    3. 键值的个数n必须满足： [ceil(m / 2)-1] <= n <= m-1。

img

比如现在我们要对键值为10的记录进行查找，过程如下：

  1. 读取根节点数据（目前进行了一次IO操作）
  2. 根据根节点数据进行判断得到10<17，因为P1指向的子树中所有值都是小于17的，所以这时我们将P1指向的节点读取（目前进行了两次IO操作）
  3. 再次进行判断，得到8<10<12，因为P2指向的子树中所有的值都是小于12大于8的，所以这时读取P2指向的节点（目前进行了三次IO操作）
  4. 成功找到。

我们接着来看，虽然B-Tree能够很好地利用二叉查找树的思想大幅度减少查找次数，但是它的查找效率还是很低

因此它的优化版本 B+Tree 诞生了，它拥有更稳定的查询效率和更低的IO读取次数：

img

我们可以发现，它和BTree有一定的区别：

  • 有n棵子树的结点中含有n个键值，B-Tree只有n-1个。
  • 所有的键值信息只在叶子节点中包含，非叶子节点仅仅保存子节点的最小（或最大）值，和指向叶子节点的指针，这样相比B-Tree每一个节点在硬盘中存放了更少的内容（没有键值信息了）
  • 所有叶子节点都有一个根据大小顺序指向下一个叶子节点的指针Q，本质上数据就是一个链表。

这样，读取IO的时间相比BTree就减少了很多，并且查询任何键值信息都需要完整地走到叶子节点，保证了查询的IO读取次数一致。因此MySQL默认选择B+Tree作为索引的存储数据结构。

这是MyISAM存储引擎下的B+Tree实现：

img

这是InnoDB存储引擎下的B+Tree实现：

img

img

InnoDB与MyISAM实现的不同之处：

  • 数据本身就是索引的一部分（所以这里建议主键使用自增）
  • 非主键索引的数据实际上存储的是对应记录的主键值（因此InnoDB必须有主键，若没有也会自动查找替代）

注：可阅读一下文章 MySQL InnoDB数据表缺少主键会怎样 - 知乎

第三节 多表查询

1. 笛卡尔积

笛卡尔积又称为笛卡尔乘积，由笛卡尔提出，表示两个集合相乘的结果。

笛卡尔积与多表查询有什么关系呢？每一张表可以看做是一个数据的集合，多表关联串时，这些表中的数据就会形成笛卡尔积。

2. 内连接

内连接相当于在笛卡尔积的基础上加上了连接条件。当没有连接条件时，内连接上升为笛卡尔积。

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;

注：上方写法效率更高，下方写法为ORACLE的写法，MySQL仍支持

3. 外连接

外连接涉及到两张表：主表和从表，要查询的信息主要来自于哪张表，哪张表就是主表。
外连接查询的结果为主表中所有的记录。如果从表中有和它匹配的，则显示匹配的值，这部分相当于内连接查询出来的结果；如果从表中没有和它匹配的，则显示null。
外连接查询的结果 = 内连接的结果 + 主表中有的而内连接结果中没有的记录
外连接分为左外连接和右外连接两种。左外连接使用LEFT JOIN关键字，LEFT JOIN左边的是主表；右外连接使用RIGHT JOIN关键字，RIGHT JOIN右边的是主表。

3.1 左外连接

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

示例：

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

3.2 右外连接

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

示例：

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

第四节 子查询

1. 什么是子查询

子查询就是嵌套在其他查询中的查询。因此，子查询出现的位置只有3种情况：在SELECT ... FROM 之间、在FROM ... WHERE之间、在WHERE之后

注：下面示例中stu表中有学生信息，score表中有成绩对应信息

2. SELECT ... FROM 之间

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

-- 先创建字典表
CREATE TABLE IF NOT EXISTS dict(
    id  INT AUTO_INCREMENT NOT NULL PRIMARY KEY COMMENT '学生id',
    type VARCHAR(50) NOT NULL COMMENT '类型',
    `value` VARCHAR(50) NOT NULL COMMENT '原始值',
    `text` VARCHAR(50) NOT NULL COMMENT '转换值',
)ENGINE=InnoDB CHARSET=UTF8 COMMENT '字典表';

-- 向字典表插入字典转换内容
INSERT INTO dict (1, sex, 0, '男'), (2, sex, 1, '女'), (3, sex, 2, '其他');

-- 执行查询
SELECT
    id,
    `name`,
    (SELECT text FROM dict WHERE tYPE='sex' ANDE value=sex) sex,
    birthday,
    class
FROM
    stu;

注：执行时机是在查询结果出来之后

3. 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.score='Java') e
ON TIMESTAMPDIFF(YEAR, a.birthday, NOW())=e.age AND d.score=e.score
WHERE d.course='Java';

注：执行时机是一开始就执行

4. 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';
MySQL
暂无评论

发送评论 编辑评论 







































































































|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
（╯‵□′）╯︵┴─┴
￣﹃￣
(/ω＼)
∠( ᐛ 」∠)＿
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ｀)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ(￣∇￣o)
ヾ(´･ ･｀｡)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò｡)
Σ(っ °Д °;)っ
( ,,´･ω･)ﾉ"(´っω･｀｡)
╮(╯▽╰)╭
o(*////▽////*)q
＞﹏＜
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
	




颜文字
Emoji
小恐龙
花!
	


									














上一篇
下一篇 




推荐文章





面向对象设计模式与Go语言实现 – 创建型模式





LazySkiplist: A Simple Optimistic skip-list Algorithm论文翻译与实现





MySQL索引及其优化总结 – CFC例会2022.4.17





面向对象设计模式与Go语言实现 – 面向对象设计原则





速通WebSocket协议 – 建立在HTTP之上的应用层”TCP”协议





速通HDD与SSD基本原理





并发编程学习笔记





从零开始学习MySQL(一) – 初识MySQL数据库





《Java并发编程的艺术》学习笔记(二) – synchronized 全解读





《Java并发编程的艺术》学习笔记(三) – Java内存模型 – JMM