MySQL数据库操作补坑(七)查询语句

「爱情、让人受尽委屈。」 2023-01-10 11:45 439阅读 0赞

# 基本查询语句 #

基本语法形式：

SELECT 属性列表
    		FROM 表名和视图列表
    		[ WHERE 条件表达式 1 ]
    		[ GROUP BY 属性名1 [ HAVING 条件表达式2 ]]
    		[ ORDER BY 属性名2 [ASC | DESC ]]

*  属性列表：需要查询的字段名
 *  表名和视图列表：在哪个 表/视图 查询数据
 *  条件表达式1：指定查询条件
 *  属性名1：参数指定该字段中的数据进行分组
 *  条件表达式2：参数表示满足该表达式的数据才能输出
 *  属性名2：参数指定按该字段中的数据进行排序，排序方式按DESC或ASC决定（降序/升序，默认升序）
 *  GROUP BY：如果`GROUP BY`子句后带着`HAVING`关键字，那么只有满足`条件表达式2`中指定的条件才能够输出。通常和COUNT()、SUM()等聚合函数一起使用。
 *  ORDER BY：按照属性名2指定的字段进行排序，排序方式由

# 单表查询 #

## 1）、列出表的所有字段 ##

`SELECT 字段名1,字段名2，...字段名n FROM 表名`

## 2）、使用\*查询所有字段 ##

`SELECT * FROM 表名`

## 3）、查询指定字段 ##

`SELECT 字段名 FROM 表名`

## 4）、查询指定记录 ##

`WHERE 条件表达式`

<table> 
 <thead> 
  <tr> 
   <th align="center">查询条件</th> 
   <th align="center">符号或关键字</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">比较</td> 
   <td align="center">=、&lt;、&lt;=、&gt;、&gt;=、!=、&lt;&gt;、!&gt;、!&lt;</td> 
  </tr> 
  <tr> 
   <td align="center">指定范围</td> 
   <td align="center">BETWEEN AND、NOT BETWEEN AND</td> 
  </tr> 
  <tr> 
   <td align="center">指定集合</td> 
   <td align="center">IN、NOT IN</td> 
  </tr> 
  <tr> 
   <td align="center">匹配字符</td> 
   <td align="center">LIKE、NOT LIKE</td> 
  </tr> 
  <tr> 
   <td align="center">是否为空值</td> 
   <td align="center">IS NULL、IS NOT NULL</td> 
  </tr> 
  <tr> 
   <td align="center">多个条件查询</td> 
   <td align="center">AND、OR</td> 
  </tr> 
 </tbody> 
</table>

*  <>：表示不等于，作用等价于`!=`
 *  !>：表示不大于，等价于`<=`
 *  !<：表示不小于，等价于`>=`
 *  BETWEEN AND：指定了某字段的取值范围
 *  IN：指定了某字段的取值的集合
 *  IS NULL：用来判断某字段的取值是否为空
 *  AND和OR：用来连接多个查询条件

## 5）、IN关键字查询 ##

IN关键字可以判断某个字段的值是否在指定集合中。

`NOT IN (元素1,元素2,...,元素n)`

*  NOT：可选参数，加上表示不在集合内满足条件
 *  元素n：集合中的元素，各元素间要用逗号隔开，字符型元素要加上单引号

## 6）、带 BETWEEN ADN 的范围查询 ##

`[NOT] BETWEEN 取值1 AND 取值2`

*  取值1：起始范围
 *  取值2：结束范围

> 场景举例：年龄段、分数段、工资水平等

## 7）、LIKE 字符匹配查询 ##

`[NOT] LIKE '字符串'`

*  字符串：该字符串必须加上单引号或者双引号。参数的值可以是一个完整的字符串，也可以是包含百分号(%)或者下划线(\_)的通配字符
    
     *  %：可以代表任意长度的字符串，长度可以为0。例如，b%k表示以字母b开头，k结尾的任意长度字符串
     *  \_：只能表示单个字符。注：一个汉字占两个字符。

> 当通配符存在时，=不能代替LIKE

## 8）、查询空值 ##

`IS [NOT] NULL`

## 9）、带AND的多条件查询 ##

`条件表达式1 AND 条件表达式2 [...AND 条件表达式n]`

可以使用多个AND链接更多表达式

## 10）、带OR的多条件查询 ##

`条件表达式1 OR 条件表达式2 [...OR 条件表达式n]`

> 当与AND一起使用时，AND比OR先运算。可通过给OR表达式加上(…OR…)来避免该情况

## 11）、查询结果不重复 ##

`SELECT DISTINCT 属性名`

## 12）、对查询结果排序 ##

`ORDER BY 属性名 [ASC|DESC]`

*  ASC：升序
 *  DESC：降序

> 存在一条记录该属性为空值时，该条记录会显示为第一个。

## 13）、分组查询 ##

`GROUP BY 属性名 [HAVING 条件表达式][WITH ROLLUP]`

*  属性名：按照字段的值惊醒分组
 *  HAVING 条件表达式：用来限制分组后的显示
 *  WITH ROLLUP：会在所有记录的最后加上一条记录

> 单独使用GROUP\_BY只显示每个分组的一条记录

### 13.1）、与GROUP\_CONCAT()一起用 ###

每个分组中的指定字段都会显示出来

`SELECT 属性名1,GROUP_CONCAT(属性名2) FROM 表名 GROUP BY 属性名1`

### 13.2）、与集合函数一起使用 ###

可以通过集合函数计算分组中的总记录、最大值、最小值

`SELECT 属性名1,COUNT(属性名1) FROM 表名 GROUP BY 属性名1`

> 集合函数还有COUNT()、SUNM()、AVG()、MAX()、MIN()

### 13.3）、与HAVING一起使用 ###

可以限制输出结果。

`SELECT 属性名1,COUNT(属性名1) FROM 表名 GROUP BY 属性名1 HAVING COUNT(sex)>=3;`

> WHERE 条件表达式作用于表或者视图，是表和视图的查询条件。HAVING 条件表达式作用于分组后的记录，用于选择满足条件的组。

### 13.4）、按多个字段进行分组 ###

`SELECT * FROM 表名 GROUP BY 属性名1,属性名2;`

### 13.5）、与WITH ROLLUP一起使用 ###

`SELECT 属性名1,COUNT(属性名1) FROM 表名 GROUP BY 属性名1 WITH ROLLUP`

会在所有记录的最后加一条记录，该记录是上面所有记录的总和。

## 14）、用LIMIT限制查询结果的数量 ##

**不指定初始位置**

从第一条记录开始显示，显示条数由LIMIT关键字指定

`LIMT 记录数`

*  记录数：表示显示记录的条数。如果记录数的值小于查询结果的记录数，将从第一条记录开始，显示指定条数的记录。如果大于查询结果的总记录数，数据库系统会直接显示查询出来的所有记录数。

**指定初始位置**

LIMIT关键字可以指定从哪条记录开始显示，并且可以指定显示多少条记录

`LIMIT 初始位置,记录数`

*  初始位置：指定从哪条记录开始显示
 *  记录数：表示显示记录的条数

> 第一条记录的位置是0，第二条是1

# 使用集合函数查询 #

## 1）、COUNT()函数 ##

用来统计记录的条数

`SELECT COUNT(*) FROM 表名`

## 2）、SUM()函数 ##

求和函数，求出某个字段值的总和

`SELECT 属性名1,SUM(属性名2) FROM 表名`

> 通常和 GROUP BY 关键字一起使用，这样可以计算出不同分组中某个字段取值的总和。
> 
> SUM()函数只能计算数值类型。计算字符串类型时为0。

## 3）、AVG()函数 ##

求出表中某个字段的平均值

`SELECT AVG(属性名) FROM 表名;`

> 通常与 GROUP BY 一起使用，来计算每个分组的平均值。
> 
> 同样不能求字符串的平均值，结果为0

## 4）、MAX函数 ##

求出表中某个字段取值的最大值

`SELECT MAX(属性名) FROM 表名;`

> MySQL中，a最小，z最大。MAX函数是根据ASCII码进行计算的

## 5）、MIN函数 ##

求出表中某个字段的最小值

`SELECT MIN(属性名) FROM 表名;`

# 连接查询 #

将两个或两个以上的表按照某个条件连接起来，从中选择需要的数据。是同时查询两个或两个以上表使用的。包括内连接和外连接。

## 内连接 ##

当表中存在表示相同意义的字段时，可以通过该字段来连接这两个表。当该字段的值相等时（可以是父表的主键和子表的外键），就查询出该记录。

`SELECT 属性名1,属性名2 FROM 表1,表2 WHERE 属性名1=属性名2;`

> 只有表中有意义相同的字段时才能进行连接

## 外连接查询 ##

外连接查询也需要通过指定字段来进行链接。包括左链接和右链接

`SELECT 属性名列表 FROM 表名1 LEFT|RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2;`

*  属性名列表：要查询的字段名称，可以来自不同的表
 *  表名1，表名2：表示将这两个表进行外链接
 *  LEFT：进行左连接查询
 *  RIGHT：右链接查询
 *  ON：后面接的连接条件

### 左链接查询 ###

可以查询出表1的所有记录，表2只能查询出匹配的记录

`SELECT 属性名列表 FROM 表名1 LEFT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2;`

### 右链接查询 ###

可以查出表2的记录，而表1所指的表中，只能查询出匹配的记录

`SELECT 属性名1，属性名2 FROM 表名1 RIGHT JOIN 表名2 ON 表名1.属性名1=表名2.属性名2`

### 复合条件连接查询 ###

其实就是加上 `GROUP BY` `ORDER BY`等条件语句查询

# 子查询 #

将一个查询语句嵌套在另一个查询语句中，内层嵌套语句的查询结果可以为外层查询语句提供查询条件。通过子查询，可以实现多表之间的查询。子查询中可能包括 `IN、NOT IN、ANY、ALL、EXISTS、NOT EXTISTS`等关键字。子查询还可能包含比较运算符

## IN ##

判断一个查询语句的条件可是否在另一个SELECT语句的查询结果中。

`SELECT * FROM 表名 WHERE 属性名 IN (SELECT 属性名 FROM 表名);`

## EXISTS ##

内层查询语句返回真假值，为真执行外层查询；为假不执行外层查询，或查询不出任何结果。

`SELECT * FROM 表名 WHERE EXISTS (SELECT 属性名1 FROM 表名 WHERE 属性名1=属性名2);`

## ANY ##

满足内层查询语句任何一个，就执行外层查询语句

`SELECT * FROM 表名1 WHERE 属性名1>=ANY(SELECT 属性名1 FROM 表名2);`

## ALL ##

满足内层查询语句返回的所有的结果，才执行外层查询语句。

`SELECT * FROM 表名1 WHERE 属性名1>=ALL(SELECT 属性名1 FROM 表名2);`

# 合并查询结果 #

将多个SELECT语句的查询结果合并显示

SELECT 语句1 UNION|UNION ALL
    	UNION|UNION ALL
    SELECT 语句2 UNION|UNION ALL
    	UNION|UNION ALL
    SELECT 语句n;

*  UNION：将所有的查询结果合并在一起，并去掉相同的记录
 *  UNION ALL：简单的合并在一起

# 为表和字段取别名 #

## 为表取别名 ##

语法：`表名 标的别名`

举例：

SELECT * FROM test t
    	WHERE t.t_id=100;

这个t就是test表的别名

## 为字段取别名 ##

语法：`属性名 [AS] 别名`

*  属性名：字段原来的名称
 *  别名：字段新的名称
 *  AS：可有可无，作用都一样

> 为字段取名必须保证该表中没有其他字段与该别名相同。字段的别名知识现实的时候代替字段本来的名称。在查询条件中是不能使用新定义的别名的。在条件表达式中不能使用字段的别名。

> 表的别名不能与该数据库的其他表同名。字段的别名不能与该标的其他字段同名。在条件表达式中不能使用字段的别名。

# 正则表达式查询 #

语法：`属性名 REGEXP '匹配方式'`

*  属性名：查询字段的名称
 *  匹配方式：以哪种方式进行匹查询

<table> 
 <thead> 
  <tr> 
   <th align="center">正则表达式的模式字符</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">^</td> 
   <td align="center">匹配字符串开始的部分</td> 
  </tr> 
  <tr> 
   <td align="center">$</td> 
   <td align="center">匹配字符串结束的部分</td> 
  </tr> 
  <tr> 
   <td align="center">.</td> 
   <td align="center">代表字符串中任意一个字符，包括回车和换行</td> 
  </tr> 
  <tr> 
   <td align="center">[字符集合]</td> 
   <td align="center">匹配"字符集合"中的任何一个字符</td> 
  </tr> 
  <tr> 
   <td align="center">[^字符集合]</td> 
   <td align="center">匹配除了"字符集合"中的任何一个字符</td> 
  </tr> 
  <tr> 
   <td align="center">S1|S2|S3</td> 
   <td align="center">匹配S1、S2和S3中的任意一个字符串</td> 
  </tr> 
  <tr> 
   <td align="center">*</td> 
   <td align="center">代表多个该符号之前的字符，包括0个和1个</td> 
  </tr> 
  <tr> 
   <td align="center">+</td> 
   <td align="center">代表多个该符号之前的字符，包括1个</td> 
  </tr> 
  <tr> 
   <td align="center">字符串{N}</td> 
   <td align="center">字符串出现N次</td> 
  </tr> 
  <tr> 
   <td align="center">字符串{M,N}</td> 
   <td align="center">字符串出现至少M次，最多N次</td> 
  </tr> 
 </tbody> 
</table>

**查询以特定字符开始的记录**

`SELECT * FROM info WHERE name REGEXP '^L'`

**查询以特定字符或字符串结尾的记录**

`SELECT * FROM info WHERE name REGEXP 'c$'`

**用符号"."来替代字符串中的任意一个字符**

`SELECT * FROM info WHERE name REGEXP '^L..y$'`

**匹配指定字符中的任意一个**

`SELECT * FROM info WHERE name REGEXP '[ceo]'`

**匹配指定字符以外的字符**

`SELECT * FROM info WHERE name REGEXP '[^字符集合]'`

**匹配指定字符串**

`SELECT * FROM info WHERE name REGEXP 'ic'`