MySQL数据库查询语句

数据库中一些复杂的查询语句

以下为四张表，操作的数据皆为四张表中的内容：
student表

score表

course表

teacher表

以下为题目以及答案：

-- 1、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select s1.s_id,s1.s_name,avg(s_score) avg
from student s1,score s2
where s1.s_id = s2.s_id
GROUP BY s1.s_id
having avg > 60

-- 2、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select s1.s_id,s1.s_name,COUNT(*) con ,sum(s2.s_score) sum
from student s1,score s2,course c
where s1.s_id = s2.s_id and s2.c_id = c.c_id
GROUP BY s1.s_id

-- 3、查询"李"姓老师的数量 
select count(*) count
from teacher
where t_name LIKE '李%'

-- 4、检索"01"课程分数小于60，按分数降序排列的学生信息
select s1.* ,s2.s_score
from student s1,score s2,course c
where s1.s_id = s2.s_id and s2.c_id = c.c_id and c.c_id = 1 and s2.s_score < 60
ORDER BY s2.s_score DESC

-- 5、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
SELECT s1.s_id,s1.s_name,s3.s_score,s2.avg
from student s1,(SELECT s.s_id,avg(s.s_score) avg
from score s,course c
where s.c_id = c.c_id
GROUP BY s.s_id) s2,score s3
where s1.s_id = s3.s_id and s3.s_id = s2.s_id
ORDER BY s2.avg desc

-- 6、查询学生的总成绩并进行排名
select s1.s_name ,SUM(s2.s_score) "总成绩"
from student s1,score s2,course c
where s1.s_id = s2.s_id and s2.c_id = c.c_id
GROUP BY s1.s_id
ORDER BY 总成绩 desc

-- 7、查询男生、女生人数
SELECT s_sex,count(*) "人数"
from student
GROUP BY s_sex
-- 8、查询名字中含有"风"字的学生信息
select *
from student
where s_name like '%风%'

-- 9、查询1990年出生的学生名单
select *
from student
where s_birth like '1990%'

-- 10、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select s1.s_id,s1.s_name,avg(s_score) avg
from student s1,score s2
where s1.s_id = s2.s_id
GROUP BY s1.s_id
having avg > 85

-- 11、查询课程名称为"数学"，且分数低于60的学生姓名和分数 
select s1.s_name , s2.s_score
from student s1,score s2 ,course c
where s1.s_id = s2.s_id and s2.c_id = c.c_id and c.c_name = '数学' and s2.s_score < 60
-- 12、查询各学生的年龄

SELECT s_name, ((unix_timestamp(now())- unix_timestamp(s_birth))/3600/24/365) "年龄"
from student