常用sql语句练习（一）

        在平常的开发工作中，经常会用到sql语句、比如常见的列表数据查询、报表数据的统计，写好sql语句对于我们非常重要。今天，简单来总结下常用的sql语句。

        建表：

-- 学生表
CREATE TABLE s_students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50) NOT NULL,
    gender VARCHAR(10),
    age INT
);

-- 课程表
CREATE TABLE s_courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL,
    teacher VARCHAR(50),
    credit INT
);

-- 成绩表
CREATE TABLE s_grades (
    student_id INT,
    course_id INT,
    score INT,
    PRIMARY KEY (student_id, course_id)
);

        1：查询学生各科成绩都大于80的学生姓名：

select st.student_name
from 
s_grades sg left join s_students st on sg.student_id = st.student_id 
where sg.score > 80 
GROUP BY sg.student_id 
HAVING count(sg.score) = 2

        2：查询课程id为1的课程比课程id为2的成绩高的学生姓名：

select st.student_name
from s_students st 
left join s_grades sg1 on st.student_id = sg1.student_id and sg1.course_id = 1
left join s_grades sg2 on st.student_id = sg2.student_id and sg2.course_id = 2
where sg1.score > sg2.score

        3：查询语文比数学成绩高的学生姓名：

select st.student_name
from s_students st 
left join s_grades sg1 on st.student_id = sg1.student_id
left join s_courses sc1 on sg1.course_id = sc1.course_id
left join s_grades sg2 on st.student_id = sg2.student_id
left join s_courses sc2 on sg2.course_id = sc2.course_id
where sg1.score > sg2.score
and sc1.course_name = '语文'
and sc2.course_name = '数学'

或者

select st.student_name,sg1.course_id,sg1.score as '语文',sg2.course_id, sg2.score as '数学'
from s_students st 
left join s_grades sg1 on st.student_id = sg1.student_id
inner join s_courses sc1 on sg1.course_id = sc1.course_id  and sc1.course_name = "语文"
left join s_grades sg2 on st.student_id = sg2.student_id
inner join s_courses sc2 on sg2.course_id = sc2.course_id  and sc2.course_name = "数学"
where sg1.score > sg2.score

        4：查询平均成绩大于70分的学生姓名：

select st.student_name from 
s_students st 
left join s_grades sg on st.student_id = sg.student_id 
GROUP BY st.student_id 
having AVG(sg.score) > 70

        5：查询学生选课数量及总成绩并按总成绩排名：

select st.student_name,
count(sg.course_id),
sum(sg.score) from 
s_students st 
left join s_grades sg on 
st.student_id = sg.student_id 
GROUP BY st.student_id 
order by sum(sg.score) desc;

        6：查询姓李老师的数量：

select count(*)
from s_courses
where teacher like '李%'

        7：查询学生的各科成绩，如果成绩为空，则用0表示：

select 
ss.student_name,
IFNULL(sg.score,0) 
from s_students ss 
left join s_grades sg on ss.student_id = sg.student_id

        8：查询学生的每门成绩，并在一行数据中展示语文，数学成绩：

select 
ss.student_name,
sum(case when sg.course_id = 1 then sg.score end) as '语文',
sum(case when sg.course_id = 2 then sg.score end) as '数学'
from s_students ss 
left join s_grades sg on ss.student_id = sg.student_id
GROUP BY ss.student_name

        9：查询有一门及以上成绩小于60分的学生信息及平均成绩：

select ss.student_name, round(avg(sg.score)) 
from
s_students ss 
left join s_grades sg on ss.student_id = sg.student_id
where sg.score < 60 
GROUP BY ss.student_id 
HAVING count(sg.score) >= 1

        10：查询每个学生数学和语文成绩，大于80为优秀，否则为一般：

select 
ss.student_name,
case when (sum(case when sg.course_id = 1 then sg.score end)) > 80 then '优秀' else '一般' end as '语文',
case when (sum(case when sg.course_id = 2 then sg.score end)) > 80 then '优秀' else '一般' end as '数学'
from s_students ss 
left join s_grades sg on ss.student_id = sg.student_id
GROUP BY ss.student_name 

        以上为常用基本sql语句总结。加油，美好的风景一直在路上！