7-MySQL基础综合练习

-- 新建stu_data数据库
create database stu_data default character set utf8;
/*
1、学生表：
学号（int， 主键）
姓名（char（20），not null）
性别（char（4），not null，只允许‘男’或‘女’）
年龄（smallint ，not null，必须大于0）
爱好（varchar（30），not null）
专业（varchar（30），not null）
2、成绩表：
学号（int ，外键关联“学生表”的“学号”）
课程号（int，外键关联“课程表”的“课程号”）
成绩（float，not null， default 0）
3、课程表：
课程号（int，主键）
课程名（char（20），not null）

*/
-- 创建学生表
create table 学生表
(
    学号 int auto_increment primary key,
    姓名 varchar(20) not null,
    性别 enum('男','女') not null ,
    年龄 smallint check (年龄>0),
    爱好 varchar(30) not null,
    专业 varchar(30) not null
);
create table 课程表
(
    课程号 int auto_increment primary key,
    课程名 varchar(20) not null
);
create table 成绩表
(
    学号 int not null REFERENCES 学生表(学号),
    课程号 int not null REFERENCES 课程表(课程号),
    成绩 float default 0,
    primary key(学号,课程号)
);

select * from 学生表;
select * from 课程表;
select * from 成绩表;

insert into 学生表(姓名,性别,年龄,爱好,专业) values('张三','男',20,'唱歌','计算机'),
('李四','男',22,'跳舞','国际贸易'),
('丽丽','女',18,'看书','计算机'),
('小丽','女',19,'唱歌','电子商务'),
('晓晓','女',21,'爬山','电子自动化'),
('张三丰','男',23,'上网','计算机'),
('王老五','男',33,'武术','软件工程'),
('李小龙','男',24,'武术','国际贸易');
insert into 学生表(姓名,性别,年龄,爱好,专业) values('黄琛','男',20,'跳舞','国际贸易');

insert into 课程表(课程名) values('HTML'),('C语言'),('SQL'),('JAVA');
insert into 成绩表 values(1,1,88),(1,2,48),(1,3,58),
(2,1,77),(2,2,56),(2,3,46),
(3,1,99),(3,2,63),(3,4,73),
(4,1,44),(4,2,74),(4,3,82),
(5,1,33),(5,2,85),(5,4,91),
(6,1,22),(6,4,96),(6,3,65),
(7,1,87),(7,4,51),(7,3,10);

-- 1.    把国际贸易专业黄琛的爱好改为唱歌
    update 学生表 set 爱好='唱歌' where 姓名='黄琛' and 专业='国际贸易';
    
-- 2.    把计算机专业年龄小于16的学生改为花季的年龄（十八岁）
select * from 学生表 where 专业='计算机';
update 学生表 set 年龄=18 where 专业='计算机' and 年龄<16;

-- 3.    统计计算机专业男生的平均年龄
select avg(年龄) 平均年龄 from 学生表 where 专业='计算机' and 性别='男';

-- 4.    查找课程名为SQL的成绩最高的学生的学号
select * from 成绩表 where 课程号=3 order by 成绩 desc ;
select 学号 from 成绩表 where 课程号 = (select 课程号 from 课程表 where 课程名='SQL') order by 成绩 desc limit 0,1;

-- 5.    查询所有考试得满分的学生的专业及姓名。
select 姓名,专业 from 学生表 where 学号 in(
    select 学号 from 成绩表 group by 学号 having min(成绩)=100
);
-- 6.    查询小胡的SQL成绩。
select * from 成绩表 where 学号=(select 学号 from 学生表 where 姓名='小胡') and 课程号=(select 课程号 from 课程表 where 课程名='SQL');

-- 7.    查询年龄在15-20之间的平均成绩
select avg(成绩) 平均成绩 from 成绩表 where 学号 in (
    select 学号 from 学生表 where 年龄 between 15 and 20
);

-- 8.    查询选修了html课程的学生最高成绩和他的姓名

select 姓名,成绩 from 学生表 s inner join 成绩表 c on s.学号=c.学号 
where 课程号=(select 课程号 from 课程表 where 课程名='HTML') and s.学号 in(
    select 学号 from 成绩表 where 课程号=(select 课程号 from 课程表 where 课程名='HTML') and 成绩=(
        select 成绩 from 成绩表 where 课程号=(select 课程号 from 课程表 where 课程名='HTML') order by 成绩 desc limit 0,1
    )
)

-- 9.    查询计算机专业的所有女生的C语言成绩
select 成绩 from 成绩表 where 课程号=(select 课程号 from 课程表 where 课程名='C语言')  and  学号 in (
    select 学号 from 学生表 where 专业='计算机' and 性别='女'
);

-- 10.    把电子商务专业的没有及格的学生提升为及格
update 成绩表 set 成绩=60 where 成绩<60 and 学号 in(
    select 学号 from 学生表 where 专业='电子商务'
);
-- 11.    查询计算机专业所有学生的姓名和sql成绩
select 姓名,
(select 成绩 from 成绩表 where s.学号 = 学号 and 课程号=(select 课程号 from 课程表 where 课程名='SQL')) 数据库
from 学生表 s where 专业='计算机'

-- 12.    查询选修了HTML课程的学生的人数
select count(*) from 成绩表 where 课程号 = (select 课程号 from 课程表 where 课程名='HTML');

-- 13.    把选修了C语言的所有学生信息放在一个新表new_c中。
insert into new_c select * from 学生表 where 学号 in (select 学号 from 成绩表 where 课程号=(select 课程号 from 课程表 where 课程名='C语言'));
select * from new_c;

-- 14.    查询计算机专业所有姓“李”的学生信息。
select * from 学生表 where 专业='计算机' and 姓名 like '李%';

-- 15.    删除所有C语言考试不及格的学生信息
delete from 学生表 where 学号 in (
    select 学号 from 成绩表 where 课程号=(select 课程号 from 课程表 where 课程名='C语言') and 成绩<60
);

-- 16.    把所有低于SQL平均分的成绩信息放在另一个表中
select * from 成绩表 where 成绩< any(
select avg(成绩) from 成绩表 where 课程号=(select 课程号 from 课程表 where 课程名='C语言'))

-- 17.    查询非国际贸易专业姓王的姓名是三个字的学生
select * from 学生表 where 专业 <>'国际贸易' and 姓名 like '王__';
-- 18.    查询学生表中第5至10条记录。
select * from 学生表 limit 5,5;
-- 19. 查找所有缺考的学生姓名及课程名

select * from 学生表 s cross join 课程表 k where not exists (

    select * from 成绩表 where s.学号=学号 and k.课程号=课程号
) 
-- 所有参加了考试的学生成绩
select * from 学生表 s cross join 课程表 k where exists (

    select * from 成绩表 where s.学号=学号 and k.课程号=课程号
) 

本人从事软件项目开发20多年，2005年开始从事Java工程师系列课程的教学工作，录制50多门精品视频课程，包含java基础，jspweb开发，SSH,SSM，SpringBoot，SpringCloud,人工智能，在线支付等众多商业项目，每门课程都包含有项目实战，上课PPT，及完整的源代码下载，有兴趣的朋友可以看看我的在线课堂

讲师课堂链接：https://edu.youkuaiyun.com/lecturer/893