數(shù)據(jù)庫(kù)原理及應(yīng)用課件：第5章 架構(gòu)與基本表

1、數(shù)據(jù)庫(kù)基礎(chǔ)與實(shí)踐技術(shù)（SQL Server 2008）第五章5.1 架構(gòu)5.2 基本表5.3 分區(qū)表第5章 架構(gòu)與基本表2/385.1.1 創(chuàng)建架構(gòu)架構(gòu)（schema，也稱為模式）是數(shù)據(jù)庫(kù)下的一個(gè)邏輯命名空間，是一個(gè)數(shù)據(jù)庫(kù)對(duì)象的容器。架構(gòu)對(duì)象的類型包括：基本表、視圖、觸發(fā)器等。一個(gè)數(shù)據(jù)庫(kù)可包含一個(gè)或多個(gè)架構(gòu)。在同一個(gè)數(shù)據(jù)庫(kù)中，架構(gòu)名必須唯一。3/38CREATE SCHEMA schema_name_clause .n := schema_name | schema_name AUTHORIZATION owner_name := table_definition | view_definit

2、ion | grant_statement revoke_statement | deny_statement 4/385.1.1 創(chuàng)建架構(gòu)【例1】為用戶“U1”定義一個(gè)架構(gòu)，架構(gòu)名為“Sales”。CREATE SCHEMA Sales AUTHORIZATION U15/385.1.1 創(chuàng)建架構(gòu)【例2】創(chuàng)建由U1擁有、包含Test表的架構(gòu)Common，同時(shí)授予U2對(duì)Test表有SELECT權(quán)，授予U3不能對(duì)Test表有刪除權(quán)限。CREATE SCHEMA Common AUTHORIZATION U1 CREATE TABLE Test ( C1 int primary key, C2 c

3、har(4) ) GRANT SELECT TO U2 DENY DELETE TO U3;6/385.1.1 創(chuàng)建架構(gòu)5.1.2 在架構(gòu)間傳輸對(duì)象在架構(gòu)之間傳輸對(duì)象就是更改對(duì)象所屬的架構(gòu)。ALTER SCHEMA schema_name TRANSFER securable_nameschema_name：架構(gòu)對(duì)象將移入其中的架構(gòu)名。securable_name：被移出架構(gòu)的對(duì)象名。僅用于在同一數(shù)據(jù)庫(kù)中對(duì)象的移動(dòng)。7/38【例3】將Test表從Common架構(gòu)傳輸?shù)絊pecial架構(gòu)中。ALTER SCHEMA Special TRANSFER Common.Test8/385.1.2 在架

4、構(gòu)間傳輸對(duì)象5.1.3 刪除架構(gòu) DROP SCHEMA schema_name【說(shuō)明】不能刪除包含對(duì)象的架構(gòu)?！纠?】刪除Special架構(gòu)，假設(shè)該架構(gòu)中包含Test表。先移除架構(gòu)中的對(duì)象 ALTER schema common transfer Special.Test; 然后刪除架構(gòu) DROP SCHEMA Special; 9/385.1 架構(gòu)5.2 基本表5.3 分區(qū)表第5章 架構(gòu)與基本表10/385.2 基本表（table）基本表是包含數(shù)據(jù)庫(kù)中所有數(shù)據(jù)的數(shù)據(jù)庫(kù)對(duì)象。是行的集合，或者說(shuō)是元組的集合。設(shè)計(jì)數(shù)據(jù)庫(kù)的關(guān)鍵任務(wù)就是設(shè)計(jì)數(shù)據(jù)庫(kù)中所需要的表。包括以下主要特征：表要包含的列數(shù)每列中

5、數(shù)據(jù)的類型和長(zhǎng)度哪些列是主鍵，哪些列是外鍵。11/385.2.1 SQL Server數(shù)據(jù)類型 SQL Server提供系統(tǒng)數(shù)據(jù)類型，定義了可與SQL Server一起使用的所有數(shù)據(jù)類型；用戶還可以使用Transact-SQL或.NET框架定義自己的數(shù)據(jù)類型，它是系統(tǒng)提供的數(shù)據(jù)類型的別名。12/38系統(tǒng)數(shù)據(jù)類型13/381.數(shù)值類型 2.字符串類型3.日期時(shí)間類型4.貨幣類型1.數(shù)值類型14/38精確數(shù)值類型說(shuō)明存儲(chǔ)空間bigint存儲(chǔ)從263 (9,223,372,036,854,775,808)到263-1 (9,223,372,036,854,775,807) 范圍的整數(shù)8字節(jié)int存儲(chǔ)

6、從231 (2,147,483,648 ) 到 231-1 ( 2,147,483,647 ) 范圍的整數(shù)。4字節(jié)smallint存儲(chǔ)從215 (32,768 ) 到 215-1 (32,767 ) 范圍的整數(shù)2字節(jié)tinyint存儲(chǔ)從 0255 之間的整數(shù)。1字節(jié)15/38bit存儲(chǔ)1或0，相當(dāng)于其它語(yǔ)言的邏輯數(shù)據(jù)類型。利用SSMS圖形方式插入bit類型數(shù)據(jù)用“true”或“false”。SQL語(yǔ)句方式，上述值都可以用。1字節(jié)numeric(p,s)或decimal(p,s)定點(diǎn)精度和小數(shù)位數(shù)。使用最大精度時(shí)，有效值從 1038 +1 到 1038 -1。p為精度，指定小數(shù)點(diǎn)左邊和右邊可以存

7、儲(chǔ)的十進(jìn)制數(shù)字的最大個(gè)數(shù)。精度必須是從1到最大精度之間的值。最大精度為38。s為小數(shù)位數(shù)，指定小數(shù)點(diǎn)右邊可以存儲(chǔ)的十進(jìn)制數(shù)字的最大個(gè)數(shù)。s的默認(rèn)值為0。0 = s = p最多17字節(jié)1.數(shù)值類型1.數(shù)值類型【例5】decimal（10，5），表示共有10位數(shù)字，其中小數(shù)5位。 在上述定義下，表示56.342689時(shí)，實(shí)際存儲(chǔ)值為？16/3817/38近似數(shù)值類型說(shuō)明存儲(chǔ)空間float(n)存儲(chǔ)從-1.79E + 308至-2.23E 308、0以及2.23E308至1.79E + 308范圍的浮點(diǎn)數(shù)。n有兩個(gè)值，如果指定的n在124之間，則使用24，占用4字節(jié)空間，相當(dāng)于real類型；如果指定

8、的n在2553之間，則使用53，占用8字節(jié)空間。若省略(n)，則默認(rèn)為53。4字節(jié)或8字節(jié)real存儲(chǔ)從3.40E + 38到3.40E + 38范圍的浮點(diǎn)型數(shù)4字節(jié)1.數(shù)值類型2.字符串類型18/38普通編碼字符串類型統(tǒng)一字符編碼字符串類型在使用字符數(shù)據(jù)類型時(shí)，需要在其前后加上英文單引號(hào)或者雙引號(hào)。普通編碼字符串類型普通編碼字符串類型說(shuō)明存儲(chǔ)空間char(n)固定長(zhǎng)度的普通編碼字符串類型，n表示字符串的最大長(zhǎng)度，取值范圍為18000 n個(gè)字節(jié)。當(dāng)實(shí)際字符串所需空間小于n時(shí)，系統(tǒng)自動(dòng)在后邊補(bǔ)空格varchar(n)可變長(zhǎng)度的字符串類型，n表示字符串的最大長(zhǎng)度，取值范圍為18000字符數(shù)2字節(jié)額

9、外開(kāi)銷text最多可存儲(chǔ)231-1 (2,147,483,647) 個(gè)字符每個(gè)字符1個(gè)字節(jié)varchar(max)最多可存儲(chǔ)231-1個(gè)字符字符數(shù)2字節(jié)額外開(kāi)銷19/3420/38【說(shuō)明】如果在使用char(n)或varchar(n)類型時(shí)未指定n，則默認(rèn)長(zhǎng)度為1。如果在使用CAST和CONVERT函數(shù)時(shí)未指定n，則默認(rèn)長(zhǎng)度為30。普通編碼字符串類型統(tǒng)一字符編碼字符串類型統(tǒng)一編碼字符串類型說(shuō)明存儲(chǔ)空間nchar(n)固定長(zhǎng)度的統(tǒng)一編碼字符串類型，n表示字符串的最大長(zhǎng)度，取值范圍為140002n字節(jié)。當(dāng)實(shí)際字符串所需空間小于2n時(shí)，系統(tǒng)自動(dòng)在后邊補(bǔ)空格nvarchar(n)可變長(zhǎng)度的統(tǒng)一編碼字符

10、串類型，n表示字符串的最大長(zhǎng)度，取值范圍為140002*字符數(shù)2字節(jié)額外開(kāi)銷ntext最多可存儲(chǔ)230-1 (1,073,741,823) 個(gè)統(tǒng)一字符編碼的字符每個(gè)字符2個(gè)字節(jié)nvarchar(max)最多可存儲(chǔ)230-1個(gè)統(tǒng)一字符編碼的字符2*字符數(shù)2字節(jié)額外開(kāi)銷21/343.日期時(shí)間類型日期時(shí)間類型說(shuō)明存儲(chǔ)空間date定義一個(gè)日期，范圍為 到 。字符長(zhǎng)度10位，默認(rèn)格式為：YYYY-MM-DD。YYYY表示4位年份數(shù)字，范圍從0001到9999；MM表示2位月份數(shù)字，范圍從01到12；DD表示2位日的數(shù)字，范圍從 01 到 31（最大值取決于具體月份）3字節(jié)time(n)定義一天中的某個(gè)時(shí)

11、間，該時(shí)間基于24小時(shí)制。默認(rèn)格式為：hh:mm:ss.nnnnnnn，范圍為00:00:00.0000000到23:59:59.9999999。精確到100納秒。n為秒的小數(shù)位數(shù)，取值范圍是0到7的整數(shù)。默認(rèn)秒的小數(shù)位數(shù)是7(100ns)35字節(jié)datetime定義一個(gè)采用24小時(shí)制并帶有秒的小數(shù)部分的日期和時(shí)間，范圍為到-，時(shí)間范圍是00:00:00到23:59:59.997。默認(rèn)格式為：YYYY-MM-DD hh:mm:ss.nnn，n為數(shù)字，表示秒的小數(shù)部分（精確到0.00333 秒）8字節(jié)22/34日期時(shí)間類型說(shuō)明存儲(chǔ)空間smalldatetime定義一個(gè)采用24小時(shí)制并且秒始終為零

12、(:00)的日期和時(shí)間，范圍為到。默認(rèn)格式為：YYYY-MM-DD hh:mm:00。精確到1分鐘4字節(jié)datetime2定義一個(gè)結(jié)合了24小時(shí)制時(shí)間的日期。可將該類型看成是datetime類型的擴(kuò)展，其數(shù)據(jù)范圍更大，默認(rèn)的小數(shù)精度更高，并具有可選的用戶定義的精度。默認(rèn)格式是：YYYY-MM-DD hh:mm:ss.nnnnnnn，n為數(shù)字，表示秒的小數(shù)位數(shù)（最多精確到100 納秒），默認(rèn)精度是7位小數(shù)。該類型的字符串長(zhǎng)度最少19 位（YYYY-MM-DD hh:mm:ss），最多27位(YYYY-MM-DD hh:mm:ss.0000000)68字節(jié)23/343.日期時(shí)間類型當(dāng)插入數(shù)據(jù)或者在

13、其他地方使用Datetime類型時(shí)，需要用單引號(hào)把它括起來(lái)。在字符串中，以斜線(/)、連字符(-)或句號(hào)(.)作為分隔符來(lái)指定年月日。年可用2位或4位表示，月和日可用1位或2位表示。24/383.日期時(shí)間類型25/38可以使用如下命令改變?nèi)掌诘捻樞颍?Set dateformat format參數(shù)可以是mdy,ymd,dmy,ydm,myd,dym?！纠?】Set dateformat dym 那么，輸入07/10/09是什么日期？3.日期時(shí)間類型4.貨幣類型貨幣類型說(shuō)明存儲(chǔ)空間money存儲(chǔ)922,337,203,685,477.5808到922,337,203,685,477.5807范圍

14、的數(shù)值，精確到小數(shù)點(diǎn)后4位8字節(jié)smallmoney存儲(chǔ)-214,748.3648 到214,748.3647范圍的數(shù)值，精確到小數(shù)點(diǎn)后4位4字節(jié)26/34【說(shuō)明】當(dāng)為money或smallmoney的類型輸入數(shù)據(jù)時(shí)，必須在有效位置前面加一個(gè)貨幣單位符號(hào)$，且如果貨幣值為負(fù)數(shù)，需要在$后面加上負(fù)號(hào)。 56.342689$15000.321.23E-32014-09-28true，false優(yōu) 良 89March 1 98SQL Server2008系統(tǒng)數(shù)據(jù)類型用戶定義數(shù)據(jù)類型28/38CREATE TYPE schema_name. type_name FROM base_type ( pre

15、cision , scale ) NULL | NOT NULL Exec sp_addtype typename, basetype,nulltype【例7】創(chuàng)建一個(gè)名為telephone的數(shù)據(jù)類型，其相應(yīng)的系統(tǒng)數(shù)據(jù)類型為：char(8)，不允許空 CREATE TYPE telephone FROM CHAR(8) NOT NULL【例8】exec sp_addtype zipcode, char(9),null29/38用戶定義數(shù)據(jù)類型30/38SQL Server中，每個(gè)數(shù)據(jù)庫(kù)最多可以包含20億個(gè)表。每個(gè)表可以定義至多1024個(gè)屬性，每行最多包含8060個(gè)字節(jié)。表中的行數(shù)及總大小僅受可

16、用存儲(chǔ)空間的限制。同一個(gè)表中不允許有相同的屬性名。5.2.2 基本表管理1.創(chuàng)建基本表2.修改基本表3.刪除基本表可以使用T-SQL語(yǔ)句實(shí)現(xiàn)，也可以在SSMS中用圖形化方法實(shí)現(xiàn)。31/385.2.2 基本表管理5.2.2 基本表管理CREATE TABLE ( ， ， )；1. 創(chuàng)建基本表1.創(chuàng)建基本表常用完整性約束1.主碼約束：PRIMARY KEY2.參照完整性約束:Foreign KeyReferences3.唯一性約束：UNIQUE4.非空值約束：NOT NULL5.缺省值約束：DEFAULT 6.檢查約束：Check5.2.2 基本表管理CREATE TABLE Student (S

17、no CHAR(20) PRIMARY KEY ， Sname CHAR(20) UNIQUE， Ssex CHAR(1) ， Sage TINYINT， Sdept CHAR(16)； 建立一個(gè)“學(xué)生”表Student，它由學(xué)號(hào)Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個(gè)屬性組成。其中學(xué)號(hào)不能為空，值是唯一的，并且姓名取值也唯一。5.2.2 基本表管理建立一個(gè)“課程”表Course，它由課程號(hào)Cno、課程名字Cname、先行課號(hào)Cpno和學(xué)分Ccredit組成。其中Cno是主碼，Cpno參照Cno取值。CREATE TABLE Course( Cno CHAR(1

18、4) PRIMARY KEY, Cname CHAR(40), Cpno CHAR(4), Ccredit SMALLINT, Foreign Key Cpno References Course (Cno); 5.2.2 基本表管理CREATE TABLE SC( Sno CHAR(20) , Cno CHAR(14) , Grade INT DEFAULT 0, Constraint SCPK Primary key (Sno, Cno),Foreign Key(Sno) Refrences Student(Sno),Foreign Key(Cno) References Course(C

19、no),Check(Grade=0 AND Grade=100)建立一個(gè)“學(xué)生選課”表SC，它由學(xué)號(hào)Sno、課程號(hào)Cno，修課成績(jī)Grade組成(0到100分取值)，其中(Sno, Cno)為主碼。5.2.2 基本表管理約束名37/38定義計(jì)算屬性：column_name as computed_column_expressionpersisted計(jì)算屬性是由同一表中的其它屬性通過(guò)表達(dá)式計(jì)算得到的。表達(dá)式可以是非計(jì)算屬性的屬性名、常量、函數(shù)、變量，也可以是一個(gè)或多個(gè)運(yùn)算符連接的上述元素的任意組合。表達(dá)式不能是子查詢，也不能包含用戶定義數(shù)據(jù)類型。1)創(chuàng)建有計(jì)算列的表示例：創(chuàng)建有計(jì)算列的表【例9】

20、創(chuàng)建一個(gè)帶計(jì)算列的表，表中包含課程的課程號(hào)，該課程的總成績(jī)和學(xué)習(xí)該課程的人數(shù)，以及平均成績(jī)。Create table sum_avg(課程號(hào) char(3), 總成績(jī) real not null, 人數(shù) int not null, 平均成績(jī) as 總成績(jī)/人數(shù))38/3839/38一個(gè)表只能有一個(gè)使用 IDENTITY 屬性定義的列，且必須通過(guò)使用 decimal、int、numeric、smallint、bigint 或 tinyint 數(shù)據(jù)類型來(lái)定義該列。必須同時(shí)指定種子和增量值。兩者都未指定時(shí)，二者的默認(rèn)值均為1。標(biāo)識(shí)符列不能允許為空值，也不能包含 DEFAULT 定義或?qū)ο?。?biāo)識(shí)列的值

21、是系統(tǒng)自動(dòng)生成的。2)創(chuàng)建包含標(biāo)識(shí)列的表如果確實(shí)要為標(biāo)識(shí)列提供值，則必須將表的IDENTITY_INSERT屬性設(shè)置為ON（默認(rèn)時(shí)該屬性的值為OFF）。SET IDENTITY_INSERT database_name. schema_name . table ON | OFF 40/382)創(chuàng)建包含標(biāo)識(shí)列的表示例：創(chuàng)建包含標(biāo)識(shí)列的表CREATE TABLE IDTable ( SID INT IDENTITY(100,1) NOT NULL, Name VARCHAR(20)41/38練習(xí)42/38class_info表結(jié)構(gòu)如下，請(qǐng)創(chuàng)建該表。屬性名數(shù)據(jù)類型長(zhǎng)度是否為空默認(rèn)值說(shuō)明班級(jí)編號(hào)int

22、not主碼，標(biāo)識(shí)列，從100開(kāi)始，增量為1班級(jí)名稱nchar8not無(wú)入學(xué)年份smalldatetimeyes無(wú)所屬系別nchar12yes無(wú)專業(yè)編號(hào)intyes無(wú) 外碼，參照專業(yè)表專業(yè)號(hào)取值人數(shù)smallintno500人數(shù)502. 修改表結(jié)構(gòu)使用ALTER TABLE語(yǔ)句實(shí)現(xiàn)。ALTER TABLE語(yǔ)句可以對(duì)已定義的表進(jìn)行添加/刪除列；修改列定義；添加/刪除約束。43/3844/382. 修改表結(jié)構(gòu)ALTER TABLE with nocheck ADD |完整性約束 DROP |column ALTER column ；45/38【說(shuō)明】alter table 語(yǔ)句中，一次只能包含一條a

23、lter column，add，drop子句中的一條。alter column子句一次只能修改一個(gè)列的屬性，add和drop子句則可以操作多個(gè)。add子句增加的約束只能是表級(jí)約束。刪除屬性列時(shí)，必須先刪除該列上的約束條件或索引。with nocheck 表示不檢查當(dāng)前數(shù)據(jù)是否違反修改后的類型或約束。2. 修改表結(jié)構(gòu)【例10】為SC表添加“修課類別”列，此列的定義為：Type NCHAR(1)，允許空。ALTER TABLE SC ADD Type NCHAR(1) NULL【例11】將新添加的Type列的數(shù)據(jù)類型改為NCHAR(2)。ALTER TABLE SC ALTER COLUMN Ty

24、pe NCHAR(2)46/382. 修改表結(jié)構(gòu)47/38【例12】向課程表添加“任課教師”和“是否必修”兩列，要求類型分別為字符類型和布爾類型，其中是否必修初值為T(必修）。 Alter table course add Cteacher varchar(16), Bixiu bit default True2. 修改表結(jié)構(gòu)【例13】刪除學(xué)生姓名必須取唯一值的約束。ALTER TABLE Student DROP UNIQUE(Sname)；2.修改表結(jié)構(gòu)【例14】ALTER TABLE tb2 WITH NOCHECKADD CONSTRAINT tb2_checkCHECK(tb1_te

25、l LIKE 13,5,8,90-90-90-90-90-90-90-90-90-9) 3. 刪除表DROP TABLE , 【注意】：如果被刪除的表中有其他表對(duì)它的外鍵引用約束，則必須先刪除外鍵所在的表，然后再刪除被引用的表。49/385.1 架構(gòu)5.2 基本表5.3 分區(qū)表第5章 架構(gòu)與基本表50/385.3 分區(qū)表分區(qū)表是按照某種標(biāo)準(zhǔn)將表中的數(shù)據(jù)按水平方式劃分成不同的子集，存儲(chǔ)在一個(gè)或多個(gè)文件組中。合理的使用分區(qū)會(huì)在很大程度上提高數(shù)據(jù)庫(kù)的性能。51/38比較適于進(jìn)行分區(qū)的情況表中數(shù)據(jù)量大；該表包含（或?qū)┮远喾N不同方式使用的大量數(shù)據(jù)。數(shù)據(jù)是分段的，比如數(shù)據(jù)以年為分隔。對(duì)表的常規(guī)維護(hù)操

26、作只針對(duì)表的一個(gè)數(shù)據(jù)子集。52/38分區(qū)表特點(diǎn)分區(qū)表是從物理上將一個(gè)大表分成幾個(gè)小表，但從邏輯上來(lái)看，還是一個(gè)大表。對(duì)用戶而言，所面對(duì)的依然是一個(gè)大表，用戶只要將記錄插入到大表中就可以了。53/38創(chuàng)建分區(qū)表步驟1創(chuàng)建分區(qū)函數(shù)。告訴SQL Server以什么方式對(duì)表進(jìn)行分區(qū)。2創(chuàng)建分區(qū)方案。告訴SQL Server將已分區(qū)的數(shù)據(jù)放在哪個(gè)文件組中。3使用分區(qū)方案創(chuàng)建分區(qū)表?！菊f(shuō)明】在創(chuàng)建分區(qū)表之前，最好先創(chuàng)建數(shù)據(jù)庫(kù)文件組。54/381. 創(chuàng)建分區(qū)函數(shù)CREATE PARTITION FUNCTION partition_function_name ( input_parameter_type )

27、AS RANGE LEFT | RIGHT FOR VALUES ( boundary_value , n )55/38示例【例15】在 int 列上創(chuàng)建左側(cè)分區(qū)函數(shù)。CREATE PARTITION FUNCTION myRangePF1(int)AS RANGE LEFT FOR VALUES (1,100,1000);上述分區(qū)函數(shù)將表分為四個(gè)分區(qū)：56/38示例【例16】在int列上創(chuàng)建右側(cè)分區(qū)函數(shù)。CREATE PARTITION FUNCTION myRangePF2(int)AS RANGE RIGHT FOR VALUES (1, 100, 1000)57/38示例【例17】在d

28、ate列上創(chuàng)建右側(cè)分區(qū)函數(shù)。將表分成12個(gè)分區(qū)，每個(gè)分區(qū)對(duì)應(yīng)一個(gè)月的值CREATE PARTITION FUNCTION myDateRangePF1(date)AS RANGE RIGHT FOR VALUES ( 20110201, 20110301, 20110401, 20110501, 20110601, 20110701, 20110801, 20110901, 20111001, 20111101, 20111201);58/382. 創(chuàng)建分區(qū)方案CREATE PARTITION SCHEME partition_scheme_nameAS PARTITION partition_function_name ALL TO ( file_group_name | PRIMARY ,.n ) ; 59/38