MySQL数据查询语句

MySQL数据查询语句

MySQL 表单查询是指从一张表的数据中查询所需的数据，主要有查询所有字段、查询指定字段、查询指定记录、查询空值、多条件的查询、对查询结果进行排序分组等。

查询结构

SELECT
{* | <字段列名>}
FROM <表 1>, <表 2>
xxx JOIN 多表联查 on ...
[WHERE <表达式>]
[GROUP BY <group by definition>]
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]

其中，各条子句的含义如下：

• {*|<字段列名>}包含星号通配符的字段列表，表示查询的字段，其中字段列至少包含一个字段名称，如果要查询多个字段，多个字段之间要用逗号隔开，最后一个字段后不要加逗号。
• FROM <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。
• xxx JOIN 多表联查 on ... ,内连接,外连接…
• WHERE , 限定查询行必须满足的查询条件。
• GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。
• [ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC）。
• [LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。

ALL: 查询表中的全部内容

在 SELECT 语句中使用星号“*”通配符查询所有字段。

SELECT 查询记录最简单的形式是从一个表中检索所有记录，实现的方法是使用星号“*”通配符指定查找所有列的名称，语法格式如下：

SELECT * FROM 表名;

【实例】从 thtf_energy_test 表中检索所有字段的数据，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM thtf_energy_test;
+--------+---------+--------+
| E_CODE | E_VALUE | E_TYPE |
+--------+---------+--------+
| 上海   | 18.88   | 0      |
| 北京   | 28.50   | 0      |
| 北京   | 23.51   | 1      |
| 北京   | 28.12   | 2      |
| 北京   | 12.30   | 0      |
| 上海   | 16.66   | 1      |
| 北京   | 15.46   | 1      |
| 上海   | 19.99   | 0      |
| 上海   | 10.05   | 0      |
+--------+---------+--------+
9 rows in set (0.04 sec)

由执行结果可知，使用星号“*”通配符时，将返回所有列，数据列按照创建表时的顺序显示。

注意：一般情况下，除非需要使用表中所有的字段数据，否则最好不要使用通配符“*”。使用通配符虽然可以节省输入查询语句的时间，但是获取不需要的列数据通常会降低查询和所使用的应用程序的效率。通配符的优势是，当不知道所需列的名称时，可以通过通配符获取它们。

COLUMN: 指定列查询

使用 SELECT 声明可以获取多个字段下的数据，只需要在关键字 SELECT 后面指定要查找的字段名称，不同字段名称之间用逗号“，”分隔开，最后一个字段后面不需要加逗号，语法格式如下：

SELECT <字段名1>,<字段名2>,…,<字段名n> FROM <表名>;

【实例】使用字段的方式查询 thtf_energy_test 表中的所有数据，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT E_CODE,E_VALUE,E_TYPE FROM thtf_energy_test;
+--------+---------+--------+
| E_CODE | E_VALUE | E_TYPE |
+--------+---------+--------+
| 上海   | 18.88   | 0      |
| 北京   | 28.50   | 0      |
| 北京   | 23.51   | 1      |
| 北京   | 28.12   | 2      |
| 北京   | 12.30   | 0      |
| 上海   | 16.66   | 1      |
| 北京   | 15.46   | 1      |
| 上海   | 19.99   | 0      |
| 上海   | 10.05   | 0      |
+--------+---------+--------+
9 rows in set (0.05 sec)

注意：使用“*”可以返回所有列的数值，但若不需要返回所有列的值，为了提高效率，一般采用 SELECT 字段名列表的形式。

DISTINCT：过滤重复数据

在使用 MySQL SELECT 语句查询数据的时候返回的是所有匹配的行。

mysql> SELECT age FROM tb_students_info;
+------+
| age  |
+------+
|   25 |
|   23 |
|   23 |
|   22 |
|   24 |
|   21 |
|   22 |
|   23 |
|   22 |
|   23 |
+------+
10 rows in set (0.00 sec)

可以看到查询结果返回了 10 条记录，其中有一些重复的 age 值，有时出于对数据分析的要求，需要消除重复的记录值。这时候就需要用到 DISTINCT 关键字指示 MySQL 消除重复的记录值，语法格式为：

SELECT DISTINCT <字段名> FROM <表名>;

【实例】查询 tb_students_info 表中 age 字段的值，返回 age 字段的值且不得重复，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT  DISTINCT age
    -> FROM tb_students_info;
+------+
| age  |
+------+
|   25 |
|   23 |
|   22 |
|   24 |
|   21 |
+------+
5 rows in set (0.11 sec)

由运行结果可以看到，这次查询结果只返回了5条记录的 age 值，且没有重复的值。

AS：设置列或表的别名

在使用 MySQL 查询时，当表名很长或者执行一些特殊查询的时候，为了方便操作或者需要多次使用相同的表时，可以为表指定别名，用这个别名代替表原来的名称。

为列取别名的基本语法格式为：

<列名> [AS] <列别名>

其中，各子句的语法含义如下：

• <列名>：为表中字段定义的名称。
• <列别名>：字段新的名称。
• AS：关键字为可选参数。

【实例】查询 tb_students_info 表，为 name 取别名 student_name，为 age 取别名student_age，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name AS student_name,
    -> age AS student_age
    -> FROM tb_students_info;
+--------------+-------------+
| student_name | student_age |
+--------------+-------------+
| Dany         |          25 |
| Green        |          23 |
| Henry        |          23 |
| Jane         |          22 |
| Jim          |          24 |
| John         |          21 |
| Lily         |          22 |
| Susan        |          23 |
| Thomas       |          22 |
| Tom          |          23 |
+--------------+-------------+
10 rows in set (0.00 sec)

注意：表别名只在执行查询时使用，并不在返回结果中显示，而列定义别名之后，将返回给客户端显示，显示的结果字段为字段列的别名。

为表取别名的基本语法格式为：

<表名> [AS] <别名>

其中各子句的含义如下：

• <表名>：数据中存储的数据表的名称。 ·
• <别名>：查询时指定的表的新名称。
• AS：关键字为可选参数。

【实例】为 tb_students_info 表取别名 stu，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT stu.name,stu.height
    -> FROM tb_students_info AS stu;
+--------+--------+
| name   | height |
+--------+--------+
| Dany   |    160 |
| Green  |    158 |
| Henry  |    185 |
| Jane   |    162 |
| Jim    |    175 |
| John   |    172 |
| Lily   |    165 |
| Susan  |    170 |
| Thomas |    178 |
| Tom    |    165 |
+--------+--------+
10 rows in set (0.04 sec)

注意：在为表取别名时，要保证不能与数据库中的其他表的名称冲突。

在使用 SELECT 语句显示查询结果时，MySQL 会显示每个 SELECT 后面指定输出的列，在有些情况下，显示的列名称会很长或者名称不够直观，MySQL 可以指定列的别名，替换字段或表达式。

LIMIT：限制查询结果的记录条数

在使用 MySQL SELECT 语句时往往返回的是所有匹配的行，有些时候我们仅需要返回第一行或者前几行，这时候就需要用到 MySQL LIMT 子句。

基本的语法格式如下：

<LIMIT> [<位置偏移量>,] <行数>

LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。

第一个参数“位置偏移量”指示 MySQL 从哪一行开始显示，是一个可选参数，如果不指定“位置偏移量”，将会从表中的第一条记录开始（第一条记录的位置偏移量是 0，第二条记录的位置偏移量是 1，以此类推）；第二个参数“行数”指示返回的记录条数。

【实例 1】显示 tb_students_info 表查询结果的前 4 行，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info LIMIT 4;
+----+-------+---------+------+------+--------+------------+
| id | name  | dept_id | age  | sex  | height | login_date |
+----+-------+---------+------+------+--------+------------+
|  1 | Dany  |       1 |   25 | F    |    160 | 2015-09-10 |
|  2 | Green |       3 |   23 | F    |    158 | 2016-10-22 |
|  3 | Henry |       2 |   23 | M    |    185 | 2015-05-31 |
|  4 | Jane  |       1 |   22 | F    |    162 | 2016-12-20 |
+----+-------+---------+------+------+--------+------------+
4 rows in set (0.00 sec)

由结果可以看到，该语句没有指定返回记录的“位置偏移量”参数，显示结果从第一行开始，“行数”参数为 4，因此返回的结果为表中的前 4 行记录。

若指定返回记录的开始位置，则返回结果为从“位置偏移量”参数开始的指定行数，“行数”参数指定返回的记录条数。

【实例 2】在 tb_students_info 表中，使用 LIMIT 子句返回从第 4 条记录开始的5条记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info LIMIT 3,5;
+----+-------+---------+------+------+--------+------------+
| id | name  | dept_id | age  | sex  | height | login_date |
+----+-------+---------+------+------+--------+------------+
|  4 | Jane  |       1 |   22 | F    |    162 | 2016-12-20 |
|  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily  |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 |
+----+-------+---------+------+------+--------+------------+
5 rows in set (0.00 sec)

由结果可以看到，第一个数字“3”表示从第 4 行开始（位置偏移量从 0 开始，第 4 行的位置偏移量为 3），第二个数字 5 表示返回的行数。

所以，，即“LIMIT n”与“LIMIT 0，n”等价,带两个参数的 LIMIT 可返回从任何位置开始的指定行数的数据。

返回第一行时，位置偏移量是 0。因此，“LIMIT 1， 1”返回第 2 行，而不是第 1 行。

使用LIMIT出现的问题

比如: 现在 t_student有10条数据 通过下面语句你猜猜 COUNT(*)会查询出多少条,

select COUNT(*) from t_student LIMIT 0,5 ; 我想你们脑海中第一印象是5条

答案是1条, 因为LIMIT只是控制显示的的结果集的条数,

而COUNT(id) 是查询整个表有多少条数据,所以只是显示一条数据,所以 LIMIT无效 ,

ORDER BY：对查询结果进行排序

在 MySQL SELECT 语句中，ORDER BY 子句主要用来将结果集中的数据按照一定的顺序进行排序。

其语法格式为：

ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC]

语法说明如下:

1) 列名

指定用于排序的列。可以指定多个列，列名之间用逗号分隔。

2) 表达式

指定用于排序的表达式。

3) 位置

指定用于排序的列在 SELECT 语句结果集中的位置，通常是一个正整数。

4) ASC|DESC

关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值。这两个关键字必须位于对应的列名、表达式、列的位置之后。

使用 ORDER BY 子句应该注意以下几个方面：

• ORDER BY 子句中可以包含子查询。
• 当排序的值中存在空值时，ORDER BY 子句会将该空值作为最小值来对待。
• 当在 ORDER BY 子句中指定多个列进行排序时，MySQL 会按照列的顺序从左到右依次进行排序。
• 查询的数据并没有以一种特定的顺序显示，如果没有对它们进行排序，则将根据插入到数据表中的顺序显示。使用 ORDER BY 子句对指定的列数据进行排序。

【实例 1】查询 tb_students_info 表的 height 字段值，并对其进行升序排序，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info ORDER BY height;
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  2 | Green  |       3 |   23 | F    |    158 | 2016-10-22 |
|  1 | Dany   |       1 |   25 | F    |    160 | 2015-09-10 |
|  4 | Jane   |       1 |   22 | F    |    162 | 2016-12-20 |
|  7 | Lily   |       6 |   22 | F    |    165 | 2016-02-26 |
| 10 | Tom    |       4 |   23 | M    |    165 | 2016-08-05 |
|  8 | Susan  |       4 |   23 | F    |    170 | 2015-10-01 |
|  6 | John   |       2 |   21 | M    |    172 | 2015-11-11 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.08 sec)

该语句通过指定 ORDER BY 子句，MySQL 对查询的 height 列的数据按数值的大小进行了升序排序。

【实例 2】有时需要根据多列进行排序。对多列数据进行排序要将需要排序的列之间用逗号隔开。

查询 tb_students_info 表中的 name 和 height 字段，先按 height 排序，再按 name 排序，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name,height
    -> FROM tb_students_info
    -> ORDER BY height,name;
+--------+--------+
| name   | height |
+--------+--------+
| Green  |    158 |
| Dany   |    160 |
| Jane   |    162 |
| Lily   |    165 |
| Tom    |    165 |
| Susan  |    170 |
| John   |    172 |
| Jim    |    175 |
| Thomas |    178 |
| Henry  |    185 |
+--------+--------+
10 rows in set (0.09 sec)

注意：在对多列进行排序时，首行排序的第一列必须有相同的列值，才会对第二列进行排序。如果第一列数据中所有的值都是唯一的，将不再对第二列进行排序。

默认情况下，查询数据按字母升序进行排序（A～Z），但数据的排序并不仅限于此，还可以使用 ORDER BY 对查询结果进行降序排序（Z～A），这可以通过关键字 DESC 实现。可以对多列进行不同的顺序排序。

【实例 3】查询 tb_students_info 表，先按 height 降序排序，再按 name 升序排序，输入的 SQL 语句和执行过程如下所示。

mysql> SELECT name,height FROM tb_student_info ORDER BY height DESC,name ASC;
+--------+--------+
| name   | height |
+--------+--------+
| Henry  |    185 |
| Thomas |    178 |
| Jim    |    175 |
| John   |    172 |
| Susan  |    170 |
| Lily   |    165 |
| Tom    |    165 |
| Jane   |    162 |
| Dany   |    160 |
| Green  |    158 |
+--------+--------+
10 rows in set (0.00 sec)

注意：DESC 关键字只对前面的列进行降序排列，在这里只对 height 排序，而并没有对 name 进行排序，因此，height 按降序排序，而 name 仍按升序排序，如果要对多列进行降序排序，必须要在每一列的后面加 DESC 关键字。

ORDER BY：自定义排序

项目中有时需要按照业务的要求对数据进行排序

ORDER BY AAA DESC,BBB ASC

这样可并不能满足要求。比如某个列的值可能为 【3,5,2,4,1】，直接排序的话要么是12345，要么是54321，但是排序要求可能是按照24135这样排，那么此时可以使用自定义排序

一、语法：

MySQL中的field()函数，可以用来对SQL中查询结果集进行指定顺序排序

ORDER BY FIELD(field，str1，str2，str3，str4……) DESC

解释:

field为排序字段，str1，str2，str3，str4…… 为指定的字段内容的名字

另外，自定义排序还有其它字段排序直接加在后面即可

ORDER BY FIELD(field，str1，str2，str3，str4……) DESC， [排序字段] [排序方式]

[案例] 只对年龄16,20,25的正序排序 ,其他的不管

最终效果就是:

SELECT * FROM t_student ORDER BY FIELD(age,16,20,25) ASC

WHERE：条件查询

在使用 MySQL SELECT语句时，可以使用 WHERE 子句来指定查询条件，从 FROM 子句的中间结果中选取适当的数据行，达到数据过滤的效果。

语法格式如下：

WHERE <查询条件> {<判定运算1>，<判定运算2>，…}

其中，判定运算其结果取值为 TRUE、FALSE 和 UNKNOWN。

判定运算的语法分类如下：

• <表达式1>{=|<|<=|>|>=|<=>|<>|！=}<表达式2>
• <表达式1>[NOT]LIKE<表达式2>
• <表达式1>[NOT][REGEXP|RLIKE]<表达式2>
• <表达式1>[NOT]BETWEEN<表达式2>AND<表达式3>
• <表达式1>IS[NOT]NULL

单一条件的查询语句

【实例 1】在表 tb_students_info 中查询身高为 170cm 的学生的姓名，输入的 SQL 语句和行结果如下所示。

mysql> SELECT name,height
    -> FROM tb_students_info
    -> WHERE height=170;
+-------+--------+
| name  | height |
+-------+--------+
| Susan |    170 |
+-------+--------+
1 row in set (0.17 sec)

该语句采用了简单的相等过滤，查询一个指定列 height 的具体值 170。

【实例 2】查询年龄小于 22 的学生的姓名，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name,age
    -> FROM tb_students_info
    -> WHERE age<22;
+------+------+
| name | age  |
+------+------+
| John |   21 |
+------+------+
1 row in set (0.05 sec)

可以看到，查询结果中所有记录的 age 字段的值均小于 22 岁，而大于或等于 22 岁的记录没有被返回。

多条件的查询语句

使用 SELECT 查询时，可以增加查询的限制条件，这样可以使查询的结果更加精确。MySQL 在 WHERE 子句中使用 AND 操作符限定只有满足所有查询条件的记录才会被返回。

可以使用 AND 连接两个甚至多个查询条件，多个条件表达式之间用 AND 分开。

【实例 3】在 tb_students_info 表中查询 age 大于 21，并且 height 大于等于 175 的学生的信息，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info
    -> WHERE age>21 AND height>=175;
+----+--------+---------+------+------+--------+------------+
| id | name   | dept_id | age  | sex  | height | login_date |
+----+--------+---------+------+------+--------+------------+
|  3 | Henry  |       2 |   23 | M    |    185 | 2015-05-31 |
|  5 | Jim    |       1 |   24 | M    |    175 | 2016-01-15 |
|  9 | Thomas |       3 |   22 | M    |    178 | 2016-06-07 |
+----+--------+---------+------+------+--------+------------+
3 rows in set (0.06 sec)

注意：上例的 WHERE 子句中只包含一个 AND 语句，把两个过滤条件组合在一起，实际上可以添加多个 AND 过滤条件，增加条件的同时增加一个 AND 关键字。

使用 LIKE 的模糊查询

字符串匹配的语法格式如下：

<表达式1> [NOT] LIKE <表达式2>

字符串匹配是一种模式匹配，使用运算符 LIKE 设置过滤条件，过滤条件使用通配符进行匹配运算，而不是判断是否相等进行比较。

相互间进行匹配运算的对象可以是 CHAR、VARCHAR、TEXT、DATETIME 等数据类型。运算返回的结果是 TRUE 或 FALSE。

利用通配符可以在不完全确定比较值的情形下创建一个比较特定数据的搜索模式，并置于关键字 LIKE 之后。可以在搜索模式的任意位置使用通配符，并且可以使用多个通配符。MySQL 支持的通配符有以下两种：

1) 百分号（%）

百分号是 MySQL 中常用的一种通配符，在过滤条件中，百分号可以表示任何字符串，并且该字符串可以出现任意次。

使用百分号通配符要注意以下几点：

• MySQL 默认是不区分大小写的，若要区分大小写，则需要更换字符集的校对规则。
• 百分号不匹配空值。
• 百分号可以代表搜索模式中给定位置的 0 个、1 个或多个字符。
• 尾空格可能会干扰通配符的匹配，一般可以在搜索模式的最后附加一个百分号。

2) 下划线（_）

下划线通配符和百分号通配符的用途一样，下画线只匹配单个字符，而不是多个字符，也不是 0 个字符。

注意：不要过度使用通配符，对通配符检索的处理一般会比其他检索方式花费更长的时间。

【实例 4】在 tb_students_info 表中，查找所有以“T”字母开头的学生姓名，输入的 SQL 的语句和执行结果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE name LIKE 'T%';
+--------+
| name   |
+--------+
| Thomas |
| Tom    |
+--------+
2 rows in set (0.12 sec)

注意：在搜索匹配时，通配符“%”可以放在不同位置。

【实例 5】在 tb_students_info 表中，查找所有包含“e”字母的学生姓名，输入的 SQL 的语句和执行结果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE name LIKE '%e%';
+-------+
| name  |
+-------+
| Green |
| Henry |
| Jane  |
+-------+
3 rows in set (0.00 sec)

由执行结果可以看出，该语句查询字符串中包含字母 e 的学生的姓名，只要名字中有字母 e，其前面或后面无论有多少个字符，都满足查询的条件。

【实例 6】在 tb_students_info 表中，查找所有以字母“y”结尾，且“y”前面只有 4 个字母的学生的姓名，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE name LIKE '____y';
+-------+
| name  |
+-------+
| Henry |
+-------+
1 row in set (0.00 sec)

日期字段作为条件的查询语句

以日期字段作为条件，可以使用比较运算符设置查询条件，也可以使用 BETWEEN AND 运算符查询某个范围内的值。

BETWEEN AND 用来查询某个范围内的值，该操作符需要两个参数，即范围的开始值和结束值，若字段值满足指定的范围查询条件，则这些记录被返回。

【实例 7】在表 tb_students_info 中查询注册日期在 2016-01-01 之前的学生的信息，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info
    -> WHERE login_date<'2016-01-01';
+----+-------+---------+------+------+--------+------------+
| id | name  | dept_id | age  | sex  | height | login_date |
+----+-------+---------+------+------+--------+------------+
|  1 | Dany  |       1 |   25 | F    |    160 | 2015-09-10 |
|  3 | Henry |       2 |   23 | M    |    185 | 2015-05-31 |
|  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 |
|  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 |
+----+-------+---------+------+------+--------+------------+
4 rows in set (0.04 sec)

【实例 8】在表 tb_students_info 中查询注册日期在 2015-10-01 和 2016-05-01 之间的学生的信息，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_students_info
    -> WHERE login_date
    -> BETWEEN '2015-10-01'
    -> AND '2016-05-01';
+----+-------+---------+------+------+--------+------------+
| id | name  | dept_id | age  | sex  | height | login_date |
+----+-------+---------+------+------+--------+------------+
|  5 | Jim   |       1 |   24 | M    |    175 | 2016-01-15 |
|  6 | John  |       2 |   21 | M    |    172 | 2015-11-11 |
|  7 | Lily  |       6 |   22 | F    |    165 | 2016-02-26 |
|  8 | Susan |       4 |   23 | F    |    170 | 2015-10-01 |
+----+-------+---------+------+------+--------+------------+
4 rows in set (0.02 sec)

常用运算符详解

MySQL 数据库中的表结构确立后，表中的数据代表的意义就已经确定。而通过 MySQL 运算符进行运算，就可以获取到表结构以外的另一种数据。

例如，学生表中存在一个 birth 字段，这个字段表示学生的出生年份。而运用 MySQL 的算术运算符用当前的年份减学生出生的年份，那么得到的就是这个学生的实际年龄数据。

MySQL 支持 4 种运算符，分别是：

1) 算术运算符

执行算术运算，例如：加、减、乘、除等。

2) 比较运算符

包括大于、小于、等于或者不等于，等等。主要用于数值的比较、字符串的匹配等方面。例如：LIKE、IN、BETWEEN AND 和 IS NULL 等都是比较运算符，还包括正则表达式的 REGEXP 也是比较运算符。

3) 逻辑运算符

包括与、或、非和异或等逻辑运算符。其返回值为布尔型，真值（1 或 true）和假值（0 或 false）。

4) 位运算符

包括按位与、按位或、按位取反、按位异或、按位左移和按位右移等位运算符。位运算必须先将数据转换为二进制，然后在二进制格式下进行操作,运算完成后，将二进制的值转换为原来的类型，返回给用户。

算术运算符

算术运算符是 SQL 中最基本的运算符，MySQL 中的算术运算符如下表所示。

算术运算符	说明
+	加法运算
-	减法运算
*	乘法运算
/	除法运算，返回商
%	求余运算，返回余数

比较运算符

比较运算符的语法格式为：

<表达式1> {= | < | <= | > | >= | <=> | < > | !=} <表达式2>

MySQL 支持的比较运算符如下表所示。

比较运算符	说明
=	等于
<	小于
<=	小于等于
>	大于
>=	大于等于
<=>	安全的等于，不会返回 UNKNOWN
<> 或!=	不等于
IS NULL 或 ISNULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

下面分别介绍不同的比较运算符的使用方法。

1) 等于运算符“=”

等号“=”用来判断数字、字符串和表达式是否相等。如果相等，返回值为 1，否则返回值为 0。

数据进行比较时，有如下规则：

• 若有一个或两个参数为 NULL，则比较运算的结果为 NULL。
• 若同一个比较运算中的两个参数都是字符串，则按照字符串进行比较。
• 若两个参数均为正数，则按照整数进行比较。
• 若一个字符串和数字进行相等判断，则 MySQL 可以自动将字符串转换成数字。

2) 安全等于运算符“<=>”

对于运算符“<=>”，当两个表达式彼此相等或都等于空值时，比较结果为 TRUE；若其中一个是空值或者都是非空值但不相等时，则为 FALSE，不会出现 UNKNOWN 的情况。

3) 不等于运算符“<>”或者“!=”

“<>”或者“!=”用于数字、字符串、表达式不相等的判断。如果不相等，返回值为 1；否则返回值为 0。这两个运算符不能用于判断空值（NULL）。

4) 小于或等于运算符“<=”

“<=”用来判断左边的操作数是否小于或等于右边的操作数。如果小于或等于，返回值为 1；否则返回值为 0。“<=”不能用于判断空值。

5) 小于运算符“<”

“<”用来判断左边的操作数是否小于右边的操作数。如果小于，返回值为 1；否则返回值为 0。“<”不能用于判断空值。

6) 大于或等于运算符“>=”

“>=”用来判断左边的操作数是否大于或等于右边的操作数。如果大于或等于，返回值为 1；否则返回值为 0。“>=”不能用于判断空值。

7) 大于运算符“>”

“>”用来判断左边的操作数是否大于右边的操作数。如果大于，返回值为 1；否则返回值为 0。“>”不能用于判断空值。

8) IS NULL（或者 ISNULL）

IS NULL 和 ISNULL 用于检验一个值是否为 NULL，如果为 NULL，返回值为 1；否则返回值为 0。

9) IS NOT NULL

IS NOT NULL 用于检验一个值是否为非 NULL，如果为非 NULL，返回值为 1；否则返回值为 0。

10) BETWWEN AND

语法格式为：

<表达式> BETWEEN <最小值> AND <最大值>

若 <表达式>大于或等于 <最小值>，且小于或等于 <最大值>，则 BETWEEN 的返回值为 1；否则返回值为 0。

11) LEAST

语法格式为：

LEAST(<值1>,<值2>,…,<值n>)

其中，值 n 表示参数列表中有 n 个值。存在两个或多个参数的情况下，返回最小值。若任意一个自变量为 NULL，则 LEAST() 的返回值为 NULL。

12) GREATEST

语法格式为：

GREATEST (<值1>,<值2>,…,<值n>)

其中，值 n 表示参数列表中有 n 个值。存在两个或多个参数的情况下，返回最大值。若任意一个自变量为 NULL，则 GREATEST() 的返回值为 NULL。

13) IN

IN 运算符用来判断操作数是否为 IN 列表中的一个值。如果是，返回值为 1；否则返回值为 0。

14) NOT IN

NOT IN 运算符用来判断表达式是否为 IN 列表中的一个值。如果不是，返回值为 1；否则返回值为 0。

逻辑运算符

在 SQL 语言中，所有逻辑运算符求值所得的结果均为 TRUE、FALSE 或 NULL。在 MySQL 中分别体现为 1（TRUE）、0（FALSE）和 NULL。

MySQL 中的逻辑运算符如下表所示。

逻辑运算符	说明
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 或者 ||	逻辑或
XOR	逻辑异或

下面分别介绍不同的逻辑运算符的使用方法。

1) NOT 或者 !

逻辑非运算符 NOT 或者 !，表示当操作数为 0 时，返回值为 1；当操作数为非零值时，返回值为 0；当操作数为 NULL 时，返回值为 NULL。

2) AND 或者 &&

逻辑与运算符 AND 或者 &&，表示当所有操作数均为非零值并且不为 NULL 时，返回值为 1；当一个或多个操作数为 0 时，返回值为 0；其余情况返回值为 NULL。

3) OR 或者 ||

逻辑或运算符 OR 或者 ||，表示当两个操作数均为非 NULL 值且任意一个操作数为非零值时，结果为 1，否则结果为 0；当有一个操作数为 NULL 且另一个操作数为非零值时，结果为 1，否则结果为 NULL；当两个操作数均为 NULL 时，所得结果为 NULL。

4) XOR

逻辑异或运算符 XOR。当任意一个操作数为 NULL 时，返回值为 NULL；对于非 NULL 的操作数，若两个操作数都不是 0 或者都是 0 值，则返回结果为 0；若一个为 0，另一个不为非 0，则返回结果为 1。

位运算符

位运算符是用来对二进制字节中的位进行移位或者测试处理的。

MySQL 中提供的位运算符如下表所示。

位运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

下面分别介绍不同的位运算符的使用方法。

1) 位或运算符“|”

位或运算的实质是将参与运算的两个数据按对应的二进制数逐位进行逻辑或运算。若对应的二进制位有一个或两个为 1，则该位的运算结果为 1，否则为 0。

2) 位与运算符“&”

位与运算的实质是将参与运算的两个数据按对应的二进制数逐位进行逻辑与运算。若对应的二进制位都为 1，则该位的运算结果为 1，否则为 0。

3) 位异或运算符“^”

位异或运算的实质是将参与运算的两个数据按对应的二进制数逐位进行逻辑异或运算。对应的二进制位不同时，对应位的结果才为 1。如果两个对应位都为 0 或者都为 1，则对应位的结果为 0。

4) 位左移运算符“<<”

位左移运算符“<<”使指定的二进制值的所有位都左移指定的位数。左移指定位数之后，左边高位的数值将被移出并丢弃，右边低位空出的位置用 0 补齐。

语法格式为 表达式<<n，这里 n 指定值要移位的位数。

5) 位右移运算符“>>”

位右移运算符“>>”使指定的二进制值的所有位都右移指定的位数。右移指定位数之后，右边高位的数值将被移出并丢弃，左边低位空出的位置用 0 补齐。

语法格式为 表达式>>n，这里 n 指定值要移位的位数。

6) 位取反运算符“~”

位取反运算符的实质是将参与运算的数据按对应的二进制数逐位反转，即 1 取反后变 0，0 取反后变为 1。

运算符的优先级决定了不同的运算符在表达式中计算的先后顺序，下表列出了 MySQL 中的各类运算符及其优先级。

优先级由低到高排列	运算符
1	=(赋值运算）、:=
2	II、OR
3	XOR
4	&&、AND
5	NOT
6	BETWEEN、CASE、WHEN、THEN、ELSE
7	=(比较运算）、<=>、>=、>、<=、<、<>、!=、 IS、LIKE、REGEXP、IN
8	|
9	&
10	<<、>>
11	-(减号）、+
12	*、/、%
13	^
14	-(负号）、〜（位反转）
15	!

可以看出，不同运算符的优先级是不同的。一般情况下，级别高的运算符优先进行计算，如果级别相同，MySQL 按表达式的顺序从左到右依次计算。

另外，在无法确定优先级的情况下，可以使用圆括号“()”来改变优先级，并且这样会使计算过程更加清晰。

子查询详解

子查询指一个查询语句嵌套在另一个查询语句内部的查询，这个特性从 MySQL4.1 开始引入，在 SELECT 子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件，查询可以基于一个表或者多个表。

子查询中常用的操作符有 ( )、IN 和EXISTS。

子查询可以添加到 SELECT、UPDATE 和 DELETE 语句中，而且可以进行多层嵌套。子查询也可以使用比较运算符，如“<”、“<=”、“>”、“>=”、“！=”等。

MySQL 带子查询的sql实际上执行了两个操作过程，即先执行内层子查询，再执行外层查询，内层子查询的结果作为外部查询的条件或者数据。

提示：子查询也可以连接表使用

0)子查询

列子查询

语法为: SELECT (列子查询) FROM table 这里的子查询只能返单个值,然后匹配其他所有行

mysql>  SELECT  name,(SELECT SUM(price)  FROM t_course )  FROM  t_student ;
+----------+-------------------------------------+
| name     | (SELECT SUM(price)  FROM t_course ) |
+----------+-------------------------------------+
| 刘艳     |                              251.00 |
| 反帝反封 |                              251.00 |
| 叶子     |                              251.00 |
| 叶玲     |                              251.00 |
| 叶花     |                              251.00 |
| 威廉     |                              251.00 |
| 小兔     |                              251.00 |
| 小新     |                              251.00 |
| 黄明     |                              251.00 |
| 黄涛     |                              251.00 |
+----------+-------------------------------------+

表子查询

语法为: SELECT a.* FROM (表子查询) as a 这里子查询可以返回多列多行

SELECT a.*  FROM (
	SELECT   a.id ,  a.`name`,c.course_name   FROM t_student  a 
	JOIN  t_student_gradeclass_course as b ON   b.studentID=a.id
	JOIN  t_course  c  ON  c.id=b.t_course 
)  as a
WHERE a.course_name='语文'

注意事项: 必须给虚拟表声明一个表名(别名) 比如: ( xxx ) AS a 表名就是 a

【实例 1】在 tb_departments 表中查询 dept_type 为 A 的学院 ID，并根据学院 ID 查询该学院学生的名字，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id IN
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type= 'A' );
+-------+
| name  |
+-------+
| Dany  |
| Henry |
| Jane  |
| Jim   |
| John  |
+-------+
5 rows in set (0.01 sec)

上述查询过程可以分步执行，首先内层子查询查出 tb_departments 表中符合条件的学院 ID，

注意: 子查询内不能嵌套多个子查询

错误嵌套: ( (),(),() )

正确嵌套的是 ( ( ( ) ) )

2) 比较运算符子查询

比较运算符所使用的子查询主要用于对表达式的值和子查询返回的值进行比较运算。其语法格式为：

<列> {= | < | > | >= | <= | <=> | < > | != } { ALL | SOME | ANY} <子查询>

语法说明如下。

• <列>：用于匹配子查询的列。

• <表达式>：用于指定要进行比较的表达式。

• ALL、SOME 和 ANY：可选项。用于指定对比较运算的限制。

{= | < | > | >= | <= | <=> | < > | != } 条件的子查询使用演示

注意: 这种方式的子查询只能返回一个值否则报错

Subquery returns more than 1 row 子查询返回多于1行

SELECT * FROM t_course 
 WHERE id =( 
			 SELECT a.t_course FROM t_student_gradeclass_course AS a 
					WHERE studentID =( 
							SELECT id FROM t_student WHERE NAME = '毛毛' 
			)
 )

ALL 条件的子查询使用演示

注意:这种方式的子查询,必须列内值和子查询内部的值完全匹配才显示

 SELECT * FROM t_course
 WHERE id != ALL ( 
	SELECT a.t_course FROM t_student_gradeclass_course AS a 			
 )

SOME 和 ANY条件的子查询使用演示

注意: 这种方式的子查询,只要有一个匹配成功那么就全部查询出来

 SELECT * FROM t_course
 WHERE id = SOME ( 
			 SELECT a.t_course FROM t_student_gradeclass_course AS a 			
 )

1) IN或者NOT IN子查询

结合关键字 IN 所使用的子查询主要用于判断一个给定值是否存在于子查询的结果集中。其语法格式为：

<表达式> [NOT] IN <子查询>

语法说明如下。

• <表达式>：用于指定表达式。当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。
• <子查询>：多值匹配,还可用于子查询,这里的子查询只能返回一列数据。对于比较复杂的查询要求，可以使用 SELECT 语句实现子查询的多层嵌套。

案例: IN 子查询使用

查询 id 为 1和2的名称

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id IN(1,2,3,4,5,);
+-------+
| name  |
+-------+
| Dany  |
| Henry |
| Jane  |
| Jim   |
| John  |
+-------+
5 rows in set (0.03 sec)

案例: NOT IN 子查询使用

查询tb_departments表dept_type为A的所有id ,然后拿查询出来的id作为tb_students_info表的查询条件

mysql> SELECT name FROM tb_students_info
    -> WHERE dept_id NOT IN
    -> (SELECT dept_id
    -> FROM tb_departments
    -> WHERE dept_type='A');
+--------+
| name   |
+--------+
| Green  |
| Lily   |
| Susan  |
| Thomas |
| Tom    |
+--------+
5 rows in set (0.04 sec)

2) EXISTS和NOT EXISTS子查询

关键字 EXISTS和NOT EXISTS 主要用于查询两个表数据的共同点和差异。其语法格式为：
EXISTS <子查询> 和 NOT EXISTS

执行原理就是拿外表的指定数据执行内层子查询,若内层查询结果为非空，则为真，否则为假。

-- 查询两个表直接的数据差异  ,a表比b表多的条数据 
select * from t_user as a  where NOT EXISTS(SELECT 1 FROM t_user_1 as b  where a.id= b.id)

-- 查询两个表直接的数据差异 , a表等于b表的数据
select * from t_user as a  where  EXISTS(SELECT 1 FROM t_user_1 as b  where a.id= b.id)

INNER JOIN：内连接查询

内连接是通过在查询中设置连接条件的方式，来移除查询结果集中某些数据行后的交叉连接。简单来说，就是利用条件表达式来消除交叉连接的某些数据行。

在 MySQL FROM 子句中使用关键字 INNER JOIN 连接两张表，并使用 ON 子句来设置连接条件。如果没有任何条件，INNER JOIN 和 CROSS JOIN 在语法上是等同的，两者可以互换。

语法格式如下：

SELECT <列名1，列名2 …>  FROM <表名1> 
INNER JOIN <表名2> [ ON子句]

等效于

SELECT <列名1，列名2 …>  FROM <表名1> ,<表名2>...
WHERE 表名1.id=表名2.id

内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只用关键字 JOIN。使用内连接后，FROM 子句中的 ON 子句可用来设置连接表的条件。

在 FROM 子句中可以在多个表之间,连续使用 INNER JOIN 或 JOIN，如此可以同时实现多个表的内连接。

【实例 1】表 tb_students_info 和表 tb_departments 都包含相同数据类型的字段 dept_id，在两个表之间使用内连接查询。输入的 SQL 语句和执行结果如下所示。

mysql> SELECT id,name,age,dept_name
    -> FROM tb_students_info,tb_departments
    -> WHERE tb_students_info.dept_id=tb_departments.dept_id;
+----+--------+------+-----------+
| id | name   | age  | dept_name |
+----+--------+------+-----------+
|  1 | Dany   |   25 | Computer  |
|  2 | Green  |   23 | Chinese   |
|  3 | Henry  |   23 | Math      |
|  4 | Jane   |   22 | Computer  |
|  5 | Jim    |   24 | Computer  |
|  6 | John   |   21 | Math      |
|  7 | Lily   |   22 | Computer  |
|  8 | Susan  |   23 | Economy   |
|  9 | Thomas |   22 | Chinese   |
| 10 | Tom    |   23 | Economy   |
+----+--------+------+-----------+
10 rows in set (0.00 sec)

在这里，SELECT 语句与前面介绍的最大差别是：

SELECT 后面指定的列分别属于两个不同的表，id、name、age 在表 tb_students_info 中，而 dept_name 在表 tb_departments 中，同时 FROM 列出了两个表 tb_students_info 和tb_departments。

WHERE 子句在这里作为过滤条件，指明只有两个表中的 dept_id 字段值相等的时候才符合连接查询的条件。

返回的结果可以看到，显示的记录是由两个表中的不同列值组成的新记录。

提示：因为 tb_students_info 表和 tb_departments 表中有相同的字段 dept_id，所以在比较的时候，需要完全限定表名（格式为“表名.列名”），如果只给出 dept_id，MySQL 将不知道指的是哪一个，并返回错误信息。

【实例 2】在 tb_students_info 表和 tb_departments 表之间，使用 INNER JOIN 语法进行内连接查询，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT id,name,age,dept_name
    -> FROM tb_students_info INNER JOIN tb_departments
    -> WHERE tb_students_info.dept_id=tb_departments.dept_id;
+----+--------+------+-----------+
| id | name   | age  | dept_name |
+----+--------+------+-----------+
|  1 | Dany   |   25 | Computer  |
|  2 | Green  |   23 | Chinese   |
|  3 | Henry  |   23 | Math      |
|  4 | Jane   |   22 | Computer  |
|  5 | Jim    |   24 | Computer  |
|  6 | John   |   21 | Math      |
|  7 | Lily   |   22 | Computer  |
|  8 | Susan  |   23 | Economy   |
|  9 | Thomas |   22 | Chinese   |
| 10 | Tom    |   23 | Economy   |
+----+--------+------+-----------+
10 rows in set (0.00 sec)

在这里的查询语句中，两个表之间的关系通过 INNER JOIN 指定。使用这种语法的时候，连接的条件使用 ON 子句给出，而不是 WHERE，ON 和 WHERE 后面指定的条件相同。

提示：

使用 WHERE 子句定义连接条件比较简单明了

而 INNER JOIN 语法是 ANSI SQL 的标准规范，

使用 INNER JOIN 连接语法能够确保不会忘记连接条件，而且 WHERE 子句在某些时候会影响查询的性能。

LEFT/RIGHT JOIN：外连接查询

MySQL 中 内连接是在交叉连接的结果集上返回满足条件的记录；而外连接先将连接的表分为基表和参考表，再以基表为依据返回满足和不满足条件的记录。

外连接更加注重两张表之间的关系。按照连接表的顺序，可以分为左外连接和右外连接。

语法:

[LEFT | RIGHT]  JOIN 表民 on 条件

左外连接

左外连接又称为左连接，在 FROM 子句中使用关键字 LEFT JOIN，用于接收该关键字左表（基表）的所有行，并用这些行与该关键字右表（参考表）中的行进行匹配，即匹配左表中的每一行及右表中符合条件的行。

在左外连接的结果集中，除了匹配的行之外，还包括左表中有但在右表中不匹配的行，对于这样的行，从右表中选择的列的值被设置为 NULL，即左外连接的结果集中的 NULL 值表示右表中没有找到与左表相符的记录。

【实例 1】在 tb_students_info 表和 tb_departments 表中查询所有学生，包括没有学院的学生，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name,dept_name
    -> FROM tb_students_info s
    -> LEFT OUTER JOIN tb_departments d
    -> ON s.dept_id = d.dept_id;
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| Dany   | Computer  |
| Jane   | Computer  |
| Jim    | Computer  |
| Henry  | Math      |
| John   | Math      |
| Green  | Chinese   |
| Thomas | Chinese   |
| Susan  | Economy   |
| Tom    | Economy   |
| Lily   | NULL      |
+--------+-----------+
10 rows in set (0.03 sec)

结果显示了 10 条记录，name 为 Lily 的学生目前没有学院，因为对应的 tb_departments 表中并没有该学生的学院信息，所以该条记录只取出了 tb_students_info 表中相应的值，而从 tb_departments 表中取出的值为 NULL。

右外连接

右外连接又称为右连接，在 FROM 子句中使用 RIGHT JOIN。与左外连接相反，右外连接以右表为基表，连接方法和左外连接相同。在右外连接的结果集中，除了匹配的行外，还包括右表中有但在左表中不匹配的行，对于这样的行，从左表中选择的值被设置为 NULL。

【实例 2】在 tb_students_info 表和 tb_departments 表中查询所有学院，包括没有学生的学院，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT name,dept_name
    -> FROM tb_students_info s
    -> RIGHT OUTER JOIN tb_departments d
    -> ON s.dept_id = d.dept_id;
+--------+-----------+
| name   | dept_name |
+--------+-----------+
| Dany   | Computer  |
| Green  | Chinese   |
| Henry  | Math      |
| Jane   | Computer  |
| Jim    | Computer  |
| John   | Math      |
| Susan  | Economy   |
| Thomas | Chinese   |
| Tom    | Economy   |
| NULL   | History   |
+--------+-----------+
10 rows in set (0.00 sec)

可以看到，结果只显示了 10 条记录，名称为 History 的学院目前没有学生，对应的 tb_students_info 表中并没有该学院的信息，所以该条记录只取出了 tb_departments 表中相应的值，而从 tb_students_info 表中取出的值为 NULL。

外连接查询注意事项

如果在连接使用不注意使用WHERE,那么就会导致查询效果和INNER JOIN一样了 或者就是查询多了或者少了…异常情况 (inner join 内连接查询除外,这个不影响) :

在执行到where之前，会先形成一个临时表而on就是临时表中的条件筛选,那么如果我们在WHERE中进行对子表赛选的话那么就有可能,会导致数据不对,但是可以对主表筛选的,而对于子表的筛选一般来说是在子表的on里而不是where,具体如何操作还需要看实际的逻辑,和不断的实验

下面两种写法都是正确过滤子表的情况可参考

主表: sortlist 子表: ods_fin_bal_sheet

左外连接条件参考案例

SELECT * FROM sortlist as a
LEFT JOIN ods_fin_bal_sheet  as b on  
a.item=b.ITEM AND  b.PERIOD_NAME='2018-04' AND b.ENTITY_NAME='集团大合并' 
WHERE a.`table`='balsheet'

主表: sortlist 子表: ods_fin_bal_sheet

子表的数据在子表对应的 ON 中进行赛选

右外连接条件参考案例

SELECT * FROM ods_fin_bal_sheet  as a
RIGHT JOIN  sortlist as b on   
a.item=b.ITEM AND a.PERIOD_NAME='2018-04' AND a.ENTITY_NAME='集团大合并' 
WHERE b.`table`='balsheet'

UNION 差集 并集 交集 合集

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

UNION ALL 查询结果集不去重

UNION 查询结果集去重 ,UNION 的去重,不是表的数据去重,而是结果集内是否有包含的,简单来说就是(并集)

还有注意的就是: 使用union 的第一条sql结尾不能有分号,否则不执行 union了

简单的UNION和 UNION ALL 案例

SELECT * FROM t_user WHERE id=6
UNION   -- 结果集去重合并 (并集)  
SELECT * FROM t_user WHERE id=15 ;

SELECT * FROM t_user WHERE id=6
UNION  ALL   --   (合集)
SELECT * FROM t_user WHERE id=6;

交集,并集,合集 ,差集 案例演示

--  查询 交集  (12346)(1345)=(134)
SELECT DISTINCT *  FROM  (   
SELECT a.country FROM Websites as a  ,apps as b WHERE a.country=b.country
) AS t_table

--  查询并集 (并集)  (12346)(1345)=(123456)
SELECT  *  FROM  (   
SELECT country FROM Websites  
UNION 
SELECT country FROM apps  
)  AS t_table
 
--  查询(合集)   (12346)(1345)=(123461345)
SELECT  *  FROM  (   
SELECT country FROM Websites  
UNION All
SELECT country FROM apps  
)  AS t_table


--  查询差集  (12346)(1345)=(256)
SELECT DISTINCT *  FROM  
(   
SELECT  country FROM Websites    WHERE country NOT IN (SELECT country FROM apps)
UNION
SELECT  country FROM apps    WHERE country NOT IN (SELECT country FROM Websites )

)  AS  t_table

GROUP BY：分组查询

在 MySQL SELECT 语句中，允许使用 GROUP BY 子句，将结果集中的数据行根据选择列的值进行逻辑分组，以便能汇总表内容的子集，实现对每个组而不是对整个结果集进行整合。

语法格式如下:

GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]

语法说明如下：

• <列名>：指定用于分组的列。可以指定多个列，彼此间用逗号分隔。
• <表达式>：指定用于分组的表达式。通常与聚合函数一块使用，例如可将表达式 COUNT(*)AS’ 人数 ’ 作为 SELECT 选择列表清单的一项。
• <位置>：指定用于分组的选择列在 SELECT 语句结果集中的位置，通常是一个正整数。例如，GROUP BY 2 表示根据 SELECT 语句列清单上的第 2 列的值进行逻辑分组。
• ASC|DESC：关键字 ASC 表示按升序分组，关键字 DESC 表示按降序分组，其中 ASC 为默认值，注意这两个关键字必须位于对应的列名、表达式、列的位置之后。

注意：GROUP BY 子句中的各选择列必须也是 SELECT 语句的选择列清单中的一项。

对于 GROUP BY 子句的使用，需要注意以下几点。

• GROUP BY 子句可以包含任意数目的列，使其可以对分组进行嵌套，为数据分组提供更加细致的控制。
• GROUP BY 子句列出的每个列都必须是检索列或有效的表达式，但不能是聚合函数。若在 SELECT 语句中使用表达式，则必须在 GROUP BY 子句中指定相同的表达式。
• 除聚合函数之外，SELECT 语句中的每个列都必须在 GROUP BY 子句中给出。
• 若用于分组的列中包含有 NULL 值，则 NULL 将作为一个单独的分组返回；若该列中存在多个 NULL 值，则将这些 NULL 值所在的行分为一组。

【实例】根据 dept_id 对 tb_students_info 表中的数据进行分组，将每个学院的学生姓名显示出来，输入的SQL语句和执行结果如下所示。

mysql> SELECT dept_id,GROUP_CONCAT(name) AS names
    -> FROM tb_students_info
    -> GROUP BY dept_id;
+---------+---------------+
| dept_id | names         |
+---------+---------------+
|       1 | Dany,Jane,Jim |
|       2 | Henry,John    |
|       3 | Green,Thomas  |
|       4 | Susan,Tom     |
|       6 | Lily          |
+---------+---------------+
5 rows in set (0.02 sec)

由运行结果可以看出，根据 dept_id 的不同分别统计了 dept_id 相同的姓名。

HAVING：指定过滤条件

在 MySQL SELECT 语句中，除了能使用 GROUP BY 子句分组数据外，还可以使用 HAVING 子句过滤分组，在结果集中规定了包含哪些分组和排除哪些分组。

语法格式如下：

HAVING <条件>

其中， <条件>指的是指定的过滤条件。

HAVING 子句和 WHERE 子句非常相似，HAVING 子句支持 WHERE 子句中所有的操作符和语法，但是两者存在几点差异：

• WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，即 HAVING 子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。
• WHERE 子句不可以包含聚合函数，HAVING 子句中的条件可以包含聚合函数。
• HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤。WHERE 子句排除的行不包含在分组中，可能会影响 HAVING 子句基于这些值过滤掉的分组。

【实例】根据 dept_id 对 tb_students_info 表中的数据进行分组，并显示学生人数大于1的分组信息，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT dept_id,GROUP_CONCAT(name) AS names
    -> FROM tb_students_info
    -> GROUP BY dept_id
    -> HAVING COUNT(name)>1;
+---------+---------------+
| dept_id | names         |
+---------+---------------+
|       1 | Dany,Jane,Jim |
|       2 | Henry,John    |
|       3 | Green,Thomas  |
|       4 | Susan,Tom     |
+---------+---------------+
4 rows in set (0.07 sec)

REGEXP：正则表达式查询

MySQL中正式表达式通常被用来检索或替换符合某个模式的文本内容，根据指定的匹配模式匹配文中符合要求的特殊字符串。

例如，从一个文件中提取电话号码，查找一篇文章中重复的单词或替换用户输入的敏感语汇等，这些地方都可以使用正则表达式。正则表达式强大而且灵活，常用于复杂的查询。

MySQL 中使用 REGEXP 关键字指定正则表达式的字符匹配模式，下表列出了 REGEXP 操作符中常用的匹配列表。

选项	说明	例子	匹配值示例
^	匹配文本的开始字符	‘^b’ 匹配以字母 b 开头 的字符串	book、big、banana、 bike
$	匹配文本的结束字符	'st$’ 匹配以 st 结尾的字 符串	test、resist、persist
.	匹配任何单个字符	'b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字 符	'f*n’ 匹配字符 n 前面有 任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	'ba+’ 匹配以 b 开头，后 面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	'fa’	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字 符	'[xz]'匹配 x 或者 z	dizzy、zebra、x-ray、 extra
[^]	匹配不在括号中的任何字符	'[^abc]’ 匹配任何不包 含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	b{2} 匹配 2 个或更多 的 b	bbb、 bbbb、 bbbbbbb
字符串 {n,m}	匹配前面的字符串至少 n 次， 至多 m 次	b{2,4} 匹配最少 2 个， 最多 4 个 b	bbb、 bbbb

查询以特定字符或字符串开头的记录

字符“^”匹配以特定字符或者字符串开头的文本。

【实例 1】在 tb_departments 表中，查询 dept_name 字段以字母“C”开头的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^C';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.05 sec)

在 tb_departments 表中有两条记录的 dept_name 字段值是以字母 C 开头的，返回结果有 2 条记录。

【实例 2】在 tb_departments 表中，查询 dept_name 字段以“Ch”开头的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.03 sec)

只有 Chinese 是以“Ch”开头的，所以查询结果中只有 1 条记录。

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

字符“$”匹配以特定字符或者字符串结尾的文本。

【实例 3】在 tb_departments 表中，查询 dept_name 字段以字母“y”结尾的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'y$';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

在 tb_departments 表中有两条记录的 dept_name 字段值是以字母 y 结尾的，返回结果有 2 条记录。

【实例 4】在 tb_departments 表中，查询 dept_name 字段以“my”结尾的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'my$';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       4 | Economy   | 44444     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.00 sec)

只有 Economy 是以“my”结尾的，所以查询结果中只有 1 条记录。

用符号“.”代替字符串中的任意一个字符

【实例 5】在 tb_departments 表中，查询 dept_name 字段值包含字母“o”与字母“y”，且两个字母之间只有一个字母的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'o.y';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

查询语句中“o.y”指定匹配字符中要有字母 o 和 y，且两个字母之间包含单个字符，并不限定匹配的字符的位置和所在查询字符串的总长度，因此 Economy 和 History 都符合匹配条件。

使用“*”和“+”来匹配多个字符

星号“*”匹配前面的字符任意多次，包括 0 次。加号“+”匹配前面的字符至少一次。

【实例 6】在 tb_departments 表中，查询 dept_name 字段值包含字母“C”，且“C”后面出现字母“h”的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch*';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

星号“*”可以匹配任意多个字符，Computer 中字母 C 后面并没有出现字母 h，但是也满足匹配条件。

【实例 7】在 tb_departments 表中，查询 dept_name 字段值包含字母“C”，且“C”后面出现字母“h”至少一次的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '^Ch+';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.00 sec)

“h+”匹配字母“h”至少一次，只有 Chinese 满足匹配条件。

匹配指定字符串

正则表达式可以匹配指定字符串，只要这个字符串在查询文本中即可，若要匹配多个字符串，则多个字符串之间使用分隔符“|”隔开。

【实例 8】在 tb_departments 表中，查询 dept_name 字段值包含字符串“in”的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'in';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
1 row in set (0.00 sec)

可以看到，dept_name 字段的 Chinese 中包含字符串“in”，满足匹配条件。

【实例 9】在 tb_departments 表中，查询 dept_name 字段值包含字符串“in”或者“on”的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP 'in|on';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       3 | Chinese   | 33333     | B         |
|       4 | Economy   | 44444     | B         |
+---------+-----------+-----------+-----------+
2 rows in set (0.00 sec)

可以看到，dept_name 字段的 Chinese 中包含字符串“in”，Economy 中包含字符串“on”，满足匹配条件。

提示：LIKE 运算符也可以匹配指定的字符串，但与 REGEXP 不同，LIKE 匹配的字符串如果在文本中间出现，就找不到它，相应的行也不会返回。而 REGEXP 在文本内进行匹配，如果被匹配的字符串在文本中出现，REGEXP 将会找到它，相应的行也会被返回。

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

方括号“[]”指定一个字符集合，只匹配其中任何一个字符，即为所查找的文本。

【实例 10】在 tb_departments 表中，查询 dept_name 字段值包含字母“o”或者“e”的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '[io]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       3 | Chinese   | 33333     | B         |
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
4 rows in set (0.00 sec)

从查询结果可以看到，所有返回的记录的 dept_name 字段的值中都包含字母 o 或者 e，或者两个都有。

方括号“[]”还可以指定数值集合。

【实例 11】在 tb_departments 表中，查询 dept_call 字段值中包含 1、2 或者 3 的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_call REGEXP '[123]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       2 | Math      | 22222     | A         |
|       3 | Chinese   | 33333     | B         |
+---------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

查询结果中，dept_call 字段值中有 1、2、3 三个数字中的一个即为匹配记录字段。

匹配集合“[123]”也可以写成“[1-3]”，即指定集合区间。例如，“[a-z]”表示集合区间为a~z的字母，“[0-9]”表示集合区间为所有数字。

匹配指定字符以外的字符

“[^字符集合]”匹配不在指定集合中的任何字符。

【实例 12】在 tb_departments 表中，查询 dept_name 字段值包含字母 a~t 以外的字符的记录，输入的 SQL 语句和执行结果如下所示。

mysql> SELECT * FROM tb_departments
    -> WHERE dept_name REGEXP '[^a-t]';
+---------+-----------+-----------+-----------+
| dept_id | dept_name | dept_call | dept_type |
+---------+-----------+-----------+-----------+
|       1 | Computer  | 11111     | A         |
|       4 | Economy   | 44444     | B         |
|       5 | History   | 55555     | B         |
+---------+-----------+-----------+-----------+
3 rows in set (0.00 sec)

返回记录中的 dept_name 字段值中包含了指定字母和数字以外的值，如 u、y 等，这些字母均不在 a～t 中，满足匹配条件。

查询扩展

列表统计查询

求每年级有多少人

SELECT  
( SELECT  count(*) FROM t_student_gradeclass_course  WHERE  gradeClassID IN (SELECT id FROM t_grade_class WHERE grade='一年级') )  as 一年级的人数 ,  

( SELECT  count(*)  FROM t_student_gradeclass_course  WHERE  gradeClassID IN (SELECT id FROM t_grade_class WHERE grade='二年级') ) as 二年级的人数 ,

( SELECT  count(*)  FROM t_student_gradeclass_course  WHERE  gradeClassID IN (SELECT id FROM t_grade_class WHERE grade='三年级') ) as 三年级的人数 ,

( SELECT  count(*)  FROM t_student_gradeclass_course  WHERE  gradeClassID IN (SELECT id FROM t_grade_class WHERE grade='四年级') ) as 四年级的人数 

效果:

一年级的人数	二年级的人数	三年级的人数	四年级的人数
5	3	3	1

二维统计列表复杂查询

这个就比较难了

下图是一对一关系查询出来的所有值

现在有一个需求是统计每个年龄段所学的课程数量,一般这种需求都是用于数据分析,对公司的整体数据直观展示,以便日后对业务更好的规划,和节约成本.前端以二维表形式展示,也就是我们需要同时查询出来这些数据,并且排版好,前端直接遍历每一行就能直接渲染到页面上去

错误数据

你想这样的数据没问题啊,但是你问问前端能显示吗,我敢说绝大部分前端都会说你这数据格式我做不了

正确数据

SELECT 
a.age,
IFNULL(b.count,0) as 生物,
IFNULL(c.count,0) as 数学,
IFNULL(d.count,0) as 英语    
FROM ( SELECT age FROM t_student GROUP BY age ) as a
LEFT JOIN (
SELECT age,count(*) as  count  FROM t_student  as a
INNER JOIN t_student_gradeclass_course as b on a.id=b.studentID
INNER JOIN t_course as c on b.t_course=c.id
where c.course_name='生物' 
GROUP BY age
) as b on a.age=b.age

LEFT JOIN (
SELECT age,count(*) as  count  FROM t_student  as a
INNER JOIN t_student_gradeclass_course as b on a.id=b.studentID
INNER JOIN t_course as c on b.t_course=c.id
where c.course_name='数学' 
GROUP BY age
) as c on a.age=c.age


LEFT JOIN (
SELECT age,count(*) as  count  FROM t_student  as a
INNER JOIN t_student_gradeclass_course as b on a.id=b.studentID
INNER JOIN t_course as c on b.t_course=c.id
where c.course_name='英语' 
GROUP BY age
) as d on a.age=d.age

ORDER BY  a.age 

当前端看到这样的数据,你说前端能不能做,我想他没话可说了吧…

​

点赞 -收藏加 -关注 便于以后复习和收到最新内容 有其他问题在评论区讨论-或者私信我-收到会在第一时间回复 感谢,配合,希望我的努力对你有帮助^_^