随笔- 364 文章- 0 评论- 79 阅读- 94万

mysql四-1：单表查询

一、单表查询的语法

SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

二、关键字的执行优先级（重点）

1、from  库.表——找到表
 
2、where  条件——按照where指定的约束条件，去表中取出一条条记录
 
3、group by  分组条件——对取出的一条条记录分组，如果没有group by，整体作为一组
 
4、having  过滤——将分组的结果进行过滤
 
5、select——从虚拟表选择出需要的内容
 
6、distinct——去重，如果在查询中指定了DISTINCT子句，则会创建一张内存临时表（如果内存放不下，就需要存放在硬盘了）。这张临时表的表结构和上一步产生的虚拟表VT7是一样的，不同的是对进行DISTINCT操作的列增加了一个唯一索引，以此来除重复数据。
 
7、order by 排序字段——对虚拟表中的内容按照指定的列进行排序，然后返回一个新的虚拟表
 
8、limit n;——限制结果的显示条数，LIMIT子句从上一步得到的VT8虚拟表中选出从指定位置开始的指定行数据。对于没有应用ORDER BY的LIMIT子句，得到的结果同样是无序的，所以，很多时候，我们都会看到LIMIT子句会和ORDER BY子句一起使用

详细见：http://www.cnblogs.com/linhaifeng/articles/7372774.html

三、简单查询

company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
create table employee(
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 double(15,2),
office int, #一个部门一个屋子
depart_id int
);


#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| 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       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

准备表和记录

#简单查询
    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;

#避免重复DISTINCT
    SELECT DISTINCT post FROM employee;    

#通过四则运算查询
    SELECT name, salary*12 FROM employee;
    SELECT name, salary*12 AS Annual_salary FROM employee;
    SELECT name, salary*12 Annual_salary FROM employee;

#定义显示格式
   CONCAT() 函数用于连接字符串
   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS() 第一个参数为分隔符
   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
   FROM employee;

   结合CASE语句：
   SELECT
       (
           CASE
           WHEN NAME = 'egon' THEN
               NAME
           WHEN NAME = 'alex' THEN
               CONCAT(name,'_BIGSB')
           ELSE
               concat(NAME, 'SB')
           END
       ) as new_name
   FROM
       emp;

mysql> select * from employee;                                                                                                                       +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

mysql> select id,name,salary from employee;
+----+------------+------------+
| id | name       | salary     |
+----+------------+------------+
|  1 | egon       |    7300.33 |
|  2 | alex       | 1000000.31 |
|  3 | wupeiqi    |    8300.00 |
|  4 | yuanhao    |    3500.00 |
|  5 | liwenzhou  |    2100.00 |
|  6 | jingliyang |    9000.00 |
|  7 | jinxin     |   30000.00 |
|  8 | 成龙       |   10000.00 |
|  9 | 歪歪       |    3000.13 |
| 10 | 丫丫       |    2000.35 |
| 11 | 丁丁       |    1000.37 |
| 12 | 星星       |    3000.29 |
| 13 | 格格       |    4000.33 |
| 14 | 张野       |   10000.13 |
| 15 | 程咬金     |   20000.00 |
| 16 | 程咬银     |   19000.00 |
| 17 | 程咬铜     |   18000.00 |
| 18 | 程咬铁     |   17000.00 |
+----+------------+------------+
18 rows in set (0.00 sec)

简单查询

　　在需要去重查询时，可以利用distinct去除重复信息。

# distinct去除重复的职位信息
mysql> select distinct post from employee;
+-----------------------------------------+
| post                                    |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使              |
| teacher                                 |
| sale                                    |
| operation                               |
+-----------------------------------------+
4 rows in set (0.00 sec)

避免重复DISTINCT

　　利用四则运算查询（加减乘除）得到需要的查询结果。针对四则运算的字段还可以取别名，以优化显示结果。

# 查看一个人的年薪
mysql> select name, salary*12 from employee;
+------------+-------------+
| name       | salary*12   |
+------------+-------------+
| egon       |    87603.96 |
| alex       | 12000003.72 |
| wupeiqi    |    99600.00 |
| yuanhao    |    42000.00 |
| liwenzhou  |    25200.00 |
| jingliyang |   108000.00 |
| jinxin     |   360000.00 |
| 成龙       |   120000.00 |
| 歪歪       |    36001.56 |
| 丫丫       |    24004.20 |
| 丁丁       |    12004.44 |
| 星星       |    36003.48 |
| 格格       |    48003.96 |
| 张野       |   120001.56 |
| 程咬金     |   240000.00 |
| 程咬银     |   228000.00 |
| 程咬铜     |   216000.00 |
| 程咬铁     |   204000.00 |
+------------+-------------+
18 rows in set (0.00 sec)

# 给年薪起一个别名
mysql> select name, salary*12 as Annual_salary from employee;
+------------+---------------+
| name       | Annual_salary |
+------------+---------------+
| egon       |      87603.96 |
| alex       |   12000003.72 |
| wupeiqi    |      99600.00 |
| yuanhao    |      42000.00 |
| liwenzhou  |      25200.00 |
| jingliyang |     108000.00 |
| jinxin     |     360000.00 |
| 成龙       |     120000.00 |
| 歪歪       |      36001.56 |
| 丫丫       |      24004.20 |
| 丁丁       |      12004.44 |
| 星星       |      36003.48 |
| 格格       |      48003.96 |
| 张野       |     120001.56 |
| 程咬金     |     240000.00 |
| 程咬银     |     228000.00 |
| 程咬铜     |     216000.00 |
| 程咬铁     |     204000.00 |
+------------+---------------+
18 rows in set (0.00 sec)

# 起别名不加as的方法
mysql> select name, salary*12 Annual_salary from employee;
+------------+---------------+
| name       | Annual_salary |
+------------+---------------+
| egon       |      87603.96 |
| alex       |   12000003.72 |
| wupeiqi    |      99600.00 |
| yuanhao    |      42000.00 |
| liwenzhou  |      25200.00 |
| jingliyang |     108000.00 |
| jinxin     |     360000.00 |
| 成龙       |     120000.00 |
| 歪歪       |      36001.56 |
| 丫丫       |      24004.20 |
| 丁丁       |      12004.44 |
| 星星       |      36003.48 |
| 格格       |      48003.96 |
| 张野       |     120001.56 |
| 程咬金     |     240000.00 |
| 程咬银     |     228000.00 |
| 程咬铜     |     216000.00 |
| 程咬铁     |     204000.00 |
+------------+---------------+
18 rows in set (0.00 sec)

四则运算及取别名方法

　　利用concat和逗号自由定义显示格式

# concat()函数用于连接字符串
mysql> select concat('姓名:',name) from employee;
+------------------------+
| concat('姓名:',name)   |
+------------------------+
| 姓名:egon              |
| 姓名:alex              |
| 姓名:wupeiqi           |
| 姓名:yuanhao           |
| 姓名:liwenzhou         |
| 姓名:jingliyang        |
| 姓名:jinxin            |
| 姓名:成龙              |
| 姓名:歪歪              |
| 姓名:丫丫              |
| 姓名:丁丁              |
| 姓名:星星              |
| 姓名:格格              |
| 姓名:张野              |
| 姓名:程咬金            |
| 姓名:程咬银            |
| 姓名:程咬铜            |
| 姓名:程咬铁            |
+------------------------+
18 rows in set (0.00 sec)

mysql> select concat('姓名:',name,'性别:',sex,'年薪:',salary*12) as emp_info_salary from employee;   # as设置查询结果标题
+------------------------------------------------+
| emp_info_salary                                |
+------------------------------------------------+
| 姓名:egon性别:male年薪:87603.96                |
| 姓名:alex性别:male年薪:12000003.72             |
| 姓名:wupeiqi性别:male年薪:99600.00             |
| 姓名:yuanhao性别:male年薪:42000.00             |
| 姓名:liwenzhou性别:male年薪:25200.00           |
| 姓名:jingliyang性别:female年薪:108000.00       |
| 姓名:jinxin性别:male年薪:360000.00             |
| 姓名:成龙性别:male年薪:120000.00               |
| 姓名:歪歪性别:female年薪:36001.56              |
| 姓名:丫丫性别:female年薪:24004.20              |
| 姓名:丁丁性别:female年薪:12004.44              |
| 姓名:星星性别:female年薪:36003.48              |
| 姓名:格格性别:female年薪:48003.96              |
| 姓名:张野性别:male年薪:120001.56               |
| 姓名:程咬金性别:male年薪:240000.00             |
| 姓名:程咬银性别:female年薪:228000.00           |
| 姓名:程咬铜性别:male年薪:216000.00             |
| 姓名:程咬铁性别:female年薪:204000.00           |
+------------------------------------------------+
18 rows in set (0.00 sec)

# 想把薪资单独分出来，用concat和逗号拼接出想要的格式
mysql> select concat('姓名:',name,'性别:',sex) as info, concat('年薪:', salary*12) as annual_salary from employee;                                   
+--------------------------------+--------------------+
| info                           | annual_salary      |
+--------------------------------+--------------------+
| 姓名:egon性别:male             | 年薪:87603.96      |
| 姓名:alex性别:male             | 年薪:12000003.72   |
| 姓名:wupeiqi性别:male          | 年薪:99600.00      |
| 姓名:yuanhao性别:male          | 年薪:42000.00      |
| 姓名:liwenzhou性别:male        | 年薪:25200.00      |
| 姓名:jingliyang性别:female     | 年薪:108000.00     |
| 姓名:jinxin性别:male           | 年薪:360000.00     |
| 姓名:成龙性别:male             | 年薪:120000.00     |
| 姓名:歪歪性别:female           | 年薪:36001.56      |
| 姓名:丫丫性别:female           | 年薪:24004.20      |
| 姓名:丁丁性别:female           | 年薪:12004.44      |
| 姓名:星星性别:female           | 年薪:36003.48      |
| 姓名:格格性别:female           | 年薪:48003.96      |
| 姓名:张野性别:male             | 年薪:120001.56     |
| 姓名:程咬金性别:male           | 年薪:240000.00     |
| 姓名:程咬银性别:female         | 年薪:228000.00     |
| 姓名:程咬铜性别:male           | 年薪:216000.00     |
| 姓名:程咬铁性别:female         | 年薪:204000.00     |
+--------------------------------+--------------------+
18 rows in set (0.00 sec)

concat()函数连接字符串

# concat_ws()  第一个参数为分隔符，处理多条记录+分隔符更方便
mysql> select concat_ws(':', name, sex, salary) as emp_info from employee;
+---------------------------+
| emp_info                  |
+---------------------------+
| egon:male:7300.33         |
| alex:male:1000000.31      |
| wupeiqi:male:8300.00      |
| yuanhao:male:3500.00      |
| liwenzhou:male:2100.00    |
| jingliyang:female:9000.00 |
| jinxin:male:30000.00      |
| 成龙:male:10000.00        |
| 歪歪:female:3000.13       |
| 丫丫:female:2000.35       |
| 丁丁:female:1000.37       |
| 星星:female:3000.29       |
| 格格:female:4000.33       |
| 张野:male:10000.13        |
| 程咬金:male:20000.00      |
| 程咬银:female:19000.00    |
| 程咬铜:male:18000.00      |
| 程咬铁:female:17000.00    |
+---------------------------+
18 rows in set (0.00 sec)

concat_ws()优化处理记录和分隔符

　　结合case语句，自定义复杂的显示格式。

# 结合CASE语句
mysql> SELECT
    -> (
    ->        CASE
    ->        WHEN NAME = 'egon' THEN
    ->            NAME
    ->        WHEN NAME = 'alex' THEN
    ->            CONCAT(name,'_BIGSB')
    ->        ELSE
    ->            concat(NAME, 'SB')
    ->        END
    ->    ) as new_name
    -> FROM
    ->    employee;
+--------------+
| new_name     |
+--------------+
| egon         |
| alex_BIGSB   |
| wupeiqiSB    |
| yuanhaoSB    |
| liwenzhouSB  |
| jingliyangSB |
| jinxinSB     |
| 成龙SB       |
| 歪歪SB       |
| 丫丫SB       |
| 丁丁SB       |
| 星星SB       |
| 格格SB       |
| 张野SB       |
| 程咬金SB     |
| 程咬银SB     |
| 程咬铜SB     |
| 程咬铁SB     |
+--------------+
18 rows in set (0.00 sec)

CASE语句自定义查询结果

小练习：

　　1、查出所有员工的名字，薪资，格式为 <名字:egon> <薪资:3000>

　　2、查出所有岗位(去掉重复)

　　3、查出所有员工名字，以及他们的年薪，年薪字段名为annual_year

mysql> select concat("<名字:",name,">") as NAME, concat("<薪资:",salary,">") as SALARY from employee;          
+---------------------+---------------------+
| NAME                | SALARY              |
+---------------------+---------------------+
| <名字:egon>         | <薪资:7300.33>      |
| <名字:alex>         | <薪资:1000000.31>   |
| <名字:wupeiqi>      | <薪资:8300.00>      |
| <名字:yuanhao>      | <薪资:3500.00>      |
| <名字:liwenzhou>    | <薪资:2100.00>      |
| <名字:jingliyang>   | <薪资:9000.00>      |
| <名字:jinxin>       | <薪资:30000.00>     |
| <名字:成龙>         | <薪资:10000.00>     |
| <名字:歪歪>         | <薪资:3000.13>      |
| <名字:丫丫>         | <薪资:2000.35>      |
| <名字:丁丁>         | <薪资:1000.37>      |
| <名字:星星>         | <薪资:3000.29>      |
| <名字:格格>         | <薪资:4000.33>      |
| <名字:张野>         | <薪资:10000.13>     |
| <名字:程咬金>       | <薪资:20000.00>     |
| <名字:程咬银>       | <薪资:19000.00>     |
| <名字:程咬铜>       | <薪资:18000.00>     |
| <名字:程咬铁>       | <薪资:17000.00>     |
+---------------------+---------------------+
18 rows in set (0.00 sec)

mysql> select distinct post from employee;
+-----------------------------------------+
| post                                    |
+-----------------------------------------+
| 老男孩驻沙河办事处外交大使              |
| teacher                                 |
| sale                                    |
| operation                               |
+-----------------------------------------+
4 rows in set (0.00 sec)

mysql> select name, salary*12 as annual_year from employee;
+------------+-------------+
| name       | annual_year |
+------------+-------------+
| egon       |    87603.96 |
| alex       | 12000003.72 |
| wupeiqi    |    99600.00 |
| yuanhao    |    42000.00 |
| liwenzhou  |    25200.00 |
| jingliyang |   108000.00 |
| jinxin     |   360000.00 |
| 成龙       |   120000.00 |
| 歪歪       |    36001.56 |
| 丫丫       |    24004.20 |
| 丁丁       |    12004.44 |
| 星星       |    36003.48 |
| 格格       |    48003.96 |
| 张野       |   120001.56 |
| 程咬金     |   240000.00 |
| 程咬银     |   228000.00 |
| 程咬铜     |   216000.00 |
| 程咬铁     |   204000.00 |
+------------+-------------+
18 rows in set (0.00 sec)

练习题答案

四、WHERE约束

where字句可以使用：

　　1、比较运算符：> < >= <= ; <> != (这两个不等于符号，一般采用!=)

　　2、between 80 and 100 指的就是大于等于80，小于等于100

　　3、in(80,90,100) 满足在这个范围内，值是80或90或100

　　4、like 'egon%' 这种是模糊匹配

　　　　pattern可以是%或_，

　　　　%表示任意多字符；_表示一个字符

　　5、逻辑运算符：在多个条件直接可以使用逻辑运算符 and(与) or（或） not(非)

#1:单条件查询
    SELECT name FROM employee
        WHERE post='sale';
        
#2:多条件查询 (AND串联条件)
    SELECT name,salary FROM employee
        WHERE post='teacher' AND salary>10000;

#3:关键字BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了

#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;

#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';

# 1、单条件查询
mysql> select id,name,age from employee where id > 7;  
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  8 | 成龙      |  48 |
|  9 | 歪歪      |  48 |
| 10 | 丫丫      |  38 |
| 11 | 丁丁      |  18 |
| 12 | 星星      |  18 |
| 13 | 格格      |  28 |
| 14 | 张野      |  28 |
| 15 | 程咬金    |  18 |
| 16 | 程咬银    |  18 |
| 17 | 程咬铜    |  18 |
| 18 | 程咬铁    |  18 |
+----+-----------+-----+
11 rows in set (0.00 sec)

mysql> select name from employee where post='sale';
+--------+
| name   |
+--------+
| 歪歪   |
| 丫丫   |
| 丁丁   |
| 星星   |
| 格格   |
+--------+
5 rows in set (0.00 sec)

# 2、多条件查询(AND串联)
mysql> select name,salary from employee 
    -> where post='teacher' and salary>10000;
+--------+------------+
| name   | salary     |
+--------+------------+
| alex   | 1000000.31 |
| jinxin |   30000.00 |
+--------+------------+
2 rows in set (0.00 sec)


mysql> select * from employee where post ='teacher' and salary>8000; 
+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex       | male   |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher | NULL         |    8300.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |    9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         |   30000.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher | NULL         |   10000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)


# 3、关键字BETWEEN x AND y ————大于等于x，小于等于y
mysql> select name,salary from employee
    -> where salary between 10000 and 20000;
+-----------+----------+
| name      | salary   |
+-----------+----------+
| 成龙      | 10000.00 |
| 张野      | 10000.13 |
| 程咬金    | 20000.00 |
| 程咬银    | 19000.00 |
| 程咬铜    | 18000.00 |
| 程咬铁    | 17000.00 |
+-----------+----------+
6 rows in set (0.01 sec)

mysql> select name,salary from employee
    -> where salary NOT BETWEEN 10000 AND 20000;    # 10000以下，20000以上
+------------+------------+
| name       | salary     |
+------------+------------+
| egon       |    7300.33 |
| alex       | 1000000.31 |
| wupeiqi    |    8300.00 |
| yuanhao    |    3500.00 |
| liwenzhou  |    2100.00 |
| jingliyang |    9000.00 |
| jinxin     |   30000.00 |
| 歪歪       |    3000.13 |
| 丫丫       |    2000.35 |
| 丁丁       |    1000.37 |
| 星星       |    3000.29 |
| 格格       |    4000.33 |
+------------+------------+
12 rows in set (0.00 sec)

#4:关键字IN集合查询
mysql> select * from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000; 
+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
2 rows in set (0.00 sec)

mysql> select * from employee
    -> where salary in (3000,3500,4000,9000);
+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary  | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher | NULL         | 3500.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         | 9000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+---------+--------+-----------+
2 rows in set (0.01 sec)

mysql> select * from employee where salary not in (3000,3500,4000,9000);
+----+-----------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | egon      | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
|  2 | alex      | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi   | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  5 | liwenzhou | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
|  7 | jinxin    | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
|  8 | 成龙      | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  9 | 歪歪      | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
| 10 | 丫丫      | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
| 11 | 丁丁      | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
| 12 | 星星      | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
| 13 | 格格      | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野      | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
16 rows in set (0.00 sec)

#5:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
# 注意NULL和''空字符串是不同的
mysql> select * from employee
    -> where post_comment is null;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

# is not NULL取反
mysql> select * from employee where post_comment is  not null;
Empty set (0.00 sec)

mysql> select * from employee where post_comment='';  # 注意‘’空字符串不是null
Empty set (0.00 sec)

#6：关键字LIKE模糊匹配
mysql> select * from employee 
    -> where name like 'eg%';   # 任意个数任意字符
+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
| id | name | sex  | age | hire_date  | post                                    | post_comment | salary  | office | depart_id |
+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
|  1 | egon | male |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         | 7300.33 |    401 |         1 |
+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
1 row in set (0.01 sec)

mysql> select * from employee
    -> where name like 'al__';   # 一个'_'对应一个任意字符
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

where约束操作示例

五、分组查询：GROUP BY

1、什么是分组？为什么要分组？

　　1、分组发生在where之后，即分组是基于where之后得到的记录而进行的

　　2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等。

　　3、为什么要分组：分门别类地进行处理。

　　　　　　　　　　每个部门员工、男人与女人数等，“每”这个字后面的字段，就是我们的分组依据。

　　4、大前提：可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数。

2、ONLY_FULL_GROUP_BY

#查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#！！！注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，
target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。


#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

　　在没有设置ONLY_FULL_GROUP_BY时，在分组的情况下查看到其他字段的信息，也可以有结果，默认都是组内的第一条记录。但是这个的意义不大。

mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

mysql> select * from emp group by post; 
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name | sex    | age | hire_date  | post                       | post_comment | salary     | office | depart_id |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| 14 | 张野 | male   |  28 | 2016-03-11 | operation                  | NULL         |   10000.13 |    403 |         3 |
|  9 | 歪歪 | female |  48 | 2015-03-11 | sale                       | NULL         |    3000.13 |    402 |         2 |
|  2 | alex | male   |  78 | 2015-03-02 | teacher                    | NULL         | 1000000.31 |    401 |         1 |
|  1 | egon | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL         |    7300.33 |    401 |         1 |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)


#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的

mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> quit #设置成功后，一定要退出，然后重新登录方可生效
Bye

mysql> use db1;
Database changed
mysql> select * from emp group by post; #报错
ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY
mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
+----------------------------+-----------+
| post                       | count(id) |
+----------------------------+-----------+
| operation                  |         5 |
| sale                       |         5 |
| teacher                    |         7 |
| 老男孩驻沙河办事处外交大使 |         1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)

3、GROUP BY

（1）`单独使用GROUP BY关键字分组：`

　　SELECT post FROM employee GROUP BY post;

　　注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数。

mysql> select post from employee GROUP BY post;
+-----------------------------------------+
| post                                    |
+-----------------------------------------+
| operation                               |
| sale                                    |
| teacher                                 |
| 老男孩驻沙河办事处外交大使              |
+-----------------------------------------+
4 rows in set (0.01 sec)

mysql> select * from employee GROUP BY post;    
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'db2.employee.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

（2）GROUP BY关键字和GROUP_CONCAT()函数一起使用

　　GROUP_CONCAT()函数将对组里的对应记录进行拼接。

#按照岗位分组，并查看组内成员名
mysql> SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;
+-----------------------------------------+---------------------------------------------------------+
| post                                    | GROUP_CONCAT(name)                                      |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
| 老男孩驻沙河办事处外交大使              | egon                                                    |
+-----------------------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
+-----------------------------------------+---------------------------------------------------------+
| post                                    | emp_members                                             |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
| 老男孩驻沙河办事处外交大使              | egon                                                    |
+-----------------------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

（3）GROUP BY与聚合函数一起使用

mysql> select post,count(id) as count from employee group by post;   # 按照岗位分组，并查看每个组有多少人
+-----------------------------------------+-------+
| post                                    | count |
+-----------------------------------------+-------+
| operation                               |     5 |
| sale                                    |     5 |
| teacher                                 |     7 |
| 老男孩驻沙河办事处外交大使              |     1 |
+-----------------------------------------+-------+
4 rows in set (0.00 sec)

　　强调：如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义。

　　　　　多条记录之间的某个字段值相同，该字段通常用来作为分组的依据。

　　　　　分组之后，只能取分组的字段，以及每个组聚合函数的结果。

4、聚合函数

　　注意：聚合函数聚合的是组的内容，若没有分组，则默认分为一组，因此没有分组也可以使用聚合函数。常用聚合函数如下：

 SELECT COUNT(*) FROM employee;
 SELECT COUNT(*) FROM employee WHERE depart_id=1;
 SELECT MAX(salary) FROM employee;
 SELECT MIN(salary) FROM employee;
 SELECT AVG(salary) FROM employee;
 SELECT SUM(salary) FROM employee;
 SELECT SUM(salary) FROM employee WHERE depart_id=3;

# 利用聚合函数查看分组情况
# 统计每个部门员工人数
mysql> select count(id) from employee group by post;
+-----------+
| count(id) |
+-----------+
|         5 |
|         5 |
|         7 |
|         1 |
+-----------+
4 rows in set (0.00 sec)

# 统计公司总人数
mysql> select count(*) from employee;  
+----------+
| count(*) |
+----------+
|       18 |
+----------+
1 row in set (0.00 sec)

# 每个职位有多少个员工？count()
mysql> select post, count(id) from employee group by post;
+-----------------------------------------+-----------+
| post                                    | count(id) |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使              |         1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)

# 为员工统计取别名 count()
mysql> select post,count(id) as emp_count from employee group by post;
+-----------------------------------------+-----------+
| post                                    | emp_count |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使              |         1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)

# 取每个部门的最大工资sum()
mysql> select post,max(salary) as max_salary from employee group by post;
+-----------------------------------------+------------+
| post                                    | max_salary |
+-----------------------------------------+------------+
| operation                               |   20000.00 |
| sale                                    |    4000.33 |
| teacher                                 | 1000000.31 |
| 老男孩驻沙河办事处外交大使              |    7300.33 |
+-----------------------------------------+------------+
4 rows in set (0.00 sec)

# 取每个部门最小工资，min()
mysql> select post,min(salary) as min_salary from employee group by post;
+-----------------------------------------+------------+
| post                                    | min_salary |
+-----------------------------------------+------------+
| operation                               |   10000.13 |
| sale                                    |    1000.37 |
| teacher                                 |    2100.00 |
| 老男孩驻沙河办事处外交大使              |    7300.33 |
+-----------------------------------------+------------+
4 rows in set (0.00 sec)

# 取每个部门的平均工资，avg()
mysql> select post,avg(salary) as avg_salary from employee group by post;
+-----------------------------------------+---------------+
| post                                    | avg_salary    |
+-----------------------------------------+---------------+
| operation                               |  16800.026000 |
| sale                                    |   2600.294000 |
| teacher                                 | 151842.901429 |
| 老男孩驻沙河办事处外交大使              |   7300.330000 |
+-----------------------------------------+---------------+
4 rows in set (0.00 sec)

# 取每个部门员工工资总和，sum()
mysql> select post,sum(salary) as sum_salary from employee group by post;
+-----------------------------------------+------------+
| post                                    | sum_salary |
+-----------------------------------------+------------+
| operation                               |   84000.13 |
| sale                                    |   13001.47 |
| teacher                                 | 1062900.31 |
| 老男孩驻沙河办事处外交大使              |    7300.33 |
+-----------------------------------------+------------+
4 rows in set (0.00 sec)

# 取id为3的部门员工年龄总和
mysql> select post,sum(age)  from employee where depart_id =3 group by post;
+-----------+----------+
| post      | sum(age) |
+-----------+----------+
| operation |      100 |
+-----------+----------+
1 row in set (0.00 sec)

mysql> select post,sum(age)  from employee where depart_id =3 group by post;

聚合函数操作实例

5、小练习

1. 查询岗位名以及岗位包含的所有员工名字
2. 查询岗位名以及各岗位内包含的员工个数
3. 查询公司内男员工和女员工的个数
4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资

练习题

mysql> select post,GROUP_CONCAT(name) as dev_members from employee group by post;
+-----------------------------------------+---------------------------------------------------------+
| post                                    | dev_members                                             |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
| 老男孩驻沙河办事处外交大使              | egon                                                    |
+-----------------------------------------+---------------------------------------------------------+
4 rows in set (0.00 sec)

mysql> select post,count(id) from employee group by post;                                  
+-----------------------------------------+-----------+
| post                                    | count(id) |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使              |         1 |
+-----------------------------------------+-----------+
4 rows in set (0.00 sec)

mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> select post,avg(salary) as avg_salary from employee group by post; 
+-----------------------------------------+---------------+
| post                                    | avg_salary    |
+-----------------------------------------+---------------+
| operation                               |  16800.026000 |
| sale                                    |   2600.294000 |
| teacher                                 | 151842.901429 |
| 老男孩驻沙河办事处外交大使              |   7300.330000 |
+-----------------------------------------+---------------+
4 rows in set (0.00 sec)

mysql> select post,max(salary) as max_salary from employee group by post;      
+-----------------------------------------+------------+
| post                                    | max_salary |
+-----------------------------------------+------------+
| operation                               |   20000.00 |
| sale                                    |    4000.33 |
| teacher                                 | 1000000.31 |
| 老男孩驻沙河办事处外交大使              |    7300.33 |
+-----------------------------------------+------------+
4 rows in set (0.00 sec)

mysql> select post,min(salary) as min_salary from employee group by post;      
+-----------------------------------------+------------+
| post                                    | min_salary |
+-----------------------------------------+------------+
| operation                               |   10000.13 |
| sale                                    |    1000.37 |
| teacher                                 |    2100.00 |
| 老男孩驻沙河办事处外交大使              |    7300.33 |
+-----------------------------------------+------------+
4 rows in set (0.00 sec)

mysql> select sex,avg(salary) as avg_sex_salary from employee group by sex;        
+--------+----------------+
| sex    | avg_sex_salary |
+--------+----------------+
| male   |  110920.077000 |
| female |    7250.183750 |
+--------+----------------+
2 rows in set (0.00 sec)

练习题答案

六、HAVING过滤

HAVING与WHERE不一样的地方？

　　执行优先级从高到低：where > group by > having

　　1、where发生在分组group by之前，因此where中可以有任意字段，但是绝对不能使用聚合函数。

　　2、having发生分组group by之后，因此having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数。

mysql> select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from employee where salary > 10000;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
|  2 | alex      | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  7 | jinxin    | male   |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
| 14 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
7 rows in set (0.00 sec)

mysql> select * from employee having salary > 10000;     
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
|  2 | alex      | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  7 | jinxin    | male   |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
| 14 | 张野      | male   |  28 | 2016-03-11 | operation | NULL         |   10000.13 |    403 |         3 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         |   17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
7 rows in set (0.00 sec)

mysql> select post,group_concat(name) from employee group by post having salary > 10000;  # 错误，分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'

# 无法直接取到其他字段，可以使用聚合函数
mysql> select post,group_concat(name) from employee group by post having avg(salary) > 10000;
+-----------+---------------------------------------------------------+
| post      | group_concat(name)                                      |
+-----------+---------------------------------------------------------+
| operation | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| teacher   | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
+-----------+---------------------------------------------------------+
2 rows in set (0.00 sec)

小练习：

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资

# 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
# 员工个数过滤前
mysql> select post, group_concat(name), count(id) from employee group by post;
+-----------------------------------------+---------------------------------------------------------+-----------+
| post                                    | group_concat(name)                                      | count(id) |
+-----------------------------------------+---------------------------------------------------------+-----------+
| operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |         5 |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |         5 |
| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |         7 |
| 老男孩驻沙河办事处外交大使              | egon                                                    |         1 |
+-----------------------------------------+---------------------------------------------------------+-----------+
4 rows in set (0.00 sec)

# 员工个数过滤后
mysql> select post, group_concat(name), count(id) from employee group by post having count(id) < 2;
+-----------------------------------------+--------------------+-----------+
| post                                    | group_concat(name) | count(id) |
+-----------------------------------------+--------------------+-----------+
| 老男孩驻沙河办事处外交大使              | egon               |         1 |
+-----------------------------------------+--------------------+-----------+
1 row in set (0.00 sec)

# 2.查询各岗位平均薪资大于10000的岗位名、平均工资
mysql> select post, avg(salary) as avg_salary from employee group by post having avg_salary > 10000; 
+-----------+---------------+
| post      | avg_salary    |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+
2 rows in set (0.00 sec)

# 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
mysql> select post, avg(salary) as avg_salary from employee group by post having avg_salary between 10000 and 20000;
+-----------+--------------+
| post      | avg_salary   |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
1 row in set (0.00 sec)

练习题答案

七、查询排序：ORDER BY

按单列排序：

　　ORDER BY...：默认是升序排列

　　ORDER BY ... ASC：升序排列

　　ORDER BY ... DESC：降序排列

mysql> select * from employee ORDER BY salary;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

mysql> select * from employee ORDER BY salary ASC;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

mysql> select * from employee ORDER BY salary DESC;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

单列排序示例

按多列排序：

　　上面是按工资排，如果按age排序，有很多人年纪是相同的，这时就需要采用多列排序了。

　　例如：先按照age升序排，如果age相同按照id降序排

mysql> select * from employee
    -> ORDER BY AGE,
    -> id DESC;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

　　有order by的sql语句，执行顺序（注意与语法顺序的异同）：

　　先执行from找到表；再按照where约束条件过滤数据；再交给group by进行分组；接着交给having进行过滤；

过滤之后运行distinct进行去重（没有distinct就不去重）；去重之后再执行order by进行排序；orderby 运行完之后运行limit。

　　最需要注意的一点：where这一步的时候还没有分组，因此where不能用聚合函数。体会下面这个用例

mysql> select * from employee where max(salary) > 1000;
ERROR 1111 (HY000): Invalid use of group function
mysql> select max(salary) from employee where salary > 1000;
+-------------+
| max(salary) |
+-------------+
|  1000000.31 |
+-------------+
1 row in set (0.00 sec)

　　另一个关注的重点：distinct语句执行是在having后面。体会下面这个用例：(用例实际执行和教材不同，需关注)

　　having不能使用distinct定义的别名，order by 则可以使用。

mysql> select post, count(id) as emp_count from employee
    -> where salary > 1000
    -> group by post
    -> having emp_count > 5;   # 此处引用不了emp_count别名，因为distinct还没有执行
+---------+-----------+
| post    | emp_count |
+---------+-----------+
| teacher |         7 |
+---------+-----------+
1 row in set (0.00 sec)

mysql> select post, count(id) as emp_count from employee where salary > 1000 group by post having count(id) > 5;         
+---------+-----------+
| post    | emp_count |
+---------+-----------+
| teacher |         7 |
+---------+-----------+
1 row in set (0.00 sec)

小练习：

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列

# 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
mysql> select * from employee ORDER BY age ASC, hire_date DESC;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary     | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         |    7300.33 |    401 |         1 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)

# 分组之后，distinct这里只能查被分组的字段和聚合函数
# 2.查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
mysql> select post, avg(salary),group_concat(name) from employee GROUP BY post having avg(salary) > 10000 order by avg(salary) ASC;
+-----------+---------------+---------------------------------------------------------+
| post      | avg(salary)   | group_concat(name)                                      |
+-----------+---------------+---------------------------------------------------------+
| operation |  16800.026000 | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| teacher   | 151842.901429 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
+-----------+---------------+---------------------------------------------------------+
2 rows in set (0.00 sec)

# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
mysql> select post, avg(salary),group_concat(name) from employee GROUP BY post having avg(salary) > 10000 order by avg(salary) DESC;
+-----------+---------------+---------------------------------------------------------+
| post      | avg(salary)   | group_concat(name)                                      |
+-----------+---------------+---------------------------------------------------------+
| teacher   | 151842.901429 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
| operation |  16800.026000 | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
+-----------+---------------+---------------------------------------------------------+
2 rows in set (0.00 sec)

练习题答案

八、限制查询的记录数：LIMIT

　　LIMIT语法上是写在最后，执行时也是最后一个执行。

　　限制最终打印在屏幕上的条数。

mysql> select * from employee order by salary desc
    -> limit 3;
+----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name      | sex  | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+
|  2 | alex      | male |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  7 | jinxin    | male |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
| 15 | 程咬金    | male |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
+----+-----------+------+-----+------------+-----------+--------------+------------+--------+-----------+
3 rows in set (0.00 sec)

# 找出工资最高人的详细信息
mysql> select * from employee order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

　　LIMIT还能够实现分页功能（但是使用LIMIT来分页效率不高，一般还是需要一些缓存机制来帮忙实现分页）

# limit做分页功能
mysql> select * from employee order by salary DESC
    -> limit 0,5;
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary     | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
|  2 | alex      | male   |  78 | 2015-03-02 | teacher   | NULL         | 1000000.31 |    401 |         1 |
|  7 | jinxin    | male   |  18 | 1900-03-01 | teacher   | NULL         |   30000.00 |    401 |         1 |
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         |   20000.00 |    403 |         3 |
| 16 | 程咬银    | female |  18 | 2013-03-11 | operation | NULL         |   19000.00 |    403 |         3 |
| 17 | 程咬铜    | male   |  18 | 2015-04-11 | operation | NULL         |   18000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+------------+--------+-----------+
5 rows in set (0.01 sec)

mysql> select * from employee order by salary desc
    -> limit 5,5;   # 从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
+----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation | NULL         | 17000.00 |    403 |         3 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation | NULL         | 10000.13 |    403 |         3 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher   | NULL         | 10000.00 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher   | NULL         |  9000.00 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher   | NULL         |  8300.00 |    401 |         1 |
+----+------------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)

limit来实现分页显示

九、正则查询

　　更加强大的模糊匹配——正则表达式。

　　调用正则表达式查询的关键字——REGEXP

# 名字是'jin'开头的记录
mysql> select * from employee where name regexp '^jin';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.01 sec)

# 名字是'jin'开头，且以g或n结尾的记录
mysql> select * from employee where name regexp '^jin.*(g|n)$';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name       | sex    | age | hire_date  | post    | post_comment | salary   | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher | NULL         |  9000.00 |    401 |         1 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher | NULL         | 30000.00 |    401 |         1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)

# 名字以on结尾的记录
mysql> select * from employee where name regexp 'on$';
+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
| id | name | sex  | age | hire_date  | post                                    | post_comment | salary  | office | depart_id |
+----+------+------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
|  1 | egon | male |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         | 7300.33 |    401 |         1 |
+----+------+------+-----+

# 匹配m字符两次'mm'
mysql> select * from employee where name regexp 'm{2}'; 
Empty set (0.00 sec)

mysql> select * from employee where name regexp 'g{1}';
+----+------------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
| id | name       | sex    | age | hire_date  | post                                    | post_comment | salary  | office | depart_id |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
|  1 | egon       | male   |  18 | 2017-03-01 | 老男孩驻沙河办事处外交大使              | NULL         | 7300.33 |    401 |         1 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         | 9000.00 |    401 |         1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+---------+--------+-----------+
2 rows in set (0.00 sec)

正则表达式使用示例

小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';

十、单表查询的语法顺序和执行顺序总结

语法顺序：

select distinct 字段1,字段2,字段3 from 库.表
    where 条件
    group by 分组条件
    having  过滤
    order by  排序字段
    limit n;

执行顺序：

　　select执行了一个打印，各个关键字关系用伪代码表示如下：

def from(db, table):
    f = open(r'%s\%s' %(db, table))
    return f

def where(condition, f):
    for line in f:
        if condition:
            yield line

def group(lines):
    pass

def having(group_res):
    pass

def distinct(having_res):
    pass

def order(distinct_res):
    pass

def limit(order_res):
    pass

def select():
    from('db1', 't1')
    lines=where('id>3',f)
    group_res=group(lines)
    having_res=having(group_res)
    distinct_res=distinct(having_res)
    order_res=order(distinct_res)
    res=limit(order_res)
    print(res)
    return res

执行顺序伪代码

posted @ 2018-05-12 13:55 休耕阅读(464) 评论(0) 编辑收藏举报

刷新页面返回顶部

登录后才能查看或发表评论，立即登录或者逛逛博客园首页

阅读排行：
· 10年+ .NET Coder 心语 ── 封装的思维：从隐藏、稳定开始理解其本质意义
· 地球OL攻略 —— 某应届生求职总结
· 周边上新：园子的第一款马克杯温暖上架
· Open-Sora 2.0 重磅开源！
· 提示词工程——AI应用必不可少的技术

公告

昵称：休耕
园龄： 7年2个月
粉丝： 323
关注： 55

+加关注

2025年3月

日

一

二

三

四

五

六

休耕

mysql四-1：单表查询

一、单表查询的语法

二、关键字的执行优先级（重点）

三、简单查询

四、WHERE约束

五、分组查询：GROUP BY

1、什么是分组？为什么要分组？

2、ONLY_FULL_GROUP_BY

3、GROUP BY

（1）`单独使用GROUP BY关键字分组：`

（2）GROUP BY关键字和GROUP_CONCAT()函数一起使用

（3）GROUP BY与聚合函数一起使用

4、聚合函数

5、小练习

六、HAVING过滤

七、查询排序：ORDER BY

八、限制查询的记录数：LIMIT

九、正则查询

十、单表查询的语法顺序和执行顺序总结

公告

搜索

常用链接

我的标签

随笔分类

随笔档案

相册

阅读排行榜

评论排行榜

推荐排行榜

最新评论

一、单表查询的语法

二、关键字的执行优先级（重点）

三、简单查询

四、WHERE约束

五、分组查询：GROUP BY

1、什么是分组？为什么要分组？

2、ONLY_FULL_GROUP_BY

3、GROUP BY

（1）单独使用GROUP BY关键字分组：

（2）GROUP BY关键字和GROUP_CONCAT()函数一起使用

（3）GROUP BY与聚合函数一起使用

4、聚合函数

5、小练习

六、HAVING过滤

七、查询排序：ORDER BY

八、限制查询的记录数：LIMIT

九、正则查询

十、单表查询的语法顺序和执行顺序总结

公告

搜索

常用链接

随笔档案

相册

（1）`单独使用GROUP BY关键字分组：`