华南理工大学 计算机学院 数据库 实验一 报告.docx
《华南理工大学 计算机学院 数据库 实验一 报告.docx》由会员分享,可在线阅读,更多相关《华南理工大学 计算机学院 数据库 实验一 报告.docx(18页珍藏版)》请在冰豆网上搜索。
华南理工大学计算机学院数据库实验一报告
华南理工大学
《数据库》课程实验报告
实验题目:
实验一:
SQL的使用
姓名:
学号:
班级:
计科一班组别:
无
合作者:
无
指导教师:
董守玲
实验概述
【实验目的及要求】
实验目的:
通过交互式SQL的使用,掌握数据库的创建、插入、更新、查询等基本操作
实验要求:
1创建Student数据库(oracle可以不创建新的数据库,直接创建下列表就可以了)
包括Students,Courses,SC表,表结构如下:
Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)
Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)
SC(SNO,CNO,GRADE)
(注:
下划线表示主键,斜体表示外键),并插入一定数据。
2完成如下的查询要求及更新的要求。
(1)查询身高大于1.80m的男生的学号和姓名;
(2)查询计算机系秋季所开课程的课程号和学分数;
(3)查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩;
(4)查询至少选修一门电机系课程的女生的姓名(假设电机系课程的课程号以EE开头);
(5)查询每位学生已选修课程的门数和总平均成绩;
(6)查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩;
(7)查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列;
(8)查询缺成绩的学生的姓名,缺成绩的课程号及其学分数;
(9)查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名;
(10)查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。
(11)在STUDENT和SC关系中,删去SNO以’01’开关的所有记录。
(12)在STUDENT关系中增加以下记录:
<0409101 何平 女 1987-03-02 1.62>
<0408130 向阳 男 1986-12-11 1.75>
(13)将课程CS-221的学分数增为3,讲课时数增为60
3.补充题:
(1)统计各系的男生和女生的人数。
(2)列出学习过‘编译原理’,‘数据库’或‘体系结构’课程,且这些课程的成绩之一在90分以上的学生的名字。
(3)列出未修选‘电子技术’课程,但选修了‘数字电路’或‘数字逻辑’课程的学生数。
(4)按课程排序列出所有学生的成绩,尚无学生选修的课程,也需要列出,相关的学生成绩用NULL表示。
(5)列出平均成绩最高的学生名字和成绩。
(SELECT句中不得使用TOPn子句)
4.选做题:
对每门课增加“先修课程”的属性,用来表示某一门课程的先修课程,每门课程应可记录多于一门的先修课程。
要求:
1)修改表结构的定义,应尽量避免数据冗余,建立必要的主键,外键。
2)设计并插入必要的测试数据,完成以下查询:
列出有资格选修数据库课程的所有学生。
(该学生已经选修过数据库课程的所有先修课,并达到合格成绩。
)
注意:
须设计每个查询的测试数据,并在查询之前用INSERT语句插入表中。
【实验环境】
PC机,WINDOWS操作系统,Oracle 或MicrosoftSQLServer数据库
实验内容
【实验过程】
一、实验步骤:
1.连接数据库
2.准备数据
3.建立模式(数据库)
4.建立表
5.插入数据
6.开始查询
二、实验数据:
(如给定文件)
三、实验主要过程:
1创建Student数据库(oracle可以不创建新的数据库,直接创建下列表就可以了)
包括Students,Courses,SC表,表结构如下:
Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)
Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)
SC(SNO,CNO,GRADE)
(注:
下划线表示主键,斜体表示外键),并插入一定数据。
CREATETABLEStudents(
SNOVARCHAR(15)PRIMARYKEY,
SNAMEVARCHAR(5),
SEXVARCHAR(3),
BDATEVARCHAR(20),
HEIGHTNUMERIC(3,2),
DEPARTMENTVARCHAR(10)
);
CREATETABLECourses(
CNOVARCHAR(10)PRIMARYKEY,
CNAMEVARCHAR(10),
LHOURVARCHAR(3),
CREDITNUMERIC(2,1),
SEMESTERVARCHAR
(2)
);
CREATETABLESC(
SNOVARCHAR(15),
CNOVARCHAR(10),
GRADESMALLINT,
FOREIGNKEY(SNO)REFERENCESStudents(SNO),
FOREIGNKEY(CNO)REFERENCESCourses(CNO)
);
2完成如下的查询要求及更新的要求。
(1)查询身高大于1.80m的男生的学号和姓名;
SELECTSNO,SNAME
FROMStudents
WHEREHEIGHT>1.80ANDSEXLIKE'男';
(2)查询计算机系秋季所开课程的课程号和学分数;
SELECTCNO,CREDIT
FROMCourses
WHERECNOLIKE'CS%'ANDSEMESTERLIKE'秋';
(3)查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩;
SELECTSNAME,SC.CNO,CREDIT,GRADE
FROMStudents,Courses,SC
WHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNO
ANDSC.CNOLIKE'CS%'ANDSEMESTERLIKE'秋';
(4)查询至少选修一门电机系课程的女生的姓名(假设电机系课程的课程号以EE开头);
SELECTDISTINCTSNAME
FROMStudents,SC
WHEREStudents.SNO=SC.SNO
ANDCNOLIKE'EE%'ANDSEXLIKE'女';
(5)查询每位学生已选修课程的门数和总平均成绩;
SELECTSNO,COUNT(*),AVG(GRADE)
FROMSC
GROUPBYSNO;
(6)查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩;
SELECTCNO,COUNT(*),MAX(GRADE),MIN(GRADE),AVG(GRADE)
FROMSC
GROUPBYCNO;
(7)查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列;
SELECTDISTINCTSNAME,SC.SNO
FROMStudents,SC
WHEREStudents.SNO=SC.SNOANDGRADE>80
ORDERBYSC.SNOASC;
(8)查询缺成绩的学生的姓名,缺成绩的课程号及其学分数;
SELECTSNAME,SC.CNO,CREDIT
FROMSCLEFTOUTERJOINStudentsONSC.SNO=Students.SNO
LEFTOUTERJOINCoursesONSC.CNO=Courses.CNO
WHEREGRADEISNULL;
(9)查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名;
SELECTDISTINCTSNAME
FROMStudents,Courses,SC
WHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNO
ANDCREDIT>3ANDGRADE<70;
(10)查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数
SELECTSNAME,AVG(GRADE),SUM(CREDIT)
FROMStudents,Courses,SC
WHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNO
AND(BDATEBETWEEN1984AND1986)
GROUPBYSNAME;
(11)在STUDENT和SC关系中,删去SNO以’01’开关的所有记录。
DELETE
FROMStudents
WHERESNOLIKE'01%';
DELETE
FROMSC
WHERESNOLIKE'01%';
(12)在STUDENT关系中增加以下记录:
<0409101何平 女 1987-03-02 1.62>
<0408130向阳 男 1986-12-11 1.75>
INSERT
INTOStudents(SNO,SNAME,SEX,BDATE,HEIGHT)
VALUES(0409101,'何平','女',1987-03-02,1.62);
INSERT
INTOStudents(SNO,SNAME,SEX,BDATE,HEIGHT)
VALUES(0408130,'向阳','男',1986-12-11,1.75);
(13)将课程CS-221的学分数增为3,讲课时数增为60
UPDATECourses
SETCREDIT=3
WHERECNOLIKE'CS-221';
UPDATECourses
SETLHOUR='60'
WHERECNOLIKE'CS-221';
3.补充题:
(1)统计各系的男生和女生的人数。
SELECTDEPARTMENT,
SUM(CASEWHENSEX='男'THEN1ELSE0END)男生人数,
SUM(CASEWHENSEX='女'THEN1ELSE0END)女生人数
FROMStudents
GROUPBYDEPARTMENT;
(2)列出学习过‘编译原理’,‘数据库’或‘体系结构’课程,且这些课程的成绩之一在90分以上的学生的名字。
SELECTDISTINCTSNAME
FROMStudents,SC,Courses
WHEREStudents.SNO=SC.SNOANDCourses.CNO=SC.CNO
ANDGRADE>90AND(CNAMELIKE'编译原理'ORCNAMELIKE'数据库'ORCNAMELIKE'体系结构');
(3)列出未修选‘电子技术’课程,但选修了‘数字电路’或‘数字逻辑’课程的学生数。
SELECTCOUNT(*)
FROMCourses
WHERE(CNAMELIKE'数字电路'ORCNAMELIKE'数字逻辑')ANDNOTEXISTS
(SELECT*
FROMSC
WHERESC.CNO=Courses.CNOANDCNAMELIKE'电子技术');
(4)按课程排序列出所有学生的成绩,尚无学生选修的课程,也需要列出,相关的学生成绩用NULL表示。
SELECTDISTINCTSC.CNO,CNAME,SNO,GRADE
FROMCoursesLEFTOUTERJOINSCONCourses.CNO=SC.CNO
GROUPBYSC.CNO,CNAME,SNO,GRADE
ORDERBYSC.CNO,CNAME,SNO,GRADE;
(5)列出平均成绩最高的学生名字和成绩。
(SELECT句中不得使用TOPn子句)
SELECTSNAME,AVG_GRADE
FROMStudents,(SELECTSNO,AVG(GRADE)AVG_GRADE
FROMSC
GROUPBYSNO)ASAVG_SC
WHEREStudents.SNO=AVG_SC.SNOANDAVG_GRADE=(SELECTMAX(AVG_GRADE2)
FROM(SELECTSNO,AVG(GRADE)AVG_GRADE2
FROMSC
GROUPBYSNO)ASAVG_SC2);
4.选做题:
对每门课增加“先修课程”的属性,用来表示某一门课程的先修课程,每门课程应可记录多于一门的先修课程。
要求:
ALTERTABLECourses
ADDPRE1VARCHAR(10),ADDPRE2VARCHAR(10);
1)修改表结构的定义,应尽量避免数据冗余,建立必要的主键,外键。
ALTERTABLECourses
ADDCONSTRAINTTHE_KEY
FOREIGNKEY(PRE1)
REFERENCESCourses(CNO);
UPDATECourses
SETPRE1='C1'
WHERECNAME='数据库';
2)设计并插入必要的测试数据,完成以下查询:
列出有资格选修数据库课程的所有学生。
(该学生已经选修过数据库课程的所有先修课,并达到合格成绩。
)
首先,设置数据库课程的先修课。
UPDATECourses
SETPRE1='C1'
WHERECNAME='数据库';
结果:
SELECTSNO
FROMSC,Courses
WHERESC.CNO=Courses.CNO
ANDSC.CNO=(SELECTPRE1
FROMCourses
WHERECNO='C2');
小结
通过这次实验,我收获了很多。
首先,是课本的理论知识通过实验得到了验证,加深了我对它们的理解。
其次,对于数据库实践的操作,通过各个习题的练习,我较为熟练地掌握了它们的用法。
因此,对日后使用数据库进行操作也增加了信心。
最后,对于具体的题目,通过实际编程这种更为严谨的过程。
我也总结了不少的技巧和方法。
较为系统的知识在课程学习过程中大致已经掌握,现将实践中一些小的知识点归纳如下:
1.sql启动的时候要用:
mysql-uroot-p,不需要密码。
2.在使用之前要先选择数据库
3.创建模式是authorization而不是authorize,而且后面直接跟用户名,没有on什么的。
4.不是每一个表都要主键
5.foreign key在列级和在表级的不同表达,在列级只需要references表名(列),在表级需要在前面加上一个
6.foreignkey(列名)。
!
!
!
注意啊,这里的列名都要加括号。
而且要定义多个外码的时候在表级要分开语句,不能用逗号分开(主码是没有表的参照的,所以是可以的)。
7.不在表定义的时候给表添加外键:
altertableA
addconstraintforeign_key
foreignkey(the_key)
referencesB(...)
8.一定要注意reference的s!
9.对于主码和外键,数据类型必须是相同的,即便是字符串类型的位数也必须是一样的。
10.sql语句的注释:
单行--; 多行/**/
11.“至少一个,一个以上”是比较能迷惑人的表达——事实上有就可以了。
12.如果是问空值的话,如果一旦判度有多个表的时候就要想外连接的问题。
13.外链接是在from语句的时候就:
表1 连接类型 表2 on 链接条件(就像where的一样)。
14.如果是多个表的外连接怎么办?
思想就是把前一个表当作整体。
15.思考方式是先看需要什么表,然后是需要什么列,看列要判断是否要去除重复。
16.插入数据是数据更新的一部分,想要定义格式的话,是在表名的后面搞。
17.数据置值的时候用=,不能用LIKE。
18.如果要给聚集函数命名的话,直接在后面加名字就好,即便是中文也不用加引号。
而对于子表,则必须用as跟上命名。
19.groupby如果有多个的话,就是按字典序排列。
20.有时候表的属性没法使用聚集函数,并且也无法使用having(因为聚集函数的使用不再分组内),这个时候可以选择在表的选择时使用子查询表,或者在外和热语句里也可以使用,用几层没关系。
21添加列的时候可以并列,但是要重复用add。
通过这次实验,我初步掌握了数据库的基本操作,对它的使用和作用有了更加具体的认识。
但是,同时我也认识到数据库内容的丰富,细节的繁多。
只凭借一次操作是不能做到熟练地运用它的。
在后面的学习过程中,我还应该注意总结和积累,在这个基础上进行更多练习。
指导教师评语及成绩
评语:
成绩:
指导教师签名:
批阅日期: