一、外键约束

外键所依赖的表为主表，字段绑定外键的表为子表。
主表：

CREATETABLEClassCharger(idTINYINTPRIMARYKEYauto_increment,nameVARCHAR(20),ageINT,is_marrigedtinyint);

注：如果ClassCharger在s3库中，而当前库不在s3中，可以将表名写为：s3.ClassCharger

INSERTINTOClassCharger(name,age,is_marriged)VALUES("冰冰",12,0),("丹丹",14,0),("歪歪",22,0),("姗姗",20,0),("小雨",21,0);

子表：
创建表时添加外键：
[CONSTRAINT 外键名]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)

注：作为外键一定要和关联主键的数据类型保持一致。如果不写CONSTRAINT 外键名，则会自动获取一个外键名。

CREATETABLEStudent(idINTPRIMARYKEYauto_increment,nameVARCHAR(20),charger_idTINYINT,CONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id))ENGINE=INNODB;

注：①charger_id：TINYINT 类型，用于存储关联到 ClassCharger 表的外键值。
②CONSTRAINT abc FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)：
这表示 Student 表的 charger_id 字段必须是 ClassCharger 表中 id 字段的合法值。
③如果 ClassCharger 表中没有对应的 id，则无法插入或更新 Student 表中的 charger_id。
④如果 ClassCharger 表中的 id 被删除，Student 表中对应的 charger_id 记录也会受到限制（默认行为是 RESTRICT，即阻止删除）。
⑤ENGINE=INNODB：指定使用 InnoDB 引擎，该引擎支持外键约束。如果使用 MyISAM 引擎，外键约束将被忽略。

INSERTINTOStudent(name,charger_id)VALUES("alvin1",2),("alvin2",4),("alvin3",1),("alvin4",3),("alvin5",1),("alvin6",3),("alvin7",2);

由于外键的作用，下列两句sql都执行不成功。

deletefromClassChargerwhereid=1;insertintostudent(name,charger_id)values('haha',10);

创建表后，也可以添加、删除外键：

ALTERTABLEStudentADDCONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id);

ALTERTABLEStudentDROPFOREIGNKEYabc;

INNODB支持的ON语句：
在关系数据库中，能唯一标识表中一行数据的列（或列的组合），叫做候选键。例如主键和唯一索引就是候选键。

外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update

外键约束对父表的含义:在父表上进行update/delete，在子表中有对应匹配行的候选键时,父表的行为取决于在定义子表的外键时指定的 on update/on delete子句。

①cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录。
外键的级联删除：如果父表中的记录被删除，则子表中对应的记录自动被删除

ALTERTABLEStudentADDCONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id)ONDELETECASCADE;

②set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null。
注：子表的外键列不能为not nul

ALTERTABLEStudentADDCONSTRAINTabcFOREIGNKEY(charger_id)REFERENCESClassCharger(id)ONDELETESETNULL;

③Restrict方式 ④No action方式：这两种方式用的比较少，了解即可，都是拒绝对父表进行删除更新操作。

二、多表查询

1、连接查询

先准备两张表：
创建表employee，并插入记录

createtableemployee(emp_idintauto_incrementprimarykeynotnull,emp_namevarchar(50),ageint,dept_idint);

insertintoemployee(emp_name,age,dept_id)values('A',19,200),('B',26,201),('C',30,201),('D',24,202),('E',20,200),('F',38,204);

创建表department，并插入记录

createtabledepartment(dept_idint,dept_namevarchar(100));

insertintodepartmentvalues(200,'人事部'),(201,'技术部'),(202,'销售部'),(203,'财政部');

（1）笛卡尔积查询

SELECT*FROMemployee,department;

（2）内连接

A inner join B：找出A表和B表中匹配on条件的所有记录。

select*fromemployee,departmentwhereemployee.dept_id=department.dept_id;

或

select*fromemployeeinnerjoindepartmentonemployee.dept_id=department.dept_id;

注：这里 inner join 前后两张表的顺序颠倒，查询结果一样，只是每条记录内显示顺序不同，先显示前面表的内容，再显示后面表的。

（3）外连接

①左外连接
A left join B：在内连接的基础上，增加A表有B表没有的结果。

select*fromemployeeleftjoindepartmentonemployee.dept_id=department.dept_id;

②右外连接
A right join B：在内连接的基础上，增加B表有A表没有的结果。

select*fromemployeeRIGHTJOINdepartmentonemployee.dept_id=department.dept_id;

③全外连接
可以理解为左连接和右连接的并集。
mysql不支持full join语句，可以使用union语句来实现全外连接。

select*fromemployeeRIGHTJOINdepartmentonemployee.dept_id=department.dept_idUNIONselect*fromemployeeLEFTJOINdepartmentonemployee.dept_id=department.dept_id;

注意：union与union all的区别：union会去掉相同的纪录

2、复合条件连接查询

查询员工年龄大于等于25岁的部门：

SELECTDISTINCTdepartment.dept_nameFROMemployee,departmentWHEREemployee.dept_id=department.dept_idANDage>25;

3、子查询

子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字。
还可以包含比较运算符：= 、 !=、> 、<等。

（1）查询employee表，但dept_id必须在department表中出现过

select*fromemployeewheredept_idIN(selectdept_idfromdepartment);

（2）复制employee到AA

createtableAA(select*fromemployee);

注：这里只复制表字段和记录，不复制约束条件。

（3）带EXISTS关键字的子查询
EXISTS关字键字表示存在，返回Ture或False。
当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询。

select*fromemployeeWHEREEXISTS(SELECTdept_namefromdepartmentwheredept_id=205);

这里EXISTS返回False，外层select查询结果为Empty set。

三、索引

索引在Mysql中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引相当于字典的目录的页码，可以大幅提高查询速度。创建与维护索引会消耗很多时间与磁盘空间，但查询速度大大提高。
注：primary key主键也是一种索引。

unique是唯一索引。设置字段约束条件为unique时，会自动加一个unique索引。

createtabletest(idintprimarykeyauto_increment,namevarchar(20),salaryintDEFAULT1000);insertintotest(name)values("111"),("222"),("333"),("444");altertabletestmodifynamevarchar(20)unique;

创建表时，创建索引语法：

createtable表名(字段名1数据类型[约束条件],字段名2数据类型[约束条件],...[unique|fulltext|spatial]index|key[索引名](字段名[(长度)][asc|desc]));

创建索引时，如果不指定索引名，则会以字段名为索引名。

创建普通索引示例：

createtabletest1(idint,namevarchar(20),indexindex_name(name)-- 关键字index换成key，效果相同);

创建全文索引示例：
全文索引适合给大文本做索引。

createtabletest2(idint,namevarchar(20),resumevarchar(100),fulltextindexindex_name(resume));

创建多列索引示例：

createtabletest3(idint,namevarchar(20),resumevarchar(100),INDEXindex_name_resume(name,resume));

在已存在表上创建索引：
方法一：

create[unique|fulltext|spatial]index索引名on表名(字段名[(长度)][asc|desc]);

方法二：

altertable表名add[unique|fulltext|spatial]index索引名(字段名[(长度)][asc|desc])

示例：

createindexindex_nameontest4(name);

altertabletest4adduniqueindexindex_name(name);

删除索引：
语法： drop index 索引名 on 表名;

示例：

dropindexindex_nameontest4;