數(shù)據(jù)庫訪問及優(yōu)化-基礎(chǔ)sqlserver

北京傳智播客教育北京傳智播客教育Part1:MSSQLServer講師：大沙漠北京傳智播客教育數(shù)據(jù)庫簡(jiǎn)介北京傳智播客教育什么是數(shù)據(jù)庫數(shù)據(jù)庫Database：存儲(chǔ)數(shù)據(jù)的倉(cāng)庫DB隨著電商的發(fā)展，倉(cāng)庫也越來越多的被大家所知道；關(guān)于倉(cāng)庫管理員的工作大家也有所了解自古至今，都有很多種倉(cāng)庫：糧倉(cāng)，彈藥庫等倉(cāng)庫中還會(huì)有倉(cāng)庫管理員(DBA)對(duì)貨物進(jìn)行管理從倉(cāng)庫中拿貨物需要憑證，取貨和進(jìn)貨的人(程序員)北京傳智播客教育我們?cè)趺创鎯?chǔ)數(shù)據(jù)發(fā)展歷程：結(jié)繩記事-》文件存儲(chǔ)-》計(jì)算機(jī)對(duì)于數(shù)據(jù)不僅需要存儲(chǔ)，更重要的是將數(shù)據(jù)進(jìn)行存儲(chǔ)以后怎么才能方便快捷的查詢、修改數(shù)據(jù)庫特點(diǎn)：海量存儲(chǔ)、查找速度快、并發(fā)性問題控制、安全性、數(shù)據(jù)完整性(保存在數(shù)據(jù)庫中的數(shù)據(jù)是正確的，真實(shí)的)高級(jí)應(yīng)用：BI，專家系統(tǒng)，人工智能北京傳智播客教育數(shù)據(jù)庫概述DBMS（DataBaseManagementSystem，數(shù)據(jù)庫管理系統(tǒng)）和數(shù)據(jù)庫。平時(shí)談到“數(shù)據(jù)庫”可能有兩種含義：MSSQLServer、Oracle等某種DBMS；存放一堆數(shù)據(jù)表的一個(gè)分類（Catalog）不同品牌的DBMS有自己的不同的特點(diǎn)：MYSQL（速度很快，適合對(duì)數(shù)據(jù)要求并不是十分嚴(yán)謹(jǐn)?shù)牡胤剑サ袅撕芏嘀行⌒推髽I(yè)中不常用的功能）、MSSQLServer（與.net結(jié)合很好,只能運(yùn)行在windows平臺(tái)下，大數(shù)據(jù)量、大交易量表現(xiàn)并不十分好）、DB2（大型）、Oracle（大型）、Access（文件）、SQLite（極其輕量級(jí)數(shù)據(jù)庫）、Sybase等。對(duì)于開發(fā)人員來講，大同小異SQL(語言)<>SQLServer<>MSSQLServer。最常見的錯(cuò)誤。除了Access、SQLServerCE、SQLite等文件型數(shù)據(jù)庫之外，大部分?jǐn)?shù)據(jù)庫都需要數(shù)據(jù)庫服務(wù)器才能運(yùn)行。學(xué)習(xí)、開發(fā)時(shí)是連接本機(jī)的數(shù)據(jù)庫，上線運(yùn)行時(shí)是數(shù)據(jù)庫運(yùn)行在單獨(dú)的服務(wù)器北京傳智播客教育關(guān)系型數(shù)據(jù)庫E-R當(dāng)前主流的數(shù)據(jù)庫，都是基于E-R模型的數(shù)據(jù)庫E-R模型指實(shí)體-關(guān)系模型典型的特點(diǎn)是：一個(gè)實(shí)體可以包含多個(gè)列，一行構(gòu)成一個(gè)實(shí)體；實(shí)體與實(shí)體之間可以有多種對(duì)應(yīng)關(guān)系這里的實(shí)例，與面向?qū)ο笾械念愊嗨?，只是這里只關(guān)注描述對(duì)象的數(shù)據(jù)（字段），而不關(guān)心對(duì)象的行為（函數(shù)）北京傳智播客教育數(shù)據(jù)庫中的概念數(shù)據(jù)庫DataBase，不同類的數(shù)據(jù)應(yīng)該放到不同的數(shù)據(jù)庫中便于對(duì)各個(gè)數(shù)據(jù)類別的進(jìn)行個(gè)性化管理避免命名沖突安全性更高Table（表）：關(guān)系數(shù)據(jù)庫中的[關(guān)系]指的就是表。不同的貨物要放到各自的貨物架，將這種區(qū)域叫做“表”（Table）。不同的表根據(jù)放的數(shù)據(jù)不同進(jìn)行空間的優(yōu)化，找起來也方便。列（Column）、字段（Field）、行(Row)元組用表格格式化數(shù)據(jù)：即便是引入了自動(dòng)識(shí)別設(shè)備也很容易識(shí)別。2003年5月入職，是產(chǎn)品開發(fā)部的，姓名馬小虎王二小，技術(shù)支持部，入職是2005年7月姓名馬小虎部門開發(fā)部入職時(shí)間2008.06.06姓名部門入職時(shí)間張三開發(fā)2002北京傳智播客教育數(shù)據(jù)庫設(shè)計(jì)：范式現(xiàn)階段，必須遵守滿足3NF1范式：列的原子性，即列不可再拆分2范式：表中不能描述多個(gè)信息，不能有數(shù)據(jù)冗余3范式：引用其它表的主鍵信息北京傳智播客教育約束-保證數(shù)據(jù)完整性北京傳智播客教育思考1.一張表中有很多條記錄，有沒有可能出現(xiàn)重復(fù)的記錄？2.什么叫做重復(fù)的記錄？3.我們可以允許表中有重復(fù)的記錄嗎？為什么？北京傳智播客教育主鍵（Primary

Key）工號(hào)姓名部門入職時(shí)間001風(fēng)姐員工培訓(xùn)部2010年7月5日002瘦瘦公關(guān)部2010年8月2日003憨憨開發(fā)部2009年3月5日主鍵就是數(shù)據(jù)行的唯一標(biāo)識(shí)。不會(huì)重復(fù)的列才能當(dāng)主鍵。一個(gè)表可以沒有主鍵，但是會(huì)非常難以處理，因此沒有特殊理由表都要設(shè)定主鍵主鍵有兩種選用策略：業(yè)務(wù)主鍵和邏輯主鍵。業(yè)務(wù)主鍵是使用有業(yè)務(wù)意義的字段做主鍵，比如身份證號(hào)、銀行賬號(hào)等；邏輯主鍵是使用沒有任何業(yè)務(wù)意義的字段做主鍵，完全給程序看的，業(yè)務(wù)人員不會(huì)看的數(shù)據(jù)。因?yàn)楹茈y保證業(yè)務(wù)主鍵不會(huì)重復(fù)（身份證號(hào)重復(fù)）、不會(huì)變化（帳號(hào)升位），因此推薦用邏輯主鍵。北京傳智播客教育外鍵（Foreign

Key）商品名價(jià)格廠家編號(hào)大大香瓜子5.00001大大開心果15.00001苦咖啡2002隨變3002冰工廠1002商品名價(jià)格生產(chǎn)廠家廠家地址廠家電話大大香瓜子5.00大大食品廠恰恰大街300號(hào)010-123456大大開心果15.00大大食品廠恰恰大街300號(hào)010-123456苦咖啡2伊利食品廠內(nèi)蒙古伊利路1號(hào)400400400隨變3伊利食品廠內(nèi)蒙古伊利路1號(hào)400400400冰工廠1伊利食品廠內(nèi)蒙古伊利路1號(hào)400400400編號(hào)名稱地址電話001大大食品廠恰恰大街300號(hào)010-123456002伊利食品廠內(nèi)蒙古伊利路1號(hào)400400400北京傳智播客教育數(shù)據(jù)庫約束是為了保證數(shù)據(jù)的完整性(正確性)而實(shí)現(xiàn)的一套機(jī)制非空約束notnull主鍵約束(PK)

primarykeyconstraint唯一且不為空唯一約束(UQ)uniqueconstraint唯一，允許為空，但只能出現(xiàn)一次默認(rèn)約束(DF)defaultconstraint默認(rèn)值檢查約束(CK)checkconstraint范圍以及格式限制外鍵約束(FK)foreignkeyconstraint表關(guān)系增加外鍵約束時(shí)，設(shè)置級(jí)聯(lián)更新、級(jí)聯(lián)刪除：[ONDELETE{NOACTION|CASCADE|SETNULL|SETDEFAULT}][ONUPDATE{NOACTION|CASCADE|SETNULL|SETDEFAULT}]北京傳智播客教育數(shù)據(jù)庫基本操作北京傳智播客教育SQLServer的管理需要安裝SQLServer2005或者SQLServer2008，若要使用SQLServer管理工具進(jìn)行開發(fā)還要安裝SQLServerManagementStudio，還可以使用VisualStudio進(jìn)行管理使用免費(fèi)的SQLServerExpress版本，Express版本的服務(wù)器名稱.\SQLEXPRESS，對(duì)于開發(fā)人員來講和其他版本沒有區(qū)別。(Express版本的數(shù)據(jù)庫功能有一些限制，比如：數(shù)據(jù)文件最大大小為4GB等等)SQLServer的兩種驗(yàn)證方式：用戶名驗(yàn)證和Windows驗(yàn)證，開發(fā)時(shí)用Windows驗(yàn)證就行；可以自已設(shè)置登錄驗(yàn)證方式開發(fā)人員關(guān)注點(diǎn)在開發(fā)上，而不是配置、備份等之上，那是DBA做的事情遠(yuǎn)程連接，需要啟用TCP/IP，端口默認(rèn)是1433北京傳智播客教育創(chuàng)建數(shù)據(jù)庫界面操作：創(chuàng)建數(shù)據(jù)庫，創(chuàng)建表，設(shè)置主鍵，數(shù)據(jù)庫的分離和附加MSSQLServer的每個(gè)數(shù)據(jù)庫包含：1個(gè)主數(shù)據(jù)文件(.mdf)必須1個(gè)事務(wù)日志文件（.ldf）必須可以包含：任意多個(gè)次要數(shù)據(jù)文件(.ndf)多個(gè)事務(wù)日志文件文件組：可將多個(gè)數(shù)據(jù)文件邏輯的分到一組，方便日后管理維護(hù)（備份、將表建在指定的文件組上等等。）練習(xí)：創(chuàng)建<部門表>：部門Id,部門名稱<員工表>：?jiǎn)T工Id,身份證號(hào)，姓名，性別，入職日期，年齡，地址，電話，所屬部門、Email北京傳智播客教育數(shù)據(jù)類型分類備注和說明類型說明二進(jìn)制數(shù)據(jù)類型存儲(chǔ)非子符和文本的數(shù)據(jù)image（varbinary(max)）可用來存儲(chǔ)圖像文本數(shù)據(jù)類型字符數(shù)據(jù)包括任意字母、符號(hào)或數(shù)字字符的組合Char,8000固定長(zhǎng)度的非Unicode字符數(shù)據(jù)。固定長(zhǎng)度的字符串相對(duì)于可變長(zhǎng)度的字符串來說效率要高一些，在數(shù)據(jù)長(zhǎng)度固定的情況下優(yōu)先選用固定長(zhǎng)度，省去了計(jì)算長(zhǎng)度的過程，提高效率Varchar,8000可變長(zhǎng)度非Unicode數(shù)據(jù)Nchar,4000固定長(zhǎng)度的Unicode數(shù)據(jù)Nvarchar,4000可變長(zhǎng)度Unicode數(shù)據(jù)Textvarchar(max)存儲(chǔ)長(zhǎng)文本信息(指針,2G)varchar(max)，大字符串類型可以保存非常多的字符，但是對(duì)于這種類型的數(shù)據(jù)DBMS經(jīng)常將它們保存到單獨(dú)的空間中，這就導(dǎo)致了數(shù)據(jù)的保存和加載速度比較慢，因此除非必要，否則不要使用。Ntextnvarchar(max)Nvarchar(max)代替日期和時(shí)間日期和時(shí)間在單引號(hào)內(nèi)輸入Datetime日期和時(shí)間數(shù)字?jǐn)?shù)據(jù)該數(shù)據(jù)僅包含數(shù)字，包括正數(shù)、負(fù)數(shù)以及分?jǐn)?shù)intsmallint整數(shù)floatreal近似值數(shù)字貨幣數(shù)據(jù)類型用于十進(jìn)制貨幣值,money和smallmoney數(shù)據(jù)類型精確到它們所代表的貨幣單位的萬分之一。MoneyBit數(shù)據(jù)類型表示是/否的數(shù)據(jù)Bit存儲(chǔ)布爾數(shù)據(jù)類型北京傳智播客教育區(qū)分char與ncharvarchar(10)與nvarchar(10)varchar(max)與nvarchar(max)text與ntext帶var和不帶var的區(qū)別：varchar是可變長(zhǎng)度的，char是不可變長(zhǎng)度的根據(jù)內(nèi)容定長(zhǎng)度；固定內(nèi)容長(zhǎng)度，如果長(zhǎng)度不夠則在末尾加空格建議：定長(zhǎng)度時(shí)使用char，不定長(zhǎng)度再用varchar，因?yàn)椴欢ㄩL(zhǎng)度在取數(shù)據(jù)時(shí)需要進(jìn)行計(jì)算長(zhǎng)度帶n與不帶n：帶n一個(gè)字符占2個(gè)字節(jié)，不帶n一個(gè)字符占1個(gè)字節(jié)，這取決于使用哪種排序規(guī)則，如果使用簡(jiǎn)體中文，則兩種類型沒有區(qū)別，對(duì)于pubs庫是unicode的，則char就不能正常顯示中文了，只有nchar才能正常顯示中文如果存儲(chǔ)內(nèi)容非常大，則使用text或ntext，不過已經(jīng)過時(shí)了，在最新版本中推薦使用varchar(max)或nvarchar(max)北京傳智播客教育練習(xí)創(chuàng)建一個(gè)School數(shù)據(jù)庫。創(chuàng)建一個(gè)班級(jí)表：TblClass：tClassId（班級(jí)編號(hào)，自動(dòng)編號(hào)，主鍵）、tClassName（班級(jí)名稱）、tClassDesc（班級(jí)簡(jiǎn)介）。創(chuàng)建一個(gè)學(xué)生信息表：TblStudent：tSId（學(xué)生編號(hào)，自動(dòng)編號(hào)，主鍵）、tSName（學(xué)生姓名）、tSGender（性別）、tSAddress（家庭地址）、tSPhone（電話）、tSAge（年齡）、tSBirthday（出生日期）、tSCardId（身份證號(hào)）、tClassId（班級(jí)Id）分離數(shù)據(jù)庫在需要分離的數(shù)據(jù)庫上點(diǎn)右鍵-任務(wù)-分離附加數(shù)據(jù)庫(在其他計(jì)算機(jī)上，親自測(cè)試！注意權(quán)限問題)在數(shù)據(jù)庫節(jié)點(diǎn)上點(diǎn)右鍵-附加打開數(shù)據(jù)之前，要打開數(shù)據(jù)庫服務(wù)北京傳智播客教育SQL語句入門(腳本、命令)北京傳智播客教育SQL

全名是結(jié)構(gòu)化查詢語言（StructuredQueryLanguage），是關(guān)系數(shù)據(jù)庫管理系統(tǒng)的標(biāo)準(zhǔn)語言SQL語句是和DBMS“交談”專用的語句，不同DBMS都認(rèn)SQL語法。=================================================SQL語句中字符串用單引號(hào)、單等號(hào)。select*、SeLeCT*：SQL語句是大小寫不敏感的，不敏感指的是SQL關(guān)鍵字，字符串值還是大小寫敏感的（大小寫是否敏感取決于具體的數(shù)據(jù)庫排序規(guī)則設(shè)置）--在數(shù)據(jù)庫中兩個(gè)'表示一個(gè)‘【''轉(zhuǎn)義‘】==============================================建庫、刪除數(shù)據(jù)庫、創(chuàng)建表、刪除表不僅可以手工完成，還可以執(zhí)行SQL語句完成，在自動(dòng)化部署、數(shù)據(jù)導(dǎo)入中用的很多簡(jiǎn)單的Insert語句。（*）SQL主要分DDL（數(shù)據(jù)定義語言,建表、建庫等語句。）、DML（數(shù)據(jù)操作語言）和DCL（數(shù)據(jù)庫控制語言）。CreateTable、DropTable、AlterTable等屬于DDL，Select、Insert、Update、Delete等屬于DML，GRANT授權(quán)、REVOKE取消授權(quán)屬于DCL北京傳智播客教育使用sql語句創(chuàng)建數(shù)據(jù)庫和表使用SQL語句創(chuàng)建School數(shù)據(jù)庫、TblClass表、TblStudent表創(chuàng)建班級(jí)表TblClasstCId、tCName、tCDescription創(chuàng)建學(xué)生表TblStudenttSId、tSName、tSGender、tSAddress、tSPhone、tSAge、tSBirthday、tSCardId、tSClassId創(chuàng)建學(xué)生成績(jī)表TblScoretScoreId（成績(jī)id,主鍵,自動(dòng)編號(hào)）、tSId（學(xué)生編號(hào)）、tEnglish（英語成績(jī)）、tMath（數(shù)學(xué)成績(jī)）創(chuàng)建老師表TblTeachertTId、tTName、tTGender、tTAge、tTSalary、tTBirthdayGo：將T-SQL語句分批發(fā)送到數(shù)據(jù)庫實(shí)例執(zhí)行北京傳智播客教育介紹生成腳本工具數(shù)據(jù)庫→右鍵→任務(wù)→生成腳本可選擇生成什么樣的腳本選擇生成的數(shù)據(jù)庫版本是否包含某些腳本等是否生成帶數(shù)據(jù)的腳本（2005、2008都有該功能,express沒有）北京傳智播客教育簡(jiǎn)單數(shù)據(jù)插入insert向表中插入一行（該行的每一列都有數(shù)據(jù)）insertinto表(列名，列名)values(值1，值2)insert語句可以省略表名后的列名，但是不推薦。Insertinto表values(值1，值2)插入數(shù)據(jù)時(shí)，只向某些列插入數(shù)據(jù)：如果插入的行中有些字段的值不確定，那么Insert的時(shí)候不指定那些列即可。Insertinto表(列1)values(值1)自動(dòng)編號(hào)列不需要手動(dòng)插入。【SETIDENTITY_INSERT表名ON】注意：主鍵不能有重復(fù)值。插入數(shù)據(jù)時(shí)的單引號(hào)問題。Insertinto表（列）select列1，列2unionInsertinto表（列）select列1，列2from表Select列into新表名from舊表N前綴：N’字符串’，在服務(wù)器上執(zhí)行的代碼中（例如在存儲(chǔ)過程和觸發(fā)器中）顯示的Unicode字符串常量必須以大寫字母N為前綴。即使所引用的列已定義為Unicode類型，也應(yīng)如此。如果不使用N前綴，字符串將轉(zhuǎn)換為數(shù)據(jù)庫的默認(rèn)代碼頁。這可能導(dǎo)致不識(shí)別某些字符。在Northwind數(shù)據(jù)庫下測(cè)試print‘中文中文’。通過Ado操作時(shí)如果遇到sql字符串也需要加前綴N。如果使用帶參數(shù)sql語句則不需要加N.北京傳智播客教育簡(jiǎn)單數(shù)據(jù)更新(數(shù)據(jù)修改)更新一個(gè)列：updateStudentsetsSex=‘男’更新多個(gè)列：updateStudentsetsSex='女',sAge=18,sBirthday='1989-8-8'更新一部分?jǐn)?shù)據(jù)：updateStudentsetsClassId=4wheresClassId=1，用where語句表示只更新Name是’tom’的行，注意SQL中等于判斷用單個(gè)=，而不是==。Where中還可以使用復(fù)雜的邏輯判斷updateStudentsetsAge=30wheresName='華佗'orsAge<25，or相當(dāng)于C#中的||（或者）所有學(xué)生的年齡加1updateStudentsetsAge=sAge+1updateStudentsetsClassId=6where(sAge>20andsAge<30)or(sAge=50)Where中可以使用的其他邏輯運(yùn)算符：(||)or、(&&)and、(!)not、<、>、>=、<=、<>（或!=）等北京傳智播客教育簡(jiǎn)單數(shù)據(jù)刪除刪除表中全部數(shù)據(jù)：DELETEFROMStudent。Delete只是刪除數(shù)據(jù)，表還在，和DropTable不同。Delete也可以帶where子句來刪除一部分?jǐn)?shù)據(jù)：DELETEFROMStudentWHEREsAge>20===========================================truncatetablestudent的作用與deletefromstudent一樣，都是刪除student表中的全部數(shù)據(jù)，區(qū)別在于：1.truncate語句非常高效。由于truncate操作采用按最小方式來記錄日志，所以效率非常高。對(duì)于數(shù)百萬條數(shù)據(jù)使用truncate刪除只要幾秒鐘，而使用delete則可能耗費(fèi)幾小時(shí)。2.truncate語句會(huì)把表中的自動(dòng)編號(hào)重置為默認(rèn)值。3.truncate語句不觸發(fā)delete觸發(fā)器。北京傳智播客教育練習(xí)：插入3條老師信息和5條成績(jī)信息給studentId是1的英語成績(jī)加10分考試題偏難，所有人的成績(jī)加5分所有女學(xué)生的年齡減1歲刪除工資大于2000的老師刪除所有老師刪除數(shù)據(jù)時(shí)候把自增長(zhǎng)列的值還原成種子作業(yè)：新建員工表，插入2條員工信息（注意：bit類型，在寫代碼中用1或0來表示，不要用’false’,會(huì)進(jìn)行類型轉(zhuǎn)換的），列如下：EmpId,EmpIdCardNo,EmpName,EmpGender,EmpJoinDate,EmpAge,EmpAddress,EmpPhone,EmpEmail,DeptId北京傳智播客教育數(shù)據(jù)檢索（查詢）北京傳智播客教育執(zhí)行備注中的代碼創(chuàng)建測(cè)試數(shù)據(jù)表。簡(jiǎn)單的數(shù)據(jù)檢索：SELECT*FROMStudent只檢索需要的列：SELECTsNameFROMStudent、ameFROMStudentWHEREsSex=‘女’。還可以檢索不與任何表關(guān)聯(lián)的數(shù)據(jù)：select1+1;selectgetdate();可以為列起別名北京傳智播客教育Top、DistinctTop獲取前幾條數(shù)據(jù)，top一般都與orderby連用獲得年紀(jì)最小的5個(gè)學(xué)生獲得年紀(jì)最大的10%的學(xué)生(percent)Distinct去除重復(fù)數(shù)據(jù)selectdistinctsNamefromstudentselectdistinctsName,sAgefromstudentDISTINCT是對(duì)查詢出的整個(gè)結(jié)果集進(jìn)行數(shù)據(jù)重復(fù)處理的，而不是針對(duì)某一個(gè)列。1.建表，帶重復(fù)數(shù)據(jù)。演示distinct2.通過altertable增加identity列，再演示distinct。altertableTT2addautoIdintidentity(1,1)3.演示查詢除主鍵外的其他列重復(fù)數(shù)據(jù)。北京傳智播客教育帶條件的查詢Select…from…where…查詢沒有及格的學(xué)生的學(xué)號(hào)查詢年齡在20-30歲之間的男學(xué)生Between…and…在之間查詢年齡在20-30歲之間的男學(xué)生查詢math成績(jī)?cè)?0-90分之間的所有學(xué)生建議：使用between…and…。（閉區(qū)間）查詢班級(jí)id為1，2，3的所有學(xué)生selectsName,sAgefromstudentwheresClassId=1orsClassId=2orsClassId=3selectsName,sAgefromstudentwheresClassIdin(1,2,3)北京傳智播客教育帶條件的查詢-模糊查詢都是針對(duì)字符串操作的問題：查詢所有姓張的同學(xué)_、%、[]、^^只有MSSQLServer支持，其他DBMS用notlike通配符%多字符匹配的通配符，它匹配任意次數(shù)（零或多個(gè)）出現(xiàn)的任意字符通配符_單字符匹配，它匹配單個(gè)出現(xiàn)的字符[]只匹配一個(gè)字符并且這個(gè)字符必須是[]范圍內(nèi)的[0-9][a-z]not與like一起使用：notlike….要通配_、%、[、^這些字符怎么辦？[_]、[%]、[[]、^(不需要放到中括號(hào)里，因?yàn)閊只有放到中括號(hào)中才認(rèn)為是通配符)北京傳智播客教育空值處理數(shù)據(jù)庫中，一個(gè)列如果沒有指定值，那么值就為null，數(shù)據(jù)庫中的null表示“不知道”，而不是表示沒有。因此selectnull+1結(jié)果是null，因?yàn)椤安恢馈奔?的結(jié)果還是“不知道”。select*fromscorewhereenglish=null；select*fromscorewhereenglish!=null；都沒有任何返回結(jié)果，因?yàn)閿?shù)據(jù)庫也“不知道”。SQL中使用isnull、isnotnull來進(jìn)行空值判斷：select*fromscorewhereenglishisnull

；select*fromscorewhereenglishisnotnull

；函數(shù)ISNULL(check_expression,replacement_value)北京傳智播客教育數(shù)據(jù)排序ORDERBY子句位于SELECT語句的末尾，它允許指定按照一個(gè)列或者多個(gè)列進(jìn)行排序，還可以指定排序方式是升序（從小到大排列，ASC）還是降序（從大到小排列，DESC）。按照年齡升序排序所有學(xué)生信息的列表：SELECT*FROMStudentORDERBYsAgeASC按照英語成績(jī)從大到小排序，如果英語成績(jī)相同則按照數(shù)學(xué)成績(jī)從大到小排序：SELECT*FROMScoreORDERBYenglishDESC,mathDESCORDERBY子句要放到WHERE子句之后：SELECT*FROMScorewhereenglish>=60andmath>=60ORDERBYenglishDESC,mathDESCOrderby語句一般要放到所有語句的后面，就是先讓其他語句進(jìn)行篩選，全部篩選完成后，最后排序一下。（表中數(shù)據(jù)是集合，集合是沒有順序的。Orderby返回的數(shù)據(jù)是有順序的，故此我們把orderby以后返回的數(shù)據(jù)集合叫“游標(biāo)”。）北京傳智播客教育聚合函數(shù)SQL聚合函數(shù)：MAX（最大值）、MIN（最小值）、AVG（平均值）、SUM（和）、COUNT（數(shù)量:記錄的條數(shù)。）聚合函數(shù)對(duì)null值不計(jì)算。如果一行的數(shù)據(jù)都是null，count(*)包含對(duì)空值行、重復(fù)行的統(tǒng)計(jì)。平均成績(jī)selectavg(english)fromscore男學(xué)生出生日期的最大值和最小值：selectmax(sBirthday),min(sBirthday)fromstudentwheresSex='男'北京傳智播客教育數(shù)據(jù)分組在使用select查詢的時(shí)候，有時(shí)需要對(duì)數(shù)據(jù)進(jìn)行分組匯總（即：將現(xiàn)有的數(shù)據(jù)按照某列來匯總統(tǒng)計(jì)），這時(shí)就需要用到groupby語句。select

語句中可以使用group

by

子句將行劃分成較小的組，然后，使用聚組函數(shù)返回每一個(gè)組的匯總信息。//分組一般都和聚合函數(shù)連用。1.請(qǐng)從學(xué)生表中查詢出每個(gè)班的班級(jí)Id和班級(jí)人數(shù)：（見備注1）2.請(qǐng)從學(xué)生表中查詢出每個(gè)班的班級(jí)Id和班級(jí)中男同學(xué)的人數(shù)：（見備注2）GROUPBY子句必須放到WHERE語句的之后,GroupBy與OrderBy都是對(duì)篩選后的數(shù)據(jù)進(jìn)行處理，而Where是用來篩選數(shù)據(jù)的。沒有出現(xiàn)在GROUPBY子句中的列是不能放到SELECT語句后的列名列表中的（聚合函數(shù)中除外）錯(cuò)誤：selectsClassId,count(sName),sAgefromstudentgroupbysClassId正確：selectsClassId,count(sName),avg(sAge)fromstudentgroupbysClassId北京傳智播客教育Having語句(對(duì)組的篩選，哪些組顯示哪些組不顯示)對(duì)表中的數(shù)據(jù)分組后，會(huì)得到一個(gè)分組后的結(jié)果集，如何對(duì)該結(jié)果集在進(jìn)行篩選？→ having查詢班級(jí)人數(shù)超過三個(gè)人的班級(jí)。（見備注1）注意Having中不能使用未參與分組的列，Having不能替代where。作用不一樣，Having是對(duì)組進(jìn)行過濾。Having是GroupBy的條件對(duì)分組后的數(shù)據(jù)進(jìn)行篩選（與Where類似，都是篩選，只不過having是用來篩選分組后的組的。）在Where中不能使用聚合函數(shù)，必須使用Having，Having要位于GroupBy之后。Having的使用幾乎是與where一樣的，也可以用in。Havingcount(*)in(5,8,10)北京傳智播客教育SQL語句的執(zhí)行順序5>…Select5-1>選擇列,5-2>distinct,7>top(應(yīng)用top選項(xiàng)最后計(jì)算)1>…From

表Join

表on2>…Where條件3>…Groupby

列4>…Having

篩選條件6>…Orderby

列北京傳智播客教育Groupby練習(xí)從MyOrders表中查詢：1.熱銷售商品排名表，【即按照每種商品的總銷售數(shù)量排序】。2.請(qǐng)統(tǒng)計(jì)銷售總價(jià)超過3000元的商品名稱和銷售總價(jià),并按銷售總價(jià)降序排序。3.統(tǒng)計(jì)各個(gè)客戶對(duì)"可口可樂"的喜愛度(既統(tǒng)計(jì)每個(gè)購(gòu)買人對(duì)“可口可樂”的購(gòu)買量)北京傳智播客教育常用函數(shù)北京傳智播客教育類型轉(zhuǎn)換函數(shù)CAST(expressionASdata_type)CONVERT(data_type,expression,[style])Select‘您的班級(jí)編號(hào)’+1錯(cuò)誤：這里+是數(shù)學(xué)運(yùn)算符SELECTFIdNumber,CAST(RIGHT(sNo,3)AS

INTEGER)as

后三位的整數(shù)形式,CAST(RIGHT(sNo,3)AS

INTEGER)+1as

后三位加1,CONVERT(INTEGER,RIGHT(sNo,3))/2as

后三位除以2FROMstudent對(duì)編號(hào)排序，但編號(hào)是字符串類型。1、2、11、3、21、36…對(duì)日期的轉(zhuǎn)換。轉(zhuǎn)換成各種國(guó)家格式的日期。select

convert(varchar(20),getdate(),104)Style的格式，查sql幫助。（輸入convert函數(shù)查詢）將日期轉(zhuǎn)換為指定格式的字符串。日期→字符串北京傳智播客教育字符串函數(shù)（*）LEN()：計(jì)算字符串長(zhǎng)度（字符的個(gè)數(shù)。）datalength();//計(jì)算字符串所占用的字節(jié)數(shù)，不屬于字符串函數(shù)。測(cè)試varchar變量與nvarchar變量存儲(chǔ)字符串a(chǎn)的區(qū)別。見備注1.LOWER()、UPPER()：轉(zhuǎn)小寫、大寫LTRIM()：字符串左側(cè)的空格去掉RTRIM()：字符串右側(cè)的空格去掉LTRIM(RTRIM('bb'))LEFT()、RIGHT()截取取字符串SELECTLEFT('abcdefg',2)SUBSTRING(string,start_position,length)，索引從1開始。參數(shù)string為主字符串，start_position為子字符串在主字符串中的起始位置，length為子字符串的最大長(zhǎng)度。SELECTSUBSTRING('abcdef111',2,3)嘗試使用SQLServer的幫助。北京傳智播客教育日期函數(shù)(在幫助中輸入“日期函數(shù)”)GETDATE()

：取得當(dāng)前日期時(shí)間DATEADD(datepart,number,date)，計(jì)算增加以后的日期。參數(shù)date為待計(jì)算的日期；參數(shù)number為增量；參數(shù)datepart為計(jì)量單位，可選值見備注。DATEADD(DAY,3,date)為計(jì)算日期date的3天后的日期，而DATEADD(MONTH,-8,date)為計(jì)算日期date的8個(gè)月之前的日期。（入職一年以上的員工發(fā)1000$）,入職日期+1年<當(dāng)前時(shí)間Sql2005中只有DateTime類型，2008中有date、datetime、datetime2等類型。DATEDIFF(datepart,startdate,enddate)：計(jì)算兩個(gè)日期之間的差額。datepart為計(jì)量單位，可取值參考DateAdd。統(tǒng)計(jì)不同入學(xué)年數(shù)的學(xué)生個(gè)數(shù)：selectDateDiff(year,sInDate,getdate()),count(*)fromstudentGroupbyDateDiff(year,sInDate,getdate())DATEPART(datepart,date)：返回一個(gè)日期的特定部分Month()、year()、day()來代替。統(tǒng)計(jì)學(xué)生的生日年份個(gè)數(shù)：selectDatePart(year,sBirthday),count(*)fromstudentgroupbyDatePart(year,sBirthday)1990年出生的人的個(gè)數(shù)？北京傳智播客教育練習(xí)創(chuàng)建一張表，記錄電話呼叫員的工作流水，記錄呼叫員編號(hào)、對(duì)方號(hào)碼、通話開始時(shí)間、通話結(jié)束時(shí)間。建表、插數(shù)據(jù)等最后都自己寫SQL語句。要求：輸出所有數(shù)據(jù)中通話時(shí)間最長(zhǎng)的5條記錄。orderbydatediff輸出所有數(shù)據(jù)中撥打長(zhǎng)途號(hào)碼（對(duì)方號(hào)碼以0開頭）的總時(shí)長(zhǎng)。like、sum輸出本月通話總時(shí)長(zhǎng)最多的前三個(gè)呼叫員的編號(hào)。輸出本月?lián)艽螂娫挻螖?shù)最多的前三個(gè)呼叫員的編號(hào).groupby,count(*)每個(gè)月打電話時(shí)間最長(zhǎng)的前三個(gè)呼叫員（**）北京傳智播客教育聯(lián)合查詢北京傳智播客教育聯(lián)合結(jié)果集union(集合運(yùn)算符)集合運(yùn)算符是對(duì)兩個(gè)集合操作的，兩個(gè)集合必須具有相同的列數(shù)，列具有相同的數(shù)據(jù)類型（至少能隱式轉(zhuǎn)換的），最終輸出的集合的列名由第一個(gè)集合的列名來確定。（可以用來連接多個(gè)結(jié)果）聯(lián)合(union)與連接(join)不一樣簡(jiǎn)單的結(jié)果集聯(lián)合(老師、學(xué)生)：selecttName,tSexfromteacherunionselectsName,sSexfromstudent基本的原則：每個(gè)結(jié)果集必須有相同的列數(shù)；每個(gè)結(jié)果集的列必須類型相容。selecttName,tSex,-1fromteacherunionselectsName,sSex,sClassIdfromstudent聯(lián)合：將多個(gè)結(jié)果集合并成一個(gè)結(jié)果集。union(去除重復(fù)，相當(dāng)于默認(rèn)應(yīng)用了distinct)、unionall（保留所有結(jié)果，不去除重復(fù)）常見應(yīng)用：底部匯總。使用Unionall北京傳智播客教育UnionallselecttName,tSexfromteacherunionselectsName,sSexfromstudentUNION合并兩個(gè)查詢結(jié)果集，并且將其中完全重復(fù)的數(shù)據(jù)行合并為一條selecttName,tSexfromteacherunionallselectsName,sSexfromstudentUnion因?yàn)橐M(jìn)行重復(fù)值掃描，所以效率低，因此如果不是確定要合并重復(fù)行，那么就用UNIONALL北京傳智播客教育案例1要求在一個(gè)表格中查詢出學(xué)生的英語最高成績(jī)、最低成績(jī)、平均成績(jī)查詢結(jié)果為3行：select'english最高成績(jī)',max(english)fromscoreunionallselect'english最低成績(jī)',min(english)fr