MySQL单表查询操作实例详解【语法、约束、分组、聚合、过滤、排序等】
作者:dawn-liu 发布时间:2024-01-24 14:41:24
本文实例讲述了MySQL单表查询操作。分享给大家供大家参考,具体如下:
语法
一、单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
二、关键字的执行优先级(重点)
重点中的重点:关键字的执行优先级
from
where
group by
having
select
distinct
order by
limit
1.找到表:from
2.拿着where指定的约束条件,去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by,如果没有group by,则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序:order by
8.限制结果的显示条数
(1)where 约束
where运算符
where子句中可以使用
1.比较运算符:>、<、>=、<=、<>、!=
2.between 80 and 100 :值在80到100之间
3.in(80,90,100)值是10或20或30
4.like 'xiaomagepattern': pattern可以是%或者_。%小时任意多字符,_表示一个字符
5.逻辑运算符:在多个条件直接可以使用逻辑运算符 and or not
(2)group by 分组查询
#1、首先明确一点:分组发生在where之后,即分组是基于where之后得到的记录而进行的
#2、分组指的是:将所有记录按照某个相同字段进行归类,比如针对员工信息表的职位分组,或者按照性别进行分组等
#3、为何要分组呢?
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数小窍门:‘每'这个字后面的字段,就是我们分组的依据
#4、大前提:
可以按照任意字段分组,但是分组完毕后,比如group by post,只能查看post字段,如果想查看组内信息,需要借助于聚合函数
当执行以下sql语句的时候,没有报错,但本身是没有意义的
mysql> select * from employee 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 | | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | NULL | 7300.33 | 401 | 1 |
+----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)
设置sql_mode为ONLY_FULL_GROUP_BY,并且退出,再进入才会生效
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
再次进入
mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------+
| @@sql_mode |
+-----------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------
mysql> select * from emp group by post;//现在的情况下就会报错
ERROR 1054 (42S22): Unknown column 'post' in 'group statement'
mysql> select * from employee group by post;
ERROR 1055 (42000): 't1.employee.id' isn't in GROUP BY
mysql> select post from employee group by post;
+-----------------------------------------+
| post |
+-----------------------------------------+
| operation |
| sale |
| teacher |
| 老男孩驻沙河办事处外交大使 |
+-----------------------------------------+
4 rows in set (0.00 sec)
或者如下使用
mysql> select name,post from employee group by post,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 | 老男孩驻沙河办事处外交大使 |
+------------+-----------------------------------------+
18 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)
(3)聚合函数
max()求最大值
min()求最小值
avg()求平均值
sum() 求和
count() 求总个数
#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
# 每个部门有多少个员工
select post,count(id) from employee group by post;
# 每个部门的最高薪水
select post,max(salary) from employee group by post;
# 每个部门的最低薪水
select post,min(salary) from employee group by post;
# 每个部门的平均薪水
select post,avg(salary) from employee group by post;
# 每个部门的所有薪水
select post,sum(age) from employee group by post;
(4)HAVING过滤
HAVING与WHERE不一样的地方在于
#!!!执行优先级从高到低:where > group by > having
#1. Where 发生在分组group by之前,因而Where中可以有任意字段,但是绝对不能使用聚合函数。#2. Having发生在分组group by之后,因而Having中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
mysql> select * from employee where salary>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 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from employee having salary>1000000;
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
# 必须使用group by才能使用group_concat()函数,将所有的name值连接
mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##错误,分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'post' in 'field list'
练习
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
答案
mysql> select post,group_concat(name),count(id) from employee group by post;
+-----------------------------------------+-----------------------------------------------------------+-----------+
| post | group_concat(name) | count(id) |
+-----------------------------------------+-----------------------------------------------------------+-----------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | 5 |
| sale | 格格,星星,丁丁,丫丫,歪歪 | 5 |
| teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | 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:
mysql> select post,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:
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
+-----------+--------------+
| post | avg(salary) |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+
1 row in set (0.00 sec)
(5)order by 查询排序
按单列排序
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age ASC;
SELECT * FROM employee ORDER BY age DESC;
按多列排序:先按照age升序排序,如果年纪相同,则按照id降序
SELECT * from employee
ORDER BY age ASC,
id DESC;
(5)limit 限制查询的记录数:
示例:
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始,即先查询出第一条,然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始,即先查询出第6条,然后包含这一条在内往后查5条
练习:每次显示5条
# 第1页数据
mysql> select * from employee limit 0,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 |
+----+-----------+------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
5 rows in set (0.00 sec)
# 第2页数据
mysql> select * from employee limit 5,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 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
# 第3页数据
mysql> select * from employee limit 10,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 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
希望本文所述对大家MySQL数据库计有所帮助。
来源:https://www.cnblogs.com/mmyy-blog/p/9626857.html
猜你喜欢
- python 2.7.11django 1.8.4错误内容:related Field has invalid lookup: iconta
- 网页可以说是网站构成的基本元素。当我们轻点鼠标,在网海中遨游,一幅幅精彩的网页会呈现在我们面前,那么,网页的精彩与否的因素是什么呢?色彩的搭
- 关于浅拷贝和深拷贝想必大家在学习中遇到很多次,这也是面试中常常被问到的问题,借由这个时间,整理一下浅拷贝和深拷贝的关系先从一个简单的例子入手
- 问题描述在电脑中重新安装Anaconda3&PyCharm后,运行原来的程序画图时出现了下图界面。不能弹出如下图所示的“figure”窗口。
- 1、下载mysql-python官网地址:http://sourceforge.net/projects/mysql-python/2、安装
- Python内置了一些非常有趣、有用的函数,如:filter、map、reduce,都是对一个集合进行处理,filter很容易理解用于过滤,
- 1.概述pyecharts 是百度开源的,适用于数据可视化的工具,配置灵活,展示图表相对美观,顺滑。2.安装python3环境下的安装:pi
- 一、源码安装首先安装开发工具包yum groupinstall -y "Development tools"安装依赖软件
- 首先上一段程序:import numpy as nplist_a = list(range(10))print("list_a:
- 本文实例为大家分享了python实现坦克大战的具体代码,供大家参考,具体内容如下本游戏制作代码量较大具体代码与图片声源可以在我的GitHub
- 上次版本如果在没有找到轮廓或轮廓的点集数很小无法拟合椭圆或在RANSAC中寻找最优解时会死循环中,优化后的代码import cv2impor
- 不少同学一提到泰勒公式,脑海里立马浮现高大上的定义和长长的公式,令人望而生畏。实际上,泰勒公式没有那么可怕,它是用简单的多项式来逼近一个光滑
- python socket 聊天室import sockets = socket.socket(socket.AF_INET, socket
- jquery基本入门 第一天:选择器相关 1.html()与.text() .html()取得第一个匹配元素的html内容。会带有标签,.t
- 本文实例讲述了Python实现的KMeans聚类算法。分享给大家供大家参考,具体如下:菜鸟一枚,编程初学者,最近想使用Python3实现几个
- 本文实例讲述了Python 类属性与实例属性,类对象与实例对象用法。分享给大家供大家参考,具体如下:demo.py(类属性,所有实例对象共用
- 在查询语句中使用 NOLOCK 和 READPAST 处理一个数据库死锁的异常时候,其中一个建议就是使用 NOLOCK 或者 READPAS
- tips:如果根目录下有favicon.ico,可省去<link rel="shortcut icon" ...&
- 参数数量及其作用该函数共有五个参数,分别是:被赋值的变量 ref要分配给变量的值 value、是否验证形状 validate_shape是否
- 前期准备1.beat插件安装pip3 install django-celery-beat2.注册APPINSTALLED_APPS = [