創(chuàng)建與管理數(shù)據(jù)庫表.ppt

第5章 管理與使用表,5.1 數(shù) 據(jù) 類 型 5.2 創(chuàng) 建 表 5.3 修 改 表 5.4 查看表屬性 5.5 刪 除 表,表是重要數(shù)據(jù)庫對象，是數(shù)據(jù)庫的基本組成部分，是儲存數(shù)據(jù)的邏輯載體 關(guān)系型數(shù)據(jù)庫的表是二維的，表的一列稱為一個字段；表的一行稱為一個記錄 SQL Server 2000中，一個表中最多允許1024列，表的行數(shù)沒有限制，與磁盤存儲空間有關(guān),概述：,表,永久表 permernet,：表一旦創(chuàng)建將一直存儲在硬盤上，除非被用戶刪除,臨時表：用戶退出時自動被系統(tǒng)刪除 temp,5.1 數(shù) 據(jù) 類 型,系統(tǒng)提供的數(shù)據(jù)類型 創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型 選擇數(shù)據(jù)類型的指導(dǎo)原則,表的內(nèi)容由列屬性組成，數(shù)據(jù)類型是最重要的列屬性，它決定了數(shù)據(jù)的存儲格式（長度、精度、小數(shù)位數(shù)等）,系統(tǒng)提供的數(shù)據(jù)類型,數(shù)值型數(shù)據(jù) 整型數(shù)據(jù)：存儲整數(shù) 小數(shù)數(shù)據(jù)：包含存儲在最小有效數(shù)上的數(shù)據(jù),近似數(shù)字?jǐn)?shù)據(jù)：表示浮點數(shù)據(jù)的近似數(shù)字 注意：存儲的非精確值而只是近似值，在使用where進行查詢時，避免使用數(shù)據(jù)類型為float和real的字段。另限制float和real的字段作和的比較,貨幣數(shù)據(jù)：表示正的或負的貨幣值 注意： 貨幣數(shù)據(jù)使用時，不需要用單引號 括起來，但貨幣數(shù) 值之前一定要帶有適當(dāng)?shù)呢泿欧?。如?78,日期和時間數(shù)據(jù) 字符數(shù)據(jù)和 Unicode 字符數(shù)據(jù),可變長度,Unicode采用兩個字節(jié)編碼,可以存儲日文、韓文和漢字,二進制數(shù)據(jù) 其他,選擇數(shù)據(jù)類型的指導(dǎo)原則,若列值的長度相差很大，那么使用變長數(shù)據(jù)類型 例如某列存儲的是人名，地址等 謹(jǐn)慎使用 tinyint 數(shù)據(jù)類型 雖然節(jié)省空間，但擴展性很小 對于小數(shù)數(shù)據(jù)來說，一般使用 decimal 數(shù)據(jù)類型 可以精確地控制精度 如果行的存儲量 超過8 000字節(jié)，使用 text 或者 image 若不大于8 000字節(jié)，可使用char、varchar或者binary數(shù)據(jù)類型 對于貨幣數(shù)據(jù)，使用 money 數(shù)據(jù)類型 不要使用類型為 float 或者 real 的列作為主鍵 因為它們不精確，所以不適合用于比較,創(chuàng)建和刪除用戶定義的數(shù)據(jù)類型,用戶定義的數(shù)據(jù)類型（擴展SQL Server的基本數(shù)據(jù)類型） 當(dāng)多個表的列中要存儲同樣類型的數(shù)據(jù)，且想確保這些列具有完全相同的數(shù)據(jù)類型、長度和為空性時，可使用用戶定義數(shù)據(jù)類型 注意：如果用戶定義數(shù)據(jù)類型是在 model 數(shù)據(jù)庫中創(chuàng)建的，它將作用于所有用戶定義的新數(shù)據(jù)庫中。如果數(shù)據(jù)類型在用戶定義的數(shù)據(jù)庫中創(chuàng)建，則該數(shù)據(jù)類型只作用于此用戶定義的數(shù)據(jù)庫。,創(chuàng)建用戶定義的數(shù)據(jù)類型 企業(yè)管理器 系統(tǒng)存儲過程 語法 sp_addtype typename = type, phystype = system_data_type , nulltype = null_type , owner = owner_name 刪除用戶定義的數(shù)據(jù)類型 sp_droptype 類型名,企業(yè)管理器創(chuàng)建用戶定義函數(shù),展開服務(wù)器組，然后展開服務(wù)器。 展開“數(shù)據(jù)庫“文件夾，再展開要在其中創(chuàng)建用戶定義的數(shù)據(jù)類型的數(shù)據(jù)庫。 右擊“用戶定義數(shù)據(jù)類型“，然后單擊“新建用戶定義數(shù)據(jù)類型“命令。 輸入新建數(shù)據(jù)類型的名稱。 在“數(shù)據(jù)類型“列表中，選擇基數(shù)據(jù)類型。 如“長度“處于活動狀態(tài)，若要更改此數(shù)據(jù)類型可存儲的最大數(shù)據(jù)長度，請鍵入另外的值。長度可變的數(shù)據(jù)類型有：binary、char、nchar、nvarchar、varbinary 和 varchar。 若要允許此數(shù)據(jù)類型接受空值，請選擇“允許空值“命令。 在“規(guī)則“和“默認值“列表中選擇一個規(guī)則或默認值（若有）以將其綁定到用戶定義數(shù)據(jù)類型上,用戶自定義函數(shù)示例(p350),A. 創(chuàng)建不允許空值的用戶定義數(shù)據(jù)類型 下面的示例創(chuàng)建一個名為 ssn（社會保險號）的用戶定義數(shù)據(jù)類型，它基于 SQL Server 提供的 varchar 數(shù)據(jù)類型。ssn 數(shù)據(jù)類型用于那些保存 11 位數(shù)字的社會保險號 (999-99-9999) 的列。該列不能為 NULL。 USE master EXEC sp_addtype ssn, VARCHAR(11), NOT NULL 請注意，varchar(11) 由單引號引了起來，這是因為它包含了標(biāo)點符號（圓括號）。,B. 創(chuàng)建允許空值的用戶定義數(shù)據(jù)類型 下面的示例創(chuàng)建了一個名為 birthday 的用戶定義數(shù)據(jù)類型（基于 datetime），該數(shù)據(jù)類型允許空值。 USE master EXEC sp_addtype birthday, datetime, NULL,C. 創(chuàng)建另外的用戶定義的數(shù)據(jù)類型 下面的示例為國內(nèi)及國際電話和傳真號碼另外創(chuàng)建兩個用戶定義的數(shù)據(jù)類型 telephone 和 fax。 USE master EXEC sp_addtype telephone, varchar(24), NOT NULL EXEC sp_addtype fax, varchar(24), NULL,例：首先創(chuàng)建一個birthday用戶定義數(shù)據(jù)類型，然后在后面的操作中使用它，最后將它刪除。 -創(chuàng)建birthday用戶定義數(shù)據(jù)類型，使用datetime基本數(shù)據(jù)類型，允許NULL use sample EXEC sp_addtype birthday, datetime, NULL GO -在數(shù)據(jù)表定義時使用birthday用戶定義數(shù)據(jù)類型 CREATE TABLE 特殊數(shù)據(jù) ( char_data char(20), birthday_data birthday ) GO,-向數(shù)據(jù)表中添加幾條記錄 INSERT INTO 特殊數(shù)據(jù) VALUES (Sarah,02/22/1976) INSERT INTO 特殊數(shù)據(jù) VALUES (Tina,04/15/1998) GO -檢索 SELECT * FROM 特殊數(shù)據(jù) GO -刪除birthday用戶定義數(shù)據(jù)類型，首先要刪除對定義數(shù)據(jù)類型的引用 DROP table 特殊數(shù)據(jù) EXEC sp_droptype birthday go,5.2 創(chuàng) 建 表,創(chuàng)建表有三種方法： 使用向?qū)?使用企業(yè)管理器 使用T-SQL語句,1 使用CREATE TABLE命令創(chuàng)建表 在指定的數(shù)據(jù)庫中創(chuàng)建表的基本語法格式如下頁所示：,CREATE TABLE database_name. owner .| owner. table_name ( | column_name AS computed_column_expression | := CONSTRAINT constraint_name | PRIMARY KEY | UNIQUE ,.n ),說明： database_name. owner .| owner. table_name：定義表名table_name 可以選加數(shù)據(jù)庫名database_name和表的所有者名owner 表名不得超過128個字符，臨時表表名不得超過116個字符, := column_name data_type COLLATE DEFAULT constant_expression | IDENTITY ( seed , increment ) ,column_name data_type ：定義列 其中column_name為列名，data_type為數(shù)據(jù)類型 NULL | NOT NULL：指定所定義的列是否可以取空值 默認情況下是NULL DEFAULT constant_expression：指定列的默認值約束 當(dāng)向表中插入一條記錄時，如果本列插入的數(shù)據(jù)為空，則系統(tǒng)自動將默認值填充到本列 IDENTITY ( seed , increment ) ：定義標(biāo)識列,也稱自動編號列，一個表中只能定義一個標(biāo)識列 ,.n：表示可以在表中設(shè)計n個列的定義，列間用逗號隔開,起始值,T-Sql創(chuàng)建表示例,例一、pubs 數(shù)據(jù)庫中所創(chuàng)建的表jobs定義，其中包含所有的約束定義。 CREATE TABLE jobs ( job_id smallint IDENTITY(1,1) PRIMARY KEY, job_desc varchar(50) NOT NULL DEFAULT New Position - title not formalized yet, min_lvl tinyint NOT NULL CHECK (min_lvl = 10), max_lvl tinyint NOT NULL CHECK (max_lvl = 250) ),例二、按如下要求在數(shù)據(jù)庫company中創(chuàng)建一個數(shù)據(jù)表 (1)表名：project_back. (2)字段：項目編號，項目名稱，級別，注冊時間，預(yù)計工期。 (3)將項目編號設(shè)置為標(biāo)識字段，種子為1，增量為1；項目名稱的默認值為”UNKNOWN”;預(yù)計工期為級別乘以20。 Use company go create table project_back ( 項目編號 int IDENTITY(1,1), 項目名稱 varchar(40) DEFAULT UNKNOWN, 級別 int, 注冊時間 varchar(20), 預(yù)計工期 as 級別*20 ),2 使用企業(yè)管理器創(chuàng)建表,返 回,作業(yè),以下作業(yè)內(nèi)容分別用企業(yè)管理器和T-sql語言實現(xiàn) 一、創(chuàng)建數(shù)據(jù)庫Group1 二、在Group1數(shù)據(jù)庫中編寫并執(zhí)行創(chuàng)建用戶自定義數(shù)據(jù)類型的語句，需要創(chuàng)建的數(shù)據(jù)類型表格如下：該過程的腳本放在E:Microsoft SQL ServerMSSQLDatacreatyp1.sql中,三、使用語句創(chuàng)建如下表格，該過程的腳本放在E:Microsoft SQL ServerMSSQLDatacreatab1.sql中,5.3 修 改 表,1 使用ALTER TABLE命令修改表結(jié)構(gòu),命令格式： ALTER TABLE table_name ALTER COLUMN column_name new_data_type NULL | NOT NULL | ADD column_name data_type NULL|DEFAULT | DROP COLUMN column_name ,.n ,Sql server規(guī)定：存在值約束或其他約束，須先刪除它們,說明： ALTER TABLE ：該關(guān)鍵字表示本命令將修改表的結(jié)構(gòu) table_name：指定需要修改的表名 ALTER COLUMN column_name： column_name指定需要修改的列名稱,命令關(guān)鍵字,new_data_typeNULL|NOT NULL ：新的數(shù)據(jù)類型，可以修改其非空屬性 ADD column_name data_type NULL|DEFAULT：添加的列的名稱及其數(shù)據(jù)類型，可添加空值NULL或DEFAULT缺省值 除DROP 外，該命令一次只允許更改表的一個屬性或修改一列,可刪除多列,舉例： CREATE TABLE doc_exb ( column_a INT, column_b VARCHAR(20) NULL) GO ALTER TABLE doc_exb DROP COLUMN column_b GO,5.4 使用企業(yè)管理器修改表結(jié)構(gòu),設(shè)計表,重新命名表 1用系統(tǒng)存儲過程修改表的名稱 命令格式