本期给大家讲解一下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;

文章来源于互联网:mysql外键连接+左右外级联+内级联

发表评论