SQL Server数据库入门教程之多表查询

SQL_Server的前情介绍

😁大家好呀,今天是我第三次写sql_server,也是最近才学习sql_server,也想着记录一下自己的学习过程,并且分享给大家尼!

一、sql_server技术介绍

SQL Server 是由微软公司(Microsoft)开发的关系型数(RDBMS)。RDBMS 是 SQL 以及所有现代数据库系统的基础,比如 MS SQL Server,IBM DB2,Oracle,MySQL 以及微软的 Microsoft Access。

二、学习前的准备工作

编程软件:SQL Server Management Studio 2012
带好你的小板凳,我们一起扬帆起航!

SQL_Server之多表查询

笛卡尔乘积的讲解

在数据库中有一种叫笛卡尔乘积其语法如下:

select * from People,Department

此查询结果会将People表的所有数据和Department表的所有数据进行依次排列组合形成新的记录。例如People表有10条记录,Department表有3条记录,则排列组合之后查询结果会有10*3=30条记录.

多表查询

接下来我们来看几个例子吧!

1.查询员工信息,显示部门信息

select * from People,department where People.DepartmentId = department.DepartmentId 

2.查询员工信息,显示职级名称

select * from People,s_rank where People.RankId = s_rank.RankId

3.查询员工信息,显示部门名称,显示职级名称

select * from People,department,s_rank 
	where People.departmentId = department.DepartmentId and People.RankId = s_rank.RankId 

内连接查询

在数据库的查询过程中,存在有内连接查询,这个时候,我们就需要用到inner这个关键字,下面我们来看几个例子吧!

1.查询员工信息,显示部门信息

select * from People inner join department on People.departmentId = department.DepartmentId

2.查询员工信息,显示职级名称

select * from People inner join s_rank on People.RankId = s_rank.RankId

3.查询员工信息,显示部门名称,显示职级名称

select * from People inner join department on People.departmentId = department.DepartmentId
inner join s_rank on People.RankId = s_rank.RankId

外连接查询(左外连,右外连,全外连)

1.查询员工信息,显示部门信息(左外连)

select * from People left join department on People.departmentId = department.DepartmentId

2.查询员工信息,显示职级名称(左外接)

select * from People left join s_rank on People.RankId = s_rank.RankId

3.查询员工信息,显示部门名称,显示职级名称(左外连)

select * from People left join department on People.departmentId = department.DepartmentId
inner join s_rank on People.RankId = s_rank.RankId

4.右外连

A left join B = B right join A

select * from People right join department on People.departmentId = department.DepartmentId

全外连查询(无论是否符合关系,都要显示数据)

1.select * from People full join department on People.departmentId = department.DepartmentId

多表查询的主要例子

1.查询出武汉地区所有的员工信息,要求显示部门名称,以及员工的详细资料(显示中文别名)

select PeopleId 员工编号,DepartmentName 部门名称,PeopleName 员工姓名,PeopleSex 员工性别,
PeopleBirth 员工生日,PeoPleSalary 月薪,PeoplePhone 电话,PeopleAddress 地址
from People,department where People.departmentId = department.DepartmentId

2.查询出武汉地区所有员工的信息,要求显示部门名称,职级名称以及员工的详细资料

select PeopleId 员工编号,DepartmentName 部门名称,RankName 职级名称, PeopleName 员工姓名,PeopleSex 员工性别,
PeopleBirth 员工生日,PeoPleSalary 月薪,PeoplePhone 电话,PeopleAddress 地址
from People,department,s_rank where People.departmentId = department.DepartmentId 
and People.RankId = s_rank.RankId and PeopleAddress = '武汉'

3.根据部门分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资

select DepartmentName 部门名称, count(*) 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资 
from People,department
	where People.departmentId = department.DepartmentId
		group by department.DepartmentId,DepartmentName

4.根据部门分组统计员工人数,员工工资总和,平均工资,最高工资和最低工资平均工资在10000元以下的不参与排序。根据平均工资降序排序

select DepartmentName 部门名称, count(*) 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资 
from People,department
	where People.departmentId = department.DepartmentId 
		group by department.DepartmentId,DepartmentName	
			having avg(PeopleSalary) >= 15000 
				order by avg(PeopleSalary) desc

5.根据部门名称,然后根据职位名称分组,统计员工人数,员工工资总和
平均工资,最高工资,和最低工资

select DepartmentName 部门名称,RankName 职级名称, count(*) 员工人数,sum(PeopleSalary) 工资总和,avg(PeopleSalary) 平均工资,max(PeopleSalary) 最高工资,min(PeopleSalary) 最低工资 
from People,department,s_rank
	where People.departmentId = department.DepartmentId and People.RankId = s_rank.RankId
		group by department.DepartmentId,DepartmentName,s_rank.RankId,s_rank.RankName

如何巩固学习

提示:在学习的过程中,我们需要先自行进行思考,而不是一遇到不会的就放弃思考直接看答案,如果最后遇到真的不会的题目,我们可以适当的进行观看答案,看自己的思路是否正确,在作出正确的判断

总结

到此这篇关于SQL Server数据库入门教程之多表查询的文章就介绍到这了,更多相关SQLServer多表查询内容请搜索aitechtogether.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持aitechtogether.com!

共计人评分,平均

到目前为止还没有投票!成为第一位评论此文章。

(0)
心中带点小风骚的头像心中带点小风骚普通用户
上一篇 2023年12月28日 下午3:11
下一篇 2023年12月28日

相关推荐

此站出售,如需请站内私信或者邮箱!