数据库 - 基本操作练习(练完就会)

文章目录

任务

数据表:
年级表:
年级id(主键),年级名称成绩表:id(主键),学员编号，科目id,分数，考试时间
学生表:
学生编号(主键)，学生姓名，登录密码，性别，年级id,电话，地址，出生日期，email
科目表:
科目id(主键),科目名称，学时，年级id

grade表增加一个字段，“就业期”
将第三阶段的学生的gradeid改为就业期的id
查询得了100分的学号
查询所有1989年出生的学生(1989-1-1~1990-1-1)
查询学生姓名为“金蝶”的全部信息
查询subjectid为1002的科自考试未及格(60分)的学号和成绩
查询第3阶段课时为50天的课程全部信息
查询11111学生的考试信息
查询所有第二阶段的女生信息
“语文”需要多少课时
查询“数学”和“英语”的课时(使用in)
查询所有地址在山东的学生信息
查询所有姓凌的单名同学
查询gradeid为11的学生信息，按出生日期升序排序
查询subjectid为1004的考试的成绩信息，用降序排序
查询gradeid为12的课程中课时最多的课程信息
查询木星的学生有多少个
查询有多少个科目学时小于50
查询gradeid为12的阶段总课时是多少
查询subjectid为1001的课程学生平均分
查询gradeid为12的课程中最多的学时和最少的学时
查询每个科目有多少人次考试
每个阶段课程的平均课时
查询每个阶段的男生和女生个数(group by 两列)

创建表

#年级表
create table grades(
	grade_id int unsigned not null primary key,
    grade_name varchar(8) not null
)

#成绩表
create table score(
	id int unsigned not null primary key auto_increment,
    stu_num varchar(8) not null,
    subject_id varchar(8) not null,
    score int,
    exam_time DATE
)

#学生表
create table students(
	stu_num varchar(8) not null primary key,
    name char(6) not null,
    password char(32) not null,
    sex enum('男','女') not null,
    grade_id int unsigned not null,
    phone varchar(11),
    address varchar(30),
    birthday DATE,
    email varchar(30)
)

#科目表
create table subjects(
	id int unsigned not null primary key,
    subject_name varchar(20) not null,
    period int not null,
    grade_id int not null
)

基本操作练习

#1. grade表增加一个字段，“就业期”
alter table grades add employee_period char(4);

#2. 将第三阶段的学生的gradeid改为就业期的id
insert into grade VALUES(5,'就业期');

#3. 查询得了100分的学号
select stu_num from score where score = 100;

#4. 查询所有1989年出生的学生(1989-1-1~1990-1-1)
select stu_num, name from students where birthday between '19890101' and '19900101';

select stu_num, name from students where birthday >= '1989-01-01' and birthday <= '1990-01-01';

#5. 查询学生姓名为“金蝶”的全部信息
select * from students where name = '金蝶';

#6. 查询subjectid为1002的科自考试未及格(60分)的学号和成绩
select stu_num, score from score where subject_id = 1002 and score < 60;

#7. 查询第3阶段课时为50天的课程全部信息
select * from subjects where period = '50天';

#8. 查询11111学生的考试信息
select * from score where stu_num = '11111';

#9. 查询所有第二阶段的女生信息
select * from students where sex= '女';

#10. “语文”需要多少课时
select period from subjects where subject_name = '语文';

#11. 查询“数学”和“英语”的课时(使用in)
select subject_name, period from subjects where subject_name in('数学','英语');

#12. 查询所有地址在山东的学生信息
select * from students where address = '山东';

#13. 查询所有姓凌的单名同学
select * from students where name like '凌%';

#14. 查询gradeid为11的学生信息，按出生日期升序排序
select * from students where grade_id = 11 order by birthday;

#15. 查询subjectid为1004的考试的成绩信息，用降序排序
select * from score where subject_id = 1004 order by score desc;

#16. 查询gradeid为12的课程中课时最多的课程信息
select * from subjects where grade_id = 12 and period = (SELECT max(period) from subjects where grade_id = 12);

#17. 查询木星的学生有多少个
select count(*) as 个数 from students where address = '木星';

#18. 查询有多少个科目学时小于50
select count(*) as 个数 from subjects where period < 50;

#19. 查询gradeid为12的阶段总课时是多少
select SUM(period) from subjects where grade_id = 12;

#20. 查询subjectid为1001的课程学生平均分
select AVG(score) from score where subject_id = 1001;

#21. 查询gradeid为12的课程中最多的学时和最少的学时
select MAX(period) from subjects where grade_id = 12;
select MIN(period) from subjects where grade_id = 12;

#22. 查询每个科目有多少人次考试
select subject_id, count(stu_num) from score group by subject_id;

#23. 每个阶段课程的平均课时
select grade_id, avg(period) from subjects group by grade_id;

#24. 查询每个阶段的男生和女生个数(group by 两列)
select grade_id, sex, count(stu_num) from students group by grade_id, sex;

运行sql文件导入

/*
Navicat MySQL Data Transfer

Source Server         : mysql
Source Server Version : 50730
Source Host           : localhost:3306
Source Database       : test

Target Server Type    : MYSQL
Target Server Version : 50730
File Encoding         : 65001

Date: 2020-11-10 20:30:47
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for grades
-- ----------------------------
DROP TABLE IF EXISTS `grades`;
CREATE TABLE `grades` (
  `grade_id` int(10) unsigned NOT NULL,
  `grade_name` varchar(8) NOT NULL,
  `employee_period` char(4) DEFAULT NULL,
  PRIMARY KEY (`grade_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of grades
-- ----------------------------
INSERT INTO `grades` VALUES ('11', '一年级一班', '否');
INSERT INTO `grades` VALUES ('12', '一年级二班', '否');
INSERT INTO `grades` VALUES ('13', '一年级三班', '否');
INSERT INTO `grades` VALUES ('23', '二年级三班', '否');
INSERT INTO `grades` VALUES ('31', '三年级一班', '是');
INSERT INTO `grades` VALUES ('42', '四年级二班', '是');

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `stu_num` varchar(8) NOT NULL,
  `subject_id` varchar(8) NOT NULL,
  `score` int(11) DEFAULT NULL,
  `exam_time` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '11111', '1001', '68', '2020-11-13');
INSERT INTO `score` VALUES ('2', '11112', '1002', '45', '2020-11-10');
INSERT INTO `score` VALUES ('3', '11113', '1003', '56', '2020-11-10');
INSERT INTO `score` VALUES ('4', '11114', '1004', '43', '2020-11-10');
INSERT INTO `score` VALUES ('5', '11111', '1002', '45', '2020-11-10');
INSERT INTO `score` VALUES ('6', '11112', '1001', '100', '2020-11-10');
INSERT INTO `score` VALUES ('7', '11113', '1004', '34', '2020-11-10');

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
  `stu_num` varchar(8) NOT NULL,
  `name` char(6) NOT NULL,
  `password` char(32) NOT NULL,
  `sex` enum('男','女') NOT NULL,
  `grade_id` int(10) unsigned NOT NULL,
  `phone` varchar(11) DEFAULT NULL,
  `address` varchar(30) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `email` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`stu_num`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('11111', '张三', '123456', '男', '11', '1234355', '木星', '1989-02-10', '214lsdfj@qq.com');
INSERT INTO `students` VALUES ('11112', '李四', '123123', '女', '12', '3245342', '山东', '1989-06-10', 'sdfelw@163.com');
INSERT INTO `students` VALUES ('11113', '凌云', '123qwe', '男', '11', '123123', '木星', '1989-08-12', 'sdfe@qq.com');
INSERT INTO `students` VALUES ('11114', '金蝶', '234wer', '女', '42', '123543', '山东', '2020-11-10', 'sdjfkzxv@qq.com');

-- ----------------------------
-- Table structure for subjects
-- ----------------------------
DROP TABLE IF EXISTS `subjects`;
CREATE TABLE `subjects` (
  `id` int(10) unsigned NOT NULL,
  `subject_name` varchar(20) NOT NULL,
  `period` int DEFAULT NULL,
  `grade_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------
-- Records of subjects
-- ----------------------------
INSERT INTO `subjects` VALUES ('1001', '语文', '50', '12');
INSERT INTO `subjects` VALUES ('1002', '数学', '34', '12');
INSERT INTO `subjects` VALUES ('1003', '英语', '50', '13');
INSERT INTO `subjects` VALUES ('1004', '历史', '28', '42');

原文链接：https://blog.csdn.net/qq_41912032/article/details/109606586