數(shù)據庫原理及應用課件：第5章 架構與基本表

1、數(shù)據庫基礎與實踐技術（SQL Server 2008）第五章5.1 架構5.2 基本表5.3 分區(qū)表第5章 架構與基本表2/385.1.1 創(chuàng)建架構架構（schema，也稱為模式）是數(shù)據庫下的一個邏輯命名空間，是一個數(shù)據庫對象的容器。架構對象的類型包括：基本表、視圖、觸發(fā)器等。一個數(shù)據庫可包含一個或多個架構。在同一個數(shù)據庫中，架構名必須唯一。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)建架構【例1】為用戶“U1”定義一個架構，架構名為“Sales”。CREATE SCHEMA Sales AUTHORIZATION U15/385.1.1 創(chuàng)建架構【例2】創(chuàng)建由U1擁有、包含Test表的架構Common，同時授予U2對Test表有SELECT權，授予U3不能對Test表有刪除權限。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)建架構5.1.2 在架構間傳輸對象在架構之間傳輸對象就是更改對象所屬的架構。ALTER SCHEMA schema_name TRANSFER securable_nameschema_name：架構對象將移入其中的架構名。securable_name：被移出架構的對象名。僅用于在同一數(shù)據庫中對象的移動。7/38【例3】將Test表從Common架構傳輸?shù)絊pecial架構中。ALTER SCHEMA Special TRANSFER Common.Test8/385.1.2 在架

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

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

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

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

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

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

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

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

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

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

14、的數(shù)值，精確到小數(shù)點后4位8字節(jié)smallmoney存儲-214,748.3648 到214,748.3647范圍的數(shù)值，精確到小數(shù)點后4位4字節(jié)26/34【說明】當為money或smallmoney的類型輸入數(shù)據時，必須在有效位置前面加一個貨幣單位符號$，且如果貨幣值為負數(shù)，需要在$后面加上負號。 56.342689$15000.321.23E-32014-09-28true，false優(yōu) 良 89March 1 98SQL Server2008系統(tǒng)數(shù)據類型用戶定義數(shù)據類型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)建一個名為telephone的數(shù)據類型，其相應的系統(tǒng)數(shù)據類型為：char(8)，不允許空 CREATE TYPE telephone FROM CHAR(8) NOT NULL【例8】exec sp_addtype zipcode, char(9),null29/38用戶定義數(shù)據類型30/38SQL Server中，每個數(shù)據庫最多可以包含20億個表。每個表可以定義至多1024個屬性，每行最多包含8060個字節(jié)。表中的行數(shù)及總大小僅受可

16、用存儲空間的限制。同一個表中不允許有相同的屬性名。5.2.2 基本表管理1.創(chuàng)建基本表2.修改基本表3.刪除基本表可以使用T-SQL語句實現(xiàn)，也可以在SSMS中用圖形化方法實現(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)； 建立一個“學生”表Student，它由學號Sno、姓名Sname、性別Ssex、年齡Sage、所在系Sdept五個屬性組成。其中學號不能為空，值是唯一的，并且姓名取值也唯一。5.2.2 基本表管理建立一個“課程”表Course，它由課程號Cno、課程名字Cname、先行課號Cpno和學分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)建立一個“學生選課”表SC，它由學號Sno、課程號Cno，修課成績Grade組成(0到100分取值)，其中(Sno, Cno)為主碼。5.2.2 基本表管理約束名37/38定義計算屬性：column_name as computed_column_expressionpersisted計算屬性是由同一表中的其它屬性通過表達式計算得到的。表達式可以是非計算屬性的屬性名、常量、函數(shù)、變量，也可以是一個或多個運算符連接的上述元素的任意組合。表達式不能是子查詢，也不能包含用戶定義數(shù)據類型。1)創(chuàng)建有計算列的表示例：創(chuàng)建有計算列的表【例9】

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

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

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

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

24、pe NCHAR(2)46/382. 修改表結構47/38【例12】向課程表添加“任課教師”和“是否必修”兩列，要求類型分別為字符類型和布爾類型，其中是否必修初值為T(必修）。 Alter table course add Cteacher varchar(16), Bixiu bit default True2. 修改表結構【例13】刪除學生姓名必須取唯一值的約束。ALTER TABLE Student DROP UNIQUE(Sname)；2.修改表結構【例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 , 【注意】：如果被刪除的表中有其他表對它的外鍵引用約束，則必須先刪除外鍵所在的表，然后再刪除被引用的表。49/385.1 架構5.2 基本表5.3 分區(qū)表第5章 架構與基本表50/385.3 分區(qū)表分區(qū)表是按照某種標準將表中的數(shù)據按水平方式劃分成不同的子集，存儲在一個或多個文件組中。合理的使用分區(qū)會在很大程度上提高數(shù)據庫的性能。51/38比較適于進行分區(qū)的情況表中數(shù)據量大；該表包含（或將包含）以多種不同方式使用的大量數(shù)據。數(shù)據是分段的，比如數(shù)據以年為分隔。對表的常規(guī)維護操

26、作只針對表的一個數(shù)據子集。52/38分區(qū)表特點分區(qū)表是從物理上將一個大表分成幾個小表，但從邏輯上來看，還是一個大表。對用戶而言，所面對的依然是一個大表，用戶只要將記錄插入到大表中就可以了。53/38創(chuàng)建分區(qū)表步驟1創(chuàng)建分區(qū)函數(shù)。告訴SQL Server以什么方式對表進行分區(qū)。2創(chuàng)建分區(qū)方案。告訴SQL Server將已分區(qū)的數(shù)據放在哪個文件組中。3使用分區(qū)方案創(chuàng)建分區(qū)表?！菊f明】在創(chuàng)建分區(qū)表之前，最好先創(chuàng)建數(shù)據庫文件組。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)建左側分區(qū)函數(shù)。CREATE PARTITION FUNCTION myRangePF1(int)AS RANGE LEFT FOR VALUES (1,100,1000);上述分區(qū)函數(shù)將表分為四個分區(qū)：56/38示例【例16】在int列上創(chuàng)建右側分區(qū)函數(shù)。CREATE PARTITION FUNCTION myRangePF2(int)AS RANGE RIGHT FOR VALUES (1, 100, 1000)57/38示例【例17】在d

28、ate列上創(chuàng)建右側分區(qū)函數(shù)。將表分成12個分區(qū)，每個分區(qū)對應一個月的值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