原始数据
# 创建习题数据库
create database school;
# 使用习题数据库
use school;
# 创建学生信息表
create table student
(sno char(7) comment '学生编号',
sname varchar(4) comment '姓名',
ssex char(1) comment '性别',
sage int(3) comment '年龄',
sdept char(4) comment '院系'
)
char set utf8;
# 创建课程信息表
create table course
(
cno char(3) comment '课程编号',
cname varchar(6) comment '名字',
hours int(3) comment '课时'
)
char set utf8;
# 创建学生成绩表
create table sc
(
sno char(7), comment '学生编号'
cno char(3) comment '课程编号',
grade int(3) comment '分数'
)
char set utf8;
# 学生信息表插值
insert into student values
('9512101', '李勇', '男', 19, '计算机系'),
('9512102', '刘晨', '男', 20, '计算机系'),
('9512103', '王敏', '女', 20, '计算机系'),
('9512103', '王敏', '女', 20, '计算机系'),
('9521101', '张立', '男', 22, '信息系'),
('9521102', '吴宾', '女', 21, '信息系'),
('9521103', '张海', '男', 20, '信息系'),
('9531101', '钱小力', '女', 18, '数学系'),
('9531102', '王大力', '男', 19, '数学系');
# 课程信息表插值
insert into course values
('c01', '计算机文化学', 70),
('c02', 'VB', 90),
('c03', '计算机网络', 80),
('c04', '数据库基础', 108),
('c05', '高等数学', 180),
('c06', '数据结构', 72);
# 学生成绩表插值
insert into sc values
('9512101', 'c01', 90),
('9512101', 'c02', 86),
('9512101', 'c06', null),
('9512102', 'c02', 78),
('9512102', 'c04', 66),
('9521102', 'c01', 82),
('9521102', 'c02', 75),
('9521102', 'c04', 92),
('9521102', 'c05', 50),
('9521103', 'c02', 68),
('9521103', 'c06', null),
('9531101', 'c01', 80),
('9531101', 'c05', 95),
('9531102', 'c05', 85);
查询练习
- 查询哪些学生的年龄相同, 要求列出年龄相同的学生的姓名和年龄
select a.sname, a.sage from student as a
inner join student as b on a.sage in
(
select sage from student
where a.sage = b.sage and a.sname != b.sname
)
group by a.sname, a.sage
order by a.sage;
- 分别查询信息系和计算机系的学生的姓名, 性别, 修课名称, 修课成绩, 并将两次查询的结果合并
select sname, ssex , course.cname, sc.grade from student
inner join sc on sc.sno = student.sno
inner join course on course.cno = sc.cno
where sdept = '信息系'
union
select sname, ssex , course.cname, sc.grade from student
inner join sc on sc.sno = student.sno
inner join course on course.cno = sc.cno
where sdept = '计算机系';
- 将计算机系成绩高于80分的学生的修课情况插入到另一张表中, 分两种情况实现
create table computer_high_grade
(
sname varchar(4),
cname varchar(6),
grade int(3)
)
char set utf8;
# 方法一: 内连接
insert computer_high_grade
select sname, course.cname, sc.grade from student
inner join sc on sc.sno = student.sno
inner join course on course.cno = sc.cno
where student.sdept = '计算机系' and sc.grade > 80;
# 方法二: 直接查询
insert computer_high_grade
select student.sname, course.cname, sc.grade
from student, course, sc
where student.sdept = '计算机系'
and sc.grade > 80
and student.sno = sc.sno
and course.cno = sc.cno;
