使用联接和子查询来查询数据

--Chapter 3 使用联接和子查询来查询数据

--内容提要

go

/* (一)、使用联接查询数据

1. 内联接

2. 外联接

3. 交叉联接

4. 等值联接

5. 自联接

*/ go

/* (二)、使用子查询查询数据

1. 使用比较运算符,IN和EXISTS关键字

2. 使用修改过的比较运算符

3. 使用聚合函数

4. 使用嵌套子查询

5. 使用关联子查询

6. APPLY运算符

*/ go

/* (三)、管理结果集

1. 并集,交集和差集

2. 临时结果集

3. 派生表

*/ go

---------------------- (一)、使用联接查询数据------------------------------

use AdventureWorks --切换到AdventureWorks数据库

--创建Student表和Marks表,用于操作各种联接

create table Student --创建学生表,里面包含两列,学号和姓名

(

RollNo char(4),

Name varchar(20)

)

insert into Student values --向Student表中插入5行记录

('S001','Allen'),

('S002','Jhon'),

('S003','David'),

('S004','Stefen'),

('S005','Steve')

create table Marks --创建成绩表,里面包含三列,学号,RDMBS和Math

(

RollNo char(4),

RDBMS int,

Math int

)

insert into Marks values --向成绩表中插入三行记录

('S001',98,76),

('S002',67,64),

('S003',76,96)

select * from Student

select * from Marks

--1. 内联接 INNTER JOIN- 显示满足公共列中联接条件的行 inner可加可不加

--问题:查询有考试成绩的学生的学号,姓名,RDBMS成绩和Math成绩

-----练习:已知

select * from HumanResources.Employee

select * from HumanResources.EmployeeAddress

go

--显示:EmployeeID, Title, AddressID 的匹配信息 ----inner join

--给表名一个别名

--问题:查询RDBMS成绩在70以上的学生的学号,姓名和RDBMS成绩

select * from Student

select * from Marks

--练习C3-编写一个查询来显示所有销售人员的销售人员ID和他们所属的所在地名称 Sales.SalesPerson,Sales.SalesTerritory

select * from Sales.SalesPerson

select * from Sales.SalesTerritory

--2. 外联接 - 显示包含一个表中的所有行以及另外一个表中匹配行的结果集,不匹配的用NULL值填充

--(1)左外联接 - 返回LEFT OUTER JOIN 左侧的表的所有行,以及右侧指定的表的匹配行,若右边找不到匹配项,显示NULL值

--(2)右外联接 - 返回RIGHT OUTER JOIN 右侧的表的所有行,以及左侧指定的表的匹配行,若左边找不到匹配项,显示NULL值

--(3)完整外联接 - 左外联接和右外联接的组合,返回两个表中所有匹配的行和不匹配的行,匹配记录只显示一次

--3. 交叉联接(Cross Join) Product运算,将一个表中的每一行与另一个表中的

--------------------

create table Course --创建Course表,里面包含一列CourseName

(CourseName varchar(10))

insert into Course values --向Course表中插入两行记录

('English'),

('C Language')

select * from Student

select * from Course

--要求显示结果为每个学生都修一遍Course表中的所有课程

--4. 等值联接 --使用=号联接表的内联接

--练习:查询员工的员工编号,所属部门名称和工资 联接多个表

select * from HumanResources.Employee

select * from HumanResources.EmployeeDepartmentHistory

select * from HumanResources.Department

--5. 自联接 - 同一个表当成两张表使用,一个表中的一行联接另一个表中的一行

select * from HumanResources.Employee

select a.EmployeeID,a.Title,a.ManagerID,b.Title from --查询员工的编号,职位,其主管的员工编号和其主管的职位

HumanResources.Employee a join HumanResources.Employee b on a.ManagerID=b.EmployeeID --根据其主管的员工编号找到对应的职位

select a.EmployeeID,a.Title,a.ManagerID,b.Title from --查询员工的编号,职位,其主管的员工编号和其主管的职位

HumanResources.Employee a , HumanResources.Employee b where a.ManagerID=b.EmployeeID --根据其主管的员工编号找到对应的职位

go

---------------------- (二)、使用子查询查询数据----------------------------

--子查询:将一个select的查询结果作为另外一个select查询的输入/条件,查询里面的查询

--1. 使用比较运算符,IN和EXISTS关键字

--比较运算符,以=号为主

select * from HumanResources.Employee

--问题:查询和员工编号为1的员工职位(Title)相同的员工的信息

--IN 多个值

--问题:查询和员工编号为1,3,4的员工的职位相同的员工的信息

--EXISTS关键字-检查一组记录是否存在,返回True或False

--if exists(select * from databases where name='UDB') drop database UDB

------------------

select * from HumanResources.Employee

select * from HumanResources.EmployeeDepartmentHistory

--2. 使用修改过的比较运算符 ALL,ANY

--问题:查询

--查询RDBMS成绩高于S002或者高于S003的学生的信息

select * from Marks

go

--查询RDBMS成绩高于S002并且高于S003的学生的信息

--3. 使用聚合函数

--问题:查询RDBMS成绩最高的学生的学号和RDBMS成绩

--4. 使用嵌套子查询 --子查询里面可以包含一个或多个子查询,这样叫做嵌套子查询

--问题:查询工资最高的员工的编号 HumanResources.EmployeePayHistory

select * from HumanResources.EmployeePayHistory

--问题:查询工资最高的员工所在的部门编号

select * from HumanResources.EmployeeDepartmentHistory

--5. 使用关联子查询 - 根据外部查询作为评估依据的查询

--问题:查询每个部门最早加入的员工的信息

select * from HumanResources.EmployeeDepartmentHistory a

where StartDate=

(

select min(StartDate) from HumanResources.EmployeeDepartmentHistory

where DepartmentID=a.DepartmentID

)

--6. APPLY运算符 --合并两个查询的结果集,

---------------------------------------------

create table Depositor --创建Depositor表,存储储蓄用户信息,表中有两列,客户姓名和储蓄账户

(

客户姓名 varchar(20),

储蓄账户 char(3)

)

insert into Depositor values --向Depositor表中插入两条记录

('Allen','D01'),

('David','D02')

create table Borrower --创建Borrower表,存储贷款用户信息,表中有两列,客户姓名和贷款账户

(

客户姓名 varchar(20),

贷款账户 char(3)

)

insert into Borrower values --向Borrower表中插入两行记录

('Amy','B11'),

('David','B12')

--------------------------------------

select * from Depositor

select * from Borrower

--CROSS APPLY - 返回外部结果集中与内部结果集匹配的行

select a.客户姓名,a.储蓄账户,br.贷款账户 from Depositor a --外部结果集

cross apply

(select * from Borrower b where b.客户姓名=a.客户姓名) br --br为内部结果集的别名

--OUTER APPLY - 返回外部结果集中所有的行,即使内部结果集中没有找到此行

select a.客户姓名,a.储蓄账户,br.贷款账户 from Depositor a --外部结果集

outer apply

(select * from Borrower b where b.客户姓名=a.客户姓名) br --br为内部结果集的别名

--练习:查询Math成绩最高的学生的学号,姓名和Math成绩

select a.RollNo,a.Name,b.Math from Student a join Marks b on a.RollNo=b.RollNo

where b.Math=(select max(Math) from Marks)

go

---------------------- (三)、管理结果集------------------------------------

--1. 交集,并集,差集 --操作两个select语句查询的结果

/* 前提条件

(1)两个结果集的列的数量和顺序要一致

(2)所有查询中的列的数据类型必须兼容 如char(10)和varchar(10)

*/

select 客户姓名 from Depositor

select 客户姓名 from Borrower

--UNION 并集 --默认不显示重复的行,ALL显示出重复的记录

select 客户姓名 from Depositor

UNION ALL

select 客户姓名 from Borrower

--INTERSECT 交集

select 客户姓名 from Depositor

INTERSECT

select 客户姓名 from Borrower

--EXCEPT 差集

select 客户姓名 from Depositor

EXCEPT

select 客户姓名 from Borrower

--2. 临时结果集 - 将一个查询结果在执行的时候临时存储,用于执行其他查询

--不保存在数据库中,只有在执行的时候存在,语句执行完之后不存在

--问题:查询工资最高的10位员工的平均工资

WITH RateCTE(Salary) --RateCTE为临时结果集的名字,可自定义,Rate为临时结果集中的列名

AS

(

select top 10 Rate from HumanResources.EmployeePayHistory order by Rate desc --临时结果集的数据来源

)

select '平均工资'=avg(Salary) from RateCTE --从临时结果集里面查询数据

--锚定查询/递归查询

WITH Employee_CTE(ManagerID,EmployeeID) AS

(

select ManagerID,EmployeeID from HumanResources.Employee where ManagerID is null --锚查询,第一个被执行

UNION ALL -- Union是将两个select语句的结果合并成一个结果

select a.ManagerID,a.EmployeeID from HumanResources.Employee a join Employee_CTE b on --递归查询,反复被执行,直到查询到最后一行结束

a.ManagerID=b.Employee

)

select * from Employee_CTE

--3. 派生表 -- 将查询结果当做一个派生表/临时表使用

select Table1.ProductID,Table1.销售总数量,Table2.每个订单对应产品数 from

(select ProductID,'销售总数量'=sum(OrderQty) from Sales.SalesOrderDetail group by ProductID) as Table1 --将该select查询的结果作为一个派生表使用,相当于一个临时表

inner join

(select ProductID,'每个订单对应产品数'=sum(OrderQty) from Sales.SalesOrderDetail group by ProductID,SalesOrderID) as Table2 --将该select查询的结果作为另一个派生表使用,相当于一个临时表

on Table1.ProductID=Table2.ProductID

go

原文发布于微信公众号 - 数据库SQL(SQLdba)

原文发表时间:2015-03-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏CDA数据分析师

十步完全理解 SQL

文 | 水果泡腾片 来源 | 伯乐在线 很多程序员视 SQL 为洪水猛兽。SQL 是一种为数不多的声明性语言,它的运行方式完全不同于我们所熟知的命令行语言、面向...

2199
来自专栏程序员的SOD蜜

使用CTE解决复杂查询的问题

最近,同事需要从数个表中查询用户的业务和报告数据,写了一个SQL语句,查询比较慢: Select S.Name, S.AccountantCode, ( Se...

2096
来自专栏IT可乐

Java 多线程详解(三)------线程的同步

Java 多线程详解(一)------概念的引入:https://cloud.tencent.com/developer/article/1012542 Jav...

18210
来自专栏JavaQ

三分钟学习分布式ID方案

在分布式系统中,当数据库数据量达到一定量级的时候,需要进行数据拆分、分库分表操作,传统使用方式的数据库自有的自增特性产生的主键ID已不能满足拆分的需求,它只能保...

922
来自专栏三木的博客

Linux系统中的信号量机制

1、信号量的定义: struct semaphore { spinlock_t lock; unsigned int count; st...

1776
来自专栏菩提树下的杨过

Replace方法与正则表达式的性能比较

今天做项目时遇到一个小需求:要将字符串中的回车符号替换成其它符号(比如"<br/>")。 考虑到不同的情况下,有些系统中是用\r\n作回车符,有些仅用\n就代表...

1889
来自专栏java工会

java设计模式-建造者模式

1819
来自专栏james大数据架构

你真的会玩SQL吗?让人晕头转向的三值逻辑

你真的会玩SQL吗?系列目录 你真的会玩SQL吗?之逻辑查询处理阶段 你真的会玩SQL吗?和平大使 内连接、外连接 你真的会玩SQL吗?三范式、数据完整性 你真...

1788
来自专栏文渊之博

参数化(四):处理非均匀数据分布

    前面我们了解了参数嗅探可能是好的也可能是坏的。当数列的分布不均匀的时候参数嗅探就是不好的事情。例如,考虑“Status”列在Orders表中有总共10M...

1958
来自专栏用户画像

3.2.8 虚拟内存管理

多道程序并发执行不仅使进程之间共享了处理器,还同时共享了主存。然而,随着处理器需求的增长,进程的执行速度会以某种合理平滑的方式慢下来。但是,如果同时执行的进程太...

492

扫码关注云+社区