SQLServer数据库的T-SQL高级查询
一、查询使用的函数
请自行创建数据库和表，表中插入数据，我的样例如下：
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：空间域名、网络空间、营销软件、网站建设、嘉峪关网站维护、网站推广。
create database accp;
use accp;
create table 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;
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; 
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;.jpg)
6）删除指定位置字符串插入新的字符串，删除第四个字符和第五个字符插入新内容
select stuff ('wwwwww',4,5,'桥边姑娘');
7）使用拼接字符显示初级运维名字和基本工资
select '中级运维'+姓名+'的基本工资是：'+ cast (基本工资 as varchar(20))+'元' from test where 职务='中级运维';.jpg)
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
(
姓名 nvarchar(5) not null,
学校 nvarchar(10) not null,
);
insert into A values ('李寒','北京大学');
insert into A values ('张玉','清华大学');
insert into A values ('刘敏','中国人民大学');
insert into A values ('孙明明','浙江大学');
select * from A; 
2、创建B表
create table B
(
姓名 nvarchar(5) not null,
职业 nvarchar(10) not null,
);
insert into B values ('张玉','咨询师');
insert into B values ('刘敏','作家');
insert into B values ('张明翰','建筑师');
insert into B values ('王博','工程师');
select * from B; 
3、内联接示例
1）内联接实现方法一
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A,B where A.姓名=B.姓名
2）内联接实现方法二
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A inner join B on A.姓名=B.姓名
4、外联接示例
1）左外联接
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A left join B on A.姓名=B.姓名
2）右外联接
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A right join B on A.姓名=B.姓名
3）完整外联接
select A.姓名 姓名A,A.学校 学校A,B.姓名 姓名B,B.职业 职业B
from A full join B on A.姓名=B.姓名
三、使用函数查询统计信息案例
1、创建数据库和表插入数据
create database bdqn;
use bdqn;
create table 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
('西瓜','水果','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; 
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
(
名称 nvarchar(5) not null,
销售地点 nvarchar(3) not null,
销售价格 money not null check (销售价格 >=0 and 销售价格 <=65),
);
insert into sales values
('苹果','河北','5.0'),
('香蕉','湖南','6.2'),
('番茄','北京','3.15'),
('黄瓜','湖北','2.45'),
('芹菜','河北','1.11'),
('草莓','北京','10.0'),
('哈密瓜','北京','8.98'),
('蓝莓','上海','59.9'),
('核桃','海南','35.8');
select * from 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 种类='蔬菜';
———————— 本文至此结束，感谢阅读 ————————
名称栏目：SQLServer数据库的T-SQL高级查询
标题路径：http://shouzuofang.com/article/jhoojs.html