一、外键约束

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

CREATE TABLE ClassCharger(id TINYINT PRIMARY KEY auto_increment,name VARCHAR (20),age INT ,is_marriged tinyint(1)
);

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

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

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

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

CREATE TABLE Student(id INT PRIMARY KEY auto_increment,name VARCHAR (20),charger_id TINYINT,CONSTRAINT abc FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
) ENGINE=INNODB;

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

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

delete from ClassCharger where id=1;
insert into student (name,charger_id) values('haha',10);

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

ALTER TABLE student  ADD CONSTRAINT abcFOREIGN KEY (charger_id)REFERENCES  classcharger(id);

ALTER TABLE student DROP FOREIGN KEY abc;

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

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

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

ALTER TABLE student  ADD CONSTRAINT abcFOREIGN KEY(charger_id)REFERENCES  classcharger(id)ON DELETE CASCADE;

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

ALTER TABLE student  ADD CONSTRAINT abcFOREIGN KEY(charger_id)REFERENCES  classcharger(id)ON DELETE SET NULL;

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

二、多表查询

1、连接查询

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

create table employee(emp_id int auto_increment primary key not null,emp_name varchar(50),age int,dept_id int
);

insert into employee(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，并插入记录

create table department(dept_id int,dept_name varchar(100)
);

insert into department values(200,'人事部'),(201,'技术部'),(202,'销售部'),(203,'财政部');

（1）笛卡尔积查询

SELECT * FROM employee,department;

（2）内连接

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

select * from employee,department where employee.dept_id = department.dept_id;

或

select * from employee inner join department on employee.dept_id = department.dept_id;

注：这里 inner join 前后两张表的顺序颠倒，查询结果一样。

（3）外连接

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

select * from employee left join department on employee.dept_id = department.dept_id;

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

select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id;

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

select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id
UNION
select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;

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

2、复合条件连接查询

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

SELECT DISTINCT department.dept_nameFROM employee,departmentWHERE employee.dept_id = department.dept_idAND age>25;

3、子查询

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

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

select * from employeewhere dept_id IN(select dept_id from department);

（2）复制employee到AA

create table AA (select * from employee);

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

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

select * from employeeWHERE EXISTS(SELECT dept_name from department where dept_id=205);

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

三、索引

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

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

create table test(id int primary key auto_increment,name varchar(20),salary int DEFAULT 1000
);insert into test(name) values("111"),("222"),("333"),("444");alter table test modify name varchar(20) unique;

---

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

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

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

创建普通索引示例：

create table test1(id int,name varchar(20),index index_name (name) -- 关键字index换成key，效果相同
);

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

create table test2(id int,name varchar(20),resume varchar(100),fulltext index index_name (resume)
);

创建多列索引示例：

create table test3(id int,name varchar(20),resume varchar(100),INDEX index_name_resume (name, resume)
);

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

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

方法二：

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

示例：

create index index_name on test4 (name);

alter table test4 add unique index index_name (name);

---

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

示例：

drop index index_name on test4;