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的基本语法,谢谢~