SQL Server T-SQL高級查詢
高級查詢在數據庫中用得是最頻繁的，也是應用最廣泛的。
? 基本常用查詢
--
select
select
*
from
student;
?
--
all
查詢所有
select
all
sex
from
student;
?
--
distinct
過濾重復
select
distinct
sex
from
student;
?
--
count
統計
select
count
(*)
from
student;
select
count
(sex)
from
student;
select
count
(
distinct
sex)
from
student;
?
--
top
取前N條記錄
select
top
3 *
from
student;
?
--
alias
column
name 列重命名
select
id
as
編號, name
'名稱'
, sex 性別
from
student;
?
--
alias
table
name 表重命名
select
id, name, s.id, s.name
from
student s;
?
--
column
列運算
select
(age + id) col
from
student;
select
s.name +
'-'
+ c.name
from
classes c, student s
where
s.cid = c.id;
?
--
where
條件
select
*
from
student
where
id = 2;
select
*
from
student
where
id > 7;
select
*
from
student
where
id < 3;
select
*
from
student
where
id <> 3;
select
*
from
student
where
id >= 3;
select
*
from
student
where
id <= 5;
select
*
from
student
where
id !> 3;
select
*
from
student
where
id !< 5;
?
--
and
并且
select
*
from
student
where
id > 2
and
sex = 1;
?
--
or
或者
select
*
from
student
where
id = 2
or
sex = 1;
?
--
between
...
and
... 相當于并且
select
*
from
student
where
id
between
2
and
5;
select
*
from
student
where
id
not
between
2
and
5;
?
--
like
模糊查詢
select
*
from
student
where
name
like
'%a%'
;
select
*
from
student
where
name
like
'%[a][o]%'
;
select
*
from
student
where
name
not
like
'%a%'
;
select
*
from
student
where
name
like
'ja%'
;
select
*
from
student
where
name
not
like
'%[j,n]%'
;
select
*
from
student
where
name
like
'%[j,n,a]%'
;
select
*
from
student
where
name
like
'%[^ja,as,on]%'
;
select
*
from
student
where
name
like
'%[ja_on]%'
;
?
--
in
子查詢
select
*
from
student
where
id
in
(1, 2);
?
--
not
in
不在其中
select
*
from
student
where
id
not
in
(1, 2);
?
--
is
null
是空
select
*
from
student
where
age
is
null
;
?
--
is
not
null
不為空
select
*
from
student
where
age
is
not
null
;
?
--
order
by
排序
select
*
from
student
order
by
name;
select
*
from
student
order
by
name
desc
;
select
*
from
student
order
by
name
asc
;
?
--
group
by
分組
按照年齡進行分組統計
select
count
(age), age
from
student
group
by
age;
按照性別進行分組統計
select
count
(*), sex
from
student
group
by
sex;
按照年齡和性別組合分組統計，并排序
select
count
(*), sex
from
student
group
by
sex, age
order
by
age;
按照性別分組，并且是id大于2的記錄最后按照性別排序
select
count
(*), sex
from
student
where
id > 2
group
by
sex
order
by
sex;
查詢id大于2的數據，并完成運算后的結果進行分組和排序
select
count
(*), (sex * id)
new
from
student
where
id > 2
group
by
sex * id
order
by
sex * id;
?
--
group
by
all
所有分組
按照年齡分組，是所有的年齡
select
count
(*), age
from
student
group
by
all
age;
?
--
having
分組過濾條件
按照年齡分組，過濾年齡為空的數據，并且統計分組的條數和現實年齡信息
select
count
(*), age
from
student
group
by
age
having
age
is
not
null
;
?
按照年齡和cid組合分組，過濾條件是cid大于1的記錄
select
count
(*), cid, sex
from
student
group
by
cid, sex
having
cid > 1;
?
按照年齡分組，過濾條件是分組后的記錄條數大于等于2
select
count
(*), age
from
student
group
by
age
having
count
(age) >= 2;
?
按照cid和性別組合分組，過濾條件是cid大于1，cid的最大值大于2
select
count
(*), cid, sex
from
student
group
by
cid, sex
having
cid > 1
and
max
(cid) > 2;
? 嵌套子查詢
??? 子查詢是一個嵌套在select、insert、update或delete語句或其他子查詢中的查詢。任何允許使用表達式的地方都可以使用子查詢。子查詢也稱為內部查詢或內部選擇，而包含子查詢的語句也成為外部查詢或外部選擇。
?
# from (select … table)示例
將一個table的查詢結果當做一個新表進行查詢
select
*
from
(
select
id, name
from
student
where
sex = 1
) t
where
t.id > 2;
上面括號中的語句，就是子查詢語句（內部查詢）。在外面的是外部查詢，其中外部查詢可以包含以下語句：
???? 1、 包含常規選擇列表組件的常規select查詢
???? 2、 包含一個或多個表或視圖名稱的常規from語句
???? 3、 可選的where子句
???? 4、 可選的group by子句
???? 5、 可選的having子句
?
# 示例
查詢班級信息，統計班級學生人生
select
*, (
select
count
(*)
from
student
where
cid = classes.id)
as
num
from
classes
order
by
num;
?
# in, not in子句查詢示例
查詢班級id大于小于的這些班級的學生信息
select
*
from
student
where
cid
in
(
select
id
from
classes
where
id > 2
and
id < 4
);
?
查詢不是班的學生信息
select
*
from
student
where
cid
not
in
(
select
id
from
classes
where
name =
'2班'
)
in、not in 后面的子句返回的結果必須是一列，這一列的結果將會作為查詢條件對應前面的條件。如cid對應子句的id；
?
# exists和not exists子句查詢示例
查詢存在班級id為的學生信息
select
*
from
student
where
exists
(
select
*
from
classes
where
id = student.cid
and
id = 3
);
?
查詢沒有分配班級的學生信息
select
*
from
student
where
not
exists
(
select
*
from
classes
where
id = student.cid
);
exists和not exists查詢需要內部查詢和外部查詢進行一個關聯的條件，如果沒有這個條件將是查詢到的所有信息。如：id等于student.id；
?
# some、any、all子句查詢示例
查詢班級的學生年齡大于班級的學生的年齡的信息
select
*
from
student
where
cid = 5
and
age >
all
(
select
age
from
student
where
cid = 3
);
?
select
*
from
student
where
cid = 5
and
age >
any
(
select
age
from
student
where
cid = 3
);
?
select
*
from
student
where
cid = 5
and
age >
some
(
select
age
from
student
where
cid = 3
);
?
? 聚合查詢
1、 distinct去掉重復數據
select
distinct
sex
from
student;
select
count
(sex),
count
(
distinct
sex)
from
student;
?
2、 compute和compute by匯總查詢
對年齡大于的進行匯總
select
age
from
student
where
age > 20
order
by
age
compute
sum
(age)
by
age;
?
對年齡大于的按照性別進行分組匯總年齡信息
select
id, sex, age
from
student
where
age > 20
order
by
sex, age
compute
sum
(age)
by
sex;
?
按照年齡分組匯總
select
age
from
student
where
age > 20
order
by
age, id
compute
sum
(age);
?
按照年齡分組，年齡匯總，id找最大值
select
id, age
from
student
where
age > 20
order
by
age
compute
sum
(age),
max
(id);
compute進行匯總前面是查詢的結果，后面一條結果集就是匯總的信息。compute子句中可以添加多個匯總表達式，可以添加的信息如下：
???? a、 可選by關鍵字。它是每一列計算指定的行聚合
???? b、 行聚合函數名稱。包括sum、avg、min、max、count等
???? c、 要對其執行聚合函數的列
???? compute by適合做先分組后匯總的業務。compute by后面的列一定要是order by中出現的列。
?
3、 cube匯總
cube匯總和compute效果類似，但語法較簡潔，而且返回的是一個結果集。
select
count
(*), sex
from
student
group
by
sex
with
cube
;
select
count
(*), age,
sum
(age)
from
student
where
age
is
not
null
group
by
age
with
cube
;
cube要結合group by語句完成分組匯總
?
? 排序函數
?? 排序在很多地方需要用到，需要對查詢結果進行排序并且給出序號。比如：
?? 1、 對某張表進行排序，序號需要遞增不重復的
?? 2、 對學生的成績進行排序，得出名次，名次可以并列，但名次的序號是連續遞增的
?? 3、 在某些排序的情況下，需要跳空序號，雖然是并列
基本語法
排序函數
over
([分組語句] 排序子句[
desc
][
asc
])
排序子句
order
by
列名, 列名
分組子句 partition
by
分組列, 分組列
?
# row_number函數
根據排序子句給出遞增連續序號
按照名稱排序的順序遞增
select
s.id, s.name, cid, c.name, row_number()
over
(
order
by
c.name)
as
number
from
student s, classes c
where
cid = c.id;
?
# rank函數函數
根據排序子句給出遞增的序號，但是存在并列并且跳空
順序遞增
select
id, name, rank()
over
(
order
by
cid)
as
rank
from
student;
?
跳過相同遞增
select
s.id, s.name, cid, c.name, rank()
over
(
order
by
c.name)
as
rank
from
student s, classes c
where
cid = c.id;
?
# dense_rank函數
根據排序子句給出遞增的序號，但是存在并列不跳空
不跳過，直接遞增
select
s.id, s.name, cid, c.name, dense_rank()
over
(
order
by
c.name)
as
dense
from
student s, classes c
where
cid = c.id;
?
# partition by分組子句
可以完成對分組的數據進行增加排序，partition by可以與以上三個函數聯合使用。
利用partition by按照班級名稱分組，學生id排序
select
s.id, s.name, cid, c.name, row_number()
over
(partition
by
c.name
order
by
s.id)
as
rank
from
student s, classes c
where
cid = c.id;
?
select
s.id, s.name, cid, c.name, rank()
over
(partition
by
c.name
order
by
s.id)
as
rank
from
student s, classes c
where
cid = c.id;
?
select
s.id, s.name, cid, c.name, dense_rank()
over
(partition
by
c.name
order
by
s.id)
as
rank
from
student s, classes c
where
cid = c.id;
?
# ntile平均排序函數
將要排序的數據進行平分，然后按照等分排序。ntile中的參數代表分成多少等分。
select
s.id, s.name, cid, c.name,
ntile(5)
over
(
order
by
c.name)
as
ntile
from
student s, classes c
where
cid = c.id;
?
? 集合運算
操作兩組查詢結果，進行交集、并集、減集運算
1、 union和union all進行并集運算
--
union
并集、不重復
select
id, name
from
student
where
name
like
'ja%'
union
select
id, name
from
student
where
id = 4;
?
--并集、重復
select
*
from
student
where
name
like
'ja%'
union
all
select
*
from
student;
?
2、 intersect進行交集運算
--交集（相同部分）
select
*
from
student
where
name
like
'ja%'
intersect
select
*
from
student;
?
3、 except進行減集運算
--減集（除相同部分）
select
*
from
student
where
name
like
'ja%'
except
select
*
from
student
where
name
like
'jas%'
;
?
? 公式表表達式
查詢表的時候，有時候中間表需要重復使用，這些子查詢被重復查詢調用，不但效率低，而且可讀性低，不利于理解。那么公式表表達式可以解決這個問題。
我們可以將公式表表達式（CET）視為臨時結果集，在select、insert、update、delete或是create view語句的執行范圍內進行定義。
--表達式
with
statNum(id, num)
as
(
select
cid,
count
(*)
from
student
where
id > 0
group
by
cid
)
select
id, num
from
statNum
order
by
id;
?
with
statNum(id, num)
as
(
select
cid,
count
(*)
from
student
where
id > 0
group
by
cid
)
select
max
(id),
avg
(num)
from
statNum;
?
? 連接查詢
1、 簡化連接查詢
--簡化聯接查詢
select
s.id, s.name, c.id, c.name
from
student s, classes c
where
s.cid = c.id;
?
2、 left join左連接
--左連接
select
s.id, s.name, c.id, c.name
from
student s
left
join
classes c
on
s.cid = c.id;
?
3、 right join右連接
--右連接
select
s.id, s.name, c.id, c.name
from
student s
right
join
classes c
on
s.cid = c.id;
?
4、 inner join內連接
--內連接
select
s.id, s.name, c.id, c.name
from
student s
inner
join
classes c
on
s.cid = c.id;
?
--inner可以省略
select
s.id, s.name, c.id, c.name
from
student s
join
classes c
on
s.cid = c.id;
?
5、 cross join交叉連接
--交叉聯接查詢，結果是一個笛卡兒乘積
select
s.id, s.name, c.id, c.name
from
student s
cross
join
classes c
--
where
s.cid = c.id;
?
6、 自連接（同一張表進行連接查詢）
--自連接
select
distinct
s.*
from
student s, student s1
where
s.id <> s1.id
and
s.sex = s1.sex;
?
? 函數
1、 聚合函數
max最大值、min最小值、count統計、avg平均值、sum求和、var求方差
select
max
(age) max_age,
min
(age) min_age,
count
(age) count_age,
avg
(age) avg_age,
sum
(age) sum_age,
var(age) var_age
from
student;
?
2、 日期時間函數
select
dateAdd(
day
, 3, getDate());--加天
select
dateAdd(
year
, 3, getDate());--加年
select
dateAdd(
hour
, 3, getDate());--加小時
--返回跨兩個指定日期的日期邊界數和時間邊界數
select
dateDiff(
day
,
'2011-06-20'
, getDate());
--相差秒數
select
dateDiff(
second
,
'2011-06-22 11:00:00'
, getDate());
--相差小時數
select
dateDiff(
hour
,
'2011-06-22 10:00:00'
, getDate());
select
dateName(
month
, getDate());--當前月份
select
dateName(
minute
, getDate());--當前分鐘
select
dateName(weekday, getDate());--當前星期
select
datePart(
month
, getDate());--當前月份
select
datePart(weekday, getDate());--當前星期
select
datePart(
second
, getDate());--當前秒數
select
day
(getDate());--返回當前日期天數
select
day
(
'2011-06-30'
);--返回當前日期天數
select
month
(getDate());--返回當前日期月份
select
month
(
'2011-11-10'
);
select
year
(getDate());--返回當前日期年份
select
year
(
'2010-11-10'
);
select
getDate();--當前系統日期
select
getUTCDate();--utc日期
?
3、 數學函數
select
pi();--PI函數
select
rand(100), rand(50), rand(), rand();--隨機數
select
round(rand(), 3), round(rand(100), 5);--精確小數位
--精確位數，負數表示小數點前
select
round(123.456, 2), round(254.124, -2);
select
round(123.4567, 1, 2);
?
4、 元數據
select
col_name(object_id(
'student'
), 1);--返回列名
select
col_name(object_id(
'student'
), 2);
--該列數據類型長度
select
col_length(
'student'
, col_name(object_id(
'student'
), 2));
--該列數據類型長度
select
col_length(
'student'
, col_name(object_id(
'student'
), 1));
--返回類型名稱、類型id
select
type_name(type_id(
'varchar'
)), type_id(
'varchar'
);
--返回列類型長度
select
columnProperty(object_id(
'student'
),
'name'
,
'PRECISION'
);
--返回列所在索引位置
select
columnProperty(object_id(
'student'
),
'sex'
,
'ColumnId'
);
?
5、 字符串函數
select
ascii(
'a'
);--字符轉換ascii值
select
ascii(
'A'
);
select
char
(97);--ascii值轉換字符
select
char
(65);
select
nchar
(65);
select
nchar
(45231);
select
nchar
(32993);--unicode轉換字符
select
unicode(
'A'
), unicode(
'中'
);--返回unicode編碼值
select
soundex(
'hello'
), soundex(
'world'
), soundex(
'word'
);
select
patindex(
'%a'
,
'ta'
), patindex(
'%ac%'
,
'jack'
), patindex(
'dex%'
,
'dexjack'
);--匹配字符索引
select
'a'
+
space
(2) +
'b'
,
'c'
+
space
(5) +
'd'
;--輸出空格
select
charIndex(
'o'
,
'hello world'
);--查找索引
select
charIndex(
'o'
,
'hello world'
, 6);--查找索引
select
quoteName(
'abc[]def'
), quoteName(
'123]45'
);
--精確數字
select
str(123.456, 2), str(123.456, 3), str(123.456, 4);
select
str(123.456, 9, 2), str(123.456, 9, 3), str(123.456, 6, 1), str(123.456, 9, 6);
select
difference(
'hello'
,
'helloWorld'
);--比較字符串相同
select
difference(
'hello'
,
'world'
);
select
difference(
'hello'
,
'llo'
);
select
difference(
'hello'
,
'hel'
);
select
difference(
'hello'
,
'hello'
);
select
replace(
'abcedef'
,
'e'
,
'E'
);--替換字符串
select
stuff(
'hello world'
, 3, 4,
'ABC'
);--指定位置替換字符串
select
replicate(
'abc#'
, 3);--重復字符串
select
subString
(
'abc'
, 1, 1),
subString
(
'abc'
, 1, 2),
subString
(
'hello Wrold'
, 7, 5);--截取字符串
select
len(
'abc'
);--返回長度
select
reverse(
'sqlServer'
);--反轉字符串
?
select
left
(
'leftString'
, 4);--取左邊字符串
select
left
(
'leftString'
, 7);
select
right
(
'leftString'
, 6);--取右邊字符串
select
right
(
'leftString'
, 3);
select
lower
(
'aBc'
),
lower
(
'ABC'
);--小寫
select
upper
(
'aBc'
),
upper
(
'abc'
);--大寫
--去掉左邊空格
select
ltrim(
' abc'
), ltrim(
'# abc#'
), ltrim(
' abc'
);
--去掉右邊空格
select
rtrim(
' abc '
), rtrim(
'# abc# '
), rtrim(
'abc'
);
?
6、 安全函數
select
current_user
;
select
user
;
select
user_id(), user_id(
'dbo'
), user_id(
'public'
), user_id(
'guest'
);
select
user_name(), user_name(1), user_name(0), user_name(2);
select
session_user
;
select
suser_id(
'sa'
);
select
suser_sid(), suser_sid(
'sa'
), suser_sid(
'sysadmin'
), suser_sid(
'serveradmin'
);
select
is_member(
'dbo'
), is_member(
'public'
);
select
suser_name(), suser_name(1), suser_name(2), suser_name(3);
select
suser_sname(), suser_sname(0x01), suser_sname(0x02), suser_sname(0x03);
select
is_srvRoleMember(
'sysadmin'
), is_srvRoleMember(
'serveradmin'
);
select
permissions(object_id(
'student'
));
select
system_user
;
select
schema_id(), schema_id(
'dbo'
), schema_id(
'guest'
);
select
schema_name(), schema_name(1), schema_name(2), schema_name(3);
?
7、 系統函數
select
app_name();--當前會話的應用程序名稱
select
cast
(2011
as
datetime),
cast
(
'10'
as
money),
cast
(
'0'
as
varbinary);--類型轉換
select
convert
(datetime,
'2011'
);--類型轉換
select
coalesce
(
null
,
'a'
),
coalesce
(
'123'
,
'a'
);--返回其參數中第一個非空表達式
select
collationProperty(
'Traditional_Spanish_CS_AS_KS_WS'
,
'CodePage'
);
select
current_timestamp
;--當前時間戳
select
current_user
;
select
isDate(getDate()), isDate(
'abc'
), isNumeric(1), isNumeric(
'a'
);
select
dataLength(
'abc'
);
select
host_id();
select
host_name();
select
db_name();
select
ident_current(
'student'
), ident_current(
'classes'
);--返回主鍵id的最大值
select
ident_incr(
'student'
), ident_incr(
'classes'
);--id的增量值
select
ident_seed(
'student'
), ident_seed(
'classes'
);
select
@@
identity
;--最后一次自增的值
select
identity
(
int
, 1, 1)
as
id
into
tab
from
student;--將studeng表的烈屬，以/1自增形式創建一個tab
select
*
from
tab;
select
@@
rowcount
;--影響行數
select
@@cursor_rows;--返回連接上打開的游標的當前限定行的數目
select
@@error;--T-SQL的錯誤號
select
@@procid;
?
8、 配置函數
set
datefirst 7;--設置每周的第一天，表示周日
select
@@datefirst
as
'星期的第一天'
, datepart(dw, getDate())
AS
'今天是星期'
;
select
@@dbts;--返回當前數據庫唯一時間戳
set
language
'Italian'
;
select
@@langId
as
'Language ID'
;--返回語言id
select
@@language
as
'Language Name'
;--返回當前語言名稱
select
@@lock_timeout;--返回當前會話的當前鎖定超時設置（毫秒）
select
@@max_connections;--返回SQL Server 實例允許同時進行的最大用戶連接數
select
@@MAX_PRECISION
AS
'Max Precision'
;--返回decimal 和numeric 數據類型所用的精度級別
select
@@SERVERNAME;--
SQL
Server 的本地服務器的名稱
select
@@SERVICENAME;--服務名
select
@@SPID;--當前會話進程id
select
@@
textSize
;
select
@@version;--當前數據庫版本信息
?
9、 系統統計函數
select
@@CONNECTIONS;--連接數
select
@@PACK_RECEIVED;
select
@@CPU_BUSY;
select
@@PACK_SENT;
select
@@TIMETICKS;
select
@@IDLE;
select
@@TOTAL_ERRORS;
select
@@IO_BUSY;
select
@@TOTAL_READ;--讀取磁盤次數
select
@@PACKET_ERRORS;--發生的網絡數據包錯誤數
select
@@TOTAL_WRITE;--sqlserver執行的磁盤寫入次數
select
patIndex(
'%soft%'
,
'microsoft SqlServer'
);
select
patIndex(
'soft%'
,
'software SqlServer'
);
select
patIndex(
'%soft'
,
'SqlServer microsoft'
);
select
patIndex(
'%so_gr%'
,
'Jsonisprogram'
);
?
10、 用戶自定義函數
# 查看當前數據庫所有函數
--查詢所有已創建函數
select
definition,*
from
sys.sql_modules m
join
sys.objects o
on
m.object_id = o.object_id
and
type
in
(
'fn'
,
'if'
,
'tf'
);
?
# 創建函數
if
(object_id(
'fun_add'
,
'fn'
)
is
not
null
)
drop
function
fun_add
go
create
function
fun_add(@num1
int
, @num2
int
)
returns
int
with
execute
as
caller
as
begin
declare
@
result
int
;
if
(@num1
is
null
)
set
@num1 = 0;
if
(@num2
is
null
)
set
@num2 = 0;
set
@
result
= @num1 + @num2;
return
@
result
;
end
go
調用函數
select
dbo.fun_add(id, age)
from
student;
?
--自定義函數，字符串連接
if
(object_id(
'fun_append'
,
'fn'
)
is
not
null
)
drop
function
fun_append
go
create
function
fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
returns
nvarchar(2048)
as
begin
return
@args + @args2;
end
go
?
select
dbo.fun_append(name,
'abc'
)
from
student;
?
# 修改函數
alter
function
fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
returns
nvarchar(1024)
as
begin
declare
@
result
varchar
(1024);
--coalesce返回第一個不為null的值
set
@args =
coalesce
(@args,
''
);
set
@args2 =
coalesce
(@args2,
''
);;
set
@
result
= @args + @args2;
return
@
result
;
end
go
?
select
dbo.fun_append(name,
'#abc'
)
from
student;
?
# 返回table類型函數
--返回table對象函數
select
name, object_id, type
from
sys.objects
where
type
in
(
'fn'
,
'if'
,
'tf'
)
or
type
like
'%f%'
;
?
if
(
exists
(
select
*
from
sys.objects
where
type
in
(
'fn'
,
'if'
,
'tf'
)
and
name =
'fun_find_stuRecord'
))
drop
function
fun_find_stuRecord
go
create
function
fun_find_stuRecord(@id
int
)
returns
table
as
return
(
select
*
from
student
where
id = @id);
go
?
select
*
from
dbo.fun_find_stuRecord(2);
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
