[ MySQL 数据库 ] 多表关联查询

       

目录

一. 数据库设计三大范式(原则)

二. 外键

      1.定义:

      2.添加/删除 (外键)列

      2.添加/删除 外键约束

三. 关联查询(多表查询)

1. 内连接(inner join):

2. 外连接(left/right  join):

3. 自连接

四. 子查询

       1. 定义:

       2. 分类:

 

       3.代码及方法:

---

       在项目开发中会用到很多的数据 , 把不同的数据存储在不同的表中(分类存储) , 但又由于数据之间会存在关系,需要使用到多表关联

一. 数据库设计三大范式(原则)

(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-- 给表起别名,可以省略asselect 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.id

2. 外连接(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.namefrom student s left join major m on s.majorid = m.idleft join student_course sc on s.id = sc.studentidleft join course c on sc.courseid = c.id

    3. 自连接

    (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) namefrom area a1 inner join area a2 on a1.pid = a2.idinner join area a3 on a2.pid = a3.idwhere 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 sselect * 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)