MySQL上机实验

create database studentdb;
use studentdb;
create table student_info
(
学号 char(4) not null primary key,
姓名 char(8) not null,
性别 char(2) not null,
出生日期 date,
家族住址 varchar(50) not null
);
insert into student_info values
('0001','张青平','男','2000-10-01','衡阳市东风路77号'),
('0002','刘东阳','男','1998-12-09','东阳市八一北路33号'),
('0003','马晓夏','女','1995-05-12','长岭市五一路763号'),
('0004','钱忠理','男','1994-09-23','滨海市洞庭大道279号'),
('0005','孙海洋','男','1995-04-03','长岛市解放路27号'),
('0006','郭小斌','男','1997-11-10','南山市红旗路113号'),
('0007','肖月玲','女','1996-12-07','东方市南京路11号'),
('0008','张玲珑','女','1997-12-24','滨江市新建路97号');
create table curriculum
(
课程编号 char(4) not null primary key,
课程名称 varchar(50),
学分 int
);
insert into curriculum values
('0001','计算机应用基础',2),
('0002','C语言程序设计',2),
('0003','数据库原理及应用',2),
('0004','英语',4),
('0005','高等数学',4);
create table grade
(
学号 char(4) not null,
课程编号 char(4) not null,
分数 int,
primary key(学号,课程编号)
);
insert into grade values
('0001','0001',80),
('0001','0002',91),
('0001','0003',88),
('0001','0004',85),
('0001','0005',77),
('0002','0001',73),
('0002','0002',68),
('0002','0003',80),
('0002','0004',79),
('0002','0005',73),
('0003','0001',84),
('0003','0002',92),
('0003','0003',81),
('0003','0004',82),
('0003','0005',75);
-- （1）在student_info表中，查询每个学生的学号、姓名、出生日期信息
select 学号,姓名,出生日期 from student_info;
-- 查询student_info表学号为 0002的学生的姓名和家庭住址。
select 姓名,家族住址 from student_info
where 学号='0002';
-- 查询student_info表所有出生日期在95年以后的女同学的姓名和出生日期。
select 姓名,出生日期
from student_info
where 性别='女' and year(出生日期)>1995;
-- （1）在grade表中查询分数在70-80范围内的学生的学号、课程编号和成绩。
select 学号,课程编号,分数 as 成绩
from grade
where 分数 between 70 and 80;
-- 在grade表中查询课程编号为0002的学生的平均成绩。 
select 课程编号,avg(分数) as 平均成绩
from grade
where 课程编号='0002';
-- 在grade表中查询选修课程编号为0003的人数和该课程有成绩的人数。
select 课程编号,count(学号) as 人数
from grade
where 课程编号='0003' and 分数 is not null;
-- 查询student_info的姓名和出生日期，查询结果按出生日期从大到小排序。
select 姓名,出生日期
from student_info
order by 出生日期;
-- 查询所有姓名“张”的学生的学号和姓名。
select 学号,姓名
from student_info
where 姓名 like '张%';
-- 对student_info表，查询学生的学号、姓名、性别、出生日期及家庭住址，查询结果先按照性别的由小到大排序，性别相同的再按学号由大到小排序。
select * from student_info
order by 性别,学号 desc;
-- 使用GROUP BY子句查询grade表中各个学生的平均成绩。
select 学号,avg(分数) as 平均成绩
from grade
group by 学号;
-- 使用UNION运算符针student_info表中姓“刘”的学生的学号、姓名与姓“张”的学生的学号、姓名返回在一个表中。
select 学号,姓名 
from student_info
where 姓名 like '刘%'union
select 学号,姓名 
from student_info
where 姓名 like '张%';
-- 在student_info表中查找与“刘东阳”性别相同的所有学生的姓名、出生日期。
select 姓名,出生日期
from student_info
where 性别=(select 性别
from student_info
where 姓名='刘东阳');
-- 使用IN子查询查找所修课程编号为0002、0005的学生学号、姓名、性别。
select student_info.学号,姓名,性别 
from student_info,grade
where student_info.学号=grade.学号 and 课程编号 in ('0002','0005');
-- 使用ANY子查询查找学号为0001的学生的分数比0002号的学生的最低分数高的课程编号和分数。
select 课程编号,分数 
from grade
where 学号='0001' and 分数>any(
select 分数 from grade
where 学号='0002');
-- 使用ALL子查询查找学号为0001的学生的分数比学号为0002的学生的最高成绩还要高的课程编号和分数。
select 课程编号,分数 
from grade
where 学号='0001' and 分数>all(
select 分数 from grade
where 学号='0002');
-- （1）查询分数在80-90范围内的学生的学号、姓名、分数。
select student_info.学号,姓名,分数 
from student_info,grade
where student_info.学号=grade.学号 and (分数 between 80 and 90);
-- 使用INNER JOIN连接方式查询学习“数据库原理及应用”课程的学生学号、姓名、分数。
select student_info.学号,姓名,分数
from student_info inner join grade
on student_info.学号=grade.学号
where 课程编号=(
select 课程编号 from curriculum
where 课程名称='数据库原理及应用');
-- 查询每个学生所选课程的最高成绩，要求列出学号、姓名、最高成绩。
select student_info.学号,姓名,max(分数)
from student_info,grade
where student_info.学号=grade.学号
group by 学号;
-- 使用左外连接查询每个学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。
select student_info.学号,姓名,sum(分数)
from student_info left join grade
on student_info.学号=grade.学号
group by 学号;
-- （5）为grade表添加数据行：学号为0004、课程编号为0006、分数为76。
-- 使用右外连接查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。
insert into grade value
('0004','0006',76);
select curriculum.课程编号,课程名称,count(学号) as 选修人数
from grade right join curriculum
on grade.课程编号=curriculum.课程编号
group by 课程编号;