一.创建外键
#测试数据表
# 教师表，主表
CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT,
name varchar(20),
age INT
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
#测试数据：
INSERT INTO teacher values
(1,'范冰冰',22),
(2,'周杰棍',30),
(3,'双杰伦',35),
(4,'梁朝伟',25);
--------------------------------分割线----------------------------
#学生表,子表
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
name varchar(20),
age INT,
teacher_id INT, # 外键要和关联的主键的数据类型一致
FOREIGN KEY (teacher_id) REFERENCES teacher(id) # 添加外键
)ENGINE=InnoDB DEFAULT CHARSET=UTF8;
#测试数据：
INSERT INTO student values
(1,'张三',22, 1),
(2,'李四',30,2),
(4,'王五',29,3),
(5,'韩信',61,4),
(6,'刘备',55,4),
(7,'曹操',21,2),
(8,'爱丽丝',32,1),
(9,'赵6',22,2);
--------------------------------分割线----------------------------
SELECT * FROM student INNER JOIN teacher ON student.teacher_id=teacher.id
--------------------------------分割线----------------------------
增加外键和删除外键：
增加:ALTER TABLE student ADD CONSTRAINT key_name(名字) FOREIGN KEY (teacher_id) REFERENCES teacher(id)
删除：ALTER TABLE student DROP FOREIGN KEY key_name
二.InnoDB数据库引擎的ON语句
InnoDB支持的常用方式：
1.cascade方式：在父表上update或delete记录时，同步update/delete掉子表的匹配记录
FOREIGN KEY (teacher_id) REFERENCES teacher(id) ON DELETE CASCADE # 如果父级表中的记录删除了，则子表中相对应的记录也会自动删除
2.set null方式 在父表update或delete记录时，将子表上的相对于的列设为null
FOREIGN KEY (teacher_id) REFERENCES teacher(id) ON DELETE SET NULL
#外键约束对子表的作用：在父表中找不到选键，则不允许在子表上进行 insert/update
#外键约束对父表的作用：
在父表上进行update/delete以更新或删除在子表中有一条或多条对应匹配行的候选键时,父表的行为取决于：在定义子表的外键时指定的on update/on delete子句
三.多表连接查询
#创建主测试表
CREATE TABLE role(
role_id int,
role_name varchar(100)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
#测试数据：
INSERT INTO role VALUES (301,'战士'),
(302,'法师'),
(303,'刺客'),
(304,'ADC');
(306,'打野')
----------------------------分割线----------------------------------
#创建子测试表
CREATE TABLE hero
( h_id INT auto_increment PRIMARY KEY NOT NULL,
h_name VARCHAR ( 50 ),
age INT,
role_id INT )ENGINE=InnoDB DEFAULT CHARSET=utf8;
#测试数据：
INSERT INTO hero(h_name,age,role_id) VALUES ('盖伦',22,301),
('流浪法师',23,302),
('布隆',22,303),
('熔岩巨兽',25,301),
('伊泽瑞尔',24,304),
('锤石',26,305),
('琴女',28,305),
('狂战士',27,301),
('稻草人',29,302),
('诡术妖姬',22,303);
- 内连接
SELECT * FROM hero inner JOIN role on hero.role_id=role.role_id;#笛卡尔积中筛选结果
- 外连接
1.左连接：在内链接的基础上增加左边有但右边没有的结果
SELECT * FROM hero LEFT JOIN role on hero.role_id=role.role_id;
----------------------------分割线----------------------------------
2.右连接：在内连接的基础上增加右边有但左边没有的结果
SELECT * FROM hero RIGHT JOIN role on hero.role_id=role.role_id;
----------------------------分割线----------------------------------
3.全外连接：在内连接的基础上增加 左边有右边没有 和 右边有左边没有 的结果
SELECT * FROM hero RIGHT JOIN role ON hero.role_id=role.role_id
UNION
SELECT * FROM hero LEFT JOIN role ON hero.role_id=role.role_id;
##mysql不支持全外连接的，可以用以上的方式间接实现
四：多表复合条件连接查询
SELECT role.role_name FROM hero,role WHERE hero.age >= 29 AND role.role_id=hero.role_id # 笛卡尔积中筛选年龄大于或等于29岁的英雄的分类
SELECT role.role_name FROM hero INNER JOIN role WHERE hero.age >= 29 AND role.role_id=hero.role_id #内连接查询
五：子查询
#子查询是将一条完整的查询语句嵌套在另外一条查询语句里
#内层查询语句的查询结果，可以为外层查询语句提供查询条件
#子查询用于为主查询返回其所需数据，或者对检索数据进行进一步的限制。
#子查询可以在 SELECT、INSERT、UPDATE 和 DELETE 语句中，同 =、<、>、>=、<=、IN、BETWEEN 等运算符一起使用。
- 子查询必须括在圆括号中。
- 子查询的 SELECT 子句中只能有一个列，除非主查询中有多个列，用于与子查询选中的列相比较。
- 子查询不能使用 ORDER BY，不过主查询可以。在子查询中，GROUP BY 可以起到同 ORDER BY 相同的作用。
- 返回多行数据的子查询只能同多值操作符一起使用，比如 IN 操作符。
- SELECT 列表中不能包含任何对 BLOB、ARRAY、CLOB 或者 NCLOB 类型值的引用。
- 子查询不能直接用在集合函数中。
- BETWEEN 操作符不能同子查询一起使用，但是 BETWEEN 操作符可以用在子查询中。
#SELECT * from hero WHERE hero.role_id in (SELECT role_id FROM role)
