创建表
create table student(
s_id varchar(20),
s_name varchar(20) not null default'',
s_birth varchar(20) not null default'',
s_sex varchar(10) not null default'',
primary key(s_id)
);
-- 课程表
create table cource(
c_id varchar(20)，
c_name varchar(20) not null default'',
t_id varchar(20) not null,
primary key(c_id)
);
-- 教师表
create table teacher(
t_id varchar(20),
t_name varchar(20) not null default'',
primary key(t_id)
);
-- 成绩表
create table score(
s_id varchar(20),
c_id varchar(20),
s_score int(3),
primary key(s_id,c_id)
);
插入数据
-- 插入学生表测试数据
insert into student values('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-05-20' , '男'),
('04' , '李云' , '1990-08-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-03-01' , '女'),
('07' , '郑竹' , '1989-07-01' , '女'),
('08' , '王菊' , '1990-01-20' , '女');
-- 课程表测试数据
insert into course values('01' , '语文' , '02'),('02' , '数学' , '01'),('03' , '英语' , '03');
-- 教师表测试数据
insert into teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');
-- 成绩表测试数据 insert into score values('01' , '01' , 80),('01' , '02' , 90),('01' , '03' , 99),
('02' , '01' , 70),('02' , '02' , 60),('02' , '03' , 80),
('03' , '01' , 80),('03' , '02' , 80),('03' , '03' , 80),
('04' , '01' , 50),('04' , '02' , 30),('04' , '03' , 20),
('05' , '01' , 76),('05' , '02' , 87),
('06' , '01' , 31),('06' , '03' , 34),
('07' , '02' , 89),('07' , '03' , 98);
练习
- 查询姓“张”的老师的个数：考察模糊查询
SELECT count(t_name)
from teacher
where t_name like '张%'
2.查询名字含“风”的学生
SELECT *
from student
where s_name like '%风%'
3.1981年出生的学生名单：考察日期类型
SELECT *
from student
where year(s_birth) = 1990;
或者
where s_birth like '1990%';
4.查询课程编号为“02”的总成绩:考察聚合函数（求和sum）
SELECT sum(s_score)
from score
where s_id='02';
sql04.png
5.查询选了课程的学生人数
SELECT count(distinct s_id) as '学生人数'
from score ；
sql5.png
6.查询各科成绩最高和最低的分： 以如下的形式显示：课程，最高分，最低分:考察分组（group），聚合函数（最大值max，最小值min）
select c_id as 课程,
max(s_score) as 最高分,
min(s_score) as 最低分
from Score
group by c_id;
sql6.png
7.查询每门课程被选修的学生数:分组（group），聚合函数（计数count）
SELECT c_id as 课程,
count(c_id) as 人数
from score
group by c_id ;
sql7.png
8.查询男生、女生人数
select s_sex ,count(s_sex)
from student
group by s_sex;
sql.png
9.查询平均成绩大于60分的学生的学号和平均成绩：考察分组（group）,having （对分组结果按条件过滤）
select s_id ,avg(s_score)
from score
group by s_id
having avg(s_score)>60;
sql9.png
10.查询至少选修两门课程的学生学号
select s_id ,count(s_score)
from score
group by s_id
having count(s_score)>=2;
sql10.png
11.查询两门以上不及格课程的同学的学号及其平均成绩
select s_id ,avg(s_score)
from score
where s_score<60
group by s_id
having count(s_id)>1;
sql11.png
12.查询同名同性学生名单并统计同名人数
select s_name,count(*)
from Student
group by s_name
having count(*)>1;
sql12.png
13.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select c_id as '课程',avg(s_score) as '平均分'
from score
group by c_id
order by avg(s_score),c_id desc;
sql13.png
14.查询不及格的课程并按课程号从大到小排列
select c_id,s_score
from Score
where s_score<60
order by c_id;
sql14.png
15.检索课程编号为“03”且分数小于60的学生学号，结果按按分数降序排列
select c_id,s_score
from Score
where s_score<60
having c_id='03'
order by s_score desc
sql15.png
16.统计每门课程的学生选修人数(超过5人的课程才统计)。 要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
select c_id,count(s_id)
from score
group by c_id
having count(s_id)>5
order by count(s_id) desc,c_id;
sql16.png
17.查询所有课程成绩小于60分的学生的学号、姓名
select s_id,s_name
from student
where s_id in (
select s_id
from score
where s_score<60);
sql17.png
18.查询没有学全所有课的学生的学号、姓名
select s_id,s_name
from student
where s_id in (
select s_id
from score
group by s_id
having count(c_id)<(
select count(c_id)
from cource
)
);
sql18.png
19.查询出只选修了两门课程的全部学生的学号和姓名
select s_id,s_name
from student
where s_id in (
select s_id
from score
group by s_id
having count(c_id)=2
);
sql19.png
查询课程编号为03且课程成绩在80分以上的学生的学号和姓名
from student
where s_id in (
select s_id
from score
where c_id='03' and s_score>80
);
select a.s_id ,s_name
from student as a join score as b on a.s_id=b.s_id
where c_id='03'
and s_score>80;
sql20.png
网友评论