增:
insert INTO student (s_id, s_name, s_birth, s_sex)
values
(09, 1, 1, 1)
删:
delete
FROM
student
WHERE
s_id = 09
改:
update student
SET s_name = "李海"
WHERE
s_id = 01
注意:
一、count(*) 和 count(1) 和 count(列名)区别
count(*):包括了所有的列,相当于行数,在统计结果的时候,不会忽略列值为NULL
count(1):包括了忽略所有列,用1代表代码行,在统计结果的时候,不会忽略列值为NULL
count(列名):只包括列名那一列,在统计结果的时候,会忽略列值为空(这里的空不是只空字符串或者0,而是表示null)的计数,即某个字段值为NULL时,不统计。
1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。(join联结自身两张表,条件:学生id相等,课程分数比较,课程号为01和02)
要点:联结
SELECT
a.s_id
FROM
Score a
JOIN Score b ON a.s_id = b.s_id
AND a.s_score > b.s_score
WHERE
a.c_id = '01'
AND b.c_id = '02'
2、查询平均成绩大于60分的学生的学号和平均成绩(group by归类学生id的having平均成绩大于60)
SELECT
s_id,
avg(s_score)
FROM
Score
GROUP BY
s_id
HAVING
avg(s_score) > 60
3、查询所有学生的学号、姓名、选课数、总成绩(联结成绩表和学生表,条件:学生id相等。group by归类学生id相等的)
要点:内联结
SELECT
student.s_id AS "学号",
student.s_name AS "姓名",
count(score.c_id) AS "选课数",
SUM(score.s_score) AS "总成绩"
FROM
score
JOIN student ON score.s_id = student.s_id
GROUP BY
score.s_id
4、查询姓“张”的老师的个数
SELECT
count(t_id)
FROM
teacher
WHERE
t_name LIKE "张%"
5、查询没学过“张三”老师课的学生的学号、姓名(重点)(三表联结)
(先查学过张三老师的课为子查询;再查询学生表的学生不在学过张三的课的表中)
SELECT
s_id,
s_name
FROM
Student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
Score
JOIN Course ON Score.c_id = Course.c_id
JOIN Teacher ON Course.t_id = Teacher.t_id
WHERE
t_name = '张三'
)
6、查询学过“张三”老师所教的所有课的同学的学号、姓名
SELECT
Student.s_id,
s_name
FROM
Student
JOIN Score ON Student.s_id = Score.s_id
JOIN Course ON Score.c_id = Course.c_id
JOIN Teacher ON Teacher.t_id = Course.t_id
WHERE
t_name = '张三'
7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名(同时满足两个条件,使用两个子查询)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id IN (
SELECT
s_id
FROM
score
WHERE
score.c_id = 01
)
AND s_id IN (
SELECT
s_id
FROM
score
WHERE
score.c_id = 02
)
8、查询课程编号为“02”的总成绩
要点:聚合
SELECT
SUM(s_score)
FROM
score
WHERE
c_id = 02
9、查询所有课程成绩小于60分的学生的学号、姓名(先查大于等于60分的学生id的表,然后再查学生表不not in这个表)
SELECT
s_id,
s_name
FROM
student
WHERE
s_id NOT IN (
SELECT
s_id
FROM
score
WHERE
score.s_score >= 60
)
18、查询各科成绩最高和最低的分: 以如下的形式显示:课程ID,最高分,最低分
(把课程id归类,比各课成绩最高分、最低分)
SELECT
c_id as "课程ID",
MAX(s_score) as "最高分",
MIN(s_score) as "最低分"
FROM
score
GROUP BY
c_id
19、按各科平均成绩从低到高和及格率的百分数从高到低排列,以如下形式显示:课程号,课程名,平均成绩,及格百分数
select a.c_id as '课程号',c_name as '课程名',avg(s_score) as '平均成绩',
concat((select count(b.s_score) from Score b where b.s_score>=60 and a.c_id=b.c_id)/
(select count(b.s_score) from Score b where a.c_id=b.c_id)*100,'%') as '及格百分数'
from Score a join Course c
on a.c_id=c.c_id
group by a.c_id,c_name
order by 平均成绩, 及格百分数 DESC;
21、查询不同老师所教不同课程平均分从高到低显示(联结老师和课程表,联结课程和成绩表,归类课程id,order by 平均分 desc)
SELECT
teacher.t_id,
teacher.t_name,
course.c_name,
avg(score.s_score)
FROM
teacher
JOIN course ON teacher.t_id = course.t_id
JOIN score ON course.c_id = score.c_id
GROUP BY
course.c_id
ORDER BY
AVG(score.s_score) DESC
23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称
select a.c_id AS '课程ID',c_name '课程名称',
sum(case when s_score between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when s_score >=70 and s_score<85 then 1 else 0 end) as '[85-70]',
sum(case when s_score>=60 and s_score<70 then 1 else 0 end) as '[70-60]',
sum(case when s_score<60 then 1 else 0 end) as '[<60]'
from Score a join Course b on a.c_id=b.c_id
group by a.c_id,c_name;
26、查询每门课程被选修的学生数
SELECT
c_id,
COUNT(s_id)
FROM
score
GROUP BY
c_id
27、查询出只选修了两门课程的全部学生的学号和姓名
SELECT
student.s_id,
student.s_name
FROM
student
JOIN score ON student.s_id = score.s_id
GROUP BY
student.s_id
HAVING
count(score.c_id) = 2
28、查询男生、女生人数
SELECT
s_sex,
COUNT(s_sex)
FROM
student
GROUP BY
s_sex
29、查询名字中含有“风”字的学生信息
SELECT
*
FROM
Student
WHERE
s_name LIKE '%风%'
30、查询同名同姓学生名单并统计同名人数
SELECT
s_name,
count(*)
FROM
Student
GROUP BY
s_name
HAVING
count(*) > 1
31、1990年出生的学生名单
SELECT
s_name
FROM
Student
WHERE
s_birth LIKE '1990%'
32、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT
student.s_id AS "学号",
student.s_name AS "姓名",
avg(score.s_score) AS "平均成绩"
FROM
student
JOIN score ON student.s_id = score.s_id
GROUP BY
student.s_id
HAVING
avg(score.s_score) > 85
34、查询课程名称为“数学”且分数低于60的学生姓名和分数
难度:***
select s_name as 学生姓名, s_score as 分数
from Student a join Score b on a.s_id=b.s_id
join Course c on c.c_id=b.c_id
where c_name='数学'
and s_score <60
35、查询所有学生的选课情况
SELECT
student.s_name,
course.c_name,
course.c_id
FROM
student
JOIN score ON student.s_id = score.s_id
JOIN course ON score.c_id = course.c_id
37、查询不及格的课程并按课程号从大到小排列
SELECT
c_id,
s_score
FROM
score
WHERE
s_score < 60
ORDER BY
c_id