当前位置:   article > 正文

【MySQL】select 条件查询、order by排序、group by分组查询以及单行处理函数的使用【附源码】_数据库的slect语句offered_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

在这里插入图片描述

声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/你好赵伟/article/detail/189170
推荐阅读
相关标签
  

闽ICP备14008679号