第四章T-SQL 高级查询1

一、简介

        在用SELECT语句时,很多时候需要查看数据统计或运算结果,而非仅仅是现有数据的罗列。还有很多时候,需要查看的数据来自于多个表,掌握这些技能对于实际工作非常重要。本章主要讲解常用函数的使用方法,分组查询和多表查询。

二、函数的分类

        为了更方便快捷地完成大量的任务,SQL Server提供了一些内部函数,可以和SQL Server 的 SELECT语句联合使用.也可以与UPDATE和INSERT一起使用,可以进行类型转换、日期处理、数学计算,实现系统功能。

根据用途,把函数分为五类,分别是系统函数、字符串函数、日期函数、聚合函数与数学函数。

1.系统函数

系统函数用来获取有关SQL Server中对象和设置的系统信息,下表是常用的一些系统函数

        上面所有这些函数,可以在T-SQL中混合使用,得到符合特殊要求的查询输出。

        CONVERT 和CAST 的类型转换使用频繁,在很多场合均可使用。尤其是在网站应用中,常常需要将各种类型的数据转换成指定格式的字符串,然后拼接成显示内容.

2.字符串函数

字符串函数用于控制返回给用户的字符串,这些功能仅用于字符型数据。下表是部分常用的字符串函数。

        字符串在信息处理时有特殊的地位,几乎所有信息都需要转换成字符串才能正确显示,尤其是不同数据拼接起来显示的使用更加广泛。

        字符串拼接很简单.两个字符串之间使用“+”即可。

语法:

3.日期函数

        在SQL Server中不能直接对日期运用数学函数,需要使用日期函数操作曰期值。例如,如果执行一个诸如“当前日期+1”的语句,SQL Server无法理解要增加的是一日、一月还是一年。

        日期函数帮助提取日期值中的日、月及年,以便分别操作它们,如下表列出了部分常用的臼期函数。

例一、查距离10天后的日期和时间

语法: SELECT DATEADD (DD,10,GETDATE () )

例二、查所有人的姓名和年龄

语法:SELECT 姓名,DATEDIFF (YY,出生日期,GETDATE () ) AS 年龄 FROM employee

例三、查某个年龄段员工的姓名和出生年份

语法:
SELECT 姓名,DATENAME (YEAR,出生日期) AS 出生年份
FROM employee
WHERE 出生日期 BETWEEN '1990-01-01' AND '1999-12-31'

注:在输入代码的时候,要注意输入法的问题,一定要保证是英文输入法状态,否则会报错。

4.聚合函数与分组查询

        在查询时还会经常碰到的是取某些列的最大值、最小值、平均值等信息,有时候还需要计算出究竟查询到多少行数据项。这个时候,查询的“统计数据”是用户比较关心的,因此 SQL Server提供了聚合函数,聚合函数能够基于列进行计算,并返回单个值。

        分组查询是指将查询结果按条件分组,然后使用聚合函数返回每个组的汇总信息。分组查询一般是用来满足统计需求的。

聚合函数

常用的聚合函数有SUM( )、AVG( )、MAX( )、MN( )和COUNT( )。

  1. SUM )函数返回表达式中所有数值的总和,它只能用于数字类型的列,不能够汇总字符、日期等其他数据类型。
    查所有员工基本工资的总和
    语法:SELECT SUM (基本工资) AS 总工资 FROM employee
  2. AVG()函数返回表达式中所有数值的平均值,它也只能用于数字类型的列。
    查所有员工的平均基本工资
    语法:SELECT AVG (基本工资) AS 平均工资 FROM employee
  3. MAX( )和MIN( )。MAX( )函数返回表达式中的最大值,MIN( )函数返回表达式中的最小值,它们都可以用于数字型、字符型及日期/时间类型的列。
    查最高和最低的基本工资
    语法:SELECT MAX (基本工资) AS 最高工资,MIN (最低工资) AS 最低工资 FROM employee
  4. COLUNT( )函数返回表达式中非空值的计数,它可以用于数字和字符类型的列。另外,也可以使用星号(*)作为COUNT( )函数的表达式,使用星号可以不必指定特定的列而计算所有的行数。
    查表的全部行数
    语法:SELECT COUNT (*) AS 总行数 FROM employee
    ---------------------------------------------------------------
    查某个年龄段员工的人数
    语法:
    SELECT COUNT (出生日期) AS '90后人数'
    FROM employee
    WHERE 出生日期>='1990-01-01'     -----如果表中有00后的,根具上面的一些查询修改一下
分组查询

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

例一:查每个职务的平均工资

语法:SELECT 职务,AVG (基本工资) AS 职务的平均工资 FRPM employee GROUP BY 职务

        不难理解,在使用GROUP BY关键字时,在SELECT 列表中可以指定的项目是有限的,SELECT语句中仅允许以下几项。

  • 被分组的列。
  • 为每个分组返回一个值的表达式,如用一个列名作为参数的聚合函数。

例二:查平均工资小于10000的职务

语法:SELECT 职务,AVG (基本工资) AS 职务的平均工资 FROM employee GROUP BY 职务
      HAVING VG (基本工资)<10000

当GROUPBY子句中使用HAVING子句时.查询结果只返回满足HAVNG条件的组。在一个T-SQL语句中可以有WHERE子句和HAVING子句,HAVING子句与WHERE子句类似.均用于设置限定条件。但HAVNG子句和WHERE子句的作用有如下区别。

  • WHERE子句的作用是在对查询结果进行分组前.根据WHERE条件筛选数据.条件中不能包含聚合函数。
  • HAVNG子句的作用是在分组之后筛选满足条件的组,条件中经常包含聚合函数,也可以使用多个分组标准进行分组。
  • 当WHERE和HAViNG同在一个SELECT 语句中使用时,执行顺序为“WHERE一GROUP BY→HAVNG"。

例三:差平均工资小于10000的职务,并且指定某个员工不计算在内

语法:
SELECT 职务,AVG (基本工资) AS 职务总工资 FROM employee
WHERE 姓名 !='段誉'
GROUP BY 职务
HAVING AVG (基本工资)<10000

        在上面这个实例中,分组前根据WHERE条件进行筛选。因此,在用GROLP BY按职务进行分组之前,已经过滤了段誉的数据,导致参与分组的DBA只有黄蓉一人、DBA的平均工资也只是计算黄蓉一人的,即10 000。分组完成后,再对分组结果用HAVING进行筛选,所以DBA的平均工资不满足筛选条件,被排除在外。

5.数学函数

数学函数用于对数值进行代数运算,由于数学函数数量众多,不可能全部列举,如下表列出了SOL Server中常用的数学函数。

例一:查所有人的平均工资,用CEILING( )取整

语法:SELECT CEILING (AVG (基本工资) ) AS 平均工资 FROM employee

三、综合函数应用

        在实际生产环境中可以根据需要,将函数进行不同的组合,以满足实际需求。

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

解决思路分为两个大的步骤:查询正确数据结果.将结果用字符串拼接。

1.查询正确数据结果
  • 计算年龄使用日期函数DATEDIFF( ),比较“出生日期”和“当前日期”,其中使用参数“YY"取其Year的差。
  • 计算距离30 岁的天数也使用日期函数DATEDIFF ( ),比较“当前日期”和“出生日期+30年”,其中使用DATEADD( )实现将出生日期加30年、使用参数“DD”比较Day的差。
语法:
SELECT 姓名,DATEDIFF (YY,出生日期,GETDATE () ) AS 年龄,DATEDIFF
(DD,GETDATE (),DATEADD (YY,30,出生日期) ) AS 距离30岁天数
FROM employee
WHERE DATEDIFF (YY,出生日期,GETDATE () ) <=30
ORDER BY 出生日期

2.将结果用字符串拼接
  • 使用系统函数CONVERT ( varchar ( 10),出生日期,111)将DATETME类型转换为字符型,定义长度10的目的是不显示10个字符以后的内容,“111”是参数style 的类型值,参数style代表日期格式,有很多种,“111”代表的style格式是“yyyy/mm/dd” (yyyy /mm/dd格式的长度就是10个字符)。
  • 使用系统函数CAST(}将年龄和天数转化为字符串。
语法:
SELECT '员工' +姓名+
',现在年龄是,+CAST (DATEDIFF(YY,出生日期,GETDATE ( ) ) AS varchar (10) )+'岁'+
',距离30岁生日还有'+
CAST (DATEDIFF(DD,GETDATE( ),DATEADD(YY,30,出生日期) ) AS varchar (10) ) +'天'
FROM employee
WHERE DATEDIFF (YY,出生日期,GETDATE () )<=30
ORDER BY 出生日期

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 3
    评论
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值