SQLServer學習筆記<>.基礎知識,一些基本命令,單表查詢(null top用法,with ties附加屬性,over開窗函數),次序函數

標籤：

Sqlserver基礎知識

（1）建立資料庫

建立資料庫有兩種方式，手動建立和編寫sql指令碼建立，在這裡我採用指令碼的方式建立一個名稱為TSQLFundamentals2008的資料庫。指令碼如下：

 

 View Code

 

同時往資料庫表插入一些資料，使用者後續對資料庫的sql的練習。在這裡有需要的可以下載相應的指令碼進行資料庫的初始化。我放到百度雲上面，請戳

我：http://yun.baidu.com/share/link?shareid=3635107613&uk=2971209779，提供了《Sqlserver2008技術內幕》這本書的電子版和指令碼。

（2）在這裡對TSQLFundamentals2008資料各個表進行表說明一下：

資料庫表介面如下：

HR.Employees	僱員表，存放員工的一些基本資料。
Production.Products	產品資訊表
Production.Suppliers	供應商表
Production.Customers	顧客資訊表
Production.Categories	產品類別表
Sales.OrderDetails	訂單詳情表
Sales.Orders	訂單表
Sales.Shippers	貨運公司表

 

 

 

 

 

 

 

 

 

 

 

 

 

Sqlserver一些基本命令：

查詢資料庫是否存在：

if DB_ID（"testDB"）is not null;

檢查表是否存在：

if OBJECT_ID（“textDB”，“U”） is not null ;其中U代表使用者表

建立資料庫：

create database+資料名

刪除資料庫：

drop database 資料庫名 --刪除資料庫的

drop table 表名--刪除表的

delete from 表名 where 條件 --刪除資料的

查詢語句：

use  資料庫名稱 --修改的資料庫

select*from +表名稱 --要查詢的表

select某某，某某，某某 from 表名稱 where 條件 --帶條件查詢的資料

插入資料：

insert into 表名稱  （條件）values （相對應的值）

單表查詢

（1）分組--對於分組查詢，select字句會有限制，需要查詢欄位要出現在group by 子句中，同時分組以後，可以對分組情況進行統計。

查詢僱員表，根據僱員所在國家分組，統計每組的人數情況：

1 select country,count(*) as N‘人數‘2 from hr.Employees3 group by country

 

當要查詢的欄位不包含在group by子句中，則會報相應的錯誤，所以此時要注意出現在select 後面的查詢欄位進行分組後，也同時需要出現在group by後面。

 

（2）在這裡提示一下：查詢條件不要使用計算資料行，下面談談具體原因：

例如：查詢僱員表裡面僱員出生為1973年的所有僱員資訊，可以這樣編寫sql語句:

1 select YEAR(birthdate),firstname,lastname from HR.Employees2 where YEAR(birthdate)=‘1973‘

可以看到查詢結果將1973年的僱員資訊查出來了，但是大家可以思考一下，上面的sql語句在查詢的時候，首先是要講birthdate進行取出年度的計算，

Year（birthdate），其中Year為sql的內建函數，可以用於對字串日期進行取出年份的計算。同時我們還可以採用下面的sql語句進行查詢：

 

通過sql執行計畫可以看出來，查詢條件帶計算資料行走的是索引掃描，而where子句後面採用尋找範圍限制，則走的是索尋找。對比兩個查詢顯然絕大部分情況下

走索引尋找的查詢效能要高於走索引掃描，特別是查詢的資料庫不是非常大的情況下，索引尋找的消耗時間要遠遠少於索引掃描的時間。所以在查詢條件中盡

量避免計算條件。

（3）說說sqlserver中的null，null在資料庫中表示不存在，與C#中的null不同，不表示Null 參考，沒有對象，NULL的運算規則：有null的任何運算都是null。

is [not] null： 只能用做條件判斷運算式，是否是null？是 條件為true，不是 條件為false。

isnull()：函數，如果第一個參數是null，則用第二個參數的值替換第一個參數的值作為函數的傳回值。記住：第二個參數的類型必須和第一個相容。

nullif()：函數，如果兩個參數值相等、有一個參數是null、或兩個參數是null，函數傳回值是null；否則返回第一個參數的值。

 （4）top用法：意在取出表中滿足條件的前多少位。top 10---前10位

說到top，突然想到了面試題中經常出現的查詢某表中的前30—40條記錄，注意id可能不連續。利用top可以這樣寫：

1 select top 10 * from A where ID2 not in(select top 30 ID from A  order by ID asc)3 order by ID asc

同時也可以採用如下寫法，只不過可讀性比較差：

1 select top 10 * fron A where ID>2 (select Max(ID) from (select top 30 ID from A order by ID)as t)3 order by ID asc

當然既然有範圍in存在，就可以用exist實現：

1 select top 10 * from A a1 2 WHERE NOT EXISTS 3 (SELECT * from 4 (SELECT TOP 30 * FROM A ORDER BY id asc) a25 WHERE a2.id =a1.id 6 )

但是目前需要考慮到----相互關聯的子查詢：主查詢每遍曆一條記錄時，都要針對主查詢的值執行子查詢，所以效率比較低。

下面介紹一下top與percent聯合使用，percent表示所佔的百分比：例如查詢僱員表裡面，前面百分之二十的僱員的資訊，可以寫sql，查詢結果為兩人。

1 select top(20) percent * from hr.employees  

我們在查詢一下hr.employees（僱員表），同時查詢一下僱員表裡面總共有多少人，查出結果顯示有9人。

1 select count(*) as N‘總人數‘ from hr.employees

可以看出，9個人按百分之二十取整數了，所以查出來的顯示有兩個人。

（5）with ties附加屬性：

當我們查詢訂單表時，查詢sql：

1 select orderid,orderdate2 from sales.orders order by orderdate  desc

加入我們查詢前五個訂單資訊時候，加入top 5

1 select top 5 orderid,orderdate2 from sales.orders order by orderdate  desc

查詢結果

對比沒有加top 5，查詢結果截取了前五條訂單資訊，但是有時候我們需要將與最後一條訂單日期相同的一起取出來，此時就需要採用附加屬性with ties。

（6）over開窗函數：

上面講到要用count彙總函式，在需要分組求和。但採用over 則可以同樣實現基於什麼的求和。省去group by。

1 select firstname,lastname ,count(*) over()  as N‘總人數‘2 from hr.employees

其中over（），括弧裡面可以附加條件，基於什麼進行匯總。不添加，則表示對所有的記錄進行匯總。例如求每位顧客所消費的訂單總額，可以這樣寫：

1 select orderid,custid,sum(val) over (partition by custid) as N‘顧客消費總額‘,2 sum(val) over() as N‘訂單總額‘ from sales.ordervalues

五.次序函數

（1）row_number，行號，一般與over聯合使用。over基於什麼排名。

1 select row_number() over(order by lastname) as N‘行號‘, lastname,firstname2 from hr.employees

（2）rank ，排名,真正意義上的排名，例如：

1 select country,row_number() over(order by country) as N‘rank排名‘, lastname,firstname2 from hr.employees

可以看出，根據country排名，確實排出來啦，但是發現前四位同為UK，按理來說使部分先後順序的，所以在此可以用rank來操作。

1 select country,rank() over(order by country) as N‘rank排名‘, lastname,firstname2 from hr.employees

可以看出來，使用rank以後，country同為UK的並列第一，類似於學生考試成績排名並列第一的情況。

（3）dense_rank,密集排名

通過上面rank排名以後，存在並列第一的情況，但是country為USA的應該為第二，所以就出現了使用密集排名dense_rank進行排名。

1 select country,dense_rank() over(order by country) as N‘dense_rank排名‘, lastname,firstname2 from hr.employees

可以看出採用dense_rank以後，就滿足了某一條件下，同屬一個名次的需求。

（4）分組ntile。按某一條件進行分組。

1 select country,ntile(3) over (order by country) as N‘ntile分組‘,dense_rank() over(order by country) as N‘dense_rank排名‘, lastname,firstname2 from hr.employees3 order by country

有時候為了在某一個範圍內進行排序，比如：

1 select lastname,firstname,country,row_number() over( order by country) as N‘排名‘2 from hr.employees

為了實現根據在country範圍內排序，即country為Uk的為一組進行排序，country為USA的為一組進行排序。可以這樣寫：

1 select lastname,firstname,country,row_number() over( partition by country order by country) as N‘排名‘2 from hr.employees

SQLServer學習筆記<>.基礎知識,一些基本命令,單表查詢(null top用法,with ties附加屬性,over開窗函數),次序函數