分支4：多表查询
-- 数据库设计范式(原则) 数据库设计三大范式:
-- 第一范式: 保证列的原子性(列不可再分) 反例:联系方式(手机,邮箱,qq) 正例: 手机号,qq,邮箱.
-- 第二范式: 要有主键,其他列依赖于主键列,因为主键是唯一的,依赖了主键,这行数据就是唯一的.
-- 第三范式: 多表关联时,在一个表中,只关联另一个表的主键即可,不需要关联另一个表的非主键列.
-- 项目开发中会用到很多的数据,把不同的数据存储不同的表中(分类存储),
-- 但是数据之间又存在关系
-- 学生与专业关系 一对多/多对一
-- 设计 专业信息表, 学生与专业信息表有关系
create table major(
id int primary key auto_increment,
name varchar(10),
major_desc varchar(50)
)
-- 修改表 添加列 najorid 称为外键列, 外键列只能与另一个表的主键关联
alter table student add column majorid int
-- 修改表 删除列
alter table student drop column majorid
-- 修改表,为外键列添加外键约束,保证数据的完整性
-- 添加约束 约束名字 外键列 与 哪个表的主键列关联
alter table student add constraint fk_student_major_on_majorid foreign key(majorid) references major(id)
-- 删除外键约束
alter table student drop foreign key fk_student_major_on_majorid
-- 学生选课案例 多对多关系
-- 创建一个课程信息表
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)
)
-- 省 市 县
-- 省份
-- 市级信息表 省份外键
-- 县/区表 市外键
-- 一张表 存储省,市,县 部门 总公司 分公司 部门
create table area(
id int primary key,
name varchar(20),
pid int
)
select count(*) from area
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
-- 查询学生信息: 学号,姓名,性别,专业名称
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
-- 笛卡尔乘积现象 由于两张表关联没有关联条件,用第一张中的每行与关联表中的每行进行关联
select s.num,s.name,s.gender,s.majorid,m.id from student s,major m
-- 解决办法,添加关联条件
-- 内连接 只把满足条件的数据筛选出来
-- 写法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
/*
写sql方法分析
1.根据题目分析出数据来源自哪些表
2.关联条件 哪两个列作为条件关联
3.确定查询结果
*/
--
select s.num,s.name,s.gender,m.name from student s inner join major m on s.majorid = m.id
-- 外连接--左外连接 不管条件是否成立,都会把左边表中的数据全部查询出来,右边表只会查询出满足条件的数据
select s.num,s.name,s.gender,m.name from student s left join major m on s.majorid = m.id
-- 外连接--右外连接,不管条件是否成立,都会把右边表中的数据全部查询出来,左边表只会查询出满足条件的数据
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.id
/*
子查询:在其他语句中出现的查询语句
一般子查询多用与查询语句(查询语句中再出现一个查询)
按子查询出现的位置：
select后面: 支持标量子查询
from后面：支持表子查询
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)
-- 列查询(结果集只有一列多行)
select * from student where height in(select height from student where height>1.60 and height<1.99)
-- 表子查询 把一个查询的结果当做一张表,为另一个查询提供数据
-- 一次查询处理不完, 写一个查询进行处理
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)
