MySQL数据库练习_mysql 50731

MySQL数据库练习

导入数据库

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50731
 Source Host           : localhost:3306
 Source Schema         : e

 Target Server Type    : MySQL
 Target Server Version : 50731
 File Encoding         : 65001

 Date: 12/03/2021 18:06:03
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for grade
-- ----------------------------
DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade` (
  `gradeid` int(11) NOT NULL,
  `gname` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`gradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of grade
-- ----------------------------
BEGIN;
INSERT INTO `grade` VALUES (1, '第一阶段');
INSERT INTO `grade` VALUES (2, '第二阶段');
INSERT INTO `grade` VALUES (3, '第三阶段');
INSERT INTO `grade` VALUES (4, '就业期');
COMMIT;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `scoreid` int(11) NOT NULL,
  `stuno` varchar(20) DEFAULT NULL,
  `subjectid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `examtime` date DEFAULT NULL,
  PRIMARY KEY (`scoreid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES (1, '1', 1, 100, '2021-03-12');
INSERT INTO `score` VALUES (2, '1', 2, 100, '2021-03-12');
INSERT INTO `score` VALUES (3, '1', 3, 90, '2021-03-12');
INSERT INTO `score` VALUES (4, '2', 1, 100, '2021-03-12');
INSERT INTO `score` VALUES (5, '2', 2, 79, '2021-03-18');
INSERT INTO `score` VALUES (6, '3', 1, 56, '2021-03-01');
INSERT INTO `score` VALUES (7, '4', 2, 55, '2021-03-15');
INSERT INTO `score` VALUES (8, '4', 3, 100, '2021-03-21');
INSERT INTO `score` VALUES (9, '4', 1, 99, '2021-03-04');
INSERT INTO `score` VALUES (10, '5', 1, 100, '2021-03-21');
COMMIT;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `stuid` varchar(11) NOT NULL,
  `stuname` varchar(20) DEFAULT NULL,
  `password` varchar(20) DEFAULT NULL,
  `sex` varchar(20) DEFAULT NULL,
  `gid` int(11) DEFAULT NULL,
  `telphone` varchar(20) DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `email` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`stuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1', '张三', 'sdaf45', '男', 2, '131333333', '山东', '1989-03-12', '423412345@qq.com');
INSERT INTO `student` VALUES ('2', '李四', 'fdg7fd3', '女', 1, '131333333', '廊坊', '1999-03-18', '456346@qq.com');
INSERT INTO `student` VALUES ('3', '王五', 'rt7er8', '男', 4, '131333333', '山西', '1995-06-16', '8474154@qq.com');
INSERT INTO `student` VALUES ('4', '赵四', 'cvbc41', '女', 3, '131333333', '内蒙古', '1997-08-22', '789456@qq.com');
INSERT INTO `student` VALUES ('5', '王六', 'ewr789wx', '男', 3, '131333333', '台湾', '2000-12-16', '97456@qq.com');
INSERT INTO `student` VALUES ('6', '钱七', 'jty465tr', '女', 1, '131333333', '深圳', '1880-03-09', '78971@qq.com');
INSERT INTO `student` VALUES ('7', '金蝶', 'y4tr84', '女', 2, '123131313', '北京', '1898-01-12', '484564@qq.com');
INSERT INTO `student` VALUES ('8', '凌凌七', 'd4fs56', '男', 1, '123131313', '山东', '1997-12-19', '4564@qq.com');
INSERT INTO `student` VALUES ('9', '凌厉', '4fd5s6', '男', 1, '123131313', '深圳', '1990-01-01', '7786@qq.com');
COMMIT;

-- ----------------------------
-- Table structure for subject
-- ----------------------------
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject` (
  `subjectid` int(11) NOT NULL,
  `subjectname` varchar(20) DEFAULT NULL,
  `studycount` int(11) DEFAULT NULL,
  `gradeid` int(11) DEFAULT NULL,
  PRIMARY KEY (`subjectid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of subject
-- ----------------------------
BEGIN;
INSERT INTO `subject` VALUES (1, '基于.NET 平台的软件系统分层开发', 80, 1);
INSERT INTO `subject` VALUES (2, '设计 MySchool 数据库', 30, 1);
INSERT INTO `subject` VALUES (3, '面向对象程序设计', 20, 1);
INSERT INTO `subject` VALUES (4, '基于.NET 平台的软件系统分层开发', 50, 2);
INSERT INTO `subject` VALUES (5, '设计 MySchool 数据库', 70, 2);
INSERT INTO `subject` VALUES (6, '面向对象程序设计', 64, 2);
INSERT INTO `subject` VALUES (7, '基于.NET 平台的软件系统分层开发', 55, 3);
INSERT INTO `subject` VALUES (8, '设计 MySchool 数据库', 35, 3);
INSERT INTO `subject` VALUES (9, '面向对象程序设计', 75, 3);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128

导入完毕即可开始MySQL的练习之旅。

1.将第三阶段的学生的 gradeid 改为就业期的 id

UPDATE `subject` set
gradeid=4 WHERE gradeid=3;
1
2

2.查询所有得了100分的学号

SELECT stuno FROM score WHERE score=100;
1

3.查询所有1989年出生的学生(1989-1-1~1990-1-1)

select * from student where birthday between '19890101' and '19900101'
1

4.查询学生姓名为“金蝶”的全部信息

SELECT * FROM student
WHERE `stuname`='金蝶';
1
2

5.查询subjectid为1002的科自考试未及格(60分)的学号和成绩

SELECT stuid,score FROM score,student WHERE subjectid=2 AND score<60;
1

6.查询第3阶段课时为50天的课程全部信息

SELECT * FROM `subject` WHERE gradeid=3 AND studycount>50;
--由于问题1已经将id=3改为id=4，所以在连续练习的情况下可以把gradeid=3改为gradeid=4
1
2

7.查询 S1101001 学生的考试信息

--暂无(表中无此学号的学生)
1

8.查询所有第二阶段的女生信息

SELECT * FROM student WHERE gid=2 AND sex='女';
1

9.“基于.NET 平台的软件系统分层开发”需要多少课时

SELECT subjectname,gradeid,studycount FROM `subject` WHERE subjectname='基于.NET 平台的软件系统分层开发';
1

10.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in)

SELECT subjectname,gradeid,studycount FROM `subject` WHERE subjectname IN ('设计 MySchool 数据库','面向对象程序设计');
1

11.查询所有地址在山东的学生信息

SELECT * FROM student WHERE address='山东';
1

12.查询所有姓凌的单名同学

SELECT * FROM student WHERE stuname LIKE '凌_';
--'凌%'会输入双名同学信息，此处应为'凌_'
1
2

13.查询 gradeid 为 1 的学生信息，按出生日期升序排序

SELECT * FROM student WHERE gid=1 ORDER BY birthday;
1

14.查询 subjectid 为 3 的考试的成绩信息，用降序排序

SELECT * FROM score WHERE subjectid=3 ORDER BY score DESC;
1

15.查询 gradeid 为 2 的课程中课时最多的课程信息

SELECT * FROM `subject` WHERE gradeid=2 AND studycount=(SELECT MAX(studycount) FROM `subject` WHERE gradeid=2);
1

16.查询北京的学生有多少个

SELECT COUNT(*) AS 个数 FROM student WHERE address='北京';
1

17.查询有多少个科目学时小于 50

SELECT COUNT(*) AS 个数 FROM `subject` WHERE studycount<50;
1

18.查询 gradeid 为 2 的阶段总课时是多少

SELECT SUM(studycount) AS 总课时 FROM `subject` WHERE gradeid=2;
1

19.查询 subjectid 为 8 的课程学生平均分

SELECT AVG(score) FROM score WHERE subjectid=8;
--这里表没有subjectid=8的信息
1
2

20.查询 gradeid 为 3 的课程中最多的学时和最少的学时

SELECT MAX(studycount),MIN(studycount) FROM `subject` WHERE gradeid=4;
--可以将guadeid=4改为3
1
2

21.查询每个科目有多少人次考试

暂无(subject表中只有课时和阶段号，无多少人考试相关信息)
1

22.每个阶段课程的平均课时

SELECT gradeid,AVG(studycount) FROM `subject` GROUP BY gradeid
1

23.查询每个阶段的男生和女生个数（group by 两列）

SELECT gid,sex,COUNT(stuid) as 个数
FROM student GROUP BY gid,sex;
1
2

以上练习希望大家能够尽快掌握MySQL的基本语法，谢谢~