mysql外键关联查询_MySQL外键约束和多表联查

一.创建外键

#测试数据表

# 教师表，主表

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)