>

【SqlServer种类】子查询

- 编辑:www.bifa688.com -

【SqlServer种类】子查询

1   概述

1   概述

1.1  已发布【SqlServer系列】文章

1.1  已发布【SqlServer系列】文章

  • 【SqlServer系列】SQLSERVER安装教程 

  • 【SqlServer系列】数据库三大范式

  • 【SqlServer系列】表单查询
  • 【SqlServer系列】表连接
  • 【SqlServer系列】子查询
  • 【SqlServer系列】远程访问
  • 【SqlServer系列】集合运算
  • 【SqlServer系列】SQLSERVER安装教程 

  • 【SqlServer系列】数据库三大范式

  • 【SqlServer系列】表单查询
  • 【SqlServer系列】表连接
  • 【SqlServer系列】子查询
  • 【SqlServer系列】开启Sqlserver远程访问
  • 【SqlServer系列】集合运算

1.2  本篇文章内容概要

1.2  本篇文章内容概要

   子查询和表连接,主要是针对两张及以上表之间关联关系进行查询。子查询,按是否独立划分,分为独立子查询和相关子查询;按内部查询返回是否为单值,分为单值子查询(注意:应理解单值子查询和标量子查询关系)和多值子查询;如下表格内容为按子查询是否独立来划分的。

88bifa必发唯一官网 1

88bifa必发唯一官网 2

1.3  本篇文章内容概括

1.3 本章测试样表和sql

在SQL语句中,关于表连接,若按照表的数量来划分,可以划分为单表连接、两表连接和两表以上连接,在本篇文章中,主要讲解两表连接,其他多表连接原理一样。

业务场景:

关于表连接有很多种类,本文主要讲解交叉连接,内连接,外连接(左外部连接,右外部连接,全连接),自连接。

有两张表,分别为顾客表(顾客ID,顾客姓名,顾客公司),即Customers(CustID,CustName,CustCompany)和顾客订单表(顾客ID,订单ID,订单日期),即Orders(CustID,OrderID,OrderDate),两张表通过顾客ID(CustID)进行关联。

1.4  本章测试样表和Sql

88bifa必发唯一官网 3

业务场景:有两张表,分为为顾客表Customers和顾客订单表Orders,SQL语句分别如下:

SQL语句分别如下:

创建Customes并初始化

(1)创建子查询DB:WJM_CHildQuery

 1 --CREATE TABLE Customers
 2 CREATE TABLE Customers
 3 (
 4    CustID VARCHAR(50) NOT NULL,  --顾客ID
 5    CustName  VARCHAR(50),--顾客姓名
 6    CustCompany VARCHAR(50) --顾客公司
 7 )
 8 
 9 --Initial Customers
10 
11 INSERT INTO Customers VALUES('SXN-DD-01','赵武','A')
12 INSERT INTO Customers VALUES('SXN-DD-02','刘杨','B')
13 INSERT INTO Customers VALUES('SXN-DD-03','张永为','C')
14 INSERT INTO Customers VALUES('SXN-DD-04','李龙飞','D')
15 INSERT INTO Customers VALUES('SXN-FF-01','邓华','E')
16 INSERT INTO Customers VALUES('SXN-HH-01','张涛明','F')
1 --创建子查询数据库WJM_ChildQuery
2 IF DB_ID('WJM_CHildQuery') IS NOT NULL
3    DROP DATABASE WJM_ChildQuery
4    GO
5    CREATE DATABASE WJM_ChildQuery

查询结果为:

(2)创建Customers表并初始化

88bifa必发唯一官网 4

 1 USE WJM_CHildQuery
 2 
 3 --CREATE TABLE Customers
 4 CREATE TABLE Customers
 5 (
 6    CustID INT IDENTITY(1,1)  NOT NULL PRIMARY KEY,  --顾客ID
 7    CustName  VARCHAR(50),--顾客姓名
 8    CustCompany VARCHAR(50) --顾客公司
 9 )
10 
11 --Initial Customers
12 
13 INSERT INTO Customers VALUES('赵武','A')
14 INSERT INTO Customers VALUES('刘杨','B')
15 INSERT INTO Customers VALUES('张永为','C')
16 INSERT INTO Customers VALUES('李龙飞','D')
17 INSERT INTO Customers VALUES('邓华','E')
18 INSERT INTO Customers VALUES('张涛明','F')

创建Order表并初始化

88bifa必发唯一官网,执行查询语句:

 1 CREATE TABLE Orders
 2 (
 3   CustID VARCHAR(50) NOT NULL,  --顾客ID
 4   OrdetID VARCHAR(50)  --订单ID
 5 )
 6 
 7 --Initial Orders
 8 
 9 INSERT INTO Orders VALUES('SXN-DD-01','SCCCCFFFFFSSOX002')
10 INSERT INTO Orders VALUES('SXN-DD-02','SCCCCFFFFFSSOX0X2')
11 INSERT INTO Orders VALUES('SXN-DD-03','')
12 INSERT INTO Orders VALUES('SXN-DD-04','')
13 INSERT INTO Orders VALUES('SXN-DD-05','SCCCCFFFFFSSOX0H2')
14 INSERT INTO Orders VALUES('SXN-DD-06','')
1 SELECT *
2 FROM Customers

查询结果为:

查询结果为:

88bifa必发唯一官网 5

88bifa必发唯一官网 6

 

 (3)创建Orders表并初始化

 

 1 USE WJM_CHildQuery
 2 
 3 CREATE TABLE Orders
 4 (
 5   OrderID INT IDENTITY(1,1) PRIMARY KEY NOT NULL, --订单ID
 6   CustID INT NOT NULL,  --顾客ID
 7   OrderDate DATE --订单日期
 8 ) 
 9 
10 --Initial Orders
11 
12 INSERT INTO Orders VALUES(1,'2015-06-25')
13 INSERT INTO Orders VALUES(2,'2017-06-03')
14 INSERT INTO Orders VALUES(3,'2016-04-25')
15 INSERT INTO Orders VALUES(4,'2016-03-04')
16 INSERT INTO Orders VALUES(5,'2013-03-04')
17 INSERT INTO Orders VALUES(6,'2017-01-01')
18 INSERT INTO Orders VALUES(2,'2016-08-25')
19 INSERT INTO Orders VALUES(5,'2016-12-25')

2   问题引入

执行查询语句:

 Q1:写一个查询,生成从1到1000的整数序列。

1 SELECT *
2 FROM Orders

3   交叉连接

查询结果为:

3.1   SQL示例及示例结果

88bifa必发唯一官网 7

1 SELECT  C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID
2 FROM Customers AS C
3 CROSS JOIN Orders AS O

2  独立子查询

结果:

2.1  独立标量子查询

88bifa必发唯一官网 8

问题:请用两种方法返回Orders表中的订单ID最大的订单信息。

3.2   示例结果分析

方法一:标量单值子查询

交叉连接使用关键字CROSS JOIN进行查询,查询的结果为笛卡儿积,从如上结果可以看出,查询出的结果共有36行数据,因为Customers表和Orders表分别有6条记录,6X6=36;

1 --返回Orders表中订单ID最大的记录
2 SELECT OutQueryOrders.OrderID,OutQueryOrders.CustID,YEAR(OutQueryOrders.OrderDate) AS OrderDateYear
3 FROM Orders AS OutQueryOrders
4 WHERE OutQueryOrders.OrderID=
5 (
6    SELECT MAX(InnerQueryOrders.OrderID)
7    FROM Orders InnerQueryOrders
8 )

3.3  小结

查询结果:

a.在逻辑上,交叉连接是一种最简单的联接;
b.交叉连接只实现一个逻辑处理步骤———笛卡儿积;
c.操作:对输入的两个表进行操作,把它们连接起来,生成两者的笛卡儿积,即将一个输入表的每行与另一个表的所以行进行匹配,如果一个表有m行,而另一个表有n行,将得到m x n行的接果集;
d.结构:
  SELECT  tb1.tb1ConumName,tb2.tb2ConumName
  FROM table1 AS tb1 
  CROSS JOIN table2 AS tb2
e.交叉连接使用的关键字:CROSS JOIN ;
f.交叉连接生成的接果集是一个虚拟表,虚拟表中的各列直接源于参与连接的两个表;

88bifa必发唯一官网 9

 

结果分析

4   内连接

a.期望结果

4.1   SQL示例及示例结果

88bifa必发唯一官网 10

1 SELECT  C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID
2 FROM Customers AS C
3 INNER JOIN Orders AS O
4 ON C.CustID=O.CustID

b.查询语句分析

结果:

88bifa必发唯一官网 11

88bifa必发唯一官网 12

方法二:采用变量

4.2   示例结果分析

1 DECLARE @MaxOrderID AS INT=
2 (
3    SELECT MAX(OD.OrderID)
4    FROM Orders AS OD
5 )
6 
7 SELECT *
8 FROM Orders 
9 WHERE Orders.OrderID=@MaxOrderID

内连接在交叉连接的基础上外加过滤条件ON,如上例子中用Customers.CustID=Orders.CustID作为过滤条件,结果显而易见。

查询结果:

4.3  小结

88bifa必发唯一官网 13

内联接规则为笛卡尔积 用户谓词过滤:它首先像交叉连接一样,对两个输入表进行笛卡尔积运算,然后根据用户指定的谓词对结果进行过滤;

结果分析:

 

a.期望结果

5   外连接

88bifa必发唯一官网 14

5.1   SQL示例及示例结果(只分析左外部连接,因为右连接和全连接原理也是一样的)

 

1 SELECT  C.CustID,C.CustName,C.CustCompany,O.CustID,O.OrdetID
2 FROM Customers AS C
3 LEFT OUTER JOIN Orders AS O
4 ON C.CustID=O.CustID

b.查询语句分析

结果:

88bifa必发唯一官网 15

88bifa必发唯一官网 16

2.2  独立子查询

5.2   示例结果分析

问题:返回订单表中订单ID>5的顾客信息

如上以Cutomers表作为左保留表,连接右表Orders列CustID缺少SXN-FF-01和SXN-HH-01,为了以左保留表为基准,用NULL占位符来填充。

1 --返回订单表中订单ID>5的顾客信息
2 SELECT C.CustID,C.CustName,C.CustCompany
3 FROM Customers AS C
4 WHERE C.CustID  IN
5 (
6     SELECT Orders.CustID
7     FROM Orders
8     WHERE Orders.OrderID>5
9 )

5.3  小结

查询结果:

a.外连接:笛卡儿积 ON过滤 外部行;
b.在外连接中,要把一个表标记为“保留的”表,可以在表名之间使用关键字LEFT OUTER JOIN、RIGHT OUTER JOIN、FULL OUTER JOIN,其中OUTER关键字是可选的。LEFT关键字表示左边表的行是保留的,RIGHT关键字表示右边表的行是保留的,而FULL关键字则表示左右两边表的行都是保留的;
c.外连接的第三个逻辑查询处理步骤就是要识别保留表中按照ON条件在另一个表找不到与之匹配的那些行,再把这些行添加到连接的前两个步骤生成的结果中。对于来自连接的非保留表的那些列,追加的外不行中的这些列则用NULL作为占位符;
d.从外连接保留表的角度来看,可以认为外连接结果中的数据行包括两种内部行和外部行。内部行是指按照ON子句中的条件能在连接的另一边找到匹配的那些行;而外部行则是指找不到匹配的那些行。内连接只返回内部行,外连接同时返回内部行和外部行;
e.使用外连接时,到底是在查询的ON子句中,还是在WHERE子句中指定连结条件?从外连接保留表中的行来考虑该问题,ON子句中的过滤条件不是最终的,换句话说,ON子句中的条件并不能最终决定保留表中的部分行是否会在结果中出现,而只是判断是否能够匹配另一边表中的某些行。所以,当需要表达一个非最终的条件时(即这个条件只决定哪些行可以匹配非保留表),就在ON子句中指定连接条件,当在生成外部行以后,要运用过滤器,而且希望过滤条件是最终的,就应该在WHERE子句中指定条件;

88bifa必发唯一官网 17

 

结果分析:

6   自连接

a.期望结果

6.1   SQL示例及示例结果

88bifa必发唯一官网 18

1 SELECT  C1.CustID AS C1CustID,C1.CustName AS C1CustName,C1.CustCompany AS C1CustCompany,C2.CustID,C2.CustName,C2.CustCompany
2 FROM Customers AS C1
3 JOIN Customers AS C2
4 ON C1.CustID=C2.CustID

b.查询语句分析

结果:

88bifa必发唯一官网 19

88bifa必发唯一官网 20

2.3  小结

6.2   示例结果分析

a.每个子查询均有所属的外部查询;
b.独立子查询独立于其外部查询;
c.可以把独立子查询单独运行;
d.独立子查询在执行外部查询之前先执行一次,接着外部查询再使用子查询的结果继续进行查询;
e.标量子查询只返回单个值,不管其是否是独立子查询;
f.标量子查询可以出现在外部查询中期望使用单个值的任何地方(WHERE、SELECT等)
g.算术运算符(>,>=,<,<=,!=,=)其后只能为单值;
h.如果标量子查询没有返回任何值,其结果就转换为NULL,和NULL进行比较得到的是UNKNOWN,查询过滤器不会返回任何让过滤表达式计算结果为UNKOWN的行;
i.多值子查询,一般与谓词连用,如IN,NOT IN,EXISTS等;
j.在子查询中,一般单值和多值是混合用,如常见与BETWEEN......AND.....连用等;

如上例子为自连接在内连接中的运用,在其他连接中的运用就不举例子了,比较简单。

3   相关子查询

6.3  小结

3.1  SQL示例及示例结果

a.自连接为单个表取不同的别名,通过别名来连接;
b.自连接可以用于其它连接;
b.自连接可以看作交叉连接、内连接、外连接等连接的一个特例;

问题:在订单表Orders表中,为每个客户返回其订单ID最大的订单

 

 1 --为每个客户返回其订单ID最大的订单
 2 
 3 SELECT OutQueryOrders.OrderID,OutQueryOrders.CustID,YEAR(OutQueryOrders.OrderDate) AS OrderDateYear
 4 FROM Orders AS OutQueryOrders
 5 WHERE OutQueryOrders.OrderID=
 6 (
 7    SELECT MAX(InnerQueryOrders.OrderID)
 8    FROM Orders InnerQueryOrders
 9    WHERE OutQueryOrders.CustID=InnerQueryOrders.CustID
10 )

7   问题答案

查询结果:

Q1:KEY

88bifa必发唯一官网 21

CREATE TABLE Digits
( 
  digit int not null primary key
)
--Initial testing data for Digits
INSERT INTO Digits VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)

--Query

SELECT D3.digit*100 D2.digit*10 D1.digit 1 AS n
FROM Digits AS D1
CROSS JOIN Digits AS D2
CROSS JOIN Digits AS D3
ORDER BY n

3.2  示例结果分析

 

a.期望结果

8   参考文献

88bifa必发唯一官网 22

【01】Microsoft  SqlServer 2008技术内幕:T-SQL 语言基础

 

【02】Microsoft  SqlServer 2008技术内幕:T-SQL 查询

b.查询语句分析

9   版权

88bifa必发唯一官网 23

 

3.3  小结

  • 感谢您的阅读,若有不足之处,欢迎指教,共同学习、共同进步。
  • 博主网址:
  • 极少部分文章利用读书、参考、引用、抄袭、复制和粘贴等多种方式整合而成的,大部分为原创。
  • 如您喜欢,麻烦推荐一下;如您有新想法,欢迎提出,邮箱:2016177728@qq.com。
  • 可以转载该博客,但必须著名博客来源。
a.相关子查询内部查询依赖于外部查询,不能独立运行;
b.相关子查询内部查询中,受限制于外部查询的条件,如上述例子中的WHERE之后的条件;
c.在逻辑上,子查询会为每个外部查询单独计算一次;
d.多值子查询,一般与谓词连用,如IN,NOT IN,EXISTS等;
e.在子查询中,一般单值和多值是混合用,如常见与BETWEEN......AND.....连用等;

4   子查询拓展

 关于子查询,还有很多内容,如返回前后记录查询,连续聚合,行为不当等,但这部分内容属于子查询的中高级部分,准备放在【SqlServer系列】中高级部分来与大家分享。

5   参考文献

【01】Microsoft  SqlServer 2008技术内幕:T-SQL 语言基础

【02】Microsoft  SqlServer 2008技术内幕:T-SQL 查询

6   版权

 

  • 感谢您的阅读,若有不足之处,欢迎指教,共同学习、共同进步。
  • 博主网址:
  • 极少部分文章利用读书、参考、引用、抄袭、复制和粘贴等多种方式整合而成的,大部分为原创。
  • 如您喜欢,麻烦推荐一下;如您有新想法,欢迎提出,邮箱:2016177728@qq.com。
  • 可以转载该博客,但必须著名博客来源。

本文由88bifa必发唯一官网发布,转载请注明来源:【SqlServer种类】子查询