MySQL数据库练习

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;

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

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

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

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

SELECT stuno FROM score WHERE score=100;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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