數(shù)據(jù)庫(kù)系統(tǒng)概論——數(shù)據(jù)定義語(yǔ)言DDL

1、 數(shù)據(jù)庫(kù)實(shí)驗(yàn)報(bào)告 題目： 數(shù)據(jù)庫(kù)定義語(yǔ)言DDL 姓名： 李軍毅 日期：2016-3-26實(shí)驗(yàn)?zāi)康?. 熟悉SQL語(yǔ)言中DDL的功能；2. 熟悉并掌握數(shù)據(jù)庫(kù)中各種完整性約束條件的作用和使用方法；3. 觀察和了解數(shù)據(jù)定義時(shí)數(shù)據(jù)字典的變化。實(shí)驗(yàn)平臺(tái)1. OS：Windows XP/72. DBMS：SQL Server 2008實(shí)驗(yàn)用時(shí)一次上機(jī)實(shí)驗(yàn)內(nèi)容1.創(chuàng)建數(shù)據(jù)庫(kù)school。2.創(chuàng)建數(shù)據(jù)表，同時(shí)在指定列上創(chuàng)建“主鍵”和“NULL/NOT NULL”完整性約束。3.為上述各表添加指定的完整性約束。4.查看表關(guān)系圖。5.索引的建立與刪除。6.刪除表和數(shù)據(jù)庫(kù)。7.關(guān)系數(shù)據(jù)庫(kù)中的簡(jiǎn)單查詢(xún)實(shí)驗(yàn)中出現(xiàn)的問(wèn)題

2、實(shí)驗(yàn)內(nèi)容1.創(chuàng)建數(shù)據(jù)庫(kù)school。2.創(chuàng)建數(shù)據(jù)表，同時(shí)在指定列上創(chuàng)建“主鍵”和“NULL/NOT NULL”完整性約束。學(xué)生信息表(Student)字段名數(shù)據(jù)類(lèi)型長(zhǎng)度可否為空備注Snovarchar5Not Null學(xué)號(hào)，主鍵SNamevarchar10Null姓名Sexvarchar2Null性別SbirthsmalldatetimeNull出生日期Sdeptvarchar20Null所在系名parentvarchar20Null家長(zhǎng)名字課程信息表(Course)字段名數(shù)據(jù)類(lèi)型長(zhǎng)度小數(shù)位可否為空備注Cnovarchar3Not Null課程號(hào)，主鍵CNamevarchar20Null課程名

3、稱(chēng)Cpnovarchar3Null先行課號(hào)CreditDecimal31Null學(xué)分教師信息表(Teacher)字段名數(shù)據(jù)類(lèi)型長(zhǎng)度小數(shù)位可否為空說(shuō)明TnoVarchar4Not Null教工號(hào)，主鍵Tnamevarchar10NULL姓名TsexChar2NULL性別TphoneVarchar12NULL電話TdeptVarchar20NULL系名TaddrVarchar30NULL家庭住址TprofVarchar10NULL職稱(chēng)TpayDecimal102NULL工資 學(xué)生選課表(SCT)字段名數(shù)據(jù)類(lèi)型長(zhǎng)度小數(shù)位可否為空備注IDIntNot Null編號(hào)，主鍵，Identity列Snovar

4、char5Not Null學(xué)號(hào)Cnovarchar3Not Null課程號(hào)Tnovarchar4Not Null教工號(hào)GradeDecimal51NULL成績(jī)1）創(chuàng)建學(xué)生信息表：create table Student(Sno varchar(5) primary key, Sname varchar(10) null, Sex varchar(2) null, Sbirth smalldatetime null, Sdept varchar(20) null, parent varchar(20) null, );如圖：2）創(chuàng)建課程信息表：create table Course(Cno va

5、rchar(3) primary key, Cname varchar(20) null, Cpno varchar(3) null, Credit Decimal(3,1) null, );如圖：3）創(chuàng)建教師信息表：create table Teacher(Tno varchar(4) primary key, Tname varchar(10) null, Tsex char(2) null, Tphone varchar(12) null, Tdept varchar(20) null, Taddr varchar(30) null, Tprof varchar(10) null, Tp

6、av Decimal(10,2) null );如圖：4）創(chuàng)建學(xué)生選課表：create table SCT(ID int identity primary key, Sno varchar(5) null, Cno varchar(3) null, Tno varchar(4) null, Grade Decimal(5,1) null, );如圖：1）實(shí)體完整性 增加表Course中屬性列CName必須取唯一值(Unique)的約束條件。Unique約束語(yǔ)法結(jié)構(gòu)如下：ALTER TABLE ADDCONSTRAINT UNIQUE CLUSTERED|NONCLUSTERED ( , ) a

7、lter table Course add Unique(Cname);如圖：2）域完整性 i.為表Teacher的屬性列Tsex設(shè)置默認(rèn)值為男。Default約束語(yǔ)法結(jié)構(gòu)如下：ALTER TABLE ADD CONSTRAINT DEFAULT FOR alter table Teacher add default 男 for Tsex;如圖： ii.限制表SCT的屬性列Grade的取值范圍為 0= Grade =100。Check約束語(yǔ)法結(jié)構(gòu)如下：ALTER TABLE ADD CONSTRAINT CHECK () alter table SCT add check (Grade=0 a

8、nd Grade=100);如圖： 3）參照完整性 為表SCT的屬性列Sno、Cno、Tno分別添加外鍵約束，參照關(guān)系如下表所示：參照列被參照列SCT.SnoStudent.SnoSCT.CnoCourse.CnoSCT.TnoTeacher.TnoForeign Key約束語(yǔ)法結(jié)構(gòu)如下：ALTER TABLE ADD CONSTRAINT FOREIGN KEY ( , ) REFERENCES ( , )如圖：通過(guò)圖形界面做以下操作，實(shí)驗(yàn)是否能成功并思考結(jié)果產(chǎn)生的原因：1. 在學(xué)生信息表中添加一名學(xué)號(hào)暫未分配（即學(xué)號(hào)為NULL）的學(xué)生。 Insert Into Student(Sno) V

9、alue(“null”);失敗原因：Sno是primary key,不允許為空2.在學(xué)生信息表中添加兩名學(xué)號(hào)完全相同的學(xué)生。insertinto Student (Sno)values (1234),(1234);失敗原因：Sno是primary key,不允許重復(fù)3.在教師信息表中插入一名不指定性別的教師。 Insert Into Teacher(Tno,Tname,Tphone,Tdept,Tprof,Tpay) Value(“1234”,“李強(qiáng)”，，“北京”，“教授”，“5000”)；4.將課程信息表中的一個(gè)課程的課程號(hào)改為6位數(shù)。 首先往課程信息表中添加一條數(shù)

10、據(jù)：insertinto Course (Cno,Cname,Cpno,Credit)values (123,數(shù)據(jù)庫(kù),234,2.3);再更改：update Courseset Cno=123456where Cno=123;失敗原因：課程信息表中規(guī)定Cno最大長(zhǎng)度為3位5.在學(xué)生選課表中插入一條對(duì)應(yīng)學(xué)號(hào)在學(xué)生信息表中不存在的選課信息。首先往學(xué)生信息表中插入一條數(shù)據(jù)：insert into Student(Sno,Sname,Sex,Sbirth,Sdept,parent)values(12345,王五,男,1995-10-2,CS,王強(qiáng));然后再往學(xué)生選課表中插入：insertinto SC

11、T(ID,Sno,Cno,Tno,Grade)values (1,12346,123456,1234,99.5);失敗原因：Sno是學(xué)生選課表的一個(gè)外鍵，參照學(xué)生信息表的Sno，如果學(xué)生信息表中不存在此學(xué)號(hào)的學(xué)生，則會(huì)出錯(cuò)。上面是嘗試用SQL語(yǔ)句做，下面是用圖形化界面做的6.在學(xué)生選課表中正確插入一條記錄后，在課程信息表中刪除對(duì)應(yīng)的課程。7.刪除SCT的一條記錄，插入下一條記錄時(shí)復(fù)用這條被刪除記錄的ID。8.修改學(xué)生選課表一條記錄的成績(jī)?yōu)?50。4.查看表關(guān)系圖?？梢酝ㄟ^(guò)SSMS來(lái)以可視化的方式查看數(shù)據(jù)庫(kù)school中各個(gè)表之間的關(guān)系。方法如下：展開(kāi)數(shù)據(jù)庫(kù)school上，在【數(shù)據(jù)庫(kù)關(guān)系圖】上單

12、擊右鍵，選擇【新建數(shù)據(jù)庫(kù)關(guān)系圖】命令；在【添加表】選項(xiàng)框中選中所有表，然后單擊【添加】、【關(guān)閉】。5.索引的建立與刪除。1)新建表test； create table test ( ID int null, Name varchar(10) null, Age int null )；2) 在表test中插入如下數(shù)據(jù)（可通過(guò)圖形化界面方式完成）；IDNameAge2Susan201John194Mary253John203)在屬性列“Name”上建唯一索引，看能否成功，并分析原因。 create unique index Name_index on test(Name);失敗原因：因?yàn)樵诒碇?，?/p>

13、兩條Name相同的數(shù)據(jù)，所以不能建唯一索引。4)首先在屬性列“ID”建聚簇索引，然后在屬性列“Age”上再次建聚簇索引，看能否成功，并分析原因。如果在列“Age”上建的是非聚簇索引呢？ create clustered index ID_index on test(ID); create clustered index Age_index on test(Age);失敗原因：無(wú)法對(duì)一個(gè)表建多個(gè)聚簇索引若建非聚簇索引：create nonclusteredindex Age_indexon test(Age);創(chuàng)建索引的sql語(yǔ)法結(jié)構(gòu)：CREATE UNIQUECLUSTERED|NONCLUS

14、TEREDINDEXON (ASC|DSC , ASC|DSC)刪除索引的sql語(yǔ)法結(jié)構(gòu)：DROP INDEX.6.刪除表和數(shù)據(jù)庫(kù)。1）刪除表Course； DROP TABLE Course;2）刪除school數(shù)據(jù)庫(kù)。DROP DATABASE school;7. 關(guān)系數(shù)據(jù)庫(kù)中的簡(jiǎn)單查詢(xún)?cè)O(shè)有一個(gè)SPJ數(shù)據(jù)庫(kù)，包括S，P，J，SPJ四個(gè)關(guān)系模式各個(gè)關(guān)系結(jié)構(gòu)如下：（可參考第二章課后習(xí)題5的題干）供應(yīng)商表 SSNOSNAMESTATUSCITY備注S1精益20天津S2盛錫10北京S3東方紅30北京S4豐泰盛20天津S5為民30上海零件表 PPNOPNAMECOLORWEIGHT備注P1螺母紅12

15、P2螺栓綠17P3螺絲刀藍(lán)14P4螺絲刀紅14P5凸輪藍(lán)40P6齒輪紅30工程項(xiàng)目表 JJNOJNAMECITY備注J1三建北京J2一汽長(zhǎng)春J3彈簧廠天津J4造船廠天津J5機(jī)車(chē)廠唐山J6無(wú)線電廠常州J7半導(dǎo)體廠南京供應(yīng)情況表 SPJSNOPNOJNOQTY備注S1P1J1200S1P1J3100S1P1J4700S1P2J2100S2P3J1400S2P3J2200S2P3J4500S2P3J5500S2P5J1400S2P5J2100S3P1J1200S3P3J1200S4P5J1100S4P6J3300S4P6J4200S5P2J4100S5P3J1200S5P6J2200S5P6J450

16、0首先建立這4個(gè)表格，并依此添加數(shù)據(jù)：S表：P表：J表：SPJ表：1) 求供應(yīng)工程J1零件的供應(yīng)商號(hào)碼SNOSELECT DISTINCT SNOFROM SPJWHERE SPJ.JNO=J1;2） 求供應(yīng)工程J1零件P1的供應(yīng)商號(hào)碼SNOSELECT DISTINCT SNOFROM SPJWHERE SPJ.JNO=J1 AND SPJ.PNO=P1;3）求供應(yīng)工程J1零件為紅色的供應(yīng)商號(hào)碼SNOSELECT DISTINCT SNOFROM SPJ,P,JWHERE SPJ.JNO=J1 AND SPJ.PNO=P.PNO AND P.COLOR=紅;4） 求沒(méi)有使用天津供應(yīng)商生產(chǎn)的紅色零件的工程號(hào)JNOSELECT DISTINCT SPJ.JNOFROM SPJ,S,P,JWHERE SPJ.JNO NOT IN (SELECT SPJ.JNO FROM SPJ,S,P,JWHERE SPJ.PNO=P.PNO AND P.COLOR=紅 AND SPJ.SNO