MySQL（2）查询数据

1.基本格式

SELECT
    [all | DISTINCT]
    <目标列1> [别名],
    <目标列2> [别名],
FROM <表1> [别名],<表2> [别名]
[WHERE <条件表达式>]
[GROUP BY <列名>]
[HAVING <条件表达式>]
[ORDER BY <列名>] [ASC | DESC]
[LIMIT <数字或者列表>];

having是分组之后过滤，where是分组之前

2.集合函数

2.1.行数

COUNT(*)        计算表中总的行数，不管某列是否有数值或者为空值。

COUNT(字段名)        计算指定列下总的行数，计算时将忽略空值的行。

2.2.和

SUM()

2.3.平均值

AVG()

2.4.最大值

MAX()

2.5.最小值

MIN()

3.连接查询

3.1.内连接

（1）概念

返回两个表中具有匹配值的记录

（2）案例

初始化两张表：

水果表：

水果供应商：

 内连接查询：

SELECT s.s_id,s_name,f_name,f_price
FROM fruits f
INNER JOIN suppliers s
ON f.s_id = s.s_id;

3.2.自连接

（1）概念

在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但可以在逻辑上分为两张表。

（2）案例

查询f_id= 'a1'的水果供应商提供的水果种类

SELECT f1.f_id,f1.f_name
FROM fruits f1,
fruits f2
WHERE f1.s_id = f2.s_id AND f2.f_id = 'a1';

3.3.外连接

3.3.1.左连接

（1）概念

返回包括左表中的所有记录和右表中连接字段相等的记录。

如果左表的某行在右表中没有匹配行，则在相关联的结果行中，右表的所有选择列表列均为空值。

 （2）案例

初始化两张表：

用户表customers：

订单表orders：

 

查询所有客户及其订单，包括没有订单的客户：

SELECT C.c_id,o_num
FROM customers C
LEFT JOIN orders O
ON C.c_id = O.c_id;

查询结果：

 

3.3.2.右连接

（1）概念

返回包括右表中的所有记录和左表中连接字段相等的记录。如果右表的某行在左表中没有匹配行，左表将返回空值。

 

（2）案例

查询所有订单，包括没有客户的订单

SELECT C.c_id,o_num
FROM customers C
RIGHT JOIN orders O
ON C.c_id = O.c_id;

查询结果：

 

4.合并查询结果

SELECT column,... FROM table1 　　

UNION [ALL] 　　

SELECT column,... FROM table2

UNION从查询结果集中自动去除了重复的行，如果要返回所有匹配行，而不进行删除，可以使用UNION ALL。

5.案例

5.1.第二高的薪水

获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null

 

SELECT IFNULL((SELECT DISTINCT salary FROM Employee ORDER BY salary DESC LIMIT 1,1),NULL) AS SecondHighestSalary;

5.2.上升的温度

查找与之前（昨天的）日期相比温度更高的所有日期的 id

select a.id
from Weather as a
cross join Weather as b
on datediff(a.recordDate,b.recordDate)=1
where a.Temperature>b.Temperature;

5.3.每个部门工资前三的所有员工

select
Department,Employee,Salary
from
(select
Department.name as Department,
Employee.name as Employee,
salary as Salary,
dense_rank() over(partition by departmentId order by salary desc) as num
from
Employee
join
Department
on 
Employee.departmentId=Department.id) f
where f.num <= 3;

5.4.树节点

 

select
id,'Root' as type
from 
tree
where
p_id is null
union
select
id,'Leaf' as type
from
tree
where
id not in (select p_id from tree where p_id is not null)
and p_id is not null
union
select
id,'Inner' as type
from
tree    
where
id in (select p_id from tree where p_id is not null)
and p_id is not null
order by id;