[大数据]Day_01【MySQL】select 条件查询、order by排序、group 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.查询单列数据：select 列名 from 表名;

select deptno from dept;

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

select deptno,dname from dept;

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

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

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

select deptno as id,dname as name from dept;

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

select deptno,dname as id from dept;

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

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

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

select ename,sal*12 as year_salary from emp;

二、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 |
+-------+--------+-----------+------+------------+---------+---------+--------+

语法格式：select 列名1,列名2... from 表名 where 条件;
1.= 等于：

select ename from emp where sal = 800;//查询sal=800的员工ename
select sal from emp where job='SALESMAN';//查询job是SALESMAN的员工sal

2.<> 不等于：

select sal from emp where deptno<>20;//查询deptno不等于20的员工sal
select sal from emp where job<>'SALESMAN';//查询job不是SALESMAN的员工sal

3.< 小于：

select ename,job from emp where sal<1000;//查询sal<1000的员工ename,job

4.> 大于：

select ename,job from emp where sal>1000;//查询sal>1000的员工ename,job

5.>= 大于等于：

select ename,job from emp where sal>=1000;//查询sal>=1000的员工ename,job

6.between a and b 两者之间：（闭区间，要求a<b）

select ename,job from emp where sal between 1000 and 1500;//查询sal在1000~1500之间的员工ename,job 
select ename,job from emp where sal>=1000 and sal<=1500;//查询sal在1000~1500之间的员工ename,job 

7.null 为null（is not null 不为null）:

select ename,job from emp where comm is null;//查询comm为null的员工ename,job

8.and 并且：

select * from emp where job='manager' and sal>2500;//查询job是manager并且sal大于2500的原信息

9.or 或者：

select * from emp where job='manager' or job='PRESIDENT';//查询job是manager或者PRESIDENT的员工信息
select * from emp where sal>2000 and (deptno=10 or deptno=20);//查询sal大于2000，并且deptno是10或20的员工信息

10.in 包含，相当于多个or：

select * from emp where sal in (800,5000);//查询sal是800和5000的员工信息

11.not in 不包含：

select * from emp where sal not in(800,5000);//查询sal不是800和5000的员工信息

12.like 模糊查询：
① %匹配任意个字符：

select * from emp where ename like '%o%';//查询ename中含有o的员工信息
select * from emp where ename like '%r';//查询ename以r结尾的员工信息
select * from emp where ename like '%\_%';//查询ename中含有_的员工信息（_有特殊含义，必须使用\进行转译）

② _匹配一个字符：

select * from emp where ename like '_a%';//查询ename第二个字母是a的员工信息
select * from emp where substr(ename,2,1)='a';//查询ename第二个字母是a的员工信息

三、order by排序

order by 列名：用于为指定的列排序，默认是升序（asc），指定为降序：order by 字段名 desc，也可以指定为升序：order by 字段名 desc。
1.asc 升序：（默认）

select * from emp order by sal asc;//将查询到的员工信息按照sal升序排列
select * from emp order by sal;//将查询到的员工信息按照sal升序排列

2.desc 降序：

select * from emp order by sal desc;//将查询到的员工信息按照sal降序排列

进阶：多个字段排序（二级排序）

select ename,sal from emp order by sal asc,ename asc;//查询所有原的ename,sal，sal升序排列，当sal相同时按照ename升序

按照select查询列的字段位置排序：（了解：不建议使用，健壮性太弱）

select ename,sal from emp order by 2;//查询ename和sal,结果按照sal进行排序（默认升序）

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

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

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

单行处理函数：一个输入对应一个输出
多行处理函数：多个输入对应一个输出（如sum）
1.lower 转小写：

select lower(ename) as ename from emp;//将查询到的数据字段转为小写

2.upper 转大写：

select upper (ename) as ename from emp;//将查询到的数据字段转为小写

3.substr 取子串：（str(被截取的字符串，起始下标，截取的长度)）

select substr(ename,1,2) from emp;//查询ename的前两个字母
select * from emp where substr(ename,2,1)='a';//查询ename第二个字母是a的员工信息

首字母大写：

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

4.length(字段名) 取长度：

select length(ename) as name_len from emp;//输出ename字段中的各个名字的长度

5.trim(字符串） 去空格：

select * from emp where ename=trim('    KING');//查询姓名是KING的员工信息

6.round（数字，保留小数位数）：取整

select round(123.564) as result from dept;//取整输出124
select round(123.564,-2) as result from dept;//取整输出100
select round(123.564,-1) as result from dept;//取整输出120
select round(123.564,1) as result from dept;//保留一位小数，输出123.6
select round(123.564,2) as result from dept;//保留两位小数，输出123.56

select 'hello' as result from dept;//输出dept中同等行数的hello（默认列名也是hello）
selecet 1000 as result from emp;//输出emp中同等行数的1000（默认列名也是1000）

7.rand() 随机数：

select rand() from dept;//生产dept同等行的随机数（0-1范围内）

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

select ename,sal+comm as sum_sal from emp;//null参与运算时结果依然是null

正确使用ifnull()：

//if(字段，0)：如果字段是null，则把null当成0处理（0可以替换为别的数字或字母）
select ename,(sal + ifnull(comm,0)) as year_Sal from emp;//查询员工的年薪和ename

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.count 计数：

select count(ename) as count from emp;//统计emp表中员工的个数

2.sum 求和：

select sum(sal) as sum from emp;//统计emp表中sal的总和

3.avg 求平均：

select avg(sal) as avg_sal from emp;//统计emp表中的sal平均值

4.max 求最大值：

select max(sal) as max_sal from emp;//统计emp表中的sal最大值

5.min 求最小值：

select min(sal) as min_sal from emp;//统计emp表中的sal最小值

分组函数使用的注意事项：
① 分组函数使用时自动处理null，不需要使用ifnul()手动处理：

select sum(comm) as comm from emp;//查询comm的总和

select count(comm) as count from emp;//统计comm的数目（comm为null不统计）

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

select count(*) from emp;//统计全部的行记录条数

select count(comm) as count from emp;

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

select ename,sal from emp where sal > min(sal);//ERROR 1111 (HY000): Invalid use of group function

④ 所有的分组函数可以放在一条语句中使用：

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

六、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;

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

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

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

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

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

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

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

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

七、distinct关键字

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

1.查询工作的名称

select distinct job from emp;//查询工作

2.统计工作岗位的数量

select count(distinct job) from emp;//统计工作岗位的数量

综合查询进阶练习：

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

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

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;