mysql笔记：9. 数据查询

数据库管理系统的一个最重要功能就是数据查询，数据查询不应只是简单返回数据库中存储的数据，还应该根据需要对数据进行筛选，以及确定数据以什么样的格式显示。MySQL提供了功能强大、灵活的语句来实现这些操作。

一、SELECT语句

从数据库表中查询数据的基本语句为SELECT语句，语法如下：

SELECT 属性列表
FROM 表名和视图
{WHERE 条件表达式}
{GROUP BY 属性名}
{HAVING 条件表达式}
{ORDER BY 属性名 ASC | DESC}

所有被使用的子句必须按语法说明中显示的顺序严格地排序。

二、简单查询

1. 查询表所有数据

1. 列出表中所有字段
   在SELECT语句的属性列表中列出查询表的所有字段，从而可以查询表中所有数据。
2. 使用星号通配符
   SELECT语句的属性列表可以为星号*，语法格式：

SELECT * FROM table_name;
SELECT * FROM table_name\G;

在执行SQL语句时，有时返回的数据中字段非常多，无法在CMD窗口的一行全部展示，而如果将字段名称显示在多行，会导致字段下的数据不能和字段名称展示在同一列，显示结果非常混乱。
使用结束符\G，可以将结果以纵向结构显示，在字段非常多时，能让显示结果更加整齐美观。

2. 查询部分数据

在SELECT语句的属性列表中指定要查找的字段名称，可以有针对性的查询结果。

MySQL中的SQL语句是不区分大小写的。因此，SELECT和select是相同的。很多书籍推荐将关键字大写，表名和数据列使用小写。
我认为，应该全部使用小写，并且我在平时的工作中也是这么做的。理由如下：
一、使用大写+小写需要切换输入法，降低开发速度，所有全部小写可以更快的编写SQL语句。
二、时间相同的情况下,单词小写的识别正确率高于大写的正确率，所以小写的SQL语句更容易识别和维护。

3. 计算结果

在查询时，可以根据需要使用算术运算符或逻辑运算符对查询结果进行处理。
例如，表emp中有员工姓名name和当前工资salary，可以查询员工工资和调薪20%以后的工资。

SELECT name, salary, salary + salary*0.2 AS salary_new FROM emp;

4. 列别名

显示查询结果时，选择的列通常是以原表中的列名作为标题。但列名通常比较简短、甚至在一些情况下含义也模糊。为改善查询结果的显示，可以在SELECT语句的列名后使用AS 新列名重新命令该列。

AS：关键字。可有可无。

5. 去除重复项

使用DISTINCT选项可以在查询结果中删除重复项。

6. 表别名

如果要查询的数据表名称较长时使用不太方便，可以为表取一个别名来代替，简化查询SQL。

7. LIMIT限制数据

当数据表中包含大量数据时，可以通过指定显示记录数限制返回结果集中的行数。
LIMIT用法有两种：

1. 指定初始位置
   LIMIT offset, row_count
   offset指定返回的第一行的偏移量。初始行的偏移量是0，不是1。
   row_count指定返回的行数的最大值。
2. 不指定初始位置
   LIMIT row_count

三、WHERE子句

WHERE子句用于给定源表和视图中记录的筛选条件，只能符合筛选条件的记录才能入选结果集。

1. 比较查询条件

在查询条件中使用比较运算符进行比较。

2. BETWEEN AND范围查询

使用BETWEEN AND可以进行范围查询，可以指定范围的开始值和结束值。如果记录的字段值满足指定的范围，则记录被返回。
相反，可以在BETWEEN AND前加关键字NOT，表示指定范围之外的值。

BETWEEN匹配范围中的所有值，包括开始值和结束值。

3. IN查询

使用IN查询时，将所有检索条件放进括号里，不同条件用逗号分隔，只要满足条件范围内的任意一个值即为匹配项。
相反，可以使用关键字NOT检索不在条件范围内的记录。

4. LIKE匹配

使用LIKE可以匹配字符串是否相等。

[NOT] LIKE '字符串'

字符串参数可以是一个完整的字符串，也可以是包含%或_的通配符。

• %：匹配任意长度的字符串，可以是空字符串。
• _：匹配任意单个字符。

5. 空数据查询

数据表中的某些列可能包含空值，它不同于0，也不同于空字符串。查询时可以使用IS NULL子句。
相反，可以使用IS NOT NULL查询不为空的记录。

6. AND多条件查询

AND关键字可以用来联合多个条件进行查询，只能同时满足所有条件的记录才会被查询出来。

7. OR多条件查询

OR关键字也可以用来联合多个条件进行查询，只要满足任意一个条件，记录就会被查询出来。

四、操作查询结果

1. 排序

使用ORDER BY可以对记录进行排序。

ORDER BY column_name [ASC | DESC]

默认情况下，按照ASC方式进行排序。
如果要排序的字段值是空值时，按升序排序时，含空值的记录将最先显示，可以理解为空值是该字段的最小值；而按降序排序时，字段为空值的记录将最后显示。

2. 分组

分组查询是对数据按照某个或多个字段进行分组。基本语法：

[GROUP BY column_name] [HAVING <条件表达式>]

• HAVING子句指定分组显示时需要满足的限定条件
• GROUP BY子句通常和集合函数一起使用。

五、聚合函数统计

有时候并不需要返回实际表中的数据，只是对数据进行统计。

函数	作用
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

COUNT()函数统计查询结果中包含的记录行的总数。其使用有三种：

• COUNT(1)：统计表的所有记录数，包含字段为NULL的记录
• COUNT(*)：统计表的所有记录数，无论某列是否为空值
• COUNT(column_name)：计算指定列的总行数，忽略空值的行

一般情况下，COUNT(1)的执行效率要优于COUNT(*)。因为COUNT(1)不需要遍历整个表，因为它只需要统计满足条件的行数，而COUNT(*)要扫描整个表。
另外，MyISAM引擎不支持事务，它的锁是表级锁，在该引擎内使用COUNT(*)更快。而InnoDB支持事务，支持行级锁，使用COUNT(1)更快。

六、多表嵌套

多表嵌套查询又称为子查询，在SELECT子句中先计算子查询，其结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

1. 比较运算符

嵌套查询中可以使用的比较运算符有<、<=、=、>=、!=等。

2. IN

使用IN关键字嵌套查询时，内层查询语句仅仅返回一个数据列。这个列里的值将提供给外层查询语句进行比较。

3. ANY

通常使用比较运算符来连接ANY得到的结果，用于比较某一列的值是否全部大于 ANY后面子查询中的最小值或小于ANY后面嵌套查询中的最大值。

4. ALL

使用ALL关键字时需要同时满足所有内层查询条件。

5. SOME

SOME的用法与ANY类似，但意义不同。SOME通常用于比较满足查询结果中的任意一个值，而ANY要满足所有值才可以。
在SOME前面使用=时，等价于IN的用途。

6. EXISTS

EXISTS关键字是存在的意思，应用于嵌套查询中，只要嵌套查询返回的结果为空，返回结果就是TRUE，此时外层查询语句将进行查询；否则就是返回FALSE，外层语句将不进行查询。
NOT EXISTS与EXISTS使用方法相同，但结果相反。

EXIST和NOT EXIST的结果只取决于是否会返回行，而不取决于这些行的内容，所以子查询输入列表通常是无关紧要的。

七、多表内连接

连接是关系数据库模型的主要特点，连接查询是关系数据库中最主要的查询，主要包括内连接、外连接等。
语法格式如下：

SELECT column_name1, column_name2, ...
FROM table_name1 INNER JOIN table_name2
ON conditions;

八、多表外连接

几乎所有的查询语句，查询结果全部都是需要符合条件才能查询出来的。换句话说，如果执行查询语句后没有符合条件的结果，那么在结果中就不会有任何记录。外连接查询则与之相反，通过外连接查询，可以在查询出符合条件的结果后显示出某张表中不符合条件的数据。

SELECT column_name1, column_name2, ...
FROM table_name1 LEFT|RIGHT|FULL OUTER JOIN table_name2
ON conditions;

• LEFT OUTER JOIN（左连接）：左外连接，使用左外连接时得到的查询结果中，除了符合条件的查询结果外，还要加上左表中余下的数据。
• RIGHT OUTER JOIN（右连接）：右外连接，使用右外连接时得到的查询结果中，除了符合条件的查询结果外，还要加上右表中余下的数据。
• FULL OUTER JOIN（全连接）：全外连接，使用全外连接时得到的查询结果中，除了符合条件的查询结果外，还要加上左表和右表中余下的数据。

九、排序函数

在MySQL中，可以对返回的查询结果排序，排序函数提供了一种按升序的方式组织输出结果集。

1. ROW_NUMBER()

ROW_NUMBER()函数为每条记录增添递增的顺序数值序号，即便存在相同的值也是递增序号。
基本语法：

ROW_NUMBER() OVER (
    [PARTITION BY <expression_list>] 
    ORDER BY <expression_list>
)

• PARTITION BY：可选项，用于将结果集分隔成不同的分区。每个分区都会从1重新开始编号。
• ORDER BY：必选项，用于指定结果集的排序顺序。

2. RANK()

如果两个或多个行与一个排名关联，则每个关联行将得到相同的排名。RANK()函数并不总返回连续整数。

3. DENSE_RANK()

DENSE_RANK()函数为结果集或分区中的每一行分配排名，在排名中没有任何间断。行的排名等于所讨论行之前的所有排名数加一。即相同的数据序号相同，接下来顺序递增。

DENSE_RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
) 

4. NTILE()

NTILE(N)函数用来将查询结果中的记录分为N组。各个组有编号，编号从1开始。对于每一行，NTILE()函数将返回此行所属的组的编号。

十、正则表达式

正则表达式是一种文本模式，包括普通字符和特殊字符（称为元字符）。正则表达式的查询能力比普通字符的查询能力更强大，而且更加灵活，因此可以应用于非常复杂的数据查询。
语法如下：

属性名 REGEXP '匹配方式'

匹配方式 参数中有很多模式匹配字符，它们分别表示不同的意思。

字符	描述
^	匹配字符串开始的位置
$	匹配字符串结束的位置
.	匹配字符串中的任意一个字符，包括回车和换行
[集合]	匹配集合中的任何一个字符
[^集合]	匹配除了集合以外的任意一个字符
*	代表多个该符号之前的字符，包括0和1个
+	代表多个该符号之前的字符，包括1个
字符串{n}	字符串出现n次
字符串{m, n}	字符串至少出现m次，最多n次

十一、补充：怎样复制表

实际工作中，我们偶尔会遇到复制一个表及其数据的情况，应该怎样操作呢？

1. show create table

mysql> SHOW CREATE TABLE table_name;

使用show create table语句可以查看一个表的建表语句。复制出来，并修改其中的表名即得到新表的建表语句。

该方式只能复制表结构，不能同步表数据。

# 两个表完全一样的情况下，同步表table_name1的数据到table_name2
mysql> INSERT INTO table_name2 SELECT * FROM table_name1;

2. create table like

mysql> CREATE TABLE new_table_name LIKE table_name;

使用create table like也可以拷贝旧表结构，同样也不能同步数据。

如需要同步表数据，使用insert into select语句执行一次即可。

3. create table select

mysql> CREATE TABLE new_table_name SELECT * FROM table_name;

该语句创建了一个字段和原表一样的新表，并且将数据页同步了过来。但是这种方式会丢失原表的自增、主键、索引等信息，需要额外处理。