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

1、贵州大学计算机科学与技术学院软件工程专业实验报告数据库原理实验报告题目数据定义基本表姓名班级日期实验环境：操作系统 Microsoft Windows 7 (64位/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语言进行对表的创建、删除和改动。二

2、、实验内容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),)CREATE TABLE Course(Cno VARCHAR(4) PRIMARY KEY,Cname VARCHAR(40),Cpno VARCHAR(4) ,

3、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 VARC

4、HAR(2) CHECK (Tsex=男or Tsex=女), Tage INT, Tdept VARCHAR(20), Ttitles VARCHAR(20), Twage INT, Tdno VARCHAR(6), FOREIGN KEY (Tdno) REFERENCES Teacher(Tno),)2. 在数据里面插入数据；INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(,李勇,男,20,CS)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(,刘晨,女,19,CS)I

5、NSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(,王敏,女,18,MA)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(,张立,男,19,IS)INSERT INTO Student(Sno,Sname,Ssex,Sage,Sdept) VALUES(,欧阳丽,女,21,FL)INSERT INTO Course(Cno,Cname,Cpno,Ccredit) VALUES(1,数据库,NULL,4)INSERT INTO Course(Cno,Cname,Cpno,Ccred

6、it) 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(C

7、no,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=1UPDATE Course SET Cpno=1 WHERE Cno=3UPDATE Course SET Cpno=6 WHERE Cno=4UPDATE Course SET Cpno=7 WHERE Cno=5UPDATE Course SET Cpno=6 WHERE Cno=7UPDAT

8、E Course SET Cpno=1 WHERE Cno=8INSERT SC(Sno,Cno,Grade) VALUES(,1,92)INSERT SC(Sno,Cno,Grade) VALUES(,2,85)INSERT SC(Sno,Cno,Grade) VALUES(,3,88)INSERT SC(Sno,Cno,Grade) VALUES(,2,90)INSERT SC(Sno,Cno,Grade) VALUES(,3,80)INSERT SC(Sno,Cno,Grade) VALUES(,1,NULL)INSERT SC(Sno,Cno,Grade) VALUES(,2,50)I

9、NSERT SC(Sno,Cno,Grade) VALUES(,3,70)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,钟灵,女,27,CS,讲师,2800,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,杨毅,男,42,CS,副教授,3500,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,周倩,女,25,CS

10、,讲师,2800,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,陈文茂,男,48,CS,教授,4000,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,江南,男,30,IS,副教授,3500,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,刘洋,男,28,IS,讲师,2800,NULL)INSERT T

11、eacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,汪明,男,44,IS,教授,4000,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,张蕾,女,35,IS,副教授,3500,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,邹佳羽,女,25,MA,讲师,2800,NULL)INSERT Teacher(Tno,Tname,Tsex,

12、Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,王力,男,30,MA,讲师,2800,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,王小峰,男,35,MA,副教授,3500,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,魏昭,男,40,MA,副教授,3500,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Tw

13、age,Tdno)VALUES(,王力,男,32,FL,副教授,3500,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,张小梅,女,27,FL,讲师,2800,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,吴娅,女,27,FL,讲师,2800,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,陈姝,女,

14、35,FL,副教授,3500,NULL)INSERT Teacher(Tno,Tname,Tsex,Tage,Tdept,Ttitles,Twage,Tdno)VALUES(,周斌,男,44,FL,教授,3500,NULL)UPDATE Teacher SET Ttitles=教授 WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE T

15、eacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHE

16、RE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=UPDATE Teacher SET Tdno= WHERE Tno=3. 在文本编译窗口编写SQL语句，完成：（1）求全体学生的学号和姓名；SELECT Sno,Sname FROM Student（2） 求数学系学生的学号和姓名；SELECT Sno,Sname,Sdept FROM Student WHERE Sdept LIKEMA（3）求选修了课程的学生学号；SE