select [distinct] 字段1 [as 别名], ..., 字段n [as 别名] from [库名.]表名

                    [

                    where 约束条件

                    group by 分组依据

                    having 过滤条件

                    order by 排序的字段

                    limit 限制显示的条数

                    ];

注：

1.查表中所有字段用*表示(select * from ...)

2.条件的书写规则严格按照语法顺序书写，可以缺省，但不可以错序

3.约束条件的流程：from -> where -> group by -> having -> distinct -> order by -> limit

4.字段可以起别名

5.字段可以直接做运算 select age + 1 'new_age' from emp;

6.分组后的条件均可以使用聚合函数

'''

'''

3. 伪代码解析流程

def from():

    return "查询的文件"

def where(file):

    return "条件筛选后的结果"

def group_by(res):

    return "分组后的结果"

def having(res):

    return "再次过滤后的结果"

def distinct(res):

    return "去重后的结果"

def order_by(res):

    return "排序后的结果"

def limit(res):

    return "限制条数后的结果"

def select(from=from, where=null, ..., limit=null):

    file = from()

    res = where(file) if where else file

    res = group_by(res) if group_by else res

    ...

    res = limit(res) if limit else res

    return res

select(where=where, group_by=group_by)

1. 比较运算符

　```

  =    <    >    <=        >=        !=

  ```

  ```

  select * from emp where area!="上海";

  ```

2. 区间运算符

   between 10 and 20    # 表示10-20 之间

   in (10,20,30)            # 表示10,20或者30

```

  select * from emp where id between 3 and 5; # [3, 5], 闭合区间,包含3和5

```

3. 逻辑运算符

   and        or        not

```

  select * from emp where area='山东' and port='济南';

  mysql> select * from emp where area='山东' and port='济南';

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

  | id | name  | gender | age  | salary | area   | port   | dep       |

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

  |  2 | owen  | 男     |   38 |    9.4 | 山东    | 济南   | 技术部     |

  | 10 | kevin | 男     |   36 |    5.8 | 山东    | 济南   | 人资部     |

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

  2 rows in set (0.00 sec)

```

4. 相似运算符(like)

  下划线或者%可以进行模糊匹配

```

  select * from emp where name like '__en%'; # 匹配出Owen

  mysql> select * from emp where name like '__en%';

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

  | id | name | gender | age  | salary | area   | port   | dep       |

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

  |  7 | owen | 男     |   28 |    8.8 | 安徽    | 宣城    | 技术部    |

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

  1 row in set (0.00 sec)

```

5. 正则匹配(regexp)

  - 由于like完成模糊匹配的范围有限 , 可以模糊个数 , 但不能模糊类型

  - ------正则可以完成类型和个数的模糊匹配(只支持部分语法)

  ```python

  select * from emp where name regexp '.*[0-9]+.*';  # 匹配出名字中带数字的记录

  mysql> select * from emp where name regexp '.*[0-9]+.*';

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

  | id | name | gender | age  | salary | area   | port   | dep       |

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

  | 13 | san1 | 男     |   30 |      6 | 上海    | 浦东   | 咨询部    |

  | 14 | san2 | 男     |   30 |      6 | 上海    | 浦西   | 研发部    |

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

  2 rows in set (0.00 sec)

  ```

___聚合函数___

max()：最大值

min()：最小值

avg()：平均值

sum()：和

count()：记数

group_concat()：组内字段拼接，用来查看组内其他字段

___example___

# eg1
# 每个部门的平均薪资
select dep, avg(salary) '平均薪资' from emp group by dep;

mysql> select dep, avg(salary) '平均薪资' from emp group by dep;
+-----------+-------------------+
|    dep    |    平均薪资        |
+-----------+-------------------+
|   咨询部   | 6.250000059604645 |
|   人资部   | 5.400000027247837 |
|   技术部   | 5.850000023841858 |
+-----------+-------------------+
3 rows in set (0.00 sec)

# eg:2
# 每个部门都有哪些人
select dep, group_concat(name) 'name' from emp group by dep;

mysql> select dep,group_concat(name) 'name' from emp group by dep;
+-----------+---------------------------------------+
|    dep    | name                                  |
+-----------+---------------------------------------+
|   咨询部   | san1,san,ying,zero                    |
|   技术部   | san2,kevin,owen,jiboy,tank,jerry,engo |
|   人资部   | monkey,yangsir                        |
+-----------+---------------------------------------+
3 rows in set (0.04 sec)

# eg3
# 各性别中附属于教学部的最高薪资
select max(salary) '最高薪资', gender from emp where dep='教学部' group by gender;

1.各部门的平均薪资

select dep, avg(salary) '平均薪资' from emp group by dep;

2.平均薪资大于6w的部门(部门与部门的平均薪资)

解决: 以dep进行分组, 以avg(salary)作为判断条件(筛选)

select dep, avg(salary) '平均薪资' from emp group by dep having avg(salary) > 6;

# 总结: having通过聚合函数结果完成筛选

select max(salary) from emp having max(salary) > 9.4;

# 虽然没有明确书写group by, 但在having中使用了聚合函数,所以该查询就将整个表当做一个默认大表来考虑,所以查询的字段只能为聚合函数的结果

# eg：order by age desc => 按照年龄降序

select * from emp order by age desc;

# 需求：

# 将部门按照部门平均工资降序方式排序

select dep, avg(salary) from emp group by dep order by avg(salary) desc;

# limit 1：只能显示一行数据

# limit 6,5：从第6+1行开始显示5条数据(索引从0开始)

select * from emp limit 1;

select * from emp limit 6,5;

# 需求:

# 获得薪资最高的人的一条信息

select * from emp order by salary desc limit 1;