今天把至今学过的知识梳理一下
一.外键约束
1.1 单表的缺点
创建一个员工表包含如下列 (id, name, age, dep_name,
dep_location) , id 主键并自动增长,添加 5 条数据。
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30), age INT,
dep_name VARCHAR(30),
dep_location VARCHAR(30)
);
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳');
INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳');
缺点 :表中出现了很多重复的数据 ( 数据冗余 ) ,如果要修
改研发部的地址需要修改 3 个地方。
解决方案 :将一张表分成 2 张表 ( 员工表和部门表 )
-- 创建部门表
CREATE TABLE department (
id INT PRIMARY KEY AUTO_INCREMENT,
dep_name VARCHAR(20),
dep_location VARCHAR(20)
);
-- 创建员工表 CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT
);
-- 添加2个部门
INSERT INTO department (dep_name, dep_location) VALUES ('研发部', '广州');
INSERT INTO department (dep_name, dep_location) VALUES('销售部', '深圳');
-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('小王', 18, 2);
问题: 当我们在 employee 的 dep_id 里面输入不存在的部
门 , 数据依然可以添加 . 但是并没有对应的部门,不能出现
这种情况。 employee 的 dep_id 中的内容只能是
department 表中存在的 id
解决方式 :使用外键约束。需要约束 dep_id 字段的值 , 只
能是 department 表中已经存在 id
1.2 外键约束
1.2.1 外键约束作用
外键约束作用: 保证引用数据的完整性。用来维护多表
间关系
外键 : 一张 从表 中的某个字段引用 主表 中的主键
主表: 约束别人
副表 / 从表: 使用别人的数据,被别人约束
1.2.2 添加外键语法
1. 新建表时增加外键:
[CONSTRAINT] [外键约束名称] FOREIGN KEY(外键字 段名) REFERENCES 主表名(主键字段名)
注意事项:外键的这个列的类型必须和参照主表主键列的类 型一致
关键字解释:
CONSTRAINT -- 约束关键字
FOREIGN KEY(外键字段名) –- 某个字段作为外键
REFERENCES -- 主表名(主键字段名) 表示参照主表中的 某个字段
2. 已有表增加外键:
ALTER TABLE 从表 ADD [CONSTRAINT] [外键约束名 称] FOREIGN KEY (外键字段名) REFERENCES 主表(主 键字段名);
-- 先删除 employee 表 , 因为在创建这张表的时候没有设置 外键约束
-- 创建表的时候增加外键
-- 语法 :[CONSTRAINT] [ 外键约束名称 ] FOREIGN KEY(外键字段名 ) REFERENCES 主表名 ( 主键字段名 )
-- 创建员工表
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
age INT,
dep_id INT,
-- 添加一个外键
constraint fk1 foreign key(dep_id) references department(id)
);
-- 添加员工,dep_id表示员工所在的部门
INSERT INTO employee (NAME, age, dep_id) VALUES('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES('小王', 18, 2);
-- 验证: 添加错误数据
INSERT INTO employee (NAME, age, dep_id) VALUES ('二王', 20, 5);// 报错
1.2.3 删除外键语法
ALTER TABLE 表 drop foreign key 外键名称;
-- 删除外键
alter table employee drop foreign key fk1;
-- 对已经创建好的表,添加外键
alter table employee add constraint fk1 foreign key(dep_id) references department(id);
1.2.4 外键的级联【了解】
问题一修改 : 要把部门表中的 id 值 2 ,改成 5 ,能不能直接修改呢?
update department set id = 5 where id = 2;
问题二删除 : 要删除部门 id 等于 1 的部门 , 能不能直接删除呢?
delete from department where id = 1;
解决方案: 级联操作
在修改和删除主表的主键时,同时更新或删除副表的外
键值,称为级联操作
ON UPDATE CASCADE -- 级联更新,主键发生更新
时,外键也会更新
ON DELETE CASCADE -- 级联删除,主键发生删除
时,外键也会删除
具体操作:
删除 employee 表
重新创建 employee 表,添加级联更新和级联删除
CREATE TABLE employee (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(30),
age INT,
dep_id INT,
CONSTRAINT employee_dep_fk FOREIGN KEY (dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE
);
再次添加数据到员工表和部门表
INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1);
INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2);
INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2);
把部门表中的 id 值 2 ,改成 5
update department set id = 5 where id = 2;
删除部门 id 等于 1 的部门
delete from department where id = 1;
二.表与表之间的关系
现实生活中,实体与实体之间肯定是有关系的,比如:
老公和老婆,部门和员工,老师和学生等。那么我们在
设计表的时候,就应该体现出表与表之间的这种关系。
分成三种:
一对多
多对多
一对一
2.1 一对多
例如:班级和学生,部门和员工,客户和订单
一的一方 : 班级 部门 客户
多的一方 : 学生 员工 订单
一对多建表原则 : 在多方的一方创建一个字段,字段作
为外键指向一的一方的主键
2.2 多对多
例如:老师和学生,学生和课程,用户和角色
一个老师可以有多个学生 , 一个学生也可以有多个老师
多对多的关系
一个学生可以选多门课程 , 一门课程也可以由多个学生选
择 多对多的关系
一个用户可以有多个角色 , 一个角色也可以有多个用户
多对多的关系
多对多关系建表原则 : 需要创建第三张表,中间表中至少
两个字段,这两个字段分别作为外键指向各自一方的主
键。
2.3 一对一(通常单表)
例如 : 一个公司可以有一个注册地址,一个注册地址只能
对一个公司。
例如 : 一个老公可以有一个老婆 , 一个老婆只能有一个老公
在实际的开发中应用不多 . 因为 一对一可以创建成一张
表 。
两种建表原则:
外键唯一:主表的主键和从表的外键(唯一),形成
主外键关系,外键唯一 UNIQUE
外键是主键:主表的主键和从表的主键,形成主外键
关系
2.4 多表分析及创建
需求 : 完成一个学校的选课系统,在选课系统中包含班
级,学生和课程这些实体。
班级和学生之间是有关系存在:一个班级下包含多个学
生,一个学生只能属于某一个班级(一对多的关系)。
学生和课程之间是有关系存在:一个学生可以选择多门
课程,一门课程也可以被多个学生所选择(多对多的关
系)。
班级和学生 : 一对多
学生和课程 : 多对多
-- 班级表
create table room(
rid int primary key auto_increment,
rname varchar(50)
);
-- 学生表
create table student(
sid int primary key auto_increment,
age int,
r_id int,
constraint stu_r_fk1 foreign key(r_id) references room(rid)
);
-- 课程表
create table course(
cid int primary key auto_increment,
cname varchar(50)
);
-- 创建中间表
create table cou_stu(
s_id int,
c_id int,
constraint stu_cou_fk2 foreign key(s_id) references student(sid),
constraint stu_cou_fk3 foreign key(c_id) references course(cid)
);
三.连接查询
3.1 环境准备
-- 创建部门表
CREATE TABLE dept (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('开发部'), ('市场部'),('财务部');
-- 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1),
-- 性别
salary DOUBLE,
-- 工资
join_date DATE,
-- 入职日期
dept_id INT
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
3.2 交叉查询【了解】
语法
select A.列,A.列,B.列,B.列 from A,B;
-- 或者
select A.*,B.* from A,B ;
-- 或者
select * from A,B;
练习 : 使用交叉查询部门和员工
select * from dept,emp;
select dept.*,emp.* from dept,emp;
以上数据其实是左表的每条数据和右表的每条数据组
合。左表有 3 条,右表有 5 条,最终组合后 3*5=15 条数
据。
交叉查询其实是一种错误 . 数据大部分是无用数据 , 叫笛卡
尔积 .
3.3 内连接查询【重点】
交叉查询产生这样的结果并不是我们想要的,那么怎么
去除错误的,不想要的记录呢,当然是通过条件过滤。
通常要查询的多个表之间都存在关联关系,那么就通过
关联关系 ( 主外键关系 ) 去除笛卡尔积。
3.3.1 隐式内连接
select [字段,字段,字段][*] from A,B where 连接 条件 --(A表里面的主键 = B表里面的外键)
3.3.2 显示内连接
显示里面是有 inner 关键字的
select [字段,字段,字段][*] from A [inner] join B on 连接条件 [ where 其它条件]
-- 隐式内连接 -- 语法:select [字段,字段,字段][*] from A,B
where 连接条件 --(A表里面的主键 = B表里面的外键)
select * from dept,emp where dept.id = emp.dept_id; -- 显示所有信息
-- 内连接:显示部门名称,员工名称,工资,性别
select dept.name,emp.name,emp.salary,emp.gender from dept,emp where dept.id = emp.dept_id;
-- 简化: 取别名 工作中常见
select d.name,e.name,e.salary,e.gender from dept as d,emp as e where d.id = e.dept_id;
-- 简化: 取别名 省略as关键字
select d.name,e.name,e.salary,e.gender from dept d,emp e where d.id = e.dept_id;
-- 显示内连接查询 -- 语法:select [字段,字段,字段][*] from A [inner] join B on 连接条件 [ where 其它条件]
select * from dept inner join emp on dept.id=emp.dept_id;
select d.name,e.name,e.salary,e.gender from dept d inner join emp e on d.id=e.dept_id;
注意事项:
内连接查询的是公共部分 , 满足连接条件 ( 主外键关系 )
的部分
使用主外键关系做为条件来去除无用信息 . 抓住主外
键的关系,用主外键作为连接条件,B表里面的外键A表
里面的主键 .
显示内连接里面的 ,on 只能用主外键关联作为条件 , 如
果还有其它条件 , 后面加 where
3.4 外连接【重点】
我们发现内连接查询出来的是公共部分 . 如果要保证某张
表的全部数据情况下进行连接查询 . 那么就要使用外连接
查询了 . 外连接分为左外连接和右外连接。
3.4.1 左外连接
以 join 左边的表为主表 , 展示主表的所有数据 , 根据条件查
询连接右边表的数据 , 若满足条件则展示 , 若不满足则以
null 显示。
可以理解为:在内连接的基础上保证左边表的数据全部
显示
语法
select [字段][*] from A left [outer] join B on 条件
练习 : 查询所有部门下的员工
-- 左外连接: 在内连接的基础上保证左边表的数据全部显示
-- 语法:select [字段][*] from A left [outer] join B on 条件
-- 练习: 查询所有部门的员工
select * from dept left outer join emp on dept.id=emp.dept_id;
select * from dept left join emp on dept.id=emp.dept_id;
3.4.2 右外连接
以 join 右边的表为主表 , 展示右边表的所有数据 , 根据条件
查询 join 左边表的数据 , 若满足则展示 , 若不满足则以 null
显示。
可以理解为:在内连接的基础上保证右边表的数据全部
显示
语法
select 字段 from A right [outer] join B on 条件
练习 : 查询所有员工所对应的部门
-- 在内连接的基础上保证右边表的数据全部显示
-- 语法: select [字段][*] from A right [outer] join B on 条件
-- 练习: 所有员工的部门
select * from dept right outer join emp on dept.id=emp.dept_id;
select * from dept right join emp on dept.id=emp.dept_id;
3.4.3 内连接和外连接的区别
内连接 : 查询的是公共部分 , 满足连接条件的部分
左外连接 : 以左边表为主表 , 查询出左边表的所有的数
据 . 再通过连接条件匹配出右边表的数据 , 如果满足连
接条件 , 展示右边表的数据 ; 如果不满足 , 右边的数据通
过 null 代替
四.子查询【重点】
4.1 子查询介绍
直观一点:一个查询里面至少包含 2 个 select
一个查询语句的结果作为另一个查询语句的条件
有查询的嵌套,内部的查询称为子查询
子查询要使用括号
子查询结果的三种情况
4.2 子查询一
子查询的结果是一个值的时候
子查询结果只要是单个值,肯定在 WHERE 后面作为条件
SELECT 查询字段 FROM 表 WHERE 字段 [= > < <>]
(子查询) ;
查询工资最高的员工是谁?
-- 步骤一: 先查询最高工资
select max(salary) from emp;
-- 结果是: 9000
-- 步骤二: 根据9000去员工表查询对应的员工信息
select * from emp where salary = 9000;
-- 合并
select * from emp where salary = (select max(salary) from emp);
查询工资小于平均工资的员工有哪些?
-- 步骤一: 先计算平均工资
select avg(salary) from emp;
-- 结果 为:5883.333333333333
-- 步骤二: 根据5883.333333333333去员工表查询小于 该值的员工信息
select * from emp where salary < 5883.333333333333;
-- 合并 select * from emp where salary < (select avg(salary) from emp);
4.3 子查询二
子查询结果只要是单列多行,肯定在 WHERE 后面作为条
件
子查询结果是单列多行,结果集类似于一个数组,父查
询使用 IN 运算符
SELECT 查询字段 FROM 表 WHERE 字段 IN (子查询) ;
查询工资大于 5000 的员工,来自于哪些部门的名字
-- 步骤一: 查询工资大于5000的员工的dept_id的值
select dept_id from emp where salary > 5000;
-- 结果是:1,2,null
-- 步骤二: 通过查询出来的员工dept_id去部门表中查询信息
select * from dept where id in (1,2,null);
-- 合并
select * from dept where id in(select dept_id from emp where salary > 5000);
查询开发部与财务部所有的员工信息
-- 步骤一: 先去部门表中查询开发部和财务部的id
select id from dept where name in ('开发 部','财务部');
-- 结果: 1,3
-- 步骤二: 根据开发部和财务部的id去员工表中查询员工 信息
select * from emp where dept_id in (1,3);
-- 合并
select * from emp where dept_id in (select id from dept where name in ('开发部','财务 部'));
4.4 子查询三
子查询结果只要是多行多列,肯定在FROM后面作为表
SELECT 查询字段 FROM (子查询) 表别名 WHERE 条
件 ;
子查询作为表需要取别名,否则这张表没用名称无法访
问表中的字段
查询出 2011 年以后入职的员工信息,包括部门名称
-- 步骤一: 查询出2011年以后入职的员工信息
select * from emp where join_date > '2011- 01-01';
-- 步骤二: 把步骤一的结果看成一张表,与部门表进行内 连接查询 关联条件: dept_id = id
select * from dept,(select * from emp where join_date > '2011-01-01') e where dept.id = e.dept_id
ok了家人们明天见。