保证技能不退步的最好方式就是勤加练习

SQL练习题

1、数据表以及表数据预置

// 院系表
create table if not exists c_depts(
id int(11) auto_increment primary key,
dept_name varchar(64) unique not null
)engine=InnoDB default charset=utf8;

// 学生表
create table if not exists c_students(
 sid int(11) primary key auto_increment not null,
 sname varchar(64) not null,
 gender int(4),
 deptId int(11) not null,
 constraint for_stu_dept foreign key(deptId) references c_depts(id)
)engine=InnoDB default charset=utf8;

// 课程表
create table if not exists c_courses(
	id int(11) primary key auto_increment,
	cname varchar(64) not null,
	teacherId int(11) not null,
	constraint for_course_dept foreign key(teacherId) references c_teachers(tid)
)engine=InnoDB default charset=utf8;

// 老师表
create table if not exists c_teachers(
 tid int(11) primary key auto_increment not null,
 tname varchar(64) not null,
 deptId int(11) not null,
 constraint for_tea_course foreign key(deptId) references c_depts(id)
)engine=InnoDB default charset=utf8;

// 选修课成绩表
create table if not exists c_scores(
 scid int(11) primary key auto_increment not null,
 score int(4) not null,
 courseId int(11) not null,
 stuId int(11) not null,
 constraint for_score_stu foreign key(stuId) references c_students(sid),
 constraint for_score_course foreign key(courseId) references c_courses(id)
)engine=InnoDB default charset=utf8;

2、插入数据

// 部门表插入数据
insert into c_depts values(1,'Education'),(2,'Computer Science'),(3,'Mathematics');

// 课程表插入数据
insert into c_courses values(1,'math',3),(2,'english',2),(3,'chinese',4),(4,'history',1),(5,'biology',5);

// 学生表数据插入数据
insert into c_students values(1,'Andrew',0,1),(2,'Andy',0,1),(3,'Bob',0,1),(4,'Ruth',1,2),(5,'Mike',0,2),(6,'John',0,3),(7,'Cindy',1,3),(8,'Susan',1,3), (9, 'Mike', 1, 2);

// 老师表数据插入数据
insert into c_teachers values(1,'Zhang san',1),(2,'Li si',1),(3,'Wang wu',2),(4,'Liu liu',3),(5,'Ding
qi',3);

// 成绩表插入数据
insert into c_scores(stuId, courseId, score) values(1,1,76),(1,2,90),(1,3,82),(1,5,56),(2,2,78),(2,4,92),(2,3,77),(2,5,65),(3,1,48),(3,2,95),(3,3,75),(3,4,89),(3,5,92),(4,3,78),(4,4,67),(5,1,75),(5,3,90),(5,4,82),
(6,2,58),(6,4,88),(7,1,55),(7,2,65),(7,3,63),(7,4,68),(7,5,70),(8,4,88),(8,5,100);

3、sql语句练习题及答案

• 1.查看所有英语成绩超过数学成绩的学生的学号和姓名
方式一：
create view en_scores as select stu.sid, stu.sname, cs.score from c_students as stu inner join c_scores as cs on cs.stuId=stu.sid inner join c_courses as cc on cc.id = cs.courseId and cc.cname='english'; 

create view math_scores as select stu.sid, stu.sname, cs.score from c_students as stu inner join c_scores as cs on cs.stuId=stu.sid inner join c_courses as cc on cc.id = cs.courseId and cc.cname='math';

select en_scores.sid, en_scores.sname from en_scores,math_scores where en_scores.sid = math_scores.sid and en_scores.score > math_scores.score;

方式二：
select aa.sid, aa.sname from ((select stu.sid, stu.sname, cs.score from c_students as stu inner join c_scores as cs on cs.stuId=stu.sid inner join c_courses as cc on cc.id = cs.courseId and cc.cname='english') as aa,(select stu.sid, stu.sname, cs.score from c_students as stu inner join c_scores as cs on cs.stuId=stu.sid inner join c_courses as cc on cc.id = cs.courseId and cc.cname='math') as bb) where aa.sid=bb.sid and aa.score > bb.score;

方式三：
select aa.sid, aa.sname from (select stu.sid, stu.sname, cs.score from c_students as stu inner join c_scores as cs on cs.stuId=stu.sid inner join c_courses as cc on cc.id = cs.courseId and cc.cname='english') as aa inner join (select stu.sid, stu.sname, cs.score from c_students as stu inner join c_scores as cs on cs.stuId=stu.sid inner join c_courses as cc on cc.id = cs.courseId and cc.cname='math') as bb on aa.sid=bb.sid and aa.score > bb.score;

• 2.查看平均成绩大于等于60的所有学生的姓名和平均成绩
select cs.stuId, stu.sname, avg(cs.score) as avg_score from c_scores as cs inner join c_students as stu on cs.stuId=stu.sid group by cs.stuId having avg_score >=60;

• 3.查询所有同学的学号，姓名，选课数和总成绩
select cs.stuId, stu.sname, count(cs.courseId), sum(cs.score) from c_scores as cs inner join c_students as stu
on stu.sid=cs.stuId group by cs.stuId;

• 4.查询姓zhang的老师的个数
select count(tid) from c_teachers where tname like 'zhang%';

• 5.查询没学过zhangsan老师课程的学生的学号和姓名
select stu.sid, stu.sname from c_students as stu where stu.sid not in (select cs.stuId from c_scores as cs inner join c_courses as cc on cc.id=cs.courseId inner join c_teachers as ct on ct.tid=cc.teacherId where ct.tname='zhang san');

• 6.查询既学过英语也学过语文的学生的学号和姓名
select stu.sid, stu.sname from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId and cs.courseId in (select cc.id from c_courses as cc where cc.cname in ('english', 'chinese')) group by cs.stuId having count(stu.sid)=2;

select stu.sid, stu.sname from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId and cc.cname in ('english', 'chinese') group by cs.stuId having count(stu.sid)=2;

• 7.查询有学生的单科成绩小于60的姓名和课程名称
select stu.sname, cc.cname from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId and cs.score<60;

• 8.按平均成绩从高到低显示所有学生的姓名和语文，数学，英语三科成绩
select stu.sname, sum(case when cc.cname='chinese' then cs.score else 0 end) as cn_score, sum(case when cc.cname='math' then cs.score else 0 end) as math_score, sum(case when cc.cname='english' then cs.score else 0 end) as en_score, avg(cs.score) as avg_score from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId group by cs.stuId order by avg_score desc;

• 9.查询各科成绩中的最高分和最低分
select cc.cname, max(cs.score) as hign_score, min(cs.score) as lower_score from c_scores as cs inner join c_courses as cc on cc.id=cs.courseId group by cs.courseId;

• 10.计算各科平均成绩和及格率百分比
select cc.cname, avg(cs.score) as avg_score, concat(sum(case when cs.score>=60 then 1 else 0 end)*100/count(*), '%') as passing_rate from c_scores as cs inner join c_courses as cc on cc.id=cs.courseId group by cs.courseId;

• 11.查询不同老师所教不同课程平均分从高到低
select ct.tname, cc.cname, avg(cs.score) as avg_score from c_scores as cs inner join c_courses as cc on cc.id=cs.courseId inner join c_teachers as ct on ct.tid=cc.teacherId group by cs.courseId order by avg_score desc;

• 12.查询英语和数学课程成绩排名第5到第10位的学生姓名和成绩[跳过前5条，总共显示6条]
select stu.sname, cs.score from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId where cc.cname='english' order by cs.score desc limit 4, 6;

select stu.sname, cs.score from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId where cc.cname='math' order by cs.score desc limit 4, 6;


• 13.统计按照各科成绩，分段统计每个课程在90分以上、80-90的、60~80、低于60分的人数

select cc.cname, sum(case when cs.score>=90 then 1 else 0 end) as excellent, sum(case when cs.score>=80 and cs.score<90 then 1 else 0 end) as good, sum(case when cs.score>=60 and cs.score<80 then 1 else 0 end) as medium, sum(case when cs.score<60 then 1 else 0 end) as failed from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId group by cs.courseId;

• 14.查看每门课程被选修的学生数

select cc.cname, count(cs.stuId) as stu_counts from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId group by cs.courseId;

• 15.查看只学习了两门课程的学生的姓名和学号
select stu.sid, stu.sname,count(cs.courseId) as course_counts from c_scores as cs inner join c_students as stu on stu.sid=cs.stuId inner join c_courses as cc on cc.id=cs.courseId group by cs.stuId having course_counts=2;

• 16.查询名字相同的学生名单和个数
select stu.sname, count(stu.sid) as counts from c_students as stu group by stu.sname having counts>1;

• 17.查询85年之后出生的学生人数
select count(stu.sid) as '85后人数' from c_students as stu where year(birthday)>1985;

• 18.查询每门课程的平均成绩，按升序排序，如果平均成绩相同按课程ID降序排序

select cc.cname, cs.courseId, avg(cs.score) as avg_score from c_scores as cs inner join c_courses as cc on cc.id=cs.courseId group by cs.courseId order by avg_score asc, cs.courseId desc;

• 19.查询有不及格学生的课程和不及格学生个数

select cc.cname, count(cs.stuId) as failed_counts from c_scores as cs inner join c_courses as cc on cc.id=cs.courseId and cs.score<60 group by cs.courseId;

• 20.将所有学生姓名中前后的空格去掉
update c_students set sname=trim(sname);

• 21.将所有学生的考试成绩展示为课程名：成绩样式
select stu.sname, concat(cc.cname, ':', cs.score) as '考试成绩' from c_scores as cs inner join c_courses as cc on cc.id=cs.courseId inner join c_students as stu on stu.sid=cs.stuId;

• 22.将所有老师的名字拆分成姓和名两个字段显示
select substring(tname, 1, locate(' ', tname,)) as Xing, substring(tname, locate(' ', tname,)+1) as Ming from c_teachers;

• 23.把所有学生的生日格式转换成年月日的格式，并计算每个学生年龄
select date_format(birthday, '%Y/%m/%d') as birth, year(now())-year(birthday) as age from c_students;

注：不要看答案，都尝试自己去把它做出来！如果每道题都能很轻松的写出来，可以说SQL语言的基础就很扎实了！