SQL数据库操作与练习-优快云博客

本文链接：https://blog.youkuaiyun.com/continueLR/article/details/121057542

1.建表

科目表

CREATE TABLE subject ( SUJECT_ID INT ( 2 ) NOT NULL PRIMARY KEY, subject_name VARCHAR ( 20 ), subject_time INT ( 2 ), grade_id VARCHAR ( 3 ) ) ENGINE = INNODB DEFAULT charset = utf8mb4;

年级表

CREATE TABLE grade ( grade_id VARCHAR ( 3 ) NOT NULL PRIMARY KEY, grade_name VARCHAR ( 4 ) ) ENGINE = INNODB DEFAULT charset = utf8mb4;

成绩表

CREATE TABLE score ( score_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, stu_num VARCHAR ( 20 ), OBJECT_ID INT ( 2 ), SCORE_NUM VARCHAR ( 3 ), TEST_TIME DATE ) ENGINE = INNODB DEFAULT charset = utf8mb4;

学生表

CREATE TABLE STUDENT (
   stu_num VARCHAR ( 10 ) NOT NULL PRIMARY KEY,
   STU_NAME VARCHAR ( 10 ),
   STU_PASSWORD VARCHAR ( 10 ),
   SEX enum ( '男', '女' ),
   grade_id VARCHAR ( 10 ),
   tel_num VARCHAR ( 20 ),
   stu_addr VARCHAR ( 20 ),
   stu_date DATE,
stu_email VARCHAR ( 20 )
) ENGINE = INNODB DEFAULT charset = utf8mb4;

2.插入数据

INSERT INTO grade VALUES (1, '初级');
INSERT INTO grade VALUES (2, '中级');
INSERT INTO grade VALUES (3, '高级');

INSERT INTO `score` VALUES (1, 'S1101001', 1, 90, '2021-01-01');
INSERT INTO `score` VALUES (2, 'S1101001', 2, 96, '2021-01-01');
INSERT INTO `score` VALUES (3, 'S1101001', 3, 99, '2021-01-01');
INSERT INTO `score` VALUES (4, 'S1101002', 1, 95, '2021-01-01');
INSERT INTO `score` VALUES (5, 'S1101002', 2, 100, '2021-01-01');
INSERT INTO `score` VALUES (6, 'S1101002', 3, 100, '2021-01-01');
INSERT INTO `score` VALUES (7, 'S1101003', 1, 80, '2021-01-01');
INSERT INTO `score` VALUES (8, 'S1101003', 2, 59, '2021-01-01');
INSERT INTO `score` VALUES (9, 'S1101003', 3, 60, '2021-01-01');
INSERT INTO `score` VALUES (10, 'S1101004', 1, 88, '2021-01-01');
INSERT INTO `score` VALUES (11, 'S1101004', 2, 90, '2021-01-01');
INSERT INTO `score` VALUES (12, 'S1101004', 3, 100, '2021-01-01');
INSERT INTO `score` VALUES (13, 'S1102001', 4, 89, '2021-01-02');
INSERT INTO `score` VALUES (14, 'S1102001', 5, 63, '2021-01-02');
INSERT INTO `score` VALUES (15, 'S1102001', 6, 99, '2021-01-02');
INSERT INTO `score` VALUES (16, 'S1102002', 4, 100, '2021-01-02');
INSERT INTO `score` VALUES (17, 'S1102002', 5, 99, '2021-01-02');
INSERT INTO `score` VALUES (18, 'S1102002', 6, 60, '2021-01-02');
INSERT INTO `score` VALUES (19, 'S1102003', 4, 78, '2021-01-02');
INSERT INTO `score` VALUES (20, 'S1102003', 5, 90, '2021-01-02');
INSERT INTO `score` VALUES (21, 'S1102003', 6, 56, '2021-01-02');
INSERT INTO `score` VALUES (22, 'S1102004', 4, 99, '2021-01-02');
INSERT INTO `score` VALUES (23, 'S1102004', 5, 99, '2021-01-02');
INSERT INTO `score` VALUES (24, 'S1102004', 6, 99, '2021-01-02');
INSERT INTO `score` VALUES (25, 'S1103001', 7, 56, '2021-01-03');
INSERT INTO `score` VALUES (26, 'S1103001', 8, 59, '2021-01-03');
INSERT INTO `score` VALUES (27, 'S1103001', 9, 100, '2021-01-03');
INSERT INTO `score` VALUES (28, 'S1103002', 7, 89, '2021-01-03');
INSERT INTO `score` VALUES (29, 'S1103002', 8, 100, '2021-01-03');
INSERT INTO `score` VALUES (30, 'S1103002', 9, 99, '2021-01-03');
INSERT INTO `score` VALUES (31, 'S1103003', 7, 66, '2021-01-03');
INSERT INTO `score` VALUES (32, 'S1103003', 8, 90, '2021-01-03');
INSERT INTO `score` VALUES (33, 'S1103003', 9, 98, '2021-01-03');
INSERT INTO `score` VALUES (34, 'S1103004', 7, 100, '2021-01-03');
INSERT INTO `score` VALUES (35, 'S1103004', 8, 100, '2021-01-03');
INSERT INTO `score` VALUES (36, 'S1103004', 9, 100, '2021-01-03');

INSERT INTO `student` VALUES ('S1101001', '金蝶', '123456', '女', 1, '18866660001', '北京', '1999-09-01', 'jindie@qq.com');
INSERT INTO `student` VALUES ('S1101002', '甄姬', '123456', '女', 1, '18866660002', '河南', '1997-08-03', 'zhenji@qq.com');
INSERT INTO `student` VALUES ('S1101003', '白起', '123456', '男', 1, '18866660003', '河北', '1990-07-09', 'baiqi@qq.com');
INSERT INTO `student` VALUES ('S1101004', '狄仁杰', '123456', '男', 1, '18866660004', '山东', '2000-06-06', 'direnjie@qq.com');
INSERT INTO `student` VALUES ('S1102001', '凌晨', '123456', '女', 2, '18866660005', '江苏', '2001-01-01', 'lingchen@qq.com');
INSERT INTO `student` VALUES ('S1102002', '小乔', '123456', '女', 2, '18866660006', '四川', '1998-06-06', 'xiaoqiao@qq.com');
INSERT INTO `student` VALUES ('S1102003', '廉颇', '666666', '男', 2, '18866660007', '陕西', '1998-09-09', 'lianpo@qq.com');
INSERT INTO `student` VALUES ('S1102004', '凌云天', '123321', '男', 2, '18866660008', '江苏', '1998-09-16', 'lingyuntian@qq.com');
INSERT INTO `student` VALUES ('S1103001', '钟馗', '888888', '男', 3, '18866660009', '北京', '1989-06-01', 'zhongkui@qq.com');
INSERT INTO `student` VALUES ('S1103002', '露娜', '123456', '女', 3, '18866660010', '海南', '1997-01-01', 'luna@qq.com');
INSERT INTO `student` VALUES ('S1103003', '后羿', '123455', '男', 3, '18866660011', '湖北', '1996-08-08', 'houyi@qq.com');
INSERT INTO `student` VALUES ('S1103004', '嫦娥', '123456', '女', 3, '18866660012', '月亮', '1999-09-01', 'change@qq.com');

INSERT INTO `subject` VALUES (1, '语文', 30, 1);
INSERT INTO `subject` VALUES (2, '数学', 40, 1);
INSERT INTO `subject` VALUES (3, '英语', 50, 1);
INSERT INTO `subject` VALUES (4, '政治', 30, 2);
INSERT INTO `subject` VALUES (5, '历史', 50, 2);
INSERT INTO `subject` VALUES (6, '地理', 70, 2);
INSERT INTO `subject` VALUES (7, '基于.NET 平台的软件系统分层开发', 60, 3);
INSERT INTO `subject` VALUES (8, '设计 MySchool 数据库', 55, 3);
INSERT INTO `subject` VALUES (9, '面向对象程序设计', 50, 3);

3.练习

#1. grade 表增加一个阶段，“就业期”
insert into grade values(4,'就业期');
#2.将第三阶段的学生的 gradeid 改为就业期的 id
update student a set a.grade_id = 4 where a.grade_id = 3;
#3.查询所有得了 100 分的学号
select distinct stu_num from score where score_num = 100
#4.查询所有 1989 年出生的学生（1989-1-1~1990-1-1）
select * from student where stu_date between '1989-01-01' and '1990-1-1';
-- 5.查询学生姓名为“金蝶” 的全部信息
select * from student s where s.stu_name = '金蝶';
-- 6.查询 subjectid 为 8 的科目考试未及格（60 分）的学号和成绩
select stu_num,score_num from score,subject where subject.object_id = 8 and score_num<60;
-- 7.查询第 3 阶段课时大于 50 的课程全部信息
select * from subject where object_time > 50;
-- 8..查询 S1101001 学生的考试信息
select * from score s where s.stu_num='S1101001';
-- 9.查询所有第二阶段的女生信息
select * from student s where s.SEX='女';
-- 10.“基于.NET 平台的软件系统分层开发”需要多少课时
select subject_time from subject where subject_name = '基于.NET 平台的软件系统分层开发';
-- 11.查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in)
select subject_time from subject where subject_name in('设计 MySchool 数据库','面向对象程序设计');
-- 12.查询所有地址在山东的学生信息
select * from student where stu_addr = '山东';
-- 13.查询所有姓凌的单名同学
select * from student where stu_name like '凌_';
-- 14.查询 gradeid 为 1 的学生信息，按出生日期升序排序
select * from student where grade_id = 1 order by stu_date asc;
-- 15.查询 subjectid 为 3 的考试的成绩信息，用降序排序
select * from score where subject_id = 3;
-- 16.查询 gradeid 为 2 的课程中课时最多的课程信息
select * from subject where grade_id= 2 order by subject_time desc limit 0,1;
-- 17.查询北京的学生有多少个
select count(*) from student where stu_addr = '北京';
-- 18.查询有多少个科目学时小于 50
select count(*) from subject where subject_time < 50;
-- 19.查询 gradeid 为 2 的阶段总课时是多少
select sum(subject_time) from subject where grade_id = 2;
-- 20.查询 subjectid 为 8 的课程学生平均分
select avg(score_num) from score where subject_id = 8;
-- 21.查询 gradeid 为 3 的课程中最多的学时和最少的学时
select max(subject_time),min(subject_time) from subject where grade_id =3;
-- 22.查询每个科目有多少人次考试
select subject_id,count(stu_num) from score where TEST_TIME is not null GROUP BY subject_id ;
-- 23.每个阶段课程的平均课时
select grade_id, avg(subject_time) from subject group by grade_id;
-- 24.查询每个阶段的男生和女生个数（group by）
SELECT grade_id,sex,count(stu_num) from student group BY grade_id,SEX;