group by 分组查询

select username from students
GROUP BY username

SELECT course from students
GROUP BY course

-- 统计姓名，对应的总成绩

select username, sum(score), COUNT(course), avg(score),max(score),min(score) FROM students
GROUP BY username

-- 统计人员姓名，对应的签到次数 
select name, count(name) from checkin
GROUP BY name;

having 条件过滤

-- 统计签到次数大于1的 人员姓名，对应的签到次数 
SELECT name, COUNT(*) from checkin
GROUP BY name
HAVING COUNT(*) > 1  -- 聚合函数条件 都放在 having

having 作为 group by 分组之后数据过滤条件， having 后面跟条件过滤聚合函数以及分组的字段

count()
max()
min()
avg()
sum()

查询students表中总成绩大于100 的人员姓名，总成绩

SELECT username, sum(score) from students
GROUP BY username
HAVING sum(score) > 100

查询students表中总成绩大于100 的人员姓名，总成绩并按照总成绩的从高到低进行排序

SELECT username, sum(score) from students
GROUP BY username
HAVING sum(score) > 100
ORDER BY sum(score)

统计语文成绩及格的同学，显示对应姓名，以及此同学的总成绩。

-- 语文及格的 人

select username from students
where course="语文" and score >= 60;


-- 显示 此人 的总成绩和名字  武大郎 林冲 嫦娥 大乔

SELECT username, sum(score) from students
GROUP BY username
HAVING username in ("武大郎","林冲","嫦娥","大乔")   -- HAVING 条件跟 分组的字段


-- 合并

SELECT username, sum(score) from students
GROUP BY username
HAVING username in (
            select username from students
            where course="语文" and score >= 60
);

select 语句的顺序

SELECT子句及其顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	仅在从表选择数据时使用
WHERE	行级过滤	否
GROUP BY	分组说明	仅在按组计算聚集时使用
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否

子查询

统计总销售额最高商品的购买人员

先找到总销售额最高商品 ```sql select products, sum(pay) from orders group by products order by sum(pay) desc limit 1;


2. 根据商品名 查询购买此商品
```sql
-- 销售额最高的商品
-- 以商品分组，分别求出每个商品的销售额
select products, sum(pay) from orders
GROUP BY products;

-- 根据销售额 求出最大的商品销售额

select max(支付总额) as 最大销售额 from (     
    select products, sum(pay) as 支付总额 from orders GROUP BY products
) as tmp1;


-- 根据最大销售额 查询对对应的商品

select products from orders
GROUP BY products
HAVING sum(pay) = (
    select max(支付总额) as 最大销售额 from (     
        select products, sum(pay) as 支付总额 from orders GROUP BY products
    ) as tmp1
)


-- 查找购买过 啤酒，小米的人

select DISTINCT(username) from orders
where products in (
    select products from orders
    GROUP BY products
    HAVING sum(pay) = (
        select max(支付总额) as 最大销售额 from (     
            select products, sum(pay) as 支付总额 from orders GROUP BY products
        ) as tmp1
    )
)

多表查询

查询每个人的工号，姓名，部门名称；

select emps.emp_id, emps.emp_name, depts.dept_name from emps,depts
WHERE emps.dept_id = depts.dept_id;  -- 两个表之间的关联关系

查询每个人的工号，姓名，部门名称, 并按照工号进行排序；

select emps.emp_id, emps.emp_name, depts.dept_name from emps,depts
WHERE emps.dept_id = depts.dept_id
ORDER BY emps.emp_id;

-- 如果两个表中 字段名不一样，可以不要加 表名
select emp_id, emp_name, dept_name from emps as e,depts as d
WHERE e.dept_id = d.dept_id   -- 表之间的关系
ORDER BY emp_id;

使用where 连接表的时候

select  *  from 表1, 表2, 表3
where 表1.链接字段= 表2.链接字段  and 表3.链接字段= 表2.链接字段  -- 确定表之间的关系
group by
having
order by
limit

inner join 内联查询

查询每个人的工号，姓名，部门名称；

SELECT emp_id,emp_name,dept_name FROM emps    -- 第一个表
INNER JOIN depts                                                         -- 第二个表
ON                                                                                        -- 第一个表与第二个表之间的关系
emps.dept_id = depts.dept_id
ORDER BY emp_id;

查询研发部门人员的工号，姓名，部门名称；

SELECT
emp_id,emp_name,dept_name
from emps
INNER JOIN depts
ON       
depts.dept_id = emps.dept_id    -- 查询所有的结果
WHERE dept_name="研发";         -- 添加过滤条件

使用where

SELECT emp_id,emp_name,dept_name FROM depts,emps
WHERE
emps.dept_id = depts.dept_id
AND
dept_name = "研发";

统计研发部门的人数

-- 统计研发部分的人数 count(dept_name)

SELECT dept_name, count(dept_name) FROM depts,emps
WHERE
emps.dept_id = depts.dept_id
AND
dept_name = "研发";

-- 查询emps表中 姓张的同学

select * from emps where emp_name like "张%";

— 查询emps表中部门id（去重）(dept_id)

select dept_id from emps group by dept_id;

select distinct(dept_id) from emps;

— 查询 emps 表中每个部门id（dept_id），以及对应人数

select dept_id, count(*) from emps group by dept_id;

作业

Mysql 数据库连接：
外网地址： rm-bp188nr95fk4l9545ao.mysql.rds.aliyuncs.com
外网端口： 3306    (默认端口)
用户名：     fanmao55
密码：        ABCfanmao55
数据库：    taofei55

checkin 表

查询张闯在 2021-07-02 的考勤记录
查询2021-07-01 考勤记录的总次数

查询每个同学的考勤次数，输出姓名，考勤次数

select * from checkin where checkintime like "2021-07-02%" and name = "张闯";
select count(checkintime) from checkin  where checkintime like "2021-07-01%";
select COUNT(checkintime),name from checkin GROUP BY name;

考勤记录中最多考勤次数是多少？（只需要统计次数即可，统计每个人的次数然后排序） ```sql select max(c) as 最大考勤次数 from ( SELECT name, count(name) as c from checkin GROUP BY name ) as tmp;

SELECT DISTINCT(count(*)) as c from checkin GROUP BY name ORDER BY c DESC LIMIT 1;


5. 考勤记录中第二多考勤次数是多少？（只需要统计次数即可）
```sql
SELECT DISTINCT(count(*)) as c from checkin
GROUP BY name
ORDER BY c DESC
LIMIT 1,1;

orders表

查询购买苹果的所有订单信息
查询订单记录中下订单总数超过 1 次的人员信息
查询销售额最多的商品名称 ```sql

select products from orders GROUP BY products having sum(pay) = ( SELECT max(销售额)as 最大销售额 from (
SELECT products ,sum(pay) as 销售额 from orders GROUP BY products ) as tmp

— 1. 销售额查询 select sum(pay) from orders GROUP BY products ORDER BY sum(pay) DESC limit 1

— 根据销售额找商品

select products from orders GROUP BY products HAVING SUM(pay) = ( select sum(pay) from orders GROUP BY products ORDER BY sum(pay) DESC limit 1 )


4. 统计订单表中的人员姓名 （去重）
```sql
-- 统计订单表中的人员姓名 （去重）
SELECT DISTINCT(username) from orders;

select username from orders
GROUP BY username;

所有订单记录中，消费最高的人员名字 ```sql select username, sum(pay) from orders GROUP BY username having sum(pay)=( SELECT sum(pay) FROM orders GROUP BY username ORDER BY sum(pay) desc limit 1 )


---

students表<br />![image.png](https://cdn.nlark.com/yuque/0/2021/png/87080/1625303479417-1c5aad15-22a8-453f-9ce7-b75a65d755fb.png#clientId=u7058294c-76e6-4&from=paste&height=307&id=u76abdde8&margin=%5Bobject%20Object%5D&name=image.png&originHeight=614&originWidth=648&originalType=binary&ratio=1&size=54401&status=done&style=none&taskId=u70224628-4643-41bc-921a-84e935f17c0&width=324)

1. 查询 age 为空的人员信息；
1. 查询 性别为男的人员姓名，（姓名有重复需要去重）
```sql
SELECT DISTINCT(username) FROM students WHERE sex="男";

查询语文分数最高的同学姓名，以及对应分数

SELECT username,course,score FROM students WHERE score=(
SELECT max(score) FROM students WHERE course="语文") and course="语文";

查询男生中总分最高的同学

select username, SUM(score) from students
WHERE sex = "男"
GROUP BY username
HAVING sum(score) = (     -- 男生同学的最高分数
 SELECT SUM(score) FROM students 
 WHERE sex="男" 
 GROUP BY username
 ORDER BY SUM(score) desc LIMIT 1)

查询每个同学的总分，并按从大到小排序

SELECT username, SUM(score) FROM students GROUP BY username ORDER BY SUM(score) DESC

emps表

deps

查询姓名，工号，部门名称 ```sql select emps.emp_name, emps.emp_id,depts.dept_name from emps INNER JOIN depts on emps.dept_id=depts.dept_id;


2. 查询 客服 部门的人员 并按照工号 降序排序
```sql
select emps.emp_name, emps.emp_id,depts.dept_name from emps
INNER JOIN depts 
on 
    emps.dept_id=depts.dept_id
and
    dept_name = "客服"
ORDER BY emp_id desc

统计部门名称，部门人数

select count(emps.emp_name),depts.dept_name from 
emps,depts 
where 
 emps.dept_id = depts.dept_id 
GROUP BY depts.dept_name;

查询人数最多的部门，输出部门名称，部门人数 ```sql — 1. 人数最多

select DISTINCT(COUNT()) from emps GROUP BY dept_id ORDER BY COUNT() desc LIMIT 1

— 2. 查找部门人数为3

select dept_name, COUNT(emp_name) from emps INNER JOIN depts ON depts.dept_id = emps.dept_id GROUP BY dept_name HAVING COUNT(emp_name) = ( select DISTINCT(COUNT()) from emps GROUP BY dept_id ORDER BY COUNT() desc LIMIT 1 ) ```

Linux，Mysql，接口测试

Mysql-03

group by 分组查询

having 条件过滤

select 语句的顺序

子查询

多表查询

inner join 内联查询

作业