笔记总结：MySQL——联表查询

联表查询

一、表间关系

1.表间关系

数据库通过主外键关联关系来体现表间关系。

2.主外键关联关系

在表中添加一个字段，使用另一个表的主键，称为外键，由此产生关联。

3.建立主外键关联关系

-- 设置当前表主键
PRIMARY KEY(主键);
-- 设置当前表外键，并与关联表主键关联
FOREIGN KEY (外键) REFERENCES 关联表(关联表主键);

4.约束

1. 主键约束

-- 添加主键约束，确保数据的唯一性
ALTER TABLE 表名 ADD PRIMARY KEY(字段名1，字段名2...);
--删除主键约束
ALTER TABLE 表名 DROP PRIMARY KEY;

2. 外键约束

--添加外键约束
ALTER TABLE 表名1 ADD CONSTRAINT 外键名称 FOREIGN KEY(表名1的字段名) REFERENCE 表名2(表名2的字段名);
ALTER TABLE 表明 DROP FOREIGN KEY 外键名称;

3. 唯一约束

-- 为字段添加唯一约束
ALTER TABLE 表名 ADD CONSTRAINT 约束名称 UNIQUE(字段名1，字段名2...);
ALTER TABLE 表名 DROP KEY 约束名称;

4. 非空约束

ALTER TABLE 表名 MODIFY 字段名 列类型 NOT　NULL;
-- 删除非空约束
ALTER TABLE 表名 MODIFY 字段名 列类型 NULL;

5. 默认值约束

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

6. 自增约束

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

二、索引

1.索引的作用

• 保证数据的准确性
• 提高检索的速度
• 提高系统性能
• 本质上改变了数据的存储结构

2.索引类型

• 唯一索引（UNIQUE）：不允许相同值，可以有NULL值
• 普通索引（INDEX）：可以有相同值
• 主键索引（PRIMARY KEY）：不允许相同值
• 全文索引（FULLTEXT INDEX）：可以针对值中的某个单词，效率差
• 组合索引：将多个字段建到一个索引，列值组合必须唯一

3.索引的创建，查看和删除

ALTER TABLE 表名 ADD INDEX 索引名称(字段名1，字段名2...);
ALTER TABLE 表名 ADD FULLTEXT 索引名称(字段名1，字段名2...);
SHOW INDEX FROM 表名;
ALTER TABLE 表名 DROP INDEX 索引名称;

4.注意事项

• 索引提高了查询速度，但降低了表的更新速度。数据库不仅要保存数据，还要保存索引文件。
• 会占用磁盘空间的索引文件。如果索引过多，导致索引文件过大，会降低数据库性能。索引要建立在经常查询的字段上。
• 在条件上不要在列上进行运算，会忽略索引的使用。
• 不建议使用like操作，like’%查询内容%’不会使用索引，而like’查询内容%’可以使用索引。
• 避免使用IS NULL、NOT IN、<>、!=、OR操作，会忽略索引而使用全表扫描。

三、多表查询

多表关联串时，表中数据会形成笛卡尔积。、

1.内连接

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

-- 直接笛卡尔积
SELECT 字段名1，字段名2... FROM 表1，表2;
-- 内连接,第一种执行效率更高
SELECT 字段名1，字段名2... FROM 表1 [INNER] JOIN 表2 [ON 关联关系];
SELECT 字段名1，字段名2... FROM 表1，表2 [WHERE 关联关系 [AND 查询条件]];

2.外连接

• 分为主表和从表，主表为查询表。

• 当主表查询结果有从表与之匹配，则显示匹配的值，若没有，则显示NULL。

• 外连接查询结果=内连接查询结果+主表中有而从表中没有的记录。

• 外连接分为左外连接和右外连接。LEFT JOIN左边是主表，RIGHT JOIN右边是主表。

1. 左外连接

SELECT 字段名1，字段名2... FROM 主表 LEFT JOIN 从表 [ON 关联关系];

2. 右外连接

SELECT 字段名1，字段名2... FROM 从表 RIGHT JOIN 主表 [ON 关联关系];

四、子查询

1.SELECT…FROM之间

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

子查询执行在查询结果出来之后

2.FROM…WHERE之间

SELECT查询结果视为一张表，再与其他表建立关联关系

执行时间是主查询前

3.WHERE之后

SELECT查询结果仅为一个值时，视为数值，加入WHERE的条件判断

执行时间是主查询前