数据库原理及应用（MYSQL 版）实验1～4 题目以及代码

实验一  数据库和表的管理

1. 1.使用 SQL 语句创建数据库 studentsdb。
2. CREATE DATABASE studentsdb;
4. 2.使用SQL 语句选择 studentsdb 为当前使用的数据库。
5. USE studentsdb;
7. 3.1.使用 SQL 语句在 studentsdb 数据库中创建数据表 student_info、curriculum、grade，3个表的数据结构如表A-1～表A-3所示。
8. USE studentsdb;
9. CREATE TABLE student_info
10. (
11.         学号        char(4) PRIMARY KEY,
12.         姓名        char(8) NOT NULL,
13.         性别        char(2),
14.         出生日期        date,
15.         家庭住址        varchar(50)
16. );
17. DESC student_info;
19. 4.
20. INSERT INTO student_info(学号,姓名,性别,出生日期,家庭住址)
21. VALUES ('0001','张青平','男','2000-10-01','衡阳市东风路77号'),
22.        ('0002','刘东阳','男','1998-12-09','东阳市八一北路33号'),
23.        ('0003','马晓夏','女','1995-05-12','长岭县五一路763号'),
24.        ('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号'),
25.        ('0005','孙海洋','男','1995-04-03','长岛县解放路27号'),
26.        ('0006','郭小斌','男','1997-11-10','南山市红旗路113号'),
27.        ('0007','肖月玲','女','1996-12-07','东方市南京路11号'),
28.        ('0008','张玲珑','女','1997-12-24','滨江市新建路97号');
29. SELECT * FROM student_info;
31. 3.2使用 SQL 语句在 studentsdb 数据库中创建数据表 student_info、curriculum、grade，3个表的数据结构如表A-1～表A-3所示。
32. CREATE TABLE curriculum
33. (
34.         课程编号        char(4) PRIMARY KEY,
35.         课程名称        varchar(50),
36.         学分        int
37. );
38. DESC curriculum;
40. 4.2
41. INSERT INTO curriculum(课程编号,课程名称,学分)
42. VALUES ('0001','计算机应用基础',2),
43.        ('0002','C语言程序设计',2),
44.        ('0003','数据库原理及应用',2),
45.        ('0004','英语',4),
46.        ('0005','高等数学',4);
47. SELECT * FROM curriculum;
49. 3.3使用 SQL 语句在 studentsdb 数据库中创建数据表 student_info、curriculum、grade，3个表的数据结构如表A-1～表A-3所示。
50. CREATE TABLE grade
51. (
52.         学号        char(4),
53.         课程编号        char(4),
54.         分数        int,
55.         PRIMARY KEY(学号,课程编号)
56. );
57. DESC grade;
59. 4.3
60. INSERT INTO grade(学号,课程编号,分数)
61. VALUES ('0001','0001',80),
62.        ('0001','0002',91),
63.        ('0001','0003',88),
64.        ('0001','0004',85),
65.        ('0001','0005',77),
66.        ('0002','0001',73),
67.        ('0002','0002',68),
68.        ('0002','0003',80),
69.        ('0002','0004',79),
70.        ('0002','0005',73),
71.        ('0003','0001',84),
72.        ('0003','0002',92),
73.        ('0003','0003',81),
74.        ('0003','0004',82),
75.        ('0003','0005',75);
76. SELECT * FROM grade;
78. 5.使用SQL语句ALTER TABLE修改curriculum表的“课程名称”列，使之为空
79. ALTER TABLE curriculum
80. MODIFY 课程名称 VARCHAR(50) NULL;
81. DESC curriculum;
83. 6.使用SQL语句ALTER TABLE修改grade表的“分数”列，使其数据类型为decimal(5,2)
84. ALTER TABLE grade
85. MODIFY 分数 DECIMAL(5,2);
86. DESC grade;
88. 7.使用SQL语句ALTER TABLE为student_info表添加一个名为“备注”的数据列，其数据类型为varchar(50)
89. ALTER TABLE student_info
90. ADD 备注 VARCHAR(50);
91. DESC student_info;
93. 8.使用SQL语句创建数据库studb，并在此数据库下创建表stu，表结构与数据与studentsdb的student_info表相同
94. CREATE DATABASE studb;
95. USE studb;
96. CREATE TABLE stu
97. SELECT * FROM studentsdb.student_info;
98. DESC stu;
100. 9.使用SQL语句删除表stu中学号为0004的记录
101. DELETE FROM stu WHERE 学号 = '0004';
102. SELECT * FROM stu;
104. 10.使用SQL语句更新表stud中学号为0002的家族住址为“滨江市新建路96号”
105. UPDATE stu
106.         SET 家庭住址 = '滨江市新建路96号' WHERE 学号 = '0002' ;
107. SELECT * FROM stu;
109. 11.删除表stud的“备注”列
110. ALTER TABLE stu
111.         DROP COLUMN 备注;
113. 12.删除表stud
114. DROP TABLE stu;
116. 13.删除数据库studb
117. DROP DATABASE studb;

复制代码

实验二  数据查询

1. 1.1 在student_info 表中查询每个学生的学号、姓名、出生日期信息。
2. USE studentsdb;
3. SELECT 学号,姓名,出生日期 FROM student_info;
5. 1.2 查询 student_info 表中学号为0002的学生的姓名和家庭住址。
6. SELECT 姓名,家庭住址 FROM student_info
7. WHERE 学号='0002';
9. 1.3 查询 student info 表中所有出生日期在1995年以后的女同学的姓名和出生目期。
10. SELECT 姓名,出生日期 FROM student_info
11. WHERE 性别 = '女' AND 出生日期 > '1995-12-31';
13. 2.1 在grade表中查询分数在70~80的学生的学号、课程编号和成绩。
14. SELECT 学号,课程编号,分数 FROM grade
15. WHERE 分数 >70 AND 分数 <80;
17. 2.2 在grade表中查询课程编号0002的学生的平均成绩。
18. SELECT AVG(分数)AS 平均成绩 FROM grade
19. WHERE 课程编号='0002';
21. 2.3 在 grade 表中查询选修课程编号为0003的人数和该课程有成绩的人数。
22. SELECT COUNT(学号) AS 选修课程编号为0003的人数,COUNT(分数) AS 有成绩的人数 FROM grade
23. WHERE 课程编号 = '0003' ;
25. 2.4 查询 student_info表中学生的姓名和出生日期，查询结果按出生日期从大到小排序。
26. SELECT 姓名,出生日期FROM student_info
27. ORDER BY 出生日期 DESC;
29. 2.5 查询所有姓“张”的学生的学号和姓名。
30. SELECT 学号,姓名 FROM student_info
31. WHERE 姓名 LIKE '张%';
33. 3.对于 student_info 表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别排序，性别相同的再按学号由大到小排序。
34. SELECT 学号,姓名,性别,出生日期,家庭住址 FROM student_info
35. ORDER BY 性别,学号 DESC;
37. 4. 使用GROUP BY 子句查询grade表中各个学生的平均成绩。
38. SELECT 学号,AVG(分数) AS 平均成绩 FROM grade
39. GROUP BY 学号;
41. 5. 使用 UNION运算符将 student_info表中姓“刘”学生的学号、姓名与姓“张”学生的学号、姓名返回在一个表中。
42. SELECT 学号,姓名 FROM student_info
43. WHERE 姓名 LIKE '刘%'
44. UNION
45. SELECT 学号,姓名 FROM student_info
46. WHERE 姓名 LIKE '张%';
48. 6.1 在student_info 表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。
49. SELECT 姓名,出生日期 FROM student_info
50. WHERE 性别 = (SELECT 性别 FROM  student_info WHERE 姓名='刘东阳');
52. 6.2 使用IN子查询查找所修课程编号为0002、0005的学生的学号、姓名、性别。
53. SELECT 学号,姓名,性别 FROM student_info
54. WHERE  学号 IN(SELECT (学号) FROM grade WHERE 课程编号='0002' OR 课程编号='0005');
56. 6.3 使用ANY子查询查找学号为0001 的学生的分数比学号为0002的学生的最低分数高的课程编号和分数
57. SELECT 课程编号,分数 FROM grade  
58. WHERE 学号='0001' AND 分数>ANY(SELECT MIN(分数) FROM grade WHERE 学号='0002');
60. 6.4使用ALL子查询查找学号为0001 的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。
61. SELECT 课程编号,分数 FROM grade
62. WHERE 学号='0001' AND 分数>ALL(SELECT MAX(分数) FROM grade WHERE 学号='0002');
64. 7.1 查询分数为80~90的学生的学号、姓名和分数。
65. SELECT grade.学号,姓名,分数 FROM grade,student_info
66. WHERE grade.学号=student_info.学号
67. AND 分数 BETWEEN 80 and 90;
69. 7.2 使用INNBR JOIN 连接方式登阅学习*數据库原理及应用”课程的学生的学号、姓名、分数。
70. SELECT grade.学号, 姓名,分数 FROM  grade
71. INNER JOIN student_info  ON student_info.学号=grade.学号
72. INNER JOIN curriculum ON curriculum.课程编号=grade.课程编号
73. WHERE curriculum.课程名称='数据库原理及应用';
75. 7.3 查询每个学生所选课程的服高成绩要求列出学母、姓名、最福成绩。
76. SELECT grade.学号,姓名,MAX(分数)最高成绩
77. FROM grade,student_info
78. WHERE grade.学号=student_info.学号
79. GROUP BY grade.学号;
81. 7.4 使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课的学生的总成绩为空。
82. SELECT student_info.学号,姓名,SUM(分数) 总成绩 FROM student_info
83. LEFT OUTER JOIN grade ON grade.学号=student_info.学号
84. GROUP BY student_info.学号;
86. 7.5 为grade 表添加数据行：学号为 0004、课程编号为 0006、分数为75。
87.     使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum 表中没有的课程列值为空。
88. INSERT INTO grade VALUES('0004','0006',76);
90. SELECT grade.课程编号,课程名称,COUNT(学号) 选修人数 FROM curriculum
91. RIGHT OUTER JOIN grade ON curriculum.课程编号=grade.课程编号
92. GROUP BY 课程编号;

复制代码

实验三  索引和视图

1. 1.使用SQL 语句 ALTER TABLE 分别删除 studentsdb 数据库中 student_info表、grade 表、curriculum 表的主键索引。
2. USE studentsdb;
3. ALTER TABLE student_info DROP PRIMARY KEY;
4. ALTER TABLE grade DROP PRIMARY KEY;
5. ALTER TABLE curriculum DROP PRIMARY KEY;
7. 2.使用SQL 语句为 curriculum 表的课程编号创建唯一索引，命名为 cno_idx。
8. CREATE UNIQUE INDEX cno_idx ON curriculum(课程编号);
9. SHOW INDEX FROM curriculum;
11. 3.使用SQL 语句为 grade表中的“分数”字段创建一个普通索引，命名为 grade_idx。
12. CREATE INDEX grade_idx ON grade(分数);
13. SHOW INDEX FROM grade;
15. 4.使用SQL 语句grade表中的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_sid_cid_idx.
16. CREATE  UNIQUE INDEX grade_sid_cid_idx ON grade(学号,课程编号);
18. 5.查看 grade 表上的索引信息。
19. SHOW INDEX FROM grade;
21. 6.使用 SQL 语句删除索引 grade_idx，然后再次查看 grade 表上的索引信息。
22. DROP INDEX grade_idx ON grade;
23. SHOW INDEX FROM grade;
25. 7.使用SQL 语句 CREATE VIEW建立一个名v_stu_c的视图，显示学生的学号、姓名、所学课程的课程编号，并利用视图查询学号为 0003的学生的情况。
26. CREATE VIEW v_stu_c
27. AS
28. SELECT student_info.学号,姓名,课程编号 FROM student_info, grade
29. WHERE student_info.学号=grade.学号;
30. SELECT * FROM v_stu_c
31. WHERE 学号='0003';
33. 8. 基于 student_info 表、curriculum 表和grade表建立一个名为v_stu_g的视图，视图包括所有学生的学号、姓名、课程名称、分数。
34. 使用视图v_stu_g查询学号0001的学生的课程平均分。
35. CREATE VIEW v_stu_g
36. AS
37. SELECT student_info.学号,student_info.姓名, curriculum.课程名称 ,grade.分数
38. FROM student_info,curriculum,grade
39. WHERE student_info.学号=grade.学号
40. AND grade.课程编号=curriculum.课程编号;
41. SELECT AVG(分数) 平均分 FROM v_stu_g
42. WHERE 学号='0001';
44. 9.使用SQL 语句修改视图 v_stu_g，显示学生的学号、姓名、性别。
45. ALTER VIEW v_stu_g
46. AS
47. SELECT 学号,姓名,性别 FROM student_info;
49. 10. 利用视图 v_stu_g 为 student_ info 表添加一行数据：学号为 0010、姓名为陈婷婷、性别为女
50. INSERT INTO v_stu_g
51. VALUES('0010','陈婷婷','女');
53. 11. 利用视图v_stu_g删除学号为0010的学生的记录。
54. DELETE FROM v_stu_g
55. WHERE 学号='0010';
57. 12. 利用视图v_stu_g修改姓名为张青平的学生的高等数学的分数为87
58. CREATE OR REPLACE VIEW v_stu_g
59. AS
60. SELECT student_info.学号,student_info.姓名, curriculum.课程名称 ,grade.分数
61. FROM student_info,curriculum,grade
62. WHERE student_info.学号=grade.学号
63. AND grade.课程编号=curriculum.课程编号;
65. UPDATE v_stu_g
66. SET 分数=87
67. WHERE 姓名='张青平' AND 课程名称='高等数学';
69. 13. 使用SQL 语句删除视图v_stu_c和v_stu_g
70. DROP VIEW v_stu_c,v_stu_g;

复制代码

实验四  数据完整性

1. 1.创建 students数据库，在该数据库下创建stu表，并同时创建约束，表结构及约束要求如表 A-7所示。
2. CREATE DATABASE students;
3. USE students;
4. CREATE TABLE stu
5. (
6.     学号 CHAR(4) PRIMARY KEY,
7.                 姓名 CHAR(8),
8.                 性别 CHAR(2),
9.                 出生日期 DATE
10. );
11. DESC stu;
13. 2. 创建表sc，并同时创建约束，表结构及约束要求如表A-8所示。设置（学号，课号）为主键。
14. CREATE TABLE sc
15. (    学号 CHAR(4) NOT NULL,
16.      课号 CHAR(4) NOT NULL,
17.                  成绩 DECIMAL(5,2) CHECK (成绩 BETWEEN 0 AND 100),
18.                  PRIMARY KEY(学号,课号),
19.                  CONSTRAINT fk_sno FOREIGN KEY (学号) REFERENCES stu(学号)
20. );
21. DESC sc;
23. 3. 创建表 course，并同时创建约束，表结构及约束要求如表A-9所示。
24. CREATE TABLE course
25. (
26.                  课号 CHAR(4) NOT NULL,
27.                  课名 CHAR(20),
28.                  学分 INT,
29.                  CONSTRAINT uq_cname UNIQUE(课名)
30. );
31. DESC course;
33. 4. 在 course表的课号列上建立主键约束。
34. ALTER TABLE course
35. ADD PRIMARY KEY(课号);
36. DESC course;
38. 5. 在 sc 表的课号列上建立外键约束 fk_cno，参照 course 表中课号列的取值，要求实现级联更新。
39. ALTER TABLE sc
40. ADD CONSTRAINT fk_cno FOREIGN KEY(课号)REFERENCES course(课号)
41. ON UPDATE CASCADE;
43. 6. 在stu 表的姓名列上建立唯一约束名 uq_sname。
44. ALTER TABLE stu
45. ADD CONSTRAINT uq_sname UNIQUE(姓名);
46. DESC stu;
48. 7.在 course表的学分列上建立检查约束 ckxf，检查条件为学分＞0。
49. ALTER TABLE course
50. ADD CONSTRAINT ck_xf CHECK(学分>0);
52. 8. 删除sc 表的外键约束 tk_cno、tk_sno。
53. ALTER TABLE sc
54. DROP FOREIGN KEY fk_cno;
55. ALTER TABLE sc
56. DROP FOREIGN KEY fk_sno;
58. 9.删除 stu 表的主键约束。
59. ALTER TABLE stu
60. DROP PRIMARY KEY;
62. 10. 删除 course 表的唯一约束 uq_cname。
63. ALTER TABLE course
64. DROP INDEX uq_cname;
66. 11. 创建测试表 test，它包含一个字段 date_time，字段类型为 VARCHAR（50）。
67.     创建触发器 test_trig，实现在stu表中每插人一条学生记录自动在 test 表中追加一条插人成功时的日期时间。
68.     SYSDATE（）函数用来获取当前的日期和时间。
69.                 为stu 表插入一条记录引发触发器，查看 test 表中的内容。
70. CREATE TABLE test
71. (
72.      date_time VARCHAR(50)
73. );
74. CREATE TRIGGER test_trig
75. AFTER INSERT
76. ON stu
77. FOR EACH ROW
78. INSERT INTO test VALUES(SYSDATE());
79. INSERT INTO stu VALUES('1','Marry','F','1999-04-09');
80. SELECT * FROM test;
82. 12. 在 course表上创建触发器del_trig，当 course 表上删除一门课程时，级联删除 sc 表中该课程的记录。
83.     删除 course 表中的一条记录，查看 sc 表中的相应记录是否被自动删除。
84. CREATE TRIGGER del_trig
85. AFTER DELETE ON course
86. FOR EACH ROW
87. DELETE FROM SC WHERE 课号=OLD.课号;
88. DELETE FROM course WHERE 课号='1';
89. SELECT*FROM SC;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。