資料庫表的建立、管理和資料操作(實驗一)_MsSql

今天我們就以實驗的形式對錶的建立、管理和資料操作進行學習，上課吧。

【實驗目的】：瞭解SQL語言的使用，進一步理解關係運算，鞏固資料庫的基礎知識。
【實驗要求】：利用SQL語言進行資料庫表的各種操作：
1．資料庫表的建立、修改和刪除操作。
2．向表中進行資料的插入、刪除和修改操作。
【實驗內容】
1. 利用資料定義語句在實驗一建立的stu_DB庫中建立學生管理系統的三個表：Student、Course、SC。
2．利用INSERT、UPDATE和DELETE語句對上述三張表的資料進行插入、更新和刪除操作。
3．在stu_DB庫中添加Teacher和TC表，對已建立的學生管理系統中的表添加充足的資料(每個表不少於20條)，以便完成本實驗的後繼操作。
【實驗步驟】
一、資料定義
(一)表的建立
在實驗一建立的資料庫stu_DB中分別用企業管理器和查詢分析器，按下面的表結構建立學生管理系統的表。

1．利用企業管理器建立表

（1）開啟企業管理器。

（2）選中實驗一建立好的資料庫stu_DB，單擊資料庫中的表對象，然後右擊視窗右側選擇建立表，彈出如圖所示表單。

 

（3）在這個表單中，列名列就示表的欄位名，可以在這個表單中為欄位選擇資料類型和長度以及是否可以為空白值。

（4）在此處可以為表選擇檔案組，在表中單擊右鍵，選擇屬性，如果已經為資料庫建立了一個次要組，可以在屬性表單的表檔案組中為此表選擇檔案組。應該把一些競爭使用的表放於不同的檔案組中，並且讓檔案組屬於不同磁碟，這樣可以在表競爭讀寫時提高並發效能。

（5）建立完成後，單擊磁碟表徵圖，並為表取一個名字。注意，應該為表取一個有意義的名字。

2．用Transact_SQL語句建立表

  文法為：

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】產生一個表名為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 ('電腦系'),)

【例2】  建立一張名為Ta1的表，此表中有3列，第一列Pid定義為主鍵，並且自動成長。第二列Name預設值為Unknown，第三列定義一個約束(日期不能大於輸入當天的日期)，插入一條記錄並進行查詢，顯示如下圖。

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

(二)表的修改
1.用企業管理器修改表
 （1）右鍵點擊所要修改的學生表，選擇“設計表”，會彈出如圖3-4所示的表單，在這個表單中可以更改資料表的欄位。在空白處點右鍵，選擇“屬性”，彈出另一表單，在此可以更改約束，也可以增加約束。

2、利用Transact-SQL語句修改表
向表中添加新的欄位：在學生表中添加一個“班級”欄位，資料類型為字元型。
ALTER TABLE student  ADD class CHAR(6)

刪除表中的舊列：將學生表中的“Sdept”欄位刪除。
ALTER TABLE student DROP COLUMN Sdept

更改表以添加具有約束的列。(sp_help：用於顯示參數清單和其資料類型)
給學生表的增加“grade”欄位並加上CHECK約束，讓其不可以大於100。
ALTER TABLE student ADD grade int CONSTRAINT ch_grade CHECK(grade<100)
EXEC sp_help ch_grade

給學生表中添加“birthday”欄位，並且這個日期不能在錄入當天的日期之後。
ALTER TABLE student ADD birthday DATETIME NULL
CONSTRAINT ch_birthday CHECK(birthday<getdate())

添加具有預設值的可為空白的列：
在學生表中加入“matriculationday”欄位，並且這一欄位的預設值為錄入當天的日期。
ALTER TABLE  student ADD matriculationday smalldatetime NULL
CONSTRAINT adddateflt  DEFAULT getdate() 

請參考上面所給的例子：
自行設計表結構修改操作（不少於8個語句），並進行實驗驗證。
修改表student，結果如表1-4。
建立表1-5、表1-6，為後續實驗建立基礎。

(三)表的刪除
可以在企業管理器中選擇要刪除的表直接刪除，也可以通過Transact-SQL語句DROP 刪除表的定義及表中的所有資料、索引、出發器、約束和許可權規範。
如要刪除ta1表，語句如下： DROP TABLE ta1
請參考上面的例子完成對錶Student 、Course 、SC的刪除操作，它們刪除的次序如何？並進行實驗驗證。

二、資料操作

（一）查看、重新命名及刪除使用者定義的資料類型
1．使用企業管理器建立一個名為newtype1、長度為6、可變長字元、允許為空白的自訂資料類型。
進入企業管理器，進入stu_DB資料庫，用滑鼠右鍵單擊“使用者定義的資料類型”後選擇“操作”菜單中的“建立使用者定義資料類型”或點擊“建立”按扭，將彈出下圖所示表單：

輸入要定義的資料名稱newtype1，選擇資料類型varCHAR，輸入長度為6，在“允許NULL值”複選框中打個“√”，單擊“確定”按鈕即可。
2.使用T-SQL語句建立一個名為newtype2，資料長度為6，定長字元型，不允許為空白的自訂資料類型。

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

3.命名使用者自訂的資料類型
（1） 使用系統預存程序sp_rename將自訂資料類型newtype1重新命名為a1。
            EXEC sp_rename newtype1, a1 
（2） 使用企業管理器再將自訂資料類型a1重新命名為newtype1。
4．刪除使用者自訂資料類型
（1） 使用系統預存程序sp_droptype來刪除使用者自訂的資料類型。
             EXEC sp_droptype newtype1
（2）使用企業管理器刪除使用者自訂資料類型
進入企業管理器，進入stu_DB資料庫，單擊“使用者定義的資料類型”後，右邊視窗將出現所有使用者定義的資料類型，再用滑鼠右鍵單擊newtype2，在彈出的捷徑功能表上單擊“刪除”按鈕即可。
注意：正在被表或其他資料庫物件使用的使用者定義型別能不能刪除？請進行實驗驗證。
（二）資料的更新
1. 利用企業管理器更新資料
開啟企業管理器，右鍵點擊所要修改的表，選擇“開啟表”，單擊“返回所有行”，會彈出如圖3-5所示的表單，在該表單中可以修改表中的資料內容。這裡的修改內容是自動儲存的，修改完之後，直接關閉該表單，資料內容即修改成功。

2.利用查詢分析器更新資料
在關聯式資料庫中，常見的資料更新的語句有三條：INSERT、UPDATE和DELETE
（1）INSERT語句
向student表中插入一行資料，具體資料如下：
學號：04265005, 姓名：劉輝, 性別：男,年齡：21, 系別：電腦系

USE stu_DBINSERT INTO student (Sno, Sname, Ssex, Sage, Sdept) VALUES (‘04265005', ‘劉輝', ‘男', 21, ‘電腦系')INSERT INTO student (Sno, Sname, Ssex, Sage) VALUES (‘04265006', ‘李慧', ‘女', 21)

向ta1表中插入資料如下：

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

請根據上述例子，自行設計不同情況下的資料插入語句（不少於10個語句），並進行實驗驗證，驗證要求如下：
不滿足唯一性限制式的資料的插入。
不滿足使用者自訂約束的資料的插入。
不滿足外鍵約束的資料的插入。
利用預設值的資料的插入。
不合理的資料是否能進入資料庫。

（2）UPDATE語句
更改（學生）表資料，將學號為‘04265005'的學生的所在系改成‘機械工程系'。

UPDATE student SET Sdept=‘機械繫' WHERE Sno=‘04265005'

請根據上述例子，自行設計資料修改語句（不少於10個語句），並進行實驗驗證，驗證要求同INSERT語句。

（3）DELETE語句刪除行
刪除student表中的學號為‘04265005'的記錄。

DELETE FROM student WHERE Sno =‘04265005'

如果想清除表中的所有資料但不刪除這個表，可以使用TRUNCATE TABLE語句。該語句相當於沒有條件的DELETE語句，並且該語句不記錄日誌。

請根據上述例子，自行設計不同情況的資料刪除語句（不少於10條語句），並進行實驗驗證不滿足外鍵約束的資料刪除操作。

對資料定義中建立的學生管理系統（Student， Course，SC，Teacher，TC表）的每個表添加充足的資料(每個表不少於20條)，以便完成本實驗的後繼操作。具體表資訊可參考如下：

今天的實驗課就結束了，小編帶大家大致的瞭解了表的建立、管理和資料操作，下次就進行實戰演練，我們繼續一起學習。

以上就是表的建立和管理實驗的全部內容，希望對大家的學習有所協助。