MySql数据库基本select查询语句练习题，初学者易懂。

客官°小女子只卖身不卖艺 2022-06-09 01:24 220阅读 0赞

在数据库建立四个表：分别为

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 and teacher.tname = "叶良辰");

\--  6、查询所有    课程有挂科的同学的学号、姓名

SELECT sid ,sname  
from 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、查询姓“李”的师生名单

(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 sc.cno=course.cno and cname='数据结构'   
);

\-- 1.统计每门课的选课人数，包括没有人选的课程，列出课程号及选课情况，其中选课情况为，如果此门课的选课人数超过100  
\-- 人，则显示人多，40-100一般 1-40人好，无人选

select  course.cno ,  
case   
when (count(\*)>=40 and count(\*)<=100) then  '较多'   
when (count(\*)>1 and count(\*)<40) then '较少'  
else '无人选' end as '选课情况'  
from course left join sc  
on course.cno=sc.cno  
GROUP BY cno;

\--  2.查询计算机有哪些学生没有选课，列出姓名和学号（用外连接）  
select sname  
from student left join  sc on  
sc.sno=student.sno  
where sdept="计科" and sc.sno is null;

2>-- 成绩小于60的学生姓名,课程,成绩

select sname,cname,grade  
from student,sc,course  
where student.sno=sc.sno and sc.cno=course.cno  
and grade<60;

\-- 3. 统计每个学生的选课人数和考试总成绩，并按照选课门数升序排列  
select sno,count(\*)'选课门数' ,sum(grade)'总成绩'  
from sc  
GROUP BY sno  
ORDER BY count(\*) DESC;