第二章数据查询语言DQL

骑猪看日落 2023-01-19 04:53 187阅读 0赞

**目录**

1 mysql登录与退出

2  常用基础语句

查询所有数据库

使用某一个数据库

显示某数据库中的所有表

显示表的属性结构

3 MySQL语法规范

关键字格式

注释

4 基础查询

语法

着重号\`字段名\`

起别名

去重

\+ 号的作用

ifnull函数

5 条件查询

按条件表达式筛选

按逻辑表达式筛选

模糊查询

between and

is null

安全等于 <=>

6 排序查询

语法

注意事项

7 常见函数

分类

单行函数

一、数值函数

约数函数ROUND

截断函数TRUNCATE

上限函数CEILING

下限函数FLOOR

绝对值函数ABS

RAND()

二、字符串函数

字符转换函数

1、ASCII()

2、CHAR()

3、LOWER()和UPPER()

4、STR()（后期补充）

去空格函数

取子串函数

1、left()

2、RIGHT()

3、SUBSTRING()

字符串比较函数

1、CHARINDEX()

2、PATINDEX()

字符串操作函数

1、QUOTENAME()  (暂时用不到，后期补充)

2、REPLICATE()

3、 REVERSE()

4、 REPLACE()

5、SPACE()

6、 STUFF()

7、CONCAT()

三、 日期函数

now（重点）

curdate

curtime

str\_to\_date（重点）

date\_format（重点）

datediff

monthname

四、聚合函数

聚合分析的基本概念

聚合函数

1、SUM函数的使用

2、计数函数——COUNT()

3、最大/最小值函数—MAX()/MIN()

4、平均值函数——AVG()

聚合分析的重值处理

聚合函数的组合使用

GROUP BY组合查询（重点）

1、GROUP BY子句创建分组

2、单一分组的查询

3、GROUP BY子句根据多列组合行

4、ROLLUP运算符和CUBE运算符

5、使用GROUPING函数来处理汇总产生的NULL值

6、使用Having子句来顾虑掉无用的数据

7、GROUP BY子句中的NULL值处理---HAVING子句

8、HAVING子句与WHERE子句

SELECT语句各查询子句总结

8 流程控制函数

一、IFNULL和COALESCE函数

二、IF函数

三、CASE运算符

1、simple case的语法结构

2、searched  case的语法结构

9 连接查询

一、定义

二、笛卡尔乘积现象

三、分类

四、内连接

1、等值连接

2、非等值连接

3、自连接

五、外连接

基本格式

六、联合连接 union

union语句注意事项

七、自然连接

八、交叉连接

九、连接总结与延伸

多表连接怎么办？

10 子句与连接复杂查询

子查询

分类

注意事项

一、WHERE条件中的子查询

二、select中的子查询

三、FROM中的子查询

四、ALL、SOME、ANY

五、关联子查询

六、exists 运算符

11 分页查询（非常重要，后期继续补充）

语法格式

--------------------

# 1 mysql登录与退出 #

启动服务：net start  服务名

停止服务： net stop  服务名

**登录：mysql -h主机名 -p端口号  -u用户名 -p密码/-p**

退出：exi或者ctrl +c

--------------------

# 2  常用基础语句 #

## 查询所有数据库 ##

show databases;

## 使用某一个数据库 ##

use  databasename;

## 显示某数据库中的所有表 ##

show tables;或者show tables from databasename;

## 显示表的属性结构 ##

desc tablename;

--------------------

# 3 MySQL语法规范 #

## 关键字格式 ##

> **不区分大小写，建议关键字大写，表名、列名小写**

每句话用;或\\g结尾

> **每条命令根据需要，各子句一般分行写，关键字不能缩写也不能分行**

--------------------

## **注释** ##

**单行注释：\#注释文字**

**单行注释：-- 注释文字（要有空格）**

**多行注释：/\* 注释文字 \*/**

--------------------

# **4 基础查询** #

## **语法** ##

> **select  查询列表  from 表名 ; **

**查询列表可以是：表中的字段、常量、表达式、函数**  
**查询的结果是一个虚拟的表格**

> **注意：在进行查询操作之前要指定所有的库：use myemployees;**

查询表中的单个字段：select last\_name from employees;

查询表中的多个字段：select last\_name, salary, email from employees;

查询表中的所有字段：select \* from employees;

> **按F12进行格式化**

--------------------

##  **着重号\`字段名\` ** ##

>  用来区分是否是关键字或者字段

选中语句进行执行或F9

查询常量值：

select 100;select ‘john’;

查询表达式：select 100\*98；

查询函数：select version();

--------------------

## 起别名 ##

如果查询的字段有重名的情况，使用别名可以区分开来

> **语法格式**
> 
> **select 字段名 as 别名 from  tablename  as 别名;**
> 
> **select 字段名  别名 from  tablename as 别名;**

**如果别名有特殊符号要加双引号：**

select salary as “out put” from employees;

--------------------

## **去重** ##

查询员工表中涉及到的所有部门编号：select distinct department\_id from employees;

去重这里有一个重点？distinct只能放在所有字段前，起到对所有字段联合去重的作用。举个例子说明一下。

select  distinct name,age from  student;

原表如下：

---------------------------------------------------
    
            id        |        name      |      age
    
            1         |        wang1     |       12
    
            2         |        wang2     |       12
    
            3         |        wang3     |       16
    
    ---------------------------------------------------

> 最终我们执行完语句后，得出的结果仍是3条结果，原因是因为，distinct先按照 name去重、在此基础上按照age去重，只有当name、age同时不同时该数据才会被保留。

--------------------

## \+ 号的作用 ##

**两个操作数为数值型，则做加法运算**

只要其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则继续做加法运算；

如果转换失败，则将字符型数值转换成0

只要其中一方为null，则结果肯定为null

使用concat连接字符串：

查询员工的名和姓连接成一个字段，并显示为姓名：select concat(last\_name,first\_name) as 姓名 from employees;

--------------------

## **ifnull函数** ##

**ifnull检测是否为null，如果为null，则返回指定的值，否则返回原本的值：**

select ifnull(commission\_pct, 0) as 奖金率, commission\_pct from employees;

isnull函数判断某字段或表达式是否为null，如果是，则返回1，否则返回0

--------------------

# 5 条件查询 #

> **语法：select 查询列表 from 表明 where 筛选条件;**

## 按条件表达式筛选 ##

条件运算符：

>     <

=     !=

<>   >=

--------------------

## 按逻辑表达式筛选 ##

主要作用：用于连接条件表达式  
逻辑运算符

&&         ||           !

and       or         not

--------------------

## 模糊查询 ##

### like ###

**一般和通配符搭配使用，可以判断字符型数值或数值型。**

通配符

> **% 任意多个字符，包含0个字符  
> \_ 任意单个字符**

### between and ###

*  使用between and可以提高语句的简洁度；
 *  包含临界值；
 *  两个临界值不能替换顺序；

### in ###

*  含义：判断某字段的值是否属于in列表中的某一项
 *  使用in提高语句简洁度
 *  in列表的值类型必须一致或兼容
 *  in相当于等于，所以不支持通配符（like才支持）

### **is null** ###

*  用于判断null值
 *  **=或者<>不能用于判断null值**

### 安全等于 <=> ###

*  is null：仅仅可以判断null值，可读性较高
 *  <=>：既可以判断null值，又可以判断普通的数值，可读性较低

--------------------

# 6 排序查询 #

## 语法 ##

> **select 查询列表 from 表【where 筛选条件】 order by 排序列表 【asc|desc】**

## 注意事项  ##

asc代表的是升序，desc代表的是降序，如果不写，默认是升序

order by子句中可以支持单个字段、多个字段、表达式、函数、别名

> **order by子句一般是放在查询语句的最后面，但limit子句除外**

--------------------

# 7 常见函数 #

## 分类 ##

*  单行函数：如数值函数、字符串函数等
 *  分组函数：做统计使用，又称为统计函数、聚合函数、组函数。

--------------------

## 单行函数 ##

## 一、数值函数 ##

这种简单的内容，我就不BB了，上才艺~

### **约数函数ROUND** ###

**m代表输入值，n代表约数小数点位数，默认为0。 **

SELECT ROUND(m,n);

### **截断函数TRUNCATE** ###

**m代表输入值，n代表截断小数点位数，默认为0。**

SELECT TRUNCATE(m,n);

### **上限函数CEILING** ###

**返回大于或等于数字X的最小整数。 **

SELECT CEILING(X);

### **下限函数FLOOR** ###

**返回小于或等于数字X的最大整数。 **

SELECT FLOOR(X);

### ** 绝对值函数ABS** ###

**返回数字X的绝对值。**

SELECT ABS(X);

### **RAND()** ###

**生成0-1区间的随机浮点数 **

SELECT RAND();

--------------------

## 二、字符串函数 ##

**字符串函数对二进制数据、字符串和表达式执行不同的运算。（难点与重点）**

此类函数**作用于CHAR、VARCHAR、 BINARY、 和VARBINARY** 数据类型

以及可以**隐式转换为 CHAR 或VARCHAR的数据类型**。

简单列举下常用的字符串函数有哪些？

### **字符转换函数** ###

> ### **1、ASCII()** ###
> 
> **返回字符表达式 最左端字符的ASCII 码值。在ASCII（）函数中，纯数字的字符串可不用‘’括起来，但含其它字符的字符串必须用‘’括起来使用**，否则会出错。
> 
> ### **2、CHAR()** ###
> 
> **将ASCII 码转换为字符。****如果没有输入 0 ~ 255 之间的ASCII 码值，CHAR（） 返回NULL 。**
> 
> ### **3、LOWER()和UPPER()** ###
> 
> **LOWER()将字符串全部转为小写；UPPER()将字符串全部转为大写。**
> 
> ### **4、STR()（后期补充）** ###
> 
> 把**数值型数据**转换为**字符型数据**。  
> STR (<float\_expression> \[，length\[， <decimal>\]\])  
> **length 指定返回的字符串的长度**，**decimal 指定返回的小数位数**。如果没有指定长度，缺省的length 值为10， decimal 缺省值为0。  
> 当length 或者decimal 为负值时，返回 NULL；  
> 当length 小于小数点左边（包括符号位）的位数时，返回length 个\*；  
> 先服从length ，再取 decimal ；  
> 当返回的字符串位数小于length ，左边补足空格。

--------------------

### **去空格函数** ###

> 1、**LTRIM() **把**字符串头部**的空格去掉。  
> 2、**RTRIM()** 把**字符串尾部**的空格去掉。

--------------------

### **取子串函数** ###

> ### **1、left() ** ###
> 
> LEFT ('helloworld'， 3)  
> 返回helloworld从左开始3个字符，即hel。
> 
> ### **2、RIGHT()**** ** ###
> 
> RIGHT ('helloworld'， 3)  
> 返回helloworld从右开始3个字符，即rld。
> 
> ### **3、SUBSTRING()** ###
> 
> SUBSTRING ('helloworld', 3, 4)  
> 返回从字符串左边第3个字符（l）开始3个字符，即llow。

--------------------

### **字符串比较函数** ###

### **1、CHARINDEX() ** ###

**返回字符串中某个指定的子串出现的开始位置。**

**CHARINDEX ('llo', 'helloworld'，2)**

其中'**llo'是所要查找的字符串**；**'helloworld'是其父字符串**；**`2`：开始查找的位置**，为空时默认从第一位开始查找。咋理解？

> 我在字符串**'helloworld'中查找**'**llo'，从左边第二个索引开始（此处索引从1开始），并返回第一个**'**llo'所在的位置（上述结果为3），**若没有发现'**llo'**，则返回0。

> **注意！！！在mysql中LOCATE('llo', 'helloworld') 函数相类似。这个函数后期好好补一下~**

SQL提供了特殊的关键字用于查询时区分大小写,即**COLLATE Latin1\_General\_CS\_AS**，**其中`CS`为`Case-Sensitve`的缩写。**

**CHARINDEX ('llo', 'helloworld'****COLLATE Latin1\_General\_CS\_AS****） \# ****此函数不能用于TEXT和IMAGE数据类型。**

### **2、PATINDEX()** ###

**返回字符串中某个指定的子串出现的开始位置。**  
PATINDEX (<’%substring \_expression%’>， <column\_ name>) **其中子串表达式前后必须有百分号“%”否则返回值为0。**  
与CHARINDEX 函数不同的是，**PATINDEX函数的子串中可以使用通配符**，且**此函数可用于CHAR、 VARCHAR 和TEXT 数据类型**。

**select PATINDEX('%lo%','helloworld')   返回结果是3；**

**select PATINDEX('%l\_o%','helloworld')   返回结果也是3；其中下划线'\_'是通配符。**

`PATINDEX`也允许支持大小写敏感，做法和`CHARINDEX`一样，此处不再累述。

> ** 注意：和`CHARINDEX`类似，`PATINDEX`也可以用来判断一个字符串中是否包含另一个字符串，两种的差异在于，前者是全匹配，后者支持模糊匹配。**

--------------------

### **字符串操作函数** ###

### 1、QUOTENAME()  (暂时用不到，后期补充) ###

返回被特定字符括起来的字符串。  
QUOTENAME (<’character\_expression’> \[， quote\_ character\]) 其中quote\_ character 标明括字符串所用的字符，缺省值为“\[\]”。

> **看不懂这个 (<’character\_expression’> \[， quote\_ character\]) ？  我来告诉你， '\[， quote\_ character\]'里的是可选择的参数，通常把','也放进'\[\]'中，这样你理解了吗~**

### 2、REPLICATE() ###

返回一个**重复 character\_expression 指定次数**的字符串。  
REPLICATE (character\_expression, integer\_expression) 如果integer\_expression 值为负值，则返回NULL 。

### 3、 REVERSE() ###

将指定的字符串的字符排列顺序颠倒。  
REVERSE (<character\_expression>) 其中character\_expression可以是字符串、常数或一个列的值。

### **4、 REPLACE()** ###

返回被替换了指定子串的字符串。  
REPLACE (<string\_expression1>， <string\_expression2>， <string\_expression3>) 用 string\_expression3 替换在string\_expression1 中的子串string\_expression2。  
例如，select REPLACE** ('helloworld'，'world', 'python')   **

> **即用python替换了world，输出结果为hellopython。是不是很惊喜呀~**

### 5、SPACE() ###

返回一个有指定长度的**空白字符串**。  
SPACE (<integer\_expression>) ，如果integer\_expression 值为负值，则返回NULL 。

### 6、 STUFF() ###

用另一子串替换父字符串**指定位置、长度**的子串。  
STUFF (<character\_expression1>， <start\_ position>， <length>，<character\_expression2>)  
如果起始位置为负或长度值为负，或者起始位置大于character\_expression1的长度，则返回NULL值。

> **如果length长度大于character\_expression1中start\_ position以右的长度，则character\_expression1只保留首字符。**

**例如，select STUFF ('helloworld'，6，6， 'python') 其结果是'hellopytho'，咦~，是不是少个n呀~**

### 7、CONCAT()  ###

**主要起到连接字符串的作用**

具体格式如下CONCAT('**hello**', '**world'**)

输出结果为**'helloworld'。**

> **注意：CONCAT('字符串 | 列名', ''字符串 | 列名',')  也是可以的~但其本质上都是字符串与字符串的拼接。**

> **补充：关于字符串的函数更多资料可查询mysql string functions **

--------------------

## 三、 日期函数 ##

### now（重点） ###

返回当前系统日期+时间

### curdate ###

返回当前系统日期，不包含时间

### curtime ###

返回当前时间，不包含日期

可以获取指定的部分，年、月、日、小时、分钟、秒

### str\_to\_date（重点） ###

将日期格式的字符转换成指定格式的日期

> **SELECT  STR\_TO\_DATE('1998-3-2', '%Y/%c/%d') AS output ;**

### date\_format（重点） ###

将日期转换成字符串

> **SELECT   DATE\_FORMAT(NOW(), '%y年%m月%d日)') AS output ;**

### datediff ###

返回两个日期相差的天数

### monthname ###

以英文形式返回月

--------------------

## 四、聚合函数 ##

### **聚合分析的基本概念** ###

聚合分析实际上就是对一组数据的统计分析，在SQL中，可以通过使用聚合函数来实现。在访问数据库时，经常需要对表中的某列数据进行统计分析，如求其最大值、最小值、平均值等。所有这些针对表中一列或者多列数据的分析就称为聚合分析。在SQL中，可以使用聚合函数快速实现数据的聚合分析。与第7章介绍的SQL中的函数不同，聚合函数是对列中的一系列数据进行处理，返回单个统计值；而前面的函数则是对列中的单个数据进行处理。

--------------------

### 聚合函数 ###

> NULL  
> NULL是一种数据类型，表示 SQL 中没有数据；  
> 查询时：WNERE name IS NULL \#查询name为NULL值的行

> COUNT()  
> COUNT(\*) \#一行记录中只要一列有数据，则计数  
> COUNT(COLUMN\_name) \#空值不计数

> SUM()  
> sum(col\_name) \#垂直聚合，只对数值进行计算总和，SUM 将忽略 NULL 值，当做0处理

> MAX() MIN()  
> MAX() \#返回最大值  
> MIN() \#返回最小值  
> 它们都可以用在非数字列上。MIN 将返回最小的数字、最早的日期或按字母表排序的最之前的非数字值，具体取决于列类型

> AVG()  
> AVG() \#返回平均值，只对值列；忽略分子和分母中的 NULL 值；

--------------------

### 1、SUM函数的使用 ###

实例代码：

SELECT SUM(待统计列名) AS 别名
    FROM  表名
    WHERE 筛选条件

说明：**SUM()函数只能作用于数值型数据**，即列column\_name中的数据必须是数值型的。

**当对某列数据进行求和时，如果该列存在NULL值，则SUM函数会忽略该值。**

--------------------

### **2、计数函数——COUNT()** ###

**COUNT()函数用来计算表中记录的个数或者列中值的个数**，计算内容由SELECT语句指定。

使用COUNT函数时，必须指定一个列的名称或者使用星号，星号表示计算一个表中的所有记录。

两种使用形式如下：

>      COUNT(\*)，计算表中行的总数，即使表中行的数据为NULL，也被计入在内。
> 
>     COUNT(column)，计算column列包含的行的数目，如果该列中某行数据为NULL，则该行不计入统计总数。

注意：

COUNT(\*)函数将准确地返回表中的**总行数**，而仅当COUNT()函数的参数列没有NULL值时，才返回表中正确的行计数，所以仅当受NOT NULL限制的列作为参数时，才可使用COUNT( )函数代替COUNT(\*)函数。

**使用COUNT( )函数对多列中的数据计数**

COUNT( )函数不仅可用于对一列中的数据值计数，**也可以对多列中的数据值计数**。如果对多列计数，**则需要将要计数的多列通过连接符连接后**，作为COUNT( )函数的参数。

**关于如何使用连接符连接多列？**

使用COUNT( )函数对多列中的数据计数

统计TEACHER表中的TNO列、TNAME列和SAL列中分别包含的数据行数，以及TNO列和TNAME列、TNAME列和SAL列一起包含的数据行数。实例代码：

SELECT COUNT(TNO) AS TOTAL_TNO
    , COUNT(TNAME) AS TOTAL_TNAME
    , COUNT(SAL) ASTOTAL_SAL
    , COUNT(CAST(TNO ASVARCHAR(5)) + TNAME) AS T_NONAME
    , COUNT(TNAME +CAST(SAL AS VARCHAR(5))) AS T_NAMESAL
    FROM TEACHER

使用COUNT( )函数对多列中的数据计数

**在进行两列的连接时，由于它们的数据类型不一致，因此要使用CAST表达式将它们转换成相同的数据类型。**

**如果在被连接的列中的任何一列有NULL值时，那么连接的结果为NULL，则该列不会被COUNT( )函数计数。**

**注意：COUNT( )函数只对那些传递到函数中的参数不是NULL的行计数。**

**使用COUNT函数对满足某种条件的记录计数**

之所以可以通过WHERE子句定义COUNT()函数的计数条件，这与SELECT语句各个子句的执行顺序是分不开的。前面已经讲过，**DBMS首先执行FROM子句，而后是WHERE子句，最后是SELECT子句。**所以COUNT()函数只能用于满足WHERE子句定义的查询条件的记录。没有包括在WHERE子句的查询结果中的记录，都不符合COUNT()函数。

--------------------

### **3、最大/最小值函数—MAX()/MIN()** ###

当需要了解一列中的最大值时，可以使用MAX()函数；同样，当需要了解一列中的最小值时，可以使用MIN()函数。语法如下。

SELECT MAX (column_name) / MIN(column_name)
    FROM  table_name

> 说明：列column\_name中的数据可以是**数值、字符串**或是**日期时间**数据类型。
> 
> MAX()/MIN()函数将返回与被传递的列同一数据类型的单一值。

然而SQL不支持如下的SELECT语句。

SELECT TNAME, DNAME, TSEX, MAX (AGE)
    FROM TEACHER

> **因为聚合函数处理的是数据组**，在本例中，MAX函数将整个表看成一组，而TNAME、DNAME和TSEX的数据都没有进行任何分组，因此SELECT语句没有逻辑意义。

同样的道理，下面的代码也是无效的。

SELECT TNAME, DNAME, TSEX,SAL ,AGE
    FROM TEACHER
    WHERE AGE＝MAX (AGE)

> 解决这个问题的方法，就是**在WHERE子句中使用子查询来返回最大值**，然后**再基于这个返回的最大值**，**查询相关信息**。

如查询TEACHER表中年纪最大的教师的教工号、姓名、性别等信息。

实例代码：

SELECT TNAME, DNAME, TSEX, SAL, AGE
    FROM TEACHER
    WHERE AGE＝
    (
    SELECT MAX (AGE) FROM TEACHER
    )

**MAX()和MIN()函数**不仅可以作用于数值型数据，也可以**作用于字符串或是日期时间数据类型**的数据。

> 说明：对字符型数据的最大值，是按照首字母由A～Z的顺序排列，越往后，其值越大。当然，对于汉字则是按照其全拼拼音排列的，若首字符相同，则比较下一个字符，以此类推。
> 
> 当然，对与日期时间类型的数据也可以求其最大/最小值，其大小排列就是日期时间的早晚，越早认为其值越小。

**注意：确定列中的最大值（最小值）时，MAX( )（MIN( )）函数忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则MAX( )/MIN( )函数将返回NULL值。**

--------------------

### **4、平均值函数——AVG()** ###

函数AVG()用于计算一列中数据值的平均值。语法如下。

SELECT AVG (column_name)
    
    FROM table_name

说明：AVG()函数是**将一列中的值加起来**，再将其**除以非NULL值的数目**。所以，与SUM( )函数一样，**AVG()函数只能作用于数值型数据**，即列column\_name中的数据必须是数值型的。

在计算平均值时，AVG()函数将忽略NULL值。因此，如果要计算平均值的列中有NULL值，计算均值时，要特别注意。

实际上，“AVG (SAL)”与“SUM(SAL)/COUNT(SAL)”语句是等价的。因为AVG(SAL)语句的执行过程实际上是将SAL列中的值加起来，再将其和（也就等价于SUM(SAL)）除以非NULL值的数目（也就等价于COUNT(SAL)）。而语句“SUM(SAL)/COUNT(\*)”则不然，因为COUNT(\*)返回的是表中所有记录的个数，而不管SAL列中的数值是否为NULL。

> **注意：AVG()函数在计算一列的平均值时，忽略NULL值。但是，如果在该列中，所有行的值都是NULL，则AVG()函数将返回NULL值。**
> 
> **如果不想对列中的所有值求平均，则可在WHERE子句中使用搜索条件来限制用于计算均值的行。**

当执行SELECT语句时，只有那些搜索条件为True时，行中的AGE值才传到均值函数AVG(AGE)中。

> **与前面介绍的MAX()函数一样，不能直接用于WHERE子句，必须以子查询的形式。**

--------------------

### **聚合分析的重值处理** ###

前面介绍的5种聚合函数，可以作用于所选列中的所有数据（不管列中的数据是否有重置），**也可以只对列中的非重值进行处理，即把重复的值只取一次进行聚合分析**。当然，对于MAX()/MIN()函数来讲，重值处理意义不大。

可以使用**ALL关键字**指明对所选列中的所有数据进行处理，使用**DISTINCT关键字**指明对所选列中的非重值数据进行处理。

以AVG()函数为例，语法如下。

SELECT AVG ([ALL/DISTINCT]column_name)
    FROM table_name

> 说明：\[ALL/DISTINCT\]在缺省状态下，默认是ALL关键字，即不管是否有重值，处理所有数据。其他聚合函数的用法与此相同。

> 注意：Microsoft Access数据库不支持在聚合函数中使用DISTINCT关键字。另外还要强调一点，在所有5种聚合函数中，除了COUNT(\*)函数外，其他的函数在计算过程中都忽略NULL值，即把NULL值的行排除在外，不进行分析。

--------------------

### **聚合函数的组合使用** ###

前面介绍的实例中，聚合函数都是单独使用的。聚合函数也可以组合使用，即在一条SELECT语句中，可以使用多个聚合函数。如下面的代码：

SELECT COUNT(*) AS num_items
           ，MAX(SAL) AS max_sal
           ，Min(AGE) AS min_age
           ，SUM(SAL)/COUNT(SAL) AS avg_sal  # 聚合函数可以做四则运算
           ，AVG(DISTINCT SAL) AS disavg_sal
    FROM TEACHER

--------------------

### **GROUP BY组合查询（重点）** ###

在实际SQL应用中，经常需要进行分组聚合，**即将查询对象按一定条件分组，然后对每一个组进行聚合分析**。

--------------------

### **1、GROUP BY子句创建分组** ###

> **创建分组是通过GROUP BY子句实现的**。与WHERE子句不同，GROUP BY子句用于归纳信息类型，以汇总相关数据。
> 
> 而为什么要使用GROUP BY子句创建分组呢？可通过下面这个简单例子来说明。

--------------------

### **2、单一分组的查询** ###

假如要从TEACHER表中查询所有男教师的平均工资，用前面介绍的聚合函数AVG(),实现代码如下：

SELECT AVG(SAL) AS boyavg_sal
    FROM TEACHER
    WHERE TSEX='男'

**而如果同时需要查询所有女教师的平均工资，该如何处理呢？**

> **显然，只能在WHERE子句中改变查询条件。因此在一次查询中会出现男、女老师的查询结果，此时AVG函数所求的就是男女老师的平均值，这显然不是我们想看到的结果。因此我们以性别为标准，通过GROUP BY****将表中的所有数据分组，即男人帮和女子团，此时再分别对其运用AVG聚合函数，即可计算其各自对应的工资的平均值。其他聚合函数同理。**

> 说明：GROUP BY子句依据column列里的数据对行进行分组，即具有相同的值的行被划为一组。**它一般与聚合函数同时使用。**当然，这里的SUM()函数也可以是其他聚合函数。
> 
> **所有的组合列（GROUP BY子句中列出的列）必须是来自FROM子句列出的表**，**不能根据实际值、聚合函数结果或者其他表达式计算的值来对行分组**。

> 下面分析一下DBMS执行该实例的步骤。
> 
> DBMS（数据库管理系统）首先执行FROM子句，**将表TEACHER作为中间表**。
> 
> 如果有WHERE子句，则根据其条件，**从中间表中剔除那些值为False的列**。
> 
> 根据GROUP BY子句指定的分组列，**将中间表中的数据进行分组**。这里TSEX只有“男”和“女”，因此中间表中的数据被分成了两组，一组中TSEX的值为“男”，另一组中TSEX的值为“女”。
> 
> **为每个组计算SELECT子句中的值**，并为每组生成查询结果中的一行。对于TSEX值为“男”的行组，SELECT子句中首先执行“TSEX+'教师'”，得到“男教师”列值，再执行“AVG(SAL)”，求得该行组中的SAL的均值，**将这两个值作为结果表中的一条记录**。同样，对TSEX值为“女”的行组，进行类似的操作得到另一条记录。

--------------------

### **3、GROUP BY子句根据多列组合行** ###

上节介绍的GROUP BY子句进行组合查询，在GROUP BY子句中只有一列，它是组合查询的最简单形式。**如果表中的行组依赖于多列，只要在查询的GROUP BY子句中，列出定义组所需的所有列即可。**

SELECT DNAME,TSEX, COUNT(*) ASTOTAL_NUM
    FROM TEACHER
    GROUP BY DNAME,TSEX
    ORDER BY DNAME

> **注意：GROUP BY DNAME,TSEX后面，分组顺序是按照GROUP BY后面所放列的顺序来的。顺序不同得到的结果可能也不一样，所以要谨慎吧~~**

--------------------

### **4、ROLLUP运算符和CUBE运算符** ###

**在使用GROUP BY子句根据多列组合行时**，可以在GROUP BY子句中使用**ROLLUP运算符**和**CUBE运算符**，扩展查询结果。两者的主要不同在于，CUBE运算符扩展的信息要比ROLLUP运算符多，下面结合具体的实例讲解二者的使用及区别。

**首先，创建一个新表**

# 在数据库中建立新表
    IF OBJECT_ID('tb') IS NOT NULL
    DROP TABLE tb;
    GO  
    CREATE TABLE tb
    (
        Provider    VARCHAR(10)
        ,MaterialNo VARCHAR(3)
        ,Quantity INT
    ); 
    # 插入数据
    INSERT INTO tb SELECT 'Canon',  '001',  500 
    UNION ALL SELECT 'Canon', '001', 200 
    UNION ALL SELECT 'Canon', '002', 100
    UNION ALL SELECT 'Canon', '002', 300
    UNION ALL SELECT 'Sony', '001', 200 
    UNION ALL SELECT 'IBM', '002', 100 
    UNION ALL SELECT 'IBM', '001', 600 
    UNION ALL SELECT 'IBM', '001', 200

**然后，使用ROLLUP来对数据进行汇总**

SELECT Provider,MaterialNo,SUM(Quantity) AS Sum_Quantity FROM tb
    GROUP BY Provider,MaterialNo
    WITH ROLLUP

结果如下：

> Provider   MaterialNo Sum\_Quantity  
> \---------- ---------- ------------  
> Canon      001        700  
> Canon      002        400  
> Canon      NULL       1100  
> IBM        001        800  
> IBM        002        100  
> IBM        NULL       900  
> Sony       001        200  
> Sony       NULL       200  
> NULL       NULL       2200  
> **由结果可以得知，ROLLUP运算符实现了对Provider级别进行汇总，结果集中Provider字段不为空，MaterialNo字段为空的时候实现了对不同的Provider分类汇总，而当Provider与MaterialNo都为空时则是对所有的Provider来实现数据汇总。**

**使用CUBE来对数据进行汇总**

SELECT Provider,MaterialNo,SUM(Quantity) AS Sum_Quantity FROM tb
    GROUP BY Provider,MaterialNo
    WITH CUBE

> /\*  --结果  
> Provider   MaterialNo Sum\_Quantity  
> \---------- ---------- ------------  
> Canon      001        700  
> Canon      002        400  
> Canon      NULL       1100  
> IBM        001        800  
> IBM        002        100  
> IBM        NULL       900  
> Sony       001        200  
> Sony       NULL       200  
> NULL       NULL       2200  
> NULL       001        1700  
> NULL       002        500  
> \*/  
> 与ROLLUP相比，从结果可以看出CUBE的结果集在ROLLUP结果集的基础上增加了两行，  
> NULL       001        1700  
> NULL       002        200  
> 即对不同MaterialNo也实现了汇总。

>  **我们来看下，ROLLUP与CUBE的差异（重点！！！）**  
> ROLLUP 生成的结果集为所选列中值的某一维度的聚合。如以上示例中**实现了对Provider维度进行汇总**。  
> CUBE  生成的结果集为所选列中值的所有维度的聚合。如以上示例中**实现了对Provider和MaterialNo所有维度进行汇总**。

--------------------

### **5、使用GROUPING函数来处理汇总产生的NULL值** ###

对于使用ROLLUP与CUBE汇总数据所产生的NULL值，容易引起与实际数据本身为NULL容易引起歧义，对此我们可以使用GROUPING函数的进行区分。当NULL为ROLLUP或CUBE所产生时，则GROUPING函数返回的值为1，当NULL来自实际数据本身的话，GROUPING函数返回的值为0。

SELECT
        CASE WHEN (GROUPING(Provider) = 1) THEN 'ALL'
             ELSE Provider END AS Provider,
        CASE WHEN (GROUPING(MaterialNo) = 1) THEN 'ALL'
             ELSE MaterialNo END AS MaterialNo,
        SUM(Quantity) AS Sum_Quantity FROM tb
    GROUP BY Provider,MaterialNo
    WITH CUBE

> /\*  --结果  
> Provider   MaterialNo Sum\_Quantity  
> \---------- ---------- ------------  
> Canon      001        700  
> Canon      002        400  
> Canon      ALL        1100  
> IBM        001        800  
> IBM        002        100  
> IBM        ALL        900  
> Sony       001        200  
> Sony       ALL        200  
> ALL        ALL        2200  
> ALL        001        1700  
> ALL        002        500  
> \*/

--------------------

### **6、使用Having子句来顾虑掉无用的数据** ###

比如本例过滤掉了总计以及MaterialNo汇总的数据。

--------------------

### **7、GROUP BY子句中的NULL值处理---HAVING子句** ###

**当GROUP BY子句中用于分组的列中出现NULL值时，将如何分组呢？**按照前面的介绍，NULL不等于NULL（在WHERE子句中有过介绍）。

**然而，在GROUP BY子句中，却将所有的NULL值分在同一组，即认为它们是“相等”的。**

GROUP BY子句分组，依据所选列的数据进行分组，**将该列具有相同值的所有行划为一组**。在实际应用中，需要**删除那些不能满足条件的行组**，为了实现这个功能，SQL提供了HAVING子句。

语法如下：

SELECT column1, SUM(column1) 
    FROM table 
    GROUP BY column1
    HAVING SUM(column1) condition value

> 说明：**HAVING通常与GROUP BY子句同时使用**。当然，语法中的SUM()函数也可以是其他任何聚合函数。DBMS将HAVING子句中的搜索条件**应用于GROUP BY子句产生的行组**，如果行组不满足搜索条件，就将其从结果表中删除。

> **注意：前面介绍的有关WHERE子句的所有操作，如使用连接符、通配符、函数等，在HAVING子句中都可以使用。**

**实例，从TEACHER表中查询至少有两位教师的系及教师人数。**实现代码：

SELECT DNAME, COUNT(*) ASnum_teacher
    FROM TEACHER
    GROUP BY DNAME
    HAVING COUNT(*)>=2

### **8、HAVING子句与WHERE子句** ###

HAVING子句和WHERE子句的相似之处在于，它也定义搜索条件。**但与WHERE子句不同，HAVING子句与组有关，而不是与单个的行有关**。

> 如果指定了GROUP BY子句，那么HAVING子句定义的搜索条件**将作用于这个GROUP BY子句创建的那些组**。
> 
> 如果指定WHERE子句，而没有指定GROUP BY子句，那么HAVING子句定义的搜索条件**将作用于WHERE子句的输出**，并把这个输出看作是一个组。
> 
> 如果既没有指定GROUP BY子句也没有指定WHERE子句，那么HAVING子句定义的搜索条件**将作用于FROM子句的输出**，并把这个输出看作是一个组。

在SELECT语句中，WHERE和HAVING子句的执行顺序不同。由SELECT语句的执行步骤可知，WHERE子句只能接收来自FROM子句的输入，而HAVING子句则可以接收来自GROUP BY子句、WHERE子句和FROM子句的输入。**在数据的分组聚合分析中，HAVING子句与WHERE子句也可以共存。WHERE子句在分组之前过滤数据，而HAVING子句则过滤分组后的数据。**

**通常情况下，HAVING子句都与GROUP BY子句一起使用。**然而，如果没有GROUP BY子句，HAVING子句也可以单独使用。

--------------------

### **SELECT语句各查询子句总结** ###

完整的SELECT查询语句可以表示为：

SELECT         select\_list

FROM          table\_source

\[ WHERE     search\_condition \]

\[ GROUP BY    group\_by\_expression \]

\[ HAVING        search\_condition \]

\[ ORDER BY    order\_expression \[ ASC | DESC \] \]

其中\[ \]中的部分为可选项。

--------------------

# 8 流程控制函数 #

## 一、IFNULL和COALESCE函数 ##

IFNULL和COALESCE函数主要用于对指定列空值进行自定义标识，以便于分类或者注释。

语法格式：

> **IFNULL（列名，'自定义标识'）**  **\# 若一行该列的属性不为null，则返回其实际值；若为NULL，则返回'自定义标识'。**

> **COALESCE（列1， 列2， '自定义标识'）** **\# 从列1开始到列2结束，返回其中第一个空值列，并用自定义标识去填充列。**

话不多说，上才艺~

SELECT 
    CONCAT(first_name,' ',last_name) as customer
    COALESCE（m, n,'not assigned'）# 从m开始到n结束，返回其中第一个空值列，并用'not assigned'去填充列中的值。
    IFNULL(m,'not assigned')  # 作用同上，但只能用于单列。
    FROM customers

--------------------

## 二、IF函数 ##

> **语法格式：IF(测试表达式**expr1 ** , '自定义标识1' , '自定义标识2' )**

**测试表达式：返回的是一个布尔值，即TRUE\\FLASE。**

**expr1的值为 TRUE，则返回值为 '自定义标识1'。**

**expr1 的值为FALSE，则返回值为'自定义标识2'。**

**上才艺~**

方法1
    SELECT 
    p.product_id
    ,p.name
    ,(SELECT COUNT(*) FROM order_items oi
    WHERE oi.product_id = p.product_id) AS orders_num
    ,IF((SELECT orders_num) = 1, 'once', 'many times') AS frequency
    FROM products p
    
    方法2
    SELECT 
    p.product_id
    ,p.name
    ,COUNT(*) as orders
    ,IF(COUNT(*) = 1, 'once', 'many times') AS frequency
    FROM products p
    JOIN order_items oi
    USING(product_id)
    GROUP BY p.product_id, p.name

--------------------

## 三、CASE运算符 ##

**有多个表达式且相对这多个表达式返回不同的值，可以采用CASE运算符。**

> **注意：在SQL语句中实现“if-then-else”逻辑计算功能有两种形式：simple case和searched case。**

### 1、simple case的语法结构 ###

CASE value
        WHEN [compare_value] THEN  result
        [WHEN [compare_value] THEN  result ...] 
        [ELSE result]  END

**语义：**

**将case后面的值value**分别和**每个when子句后面的值compare\_value**进行相等比较：

如果一旦和某个when子句后面的值相等则返回相应的then子句后面的值result；

如果和所有when子句后面的值都不相等，则返回else子句后面的值；

如果没有else部分则返回null。

> **注意：**
> 
> **　　①value可以是字面量、表达式或者列名**
> 
> **　　②CASE表达式的数据类型取决于跟在then或else后面的表达式的类型**
> 
> **类型必须相同（可尝试做隐式类型转换），否则出错**

上才艺~

select 
    userid
    ,case salary                                             
    when 1000 then 'low'
    when 2000 then 'med'
    when 3000 then 'high'
    else '无效值' 
    end as salary_grade
    from salary_tab;
    
    
    结果如下：
    +--------+--------------+
    | userid | salary_grade |
    +--------+--------------+
    |      1 | low          |
    |      2 | med          |
    |      3 | high         |
    |      4 | 无效值        |
    |      5 | low          |
    +--------+--------------+

--------------------

### 2、searched  case的语法结构 ###

CASE
        WHEN [condition] THEN result
        [WHEN [condition] THEN result ...]
        [ELSE result]  END

**语义：**

如果**某个when子句后面的条件condition**为true，则返回**相应的when子句后面的值result**；

如果**所有的when子句后面的条件condition**都不为true，则返回else子句后面的值；

如果没有else部分则返回null。

上才艺~

SELECT 
    CONCAT(first_name,last_name) as customer
    ,points
    ,CASE 
        WHEN points > 3000 THEN '黄金'
        WHEN points < 3000 AND points >2000 THEN '白银'
        ELSE '青铜'
    END AS class   --这里的as class ，其实是给case返回值起的别名
    FROM sql_store.customers
    ORDER BY points DESC

--------------------

# 9 连接查询 #

## 一、定义 ##

> 又称多表查询，当查询的字段来自于多个表时，就会用到连接查询。

## 二、笛卡尔乘积现象 ##

**什么是笛卡尔积？实质上就是将不同事物之间建立一一对应的映射关系。数据库中指不同记录之间所有映射结果。**

> **表1有m行，表2有n行，结果 = m\*n**

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

--------------------

## 三、分类 ##

**按年代分类：**

sql92标准：仅仅支持内连接

sql99标准：支持内连接+外连接（左外和右外）+交叉连接

--------------------

**按功能分类：**

**内连接**

等值连接

非等值连接

自连接

**外连接**

左外连接

右外连接

全外连接（mysql不支持）

交叉连接

--------------------

## 四、内连接 ##

什么是内连接？

内连接就是两个相同级别的表之间进行联合查询。其产生的结果表包含两个表的部分结果。

--------------------

### **1、**等值连接 ###

**select \* from a\_table a**

**inner join b\_table b**

**on a.a\_id = b.b\_id;   （条件是等值条件）**

> **说明**：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

![70][]

--------------------

### 2、非等值连接 ###

SELECT

salary,grade\_level

FROM  employees e

INNER JOIN job\_grades g

**ON  e.\`salary\`  BETWEEN g.\`lowest\_sal\`  AND g.\`highest\_sal\` ；（非等值条件）**

--------------------

### 3、自连接 ###

自连接就是自己跟自己连接！

其他与内连接相同。

--------------------

## 五、外连接 ##

> **outer join，以某张表为主，取出里面的所有记录，然后每条与另外一张表进行连接，不管能不能匹配上条件，最终都会保留。**
> 
> **能匹配，正确保留；不能匹配，其它表的字段都置空（null）。**

外连接分为两种：是以某张表为主，有主表

**left join：左外连接（左连接），以左表为主表**

**right join：右外连接（右连接），以右表为主表**

--------------------

### 基本格式 ###

> **左表 left/right join 右表 on 左表.字段 = 右表.字段；**

> on表示连接条件：条件字段就是代表相同的业务含义（如my\_student.c\_id和my\_class.id），大多数情况下为两张表中的主外键关系。

--------------------

## 六、联合连接 union ##

什么是联合连接？

union：联合，合并，将多条查询语句的结果合并成一个结果

应用场景：要查询的结果来自于多个表，且多个表没有直接的连接关系，但查询的信息一致。

> **就是把两个查询语句的结果直接拼接到一起！**

> **关键字：union /union all**

直接上才艺~

> **(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )**

> **(select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB )；**

> ### union语句注意事项 ###
> 
>          1.通过union连接的SQL它们分别单独取出的**列数必须相同**；
> 
>          2.不要求合并的表列名称相同时，**以第一个sql 表列名为准**；
> 
>          3.使用union 时，**完全相等的行，将会被合并**，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 剔除重复值；
> 
>          4.被union 连接的sql 子句，**单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；**

--------------------

## 七、自然连接 ##

自然连接：natural join，自然连接，就是**自动匹配连接条件**，系统以字段名字作为匹配模式（同名字段就作为条件，多个同名字段就都作为条件）。  
盗图举个例子：

![70 1][]

--------------------

## **八、交叉连接** ##

交叉连接：cross join，从一张表中循环取出每一条记录，每条记录都去另外一张表进行匹配，

匹配一定保留（没有条件匹配），而连接本身字段就会增加（保留），最终形成的结果叫做：笛卡尔积。

![70 2][]

--------------------

## **九、连接总结与延伸** ##

### 多表连接怎么办？ ###

实质上，多表连接就是两两连接。

如有3个表，表A是主表，表B，表C是此表，我要将根据这个三个表来查询想要的结果。

select A.class, B.sex, avg(C.age) 
    from A 
    left join B
    on A.name = B.name
    left join C 
    on A.name = C.name
    where e.age >=15;
    group by  A.class, B.sex

> **其中表A的列属性为name、class;**
> 
> **表B的列属性为name、sex;**
> 
> **表C的列属性为name、age;**

学东西要学会举一反三？

> **为什么要放在不同的表中，即为什么要把一个信息“肢解”？**
> 
> **原因很简单，避免表结构的冗余，减少表占用的内存。**

--------------------

# 10 子句与连接复杂查询 #

## **子查询** ##

**在 select、where或其他条件语句中出现的查询块，称之为子查询。**

> 1.  出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询
> 2.  嵌套在其他语句内部的select语句成为子查询或内查询
> 3.  外面的语句可以是insert、update、delete、select等，一般select作为外面语句较多
> 4.  外面如果为select语句，则此语句称为外查询或主查询

## 分类 ##

> 按子查询出现的位置：
> 
> 1.  select后面：仅仅支持**标量子查询**
> 2.  from后面：支持**表子查询**
> 3.  where或having后面：支持标量子查询，列子查询，行子查询（较少）
> 4.  exists后面（相关子查询）：支持表子查询
> 
> **按功能、结果集的行列数不同：（重点）**
> 
> 1.  **标量子查询（结果集只有一行一列）**
> 2.  **列子查询（结果集只有一列多行）**
> 3.  **行子查询（结果集有一行多列）**
> 4.  **表子查询（结果集一般为多行多列）**

## 注意事项  ##

>  *  **子查询放在小括号内**
>  *  标量子查询，**一般搭配着单行操作符使用**（> < >= <= = <>）
>  *  列子查询，一般搭配着多行操作符使用（IN、ANY/SOME、ALL）
>  *  **子查询的执行优选于主查询执行**，主查询的条件用到了子查询的结果

话不多说，直接上才艺！

## 一、WHERE条件中的子查询 ##

USE sql_store;
    SELECT *
    FROM customers c
    WHERE customer_id in
    (                # 括号内是子查询
    SELECT DISTINCT o.customer_id
    FROM order_items oi
    JOIN orders o USING(order_id)        # 在子查询中使用了join连接查询
    WHERE product_id = 3
    )

--------------------

## 二、select中的子查询 ##

USE sql_invoicing;
    SELECT 
    invoice_id
    ,invoice_total
    ,(SELECT AVG(invoice_total) FROM sql_invoicing.invoices) AS invoice_avg  #select子句
    ,invoice_total - (SELECT invoice_avg) AS diff  #select子句当作子查询
    from sql_invoicing.invoices i

--------------------

## 三、FROM中的子查询 ##

> **注意：每个派生表都必须有别名。后面还会涉及到视图~当然要放到后面说了，循序渐进。**

SELECT *
    FROM
    (
    	SELECT DISTINCT
    		c.client_id
    		,`name`
    		,(SELECT SUM(invoice_total) FROM sql_invoicing.invoices
    		WHERE client_id = i.client_id
    		) AS total_sales
    		,(SELECT AVG(invoice_total) FROM sql_invoicing.invoices) AS invoice_avg
    		,(SELECT total_sales) - (SELECT invoice_avg) AS diff
    	from sql_invoicing.clients c
    	LEFT JOIN sql_invoicing.invoices i
    	USING(client_id)
    ) AS sales_summary  #每个派生表都必须有别名，注意！建议使用视图来解决主查询过长的问题。
    WHERE total_sales is NOT NULL 
    ;

--------------------

## 四、ALL、SOME、ANY ##

> 注意：
> 
> **当子查询返回结果唯一时**，**可正常搭配四则运算法符号；**
> 
> **但其返回结果与多个时，一般搭配 in 或者not in来使用。**

让我们简明地说下这几个关键字 !

> **ALL关键字：ALL(NUM1, NUM2, ......)，返回括号内的最大数字元素（所有NUM1,NUM2...等），只返回唯一值。**

> **SOME关键字：作用于ALL关键字相似。可简单认为其彼此互相等同。**

> **ANY关键字：ANY等同于IN，这样说能够理解吧~~。**

话不多说，上才艺！

USE sql_invoicing;
    SELECT *
    FROM sql_invoicing.invoices i
    WHERE invoice_total > [ALL|SOME](   #  [ALL|SOME] 二选一
    		SELECT invoice_total
    		FROM sql_invoicing.invoices 
    		WHERE client_id = 3)

USE sql_invoicing;
    SELECT *
    FROM sql_invoicing.invoices i
    WHERE invoice_total = [ANY|IN](  # [ANY|IN]二选一
    		SELECT invoice_total
    		FROM sql_invoicing.invoices 
    		WHERE client_id = 3)

--------------------

## 五、关联子查询 ##

> **什么是关联子查询？即这段子查询与外查询存在相关性，我们称这样子的子查询为关联子查询。** 
> 
> 相关子查询与普通子查询的区别：
> 
> **普通子查询：只执行一次**
> 
> **相关子查询：外查询的每一行都需要执行一次子查询。是不是很绕啊~**
> 
> **你只需要记住，通常在子查询条件里会调用外查询的属性或条件，这也是与普通子查询的显著区别。用多了，你自然就理解了。**
> 
> 因此，数据越多，查询更费力，占用存储资源也就越多。

SELECT *
    FROM invoices m
    WHERE invoice_total >(
    		SELECT AVG(invoice_total)
    		FROM invoices n
    		WHERE n.client_id = m.client_id # 通常在子查询条件里会调用外查询的属性或条件，这也是与普通子查询的显著区别
    		)
    ORDER BY client_id
    ;

--------------------

## 六、exists 运算符 ##

**exists 运算符与IN运算符的区别？**

**先上才艺好吧，多捞。**

**我要查询****clients****表中****client\_id****满足子查询返回结果的所有****行数据****。**

第一种方法IN：这里子查询返回的应该是列子查询，即返回的是一列多行的数据。
    SELECT *
    FROM clients
    WHERE client_id in(
    SELECT DISTINCT client_id 
    FROM invoices 
    );

第二种方法:内连接
    SELECT DISTINCT c.*
    FROM clients c
    JOIN invoices i
    ON c.client_id = i.client_id

> **敲黑板！问题来了！！使用第一种方法IN()，当其所返回的数据量过大时，就会生成一个很大的列表，然后再返还给where ，  
> 此时很浪费空间，肿么办？采用exists()就能够解决这个问题，如下代码。 **

SELECT * 
    FROM clients c
    WHERE EXISTS
    (
    SELECT client_id 
    FROM invoices i
    # 这里之所以用关联子查询，
    #	是因为子句需要返回一组与p相关的布尔值
    #	以便于where接收,并作出条件选择。
    WHERE i.client_id= c.client_id
    )；

> 为什么exists()就能够解决这个问题？**是因为exists（）仅仅返回的是布尔值（满足子句筛选条件的为TRUE，否则为FLASE）。**
> 
> **这个过程并不会返回一大串结果，当外查询WHERE条件语句收到TRUE时，就会在外查询的查询结果中返回符合以子查询为条件的行数据结果。**
> 
> **这样是不是简便很多。**

--------------------

# 11 分页查询（非常重要，后期继续补充） #

> **应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求。**

## 语法格式 ##

select 查询列表
    from 主表
    [join type] join 次表
    on 连接条件
    where 筛选条件
    group by 分组字段
    having 分组后的筛选
    order by 排序字段
    limit offset，size；

> **offset：要显示条目的起始索引（从0开始）**
> 
> **size：要显示的条目个数**

> **注意：limit语句放在查询语句的最后**

**那么问题来了？如何让mysql自动去确定这个offset和size呢？**

手推一下公式：

假设要显示的页数为m，每页的条目数为n

则有以下公式：

> **offset = （m - 1）\* n**
> 
> **size = n**

则此时的查询语句为

select 查询列表
    from 表
    limit （m - 1）* n， n

[70]: /images/20221021/9d9da002457444d1b5de17285016e984.png
[70 1]: /images/20221021/01f27635a50e4c65926d2f810a647672.png
[70 2]: /images/20221021/f21e49616ebc44a1a4f2c2d622b3220f.png