MySQL 查询、聚合函数的使用

查询产生一个虚拟表，看到的是表形式显示的结果，但结果并不真正存储，每次执行查询只是现从数据表中提取数据，并按照表的形式显示出来。

1 SQL查询语句

1.1 select子句

使用以下几种方式指定字段列表：

字段列表	说明
*	字段列表为数据源的全部字段
表名*	多表查询时，指定某个表的全部字段
字段列表	指定所需要显示的列

• 可以为字段列表中的字段名或表达式指定别名，中间使用as关键字分隔即可（as关键字可以省略）。
• 多表查询时，同名字段前必须添加表名前缀，中间使用“.”分隔。

1.1 where子句

数据库中存储着海量数据，数据库用户往往需要的是满足特定条件的记录，where子句可以实现结果集的过滤筛选。

where 条件表达式

（1）条件表达式

关键字	解释
比较运算符	单一条件过滤
逻辑运算符	组合多个条件查询
BETWEEN AND	在两数之间
NOT BETWEEN AND	不在两数之间
IN <值表>	是否在特定的集合里（枚举）
NOT IN <值表>	与上面相反
LIKE	是否匹配于一个模式
IS NULL	为空的
IS NOT NULL	不为空的
Binary	区分大小写

比较运算符

• 常用的比较运算符有=（等于）、>（大于）、>=（大于等于）、<（小于）、<=（小于等于）、<>（不等于）、!=（不等于）、!<（不小于）、!>（不大于）。

表达式1  比较运算符 表达式2

• 说明：“表达式1”和“表达式2”可以是一个字段名、常量、变量、函数甚至是子查询。比较运算符用于比较两个表达式的值，比较的结果是一个布尔值（true或者false）。
• 如果表达式的结果是数值，则按照数值的大小进行比较；如果表达式的结果是字符串，则需要参考字符序collation的设置进行比较

逻辑运算符

where子句中可以包含多个查询条件，使用逻辑运算符可以将多个查询条件组合起来，完成更为复杂的过滤筛选。

常用的逻辑运算符包括逻辑与（and）、逻辑或（or）以及逻辑非（not），其中逻辑非（not）为单目运算符。

• 逻辑非（not或!）
  逻辑非（not）为单目运算符

• and逻辑运算符使用and逻辑运算符连接两个布尔表达式时，只有两个布尔表达式的值都为true时，整个逻辑表达式的结果才为true。

• 语法格式如下:布尔表达式1 and 布尔表达式2

• or逻辑运算符or逻辑运算符连接两个布尔表达式时，只有两个表达式的值都为false时，整个逻辑表达式的结果才为false。
  语法格式如下:布尔表达式1 or 布尔表达式2

is (not) null运算符

is NULL用于判断表达式的值是否为空值NULL（is not 恰恰相反），is NULL的语法格式如下。

表达式 is null 
#说明：不能将“score is NULL”写成“score = NULL;”，
#原因是NULL是一个不确定的数，不能使用“=”、“！=”等比较运算符与NULL进行比较。
#例子
SELECT emp_name As 姓名, emp_address  AS 地址 
FROM EMPLOYEE 
WHERE emp_address IS NULL

between and

把某一字段中内容在特定范围内的记录查询出来

SELECT emp_name,emp_age 
FROM EMPLOYEE 
WHERE emp_age BETWEEN 20 AND 30

in

把某一字段中内容与所列出的查询内容列表匹配的记录查询出来

SELECT emp_name AS 员工姓名,emp_contry As 地址 
FROM EMPLOYEE 
WHERE emp_contry IN ('北京','广州','上海')

模糊查询（LIKE）

like运算符用于判断一个字符串是否与给定的模式相匹配。
模式是一种特殊的字符串，特殊之处在于不仅包含普通字符，还包含有通配符。
在实际应用中，如果不能对字符串进行精确查询，此时可以使用like运算符与通配符实现模糊查询，like运算符的语法格式如下。
字符串表达式 [ not ] like 模式

SELECT emp_name AS 姓名 
FROM EMPLOYEE 
WHERE emp_name LIKE '张%'

BINARY运算符

默认情况下，比较是不区分大小写的方式执行的。然而，以前我们注意到，可以添加BINARY关键字让MySQL执行区分大小写的比较。

SELECT * 
FROM emp 
WHERE empname  LIKE BINARY '%tom%';

2 聚合函数

工资表中存储了所有员工的工资，求：公司支出的总薪水、员工的平均工资、工资水平在2000块钱以上的总共有多少人?

2.1 SUM

SELECT SUM(sal_disburse) AS ‘2009年5月公司总薪水支出’
FROM SALARY 
WHERE sal_ym=‘200905’ 

2.2 AVG

SELECT AVG(sal_disburse) AS ‘2009年6月平均工资’
FROM SALARY 
WHERE sal_ym=‘200906’ 

2.3 MAX、MIN

SELECT AVG(sal_disburse) AS 平均工资,  MAX (sal_disburse) AS 最高工资,  MIN (sal_disburse) AS 最低工资 
FROM SALARY 
WHERE sal_ym =‘200911’

2.4 COUNT

SELECT COUNT (*)  AS ‘2000元工资以上的员工数量’
FROM SALARY 
WHERE sal_disburse>=2000
AND sal_ym=‘200907’

3 完整的sql语句

select dept_id, count(*) from employee where dept_id is not null -- 对所有数据进行筛选，不能使用聚合函数
GROUP BY dept_id   -- 对where筛选后的数据进行分组
having count(*)>2  -- 对分组后的数据进行筛选
order by dept_id desc; -- 对数据进行排序

4 连接查询

学员内部测试成绩查询的每次显示的都是学员的编号信息，因为该表中只存储了学员的编号；实际上最好显示学员的姓名，而姓名存储在学员信息表；如何同时从这两个表中取得数据？

from 表名1  [ 连接类型 ]  join 表名2  on 表1和表2之间的连接条件

4.1 SQL连接分类

• inner连接（内连接）
• outer连接（外连接）
  left（左外连接，简称为左连接）
  right（右外连接，简称为右连接）
• cross连接（交叉连接）

4.2 内连接 inner join

关键字：inner join on

#组合两个表中的记录，返回关联字段相符的记录，即返回两个表的交集部分
SELECT t1.name, t2.salary  
FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;

4.3 左连接查询 left join

关键字：left join on / left outer join on

left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。 左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

SELECT * 
FROM table1 
LEFT JOIN table2 ON table1.id = table2.id;

#多张表连接
SELECT * 
FROM table1 
LEFT JOIN table2 ON table1.id = table2.id
LEFT JOIN table3 ON table2.id = table3.id;

4.4 右连接查询 right join

关键字：right join on / right outer join on

说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。

SELECT * 
FROM table1 
LEFT JOIN table2 ON table1.id = table2.id;

4.5 交叉连接 cross join

在 MySQL 中JOIN，CROSS JOIN、 和INNER JOIN是句法是等价（它们可以相互替换）。在标准 SQL 中，它们不是等价的。

select * 
from employee e CROSS JOIN dept d on e.dept_id=d.dept_id;

#在mysql中等价于
select * 
from employee e INNER JOIN dept d on e.dept_id=d.dept_id;

#在mysql中等价于
select * 
from employee e,dept d where e.dept_id=d.dept;

5 子查询

出现在其他语句中的select语句，称为子查询或内查询。

在 MySQL 中，除了在 ORDER BY 列表中以外，在 SELECT、UPDATE、INSERT 和 DELETE 语句中任何可以使用表达式的地方都可以使用子查询来替代。

子查询特征

• 子查询的 SELECT 查询总是使用圆括号括起来。
• 子查询可以嵌套在外部 SELECT、INSERT、UPDATE 或 DELETE 语句的 WHERE 或 HAVING 子句内，或者其它子查询中。
• 根据可用内存和查询中其它表达式的复杂程度不同，嵌套限制也有所不同，嵌套到 32 层。

5.1 子查询出现的位置

位置	类型	说明
select后面	仅仅支持标量子查询	做为一个字段值
from后面	支持表子查询	做为一个临时表
where 或 having后面	支持标量子查询（单行）、列子查询（多行）、行子查询	做为过滤条件
exists后面（也被称为相关子查询）	支持表子查询

• 标量子查询：结果集为一行一列
• 列子查询：结果集为一列多行
• 行子查询：结果集为一行多列
• 表子查询：结果集为多行多列

HERE 包括子查询的语句通常采用以下格式中的一种：

• WHERE 表达式 比较运算符 (子查询)
• WHERE 表达式 [NOT] IN (子查询)
• WHERE [NOT] EXISTS (子查询)

5.2 比较运算符（子查询）

子查询可由一个比较运算符（=、< >、>、> =、<、!>, ! < 或 < =）引入。要使用由比较运算符引入的子查询，必须对数据和问题的本质非常熟悉，以了解该子查询实际是否只返回一个值。

--示例：取出所有大于平均工资的所有员工信息
select * 
from employee 
where emp_salary > (select avg(emp_salary) from employee);

5.3 IN子查询

通过 IN（或 NOT IN）引入的子查询结果是一列零值或更多值。子查询返回结果之后，外部查询将利用这些结果。

--示例：显示部门为销售部和研发部，并且年龄为22岁的员工的所有信息
select * 
from employee where emp_age=22 and dept_id in (select dept_id from dept where dept_name='销售部' or dept_name='研发部')

5.4 EXISTS子查询

使用 EXISTS 关键字引入一个子查询时，就相当于进行一次存在测试。外部查询的 WHERE 子句测试子查询返回的行是否存在。子查询实际上不产生任何数据；它只返回 TRUE 或 FALSE 值。 使用 EXISTS 引入的子查询在以下几方面与其它子查询略有不同，EXISTS 关键字前面没有列名、常量或其它表达式。

-- 如果存储部位1004，则显示所有员工信息
SELECT * 
FROM employee WHERE EXISTS (SELECT d_name FROM department WHERE d_id=1004);
--如果此处内层循环并没有查询到满足条件的结果，则返回false，外层查询不执行

6 MySQL分页

MySQL数据库实现分页比较简单，提供了LIMIT函数。一般只需要直接写到sql语句后面就行了。

6.1 分页实现

select字段列表
from数据源
limit [start,]length;
--start表示从第几行记录开始检索，length表示检索多少行记录。表中第一行记录的start值为0。

--例子
select * 
from table WHERE … LIMIT 10; #返回前10行
--上sql语句等效于
select * 
from table WHERE … LIMIT 0,10; #返回前10行 
select * 
from table WHERE … LIMIT 10,10; #返回第10-20行数据 

6.2 分页优化（扩展）

问题：对于limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行。如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。limit n性能是没问题的，因为只扫描n行。
优化解决办法：

SELECT * 
FROM message 
WHERE id>=1000 ORDER BY id ASC LIMIT 20;//当前页

SELECT * 
FROM message 
WHERE id>1020 ORDER BY id ASC LIMIT 20;//下一页

SELECT * 
FROM message 
WHERE id<1000 ORDER BY id DESC LIMIT 20;//上一页