1、实验二实验一:SQL数据定义功能 一、实验内容及要求1. 使用SQL语句建立学生管理系统相关的表,同时完善各表的相关完整性约束。其中带有下划线的为主码 学院(学院代码,学院名称) 学生(学号,姓名,性别,学院代码) ,学院代码为外码 教师(教师号,教师姓名,学院代码),学院代码为外码 课程(课程号,课程名,学时) 学习(学号,课程号,成绩),学号为外码,课程号为外码 开课(教师号,课程号),教师号为外码,课程号为外码2. 对各表进行增加、删除、修改属性操作添加操作:对学生表添加出生日期字段,和家庭地址字段,教师表增加性别字段,出生日期字段,对课程表增加先修课程字段,并且先修课程为本关系外码。删
2、除操作: 删除学生表家庭地址字段,删除教师表出生日期字段修改操作: 修改课程表中先修课程字段名称为先修课程号,修改学生姓名字段,该字段值不允许取空值3. 建立索引为学生表在姓名上建立名为sname-index 的索引,在学院代码字段上建立名为dept-index,降序。4. 删除表操作删除上述表,重新执行查询再次建立各表5. 利用ACCESS 2003 /2007完善各表的数据内容二、实验目的熟练掌握SQL的各种数据定义功能,包括1.定义表的功能,包括主码和外码的定义2.修改表的定义功能,包括增加属性,删除属性,修改属性类型4.建立和删除索引操作,理解索引的作用5.删除表功能三、实验步骤及运行
3、结果1. 使用SQL语句建立学生管理系统相关的表(1)创建学院表实验步骤:查询在设计视图中创建查询选择SQL视图,输入如下SQL语句:CREATE TABLE 学院( 学院代码 CHAR (8) NOT NULL UNIQUE, 学院名称 CHAR (15), PRIMARY KEY(学院代码);运行结果:(2)创建学生表CREATE TABLE 学生(学号 CHAR(8),姓名 CHAR(10),性别 CHAR(8),学院代码 CHAR(10),PRIMARY KEY(学号),FOREIGN KEY(学院代码) REFERENCES 学院(学院代码);(3)创建教师表CREATE TABLE
4、 教师 (教师号 CHAR(8), 教师姓名 CHAR(8), 学院代码 CHAR(10), PRIMARY KEY(教师号), FOREIGN KEY(学院代码) REFERENCES 学院(学院代码);(4)创建课程表CREATE TABLE 课程 ( 课程号 CHAR(8) NOT NULL UNIQUE, 课程名 CHAR(15), 学时 SMALLINT, PRIMARY KEY (课程号);(5)创建学习表CREATE TABLE 学习 ( 学号 CHAR(8), 课程号 CHAR(8), 成绩 SMALLINT, PRIMARY KEY (学号), FOREIGN KEY (学号
5、) REFERENCES 学生(学号), FOREIGN KEY (课程号) REFERENCES 课程(课程号);(6)创建开课表CREATE TABLE 开课 ( 教师号 CHAR(8), PRIMARY KEY (教师号,课程号), FOREIGN KEY(教师号) REFERENCES 教师(教师号),对学生表添加出生日期字段,和家庭地址字段:选择SQL视图,分别输入如下SQL语句:ALTER TABLE 学生 ADD 出生日期 DATETIME;ALTER TABLE 学生 ADD 家庭地址 CHAR(20);教师表增加性别字段,出生日期字段:ALTER TABLE 教师 ADD 出
6、生日期 DATETIME;ALTER TABLE 教师 ADD 性别 CHAR(2);对课程表增加先修课程字段:ALTER TABLE 课程 ADD 先修课程 CHAR(20);删除学生表家庭地址字段:ALTER TABLE 学生 DROP COLUMN 家庭地址;删除教师表出生日期字段:ALTER TABLE 教师 DROP COLUMN 出生日期;修改学生姓名字段,该字段值不允许取空值ALTER TABLE 学生 ALTER COLUMN 姓名 CHAR(8) NOT NULL;为学生表在姓名上建立名为sname-index 的索引在学院代码字段上建立名为dept-index的索引,降序。
7、CREATE UNIQUE INDEX sname_index ON 学生(姓名 DESC)CREATE UNIQUE INDEX dept_index ON 学院(学院代码 DESC);DROP TABLE 学院 CASCADEDROP TABLE 学生 CASCADEDROP TABLE 教师 CASCADEDROP TABLE 学习 CASCADEDROP TABLE 开课 CASCADEDROP TABLE 课程 CASCADE各表被删除。重新执行建立表的语句,就能从新建立各表。四、实验体会通过本次实验,我掌握了SQL数据定义功能包括使用SQL语句建立各表以及主码和外码的定义等,熟练了
8、对各表进行增加、删除、修改属性等操作以及对表进行建立和删除删除表等功能。 实验二:练习SQL数据操纵功能。使用SQL语句进行各种查询1、熟练掌握单表查询,包括 (1) 选择表中的若干列(投影) (2) 选择表中的若干元组(选择) (3) 对查询结果排序 (4) 使用集函数 (5) 对查询结果分组2、熟练掌握复杂查询,包括1) 等值与非等值连接查询 2) 自身连接:一个表与其自己进行连接。3) 外连接查询4) 复合条件连接:WHERE 子句中可以有多个连接条件。3、熟练掌握嵌套查询,包括1) 带有IN谓词的子查询2) 带有比较运算符的子查询(子查询一定要跟在比较符之后)3) 带有ANY或ALL谓
9、词的子查询(使用ANY和ALL时必须同时使用比较运算符)4) 带有EXISTS谓词的子查询(查询结果不返回任何数据,只产生逻辑“真”或“假”)4、使用SQL和ACCESS结合完成一些复杂的统计1求选修了老师“王刚”开课课程且成绩在90分以上的学生姓名、课程名称和成绩;(姓名,课程名,成绩)SELECT 姓名, 课程名, 成绩FROM 学生, 课程, 学习, 教师, 开课WHERE 教师姓名=王刚AND 成绩90AND 学生.学号=学习.学号AND 学习.课程号=课程.课程号AND 教师.教师号=开课.教师号AND 开课.课程号=课程.课程号;2求选修了“王刚”老师所授全部课程的学生姓名和学院名
10、称;(姓名,学院名称)SELECT 姓名, 学院名称FROM 学生, 学院WHERE NOT EXISTS(SELECT *FROM 开课 ,教师WHERE 教师.教师号=开课.教师号 AND 教师.教师姓名=AND NOT EXISTS FROM 学习 WHERE 学生.学号=学习.学号 AND 学习.课程号=开课.课程号 AND 学生.学院代码=学院.学院代码 );3求没有选修课程“软件工程”的学生学号和姓名;(学生学号,姓名)SELECT 学号, 姓名FROM 学生( SELECT * FROM 学习,课程 WHERE 课程名=软件工程 AND 学生.学号=学习.学号AND 课程.课程号
11、=学习.课程号);4求至少选修了两门课程的学生学号;(学号)SELECT 学号FROM 学习GROUP BY 学号HAVING COUNT (课程号)=2;5求课程“经济学”不及格学生姓名和考试成绩;(姓名,成绩)SELECT 姓名, 成绩FROM 课程, 学习, 学生WHERE 课程.课程名=经济学AND 成绩60AND 学习.学号=学生.学号AND 学习.课程号=课程.课程号;6求至少选修了与学号“T06” 同学选修的课程相同的学生学号;SELECT A.学号FROM 学习 AS A, 学习 AS BWHERE A.课程号=B.课程号AND B.学号=T06AND A.学号GROUP BY
12、 A.学号HAVING COUNT (A.课程号)=(SELECT COUNT (课程号) WHERE 学号=);7求至少选修了“C3,C4”两门课程的学生姓名和学院名称;WHERE 学生.学院代码=学院.学院代码 FROM 学习 AS A WHERE (课程号=C3OR 课程号=C4) (SELECT *FROM 学习 AS BWHERE 学生.学号=B.学号AND A.课程号=B.课程号);8查询“王石”同学没有选修的课程号和课程名; (课程号,课程名)SELECT 课程号, 课程名FROM 课程 FROM 学生,学习 WHERE 姓名=王石 AND 学习.课程号=课程.课程号);9查询没
13、有被任何学生选修的课程的课程号;(课程号)SELECT 课程号 WHERE 学习.课程号=课程.课程号);10求选修了全部课程的学生姓名;(姓名)SELECT 姓名 FROM 课程 WHERE NOT EXISTS WHERE 学习.学号=学生.学号 AND 学习.课程号=课程.课程号);11查询各学院课程“经济学”的平均分,并按照成绩从高到低的顺序排列;(学院名称,平均分)SELECT 学院名称, AVG(成绩) AS 平均分FROM 学习, 课程, 学生, 学院WHERE 学习.课程号=课程.课程号 AND 课程名=AND 学生.学号=学习.学号 AND 学生.学院代码=学院.学院代码GR
14、OUP BY 学院名称ORDER BY AVG(成绩) DESC;12查询选修课程“经济学”的学生姓名和所在院系,结果按各院系排列,同时成绩从高到低排列;(姓名,学院名称,成绩)SELECT 姓名, 学院名称, 成绩FROM 学生, 学习, 学院, 课程WHERE 课程名=ORDER BY 学院名称, 成绩 DESC;13求学时在30-45之间(含30和45)的课程的课程号和课程名称及授课教师;(课程号,课程名,教师姓名)SELECT 课程号, 课程名, 教师姓名FROM 课程, 教师WHERE EXISTSFROM 开课WHERE 学时 BETWEEN 30 AND 45 AND 课程.课程
15、号=开课.课程号AND 开课.教师号=教师.教师号);14检索选修课程“经济学”的最高分学生的姓名;FROM 学生, 学习WHERE 成绩=(SELECT MAX(成绩) WHERE 课程.课程名= AND 学习.课程号=课程.课程号)AND 学生.学号=学习.学号;15查询选课人数超过5人的课程的课程号及课程名;(课程号,课程名)SELECT 课程名, 课程号WHERE 课程号 IN( SELECT 课程号 GROUP BY 课程号 HAVING COUNT(学号)=5);通过本次实验,我熟练掌握了使用SQL语句进行各种查询的操作方法;使用集函数查询、对查询结果进行分组排序;熟悉了复杂查询对表的连接、使用嵌套查询中带有IN谓词的子查询以及带有EXISTS谓词的子查询等。我也明白了只有不断练习才能掌握好数据库。
copyright@ 2008-2023 冰点文库 网站版权所有
经营许可证编号:鄂ICP备19020893号-2