MySQL的基础学习（三）

一、数据查询

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);