MySql语句50道练习

MySql练习

碎语

题源于：题源于知乎
这里的题解一些是我自己写的，一些是参考上面知乎的。
本身是不想发出来的，但学习嘛！总要有些动力和目的，我呢就是写写笔记咯！，还有就是像他说的给自己一个练习的机会。

正文

#创建数据库
create database studentsystem;
#查看所有数据库
show databases;
#使用数据库
use studentsystem;
#删除数据库
drop database studentsystem;

#[1]建立学生表，id（主键） 姓名，生日，性别
CREATE TABLE Student(
Sid int PRIMARY key auto_increment,
Sname VARCHAR(20) not null,
Sbirthday VARCHAR(20) not null,
Ssex VARCHAR(20) not null
);

#[2]课程表 id主键 课名id 老师id
#auto_increment 主键自动增长
#primary key(_) 设置主键

#添加外键的两种方式
#1，创建表时，foreign key (Tid) references Teacher (Tid)
#2，对建好的表经行修改，
#ALTER TABLE 表名 ADD FOREIGN KEY [外键名字] (外键字段) REFERENCES 父表 (主键字段);
#alter table Teacher add foreign key (Tid) references Teacher(Tid)

create table Course(
Cid int auto_increment,
Cname varchar(20) not null,
Tid int not null,
primary key(Cid)
);

#[3]老师表 id主键 老师姓名
#add constraint 添加约束
#对以建好的表加约束，alter table Teacher add constraint primary key(Tname)
create table Teacher(
Tid int auto_increment,
Tname varchar(20) not null,
primary key(Tid)
);

#[4]成绩表 学生id 课程id 主键 成绩表
#float(3,3) 3代表整数部分，3代表小数部分，只能存6个数字
#超过6个就用了科学记数法
create table Score(
Sid int,
Cid int,
Score float(3,3),
primary key(Sid,Cid)
);
#查看表结构
describe Score;
#增加列： alter table 表名 add 列名 类型(长度) 约束;
alter table score add name varchar(20);
#修改现有列类型、长度和约束 语法：alter table 表名 modify 列名 类型(长度) 约束;
alter table Score modify Score float(4,2);
#修改现有列名称 语法：alter table 表名 change 旧列名 新列名 类型(长度) 约束;
alter table score change name scorenamae varchar(20);
#删除现有列 语法：alter table 表名 drop 列名 ;
alter table score drop scorenamae;
#修改表名 rename table 旧表名 to 新表名;
#[5]插入数据
insert into Student values(‘01’ , ‘蔡文姬’ , ‘1990-01-01’ , ‘女’);
insert into Student values(‘02’ , ‘曹操’ , ‘1990-02-02’ , ‘男’);
insert into Student values(‘03’ , ‘孙尚香’ , ‘1990-03-03’ , ‘女’);
insert into Student values(‘04’ , ‘刘备’ , ‘1990-04-04’ , ‘男’);
insert into Student values(‘05’ , ‘小乔’ , ‘1990-05-05’ , ‘女’);
insert into Student values(‘06’ , ‘周瑜’ , ‘1990-06-06’ , ‘男’);
insert into Student values(‘07’ , ‘紫霞仙子’ , ‘1990-07-07’ , ‘女’);
insert into Student values(‘08’ , ‘至尊宝’ , ‘1990-08-08’ , ‘男’);

insert into Course values(‘01’,‘c语言’,‘02’);
insert into Course values(‘02’,‘java高级语言’,‘01’);
insert into Course values(‘03’,‘linux课程’,‘03’);
insert into Course values(‘04’,‘python设计’,‘04’);

insert into Teacher values(‘01’,‘杨玉环’);
insert into Teacher values(‘02’,‘武则天’);
insert into Teacher values(‘03’,‘大乔’);
insert into Teacher values(‘04’,‘诸葛亮’);

insert into Score values(‘01’ ,‘01’ , ‘80’);
insert into Score values(‘01’ , ‘02’ , 90);
insert into Score values(‘01’ , ‘03’ , 99);
insert into Score values(‘01’ , ‘04’ , 88);
insert into Score values(‘02’ , ‘01’ , 70);
insert into Score values(‘02’ , ‘02’ , 60);
insert into Score values(‘02’ , ‘03’ , 80);
insert into Score values(‘02’ , ‘04’ , 69);
insert into Score values(‘03’ , ‘01’ , 80);
insert into Score values(‘03’ , ‘02’ , 78);
insert into Score values(‘03’ , ‘03’ , 60);
insert into Score values(‘03’ , ‘04’ , 83);
insert into Score values(‘04’ , ‘01’ , 54);
insert into Score values(‘04’ , ‘02’ , 35);
insert into Score values(‘04’ , ‘03’ , 25);
insert into Score values(‘04’ , ‘04’ , 56);
insert into Score values(‘05’ , ‘01’ , 76);
insert into Score values(‘05’ , ‘02’ , 87);
insert into Score values(‘05’ , ‘03’ , 97);
insert into Score values(‘06’ , ‘01’ , 31);
insert into Score values(‘06’ , ‘03’ , 34);
insert into Score values(‘07’ , ‘02’ , 89);
insert into Score values(‘07’ , ‘03’ , 98);
insert into Score values(‘08’ , ‘02’ , 100);

#删除表中数据
DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
delete from score where sid=1;
题目

#1，查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。

##inner join(可以简写为join)来链接以下两种表，join（叫内连接，或等值链接）：获取两张表中字段匹配关系的记录
##与之还有的链接有左连接和右链接
##LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
##RIGHT JOIN（右连接）： 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

select a.sid from (select * from score where cid=‘01’) as a
join (select * from score where cid=‘02’) as b
on a.sid=b.sid
where a.score > b.score;
另一种方法：

#2、查询平均成绩大于60分的学生的学号和平均成绩

#group by语句根据一个或多个列对结果集进行分组，在分组列上可以使用count，sum，avg函数
#having与where
#区别一：[1]where 是数据从磁盘读入内存时候一条一条判断的[2]having 是将所有数据读入内存，在分组统计前，根据having的条件再将不符合条件的数据删除
#区别二：having 子句可以使用字段别名，where不可以用
#区别三：[1]having可以使用统计函数，where不可用，[2]having筛选必须是 根据前面select字段的值 进行筛选

select sid,avg(score) from score group by sid having avg(score)>60;

#3、查询所有学生的学号、姓名、选课数、总成绩
select student.sid,sname,count(cid) as “count”,sum(score) as “sum”
from student join score on student.sid=score.sid
group by score.sid,sname;

#4、查询姓“杨”的老师的个数
select count(Tname) where tname like “杨%”;

#5.查询没学过“大乔”老师课的学生的学号、姓名(重点)
select student.sid,sname from student
where sid not in (select sid from score
join course on score.cid=course.cid
join teacher on teacher.tid = course.tid
where tname=“大乔”);

#6、查询学过“大乔”老师所教的所有课的同学的学号、姓名
select sid,sname from student
where sid in (select sid from score
join course on score.cid=course.cid
join teacher on teacher.tid = course.tid
where tname=“大乔”);
);

#7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
select student.sid,sname from student where sid in
(select a.sid from
(select sid from score where cid=“01”) as a
join (select sid from score where cid=“02”) as b
where a.sid=b.sid
);

#8、查询课程编号为“02”的总成绩
select sum(score) from score where sid=“02”;

#9、查询所有课程成绩小于60分的学生的学号、姓名
select sid,sname from student student where sid in
(select sid from score where score<60);

#10、查询没有学全所有课的学生的学号、姓名
[1]
select student.sid,sname from student join
(select sid ,count(cid) as count from score group by sid) as a
on student.sid=a.sid
join (select count(cid)as count from course) as b
where a.count<b.count;
[2]
select sid ,sname from student where sid in
(select sid from score group by sid
having count(cid)<(select count(cid) from course));

#11、查询至少有一门课与学号为“01”的学生所学课程相同的学生的学号和姓名
#distinct:去重，对null是不进行过滤的
#select distinct expression[,expression…] from tables [where conditions];
[1]
select sid,sname from student where sid in
(select sid from score where cid in (select cid from score where sid=‘01’))
and sid <>“01”;
[2]
select distinct student.sid , sname from student join score on student.sid=score.sid
where cid in (select cid from score where sid=‘01’)
and student.sid!= “01”;

#12、查询和“01”号同学所学课程完全相同的其他同学的学号
select sid from score where cid in (select cid from score where sid =“01”)
and sid <> “01”
group by sid having count(cid)=(select count(cid) from score where sid=“01”);

#13、把“SCORE”表中“大乔”老师教的课的成绩都更改为此课程的平均成绩
#UPDATE [LOW_PRIORITY] [IGNORE] table_reference
#SET assignment_list
#[WHERE where_condition]
update score,
(select score.cid ,avg(score) as avg from score
join course on score.cid = course.cid
join teacher on course.tid =teacher.tid where tname=“大乔”) as a
set score= a.avg
where score.cid =a.cid;

#14、查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名(同12题）
select student.sid,sname from student join score on student.sid=score.sid
where cid in
(select cid from score where sid=“02”)
and student.sid <> “02”
group by student.sid having count(cid)=(select count(cid) from score where sid=“02”);

#15、删除学习“大乔”老师课的SC表记录
delete from score where cid in(select cid from course join teacher on course.tid=teacher.tid where
tname=‘大乔’);

##case when语句，用于计算条件列表并返回多个可能结果表达式之一
##CASE input_expression
##WHEN when_expression THEN
##result_expression […n ] [
##ELSE
##else_result_expression
##END

#17、按平均成绩从高到低显示所有学生的“数据库”（c_id=‘04’）、“企业管理”（c_id=‘01’）、“英语”（c_id=‘06’）三门的课程成绩，按如下形式显示：学生ID，数据库，企业管理，英语，有效课程数，有效平均分
select sid as ‘学生ID’,
(case when cid=‘04’ then score else NULL end) as ‘数据库’,
(case when cid=‘01’ then score else NULL end) as ‘企业管理’,
(case when cid=‘06’ then score else NULL end) as ‘英语’,
count(cid) as 有效课程数,
avg(score) as 有效平均分
from Score
group by sid
order by avg(score) DESC;

#18、查询各科成绩最高和最低的分： 以如下的形式显示：课程ID，最高分，最低分
select cid as ‘课程id’,max(score) as ‘最低分’,min(score) as ‘最高分’ from score group by cid;

##concat一句话概括就是拼接字符串，concat(a,b):拼接a和b
#19、按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：课程号,课程名,平均成绩,及格百分数
select a.cid as ‘课程号’,cname as ‘课程名’,avg(score) as ‘平均成绩’,concat(
(select count(b.score) from Score b where b.score>=60 and a.cid=b.cid)/
(select count(b.score) from Score b where a.cid=b.cid)*100
,’%’) as ‘及格百分数’
from Score a join Course c
on a.cid=c.cid
group by a.cid,cname
order by 平均成绩,及格百分数 DESC;

#20、查询如下课程平均成绩和及格率的百分数（用1行显示），其中企业管理为001，马克思为002，UML为003，数据库为004
……
#21、查询不同老师所教不同课程平均分从高到低显示
select teacher.tid,teacher.tname,course.cname,avg(score.score)
from teacher join course on teacher.tid=course.tid
join score on score.cid=course.cid
group by tid
order by avg(score.score) desc;

#22、查询如下课程成绩第3名到第6名的学生成绩单，其中企业管理为001，马克思为002，UML为003，数据库为004，以如下形式显示：
学生ID学生姓名企业管理马克思UML数据库平均成绩
……

#23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称
select a.cid as ‘课程id’,cname as ‘课程名’ ,
sum(case when score between 85 and 100 then 1 else 0 end) as ‘[100-85]’,
sum(case when score >= 70 and score < 85 then 1 else 0 end) as ‘[70-85]’,
sum(case when score >= 60 and score < 70 then 1 else 0 end) as ‘[70-60]’,
sum(case when score < 60 then 1 else 0 end) as ‘[<60]’ from
score a join course b on a.cid = b.cid
group by a.cid,cname;

#24、查询学生平均成绩及其名次
select sid as ‘学生id’,平均成绩,
(select count(*) from (
select sid ,avg(score) as ‘平均成绩’ from score group by sid) as b
where b.平均成绩>a.平均成绩)+1 as rank
from (select sid,avg(score) as 平均成绩 from score group by sid) as a
order by 平均成绩 desc;

#25、查询各科成绩前三名的记录（不考虑成绩并列情况）
select sid ,cid,score from score a
where (select count(*) from score b where a.cid=b.cid and a.score<b.score)<=2
order by cid asc,score desc;

#26、查询每门课程被选修的学生数
select cid,count(sid) from score group by cid;

#27 、查询出只选修了两门课程的全部学生的学号和姓名
select score.sid,sname from score join student on score.sid=student.sid
group by score.sid
having count(cid)=2
;

#28、查询男生、女生人数
select ssex,count(*) from student group by ssex;

#29、查询名字中含有“风”字的学生信息
select * from student where sname like ‘%乔%’;

#30、查询同名同姓学生名单并统计同名人数
insert into Student values(‘11’ , ‘至尊宝’ , ‘1990-08-08’ , ‘男’);
select a.sid,a.sname,count(*) from student a join student b on a.sname=b.sname
where a.sid<>b.sid;

#31、1990年出生的学生名单（注：Student表中s_birth列的类型是datetime）
select * from student where Sbirthday like ‘1990%’;

#32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
select student.sid,sname,avg(score) from student join score on score.sid=student.sid
group by student.sid
having avg(score)>85
;

#33、查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
select cid,avg(score) from score
group by cid desc
order by avg(score) asc;

#34、查询课程名称为“c语言”且分数低于60的学生姓名和分数
select sname,score from student join score on score.sid = student.sid join course on course.cid=score.cid
where cname = ‘c语言’ and score<60;

#35、查询所有学生的选课情况
select sid,course.cid,cname from score join course on score.cid=course.cid;

#36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数
select sname,cid,score from score join student on student.sid=score.cid
where score >70;

#37、查询不及格的课程并按课程号从大到小排列
select cid,sid from score where score<60
order by cid desc;

#38、查询课程编号为02且课程成绩在80分以上的学生的学号和姓名
select student.sid,sname,score from student join score on score.sid=student.sid
where cid=‘02’ and score>80;

##DISTINCT：去重
#39、查询选了课程的学生人数
select count(DISTINCT sid) from score;

#40、查询选修“张三”老师所授课程的学生中成绩最高的学生姓名及其成绩
select sname,score from student join score on score.sid=student.sid
join teacher on score.cid=teacher.cid
where tname=‘大乔’ and max(score)=score
group by cid;

#41、查询各个课程及相应的选修人数
select cid,count(sid) from score
group by cid;

#42、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.sid,a.cid,a.score from score a join score b on a.score=b.score
where a.cid<>b.cid ;

#43、查询每门课程成绩最好的前两名
select * from score b
where(select count(*) from score a where a.cid=b.cid and a.score>b.score)<=1
group by cid asc,score desc;

#44 、 统计每门课程的学生选修人数(超过5人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，按课程号升序排序
select cid,count(*) from score
group by cid desc
having count(sid)>5
order by count(sid) desc;

#45、查询至少选修两门课程的学生学号
select sid,count(cid) from score
group by sid
having count(cid)>2
;

#46、查询选修了全部课程的学生信息
select a.sid,a.sname from student a join score b on a.sid=b.sid
group by a.sid
having count(cid)=(select count(DISTINCT cid) from score);

#47、查询没学过“大乔”老师讲授的任一门课程的学生姓名
一，我的这个有问题，当没选课的时候就不能判断
select sname from student
join score on student.sid=score.sid
join course on course.cid=score.cid
join teacher on teacher.tid=course.tid
where tname <> ‘大乔’
group by student.sid;
二
select sname from Student
where sid not in (
select sid from Score
join Course on Score.cid=Course.cid
join Teacher on Teacher.tid=Course.tid
where tname = ‘大乔’);

#48、查询两门以上不及格课程的同学的学号及其平均成绩
select sid , avg(score)
from Score
where score <60
group by sid
having count(cid)>=2;

#49、检索课程编号为“04”且分数小于60的学生学号，结果按按分数降序排列
select sid from score where cid = ‘04’ and score <60
order by score desc;

#50、删除学生编号为“02”的课程编号为“01”的成绩
delete from score
where sid =‘02’ and cid =‘01’;