在平常的开发工作中,经常会用到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语句总结。加油,美好的风景一直在路上!