S2-SQL-SQL基礎(chǔ)與表管理課件

1、HandsOnT-SQL基礎(chǔ)與表管理基礎(chǔ)與表管理HandsOn回顧回顧l什么是什么是Data、DB、DBMS、DBA、DBS？l為何需要數(shù)據(jù)庫(kù)？為何需要數(shù)據(jù)庫(kù)？l數(shù)據(jù)庫(kù)的發(fā)展過程？數(shù)據(jù)庫(kù)的發(fā)展過程？l關(guān)系模型與關(guān)系數(shù)據(jù)庫(kù)相關(guān)概念？關(guān)系模型與關(guān)系數(shù)據(jù)庫(kù)相關(guān)概念？l什么是什么是E-R圖？圖？l何為范式？何為范式？l系統(tǒng)數(shù)據(jù)庫(kù)有哪幾個(gè)？作用是？系統(tǒng)數(shù)據(jù)庫(kù)有哪幾個(gè)？作用是？l如何創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)？如何創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)？HandsOn本章目標(biāo)本章目標(biāo)lSQL與標(biāo)準(zhǔn)與標(biāo)準(zhǔn)SQLlT-SQL簡(jiǎn)介簡(jiǎn)介lT-SQL數(shù)據(jù)類型與運(yùn)算符數(shù)據(jù)類型與運(yùn)算符l管理數(shù)據(jù)庫(kù)表管理數(shù)據(jù)庫(kù)表l數(shù)據(jù)完整性與表約束數(shù)據(jù)完整性與表約束Han

2、dsOn標(biāo)準(zhǔn)標(biāo)準(zhǔn)SQLlSQL(Structured Query Language)結(jié)構(gòu)結(jié)構(gòu)化查詢語言，一般念做化查詢語言，一般念做”sequel”或或”S-Q-L”標(biāo)準(zhǔn)標(biāo)準(zhǔn)SQL的版本：的版本：SQL-86SQL-89SQL-92SQL:1999SQL:2003HandsOnSQL特點(diǎn)特點(diǎn)lSQL特點(diǎn)：特點(diǎn)：綜合統(tǒng)一綜合統(tǒng)一高度非過程化，既不要求用戶指定對(duì)數(shù)據(jù)的存高度非過程化，既不要求用戶指定對(duì)數(shù)據(jù)的存放方法，也不需要用戶了解其具體的數(shù)據(jù)存放放方法，也不需要用戶了解其具體的數(shù)據(jù)存放方式方式 面向集合面向集合既是自含式語言，又是嵌入式語言既是自含式語言，又是嵌入式語言語言簡(jiǎn)潔、易學(xué)易用語言簡(jiǎn)潔

3、、易學(xué)易用HandsOnSQL功能功能lSQL語言由以下功能組成：語言由以下功能組成：SQL 功能動(dòng)詞數(shù)據(jù)查詢DQLSELECT數(shù)據(jù)定義DDLCREATE、DROP、ALTER數(shù)據(jù)操縱DMLINSERT、UPDATE、DELTE數(shù)據(jù)控制DCLGRANT、REVOKE事務(wù)控制TCLROLLBACK、COMMIT、SAVEHandsOnSQL的不同實(shí)現(xiàn)版本的不同實(shí)現(xiàn)版本數(shù)據(jù)庫(kù)廠商一般叫法全稱ANSI/ISO StandardSQL/PSM標(biāo)準(zhǔn)SQLSQL/Persistent Stored Modules IBM DB2SQL PLSQL Procedural Language (implemen

4、ts SQL/PSM) Sybase/SQL ServerT-SQLTransact-SQL MySQLSQL/PSMSQL/Persistent Stored Module (implements SQL/PSM) OraclePL/SQLProcedural Language/SQL (based on Ada)PostgreSQLPL/pgSQLbased on Oracle PL/SQL PostgreSQLPL/PSMimplements SQL/PSMHandsOn標(biāo)準(zhǔn)標(biāo)準(zhǔn)SQL數(shù)據(jù)類型數(shù)據(jù)類型主要標(biāo)準(zhǔn)SQL類型對(duì)應(yīng)Java類型VARCHARStringINTintTINYINTb

5、yteREALfloatFLOAT/DOUBLEdoubleNUMERICBigDecimalTEXT/CLOBStringBINARY/BLOBbyteDATEjava.sql.Date非java.util.DateFLOAT也是對(duì)應(yīng)doubleHandsOnT-SQL簡(jiǎn)介簡(jiǎn)介l全稱全稱Transact-SQL，用于創(chuàng)建和管理，用于創(chuàng)建和管理SQL Server實(shí)例中的所有數(shù)據(jù)的命令語言。最早實(shí)例中的所有數(shù)據(jù)的命令語言。最早由由Sybase公司和公司和Microsoft公司聯(lián)合開發(fā)。公司聯(lián)合開發(fā)。是是SQL Server的核心組件，與的核心組件，與SQL Server通信，通信，并訪問并訪問

6、SQL Server中的對(duì)象中的對(duì)象對(duì)對(duì)SQL-92標(biāo)準(zhǔn)的基礎(chǔ)上進(jìn)行擴(kuò)展標(biāo)準(zhǔn)的基礎(chǔ)上進(jìn)行擴(kuò)展HandsOnT-SQL的擴(kuò)展的擴(kuò)展l增加批次結(jié)束指令增加批次結(jié)束指令GO表示一批的結(jié)束，注意表示一批的結(jié)束，注意GO是一個(gè)命令，不是是一個(gè)命令，不是SQL語句語句l注釋注釋單行注釋單行注釋 -多行注釋多行注釋 /* */l新的數(shù)據(jù)類型新的數(shù)據(jù)類型l變量、運(yùn)算符、語句變量、運(yùn)算符、語句l系統(tǒng)函數(shù)系統(tǒng)函數(shù)HandsOnT-SQL的數(shù)據(jù)類型的數(shù)據(jù)類型l數(shù)值類型數(shù)值類型l日期類型日期類型l二進(jìn)制類型二進(jìn)制類型l文本類型文本類型HandsOn數(shù)值數(shù)值數(shù)字類型數(shù)字類型T-SQL類型類型字節(jié)數(shù)字節(jié)數(shù)整數(shù)整數(shù)int

7、(-231231-1)bigint (-263263-1)smallint (-215215-1)tinyint (0 255)4 int8 long2 short1 byte精確數(shù)精確數(shù)numeric(p, s) -1038+11038-1decimal(p, s) p:有效位數(shù)有效位數(shù) s小數(shù)位數(shù)小數(shù)位數(shù)217近似數(shù)近似數(shù)floatreal4或或84貨幣貨幣moneysmallmoney -21474821474884其他其他bit(0, 1, NULL)1HandsOn日期與二進(jìn)制日期與二進(jìn)制日期類型日期類型范圍與精度范圍與精度字節(jié)數(shù)字節(jié)數(shù)datetime1753-1-19999-12-

8、31精確度：精確度： 3.33毫秒毫秒8smalldatetime 1900-1-12079-6-6精確度：精確度： 1分鐘分鐘4二進(jìn)制類型二進(jìn)制類型T-SQL類型類型字節(jié)數(shù)字節(jié)數(shù)二進(jìn)制數(shù)據(jù)二進(jìn)制數(shù)據(jù)binary varbinary(max)0800002GB影像影像(已廢棄已廢棄)image02GBHandsOn文本文本文本類型文本類型T-SQL類型類型字節(jié)數(shù)字節(jié)數(shù)非非Unicode字符字符char(n)varchar (n)text080000800002GBUnicode字符字符nchar (n)nvarchar (n)ntext080000800002GB每個(gè)每個(gè)Unicode字符使用

9、存儲(chǔ)器的字節(jié)是字符數(shù)的字符使用存儲(chǔ)器的字節(jié)是字符數(shù)的兩倍兩倍，這，這意味著：意味著：char(30)最多可以存最多可以存15個(gè)漢字，而個(gè)漢字，而nchar(30)最多最多可以存儲(chǔ)可以存儲(chǔ)30個(gè)漢字。個(gè)漢字。HandsOn思考思考l電話號(hào)碼一般使用什么數(shù)據(jù)類型存儲(chǔ)電話號(hào)碼一般使用什么數(shù)據(jù)類型存儲(chǔ)?l性別一般使用什么數(shù)據(jù)類型存儲(chǔ)性別一般使用什么數(shù)據(jù)類型存儲(chǔ)?l年齡信息一般使用什么數(shù)據(jù)類型存儲(chǔ)年齡信息一般使用什么數(shù)據(jù)類型存儲(chǔ)?l照片信息一般使用什么數(shù)據(jù)類型存儲(chǔ)照片信息一般使用什么數(shù)據(jù)類型存儲(chǔ)?l薪水一般使用什么數(shù)據(jù)類型存儲(chǔ)薪水一般使用什么數(shù)據(jù)類型存儲(chǔ)?HandsOnT-SQL運(yùn)算符運(yùn)算符運(yùn)算符類型運(yùn)

10、算符類型運(yùn)算符運(yùn)算符備注備注算術(shù)運(yùn)算符算術(shù)運(yùn)算符+ - * / %+ - 可以用于日期計(jì)算可以用于日期計(jì)算比較運(yùn)算符比較運(yùn)算符= = = 除了除了text和和image外都可以進(jìn)外都可以進(jìn)行比較行比較邏輯運(yùn)算符邏輯運(yùn)算符AND OR NOTBETWEEN ANDALL ANY SOMEIN EXISTS LIKE模糊查詢模糊查詢HandsOnSQL Server操縱數(shù)據(jù)庫(kù)操縱數(shù)據(jù)庫(kù)l操縱操縱SQL Server有兩種方式：有兩種方式：使用使用SQL語言操縱語言操縱利用利用SQL語言操縱，靈活性較高，通用性強(qiáng)，對(duì)語言操縱，靈活性較高，通用性強(qiáng)，對(duì)SQL語言的要求比較高語言的要求比較高開發(fā)速度慢開

11、發(fā)速度慢使用使用Management Studio的圖形用戶界面操的圖形用戶界面操縱縱利用利用GUI界面操縱，靈活性比較低，對(duì)界面操縱，靈活性比較低，對(duì)SQL Server Management Studio的要求比較高的要求比較高開發(fā)速度快開發(fā)速度快HandsOnDDL之表操作之表操作l創(chuàng)建表創(chuàng)建表 CREATE TABLE table_name ( column_name data_type )l刪除表刪除表 DROP TABLE table_nameHandsOnCREATE TABLE tbl_personnle ( -員工員工表表psn_id int, -編號(hào)編號(hào) psn _name

12、 varchar(60), -姓名姓名 psn_gender bit, -性別性別 psn_idc char(18), -身份證號(hào)身份證號(hào) psn_salary smallmoney, -工資工資 psn_dpt int -部門部門) HandsOn出現(xiàn)的問題出現(xiàn)的問題l輸入的類型是否正確？輸入的類型是否正確？工資必須是數(shù)字工資必須是數(shù)字l輸入的格式是否正確？輸入的格式是否正確？身份證號(hào)碼必須是身份證號(hào)碼必須是18位且唯一位且唯一l是否在允許的范圍內(nèi)？是否在允許的范圍內(nèi)？部門只能是已經(jīng)存在的部門部門只能是已經(jīng)存在的部門l是否存在重復(fù)輸入？是否存在重復(fù)輸入？不允許員工編號(hào)重復(fù)不允許員工編號(hào)重復(fù)l

13、是否符合其他特定要求？是否符合其他特定要求？工資必須高于最低工資標(biāo)準(zhǔn)工資必須高于最低工資標(biāo)準(zhǔn)l列值要求（約束）整行要求（約束）HandsOn數(shù)據(jù)完整性數(shù)據(jù)完整性數(shù)據(jù)完整性數(shù)據(jù)完整性可靠性可靠性準(zhǔn)確性準(zhǔn)確性HandsOn完整性完整性域完整性實(shí)體完整性引用完整性自定義完整性HandsOn實(shí)體完整性實(shí)體完整性山東青島市劉七1 1約束方法：唯一約束、主鍵約束、標(biāo)識(shí)列約束方法：唯一約束、主鍵約束、標(biāo)識(shí)列HandsOn域完整性域完整性123456789李亮約束方法：數(shù)據(jù)類型、檢查約束、外鍵約束、默認(rèn)約束方法：數(shù)據(jù)類型、檢查約束、外鍵約束、默認(rèn)值、非空約束值、非空約束HandsOn引用完整性引用完整性1 1

14、嚴(yán)三11數(shù)學(xué)約束方法：外鍵約束約束方法：外鍵約束HandsOn自定義完整性自定義完整性約束方法：檢查約束、存儲(chǔ)過程、觸發(fā)器約束方法：檢查約束、存儲(chǔ)過程、觸發(fā)器觸發(fā)器：記錄日志向員工表中插入記錄的同時(shí)將此操作記錄日志：向員工表中插入記錄的同時(shí)將此操作記錄日志：HandsOn表操作表操作在SQL管理平臺(tái)中演示打開表、數(shù)據(jù)輸入等操作HandsOn創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建數(shù)據(jù)庫(kù)表-1HandsOn創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建數(shù)據(jù)庫(kù)表-2HandsOn思考思考l在主鍵列輸入的數(shù)值，允許為空嗎在主鍵列輸入的數(shù)值，允許為空嗎?l一個(gè)表可以有多個(gè)主鍵嗎一個(gè)表可以有多個(gè)主鍵嗎?l在數(shù)據(jù)庫(kù)中，如果公司內(nèi)重名的員工，但是一個(gè)在數(shù)據(jù)庫(kù)中

15、，如果公司內(nèi)重名的員工，但是一個(gè)部門內(nèi)不允許員工重名，可以組合部門和姓名兩部門內(nèi)不允許員工重名，可以組合部門和姓名兩個(gè)字段一起來作為主鍵嗎？個(gè)字段一起來作為主鍵嗎？HandsOn選擇主鍵的原則選擇主鍵的原則l最少性最少性盡量選擇單個(gè)鍵作為主鍵盡量選擇單個(gè)鍵作為主鍵l穩(wěn)定性穩(wěn)定性盡量選擇數(shù)值更新少的列作為主鍵盡量選擇數(shù)值更新少的列作為主鍵HandsOn創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建數(shù)據(jù)庫(kù)表-3l表中沒有合適的列作為主鍵怎么辦？表中沒有合適的列作為主鍵怎么辦？HandsOn思考思考l標(biāo)識(shí)列允許為字符數(shù)據(jù)類型嗎？標(biāo)識(shí)列允許為字符數(shù)據(jù)類型嗎？l如果標(biāo)識(shí)列如果標(biāo)識(shí)列A的初始值為的初始值為1，增長(zhǎng)量為，增長(zhǎng)量為2，則輸

16、入，則輸入三行數(shù)據(jù)以后，再刪除兩行，下次再輸入數(shù)據(jù)行三行數(shù)據(jù)以后，再刪除兩行，下次再輸入數(shù)據(jù)行的時(shí)候，標(biāo)識(shí)值從多少開始？的時(shí)候，標(biāo)識(shí)值從多少開始？HandsOn創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建數(shù)據(jù)庫(kù)表-4設(shè)置列的默認(rèn)值：設(shè)置列的默認(rèn)值：HandsOn創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建數(shù)據(jù)庫(kù)表-5演示建立主-外鍵關(guān)系設(shè)置外鍵：設(shè)置外鍵：HandsOn創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建數(shù)據(jù)庫(kù)表-6HandsOn關(guān)系圖關(guān)系圖演示關(guān)系圖HandsOn關(guān)系圖關(guān)系圖HandsOn主表和從表主表和從表l1、當(dāng)主表中沒有對(duì)應(yīng)的記錄時(shí)，不能將記錄添加到子表、當(dāng)主表中沒有對(duì)應(yīng)的記錄時(shí)，不能將記錄添加到子表員工表中不能出現(xiàn)部門表中不存在的部門；員工表中不能出現(xiàn)部門表

17、中不存在的部門；l2、不能更改主表中的值而導(dǎo)致子表中的記錄孤立、不能更改主表中的值而導(dǎo)致子表中的記錄孤立把部門表中的編號(hào)改變了，員工表表中的編號(hào)號(hào)也應(yīng)把部門表中的編號(hào)改變了，員工表表中的編號(hào)號(hào)也應(yīng)當(dāng)隨之改變；當(dāng)隨之改變；l3、子表存在與主表對(duì)應(yīng)的記錄，不能從主表中刪除該行、子表存在與主表對(duì)應(yīng)的記錄，不能從主表中刪除該行不能把有員工的部門刪除不能把有員工的部門刪除l4、刪除主表前，先刪子表、刪除主表前，先刪子表先刪員工信息表、后刪除部門表先刪員工信息表、后刪除部門表HandsOn創(chuàng)建數(shù)據(jù)庫(kù)表創(chuàng)建數(shù)據(jù)庫(kù)表-7檢查約束HandsOn總結(jié)總結(jié)lSQL Server 創(chuàng)建創(chuàng)建 表的過程是規(guī)定數(shù)據(jù)列的屬性

18、的表的過程是規(guī)定數(shù)據(jù)列的屬性的過程過程, 同時(shí)也是實(shí)施數(shù)據(jù)完整性（包括實(shí)體完整性、同時(shí)也是實(shí)施數(shù)據(jù)完整性（包括實(shí)體完整性、引用完整性和域完整性等）保證的過程引用完整性和域完整性等）保證的過程l實(shí)體完整性數(shù)據(jù)行不能存在重復(fù)，引用完整性要求實(shí)體完整性數(shù)據(jù)行不能存在重復(fù)，引用完整性要求子表中的相關(guān)項(xiàng)必須在主表中存在子表中的相關(guān)項(xiàng)必須在主表中存在l域完整性實(shí)現(xiàn)了對(duì)輸入到特定列的數(shù)值的限制域完整性實(shí)現(xiàn)了對(duì)輸入到特定列的數(shù)值的限制lSQL Server中存在五種約束，分別是：主鍵約束、中存在五種約束，分別是：主鍵約束、外鍵約束、檢查約束、默認(rèn)約束和唯一性約束外鍵約束、檢查約束、默認(rèn)約束和唯一性約束Hand

19、sOn總結(jié)總結(jié)l創(chuàng)建數(shù)據(jù)庫(kù)表需要：確定表的列名、數(shù)據(jù)類型、創(chuàng)建數(shù)據(jù)庫(kù)表需要：確定表的列名、數(shù)據(jù)類型、是否允許為空，還需要確定主鍵、必要的默認(rèn)值、是否允許為空，還需要確定主鍵、必要的默認(rèn)值、標(biāo)識(shí)列和檢查約束標(biāo)識(shí)列和檢查約束l如果建立了主表和子表的關(guān)系，則：如果建立了主表和子表的關(guān)系，則：子表中的相關(guān)項(xiàng)目的數(shù)據(jù)，在主表中必須存在；子表中的相關(guān)項(xiàng)目的數(shù)據(jù)，在主表中必須存在；主表中相關(guān)項(xiàng)的數(shù)據(jù)更改了，則子表對(duì)應(yīng)的數(shù)據(jù)項(xiàng)主表中相關(guān)項(xiàng)的數(shù)據(jù)更改了，則子表對(duì)應(yīng)的數(shù)據(jù)項(xiàng)也應(yīng)當(dāng)隨之也應(yīng)當(dāng)隨之 更改；更改；在刪除子表之前，不能夠刪除主表；在刪除子表之前，不能夠刪除主表；HandsOn作業(yè)作業(yè)l使用使用PowerDesigner打開打開HRMS.pdm，根，根據(jù)其中內(nèi)容，結(jié)合課上所講知識(shí)，創(chuàng)建以據(jù)其中內(nèi)容，結(jié)合課