數(shù)據(jù)庫原理與應(yīng)用

1、數(shù)據(jù)庫原理與應(yīng)用數(shù)據(jù)庫原理與應(yīng)用06 SQL語言 鄭捷lSQL概述l數(shù)據(jù)定義l數(shù)據(jù)查詢l數(shù)據(jù)更新l視圖SQL語言語言數(shù)據(jù)庫原理與應(yīng)用2l綜合統(tǒng)一l集數(shù)據(jù)定義語言、數(shù)據(jù)操縱語言、數(shù)據(jù)控制語言功能、風(fēng)格統(tǒng)一l高度非過程化l聲明式地提出做什么，無須指明怎么做l 面向集合的操作方式l以同一種語法結(jié)構(gòu)提供多種使用方式lSQL既是獨(dú)立的語言，又是嵌入式語言l語言簡潔、易學(xué)易用SQL語言特點(diǎn)語言特點(diǎn)數(shù)據(jù)庫原理與應(yīng)用3各物體之間的關(guān)系各物體之間的關(guān)系數(shù)據(jù)庫原理與應(yīng)用4主機(jī)（硬件、操作系統(tǒng)平臺(tái)）數(shù)據(jù)庫服務(wù)器實(shí)例其他實(shí)例數(shù)據(jù)庫其他數(shù)據(jù)庫表視圖程序l創(chuàng)建新數(shù)據(jù)庫時(shí)，需要確定的參數(shù)有：l數(shù)據(jù)庫名稱：必須的！而且不能和

2、當(dāng)前服務(wù)器實(shí)例中其他數(shù)據(jù)庫同名l主數(shù)據(jù)文件以及細(xì)節(jié)：可以不指定，不指定時(shí)按照默認(rèn)位置、名稱等進(jìn)行創(chuàng)建l日志文件以及細(xì)節(jié)：可以不指定，基本同上l次數(shù)據(jù)文件：一個(gè)數(shù)據(jù)庫可以將數(shù)據(jù)存放在不同文件中，如果不指定次數(shù)據(jù)文件，系統(tǒng)不會(huì)自動(dòng)建立創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫原理與應(yīng)用5lCREATE DATABASE ON PRIMARY LOG ON l:= ( NAME=邏輯文件名, FILENAME=操作系統(tǒng)下的邏輯和文件名 ,SIZE= 文件初始大小 ,MAXSIZE=最大文件大小|UMLIMITED ,FILEGROWTH =遞增值)創(chuàng)建數(shù)據(jù)庫創(chuàng)建數(shù)據(jù)庫數(shù)據(jù)庫原理與應(yīng)用6數(shù)據(jù)庫管理工具與數(shù)據(jù)庫數(shù)據(jù)庫管

3、理工具與數(shù)據(jù)庫數(shù)據(jù)庫原理與應(yīng)用7l基本表對(duì)象是數(shù)據(jù)庫的核心對(duì)象l是實(shí)際保存數(shù)據(jù)的對(duì)象，基本上所有對(duì)象都與其有關(guān)l表的定義，主要是針對(duì)其結(jié)構(gòu)，也就是列（又稱為屬性、字段等）進(jìn)行定義l附加的，還包括對(duì)約束的定義基本表的定義基本表的定義數(shù)據(jù)庫原理與應(yīng)用8lCREATE TABLE （ 列級(jí)約束 , 列級(jí)約束 , ）l如果完整性約束條件涉及到表的多個(gè)屬性列時(shí)，則必須定義在表級(jí)，否則可以定義在列級(jí)也可以定義在表級(jí)表的創(chuàng)建表的創(chuàng)建數(shù)據(jù)庫原理與應(yīng)用9l數(shù)值類型（精度、范圍、表示法）l整數(shù)：TinyInt、SmallInt、Int、BigIntl浮點(diǎn)數(shù)：Real、Floatl精確數(shù)：Decimal、Numer

4、icl貨幣：SmallMoney、Moneyl字符串類型（長短、國際化、可變長）l常規(guī)字符串：Char、NChar、VarChar、NVarCharl長字符串：Text、NTextl二進(jìn)制類型（長短、可變長）l二進(jìn)制類型：Binary、VarBinary、Imagel時(shí)間日期類型（精度、范圍）l時(shí)間日期：SmallDateTime、DateTimel其他類型l位：Bitl時(shí)間戳：TimeStamp (RowVersion)l世界唯一編號(hào)：UniqueIdentifier (GUID)SQL Server中的數(shù)據(jù)類型中的數(shù)據(jù)類型數(shù)據(jù)庫原理與應(yīng)用10lCREATE TABLE Student(St

5、uIDChar(12),StuNameNVarChar(5),StuSexNchar(1),StuAgeTinyInt,CitizenIDChar(18)創(chuàng)建表（列）創(chuàng)建表（列）數(shù)據(jù)庫原理與應(yīng)用11l盡可能用英文或者通用的英文縮寫l不建議用拼音，禁止用拼音聲母縮寫！l可以用漢字，但是不建議使用l選擇的單詞必須如實(shí)其含義，不同含義的應(yīng)選擇不同單詞，以不造成歧義為準(zhǔn)l書寫注意要有縮進(jìn)l通過大小寫或者下劃線的形式分割單詞l保留空格、空行等使代碼便于閱讀，適當(dāng)進(jìn)行對(duì)齊編程規(guī)范：命名規(guī)則、書寫規(guī)則編程規(guī)范：命名規(guī)則、書寫規(guī)則數(shù)據(jù)庫原理與應(yīng)用12lPRIMARY KEY：主鍵約束，等價(jià)非空、唯一lPK_表

6、名lFOREIGN KEY：外鍵約束lFK_表名_被參照表名lUNIQUE：唯一值約束（允許空值）lUQ_表名_字段名lCHECK (邏輯表達(dá)式)：檢查約束lCK_表名_字段名lDEFAULT：默認(rèn)值約束l DE_表名_字段名lNULL/NOT NULL：空值/非空值約束lNN_表名_字段名，往往不顯式命名約束約束數(shù)據(jù)庫原理與應(yīng)用13lSQL概述l數(shù)據(jù)定義l數(shù)據(jù)查詢l數(shù)據(jù)更新l視圖SQL語言語言數(shù)據(jù)庫原理與應(yīng)用14lCREATE TABLE Student(StuIDChar(12)NOT NULLCONSTRAINT PK_StudentPRIMARY KEY,StuNameNVarChar

7、(5) NOT NULL,StuSexNchar(1)NOT NULLCONSTRAINT CK_Student_StuSexCHECK (StuSex = 男 OR StuSex = 女),StuAgeTinyIntNULLCONSTRAINT CK_Student_StuAgeCHECK (StuAge BETWEEN 16 AND 40),CitizenIDChar(18)NULLCONSTRAINT UQ_Student_CitizenIDUNIQUE)帶約束的表創(chuàng)建帶約束的表創(chuàng)建數(shù)據(jù)庫原理與應(yīng)用15lCREATE TABLE Student(StuIDChar(12)NOT NULL

8、,StuNameNVarChar(5) NOT NULL,StuSexNchar(1)NOT NULL,StuAgeTinyIntNULL,CitizenIDChar(18)NULL,CONSTRAINT PK_StudentPRIMARY KEY (StuID),CONSTRAINT CK_Student_StuSexCHECK (StuSex = 男 OR StuSex = 女),CONSTRAINT CK_Student_StuAgeCHECK (StuAge BETWEEN 16 AND 40),CONSTRAINT UQ_Student_CitizenIDUNIQUE (Citize

9、nID)改寫為表級(jí)約束改寫為表級(jí)約束數(shù)據(jù)庫原理與應(yīng)用16l索引是對(duì)數(shù)據(jù)進(jìn)行檢索的一種輔助數(shù)據(jù)結(jié)構(gòu)l生活中的案例：新華字典的部首、拼音檢字法l索引占據(jù)一定的空間，并且依附于特定的表，索引的內(nèi)容與基本表（正文）保持絕對(duì)一致l索引用于加快檢索速度索引索引數(shù)據(jù)庫原理與應(yīng)用17l單一索引與復(fù)合索引l索引內(nèi)部包含的字段是單一的還是多個(gè)l唯一索引與不唯一索引l是否在索引中記錄重復(fù)值l聚集索引與非聚集索引l索引是否影響基本表記錄的存儲(chǔ)順序索引的分類索引的分類數(shù)據(jù)庫原理與應(yīng)用18lCREATE INDEX ON ( ASC|DESC, ASC|DESC)l索引名一般命名規(guī)則為：IX_表_字段索引的創(chuàng)建索引的創(chuàng)建

10、數(shù)據(jù)庫原理與應(yīng)用19l優(yōu)點(diǎn)（益處）l加快對(duì)信息的檢索l缺點(diǎn)（開銷、代價(jià)）l占用數(shù)據(jù)庫空間（磁盤空間）l加大信息維護(hù)開銷l索引建立的原則等暫不討論，在研究數(shù)據(jù)庫設(shè)計(jì)時(shí)進(jìn)行分析索引的優(yōu)缺點(diǎn)索引的優(yōu)缺點(diǎn)數(shù)據(jù)庫原理與應(yīng)用20lSQL概述l數(shù)據(jù)定義l數(shù)據(jù)查詢l數(shù)據(jù)更新l視圖SQL語言語言數(shù)據(jù)庫原理與應(yīng)用21l單表簡單查詢l分組查詢l連接查詢l嵌套查詢（子查詢）l集合查詢數(shù)據(jù)查詢數(shù)據(jù)查詢數(shù)據(jù)庫原理與應(yīng)用22l數(shù)據(jù)查詢語言（DQL）只有一個(gè)語句：SELECTl但是它是編程中最重要的語句，也是變化最復(fù)雜的語句l完整的SELECT語句由若干子句構(gòu)成，格式如下：lSELECT FROM WHERE GROUP B

11、Y HAVING ORDER BY 數(shù)據(jù)查詢語言數(shù)據(jù)查詢語言數(shù)據(jù)庫原理與應(yīng)用23l案例數(shù)據(jù)庫是一個(gè)工程管理的數(shù)據(jù)庫l涉及公司、工程人員、工程項(xiàng)目、人員參與項(xiàng)目等等案例數(shù)據(jù)庫案例數(shù)據(jù)庫數(shù)據(jù)庫原理與應(yīng)用24l簡單查詢就是從表中直接查詢出信息l可以用*指代全部字段，也可以自己指定是哪些字段，還可以對(duì)字段進(jìn)行計(jì)算，或者給字段添加別名l簡單查詢的語法如下：lSELECT FROM 字段：=| AS 簡單查詢簡單查詢數(shù)據(jù)庫原理與應(yīng)用25l1. 查出全部工程人員的全部信息l2. 查出全部工程人員的姓名和工資l3. 查出全部工程人員的工號(hào)和日平均工資（取整）l備注：按照規(guī)定，每月算22.5個(gè)工作日簡單查詢簡單

12、查詢數(shù)據(jù)庫原理與應(yīng)用26l選擇查詢是對(duì)查詢的記錄進(jìn)行篩選，只保留滿足條件的記錄l選擇查詢是在FROM子句后添加WHERE子句lSELECT FROM WHERE 選擇查詢選擇查詢數(shù)據(jù)庫原理與應(yīng)用27l4. 找出參加工作信息中在工地超過15個(gè)月的信息l5. 找出編號(hào)為“A01”的公司信息l6. 找出在重慶施工的工程項(xiàng)目名稱選擇查詢選擇查詢數(shù)據(jù)庫原理與應(yīng)用28l在條件之間可以用邏輯運(yùn)算連接多個(gè)條件l條件運(yùn)算優(yōu)先級(jí)是各種比較NOTAND ORl必要時(shí)添加括號(hào)調(diào)整運(yùn)算順序邏輯運(yùn)算邏輯運(yùn)算數(shù)據(jù)庫原理與應(yīng)用29l對(duì)于字符串而言，除了直接進(jìn)行比較，還可以進(jìn)行模式匹配，或者叫做模糊查找l模糊查找使用 LIKE

13、 謂詞lLIKE左側(cè)為要被檢索的字段，右側(cè)為匹配的模式，即模型字符串l模式(Pattern)中的特殊字符：l%代表任意個(gè)字符（0個(gè)、1個(gè)或多個(gè)）l_(下劃線)代表任意一個(gè)字符l ：指定范圍 (a-d) 或集合 (abcd) 中的任何單個(gè)字符l：不屬于指定范圍 (a-d) 或集合 (abcd) 的任何單個(gè)字符字符串匹配字符串匹配數(shù)據(jù)庫原理與應(yīng)用30l7. 查找姓名以“陳”開頭的工程師l8. 查找姓名以“言”結(jié)尾的工程師l9. 查找姓名以“張”開頭以“言”結(jié)尾的工程師l10. 查找名稱中帶有“海”字的公司l11. 查找編號(hào)第2-3位是01，最后一位是2，其余位數(shù)不限的工程師l12. 查找姓傅、趙的

14、工程師l13. 查找不姓傅、趙的工程師字符串匹配字符串匹配數(shù)據(jù)庫原理與應(yīng)用31lIN和BETWEEN都是用于范圍（區(qū)間）比較的謂詞l它們的左側(cè)都是用來比較的字段，右側(cè)是條件lIN，右側(cè)是用括號(hào)包圍的一個(gè)列表，左側(cè)字段的值等于列表中的任意一個(gè)，則比較成立lBETWEEN，右側(cè)是形如XX AND YY形式的區(qū)間，當(dāng)左側(cè)的值大于等于XX，小于等于YY，則比較成立lIN相當(dāng)于左側(cè)的字段逐個(gè)寫=，并用OR連接lBETWEEN相當(dāng)于 左側(cè)=XX AND 左側(cè)=YYIN和和BETWEEN數(shù)據(jù)庫原理與應(yīng)用32l14. 找出地點(diǎn)在上海或北京的公司l兩種方法l15. 找出年齡在25到30歲之間的工程師l兩種方法l

15、16. 找出14和15的相反結(jié)果lNOT的位置IN和和BETWEEN數(shù)據(jù)庫原理與應(yīng)用33l空值的運(yùn)算特殊性l空值與任何值進(jìn)行任何運(yùn)算結(jié)果均為空值l空值與任何值進(jìn)行比較，結(jié)果均為FALSE，哪怕是兩個(gè)空值進(jìn)行比較l因此，判定某個(gè)字段是否空值，要用專門的命令：lIS NULLlIS NOT NULL空值的判定空值的判定數(shù)據(jù)庫原理與應(yīng)用34l17. 分別找出年齡明確和不明確的工程師l18. 找出年齡在30歲以上，或者年齡未知的工程師空值的判定空值的判定數(shù)據(jù)庫原理與應(yīng)用35l之前的查詢結(jié)果，都是按照其自然順序排列的l自然順序是數(shù)據(jù)在數(shù)據(jù)庫中保存的物理順序，通常為其主鍵順序l我們可以在查詢中通過ORDER BY子句指定查詢結(jié)果的順序查詢結(jié)果的排序查詢結(jié)果的排序數(shù)據(jù)庫原理與應(yīng)用36l19. 列出全部工程師信息，按工資排序l20. 列出全部工程師的姓名和工資，按工資由高到低排序l2