T-SQL的高级查询语法

清疚 2021-11-10 11:02 309阅读 0赞

既然是高级查询，那么使用函数也将是必不可少的，常见的函数类型有以下几种：  
![T-SQL的高级查询语法][T-SQL]![T-SQL的高级查询语法][T-SQL 1]![T-SQL的高级查询语法][T-SQL 2]

常见的高级查询语法举例：

**显示test表中所有运维工程师的姓名和基本工资，要求以下格式：  
运维工程师XXX的基本工资是：XXXXX元。**  
语法格式如下：

`select '运维工程师'+姓名+'的基本工资是:'+CAST(基本工资 as varchar(10))+'元'`  
`from test where 职务='运维工程师'`

**显示距离当前10天之后的日期和时间：**

`select DATEADD(DD ,10,GETDATE())`

**显示test表中所有人的姓名和年龄：**

`select 姓名, DATEDIFF(YY,出生日期,GETDATE()) 年龄 from test`

**显示test表中所有90后员工的姓名和出生年份：**

`select 姓名,DATENAME(YY,出生日期) 出生年份 from employee`  
`where 出生日期 between '1990-01-01' and '1999-12-31'`

**聚合函数与分组查询：**

聚合函数能够基于列进行计算，并返回单个值，分组查询是指将查询结果按条件分组，然后使用聚合函数返回每个组的汇总信息，分组查询一般是用来满足统计需求的。

常用的聚合函数有：SUM( )、AVG( )、MAX( )、M I N( )、COUNT(　）。

SUM( )：用来返回表达式中所有数值的总和，它只能用于数字类型的列，不能汇总字符、日期等。

**如：查询test表中所有员工基本工资的总和**

`select SUM(基本工资) 总工资 from test`

AVG（ ）：用来返回表达式中所有数值的平均值，它也只能用于数字类型的列。

**查询test表中所有员工的平均基本工资**

`select AVG(基本工资) 平均工资 from test`

MAX( )、M I N( )：前者用来返回表达式中的最大值，后者用来返回表达式中的最小值。它们都可以用于数字型、字符型及日期/时间类型的列。

**如：查询test表中的最高工资和最低工资：**

`select MAX(基本工资) 最高工资,MIN(基本工资) 最低工资 from test`

COUNT（）：返回表达式中非空值的技术，可以用于数字和字符类型的列，也可使用星号`*`作为COUNT（）函数的表达方式，使用星号`*`可以不必指定特定的列而计算所有的行数。

**查询test表中全部行数：**

`select COUNT(*) from test`

**查询test表中所有90后的人数：**

`select COUNT(出生日期) '90后人数' from test`  
`where 出生日期>='1990-01-01' and 出生日期<='1999-12-31'`

**分组查询：就是将表中的数据通过GROUP BY的子句分类组合，再根据需要得到要统计的信息。如果需要对分组进行筛选，只显示满足限定条件的组，需要时用HAAVING子句。**

**查询test表中每个职务的平均工资：**  
`select 职务,AVG(基本工资) 职务平均工资 from test group by 职务`

**查询test表中平均工资小于10000的职务：**

`select 职务,AVG(基本工资) 平均工资 from test group by 职务 having AVG(基本工资)<10000`

当group by 子句中使用having 子句时，查询结果只返回满足having条件的组。在一个T-SQL语句中可以有where子句和having子句，having子句与where子句类似，均用于设置限定条件。二者作用的区别如下：

①：where子句的作用是在对分组查询结果进行分组之前，根据where条件筛选数据，条件中不能包含聚合函数。  
②：having子句的作用是在分组之后筛选满足条件的组，条件中经常包含聚合函数，也可以使用多个分组标准进行分组。

**查询test表中平均工资小于10000的职务，前提是员工张三不计算在内：**

select 职务,AVG(基本工资) 平均工资 from employee
      where 姓名!='张三'
      group by 职务 having AVG(基本工资)<10000

![T-SQL的高级查询语法][T-SQL 3]

**查询test表中所有人的平均工资，用CEILING( )取整**

`select CEILING(AVG(基本工资)) 平均工资 from test`

**函数的综合应用：**

**查询未满30岁的员工的生日和年龄，并且计算出距离30岁的天数，最后用字符串拼接显示结果：**

①：查询正确的结果

select 姓名,出生日期,DATEDIFF(YY,出生日期,GETDATE()) 年龄,
      DATEDIFF(DD,GETDATE(),DATEADD(YY,30,出生日期)) 距离三十岁的天数
      from test
      where DATEDIFF(YY,出生日期,GETDATE())<=30
      order by 出生日期

②将结果用字符串拼接：

select '员工'+姓名+'的生日是:'+CONVERT(varchar(10),出生日期,111)+
      ',现在年龄是'+CAST(DATEDIFF(YY,出生日期,GETDATE()) AS varchar(10))
      +'岁'+',距离三十岁还有'+
      CAST( DATEDIFF(DD,GETDATE(),DATEADD(YY,30,出生日期)) AS varchar(10))+'天'
      from employee
      where DATEDIFF(YY,出生日期,GETDATE())<=30
      order by 出生日期

最终查询显示结果如下图所示：  
![T-SQL的高级查询语法][T-SQL 4]

**多表查询的案例：  
案例①**  
**使用T-SQL实现多表查询（内联接、左外联接、右外联接、全联接/完整外联接）：**  
原表如下：  
![T-SQL的高级查询语法][T-SQL 5]表A  
![T-SQL的高级查询语法][T-SQL 6]表B

**使用内联接在表A和表B中使用内联接查询学生姓名、学校和职业。（两种查询方式）**

select a.name 姓名,a.school 学校,b.name,b.job 
    from A,B
    where a.name=b.name

或：

select a.name 姓名,a.school 学校,b.name,b.job 
    from A inner join B on a.name=B.name

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 7]

**使用左外联接查询在表A和表B中使用内联接查询学生姓名、学校和职业。**

select a.name 姓名,a.school 学校,b.name,b.job 
    from A left join B on a.name=B.name

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 8]

**使用右外联接查询在表A和表B中使用内联接查询学生姓名、学校和职业。**

select a.name 姓名,a.school 学校,b.name,b.job 
    from A right join B on a.name=B.name

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 9]

**使用完整外联接查询在表A和表B中使用内联接查询学生姓名、学校和职业。**

select a.name 姓名,a.school 学校,b.name,b.job 
    from A full join B on a.name=B.name

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 10]

**案例②**

原表如下 ：

![T-SQL的高级查询语法][T-SQL 11]

**查询出厂日期晚于2011年4月的水果信息。**

select * from products
    where 种类='水果'
    and 出厂日期>'2011-04-01'

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 12]

**分组查询所有水果、蔬菜、坚果的总成本。**

select 种类,SUM(成本) from products
    group by 种类

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 13]

**查询所有水果的名称和出厂日期，以特定格式拼接字符串，如：“西瓜的出厂日期是：2017/05/06”**

select 种类+'的出厂日期是:'
    +CONVERT(varchar(10),出厂日期,111)
    from products

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 14]

**查询所有蔬菜的平均成本：**

select 种类,AVG(成本)
    from products
    group by 种类
    having 种类='蔬菜'

查询结果如下：

![T-SQL的高级查询语法][T-SQL 15]

**案例②（多表查询）：**  
原表如下：

表products和表sales

![T-SQL的高级查询语法][T-SQL 16]

![T-SQL的高级查询语法][T-SQL 17]

**在两个表中查询产品的名称、种类、成本、销售地点和销售价格：**

select products.名称,products.种类,
    products.成本,sales.销售地点,sales.销售价格
    from products inner join sales 
    on products.名称=sales.名称

查询结果如下：

![T-SQL的高级查询语法][T-SQL 18]

**在两个表中查询销往海南的产品名称、种类、成本和销售价格：**

select products.名称,products.种类,
    products.成本,sales.销售价格
    from products inner join sales 
    on products.名称=sales.名称
    and sales.销售地点='海南'

查询结果如下：

![T-SQL的高级查询语法][T-SQL 19]

**在两个表中查询销往北京的蔬菜名称、种类、成本和销售价格：**

select products.名称,products.种类,
    products.成本,sales.销售价格
    from products inner join sales 
    on products.名称=sales.名称
    and sales.销售地点='北京'
    where 种类='蔬菜'

查询结果如下：  
![T-SQL的高级查询语法][T-SQL 20]

[T-SQL]: https://s1.51cto.com/images/blog/201904/05/1554668c919e95e91408296165f866b7.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 1]: https://s1.51cto.com/images/blog/201904/05/de04980495a84f83a9c14209cd964f34.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 2]: https://s1.51cto.com/images/blog/201904/05/e2a993a3baedcc9f5a27247f041e4e88.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 3]: https://s1.51cto.com/images/blog/201904/05/51a77ba83ca24f66ba0895c4d29a6a18.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 4]: https://s1.51cto.com/images/blog/201904/05/c103422f3f7b41574c218b92cdba5aa9.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 5]: https://s1.51cto.com/images/blog/201904/05/545e91f5513b8021739452d7bb928ee6.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 6]: https://s1.51cto.com/images/blog/201904/05/73a1ea96e142a0dd9929c1904561619c.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 7]: https://s1.51cto.com/images/blog/201904/05/eabedc1a4384a2ee6006900f3527c6c1.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 8]: https://s1.51cto.com/images/blog/201904/05/396c4f52b0801ce34054d4eb945ba125.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 9]: https://s1.51cto.com/images/blog/201904/05/65cfb28aaf12e84fc31db2cdbb14253e.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 10]: https://s1.51cto.com/images/blog/201904/05/42ddb3acb35be805791c8e31079aebe3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 11]: https://s1.51cto.com/images/blog/201904/05/e6311193b386d3c21c16f259fab9befa.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 12]: https://s1.51cto.com/images/blog/201904/05/19f0b858274a922cd3c5d3e7f2be37ef.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 13]: https://s1.51cto.com/images/blog/201904/05/af4507a0a840b4d7dfaafc73dd63fa83.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 14]: https://s1.51cto.com/images/blog/201904/05/cb25bb1ebdc52ff235a940935d789bff.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 15]: https://s1.51cto.com/images/blog/201904/05/315f354320f98a72c65b0ac3ae8be634.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 16]: https://s1.51cto.com/images/blog/201904/05/1c52725d26924666121f8a3a4351d375.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 17]: https://s1.51cto.com/images/blog/201904/05/5b386d3bf026b99a91648679df249334.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 18]: https://s1.51cto.com/images/blog/201904/05/0bd6d9032a5ae5509317bab0729db3ea.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 19]: https://s1.51cto.com/images/blog/201904/05/cbf7e2326b02e205cf88b9b2710cd7d4.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=
[T-SQL 20]: https://s1.51cto.com/images/blog/201904/05/1b446b86ac1675518a2664cbd9f9e4d3.png?x-oss-process=image/watermark,size_16,text_QDUxQ1RP5Y2a5a6i,color_FFFFFF,t_100,g_se,x_10,y_10,shadow_90,type_ZmFuZ3poZW5naGVpdGk=