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

———————— 本文至此結束，感謝閱讀 ————————