distinct 去除重复数据

-- 统计students表中的学生姓名，姓名不能有重复名字
-- 去重 distinct
SELECT DISTINCT(username) from students;
-- 去重 age
select distinct(age) from students;

需要注意的是：去重的时候 select 后面只能跟1个字段。
下面的代码不支持


select distinct(username),DISTINCT(age) from students;

distinct 去重也支持where条件查询

-- 语文成绩去重后显示出来
select DISTINCT(score) from students
WHERE course="语文";

order by

进行排序

-- 查询所有的学生信息， 按照年龄从小到大进行排序
select * from students
ORDER BY age

desc 表示倒序排序

select * from students
ORDER BY age DESC

-- 男生同学 语文成绩 从大到小进行排序。 需要使用where 条件， 注意where条件
-- 放在 order by 之前。
SELECT * from students
WHERE sex="男" and course="语文"   -- 先把符合条件的数据过滤出来
ORDER BY score desc;              -- 再进行排序

按照多个字段进行排序

-- 排序的时候可以按照多个字段排序
-- 1. 先按年龄，如果年龄相同，再按成绩排序,成绩相同，按id
SELECT * from students
order by age desc, score desc,id desc

limit 限制

默认前3个

-- 过滤排序之后的时候，显示前三个
select * from students
ORDER BY score desc
LIMIT 3;

limit 1，3

select * from students
LIMIT 2,3  -- 2.从2+1=3 第三行开始。 3表示显示3行数据

-- 体育成绩大-小排序。 显示排在前三位的同学信息；
SELECT * from students
WHERE course = "体育"
ORDER BY score DESC
LIMIT 3;
-- 年龄大-小排序。 显示排在第3-5的同学信息；
select * from students
ORDER BY age desc
LIMIT 2,3  -- 2.从2+1=3 第三行开始。 3表示显示3行数据

练习

1. 语文最高分是多少？
2. 体育最低分是多少？
3. 数学成绩第二高是多少？

select score from students
where course = "语文"
ORDER BY score DESC
LIMIT 1;
select score from students
where course = "体育" AND score is not Null   -- 过滤掉Null值
ORDER BY score
LIMIT 1;
SELECT DISTINCT(score) from students
WHERE course="数学"
ORDER BY score desc
limit 1,1

思考

查找语文分数最高的人？

-- 找到最高的语文分数
select score from students
where course = "语文"
ORDER BY score DESC
LIMIT 1;
-- 我已经知道最高分为100， 查询分数为100的人员
select * from students
WHERE course="语文" and score=100
-- 将两条sql 语句结合在一起
select * from students
WHERE course="语文" 
and score=(   -- 先运行 () 中的语句
    select score from students
        where course = "语文"
        ORDER BY score DESC
        LIMIT 1
);

数学成绩第二高的人员

select * from students
WHERE course="数学" 
and score=(   -- 先运行 () 中的语句
    select DISTINCT(score) from students
        where course = "数学"
        ORDER BY score DESC
        LIMIT 1,1
);

聚合函数

max() 最大值

-- 语文最大分数
select max(score) from students where course = "语文";

-- 男生中第二最大年龄 
-- 年龄在小于 最大年龄中的最大年龄
select max(age) from students 
where sex="男" and age < (select max(age) from students WHERE sex="男");
select max(age) from students 
where sex="男" and age < 30;

min() 最小值

-- 年龄最小的女生的年龄
select min(age) from students where sex="女";
-- 年龄第二小的女生年龄
select min(age) from students WHERE sex = "女" and
age > (select min(age) from students where sex="女");
-- 年龄最小的女生姓名
select DISTINCT(username) from students WHERE 
        sex="女" 
    and 
        age is not null
    and
        age = (
        select min(age) from students where sex="女"
);

-- 血量hp_max最高的人员：姓名，血量
-- 1. 最大的血量
select max(hp_max) from heros;
-- 2. 根据最大血量找人
select name,hp_max from heros WHERE hp_max = 9328;
-- 3. 两个sql语句结合一起
select name,hp_max from heros 
where hp_max = (select max(hp_max) from heros);

count() 计数

-- 统计名字4个字的英雄个数
select COUNT(*) from heros WHERE name LIKE "____";
-- 统计hp_max血量 大于6000 的英雄个数
select COUNT(*) from heros  WHERE hp_max > 6000;
-- 统计students表中不重名的名字个数
SELECT count(DISTINCT(username)) from students;

sum() 总和


-- hp_max 血量总和
SELECT sum(hp_max) from heros;
-- 学生表中 语文成绩总和
SELECT sum(score) from students
WHERE course="语文";

avg() 平均值

-- 所有英雄的血量平均值
select avg(hp_max) from heros;

-- 所有英雄的血量平均值
select avg(hp_max) from heros;
-- 血量大于平均值的英雄信息
select * from heros
WHERE hp_max > (select avg(hp_max) from heros);
-- 血量大于平均值的英雄信息 并且按照血量的大-小 排序 
select * from heros
WHERE hp_max > (select avg(hp_max) from heros)
ORDER BY hp_max DESC;

group by 分组

SELECT DISTINCT(role_main) FROM heros;
SELECT role_main FROM heros
GROUP BY role_main;  -- GROUP BY 后面跟上对应的字段，可以将该字段中的有重复数据自动去重。

-- 计算 students表中每个科目的分数总和
-- 1. 要按照 course 进行分组
-- 2. 分数总和 sum(score)
select course,sum(score) from students
WHERE course is not null  -- 先去掉空数据
GROUP BY course;          -- 再进行分组

-- 按照攻击类型进行分组，分别计算 总和，平均值，数量
select attack_range, sum(hp_max), avg(hp_max),COUNT(name) from heros
GROUP BY attack_range;

having 条件过滤

在group by 分组之后，如果需要进行相关数据的条件过滤，使用 having 条件过滤；
having后跟的条件主要是聚合函数中统计数据，
比如 sum，min，max

-- 英雄表中 按照 role_main 进行分组，统计出 hp_max 总和， 要求 hp_max的总和大于 30000的相关信息
SELECT role_main,sum(hp_max) from heros
GROUP BY role_main -- 如果是涉及到统计数据的过滤，使用 group by ... HAVING 进行过滤
HAVING sum(hp_max) > 30000;

执行顺序

select * from table
where  条件 
group by ... 分组
having...   针对分组的统计数据过滤
order by.  排序
limit ..  限制

练习

-- students 表中 课程进行分组，算出每门学课的成绩总和 以及 平均值
-- course 分组， sum(score), avg(score)
select course,sum(score),avg(score) from students
WHERE score is not null
group by course
-- students 表中 针对每个学生进行分组，计算该学生的 成绩总和，平均值
select username,sum(score),avg(score) from students
WHERE score is not null
group by username

作业

checkin 表

2021-7月份考勤的总数
2021-7月份每个人的考勤次数（分组）
总共考勤中每个人的考勤次数并按照考勤次数的多少从大到小排序
2021-7月份考勤的人员最多的人的姓名


 -- 2021-7月份考勤的总数 
 SELECT * FROM checkin;
 select count(*) from checkin WHERE checkin_time LIKE "2021-07%"
 --  2021-7月份每个人的考勤次数 （分组）
 select name, COUNT(name) from checkin
 WHERE checkin_time LIKE "2021-07%"
 GROUP BY name
 -- 总共考勤中每个人的考勤次数 并按照考勤次数的多少 从大到小排序
  select name, COUNT(*) from checkin
    GROUP BY name
    ORDER BY count(name) DESC
 -- 2021-7月份考勤的人员最多的人的姓名
 select name, count(*) from checkin
    WHERE checkin_time LIKE "2021-07%"
    GROUP BY name
    HAVING count(*) = (
         select COUNT(checkin_time) from checkin
         WHERE checkin_time LIKE "2021-07%"
         GROUP BY name
         ORDER BY count(checkin_time) desc
         LIMIT 1
    )

students 表

— 1、查询出所有学生的姓名

— 2、查询出所有学生的姓名和性别

— 3、查询出学生表全部信息

— 4、查询出成绩大于80分的学生编号，成绩

— 5、查询出成绩等于80分的学生编号，成绩

— 6、查询出成绩不超过80分的学生编号，成绩

— 7、查询出成绩不低于80分的学生编号，成绩

— 8、查询出成绩低于80分的学生编号，成绩

— 9、查询出成绩在60到80分之间的学生编号，成绩

— 10、查询出姓王的学生

— 11、查询出姓名中包含兰字的学生

— 12、查询出姓名中第二个字为风字的学生

— 13、查询出不姓王的女学生

— 14、查询出成绩不在60到80分之间的学生

— 15、查询出成绩为80分或者60分的学生编号并对学生编号去重

— 16、查询出成绩不为60分和80分的学生，并对学生编号去重

— 17、查询出学生编号01的学生，成绩高于80分的课程编号

— 18、查询出所有学生的成绩并按照成绩从高到低的顺序排序

— 20、查询出所有学生的成绩，取成绩最高的三条记录

— 21、查询出所有学生的成绩，取3到5条记录

— 22、统计出所有学生的总成绩

— 23、统计出所有学生的平均成绩

— 24、统计出所有学生的最高成绩

— 25、统计出所有学生的最低成绩

— 26、统计出成绩表中共有多少条记录

— 27、统计每个学生的总成绩

— 28、统计每个学生的平均成绩

— 29、统计每个学生的最高成绩

— 30、统计每个学生的最低成绩

— 31、统计每门课及格的学生数


 SELECT course ,count(score) from students
 WHERE score >=60
 GROUP BY course

— 32、统计每门课都及格的学生姓名

SELECT username from students
WHERE course="体育" AND score >= 60 and username in (
    SELECT username from students
    WHERE course="语文" AND score >= 60 and username in (
        SELECT username from students
        WHERE course="数学" AND score >= 60) 
)

男生的人数

SELECT COUNT(DISTINCT(username)) from students
WHERE sex = "男"

一条sql语句统计出男生，女生的人数

SELECT sex, COUNT(DISTINCT(username)) FROM
students
WHERE sex is not null
GROUP BY sex

语文成绩按从大到小进行排序

SELECT * from students
WHERE course="语文"
ORDER BY score DESC

体育成绩最大值

select max(score) FROM students
WHERE course="体育"

平均成绩大于60分的学生姓名 ```sql

select username as “姓名”, sum(score)/3 as “平均成绩” from students GROUP BY username

— 2. 平均成绩大于60

select username as 姓名, sum(score)/3 as 平均成绩 from students GROUP BY username HAVING 平均成绩 > 60


6. 统计学生的平均年龄
```sql
 select avg(age) from (SELECT DISTINCT(username),age from students
                                            WHERE age is not null) as tmpdata;

大于平均年龄的学生姓名


 SELECT DISTINCT(username), age from students
     WHERE age > (
         select avg(age) from (SELECT DISTINCT(username),age from students
                                         WHERE age is not null) as tmpdata
     )

总成绩最高的学生 ```sql — 总成绩最高的学生 SELECT * from students; — 1. 总成绩 sum(score) SELECT username,sum(score) from students GROUP BY username;

— 2，最高的总成绩

— a. 使用order by

SELECT sum(score) from students GROUP BY username ORDER BY sum(score) DESC LIMIT 1;

— b 使用max

SELECT sum(score) from students GROUP BY username — 使用子查询

select max(总分) as 最高分 from (SELECT sum(score) as 总分 from students GROUP BY username) as tmp — 将查询结果作为临时表 as后跟临时表的表名

— 3。根据最高总成绩招人

select username FROM students — 对应的人 GROUP BY username HAVING sum(score) = — 总分=最高分 ( SELECT sum(score) from students GROUP BY username ORDER BY sum(score) DESC LIMIT 1 ) — 最高分

— select username FROM students GROUP BY username HAVING sum(score) = ( select max(总分) as 最高分 from (SELECT sum(score) as 总分 from students GROUP BY username) as tmp )


9. 语文和数学都高于70分的学生
```sql
 -- 如果都大于70 那么这个名字应该出现2次 COUNT(expr)
 SELECT username from students 
 WHERE course in ("语文","数学") and score > 70
 GROUP BY username
 HAVING count(username) = 2
 -- 先查询语文大于70的人
 select username from students
 WHERE course="语文" AND score > 70
 select username from students
 WHERE course="数学" AND score > 70 and username in ( select username from students WHERE course="语文" AND score > 70)

Linux，Mysql，接口测试

Mysql-02