mysql select 子查询_MySQL查询命令_SELECT 子查询

首先创建一个table

mysql> create table Total (id int AUTO_INCREMENT PRIMARY KEY,name char(20),stu_num int NOT NULL,teacher_num int NOT NULL);

Query OK, 0 rows affected (0.11 sec)

mysql> insert into Total (name,stu_num,teacher_num) values ("小学1",400,20),("小学2",356,24),("小学3",403,19),("小学4",367,26),("小学5",373,20),("小学6",406,21);

Query OK, 6 rows affected (0.00 sec)

Records: 6 Duplicates: 0 Warnings: 0

mysql> select * from Total;

+----+---------+---------+-------------+

| id | name | stu_num | teacher_num |

+----+---------+---------+-------------+

| 1 | 小学1 | 400 | 20 |

| 2 | 小学2 | 356 | 24 |

| 3 | 小学3 | 403 | 19 |

| 4 | 小学4 | 367 | 26 |

| 5 | 小学5 | 373 | 20 |

| 6 | 小学6 | 406 | 21 |

+----+---------+---------+-------------+

6 rows in set (0.00 sec)

接下来就以这个表格为例，介绍select语句的深层运用。

一、查询不重复的记录

语句：select distinct [属性1,属性2] from 表名

说明：distinct必须放在最前的位置；

distinct只能使用需要去重的字段进行操作，也就是说如果distinct采用了name，stu_num两个字段，但是后面想利用id进行排序是不可以的，因为只能对name和stu_name两个字段进行操作；

distinct去重多个字段时，含义是几个字段同时重复时才会被过滤。

示例：

mysql> select distinct name,teacher_num from Total;

+---------+-------------+

| name | teacher_num |

+---------+-------------+

| 小学1 | 20 |

| 小学2 | 24 |

| 小学3 | 19 |

| 小学4 | 26 |

| 小学5 | 20 |

| 小学6 | 21 |

+---------+-------------+

6 rows in set (0.00 sec)

mysql> select distinct teacher_num from Total;

+-------------+

| teacher_num |

+-------------+

| 20 |

| 24 |

| 19 |

| 26 |

| 21 |

+-------------+

5 rows in set (0.00 sec)

二、排序

语法：select * from 表名 [where 条件] [ order by 属性1 [desc/asc],属性2 [desc/asc]... ];

说明：desc 降序排列，asc 升序排列；

order by 可以有多个参数，每个排序参数可以有不同的排序顺序；

如果第一个排序字段的值一样，则按照第二个排序字段进行排序；

如果只有一个排序字段，则字段值相同的记录将会无序排列。

示例：

mysql> select id,name from Total where id<4 order by teacher_num desc;

+----+---------+

| id | name |

+----+---------+

| 2 | 小学2 |

| 1 | 小学1 |

| 3 | 小学3 |

+----+---------+

3 rows in set (0.00 sec)

三、限制

语句：select ... [limit 起始偏移量,行数];    或    select ... [limit 行数] offset 偏移量;

说明：.默认情况下，起始偏移量为0

示例：

mysql> select * from Total order by teacher_num asc;

+----+---------+---------+-------------+

| id | name | stu_num | teacher_num |

+----+---------+---------+-------------+

| 3 | 小学3 | 403 | 19 |

| 1 | 小学1 | 400 | 20 |

| 5 | 小学5 | 373 | 20 |

| 6 | 小学6 | 406 | 21 |

| 2 | 小学2 | 356 | 24 |

| 4 | 小学4 | 367 | 26 |

+----+---------+---------+-------------+

6 rows in set (0.00 sec)

mysql> select * from Total limit 4 offset 1;

+----+---------+---------+-------------+

| id | name | stu_num | teacher_num |

+----+---------+---------+-------------+

| 2 | 小学2 | 356 | 24 |

| 3 | 小学3 | 403 | 19 |

| 4 | 小学4 | 367 | 26 |

| 5 | 小学5 | 373 | 20 |

+----+---------+---------+-------------+

4 rows in set (0.00 sec)

mysql> select * from Total order by teacher_num asc limit 4 offset 1;

+----+---------+---------+-------------+

| id | name | stu_num | teacher_num |

+----+---------+---------+-------------+

| 1 | 小学1 | 400 | 20 |

| 5 | 小学5 | 373 | 20 |

| 6 | 小学6 | 406 | 21 |

| 2 | 小学2 | 356 | 24 |

+----+---------+---------+-------------+

4 rows in set (0.00 sec)

mysql> (select * from Total limit 4 offset 1) order by teacher_num asc;

+----+---------+---------+-------------+

| id | name | stu_num | teacher_num |

+----+---------+---------+-------------+

| 3 | 小学3 | 403 | 19 |

| 5 | 小学5 | 373 | 20 |

| 2 | 小学2 | 356 | 24 |

| 4 | 小学4 | 367 | 26 |

+----+---------+---------+-------------+

4 rows in set (0.00 sec)

四、聚合

语句：select 字段 fun_name from 表名 [where 条件] [group by 属性1,属性2...] [with rollup] [having 条件];

说明：fun_name表示要做的聚合操作，也就是聚合函数，常用的有sum(求和)、count(*)(记录数)、max(最大值)、min(最小值)等；

group by 表示要进行分类聚合的字段，比如要按照部门分类统计员工数量；

with rollup是可选项，表示是否对分类聚合后的结果进行再汇总；

having 表示对分类后的结果再进行条件过滤。

示例：

mysql> create table Staff

-> (id int AUTO_INCREMENT PRIMARY KEY,

-> xing char(20),

-> ming char(20),

-> slary int NOT NULL);

Query OK, 0 rows affected (0.07 sec)

mysql> insert into Staff (xing,ming,slary) value('guo','ding',3200),

-> ('ding','tao',2800),

-> ('hao','fugui',3500),

-> ('guo','ming',4000),

-> ('hao','tian',2900),

-> ('feng','fei','3200'),

-> ('guo','ting',2600);

Query OK, 7 rows affected (0.00 sec)

Records: 7 Duplicates: 0 Warnings: 0

mysql> select * from Staff;

+----+------+-------+-------+

| id | xing | ming | slary |

+----+------+-------+-------+

| 8 | guo | ding | 3200 |

| 9 | ding | tao | 2800 |

| 10 | hao | fugui | 3500 |

| 11 | guo | ming | 4000 |

| 12 | hao | tian | 2900 |

| 13 | feng | fei | 3200 |

| 14 | guo | ting | 2600 |

+----+------+-------+-------+

7 rows in set (0.00 sec)

mysql> select count('xing') as xing_num from Staff where xing='guo'; //此处涉及了新的语法

+----------+

| xing_num |

+----------+

| 3 |

+----------+

1 row in set (0.00 sec)

mysql> select sum(slary) from Staff;

+------------+

| sum(slary) |

+------------+

| 22200 |

+------------+

1 row in set (0.00 sec)

mysql> select xing,sum(slary) from Staff group by xing;

+------+------------+

| xing | sum(slary) |

+------+------------+

| ding | 2800 |

| feng | 3200 |

| guo | 9800 |

| hao | 6400 |

+------+------------+

4 rows in set (0.00 sec)

mysql> select xing,sum(slary) from Staff group by xing with rollup;

+------+------------+

| xing | sum(slary) |

+------+------------+

| ding | 2800 |

| feng | 3200 |

| guo | 9800 |

| hao | 6400 |

| NULL | 22200 |

+------+------------+

5 rows in set (0.00 sec)

mysql> select xing,sum(slary) from Staff group by xing having sum(slary)>5000;

+------+------------+

| xing | sum(slary) |

+------+------------+

| guo | 9800 |

| hao | 6400 |

+------+------------+

2 rows in set (0.00 sec)

五、表连接

表连接分为内连接和外连接，其中内连接仅选出两张表中互相匹配的记录，外连接会选出其他不匹配的记录。

假设有以下Staff表和post表：

mysql> select * fromstaff;+----+----------+-------+

| id | name | slary |

+----+----------+-------+

| 1 | guoding | 3200 |

| 2 | dingtao | 2800 |

| 3 | haofugui | 3500 |

| 4 | guoming | 4000 |

| 5 | haotian | 2900 |

| 6 | fengfei | 3200 |

| 7 | guoting | 2600 |

+----+----------+-------+

7 rows in set (0.00sec)

mysql> select * frompost;+-----+----------+-------+

| num | name | level |

+-----+----------+-------+

| 1 | | 6 |

| 2 | fengfei | 4 |

| 3 | haotian | 3 |

| 4 | guoming | 1 |

| 5 | haofugui | 2 |

| 6 | dingtao | 5 |

| 7 | guoding | 4 |

+-----+----------+-------+

7 rows in set (0.00 sec)

示例：(内连接)，语句：select 属性1,属性2 from 表1,表2 where 表1.属性值=表2.属性值

mysql> select post.name,level,slary from staff,post where staff.name=post.name;+----------+-------+-------+

| name | level | slary |

+----------+-------+-------+

| fengfei | 4 | 3200 |

| haotian | 3 | 2900 |

| guoming | 1 | 4000 |

| haofugui | 2 | 3500 |

| dingtao | 5 | 2800 |

| guoding | 4 | 3200 |

+----------+-------+-------+

外连接又分为左连接与右连接：

左连接：包含所有左边表中的记录，甚至是右边表中没有和他匹配的记录。

右连接：包含所有右边表中的记录，甚至是右边表中没有和他匹配的记录。

示例：(左连接)，语句：select 属性1,属性2 from 表1 left join 表2 on 表1.属性值=表2.属性值

mysql> select post.name,level,slary from staff left join post on staff.name=post.name;+----------+-------+-------+

| name | level | slary |

+----------+-------+-------+

| guoding | 4 | 3200 |

| dingtao | 5 | 2800 |

| haofugui | 2 | 3500 |

| guoming | 1 | 4000 |

| haotian | 3 | 2900 |

| fengfei | 4 | 3200 |

| NULL | NULL | 2600 |

+----------+-------+-------+

7 rows in set (0.00 sec)

示例：(右连接)，语句：select 属性1,属性2 from 表1 right join 表2 on 表1.属性值=表2.属性值

mysql> select post.name,level,slary from staff right join post on staff.name=post.name;+----------+-------+-------+

| name | level | slary |

+----------+-------+-------+

| | 6 | NULL |

| fengfei | 4 | 3200 |

| haotian | 3 | 2900 |

| guoming | 1 | 4000 |

| haofugui | 2 | 3500 |

| dingtao | 5 | 2800 |

| guoding | 4 | 3200 |

+----------+-------+-------+

7 rows in set (0.00 sec)

需要说明的是，如果根据ON给出的连接条件，两个欲进行连接查询的表中存在一对多的匹配关系，则会输出对应的多条记录。

比如有以下两个表：

mysql> select * fromYSHA;+------+------+

| code | NAME |

+------+------+

| 1 | A1 |

| 2 | A2 |

+------+------+

2 rows in set (0.01sec)

mysql> select * fromYSHB;+------+------+

| code | col |

+------+------+

| 1 | Row1 |

| 1 | Row2 |

| 1 | Row3 |

| 3 | Row1 |

+------+------+

4 rows in set (0.00 sec)

接下来以YSHA.code=YSHB.code作为连接条件：

mysql> select * from YSHA left join YSHB on YSHA.code=YSHB.code;+------+------+------+------+

| code | NAME | code | col |

+------+------+------+------+

| 1 | A1 | 1 | Row1 |

| 1 | A1 | 1 | Row2 |

| 1 | A1 | 1 | Row3 |

| 2 | A2 | NULL | NULL |

+------+------+------+------+mysql> select * from YSHA right join YSHB on YSHA.code=YSHB.code;+------+------+------+------+

| code | NAME | code | col |

+------+------+------+------+

| 1 | A1 | 1 | Row1 |

| 1 | A1 | 1 | Row2 |

| 1 | A1 | 1 | Row3 |

| NULL | NULL | 3 | Row1 |

+------+------+------+------+

六、联合查询

MySQL使用UNION和UNION ALL实现数据的联合查询。

假设有以下4个table：

mysql> select * fromstaff;+----+----------+-------+

| id | name | slary |

+----+----------+-------+

| 1 | guoding | 3200 |

| 2 | dingtao | 2800 |

| 3 | haofugui | 3500 |

| 4 | guoming | 4000 |

| 5 | haotian | 2900 |

| 6 | fengfei | 3200 |

| 7 | guoting | 2600 |

+----+----------+-------+rowsin set (0.00sec)

mysql> select * fromstaff_1;+----+----------+-------+

| id | name | slary |

+----+----------+-------+

| 1 | guoding | 3200 |

| 2 | liding | 2700 |

| 3 | haofugui | 3500 |

| 4 | xiaoli | 3600 |

| 5 | yazhi | 3200 |

| 6 | yuanfei | 3200 |

| 7 | guoting | 3500 |

+----+----------+-------+rowsin set (0.00sec)

mysql> select * fromstaff_2;+----+-------+----------+

| id | slary | name |

+----+-------+----------+

| 1 | 3200 | guoding |

| 2 | 2700 | liding |

| 3 | 3500 | haofugui |

| 4 | 3600 | xiaoli |

| 5 | 3200 | yazhi |

| 6 | 3200 | yuanfei |

| 7 | 3500 | guoting |

+----+-------+----------+rowsin set (0.00sec)

mysql> select * fromstaff_3;+----------+-------+

| name | slary |

+----------+-------+

| guoding | 3200 |

| liding | 2700 |

| haofugui | 3500 |

| xiaoli | 3600 |

+----------+-------+rowsin set (0.00 sec)

1. UNION

语句：SELECT column_nameFROM table1UNIONSELECT column_nameFROM table2

说明：1)用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行；

2)UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型；

3)同时，每条 SELECT 语句中的列的顺序必须相同；

4)如果子句中有order by,limit等，需用括号()包起来，推荐放到所有子句之后，即对最终合并的结果来排序或筛选。

mysql> select * from staff union select * fromstaff_1;+----+----------+-------+

| id | name | slary |

+----+----------+-------+

| 1 | guoding | 3200 |

| 2 | dingtao | 2800 |

| 3 | haofugui | 3500 |

| 4 | guoming | 4000 |

| 5 | haotian | 2900 |

| 6 | fengfei | 3200 |

| 7 | guoting | 2600 |

| 2 | liding | 2700 |

| 4 | xiaoli | 3600 |

| 5 | yazhi | 3200 |

| 6 | yuanfei | 3200 |

| 7 | guoting | 3500 |

+----+----------+-------+rowsin set (0.00sec)

mysql> select * from staff union select * fromstaff_2;+----+----------------------+----------------------+

| id | name | slary |

+----+----------------------+----------------------+

| 1 | guoding | 3200 |

| 2 | dingtao | 2800 |

| 3 | haofugui | 3500 |

| 4 | guoming | 4000 |

| 5 | haotian | 2900 |

| 6 | fengfei | 3200 |

| 7 | guoting | 2600 |

| 1 | 3200 | guoding |

| 2 | 2700 | liding |

| 3 | 3500 | haofugui |

| 4 | 3600 | xiaoli |

| 5 | 3200 | yazhi |

| 6 | 3200 | yuanfei |

| 7 | 3500 | guoting |

+----+----------------------+----------------------+rowsin set (0.00sec)

mysql> select * from staff union select name fromstaff_1;

ERROR1222 (21000): The used SELECT statements have a different number of columns

mysql> select * from staff union select * fromstaff_3;

ERROR1222 (21000): The used SELECT statements have a different number of columns

mysql> (select * from staff order by id asc) union (select * fromstaff_1 order by slary desc);+----+----------+-------+

| id | name | slary |

+----+----------+-------+

| 1 | guoding | 3200 |

| 2 | dingtao | 2800 |

| 3 | haofugui | 3500 |

| 4 | guoming | 4000 |

| 5 | haotian | 2900 |

| 6 | fengfei | 3200 |

| 7 | guoting | 2600 |

| 2 | liding | 2700 |

| 4 | xiaoli | 3600 |

| 5 | yazhi | 3200 |

| 6 | yuanfei | 3200 |

| 7 | guoting | 3500 |

+----+----------+-------+rowsin set (0.00 sec)

2. UNION ALL

作用及规则与UNION相同，区别是，UNION ALL不消除重复行

mysql> select * from staff union select * fromstaff_1;+----+----------+-------+

| id | name | slary |

+----+----------+-------+

| 1 | guoding | 3200 |

| 2 | dingtao | 2800 |

| 3 | haofugui | 3500 |

| 4 | guoming | 4000 |

| 5 | haotian | 2900 |

| 6 | fengfei | 3200 |

| 7 | guoting | 2600 |

| 2 | liding | 2700 |

| 4 | xiaoli | 3600 |

| 5 | yazhi | 3200 |

| 6 | yuanfei | 3200 |

| 7 | guoting | 3500 |

+----+----------+-------+rowsin set (0.00sec)

mysql> select * from staff union all select * fromstaff_1;+----+----------+-------+

| id | name | slary |

+----+----------+-------+

| 1 | guoding | 3200 |

| 2 | dingtao | 2800 |

| 3 | haofugui | 3500 |

| 4 | guoming | 4000 |

| 5 | haotian | 2900 |

| 6 | fengfei | 3200 |

| 7 | guoting | 2600 |

| 1 | guoding | 3200 |

| 2 | liding | 2700 |

| 3 | haofugui | 3500 |

| 4 | xiaoli | 3600 |

| 5 | yazhi | 3200 |

| 6 | yuanfei | 3200 |

| 7 | guoting | 3500 |

+----+----------+-------+rowsin set (0.00 sec)