正文
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: 0mysql> 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: 0mysql> 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 * from staff;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | guoding | |
| | dingtao | |
| | haofugui | |
| | guoming | |
| | haotian | |
| | fengfei | |
| | guoting | |
+----+----------+-------+
rows in set (0.00 sec)mysql> select * from post;
+-----+----------+-------+
| num | name | level |
+-----+----------+-------+
| | | |
| | fengfei | |
| | haotian | |
| | guoming | |
| | haofugui | |
| | dingtao | |
| | guoding | |
+-----+----------+-------+
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 | | |
| haotian | | |
| guoming | | |
| haofugui | | |
| dingtao | | |
| guoding | | |
+----------+-------+-------+
外连接又分为左连接与右连接：
左连接：包含所有左边表中的记录，甚至是右边表中没有和他匹配的记录。
右连接：包含所有右边表中的记录，甚至是右边表中没有和他匹配的记录。
示例：（左连接），语句：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 | | |
| dingtao | | |
| haofugui | | |
| guoming | | |
| haotian | | |
| fengfei | | |
| NULL | NULL | |
+----------+-------+-------+
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 |
+----------+-------+-------+
| | | NULL |
| fengfei | | |
| haotian | | |
| guoming | | |
| haofugui | | |
| dingtao | | |
| guoding | | |
+----------+-------+-------+
rows in set (0.00 sec)
需要说明的是，如果根据ON给出的连接条件，两个欲进行连接查询的表中存在一对多的匹配关系，则会输出对应的多条记录。
比如有以下两个表：
mysql> select * from YSHA;
+------+------+
| code | NAME |
+------+------+
| | A1 |
| | A2 |
+------+------+
rows in set (0.01 sec)mysql> select * from YSHB;
+------+------+
| code | col |
+------+------+
| | Row1 |
| | Row2 |
| | Row3 |
| | Row1 |
+------+------+
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 |
+------+------+------+------+
| | A1 | | Row1 |
| | A1 | | Row2 |
| | A1 | | Row3 |
| | A2 | NULL | NULL |
+------+------+------+------+mysql> select * from YSHA right join YSHB on YSHA.code=YSHB.code;
+------+------+------+------+
| code | NAME | code | col |
+------+------+------+------+
| | A1 | | Row1 |
| | A1 | | Row2 |
| | A1 | | Row3 |
| NULL | NULL | | Row1 |
+------+------+------+------+
六、联合查询
MySQL使用UNION和UNION ALL实现数据的联合查询。
假设有以下4个table：
mysql> select * from staff;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | guoding | |
| | dingtao | |
| | haofugui | |
| | guoming | |
| | haotian | |
| | fengfei | |
| | guoting | |
+----+----------+-------+
rows in set (0.00 sec)mysql> select * from staff_1;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | guoding | |
| | liding | |
| | haofugui | |
| | xiaoli | |
| | yazhi | |
| | yuanfei | |
| | guoting | |
+----+----------+-------+
rows in set (0.00 sec)mysql> select * from staff_2;
+----+-------+----------+
| id | slary | name |
+----+-------+----------+
| | | guoding |
| | | liding |
| | | haofugui |
| | | xiaoli |
| | | yazhi |
| | | yuanfei |
| | | guoting |
+----+-------+----------+
rows in set (0.00 sec)mysql> select * from staff_3;
+----------+-------+
| name | slary |
+----------+-------+
| guoding | |
| liding | |
| haofugui | |
| xiaoli | |
+----------+-------+
rows in set (0.00 sec)
1. UNION
语句：SELECT column_name FROM table1 UNION SELECT column_name FROM table2
说明：1）用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行；
2）UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型；
3）同时，每条 SELECT 语句中的列的顺序必须相同；
4）如果子句中有order by,limit等，需用括号()包起来，推荐放到所有子句之后，即对最终合并的结果来排序或筛选。
mysql> select * from staff union select * from staff_1;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | guoding | |
| | dingtao | |
| | haofugui | |
| | guoming | |
| | haotian | |
| | fengfei | |
| | guoting | |
| | liding | |
| | xiaoli | |
| | yazhi | |
| | yuanfei | |
| | guoting | |
+----+----------+-------+
rows in set (0.00 sec)mysql> select * from staff union select * from staff_2;
+----+----------------------+----------------------+
| id | name | slary |
+----+----------------------+----------------------+
| | guoding | |
| | dingtao | |
| | haofugui | |
| | guoming | |
| | haotian | |
| | fengfei | |
| | guoting | |
| | | guoding |
| | | liding |
| | | haofugui |
| | | xiaoli |
| | | yazhi |
| | | yuanfei |
| | | guoting |
+----+----------------------+----------------------+
rows in set (0.00 sec)mysql> select * from staff union select name from staff_1;
ERROR (): The used SELECT statements have a different number of columnsmysql> select * from staff union select * from staff_3;
ERROR (): The used SELECT statements have a different number of columns
mysql> (select * from staff order by id asc) union (select * from staff_1 order by slary desc);
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | guoding | |
| | dingtao | |
| | haofugui | |
| | guoming | |
| | haotian | |
| | fengfei | |
| | guoting | |
| | liding | |
| | xiaoli | |
| | yazhi | |
| | yuanfei | |
| | guoting | |
+----+----------+-------+
rows in set (0.00 sec)
2. UNION ALL
作用及规则与UNION相同，区别是，UNION ALL不消除重复行
mysql> select * from staff union select * from staff_1;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | guoding | |
| | dingtao | |
| | haofugui | |
| | guoming | |
| | haotian | |
| | fengfei | |
| | guoting | |
| | liding | |
| | xiaoli | |
| | yazhi | |
| | yuanfei | |
| | guoting | |
+----+----------+-------+
rows in set (0.00 sec)mysql> select * from staff union all select * from staff_1;
+----+----------+-------+
| id | name | slary |
+----+----------+-------+
| | guoding | |
| | dingtao | |
| | haofugui | |
| | guoming | |
| | haotian | |
| | fengfei | |
| | guoting | |
| | guoding | |
| | liding | |
| | haofugui | |
| | xiaoli | |
| | yazhi | |
| | yuanfei | |
| | guoting | |
+----+----------+-------+
rows in set (0.00 sec)
