文章目录
一、数据查询
1. 单表查询
select [distinct ] col_name1, col_name2, … , col_namen
from tb_name
[where 限制条件]
[group by 分组列名]
[having 限制分组之后的条件]
[order by 排序列 [asc|desc] ];
[limit 偏移位置,显示行数]
除了select和from之外其他的都不是必须的。
1.1 select执行顺序
- from子句,组装来自表的数据,有可能是多张表
- where子句,基于指定的条件对记录行进行筛选
- group by子句,将数据划分为多个分组
- 使用聚合函数对每个小组中的数据进行计算
- having子句,进行条件筛选,这里可以使用聚合函数的计算结果
- 计算所有的运算表达式,主要是select部分
- order by子句,对结果集进行排序
1.2 where条件限定
- 限制查询条件,使用where子句
- 条件可以多个,使用逻辑操作符或者小括号进行条件的逻辑整合
- where子句的优先级别最高
- 比较操作表达式由比较操作符、逻辑操作符和值等组成
案例:
- 查看员工工资小于1000的员工id和名字
select id,last_name,salary
from s_emp
where salary < 1000;
- 查看员工部门id为41且职位名称为Stock Clerk (存库管理员)的员工id和名字
select id,last_name,dept_id,title
from s_emp
where dept_id = 41 and title = 'Stock Clerk';
- 查看员工部门为41或者44号部门,且工资大于1000的员工id和名字
select id,last_name,dept_id,title,salary
from s_emp
where salary > 1000 and (dept_id = 41 or dept_id = 44 );
- 查看员工部门为41且工资大于1000, 或者44号部门的员工id和名字
select id,last_name,dept_id,title,salary
from s_emp
where salary > 1000 and dept_id = 41 or dept_id = 44;
- 基于员工表查询部门编号,重复的只保留一个
select distinct dept_id
from s_emp;
distinct后面如果有多列,列的组合重复的保留一个
1.3 排序
查询数据的时候进行排序,就是根据某个字段的值,按照升序或者降序的情况将记录显示出来
- order by语句,只对查询记录显示调整,并不改变查询结果,所以执行权最低,最后执行
- 排序的默认值是asc:表示升序,desc: 表示降序
- 如果有多个列排序,后面的列排序的前提是前面的列排好序以后有重复(相同)的值。
语法:
select col_name,...
from tb_name
order by col_name [asc|desc],...
案例:
- 查询员工信息,按照名字升序排序
select id,last_name,salary
from s_emp
order by last_name;
asc可以省略,默认升序
- 查询员工信息,按照薪水排序
select id,last_name,salary
from s_emp
order by 3 desc;
order by后的列可以直接写数字代替,数字代表select后列的位置,位置从1开始
- 查询员工信息,先对salary进行降序排列,再对last_name进行升序排序
select id,last_name,salary
from s_emp
order by 3 desc,2 asc;
1.4 分页
分页显示,就是将数据库中的结果集,一段一段显示出来需要的条件。
案例:
- 查询前5行数数据
select id,last_name,salary
from s_emp
limit 0,5;
//第一个参数表示:起始行从0开始
//第二个参数表示:每次拿几行数据
select id,last_name,salary
from s_emp
limit 5;
- 查询6到10行数据
select id,last_name,salary
from s_emp
limit 5,5;
1.5 聚合函数(多行变一行)
聚合函数作用于一组数据,并对一组数据返回一个值。
语法:
select 组函数(field1|*)
from table_name
where condition
group by field1,field2,...
[having condition];
- 组函数出现的位置
select后面
having后面
order by后面
where后面一定[不能]出现组函数- where和having对比:
where和having都是做条件筛选的
where执行的时间比having要早
where后面不能出现组函数
having后面可以出现组函数
where语句要紧跟from后面
having语句要紧跟group by后面- group by和having的关系
group by可以单独存在,后面可以不出现having语句
having不能单独存在,有需要的话,必须出现在group by后面- order by语句
如果sq|l语句中需要排序,那么就一定要写在sq|l语句的最后面
order by后也可以出现组函数
在使用聚合函数的时候:
- 如果还使用了group by分组,那么就表示先分组,然后对每一个小组使用聚 合函数
- 如果没有使用group by分组,那么就表示全部数据是一个默认小组,然后对这个全部数据使用聚合函数
分组函数:
- avg :求平均值…
- count:计算有多少条数据
- max:求最大值
- min:求 最小值
- sum:求和
案例:
- 查看员工平均薪水、总人数、最大薪水、最小薪水、总薪水。
select avg(salary),count(*),max(salary),min(salary),sum(salary)
from s_emp;
- 查询s_emp表中每个部门]的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;
- 查询41号部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id
having dept_id=41;
having对分组之后的结果筛选
- 查询s_emp表中每个部门员工的最高工资
select dept_id,max(salary)
from s_emp
group by dept_id;
- 查询s_emp表中每个部门]员工的工资总和
select dept_id,sum(salary)
from s_emp
group by dept_id;
- 统计每个部门中,每个职位的人数有多少(相同部门、相同职位的人数统计)
select dept_id,title,count(*)
from s_emp
group by dept_id,title
order by dept_id;
- 查询s_emp表中部门门]的平均工资大于等于1400的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>=1400;
- 查询部门门编号为41、42、43、44、45的部门平均薪水
select dept_id,avg(salary)
from s_emp
where dept_id in(41,42,43,44,45)
group by dept_id;
或者
select dept_id,avg(salary)
from s_emp
group by dept_id
having dept_id in(41,42,43,44,45);
- 查询员工表中每个部门门]的平均薪水,薪水相同的只计算一个人;
select dept_id,avg(distinct salary)
from s_emp
group by dept_id;
组函数中加入distinct,表示相同的值只计算一次
2. 子查询
子查询,也称嵌套查询,即一个select语句中嵌套了另外的一个或者多个select语句。
子查询的思路就是,把第一个sql语句的查询结果,在第二个sql语句中使用,这时候第-条sql语句的结果,在第二条sql中就可以充当一个where条件中的-一个值,或者充当一张虚拟的表。
2.1 单行子查询
操作符 | 含义 |
---|---|
= | equal to |
> | greater than |
>= | greater than or equal to |
< | less than |
<= | less than or equal to |
<> | not equal to |
案例:
(1)查询工资比Smith工资高的员工信息
- Smith工资
select salary
from s_emp
where last_name='Smith';
- 工资比940高的员工信息
select last_name,salary
from s_emp
where salary>940;
- 整合
select last_name,salary
from s_emp
where salary>(select salary
from s_emp
where last_name='Smith');
子查询不能为空,使用=、>、<、<、>=、<=值不能是多个值
(2)查询公司薪水最低的员工信息
- 公司最低薪水
select min(salary)
from s_emp;
- 找出薪水为750的员工信息
select id,last_name,salary
from s_emp
where salary = 750;
- 整合
select id,last_name,salary
from s_emp
where salary = (select min(salary)
from s_emp);
(3)查询最低工资大于41号部门最低工资的部门]jid和其最低工资
- 41号部门最低工资
select min(salary)
from s_emp
where dept_id=41;
- 查询最低工资大于940的部门门及最低薪水
select dept_id,min(salary)
from s_emp
group by dept_id
having min(salary)>940;
- 整合
select dept_id,min(salary)
from s_emp
group by dept_id
having min(salary)>(select min(salary)
from s_emp
where dept_id=41);
2.2 多行子查询
多行子查询也称为集合比较子查询,内查询返回多行,需要使用多行比较操作符。
操作符 | 含义 |
---|---|
IN | 等于列表中的任意一个 |
ANY | 需要和单行比较操作符一起使用,和子查询返回的某-一个值比较 |
ALL | 需要和单行比较操作符一起使用,和子查询返回的所有值比较 |
SOME | 实际上是ANY的别名,作用相同,一般常使用ANY |
案例:
(1)查询平均工资比41号部门的平均工资高的部门中员工的信息
- 41号部门的平均工资
select avg(salary)
from s_emp
where dept_id=41;
- 查询平均工资比1247.5高的部门]编号
select dept_id
from s_emp
where dept_id is not null
group by dept_id
having avg(salary)>1247.5;
- 查询(平均工资比41号部门的平均工资高的部门)中员工
select last_name,salary,dept_id
from s_emp
where dept_id in(10,31,32,33,35,50);
- 整合
select last_name,salary,dept_id
from s_emp
where dept_id in(select dept_id
from s_emp
where dept_id is not null
group by dept_id
having avg(salary)>(select avg(salary)
from s_emp
where dept_id=41));
(2)查询高于41号部门任意员工薪水的员工薪资
- 41号部门员工薪水
select salary
from s_emp
where dept_id=41;
- 查询薪水高于1450、1200. 1400、 940这些任意薪水的员工信息
select id,last_name,salary
from s_emp
where salary>1450 or salary>1200 or salary>1400 or salary>940;
- 整合
select id,last_name,salary
from s_emp
where salary > Any(select salary
from s_emp
where dept_id=41);
(3)查询高于41号部门所有员工薪水的员工薪资
- 41号部门员工薪水
select salary
from s_emp
where dept_id=41;
- 查询薪水高于1450、1200.、 1400、 940这些所有薪水的员工信息
select id,last_name,salary
from s_emp
where salary>1450 and salary>1200 and salary>1400 and salary>940;
- 整合
select id,last_name,salary
from s_emp
where salary > All(select salary
from s_emp
where dept_id=41);
3. 结果处理
每一条sql语句,查询出的一个结果,都可以被称为结果集,如果有俩条sql语句,每一条sql都可以查询出一个结果,这个被称之为结果集。那么我们可以使用下面的关键字对俩个结果集进行操作。
- union,取俩个结果集的并集
- union all,把俩个结果集合在-起显示出来
- minus,第一个结果集除去第二个结果集和它相同的部分,mysql不支持
- intersect,求俩个结果集的交集,mysqI不支持
俩个结果集中查询的列要完全一致(名称和类型)
案例:
- 取两个结果集的并集,重复的剔除数据
select id
from s_emp
where id<=5
union
select id
from s_emp
where id>=3 and id<=8;
- 取两个结果集的并集。重复的数据不剔除
select id
from s_emp
where id<=5
union all
select id
from s_emp
where id>=3 and id<=8;
4. 多表查询
多表查询,又称表联合查询,即一条sql语句涉及到的表有多张,数据通过特定的连接进行联合显示。
在数学中,两个集合x和Y的笛卡尔积,又称直积,表示X x Y,假设集合A={a, b},集合B={0, 1,2},则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。在数据库中,如果直接查询俩张表那么其查询结果就会产生笛卡尔。
原始数据:
create table bdept(
id int primary key auto_increment,
name varchar(20)
);
create table bemp(
id int primary key auto_increment,
name varchar(20),
salary float,
dept_id int,
foreign key(dept_id) references bdept(id)
);
insert into bdept values(1,'java'),(2,'bigdata'),(3,'web');
insert into bemp values(1,'lisi',1000,1),(2,'zhansan',1200,1),(3,'wangwu',1400,1),
(4,'zhaoliu',900,2),(5,'wangduidui',3500,null);
select * from bdept;
select * from bemp;
案例:
- 基于bemp和bdept两张表,构建笛卡尔积
select *
from bemp,bdept;
select *
from bemp join bdept;
连接查询又可以大致分为:
- 等值连接
- 不等值连接
- 外连接
- 左外连接
- 右外连接
- 全连接
- 自连接
4.1 等值连接
等值连接又称为内连接(自然连接),将两张具有关联关系的列的数据连接起来,连接查询where子句中用来连接两个表的条件称为连接条件或者连接谓词,当连接运算为=的时候,称之为等值连接,格式:
select col_name....
from 表1,表2
where [表1].[列名]=[表2].[列名]
或者
select col_name....
from 表1 inner join 表2
on [表1].[列名]=[表2].[列名]
案例:
- 查看bemp中员工信息及bdept部门名称
select s.id,s.name,s.salary,s.dept_id,d.id,d.name
from bemp s,bdept d
where s.dept_id=d.id;
或者
select s.id,s.name,s.salary,s.dept_id,d.id,d.name
from bemp s join bdept d
on s.dept_id=d.id;
或者
select s.id,s.name,s.salary,s.dept_id,d.id,d.name
from bemp s inner join bdept d
on s.dept_id=d.id;
两张表等值连接的时候,如果两张表字段相同,建议起别名区分
- 基于s_emp、 s_dept、s_region,查询出员工信息、所属部门名称及区域名称
select s.id,s.last_name,s.salary,s.dept_id,d.id,d.name,d.region_id,r.id,r.name
from s_emp s,s_dept d,s_region r
where s.dept_id=d.id and d.region_id=r.id;
或者
select s.id,s.last_name,s.salary,s.dept_id,d.id,d.name,d.region_id,r.id,r.name
from s_emp s
join s_dept d on s.dept_id=d.id
join s_region r on d.region_id=r.id;
4.2 外连接
外连接分为,左外连接、右外连接、全连接
(1)左外连接
左外连接:保留表关系中所有匹配的数据记录,包含关联左边表中不匹配的数据记录
语法:
select col_name....
from 表1 left [outer] join 表2
on [表1].[列名]=[表2].[列名]
案例:
- 查看bemp中员工信息及bdept部门名称,没有分配部门的员工也要显示
select s.id,s.name,s.salary,s.dept_id,d.id,d.name
from bemp s left join bdept d
on s.dept_id=d.id;
(2)右外连接
右外链接:保留表关系中所有匹配的数据记录,包含关联右边表中不匹配的数据记录。
语法:
select col_name....
from 表1 right [outer] join 表2
on [表1].[列名]=[表2].[列名]
案例:
- 查看bemp中员工信息及bdept部门名称, 没有员工的部门也要显示
select s.id,s.name,s.salary,s.dept_id,d.id,d.name
from bemp s right join bdept d
on s.dept_id=d.id;
(3)全连接
全连接:保留表关系中所有匹配的数据记录,包含关联左右两边表中不匹配的数据记录。
语法:
select col_name....
from 表1 left [outer] join 表2
on [表1].[列名]=[表2].[列名]
union
select col_name....
from 表1 right [outer] join 表2
on [表1].[列名]=[表2].[列名]
案例:
- 查看bemp中员工信息及bdept部门名称,员工没有分配部门]要显示出来,没有员工的部门也要显示
select s.id,s.name,s.salary,s.dept_id,d.id,d.name
from bemp s left outer join bdept d
on s.dept_id=d.id
union
select s.id,s.name,s.salary,s.dept_id,d.id,d.name
from bemp s right outer join bdept d
on s.dept_id=d.id;
(4)自连接
自连接就是一-张表,自己和自己连接后进行查询
案例:
- s_emp查询每个员工的名字以及员工对应的经理的名字
select e.id,e.last_name,e.manager_id,m.id as mid,m.last_name
from s_emp e,s_emp m
where e.manager_id=m.id;
(5)公式表达式
公用表表达式类似于子查询,不过,跟子查询不同的是,它可以被多次引用,而且可以被其他的普通公用表表达式所引用
语法:
with 临时表名称
as (子查询)
select |delete |update 语句;
案例:
- 查询员工所在的部门]的详细信息
select id,name
from s_dept
where id in(select distinct dept_id
from s_emp
where dept_id is not null);
with emp_deptid
as (select distinct dept_id
from s_emp
where dept_id is not null)
select d.id,d.name
from s_dept d,emp_deptid e
where d.id=e.dept_id;
二、运算符
1. 算术运算符
运算符 | 名称 | 作用 | 示例 |
---|---|---|---|
+ | 加法运算符 | 计算两个值或表达式的和 | SELECT A+B |
- | 减法运算符 | 计算两个值或表达式的差 | SELECTA-B |
* | 乘法运算符 | 计算两个值或表达式的乘积 | SELECT A*B |
/或DIV | 除法运算符 | 计算两个值或表达式的商 | SELECT A/B 或者 SELECT A DIV B |
%或MOD | 求模(求余)运算符 | 计算两个值或表达式的余数 | SELECT A %B 或者 SELECT A MOD B |
案例:
- 数字计算
select 1+1;
- 计算员工年薪
select id,last_name,salary,salary*13 as sumsal
from s_emp;
2. 比较运算符
运算符 | 名称 | 作用 | 示例 |
---|---|---|---|
= | 等于运算符 | 判断两个值、字符串或表达式是否相等 | SELECT C FROM TABLE WHERE A=B |
<=> | 安全等于运算符 | 安全地判断两个值、字符串表达式是否相等 | SELECT C FROM TABLE WHERE A <=> B |
<>(!=) | 不等于运算符 | 判断两个值、字符串或表达式是否不相等 | SELECT C FROM TABLE WHERE A<>B 或者 SELECT C FROM TABLE WHEREA!=B |
< | 小于运算符 | 判断前面的值、字符串或表达式是否小于后面的值、字符串或表达式 | SELECT C FROM TABLE WHERE A<B |
<= | 小于等于运算符 | 判断前面的值、字符串或表达式是否小于等于后面的值、字符串或表达式 | SELECT C FROM TABLE WHERE A <= B |
> | 大于运算符 | 判断前面的值、字符串或表达式是否大于后面的值、字符串或表达式 | SELECT C FROM TABLE WHERE A>B |
>= | 大于等于运算符 | 判断前面的值、字符串或表达式是否大于等于后面的值、字符串或表达式 | SELECT C FROM TABLE WHERE A >= B |
IS NULL | 为空运算符 | 判断值、字符串或表达式是否为空 | SELECT B FROM TABLE WHERE A IS NULL |
ISNOTNULL | 不为空运算符 | 判断值、字符串或表达式是否不为空 | SELECT B FROM TABLE WHERE A IS NOT NULL |
LEAST | 最小值运算符 | 在多个值中返回最小值 | SELECT D FROM TABLE WHERE C LEAST(A, B) |
GREATEST | 最大值运算符 | 在多个值中返回最大值 | SELECT D FROM TABLE WHERE C GREATEST(A, B) |
BETWEEN AND | 两值之间的运算符 | 判断一个值是否在两个值之间 | SELECT D FROM TABLE WHERE C BETWEEN A AND B |
IN | 属于运算符 | 判断一个值是否为列表中的任意一个值 | SELECT D FROM TABLE WHERE C IN(A, B) |
NOT IN | 不属于运算符 | 判断一个值是否不是一个列表中的任意一个值 | SELECT D FROM TABLE WHERE C NOT IN (A, B) |
LIKE | 模糊匹配运算符 | 判断一个值是否符合模糊匹配规则 | SELECT C FROM TABLE WHERE A LIKE B |
REGEXP | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | SELECT C FROM TABLE WHERE A REGEXP B |
RLIKE | 正则表达式运算符 | 判断一个值是否符合正则表达式的规则 | SELECT C FROM TABLE WHERE A RLIKE B |
案例:
- 查询员工表名字为Patel的信息
select id,last_name,salary
from s_emp
where last_name='Patel';
- :查询薪水为null的员工信息
select id,last_name,salary
from s_emp
where salary <=> null;
或者
select id,last_name,salary
from s_emp
where salary is null;
null参与运算结果都为null;
<=>和=作用一样,唯一的区别‘<=>可以用来对NULL进行判断
- 查询员工表中编号大于等于22的员工信息
select id,last_name,salary
from s_emp
where id>=22;
- 查询员工表中有薪水的员工信息
select id,last_name,salary
from s_emp
where salary is not null;
- 找出一组数字中的最小值
select least(1,3,4,87,34);
- 找出员工薪水在850到1200之间的员工
select id,last_name,salary
from s_emp
where salary between 850 and 1200;
- 找出员工部门编号为41、42、43、44的员工信息
select id,last_name,dept_id
from s_emp
where dept_id in(41,42,43,44);
- 找出员工名字中含有a的员工信息
select id,last_name
from s_emp
where last_name like '%a%';
%表示0到任意字符
_表示1个字符占位
- 找出员工名字中第一个字符为下划线的员工信息
insert into s_emp(id,last_name,salary) values(27,'_briup',2000);
select id,last_name,salary
from s_emp
where last_name like '$_%' escape '$';
escape指定转义符
- 找出员工名字中第一 一个字符为s开头的员工信息
select id,last_name,salary
from s_emp
where last_name regexp '^S';
3. 逻辑运算符
运算符 | 作用 | 示例 |
---|---|---|
NOT或! | 逻辑非 | SELECT NOT A |
AND或&& | 逻辑与 | SELECT A AND B 或者 SELECTA&&B; |
OR或| | 逻辑或 | SELECT A OR B 或者 SELECT A || B |
XOR | 逻辑异或 | SELECT A XOR B |
案例:
- 找出员工薪水不在在850到1200之间的员工
select id,last_name,salary
from s_emp
where salary not between 850 and 1200;
或者
select id,last_name,salary
from s_emp
where salary not (salary>=850 and salary<=1200);
或者
select id,last_name,salary
from s_emp
where salary<850 or salary>1200;
OR可以和AND一起使用,AND的优先级高于OR。
- 找出员工部门编号不为为41、42、 43、44的员工信息
select id,last_name,dept_id
from s_emp
where dept_id not in(41,42,43,44);
- 员工在41或42号部门,但是薪水不能高于1000的员工信息,或员工不在在41或42号部门,但是薪水高于1000的员工信息
select id,last_name,salary,dept_id
from s_emp
where dept_id in(41,42) xor salary>1000;
逻辑异或(XOR)运算符是当给定的值中任意一个值为NULL时,则返回NULL;如果两个非NULL的值都是0或者都不等于0时,则返回0;如果一个值为0, 另一个值不为0时,则返回1
4.位运算符
运算符 | 作用 | 示例 |
---|---|---|
& | 按位与(位AND) | SELECT A& B |
| | 按位或(位OR) | SELECT A | B |
^ | 按位异或(位XOR) | SELECT A^B |
~ | 按位取反 | SELECT~ A |
>> | 按位右移 | SELECT A >>2 |
<< | 按位左移 | SELECT B << 2 |
三、单行函数
单行函数接受参数返回-个结果,只对一-行进行变换,每行返回一个结果,可以嵌套,参数可以是一列或一个值。
单行函数分为:
- 数值函数
- 字符串函数
- 日期函数
- 流程控制函数
- 加密与解密函数
- 获取MySQL信息函数
- 聚合函数
1. 数值函数
函数 | 用法 |
---|---|
ABS(x) | 返回x的绝对值 |
SIGN(X) | 返回X的符号。正数返回1,负数返回-1,0返回0 |
PI() | 返回圆周率的值 |
CEIL(x),CEILING(x) | 返回大于或等于某个值的最小整数 |
FLOOR(x) | 返回小于或等于某个值的最大整数 |
LEAST(e1,e2,e3 …) | 返回列表中的最小值 |
GREATEST(e1,e2,e3 …) | 返回列表中的最大值 |
MOD(x,y) | 返回x除以Y后的余数 |
RAND() | 返回0~1的随机值 |
RAND(x) | 返回0~1的随机值,其中x的值用作种子值,相同的X值会产生相同的随机数 |
ROUND(x) | 返回一个对x的值进行四舍五入后,最接近于X的整数 |
ROUND(x,y) | 返回一个对x的值进行四舍五入后最接近X的值,并保留到小数点后面Y位 |
TRUNCATE(x,y) | 返回数字x截断为y位小数的结果 |
SQRT(x) | 返回x的平方根。当x的值为负数时,返回NULL |
RADIANS(x) | 将角度转化为弧度,其中,参数x为角度值 |
DEGREES(x) | 将弧度转化为角度,其中,参数x为弧度值 |
SIN(x) | 返回x的正弦值,其中,参数x为弧度值 |
ASIN(x) | 返回x的反正弦值,即获取正弦为x的值。如果x的值不在-1到1之间,则返回NULL |
COS(x) | 返回x的余弦值,其中,参数x为弧度值 |
ACOS(x) | 返回x的反余弦值,即获取余弦为x的值。如果x的值不在-1到1之间,则返回NULL |
TAN(x) . | 返回x的正切值,其中,参数x为弧度值 |
ATAN(x) | 返回x的反正切值,即返回正切值为x的值 |
ATAN2(m,n) | 返回两个参数的反正切值 |
COT(x) | 返回x的余切值,其中,x为弧度值 |
POW(x,y),POWER(X,Y) | 返回x的y次方 |
EXP(X) | 返回e的x次方,其中e是一个常数,2.718281828459045 |
LN(X),LOG(X) | 返回以e为底的X的对数,当X<= 0时,返回的结果为NULL |
LOG10(X) | 返回以10为底的X的对数,当X<= 0时,返回的结果为NULL |
LOG2(X) | 返回以2为底的x的对数,当X<= 0时,返回NULL |
BIN(x) | 返回x的二进制编码 |
0CT(x) | 返回x的八进制编码 |
HEX(x) | 返回x的十六进制编码 |
案例:
- 返回-20的绝对值
select abs(-20);
- 返回32.56的四舍五入保留小数点后-2位
select round(32.56,-2);
- 将10转化为二-进制、八进制、十六进制
select bin(10),oct(10),hex(10);
2.字符函数
函数 | 用法 |
---|---|
ASCI(S) | 返回字符串S中的第一个字符的ASCII码值 |
CHAR_LENGTH(s) | 返回字符串s的字符数。作用与CHARACTER_ LENGTH(s)相同 |
LENGTH(s) | 返回字符串s的字节数,和字符集有关 |
CONCAT1.2…sn) | 连接1…n为一个字符串 |
CONCAT_ W(x,.,… ,sn) | 同CONCAT(51,2…)函数,但是每个字符串之间要加,上x |
INSERT(str, idx,len,replacestr) | 将字符串str从第idx位置开始,len个字符 长的子串替换为字符串replacestr |
REPLACE(str,a, b) | 用字符串b替换字符串str中所有出现的字符串a |
UPPER(s)或UCASE(s) | 将字符串s的所有字母转成大写字母 |
LOWER(s)或LCASE(s) | 将字符串s的所有字母转成小写字母 |
LEFT(str,n) | 返回字符串str最左边的n个字符 |
RIGHT[(str,n) | 返回字符串str最右边的n个字符 |
LPAD(str, len, pad) | 用字符串pad对str最左边进行填充,直到str的长度为len个字符 |
RPAD(str ,len, pad) | 用字符串pad对str最右边进行填充,直到str的长度为len个字符 |
LTRIM(s) | 去掉字符串s左侧的空格 |
RTRIM(s) | 去掉字符串s右侧的空格 |
TRIM(s) | 去掉字符串s开始与结尾的空格 |
TRIM(s1 FROM s) | 去掉字符串s开始与结尾的s1 |
TRIM(LEADING s1FROM s) | 去掉字符串s开始处的s1 |
TRIM(TRAILING s1FROM s) | 去掉字符串s结尾处的s1 |
REPEAT(str, n) | 返回str重复n次的结果 |
SPACE(n) | 返回n个空格 |
STRCMP(s1,s2) | 比较字符串s1 ,s2的ASCII码值的大小 |
SUBSTR(s,index,len) | 返回从字符串s的index位置其len个字符,作用与SUBSTRING(s,n,len)、MID(,n,len)相同 |
LOCATE(substr,str) | 返回字符串substr在字符串str中首次出现的位置,作用于POSITION(substr IN str)、INSTR(str,substr)相同。 未找到,返回0 |
ELT(m,s1,s2, …sn) | 返回指定位置的字符串,如果m=1,则返回s1,如果m=2,则返回s2,如果m=n,则返回sn |
FlELD(s,s1,s2, …sn) | 返回字符串s在字符串列表中第一次出现的位置 |
FIND_ IN_ SET(s1,s2) | 返回字符串s1在字符串s2中出现的位置。其中,字符串s2是一 个以逗号分隔的字符串 |
REVERSE(s) | 返回s反转后的字符串 |
NULLIF(value1,value2) | 比较两个子符串,如果value1与 value2相等,则返回NULL,否则返回value1 |
案例:
- 将hello转化为大写,将GOOD转化为小写
select upper('hello'),lower('GOOD');
- 将hello和world字符串拼接
select concat('hello','world');
- 将hello和world字符串基于#拼接
select concat_ws('#','hello','world');
- 将helloworld从第3个位置开始截取
select substr('helloworld',3);
- 比较两个字符串是否相等
select nullif('hello','hello'),nullif('hello','test');
3. 日期函数
函数 | 用法 |
---|---|
CURDATE(),CURRENT_DATE() | 返回当前日期,只包含年、月、日 |
CURTIME(),CURRENT_TIME() | 返回当前时间,只包含时、分、秒 |
NOW()/ SYSDATE()/ CURRENT_TIMESTAMP()/LOCALTIME()/ LOCALTIMESTAMP() | 返回当前系统日期和时间 |
UTC_DATE() | 返回UTC (世界标准时间)日期 |
UTC_TIME() | 返回UTC (世界标准时间)时间 |
UNIX_ TIMESTAMP() | 以UNIX时间戳的形式返回当前时间。SELECT UNIX_TIMESTAMP() - >1634348884 |
UNIX_TIMESTAMP(date) | 将时间date以UNIX时间戳的形式返回。 |
FROM_UNIXTIME(timestamp) | 将UNIX时间戳的时间转换为普通格式的时间 |
YEAR(date) / MONTH(date) / DAY(date) | 返回具体的日期值 |
HOUR(time) / MINUTE(time) /SECOND(time) | 返回具体的时间值 |
MONTHNAME(date) | 返回月份: January, … |
DAYNAME(date) | 返回星期几: MONDAY, TUESD… SUNDAY |
WEEKDAY(date) | 返回周几,注意,周1是0,周2是1,。。。周日是6 |
QUARTER(date) | 返回日期对应的季度,范围为1~4 |
WEEK(date),WEEKOFYEAR(date) | 返回一年中的第几周 |
DAYOFYEAR(date) | 返回日期是一年中的第几天 |
DAYOFMONTH(date) | 返回日期位于所在月份的第几天 |
DAYOFWEEK(date) | 返回周几,注意:周日是1,周一是2,。。。周六是7 |
EXTRACT(type FROM date) | 返回指定日期中特定的部分,type指定返回的值 |
TIME_TO_SEC(time) | 将time转化为秒并返回结果值。转化的公式为:小时*3600+分钟 *60+秒 |
SEC_TO_TIME(seconds) | 将seconds描述转化为包含小时、分钟和秒的时间 |
DATE_ADD(datetime, INTERVAL expr type),ADDDATE(date,INTERVAL expr type) | 返回与给定日期时间相差INTERVAL时间段的日期时间 |
DATE_SUB(date,INTERVAL expr type),SUBDATE(date,INTERVAL expr type) | 返回与date相差INTERVAL时间间隔的日期 |
ADDTIME(time1,time2) | 返回time1加上time2的时间。当time2为一个数字时,代表的是秒,可以为负数 |
SUBTIME(time1,time2) | 返回time1减去time2后的时间。当time2为一个数字时,代表的是秒,可以为负数 |
DATEDIFF(date1,date2) | 返回date1 - date2的日期间隔天数 |
TIMEDIFF(time1, time2) | 返回time1 - time2的时间间隔 |
FROM_DAYS(N) | 返回从0000年1月1日起,N天以后的日期 |
TO_DAYS(date) | 返回日期date距离0000年1月1日的天数 |
LAST_DAY(date) | 返回date所在月份的最后一天的日期 |
MAKEDATE(year,n) | 针对给定年份与所在年份中的天数返回-一个日期 |
MAKETIME(hour,minute,second) | 将给定的小时、分钟和秒组合成时间并返回 |
PERIOD_ADD(time,n) | 返回time加上n后的时间 |
DATE_FORMAT(date,fmt) | 按照字符串fmt格式化日期date值 |
TIME_FORMAT(time,fmt) | 按照字符串fmt格式化时间time值 |
GET_FORMAT(date_type,format_type) | 返回日期字符串的显示格式 |
STR_TO_DATE(str, fmt) | 按照字符串fmt对str进行解析,解析为一个日期 |
- extract中type:
type取值 | 含义 |
---|---|
MICROSECOND | 返回亳秒数 |
SECOND | 返回秒数 |
MINUTE | 返回分钟数 |
HOUR | 返回小时数 |
DAY | 返回天数 |
WEEK | 返回日期在一.年中的第几个星期 |
MONTH | 返回日期在- "年中的第几个月 |
QUARTER | 返回日期在一年中的第几个季度 |
YEAR | 返回日期的年份 |
SECOND_MICROSECOND | 返回秒和毫秒值 |
MINUTE_MICROSECOND | 返回分钟和毫秒值 |
MINUTE_SECOND | 返回分钟和秒值 |
HOUR_MICROSECOND | 返回小时和毫秒值 |
HOUR_SECOND | 返回小时和秒值 |
HOUR MINUTE | 返回小时和分钟值 |
DAY_MICROSECOND | 返回天和毫秒值 |
DAY_SECOND | 返回天和秒值 |
- expr type类型:
间隔类型 | 含义 |
---|---|
HOUR | 小时 |
MINUTE | 分钟 |
SECOND | 秒 |
YEAR | 年 |
MONTH | 月 |
DAY | 日 |
YEAR_MONTH | 年和月 |
DAY_HOUR | 日和小时 |
DAY_MINUTE | 日和分钟 |
DAY_SECOND | 日和秒 |
HOUR MINUTE | 小时和分钟 |
HOUR_SECOND | 小时和秒 |
MINUTE_SECOND | 分钟和秒 |
- fm格式:
格式符 | 说明 | 格式符 | 说明 |
---|---|---|---|
%Y | 4位数字表示年份 | %y | 表示两位数字表示年份 |
%M | 月名表示月份(Janau…) | %m | 两位数字表示月份(01,02,03。。。) |
%b | 缩写的月名(Jan,Feb, …) | %c | 数字表示月份(1,2,3…) |
%D | 英文后缀表示月中的天数(1st,2nd-,rd…) | %d | 两位数字表示月中的天数(01,02…) |
%e | 数字形式表示月中的天数( 1,2,3,4.5…) | ||
%H | 两位数字表示小数,24小时制 ( 01,02…) | %h和%I | 两位数字表示小时,12小时制 (01,02…) |
%k | 数字形式的小时,24小时制(1,2,3) | %l | 数字形式表示小时,12小时制 (1.2,3…) |
%i | 两位数字表示分钟(00,01,02) | %S和%s | 两位数字表示秒(00,01,02…) |
%W | 一周中的星期名称(Sunday…) | %a | 一周中的星期缩写(Sun,Mon.,Tues.,…) |
%w | 以数字表示周中的天数(0=Sunday,1=Monday…) | ||
%j | 以3位数字表示年中的天数(001,002… | %U | 以数字表示年中的第几周,(1,2,3。。)其中Sunday为周中第一天 |
%u | 以数字表示年中的第几周,(1,2,3。 。)其中Monday为周中第一天 | ||
%T | 24小时制 | %r | 12小时制 |
%p | AM或PM | %% | 表示% |
- date_type,format_type格式:
日期类型 | 格式化类型 | 返回的格式化字符串 |
---|---|---|
DATE | USA | %m.%d.%Y |
DATE | JIS | %Y-%m-%d |
DATE | ISO | %Y-%m-%d |
DATE | EUR | %d.%m.%Y |
DATE | INTERNAL | %Y%m%d |
TIME | USA | %h:%i:%s %p |
TIME | JIS | %H:%i:%s |
TIME | ISO | %H:%i:%s |
TIME | EUR | %H.%i.%s |
TIME | INTERNAL | %H%i%s |
DATETIME | USA | %Y-%m-%d %H.%i.%s |
DATETIME | JIS | %Y-%m-%d %H:%i:%s |
DATETIME | ISO | %Y-%m-%d %H:%i:%s |
DATETIME | EUR | %Y-%m-%d %H.%i.%s |
DATETIME | INTERNAL | %Y%m%d%H%i%s |
案例:
- 获取当前时间,包括年月日
select curdate(),current_date();
- 获取当前时间,包括小时、分钟、秒
select curtime(),current_time();
- 获取当前系统时间
select now(),sysdate();
- :获取世间标准日期和时间
select utc_date(),utc_time();
- 获取当前系统时间戳
select unix_timestamp();
- :将当前时间转化为时间戳
select unix_timestamp(now());
- 将时间戳转化为时间
select from_unixtime(1661789223);
- :返回当前是那年
select year(now());
- 返回当前时间的年月
select extract(year_month from now());
- :将当前时间转化为秒
select time_to_sec(now());
- 将秒转化为时间
select sec_to_time(58741);
- :获取当前时间的下一天
select adddate(now(),interval 1 day);
- 返回指定当前月份的最后一天
select last_day(now());
- 将当前时间转化为年-月-日.
select date_format(now(),'%Y-%m-%d');
- 获取美国时间字符串格式
select get_format(date,'usa');
- 将当前时间转化为USA格式的字符串
select date_format(now(),get_format(date,'usa'));
- 将字符串转化为时间
select str_to_date('22/02/2022','%d/%m/%Y');
- 返回202203的后3个月
select period_add('2203',3);
4. 流程控制
- 员工薪水大于1500,显示蓝领,否则白领
select id,last_name,salary,if(salary>2000,'蓝领','白领')
from s_emp;
- 显示员工年薪薪水为nul的用0计算
select id,last_name,salary,ifnull(salary,0)*13 sum_sal
from s_emp;
- 员工薪水大于2500,显示金领,1500-2500蓝领,否则白领
select id,last_name,salary,
case when salary>2500 then '金领' when salary<=2500 and salary>=1500 then '蓝领' else
'白领' end as "rank"
from s_emp;
- 按照员工编号指定3组员工
select id,last_name,salary,
case id%3 when 1 then '1group' when 2 then '2group' else '3group' end as "group"
from s_emp;
5. 加密函数
加密与解密函数主要用于对数据库中的数据进行加密和解密处理。
函数 | 用法 |
---|---|
AES_ENCRYPT(str,key_str) | 使用官方AES进行加密,数据加密(高级加密标准)算法 |
AES_DECRYPT(crypt_str,key_str) | 使用官方AES进行解密 |
ENCRYPT(str[,strkey]) | 使用Unix crypt() 系统调用加密str |
MD5(str) | 返回字符串str的md5加密后的值,也是一种加密方式。若参数为NULL,则会返回NULL |
SHA(str) | 从原明文密码str计算并返回加密后的密码字符串,当参数为NULL时,返回NULL。SHA加密算法比MD5更加安全。 |
案例:
- 使用AES_ENCRYPT基于hello对root加密, 使用AES_DECRYPT基于hello解密
select AES_ENCRYPT('root','hello') en,AES_DECRYPT(AES_ENCRYPT('root','hello'),'hello')de;
- 使用ENCRYPT对root进入加密
select ENCRYPT('root') en;
- :使用MD5对root加密
select MD5('root');
- 使用SHA对root加密
select SHA('root');
6. 信息函数
- 查看数据库的版本
select version();
- 查看数据库的连接数
select connection_id();
- 查看当前操作的数据库
select database(),schema();
- 查询连接数据库的用户
select user(),current_user(),system_user(),session_user();
- 返回字符集
select charset('helloworld');
- 返回字符的比较规则
select collation('helloworld');
7. 窗口函数
窗口函数(Window functions)也叫做开窗函数0LAP,输入值是SELECT查询语句结果集中的一行或多行窗口中获取,select语句中具有over字句,则其是窗口函数。group by子句组合的聚合函数会隐藏正在聚合的各个行,最终输出一行,窗口函数聚合之后可以访问当中的各个行,并且可以将这些行中的某些属性添加到结果集中。
窗口函数总体上可以分为序号函数、分布函数、前后函数、首尾函数和其他函数。
函数分类 | 函数 | 函数说明 |
---|---|---|
序号函数 | ROW_NUMBER() | 顺序排序 |
序号函数 | RANK() | 并列排序, 会跳过重复的序号,比如序号为1、1.、3 |
序号函数 | DENSE_ RANK() | 并列排序,不会跳过重复的序号,比如序号为1、1、2 |
分布函数 | PERCENT_RANK() | 等级值百分比 |
分布函数 | CUME_DIST() | 累积分布值 |
前后函数 | LAG(expr, n) | 返回当前行的前n行的expr的值 |
前后函数 | LEAD(expr, n) | 返回当前行的后n行的expr的值 |
首尾函数 | FIRST_VALUE(expr) | 返回第一个expr的值 |
首尾函数 | LAST_VALUE(expr) | 返回最后一个expr的值 |
其他函数 | NTH_VALUE(expr, n) | 返回第n个expr的值 |
其他函数 | NTILE(n) | 将分区中的有序数据分为n个桶,记录桶编号 |
语法:
函数 over ([partition by 字段名 order by 字段名 asc |desc] )
函数 over 窗口名 … WINDOW 窗口名 AS ([partition by 字段名 order by 字段名 asc|desc] )
说明:
OVER
关键字指定函数窗口的范围。
如果省略后面括号中的内容,则窗口会包含满足WHERE条件的所有记录,窗口函数会基于所有满足WHERE条件的记录进行计算。窗口名
:为窗口设置一个别名,用来标识窗口。PARTITION BY
子句:指定窗口函数按照哪些字段进行分组。分组后,窗口函数可以在每个分组中分别执行。ORDER BY
子句:指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
案例:
- 查询员工信息及平均薪水
select id,last_name,salary,dept_id,avg(salary) over() as avgSal
from s_emp;
- 查询员工信息,及员工所在部门]的平均薪水
select id,last_name,salary,dept_id,avg(salary) over(partition by dept_id) as avgSal
from s_emp;
或者
select id,last_name,salary,dept_id,avg(salary) over w as avgSal
from s_emp window w as(partition by dept_id);
- 查询员工信息,基于编号排序,累加计算金额
select id,last_name,salary,dept_id,sum(salary) over(order by id) as sumSal
from s_emp;
- 查询员工信息,基于部门编号分组,组内基于名字排序,累计计算金额
select id,last_name,salary,dept_id,sum(salary) over(partition by dept_id order by
last_name) as sumSal
from s_emp
order by dept_id;
或者
select id,last_name,salary,dept_id,sum(salary) over w as sumSal
from s_emp window w as(partition by dept_id order by last_name)
order by dept_id ;
- 查询员工信息,基于部门分组,按照薪水排序,并顺序标出编号
insert into s_emp(id,last_name,salary,dept_id) values(28,'lisi',1200,31),
(29,'zhansan',1500,31);
select id,last_name,salary,dept_id,row_number() over(partition by dept_id order by
salary) as sumSal
from s_emp;
或者
select id,last_name,salary,dept_id,row_number() over w as sumSal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,并顺序标出编号,并且跳过重复的序号
select id,last_name,salary,dept_id,rank() over(partition by dept_id order by salary)
as sumSal
from s_emp;
或者
select id,last_name,salary,dept_id,rank() over w as sumSal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,并顺序标出编号,相同值并列显示,并且不跳过重复的序号
select id,last_name,salary,dept_id,dense_rank() over(partition by dept_id order by
salary) as sumSal
from s_emp;
或者
select id,last_name,salary,dept_id,dense_rank() over w as sumSal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门]分组,按照薪水排序,计算出等级值百分
select id,last_name,salary,dept_id,rank() over(partition by dept_id order by salary)
as rank_num,percent_rank() over(partition by dept_id order by salary) as pr_num
from s_emp;
或者
select id,last_name,salary,dept_id,rank() over w as rank_num,percent_rank() over w as
pr_num
from s_emp window w as(partition by dept_id order by salary);
percent_ rank()计算公式: ( rank- 1)/( rows-1)
rank的值为使用RANK()函数产生的序号,rows的值为 当前窗口的总记录数
- 查询员工信息,基于部门分组,按照薪水排序,查询小于等于当前薪水的比例
select id,last_name,salary,dept_id,cume_dist() over(partition by dept_id order by
salary) as pr_num
from s_emp;
或者
select id,last_name,salary,dept_id,cume_dist() over wd as pr_num
from s_emp window wd as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,当前行同时显示,上- -行的薪水
select id,last_name,salary,dept_id,lag(salary,1) over(partition by dept_id order by
salary) as pr_sal
from s_emp;
或者
select id,last_name,salary,dept_id,lag(salary,1) over w as pr_sal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,当前行同时显示下一-行的薪水
select id,last_name,salary,dept_id,lead(salary,1) over(partition by dept_id order by
salary) as la_sal
from s_emp;
或者
select id,last_name,salary,dept_id,lead(salary,1) over w as la_sal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,当前行显示每组第一行的薪水
select id,last_name,salary,dept_id,first_value(salary) over(partition by dept_id order
by salary) as first_sal
from s_emp;
或者
select id,last_name,salary,dept_id,first_value(salary) over w as first_sal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,当前行显示最后一个员工薪水
select id,last_name,salary,dept_id,last_value(salary) over(partition by dept_id order
by salary) as last_sal
from s_emp;
或者
select id,last_name,salary,dept_id,last_value(salary) over w as last_sal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,并显示排名第二和第三的薪水
select id,last_name,salary,dept_id,nth_value(salary,2) over(partition by dept_id order
by salary) as sec_sal,nth_value(salary,3) over(partition by dept_id order by salary)
as third_sal
from s_emp;
或者
select id,last_name,salary,dept_id,nth_value(salary,2) over w as
sec_sal,nth_value(salary,3) over w as third_sal
from s_emp window w as(partition by dept_id order by salary);
- 查询员工信息,基于部门分组,按照薪水排序,将每一组数据分成3个桶数据
select id,last_name,salary,dept_id,ntile(3) over(partition by dept_id order by salary)
as but_data
from s_emp;
或者
select id,last_name,salary,dept_id,ntile(3) over w as but_data
from s_emp window w as(partition by dept_id order by salary);