原始数据

# 创建习题数据库
create database school;
# 使用习题数据库
use school;
# 创建学生信息表
create table student
(
id int(3) not null unique auto_increment comment '学号',
name varchar(4) not null comment '姓名',
sex char(1) comment '性别',
birth year comment '出生年份',
department varchar(4) not null comment '院系',
address varchar(6) comment '家庭住址',
primary key(id)
)
char set utf8;
# 创建学生成绩表
create table score
(
id int(2) not null unique auto_increment comment '编号',
stu_id int(3) not null comment '学号',
c_name varchar(3) comment '课程名',
grade int(3) comment '分数',
primary key(id)
)
char set utf8;
# 学生信息表插值
insert into student values
(901,'张老大','男','1985','计算机系','北京市海淀区'),
(902,'张老二','男','1986','中文系','北京市昌平区'),
(903,'张三','女','1990','中文系','湖南省永州市'),
(904,'李四','男','1990','英语系','辽宁省阜新市'),
(905,'王五','女','1991','英语系','福建省厦门市'),
(906,'王六','男','1988','计算机系','湖南省衡阳市');
# 学生成绩表插值
insert into score values
(null,901,'计算机',98),
(null,901,'英语',80),
(null,902,'计算机',65),
(null,902,'中文',88),
(null,903,'中文',95),
(null,904,'计算机',70),
(null,904,'英语',92),
(null,905,'英语',94),
(null,906,'计算机',90),
(null,906,'英语',85);

查询练习

1. 查询student表的第2条到4条记录

select * from student limit 1,3;

2. 查询计算机系和英语系的学生的信息

select * from student
where department = '计算机系' or department = '英语系';

3. 从student表中查询每个院系年龄18~30岁的学生信息

select * from student
where year(curdate())-birth between 18 and 30
order by id;

4. 查询计算机成绩低于95的学生信息

# 方法一: 左连接
select * from student
left join score on student.id = score.stu_id
where score.grade < 95 and score.c_name = '计算机'
group by student.id
order by student.id;
# 方法二: 子查询
select * from student
where id in
(select stu_id from score
where c_name = '计算机' and grade < 95);

5. 查询同时参加计算机和英语考试的学生的信息

select * from student
where id in
(select stu_id from score
where c_name in ('计算机','英语')
group by stu_id having count(stu_id) = 2);