本文共 6645 字，大约阅读时间需要 22 分钟。

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` setgradeid=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 studentWHERE `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的基本语法，谢谢~

转载地址：http://segdi.baihongyu.com/