MySQL基础（二）排序与分页、多表查询、单行函数

最新推荐文章于 2023-03-18 16:51:55 发布

独憩

最新推荐文章于 2023-03-18 16:51:55 发布

阅读量1.1k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/qq_54517101/article/details/129294865

版权

MySQL 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

上接

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

排序与分页

排序

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

使用 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