MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】

2023-08-04 07:38:04 69

本文实例讲述了MySQL单表查询操作。分享给大家供大家参考，具体如下：

语法

一、单表查询的语法

SELECT字段1,字段2...FROM表名
WHERE条件
GROUPBYfield
HAVING筛选
ORDERBYfield
LIMIT限制条数

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

重点中的重点：关键字的执行优先级

from
where
groupby
having
select
distinct
orderby
limit

1.找到表:from

2.拿着where指定的约束条件，去文件/表中取出一条条记录

3.将取出的一条条记录进行分组groupby，如果没有groupby，则整体作为一组

4.将分组的结果进行having过滤

5.执行select

6.去重

7.将结果按条件排序：orderby

8.限制结果的显示条数

（1）where约束

where运算符

where子句中可以使用
1.比较运算符：>、<、>=、<=、<>、!=
2.between80and100：值在80到100之间
3.in(80,90,100)值是10或20或30
4.like'xiaomagepattern':pattern可以是%或者_。%小时任意多字符，_表示一个字符
5.逻辑运算符：在多个条件直接可以使用逻辑运算符andornot

（2）groupby分组查询

#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

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

#3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数

小窍门：‘每'这个字后面的字段，就是我们分组的依据

#4、大前提：

可以按照任意字段分组，但是分组完毕后，比如groupbypost，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

当执行以下sql语句的时候，没有报错，但本身是没有意义的

mysql>select*fromemployeegroupbypost;
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|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||1000000.31|401|1|
|1|egon|male|18|2017-03-01|老男孩驻沙河办事处外交大使|NULL|7300.33|401|1|
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
4rowsinset(0.00sec)

设置sql_mode为ONLY_FULL_GROUP_BY，并且退出，再进入才会生效

mysql>setglobalsql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
QueryOK,0rowsaffected(0.00sec)

再次进入

mysql>select@@sql_mode;
+-----------------------------------------------------------------------------------+
|@@sql_mode|
+-----------------------------------------------------------------------------------+
|ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION|
+-----------------------------------------------------------------------------------

mysql>select*fromempgroupbypost;//现在的情况下就会报错
ERROR1054(42S22):Unknowncolumn'post'in'groupstatement'
mysql>select*fromemployeegroupbypost;
ERROR1055(42000):'t1.employee.id'isn'tinGROUPBY
mysql>selectpostfromemployeegroupbypost;
+-----------------------------------------+
|post|
+-----------------------------------------+
|operation|
|sale|
|teacher|
|老男孩驻沙河办事处外交大使|
+-----------------------------------------+
4rowsinset(0.00sec)

或者如下使用

mysql>selectname,postfromemployeegroupbypost,name;
+------------+-----------------------------------------+
|name|post|
+------------+-----------------------------------------+
|张野|operation|
|程咬金|operation|
|程咬铁|operation|
|程咬铜|operation|
|程咬银|operation|
|丁丁|sale|
|丫丫|sale|
|星星|sale|
|格格|sale|
|歪歪|sale|
|alex|teacher|
|jingliyang|teacher|
|jinxin|teacher|
|liwenzhou|teacher|
|wupeiqi|teacher|
|xiaomage|teacher|
|yuanhao|teacher|
|egon|老男孩驻沙河办事处外交大使|
+------------+-----------------------------------------+
18rowsinset(0.00sec)

mysql>selectpost,count(id)fromemployeegroupbypost;
+-----------------------------------------+-----------+
|post|count(id)|
+-----------------------------------------+-----------+
|operation|5|
|sale|5|
|teacher|7|
|老男孩驻沙河办事处外交大使|1|
+-----------------------------------------+-----------+
4rowsinset(0.00sec)

（3）聚合函数

max()求最大值
min()求最小值
avg()求平均值
sum()求和
count()求总个数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组
#每个部门有多少个员工
selectpost,count(id)fromemployeegroupbypost;
#每个部门的最高薪水
selectpost,max(salary)fromemployeegroupbypost;
#每个部门的最低薪水
selectpost,min(salary)fromemployeegroupbypost;
#每个部门的平均薪水
selectpost,avg(salary)fromemployeegroupbypost;
#每个部门的所有薪水
selectpost,sum(age)fromemployeegroupbypost;

（4）HAVING过滤

HAVING与WHERE不一样的地方在于

#！！！执行优先级从高到低：where>groupby>having
#1.Where发生在分组groupby之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。

#2.Having发生在分组groupby之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

mysql>select*fromemployeewheresalary>1000000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|id|name|sex|age|hire_date|post|post_comment|salary|office|depart_id|
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|2|alex|male|78|2015-03-02|teacher||1000000.31|401|1|
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1rowinset(0.00sec)

mysql>select*fromemployeehavingsalary>1000000;
ERROR1463(42000):Non-groupingfield'salary'isusedinHAVINGclause

#必须使用groupby才能使用group_concat()函数，将所有的name值连接
mysql>selectpost,group_concat(name)fromempgroupbyposthavingsalary>10000;##错误，分组后无法直接取到salary字段
ERROR1054(42S22):Unknowncolumn'post'in'fieldlist'

练习

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

答案

mysql>selectpost,group_concat(name),count(id)fromemployeegroupbypost;
+-----------------------------------------+-----------------------------------------------------------+-----------+
|post|group_concat(name)|count(id)|
+-----------------------------------------+-----------------------------------------------------------+-----------+
|operation|程咬铁,程咬铜,程咬银,程咬金,张野|5|
|sale|格格,星星,丁丁,丫丫,歪歪|5|
|teacher|xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex|7|
|老男孩驻沙河办事处外交大使|egon|1|
+-----------------------------------------+-----------------------------------------------------------+-----------+
4rowsinset(0.00sec)

mysql>selectpost,group_concat(name),count(id)fromemployeegroupbyposthavingcount(id)<2;
+-----------------------------------------+--------------------+-----------+
|post|group_concat(name)|count(id)|
+-----------------------------------------+--------------------+-----------+
|老男孩驻沙河办事处外交大使|egon|1|
+-----------------------------------------+--------------------+-----------+
1rowinset(0.00sec)

#题2：
mysql>selectpost,avg(salary)fromemployeegroupbyposthavingavg(salary)>10000;
+-----------+---------------+
|post|avg(salary)|
+-----------+---------------+
|operation|16800.026000|
|teacher|151842.901429|
+-----------+---------------+
2rowsinset(0.00sec)

#题3：
mysql>selectpost,avg(salary)fromemployeegroupbyposthavingavg(salary)>10000andavg(salary)<20000;
+-----------+--------------+
|post|avg(salary)|
+-----------+--------------+
|operation|16800.026000|
+-----------+--------------+
1rowinset(0.00sec)

（5）orderby查询排序

按单列排序

SELECT*FROMemployeeORDERBYage;
SELECT*FROMemployeeORDERBYageASC;
SELECT*FROMemployeeORDERBYageDESC;

按多列排序:先按照age升序排序，如果年纪相同，则按照id降序

SELECT*fromemployee
ORDERBYageASC,
idDESC;

（5）limit 限制查询的记录数:

示例：

SELECT*FROMemployeeORDERBYsalaryDESC
LIMIT3;#默认初始位置为0

SELECT*FROMemployeeORDERBYsalaryDESC
LIMIT0,5;#从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

SELECT*FROMemployeeORDERBYsalaryDESC
LIMIT5,5;#从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

练习：每次显示5条

#第1页数据
mysql>select*fromemployeelimit0,5;
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
|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||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|
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
5rowsinset(0.00sec)
#第2页数据
mysql>select*fromemployeelimit5,5;
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
|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|
|8|xiaomage|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|
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5rowsinset(0.00sec)
#第3页数据
mysql>select*fromemployeelimit10,5;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
|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|
|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|
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5rowsinset(0.00sec)

更多关于MySQL相关内容感兴趣的读者可查看本站专题：《MySQL查询技巧大全》、《MySQL常用函数大汇总》、《MySQL日志操作技巧大全》、《MySQL事务操作技巧汇总》、《MySQL存储过程技巧大全》及《MySQL数据库锁相关技巧汇总》

希望本文所述对大家MySQL数据库计有所帮助。

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】

热门推荐

随机推荐