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;

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,723评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,080评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,604评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,440评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,431评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,499评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,893评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,541评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,751评论 1赞 296
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,547评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,619评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,320评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,890评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,896评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,137评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,796评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,335评论 2赞 342