SQL Server数据库的T-SQL高级查询

一、查询使用的函数

请自行创建数据库和表，表中插入数据，我的样例如下：

create database accp;    <!--创建accp数据库-->
use accp;     <!--切换到accp数据库-->
create table test (            <!--创建test表-->
    编号  int     identity(1,1) not null,
    姓名  nvarchar(4) not null,
    证件号码 varchar(18) primary key,
    职务  nvarchar(6) null,
    入职时间 datetime   null,
    出生日期 datetime null,
    基本工资 money null check (基本工资 >=0 and 基本工资 <= 10000),
);
select * from test;      <!--查看test表结构-->
alter   table test add 备注 nvarchar(50);    <!--添加一列备注列-->
     <!--表中插入数据 -->
insert into test values (‘王三‘,‘111111111111111111‘,‘初级运维‘,‘2011/3/8‘,‘1980/12/3‘,7500,‘老员工‘);
insert into test values (‘杜五‘,‘222222222222222222‘,‘中级运维‘,‘2007/7/24‘,‘1999/9/7‘,8500,‘老员工‘);
insert into test values (‘丽丽‘,‘333333333333333333‘,‘高级运维‘,‘2017/1/1‘,‘1999/12/1‘,6500,‘新员工‘);
insert into test values (‘赵六‘,‘444444444444444444‘,‘高级运维‘,‘2016/4/18‘,‘1985/5/9‘,6000,‘新员工‘);
insert into test values (‘王五‘,‘555555555555555555‘,‘中级运维‘,‘2014/9/9‘,‘1994/11/21‘,10000,‘老员工‘);
insert into test values (‘李四‘,‘666666666666666666‘,‘初级运维‘,‘2018/4/16‘,‘1996/12/7‘,7500,‘新员工‘);
insert into test values (‘王朝‘,‘777777777777777777‘,‘初级运维‘,‘2015/6/3‘,‘1991/10/2‘,8000,‘老员工‘);
select * from test;     <!--查看test表中数据-->

1、系统函数

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

系统函数示例如下：

1）数据转换，将字符串转换为整数

select convert(varchar(5),12345) as 将字符转换为整数;

2）数据转换，将日期转换为日期时间

select cast(‘2019-12-29‘as datetime) as 日期和时间;

3）显示登录数据库的用户

select current_user as 登录用户;
select system_user as 系统用户;

4）显示登录计算机的名字

select host_name() as 登录计算机名字;

5）统计输入的字符串长度

select datalength(‘缘分让我们相遇！！！‘) as 字符串长度;

6）使用用户的ID查询账户

select user_name(1) as 当前用户;

2、字符串函数

字符串函数用于控制返回给用户的字符串，这些功能仅用于字符型数据。字符串在信息处理时有特殊的地位，几乎所有信息都需要转换成字符串才能正确显示，尤其是不同数据拼接起来显示的使用更加广泛，字符串拼接很简单，两个字符串之间使用“ ”即可。

字符串函数示例如下：

1）比较一个字符串在另一个字符串中的位置

select charindex(‘alibaba‘,‘www.alibaba.com‘) as 字符串比较;

2）统计输入的字符串长度

select len (‘好好学习天天向上‘) as 字符串长度;

3）将小写字符串调整为大写

select upper (‘www.baiduyun.com‘) as 调整为大写;

4）字符串从右边第七个显示

select RIGHT (‘www.baiduyun.com‘,7);

5）替换字符串中的字符

select replace (‘ababababa‘,‘a‘,‘b‘) as 将a替换成b;

6）删除指定位置字符串插入新的字符串，删除第四个字符和第五个字符插入新内容

select stuff (‘wwwwww‘,4,5,‘桥边姑娘‘);

7）使用拼接字符显示初级运维名字和基本工资

select ‘中级运维‘ 姓名 ‘的基本工资是：‘  cast (基本工资 as varchar(20)) ‘元‘ from test where 职务=‘中级运维‘;

3、日期函数

在SQL Server中不能直接对日期运用数学函数，需要使用日期函数操作日期值。日期函数帮助提取日期值中的日、月及年，以便分别操作它们。

日期函数示例如下：

1）获取系统当前时间

select getdate() as 当前系统时间;

2）添加时间，（YY年、MM月、DD日）

select dateadd (mm,20,‘2019-5-3‘) as 添加时间;

3）判断两个时间的差

select datediff (yy,‘2019-10-1‘,‘2029-10-1‘) as 差;

4）修改日期中的特定部分字符串，显示星期

select datename (dw,‘2020-1-3‘) as 星期;

5）显示时间中的日期

select datepart (month,‘2019-1-1‘) as 日;

6）显示二十天后的日期

select dateadd (dd,20,getdate()) as 二十天后的日期;

7）计算test表周员工的年龄

select 姓名,datediff (yy,出生日期,getdate()) as 年龄 from test;

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

4、聚合函数与分组查询

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

• SUM()：SUM()函数返回表达式中所有数值的总和，它只能用于数字类型的列，不能够汇总字符、日期等其他数据类型；

• AVG()：AVG()函数返回表达式中所有数值的平均值，它们只能用于数字类型的列；

• MAX()和MIN()：MAX()函数返回表达式中的最大值，MIN()函数返回表达式中的最小值，它们都可以用于数字型、字符型及日期/时间类型的列；

• COUNT()：COUNT()函数返回表达式中非空值的技数，它可以用于数字和字符类型的列；

另外，也可以使用星号（*）作为COUNT()函数的表达式，使用星号可以不必指定特定的列而计算所有的行数；

聚合函数示例如下：

1） 计算员工信息表的总工资

select sum(基本工资) as 总工资 from test;

2）计算员工平均工资

select avg(基本工资) as 平均工资 from test;

3）计算最高和最低的工资

select max(基本工资) as 最高工资, min(基本工资) as 最低工资 from test;

4）统计表中行数据

select COUNT(*) as 总行数 from test;

分组查询示例如下：

分组查询就是将表中的数据通过GROUP BY子句分类组合，再根据需要得到统计信息。如果需要对分组结果进行筛选，只显示满足限定条件的组，需要使用HAVING子句。
不难理解，在使用GROUP BY关键字时，在SELECT列表中可以指定的项目是有限的，SELECT语句中仅允许以下几项：

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

1）分组查询表中每个职务的平均工资

select 职务,SUM(基本工资) as 职务总工资 from test group by 职务;

2）分组查询表中平均工资小于13000显示出来

select 职务,SUM(基本工资) as 职务总工资 from test group by 职务 having sum(基本工资) < 13000;

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

• WHERE子句的作用是在对分组查询结果进行分组之前，根据WHERE条件筛选数据，条件中不能包含聚合函数；

• HAVING子句的作用是在分组之后筛选满足条件的组，条件中经常包含聚合函数，也可以使用多个分组标准进行分组；

5、数学函数

数学函数用于对数值进行代数运算，由于数学函数数量众多，不可能全部列举。简单列举几个如下表：

数学函数示例如下：

1）显示整数

select ABS (-100) as 显示整数;
select ABS (20) as 显示整数;

2）取值大于，四舍五入

select CEILING (33.7) as 取值大于四舍五入;

3）取值小于，四舍

select FLOOR (22.4) as 取值小于五舍去;

4）计算平方或者次幂

select POWER (2,3) as 计算次幂;

5）四舍五入精确数

select ROUND (50,333.2) as 四舍五入精确数;

6）正数返回 1，负数返回-1，0返回0

select SIGN (-10) as 负数返负1; 
select SIGN (30) as 正数返1;
select SIGN (0) as 返回0;

7）计算数字平均值

select SQRT (40) as 计算数字平均值;

8）计算test表基本工资的平均数

select CEILING (avg (基本工资)) as 平均工资 from test;

9）统计员工姓名计算距离30岁还有多少天

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

10）拼接字符显示姓名年龄未满30岁还有多少天

select
‘员工‘ 姓名 
‘的生日是‘ CONVERT(varchar(20),出生日期,111) 
‘,现在年龄是‘ CAST(DATEDIFF(YY,出生日期,GETDATE()) AS varchar(10)) ‘岁‘ 
‘,距离30岁生日还有‘ 
CAST (DATEDIFF(DD,GETDATE(),DATEADD(YY,30,出生日期)) AS varchar(10)) ‘天‘
from test
where datediff (yy,出生日期,getdate()) <=30
order by 出生日期;

二、T-SQL语句多表查询案例

使用T-SQL实现多表查询（内联接、左外联接、右外联接、全联接/完整外联接）

1、创建A表

create table A      <!--创建A表-->
(
    姓名 nvarchar(5) not null,
    学校 nvarchar(10) not null,
);
insert into A values (‘李寒‘,‘北京大学‘);    <!--数据插入A表-->
insert into A values (‘张玉‘,‘清华大学‘);      <!--数据插入A表-->
insert into A values (‘刘敏‘,‘中国人民大学‘);    <!--数据插入A表-->
insert into A values (‘孙明明‘,‘浙江大学‘);     <!--数据插入A表-->
select * from A;     <!--查看A表数据-->

2、创建B表

create table B    <!--创建B表-->
(
    姓名 nvarchar(5) not null,
    职业 nvarchar(10) not null,
);
insert into B values (‘张玉‘,‘咨询师‘);    <!--数据插入B表-->
insert into B values (‘刘敏‘,‘作家‘);       <!--数据插入B表-->
insert into B values (‘张明翰‘,‘建筑师‘);    <!--数据插入B表-->
insert into B values (‘王博‘,‘工程师‘);          <!--数据插入B表-->
select * from B;    <!--查看B表数据-->

3、内联接示例

1）内联接实现方法一

  <!--在表A和表B中使用内联接查询学生姓名、学校和职业-->
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A,B where A.姓名=B.姓名

2）内联接实现方法二

<!--在from子句中使用inner join…on子句来实现-->
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A inner join B on A.姓名=B.姓名

4、外联接示例

1）左外联接

<!--左外联接，在表A和表B中使用左外联接查询学生姓名、
学校和职业-->
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A left join B on A.姓名=B.姓名

2）右外联接

<!--右外联接，在表A和表B中使用右外联接查询学生姓名、
学校和职业-->
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A right join B on A.姓名=B.姓名

3）完整外联接

<!--完整外联接，在表A和表B中使用完整外联接查询学生姓名、
学校和职业-->
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A full join B on A.姓名=B.姓名

三、使用函数查询统计信息案例

1、创建数据库和表插入数据

create database bdqn;    <!--创建bdqn数据库-->
use bdqn;      <!--切换到bdqn数据库-->
create table products    <!--创建products表-->
(
    编号 int identity (1,1) primary key,
    名称 nvarchar(10) not null,
    种类 nvarchar(10) not null,
    成本 money not null check (成本 >=0 and 成本 <=60),
    出厂日期 datetime not null,
);
insert into products values      <!--products表插入数据-->
(‘西瓜‘,‘水果‘,‘4.1‘,‘2017/05/06‘),
(‘芹菜‘,‘蔬菜‘,‘1.0‘,‘2017/04/01‘),
(‘番茄‘,‘蔬菜‘,‘2.9‘,‘2017/04/01‘),
(‘黄瓜‘,‘蔬菜‘,‘2.2‘,‘2017/05/09‘),
(‘香蕉‘,‘水果‘,‘6.1‘,‘2017/05/23‘),
(‘核桃‘,‘坚果‘,‘28.5‘,‘2017/06/02‘),
(‘开心果‘,‘坚果‘,‘38.11‘,‘2017/06/21‘),
(‘蓝莓‘,‘水果‘,‘50.2‘,‘2017/05/15‘);
select * from products;        <!--查看products表数据-->

2、函数操作示例

1）查询出厂日期晚于2017年4月的水果信息

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

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

select 种类,sum (成本) as 总成本 from products group by 种类;

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

select ‘查询‘ 种类 ‘的出厂日期是:‘   convert (varchar(10),出厂日期,111) from products;

4）查询所有蔬菜的平均成本

select 种类, avg(成本) as 蔬菜的平均成本 from products group by 种类 having 种类=‘蔬菜‘;

四、多表查询案例

此案例接上一个案例而做！！！

1、创建sales表插入数据

create table sales   <!--创建sales表-->
(
    名称 nvarchar(5) not null,
    销售地点 nvarchar(3) not null,
    销售价格 money not null check (销售价格 >=0 and 销售价格 <=65),
);
insert into sales values    <!--sales表中插入数据-->
(‘苹果‘,‘河北‘,‘5.0‘),
(‘香蕉‘,‘湖南‘,‘6.2‘),
(‘番茄‘,‘北京‘,‘3.15‘),
(‘黄瓜‘,‘湖北‘,‘2.45‘),
(‘芹菜‘,‘河北‘,‘1.11‘),
(‘草莓‘,‘北京‘,‘10.0‘),
(‘哈密瓜‘,‘北京‘,‘8.98‘),
(‘蓝莓‘,‘上海‘,‘59.9‘),
(‘核桃‘,‘海南‘,‘35.8‘);
select * from sales;     <!--查看sales表中数据-->

2、多表查询示例

1） 在products表和sales表中查询产品的名称、种类、成本、销售地点和销售价格

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

2） 在products表和sales表中查询销往海南的产品名称、种类、成本和销售价格

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

3）在products表和sales表中查询销往北京的蔬菜名称、种类、成本和销售价格

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

———————— 本文至此结束，感谢阅读 ————————