目录
在项目开发中会用到很多的数据 , 把不同的数据存储在不同的表中(分类存储) , 但又由于数据之间会存在关系,需要使用到多表关联
一. 数据库设计三大范式(原则)
(1)第一范式: 保证列的原子性(即列不可再分) 反例:联系方式(手机,邮箱,QQ)
(2)第二范式: 要有主键(id), 由于主键是唯一的,依赖于主键列的其他列也就是唯一的
(3)第三范式: 多表关联时,一个表只关联其他表的主键,不需要关联其他表的非主键列
二. 外键
1.定义:
使用外键可以引用另外一个数据表的某条数据
数据表之间的关联/引用关系依靠于具体的主键(primary key)和外键建立
2.添加/删除 (外键)列
-- 学生与专业关系(多对一)
-- 设计专业信息表
create table major(
id int primary key auto_increment,
name varchar(10),
major_desc varchar(50)
)
-- 修改表
-- 1.添加列 majorid称为外键列,键列只能与另一个表的主键关联
alter table student add column majorid int
-- 2.删除列
alter table student drop column majorid
2.添加/删除 外键约束
(1)表创建后添加外键约束
-- 3.为(外键)列添加外键约束,保证数据的完整性
-- 添加约束 约束名字 外键列 关联 表(列)
alter table student add constraint fk_student_major_on_majorid foreign key(majorid) references major(id)
-- 4.删除外键约束
alter table student drop foreign key fk_student_major_on_majorid(2)表创建时添加外键约束
-- 学生选课案例(多对多关系)
-- 创建一个课程信息表
create table course(
id int primary key auto_increment,
name varchar(20)
)
-- 创建学生选课表 在创建表时就添加外键
create table student_course(
studentid int,
courseid int,
constraint fk_student_course_student_on_studentid foreign key(studentid) references student(id),
constraint fk_student_course_course_on_corseid foreign key(courseid) references course(id)
)
三. 关联查询(多表查询)
写sql的方法分析:
1.根据题目分析出数据分别来源自哪些表
2.关联条件---哪两个列作为关联的条件
3.确定要查询的结果
-- 查询学生信息: 学号,姓名,性别,专业名称
select student.num,student.name,student.gender,student.majorid from student,major
-- 给表起别名
select s.num,s.name,s.gender,s.majorid from student as s,major as m
-- 给表起别名,可以省略as
select s.num,s.name,s.gender,s.majorid,m.id from student s,major m
-- 但是这样查询可能会出现笛卡尔乘积现象
-- 两张表关联没有关联条件,就会用第一张中的每行与关联表中的每行进行关联,需要添加关联条件注意笛卡尔乘积现象: 表1有m行，表2有n行，结果=m*n
发生原因：两张表关联没有有效的关联条件,用第一张中的每行与关联表中的每行进行关联
如何避免：添加有效的关联条件
1. 内连接(inner join):
只把满足条件的数据筛选出来
-- (1)内连接: 只把满足条件的数据筛选出来
-- 写法1(推荐写法)
select s.num,s.name,s.gender,m.name from student s inner join major m on s.majorid = m.id
-- 写法2:
select s.num,s.name,s.gender,m.name from student s,major m where s.majorid = m.id
-- 要查询的结果 来自于 表1 内关联 表2 关联条件
select s.num,s.name,s.gender,m.name from student s inner join major m on s.majorid = m.id2. 外连接(left/right join):
(1).左外连接: 不管条件是否成立,都会把左边表中的数据全部查询出来,右边表只查询出满足条件的
(2).右外连接: 不管条件是否成立,都会把右边表中的数据全部查询出来,左边表只查询出满足条件的
-- (2)外连接
-- 1.左外连接: 不管条件是否成立,都会把左边表中的数据全部查询出来,右边表只会查询出满足条件的数据
select s.num,s.name,s.gender,m.name from student s left join major m on s.majorid = m.id
-- 2.右外连接: 不管条件是否成立,都会把右边表中的数据全部查询出来,左边表只会查询出满足条件的数据
select s.num,s.name,s.gender,m.name from student s right join major m on s.majorid = m.id -- 四张表关联查询
-- 查询: 学号,姓名,专业名称,学生所选的课程名称
select s.num,s.name,m.name,c.name
from student s left join major m on s.majorid = m.id
left join student_course sc on s.id = sc.studentid
left join course c on sc.courseid = c.id3. 自连接
(1). 问题引出: 查询 各省 市 县
(2). 解决方法: 方法一: 省份-----市级信息表 省份外键 -----县/区表 市外键
方法二: 一张表存储 省 市 县 (同理 部门 总公司 分公司 部门)
-- 方法二: 注意 parentid是父级id
create table area(
id int primary key,
name varchar(20),
pid int
)
-- 查询省 自治区 特别行政区
select * from area where pid = 0
-- 查询市 例如 陕西省的所有市
select * from area where pid = 610000
-- 查询区/县 例如 汉中市的所有区/县
select * from area where pid = 610700-- 自连接
-- 查询汉台区所属市,以及汉台区所属省
select concat(a1.name,'-',a2.name,'-',a3.name) name
from area a1 inner join area a2 on a1.pid = a2.id
inner join area a3 on a2.pid = a3.id
where a1.id = 610702
四. 子查询
1. 定义:
在其他语句中出现的查询语句,即查询语句中再出现一个查询
2. 分类:
3.代码及方法:
(1)标量子查询: 查询结果只有一行一列 适用于select和where后面
-- 标量子查询(查询结果只有一行一列) 适用于select和where后面
select s.num,s.name,(select m.name from major m where m.id = s.majorid) from student s
select * from student where height = (select max(height) from student) (2)列子查询: 结果集只有一列多行
-- 列查询(结果集只有一列多行)
select * from student where height in(select height from student where height > 1.60 and height < 1.99) (3)表子查询: 把一个查询的结果当做一张表,为另一个查询提供数据 适用于from后面
一次查询处理不完, 再写一个查询进行处理
-- 表子查询 把一个查询的结果当做一张表,为另一个查询提供数据 适用于from后面
-- 一次查询处理不完, 再写一个查询进行处理
select * from (select name,count(*)c from student group by name )t where t.c>1
-- 查询程么每门课都及格学生
select * from score where num not in (select num from score where score < 60)