关于mysql的练习习题 45道

于 2024-07-23 15:45:59 发布
阅读量391 收藏 9
点赞数 5
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/nice0164/article/details/140638136
版权

结果都可输出，可自行优化，本人所写较为繁琐

create database test1;
use test1;

create table Student(
	SId varchar(10),-- 学生编号 
	Sname varchar(10),-- 学生姓名
	Sage datetime,-- 出生年月
	Ssex varchar(10)-- 学生性别
	);
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-12-20' , '男');
insert into Student values('04' , '李云' , '1990-12-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-01-01' , '女');
insert into Student values('07' , '郑竹' , '1989-01-01' , '女');
insert into Student values('09' , '张三' , '2017-12-20' , '女');
insert into Student values('10' , '李四' , '2017-12-25' , '女');
insert into Student values('11' , '李四' , '2012-06-06' , '女');
insert into Student values('12' , '赵六' , '2013-06-13' , '女');
insert into Student values('13' , '孙七' , '2014-06-01' , '女');

create table Course(
	CId varchar(10),-- 课程编号
	Cname nvarchar(10),-- 课程名称
	TId varchar(10)-- 教师编号
	);
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');

create table Teacher(
	TId varchar(10),-- 教师编号
	Tname varchar(10)-- 教师名称
	);
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');

create table SC(
	SId varchar(10),-- 学生编号
	CId varchar(10),-- 课程编号
	score decimal(18,1)-- 分数
	);
insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

-- 1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select t1.sid,t1.s1,t2.s2
from (select sid,score s1 from sc where cid = '01') t1,(select sid,score s2 from sc where cid = '02') t2 
where t1.sid=t2.sid and t1.s1>t2.s2; -- 01比02大的课程

select * from student a join (select t1.sid,t1.s1,t2.s2
from (select sid,score s1 from sc where cid = '01') t1,(select sid,score s2 from sc where cid = '02') t2 where t1.sid=t2.sid and t1.s1>t2.s2) b on a.sid = b.sid

-- 1.1 查询同时存在" 01 “课程和” 02 "课程的情况
select t1.sid,t1.s01,t2.s02
from (select sid,score s01 from sc where cid = '01') t1,(select sid,score s02 from sc where cid = '02') t2 
where t1.sid=t2.sid;
-- 1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select t1.sid,t1.s01,t2.s02
from (select sid,score s01 from sc where cid = '01') t1 
left join
(select sid,score s02 from sc where cid = '02') t2 
on t1.sid=t2.sid;
-- 1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
select t1.sid,t1.s01,t2.s02
from (select sid,score s01 from sc where cid = '01') t1 
right join
(select sid,score s02 from sc where cid = '02') t2 
on t1.sid=t2.sid;

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select sid,AVG(score) from sc GROUP BY sid having AVG(score)>60;

select a.sid,a.sname,b.s from student a,(select sid,AVG(score) s from sc GROUP BY sid having AVG(score)>60) b where a.sid = b.sid;
-- 3.查询在 SC 表存在成绩的学生信息
select DISTINCT(a.sid),a.sname,a.sage,a.ssex from student a RIGHT JOIN sc b on a.sid = b.sid;
select DISTINCT student.* from student,sc where student.sid = sc.sid;
-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select sid,sum(score),count(*) from sc GROUP BY sid;

select a.sid,a.sname,b.fen,b.shu from student a,(select sid,sum(score) fen,count(*) shu from sc GROUP BY sid) b where a.sid = b.sid;


-- 4.1查有成绩的学生信息
select DISTINCT student.* from student,sc where student.sid = sc.sid;
select * from student where sid in(select sid from sc)
-- 5.查询「李」姓老师的数量
select count(*) from teacher where tname like '李%';
-- 6.查询学过「张三」老师授课的同学的信息
select cid from course c,teacher t where c.tid=t.tid and t.tname='张三';-- 先找老师所教课程对应的cid
select sid from sc where cid = (select cid from course c,teacher t where c.tid=t.tid and t.tname='张三');-- 根据上一步的cid找对应的sid
select * from student where sid in(select sid from sc where cid = (select cid from course c,teacher t where c.tid=t.tid and t.tname='张三'));-- 结合查询学生信息

-- 7.查询没有学全所有课程的同学的信息
select count(cid) from course;
select sid,count(cid) from sc GROUP BY sid HAVING count(cid)=(select count(cid) from course);
select * from student where sid not in(select sid from sc GROUP BY sid HAVING count(cid)=(select count(cid) from course));
-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信
select cid from sc where sid ='01';
select DISTINCT sid from sc where cid in(select cid from sc where sid ='01');
select * from student  where sid in(select sid from sc where cid in(select cid from sc where sid ='01'));
select * from student  where sid in(select DISTINCT sid from sc where cid in(select cid from sc where sid ='01'));
-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
select cid from sc where sid ='01';
select count(*) from sc where sid='01' GROUP BY sid;

SELECT s.* FROM student AS s WHERE SId IN(
SELECT sc2.SId FROM 
(SELECT * FROM sc WHERE SId='01') AS sc1 
JOIN sc as sc2 
ON sc1.CId=sc2.CId GROUP BY sc2.SId 
HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE SId='01' GROUP BY SId)
) AND SId<>'01';

-- 10.查询没学过"张三"老师讲授的任一门课程的学生姓名
select c.* from course c,teacher t where c.tid=t.tid and t.tname='张三';
select sid from sc where cid in(select cid from course c,teacher t where c.tid=t.tid and t.tname='张三');
select * from student where sid not in(select sid from sc where cid in(select cid from course c,teacher t where c.tid=t.tid and t.tname='张三'));


-- 11.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select * from sc where score < 60;
select sid from sc where score < 60 GROUP BY sid HAVING count(*)>1;

select a.sid,a.sname,b.avg
	from student a join 
	(select sid,AVG(score) avg from sc where sid in(select sid from sc where score < 60 GROUP BY sid HAVING count(*)>1) GROUP BY sid) b
	on a.sid = b.sid;
	
/*	select Student.SId, Student.Sname,b.avg
     from Student RIGHT JOIN
     (select sid, AVG(score) as avg from SC
         where sid in (
                   select sid from SC
                   where score<60
                   GROUP BY sid
                   HAVING count(score)>1)
         GROUP BY sid) b on Student.sid=b.sid;
*/	
	
-- 12.检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select * from sc where cid = '01' and score < 60;

select a.* from student a,sc b where a.sid=b.sid and a.sid in(select sid from sc where cid = '01' and score < 60) and b.cid = '01' order by b.score desc;

-- 13.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select sid,AVG(score) avg from sc GROUP BY sid;
select a.sid,a.sname,b.avg from student a 
	join (select sid,AVG(score) avg from sc GROUP BY sid) b on a.sid = b.sid;
	
select * from sc a join (select sid,AVG(score) avg from sc GROUP BY sid) b on a.sid = b.sid;

-- 14.查询各科成绩最高分、最低分和平均分：
/*以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
*/
select b.cid,b.cname,AVG(score)as 平均分,MAX(score)as 最高分,
MIN(score)as 最低分,count(*)as 选修人数,
sum(case when a.score>=60 then 1 else 0 end )/count(*)as 及格率,
sum(case when a.score>=70 and a.score<80 then 1 else 0 end )/count(*)as  中等率,
sum(case when a.score>=80 and a.score<90 then 1 else 0 end )/count(*)as  优良率,
sum(case when a.score>=90 then 1 else 0 end )/count(*)as 优秀率
from sc a join course b on a.cid=b.cid GROUP BY b.cid,b.cname
ORDER BY count(*)DESC,a.CId ASC;
/*结尾处不加c.cname会因为b.cname列未包含在GROUP BY子句中，而它是一个非聚合列，进而报错*/

 select
     SC.CId ,cname,
     max(SC.score)as 最高分,
     min(SC.score)as 最低分,
     AVG(SC.score)as 平均分,
     count(*)as 选修人数,
     sum(case when SC.score>=60 then 1 else 0 end )/count(*)as 及格率,
     sum(case when SC.score>=70 and SC.score<80 then 1 else 0 end )/count(*)as        中等率,
     sum(case when SC.score>=80 and SC.score<90 then 1 else 0 end )/count(*)as        优良率,
     sum(case when SC.score>=90 then 1 else 0 end )/count(*)as 优秀率
     from SC JOIN course b on sc.cid = b.cid
     GROUP BY SC.CId,b.cname
     ORDER BY count(*)DESC, SC.CId ASC;


-- 15.按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
select *,DENSE_RANK() over(PARTITION by cid order by score) from sc;


-- 16.查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select *,dense_rank()over(order by 总成绩 desc)排名 from(
select sid,SUM(score)总成绩 from SC group by sid)A

-- 17.统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比

select course.cname, course.cid,
sum(case when sc.score<=100 and sc.score>85 then 1 else 0 end)/count(*) as "[100-85]",
sum(case when sc.score<=85 and sc.score>70 then 1 else 0 end)/count(*) as "[85-70]",
sum(case when sc.score<=70 and sc.score>60 then 1 else 0 end)/count(*) as "[70-60]",
sum(case when sc.score<=60 and sc.score>0 then 1 else 0 end)/count(*) as "[60-0]"
from sc left join course
on sc.cid = course.cid
group by sc.cid,course.cname;-- 只有小数，没转换为百分比


select
	s.cid  编号
	,c.cname  科目
	,sum(case when s.score >= 85 and  s.score<= 100 then 1 else 0 end) "[85,100]人数"
	,round(100 * (sum(case when s.score >= 85 and  s.score<= 100 then 1 else 0 end) / sum(case when s.score then 1 else 0 end)), 2) as '[85,100]百分比'
	,sum(case when s.score >= 70 and  s.score<= 85 then 1 else 0 end) "[70,85]人数"
	,round(100 * (sum(case when s.score >= 70 and  s.score<= 85 then 1 else 0 end) / sum(case when s.score then 1 else 0 end)), 2) as '[70,85]百分比'
	,sum(case when s.score >= 60 and  s.score<= 70 then 1 else 0 end) "[60,70]人数"
	,round(100 * (sum(case when s.score >= 60 and  s.score<= 70 then 1 else 0 end) / sum(case when s.score then 1 else 0 end)), 2) as '[60,70]百分比'
	,sum(case when s.score >= 0 and  s.score<= 60 then 1 else 0 end) "[0,60]人数"
	,round(100 * (sum(case when s.score >= 0 and  s.score<= 60 then 1 else 0 end) / sum(case when s.score then 1 else 0 end)), 2) as '[0,60]百分比'
from sc s
left join course c
on s.cid = c.cid
group by s.cid, c.cname;


-- 18.查询各科成绩前三名的记录
select * from (select *,dense_rank() over(PARTITION by cid order by score desc) as number from sc) a where a.number<=3;

 select * from SC
     where (
     select count(*) from SC as a
     where SC.cid = a.cid and SC.score<a.score
     )< 3
     order by cid asc, SC.score desc;


-- 19.查询每门课程被选修的学生数
select cid,count(*) from sc GROUP BY cid;
-- 20.查询出只选修两门课程的学生学号和姓名
select * from student where sid in (select sid from sc GROUP BY sid having count(cid)=2);
-- 21.查询男生、女生人数
select ssex,count(*) from student GROUP BY ssex;
-- 22.查询名字中含有「风」字的学生信息
select * from student where sname like '%风%';
-- 23.查询同名同性学生名单，并统计同名人数
select sname,count(*) from student GROUP BY sname HAVING count(*) > 1;
-- 24.查询 1990 年出生的学生名单
select * from student where year(sage)='1990';
-- 25.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select cid,AVG(score) 平均成绩 from sc GROUP BY cid ORDER BY 平均成绩 desc,cid asc;

-- 26.查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩
select student.*,a.平均成绩 from student join (select sid,round(avg(score),2) 平均成绩 from sc GROUP BY sid) a on student.sid=a.sid where a.平均成绩>85;

-- 27.查询课程名称为「数学」，且分数低于 60 的学生姓名和分数
select * from sc a join course b on a.cid=b.cid where b.cname ='数学';
select a.sname,b.cname,b.score from student a 
	join (select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid where b.cname ='数学') b 
	on a.sid=b.sid
	where b.score < 60;
-- 28.查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid ;
select * from student a 
left join (select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid) b 
on a.sid=b.sid;

select Student.sname, cid, score from Student
left join SC
on Student.sid = SC.sid;


-- 29.查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid where score >70;
select a.sname,b.cname,b.score from student a 
	join (select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid where score >70) b on a.sid=b.sid;
	
-- 30.查询不及格的课程
select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid where score < 60;

-- 31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid where score >= 80 and a.cid = '01';

select a.sid,a.sname,b.cname,b.score from student a 
	join (select a.sid,a.cid,a.score,b.cname from sc a join course b on a.cid=b.cid where score >= 80 and a.cid = '01') b on a.sid=b.sid;


-- 32.求每门课程的学生人数
select cid,count(*)学生人数 from sc GROUP BY cid;

-- 33.成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,b.score,c.cname,d.tname from student a,sc b,course c,teacher d
	where a.sid=b.sid 
	and b.cid=c.cid 
	and c.tid=d.tid 
	and d.tname ='张三' 
	ORDER BY b.score desc
	LIMIT 1;


select a.tid,a.tname,b.sid,b.cid,b.score,b.cname from teacher a join (select a.sid,a.cid,a.score,b.cname,b.tid from sc a join course b on a.cid=b.cid ORDER BY a.score desc) b on a.tid=b.tid where a.tname = '张三';

select a.sid,a.cid,a.score,b.cname,b.tid from sc a 
	join course b on a.cid=b.cid ORDER BY a.score desc; 

-- 34.绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select Student.*, SC.score, SC.cid from Student, Teacher, Course,SC
     where Teacher.tid = Course.tid
     and SC.sid = Student.sid
     and SC.cid = Course.cid
     and Teacher.tname = "张三"
     and SC.score = (
         select Max(SC.score)
         from SC,Student, Teacher, Course
         where Teacher.tid = Course.tid
         and SC.sid = Student.sid
         and SC.cid = Course.cid
         and Teacher.tname = "张三"
     );


-- 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select 	DISTINCT a.sid,a.cid,a.score from sc a join sc b on a.sid !=b.sid where a.score=b.score and a.cid != b.cid; 

select
	DISTINCT a.sid
	,a.cid
	,a.score
from sc a
join sc b
on a.cid != b.cid
where a.score = b.score
and a.sid != b.sid;

-- 36.查询每门功成绩最好的前两名
select cid,max(score) from sc GROUP BY cid;
-- (1) 先查询出每个课程的成绩排名
select *,dense_rank() over(PARTITION by cid ORDER BY score desc) as pai
from (select a.sid,a.sname,b.cid,c.cname,b.score 
	from student a,sc b,course c 
	where a.sid =b.sid 
	and c.cid=b.cid) a;
-- (2)在(1)的基础上筛选出pai对于1,2的值	
select *
from (
select *,dense_rank() over(PARTITION by cid ORDER BY score desc) as pai
from (select a.sid,a.sname,b.cid,c.cname,b.score 
	from student a,sc b,course c 
	where a.sid =b.sid 
	and c.cid=b.cid) a
) b
where pai in(1,2);

-- 先找出语文的前2名同学
(select c.cid,sc.sid,sc.score,c.cname from Sc join Course c on sc.cid = c.cid where c.cname = '语文' order by sc.score desc limit 2)
UNION
(select c.cid,sc.sid,sc.score,c.cname from Sc join Course c on sc.cid = c.cid where c.cname = '数学' order by sc.score desc limit 2) 
UNION
(select c.cid,sc.sid,sc.score,c.cname from Sc join Course c on sc.cid = c.cid where c.cname = '英语' order by sc.score desc limit 2);


select *
from sc as t1
where (select count(*) from sc as t2 where t1.CId=t2.CId and t2.score >t1.score)<2
ORDER BY t1.CId
-- 37.统计每门课程的学生选修人数（超过 5 人的课程才统计）
select cid,count(*) from sc GROUP BY cid HAVING count(*)>5;
-- 38.检索至少选修两门课程的学生学号
select sid,count(*) from sc GROUP BY sid HAVING count(*)>=2;
-- 39.查询选修了全部课程的学生信息
select * from student;
select count(cid) from sc GROUP BY sid;
select count(cname) from course;
select * from teacher;

select student.*  from student where sid in(select sid from sc GROUP BY sid having count(*)>= (select count(*) from course));
-- 40.查询各学生的年龄，只按年份来算
select sname,(YEAR(NOW())-YEAR(sage)) 年龄 from student; 
-- 41.按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select sname,date(NOW()),DATE(sage) from student ;

-- 自己的方法

select *
	,case when dayofyear(now()) >= dayofyear(sage) then year(now()) - year(sage)
		when dayofyear(now()) < dayofyear(sage) then year(now()) - year(sage) - 1
	else 'other' end as 'age'
from Student;

-- 42.查询本周过生日的学生
select *
from Student
where week(sage) = week(now());

-- 43.查询下周过生日的学生
select *
from Student
where week(sage) = week(now())+1;

-- 44.查询本月过生日的学生
select *
from Student
where month(sage) = month(now());

-- 45.查询下月过生日的学生
select *
from Student
where month(sage) = month(now()) + 1;

nice0164
关注
  • 5
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL中教师职称英文_mysql综合性练习
weixin_36349918的博客
02-27 524
题目描述设定有一个数据库，里面有4张表：学生表(student)课程表(course)成绩表(score)教师信息表(teacher)表结构如下：表一_学生表(student)属性名数据类型含 义snoint学号(主键)snamevarchar学生姓名ssexvarchar学生性别sbirthdaydate学生出生年月classidvarchar学生所在班级表二_课程表(course)属性名数据类...
MySQL50道练习题.zip
04-13
这里我们有“MySQL50道练习题”的压缩包，它包含了一系列问题，旨在帮助用户深入理解和掌握MySQL的基本操作、查询技巧以及高级功能。通过解答这些练习题，你可以提升自己的SQL技能，更好地应对实际工作中的数据管理...
学习好sql
HuoDaLao的博客
09-16 438
科目表 Course数据表介绍 --1.学生表 Student(SId,Sname,Sage,Ssex) --SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别 --2.课程表 Course(CId,Cname,TId) --CId 课程编号,Cname 课程名称,TId 教师编号 --3.教师表 Teacher(TId,Tname) --TId 教师编号,Tna...
sql面试题（学生表_课程表_成绩表_教师表）
04-02 4577
原帖链接:http://bbs.csdn.net/topics/280002741 表架构 Student(S#,Sname,Sage,Ssex) 学生表Course(C#,Cname,T#) 课程表SC(S#,C#,score) 成绩表Teacher(T#,Tname) 教师表 建表语句 CREATE TABLE student ( s# ...
SQL语句加强
不知谁唱、唱给谁的专栏
03-20 624
这里是我总结的SQL语句练习员工表准备CREATE TABLE employee ( id INT PRIMARY KEY auto_increament, -- 主键 name VARCHAR (50), -- 名字 salary BIGINT, -- 薪水 depid INT -- 部门id )一、用两种方式根据部门号从高到低，工资从低到高列出每个员工的信息。
MySQL 学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）
热门推荐
lenka的专栏
09-04 7万+
设有一数据库，包括四个表：学生表（Student）、课程表（Course）、成绩表（Score）以及教师信息表（Teacher）。四个表的结构分别如表1-1的表（一）~表（四）所示，数据如表1-2的表（一）~表（四）所示。用SQL语句创建四个表并完成相关题目。 表1-1数据库的表结构 表（一）Student (学生表) 字段名...
MySQL练习题（34道）
09-12
MySQL练习题涵盖了一系列基础到进阶的查询技巧，旨在帮助初学者巩固基础，熟练掌握数据库操作。以下是对这些练习题的详细解析： 1. **取得每个部门最高薪水的人员名称**： 这个问题可以通过`GROUP BY`与`MAX`函数...
MYSQL练习题34道.zip
06-16
这份"MYSQL练习题34道.zip"资源包含了34个针对MySQL基础知识的练习题目，旨在帮助初学者巩固和提升他们在数据库操作方面的能力，特别是涉及增删改查（CRUD）的基本操作。 1. **增（Create）**: 在MySQL中，创建数据...
50道MySQL经典练习题
12-14
这个是网上流传的50道SQL练习题，最近拿来练习，刚做完，这里把我做的答案给大家做个参考，如有错误，还请告知。 另外，做题的时候先不要看答案（答案不唯一，只要满足要求即可），要有自己的一个思考过程，这样做...
MySQL数据库练习题(50道).pdf
04-25
MySQL数据库练习题50道.pdf 本资源为MySQL数据库练习题，共50道题，涵盖数据库设计、数据表创建、数据插入、查询等多方面的知识点。下面是对该资源的详细知识点总结： 一、数据库设计 * 数据库设计是指根据具体的...
sql面试题- (学生表_课程表_成绩表_教师表)
08-06
1、查询“001”课程比“002”课程成绩高的所有学生的学号； select a.S# from (select s#,score from SC where C#='001') a,(select s#,score from SC where C#='002') b where a.score>b.score and a.s#=b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) >60; 3、查询所有同学的学号、姓名、选课数、总成绩； select Student.S#,Student.Sname,count(SC.C#),sum(score) from Student left Outer join SC on Student.S#=SC.S# group by Student.S#,Sname 4、查询姓“李”的老师的个数； select count(distinct(Tname)) from Teacher where Tname like '李%'; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname from Student where S# not in (select distinct( SC.S#) from SC,Course,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
【mysql题目】按各科成绩排序，并显示名次（同名次空缺vs合并）
Athain的博客
12-11 1万+
按各科成绩排序，并显示名次（同名次空缺vs合并） 数据表 -- 学生表 Student(sid,sname,sage,ssex) insert into Student values('01','张三','1991-11-11','男'); -- 课程表 Course(cid,cname,tid) insert into Course values('01','语文','01'); -- 教师表 ...
MySql建表语句，教师，学生，班级
07-13 3450
DROP DATABASE IF EXISTS Teach; CREATE DATABASE Teach DEFAULT CHARACTER SET utf8; USE Teach; CREATE TABLE class ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(255) DEFAULT NULL, PRIMARY K...
SQL加强（一）概念
有梦就有马
07-26 688
MySQL技术内幕：SQL编程学习
SQL server语句创建表
雷的博客
04-29 551
use LstDatabase --打开数据库名 create table techer --创建表名 ( 教师编号 int , --字段信息 教师职称 varchar(50), 教师年龄 int, 教师薪金 money, )
SQL经典案例（学生表，课程表，选课表，教师表） 练习
weixin_45791987的博客
09-13 3684
练习
50条经典（学生，课程，成绩，教师）表SQL语句~~值得一看!
OYY_90的博客
10-26 2314
  /*student(学号#,姓名,性别,年龄)  course(课程号#，课程名，教师号#)  score(学号#，课程号#，成绩)  teacher(教师号#，教师名)*/     --1.查询“001”课程比“002”课程成绩高的所有学生的学号   select a.stuNo from score a,score b   where a.cNo='c001' and b.cNo='c...
50条常用SQL语句
feiyangyangfei的专栏
10-31 2293
[cpp] view plaincopy /*student(学号#,姓名,性别,年龄)  course(课程号#，课程名，教师号#)  score(学号#，课程号#，成绩)  teacher(教师号#，教师名)*/   --1.查询“001”课程比“002”课程成绩高的所有学生的学号   select a.stuNo from score a,score 
MySQL经典四表查询(教师，学生，成绩，课程表)多表查询
hank.ll的博客
06-15 3万+
我用的工具：MySQL5.7+SQLyog-11.2.5-0 1、表架构呈现 student(sid,sname,sage,ssex) 学生表 teacher(tid,tname) 教师表 course(cid,cname,tid) 课程表 sc(sid,cid,score) 成绩表 2、建表SQL语句并插入数据 -- 1.学生表 CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, sname...
mysql查询语句练习题45题版
最新发布
09-12
很抱歉，我无法提供关于MySQL查询语句练习题的具体内容，因为您引用的内容似乎没有提到具体的第45题。但是，根据您所提供的引用内容，我可以给出一些关于MySQL查询语句练习的一般建议。 在MySQL中，查询语句用于从...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值