mysql查询语句习题._MySql数据库基本select查询语句练习
题，初学者易懂。
在数据库建⽴四个表：分别为
student(sid,sname,sage,ssex)
teacher(tid,tname)
course(cid,cname,tid)
sc(sid,cid,score)
-- 1、查询“001”课程⽐"002"课程成绩⾼的所有学⽣的学号。
select a.sid FROM
(select * from sc where cid="001") as a,
(select * from sc where cid = "002")as b
where a.sid = b.sid and a.score>b.score
-- 2、查询平均成绩⼤于60分的同学的学号和平均成绩
select sid,avg(score)
FROM sc
GROUP BY sid
HAVING avg(score)>=70;
-- 3、查询所有的同学的学号、姓名、选课数、总成绩
select student.sid, sname,COUNT(*),sum(score)
from sc,student
where sc.sid = student.sid
GROUP BY sid;
-
- 4、查询姓“李”的⽼师的个数
select COUNT(*)
from teacher
where tname LIKE '⼤%';
-- 5、查询没学过“叶平“⽼师的课程的同学的学号、姓名
SELECT sid,sname
from student
where sid NOT in(select sid
from sc,course,teacher
where sc.cid = course.cid and teacher.tid = course.tid ame = "叶良⾠");
--  6、查询所有    课程有挂科的同学的学号、姓名
SELECT sid ,sname
take chargefrom student
where
sid in (select sid from sc) AND
sid not in(select sid from sc where score<60 GROUP BY sid);
--  7、查询⾄少⼀门课与学号为“2”的同学所学相同的学⽣的学号和姓名
SELECT DISTINCT(sc.sid)
from sc,student
where student.sid = sc.sid and cid in(select cid from sc where sc.sid='2') and student.sid<>'2'; -- 8、统计列印各科成绩，各分数段⼈数：课程ID、课程名称，100-85,85-70,70-60,<60
select sc.cid as '课程ID',cname as '课程名称',
SUM(case WHEN score between 85 and 100 then 1 else 0 end) as '85-100',
SUM(case WHEN score between 70 and 84 then 1 else 0 end) as '70-84',
SUM(case WHEN score between 69 and 60 then 1 else 0 end) as '60-69',
SUM(case WHEN score between 0 and 100 then 59 else 0 end) as '0-59'
from course,sc
where sc.cid=course.cid
GROUP BY sc.cid;
--  9、查询每门课程的课程名和选修的学⽣数
select cname,count(*)
from sc,course
where course.cid=sc.cid
group by sc.cid;
-
- 10、查询出只选修了⼀门课程的全部同学的学号、姓名
select sc.sid,sname
from sc,student
where student.sid=sc.sid
GROUP BY (sc.sid)
HAVING COUNT(*)=1;
-- 11、查询男⽣、⼥⽣的⼈数
(select "男⽣" AS "性别",count(*) from student where ssex="男")
UNION
(select "⼥⽣" as "性别", count(*) from student where ssex="⼥");
--  12、查询姓“李”的师⽣名单mysql语句顺序
(select sname as '名单' from student where sname like '李%')
union
(select tname as '名单' from teacher where tname like "李%");
在数据库建⽴三个表：
学⽣表：student(sno,sname,sage,ssex,sdept)==(学号，姓名，年龄，性别，系别)
课程表：course(cno,cname,credit)==(课程号，课程名，学分)
选课表：sc(sno,cno,grade)===(学号，课程号，成绩)
-- 写出选修了数据结构的同学的学号和姓名
select *
from student
where sno in(select sno威尔士对阵美国
from sc,course
where sco=courseo and cname='数据结构'
);
-- 1.统计每门课的选课⼈数，包括没有⼈选的课程，列出课程号及选课情况，其中选课情况为，如果此门课的选课⼈数超过100 -- ⼈，则显⽰⼈多，40-100⼀般 1-40⼈好，⽆⼈选
select  courseo ,
颜英文的复数
case
when (count(*)>=40 and count(*)<=100) then  '较多'
热门的mysql数据库优化when (count(*)>1 and count(*)<40) then '较少'
else '⽆⼈选' end as '选课情况'
from course left join sc
on courseo=sco
GROUP BY cno;
--  2.查询计算机有哪些学⽣没有选课，列出姓名和学号(⽤外连接)
select sname
from student left join  sc on
sc.sno=student.snofgets段错误
where sdept="计科" and sc.sno is null;
2>-- 成绩⼩于60的学⽣姓名，课程，成绩
select sname,cname,grade
from student,sc,course
where student.sno=sc.sno and sco=courseo
and grade<60;
-
- 3. 统计每个学⽣的选课⼈数和考试总成绩，并按照选课门数升序排列select sno,count(*)'选课门数' ,sum(grade)'总成绩'
from sc
GROUP BY sno
ORDER BY count(*) DESC;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。