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

时间:2021-12-17 04:37:40

一、查询使用的函数

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

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表中数据-->

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

1、系统函数

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

系统函数示例如下:

1)数据转换,将字符串转换为整数

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

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

2)数据转换,将日期转换为日期时间

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

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

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

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

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

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

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

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

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

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

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

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

select user_name(1) as 当前用户;

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

2、字符串函数

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

字符串函数示例如下:

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

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

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

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

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

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

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

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

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

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

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

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

5)替换字符串中的字符

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

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

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

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

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

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

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

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

3、日期函数

在SQL Server中不能直接对日期运用数学函数,需要使用日期函数操作日期值。日期函数帮助提取日期值中的日、月及年,以便分别操作它们。
SQL Server数据库的T-SQL高级查询

日期函数示例如下:

1)获取系统当前时间

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

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

2)添加时间,(YY年、MM月、DD日)

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

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

3)判断两个时间的差

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

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

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

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

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

5)显示时间中的日期

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

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

6)显示二十天后的日期

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

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

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

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

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

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

4、聚合函数与分组查询

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

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

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

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

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

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

聚合函数示例如下:

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

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

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

2)计算员工平均工资

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

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

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

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

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

4)统计表中行数据

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

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

分组查询示例如下:

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

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

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

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

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

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

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

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

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

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

5、数学函数

数学函数用于对数值进行代数运算,由于数学函数数量众多,不可能全部列举。简单列举几个如下表:
SQL Server数据库的T-SQL高级查询

数学函数示例如下:

1)显示整数

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

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

2)取值大于,四舍五入

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

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

3)取值小于,四舍

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

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

4)计算平方或者次幂

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

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

5)四舍五入精确数

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

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

6)正数返回 1,负数返回-1,0返回0

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

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

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

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

7)计算数字平均值

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

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

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

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

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

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

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

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

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 出生日期;

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

二、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表数据-->

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

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表数据-->

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

3、内联接示例

1)内联接实现方法一

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

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

2)内联接实现方法二

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

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

4、外联接示例

1)左外联接

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

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

2)右外联接

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

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

3)完整外联接

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

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

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

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表数据-->

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

2、函数操作示例

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

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

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

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

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

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

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

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

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

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

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

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

四、多表查询案例

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

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表中数据-->

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

2、多表查询示例

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

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

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

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

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

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

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

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

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

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