导读：思百遍，练千遍，循序渐进，终有所成。加油-陌生人
一、创建数据库
CREATE DATABASE IF NOT EXISTS XSCJ DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;
二、创建各个表格及数据
1、xs表
create table XS(学号 int(6) not null primary key ,
姓名 char(8) not null,
专业名 char(8) not null,
性别 char(2) not null,
出生时间 date not null,
总学分 int(4) not null,
照片 blob,
备注 char(30));
数据
insert into xs values(081101,'王林','计算机','男','1990-02-10',50,null,null),
(081102,'程明','计算机','男','1991-02-1',50,null,null),
(081103,'王燕','计算机','女','1989-10-06',50,null,null),
(081104,'韦艳萍','计算机','男','1990-08-26',50,null,null),
(081106,'李方方','计算机','男','1990-11-20',50,null,null),
(081107,'李明','计算机','男','1990-05-1',54,null,'提前修完《数据结构》'),
(081108,'林一凡','计算机','男','1989-08-5',52,null,'已提前修完一门课'),
(081109,'张强民','计算机','男','1989-08-11',50,null,null),
(081110,'张威','计算机','女','1991-07-22',50,null,'三好生'),
(081111,'赵琳','计算机','女','1990-03-18',50,null,null),
(081113,'嫣红','计算机','女','1989-08-11',48,null,'有一门功课不及格待考'),
(081201,'王敏','通信工程','男','1989-06-10',42,null,null),
(081202,'王林','通信工程','男','1989-01-29',40,null,'有一门功课不及格待考');
2、kc表
create table KC(课程号 int(6) not null primary key ,
课程名 char(8) not null,
开课学期 int(4) not null,
学时 int(4) not null,
学分 int(3));
数据
insert into KC values(101,'计算机基础',1,80,5),
(102,'程序设计语言',2,68,4),
(206,'离散数学',4,68,4),
(208,'数据结构',5,68,4),
(209,'操作系统',6,68,4),
(210,'计算机原理',5,85,5),
(212,'数据库原理',7,68,4),
(301,'计算机网络',7,51,3),
(302,'软件工程',7,51,3);
3、xs_kc表
create table XS_KC(学号 int(6) not null,
课程号 int(8) not null,
成绩 int(4) not null,
学分 int(3));
数据
insert into XS_KC values(081101,101,80,5),
(081101,102,78,4),
(081101,206,76,4),
(081102,102,78,4),
(081102,206,78,4),
(081103,101,62,5),
(081103,102,70,4),
(081103,206,81,4);
三、解题方式
1、#查询xs表中各个同学的姓名、专业名、总学分
select 姓名,专业名,总学分
from xs;
2、#查询xs表中计算机系同学的学号、姓名、总学分，结果中各列的标题分别制定为number、name、mark
select 学号 as number,姓名 as name,总学分 as mark
from xs;
3、#查询xs表中计算机系各同学的学号、姓名、总学分，对其总学分按一下规则进行替换：若总分数位为空值，替换为“尚未选课”；若总学分小于50，替换为“不及格”；若总学分在50~52之间，替换为“合格”；总学分大于52替换为优秀，。列标题改为等级。
select 学号,姓名,总学分,
case
when 总学分 is null then '尚未选课'
when 总学分 < 50 then '不及格'
when 总学分>=50 and 总学分<=52 then '合格'
when 总学分>52 then '优秀'
end as 等级
from xs;
4、#按120分计算成绩，显示xs_kc表中的学号为081101的学生课程信息。
select kc.*,xs_kc.成绩
from xs join xs_kc on xs.学号 = xs_kc.学号 join kc on xs_kc.课程号=kc.课程号
where xs.学号=081101;
5、 #对xs表只选择专业名和总学分，消除结果集中的重复行
select distinct 专业名,总学分
from xs;
6、#求学生的总人数
select count(*) as 总人数
from xs;
7、 #统计备注不为空的学生数目
select count(*) as 备注不为空
from xs
where 备注 IS NOT NULL;
8、#统计总学分在50分以上的人数
select count(总学分>50 or null) as 总学分50分以上
from xs;
9、 #求选修101课程的学生所学课程的最高分和最低分
select max(成绩),min(成绩)
from xs_kc
where 课程号=101;
10、 #求学号081101的学生所学课程的总成绩
select sum(成绩) as 总成绩,xs.学号
from xs join xs_kc using(学号)
where 学号=081101;
11、 #求选修101课程的学生的平均成绩
select avg(成绩)
from xs_kc
where 课程号 like 101;
12、 #查询xs表中学号为081101的学生情况
select 学号,姓名,性别,总学分
from xs
where 学号 = 081101;
13、 #查询xs表中总学分大于50的学生情况
select 学号,姓名,性别,总学分
from xs
where 总学分>50;
14、 #查询xs表中备注为空的同学情况
select 学号,姓名,性别,总学分
from xs
where 备注 is null;
15、 #查询xs表中专业为计算机，性别为女的同学情况
select 学号,姓名,性别,总学分
from xs
where 专业名='计算机' and 性别='女';
16、#查询xs_kc表中102和206课程中大于80分的同学的记录
select *
from xs_kc
where (课程号=102 or 课程号=206) and 成绩>80;
17、 #查询表中姓王的学生学号、性别、姓名
select 学号,性别,姓名
from xs
where 姓名 like '王%';
18、 #查询xs表中学号倒数第2位为0的学生学号、姓名、专业名
select 学号,姓名,专业名
from xs
where 学号 like '%0_';
19、#查询xs表中名字包括下划线的学生学号和姓名
select 学号,姓名
from xs
where 姓名 like '%#_%' escape '#';
20、#查询xs表中不在1989年出生的学生情况
select 学号,姓名,性别,出生时间
from xs
where 出生时间 <'1989-1-1' or 出生时间>'1989-12-31';
21、 #查询xs表中专业名为 计算机 通信工程 的学生情况
select 学号,姓名,性别
from xs
where 专业名='计算机' or 专业名='通信工程';
22、 #查询xs中总学分尚不定的学生情况
select 学号,姓名,性别
from xs
where 总学分 is null;
23、 #查找所有学生选过的课程名和课程号
select distinct 课程名,课程号
from xs_kc join kc using(课程号)
where kc.课程号 in (select 课程号 from xs_kc);
24、 #查找选修了206课程且成绩在80分以上的学生姓名、成绩。
select 姓名,成绩
from xs join xs_kc using (学号)
where 课程号=206 and 成绩>80;
25、 #查找选修了 计算机基础 课程且成绩在80分以上的学生学号 、姓名、课程名及成绩
select 学号,姓名,课程名,成绩
from kc join xs_kc using (课程号)
join xs using (学号)
where 课程名='计算机基础' and 成绩>80;
26、 # #查找课程不同、成绩相同的学生的学号、课程号、成绩
select distinct 课程号,学号,成绩
from kc join xs_kc using (课程号)
where 成绩 in (select 成绩 from xs_kc group by 成绩 having count(成绩)>1);
27、 #查找kc表中所有学生选过的课程名
select distinct 课程名
from kc join xs_kc using (课程号);
28、 #查找所有学生情况及他们选修的课程号，若学生为选修任何课，也要包括其情况
select distinct 姓名,专业名,性别,
case
when xs.学号 not in (select xs_kc.学号 from xs_kc) then null
when xs.学号 in (select xs_kc.学号 from xs_kc) then xs_kc.课程号
end as 课程号
from xs_kc,xs;
29、 #查找被选修了的课程的选修情况和所有开设的课程名
select distinct 课程名,
case
when kc.课程号 not in (select 课程号 from xs_kc) then '未被选修'
when kc.课程号 in (select 课程号 from xs_kc) then '被选修'
end as 是否选修
from kc,xs_kc;
30、 #查找选修了的课程号为206课程的学生的姓名、学号
select 姓名,学号
from xs_kc join xs using (学号)
where 课程号=206;
31、#查找未选修离散数学的学生的姓名、学号、专业名
select 学号,姓名,专业名
from xs where 学号 not in
(select 学号 from xs join xs_kc using (学号) join kc using (课程号) where 课程名='离散数学');
32、#查找选修离了散数学的学生的姓名、学号、专业名
select 姓名,学号,专业名
from xs join xs_kc using (学号) join kc using (课程号)
where 课程名='离散数学';
33、#查找xs表中比所有计算机系的学生年龄都大的学生的学号、姓名、专业名、出生日期
select 学号,姓名,专业名,出生时间
from xs
where 出生时间< all (select 出生时间 from xs where 专业名='计算机');
34、 #查找xs_kc表中课程号206的成绩不低于课程号101的最低成绩的学生的学号
select 学号
from xs_kc
where 课程号=206 and 成绩>(select min(成绩) from xs_kc where 课程号=101);
35、 #查找选修了206号课程的学生姓名
select 姓名
from xs join xs_kc using (学号)
where 课程号=206;
36、 #将xs中各专业名输出
select distinct 专业名
from xs;
37、 #求xs中各专业的学生数
select 专业名,count(专业名) as '各专业的学生数'
from xs
group by 专业名;
38、 #求被选修的各门课程的平均成绩和选修该课程的人数
select count(课程号) as 选修该课程的人数 ,avg(成绩) as 平均成绩
from xs_kc
group by 课程号;
39、#在xs_kc数据库上产生一个结果集，包括每个专业的男生人数、女生人数、总人数、学生总人数
select 专业名,性别,count(*) as 人数
from xs group by 专业名,性别
with rollup ;
40、 #查找平均成绩在85分以上的学生的学号和平均成绩
select 学号,avg(成绩) as 平均成绩
from xs_kc
group by 学号
having avg(成绩)>85;
41、#查找选修课超过2门且成绩都在80分以上的学生的学号
select distinct 学号
from xs_kc
where 成绩>=80
group by 学号 having count(*)>2;
42、 #将通信工程专业的学生按出生时间先后排序
select 出生时间
from xs
where 专业名='通信工程'
order by 出生时间 asc ;
43、 #将计算机专业的学生的 “计算机基础” 课程成绩按降序排列
select 成绩
from xs_kc join kc using (课程号)
where 课程名='计算机基础'
order by 成绩 desc ;
44、 #将计算机专业的学生按其平均成绩排列
select distinct xs_kc.学号,avg(成绩)
from xs_kc,xs
where 专业名='计算机'
group by xs_kc.学号;
45、 #查找xs表中学号最靠前的5为学生信息
select 学号,姓名,性别
from xs
limit 5;
46、 #查找xs表中从第4位同学开始的5位学生的信息
select 学号,姓名,性别
from xs
limit 4,5;
注：上述例题在一心三用中所写，如有错误，烦请指正。
如有更好的解题思路或想法，还请阁下谏言。
399
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
