既然是高级查询,那么使用函数也将是必不可少的,常见的函数类型有以下几种:

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

显示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

查询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实现多表查询(内联接、左外联接、右外联接、全联接/完整外联接): 原表如下: 表A 表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 

查询结果如下:

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

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

查询结果如下:

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

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

查询结果如下:

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

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

查询结果如下:

案例②

原表如下 :

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

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

查询结果如下:

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

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

查询结果如下:

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

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

查询结果如下:

查询所有蔬菜的平均成本:

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

查询结果如下:

案例②(多表查询): 原表如下:

表products和表sales

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

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

查询结果如下:

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

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

查询结果如下:

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

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

查询结果如下: