数据库SQL练习题

矫情吗；* 2023-01-15 03:57 157阅读 0赞

group by 只出一行记录 concat having where

**本练习题测试在mysql5.7**

## 问题 ##

已知有如下4张表：

学生表：student(学号,学生姓名,出生年月,性别)

成绩表：score(学号,课程号,成绩)

课程表：course(课程号,课程名称,教师号)

教师表：teacher(教师号,教师姓名)

1、自行完成建表并作数据插入（数据可参考我后面的代码）

2、查询练习

查询名字 中有 ‘小‘ 字的

查询名字中有 ‘小‘ 字的人的个数

查询学号为“20200301”的总成绩

查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分

查询每门课程被选修的学生数

查询男生、女生人数

查询平均成绩大于60分学生的学号和平均成绩

查询至少选修两门课程的学生学号

查询不及格的课程并按课程号从大到小排列

查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

查询两门以上不及格课程的同学的学号及其平均成绩

**复杂查询**

查询所有课程成绩小于60分学生的学号、姓名

查询没有学全所有课的学生的学号、姓名

查询出只选修了两门课程的全部学生的学号和姓名

1998年出生的学生名单

查询各科成绩前两名的记录

4.多表查询

查询所有学生的学号、姓名、选课数、总成绩

查询学生的选课情况：学号，姓名，课程号，课程名称

使用分段\[100-85\],\[85-70\],\[70-60\],\[<60\]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

### 代码 ###

准备：

建表

create database school character set utf8;
    use school;
    CREATE TABLE student(
       stu_id int(12) primary key,
       stu_name varchar(10) ,
       stu_birth date,
       stu_gender varchar(1) 
    ) ENGINE= InnoDB DEFAULT CHARSET=UTF8;
    
    CREATE TABLE teacher(
       teacher_id int(12) primary key,
       teacher_name varchar(10) 
    )ENGINE= InnoDB DEFAULT CHARSET=UTF8; 
    CREATE TABLE course(
       course_id int(8) primary key,
       course_name varchar(20),
       teacher_id int(12),
       FOREIGN KEY(teacher_id) REFERENCES teacher(teacher_id)
    )ENGINE= InnoDB DEFAULT CHARSET=UTF8;
    CREATE TABLE score(
       stu_id int(12),
       course_id int(8),
       grade double,
       primary key(stu_id,course_id),
       CONSTRAINT fk_score_stu_sid FOREIGN KEY(stu_id) REFERENCES student(stu_id),
       CONSTRAINT fk_score_course_cid FOREIGN KEY(course_id) REFERENCES course(course_id)
    )ENGINE= InnoDB DEFAULT CHARSET=UTF8;
    
    以下是一些方法
    primary key(stu_id,course_id)// 联合主键
    ALTER TABLE score  ADD CONSTRAINT  primary key(stu_id,course_id);
    select * from information_schema.key_column_usage where TABLE_NAME='course';
    alter table course drop foreign key course_ibfk_1;
    alter table course add CONSTRAINT fk_crouse_teacher_tid FOREIGN KEY(teacher_id) 
    REFERENCES teacher(teacher_id);
    ALTER TABLE STUDENT MODIFY stu_name varchar(10) NOT null;
    ALTER TABLE teacher MODIFY teacher_name varchar(10) NOT null;

数据插入

学生表
    insert into student values('20200301' , '小猴子' , '1999-04-01' , '男');
    insert into student values('20200302' , '小李子' , '1998-12-21' , '男');
    insert into student values('20200303' , '貂蝉' , '1991-12-21' , '女');
    insert into student values('20200201' , '诸葛亮' , '1997-05-20' , '男');
    insert into student values('20200202' , '甄姬' , '1997-05-20' , '女');
    insert into student values('20200304' , '吕布' , '2000-05-20' , '男');
    insert into student values('20200306' , '鲁班小师' , '1999-04-01' , '男');
    教师表里添加数据
    insert into teacher values('10001' , '孟扎扎');
    insert into teacher values('10002' , '马腾腾');
    insert into teacher values('10003' , '刘晓晓');
    insert into teacher values('10004' , '铁蛋蛋');
    insert into teacher values('10005' , '华鸡鸡');
    课程表
    insert into course values('20210001' , '语文' , '10002');
    insert into course values('20210002' , '数学' , '10001');
    insert into course values('20210003' , '英语' , '10003');
    insert into course values('20210004' , '数据库' , '10004');
    insert into course values('20210005' , '语言学' , '10005');
    成绩表
    insert into score values('20200301' , '20210001' , 80);
    insert into score values('20200301' , '20210002' , 90);
    insert into score values('20200301' , '20210003' , 99);
    insert into score values('20200302' , '20210001' , 59);
    insert into score values('20200302' , '20210002' , 80);
    insert into score values('20200302' , '20210003' , 68);
    insert into score values('20200303' , '20210001' , 90);
    insert into score values('20200303' , '20210004' , 70);
    insert into score values('20200201' , '20210002' , 99);
    insert into score values('20200201' , '20210001' , 50);
    insert into score values('20200201' , '20210004' , 80);
    insert into score values('20200202' , '20210002' , 70);
    insert into score values('20200304' , '20210001' , 76);
    insert into score values('20200304' , '20210003' , 80);
    insert into score values('20200301' , '20210005' , 100);
    insert into score values('20200304' , '20210005' , 50);
    insert into score values('20200306' , '20210001' , 50);
    insert into score values('20200306' , '20210002' , 40);
    insert into score values('20200306' , '20210003' , 42);

查询名字 中有 ‘小‘ 字的

select \* from student where stu\_name like ‘%小%’;

查询名字中有 ‘小‘ 字的人的个数

select count(\*) from student where stu\_name like ‘%小%’;

查询学号为“20200301”的总成绩

select sum(grade) from score where stu\_id = ‘20200301’ ;

查询各科成绩最高和最低的分， 以如下的形式显示：课程号，最高分，最低分

select course\_id , max(grade) , min(grade) from score group by course\_id;

查询每门课程被选修的学生数

select course\_id,count(\*) from score group by course\_id;

查询男生、女生人数

select stu\_gender,count(\*) from student group by stu\_gender;

查询平均成绩大于60分学生的学号和平均成绩

select stu\_id,avg(grade) from score group by stu\_id having avg(grade)>60;

查询至少选修两门课程的学生学号

select stu\_id from score group by stu\_id having count(\*)>=2 ;

查询不及格的课程并按课程号从大到小排列

select course\_id from score where grade<60 order by course\_id desc;

查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select course\_id ,avg(grade) as avgscore from score group by course\_id order by avgscore asc,course\_id desc;

查询两门以上不及格课程的同学的学号及其平均成绩

select stu\_id,avg(grade) from score where grade<60 group by stu\_id having count(\*)>=2 ;

复杂查询

查询所有课程成绩小于60分学生的学号、姓名

select stu\_id,stu\_name from student where stu\_id in(select stu\_id from score where grade<60);

查询没有学全所有课的学生的学号、姓名

select stu_id,stu_name from student where stu_id in(select stu_id from score group by stu_id having count(*) < (select count(*) from course)) ;

查询出只选修了两门课程的全部学生的学号和姓名

select stu\_id,stu\_name from student where stu\_id in(select stu\_id from score group by stu\_id having count(\*)=2);

1998年出生的学生名单  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDgwNjA3_size_16_color_FFFFFF_t_70]  
select stu\_id,stu\_name from student where year(stu\_birth)=1998;

查询各科成绩前两名的记录

(select \* from score where course\_id= ‘20210001’ order by grade desc limit 2)  
union all  
(select \* from score where course\_id= ‘20210002’ order by grade desc limit 2)  
union all  
(select \* from score where course\_id = ‘20210003’ order by grade desc limit 2);

4.多表查询

查询所有学生的学号、姓名、选课数、总成绩

select st.stu\_id,st.stu\_name,count(sc.course\_id),sum(sc.grade) from student st,score sc where st.stu\_id = sc.stu\_id group by sc.stu\_id;

select a.学号,a.姓名,count(b.课程号) ,sum(b.成绩)  
from student st join score as b  
on a.学号 = b.学号  
group by a.学号;

查询学生的选课情况：学号，姓名，课程号，课程名称

select st.stu\_id,st.stu\_name,co.course\_id,co.course\_name from student st,course co,score sc where st.stu\_id = sc.stu\_id and sc.course\_id = co.course\_id ;

使用分段\[100-85\],\[85-70\],\[70-60\],\[<60\]来统计各科成绩，分别统计：各分数段人数，课程号和课程名称

-- 考察case表达式
    select sc.course_id,co.course_name,
    sum(case when grade between 85 and 100 
    	 then 1 else 0 end) as '[100-85]',
    sum(case when grade >=70 and grade<85 
    	 then 1 else 0 end) as '[85-70]',
    sum(case when grade>=60 and grade<70  
    	 then 1 else 0 end) as '[70-60]',
    sum(case when grade<60 then 1 else 0 end) as '[<60]'
    from score sc, course co 
    where sc.course_id=co.course_id
    group by sc.course_id,co.course_name;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDgwNjA3_size_16_color_FFFFFF_t_70 1]

create table scoreo(
       sid int(10),
       cid int(10),
       grade double
    )
    insert into scoreo values('10001','20210001',80);
    insert into scoreo values('10001','20210002',90);
    insert into scoreo values('10001','20210003',99);
    insert into scoreo values('10002','20210002',60);
    insert into scoreo values('10002','20210003',80);
    insert into scoreo values('10003','20210001',80);
    insert into scoreo values('10003','20210002',80);
    insert into scoreo values('10003','20210003',80);
    
    
    select sid,
    (case cid when '20210001' then grade else 0 end) as '课程号0001',
    (case cid when '20210002' then grade else 0 end) as  '课程号0002',
    (case cid when '20210003' then grade else 0 end) as '课程号0003'
    from scoreo;
    
    select sid,
    sum(case cid when '20210001' then grade else 0 end) as '课程号0001',
    sum(case cid when '20210002' then grade else 0 end) as  '课程号0002',
    sum(case cid when '20210003' then grade else 0 end) as '课程号0003'
    from scoreo
    group by sid;
    
    select sid,
    max(case cid when '20210001' then grade else 0 end) as '课程号0001',
    max(case cid when '20210002' then grade else 0 end) as  '课程号0002',
    max(case cid when '20210003' then grade else 0 end) as '课程号0003'
    from scoreo
    group by sid;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDgwNjA3_size_16_color_FFFFFF_t_70 2]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDgwNjA3_size_16_color_FFFFFF_t_70]: /images/20221022/fa045c8352c44ef48596f92725ac28b0.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDgwNjA3_size_16_color_FFFFFF_t_70 1]: /images/20221022/93bd51481f4a40ad963ab8613c5d5bbe.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIxNDgwNjA3_size_16_color_FFFFFF_t_70 2]: /images/20221022/8d5b61ced1094473abb21f2f0f80866d.png