MySQL基础（二）排序与分页、多表查询、单行函数_分组后分页还需要排序吗

上接 

MySQL基础（一）SQL分类、导入、SELECT语句，运算符_独憩的博客-CSDN博客

目录

排序与分页 

排序

二级排序

分页

多表查询

基础多表查询

等值连接vs非等值连接

自连接vs非自连接

内连接vs外连接

自然连接

单行函数

 数值函数

 字符串函数

 日期和时间函数

获取日期、时间 

日期与时间戳的转换

 获取月份、星期、星期数、天数等函数

日期的操作函数

 时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

 流程控制函数

 加密与解密函数

MySQL信息函数

 其他函数

---

排序与分页 

排序

如果不加任何排序操作，那显示数据的顺序就是数据添加的顺序

使用 ORDER BY 子句排序
        ASC（ascend）: 升序
        DESC（descend）:降序
ORDER BY 子句在SELECT语句的结尾。

SELECT * 
FROM employees
ORDER BY salary ASC

也可以根据 列的别名 来排序

SELECT salary*12 salary_all
FROM employees
ORDER BY salary_all ASC

• 值得注意的是，列的别名 只能在order by中使用，不能在 where 使用 ，在一起使用时，先使用where再使用order by
• 因为这里的操作顺序是  FROM--WHERE--SELECT--ORDER BY，这也解释了WHERE不能用别名的原因

SELECT salary*12 salary_all
FROM employees
WHERE salary>8000
ORDER BY salary_all ASC

二级排序

在排序时，会遇到数据相同的情况，那么这些数据就可以通过二级排序再次进行排序

SELECT employee_id,salary,department_id
FROM employees
ORDER BY department_id DESC,salary ASC

先根据department_id 降序排列，再根据salary升序排列

分页

• 所谓分页显示，就是将数据库中的结果集，一段一段显示出来需要的条件。
• MySQL中使用 LIMIT 实现分页

 显示前20条记录，代表从 第0个数据开始，显示20条数据

SELECT employee_id,salary,department_id
FROM employees
LIMIT 0,20

同理，显示第二页，即20-40数据

SELECT employee_id,salary,department_id
FROM employees
LIMIT 20,20

同时使用where，order by ，limit的顺序为

SELECT employee_id,salary,department_id
FROM employees
WHERE salary>8000
ORDER BY salary ASC
LIMIT 20,20

多表查询

基础多表查询

• 多表查询，也称为关联查询，指两个或更多个表一起完成查询操作。
• 前提条件：这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。比如：员工表和部门表，这两个表依靠“部门编号”进行关联

 这里提供一个例子说明什么是多表查询

这里有三个表，employees表通过department_id与departments表 联系，departments表通过location_id与location表联系，这就是一个典型的多表结构

那为什么不直接用一个表写所有的信息呢？

举一个例子：departments表中有很多员工的department_id是1，在department表中，部门1的信息一行就能描述完全，但如果合成一个表，那所有部门是1的员工后面都要加上department表中部门1的信息，这会造成 冗余

在这里，例如通过一个员工的employee_id查询其city，就是一个典型的多表查询

例如我想找到employee_id =110这个员工的department_name，很自然的想到：

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.employee_id =110

 

会出现错误，结果显示这个人在每个部门都工作过，这是因为这里出现了笛卡尔积的错误 ，即将employee_id=110这个人与每一个department信息连接，原因是没有建立两个表直之间的连接

正确方式，先建立连接employees.department_id = departments.department_id 再提供需求

employees.employee_id =110 

SELECT employee_id,department_name
FROM employees,departments
WHERE employees.department_id = departments.department_id 
	AND employees.employee_id =110 

注意到，两个表中都有department_id这列，当我想要查询它时，必须指明来源：

SELECT employee_id,department_name,employees.department_id
FROM employees,departments
WHERE employees.department_id = departments.department_id 
	AND employees.employee_id =110 

为了简化代码，可以给表起别名：

SELECT employee_id,department_name,e.department_id
FROM employees e,departments d
WHERE e.department_id = d.department_id 
	AND e.employee_id =110 

同理，三个表的时候，只需要再加一个连接条件就可以了

即如果n个表实现多表查询，则至少需要n-1个连接条件

SELECT employee_id,department_name,e.department_id,city
FROM employees e,departments d,locations l
WHERE e.department_id = d.department_id 
	AND d.location_id = l.location_id
	AND e.employee_id =110 

等值连接vs非等值连接

前面说的都是等值连接

案例：

假设 job_grades表 中的存放着一些薪水分类标准

现在想根据这个标准，查询employee表中的数据：

SELECT e.employee_id,salary,j.grade_level
FROM employees e,job_grades j
WHERE e.salary BETWEEN j.lowest_sal AND j.highest_sal;

 这就是一种 非等值连接

自连接vs非自连接

自连接的意思是，在一个表中，会自己引用自己

例如下面中这个表，每个 employee  都有一个 manager_id，这个 manager_id 本身也是employee_id

需求是，查询每个人的id，名字及其manager的id，名字 ：

SELECT e1.employee_id,e1.last_name,e2.employee_id 'manager id',e2.last_name 'manager name'
FROM employees e1,employees e2
WHERE e1.manager_id = e2.employee_id

 

这种连接就叫自连接，显然之前的都是非自连接

内连接vs外连接

• 内连接: 合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行
• 外连接: 两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足条件的行 ，这种连接称为左（或右） 外连接。没有匹配的行时, 结果表中相应的列为空(NULL)。
• 如果是左外连接，则连接条件中左边的表也称为主表 ，右边的表称为从表。
• 如果是右外连接，则连接条件中右边的表也称为主表 ，左边的表称为从表。

 对于基础多表查询中的那个例子中，连接条件是e.department_id = d.department_id ，但是由于e表中并不是每个人都有department_id，所以在联合查询中会省略这种数据，这就是内连接

这里引入SQL99语法，使用 JOIN 表名 ON  连接条件 ，可以改写为：

SELECT employee_id,department_name,e.department_id,l.city
FROM employees e JOIN departments d
ON e.department_id = d.department_id 
JOIN locations l
ON d.location_id = l.location_id

 

左外连接，右外连接：

SELECT employee_id,department_name,e.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id 

e中某些没有 department_id，用 左外连接  LEFT JOIN 可以使这部分也显示

同理 d中某些没有department_id，用 右外连接  RIGHT JOIN 可以使这部分也显示

对应了上图中的左上图与右上图

左中图与右中图：

SELECT employee_id,department_name,e.department_id
FROM employees e left join departments d
on e.department_id = d.department_id 
where d.department_id is null

d.department_id is null 就将中间部分去除了 

满外连接（左下图）

FULL OUTER JOIN在mysql中不能用，故我们这里考虑采用左上图与右中图联合

先介绍UNION 操作符和UNION ALL操作符

• UNION 操作符返回两个查询的结果集的并集，去除重复记录。
• UNION ALL操作符返回两个查询的结果集的并集。对于两个结果集的重复部分，不去重。
• 注意：执行UNION ALL语句时所需要的资源比UNION语句少。如果明确知道合并数据后的结果数据
  不存在重复数据，或者不需要去除重复的数据，则尽量使用UNION ALL语句，以提高数据查询的效率。

只需要在两者之间加上UNION操作符就可以：

SELECT employee_id,department_name,e.department_id
FROM employees e LEFT JOIN departments d
ON e.department_id = d.department_id 
UNION ALL
SELECT employee_id,department_name,e.department_id
FROM employees e RIGHT JOIN departments d
ON e.department_id = d.department_id 
WHERE e.department_id IS NULL

自然连接

SQL99 在 SQL92 的基础上提供了一些特殊语法，比如 NATURAL JOIN 用来表示自然连接。我们可以把自然连接理解为 SQL92 中的等值连接。它会帮你自动查询两张连接表中所有相同的字段 ，然后进行等值连接 

SELECT employee_id,department_name,e.department_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id 
AND e.manager_id = d.manager_id

等价于

SELECT employee_id,department_name,e.department_id
FROM employees e NATURAL JOIN departments d

单行函数

MySQL 函数 | 菜鸟教程

单行函数

• 操作数据对象
• 接受参数返回一个结果
• 只对一行进行变换
• 每行返回一个结果
• 可以嵌套
• 参数可以是一列或一个值

 数值函数

基础函数

 三角函数

 指数和对数

 字符串函数

 

 日期和时间函数

获取日期、时间 

SELECT CURDATE(),CURTIME(),NOW(),SYSDATE()+0,UTC_DATE(),UTC_DATE()+0,UTC_TIME(),UTC_TIME()+0
FROM DUAL;

  日期与时间戳的转换

 时间戳是将时间转换为一串数字进行存储

SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2000-9-24'),FROM_UNIXTIME(UNIX_TIMESTAMP())
FROM DUAL;

 获取月份、星期、星期数、天数等函数

日期的操作函数

 

SELECT EXTRACT(MINUTE FROM NOW()),EXTRACT( WEEK FROM NOW()),
EXTRACT( QUARTER FROM NOW()),EXTRACT( MINUTE_SECOND FROM NOW())
FROM DUAL;

 时间和秒钟转换的函数

计算日期和时间的函数

日期的格式化与解析

 FMT可以取以下：

SELECT DATE_FORMAT(NOW(),'%y-%M--%D-%e')
FROM DUAL;

 GET_FORMATE可以得到不同地区的FMT

SELECT get_format(DATE,'USA')
FROM DUAL;

 流程控制函数

 IF(value,value1,value2)，这个类似与三元运算符

SELECT last_name,salary,IF(salary>=6000,'high','low')
FROM employees

 CASE WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2
.... [ELSE resultn] END

SELECT last_name,salary,case when salary>=15000 then '高薪'
				when salary >=1000 then '中薪'
				when salary >=8000 then '低薪'
				else '草根' END 
FROM employees

 加密与解密函数

加密函数是不可逆的

SELECT MD5('123123213'),SHA('sada')

MySQL信息函数

 其他函数