数据库单表查询

数据库的完整查询语法

　　在平常的工作中经常需要与数据库打交道 , 虽然大多时间都是简单的查询抑或使用框架封装好的ORM的查询方法 , 但是还是要对数据库的完整查询语法做一个加深理解

数据库完整查询语法框架

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)

• 基础语法

  • 　　select field_name from Table_name　　>>>      表中单个字段查询
  • 　　select * from Table_name　　>>>　　表中所有字段查询

• where　　条件

  • 　　条件可以是各种运算及正则匹配

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)
  ```

• group by　　分组依据

  • 　　以记录的字段共性对记录进行分组 
  • 　　分组后字段可进行聚合函数处理

___聚合函数___

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;

• having　　筛选

  • 　　对where和group by处理的结果进一步筛选 , 得到我们想要的数据

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中使用了聚合函数,所以该查询就将整个表当做一个默认大表来考虑,所以查询的字段只能为聚合函数的结果

• order by　　排序

  • 　　对筛选结果进行升序或者降序排列(默认是升序)
  • 　　升序:  asc ;         降序:  desc

# 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　　限制

  • 　　显示最终数据结果的行数  ,  只能与数字配合使用

# 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;