第九单元连接查询，--每位同学除了自己

和通数据库htsjk.Com2023-11-26 03:57 来源:未知阅读:9703 评论 518 热度3

标签：

第九单元连接查询，--每位同学除了自己

请问上图中显示的数据是一张表中的吗？

1. 连接查询的分类

内连接
外连接（左外连接，右外连接，全连接）
自连接
交差连接（不讲）

2. 内连接

注意:Join 与 inner join等效(默认为内连接)

语法格式： select 字段列表 from 表1 inner join 表2 on 表1.外键=表2.外键内连接：会把两表中匹配上的数据进行连接显示 inner join:内连接 on:当...时候在使用连接时，尽量避免使用 * 获取字段

部门表(Dept)

字段名	类型	主键	非空	唯一	默认值	描述
Id	int	√				部门编号,自增
`DName`	`Varchar(30)`		√	√		部门名称

员工表(Emp)

字段名	类型	主键	非空	唯一	默认值	描述
Id	int	√				员工编号,自增
`EName`	`Varchar(30)`		√	√		员工名称
Sex	`char(4)`		√		男	性别
Age	tinyint		√		10	年龄
Sal	Decimal(10,2)		√		0	工资
Did	Int		√			部门编号

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

3. 自连接

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

4. 外连接

外连接是以一张表为基表，返回基表中所有记录及连接表中符合条件的记录的连接查询。

左外连接
右外连接
全外连接

1. 左外连接

是以左表为基表，返回左表中所有记录及连接表中符合条件的记录的外连接。

1、以左表为基表 2、返回左表中所有数据 3、连接表不符合条件以NULL填充

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

2. 右外连接

是以右表为基表，返回右表中所有记录及连接表中符合条件的记录的外连接。

1、以右表为基表 2、返回右表中所有数据 3、连接表不符合条件以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班    经济管理系

3. 全外连接

是分别以左右表为基表的外连接。

1、将左表和右表所有的记录都显示 2、连接表不符合条件以NULL填充

-- 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班   经济管理系

5. 联合查询

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');

UNION去重且排序
UNION ALL不去重不排序

UNION用的比较多union all是直接连接，取到得是所有值，记录可能有重复 union 是取唯一值，记录没有重复 1、UNION 的语法如下：

 [SQL 语句 1]    
 UNION   
 [SQL 语句 2]

2、UNION ALL 的语法如下：

 [SQL 语句 1]    
 UNION ALL  
 [SQL 语句 2]

union 或者 union all 左右两边的查询列数要一样，类型最好也要一样。

效率：

UNION和UNION ALL关键字都是将两个结果集合并为一个，但这两者从使用和效率上来说都有所不同。

1、对重复结果的处理：UNION在进行表链接后会筛选掉重复的记录，Union All不会去除重复记录。

2、对排序的处理：Union将会按照字段的顺序进行排序；UNION ALL只是简单的将两个结果合并后就返回。从效率上说，UNION ALL 要比UNION快很多.

所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

6. 作业

Students(学生表)：

字段名	数据类型	描述
Id	int，主键，自增。	学生编号。
Name	nvarchar(20)，非空。	学生姓名。
Age	int，非空。	学生年龄。
Sex	bit，非空，默认值为1。	学生性别，1表示男，0表示女。

Teachers(老师表)：

字段名	数据类型	描述
Id	int，主键，自增。	老师编号。
Name	varchar(20)，非空。	老师姓名。
CardId	char(18),非空，唯一约束。	老师身份证号。

Courses(课程表)：

字段名	数据类型	描述
Id	int，主键，自增。	课程编号。
Name	nvarchar(200)，非空。	课程名称。
TeacherId	int，外键，关联Teachers表的Id字段。	老师编号。

Scores(成绩表):

字段名	数据类型	描述
Id	int，主键，自增。	成绩编号。
Score	Numeric(5,2)，检查约束(>=0,<=100.00)。	分数。
StudentId	int，外键，关联Students表的Id字段。	学生编号。
CourseId	int，外键，关联Courses表的Id字段。	课程编号。

(一) 开发要求：

使用SSMS或SQL语句创建数据库。（5分）
按数据字典要求创建表结构。（10分）

分别给各表按照下列语句添加数据。（10分）

--1.向表Students中添加数据
insert into Students values('冯程',20,0)
insert into Students values('许文强',18,1)
insert into Students values('凌凌七',19,0)
insert into Students values('莫向南',22,1)
insert into Students values('路宝妮',25,0)
insert into Students values('凤凰',16,1)

--2.向表Teachers中添加数据
insert into Teachers values('梁冰','112')
insert into Teachers values('李剑','113')
insert into Teachers values('耿彬彬','001')
insert into Teachers values('刘龙飞','002')
insert into Teachers values('吴慧敏','116')
insert into Teachers values('张静敏','068')
insert into Teachers values('刘泽飞','167')
insert into Teachers values('项天佑','153')

--3.向表Courses中添加数据
insert into Courses values('C#入站编程',3)
insert into Courses values('SQL Server',3)
insert into Courses values('ASP.NET Core',5)
insert into Courses values('前端入门',6)
insert into Courses values('ASP.NET WebApi',8)
insert into Courses values('Linux虚拟化技术',1)
insert into Courses values('MySQL',1)

--4.向表Scores中添加数据
insert into Scores values(100,6,1)
insert into Scores values(90,3,1)
insert into Scores values(100,3,2)
insert into Scores values(99,3,3)
insert into Scores values(95,3,4)
insert into Scores values(90,3,5)
insert into Scores values(91,3,6)
insert into Scores values(89,3,7)
insert into Scores values(100,1,3)
insert into Scores values(70,1,4)
insert into Scores values(100,2,3)
insert into Scores values(80,2,1)
insert into Scores values(90,2,2)

使用SQL语句查询如下：

(1) 查询学生表中的前5条记录并按照年龄进行降序排序，显示字段为中文：学号，姓名，年龄。（5分）

(2) 查询姓“李”的老师的个数。（5分）

(3) 查询学生表中年龄大于16岁的男生信息（5分）

(4) 统计每个教师所教的课程数量，显示字段包括老师编号，课程数量。（5分）

(5) 查询“梁冰”老师所教课程的信息。（5分）

(6) 查询成绩最高的学生编号及学生姓名。（10分）

(7) 按成绩进行降序排序，查询第6名到第10名的成绩信息。（5分）

(8) 按学生编号分组，查询平均成绩大于90分的同学的学生编号和平均成绩。（10分）

(9) 查询所有的课程信息及对应的老师信息，显示字段包含课程编号、课程名称、老师编号、老师姓名。（10分）

(10) 查询学过“耿彬彬”老师所教课程的同学的学号、姓名。（10分）

每行SQL语句添加注释。（5分）

配套视频链接：【阶段二】 - SQLServer 基础(超级详细，口碑爆盆)_哔哩哔哩_bilibili

海阔平鱼跃，天高任我行,给我一片蓝天,让我自由翱翔。

本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。

返回首页

暂无相关文章

评论暂时关闭