mysql 外键级联_mysql外键连接+左右外级联+内级联

本期给大家讲解一下mysql的数据约束和标题所说的连接语法规则

-- **********************数据约束**********************

-- 1.1 默认值

create table student(

id int,

name varchar(20),

country varchar(20) default '中国' -- 默认值

);

-- 没有设置默认值的字段，在没有赋值的情况下都是NULL

insert into student(id, name) values(1, "孬蛋");

insert into student(id, name, country) values(2, "金豆", "China");

insert into student(id) values(3);

drop table student;

-- 1.2 非空

create table student(

id int,

name varchar(20),

gender varchar(2) not null -- 非空

);

-- 如果存在非空字段，必须赋值

insert into student(id, name) values(1, "孬蛋");

-- ERROR 1364 (HY000): Field 'gender' doesn't have a default value

-- 必须给非空字段赋值

insert into student(id, name, gender) values(1, "狗蛋", "男");

-- 非空字段用在什么地方？

-- 用户名，密码，邮箱，手机

drop table student;

-- 1.3 唯一

create table student(

id int UNIQUE, -- 唯一

name varchar(20)

);

insert into student(id, name) values(1, "孬蛋");

insert into student(id, name) values(2, "孬蛋");

-- ERROR 1062(23000):Duplicate entry '1' for key 'id'

-- 唯一值不能重复

insert into student(id, name) values(1, "狗剩");

drop table student;

-- 1.4 主键 (非空+唯一)

create table student(

id int primary key,

name varchar(20)

);

insert into student(id, name) values(1, "狗蛋");

insert into student(id, name) values(2, "狗剩");

-- ERROR 1364 (HY000): Field 'id' doesn't have a default value

-- ID值不能为空，必须有数据

insert into student(name) values('辣鸡');

-- ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

-- ID值是唯一索引，不能为空，并且不能重复

insert into student(id, name) values(1, "炸鸡");

drop table student;

-- 1.5 自增长

CREATE TABLE student(

-- 自增长 ZEROFILL 零填充，从0开始

id INT ZEROFILL PRIMARY KEY AUTO_INCREMENT,

name VARCHAR(20)

);

insert into student(name) values("张三");

insert into student(name) values("李四");

insert into student(name) values("王五");

insert into student(name) values("马六");

-- 1.6 外键约束

create table employee(

id int primary key, -- 员工ID

empName varchar(20), -- 员工名

deptName varchar(20) -- 部门名

);

insert into employee values(1, "ll", "JavaEE教学部");

insert into employee values(2, "wd", "JavaEE教学部");

insert into employee values(3, "df", "JavaEE教学部");

insert into employee values(4, "rg", "JavaEE教学部");

insert into employee values(5, "ff", "JavaEE教学部");

-- 每一个人都是JavaEE教学部，这里每一个数据行中，都有JavaEE教学部

-- 导致了数据的冗余

-- 是否可以把部门做成一张表

-- 设计一个独立的部门表

create table dept(

id int primary key,

deptName varchar(20)

);

drop table employee;

-- 设计一个新的员工表，带有部门ID

create table employee(

id int primary key,

empName varchar(20),

deptID int, -- 用部门的ID来表示当前员工的部门是哪一个

-- 建立一个外键约束

CONSTRAINT emp_dept_fk foreign key(deptID) references dept(id) on update cascade on delete cascade -- 级联修改

--        外键名称              外键                连接的参考字段

);

insert into dept(id, deptName) values(1, "JavaEE教学部");

insert into dept(id, deptName) values(2, "PHP教学部");

insert into dept(id, deptName) values(3, "iOS教学部");

insert into employee values(1, "张三", 1);

insert into employee values(2, "李四", 2);

insert into employee values(3, "王五", 3);

insert into employee values(4, "赵六", 1);

-- 下面一条语句存在问题，因为在部门中并没有部门ID为4的部门，这里数据无法添加

-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f

-- ails (`day38`.`employee`, CONSTRAINT `emp_dept_fk` FOREIGN KEY (`deptID`) REFERE

-- NCES `dept` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

insert into employee values(5, "sf", 4);

解决的办法是： 先添加这下一行语句，在添加上面一条语句；

insert into dept(id, deptName) values(4, "Python教学部");

-- 级联修改 on update cascade on delete cascade

-- 修改部门，这里会随之修改主表里面的数据

update dept set id=4 where id=3;

-- 删除部门表 同时会帮助我们删除员工信息

delete from dept where id=2;

-- 修改员工表信息

update employee set deptID=2 where id=4;

-- 如果没有级联修改，请问如果是好？？？

-- 1) 当存在外键约束，添加数据的顺序：先添加主表，在添加副表

-- 2) 当存在外键约束，修改数据的顺序：先修改副表，在修改主表

-- 3) 当存在外键约束，删除数据的顺序：先删除副表，在删除主表

-- **************关联查询*****************

-- 严重错误的查询方法

-- 交叉查询 这个结果是有问题的 笛卡尔乘积 存在重复数据，不能使用

select empName,deptName from employee, dept;

-- 本来一个人在一个接岗位，但是这样查询就会出

现笛卡尔成绩 1 2 3  对应  4 5 6  就会出现 1和4 ，1和5 ，1和6， 2和4 ，2和5 ，2和6 ，3和4 ，3和5， 3和6 违背查询规则

-- 需求：查询员工及其所在目标，显示员工姓名和部门名称

-- 多表查询

-- 1. 确定查询那些表格 2. 确定要查询的字段 3. 表和表之间的关系

-- 内连接查询。只有满足条件的结果才会展示(使用最多的多表查询)

select empName,deptName  -- 要查询的字段

from employee, dept  -- 要查询的表格

where employee.deptID = dept.id; -- 表和表之间的关系

-- inner join 内连接的另一种语法

select empName, deptName

from employee  -- 主表

inner join dept -- 连接的是哪一张表

on employee.deptID = dept.id; -- 表示条件

-- 使用别名

select e.empName, d.deptName

from employee e

inner join dept d

on e.deptID = d.id;

-- 需求，查看每一个部门的员工

-- 预期结果

--JavaEE 张三

-- JavaEE 赵六

--  iOS  王五

--  PHP  李四

-- 左[外]连接查询：使用左边表中的数据来匹配右边表的数据，如果符合条件，展示数据

-- 如果没有符合条件的连接数据，显示null

select d.deptName, e.empName

from dept d

left outer join employee e

on d.id = e.deptID;

-- 右[外]连接查询：使用右边表中的数据来匹配左边表的数据，如果符合条件，展示数据

-- 如果没有符合条件的连接数据，显示null

select d.deptName, e.empName

from employee e

right outer join dept d

on d.id = e.deptID;

-- 自连接查询

-- 修改员工表结构，添加上司

alter table employee add bossId int;

update employee set bossId=1 where id=2;

update employee set bossId=2 where id=3;

update employee set bossId=3 where id=4;

-- 预期结果

-- 张三  null

-- 李四  张三

-- 王五  李四

-- 赵六  王五

select e.empName, b.empName

from employee e

left outer join employee b

on e.bossId = b.id;