【MySQL】select 条件查询、order by排序、group by分组查询以及单行处理函数的使用【附源码】_数据库的slect语句offered_by

一、select 基本语法

select 语句用于从数据库中选取数据。结果被存储在一个结果表中，称为结果集。
select查询语句中出现引号和分号都是英文符号，查询语句的结束标志是分号。

以dept表（部门表）为例演示：
说明：deptno（部门号）、dname（部门名）、loc（地点）

+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
1
2
3
4
5
6
7
8

1.查询单列数据：select 列名 from 表名;

select deptno from dept;
1

2.查询多列数据：select 列名1,列名2... from 表名;

select deptno,dname from dept;
1

3.查询所有数据：select 列名1,列名2... from 表名;（列出所有列名）
或 select * from dept;

select deptno,dname,loc from dept;
select * from dept;
1
2

4.给查询的列起别名：select 列名1 as 别名1,列名2 as 别名2... from 表名;

select deptno as id,dname as name from dept;
1

若写成：select 列名1,列名2... as 别名1 from 表名;（就近将select语句中的最后的一个列名改为别名）

select deptno,dname as id from dept;
1

（起别名时as可以省略，若别名中含有空格或中文，则使用单引号将别名括起来，起别名不会改变原本的表的列名）

select deptno as 'd id' from dept;
select deptno as '编号' from dept;
1
2

5.列名参数数学运算：（查询员工的年薪）

select ename,sal*12 as year_salary from emp;
1

二、select 条件查询

以emp表（员工表）为例演示：
说明：
empno（员工编号）、ename（员工姓名）、job（工作）、mgr（上级领导编号）、hiredate（入职日期）、sal（月薪）、comm（津贴）、deptno（所在部门编号）

+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

语法格式：select 列名1,列名2... from 表名 where 条件;
1.= 等于：
例1：查询工资是800的员工姓名

select ename from emp where sal = 800;
1

例2：查询工作是SALESMAN的员工工资

select sal from emp where job='SALESMAN';
1

2.<> 不等于：
例1：查询部门号不是20的员工工资

select sal from emp where deptno<>20;
1

例2：查询工作不是SALESMAN的员工工资

select sal from emp where job<>'SALESMAN';
1

3.< 小于：
例：查询工资低于1000的员工姓名和工作

select ename,job from emp where sal<1000;
1

4.> 大于：
例：查询工资大于1000的员工姓名和工作

select ename,job from emp where sal>1000;
1

5.>= 大于等于：
例：查询工资大于等于1000的员工姓名和工作

select ename,job from emp where sal>=1000;
1

6.between a and b 两者之间：（闭区间，要求a<b）
例1：查询工资在1000~1500之间的员工姓名和工作

select ename,job from emp where sal between 1000 and 1500;
1

例2：查询工资在1000~1500之间的员工姓名和工作

select ename,job from emp where sal>=1000 and sal<=1500;
1

7.null 为null（is not null 不为null）:
例：查询津贴为null的员工姓名和工作

select ename,job from emp where comm is null;
1

8.and 并且：
例：查询工作是manager并且工资大于2500的员工信息

select * from emp where job='manager' and sal>2500;
1

9.or 或者：
例1：查询工作是manager或者president的员工信息

select * from emp where job='manager' or job='president';
1

例2：查询工资大于2000，并且部门号是10或20的员工信息

select * from emp where sal>2000 and (deptno=10 or deptno=20);
1

10.in 包含，相当于多个or：
例：查询工资是800和5000的员工信息

select * from emp where sal in (800,5000);
1

11.not in 不包含：
例：查询工资不是800和5000的员工信息

select * from emp where sal not in(800,5000);
1

12.like 模糊查询：
① %匹配任意个字符：
例1：查询员工名中含有o的员工信息

select * from emp where ename like '%o%';
1

例2：查询员工名以r结尾的员工信息

select * from emp where ename like '%r';
1

例3：查询员工名中含有_的员工信息（_有特殊含义，必须使用\进行转译）

select * from emp where ename like '%\_%';
1

② _匹配一个字符：
例1：查询员工名中第二个字母是a的员工信息

select * from emp where ename like '_a%';
1

例2：查询ename第二个字母是a的员工信息

select * from emp where substr(ename,2,1)='a';
1

注意：在sql中%和_有特殊的含义，若想使用这两个符号，必须在符号前面使用\进行转译。

三、order by排序

order by 列名：用于为指定的列排序，默认是升序（asc），指定为降序：order by 字段名 desc，也可以指定为升序：order by 字段名 desc。
1.asc 升序：（默认）
例1：查询员工信息，将查询到的员工信息按照工资升序输出

select * from emp order by sal asc;
1

例2：查询员工信息，将查询到的员工信息按照工资升序输出

select * from emp order by sal;
1

2.desc 降序：
例：查询员工信息，将查询到的员工信息按照工资降序输出

select * from emp order by sal desc;
1

进阶：多个字段排序（二级排序）
例：查询所有原的员工姓名、工资，按照工资升序，当工资相同时按照姓名升序输出。

select ename,sal from emp order by sal asc,ename asc;
1

按照select查询列的字段位置排序：（了解：不建议使用，健壮性太弱）
例：查询员工姓名和工资，结果按照工资进行排序（默认升序）

select ename,sal from emp order by 2;
1

综合实例：
例： 查询工资在1500到2500之间的员工的姓名和工资，按工资的升序输出，当工资相同时按照姓名升序。

select ename,sal from emp where sal between 1500 and 2500 order by sal asc,ename asc;
//执行顺序：from--->where--->select--->order by
1
2

四、数据处理函数（单行处理函数）

单行处理函数：一个输入对应一个输出（如lower、upper…）
多行处理函数：多个输入对应一个输出（如sum、count…）
1.lower 转小写：
例：查询员工的姓名，将查询到的姓名转为小写输出

select lower(ename) as ename from emp;
1

2.upper 转大写：
例：查询员工的姓名，将查询到的姓名转为大写输出

select upper (ename) as ename from emp;
1

3.substr 取子串：（str(被截取的字符串，起始下标，截取的长度)）
例1：查询员工姓名的前两个字母

select substr(ename,1,2) from emp;
1

例2：查询员工姓名第二个字母是a的员工信息

select * from emp where substr(ename,2,1)='a';
1

扩展：首字母大写：
例：查询学生姓名，将查询到的员工姓名的首字母转为大写输出

select concat(upper(substr(name ,1,1.)) , substr(name ,2 , length (nane) - 1)) as result from t_student;
1

4.length(字段名) 取长度：
例：查询并输出员工姓名中各个名字的长度

select length(ename) as name_len from emp;
1

5.trim(字符串） 去空格：
例：查询姓名是KING的员工信息

select * from emp where ename=trim('    KING');
1

6.round（数字，保留小数位数）： 取整
例1：取整输出124

select round(123.564) as result from dept;
1

例2：取整输出100

select round(123.564,-2) as result from dept;
1

例3：取整输出120

select round(123.564,-1) as result from dept;
1

例4：保留一位小数，输出123.6

select round(123.564,1) as result from dept;
1

例5：保留两位小数，输出123.56

select round(123.564,2) as result from dept;
1

例6：输出dept中同等行数的hello（默认列名也是hello）

select 'hello' as result from dept;
1

例7：输出emp中同等行数的1000（默认列名也是1000）

selecet 1000 as result from emp;
1

7.rand() 随机数：
例：输出与部门表同等行的随机数（0-1范围内）

select rand() from dept;
1

8.ifnull(字段，替换值) 将null转换为一个具体值:
（在数据库中，null不是值，是代表为空，所以和它做运算结果任然是null）
例：null参与运算时结果依然是null

select ename,sal+comm as sum_sal from emp;
1

正确使用ifnull()：
语法：if(字段，0)：如果字段是null，则把null当成0处理（0可以替换为别的数字或字母）
例：查询员工的年薪和员工姓名

select ename,(sal + ifnull(comm,0)) as year_Sal from emp;
1

9.case…when…then…when…then…else…end
例：当员工的工作岗位是MANAGER的时候，工资上调10%，当工作岗位是SALESMAN的时候，工资上调50% ,其它正常。（注意：不修改数据库，只是将查询结果显示为工资上调)

select ename,job,sal as oldsal,(case job when 'manager' then sal*1.1 when 'salesman' then sal*1.5 else sal end) as newsal from emp;
1

10.format 格式化
例：查询员工对应的工资，格式化后输出。

select ename,format(sal,"$999,999") as sal from emp;
1

11.str_to_date 将varchar转换为date（通常用于insert语句中，用于向表中插入数据）
语法规则：str_to_date("日期字符串","日期格式")

mysql日期格式：%Y 年，%m 月，%d 日，%h 时，%i 分，%s 秒
（若在插入date时，日期格式就是%Y-%m-%d则可以不用转换）
1
2

create table t_user(id int,name varchar(32),birth date);
1

例：向t_user表中添加一条记录

insert into t_user values(1,"小王",str_to_date("15-01-2000","%d-%m-%Y"));
insert into t_user values(2,"小吕","2002-5-25");
1
2

select id,name,birth from t_user;
1

12.date_format 将date类型转换为varchar
（注意：当日期格式是）
语法格式：date_format("日期类型数据","日期格式")
例：查询用户的信息，将生日以“年.月.日”格式输出

select id,name,date_format(birth,"%Y.%m.%d") as birth from t_user;
1

五、分组函数（多行处理函数）

多行处理函数特点：输入多行，结果输出一行。
分组函数在使用时必须先进行分组，才能使用，否则默认是整张表作为一组。
1.count 计数：
例：查询员工表中员工的个数

select count(ename) as count from emp;
1

2.sum 求和：
例：查询员工表中工资的总和

select sum(sal) as sum from emp;
1

3.avg 求平均：
例：查询员工表中的工资的平均值

select avg(sal) as avg_sal from emp;
1

4.max 求最大值：
例：查询员工表中的工资的最大值

select max(sal) as max_sal from emp;
1

5.min 求最小值：
例：查询工资表中的工资的最小值

select min(sal) as min_sal from emp;
1

分组函数使用的注意事项：
① 分组函数使用时自动处理null，不需要使用ifnul()手动处理
例1：查询员工表中津贴的总和

select sum(comm) as comm from emp;
1

例2：查询员工表中津贴不为null的数目（comm为null不统计）

select count(comm) as count from emp;
1

② count(*)和count(字段名)不同，前者统计的是全部的行记录条数；后者统计的是该列的字段中不为null的条数。（因为一条记录中不可能每个列都为null）
例1：查询员工表中全部的行记录条数

select count(*) from emp;
1

例2：统计员工表中津贴的个数（津贴为null时不统计）

select count(comm) as count from emp;
1

③ 分组函数不能直接使用在where条件中
如下语句会报错：（ERROR 1111 (HY000): Invalid use of group function）

select ename,sal from emp where sal > min(sal);
1

④ 所有的分组函数可以放在一条语句中使用
例：查询员工表的行记录条数、工资总和、工资最小值、工资最大值以及工资的平均值

select count(*),sum(sal),max(sal),min(sal),avg(sal) from emp;
1

六、group by分组查询

语法格式：select 字段1，字段2... from 表名 group by 列名

所有的关键字放在一起：select… from… where… group by…having… order by…
关键字执行顺序：from—>where—>group by—>having—>select—>order by
先从某张表中查询数据，然后经过where条件筛选出有价值的数据，对这些有价值的数据进行分组。再分组之后可以使用having继续筛选。select查询出来。最后排序输出!

重点结论:
① 在一条select语句当中,，如果有group by语句的话，select后面只能跟：参加分组的字段，以及分组函数，其它的一律不能跟（没有意义）。
② 在分组查询中，group by可以跟多个列名（对多个数据进行多层分组），如果有筛选条件在group by后使用，必须使用havIng 语句进行筛选。

1.查询每个岗位的工资总和

select job,sum(sal) as sum_sal from emp group by job;
1

2.查询每个部门中工资的最大值

select deptno,max(sal) from emp group by deptno;
1

3.查询每个部门，不同工作岗位的工资

//此处order by排序操作只能放在group by后面，顺序不能颠倒
select deptno,job,max(sal) from emp group by deptno,job order by deptno;
1
2

4.查询每个部门的最高薪资，要求输出最高薪资大于3000的部门编号

//数据库数据较多时，效率比较低
select deptno,max(sal) from emp group by deptno having max(sal)>3000;
1
2

优化策略：where和having，优先选择where，where实在完成不了了，再选择having。

//先使用where进行条件筛选，再进行分组，效率较高
select deptno,max(sal) from emp where sal>3000 group by deptno;
1
2

七、distinct关键字

distinct关键字：查询结果中去除重复的数据。
语法格式：select distinct 列名1，列名2... from 表名
① distinct关键字只能放在查询的第一个列名前面，只是对查询结果去重，不会影响数据库中的表数据和结构；
② 当distinct关键字后面跟多个多个列名时表示对多个列名联合去重。

1.查询工作的名称
例：查询员工表中的工作名称

select distinct job from emp;
1

2.统计员工表中工作岗位的数量

select count(distinct job) from emp;
1

综合查询进阶练习：

1.查询每个部门的平均工资，要求输出平均工资大于2000的部门编号和平均工资

//此处只能使用having进行条件筛选
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
1
2

5.查询每个岗位的平均薪资，要求输出平均薪资大于1500的，除MANAGER岗位之外，要求按照平均薪资降序排列。

select job,avg(sal) as avg_sal from emp where job<>'manager' group by job having avg_sal > 1500 order by avg_sal desc;
1