【MySQL】查询语句：条件、排序和分页

基本查询

MySQL 数据库使用SELECT语句来查询数据。

查询字段

• 以下为在MySQL数据库中查询数据通用的 SELECT 语法：

SELECT 字段名,字段名... FROM 表名;

• 选择全部列

SELECT * FROM emp;	-- 查询所有字段

一般情况下，除非需要使用表中所有的字段数据，最好不要使用通配符‘*’。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。

• 选择指定的列

SELECT empno,ename,job FROM emp;

列(字段)别名

在很多情况下为了方便查看结果或者简化字段名，会对查询的字段取别名。

• 语法：

SELECT 字段1 [AS 别名],字段2 [AS 别名]... FROM 表名;

• 举例一：
  通过直接在选择的列或表后面提供别名

SELECT empno 员工编号,ename 员工姓名,job 工作 FROM emp;

• 举例二：
  使用AS关键字为查询结果的列或表取别名

SELECT empno AS 员工编号,ename AS 员工姓名,job AS 工作 FROM emp;

• 举例三：
  还可以用单引号或双引号把别名包裹起来。

SELECT empno AS "员工编号",ename AS "员工姓名",job AS "工作" FROM emp;	-- 用双引号包裹别名

SELECT empno AS '员工编号',ename AS '员工姓名',job AS '工作' FROM emp;	-- 用单引号包裹别名

当别名中包含空格时，必须用单引号或双引号进行包裹！如下：

SELECT empno AS 员工 编号 FROM emp;

会直接报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '编号 FROM emp' at line 1

SELECT empno AS "员工 编号" FROM emp;

DISTINCT去重

从表中查询数据时，可能会收到重复的行记录。为了删除这些重复行，可以在SELECT语句中使用DISTINCT子句。

• 语法

SELECT DISTINCT 字段列表 FROM 表名;

单列中使用 DISTINCT

查询员工表(emp)中所有员工的岗位(job)有哪些。

SELECT job FROM emp;

执行SQL语句，得到如下结果：

可看到上面结果中，有些结果是重复的，比如：SALESMAN，MANAGER为了做到相同的结果只显示一个就要删除重复的记录，将DISTINCT子句添加到SELECT语句中即可

SELECT DISTINCT job FROM emp;

查询到的结果如下：

可以看到，当使用DISTINCT子句时，重复的job被消除了。

多列中使用 DISTINCT

• 举例二：查询员工表(emp)中，job对应的部门(deptno)，去掉重复记录

SELECT DISTINCT job,deptno FROM emp;

这里会发现job或者deptno中会有重复，但是这个并没有问题DISTINCT是删除重复的行，这里并没有哪一行是重复的。

注意DISTNCT关键字必须写在所有字段之前

空值参与运算

先来一个查询案例，查询员工年薪，即 (sal+comm)* 12。

SELECT ename,(sal + comm)*12 AS '年薪' FROM emp;

查询结果集如下：

发现有很多员工的年薪竟然是NULL，白干一年！这是为啥呢，emp表中每个员工都是有薪资的，只不过不是所有员工都有奖金，有的是NULL。当NULL值参与运算时，计算的结果一定是NULL，所以要对NULL进行处理！

SELECT ename,sal + IFNULL(comm,0)*12 AS '年薪' FROM emp;

IFNULL(expr, value_if_null)是一个处理NULL值的函数expr是要处理的表达式，而value_if_null是表达式为NULL时的值,这样就不会出现被公司白嫖一年的情况了。

注意：空值不等于任何值，甚至不等于NULL。
要想判断是不是NULL值，必须使用IS NULL。

SELECT NULL IS NULL;

查询常数

在使用MySQL进行数据查询时，经常需要加入一列常数来进行特定的计算或筛选。通过在SELECT语句中使用常数值和别名，可以方便地在查询结果中添加一列常数。

比如，我们想对emp表中的员工姓名进行查询，同时增加一列字段corporation，这个字段固定为“XX公司”，可以这样写：

SELECT 'XX公司' AS 'corporation',ename FROM emp;

条件查询

从 MySQL 表中使用 SELECT 语句来查询数据，如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。

语法

SELECT column1, column2, ...
FROM table_name
WHERE condition;

在条件中，可以使用以下基本的比较操作符进行比较也可以用下面的逻辑运算符连接多个条件

运算符表

关系运算符	功能
>	大于
>=	大于等于
<	小于
<=	小于等于
=	等于
<=>	安全等于
<> 或 !=	不等于

逻辑运算符	功能
AND 或 &&	并且（多个条件同时成立）
OR 或 ||	或者（多个条件任意成立一个）
NOT 或 ！	非，不是
XOR	逻辑异或 （一真一假才为真）

其他	功能
BETWEEN…AND…	在某个范围之间（含最小、最大值）
[NOT] IN(…)	在in之后的列表中的值，多选一
LIKE	模糊匹配（_匹配单个字符，%匹配任意个字符）
IS [NOT] NULL	是 NULL
REGEXP	正则表达式运算符
RLIKE	正则表达式运算符
LEAST	最小值运算符
GREATEST	最大值运算符

查询需求

1. 查询工资等于3000的员工

SELECT * FROM emp WHERE emp.sal<3000;

2. 查询没有奖金的员工

SELECT * FROM emp WHERE emp.comm IS NULL;

3. 查询工资在1200到1800之间的员工(包含1200和1800岁)

SELECT * FROM emp WHERE emp.sal BETWEEN 1200 AND 1800;

4. 查询职位为推销员，且工资小于1500的员工

SELECT * FROM emp WHERE emp.sal BETWEEN 1200 AND 1800 && emp.job='SALESMAN';

5. 查询姓名为四个字的员工

SELECT * FROM emp WHERE emp.ename LIKE '____';
#'____'是四个连续的下划线，注意下划线之间不要有空格

6. 查询姓名最后一位是S的员工

SELECT * FROM emp WHERE emp.ename LIKE '%S';

排序查询

通过条件查询语句可以查询到符合用户需求的数据，但是查询到的数据一般都是按照数据最初被添加到表中的顺序来显示。为了使查询结果的顺序满足用户的要求，在 MySQL 中，你可以使用 ORDER BY 子句对查询结果进行排序。ORDER BY 允许你按照一个或多个列的值对结果进行升序（ASC）或降序（DESC）排序。

语法

SELECT 字段名 FROM 表名 ... ORDER BY 排序字段名 [ASC|DESC],[排序字段名 [ASC|DESC]];

语法说明：

• 排序字段名：表示需要排序的字段名称，多个字段时用逗号隔开。
• ASC|DESC：ASC表示字段按升序排序；DESC表示字段按降序排序。其中ASC为默认值。

特点

• ORDER BY子句一般放到查询语句的最后面，LIMIT字句除外。

• 当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。在语句后加上NULLS LAST空值会最后出现。
• ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。
• 注意：在对多个字段进行排序时，排序的第一个字段必须有相同的值，才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的，MySQL 将不再对第二个字段进行排序。

查询需求

1. 根据姓名对员工进行排序(升序)

SELECT * FROM emp ORDER BY emp.ename ASC;

2. 查询入职时间大于1981年6月6日的员工信息，并按照入职时间升序排列

SELECT * FROM emp WHERE emp.hiredate>'1981-6-6' ORDER BY emp.hiredate ASC;

3. 根据工作职位，进行升序排列，职位相同的情况下，再根据入职时间降序排列

SELECT * FROM emp ORDER BY emp.job ASC , emp.hiredate DESC;

分页查询

对于比较多的数据，如果在一个页面全部显示，查看起来会眼花缭乱。

进行分页查询有两种写法，如下所示：

LIMIT

SELECT 字段名 FROM 表名 LIMIT 起始索引,查询记录数;

SELECT column1, column2, ...
FROM table_name
LIMIT 20 OFFSET 10;
#这将返回从第 21 条记录开始的下 10 条记录

LIMIT OFFSET

SELECT 字段名 FROM 表名 LIMIT 查询记录数 OFFSET 起始索引;

User
SELECT column1, column2, ...
FROM table_name
LIMIT 20 offset 10;
#这将返回从第11条记录开始的后续20条记录