SQLServer2000數(shù)據(jù)庫表的創(chuàng)建和管理

1、SQL Server 2000數(shù)據(jù)庫表的創(chuàng)建和管理 51 表的創(chuàng)建與修改表的創(chuàng)建與修改 52 添加數(shù)據(jù)信息添加數(shù)據(jù)信息 53 對已有數(shù)據(jù)進(jìn)行管理對已有數(shù)據(jù)進(jìn)行管理 54 約束約束5.1 表的創(chuàng)建與修改 5.1.1 字段的屬性 5.1.2 使用 Enterprise Manager創(chuàng)建表 【例5.1】在demo數(shù)據(jù)庫中創(chuàng)建一個用戶信息表，表名為users，表結(jié)構(gòu)如表5.1所示。 返回目錄字段名數(shù)據(jù)類型長度允許空Idbigint8 Usernamevarchar20Passwordvarchar20Emailvarchar50Resumevarchar1000Phonevarchar20 5.1.

2、3 5.1.3 使用使用T-SQLT-SQL語句創(chuàng)建表語句創(chuàng)建表 其語法格式如下： CREATE TABLE database_name.owner.|owner.table_name /*指定表名*/ ( /*字段的定義*/ |column_name AS computed_column_expression /*定義計算字段*/ | /*指定表的約束*/ ) ONfilegroup|DEFAULT /*指定存儲表的文件組*/ TEXTIMAGE_ONfilegroup|DEFAULT /*指定存儲text、ntext和image類型數(shù)據(jù)的文件組*/ 以CREATE TABLE語句創(chuàng)建用戶信

3、息表users 返回目錄5.1.4 5.1.4 修改表修改表1使用使用Enterprise ManagerEnterprise Manager修改表修改表2. 2. 使用使用ALTER TABLEALTER TABLE命令修改表命令修改表ALTER TABLE語句的語法格式如下所示 ：ALTER TABLE table_name ALTER COLUMN column_namenew_data_type(precision,scale) NULL|NOT NULL|ADD,n |DROPCONSTRATINTconstraint_name|COLUMN,n 返回目錄 table_name：此參

4、數(shù)是指定要修改的表的名稱。 ALTER COLUMN子句：是指定要進(jìn)行修改表中的字段的屬性。要修改的字段名由column_name給出。 new_data_type：為被修改字段的新的數(shù)據(jù)類型。 Precision：此參數(shù)是指定其數(shù)據(jù)類型的有效位數(shù)。 Scale：此參數(shù)是指定數(shù)據(jù)類型的小數(shù)位數(shù)。 NULL|NOT NULL：此參數(shù)是指定其字段是否可接受NULL。 ADD子句：向表中增加新字段。新字段的定義方法與CREATE TABLE語句中定義字段的方法相同。 DROP子句：從表中刪除字段或約束。COLUMN參數(shù)中指定的是被刪除的字段名，constraint_name是被刪除的約束名。 【例5

5、.2】在用戶信息表users中增加一個新字段sex，說明用戶的性別，并將username字段的長度由原來的20改為15。 返回目錄 5.1.4 5.1.4 修改表修改表 3使用使用DROP TABLEDROP TABLE命令刪除表命令刪除表 ALTER TABLE語句的語法格式如下所示：ALTER TABLE table_nameALTER COLUMN column_namenew_data_type(precision,scale)NULL|NOT NULL|ADD,n|DROPCONSTRATINTconstraint_name|COLUMN,n 返回目錄 3使用DROP TABLE命令

6、刪除表 DROP TABLE命令可以刪除一個表和表中的數(shù)據(jù)及其與表有關(guān)的所有索引、觸發(fā)器、約束、許可對象（與表相關(guān)的視圖和存儲過程，需要用 DROP VIEW 和 DROP PROCEDURE命令來刪除）。 DROP TABLE 命令的語法如下 DROP TABLE table_name 【例5.3】 刪除demo數(shù)據(jù)庫中的表users drop table demo.dbo.users 返回目錄5.2 添加數(shù)據(jù)信息添加數(shù)據(jù)信息可以通過企業(yè)管理器工具和T-SQL語句來進(jìn)行添加數(shù)據(jù)信息。5.2.1 通過企業(yè)管理器添加數(shù)據(jù)信息通過企業(yè)管理器添加數(shù)據(jù)信息5.2.2 使用使用T-SQL語句添加表數(shù)據(jù)語

7、句添加表數(shù)據(jù)INSERT語句通常有兩種形式：一種是添加一條記錄，另一種是添加子查詢的結(jié)果；后者可以一次添加多條記錄。INSERT語句語法格式如下所示： INSERT INTO table_name WITH ( .n) | view_name | rowset_function_limited (column_list) VALUES ( DEFAULT | NULL | expression ,.n ) | derived_table | execute_statement | DEFAULT VALUES返回目錄下面說明主要參數(shù)與子句的含義： INTO：用在INSERT關(guān)鍵字和目標(biāo)表之間的

8、可選關(guān)鍵字。table_name：指目標(biāo)數(shù)據(jù)表的表名。WITH ( .n)：指定一個或多個目標(biāo)表允許的表線索，可選。view_name：指定視圖名稱。rowset_function_limited：行集合函數(shù)限制 即使用 OPENQUERY或 OPENROWSET函數(shù)column_list：指定要添加數(shù)據(jù)的字段，字段之間用逗號隔開。VALUES：指定對應(yīng)于column_list的值DEFAULT指定 SQL Server 使用為此字段指定的缺省值Expression：指定一個常數(shù)、變量或表達(dá)式，表達(dá)式中不能含有SELECT或EXECUTE語句。derived_table：指定一個返回數(shù)據(jù)行的S

9、ELECT語句。execute_statement：指定一個返回SELECT或READTEXT語句的EXECUTE語句，其返回集必須與column_list 中指定的字段一致或兼容。DEFAULT VALUES：讓所有的字段使用缺省值作為添加數(shù)據(jù)。返回目錄 【例5.4】添加數(shù)據(jù)到用戶信息表users中use demo insert users (id, username, phone) values(20033506, zhang,使用T-SQL的INSERTSELECT語句來添加多條記錄。 【例5.5】將留言簿數(shù)據(jù)庫表guestbook_admin中的數(shù)據(jù)添加到gu

10、estbook_users用戶信息表。 use demo insert guestbook_users select username，Password from guestbook_admin select * from guestbook_users 返回目錄 5.3.15.3.1修改數(shù)據(jù)信息修改數(shù)據(jù)信息 其語法如下：UPDATE table_name WITH ( .n) | view_name | rowset_function_limited SET column_name = expression | DEFAULT | NULL | variable = expression |

11、 variable = column = expression ,.n FROM ,.n WHERE | WHERE CURRENT OF GLOBAL cursor_name | cursor_variable_name OPTION ( ,.n )返回目錄5.3 對已有數(shù)據(jù)進(jìn)行管理其中主要參數(shù)說明如下：table_name：此參數(shù)是用來指定要進(jìn)行修改記錄的表名。WHERE：此參數(shù)是用來設(shè)置篩選要進(jìn)行修改記錄的條件式。SET子句：用于指定更新的方法。FROM：此參數(shù)是用來指定提供修改作業(yè)條件的表名。column_name：指定要更新數(shù)據(jù)的字段。IDENTITY字段不能被更新。expressi

12、on：可以是常量、變量、表達(dá)式或返回單個值的子查詢。DEFAULT：指定使用已經(jīng)定義的缺省值作為字段的值。NULL：指定使用 NULL 值作為字段的值。 variable：事先定義的變量，用于存儲表達(dá)式的值。應(yīng)注意，SET variable = column = expression將變量和字段設(shè)定為與表達(dá)式相同的值，它與 SET variable = column, column = expression語句不同，后者將字段的值賦給變量后再將表達(dá)式的值賦給字段。 【例5.6】將users表內(nèi)的性別字段內(nèi)容，都改為“男”use demoUPDATE usersSET sex男where sex

13、女Go 【例5.7】修改users表內(nèi)的所有用戶的username后綴為user。 Use demoupdate usersset username = username +userGO 返回目錄5.3 對已有數(shù)據(jù)進(jìn)行管理 5.3.2 5.3.2 刪除數(shù)據(jù)信息刪除數(shù)據(jù)信息在TSQL語句中，提供了DELETE 和TRUNCATE TABLE命令來刪除表中的數(shù)據(jù)信息。DELETE語句的語法如下所示：DELETE FROM table_name WITH ( .n) | view_name | rowset_function_limited FROM ,.n WHERE | CURRENT OF G

14、LOBAL cursor_name | cursor_variable_name OPTION ( ,.n)返回目錄其中主要參數(shù)說明如下FROM：此參數(shù)為可選選項，用于連接DELETE關(guān)鍵字和要刪除數(shù)據(jù)的對象名稱。table_name：指定要刪除數(shù)據(jù)的表名。WITH ( .n)：指定一個或多個目標(biāo)表允許的表提示。view_name：指定用于刪除數(shù)據(jù)的視圖。rowset_function_limited：行集合函數(shù)限制。即使用OPENQUERY或OPENROWSET 函數(shù)。FROM ：指定一個額外的 FROM 子句。WHERE：指定限制數(shù)據(jù)刪除的條件。如果不指定WHERE子句，就會刪除表中的所有

15、數(shù)據(jù)。在WHERE子句中指定兩種形式的刪除操作：一種是搜索刪除，即使用搜索條件來限定要刪除的數(shù)據(jù)行。 另一種是定位刪除，即使用CURRENT OF子句來指定一個游標(biāo)，刪除操作會在游標(biāo)的當(dāng)前位置產(chǎn)生 定位刪除，比搜索刪除更精確。返回目錄【例5.8】刪除users表中Id為20033506的用戶記錄。 use demoDELETE FROM userswhere Id = 20033506 GO 【例5.9】刪除users表中所有用戶的信息記錄。 use demoDELETE FROM usersGO 返回目錄5.4 表的約束 約束(Constraint)是 Microsoft SQL Serve

16、r 提供的自動保持?jǐn)?shù)據(jù)庫完整性的一種方法。在SQL Server中創(chuàng)建一個表時，有5種約束類型： 主關(guān)鍵字約束（Primary Key Constraint） 外關(guān)鍵字約束（Foreign Key Constraint） 惟一性約束（Unique Constraint） 檢查約束（Check Constraint） 缺省約束（default Constraint）1. .主關(guān)鍵字約束主關(guān)鍵字約束也稱主鍵約束，能惟一地指定一行記錄。每一個主鍵字段的定義不允許為NULL屬性。一個表的定義只能有一個主鍵約束。定義主關(guān)鍵字約束的語法如下 CONSTRAINT constraint_name PRIMA

17、RY KEY CLUSTERED | NONCLUSTERED column_name1, column_name2,column_name16 返回目錄各參數(shù)說明如下：constraint_nam：指定約束的名稱。CLUSTERED|NONCLUSTERED：指定索引類別，CLUSTERED為缺省值。column_name：指定組成主關(guān)鍵字的字段名，主關(guān)鍵字最多由16個字段組成?！纠?.11】在留言簿數(shù)據(jù)庫（庫名為demo）中, 創(chuàng)建一個留言信息表guestbook_messages， 以留言編號為主關(guān)鍵字。CREATE TABLE dbo.guestbook_messages ( id b

18、igint IDENTITY (1, 1) NOT NULL , username char (20) COLLATE Chinese_PRC_CI_AS NULL , subject char (100) COLLATE Chinese_PRC_CI_AS NULL , content char (1000) COLLATE Chinese_PRC_CI_AS NULL , date datetime NULL constraint pk_ id primary key (id) ON PRIMARYGO返回目錄5.4 表的約束 2.外關(guān)鍵字約束 當(dāng)一個表中的一個字段或多個字段的組合和其它表

19、中的主關(guān)鍵字定義相同時，就可以將這些字段或字段的組合定義為外關(guān)鍵字。 作用：1.在更新字段值時,其它表中有與之相關(guān)聯(lián)的外關(guān)鍵字約束的表中的外關(guān)鍵字字段也將被相應(yīng)地做相同的更新. 2.插入數(shù)據(jù)時,如果與之相關(guān)聯(lián)的表的字段中無與插入的外關(guān)鍵字字段值相同的值時,系統(tǒng)會拒絕插入數(shù)據(jù). 定義外關(guān)鍵字約束的語法如下 CONSTRAINT constraint_name F O R E I G N K E Y c o l u m n _ n a m e 1 , column_name2,column_name16 REFERENCES ref_table ref_column1,ref_column2, r

20、ef_column16 ON DELETE CASCADE | NO ACTION ON UPDATE CASCADE | NO ACTION NOT FOR REPLICATION 返回目錄各參數(shù)說明如下 REFERENCES：指定要建立關(guān)聯(lián)的表的信息ref_table：指定要建立關(guān)聯(lián)的表的名稱ref_column指定要建立關(guān)聯(lián)的表中的相關(guān)字段的名稱ON DELETE CASCADE | NO ACTION：指定在刪除表中數(shù)據(jù)時，對關(guān)聯(lián)表所做的相關(guān)操作。ON UPDATE CASCADE | NO ACTION：指定在更新表中數(shù)據(jù)時，對關(guān)聯(lián)表所做的相關(guān)操作。 NOT FOR REPLICAT

21、ION：指定字段的外關(guān)鍵字約束在把從其它表中復(fù)制的數(shù)據(jù)插入到表中時不發(fā)生作用。 【例5.12】 創(chuàng)建一個留言用戶信息表guestbook_users，與前面創(chuàng)建的留言信息表相關(guān)聯(lián)。CREATE TABLE dbo.guestbook_users (userid bigint IDENTITY (1, 1) NOT NULL , username char (20) COLLATE Chinese_PRC_CI_AS NULL , password char (20) COLLATE Chinese_PRC_CI_AS NULL constraint pk_userid primary key

22、(userid) ,foreign key(id) references products(id) ON PRIMARYGO返回目錄 5.4 表的約束3. 惟一性約束惟一性約束指定一個或多個字段的組合的值具有惟一性，以防止在字段中輸入重復(fù)的值。惟一性約束指定的字段可以有NULL屬性。由于主關(guān)鍵字值是具有惟一性的，因此主關(guān)鍵字字段不能再設(shè)定惟一性約束。惟一性約束最多由16個字段組成，并且一個表最多可有250個唯一性約束。定義惟一性約束的語法如下 CONSTRAINT constraint_name UNIQUE CLUSTERED | NONCLUSTERED column_name1, col

23、umn_name2,column_name16 返回目錄【例5.13】創(chuàng)建一個用戶信息表，其中用戶的手機(jī)號碼具有惟一性。CREATE TABLE dbo.users ( id bigint IDENTITY (1, 1) NOT NULL , u s e r n a m e v a r c h a r ( 2 0 ) C O L L A T E Chinese_PRC_CI_AS NULL , p a s s w o r d v a r c h a r ( 2 0 ) C O L L A T E Chinese_PRC_CI_AS NULL , email varchar (50) COLLA

24、TE Chinese_PRC_CI_AS NULL , r e s u m e v a r c h a r ( 1 0 0 0 ) C O L L A T E Chinese_PRC_CI_AS NULL , phone varchar (20) COLLATE Chinese_PRC_CI_AS NULL constraint pk_id primary key (id),constraint uk_phone unique (phone) ON PRIMARYGO返回目錄4.檢查約束檢查約束對輸入字段或整個表中的值設(shè)置檢查條件，以限制輸入值，保證數(shù)據(jù)庫的數(shù)據(jù)完整性。 可以對每個字段設(shè)置符合