MySQL查询命令_SELECT 子查询

YY&BB姐姐的光阴故事
关注文章
TinyMind专栏频道

首先创建一个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 * from staff;
+----+----------+-------+
| 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.00 sec)

mysql> select * from post;
+-----+----------+-------+
| 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 * from YSHA;
+------+------+
| code | NAME |
+------+------+
| 1    | A1   |
| 2    | A2   |
+------+------+
2 rows in set (0.01 sec)

mysql> select * from YSHB;
+------+------+
| 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 * from staff;
+----+----------+-------+
| id | name     | slary |
+----+----------+-------+
|  1 | guoding  |  3200 |
|  2 | dingtao  |  2800 |
|  3 | haofugui |  3500 |
|  4 | guoming  |  4000 |
|  5 | haotian  |  2900 |
|  6 | fengfei  |  3200 |
|  7 | guoting  |  2600 |
+----+----------+-------+
rows in set (0.00 sec)

mysql> select * from staff_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 |
+----+----------+-------+
rows in set (0.00 sec)


mysql> select * from staff_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  |
+----+-------+----------+
rows in set (0.00 sec)
 
mysql> select * from staff_3;
+----------+-------+
| name     | slary |
+----------+-------+
| guoding  |  3200 |
| liding   |  2700 |
| haofugui |  3500 |
| xiaoli   |  3600 |
+----------+-------+
rows in set (0.00 sec)



1. UNION


语句：SELECTcolumn_nameFROMtable1UNIONSELECTcolumn_nameFROMtable2


说明：1）用于合并两个或多个 SELECT 语句的结果集，并消去表中任何重复行；
     2）UNION 内部的 SELECT 语句必须拥有相同数量的列，列也必须拥有相似的数据类型；
     3）同时，每条 SELECT 语句中的列的顺序必须相同；


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




mysql> select * from staff union select * from staff_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 |
+----+----------+-------+
rows in set (0.00 sec)

mysql> select * from staff union select * from staff_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              |
+----+----------------------+----------------------+
rows in set (0.00 sec)

mysql> select * from staff union select name from staff_1;
ERROR 1222 (21000): The used SELECT statements have a different number of columns

mysql> select * from staff union select * from staff_3;
ERROR 1222 (21000): 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 |
+----+----------+-------+
|  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 |
+----+----------+-------+
rows in set (0.00 sec)





2. UNION ALL


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




mysql> select * from staff union select * from staff_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 |
+----+----------+-------+
rows in set (0.00 sec)

mysql> select * from staff union all select * from staff_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 |
+----+----------+-------+
rows in set (0.00 sec)




			

                        优质内容筛选与推荐>>
1、学习进度（第七周）
2、[C#、winform]  FormDesigner.cs报错The variable 'xxxxxx' is either undeclared or was never assigned
3、第一步  约出来的方法
4、[转]vim 如何编辑 GB2312 编码的文件?
5、asp.net beta 2到RTM的变化
                        
                        
    
                        

          
           
            
             
              
               
                
                 
                 
 
                 
                
               
              
             
            
           
          
         
        
       
      
    
 
     
    
 
    
 
    
 
     
    
 
     
长按二维码向我转账
 
     
 
      
     
受苹果公司新规定影响，微信 iOS 版的赞赏功能被关闭，可通过二维码转账支持公众号。
 
    
 
   
 
   
     
   
     
    
    
      阅读 
      
    
     
       
      好看   
    
     
     
     
     
     
      
     
       已推荐到看一看 
      
     
      
     
        你的朋友可以在“发现”-“看一看”看到你认为好看的文章。 
      
     
       
       
     
     
    
     
    
     
     
     
     
     
      
    
        已取消，“好看”想法已同步删除 
      
     
       
     
     
    
     
   
     
   
     
    
     
     
     
     
     
      
     
       已推荐到看一看 
       和朋友分享想法 
      
     
     
     
     
     
      最多200字，当前共字 
      发送 
     
     
    
     
   
     
   
     
    
     
    
     
      
     
    已发送
     
    
     
   
     
   
     
    
     
     
     
      
     
       
     朋友将在看一看看到 
     
       确定 
      
     
      
     
        分享你的想法...  
      
     
     
     
    
     
    
     
   
     
   
     
    
     
     
     
      
     
       取消 
       
     分享想法到看一看 
     
       确定 
      
     
      
     
        
      
     
      最多200字，当前共字 
     
     
    
     
    
     
   
     
   
     
    
     
    
     
      
     
    发送中
     
    
     
   
     
   
     
    
     
    
     
     
    
       网络异常，请稍后重试 
     
     
      
    
     
   
     
   
     
   
     
  
     
 
     
 
     
 
     
  
     
   
     
   
     
   
     
   
     
   
     
    
     
    
     
    
     
    
     
   
     
  
     
 
     
 
     
  
     
   
     
     
    
    微信扫一扫
    关注该公众号
     
   
     
  
     
 
     

    
                
    
                    
                        
                        0
                        │
                    
                    
                        
                        收藏
                        │
                    
                    
                        
                        举报
                    
                
    
            
    
    
        
    




    
    
    
        
    
            
    
		
            
    
        
    
        
    
    
    


    
    
    
        
    
            
    
                
    联系我们
    
                
    欢迎来到TinyMind。
    
                
    关于TinyMind的内容或商务合作、网站建议，举报不良信息等均可联系我们。
    
                
    TinyMind客服邮箱：support@tinymind.net.cn
    
                
    TinyMind.net.cn，专业AI技术社区