【MySQL】MySQL学习笔记（七）查询数据

1 概述

  MySQL从数据表中查询数据的基本语句为SELECT语句，SELECT语句的基本格式是：

select *|column,... from table,... 
    [where condition
        [group by condition]
        [having condition]
        [order by condition]
        [limit [number],count]
    ]
;

2 单表查询

  单表查询是指从一张数据表中查询所需的数据

2.1 基本查询

• 查询所有字段：select * from table_name;
• 查询指定字段：select column_name,... from table_name;
• 查询指定记录：select column_name,... from table_name where condition;

表2-1 WHERE条件判断符

操作符	说明
=	相等
<>,!=	不等于
<	小于
<=	小于等于
>	大于
>=	大于等于
BETWEEN	位于两值之间

- 查询空值：在SELECT语句中使用IS NULL子句来查询某字段内容为空的记录：select column_name,... from table_name where column is [not] null;

2.2 范围查询

• 带IN关键字的查询，用来查询满足指定范围内的条件的记录，使用IN操作符，将所有检索条件用括号括起来，检索条件之间用逗号分隔开，只要满足条件范围内的一个值即为匹配项：select column_name,... from table_name where column_name [not] in (value,...);
• 带BETWEEN AND的范围查询，用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，如果字段值满足指定的范围查询条件，则这些记录被返回：select column_name,... from table_name where column_name [not] between min and max;

2.3 字符匹配查询

• 百分号通配符%，匹配任意长度的字符，甚至包括0字符：select column_name from table_name where column like '[other char]%[other char]';
• 下划线通配符_，一次只能匹配任意一个字符：select column_name from table_name where column like '[other char]_[other char]';

2.4 多条件查询

• 带AND的多条件查询，MySQL在WHERE子句中使用AND操作符限定只有满足所有查询条件的记录才会被返回，可以使用AND连接两个甚至多个查询条件，多个条件表达式之间用AND分开：select column_name,... from table_name where condition and condition...;
• 带OR的多条件查询，MySQL在WHERE子句中使用OR操作符限定满足任意查询条件的记录就会被返回，可以使用OR连接两个甚至多个查询条件，多个条件表达式之间用OR分开：select column_name,... from table_name where condition or condition...;

2.5 查询结果处理

• 查询结果不重复——在SELECT语句中，使用DISTINCT关键字指示MySQL消除重复的记录值：select dinstinct column_name,... from table_name;
• 查询结果排序——MySQL使用ORDER BY子句对查询的结果进行排序：select column_name,... from table_name order by column_name [ASC|DESC],...;，DESC代表降序排序，默认是增序。
• 分组查询——MySQL使用GROUP BY关键字对数据进行分组：[group by column_name,...] [having condition]，后面的having子句用于过滤分组
  
  • 在GROUP BY子句末尾使用WITH ROLLUP，用于计算查询出的所有记录的总和。
• 使用LIMIT限制查询结果的数量——返回有限记录条数：select column_name... from table_name limit offset,count;，表示的是从offset开始的count条记录返回。

3 连接查询

3.1 内连接

• 内连接(INNER JOIN)使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新纪录
• 内部连接是使用比较运算符比较要连接列中的值的连接，分为等值连接和不等值连接两种查询方式。
• 等值连接：指的是在连接条件中使用等号(=)运算符比较被连接列的列值，其查询结果中列出了被连接表中所有的列，包括重复的列，其使用语法如下：

select 列
from 表1 [inner] join 表2
on 表1.列=表2.列;

• 不等值连接：指的是在连接条件中使用除等于(=)运算符外的其他比较运算符比较背连接的列值，其使用语法如下：

select 列
from 表1 [inner] join 表2
on 表1.列<>表2.列;

图3-1 内连接示意图

• 如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接查询时一种特殊的内连接，它是指互相连接的表在物理上为同一张表，但可以在逻辑上分为两张表。
• 自连接是指使用表的别名实现表与其自身进行连接，用于实现表内数据的相互比较，其使用语法如下：

select column
from table A, table B
where A.column = B.column;

3.2 外连接查询

• 外连接查询将查询多个表中相关联的行，包括左外链接、右外连接
  
  • 左链接是指将左表中的所有数据分别与右表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括左表中不符合条件的数据，并在右表的相应列中添加NULL值
  • 右链接是指将右表中的所有数据分别与左表中的每条数据进行连接组合，返回的结果除内连接的数据外，还包括右表中不符合条件的数据，并在左表的相应列中添加NULL值
• 左连接(LEFT JOIN)返回包括左表中的所有记录和右表中连接字段相等的记录，select column_name,... from table_name left outer join table_name on condition
• 右连接(RIGHT JOIN)返回包括右表中的所有记录和左表中连接字段相等的记录,select column_name,... from table_name right outer join table_name on condition
  
  图3-2 左连接示意图
  
  图3-3 右连接示意图

注：图3-1、图3-2、图3-3 引用自菜鸟教程

4 子查询

  子查询指一个查询语句嵌套在另一个查询语句内部的查询，在SELECT子句中先计算子查询，子查询的结果作为外层另一个查询的过滤条件，查询可以基于一个表或多个表，常用的操作符有：ANY、ALL、IN、EXISTS，子查询可以添加到SELECT、UPDATE和DELETE语句中，而且可以进行多层嵌套，在子查询中也可以使用比较运算符：<、<=、>、>=、!= 等。

• 在子查询中使用ANY或ALL运算符：
  
  • ANY运算符表示与子查询中的每个值都进行比较，在比较过程中，如果有一个比较值为真，则整个子查询结果集的比较值就为真
  • ALL运算符表示与子查询中的每个值都进行比较，在比较过程中，必须所有比较值为真，整个子查询结果集的比较值才为真

表4-1 比较运算符与ALL或ANY连用时的取值情况

比较运算符	修饰符	所取子查询结果中的值
>、>=、!<	ALL	最大值
	ANY	最小值
=	ANY	取所有值，相当于IN
<>	ANY	相当于NOT IN
<、<=、!>	ALL	最小值
	ANY	最大值

• 带ANY、SOME关键字的子查询：表示满足其中任一条件，他们允许创建爱你一个表达式对子查询的返回值列表进行标胶，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件，select column_name,... from table_name where column_name 比较运算符 ANY (subquery);
• 带ALL关键字的子查询：使用ALL时需要同时满足所有内层查询的条件，select column_name,... from table_name where column_name 比较运算符 ALL (subquery);
• 带EXISTS关键字的子查询：EXISTS关键字后面的参数时一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；反之，EXISTS返回的结果是false，此时外层语句将不进行查询，select column_name,... from table_name where [not] exists subquery;
• 带IN关键字的子查询：IN关键字进行子查询时，内层查询语句仅仅返回一个数据列，这个数据列里的值将提供给外层查询语句进行比较操作，select column_name,... from table_name where column_name [not] in (subquery);

5 合并查询结果

  利用UNION关键字，可以给出多条SELECT语句，并将它们的结果组合成单个结果集。合并时，两个表对应的列数和数据类型必须相同，各个SELECT语句之间使用UNION或 UNION ALL关键字分隔，UNION执行的时候删除重复的记录，所有返回的行都是唯一的；使用UNION ALL的作用是不删除重复行也不对结果进行自动排序，其基本语法格式为：

select column_name,... from table
union [all]
select column_name,... from table
...;

6 别名

6.1 为表取别名

• 当表名字很长或者执行一些特殊查询时，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名代替表原来的名称，为表取别名的基本语法格式为：table_name [as] other_name
• MySQL可以同时为多个表取别名，而且表别名可以放在不同的位置，如WHERE子句、SELECT子句、ON子句以及ORDER BY子句等。
• 注意：为表取别名时，要保证不能与数据库中的其他表的名称冲突

6.2 为字段取别名

• MySQL可以指定列别名，替换字段或表达式，为字段取别名的基本语法格式为：coloum_name [as] other_name

7 使用正则表达式查询

  正则表达式通常被用来检索或替换那些符合某个模式的文本内容，根据指定的匹配模式匹配文本中符合要求的特殊字符串，正则表达式强大而且灵活，可以应用于非常复杂的查询。MySQL中使用REGEXP关键字指定正则表达式的字符匹配模式。

表 正则表达式常用字符匹配列表

选项	说明	例子	说明
^	匹配文本的开始字符	‘^b’	匹配以字母b开头的字符串
$	匹配文本的结束字符	‘d$’	匹配以d结尾的字符串
.	匹配任何单个字符	‘b.t’	匹配任何b和t之间有一个的字符
*	匹配零个或多个在它前面的字符	‘f*n’	匹配字符n前面有任意个字符f
+	匹配前面的字符1次或多次	‘ba+’	匹配以b开头后面紧跟至少有一个a
<字符串>	匹配包含指定的字符串的文本	‘fa’	含有fa的字符串
[字符集合]	匹配字符集合中的任何一个字符	‘[xz]’	匹配x或者z
[^]	匹配不在括号中的任何字符	‘[^abc]’	匹配任何不包含a、b、c的字符串
字符串{n,}	匹配前面的字符串至少n次	b{2}	匹配字符串中含有2个或更多的b
字符串{n,m}	匹配前面的字符串至少n次，至多m次	b{2,4}	匹配最少2个，最多4个b

• 查询以特定字符或字符串开头的记录：select column_name,... from table_name where column_name REGEXP '^字符或字符串';
• 查询以特定字符或字符串结尾的记录：select column_name,... from table_name where column_name REGEXP '字符或字符串$';
• 用符号”.”来代替字符串中的任意一个字符：select column_name,... from table_name where column_name REGEXP '字符或字符串.字符或字符串';
• 使用”*”和”+”来匹配多个字符：select column_name,... from table_name where column_name REGEXP '字符或字符串[*|+]字符或字符串';
• 匹配包含指定字符串：select column_name,... from table_name where column_name REGEXP '字符或字符串|字符或字符串';
• 匹配指定字符中的任意一个：select column_name,... from table_name where column_name REGEXP '[字符或字符串]';，连续的字符也可以用”-“来进行集合描述，例如[a-z]、[0-9]等
• 匹配指定字符以外的字符：select column_name,... from table_name where column_name REGEXP '[^字符或字符串]';
• 匹配指定字符串出现次数：select column_name,... from table_name where column_name REGEXP '字符或字符串{n,m}';