09@mysql单表查询的查询语句(select，where等）

0|1文章目录

1|0mysql单表查询

1|1一、单表查询语法

 
#基础格式：
SELECT 字段1,字段2... FROM 表名
                  WHERE 条件
                  GROUP BY field
                  HAVING 筛选
                  ORDER BY field
                  LIMIT 限制条数

1|2二、 关键字执行的优先级(重点)

【select查询语句执行顺序详解URL】

基本流程：

首先执行命令:select —>找到表:from —>拿着指定的约束条件，去文件/表中取出一条条记录：where —>将取出的一条条记录进行分组，如果没有group by，则整体作为一组：group by —>将分组的结果进行过滤：having —>然后进行去重：distinct —>将结果按条件进行排序：order by —> 最后输出限制结果的显示条数:limit

# ！！！重点：(关键字的执行优先级)
1:from      #找到表
2:where     #使用where指定的条件,去表中取出一条条记录
3:group by  #将取出的数据进行分组,如果没有指定,则整体作为一组
4:having    #将分组的结果按照having指定的条件进行过滤
5:select    #指定select查询
6:distinct  #去重
7:order by  #将查询的结果按照order by指定的字段进行排序
8:limit     #限制结果的显示数量

1|3三、简单查询

 
#准备表和记录
company.employee
    员工id      id                  int             
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int





#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);



#查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+



#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;

    #ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk


###########################(简单查询)###########################
    SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id 
    FROM employee;

    SELECT * FROM employee;

    SELECT name,salary FROM employee;



#避免重复DISTINCT(去重)
    SELECT DISTINCT post FROM employee;  
   
   
   

#通过四则运算查询（运算）
    SELECT name, salary*12 FROM employee;
    SELECT name, salary*12 AS Annual_salary FROM employee;
    SELECT name, salary*12 Annual_salary FROM employee;




#定义显示格式
   CONCAT()     #函数用于连接字符串
   SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS Annual_salary 
   FROM employee;
   
   CONCAT_WS()  #第一个参数为分隔符
   SELECT CONCAT_WS(':',name,salary*12)  AS Annual_salary 
   FROM employee;

   #结合CASE语句：
   SELECT
       (
           CASE
           WHEN NAME = 'egon' THEN
               NAME
           WHEN NAME = 'alex' THEN
               CONCAT(name,'_BIGSB')
           ELSE
               concat(NAME, 'SB')
           END
       ) as new_name
   FROM
       emp;

###########(小练习）
1)查出所有员工的名字，薪资,格式为:
    <名字:egon>    <薪资:3000>
2)查出所有的岗位（去掉重复）
3)查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year



#查询以上条件（code编写）
select concat('<名字:',name,'>    ','<薪资:',salary,'>') from employee;
select distinct depart_id from employee;
select name,salary*12 annual_salary from employee;

1|4四、单表查询

1|01、select（筛选列语句）
1|01）select 查询操作
 
   #注意:所有select 查询后面右加括号的方法,比如,select user()这种带括号的都是Mysql的内置方法,select本身就是查询操作,from只是说明从哪查  
	
	
	
	select * from 表名;      #查询表中所有的数据  *代表所有 
	select 字段名 from 表名;  #查询表中某一个字段的数据
	select 字段名,字段名,字段名 from 表名;  #查询表中多个字段的数据
	select database();  #查询返回当前操作的数据库
	select user();      #查询当前登陆的用户
	select now;         #查询返回当前的时间
1|02）distinct 去重操作
 
   当使用select查询表某些字段数据的时候,这个字段中可能出现多个重复的数据,就可以使用	distinct对查询出来的数据进行去重操作,distinct也可以对多个字段的数据进行联合去重
	'''
	select dictinct 字段名 from 表名;#对查出来的字段数据进行去重
	select distinct 字段名,字段名 from 表名;#对多个字段查询的数据进行联合去重
1|03）四则运算
 
   #使用select 查询某个字段的数据的时候,对数值类型的字段数据,可以进行四则运算,四则运算包括加减乘除等操作

	select 字段名*12 from 表名;     #对查询出来的数据乘以12,返回结果,>>字段名要是数值类型
1|04）concat和concat_ws(自定义显示的格式)

concat (字符串拼接)

concat_ws (指定分割符进行拼接)

#concat内置函数可以对查询出来的字段数据进行字符串拼接




select concat('姓名:',name) from 表名;    #对查询出来的姓名字段的数据前面都加上一个字符串姓名,能够友好的显示查询出来的数据的意思,其实就是字符串拼接


select concat(name,':',salary)as info from 表名;        #concat还可以将两个字段的数据拼接,,产生一个新的字段数据显示出来,as是个这个字段重命名
	
	
concat_ws()函数也只是字符串拼接,不过concat_ws的凭借方式是类似与python join拼接的方式,就是以某个元素对多个字段的数据进行拼接
select concat_ws('_','姓名:',name,'性别:',sex)as info from 表名;
1|05）case end 语句

case end 语句对查询出来的每一个数据进行加工和显示

#case语句的作用:
   对表中数据进行查询时,对于查询出来的语句进行进一步的加工,并且显示出来,case end语句有点像python里的if语句,不过sql的case语句需要表明开始和结束,case代表开始,end代表束.when代表的就是if条件,else就是其他,当有多个when的时候,从第二个when开始就代表的类似于elif的意思,知道了解就好----->






select(
	case
    when name='田少岗' concat(name,'逗比')
    when name='田彩' concat(name,'女神')
    else 
    	concat(name,'边玩去')
   	end
)as now_name from 表名;
1|06）select的相关总结
 
1>#可以查一个,多个,*所有调用函数：     now(),database(),concat(),concat_ws()

2>#可以四则运算

3>#可以去重 distinct

4>#可以使用case end 条件判断语句

1|02、 where （筛选行语句）
1|01） where 概述
 
1:#范围查询
    #< > >= <= !=  <>代表不等于和!=是一个意思 
    select age from 表名 where age <100 and age>20;(多条件查询)
    #between 1 and 10 找寻1到10之间的
    select age from 表名 where between 1 and 100; >>注意包含1和100
    #in (1,2,3,4) 多选一
    select age from 表名 where age in (10,20,30,40);
    #将age=10和age=20的都能取出来
    select age from 表名 where age=10 or age=20;




2:#模糊查询
    #like
    like的表现形式是:'%a'查询以a结尾的,'a%'查询以a开始的,'%a%'查询包含a的
    
    select name from 表名 where name like '田%';>>>     #查询出姓田的所有人
    like还有一种形式,'_a','a_','a__'一个划线代表一个字符,%代表的是任意长度
    select name from 表名 where name 'like '田_';>>>    #查询出以田开头的两个字的姓名
    (pattern可以是%或_
    %表示任意多字符
    _表示一个字符)
    
    #regexp 可以使用正则匹配(记一下正则表达式)



3:#is is not 
   is null is not null    #一般用来判断是不是空


4:#逻辑运算:
  在多个条件直接可以使用逻辑运算符and or not
1|02）where 查询的使用
 
#1:单条件查询
    SELECT name FROM employee
        WHERE post='sale';
      
      
#2:多条件查询
    SELECT name,salary FROM employee
        WHERE post='teacher' AND salary>10000;



#3:关键字BETWEEN AND
    SELECT name,salary FROM employee 
        WHERE salary BETWEEN 10000 AND 20000;

    SELECT name,salary FROM employee 
        WHERE salary NOT BETWEEN 10000 AND 20000;
    
    
    
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NULL;

    SELECT name,post_comment FROM employee 
        WHERE post_comment IS NOT NULL;
        
    SELECT name,post_comment FROM employee 
        WHERE post_comment=''; 注意''是空字符串，不是null
    ps：
        执行
        update employee set post_comment='' where id=2;
        再用上条查看，就会有结果了





#5:关键字IN集合查询
    SELECT name,salary FROM employee 
        WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
    
    SELECT name,salary FROM employee 
        WHERE salary IN (3000,3500,4000,9000) ;

    SELECT name,salary FROM employee 
        WHERE salary NOT IN (3000,3500,4000,9000) ;





#6:关键字LIKE模糊查询
    通配符’%’
    SELECT * FROM employee 
            WHERE name LIKE 'eg%';

    通配符’_’
    SELECT * FROM employee 
            WHERE name LIKE 'al__';
1|03）案列：
 
#示列：
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为NULL的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪





#where使用
1》select name,age from employee where post = 'teacher';
2》select name,age from employee where post='teacher' and age > 30; 
3》select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
4》select * from employee where post_comment is not null;
5》select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
6》select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
7》select name,salary*12 from employee where post='teacher' and name like 'jin%';

1|03、 group by(分组查询)
1|01）group by概述
 
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

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

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

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


#4、大前提：
    可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

group by：可以对某个字段的值进行分组,这个字段有多少种值就分多少组,group by还有一个特性就是去重,一旦使用group by对数据分组了,就不能对某一条数据进行操作,永远都是这一组数据

group_concat()函数(只用来做最终的显示,不做中间数据的操作)可以显示当前这一组的所有信息,拼在一起显示
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
'''


###了解即可
mysql> set global sql_mode='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值
1|02）group by 查询的使用
 
############！！！SQL_MODE设置！！！

#查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

#注意！！！
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。


#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';


mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.00 sec)

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


#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的

mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql> quit      #设置成功后，一定要退出，然后重新登录方可生效
Bye

mysql> use db1;
Database changed
mysql> select * from emp group by post;    #报错
ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY
mysql> select post,count(id) from emp group by post;             #只能查看分组依据和使用聚合函数
+----------------------------+-----------+
| post                       | count(id) |
+----------------------------+-----------+
| operation                  |         5 |
| sale                       |         5 |
| teacher                    |         7 |
| 老男孩驻沙河办事处外交大使 |         1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)
1|03) group by

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义,多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

#单独使用GROUP BY关键字分组
    SELECT post FROM employee GROUP BY post;
    
    #注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数




#GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;       #按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;




#GROUP BY与聚合函数一起使用
    select post,count(id) as count from employee group by post;       #按照岗位分组，并查看每个组有多少人

1|04、 聚合函数 （count、max、min、avg、sum ）

分组之后:select只能看到分组字段以及聚合的结果

1|01）常用的聚合函数
 
1:#count 计数
2:#max  求最大值
3:#avg  求平均值
4:#min  求最小值
5:#sum  求和
1|02）聚合函数的使用
 
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组


#示例：
    SELECT COUNT(*) FROM employee;
    SELECT COUNT(*) FROM employee WHERE depart_id=1;
    SELECT MAX(salary) FROM employee;
    SELECT MIN(salary) FROM employee;
    SELECT AVG(salary) FROM employee;
    SELECT SUM(salary) FROM employee;
    SELECT SUM(salary) FROM employee WHERE depart_id=3;
1|03) 案列：
 
#示列：
1》查询岗位名以及岗位包含的所有员工名字
2》查询岗位名以及各岗位内包含的员工个数
3》查询公司内男员工和女员工的个数
4》查询岗位名以及各岗位的平均薪资
5》查询岗位名以及各岗位的最高薪资
6》查询岗位名以及各岗位的最低薪资
7》查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
8》查询出每个部门的员工数
9》查询男人数与女人数
10》查询年龄在20岁以上的男人数与女人数
11》查询每个部门20岁以上人的平均薪资
12》查询每个部门的平均薪资及最大薪资最小年龄和年龄的和
13》查询每个部门的员工及年龄

#聚合函数的使用：
#题目1:分组
mysql> select post,group_concat(name) from employee group by post;
+-----------------------------------------+---------------------------------------------------------+
| post                                    | group_concat(name)                                      |
+-----------------------------------------+---------------------------------------------------------+
| operation                               | 张野,程咬金,程咬银,程咬铜,程咬铁                        |
| sale                                    | 歪歪,丫丫,丁丁,星星,格格                                |
| teacher                                 | alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙   |
| 老男孩驻沙河办事处外交大使              | egon                                                    |
+-----------------------------------------+---------------------------------------------------------+





#题目2：
mysql> select post,count(id) from employee group by post;
+-----------------------------------------+-----------+
| post                                    | count(id) |
+-----------------------------------------+-----------+
| operation                               |         5 |
| sale                                    |         5 |
| teacher                                 |         7 |
| 老男孩驻沙河办事处外交大使              |         1 |
+-----------------------------------------+-----------+





#题目3：
mysql> select sex,count(id) from employee group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+




#题目4：
mysql> select post,avg(salary) from employee group by post;
+-----------------------------------------+---------------+
| post                                    | avg(salary)   |
+-----------------------------------------+---------------+
| operation                               |  16800.026000 |
| sale                                    |   2600.294000 |
| teacher                                 | 151842.901429 |
| 老男孩驻沙河办事处外交大使              |   7300.330000 |
+-----------------------------------------+---------------+




#题目5
mysql> select post,max(salary) from employee group by post;
+-----------------------------------------+-------------+
| post                                    | max(salary) |
+-----------------------------------------+-------------+
| operation                               |    20000.00 |
| sale                                    |     4000.33 |
| teacher                                 |  1000000.31 |
| 老男孩驻沙河办事处外交大使              |     7300.33 |
+-----------------------------------------+-------------+




#题目6
mysql> select post,min(salary) from employee group by post;
+-----------------------------------------+-------------+
| post                                    | min(salary) |
+-----------------------------------------+-------------+
| operation                               |    10000.13 |
| sale                                    |     1000.37 |
| teacher                                 |     2100.00 |
| 老男孩驻沙河办事处外交大使              |     7300.33 |
+-----------------------------------------+-------------+




#题目七
mysql> select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex    | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+


#题目八
mysql> select post,count(id) from employee group by post;


#题目九
mysql> select sex,count(id) from employee group by sex;


#题目十
mysql> select sex,count(id) from employee where age > 20 group by sex;


#题十一
mysql> select post,avg(salary) from employee where age > 20 group by post;



#题十二
mysql> select post,avg(salary),max(salary),min(age),count(id),sum(age) from employee group by post;



#题十三
mysql> select post,group_concat(name,":",age) from employee group by post;
+-----------------------------------------+------------------------------------------------------------------------------+
| post                                    | group_concat(name,":",age)                                                   |
+-----------------------------------------+------------------------------------------------------------------------------+
| operation                               | 程咬铁:18,程咬铜:18,程咬银:18,程咬金:18,张野:28                              |
| sale                                    | 格格:28,星星:18,丁丁:18,丫丫:38,歪歪:48                                      |
| teacher                                 | 成龙:48,jinxin:18,jingliyang:18,liwenzhou:28,yuanhao:73,wupeiqi:81,alex:78   |
| 老男孩驻沙河办事处外交大使              | egon:18                                                                      |
+-----------------------------------------+------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

1|05、 having (过滤条件)

having是针对一个组做的过滤条件,是放在group by 后面执行的,他的意思和where是一样的,where group 和 having的执行顺序是:(where—>group by—>having)having一般都是和having一起用的

#示列： 
select post,group_concat(name),count(id) from employee group by post having count(id) < 2;
1|01）having与where的关系
 
#having与where的之间的不同！！！
！！！执行优先级从高到低：where > group by > having 


1》Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数

2》Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
1|02）having 与where对比

sql_mode:简而言之就是：它定义了你MySQL应该支持的sql语法，对数据的校验等等

#select @@sql_mode:使用该命令我们可以查看我们当前数据库的sql_mode

mysql> select @@sql_mode;
+--------------------+
| @@sql_mode         |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)


mysql> select * from emp where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)


mysql> select * from emp having salary > 100000;
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause


mysql> select post,group_concat(name) from emp group by post having salary > 10000;     #错误，分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
mysql> select post,group_concat(name) from emp group by post having avg(salary) > 10000;
+-----------+-------------------------------------------------------+
| post | group_concat(name) |
+-----------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+-------------------------------------------------------+
2 rows in set (0.00 sec)
1|03）案列：
 
#示列：
1》查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
2》查询各岗位平均薪资大于10000的岗位名、平均工资
3》查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
4》查询出平均薪资在10000以上的部门
5》查出部门内男员工平均工资在3000以上的部门

#题1：
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 |
+-----------------------------------------+--------------------+-----------+


#题目2：
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+


#题目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 |
+-----------+--------------+


#题目4：
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)



#题目5:
mysql> select post,avg(salary) from employee where sex="male" group by post 
having avg(salary) > 3000;
+-----------------------------------------+---------------+
| post                                    | avg(salary)   |
+-----------------------------------------+---------------+
| operation                               |  16000.043333 |
| teacher                                 | 175650.051667 |
| 老男孩驻沙河办事处外交大使              |   7300.330000 |
+-----------------------------------------+---------------+
3 rows in set (0.00 sec)

1|06、 order by （排序）

对查询出来的数据进行排序,可以升序或者降序(desc),可以使用多个条件一起排序,执行顺序按照从左到右执行排序

1|01）asc与desc（升序与降序）
 


SELECT * FROM employee ORDER BY salary;       #默认不写，就是升序排列
SELECT * FROM employee ORDER BY salary ASC;   #升序
SELECT * FROM employee ORDER BY salary DESC;  #降序
1|02）排序方式
 
#按单列排序
    SELECT * FROM employee ORDER BY salary;
    SELECT * FROM employee ORDER BY salary ASC;
    SELECT * FROM employee ORDER BY salary DESC;
    
    

#按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
    SELECT * from employee
        ORDER BY age,
        salary DESC;
1|03）案列：
 
#示列：
1》查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
2》查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
3》查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
4》查询所有员工信息，按照年龄进行升序排列
5》查询所有员工信息，按照年龄进行降序排列
6》查询每个部门薪资大于3000的平均薪资，结果按升序排列
7》查询所有的员工的年龄按升序排列，id按降序排列


#题目1
mysql> select * from employee ORDER BY age asc,hire_date desc;
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
| 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 |
| 12 | 星星       | female |  18 | 2016-05-13 | sale                                    | NULL         |    3000.29 |    402 |         2 |
| 17 | 程咬铜     | male   |  18 | 2015-04-11 | operation                               | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁     | female |  18 | 2014-05-12 | operation                               | NULL         |   17000.00 |    403 |         3 |
| 16 | 程咬银     | female |  18 | 2013-03-11 | operation                               | NULL         |   19000.00 |    403 |         3 |
| 11 | 丁丁       | female |  18 | 2011-03-12 | sale                                    | NULL         |    1000.37 |    402 |         2 |
|  6 | jingliyang | female |  18 | 2011-02-11 | teacher                                 | NULL         |    9000.00 |    401 |         1 |
| 15 | 程咬金     | male   |  18 | 1997-03-12 | operation                               | NULL         |   20000.00 |    403 |         3 |
|  7 | jinxin     | male   |  18 | 1900-03-01 | teacher                                 | NULL         |   30000.00 |    401 |         1 |
| 13 | 格格       | female |  28 | 2017-01-27 | sale                                    | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野       | male   |  28 | 2016-03-11 | operation                               | NULL         |   10000.13 |    403 |         3 |
|  5 | liwenzhou  | male   |  28 | 2012-11-01 | teacher                                 | NULL         |    2100.00 |    401 |         1 |
| 10 | 丫丫       | female |  38 | 2010-11-01 | sale                                    | NULL         |    2000.35 |    402 |         2 |
|  9 | 歪歪       | female |  48 | 2015-03-11 | sale                                    | NULL         |    3000.13 |    402 |         2 |
|  8 | 成龙       | male   |  48 | 2010-11-11 | teacher                                 | NULL         |   10000.00 |    401 |         1 |
|  4 | yuanhao    | male   |  73 | 2014-07-01 | teacher                                 | NULL         |    3500.00 |    401 |         1 |
|  2 | alex       | male   |  78 | 2015-03-02 | teacher                                 | NULL         | 1000000.31 |    401 |         1 |
|  3 | wupeiqi    | male   |  81 | 2013-03-05 | teacher                                 | NULL         |    8300.00 |    401 |         1 |
+----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)


#题目2
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+

#题目3
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| teacher   | 151842.901429 |
| operation |  16800.026000 |
+-----------+---------------+

#题目4：
mysql> select * from employee order by age asc;


#题目5：
mysql> select * from employee order by age desc;



#题目6：
mysql> select post,avg(salary) from employee where sex="male" group by post 
having avg(salary) > 3000
order by avg(salary);


#题目7：
mysql> select * from employee order by age asc,id desc;

1|07、limit（限制查询记录；分页）

可以和order by一起使用,先排序再分页取几个,还可以指定删选的范围,limit m,n这个意思是从m+1开始取n条数据,不如limit 0,6,就是从1开始取六条数据,然是limit分页时,只适合做小数据的分页,当数据量过大时,效率就会非常慢,limit还有一种语法是:limit n offset m,这个意思也是从m+1开始,取n条,这个语法知道就行,

1|01）limit的使用
 
#默认初始位置为0,从第一条开始顺序取出三条 
	SELECT * FROM employee ORDER BY salary DESC LIMIT 3;                #排序后限制显示的行数
	
	
	
#从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 0,5;     #排序后指定显示的行数 



##从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
    SELECT * FROM employee ORDER BY salary DESC
        LIMIT 5,5;
1|02）案列：
 
1》分页显示，每页5条


#从第一条开始，每页显示5条
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)




#从第5条开始，每页显示5条
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 | 成龙       | 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)




#从第10条开始，每页显示5条
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)




#使用limit取出薪资最高的员工
mysql> select * from employee order by salary desc limit 1; 
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex  | age | hire_date  | post    | post_comment | salary     | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
|  2 | alex | male |  78 | 2015-03-02 | teacher | NULL         | 1000000.31 |    401 |         1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)

1|08、regexp(正则查询)
1|01）regexp使用
 
#查询name里以ale开头的名字
SELECT * FROM employee WHERE name REGEXP '^ale';


#查询name里以xin结尾的名字
select * from employee where name regexp "xin$";


#查询name里包含两个mm的名字
SELECT * FROM employee WHERE name REGEXP 'm{2}';


小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
1|02）案列：
 
1》查看所有员工中名字是jin开头，n或者g结果的员工信息
2》查询所有员工名字是程开头的，金铁结尾的员工信息

#使用regexp查询
mysql> select * from employee where name regexp '^jin.*[gn]$';



mysql> select * from employee where name regexp "^程.*[金铁]$";
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name      | sex    | age | hire_date  | post      | post_comment | salary   | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金    | male   |  18 | 1997-03-12 | operation | NULL         | 20000.00 |    403 |         3 |
| 18 | 程咬铁    | female |  18 | 2014-05-12 | operation | NULL         | 17000.00 |    403 |         3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)

__EOF__

本文作者：ଲ小何才露煎煎饺
本文链接：https://www.cnblogs.com/zeny/p/15121498.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   ଲ小何才露煎煎饺  阅读(247)  评论(0)  编辑  收藏  举报
编辑推荐：
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
阅读排行：
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
点击右上角即可分享
微信分享提示