本文实例讲述了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语句的时候,没有报错,但本身是没有意义的
1
2
3
4
5
6
7
8
9
10
|
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,并且退出,再进入才会生效
1
2
|
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)
|
再次进入
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
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)
|
或者如下使用
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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() 求总个数
1
2
3
4
5
6
7
8
9
10
11
|
#强调:聚合函数聚合的是组的内容,若是没有分组,则默认一组
# 每个部门有多少个员工
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中可以使用分组的字段,无法直接取到其他字段,可以使用聚合函数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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的岗位名、平均工资
答案
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
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)
|
1
2
3
4
5
6
7
8
9
|
#题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)
|
1
2
3
4
5
6
7
8
|
#题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 查询排序
按单列排序
1
2
3
|
SELECT * FROM employee ORDER BY age;
SELECT * FROM employee ORDER BY age ASC ;
SELECT * FROM employee ORDER BY age DESC ;
|
按多列排序:先按照age升序排序,如果年纪相同,则按照id降序
1
2
3
|
SELECT * from employee
ORDER BY age ASC ,
id DESC ;
|
(5)limit 限制查询的记录数:
示例:
1
2
3
4
5
6
7
8
|
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
|
# 第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