华南理工大学 计算机学院 数据库 实验一 报告.docx

1、华南理工大学 计算机学院 数据库 实验一 报告华南理工大学数据库课程实验报告实验题目： 实验一：SQL的使用 姓名： 学号： 班级： 计科一班 组别： 无 合作者： 无 指导教师： 董守玲 实验概述【实验目的及要求】实验目的： 通过交互式SQL的使用， 掌握数据库的创建、插入、更新、查询等基本操作实验要求：1 创建Student数据库（oracle可以不创建新的数据库，直接创建下列表就可以了）包括Students,Courses,SC表，表结构如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CR

2、EDIT,SEMESTER)SC(SNO,CNO,GRADE)(注：下划线表示主键，斜体表示外键)，并插入一定数据。2 完成如下的查询要求及更新的要求。（1）查询身高大于1.80m的男生的学号和姓名；（2）查询计算机系秋季所开课程的课程号和学分数；（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；（5）查询每位学生已选修课程的门数和总平均成绩；（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；（8）查询缺

3、成绩的学生的姓名，缺成绩的课程号及其学分数；（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；（10）查询1984年1986年出生的学生的姓名,总平均成绩及已修学分数。（11） 在STUDENT和SC关系中，删去SNO以01开关的所有记录。（12）在关系中增加以下记录：（13）将课程CS-221的学分数增为，讲课时数增为3补充题：(1) 统计各系的男生和女生的人数。(2) 列出学习过编译原理，数据库或体系结构课程，且这些课程的成绩之一在90分以上的学生的名字。(3) 列出未修选电子技术课程，但选修了数字电路或数字逻辑课程的学生数。(4) 按课程排序列出所有学生的成绩，

4、尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句)4选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2) 设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。【实验环境】PC机，WINDOWS操作系统，Oracle 或

5、Microsoft SQL Server 数据库实验内容【实验过程】一、实验步骤：.连接数据库.准备数据.建立模式（数据库）.建立表.插入数据.开始查询二、实验数据：（如给定文件）三、实验主要过程：1 创建Student数据库（oracle可以不创建新的数据库，直接创建下列表就可以了）包括Students,Courses,SC表，表结构如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE) (注：下划线表示主键，斜体表示外键)，并插入一

6、定数据。CREATE TABLE Students(SNO VARCHAR(15) PRIMARY KEY,SNAME VARCHAR(5),SEX VARCHAR(3),BDATE VARCHAR(20),HEIGHT NUMERIC(3,2),DEPARTMENT VARCHAR(10);CREATE TABLE Courses(CNO VARCHAR(10) PRIMARY KEY,CNAME VARCHAR(10),LHOUR VARCHAR(3),CREDIT NUMERIC(2,1),SEMESTER VARCHAR(2);CREATE TABLE SC(SNO VARCHAR(1

7、5),CNO VARCHAR(10),GRADE SMALLINT,FOREIGN KEY (SNO) REFERENCES Students(SNO),FOREIGN KEY (CNO) REFERENCES Courses(CNO);2 完成如下的查询要求及更新的要求。（1）查询身高大于1.80m的男生的学号和姓名；SELECT SNO,SNAMEFROM StudentsWHERE HEIGHT1.80 AND SEX LIKE 男;（2）查询计算机系秋季所开课程的课程号和学分数；SELECT CNO,CREDITFROM CoursesWHERE CNO LIKECS% AND SEM

8、ESTER LIKE秋;（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；SELECT SNAME,SC.CNO,CREDIT,GRADEFROM Students,Courses,SCWHERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND SC.CNO LIKECS% AND SEMESTER LIKE秋;（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；SELECT DISTINCT SNAMEFROM Students,SCWHERE Students.SNO=SC.SNO AND CN

9、O LIKEEE% AND SEX LIKE女;（5）查询每位学生已选修课程的门数和总平均成绩；SELECT SNO,COUNT(*),AVG(GRADE)FROM SCGROUP BY SNO;（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；SELECT CNO,COUNT(*),MAX(GRADE),MIN(GRADE),AVG(GRADE)FROM SCGROUP BY CNO;（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；SELECT DISTINCT SNAME,SC.SNOFROM Students,SCWHERE Students.S

10、NO=SC.SNO AND GRADE80ORDER BY SC.SNO ASC;（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；SELECT SNAME,SC.CNO,CREDITFROM SC LEFT OUTER JOIN Students ON SC.SNO=Students.SNO LEFT OUTER JOIN Courses ON SC.CNO=Courses.CNOWHERE GRADE IS NULL;（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；SELECT DISTINCT SNAMEFROM Students,Courses,SCW

11、HERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND CREDIT3 AND GRADE70;（10）查询1984年1986年出生的学生的姓名,总平均成绩及已修学分数SELECT SNAME,AVG(GRADE),SUM(CREDIT)FROM Students,Courses,SCWHERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND (BDATE BETWEEN 1984 AND 1986 ) GROUP BY SNAME;（11） 在STUDENT和SC关系中，删去SNO以01开关的所有记

12、录。DELETE FROM StudentsWHERE SNO LIKE01%;DELETE FROM SCWHERE SNO LIKE01%;（12）在关系中增加以下记录： INSERTINTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)VALUES(0409101,何平,女,1987-03-02,1.62);INSERTINTO Students(SNO,SNAME,SEX,BDATE,HEIGHT)VALUES(0408130,向阳,男,1986-12-11,1.75);（13）将课程CS-221的学分数增为，讲课时数增为UPDATE CoursesSET

13、CREDIT=3WHERE CNO LIKECS-221;UPDATE CoursesSET LHOUR =60WHERE CNO LIKECS-221;3补充题：(1) 统计各系的男生和女生的人数。SELECT DEPARTMENT, SUM(CASE WHEN SEX=男 THEN 1 ELSE 0 END)男生人数, SUM(CASE WHEN SEX=女 THEN 1 ELSE 0 END)女生人数FROM StudentsGROUP BY DEPARTMENT;(2) 列出学习过编译原理，数据库或体系结构课程，且这些课程的成绩之一在90分以上的学生的名字。SELECT DISTINC

14、T SNAMEFROM Students,SC,CoursesWHERE Students.SNO=SC.SNO AND Courses.CNO=SC.CNO AND GRADE90 AND (CNAME LIKE编译原理 OR CNAME LIKE数据库 OR CNAME LIKE体系结构);(3) 列出未修选电子技术课程，但选修了数字电路或数字逻辑课程的学生数。SELECT COUNT(*)FROM CoursesWHERE (CNAME LIKE数字电路OR CNAME LIKE数字逻辑)AND NOT EXISTS (SELECT* FROM SC WHERE SC.CNO=Cours

15、es.CNO AND CNAME LIKE电子技术);(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。SELECT DISTINCT SC.CNO,CNAME,SNO,GRADEFROM Courses LEFT OUTER JOIN SC ON Courses.CNO=SC.CNOGROUP BY SC.CNO,CNAME,SNO,GRADEORDER BY SC.CNO,CNAME,SNO,GRADE;(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句)SELECT SNAME,AVG_GRADEFROM

16、Students,(SELECT SNO,AVG(GRADE)AVG_GRADE FROM SC GROUP BY SNO)AS AVG_SCWHERE Students.SNO=AVG_SC.SNO AND AVG_GRADE=(SELECT MAX(AVG_GRADE2) FROM (SELECT SNO,AVG(GRADE)AVG_GRADE2 FROM SC GROUP BY SNO)AS AVG_SC2);4选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：ALTER TABLE CoursesADD PRE1 VARCH

17、AR(10),ADD PRE2 VARCHAR(10);1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。ALTER TABLE CoursesADD CONSTRAINT THE_KEYFOREIGN KEY(PRE1)REFERENCES Courses(CNO);UPDATE CoursesSET PRE1=C1WHERE CNAME=数据库;2) 设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)首先，设置数据库课程的先修课。UPDATE CoursesSET PRE1=C1WHERE

18、 CNAME=数据库;结果：SELECT SNOFROM SC,CoursesWHERE SC.CNO=Courses.CNO AND SC.CNO=(SELECT PRE1 FROM Courses WHERE CNO=C2);小结通过这次实验，我收获了很多。首先，是课本的理论知识通过实验得到了验证，加深了我对它们的理解。其次，对于数据库实践的操作，通过各个习题的练习，我较为熟练地掌握了它们的用法。因此，对日后使用数据库进行操作也增加了信心。最后，对于具体的题目，通过实际编程这种更为严谨的过程。我也总结了不少的技巧和方法。较为系统的知识在课程学习过程中大致已经掌握，现将实践中一些小的知识点归

19、纳如下：1.sql启动的时候要用：mysql -uroot -p,不需要密码。2.在使用之前要先选择数据库3.创建模式是authorization而不是authorize，而且后面直接跟用户名，没有on什么的。4.不是每一个表都要主键5.foreignkey 在列级和在表级的不同表达，在列级只需要references 表名（列），在表级需要在前面加上一个6.foreign key（列名）。!注意啊，这里的列名都要加括号。而且要定义多个外码的时候在表级要分开语句，不能用逗号分开（主码是没有表的参照的，所以是可以的）。7.不在表定义的时候给表添加外键： alter table A add cons

20、traint foreign_key foreign key(the_key) references B(.)8.一定要注意reference 的s!9.对于主码和外键，数据类型必须是相同的，即便是字符串类型的位数也必须是一样的。10.sql语句的注释：单行;多行*/11.“至少一个，一个以上”是比较能迷惑人的表达事实上有就可以了。12.如果是问空值的话，如果一旦判度有多个表的时候就要想外连接的问题。13.外链接是在from语句的时候就：表连接类型表on链接条件（就像where的一样）。14.如果是多个表的外连接怎么办？思想就是把前一个表当作整体。15.思考方式是先看需要什么表，然后是需要什么

21、列，看列要判断是否要去除重复。16.插入数据是数据更新的一部分，想要定义格式的话，是在表名的后面搞。17.数据置值的时候用，不能用LIKE。18.如果要给聚集函数命名的话，直接在后面加名字就好，即便是中文也不用加引号。而对于子表，则必须用as跟上命名。19.group by 如果有多个的话，就是按字典序排列。20.有时候表的属性没法使用聚集函数，并且也无法使用（因为聚集函数的使用不再分组内），这个时候可以选择在表的选择时使用子查询表，或者在外和热语句里也可以使用，用几层没关系。21添加列的时候可以并列，但是要重复用add。通过这次实验，我初步掌握了数据库的基本操作，对它的使用和作用有了更加具体的认识。但是，同时我也认识到数据库内容的丰富，细节的繁多。只凭借一次操作是不能做到熟练地运用它的。在后面的学习过程中，我还应该注意总结和积累，在这个基础上进行更多练习。指导教师评语及成绩评语： 成绩： 指导教师签名： 批阅日期：