2017/3/14

RDBMS:關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)

關(guān)系模型獨(dú)立于語(yǔ)言

SQL有幾種不同類型的語(yǔ)言：數(shù)據(jù)定義語(yǔ)言(DDL)拦英、數(shù)據(jù)處理語(yǔ)言(DML)裂垦、數(shù)據(jù)控制語(yǔ)言(DCL)

DDL:用于處理數(shù)據(jù)對(duì)象的定義，包括的語(yǔ)句有CREATE、ALTER仿野、DROP

DML:用于查詢和修改數(shù)據(jù)蹦哼，包括的語(yǔ)句有SELECT王悍、INSERT岛心、UPDATE沦零、DELTE祭隔、MERGE

DCL:用于處理權(quán)限管理，包括的語(yǔ)句有GRANT路操、REVOKE

關(guān)系模型基于的兩個(gè)分支：集合論和謂詞邏輯

集合：一組對(duì)象疾渴、實(shí)體、

謂詞：在關(guān)系模型中屯仗，謂詞用于維護(hù)數(shù)據(jù)的邏輯完整性和定義它的結(jié)構(gòu);對(duì)數(shù)據(jù)進(jìn)行過(guò)濾以定義其子集;在結(jié)合論中可以用謂詞來(lái)定義集合

example:集合{0,1,2,3,4,5,6,7,8,9}可以定義為以下謂詞為true的所有元素的集合==>"X是一個(gè)大于等于0搞坝，而小于等于9的整數(shù)"

關(guān)系模型的定義(Relational Model)：表示數(shù)據(jù)的語(yǔ)義模型，理論基礎(chǔ)是集合論和謂詞邏輯

關(guān)系模型的目標(biāo)：要用最少或者完全無(wú)冗余的支持完整數(shù)據(jù)的持久化表示魁袜，而且還要將數(shù)據(jù)完整性定義為數(shù)據(jù)的一部分

什么是關(guān)系(Relation)桩撮？

在集合論中關(guān)系是集合的一種表示，在關(guān)系模型中關(guān)系是相關(guān)信息的一個(gè)集合峰弹，在數(shù)據(jù)庫(kù)的實(shí)現(xiàn)中就表現(xiàn)為(數(shù)據(jù)表)

關(guān)系模型的關(guān)鍵要點(diǎn)：一個(gè)關(guān)系代表一個(gè)集合ps:對(duì)關(guān)系進(jìn)行操作的結(jié)果得到的還是一個(gè)關(guān)系

數(shù)據(jù)庫(kù)中設(shè)計(jì)數(shù)據(jù)模型時(shí)店量，所有數(shù)據(jù)都是用關(guān)系來(lái)表示的

域或類型是最基礎(chǔ)的關(guān)系型構(gòu)建模塊之一，一個(gè)域就是一個(gè)屬性可能的(有效的)一組取值的集合

域是一種數(shù)據(jù)庫(kù)中形式最簡(jiǎn)單的謂詞鞠呈，因?yàn)樗拗茖傩栽试S的取值

關(guān)系模型最大的優(yōu)點(diǎn)之一就是將數(shù)據(jù)完整性定義為模型的一部分融师，完整性是通過(guò)規(guī)則(或約束來(lái)實(shí)施的)

域完整性(Domain Integrity),約束的其他例子還包括提供實(shí)體完整性(entity Integrity)的候選鍵，以及提供引用完整性的外鍵(Referential Integrity)

候選鍵(candidate key)：是指在關(guān)系中能夠防止同一元組(數(shù)據(jù)行)多次出現(xiàn)的屬性集(一個(gè)或多個(gè)屬性)蚁吝，基于候選鍵的謂詞能夠唯一標(biāo)識(shí)一行數(shù)據(jù)旱爆。在關(guān)系中可以定義多個(gè)候選鍵，可以任意選擇一個(gè)候選鍵作為主鍵(Primary Key)窘茁，換言之主鍵一定要在候選鍵中選擇疼鸟，主鍵以外的候選鍵成為備用候選鍵(alternate key)

外鍵(foreign key)用于實(shí)施引用完整性，外鍵只在關(guān)系（這里的關(guān)系在數(shù)據(jù)庫(kù)中稱之為表）中一個(gè)或多個(gè)屬性上定義的庙曙，通過(guò)它引用另一個(gè)關(guān)系（數(shù)據(jù)庫(kù)中即為表）中的候選鍵空镜，這種約束要求引用關(guān)系中的外鍵屬性值要與被引用關(guān)系（referenced relation）的候選鍵的屬性值相一致

關(guān)系模型中的規(guī)范化規(guī)則（范式）

第一范式(1NF)：要求表中的行必須是唯一的，屬性應(yīng)該是原子的，這個(gè)范式對(duì)關(guān)系的定義來(lái)說(shuō)是冗余的（最低要求）

行的唯一性是通過(guò)在表中定義一個(gè)唯一的主鍵而實(shí)現(xiàn)的

第二范式(2NF)：第二范式包括兩條規(guī)則==>1）首頁(yè)數(shù)據(jù)必須滿足第一范式吴攒，其次要求非建屬性和候選屬性之間必須滿足一定的條件张抄，每個(gè)非建屬性必須完全函數(shù)依賴于整個(gè)候選鍵ps:函數(shù)依賴指的是存在組合候選鍵中的某些字段決定非關(guān)鍵字段的情況

2017/3/15

第三范式(3NF)：第三范式也有兩條規(guī)則，首先數(shù)據(jù)必須滿足第二范式洼怔，其次所有非建屬性必須非傳遞依賴于候選鍵署惯，這條規(guī)則意味著所有的非建屬性都必須互相獨(dú)立，換句話說(shuō)镣隶，一個(gè)非建屬性不能依賴于其他的非建屬性

總的概括一下第二范式和第三范式：每個(gè)非鍵屬性都依賴于鍵极谊，全部鍵，除了鍵沒(méi)有別的

聯(lián)機(jī)事物處理(OLTP)：數(shù)據(jù)首先進(jìn)入聯(lián)機(jī)事務(wù)處理系統(tǒng)安岂，OLTP系統(tǒng)的重點(diǎn)是的數(shù)據(jù)的輸入轻猖，而不是生成報(bào)表主要處理的事物包括插入、更新和刪除數(shù)據(jù)域那，關(guān)系模型的目標(biāo)主要定位于OLTP系統(tǒng)

數(shù)據(jù)倉(cāng)庫(kù)(Data WareHouses)： 專為數(shù)據(jù)檢索和生成報(bào)表而設(shè)計(jì)的環(huán)境咙边，數(shù)據(jù)倉(cāng)庫(kù)最簡(jiǎn)單的設(shè)計(jì)就是所謂的星型模式，如果規(guī)范化一個(gè)維度表生成表示該維度的多個(gè)表次员，得到的就是雪花型維度败许，而包含雪花型 維度的模式成為雪花模式（相對(duì)于星型模式）

從原系統(tǒng)抽取數(shù)據(jù)，對(duì)數(shù)據(jù)進(jìn)行處理淑蔚，并將數(shù)據(jù)加載到數(shù)據(jù)倉(cāng)庫(kù)的工具成為ETL*(Extract Transform anf Load)市殷。SQL Server提供一個(gè)稱為Microsoft的SSIS（SQL Server Intergration Services）的工具來(lái)處理ETL需求

聯(lián)機(jī)分析處理（OLAP）：系統(tǒng)支持對(duì)聚合后的數(shù)據(jù)進(jìn)行動(dòng)態(tài)的在線分析，為OLAP需求而設(shè)計(jì)的特殊的產(chǎn)品--SSAS(這是一個(gè)獨(dú)立于Sql Sever服務(wù)的一種服務(wù)或引擎)刹衫。用于管理和查詢SSAS的數(shù)據(jù)方塊的語(yǔ)言稱為多為表達(dá)式(MDX,Multidimensional Expressions)

數(shù)據(jù)挖掘（DM,data mining）,數(shù)據(jù)挖掘模型進(jìn)行篩選數(shù)據(jù);用于管理和查詢數(shù)據(jù)挖掘模型的語(yǔ)言是數(shù)據(jù)挖掘擴(kuò)展插件語(yǔ)句（DMX）

數(shù)據(jù)庫(kù)：各種對(duì)象的容器醋寝，這些對(duì)象可以是表、視圖绪妹、存儲(chǔ)過(guò)程（stored procedure）等。

系統(tǒng)數(shù)據(jù)庫(kù)：master柿究、Resource邮旷、model、tempdb蝇摸、msdb

數(shù)據(jù)庫(kù)登錄的賬號(hào)可以關(guān)聯(lián)到windows憑據(jù)婶肩，使用windows驗(yàn)證登陸不需要提供用戶名和密碼使用Sql server驗(yàn)證來(lái)連接SQL Server時(shí)就必須提供用戶名和密碼

數(shù)據(jù)庫(kù)對(duì)象是將被授權(quán)訪問(wèn)的數(shù)據(jù)庫(kù)對(duì)象的實(shí)體。每個(gè)數(shù)據(jù)庫(kù)至少得有一個(gè)數(shù)據(jù)文件和一個(gè)事務(wù)日志文件貌夕。數(shù)據(jù)文件用于保存數(shù)據(jù)庫(kù)對(duì)象數(shù)據(jù)律歼，日志文件則保存SQL Server為了維護(hù)事物而需要的文件

表屬于架構(gòu)，而架構(gòu)又屬于數(shù)據(jù)庫(kù)

在SQL Server環(huán)境中創(chuàng)建數(shù)據(jù)庫(kù)代碼如下：

IF DB_ID('testdb') IS NULL

CREATE ?DATABASE testdb;

ps:DB_ID('')接受一個(gè)數(shù)據(jù)庫(kù)名稱作為輸入啡专，返回它的內(nèi)部數(shù)據(jù)庫(kù)ID险毁，如果輸入名稱的指定的數(shù)據(jù)庫(kù)不存在這個(gè)函數(shù)將返回NULL

在例子中，使用的架構(gòu)是dbo，在每個(gè)數(shù)據(jù)庫(kù)中都會(huì)自動(dòng)創(chuàng)建這個(gè)架構(gòu)畔况，鲸鹦，當(dāng)用戶沒(méi)有將默認(rèn)架構(gòu)顯示關(guān)聯(lián)到其他架構(gòu)時(shí)，就會(huì)將這個(gè)dbo作為默認(rèn)架構(gòu)跷跪。

以下代碼是在數(shù)據(jù)庫(kù)testdb中創(chuàng)建一個(gè)名為Employees的表：

USE testdb /**切換數(shù)據(jù)庫(kù)**/

IF OBJECT_ID('dbo.Employees','U') IS NOT NULL ? /**OBJECT_ID('','')函數(shù)判斷當(dāng)前創(chuàng)建的表是否已經(jīng)存在于testdb數(shù)據(jù)庫(kù)中馋嗜，該函數(shù)接受一個(gè)對(duì)象名稱和類型作為它的參數(shù) ?類型'U'代表用戶表，如果該表在testdb數(shù)據(jù)庫(kù)中存在吵瞻，則刪除它葛菇，然后再創(chuàng)建的新的表**/

DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees (

empid INT NOT NULL,

firstname VARCHAR(30) NOT NULL,

lastname VARCHAR(30) ?NOT NULL,

hiredate ? DATE NOT NULL,

mgrid ? ? ?INT ? NULL,

ssn ? ? ? ? VARCHAR(20) NOT NULL,

salary ? ? ?MONEY ? ? NOT NULL

);

PS:上面的代碼使用了前面推薦的兩部分名稱，如果省略架構(gòu)名橡羞，SQL Server將使用與運(yùn)行這段代碼的數(shù)據(jù)庫(kù)用戶相關(guān)聯(lián)的默認(rèn)架構(gòu)

數(shù)據(jù)的完整性：作為模型的一部分而實(shí)施的數(shù)據(jù)完整性(也就是說(shuō)作為表定義的一部分)稱為成為聲明式數(shù)據(jù)完整性;用代碼來(lái)實(shí)施的數(shù)據(jù)完整性稱為過(guò)程式數(shù)據(jù)完整性

約束（主鍵約束眯停、唯一約束、外鍵約束）

主鍵約束：以前面創(chuàng)建好的Employees表為例尉姨，在它的empid列上定義一個(gè)主鍵約束

ALTER TABLE dbo.Employees

ADD CONSTRAINT PK_Employees

PRIMARY KEY(empid);

唯一約束：唯一約束用來(lái)保證數(shù)據(jù)行的一個(gè)列（或一組列）數(shù)據(jù)的唯一庵朝，可以在數(shù)據(jù)庫(kù)中實(shí)現(xiàn)關(guān)系模型的替換鍵的概念。

2017/3/16

SQL Server Management Studio中快捷鍵

Ctrl+k,Ctrl+c ?注釋選中行

以下代碼在Employees表中定義了ssn列上的唯一約束：

ALTER TABLE dbo.Employees

ADD CONSTRAINT UNQ_Employees_ssn

UNIQUE(ssn);

外鍵約束：用于實(shí)施引用完整性又厉。這種約束在引用表的一組屬性上進(jìn)行定義并指向被引用表中的一組候選鍵(主鍵或唯一約束)九府。注意引用表或被引用表可能是同一張表。外鍵的目的是為了將外鍵允許的值域限制為被引用列中現(xiàn)有的值覆致。

以下代碼創(chuàng)建了一個(gè)名為Orders的表侄旬，其主鍵定義在orderid列上：

--IF OBJECT_ID('dbo.Orders','U') IS NOT NULL

--?????DROP TABLE dbo.Orders;

--CREATE TABLE dbo.Orders(

--?????orderid? INT? NOT NULL,

--?????empid??? INT? NOT NULL,

--?????custid?? VARCHAR(10) NOT NULL,

--?????orderts? DATETIME??? NOT NULL,

--?????qty????? INT???????? NOT NULL,

--?????CONSTRAINT PK_Orders PRIMARY KEY(orderid)

--);

現(xiàn)在要在Orders表的empid列上添加一個(gè)外鍵約束，讓它指向Employees表的主鍵的empid列煌妈，如下所示：

ALTER TABLE dbo.Orders

ADD CONSTRAINT FK_Orders_Employees

FOREIGN KEY(empid)

REFERENCES dbo.Employees(empid);

如果想限制Employees表中mgrid列支持的值域?yàn)橥粡埍碇幸呀?jīng)存在的那些empid列的值儡羔，可以增加以下約束：

ALTER TABLE dbo.Employees

ADD CONSTRAINT FK_Employees_Employees

FOREIGN KEY(mgrid)

REFERENCES dbo.Employees(empid);

禁止操作：當(dāng)試圖刪除表被引用表中的行時(shí)，或更新被引用的候選鍵時(shí)璧诵，如果在引用表中存在相關(guān)的行汰蜘，則此操作不能執(zhí)行

CASCADE:操作將被級(jí)聯(lián)到引用表中的相關(guān)行

ON DELETE CASCADE：意味著當(dāng)從被引用表中刪除一行時(shí)，RDBMS也將從引用表中刪除相關(guān)的行

CHECK約束：定義在表中輸入或修改一行數(shù)據(jù)之前必須滿足的一個(gè)謂詞

以下的檢查約束能保證Employees表中的salary列只支持正數(shù)：

ALTER TABLE dbo.Employees

ADD CONSTRAINT CHK_Employees_salary

CHECK(salary>0);

默認(rèn)約束：默認(rèn)約束與特定的屬性關(guān)聯(lián)

以下代碼為orderts屬性定義了一個(gè)默認(rèn)約束：

ALTER TABLE dbo.Orders

ADD CONSTRAINT DFT_Orders_orderts

DEFAULT(CURRENT_TIMESTAMP) FOR orderts;

第二章 單表查詢

查詢子句是查詢語(yǔ)法的組成部分之宿，當(dāng)討論邏輯查詢處理中發(fā)生的某一部邏輯處理時(shí)族操，通常會(huì)使用階段

FROM子句是邏輯處理階段第一個(gè)要處理的查詢子句，這個(gè)子句用于指定要查詢的表名以及對(duì)這些表進(jìn)行操作的表運(yùn)算符

WHERE子句可以指定一個(gè)謂詞或邏輯表達(dá)式比被，從而過(guò)濾由FROM階段返回的行

SELECT firstname,lastname,ssn,salary

FROM dbo.Employees

WHERE empid=1;

TSQL使用的是三值謂詞邏輯色难，返回結(jié)果為true和不為true是兩個(gè)不同的概念，因?yàn)檫€有一個(gè)結(jié)果為unknown

GROUP BY子句：以指定的元素進(jìn)行分組

SELECT empid,hiredate

FROM dbo.Employees

WHERE empid=2

GROUP BY empid,hiredate

--SELECT empid,YEAR(hiredate) as hiredate

--?????FROM dbo.Employees

--?????WHERE empid=2

--?????GROUP BY empid,YEAR(hiredate);

因?yàn)榫酆虾瘮?shù)只為每個(gè)組返回一個(gè)值等缀，所以一個(gè)元素如果不在GROUP BY列表中出現(xiàn)枷莉，就只能作為聚合函數(shù)(COUNT、SUM尺迂、AVG笤妙、MIN以及MAX)的輸入

SELECT empid,firstname,lastname

FROM dbo.Employees

GROUP BY empid,firstname,lastname;

PS:如果試圖引用不在GROUP BY列表中出現(xiàn)的屬性（例如 ssn）,而且也沒(méi)有將其作為GROUP BY子句之后處理的任何子句中聚合函數(shù)的輸入冒掌，SQL Server引擎就會(huì)報(bào)錯(cuò)

選擇列表中的列 'dbo.Employees.ssn' 無(wú)效，因?yàn)樵摿袥](méi)有包含在聚合函數(shù)或 GROUP BY 子句中危喉。

注意所有的聚合函數(shù)都會(huì)忽略NULL值宋渔，只有一個(gè)例外-----COUNT(*)===>假設(shè)一個(gè)組有5行，其中qty列分別為 10辜限，20皇拣，NULL，20薄嫡，10氧急。表達(dá)式COUNT(*)將返回5，因?yàn)檫@個(gè)組中有5行毫深，而COUNT(qty)返回4吩坝，因?yàn)橹挥?行已知值，如果想處理不重復(fù)的已知值可以在聚合函數(shù)的圓括號(hào)中指定DISTINCT關(guān)鍵字

HAVING子句：用于指定對(duì)組進(jìn)行過(guò)濾的謂詞和表達(dá)式

記住SELECT子句是在FROM哑蔫、WHERE钉寝、GROUP BY、HAVING子句后處理的

ORDER BY子句：用于展示數(shù)據(jù)時(shí)對(duì)輸出結(jié)果中的行進(jìn)行排序闸迷，從邏輯查詢處理來(lái)看嵌纲，ORDER BY是最后一個(gè)處理的子句。

SELECT empid,YEAR(orderts) AS orderyear,COUNT(*) AS numorders

FROM dbo.Orders

WHERE custid=1

GROUP BY empid,YEAR(orderts)

HAVING COUNT(*)>1

ORDER BY empid,orderyear;

表不保證是有序的腥沽，因?yàn)楸硎菫榱舜硪粋€(gè)集合逮走，而集合是無(wú)序的。

ORDER BY是唯一一個(gè)在SELEC階段后被處理的階段

排序出現(xiàn)的列不一定要出現(xiàn)在輸出返回的類中今阳，代碼如下：

SELECT empid,firstname,lastname,salary

FROM dbo.Employees

ORDER BY hiredate;

當(dāng)指定了DISTINCT以后师溅，ORDER BY子句就被限制為只能選取在SELECT列表中出現(xiàn)的那些元素，以下的查詢無(wú)效：

SELECT DISTINCT orderid

FROM dbo.Orders

ORDER BY empid;

消息 145盾舌，級(jí)別 15墓臭，狀態(tài) 1，第 78 行

如果指定了 SELECT DISTINCT妖谴，那么 ORDER BY 子句中的項(xiàng)就必須出現(xiàn)在選擇列表中窿锉。

TOP選項(xiàng)：用于限制查詢返回的行數(shù)或百分比。如果要從Orders表中返回最近的5個(gè)訂單可以采用如下的代碼：

SELECT TOP(5) orderid,orderts,custid,empid

FROM dbo.Orders

ORDER BY orderts DESC;

當(dāng)使用TOP時(shí)窖维，同一ORDER BY 子句既擔(dān)當(dāng)了為TOP決定行的邏輯優(yōu)先順序的角色榆综，同時(shí)也擔(dān)當(dāng)了它的常規(guī)角色（展示數(shù)據(jù)）妙痹，只是最終生成的結(jié)果由表變成了具有固定順序的游標(biāo)铸史。

PERCENT關(guān)鍵字：

SELECT TOP(50) PERCENT orderid,orderts,custid,empid

FROM dbo.Orders

ORDER BY orderts;

2017/03/17

OVER子句用于為行定義一個(gè)窗口，以便進(jìn)行特定的運(yùn)算：

SELECT empid,ssn,salary,

SUM(salary) OVER() AS totalvalue,

SUM(salary) OVER(PARTITION BY ssn ) AS custtotalValue

FROM dbo.Employees;

所有結(jié)果行的totalvalue列表示所有行的價(jià)格總數(shù)怯伊，custtotalValue列表示所有行中與當(dāng)前行具有相同ssn值的那些行的價(jià)格總數(shù)

OVER子句的一個(gè)優(yōu)點(diǎn)就是能夠在返回基本列的同時(shí) 琳轿，在同一行對(duì)他們進(jìn)行聚合 。一下代碼查詢?yōu)镋mployees的每一行計(jì)算當(dāng)前價(jià)格占總價(jià)格的百分比：

SELECT empid,ssn,salary,

100. *salary/ SUM(salary) OVER() AS totalvalue,

100. *salary/SUM(salary) OVER(PARTITION BY ssn ) AS custtotalValue

FROM dbo.Employees;

聚合函數(shù)和排名函數(shù)都是可以支持OVER子句的運(yùn)算類型，由于OVER子句為這些函數(shù)提供了一個(gè)行的窗口崭篡，所以這些函數(shù)又稱為開窗函數(shù)

OVER子句支持的四種排名函數(shù)：ROW_NUMBER(行號(hào))挪哄，RANK(排名)，DENSE_RANK(密集排名)琉闪，以及NTILE迹炼。以下代碼演示了這些函數(shù)的用法：

SELECT empid,ssn,salary,

ROW_NUMBER() OVER(ORDER BY salary) AS rownum,

RANK() OVER(ORDER BY salary) AS rank,

DENSE_RANK() OVER(ORDER BY salary) AS dense_rank,

NTILE(10)? OVER(ORDER BY salary) AS ntile

FROM dbo.Employees

ORDER BY salary;

RANK列為9表示前面有8行具有更小的排序值，DENSE_RANK列為9表示前面有8個(gè)更小的不同的排序值

表達(dá)式ROW_NUMBER() OVER(PARTITION BYcustidORDER BY oredrts)為各行中具有相同custid的子集獨(dú)立的分配行號(hào)

SELECT orderid,empid,custid,orderts,

ROW_NUMBER() OVER(PARTITION BY custid ORDER BY orderts) AS rownum

FROM dbo.Orders

ORDER BY custid,orderts;

如果在SELECT處理階段指定了開窗函數(shù)颠毙，開窗計(jì)算會(huì)在DISTINCT子句（如果存在）之前處理

總結(jié)點(diǎn)：作為目前為止已經(jīng)討論過(guò)的所有子句的總結(jié)斯入，以下列出了他們的邏輯處理順序：

FROM

WHERE

GROUP BY

HAVING

SELECT

1)OVER

2)DISTINCT

3)TOP

ORDER BY

謂詞和運(yùn)算符

T-SQL支持的謂詞有IN、BETWEEN蛀蜜、LIKE等刻两。

IN這個(gè)謂詞用于檢查一個(gè)值（標(biāo)量表達(dá)式）是否與一組元素中的至少一個(gè)相等。以下代碼查詢返回訂單orderid等于1滴某、3磅摹、5的訂單：

SELECT orderid,empid,custid,orderts

FROM dbo.Orders

WHERE orderid IN(1,3,5);

BETWEEN這個(gè)謂詞用于檢查一個(gè)值是否在指定的范圍內(nèi)，包括邊界值霎奢，如下代碼查詢訂單ID在2到5之間的訂單：

SELECT orderid,empid,custid,orderts

FROM dbo.Orders

WHERE orderid BETWEEN 2 AND 5;

LIKE這個(gè)謂詞用于檢查一個(gè)字符串是否與指定的模式匹配户誓，如下代碼查詢返回姓氏以'x'開頭的所有雇員：

SELECT empid,firstname,lastname,salary

FROM dbo.Employees

WHERE lastname LIKE N'x%';

T-SQL中比較運(yùn)算符的使用

以下查詢返回訂單日期在2017/03/05之后生成的所有訂單：

SELECT orderid,empid,custid,orderts

FROM dbo.Orders

WHERE orderts>='2017/03/05';

以下的查詢返回訂單日期在2017/03/05之后并且orderid為1，3椰憋，5的所有訂單：

SELECT orderid,empid,custid,orderts

FROM dbo.Orders

WHERE orderts>='2017/03/05'

AND orderid IN(1,3,5);

CASE表達(dá)式

case表達(dá)式是一個(gè)標(biāo)量表達(dá)式厅克，它基于條件邏輯來(lái)返回一個(gè)值。CASE表達(dá)式有兩種：簡(jiǎn)單表達(dá)式和搜索表達(dá)式橙依。

CASE簡(jiǎn)單格式將一個(gè)值（或一個(gè)標(biāo)量）與一組可能的取值進(jìn)行比較证舟，并返回第一個(gè)匹配的結(jié)果。如果列表中沒(méi)有值等于測(cè)試值窗骑，CASE表達(dá)式就返回其ELSE子句中列出的值女责。如果CASE表達(dá)式中沒(méi)有ELSE子句，則默認(rèn)將其視為ELSE NULL.以下代碼用于描述categoryid列取值的信息：

SELECT orderid,empid,custid,

CASE custid

WHEN 1 THEN '顧客1'

WHEN 2 THEN '顧客2'

ELSE 'UNKNOWN CATEGORY'

END AS categoryName

FROM dbo.Orders;

CASE簡(jiǎn)單表達(dá)式只有一個(gè)測(cè)試值创译，而CASE搜索表達(dá)式不限于只進(jìn)行相等性比較抵知，代碼如下：

SELECT empid,firstname,lastname,salary,

CASE

WHEN salary < 1500 THEN '工資較低'

WHEN salary BETWEEN 1500 AND 3000 THEN '工資一般'

WHEN salary > 3000 THEN '工資較高'

ELSE 'UNKNOWN SALARY'

END AS salaryDesc

FROM dbo.Employees;

NULL值

UNKNOWN和NOT(UNKNOWN)一樣

如果想查找mgrid是NULL的所有行，不應(yīng)該使用謂詞mgrid=NULL,而應(yīng)該使用mgrid IS NULL,以下代碼是兩種查詢方式的對(duì)比：

SELECT empid,firstname,lastname

FROM dbo.Employees

WHERE mgrid=NULL;

返回空集

SELECT empid,firstname,lastname

FROM dbo.Employees

WHERE mgrid IS NULL;

1 ?dai ?xin

2 ? gu shiyi

3 ? li ?xueji

3行記錄

2017/03/20

同時(shí)操作

SELECT子句中所有表達(dá)式的計(jì)算是沒(méi)有順序的软族，他們只是一組表達(dá)式刷喜。在邏輯上列表中的所有表達(dá)式都是在同一時(shí)刻進(jìn)行計(jì)算的。以下代碼是沒(méi)有作用的：

SELECT orderid,YEAR(orderts) AS orderyear,orderyear +1 AS nextyear

FROM dbo.Orders;

SQL Server中的短路求值立砸，代碼示例如下：

SELECT col1,col2

FROM dbo.T1

WHERE col1 <> 0 AND col2 > 2*col1;

處理字符數(shù)據(jù)

SQL Server支持兩種字符數(shù)據(jù)類型--普通字符和Unicode字符掖疮。普通字符數(shù)據(jù)類型包括CHAR和VARCHAR，Unicode字符類型包括NCHAR和NVARCHAR颗祝。

普通字符需要一個(gè)字節(jié)來(lái)保存每個(gè)字符浊闪，而Unicode字符需要兩個(gè)字節(jié)來(lái)保存每個(gè)字符恼布。

名稱中不包含VAR元素的任何數(shù)據(jù)類型都是固定長(zhǎng)度的(CHAR,NCHAR)，SQL Server會(huì)按照列定義的大小搁宾，在行中為該列預(yù)留出固定的空間折汞，所以該列的長(zhǎng)度并不是字符的實(shí)際個(gè)數(shù);名稱中含有VAR元素的數(shù)據(jù)類型是可變長(zhǎng)度(VARCHAR.NVARCHAR),SQL Sercver會(huì)在行中會(huì)字符串的實(shí)際長(zhǎng)度來(lái)保存數(shù)據(jù)。

排序規(guī)則(collation)

得到系統(tǒng)中目前支持的所有排序規(guī)則及其描述盖腿，可以查詢表函數(shù)fn_helpcollations()爽待，代碼如下所示：

SELECT name,description

FROM sys.fn_helpcollations();

當(dāng)沒(méi)有顯示定義任何排序規(guī)則時(shí)，就默認(rèn)使用字典排序(更確切的說(shuō)排序規(guī)則名稱中沒(méi)有顯示的出現(xiàn)BIN元素)翩腐。如果出現(xiàn)BIN元素堕伪，就表示要根據(jù)字符的二進(jìn)制表示對(duì)字符數(shù)據(jù)進(jìn)行排序和比較。

CI--數(shù)據(jù)不區(qū)分大小寫

AS--數(shù)據(jù)區(qū)分重音

可以在4重不同的級(jí)別上定義排序規(guī)則：SQL Server實(shí)例栗菜，數(shù)據(jù)庫(kù)欠雌，列，以及表達(dá)式疙筹。最低級(jí)的排序規(guī)則是比較有效的定義方式富俄。

當(dāng)創(chuàng)建用戶數(shù)據(jù)庫(kù)時(shí)，可以使用COLLATE子句指定數(shù)據(jù)庫(kù)的排序規(guī)則而咆，如果不指定則默認(rèn)采用SQL Server實(shí)例的排序規(guī)則霍比。

數(shù)據(jù)庫(kù)的排序規(guī)則決定數(shù)據(jù)庫(kù)中對(duì)象元數(shù)據(jù)的排序規(guī)則，同時(shí)也是用戶表列的默認(rèn)使用的排序規(guī)則暴备。

以下查詢是不區(qū)分大小寫的：

SELECT empid,firstname,lastname

FROM dbo.Employees

WHERE lastname=N'XIN';

以下查詢是區(qū)分大小寫的：

SELECT empid,firstname,lastname

FROM dbo.Employees

WHERE lastname COLLATE Latin1_General_CS_AS =N'XIN';

單引號(hào)('')用于分割文字字符串悠瞬，如果單引號(hào)是文字字符串的一部分，則需要由兩個(gè)單引號(hào)表示('')

運(yùn)算符和函數(shù)

字符串串聯(lián)運(yùn)算符

T-SQL提供加號(hào)運(yùn)算符涯捻，可以將兩個(gè)或多個(gè)字符串合并或串聯(lián)成一個(gè)字符串浅妆。示例如下：

SELECT empid,firstname +N' '+lastname AS fullname

FROM dbo.Employees;

通過(guò)將一個(gè)CONCAT_NULL_YIELDS_NULL的會(huì)話選項(xiàng)設(shè)置為OFF，就可以改變SQL Server處理串聯(lián)的方式障癌。設(shè)置的代碼如下：

SET CONCAT_NULL_YIELDS_NULL OFF;

如果想把NULL作為一個(gè)空字符串凌外，則應(yīng)該以編程的方式來(lái)實(shí)現(xiàn)。COALESCE()函數(shù)接受一列值涛浙，返回其中第一個(gè)不為NULL的值康辑，COALESCE(region,N''）

SUBSTRING(string,start,length)---SELECT SUBSTRING('abcde',1,3),返回'abc'

LEFT、RIGHT函數(shù)轿亮。他們分別返回輸入字符串從左邊或右邊開始指定字符的個(gè)數(shù)疮薇。SELECT RIGHT('abcde',3);

LEN和DATELENGTH函數(shù)，LEN函數(shù)返回字符串中的字符數(shù)我注，即字符長(zhǎng)度按咒，如果要得到字節(jié)數(shù)則選喲使用DATELENGTH函數(shù)

SELECT LEN(N'abcde');返回5

SELECT DATALENGTH(N'abcde');返回10;

PS:值得注意的是，字節(jié)數(shù)并不一定等于字符數(shù)仓手，對(duì)于LEN（）函數(shù)而言返回的是字符數(shù)胖齐。對(duì)于普通字符而言，這兩個(gè)值相等;但是對(duì)于Unicode字符嗽冒，這兩個(gè)值不等呀伙，因?yàn)閁nicode字符中每個(gè)字符需要兩個(gè)字節(jié)來(lái)保存，所以對(duì)于Unicode字符而言添坊，字節(jié)數(shù)等于2倍字符數(shù)剿另。

CHARINDEX函數(shù)，返回字符串中某個(gè)子串第一次出現(xiàn)的起始位置贬蛙。CHARINDEX(substring,string[,start_pos])雨女，在String中沒(méi)有找到SubString，則CHARINDEX返回0阳准，示例代碼如下：

SELECT CHARINDEX(' ','Dai Xin');

PATINDEX函數(shù)氛堕，PATINDEX(pattern,string)。 參數(shù)pattern使用的模式與T-SQL中LIKE謂詞使用的模式類似野蝇，示例代碼如下讼稚，返回結(jié)果為5：

SELECT PATINDEX('%[0-9]%','abcd123efgh');

REPLACE函數(shù)，將字符串中出現(xiàn)的所有某個(gè)子串替換為另一個(gè)字符串绕沈。REPLACE(string,substring1,substring2);該函數(shù)會(huì)將string中出現(xiàn)的所有substring1替換為substring2锐想。示例代碼如下：

SELECT REPLACE('1-A 2-B','-',':');

1:A 2:B

REPLACE函數(shù)也可以用來(lái)計(jì)算字符串中某個(gè)字符出現(xiàn)的次數(shù)。為此乍狐，先將先將字符串中所有的那個(gè)字符替換為空字符串(長(zhǎng)度為0的字符串)赠摇，再計(jì)算字符串的原始長(zhǎng)度和新長(zhǎng)度的差值。示例代碼如下：

SELECT empid,lastname,LEN(lastname)-LEN(REPLACE(lastname,'i','')) AS numoccur

FROM dbo.Employees;

1 ?xin ?1

2 ?shiyi ?2

3 ?xueji ?1

REPLICATE函數(shù)以指定的次數(shù)復(fù)制字符串的值浅蚪。REPLICATE(string,n)藕帜，示例代碼如下：

SELECT REPLICATE('ABC',3);

ABCABCABC

整數(shù)類型的供應(yīng)商ID的字符串表示是用CAST函數(shù)生成的，這個(gè)函數(shù)用于轉(zhuǎn)換輸入值的數(shù)據(jù)類型

STUFF函數(shù)可以先刪除字符串中的一個(gè)字串惜傲，再插入一個(gè)新的字符串作為替換耘戚。STUFF(string,pos,delete_length,insertstring)。示例代碼如下：

SELECT STUFF('xyz',2,1,'daixin');

xdaixinz

UPPER和LOWER函數(shù)將輸入字符串中所有字符都轉(zhuǎn)換為大寫或小寫操漠。UPPER(string),LOWER(string)收津。

SELECT UPPER('daixin')

SELECT LOWER('DAIXIN');

RTRIM和LTRIM函數(shù)用于刪除字符串中的尾隨空格和前導(dǎo)空格。如果既想刪除輸入字符串的前導(dǎo)空格和尾隨空格浊伙，則可以將一個(gè)函數(shù)的結(jié)果所謂另一個(gè)函數(shù)的輸入來(lái)使用撞秋，示例代碼如下：

SELECT RTRIM(LTRIM(' daixin '));

LIKE謂詞

%通配符，%代表任意長(zhǎng)度的字符串嚣鄙，包括空字符串吻贿，示例代碼如下：

SELECT empid,lastname

FROM dbo.Employees

WHERE lastname LIKE N'x%';

_通配符，_代表任意單個(gè)字符哑子，示例代碼如下：

SELECT empid,lastname

FROM dbo.Employees

WHERE lastname LIKE N'_i%';

[<字符列>]通配符舅列，方括號(hào)中包含一列字符肌割，表示必須匹配列指定字符中的一個(gè)字符，示例代碼如下：

SELECT empid,lastname

FROM dbo.Employees

WHERE lastname LIKE N'[xn]%';

[<字符>-<字符>]通配符帐要，方括號(hào)中包含一個(gè)字符范圍把敞，表示必須匹配指定范圍內(nèi)的一個(gè)字符。示例代碼如下：

SELECT empid,lastname

FROM dbo.Employees

WHERE lastname LIKE N'[a-z]%';

[^<字符列或范圍>]通配符榨惠，表示不屬于指定字符列或范圍內(nèi)的單個(gè)字符奋早，示例代碼如下：

SELECT empid,lastname

FROM dbo.Employees

WHERE lastname LIKE N'[^a-s]%';

ESCAPE轉(zhuǎn)義字符

col1 LIKE '%!_%' ESCAPE '!'；說(shuō)明‘赠橙！’是轉(zhuǎn)義字符的標(biāo)識(shí)耽装，那么后面的'_'就不是通配符

如果想指定字符串文字‘02/12/2017’的格式為mm/dd/yyyy，則可以使用樣式號(hào)101

SELECT CONVERT(DATETIME,'02/12/2017',101);

2017-02-12 00:00:00.000

如果想指定字符串文字‘02/12/2017’的格式為dd/mm/yyyy期揪，則可以使用樣式號(hào)103

SELECT CONVERT(DATETIME,'02/12/2017',103);

2017-12-02 00:00:00.000

CURRENT_TIMESTAMP和GETDATE返回的內(nèi)容相同掉奄，當(dāng)時(shí)優(yōu)先選用CURRENT_STAMP

以下代碼為取得當(dāng)前時(shí)間和時(shí)間函數(shù)的用法：

SELECT GETDATE() AS [GATEDATE],

CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP],

GETUTCDATE() AS [GETUTCDATE],

SYSDATETIME() AS [SYSDATETIME],

SYSUTCDATETIME() AS [SYSUTCDATETIME],

SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET];

將CURRENT_TIMESTAMP或SYSDATETIME轉(zhuǎn)換為DATE或TIME，代碼如下所示：

SELECT CAST(SYSDATETIME() AS DATE) AS [CURRENT_DATE],

CAST(SYSDATETIME() AS TIME) AS [CURRENT_DATE];

CAST和CONVERT函數(shù)用于轉(zhuǎn)換值的數(shù)據(jù)類型

語(yǔ)法：CAST(value AS datetype)

CONVERT(datetype,value[,style_number])

CAST是ANSI標(biāo)準(zhǔn)SQL 凤薛，CONVERT不是

以下代碼是使用CAST和CONVERT處理時(shí)間和日期數(shù)據(jù)類型的例子：

SELECT CAST('20121225' AS DATE);

SELECT CAST(SYSDATETIME() AS DATE);

SELECT CAST(SYSDATETIME() AS TIME);

SELECT CONVERT(CHAR(8),CURRENT_TIMESTAMP,112);

SELECT CAST(CONVERT(CHAR(8),CURRENT_TIMESTAMP,112) AS DATETIME);

SELECT CONVERT(CHAR(12),CURRENT_TIMESTAMP,114);

SELECT CAST(CONVERT(CHAR(12),CURRENT_TIMESTAMP,114) AS DATETIME);

SWITCHOFFSET函數(shù)可以按指定的時(shí)區(qū)對(duì)輸入的DATETIMEOFFSET值進(jìn)行調(diào)整

SWITCHOFFSET（datetimeoffset_value,time_zone）

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'-05:00');//按時(shí)區(qū)-05：00調(diào)整

SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(),'+00:00');//調(diào)整為UTC時(shí)間

TODATETIMEOFFSET函數(shù)

SELECT TODATETIMEOFFSET(SYSDATETIMEOFFSET(),'-05:00');

為當(dāng)前日期增加一年DATEADD(part,n,dt_val)

SELECT DATEADD(year,1,'20170320');

DATEDIF返回兩個(gè)日期和時(shí)間值之間相差的指定部分的計(jì)數(shù)：DATEDIFF(part,dt_val1,dt_val2)

SELECT DATEDIFF(day,'20121225','20170320');

DATEPART函數(shù)返回給定日期和時(shí)間值的指定部分的整數(shù)：

SELECT DATEPART(month,CURRENT_TIMESTAMP);

YEAR挥萌、MONTH、DAY函數(shù)是DATEPART的簡(jiǎn)略版本枉侧。

2017/03/21

以下代碼返回給定日期的所在月份的最后一天引瀑，很重要：

SELECT DATEADD(month,DATEDIFF(month,'19991231','20170321'),'19991231');

所以解決一下問(wèn)題就很容易了，以下代碼返回訂單日期是每個(gè)月最后一天的所有的訂單：

SELECT orderid,empid,orderts,custid

FROM dbo.Orders

WHERE orderts=DATEADD(month,DATEDIFF(month,'19991231',orderts),'19991231');

可以用模式‘%a%a%'來(lái)表達(dá)字符'a'在字符串的任意位置至少出現(xiàn)了兩次

在訂單列表中返回總價(jià)格大于10000的訂單榨馁，這個(gè)問(wèn)題很容易被誤解成返回價(jià)格大于10000的訂單憨栽，注意這是兩個(gè)不同的問(wèn)題，分析該問(wèn)題時(shí)翼虫，要知道首先這不是一行記錄能解決的事情屑柔，我們得按orderid進(jìn)行分組，分完組之后珍剑，我們?cè)偬砑舆^(guò)濾條件掸宛，分析代碼如下：

SELECT orderid,SUM(qty*unitprice) AS totalPrice

FROM dbo.Orders

GROUP BY orderid

HAVING SUM(qty*unitprice)>10000

ORDER BY totalPrice DESC;

以下代碼返回平均運(yùn)費(fèi)最高的三個(gè)國(guó)家：

第三章 聯(lián)接查詢

JOIN表運(yùn)算符對(duì)兩個(gè)表進(jìn)行操作，聯(lián)接有三種基本類型：交叉聯(lián)接招拙、內(nèi)鏈接唧瘾、外聯(lián)接。

交叉聯(lián)接

ANSI SQL-92語(yǔ)法别凤。示例代碼如下：

SELECT e.empid,o.orderid

FROM dbo.Employees AS e

CROSS JOIN dbo.Orders AS o;

交叉聯(lián)接的關(guān)鍵字 ?CROSS JOIN

ANSI SQL-89語(yǔ)法.示例代碼如下：

SELECT e.empid,o.orderid

FROM dbo.Employees AS e,dbo.Orders AS o;

三種聯(lián)接類型都支持自聯(lián)接(self-join),在自聯(lián)接中饰序，必須為 表起別名，如果不為表指定別名规哪，聯(lián)接結(jié)果中的列名就會(huì)有歧義求豫。

創(chuàng)建表Digits,并向其中插入數(shù)據(jù)

--IF OBJECT_ID('dbo.Digits','U') IS NOT NULL

--?????DROP TABLE dbo.Digits;

--CREATE TABLE Digits(

--?????digit INT NOT NULL PRIMARY KEY

--);

--INSERT INTO dbo.Digits(digit) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

已下代碼生成1-1000的整數(shù)序列：

SELECT D3.digit*100+D2.digit*10+D1.digit+1 AS n

FROM dbo.Digits AS D1

CROSS JOIN dbo.Digits AS D2

CROSS JOIN dbo.Digits AS D3

ORDER BY n;

內(nèi)聯(lián)接

ANSI SQL-92語(yǔ)法。須在兩個(gè)表名之間指定INNER_JOIN關(guān)鍵字。INNER關(guān)鍵字是可選的,因?yàn)閮?nèi)聯(lián)接是默認(rèn)的聯(lián)接方式蝠嘉，所以可以只單獨(dú)指定JOIN關(guān)鍵字最疆，聯(lián)接條件的關(guān)鍵字是ON

以下代碼是對(duì)數(shù)據(jù)庫(kù)中的Employees表和Orders表進(jìn)行內(nèi)聯(lián)接運(yùn)算：

SELECT e.empid,e.firstname,e.lastname,o.orderid

FROM dbo.Employees AS e

JOIN dbo.Orders AS o

ON e.empid=o.orderid;

ANSI SQL-89語(yǔ)法.

SELECT e.empid,e.firstname,e.lastname,o.orderid

FROM dbo.Employees AS e,dbo.Orders AS o

WHERE e.empid=o.orderid;

特殊的聯(lián)接實(shí)例：

組合聯(lián)接（composite join）、不等聯(lián)接(non-equi join)蚤告、多表聯(lián)接(multi-table join)

組合聯(lián)接：聯(lián)接條件涉及聯(lián)接兩邊的多個(gè)列的查詢努酸。

新建一個(gè)名為OrderDetailsAudit的客戶審核表，代碼如下：

不等聯(lián)接

多表聯(lián)接

3.4外聯(lián)接

外聯(lián)接是在ANSI SQL-92中被引入的罩缴，因?yàn)樗挥幸环N標(biāo)準(zhǔn)語(yǔ)法---在表名之間指定JOIN關(guān)鍵字，在ON子句中指明聯(lián)接條件层扶。

外聯(lián)接會(huì)應(yīng)用內(nèi)聯(lián)接的兩個(gè)邏輯處理步驟箫章，此外還多加一個(gè)外聯(lián)接特有的第三步--添加外部行。

在外聯(lián)接種要把一個(gè)表標(biāo)記為‘保留表’

LEFT OUTER JOIN ?镜会、 RIGHT OUTER JOIN 檬寂、 ?FULL OUTER JOIN（OUTER 關(guān)鍵字可選）

LEFT關(guān)鍵字表示左邊表的行是保留的

RIGHT關(guān)鍵字表示右邊表的行是保留的

FULL關(guān)鍵字表示左右兩邊表的行都是保留的

新建并填充輔助表Nums的代碼：

--SET NOCOUNT ON;

--IF OBJECT_ID('dbo.Nums','U') IS NOT NULL

--?????DROP TABLE dbo.Employees;

--CREATE TABLE dbo.Nums(

--?????n INT NOT NULL PRIMARY KEY

--);

--DECLARE @i AS INT =1;

--BEGIN TRAN

--?????WHILE @i <=100000

--?????BEGIN

--????????????INSERT INTO dbo.Nums VALUES (@i);

--????????????SET @i=@i+1;

--?????END

--?????COMMIT TRAN

--?????SET NOCOUNT OFF;

這部分較難，過(guò)后再理解

1)寫一條查詢語(yǔ)句把所有雇員的記錄復(fù)制5次戳表。涉及的表：dbo.Employees和dbo.Nums

SELECT E.empid,E.firstname,E.lastname,Nums.n

FROM dbo.Employees AS E

CROSS JOIN dbo.Nums

WHERE Nums.n <= 5

ORDER BY n,empid;

2)寫一個(gè)查詢桶至，為每個(gè)雇員和從2009/06/12至2009/06/16范圍內(nèi)的每天返回一行

第4章 子查詢

最外層查詢結(jié)果集會(huì)返回給調(diào)用者，稱為外部查詢匾旭；內(nèi)部查詢結(jié)果是供外部查詢使用的镣屹，也稱為子查詢。

子查詢可以分為獨(dú)立子查詢和相關(guān)子查詢价涝。獨(dú)立子查詢不依賴于它所屬的外部查詢女蜈，而相關(guān)子查詢必須依賴于它所屬的外部查詢。

子查詢返回的結(jié)果可以是一個(gè)單獨(dú)的值色瘩，多值或整個(gè) 表結(jié)果伪窖。本章重點(diǎn)討論返回單個(gè)值的子查詢（標(biāo)量子查詢）和多個(gè)值的子查詢（多值子查詢）

標(biāo)量子查詢是返回單個(gè)值的子查詢，而不管它是不是獨(dú)立子查詢或是相關(guān)子查詢居兆，因?yàn)橄嚓P(guān)子查詢也可以返回單個(gè)值覆山。標(biāo)量子查詢可以出現(xiàn)在外部查詢中使用單個(gè)值的任何地方（WHERE、 SELECT）

以下代碼返回訂單表中泥栖，訂單ID最大的訂單信息：

DECLARE @maxid AS INT =(SELECT MAX(orderid) FROM dbo.Orders);

SELECT orderid,orderts,empid,custid

FROM dbo.Orders

WHERE orderid=@maxid;

將變量轉(zhuǎn)化成標(biāo)量子查詢簇宽，代碼示例如下：

SELECT orderid,orderts,empid,custid

FROM dbo.Orders

WHERE orderid=(SELECT MAX(orderid) FROM dbo.Orders);

對(duì)于有效的標(biāo)量子查詢，它的返回值不能超過(guò)一個(gè)吧享。如果標(biāo)量子查詢返回了多個(gè)值晦毙，在運(yùn)行時(shí)可能會(huì)失效

SELECT orderid

FROM dbo.orders

WHERE empid=(SELECT E.empid FROM dbo.Employees AS E WHERE E.lastname LIKE N'x%');

消息 512，級(jí)別 16耙蔑，狀態(tài) 1见妒，第 357 行

子查詢返回的值不止一個(gè)。當(dāng)子查詢跟隨在 =、!=须揣、<盐股、<=、>耻卡、>= 之后疯汁，或子查詢用作表達(dá)式時(shí)，這種情況是不允許的卵酪。

多值子查詢幌蚊，它的返回值不只一個(gè)，謂詞用IN溃卡，還是上面的例子：

SELECT orderid

FROM dbo.orders

WHERE empid IN (SELECT E.empid FROM dbo.Employees AS E WHERE E.lastname LIKE N'x%');

以下代碼返回沒(méi)有下過(guò)訂單的客戶：

SELECT empid,firstname,lastname

FROM dbo.Employees

WHERE empid NOT IN (SELECT O.empid FROM dbo.Orders O);

2017/03/22

新建一個(gè)名為Orders的表溢豆，并用TSQLFundamentals2008數(shù)據(jù)庫(kù)中Orders表訂單ID為偶數(shù)的訂單來(lái)填充這個(gè)表

這里需要注意一下 SELECT INTO子句是用來(lái)創(chuàng)建一個(gè)目標(biāo)表，并用查詢出來(lái)的結(jié)果集填充這個(gè)表

借助輔助表Nums瘸羡，篩選出介于Orders表中訂單ID的最小值和最大值之間漩仙，而且沒(méi)有在Orders表的訂單ID集合中出現(xiàn)的數(shù)字。

以下代碼是自己運(yùn)行的結(jié)果：

USE tempdb;

--SELECT *

--?????INTO dbo.Orders

--?????FROM testdb.dbo.Orders

--?????WHERE orderid % 2=0;

SELECT n

FROM testdb.dbo.Nums

WHERE n BETWEEN (SELECT MIN(O.orderid) FROM dbo.Orders AS O) AND (SELECT MAX(O.orderid) FROM dbo.Orders AS O)

AND n NOT IN (SELECT O.orderid FROM dbo.Orders AS O);

4.2相關(guān)子查詢

相關(guān)子查詢是指引用外部查詢中出現(xiàn)的表的列的子查詢犹赖，這就意味著子查詢要依賴于外部查詢队他。

以下代碼查詢會(huì)為每一個(gè)客戶返回其訂單ID的最大的訂單：

SELECT orderid,custid,orderts,empid

FROM dbo.Orders AS O1

WHERE orderid=(

SELECT MAX(O2.orderid) FROM dbo.Orders AS O2

WHERE O2.custid=O1.custid//子查詢依賴于外部查詢，一個(gè)客戶可能有多個(gè)訂單峻村，我們選擇orderid最大的那個(gè)

);

以下代碼返回麸折，當(dāng)前訂單金額占客戶訂單總額的百分比：

4.2.1 EXISTS謂詞

它的輸入是一個(gè)子查詢，粘昨，如果子查詢能夠返回任何行磕谅，則謂詞返回TRUE，否則返回FALSE雾棺。

以下代碼返回膊夹，下過(guò)訂單的西班牙客戶：

以下代碼返回沒(méi)有下過(guò)訂單的西班牙客戶：

EXISTS謂詞是使用二值邏輯

對(duì)于每一個(gè)訂單，返回當(dāng)前訂單的信息和前一個(gè)訂單的ID捌浩，示例代碼如下所示：

SELECT orderid,orderts,empid,

(SELECT MAX(O2.orderid) FROM dbo.Orders AS O2

WHERE O2.orderid < O1.orderid) AS preorderid

FROM dbo.Orders AS O1;

對(duì)于每一個(gè)訂單放刨，返回當(dāng)前訂單的信息和下一個(gè)訂單的ID，示例代碼如下：

SELECT orderid,orderts,empid,

(SELECT MIN(O2.orderid) FROM dbo.Orders AS O2

WHERE O2.orderid > O1.orderid) AS nextorderid

FROM dbo.Orders AS O1;

假設(shè)現(xiàn)在有個(gè)任務(wù)需要返回每年的訂單年份尸饺、訂貨量进统，以及連續(xù)幾年的訂貨量：

現(xiàn)在我們要返回沒(méi)有下過(guò)訂單的客戶，示例代碼如下：

現(xiàn)在我們假設(shè)Orders訂單表中浪听，新增一條數(shù)據(jù)然后將他的custid設(shè)置為NULL螟碎，那么以上的代碼就會(huì)返回空集，unknown迹栓，那么這時(shí)候怎么做才能讓他顯示上面的結(jié)果呢掉分？示例代碼如下：

SELECT empid,firstname,lastname

FROM dbo.Employees AS E

WHERE empid NOT IN(SELECT O.empid FROM dbo.Orders AS O WHERE O.empid IS NOT NULL);

隱式的排除NULL值的一個(gè)例子是使用NOT EXISTS謂詞取代NOT IN謂詞，如下圖所示：

--IF OBJECT_ID('dbo.MyShippers','U') IS NOT NULL

--?????DROP TABLE dbo.MyShippers;

--CREATE TABLE dbo.MyShippers(

--?????shipper_id INT NOT NULL,

--?????companyname NVARCHAR(40) NOT NULL,

--?????phone NVARCHAR(24) NOT NULL,

--?????CONSTRAINT PK_MyShippers PRIMARY KEY (shipper_id)

--);

--INSERT INTO dbo.MyShippers(shipper_id,companyname,phone) VALUES(1,N'Shipper GVSUA','(503) 555-0137');

--INSERT INTO dbo.MyShippers(shipper_id,companyname,phone) VALUES(2,N'Shipper ETYNR','(425) 555-0136');

--INSERT INTO dbo.MyShippers(shipper_id,companyname,phone) VALUES(3,N'Shipper ZHISN','(415) 555-0138');

以下查詢返回將訂單發(fā)貨給1號(hào)客戶的發(fā)貨人，代碼如下：

SELECT shipper_id,companyname

FROM dbo.MyShippers

WHERE shipper_id IN(SELECT shipper_id FROM dbo.Orders WHERE custid=1);

以上的代碼是有問(wèn)題的酥郭，因?yàn)镺rders表中沒(méi)有shipper_id列华坦，但是它也能返回以下的數(shù)據(jù)：

1 ?Shipper GVSUA

2 ?Shipper ETYNR

3?Shipper ZHISN

因?yàn)樵谧硬樵冎袥](méi)有找到，就會(huì)去外部查詢中找不从，這樣就變成了相關(guān)子查詢了惜姐。

怎么解決這個(gè)問(wèn)題呢？

為表起別名椿息，在被查詢的列名前加上前綴歹袁。

1）返回活動(dòng)最后一天所下的所有訂單

SELECT orderid,empid,orderts,custid

FROM dbo.Orders

WHERE orderts=(SELECT MAX(O.orderts)? FROM dbo.Orders AS O);

2)返回訂單數(shù)量最多的客戶的信息

SELECT TOP(1) WITH TIES O.custid

FROM dbo.Orders AS O

GROUP BY O.custid

ORDER BY COUNT(*) DESC;//先獲取訂單最多的客戶的ID

//接下來(lái)在根據(jù)custid查詢訂單最多的客戶下過(guò)的所有訂單

SELECT custid,orderid,orderts,empid

FROM dbo.Orders

WHERE custid IN(SELECT TOP(1) WITH TIES O.custid

FROM dbo.Orders AS O

GROUP BY O.custid

ORDER BY COUNT(*) DESC);

3）返回從2017/03/10以后沒(méi)有處理過(guò)訂單的雇員

首先查詢03/10以后的處理訂單的雇員ID

SELECT O.empid

FROM dbo.Orders AS O

WHERE orderts > '20170310';

然后再在Employees表中查找empid列不屬于以上結(jié)果集的記錄

SELECT empid,firstname,lastname

FROM dbo.Employees

WHERE empid NOT IN(SELECT O.empid

FROM dbo.Orders AS O

WHERE orderts > '20170310');

還有例子沒(méi)有列出來(lái)