高级SQL（第十七天）：外键foreign key，索引index，表关联association；

一，外键（别的表里的主键）：描述了两张表的关系

USE cgb2017;

CREATE TABLE tb_user(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
)
CREATE TABLE tb_user_adder(
user_id INT PRIMARY KEY,
addr VARCHAR(20),
#foreign key (当前表的主键) REFERENCES 对方表(对方的主键)
FOREIGN KEY(user_id) REFERENCES tb_user(id) 
);

注：子表要保证没人用，才可以删除主表的信息

二，索引

1,最大事物作用就是提高查询效率，实现过程（1，设置索引，2，使用索引），坏处索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引需要占用大量的内存空间，不适合大量的添加。

2，分类

        ①单值索引：一个索引只包括一个列，列的值必须唯一

        ②唯一索引：索引列的值必须唯一，但允许有空值；主键会自动创键唯一索引

        ③复合索引：一个索引同时包括多列【遵循最左特性（最左边一个列），否则索引失效】

3，展示创建索引

#查看索引

SHOW INDEX FROM tb_user;

#创建索引（给常用来作为查询条件的字段加）；

#CREATE INDEX 索引名 on 表名（字段名）；
CREATE INDEX loc_index ON tb_user(NAME);

#使用索引（对于SQL语句没什么影响）

select * from dept  emp where depton=‘java’；

#观察SQL的性能/执行计划

explain select * from dept  emp where depton=‘java’；

#创建唯一索引 unique

CREATE unique INDEX loc_index ON tb_user(NAME);

#复合索引

一个索引作用在多个字段上

CREATE UNIQUE INDEX sel_index ON tb_user(NAME,id);

#删除索引

alter table 表名 drop index 索引名；

三，多表联查

四种表关联：

                一对一：员工：员工编号

                一对多：部门和员工 

                多对一：一对多反过来，一群人一个饭店

                多对多：老师和课程

1，笛卡尔积（产生了大量的冗余数据）

        SELECT * FROM teachers,students;

2，连接join

        SELECT * FROM teachers JOIN students;
SELECT * FROM teachers,courses WHERE teachers.tno=courses.tno;

#描述两张表的关系表名.字段名
SELECT * FROM teachers JOIN courses ON teachers.tno=courses.tno;

#

SELECT tname,tsex FROM teachers,courses WHERE teachers.tno=courses.tno AND teachers.tname='陈冰';

①内连接 inner join：取两个表的交集

②左（外）连接left join：左表的所有和右表满足条件的，不满足是null

③右（外）连接right join：右表的所有和左表满足条件的，不满足是null

列出所有男老师的信息
SELECT teachers.* FROM teachers JOIN courses ON teachers.tno=courses.tno WHERE tsex='男';
#内连接
SELECT * FROM teachers INNER JOIN courses ON teachers.tno=courses.tno; #WHERE tsex='男';
#左连接
SELECT * FROM teachers LEFT JOIN courses ON teachers.tno=courses.tno; #WHERE tsex='男';
#右连接
 SELECT* FROM teachers RIGHT JOIN courses ON teachers.tno=courses.tno; #WHERE tsex='男';
 
 3.子查询subquery

概念：子查询是指嵌入在其他select 语句中的select语句，也叫嵌套查询，执行效率很低

#查询工作地址在二区的所有员工信息

可以分成

#根据部门地址查部门编号

#根据编号查员工信息