select * from student where department="英语系" or department="计算机系";
从student表中查询年龄18~22岁的学生信息
select * from student where 2023-birth>18 or 2023-birth<22;
从student表中查询每个院系有多少人
select department,count(*) from student group by department ;
从score表中查询每个科目的最高分
select c_name,max(grade) from score group by c_name;
查询李四的考试科目(c_name)和考试成绩(grade)
select name,c_name,grade from score s inner join student stu on stu.id=s.stu_id where name="李四";
用连接的方式查询所有学生的信息和考试信息
select * from score s inner join student stu on stu.id=s.stu_id;
计算每个学生的总成绩
select name,sum(grade) from student inner join score on student.id=score.stu_id group by student.id;
计算每个考试科目的平均成绩
select c_name,avg(grade) from student inner join score on student.id=score.stu_id group by score.c_name;
查询计算机成绩低于95的学生信息
select * from student inner join score on student.id=score.stu_id where grade<95 and c_name="计算机";
查询同时参加计算机和英语考试的学生的信息
select sc.stu_id 学生id,group_concat(sc.c_name) 共同参与的学科,st.name 姓名 from score sc,student st where st.id=sc.stu_isc.stu_id group by sc.stu_id having group_concat(sc.c_name)='计算机,英语';
将计算机考试成绩按从高到低进行排序
select c_name,grade from student inner join score on score.stu_id=student.id where c_name="计算机" order by grade desc ;
从student表和score表中查询出学生的学号,然后合并查询结果
select * from student st,score sc where st.id=sc.stu_id;
查询姓张或者姓王的同学的姓名、院系和考试科目及成绩
select name,department,c_name,grade from student inner join score on student.id=score.stu_id where name like"张%"" or name like "王%";
查询都是湖南的学生的姓名、年龄、院系和考试科目及成绩
select address, name,birth,department,c_name,grade from student inner join score on student.id=score.stu_id where address like "湖南%";