数据库单表查询
数据库的完整查询语法
在平常的工作中经常需要与数据库打交道 , 虽然大多时间都是简单的查询抑或使用框架封装好的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;