MSSQL數(shù)據(jù)庫各種語句學(xué)習(xí)資料

1、【精品文檔】如有侵權(quán)，請聯(lián)系網(wǎng)站刪除，僅供學(xué)習(xí)與交流MSSQL數(shù)據(jù)庫各種語句學(xué)習(xí)資料.精品文檔.數(shù)據(jù)庫（catalog）表（table）列（column） 或叫 字段（field） 數(shù)據(jù)類型（datatype）記錄（record） 或叫 行（row）主鍵（PrimaryKey）索引（index）表關(guān)聯(lián)：這種將兩張表通過字段關(guān)聯(lián)起來的方式就被稱為“表關(guān)聯(lián)”，關(guān)聯(lián)到其他表主鍵的字段被稱為“外鍵”例子:select * from employees where age<18 delete from employees where position=名譽(yù)總裁create table T_pers

2、on (FName Varchar(20),FAge int,FRemark Varchar(20),primarykey(FName);create table T_Debt(FNumber Varchar(20),FAmount Numeric(10,2) NOT NULL,FPerson varchar(20),PrimaryKey(FNumber),foreignkey(FPerson) references T_Person(FName);insert into T_person(FName,FAge,FRemark)values('tom',18,'USA&

3、#39;) 注：在插入數(shù)據(jù)的時候某些字段沒有值，我們可以忽略這些字段，例子：insert into T_Person(FAge,FName) values(22,'lxf')說明：Numeric(10,2) 指定字段是數(shù)字型，長度為10位，小數(shù)為兩位 foreignkey(FPerson)外部約束主鍵為FPerson說明：增加一個列Alter table tabname add columnname type例子：alter table dbo.T_Person add Fcity varchar(20)*非空約束對數(shù)據(jù)插入或更新的影響 如果對一個字段添加了非空約束，那么我們是

4、不能向這個字段中插入或更新為NULL值的。*主鍵對數(shù)據(jù)插入或更新的影響 主鍵是在同一張表中必須是唯一的，如果在進(jìn)行數(shù)據(jù)插入或更新的時候指定的主鍵與表中已有的數(shù)據(jù)重復(fù)的話則會導(dǎo)致違反主鍵約束的異常。*外鍵對數(shù)據(jù)插入或更新的影響 外鍵是指向另一個表中已有的數(shù)據(jù)的約束，因此外鍵值必須是在目標(biāo)表中存在的。如果插入或更新的數(shù)據(jù)在目標(biāo)表中不存在的話則會導(dǎo)致違反外鍵約束異常。*UPDATEupdate T_Person set FRemark='sonin'update T_Personset FAge=12where FName='tom'update T_Personse

5、t FAge=22where FName='jim' or FName='LXF'*DELETEdelete from T_Person;刪除T_Person表中的所有數(shù)據(jù)drop table T_Person;刪除表中的所有數(shù)據(jù)，及把表結(jié)構(gòu)全部刪除。delete from T_Person where FAge>20 or FRemark='Mars'*數(shù)據(jù)檢索select * from T_Employeeselect FNumber,FName,FAge,FSalary from T_Employeeselect FNumber as

6、 編號，F(xiàn)Name as 姓名，F(xiàn)Age as 年齡 from T_Employee (其中的as不是必須的，是可以省略的)select * from T_Employeewhere FSalary<5000 or FAge>25;幾種聚合函數(shù)：MAX 計(jì)算字段最大值MIN 計(jì)算字段最小值A(chǔ)VG 計(jì)算字段平均值SUM 計(jì)算字段合計(jì)值COUNT 統(tǒng)計(jì)數(shù)據(jù)條數(shù)select MAX(FSalary) from T_Employeewhere FAge>25 注：查詢年齡大于25歲的員工的最高工資。select MAX(FSalary) as MAX_SALARY from T_Em

7、ployeewhere FAge>25 select AVG(FAge) from T_Employeewhere FSalary>3800 注：統(tǒng)計(jì)工資大于3800元的員工的平均年齡。select SUM(FSalary) from T_Employee; 注：統(tǒng)計(jì)應(yīng)支出工資的總額。select MIN(FSalary),MAX(FSalary) from T_Employee; 注：多次使用聚合函數(shù)，統(tǒng)計(jì)公司的最低工資和最高工資。select COUNT(*),COUNT(FNumber) from T_Employee; 注：COUNT(*)統(tǒng)計(jì)的是結(jié)果集的總條數(shù)，而COUN

8、T(FNumber)統(tǒng)計(jì)的則是除了結(jié)果集中FNumber字段不為空值（也就是不等于NULL）的記錄的總條數(shù)。*排序select * from T_Employeeorder by FAge ASC 注：按升序排列， ASC是可以省略的 select * from T_Employeeorder by FAge DESC 注：按降序排列，select * from T_Employeeorder by FAge DESC, FSalary DESC; 注：order by 允許指定多個排序列，首先按第一個排序，分不出的按第二個排序。* select * from T_Employee where

9、 FAge>23 order by FAge DESC,FSalary DESC;注：ORDER BY 子句要放到where子句后，不能顛倒它們的順序。*通配符過濾 SQL中的通配符過濾使用LIKE關(guān)鍵字。注：使用通配符時，數(shù)據(jù)庫要對全表進(jìn)行掃描，所以速度非常慢，不要過分使用通配符。1.單字符匹配 select * from T_Employee where FName LIKE '_erry' 注：以任意字符開頭，剩余部分為“erry”。 select * from T_Employee where FName LIKE '_n_' ; 注：檢索長度為4

10、，第三個字符為“n”，其他字符為任意字符的姓名。2.多字符匹配 select * from T_Employeewhere FName LIKE 'T%' ; 注：檢索以“T”開頭，長度任意， select * from T_Employeewhere FName LIKE '%n%' ; 注：檢索姓名中包含字母“n”的員工信息select * from T_Employeewhere FName LIKE '%n_' ; 注：檢索最后一個字符為任意字符，倒數(shù)第二個字符為“n”長度任意的字符串。select * from T_Employeewh

11、ere FName LIKE 'SJ%' ;注：檢索的是以“S”或者“J”開頭，長度任意的數(shù)據(jù)select * from T_Employeewhere FName LIKE 'SJ%' ;注： 否定符“”是來對集合取反，即檢索的是不以“S”或者“J”開頭，長度任意的數(shù)據(jù)*空值檢測 select * from T_Employeewhere FName IS NULL ; 注：不能使用普通的等于運(yùn)算符進(jìn)行判斷，而要使用IS NULL關(guān)鍵字。select * from T_Employeewhere FName IS NOT NULL ; 注：檢索FName字段不

12、為空的數(shù)據(jù)。select * from T_Employeewhere FName IS NOT NULL AND FSalary<5000; 注：查詢所有姓名已知 且工資小于5000的員工的信息。*反義運(yùn)算符select * from T_Employeewhere FAge!=22 AND FSalary!<2000 ;注：檢索所有年齡不等于22歲并且工資不小于2000員的信息。<> 不等于<= 不大于>= 不小于NOT 運(yùn)算符用來將一個表達(dá)式的值取反select * from T_Employeewhere NOT(FAge=22) AND NOT(F

13、Salary<2000) ;注：檢索所有年齡不等于22歲并且工資不小于2000元的信息。“!”運(yùn)算符只能運(yùn)行MSSQL和DB2兩種數(shù)據(jù)庫上，統(tǒng)一運(yùn)算符可以使用在所有數(shù)據(jù)庫中，建議采用NOT運(yùn)算符，能比較容易的表達(dá)要實(shí)現(xiàn)的需求。*多值檢測 select FAge,FNumber,FName from T_Employeewhere FAge IN(23,25,28) ; 注：為了解決進(jìn)行多個離散值的匹配問題，SQL提供了IN語句。檢索年齡為23，25，28的數(shù)據(jù)。select * from T_Employeewhere FAge between 23 and 60 ;注：檢索年齡在23到

14、60歲之間的數(shù)據(jù)，包括23和60。select * from T_Employeewhere (FSalary between 2000 and 3000)OR (FSalary between 5000 and 8000) ; 注：檢索所有工資介于2000元到3000元之間以及5000元到8000元的員工信息。*數(shù)據(jù)分組ALTER TABLE T_Employee ADD FSubCompany VARCHAR(20);ALTER TABLE T_Employee ADD FDepartment VARCHAR (20); 注：ALTER ADD 通過更改、添加、除去列和約束，或者通過啟用或

15、禁用約束和觸發(fā)器來更改表的定義。*GROUP BY 子句進(jìn)行分組 select FAge from T_Employeewhere FSubCompany='Beijing'group by FAge ; 注：采用分組以后的查詢結(jié)果是以分組形式提供的。select FSubCompany,FDepartment from T_Employeegroup by FSubCompany,FDepartment ; 注：先根據(jù)FSubCompany，再在每個小組內(nèi)根據(jù)FDepartment進(jìn)行二次分組，查詢數(shù)據(jù)select FAge,COUNT(*) AS CountOfThisAg

16、e from T_EmployeeGROUP BY FAge ; 注：檢索每個年齡段的員工的人數(shù)select FSubCompany,FAge,COUNT(*) AS CountOfThisSubCompAge from T_Employeegroup by FSubCompany,FAgeorder by FSubCompany ; 注：統(tǒng)計(jì)每個公司的年齡段的人數(shù)。*“COUNT(*)”對每個分組統(tǒng)計(jì)總數(shù)，這樣就可以統(tǒng)計(jì)出每個公司每個年齡段的員工的人數(shù)了。select FAge,COUNT(*) AS CountOfThisAge from T_Employeegroup by FAgeHA

17、VING COUNT(*) IN (1,3) ; 注：HAVING語句和WHERE幾乎是一樣的，不過使用WHERE的時候GROUP BY子句要位于WHERE子句之后，而HAVING時，要在之前。*在HAVING語句中不能包含未分組的列名。例：select FAge,COUNT(*) AS CountOfThisAge from T_Employeewhere FName IS NOT NULLGROUP BY FAge ;*抑制數(shù)據(jù)重復(fù)distinct 關(guān)鍵字是用來進(jìn)行重復(fù)數(shù)據(jù)抑制的最簡單的功能。select DISTINCT FDepartment from T_Employee ; 注：D

18、ISTINCT是對整個結(jié)果集進(jìn)行數(shù)據(jù)重復(fù)抑制的，而不是針對每個列。計(jì)算字段*常量字段select 'CowNew集團(tuán)',918000000,FName,FAge,FSubCompany from T_Employee*字段間的計(jì)算select FNumber,FName,FAge*FSalary AS '工資指數(shù)' from T_Employee ;select 125+521,FNumber,FName,FSalary/(FAge-21) AS FHappyIndex from T_Employeeselect * from T_Employeewhere F

19、Salary/(FAge-21)>1000 ;*數(shù)據(jù)處理函數(shù)LEN 計(jì)算字符串長度的函數(shù) select FName,LEN(FName) AS namelength from T_Employeewhere FName IS NOT NULLSUBSTRING 取得字符串的子串的函數(shù),接受三個參數(shù),第一個為要取的主字符串,第二個為子串的起始位置,第三個為子串的長度 select FName,SUBSTRING(FName,2,3) from T_Employeewhere FName IS NOT NULLSIN 計(jì)算正弦函數(shù)值的函數(shù)ABS 計(jì)算絕對值的函數(shù)*多個函數(shù)可以嵌套使用sele

20、ct FName,FAge,SIN(FAge),ABS(SIN(FAge) from T_Employee*字符串的拼接select '工號為'+FNumber+'的員工姓名為'+FName from T_Employeewhere FName IS NOT NULLselect * from T_Employeewhere FSalary BETWEEN FAge*1.5+2000 AND FAge*1.8+5000 ;注：檢索上限為年齡的1.8倍加上5000，下限為年齡的1.5倍加上2000元的員工的信息。select MAX(FSalary/FAge) A

21、S MAXVALUE,MIN(FSalary/FAge) AS MINVALUE from T_Employee ;查詢工資年齡指數(shù)的最高和最低值*年齡全部加1UPDATE T_Employee SET FAge=FAge+1 ;*不從實(shí)體表中取得數(shù)據(jù)select 1 ;select LEN('abc')select 1,2,3,'a','b','c'*聯(lián)合結(jié)果集select FNumber,FName,FAge from T_Employeewhere FAge<30UNIONselect FldCardNumber,FN

22、ame,FAge from T_TempEmployeewhere FAge>40UNIONselect FldCardNumber,FName,FAge from T_TempEmployeewhere FAge<30 ;*注：聯(lián)合結(jié)果集不必受被聯(lián)合的多個結(jié)果集之間的關(guān)系限制，但還是要遵守兩個原則：一是每個結(jié)果集必須有相同的列數(shù)，二是每個結(jié)果集的列必須類型相容。（如果需要將未知列補(bǔ)足為一個默認(rèn)值，那么可以使用常量字段）在默認(rèn)情況下，UNION運(yùn)算符合并了兩個查詢結(jié)果集，其中完全重復(fù)的數(shù)據(jù)行被合并為了一條。如果需要在聯(lián)合結(jié)果集中返回所有的記錄而不管它們是否唯一，則需要在UNION運(yùn)

23、算符后使用ALL操作符，例子：select FName,FAge from T_Employee UNION ALL select FName,FAge from T_Employee*聯(lián)合結(jié)果集應(yīng)用舉例*員工年齡報(bào)表select '正式員工最高年齡',MAX(FAge) from T_EmployeeUNIONselect '正式員工最低年齡',MIN(FAge) from T_employeeUNIONselect '臨時工最高年齡',MAX(FAge) from T_TempEmployeeUNIONselcet '臨時工最低年齡&

24、#39;,MIN(FAge) from T_TempEmployee*正式員工工資表表要求查詢每位正式員工的信息，包括工號、工資，并且在最后一行加上所有員工工資額合計(jì)。select FNumber,FSalary from T_EmployeeUNIONselect '工資合計(jì)',SUM(FSalary) from T_Employee*打印5以內(nèi)自然數(shù)的平方select 1,1*1UNIONselect 2,2*2UNIONselect 3,3*3UNIONselect 4,4*4UNIONselect 5,5*5*列出員工姓名要求列出公司中所有員工（包括臨時工）的姓名，將重

25、復(fù)的姓名過濾掉select FName from T_EmployeeUNIONselect FName from T_TempEmployee*分別列出正式員工和臨時工的姓名要求分別列出正式員工和臨時工的姓名，要保留重復(fù)的姓名select '以下是正式的員工的姓名'UNION ALLselect FName from T_EmployeeUNION ALLselect '以下是臨時工的姓名'UNION ALLselect FName from T_TempEmployee函數(shù)的應(yīng)用*數(shù)學(xué)函數(shù)（空，以后補(bǔ)齊）*字符串函數(shù)（空，以后補(bǔ)齊）*日期時間函數(shù)日期類型：年

26、-月-日 Date來表示日期類型時間類型：小時-分-秒 Time來表示時間類型日期時間類型：年-月-日 小時-分-秒 DateTime來表示日期時間類型時間戳類型：比日期時間類型精度要求還要高的日期時間信息， TimeStamp來表示日期時間戳類型*在MYSQL MSSQL DB2中可以用字符串來表示日期時間類型，數(shù)據(jù)庫系統(tǒng)會自動在內(nèi)部將它們轉(zhuǎn)換為日期時間類型。在MSSQL中，GETDATE() 返回的是包括了日期、時間的時間戳信息，CONVERT (VARCHAR(50),GETDATE(),101) ,可以得到日期時間值的日期部分 01/24/2008CONVERT(VARCHAR(50)

27、,GETDATE(),108) ,可以得到日期時間值的時間部分 21：37：19*日期增減在MSSQL中 提供了DATEADD()函數(shù)，進(jìn)行日期時間的加法運(yùn)算，格式如：DATEADD(datepart,number,date)其中date為待計(jì)算的日期，datepart指定要返回心智的日期組成部分，MSSQL 2005中可識別的日期部分及其縮寫：取值 說明year 年份quarter 季度month 月份dayofyear 當(dāng)年度的第幾天day 日week 當(dāng)年度的第幾周weekday 星期幾hour 小時minute 分second 秒millisecond 毫秒例子：select FBir

28、thDay,DATEADD(YEAR,3,FBirthDay) AS threeyrs,DATEADD(QUARTER,20,FBirthDay) AS ttqutrs,DATEADD(MONTH,68,FBirthDay) AS sxtmonths,DATEADD(WEEK,-1000,FBirthDay) AS thweeikfrom T_Person ; 注：計(jì)算每個人出生后3年、20個季度、68個月以及1000個周前的日期。*計(jì)算日期差額在MSSQL中提供了DATEDIFF()函數(shù)用于計(jì)算兩個日期之間的差額，格式：DATEDIFF(datepart，startdate,enddate)

29、 其中參數(shù)datepart為計(jì)算差額時使用的單位，startdate為起始日期，enddate為結(jié)束日期。例子：select FRegDay,FBirthDay,DATEDIFF(WEEK,FBirthDay,FRegDay) from T_Person ;注：計(jì)算注冊日期和出生日期之間的周數(shù)差額。*計(jì)算一個日期是星期幾在MSSQL中提供了DATENAME函數(shù)，這個函數(shù)可以返回一個日期的特定部分，并且盡量用名稱來表述這個特定部分，格式：DATENAME(datepart，date) ；其中參數(shù)date為待計(jì)算日期，date參數(shù)也可以是日期格式的字符串；參數(shù)datepart指定要返回的日期部分的

30、參數(shù)。例子：select FBirthDay,DATENAME(Weekday,FBirhtDay),FRegDay,DATENAME(DW,FRegDay)from T_Person注：計(jì)算出生日期和注冊日期各是星期幾；*取得日期的指定部分，比如檢索本年的每個月的16日的銷售量在MSSQL中使用前面介紹了的DATENAME()函數(shù)，使用它可以提取日期的任意部分例子：select FBirthDay, DATENAME(year,FBirthDay) as y, DATENAME(dayofyear,FBirthDay) as d, DATENAME(week,FBirthDay) as u

31、from T_Person; 注：提取每個人員的出生年份、出生是是當(dāng)年的第幾天、出生時是當(dāng)年的第幾周； 在MSSQL中還提供了一個DATEPART()函數(shù)，這個函數(shù)也可以用來返回一個日期的特定部分，格式：DATEPART(datepart，date) 其中參數(shù)date為待計(jì)算日期，date參數(shù)也可以是日期格式的字符串，參數(shù)datepart指定要返回的日期部分參數(shù)，顯然使用dayofyear做為datepart參數(shù)調(diào)用DATEPART()函數(shù)就可以得到一個日期是當(dāng)年的第幾天；使用year做為datepart參數(shù)調(diào)用DATEPART()函數(shù)就可以得到一個日期的年份；以此類推。例子：select F

32、BirthDay,DATEPART(Dayofyear,FBirthDay),FRegDay,DATEPART(YEAR,FRegDay)from T_Person ; 計(jì)算出生日期是當(dāng)年第幾天以及注冊日期中的年份部分；DATEPART() 和 DATENAME() 的不同：DATEPART()函數(shù)返回值是數(shù)字， DATENAME()函數(shù)則會將盡可能的以名稱的方式做為返回值。*其他函數(shù)*類型轉(zhuǎn)換在MSSQL中提供了CAST()和CONVERT()兩個函數(shù)進(jìn)行類型轉(zhuǎn)換，CAST()是符合ANSI SQL99的函數(shù)，CONVERT()是符合ODBC標(biāo)準(zhǔn)的函數(shù)，格式：CAST(expression

33、AS data_type) CONVERT(data_type,expression) 參數(shù)expression為待進(jìn)行類型轉(zhuǎn)換的表達(dá)式，而type為轉(zhuǎn)換的目標(biāo)類型，例子：select CAST('-30' AS INTEGER) AS i, CONVERT(DECIMAL,'3.1415926') AS d, CONVERT(DATETIME,'2008-08-08 08:09:10') AS dt 注：以整形、數(shù)值、日期時間類型為目標(biāo)類型的數(shù)據(jù)轉(zhuǎn)換例子2：select FIdNumber, RIGHT(FIdNumber,3) AS 后三位，

34、 CAST(RIGHT(FIdNumber,3) AS INTEGER) AS 后三位的整數(shù)形式 CAST(RIGHT(FIdNumber,3) AS INTEGER)+1 AS 后三位數(shù)加1， CONVERT(INTEGER,RIGHT(FIdNumber,3)/2 AS 后三位除以2 from T_Person*空值處理COALESCE()函數(shù)，這個函數(shù)主要用來進(jìn)行空值處理，COALESCE(expression,value1,value.,valuen) 注：第一個參數(shù)expression為待檢測的表達(dá)式，而其后的參數(shù)個數(shù)不定。COALESCE()函數(shù)將會返回包括expression在內(nèi)

35、的所有參數(shù)中的第一個非空表達(dá)式，如果expression不為空值則返回expression；否則判斷value1是否是空值，如果value1不為空值則返回value1；否則判斷value2是否是空值，如果不為空值則返回value3，以此類推。例子：select FName,FBirthDay,FRegDay,COALESCE(FBirthDay,FRegDay,'2008-08-08') AS ImportDayfrom T_Person注：如果出生日期不為空則將出生日期作為“重要日期”，如果出生日期為空則判斷注冊日期是否為空，如果注冊日期不為空則將注冊日期作為“重要日期”，如

36、果注冊日期也為空則將“2008年8月8日”做為重要日期。MSSQL中，COALESCE()函數(shù)的簡化版：ISNULL(expression,value),這個函數(shù)只接受兩個變量。例子：select FBirthDay,FRegDayISNULL(FBirthDay,FRegDay) AS ImportDayfrom T_Person ;如果出生日期不為空則將出生日期做為“重要日期”，NULLIF()函數(shù) 主流數(shù)據(jù)庫都支持，格式：NULLIF(expression1,exprssion2)如果兩個表達(dá)式不等價，則NULLIF返回第一個expression1的值，如果兩個表達(dá)式等價，則NULLIF

37、返回第一個expression1類型的空值CASE函數(shù) 有兩種用法：*第一種用法:CASE expressionWHEN value1 THEN returnvalue1WHEN value2 THEN returnvalue2WHEN value3 THEN returnvalue3ELSE defaultreturnvalueEND注：CASE函數(shù)對表達(dá)式expression進(jìn)行測試，如果expression等于value1則返回returnvalue1，如果expression等于value2則返回returnvalue2，以此類推，如果不符合所有的WHEN條件，則返回默認(rèn)值defaul

38、treturnvalue。例子：use test1select id as 編號,name as 姓名,remtent as 備注,(case name when '涂志勇' then '真正的中文名'when 'hh' then '錯誤的英文名字'when 'ttt' then '正確的英文名字'else '錯誤的名字'end) as 名字判斷from dbo.member*第二種用法CASE WHEN condition1 THEN returnvalue1WHEN condit

39、ion2 THEN returnvalue2WHEN condition3 THEN returnvalue3ELSE defaultreturnvalueEND其中condition1，condition2，condition3.為條件表達(dá)式，CASE函數(shù)對各個表達(dá)式從前向后進(jìn)行測試，如果條件condition1為真則返回returnvalue1，否則如果條件condition2為真則返回returnvalue2，否則如果條件condition3為真則返回returnvalue3，。以此類推，如果不符合所有的WHEN條件，則返回默認(rèn)值defaultreturnvalue。 這種用法中沒有限制只能對一個表達(dá)式進(jìn)行判斷，因此使用起來