數(shù)據(jù)庫表的創(chuàng)建、管理和數(shù)據(jù)操作(實(shí)驗(yàn)一)

今天我們就以實(shí)驗(yàn)的形式對(duì)表的創(chuàng)建、管理和數(shù)據(jù)操作進(jìn)行學(xué)習(xí)，上課吧。

【實(shí)驗(yàn)?zāi)康摹浚?/span>了解SQL語言的使用，進(jìn)一步理解關(guān)系運(yùn)算，鞏固數(shù)據(jù)庫的基礎(chǔ)知識(shí)。
【實(shí)驗(yàn)要求】：利用SQL語言進(jìn)行數(shù)據(jù)庫表的各種操作：
1．?dāng)?shù)據(jù)庫表的創(chuàng)建、修改和刪除操作。
2．向表中進(jìn)行數(shù)據(jù)的插入、刪除和修改操作。
【實(shí)驗(yàn)內(nèi)容】
1. 利用數(shù)據(jù)定義語句在實(shí)驗(yàn)一創(chuàng)建的stu_DB庫中建立學(xué)生管理系統(tǒng)的三個(gè)表：Student、Course、SC。
2．利用INSERT、UPDATE和DELETE語句對(duì)上述三張表的數(shù)據(jù)進(jìn)行插入、更新和刪除操作。
3．在stu_DB庫中添加Teacher和TC表，對(duì)已建立的學(xué)生管理系統(tǒng)中的表添加充足的數(shù)據(jù)(每個(gè)表不少于20條)，以便完成本實(shí)驗(yàn)的后繼操作。
【實(shí)驗(yàn)步驟】
一、數(shù)據(jù)定義
(一)表的創(chuàng)建
在實(shí)驗(yàn)一創(chuàng)建的數(shù)據(jù)庫stu_DB中分別用企業(yè)管理器和查詢分析器，按下面的表結(jié)構(gòu)創(chuàng)建學(xué)生管理系統(tǒng)的表。

1．利用企業(yè)管理器創(chuàng)建表

（1）打開企業(yè)管理器。

（2）選中實(shí)驗(yàn)一創(chuàng)建好的數(shù)據(jù)庫stu_DB，單擊數(shù)據(jù)庫中的表對(duì)象，然后右擊窗口右側(cè)選擇新建表，彈出如圖所示窗體。

（3）在這個(gè)窗體中，列名列就示表的字段名，可以在這個(gè)窗體中為字段選擇數(shù)據(jù)類型和長(zhǎng)度以及是否可以為空值。

（4）在此處可以為表選擇文件組，在表中單擊右鍵，選擇屬性，如果已經(jīng)為數(shù)據(jù)庫創(chuàng)建了一個(gè)次要組，可以在屬性窗體的表文件組中為此表選擇文件組。應(yīng)該把一些競(jìng)爭(zhēng)使用的表放于不同的文件組中，并且讓文件組屬于不同磁盤，這樣可以在表競(jìng)爭(zhēng)讀寫時(shí)提高并發(fā)性能。

（5）創(chuàng)建完成后，單擊磁盤圖標(biāo)，并為表取一個(gè)名字。注意，應(yīng)該為表取一個(gè)有意義的名字。

2．用Transact_SQL語句創(chuàng)建表

  語法為：

CREATE TABLE 
 [ database_name.[owner].] table_name
 ( {column_name data_type [DEFAULT ‘default_value']|
 [CONSTRAINT CONSTRAINT_name]
 }, […n] [IDENTITY [(seed, increment )]]
 )
 [ON { filegroup | DEFAULT }]
 [TEXTIMAGE_ON {filegroup | DEFAULT }]

【例1】生成一個(gè)表名為student的表

create table student
(
 sno CHAR(8) primary key,
 sname CHAR(10) not null,
 ssex CHAR (2) check (ssex='男'or ssex='女'),
 sage smallint,
 sdept CHAR (20) default ('計(jì)算機(jī)系'),
)

【例2】  創(chuàng)建一張名為Ta1的表，此表中有3列，第一列Pid定義為主鍵，并且自動(dòng)增長(zhǎng)。第二列Name默認(rèn)值為Unknown，第三列定義一個(gè)約束(日期不能大于輸入當(dāng)天的日期)，插入一條記錄并進(jìn)行查詢，顯示如下圖。

CREATE TABLE ta1 
( 
pid int identity(1,1) primary key, 
[name] CHAR(10) default ('unknow'),
birthday datetime CHECK( birthday getdate() )
)

(二)表的修改
1.用企業(yè)管理器修改表
 （1）右鍵點(diǎn)擊所要修改的學(xué)生表，選擇“設(shè)計(jì)表”，會(huì)彈出如圖3-4所示的窗體，在這個(gè)窗體中可以更改數(shù)據(jù)表的字段。在空白處點(diǎn)右鍵，選擇“屬性”，彈出另一窗體，在此可以更改約束，也可以增加約束。

2、利用Transact-SQL語句修改表
向表中添加新的字段：在學(xué)生表中添加一個(gè)“班級(jí)”字段，數(shù)據(jù)類型為字符型。
ALTER TABLE student  ADD class CHAR(6)

刪除表中的舊列：將學(xué)生表中的“Sdept”字段刪除。
ALTER TABLE student DROP COLUMN Sdept

更改表以添加具有約束的列。(sp_help：用于顯示參數(shù)清單和其數(shù)據(jù)類型)
給學(xué)生表的增加“grade”字段并加上CHECK約束，讓其不可以大于100。
ALTER TABLE student ADD grade int CONSTRAINT ch_grade CHECK(grade100)
EXEC sp_help ch_grade

給學(xué)生表中添加“birthday”字段，并且這個(gè)日期不能在錄入當(dāng)天的日期之后。
ALTER TABLE student ADD birthday DATETIME NULL
CONSTRAINT ch_birthday CHECK(birthdaygetdate())

添加具有默認(rèn)值的可為空的列：
在學(xué)生表中加入“matriculationday”字段，并且這一字段的默認(rèn)值為錄入當(dāng)天的日期。
ALTER TABLE  student ADD matriculationday smalldatetime NULL
CONSTRAINT adddateflt  DEFAULT getdate() 

請(qǐng)參考上面所給的例子：
自行設(shè)計(jì)表結(jié)構(gòu)修改操作（不少于8個(gè)語句），并進(jìn)行實(shí)驗(yàn)驗(yàn)證。
修改表student，結(jié)果如表1-4。
創(chuàng)建表1-5、表1-6，為后續(xù)實(shí)驗(yàn)建立基礎(chǔ)。

(三)表的刪除
可以在企業(yè)管理器中選擇要?jiǎng)h除的表直接刪除，也可以通過Transact-SQL語句DROP 刪除表的定義及表中的所有數(shù)據(jù)、索引、出發(fā)器、約束和權(quán)限規(guī)范。
如要?jiǎng)h除ta1表，語句如下： DROP TABLE ta1
請(qǐng)參考上面的例子完成對(duì)表Student 、Course 、SC的刪除操作，它們刪除的次序如何？并進(jìn)行實(shí)驗(yàn)驗(yàn)證。

二、數(shù)據(jù)操作

（一）查看、重命名及刪除用戶定義的數(shù)據(jù)類型
1．使用企業(yè)管理器創(chuàng)建一個(gè)名為newtype1、長(zhǎng)度為6、可變長(zhǎng)字符、允許為空的自定義數(shù)據(jù)類型。
進(jìn)入企業(yè)管理器，進(jìn)入stu_DB數(shù)據(jù)庫，用鼠標(biāo)右鍵單擊“用戶定義的數(shù)據(jù)類型”后選擇“操作”菜單中的“新建用戶定義數(shù)據(jù)類型”或點(diǎn)擊“新建”按扭，將彈出下圖所示窗體：

輸入要定義的數(shù)據(jù)名稱newtype1，選擇數(shù)據(jù)類型varCHAR，輸入長(zhǎng)度為6，在“允許NULL值”復(fù)選框中打個(gè)“√”，單擊“確定”按鈕即可。
2.使用T-SQL語句創(chuàng)建一個(gè)名為newtype2，數(shù)據(jù)長(zhǎng)度為6，定長(zhǎng)字符型，不允許為空的自定義數(shù)據(jù)類型。

USE stu_DB
EXEC sp_addtype newtype2, ‘CHAR(6)', ‘not null'

3.命名用戶自定義的數(shù)據(jù)類型
（1） 使用系統(tǒng)存儲(chǔ)過程sp_rename將自定義數(shù)據(jù)類型newtype1重新命名為a1。
            EXEC sp_rename newtype1, a1 
（2） 使用企業(yè)管理器再將自定義數(shù)據(jù)類型a1重新命名為newtype1。
4．刪除用戶自定義數(shù)據(jù)類型
（1） 使用系統(tǒng)存儲(chǔ)過程sp_droptype來刪除用戶自定義的數(shù)據(jù)類型。
             EXEC sp_droptype newtype1
（2）使用企業(yè)管理器刪除用戶自定義數(shù)據(jù)類型
進(jìn)入企業(yè)管理器，進(jìn)入stu_DB數(shù)據(jù)庫，單擊“用戶定義的數(shù)據(jù)類型”后，右邊窗口將出現(xiàn)所有用戶定義的數(shù)據(jù)類型，再用鼠標(biāo)右鍵單擊newtype2，在彈出的快捷菜單上單擊“刪除”按鈕即可。
注意：正在被表或其他數(shù)據(jù)庫對(duì)象使用的用戶定義類型能不能刪除？請(qǐng)進(jìn)行實(shí)驗(yàn)驗(yàn)證。
（二）數(shù)據(jù)的更新
1. 利用企業(yè)管理器更新數(shù)據(jù)
打開企業(yè)管理器，右鍵點(diǎn)擊所要修改的表，選擇“打開表”，單擊“返回所有行”，會(huì)彈出如圖3-5所示的窗體，在該窗體中可以修改表中的數(shù)據(jù)內(nèi)容。這里的修改內(nèi)容是自動(dòng)存儲(chǔ)的，修改完之后，直接關(guān)閉該窗體，數(shù)據(jù)內(nèi)容即修改成功。

2.利用查詢分析器更新數(shù)據(jù)
在關(guān)系數(shù)據(jù)庫中，常見的數(shù)據(jù)更新的語句有三條：INSERT、UPDATE和DELETE
（1）INSERT語句
向student表中插入一行數(shù)據(jù)，具體數(shù)據(jù)如下：
學(xué)號(hào)：04265005, 姓名：劉輝, 性別：男,年齡：21, 系別：計(jì)算機(jī)系

USE stu_DB
INSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) 
VALUES (‘04265005', ‘劉輝', ‘男', 21, ‘計(jì)算機(jī)系')
INSERT INTO student (Sno, Sname, Ssex, Sage) 
VALUES (‘04265006', ‘李慧', ‘女', 21)

向ta1表中插入數(shù)據(jù)如下：

INSERT ta1([name],birthday) values (‘lan','1977-03-02')
INSERT ta1 values (‘lan','1977-03-02')
INSERT ta1(birthday) values (‘1977-03-02')

請(qǐng)根據(jù)上述例子，自行設(shè)計(jì)不同情況下的數(shù)據(jù)插入語句（不少于10個(gè)語句），并進(jìn)行實(shí)驗(yàn)驗(yàn)證，驗(yàn)證要求如下：
不滿足唯一性約束的數(shù)據(jù)的插入。
不滿足用戶自定義約束的數(shù)據(jù)的插入。
不滿足外鍵約束的數(shù)據(jù)的插入。
利用缺省值的數(shù)據(jù)的插入。
不合理的數(shù)據(jù)是否能進(jìn)入數(shù)據(jù)庫。

（2）UPDATE語句
更改（學(xué)生）表數(shù)據(jù)，將學(xué)號(hào)為‘04265005'的學(xué)生的所在系改成‘機(jī)械工程系'。

UPDATE student SET Sdept=‘機(jī)械系' WHERE Sno=‘04265005'

請(qǐng)根據(jù)上述例子，自行設(shè)計(jì)數(shù)據(jù)修改語句（不少于10個(gè)語句），并進(jìn)行實(shí)驗(yàn)驗(yàn)證，驗(yàn)證要求同INSERT語句。

（3）DELETE語句刪除行
刪除student表中的學(xué)號(hào)為‘04265005'的記錄。

DELETE FROM student WHERE Sno =‘04265005'

如果想清除表中的所有數(shù)據(jù)但不刪除這個(gè)表，可以使用TRUNCATE TABLE語句。該語句相當(dāng)于沒有條件的DELETE語句，并且該語句不記錄日志。

請(qǐng)根據(jù)上述例子，自行設(shè)計(jì)不同情況的數(shù)據(jù)刪除語句（不少于10條語句），并進(jìn)行實(shí)驗(yàn)驗(yàn)證不滿足外鍵約束的數(shù)據(jù)刪除操作。

對(duì)數(shù)據(jù)定義中創(chuàng)建的學(xué)生管理系統(tǒng)（Student， Course，SC，Teacher，TC表）的每個(gè)表添加充足的數(shù)據(jù)(每個(gè)表不少于20條)，以便完成本實(shí)驗(yàn)的后繼操作。具體表信息可參考如下：

今天的實(shí)驗(yàn)課就結(jié)束了，小編帶大家大致的了解了表的創(chuàng)建、管理和數(shù)據(jù)操作，下次就進(jìn)行實(shí)戰(zhàn)演練，我們繼續(xù)一起學(xué)習(xí)。

以上就是表的創(chuàng)建和管理實(shí)驗(yàn)的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助。