mysql练习

时间:2022-03-27 03:04:58

格式:

select * from tableName limit i,n
# tableName:表名
# i:为查询结果的索引值(默认从0开始),当i=0时可省略i
# n:为查询结果返回的数量
# i与n之间使用英文逗号","隔开
?
#
limit n 等同于 limit 0,n
12345678

栗子:

# 查询10条数据,索引从0到9,第1条记录到第10条记录
select * from t_user limit 10;
select * from t_user limit 0,10;
?
# 查询8条数据,索引从5到12,第6条记录到第13条记录
select * from t_user limit 5,8;

 

 

1、查询男生、女生的人数;

select group_concat(sname),count(sid) from student group by gender;
select count(sid) from student group by gender;

 

2、查询姓“张”的学生名单

select sname from student where sname like 张%;

 

3、课程平均分从高到低显示

select avg(num) from score group by course_id order by avg(num) desc;

 

4、查询有课程成绩小于60分的同学的学号、姓名;

select sid,sname from student where sid in (select student_id from score where num < 60);
select stu.sid,stu.sname,s.num from student as stu inner join score as s on stu.sid = s.student_id where s.num < 60;

 

5、查询至少有一门课与学号为1的同学所学课程相同的同学的学号和姓名;

select sid,sname from student where sid in (select student_id from score where course_id in (select course_id from score where student_id = 1));

 

6、查询出只选修了一门课程的全部学生的学号和姓名;

查询时报错:Operand should contain 1 column(s)。

原因是因为in条件后面有多个字段,in后面只能有一个字段。

如果如下写法加了一个course_id 就会报错

select sid,sname from student where sid in (select student_id ,course_id from score group by student_id having count(course_id) = 1);
select sid,sname from student where sid in (select student_id from score group by student_id having count(course_id) = 1);

 

7、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;

select * from (select  max(num),min(num),course_id from score group by course_id) as sc inner join course as cc on sc.course_id = cc.cid;

 

8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名;

自己的答案:

包括:  student_num | sname  | sid | student_id | course_id | num | cid | cname  | teacher_id
select * from (select * from (select student.sid as student_num,student.sname from student where 
((select score.num from score where score.course_id =1 and student.sid = score.student_id) >
(select score.num from score where score.course_id =2 and student.sid = score.student_id)))

as stu inner join score as s on stu.student_num = s.student_id where s.course_id in (1,2)) as ss inner join course as cc on ss.course_id = cc.cid;

 

 

标准答案:

包括:student_num | sname
select student.sid as student_num,student.sname from student where 
((select score.num from score where score.course_id =1 and student.sid = score.student_id) >

(select score.num from score where score.course_id =2 and student.sid = score.student_id)) ?

 

解析:通过select student.sid as student_num,student.sname from student 
查出student表中所有的student.sid as student_num,student.sname,然后通过where中的两个子查询,
查出当前的student.sid = score.student_id 的学生的score.course_id =‘1‘的课程分数(num) > 当前的student.sid = score.student_id 的学生的score.course_id =‘2‘的课程分数(num) 的学号和姓名

9、查询“生物”课程比“物理”课程成绩高的所有学生的学号;

自己的答案:

select * from (select * from (select student.sid as student_num,student.sname from student where 
((select score.num from score where score.course_id = (select cid from course where cname = 生物)

and student.sid = score.student_id) > (select score.num from score

where score.course_id = (select cid from course where cname = 物理) and student.sid = score.student_id)))
as stu inner join score as s on stu.student_num = s.student_id where s.course_id in (1,2)) as ss inner join course as cc on ss.course_id = cc.cid order by sname;

 

10、查询平均成绩大于60分的同学的学号和平均成绩;

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

 

11、查询所有同学的学号、姓名、选课数、总成绩;

自己的答案:

 select * from 
     (select stud.sid,stud.sname,ss.count_course from 
        (select stu.sid,stu.sname,count(course_id) as count_course,s.num from student 
        as stu left join score as s on stu.sid = s.student_id group by student_id) 
        as ss right join student as stud on ss.sid = stud.sid)
as a left join 
(select sum(num),student_id from score group by student_id) as b on a.sid = b.student_id;

 

标准答案:

 select sid,sname,
     (select sum(num) from score sc where sc.student_id = s1.sid) as sum_score,
     (select count(course_Id) from score s2 where s2.student_Id = s1.sid) as count_course
from student s1;

 

 

12、查询姓“李”的老师的个数;

select count(tid) from teacher where tname like 李%;

 

13、查询没学过“张磊老师”课的同学的学号、姓名;

select sid,sname from student where sid not in  (select student_id from score  where course_id in

(select cid from course where teacher_id in (select tid from teacher where tname = 张磊老师)));

 

14、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名;

select sid,sname from student where sid in (select student_id from score where course_id in(1,2) group by student_id having count(student_id) = 2);

 

15、查询学过“李平老师”所教的所有课的同学的学号、姓名;

select sid,sname from student 
    where sid in (select student_id from score where course_id in 
                    (select cid from course where teacher_id in 
                     
                        (select tid from teacher where tname = 李平老师)
                    )
                 );

 

 

既选体育又选物理的人名