SQL Server數(shù)據(jù)庫(kù)的T-SQL高級(jí)查詢

一、查詢使用的函數(shù)

請(qǐng)自行創(chuàng)建數(shù)據(jù)庫(kù)和表，表中插入數(shù)據(jù)，我的樣例如下：

create database accp;    <!--創(chuàng)建accp數(shù)據(jù)庫(kù)-->
use accp;     <!--切換到accp數(shù)據(jù)庫(kù)-->
create table test (            <!--創(chuàng)建test表-->
    編號(hào)  int     identity(1,1) not null,
    姓名  nvarchar(4) not null,
    證件號(hào)碼 varchar(18) primary key,
    職務(wù)  nvarchar(6) null,
    入職時(shí)間 datetime   null,
    出生日期 datetime null,
    基本工資 money null check (基本工資 >=0 and 基本工資 <= 10000),
);
select * from test;      <!--查看test表結(jié)構(gòu)-->
alter   table test add 備注 nvarchar(50);    <!--添加一列備注列-->
     <!--表中插入數(shù)據(jù) -->
insert into test values ('王三','111111111111111111','初級(jí)運(yùn)維','2011/3/8','1980/12/3',7500,'老員工');
insert into test values ('杜五','222222222222222222','中級(jí)運(yùn)維','2007/7/24','1999/9/7',8500,'老員工');
insert into test values ('麗麗','333333333333333333','高級(jí)運(yùn)維','2017/1/1','1999/12/1',6500,'新員工');
insert into test values ('趙六','444444444444444444','高級(jí)運(yùn)維','2016/4/18','1985/5/9',6000,'新員工');
insert into test values ('王五','555555555555555555','中級(jí)運(yùn)維','2014/9/9','1994/11/21',10000,'老員工');
insert into test values ('李四','666666666666666666','初級(jí)運(yùn)維','2018/4/16','1996/12/7',7500,'新員工');
insert into test values ('王朝','777777777777777777','初級(jí)運(yùn)維','2015/6/3','1991/10/2',8000,'老員工');
select * from test;     <!--查看test表中數(shù)據(jù)-->

1、系統(tǒng)函數(shù)

CONVERT和CAST的類型轉(zhuǎn)換使用頻繁，在很多場(chǎng)合均可使用。尤其是在網(wǎng)站應(yīng)用中，常常需要將各種類型的數(shù)據(jù)轉(zhuǎn)換成指定格式的字符串，然后拼接成顯示內(nèi)容。

系統(tǒng)函數(shù)示例如下：

1）數(shù)據(jù)轉(zhuǎn)換，將字符串轉(zhuǎn)換為整數(shù)

select convert(varchar(5),12345) as 將字符轉(zhuǎn)換為整數(shù);

2）數(shù)據(jù)轉(zhuǎn)換，將日期轉(zhuǎn)換為日期時(shí)間

select cast('2019-12-29'as datetime) as 日期和時(shí)間;

3）顯示登錄數(shù)據(jù)庫(kù)的用戶

select current_user as 登錄用戶;
select system_user as 系統(tǒng)用戶;

4）顯示登錄計(jì)算機(jī)的名字

select host_name() as 登錄計(jì)算機(jī)名字;

5）統(tǒng)計(jì)輸入的字符串長(zhǎng)度

select datalength('緣分讓我們相遇?。?！') as 字符串長(zhǎng)度;

6）使用用戶的ID查詢賬戶

select user_name(1) as 當(dāng)前用戶;

2、字符串函數(shù)

字符串函數(shù)用于控制返回給用戶的字符串，這些功能僅用于字符型數(shù)據(jù)。字符串在信息處理時(shí)有特殊的地位，幾乎所有信息都需要轉(zhuǎn)換成字符串才能正確顯示，尤其是不同數(shù)據(jù)拼接起來(lái)顯示的使用更加廣泛，字符串拼接很簡(jiǎn)單，兩個(gè)字符串之間使用“+”即可。

字符串函數(shù)示例如下：

1）比較一個(gè)字符串在另一個(gè)字符串中的位置

select charindex('alibaba','www.alibaba.com') as 字符串比較;

2）統(tǒng)計(jì)輸入的字符串長(zhǎng)度

select len ('好好學(xué)習(xí)天天向上') as 字符串長(zhǎng)度;

3）將小寫字符串調(diào)整為大寫

select upper ('www.baiduyun.com') as 調(diào)整為大寫;

4）字符串從右邊第七個(gè)顯示

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

5）替換字符串中的字符

select replace ('ababababa','a','b') as 將a替換成b;

6）刪除指定位置字符串插入新的字符串，刪除第四個(gè)字符和第五個(gè)字符插入新內(nèi)容

select stuff ('wwwwww',4,5,'橋邊姑娘');

7）使用拼接字符顯示初級(jí)運(yùn)維名字和基本工資

select '中級(jí)運(yùn)維'+姓名+'的基本工資是：'+ cast (基本工資 as varchar(20))+'元' from test where 職務(wù)='中級(jí)運(yùn)維';

3、日期函數(shù)

在SQL Server中不能直接對(duì)日期運(yùn)用數(shù)學(xué)函數(shù)，需要使用日期函數(shù)操作日期值。日期函數(shù)幫助提取日期值中的日、月及年，以便分別操作它們。

日期函數(shù)示例如下：

1）獲取系統(tǒng)當(dāng)前時(shí)間

select getdate() as 當(dāng)前系統(tǒng)時(shí)間;

2）添加時(shí)間，（YY年、MM月、DD日）

select dateadd (mm,20,'2019-5-3') as 添加時(shí)間;

3）判斷兩個(gè)時(shí)間的差

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

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

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

5）顯示時(shí)間中的日期

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

6）顯示二十天后的日期

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

7）計(jì)算test表周員工的年齡

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

在輸入代碼的時(shí)候，一定要注意輸入法的問(wèn)題，一定要保證是英文輸入法狀態(tài)，否則會(huì)報(bào)錯(cuò)。

4、聚合函數(shù)與分組查詢

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

• SUM()：SUM()函數(shù)返回表達(dá)式中所有數(shù)值的總和，它只能用于數(shù)字類型的列，不能夠匯總字符、日期等其他數(shù)據(jù)類型；

• AVG()：AVG()函數(shù)返回表達(dá)式中所有數(shù)值的平均值，它們只能用于數(shù)字類型的列；

• MAX()和MIN()：MAX()函數(shù)返回表達(dá)式中的最大值，MIN()函數(shù)返回表達(dá)式中的最小值，它們都可以用于數(shù)字型、字符型及日期/時(shí)間類型的列；

• COUNT()：COUNT()函數(shù)返回表達(dá)式中非空值的技數(shù)，它可以用于數(shù)字和字符類型的列；

另外，也可以使用星號(hào)（*）作為COUNT()函數(shù)的表達(dá)式，使用星號(hào)可以不必指定特定的列而計(jì)算所有的行數(shù)；

聚合函數(shù)示例如下：

1） 計(jì)算員工信息表的總工資

select sum(基本工資) as 總工資 from test;

2）計(jì)算員工平均工資

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

3）計(jì)算最高和最低的工資

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

4）統(tǒng)計(jì)表中行數(shù)據(jù)

select COUNT(*) as 總行數(shù) from test;

分組查詢示例如下：

分組查詢就是將表中的數(shù)據(jù)通過(guò)GROUP BY子句分類組合，再根據(jù)需要得到統(tǒng)計(jì)信息。如果需要對(duì)分組結(jié)果進(jìn)行篩選，只顯示滿足限定條件的組，需要使用HAVING子句。
不難理解，在使用GROUP BY關(guān)鍵字時(shí)，在SELECT列表中可以指定的項(xiàng)目是有限的，SELECT語(yǔ)句中僅允許以下幾項(xiàng)：

• 被分組的列；
• 為每個(gè)分組返回一個(gè)值的表達(dá)式，如用一個(gè)列名作為參數(shù)的聚合函數(shù)；

1）分組查詢表中每個(gè)職務(wù)的平均工資

select 職務(wù),SUM(基本工資) as 職務(wù)總工資 from test group by 職務(wù);

2）分組查詢表中平均工資小于13000顯示出來(lái)

select 職務(wù),SUM(基本工資) as 職務(wù)總工資 from test group by 職務(wù) having sum(基本工資) < 13000;

當(dāng)GROUP BY子句中使用HAVING子句時(shí)，查詢結(jié)果只返回滿足HAVING條件的組。在一個(gè)T-SQL語(yǔ)句中可以有WHERE子句和HAVING子句，HAVING子句與WHERE子句類似，均用于設(shè)置限定條件。但HAVING子句和WHERE子句的區(qū)別如下：

• WHERE子句的作用是在對(duì)分組查詢結(jié)果進(jìn)行分組之前，根據(jù)WHERE條件篩選數(shù)據(jù)，條件中不能包含聚合函數(shù)；

• HAVING子句的作用是在分組之后篩選滿足條件的組，條件中經(jīng)常包含聚合函數(shù)，也可以使用多個(gè)分組標(biāo)準(zhǔn)進(jìn)行分組；

5、數(shù)學(xué)函數(shù)

數(shù)學(xué)函數(shù)用于對(duì)數(shù)值進(jìn)行代數(shù)運(yùn)算，由于數(shù)學(xué)函數(shù)數(shù)量眾多，不可能全部列舉。簡(jiǎn)單列舉幾個(gè)如下表：

數(shù)學(xué)函數(shù)示例如下：

1）顯示整數(shù)

select ABS (-100) as 顯示整數(shù);
select ABS (20) as 顯示整數(shù);

2）取值大于，四舍五入

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

3）取值小于，四舍

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

4）計(jì)算平方或者次冪

select POWER (2,3) as 計(jì)算次冪;

5）四舍五入精確數(shù)

select ROUND (50,333.2) as 四舍五入精確數(shù);

6）正數(shù)返回+1，負(fù)數(shù)返回-1，0返回0

select SIGN (-10) as 負(fù)數(shù)返負(fù)1; 
select SIGN (30) as 正數(shù)返1;
select SIGN (0) as 返回0;

7）計(jì)算數(shù)字平均值

select SQRT (40) as 計(jì)算數(shù)字平均值;

8）計(jì)算test表基本工資的平均數(shù)

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

9）統(tǒng)計(jì)員工姓名計(jì)算距離30歲還有多少天

select 姓名,出生日期,DATEDIFF(YY,出生日期,GETDATE())
AS 年齡,DATEDIFF(DD,GETDATE(),DATEADD(yy,30,出生日期))
AS 距離30歲天數(shù) from test
where DATEDIFF(YY,出生日期,GETDATE()) <=30 order by 出生日期;

10）拼接字符顯示姓名年齡未滿30歲還有多少天

select
'員工'+姓名+
'的生日是'+CONVERT(varchar(20),出生日期,111)+
',現(xiàn)在年齡是'+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語(yǔ)句多表查詢案例

使用T-SQL實(shí)現(xiàn)多表查詢（內(nèi)聯(lián)接、左外聯(lián)接、右外聯(lián)接、全聯(lián)接/完整外聯(lián)接）

1、創(chuàng)建A表

create table A      <!--創(chuàng)建A表-->
(
    姓名 nvarchar(5) not null,
    學(xué)校 nvarchar(10) not null,
);
insert into A values ('李寒','北京大學(xué)');    <!--數(shù)據(jù)插入A表-->
insert into A values ('張玉','清華大學(xué)');      <!--數(shù)據(jù)插入A表-->
insert into A values ('劉敏','中國(guó)人民大學(xué)');    <!--數(shù)據(jù)插入A表-->
insert into A values ('孫明明','浙江大學(xué)');     <!--數(shù)據(jù)插入A表-->
select * from A;     <!--查看A表數(shù)據(jù)-->

2、創(chuàng)建B表

create table B    <!--創(chuàng)建B表-->
(
    姓名 nvarchar(5) not null,
    職業(yè) nvarchar(10) not null,
);
insert into B values ('張玉','咨詢師');    <!--數(shù)據(jù)插入B表-->
insert into B values ('劉敏','作家');       <!--數(shù)據(jù)插入B表-->
insert into B values ('張明翰','建筑師');    <!--數(shù)據(jù)插入B表-->
insert into B values ('王博','工程師');          <!--數(shù)據(jù)插入B表-->
select * from B;    <!--查看B表數(shù)據(jù)-->

3、內(nèi)聯(lián)接示例

1）內(nèi)聯(lián)接實(shí)現(xiàn)方法一

  <!--在表A和表B中使用內(nèi)聯(lián)接查詢學(xué)生姓名、學(xué)校和職業(yè)-->
select A.姓名 姓名A,A.學(xué)校 學(xué)校A,B.姓名 姓名B,B.職業(yè) 職業(yè)B
from A,B where A.姓名=B.姓名

2）內(nèi)聯(lián)接實(shí)現(xiàn)方法二

<!--在from子句中使用inner join…on子句來(lái)實(shí)現(xiàn)-->
select A.姓名 姓名A,A.學(xué)校 學(xué)校A,B.姓名 姓名B,B.職業(yè) 職業(yè)B
from A inner join B on A.姓名=B.姓名

4、外聯(lián)接示例

1）左外聯(lián)接

<!--左外聯(lián)接，在表A和表B中使用左外聯(lián)接查詢學(xué)生姓名、
學(xué)校和職業(yè)-->
select A.姓名 姓名A,A.學(xué)校 學(xué)校A,B.姓名 姓名B,B.職業(yè) 職業(yè)B
from A left join B on A.姓名=B.姓名

2）右外聯(lián)接

<!--右外聯(lián)接，在表A和表B中使用右外聯(lián)接查詢學(xué)生姓名、
學(xué)校和職業(yè)-->
select A.姓名 姓名A,A.學(xué)校 學(xué)校A,B.姓名 姓名B,B.職業(yè) 職業(yè)B
from A right join B on A.姓名=B.姓名

3）完整外聯(lián)接

<!--完整外聯(lián)接，在表A和表B中使用完整外聯(lián)接查詢學(xué)生姓名、
學(xué)校和職業(yè)-->
select A.姓名 姓名A,A.學(xué)校 學(xué)校A,B.姓名 姓名B,B.職業(yè) 職業(yè)B
from A full join B on A.姓名=B.姓名

三、使用函數(shù)查詢統(tǒng)計(jì)信息案例

1、創(chuàng)建數(shù)據(jù)庫(kù)和表插入數(shù)據(jù)

create database bdqn;    <!--創(chuàng)建bdqn數(shù)據(jù)庫(kù)-->
use bdqn;      <!--切換到bdqn數(shù)據(jù)庫(kù)-->
create table products    <!--創(chuàng)建products表-->
(
    編號(hào) 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表插入數(shù)據(jù)-->
('西瓜','水果','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'),
('核桃','堅(jiān)果','28.5','2017/06/02'),
('開(kāi)心果','堅(jiān)果','38.11','2017/06/21'),
('藍(lán)莓','水果','50.2','2017/05/15');
select * from products;        <!--查看products表數(shù)據(jù)-->

2、函數(shù)操作示例

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

select * from products where 種類='水果' and 出廠日期>'2011-04-01';

2）分組查詢所有水果、蔬菜、堅(jiān)果的總成本

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 種類='蔬菜';

四、多表查詢案例

此案例接上一個(gè)案例而做?。?！

1、創(chuàng)建sales表插入數(shù)據(jù)

create table sales   <!--創(chuàng)建sales表-->
(
    名稱 nvarchar(5) not null,
    銷售地點(diǎn) nvarchar(3) not null,
    銷售價(jià)格 money not null check (銷售價(jià)格 >=0 and 銷售價(jià)格 <=65),
);
insert into sales values    <!--sales表中插入數(shù)據(jù)-->
('蘋果','河北','5.0'),
('香蕉','湖南','6.2'),
('番茄','北京','3.15'),
('黃瓜','湖北','2.45'),
('芹菜','河北','1.11'),
('草莓','北京','10.0'),
('哈密瓜','北京','8.98'),
('藍(lán)莓','上海','59.9'),
('核桃','海南','35.8');
select * from sales;     <!--查看sales表中數(shù)據(jù)-->

2、多表查詢示例

1） 在products表和sales表中查詢產(chǎn)品的名稱、種類、成本、銷售地點(diǎn)和銷售價(jià)格

select products.名稱,products.種類,
products.成本,sales.銷售地點(diǎn),sales.銷售價(jià)格
from products inner join sales
on products.名稱=sales.名稱;

2） 在products表和sales表中查詢銷往海南的產(chǎn)品名稱、種類、成本和銷售價(jià)格

select products.名稱,products.種類,
products.成本,sales.銷售價(jià)格
from products inner join sales 
on products.名稱=sales.名稱
and sales.銷售地點(diǎn)='海南';

3）在products表和sales表中查詢銷往北京的蔬菜名稱、種類、成本和銷售價(jià)格

select products.名稱,products.種類,products.成本,sales.銷售價(jià)格
from products inner join sales on products.名稱=sales.名稱 and sales.銷售地點(diǎn)='北京' where 種類='蔬菜';

———————— 本文至此結(jié)束，感謝閱讀 ————————