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

实验一 数据库和表的管理

1.使用 SQL 语句创建数据库 studentsdb。
CREATE DATABASE studentsdb;

2.使用SQL 语句选择 studentsdb 为当前使用的数据库。
USE studentsdb;

3.1.使用 SQL 语句在 studentsdb 数据库中创建数据表 student_info、curriculum、grade，3个表的数据结构如表A-1～表A-3所示。
USE studentsdb;
CREATE TABLE student_info
(
    学号    char(4) PRIMARY KEY,
    姓名    char(8) NOT NULL,
    性别    char(2),
    出生日期    date,
    家庭住址    varchar(50)
);
DESC student_info;

4.
INSERT INTO student_info(学号,姓名,性别,出生日期,家庭住址)
VALUES ('0001','张青平','男','2000-10-01','衡阳市东风路77号'),
       ('0002','刘东阳','男','1998-12-09','东阳市八一北路33号'),
       ('0003','马晓夏','女','1995-05-12','长岭县五一路763号'),
       ('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号'),
       ('0005','孙海洋','男','1995-04-03','长岛县解放路27号'),
       ('0006','郭小斌','男','1997-11-10','南山市红旗路113号'),
       ('0007','肖月玲','女','1996-12-07','东方市南京路11号'),
       ('0008','张玲珑','女','1997-12-24','滨江市新建路97号');
SELECT * FROM student_info;

3.2使用 SQL 语句在 studentsdb 数据库中创建数据表 student_info、curriculum、grade，3个表的数据结构如表A-1～表A-3所示。
CREATE TABLE curriculum 
(
    课程编号    char(4) PRIMARY KEY,
    课程名称    varchar(50),
    学分    int
);
DESC curriculum;

4.2
INSERT INTO curriculum(课程编号,课程名称,学分)
VALUES ('0001','计算机应用基础',2),
       ('0002','C语言程序设计',2),
       ('0003','数据库原理及应用',2),
       ('0004','英语',4),
       ('0005','高等数学',4);
SELECT * FROM curriculum;

3.3使用 SQL 语句在 studentsdb 数据库中创建数据表 student_info、curriculum、grade，3个表的数据结构如表A-1～表A-3所示。
CREATE TABLE grade
(
    学号    char(4),
    课程编号    char(4),
    分数    int,
    PRIMARY KEY(学号,课程编号) 
);
DESC grade;

4.3
INSERT INTO grade(学号,课程编号,分数)
VALUES ('0001','0001',80),
       ('0001','0002',91),
       ('0001','0003',88),
       ('0001','0004',85),
       ('0001','0005',77),
       ('0002','0001',73),
       ('0002','0002',68),
       ('0002','0003',80),
       ('0002','0004',79),
       ('0002','0005',73),
       ('0003','0001',84),
       ('0003','0002',92),
       ('0003','0003',81),
       ('0003','0004',82),
       ('0003','0005',75);
SELECT * FROM grade;

5.使用SQL语句ALTER TABLE修改curriculum表的“课程名称”列，使之为空
ALTER TABLE curriculum
MODIFY 课程名称 VARCHAR(50) NULL;
DESC curriculum;

6.使用SQL语句ALTER TABLE修改grade表的“分数”列，使其数据类型为decimal(5,2)
ALTER TABLE grade
MODIFY 分数 DECIMAL(5,2);
DESC grade;

7.使用SQL语句ALTER TABLE为student_info表添加一个名为“备注”的数据列，其数据类型为varchar(50)
ALTER TABLE student_info
ADD 备注 VARCHAR(50);
DESC student_info;

8.使用SQL语句创建数据库studb，并在此数据库下创建表stu，表结构与数据与studentsdb的student_info表相同
CREATE DATABASE studb;
USE studb;
CREATE TABLE stu
SELECT * FROM studentsdb.student_info;
DESC stu;

9.使用SQL语句删除表stu中学号为0004的记录
DELETE FROM stu WHERE 学号 = '0004';
SELECT * FROM stu;

10.使用SQL语句更新表stud中学号为0002的家族住址为“滨江市新建路96号”
UPDATE stu
    SET 家庭住址 = '滨江市新建路96号' WHERE 学号 = '0002' ;
SELECT * FROM stu;

11.删除表stud的“备注”列
ALTER TABLE stu
    DROP COLUMN 备注;

12.删除表stud
DROP TABLE stu;

13.删除数据库studb
DROP DATABASE studb;

实验二 数据查询

1.1 在student_info 表中查询每个学生的学号、姓名、出生日期信息。
USE studentsdb;
SELECT 学号,姓名,出生日期 FROM student_info;

1.2 查询 student_info 表中学号为0002的学生的姓名和家庭住址。
SELECT 姓名,家庭住址 FROM student_info
WHERE 学号='0002';

1.3 查询 student info 表中所有出生日期在1995年以后的女同学的姓名和出生目期。
SELECT 姓名,出生日期 FROM student_info
WHERE 性别 = '女' AND 出生日期 > '1995-12-31';

2.1 在grade表中查询分数在70~80的学生的学号、课程编号和成绩。
SELECT 学号,课程编号,分数 FROM grade
WHERE 分数 >70 AND 分数 <80;

2.2 在grade表中查询课程编号0002的学生的平均成绩。
SELECT AVG(分数)AS 平均成绩 FROM grade
WHERE 课程编号='0002';

2.3 在 grade 表中查询选修课程编号为0003的人数和该课程有成绩的人数。
SELECT COUNT(学号) AS 选修课程编号为0003的人数,COUNT(分数) AS 有成绩的人数 FROM grade 
WHERE 课程编号 = '0003' ;

2.4 查询 student_info表中学生的姓名和出生日期，查询结果按出生日期从大到小排序。
SELECT 姓名,出生日期FROM student_info
ORDER BY 出生日期 DESC;

2.5 查询所有姓“张”的学生的学号和姓名。
SELECT 学号,姓名 FROM student_info
WHERE 姓名 LIKE '张%';

3.对于 student_info 表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别排序，性别相同的再按学号由大到小排序。
SELECT 学号,姓名,性别,出生日期,家庭住址 FROM student_info
ORDER BY 性别,学号 DESC;

4. 使用GROUP BY 子句查询grade表中各个学生的平均成绩。
SELECT 学号,AVG(分数) AS 平均成绩 FROM grade
GROUP BY 学号;

5. 使用 UNION运算符将 student_info表中姓“刘”学生的学号、姓名与姓“张”学生的学号、姓名返回在一个表中。
SELECT 学号,姓名 FROM student_info
WHERE 姓名 LIKE '刘%'
UNION 
SELECT 学号,姓名 FROM student_info 
WHERE 姓名 LIKE '张%';

6.1 在student_info 表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。
SELECT 姓名,出生日期 FROM student_info
WHERE 性别 = (SELECT 性别 FROM  student_info WHERE 姓名='刘东阳');

6.2 使用IN子查询查找所修课程编号为0002、0005的学生的学号、姓名、性别。
SELECT 学号,姓名,性别 FROM student_info
WHERE  学号 IN(SELECT (学号) FROM grade WHERE 课程编号='0002' OR 课程编号='0005');

6.3 使用ANY子查询查找学号为0001 的学生的分数比学号为0002的学生的最低分数高的课程编号和分数
SELECT 课程编号,分数 FROM grade  
WHERE 学号='0001' AND 分数>ANY(SELECT MIN(分数) FROM grade WHERE 学号='0002');

6.4使用ALL子查询查找学号为0001 的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。
SELECT 课程编号,分数 FROM grade 
WHERE 学号='0001' AND 分数>ALL(SELECT MAX(分数) FROM grade WHERE 学号='0002');

7.1 查询分数为80~90的学生的学号、姓名和分数。
SELECT grade.学号,姓名,分数 FROM grade,student_info
WHERE grade.学号=student_info.学号
AND 分数 BETWEEN 80 and 90;

7.2 使用INNBR JOIN 连接方式登阅学习*數据库原理及应用”课程的学生的学号、姓名、分数。
SELECT grade.学号, 姓名,分数 FROM  grade
INNER JOIN student_info  ON student_info.学号=grade.学号
INNER JOIN curriculum ON curriculum.课程编号=grade.课程编号 
WHERE curriculum.课程名称='数据库原理及应用';

7.3 查询每个学生所选课程的服高成绩要求列出学母、姓名、最福成绩。
SELECT grade.学号,姓名,MAX(分数)最高成绩
FROM grade,student_info
WHERE grade.学号=student_info.学号
GROUP BY grade.学号;

7.4 使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课的学生的总成绩为空。
SELECT student_info.学号,姓名,SUM(分数) 总成绩 FROM student_info
LEFT OUTER JOIN grade ON grade.学号=student_info.学号
GROUP BY student_info.学号;

7.5 为grade 表添加数据行：学号为 0004、课程编号为 0006、分数为75。
    使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum 表中没有的课程列值为空。
INSERT INTO grade VALUES('0004','0006',76);

SELECT grade.课程编号,课程名称,COUNT(学号) 选修人数 FROM curriculum
RIGHT OUTER JOIN grade ON curriculum.课程编号=grade.课程编号
GROUP BY 课程编号;

实验三 索引和视图

1.使用SQL 语句 ALTER TABLE 分别删除 studentsdb 数据库中 student_info表、grade 表、curriculum 表的主键索引。
USE studentsdb; 
ALTER TABLE student_info DROP PRIMARY KEY;
ALTER TABLE grade DROP PRIMARY KEY;
ALTER TABLE curriculum DROP PRIMARY KEY;

2.使用SQL 语句为 curriculum 表的课程编号创建唯一索引，命名为 cno_idx。
CREATE UNIQUE INDEX cno_idx ON curriculum(课程编号);
SHOW INDEX FROM curriculum;

3.使用SQL 语句为 grade表中的“分数”字段创建一个普通索引，命名为 grade_idx。
CREATE INDEX grade_idx ON grade(分数);
SHOW INDEX FROM grade;

4.使用SQL 语句grade表中的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_sid_cid_idx.
CREATE  UNIQUE INDEX grade_sid_cid_idx ON grade(学号,课程编号);

5.查看 grade 表上的索引信息。
SHOW INDEX FROM grade;

6.使用 SQL 语句删除索引 grade_idx，然后再次查看 grade 表上的索引信息。
DROP INDEX grade_idx ON grade;
SHOW INDEX FROM grade;

7.使用SQL 语句 CREATE VIEW建立一个名v_stu_c的视图，显示学生的学号、姓名、所学课程的课程编号，并利用视图查询学号为 0003的学生的情况。
CREATE VIEW v_stu_c 
AS
SELECT student_info.学号,姓名,课程编号 FROM student_info, grade
WHERE student_info.学号=grade.学号;
SELECT * FROM v_stu_c
WHERE 学号='0003';

8. 基于 student_info 表、curriculum 表和grade表建立一个名为v_stu_g的视图，视图包括所有学生的学号、姓名、课程名称、分数。
使用视图v_stu_g查询学号0001的学生的课程平均分。
CREATE VIEW v_stu_g
AS
SELECT student_info.学号,student_info.姓名, curriculum.课程名称 ,grade.分数 
FROM student_info,curriculum,grade
WHERE student_info.学号=grade.学号 
AND grade.课程编号=curriculum.课程编号;
SELECT AVG(分数) 平均分 FROM v_stu_g
WHERE 学号='0001';

9.使用SQL 语句修改视图 v_stu_g，显示学生的学号、姓名、性别。
ALTER VIEW v_stu_g
AS
SELECT 学号,姓名,性别 FROM student_info;

10. 利用视图 v_stu_g 为 student_ info 表添加一行数据：学号为 0010、姓名为陈婷婷、性别为女
INSERT INTO v_stu_g 
VALUES('0010','陈婷婷','女');

11. 利用视图v_stu_g删除学号为0010的学生的记录。
DELETE FROM v_stu_g 
WHERE 学号='0010';

12. 利用视图v_stu_g修改姓名为张青平的学生的高等数学的分数为87
CREATE OR REPLACE VIEW v_stu_g
AS
SELECT student_info.学号,student_info.姓名, curriculum.课程名称 ,grade.分数 
FROM student_info,curriculum,grade
WHERE student_info.学号=grade.学号 
AND grade.课程编号=curriculum.课程编号;

UPDATE v_stu_g
SET 分数=87
WHERE 姓名='张青平' AND 课程名称='高等数学';

13. 使用SQL 语句删除视图v_stu_c和v_stu_g
DROP VIEW v_stu_c,v_stu_g;

实验四 数据完整性

1.创建 students数据库，在该数据库下创建stu表，并同时创建约束，表结构及约束要求如表 A-7所示。
CREATE DATABASE students;
USE students;
CREATE TABLE stu
(
    学号 CHAR(4) PRIMARY KEY,
        姓名 CHAR(8),
        性别 CHAR(2),
        出生日期 DATE
);
DESC stu;

2. 创建表sc，并同时创建约束，表结构及约束要求如表A-8所示。设置（学号，课号）为主键。
CREATE TABLE sc
(    学号 CHAR(4) NOT NULL,
     课号 CHAR(4) NOT NULL,
         成绩 DECIMAL(5,2) CHECK (成绩 BETWEEN 0 AND 100),
         PRIMARY KEY(学号,课号),
         CONSTRAINT fk_sno FOREIGN KEY (学号) REFERENCES stu(学号)
);
DESC sc;

3. 创建表 course，并同时创建约束，表结构及约束要求如表A-9所示。
CREATE TABLE course
(
         课号 CHAR(4) NOT NULL,
         课名 CHAR(20),
         学分 INT,
         CONSTRAINT uq_cname UNIQUE(课名)
);
DESC course;

4. 在 course表的课号列上建立主键约束。
ALTER TABLE course
ADD PRIMARY KEY(课号);
DESC course;

5. 在 sc 表的课号列上建立外键约束 fk_cno，参照 course 表中课号列的取值，要求实现级联更新。
ALTER TABLE sc
ADD CONSTRAINT fk_cno FOREIGN KEY(课号)REFERENCES course(课号)
ON UPDATE CASCADE;

6. 在stu 表的姓名列上建立唯一约束名 uq_sname。
ALTER TABLE stu
ADD CONSTRAINT uq_sname UNIQUE(姓名);
DESC stu;

7.在 course表的学分列上建立检查约束 ckxf，检查条件为学分＞0。
ALTER TABLE course
ADD CONSTRAINT ck_xf CHECK(学分>0);

8. 删除sc 表的外键约束 tk_cno、tk_sno。
ALTER TABLE sc
DROP FOREIGN KEY fk_cno;
ALTER TABLE sc
DROP FOREIGN KEY fk_sno;

9.删除 stu 表的主键约束。
ALTER TABLE stu
DROP PRIMARY KEY;

10. 删除 course 表的唯一约束 uq_cname。
ALTER TABLE course
DROP INDEX uq_cname;

11. 创建测试表 test，它包含一个字段 date_time，字段类型为 VARCHAR（50）。
    创建触发器 test_trig，实现在stu表中每插人一条学生记录自动在 test 表中追加一条插人成功时的日期时间。
    SYSDATE（）函数用来获取当前的日期和时间。
        为stu 表插入一条记录引发触发器，查看 test 表中的内容。
CREATE TABLE test
(
     date_time VARCHAR(50)
);
CREATE TRIGGER test_trig
AFTER INSERT
ON stu
FOR EACH ROW
INSERT INTO test VALUES(SYSDATE());
INSERT INTO stu VALUES('1','Marry','F','1999-04-09');
SELECT * FROM test;

12. 在 course表上创建触发器del_trig，当 course 表上删除一门课程时，级联删除 sc 表中该课程的记录。
    删除 course 表中的一条记录，查看 sc 表中的相应记录是否被自动删除。
CREATE TRIGGER del_trig
AFTER DELETE ON course
FOR EACH ROW
DELETE FROM SC WHERE 课号=OLD.课号;
DELETE FROM course WHERE 课号='1';
SELECT*FROM SC;