数据库基础操作练习

必要软件

• MySQL
• Navicat Premium16（其它软件也可以，例如SQLyog）

如果不会安装MySQL，请查看我的 MySQL 安装篇
Navicat Premium16 下载地址：https://www.navicat.com.cn/download/navicat-premium

题目

（1） 创建一个名为“st”的数据库。后续操作均在此数据库下完成。

（2） 参照下表结构创建学生“student”表。

属性	类型	长度	是否为空	主码	外码	其它约束	说明
Sno	char	9		Y			学号
Sname	varchar	20	N				姓名
Ssex	char	2	Y			[男，女]	性别
Sage	smallint		Y				年龄
Sdept	varchar	20	Y				系别

（3） 参照下表结构创建课程“course”表。

属性	类型	长度	是否为空	主码	外码	其它约束	说明
Cno	char	4		Y			课程号
Cname	varchar	50	N			UNIQUE	课程名
Cpno	char	4	Y		Y		先修课程号 参照本表的Cno
Ccredit	smallint		N				学分

（4） 参照下表结构创建学生选课“sc”表。

属性	类型	长度	是否为空	主码	外码	其它约束	说明
Sno	char	9	N	Y	Y		学号 参照学生表的Sno
Cno	char	4	N	Y		课程号 参照课程表的Cno
Grade	smallint		Y			[0，100]	成绩

（5） 在MySQL可视化工具中，选中（激活） st 数据库，并执行下面提供的data.sql文件，导入学生、课程和学生选课表数据。

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`st` /*!40100 DEFAULT CHARACTER SET utf8 */;


/*Data for the table `student` */

insert  into `student`(`Sno`,`Sname`,`Ssex`,`Sage`,`Sdept`) values ('201615121','李勇','男',20,'CS'),('201615122','刘晨','女',19,'CS'),('201615123','王敏','女',18,'MA'),('201615124','马阳晨','男',20,'PH'),('201615125','张立','男',19,'IS'),('201615126','季晨','男',21,'CS'),('201615127','汪铭','女',19,'CS'),('201615128','李娜','女',19,'MA'),('201615129','欧阳鹏','男',20,'IS'),('201615130','刘志恒','男',18,'IS'),('201615131','黄文军','男',20,'CS'),('201615132','沈青遥','女',19,'PH'),('201615133','袁浙时','女',18,'MA'),('201615134','马晨佳','女',20,'MA'),('201615135','林子','男',19,'IS'),('201615136','沈辉','男',23,'CS'),('201615137','蒋怡雨','女',19,'PH'),('201615138','陈雨箫','女',22,'MA'),('201615139','刘阳春','男',20,'IS');


/*Data for the table `course` */

insert  into `course`(`Cno`,`Cname`,`Cpno`,`Ccredit`) values ('1','数据库','5',4),('2','数学',NULL,2),('3','信息系统','1',4),('4','操作系统','6',3),('5','数据结构','7',4),('6','数据处理',NULL,2),('7','PASCAL语言','6',4),('8','DB_Design',NULL,3);


/*Data for the table `sc` */

insert  into `sc`(`Sno`,`Cno`,`Grade`) values ('201615121','1',92),('201615121','2',85),('201615121','3',88),('201615122','2',90),('201615122','3',80),('201615123','1',95),('201615123','3',93),('201615123','4',83),('201615124','2',94),('201615124','3',87),('201615124','4',75),('201615125','1',98),('201615125','2',91),('201615125','5',78);

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

（6） 将一个新学生元组（学号：201615140；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入到 Student 表中。

（7） 在表SC中，插入一条选课记录( '201615128', '1' )。

（8） 在数据库中创建一个表Dept_age，然后对每一个系求学生的平均年龄，最后把结果存入表Dept_age中。

（9） 将学生201615121的年龄改为22岁。

（10） 将所有学生的数学成绩增加3分。

（11） 将计算机科学系全体女学生的成绩置零。

（12） 将学生号为201615140的学生所属的系改为空值。

（13） 删除学号为201615140的学生记录。

（14） 删除计算机科学系所有学生的选课记录。

（15） 查询全体学生的详细记录。

（16） 查全体学生的姓名及其出生年份（出生年份通过获得系统当前日期进行计算，且属性别名为“Birth”）。

（17） 查询选修了课程的学生学号（选修了多门课程的学生只显示一次）。

（18） 查询计算机科学系全体学生的名单。

（19） 查询所有年龄在 20 岁以下的学生姓名及其年龄。

（20） 查询考试成绩有不及格的学生的学号。

（21） 查询年龄在20~23岁（包括20岁和23岁）之间的学生的姓名、系别和年龄。

（22） 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别。

（23） 查询既不是信息系、数学系，也不是计算机科学系的学生的姓名和性别。

（24） 查询所有姓刘学生的姓名、学号和性别。

（25） 查询所有姓刘的单名的学生的姓名、学号和性别。

（26） 查询姓"欧阳"且全名为三个汉字的学生的姓名。

（27） 查询名字中第2个字为"阳"字的学生的姓名和学号。

（28） 查询所有不姓刘的学生姓名。

（29） 查询 DB_Design 课程的课程号和学分。

（30） 查询以 "DB_" 开头，且倒数第3个字符为 i 的课程的详细情况。

（31） 查询缺少成绩的学生的学号和相应的课程号。

（32） 查询没有先修课程的课程的课程号、课程名和学分。

（33） 查所有有成绩的学生学号和课程号。

（34） 查询计算机系年龄在20岁以下的学生姓名。

（35） 查询选修了3号课程的学生的学号及其成绩，查询结果按分数降序排列。

（36） 查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列。

答案

1. CREATE DATABASE IF NOT EXISTS st ;

2. -- 如果还没有选择st数据库的话 记得加上USE st; 后面也一样
   CREATE TABLE student (
       Sno CHAR(9) COMMENT '学号',
       Sname VARCHAR(20) NOT NULL COMMENT '姓名',
       Ssex CHAR(2) NULL CHECK (Ssex IN ('男', '女')) COMMENT '性别',
       Sage SMALLINT NULL COMMENT '年龄',
       Sdept VARCHAR(20) NULL COMMENT '系别',
       PRIMARY KEY (Sno)
   ) COMMENT='学生表';

3. CREATE TABLE course (
       Cno CHAR(4) COMMENT '课程号',
       Cname VARCHAR(50) NOT NULL UNIQUE COMMENT '课程名',
       Cpno CHAR(4) NULL COMMENT '先修课程号',
       Ccredit SMALLINT NOT NULL COMMENT '学分',
       PRIMARY KEY (Cno),
       FOREIGN KEY (Cpno) REFERENCES course(Cno)  -- 添加外键约束，参照本表的Cno
   ) COMMENT='课程表';

4. CREATE TABLE sc (
       Sno CHAR(9) COMMENT '学号',
       Cno CHAR(4) COMMENT '课程号',
       Grade SMALLINT NULL CHECK (Grade BETWEEN 0 AND 100) COMMENT '成绩',
       PRIMARY KEY (Sno, Cno),  -- 联合主键
       FOREIGN KEY (Sno) REFERENCES student(Sno),  -- 学号外键
       FOREIGN KEY (Cno) REFERENCES course(Cno)     -- 课程号外键
   ) COMMENT='学生选课表';

5. 跟着执行就行了

6. INSERT INTO student (Sno, Sname, Ssex, Sdept, Sage) 
   VALUES ('201615140', '陈冬', '男', 'IS', 18);

7. INSERT INTO sc (Sno, Cno) 
   VALUES ('201615128', '1');

8. CREATE TABLE Dept_age (
       Sdept VARCHAR(20) NOT NULL COMMENT '系别',
       Avg_Age DECIMAL(5, 2) NOT NULL COMMENT '平均年龄',
       PRIMARY KEY (Sdept)
   ) COMMENT='系别学生平均年龄表';
   
   INSERT INTO Dept_age (Sdept, Avg_Age)
   SELECT Sdept, AVG(Sage) AS Avg_Age
   FROM student
   GROUP BY Sdept;

9. UPDATE student 
   SET Sage = 22 
   WHERE Sno = '201615121';

10. UPDATE sc
    SET Grade = Grade + 3
    WHERE Cno = (
    	SELECT Cno
    	FROM course
    	WHERE Cname = '数学'
    );

11. UPDATE sc 
    SET Grade = 0 
    WHERE Sno IN (
        SELECT Sno 
        FROM student 
        WHERE Sdept = 'CS' AND Ssex = '女'
    );

12. UPDATE student 
    SET Sdept = NULL 
    WHERE Sno = '201615140';

13. DELETE FROM student 
    WHERE Sno = '201615140';

14. DELETE FROM sc 
    WHERE Sno IN (
        SELECT Sno 
        FROM student 
        WHERE Sdept = 'CS'
    );

15. SELECT * FROM student;

16. SELECT Sname, YEAR(CURDATE()) - Sage AS Birth
    FROM student;

17. SELECT DISTINCT Sno 
    FROM sc;

18. SELECT *
    FROM student 
    WHERE Sdept = 'CS';

19. SELECT Sname, Sage 
    FROM student 
    WHERE Sage < 20;

20. SELECT DISTINCT Sno 
    FROM sc 
    WHERE Grade < 60;

21. SELECT Sname, Sdept, Sage 
    FROM student 
    WHERE Sage BETWEEN 20 AND 23;

22. SELECT Sname, Ssex 
    FROM student 
    WHERE Sdept IN ('IS', 'MA', 'CS');

23. SELECT Sname, Ssex 
    FROM student 
    WHERE Sdept NOT IN ('IS', 'MA', 'CS');

24. SELECT Sname, Sno, Ssex 
    FROM student 
    WHERE Sname LIKE '刘%';

25. SELECT Sname, Sno, Ssex 
    FROM student 
    WHERE Sname LIKE '刘_';

26. SELECT Sname 
    FROM student 
    WHERE Sname LIKE '欧阳__';

27. SELECT Sname, Sno 
    FROM student 
    WHERE Sname LIKE '_阳%';

28. SELECT Sname 
    FROM student 
    WHERE Sname NOT LIKE '刘%';

29. SELECT Cno, Ccredit 
    FROM course 
    WHERE Cname = 'DB_Design';

30. SELECT *
    FROM course
    WHERE Cname LIKE 'DB\_%i__';

31. SELECT Sno, Cno 
    FROM sc 
    WHERE Grade IS NULL;

32. SELECT Cno, Cname, Ccredit 
    FROM course 
    WHERE Cpno IS NULL;

33. SELECT Sno, Cno 
    FROM sc 
    WHERE Grade IS NOT NULL;

34. SELECT Sname 
    FROM student 
    WHERE Sdept = 'CS' AND Sage < 20;

35. SELECT Sno, Grade 
    FROM sc 
    WHERE Cno = '3'
    ORDER BY Grade DESC;

36. SELECT * 
    FROM student 
    ORDER BY Sdept, Sage DESC;