实验2 数据定义——基本表

来源：九壹网

贵州大学计算机科学与技术学院

软件工程专业实验报告

题目数据定义——基本表《数据库原理》实验报告姓名班级日期实验环境：操作系统 Microsoft Windows 7 (位/Service Pack 1) CPU (英特尔)Intel(R) Core(TM) i7-4710MQ CPU @ 2.50GHz(2501 MHz) 内存 8.00 GB ( 1600 MHz) 数据库 Microsoft SQL Server 2012 实验内容与完成情况：一、实验目的本次实验了解DDL语言的CREATE、DROP、ALTER对表进行操作，学会在SQL Server 2012中使用DDL语言进行对表的创建、删除和改动。二、实验内容 1.在文本编译窗口编写SQL语句，将下列数据分别插入Student，Course，SC和Teacher表中，操作方法同上。 CREATE TABLE Student( Sno VARCHAR(9) PRIMARY KEY, Sname VARCHAR(20) UNIQUE, Ssex VARCHAR(2) CHECK (Ssex='男'or Ssex='女'), Sage SMALLINT , Sdept VARCHAR(20), ) 1

CREATE TABLE Course( Cno VARCHAR(4) PRIMARY KEY, Cname VARCHAR(40), Cpno VARCHAR(4) , Ccredit SMALLINT , FOREIGN KEY (Cno) REFERENCES Course(Cno) ) CREATE TABLE SC( Sno VARCHAR(9) , Cno VARCHAR(4), Grade SMALLINT CHECK (Grade>=0 AND Grade<=100) , PRIMARY KEY (SnO,Cno), FOREIGN KEY (Sno,Cno) REFERENCES SC(Sno,Cno)

) CREATE TABLE Teacher( Tno Varchar(6) PRIMARY KEY, Tname Varchar(20), Tsex VARCHAR(2) CHECK (Tsex='男'or Tsex='女'), Tage INT, Tdept VARCHAR(20), Ttitles VARCHAR(20), Twage INT, Tdno VARCHAR(6), FOREIGN KEY (Tdno) REFERENCES Teacher(Tno), ) 3

2.在数据里面插入数据； INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215121','李勇','男',20,'CS') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215122','刘晨','女',19,'CS') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215123','王敏','女',18,'MA') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215125','张立','男',19,'IS') INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES('200215126','欧阳丽','女',21,'FL') INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('1','数据库',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('2','数学',NULL,2) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('3','信息系统',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('4','操作系统',NULL,3) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('5','数据结构',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('6','数据处理',NULL,2) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('7','PASCAL',NULL,4) INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES('8','DB_Design',NULL,2) UPDATE Course SET Cpno='5' WHERE Cno='1'

UPDATE Course SET Cpno='1' WHERE Cno='3' UPDATE Course SET Cpno='6' WHERE Cno='4' UPDATE Course SET Cpno='7' WHERE Cno='5' UPDATE Course SET Cpno='6' WHERE Cno='7' UPDATE Course SET Cpno='1' WHERE Cno='8' INSERT SC(Sno,Cno,Grade) VALUES('200215121','1',92) INSERT SC(Sno,Cno,Grade) VALUES('200215121','2',85) INSERT SC(Sno,Cno,Grade) VALUES('200215121','3',88) INSERT SC(Sno,Cno,Grade) VALUES('200215122','2',90) INSERT SC(Sno,Cno,Grade) VALUES('200215122','3',80) INSERT SC(Sno,Cno,Grade) VALUES('200215122','1',NULL) INSERT SC(Sno,Cno,Grade) VALUES('200215123','2',50) INSERT SC(Sno,Cno,Grade) VALUES('200215123','3',70) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110001','钟灵','女',27,'CS','讲师',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110002','杨毅','男',42,'CS','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110003','周倩','女',25,'CS','讲师',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('110005','陈文茂','男',48,'CS','教授',4000,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120001','江南','男',30,'IS','副教授',3500,NULL)

INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120002','刘洋','男',28,'IS','讲师',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120003','汪明','男',44,'IS','教授',4000,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('120004','张蕾','女',35,'IS','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130001','邹佳羽','女',25,'MA','讲师',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130002','王力','男',30,'MA','讲师',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130003','王小峰','男',35,'MA','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('130004','魏昭','男',40,'MA','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140001','王力','男',32,'FL','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140002','张小梅','女',27,'FL','讲师',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140003','吴娅','女',27,'FL','讲师',2800,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140004','陈姝','女',35,'FL','副教授',3500,NULL) INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno) VALUES('140005','周斌','男',44,'FL','教授',3500,NULL) UPDATE Teacher SET Ttitles='教授' WHERE Tno='140005' UPDATE Teacher SET Tdno='110005' WHERE Tno='110001' UPDATE Teacher SET Tdno='110005' WHERE Tno='110002' UPDATE Teacher SET Tdno='110005' WHERE Tno='110003'

UPDATE Teacher SET Tdno='110005' WHERE Tno='110005' UPDATE Teacher SET Tdno='120003' WHERE Tno='120001' UPDATE Teacher SET Tdno='120003' WHERE Tno='120002' UPDATE Teacher SET Tdno='120003' WHERE Tno='120003' UPDATE Teacher SET Tdno='120003' WHERE Tno='120004' UPDATE Teacher SET Tdno='130003' WHERE Tno='130001' UPDATE Teacher SET Tdno='130003' WHERE Tno='130002' UPDATE Teacher SET Tdno='130003' WHERE Tno='130003' UPDATE Teacher SET Tdno='130003' WHERE Tno='130004' UPDATE Teacher SET Tdno='140005' WHERE Tno='140001' UPDATE Teacher SET Tdno='140005' WHERE Tno='140002' UPDATE Teacher SET Tdno='140005' WHERE Tno='140003' UPDATE Teacher SET Tdno='140005' WHERE Tno='140004' UPDATE Teacher SET Tdno='140005' WHERE Tno='140005' 3.在文本编译窗口编写SQL语句，完成：（1）求全体学生的学号和姓名； SELECT Sno,SnameFROM Student （2）求数学系学生的学号和姓名； SELECT Sno,Sname,SdeptFROM StudentWHERE Sdept LIKE'MA'

（3）求选修了课程的学生学号； SELECT DISTINCT SnoFROM SC （4）求开设的课程号和课程名； SELECT Cno,CnameFROM Course （5）求计算机系教师的姓名和性别。 SELECT Tname,TsexFROM Teacher 8

4.编写SQL语句实现删除Teacher表。 DROP TABLE Teacher 三、出现的问题及其解决方案(列出遇到的问题和解决办法，列出没有解决的问题) 问题1：遇到外键时先输入外键时数据录入会失败分析原因：外键后录入的话参考建就会出问题解决办法：先录入外键在录入主键

因篇幅问题不能全部显示，请点此查看更多更全内容

查看全文