一、单表查询：

单个表的查询方法及语法顺序需要通过实际例子来熟悉

先将表数据创建下：

mysql> create database singe_t1;   # 建个数据库singe_t1
Query OK, 1 row affected (0.01 sec)

mysql> use singe_t1     # 进入数据库singe_t1
Database changed
mysql> create table emp(                 # 创建表emp
    -> id int not null unique auto_increment,
    -> name varchar(20) not null,
    -> sex enum('male','female') not null default 'male',
    -> age int(3) unsigned not null default 28,
    -> hire_date date not null,
    -> post varchar(50),
    -> post_comment varchar(100),
    -> salary int unsigned,
    -> office int,
    -> depart_id int
    -> );
Query OK, 0 rows affected (0.05 sec)    
mysql> desc emp;    #  查看创建的表结构                                                                
+--------------+-----------------------+------+-----+---------+----------------+ 
| Field        | Type                  | Null | Key | Default | Extra          | 
+--------------+-----------------------+------+-----+---------+----------------+ 
| id           | int(11)               | NO   | PRI | NULL    | auto_increment | 
| name         | varchar(20)           | NO   |     | NULL    |                | 
| sex          | enum('male','female') | NO   |     | male    |                | 
| age          | int(3) unsigned       | NO   |     | 28      |                | 
| hire_date    | date                  | NO   |     | NULL    |                | 
| post         | varchar(50)           | YES  |     | NULL    |                | 
| post_comment | varchar(100)          | YES  |     | NULL    |                | 
| salary       | int(10) unsigned      | YES  |     | NULL    |                | 
| office       | int(11)               | YES  |     | NULL    |                | 
| depart_id    | int(11)               | YES  |     | NULL    |                | 
+--------------+-----------------------+------+-----+---------+----------------+ 
# 开始插入表记录
mysql> insert into emp(name,sex,age,hire_date,post,salary,office,d
    -> ('jason','male',18,'20170301','外交部',6600,401,1), #以下是教学部   
    -> ('egon','male',78,'20150302','教学部',1000000,401,1),         
    -> ('kevin','male',81,'20130305','教学部',8300,401,1),           
    -> ('tank','male',73,'20140701','教学部',3500,401,1),            
    -> ('owen','male',28,'20121101','教学部',2100,401,1),            
    -> ('jerry','female',18,'20110211','教学部',9000,401,1),         
    -> ('nick','male',18,'19000301','教学部',30000,401,1),           
    -> ('sean','male',48,'20101111','教学部',10000,401,1),           
    ->                                                            
    -> ('歪歪','female',48,'20150311','销售部',3000,402,2),#以下是销售部门    
    -> ('丫丫','female',38,'20101101','销售部',2000,402,2),            
    -> ('丁丁','female',18,'20110312','销售部',1000,402,2),            
    -> ('星星','female',18,'20160513','销售部',3000,402,2),            
    -> ('格格','female',28,'20170127','销售部',4000,402,2),            
    ->                                                            
    -> ('张野','male',28,'20160311','运营部',10000,403,3), #以下是运营部门    
    -> ('程咬金','male',18,'19970312','运营部',20000,403,3),            
    -> ('程咬银','female',18,'20130311','运营部',19000,403,3),          
    -> ('程咬铜','male',18,'20150411','运营部',18000,403,3),            
    -> ('程咬铁','female',18,'20140512','运营部',17000,403,3)           
    -> ;                                                          
Query OK, 18 rows affected (0.01 sec)                             
Records: 18  Duplicates: 0  Warnings: 0                           
# 最终结果：
mysql> select * from emp;                                                                                 
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+  
| id | name      | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id |  
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+  
|  1 | jason     | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |     
|  2 | egon      | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |     
|  3 | kevin     | male   |  81 | 2013-03-05 | 教学部    | NULL         |    8300 |    401 |         1 |     
|  4 | tank      | male   |  73 | 2014-07-01 | 教学部    | NULL         |    3500 |    401 |         1 |     
|  5 | owen      | male   |  28 | 2012-11-01 | 教学部    | NULL         |    2100 |    401 |         1 |     
|  6 | jerry     | female |  18 | 2011-02-11 | 教学部    | NULL         |    9000 |    401 |         1 |     
|  7 | nick      | male   |  18 | 1900-03-01 | 教学部    | NULL         |   30000 |    401 |         1 |     
|  8 | sean      | male   |  48 | 2010-11-11 | 教学部    | NULL         |   10000 |    401 |         1 |     
|  9 | 歪歪      | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |       
| 10 | 丫丫      | female |  38 | 2010-11-01 | 销售部    | NULL         |    2000 |    402 |         2 |       
| 11 | 丁丁      | female |  18 | 2011-03-12 | 销售部    | NULL         |    1000 |    402 |         2 |       
| 12 | 星星      | female |  18 | 2016-05-13 | 销售部    | NULL         |    3000 |    402 |         2 |       
| 13 | 格格      | female |  28 | 2017-01-27 | 销售部    | NULL         |    4000 |    402 |         2 |       
| 14 | 张野      | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |       
| 15 | 程咬金    | male   |  18 | 1997-03-12 | 运营部    | NULL         |   20000 |    403 |         3 |        
| 16 | 程咬银    | female |  18 | 2013-03-11 | 运营部    | NULL         |   19000 |    403 |         3 |        
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | 运营部    | NULL         |   18000 |    403 |         3 |        
| 18 | 程咬铁    | female |  18 | 2014-05-12 | 运营部    | NULL         |   17000 |    403 |         3 |        
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+  

①、基本语法执行顺序

# 最基本的查询语句：
mysql> select * from emp where id >= 3 and id <= 6;                                                   
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+   
| id | name  | sex    | age | hire_date  | post      | post_comment | salary | office | depart_id |   
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+   
|  3 | kevin | male   |  81 | 2013-03-05 | 教学部    | NULL         |   8300 |    401 |         1 |      
|  4 | tank  | male   |  73 | 2014-07-01 | 教学部    | NULL         |   3500 |    401 |         1 |      
|  5 | owen  | male   |  28 | 2012-11-01 | 教学部    | NULL         |   2100 |    401 |         1 |      
|  6 | jerry | female |  18 | 2011-02-11 | 教学部    | NULL         |   9000 |    401 |         1 |      
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+   
# 基本语句语法的解析：（先后顺序）
from------------> where----------------> select
找到表             查询数据的条件依据        找到数据形成虚拟表

②、where约束条件的使用

# 1.查询id大于等于3小于等于6的数据
mysql> select * from emp where id >= 3 and id <= 6;
mysql> select * from emp where id between 3 and 6;
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+ 
| id | name  | sex    | age | hire_date  | post      | post_comment | salary | office | depart_id | 
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+ 
|  3 | kevin | male   |  81 | 2013-03-05 | 教学部    | NULL         |   8300 |    401 |         1 |    
|  4 | tank  | male   |  73 | 2014-07-01 | 教学部    | NULL         |   3500 |    401 |         1 |    
|  5 | owen  | male   |  28 | 2012-11-01 | 教学部    | NULL         |   2100 |    401 |         1 |    
|  6 | jerry | female |  18 | 2011-02-11 | 教学部    | NULL         |   9000 |    401 |         1 |    
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+ 
# 2.查询薪资是20000或者18000或者17000的数据
mysql> select * from emp where salary in (20000,18000,17000);
mysql> select * from emp where salary=20000 or salary=18000 or salary=17000;
+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
| 15 | 程咬金    | male   |  18 | 1997-03-12 | 运营部    | NULL         |  20000 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | 运营部    | NULL         |  18000 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | 运营部    | NULL         |  17000 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+--------+--------+-----------+
# 3.查询员工姓名中包含o字母的员工姓名和薪资
mysql> select name,salary from emp where name like '%o%';
+-------+---------+
| name  | salary  |
+-------+---------+
| jason |    6600 |
| egon  | 1000000 |
| owen  |    2100 |
+-------+---------+
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
mysql> select name,salary from emp where name like '____';  # like语句里面下划线代表单个通配字符
mysql> select name,salary from emp where char_length(name)=4; # 或者通过计算name字段的长度为4来查询
+------+---------+
| name | salary  |
+------+---------+
| egon | 1000000 |
| tank |    3500 |
| owen |    2100 |
| nick |   30000 |
| sean |   10000 |
+------+---------+
# 5.查询id小于3或者大于6的数据
mysql> select * from emp where id not between 3 and 6;  # 运用between语句
mysql> select * from emp where id < 3 or id >6;  # 运用or语句
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
| id | name      | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id | 
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
|  1 | jason     | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |    
|  2 | egon      | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |    
|  7 | nick      | male   |  18 | 1900-03-01 | 教学部    | NULL         |   30000 |    401 |         1 |    
|  8 | sean      | male   |  48 | 2010-11-11 | 教学部    | NULL         |   10000 |    401 |         1 |    
|  9 | 歪歪      | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |      
| 10 | 丫丫      | female |  38 | 2010-11-01 | 销售部    | NULL         |    2000 |    402 |         2 |      
| 11 | 丁丁      | female |  18 | 2011-03-12 | 销售部    | NULL         |    1000 |    402 |         2 |      
| 12 | 星星      | female |  18 | 2016-05-13 | 销售部    | NULL         |    3000 |    402 |         2 |      
| 13 | 格格      | female |  28 | 2017-01-27 | 销售部    | NULL         |    4000 |    402 |         2 |      
| 14 | 张野      | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |      
| 15 | 程咬金    | male   |  18 | 1997-03-12 | 运营部    | NULL         |   20000 |    403 |         3 |       
| 16 | 程咬银    | female |  18 | 2013-03-11 | 运营部    | NULL         |   19000 |    403 |         3 |       
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | 运营部    | NULL         |   18000 |    403 |         3 |       
| 18 | 程咬铁    | female |  18 | 2014-05-12 | 运营部    | NULL         |   17000 |    403 |         3 |       
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
# 6.查询薪资不在20000，18000，17000范围的数据
mysql> select * from emp where salary not in (20000,18000,17000);
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
|  1 | jason     | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |   
|  2 | egon      | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |   
|  3 | kevin     | male   |  81 | 2013-03-05 | 教学部    | NULL         |    8300 |    401 |         1 |   
|  4 | tank      | male   |  73 | 2014-07-01 | 教学部    | NULL         |    3500 |    401 |         1 |   
|  5 | owen      | male   |  28 | 2012-11-01 | 教学部    | NULL         |    2100 |    401 |         1 |   
|  6 | jerry     | female |  18 | 2011-02-11 | 教学部    | NULL         |    9000 |    401 |         1 |   
|  7 | nick      | male   |  18 | 1900-03-01 | 教学部    | NULL         |   30000 |    401 |         1 |   
|  8 | sean      | male   |  48 | 2010-11-11 | 教学部    | NULL         |   10000 |    401 |         1 |   
|  9 | 歪歪      | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |     
| 10 | 丫丫      | female |  38 | 2010-11-01 | 销售部    | NULL         |    2000 |    402 |         2 |     
| 11 | 丁丁      | female |  18 | 2011-03-12 | 销售部    | NULL         |    1000 |    402 |         2 |     
| 12 | 星星      | female |  18 | 2016-05-13 | 销售部    | NULL         |    3000 |    402 |         2 |     
| 13 | 格格      | female |  28 | 2017-01-27 | 销售部    | NULL         |    4000 |    402 |         2 |     
| 14 | 张野      | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |     
| 16 | 程咬银    | female |  18 | 2013-03-11 | 运营部    | NULL         |   19000 |    403 |         3 |      
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+
# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
mysql> select name,post from emp where post_comment is null;
+-----------+-----------+   
| name      | post      |   
+-----------+-----------+   
| jason     | 外交部    |      
| egon      | 教学部    |      
| kevin     | 教学部    |      
| tank      | 教学部    |      
| owen      | 教学部    |      
| jerry     | 教学部    |      
| nick      | 教学部    |      
| sean      | 教学部    |      
| 歪歪      | 销售部    |        
| 丫丫      | 销售部    |        
| 丁丁      | 销售部    |        
| 星星      | 销售部    |        
| 格格      | 销售部    |        
| 张野      | 运营部    |        
| 程咬金    | 运营部    |         
| 程咬银    | 运营部    |         
| 程咬铜    | 运营部    |         
| 程咬铁    | 运营部    |         
+-----------+-----------+

③、group by 分组

# 数据有时候都有相似性，这个相似性有时候就是我们查询同一类数据的依据，大部分情况下，数据的相似可以说就是数据分组的意义所在，比如每个部门，男女、每个地方、、、等等。
# 按部门分组
mysql> select * from emp group by post;                                                               
+----+--------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
| id | name   | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id | 
+----+--------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
|  1 | jason  | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |    
|  2 | egon   | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |    
| 14 | 张野   | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |      
|  9 | 歪歪   | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |      
+----+--------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
mysql> select id,name,sex from emp group by post;
+----+--------+--------+
| id | name   | sex    |
+----+--------+--------+
|  1 | jason  | male   |
|  2 | egon   | male   |
| 14 | 张野   | male   |
|  9 | 歪歪   | female |
+----+--------+--------+
可以看出以上按部门分组取出的数据都是能找到的部门分组数据的第一条，这样做肯定是不合理的，因为我只是要查询分组的数据，你给我每个分组的第一条id的数据有何用。
所以需要设置sql_model 为only_full_group_by,这样意味着以后但凡分组，只能取到分组的依据，不应该去取组里面的单个元素的值。
mysql> set global sql_mode='strict_trans_tables,only_full_group_by';  # 设置分组严格模式
mysql> exit     # 退出才能生效
Bye    
mysql -uroot -p
Enter password: ******
......
mysql> use singe_t1
Database changed
mysql> show variables like '%sql_mode%';   # 查看是否生效
+---------------+----------------------------------------+  
| Variable_name | Value                                  |  
+---------------+----------------------------------------+  
| sql_mode      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES |  
+---------------+----------------------------------------+  
# 此时如果按照以前的分组查询方式就会报错
mysql> select * from emp group by post;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'singe_t1.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
# 必须 按分组查询只显示post字段名才行：
mysql> select post from emp group by post;   
+-----------+                                
| post      |                                
+-----------+                                
| 外交部    |                                   
| 教学部    |                                   
| 运营部    |                                   
| 销售部    |                                   
+-----------+   
 # 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
-------------------------------------------------------------------------------
接下来开始用实例来熟悉分组的运用：
# 2.获取每个部门的最高工资  
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
mysql> select post,max(salary) from emp group by post;
+-----------+-------------+
| post      | max(salary) |
+-----------+-------------+
| 外交部    |        6600 |
| 教学部    |     1000000 |
| 运营部    |       20000 |
| 销售部    |        4000 |
+-----------+-------------+
# 每个部门的最低工资
mysql> select post,min(salary) from emp group by post;
+-----------+-------------+
| post      | min(salary) |
+-----------+-------------+
| 外交部    |        6600 |
| 教学部    |        2100 |
| 运营部    |       10000 |
| 销售部    |        1000 |
+-----------+-------------+
# 每个部门的平均工资
mysql> select post,avg(salary) from emp group by post;
+-----------+-------------+
| post      | avg(salary) |
+-----------+-------------+
| 外交部    |   6600.0000 |
| 教学部    | 151842.8571 |
| 运营部    |  16800.0000 |
| 销售部    |   2600.0000 |
+-----------+-------------+
# 每个部门的工资总和
mysql> select post,sum(salary) from emp group by post;      
+-----------+-------------+                                 
| post      | sum(salary) |                                 
+-----------+-------------+                                 
| 外交部    |        6600 |                                    
| 教学部    |     1062900 |                                    
| 运营部    |       84000 |                                    
| 销售部    |       13000 |                                    
+-----------+-------------+                                 
# 每个部门的人数
mysql> select post,count(id) from emp group by post;
+-----------+-----------+
| post      | count(id) |
+-----------+-----------+
| 外交部    |         1 |
| 教学部    |         7 |
| 运营部    |         5 |
| 销售部    |         5 |
+-----------+-----------+
-------------------------------------------------------------------------------
# 3.查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
mysql> select post,group_concat(name) from emp group by post;                  
+-----------+------------------------------------------------+                 
| post      | group_concat(name)                             |                 
+-----------+------------------------------------------------+                 
| 外交部    | jason                                          |                    
| 教学部    | egon,kevin,tank,owen,jerry,nick,sean           |                    
| 运营部    | 张野,程咬金,程咬银,程咬铜,程咬铁               |                                  
| 销售部    | 歪歪,丫丫,丁丁,星星,格格                       |                              
+-----------+------------------------------------------------+                 
                                                                                                                               
mysql> select post,group_concat(name) as 部门全部人员 from emp group by post;        
+-----------+------------------------------------------------+                 
| post      | 部门全部人员                                   |                       
+-----------+------------------------------------------------+                 
| 外交部    | jason                                          |                    
| 教学部    | egon,kevin,tank,owen,jerry,nick,sean           |                    
| 运营部    | 张野,程咬金,程咬银,程咬铜,程咬铁               |                                  
| 销售部    | 歪歪,丫丫,丁丁,星星,格格                       |                              
+-----------+------------------------------------------------+                 
 mysql> select post,group_concat(name,'__') from emp group by post;
+-----------+----------------------------------------------------------+
| post      | group_concat(name,'__')                                  |
+-----------+----------------------------------------------------------+
| 外交部    | jason__                                                  |
| 教学部    | egon__,kevin__,tank__,owen__,jerry__,nick__,sean__       |
| 运营部    | 张野__,程咬金__,程咬银__,程咬铜__,程咬铁__               |
| 销售部    | 歪歪__,丫丫__,丁丁__,星星__,格格__                       |
+-----------+----------------------------------------------------------+
mysql> select post,group_concat(name,':',salary) from emp group by post;
+-----------+------------------------------------------------------------------------------+
| post      | group_concat(name,':',salary)                                                |
+-----------+------------------------------------------------------------------------------+
| 外交部    | jason:6600                                                                   |
| 教学部    | egon:1000000,kevin:8300,tank:3500,owen:2100,jerry:9000,nick:30000,sean:10000 |
| 运营部    | 张野:10000,程咬金:20000,程咬银:19000,程咬铜:18000,程咬铁:17000               |
| 销售部    | 歪歪:3000,丫丫:2000,丁丁:1000,星星:3000,格格:4000                            |
+-----------+------------------------------------------------------------------------------+
mysql> select post,group_concat(salary) as 薪资 from emp group by post;    
+-----------+-----------------------------------------+                  
| post      | 薪资                                    |                    
+-----------+-----------------------------------------+                  
| 外交部    | 6600                                    |                     
| 教学部    | 1000000,8300,3500,2100,9000,30000,10000 |                     
| 运营部    | 10000,20000,19000,18000,17000           |                     
| 销售部    | 3000,2000,1000,3000,4000                |                     
+-----------+-----------------------------------------+                  
 ----------------------------------------------------------------------------
# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用(concat_ws(':',字段1，字段2，字段3)用冒号拼接后面字段）
mysql> select name as 姓名,salary as 薪资 from emp where post='教学部';        
+--------+---------+                                                    
| 姓名   | 薪资    |                                                        
+--------+---------+                                                    
| egon   | 1000000 |                                                    
| kevin  |    8300 |                                                    
| tank   |    3500 |                                                    
| owen   |    2100 |                                                    
| jerry  |    9000 |                                                    
| nick   |   30000 |                                                    
| sean   |   10000 |                                                    
+--------+---------+       
mysql> select concat('姓名:',name) 姓名,concat('工资:',salary) as 薪资 from emp where post='教学部';
+--------------+----------------+
| 姓名         | 薪资           |
+--------------+----------------+
| 姓名:egon    | 工资:1000000   |
| 姓名:kevin   | 工资:8300      |
| 姓名:tank    | 工资:3500      |
| 姓名:owen    | 工资:2100      |
| 姓名:jerry   | 工资:9000      |
| 姓名:nick    | 工资:30000     |
| 姓名:sean    | 工资:10000     |
+--------------+----------------+
 # 补充as语法 即可以给字段起别名也可以给表起
mysql> create table t2(id int,name char(16));  # 建个t2表
Query OK, 0 rows affected (0.03 sec)   
mysql> show tables;  #  该数据库内有2个表
+--------------------+ 
| Tables_in_singe_t1 | 
+--------------------+ 
| emp                | 
| t2                 | 
+--------------------+ 
mysql> select emp.id,emp.name from emp as t1; # 如果将emp起名为t1，那么前面的emp也得改为起名的t1才能找到id和name，否则报错
ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'   
mysql> select t1.id,t1.name from emp as t1 where id between 1 and 6;
+----+-------+
| id | name  |
+----+-------+
|  1 | jason |
|  2 | egon  |
|  3 | kevin |
|  4 | tank  |
|  5 | owen  |
|  6 | jerry |
+----+-------+
小测试：当把表名起名为库中已有的表t2呢？是否会报错？
mysql> select t2.id,t2.name from emp as t2 where id between 1 and 6; 
+----+-------+                                                       
| id | name  |                                                       
+----+-------+                                                       
|  1 | jason |                                                       
|  2 | egon  |                                                       
|  3 | kevin |                                                       
|  4 | tank  |                                                       
|  5 | owen  |                                                       
|  6 | jerry |                                                       
+----+-------+                                                       
结果是不会报错
分析：查询表应该是将结果放进内存中然后显示的，这样起名只是临时性的，显示的结果也是临时的，所以和数据库中实际表名没有关系。只在查询那段语句中起作用。互不影响。
--------------------------------------------------------------------------------
# 查询四则运算
# 查询每个人的年薪
mysql> select name as 员工,salary*12 as 年薪 from emp where id between 1 and 6;      
+--------+----------+                                                            
| 员工   | 年薪     |                                                                
+--------+----------+                                                            
| jason  |    79200 |                                                            
| egon   | 12000000 |                                                            
| kevin  |    99600 |                                                            
| tank   |    42000 |                                                            
| owen   |    25200 |                                                            
| jerry  |   108000 |                                                            
+--------+----------+                                                            
友情提醒：上述的起名语句中的as可以省略，但是省略后会减弱查询语句的可读性，建议还是不省略好。

④、having （类似于where，但是必须在group by语句后使用）

having的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！

1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门
mysql> select post,avg(salary) from emp where age > 30 group by post having avg(salary)>10000;
+-----------+-------------+
| post      | avg(salary) |
+-----------+-------------+
| 教学部    | 255450.0000 |
+-----------+-------------+

⑤、distinct 去重

对有重复的展示数据进行去重操作

mysql> create table tt1(id int,name char(16));
mysql> insert into tt1 values (1,'张三'),(2,'王五'),(2,'王五'),(3,'张三'),(1,'赵六');
mysql> select * from tt1;
+------+--------+        
| id   | name   |        
+------+--------+        
|    1 | 张三   |          
|    2 | 王五   |          
|    2 | 王五   |          
|    3 | 张三   |          
|    1 | 赵六   |          
+------+--------+        
mysql> select distinct * from tt1;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
|    2 | 王五   |
|    3 | 张三   |
|    1 | 赵六   |
+------+--------+
mysql> select distinct name from tt1;
+--------+
| name   |
+--------+
| 张三   |
| 王五   |
| 赵六   |
+--------+
mysql> select distinct id from tt1;  
+------+                             
| id   |                             
+------+                             
|    1 |                             
|    2 |                             
|    3 |                             
+------+

⑥、order by (排序）

mysql> select * from emp order by salary; # 按照工资升序排序
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
| id | name      | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id | 
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
| 11 | 丁丁      | female |  18 | 2011-03-12 | 销售部    | NULL         |    1000 |    402 |         2 |      
| 10 | 丫丫      | female |  38 | 2010-11-01 | 销售部    | NULL         |    2000 |    402 |         2 |      
|  5 | owen      | male   |  28 | 2012-11-01 | 教学部    | NULL         |    2100 |    401 |         1 |    
|  9 | 歪歪      | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |      
| 12 | 星星      | female |  18 | 2016-05-13 | 销售部    | NULL         |    3000 |    402 |         2 |      
|  4 | tank      | male   |  73 | 2014-07-01 | 教学部    | NULL         |    3500 |    401 |         1 |    
| 13 | 格格      | female |  28 | 2017-01-27 | 销售部    | NULL         |    4000 |    402 |         2 |      
|  1 | jason     | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |    
|  3 | kevin     | male   |  81 | 2013-03-05 | 教学部    | NULL         |    8300 |    401 |         1 |    
|  6 | jerry     | female |  18 | 2011-02-11 | 教学部    | NULL         |    9000 |    401 |         1 |    
|  8 | sean      | male   |  48 | 2010-11-11 | 教学部    | NULL         |   10000 |    401 |         1 |    
| 14 | 张野      | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |      
| 18 | 程咬铁    | female |  18 | 2014-05-12 | 运营部    | NULL         |   17000 |    403 |         3 |       
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | 运营部    | NULL         |   18000 |    403 |         3 |       
| 16 | 程咬银    | female |  18 | 2013-03-11 | 运营部    | NULL         |   19000 |    403 |         3 |       
| 15 | 程咬金    | male   |  18 | 1997-03-12 | 运营部    | NULL         |   20000 |    403 |         3 |       
|  7 | nick      | male   |  18 | 1900-03-01 | 教学部    | NULL         |   30000 |    401 |         1 |    
|  2 | egon      | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |    
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+ 
mysql> select * from emp order by age,salary;    # 首选排序规则为age，当排完序的age中有相同的时，在相同age的几个记录中进行salary排序。                                                         
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+  
| id | name      | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id |  
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+  
| 11 | 丁丁      | female |  18 | 2011-03-12 | 销售部    | NULL         |    1000 |    402 |         2 |       
| 12 | 星星      | female |  18 | 2016-05-13 | 销售部    | NULL         |    3000 |    402 |         2 |       
|  1 | jason     | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |     
|  6 | jerry     | female |  18 | 2011-02-11 | 教学部    | NULL         |    9000 |    401 |         1 |     
| 18 | 程咬铁    | female |  18 | 2014-05-12 | 运营部    | NULL         |   17000 |    403 |         3 |        
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | 运营部    | NULL         |   18000 |    403 |         3 |        
| 16 | 程咬银    | female |  18 | 2013-03-11 | 运营部    | NULL         |   19000 |    403 |         3 |        
| 15 | 程咬金    | male   |  18 | 1997-03-12 | 运营部    | NULL         |   20000 |    403 |         3 |        
|  7 | nick      | male   |  18 | 1900-03-01 | 教学部    | NULL         |   30000 |    401 |         1 |     
|  5 | owen      | male   |  28 | 2012-11-01 | 教学部    | NULL         |    2100 |    401 |         1 |     
| 13 | 格格      | female |  28 | 2017-01-27 | 销售部    | NULL         |    4000 |    402 |         2 |       
| 14 | 张野      | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |       
| 10 | 丫丫      | female |  38 | 2010-11-01 | 销售部    | NULL         |    2000 |    402 |         2 |       
|  9 | 歪歪      | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |       
|  8 | sean      | male   |  48 | 2010-11-11 | 教学部    | NULL         |   10000 |    401 |         1 |     
|  4 | tank      | male   |  73 | 2014-07-01 | 教学部    | NULL         |    3500 |    401 |         1 |     
|  2 | egon      | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |     
|  3 | kevin     | male   |  81 | 2013-03-05 | 教学部    | NULL         |    8300 |    401 |         1 |     
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+  
#  默认为升序，如果想要降序，在后面加上desc。
mysql> select * from emp order by salary desc;                                                              
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+    
| id | name      | sex    | age | hire_date  | post      | post_comment | salary  | office | depart_id |    
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+    
|  2 | egon      | male   |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |       
|  7 | nick      | male   |  18 | 1900-03-01 | 教学部    | NULL         |   30000 |    401 |         1 |       
| 15 | 程咬金    | male   |  18 | 1997-03-12 | 运营部    | NULL         |   20000 |    403 |         3 |          
| 16 | 程咬银    | female |  18 | 2013-03-11 | 运营部    | NULL         |   19000 |    403 |         3 |          
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | 运营部    | NULL         |   18000 |    403 |         3 |          
| 18 | 程咬铁    | female |  18 | 2014-05-12 | 运营部    | NULL         |   17000 |    403 |         3 |          
|  8 | sean      | male   |  48 | 2010-11-11 | 教学部    | NULL         |   10000 |    401 |         1 |       
| 14 | 张野      | male   |  28 | 2016-03-11 | 运营部    | NULL         |   10000 |    403 |         3 |         
|  6 | jerry     | female |  18 | 2011-02-11 | 教学部    | NULL         |    9000 |    401 |         1 |       
|  3 | kevin     | male   |  81 | 2013-03-05 | 教学部    | NULL         |    8300 |    401 |         1 |       
|  1 | jason     | male   |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |       
| 13 | 格格      | female |  28 | 2017-01-27 | 销售部    | NULL         |    4000 |    402 |         2 |         
|  4 | tank      | male   |  73 | 2014-07-01 | 教学部    | NULL         |    3500 |    401 |         1 |       
|  9 | 歪歪      | female |  48 | 2015-03-11 | 销售部    | NULL         |    3000 |    402 |         2 |         
| 12 | 星星      | female |  18 | 2016-05-13 | 销售部    | NULL         |    3000 |    402 |         2 |         
|  5 | owen      | male   |  28 | 2012-11-01 | 教学部    | NULL         |    2100 |    401 |         1 |       
| 10 | 丫丫      | female |  38 | 2010-11-01 | 销售部    | NULL         |    2000 |    402 |         2 |         
| 11 | 丁丁      | female |  18 | 2011-03-12 | 销售部    | NULL         |    1000 |    402 |         2 |         
+----+-----------+--------+-----+------------+-----------+--------------+---------+--------+-----------+    
# 来个复杂的：
# 统计各部门年龄在25岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post,avg(salary) from emp
    -> where age>25
    -> group by post
    -> having avg(salary)>1000
    -> order by avg(salary) desc;
+-----------+-------------+
| post      | avg(salary) |
+-----------+-------------+
| 教学部    | 204780.0000 |
| 运营部    |  10000.0000 |
| 销售部    |   3000.0000 |
+-----------+-------------+

⑦、limit （限制显示条数）

# 限制展示条数 limit 参数1    limit 参数1，参数2
mysql> select * from emp limit 5;     # 单个数字5代表从最前面开始显示5条                                                               
+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+  
| id | name  | sex  | age | hire_date  | post      | post_comment | salary  | office | depart_id |  
+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+  
|  1 | jason | male |  18 | 2017-03-01 | 外交部    | NULL         |    6600 |    401 |         1 |     
|  2 | egon  | male |  78 | 2015-03-02 | 教学部    | NULL         | 1000000 |    401 |         1 |     
|  3 | kevin | male |  81 | 2013-03-05 | 教学部    | NULL         |    8300 |    401 |         1 |     
|  4 | tank  | male |  73 | 2014-07-01 | 教学部    | NULL         |    3500 |    401 |         1 |     
|  5 | owen  | male |  28 | 2012-11-01 | 教学部    | NULL         |    2100 |    401 |         1 |     
+----+-------+------+-----+------------+-----------+--------------+---------+--------+-----------+                                                                             
                                                                                                    
mysql> select * from emp limit 2,6;    # 从起始行数为2开始往后显示6行，这里不包含其实行数2。                                                             
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+ 
| id | name  | sex    | age | hire_date  | post      | post_comment | salary | office | depart_id | 
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+ 
|  3 | kevin | male   |  81 | 2013-03-05 | 教学部    | NULL         |   8300 |    401 |         1 |    
|  4 | tank  | male   |  73 | 2014-07-01 | 教学部    | NULL         |   3500 |    401 |         1 |    
|  5 | owen  | male   |  28 | 2012-11-01 | 教学部    | NULL         |   2100 |    401 |         1 |    
|  6 | jerry | female |  18 | 2011-02-11 | 教学部    | NULL         |   9000 |    401 |         1 |    
|  7 | nick  | male   |  18 | 1900-03-01 | 教学部    | NULL         |  30000 |    401 |         1 |    
|  8 | sean  | male   |  48 | 2010-11-11 | 教学部    | NULL         |  10000 |    401 |         1 |    
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+

8、正则（没错，表查询也能用正则匹配）

# 查询记录，其中匹配名字条件为以j开头，n或者y结尾的名字
mysql> select * from emp where name regexp '^j.*(n|y)$';                                             
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+  
| id | name  | sex    | age | hire_date  | post      | post_comment | salary | office | depart_id |  
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+  
|  1 | jason | male   |  18 | 2017-03-01 | 外交部    | NULL         |   6600 |    401 |         1 |     
|  6 | jerry | female |  18 | 2011-02-11 | 教学部    | NULL         |   9000 |    401 |         1 |     
+----+-------+--------+-----+------------+-----------+--------------+--------+--------+-----------+

二、多表查询

多个表之间的查询一般都是在表之间存在某种逻辑关联的情况下进行的查询，这种逻辑上的关联其实就是表中某个字段名和另外一个表中的字段名存在一个一一对应的关系或者关联。

先创建2张表作为示例
mysql> #建表                                                           
mysql> create table dep(                                             
    -> id int,                                                       
    -> name varchar(20)                                              
    -> );                                                            
Query OK, 0 rows affected (0.04 sec)                                 
                                                                     
mysql>                                                               
mysql> create table emp(                                             
    -> id int primary key auto_increment,                            
    -> name varchar(20),                                             
    -> sex enum('male','female') not null default 'male',            
    -> age int,                                                      
    -> dep_id int                                                    
    -> );                                                            
Query OK, 0 rows affected (0.04 sec)                                 
mysql> #插入数据

栏目列表

首页 > Python基础教程 >

python基础教程之MySQL之单表查询、多表查询