-- 新建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.课程号=课程号
)