第九单元 连接查询

create database unit10;
go
use unit10;
go
create table Dept
(
    Id int primary key identity,
    DName varchar(30) not null unique
);
go
create table Emp
(
    Id int primary key identity,
    EName varchar(30) not null unique,
    Sex char(4) not null default '男',
    Age tinyint not null default 10,
    Sal decimal(10,2) not null default 0,
    Did int not null
);
go
insert into Dept values('开发部'),('测试部'),('市场部'),('销售部'),('账务部');
go
insert into Emp values
('任我行','男',30,8000,1),
('李换英','女',20,3100,3),
('张三','男',25,6000,2),
('李国华','男',30,3000,4),
('徐媛媛','女',20,7200,1),
('帅天行','男',32,9000,1),
('曲芙蓉','女',22,3100,3),
('李四','男',25,6000,2),
('王五','男',28,4000,4),
('姖倩倩','女',23,5200,1);
 

--2.查询部门编号、部门名
select Id,DName from Dept
​
--3.查询员工姓名，薪资
select * from Emp
​
--4.查询员工姓名，薪资，所在部门
select  * from Emp inner join Dept on Emp.Did=Dept.Id
​
--5.查询分配了部门的员工信息和相应的部门信息(内连接)
-- Dept:部门表   Emp:员工表
select a.*,b.DName  from Emp a join Dept b on a.Did=b.Id -- 为什么取别名，因为下面可能还会用到这个表
​
-- 1.重复意思的字段不要查询出来
-- 2.重名的字段，但是字段意思不同，此时应该把字段取个别名
​
use step2;
-- 查询学生的姓名，性别，成绩
select stuName,stuSex,skillScore  from StudentInfo 
inner join StudentScore on StudentInfo.stuId=StudentScore.stuId
--查询学生的学生编号，姓名，性别，成绩，课程
--  涉及到2张表
select a.stuId,stuName,stuSex,skillScore,CourseName
from StudentInfo a join  StudentScore b on a.stuId=b.stuId
​
​
-- 查询学生在20岁以上的 学生编号，姓名，性别，成绩，课程
-- 连接查询+条件 需要把where 放到最后面
select a.stuId,stuName,stuSex,skillScore,CourseName
from StudentInfo a join  StudentScore b on a.stuId=b.stuId
where (year(getdate())-year(stuBirthday))>20

select * from  表1  （inner） join  表2  on  表1的主键列 = 表2的外键列
注：表1是主键表，表2是外键表
use step2;
​
-- 1.查询出分配了班级的学生和班级信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
​
-- 2.查询出软件技术1班的学生和班级信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where b.Name='软件技术1班'
​
-- 3.查询分配了班级，年龄又在20岁以上的学生和班级信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where (year(getdate())-year(stuBirthday))>20
​
-- 4.查询分配了班级，年龄又在20-24之间的学生和班级信息
select a.*,b.Name,b.College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where (year(getdate())-year(stuBirthday)) between 20 and 24
​
-- 5.查询没有学生的班级名称
-- 如果这个班有学生，是不是一定会在学生中有classId
-- 如果说在学生表中没有出现的classId,是不是就表示 那个班没有学生呢？
select Name from ClassInfo where id not in
(
    select classId from StudentInfo
)
​
-- 6.查询分配了班级，性别又为女的学生和班级信息
select stuName,stuBirthday,Name,College from StudentInfo a inner join ClassInfo b on a.ClassId=b.Id
where stuSex='女'
不等连接
定义:是使用除(=)以外的比较运算符查询的内连接, 如:>，>=，<=，<，!>，!<，<> ,!=。

-- 每位同学除了自己所属的班级外查询出其余可选择的班级
select * from StudentInfo a join ClassInfo b on a.ClassId!=b.Id
​
-- 查询其他同学的成绩
select * from StudentInfo a join StudentScore b on a.stuId!=b.stuId
 

select * from  表1  （inner） join  表2  on  表1的主键列 = 表2的外键列
注：表1和表2是一张表
​
create table menu
(
    Id int primary key identity,
    Name varchar(30), -- 菜单名称
    ParentId int  -- 上级菜单
) ;
​
insert into menu values('商品管理',0),('系统管理',0),('订单管理',0) ;
insert into menu values('商品列表',1),('商品分类',1),('发布商品',1) ;
insert into menu values('权限管理',2),('用户管理',2),('角色管理',2) ;
insert into menu values('订单列表',3),('运费模板',3),('物流跟踪',3) ;

-- 查询所有菜单信息以及它的上级菜单名称
-- 自连接一定要取别名
select a.*,b.Name from menu a inner join menu b  on a.ParentId=b.Id
​

use step2;
-- 1.查询所有学生信息以及对应的班级信息（要求显示学生编号，学生姓名，班级编号，班级名称）
-- 左外连接：left outer join
select * from StudentInfo a left outer join  ClassInfo b on a.ClassId =b.Id
-- 左连接：以左表主表，不管是否匹配上，都会把左表中的数据都显示出来,未匹配的数据会以NULL进行填充
​
​
-- 执行结果
180325011   任我行 5   13823204456 男   1999-09-09 00:00:00.000 5   会计3班    经济管理系
180325012   张三  4   13823204452 女   1998-08-08 00:00:00.000 4   欧美软件外包班 计算机系
180325013   李四  2   18899251152 男   1997-07-07 00:00:00.000 2   会计1班    经济管理系
180325014   王五  1   13597445645 女   1998-08-08 00:00:00.000 1   软件技术1班  计算机系
180325015   帅天行 5   13814204456 男   1998-06-06 00:00:00.000 5   会计3班    经济管理系
180325016   叶星辰 5   17623204936 男   1998-05-05 00:00:00.000 5   会计3班    经济管理系
180325017   赵日天 0   13922044932 男   1997-07-15 00:00:00.000 NULL    NULL    NULL
 

-- 1.查询所有班级信息以及对应的学生信息（要求显示学生编号，学生姓名，班级编号，班级名称）
select * from StudentInfo a right outer join ClassInfo b on a.ClassId=b.Id
-- 右连接：以右表为主表，不管是否匹配上，都会把右表中的数据都显示出来，未匹配上的数据会以NULL进行填充

-- 执行结果
180325014   王五  1   13597445645 女   1998-08-08 00:00:00.000 1   软件技术1班  计算机系
180325013   李四  2   18899251152 男   1997-07-07 00:00:00.000 2   会计1班    经济管理系
NULL       NULL  NULL   NULL    NULL    NULL      3               会计2班    经济管理系
180325012   张三  4   13823204452 女   1998-08-08 00:00:00.000 4   欧美软件外包班 计算机系
180325011   任我行 5   13823204456 男   1999-09-09 00:00:00.000 5   会计3班    经济管理系
180325015   帅天行 5   13814204456 男   1998-06-06 00:00:00.000 5   会计3班    经济管理系
180325016   叶星辰 5   17623204936 男   1998-05-05 00:00:00.000 5   会计3班    经济管理系

-- 1.查询所有学生信息以及所有班级信息（要求显示学生编号，学生姓名，班级编号，班级名称）
-- 全连接：full  join,不管是否有没有匹配上，都会显示
select * from StudentInfo a full outer join ClassInfo b on a.ClassId=b.Id
​
180325011   任我行 5   13823204456 男   1999-09-09 00:00:00.000 5   会计3班    经济管理系
180325012   张三  4   13823204452 女   1998-08-08 00:00:00.000 4   欧美软件外包班 计算机系
180325013   李四  2   18899251152 男   1997-07-07 00:00:00.000 2   会计1班    经济管理系
180325014   王五  1   13597445645 女   1998-08-08 00:00:00.000 1   软件技术1班  计算机系
180325015   帅天行 5   13814204456 男   1998-06-06 00:00:00.000 5   会计3班    经济管理系
180325016   叶星辰 5   17623204936 男   1998-05-05 00:00:00.000 5   会计3班    经济管理系
180325017   赵日天 0   13922044932 男   1997-07-15 00:00:00.000 NULL    NULL    NULL
NULL        NULL  NULL  NULL     NULL   NULL                   3    会计2班   经济管理系

create table course1
(
    Id int primary key,
    Name varchar(30)
);
create table course2
(
    Id int primary key,
    Name varchar(30)
);
insert into course1 values(1,'c#'),(2,'sql'),(3,'.net core'),(4,'mysql');
insert into course2 values(1,'c#'),(2,'sql'),(3,'asp.net core'),(4,'docker');