SQL:DQL数据查询语言以及系统函数(oracle)

SQL

Structured Query Language，结构化查询语言, 是一种用于管理和操作关系数据库的标准编程语言。

sql的分类

DQL（Data Query Language）：数据查询语言

DDL（Data Definition Language）：数据定义语言

DML（Data Manipulation Language）：数据操纵语言

DCL（Data Control Language）：数据控制语言

DTL（Data Transaction Language）：数据事务语言

TCL（Transaction Control Language）:事务控制语言

DQL数据查询语言

简单查询

SELECT 内容  FROM 数据来源;

条件查询

 SELECT 内容 FROM 数据来源 WHERE 条件表达式 ;

比较运算符：`<`、`>`、`<=`、`>=`、`=`、`!= <>`

逻辑运算符：AND 、OR、 NOT

模糊查询

NOT LIKE

分组查询

Group BY

条件过滤

where

组内查询

having

去重

distinct

排序

ORDER BY : ASC 升序(默认)、DESC 降序

范围查询

BETWEEN ... AND
IN

NOT IN
ANY
ALL

集合操作符

并集：UNION、UNION ALL
交集：INTERSECT
差集：MINUS

子查询

select 里面的select

子查询通常被嵌套在主查询中，可以出现在 SELECT、FROM、WHERE 或 HAVING 子句中。子查询的结果可以是一个值、一行数据或一个表

联合查询/多表查询

交叉连接 cross join >>笛卡尔积
内连接 join ... on >>只保留匹配上的数据
外连接 ( 左外连接 left join... on , 右外连接 right join ... on , 全外连接 full join )
自连接 ( 通过取别名的方式让表与自身进行连接, 自连接本质上属于内连接的一种,也可以作为其他连接 )
自然连接 natural join (内连接的一种,同字段会去重)

系统函数

数值函数

abs (数值)   绝对值函数
        CEIL(数值) 向上取整函数
        FLOOR(数值) 向下取整函数
        ROUND(数值, 小数位数) 四舍五入函数
        MOD(数值1,数值2) 取余函数(数值1/数值2的余数)
        POWER(数值1,数值2) 幂函数(数值1的数值2次幂)
        TRUNC(数值, 小数位数) 截断函数(将数值截断到指定的小数位数，不进行四舍五入)

字符函数

substr(string, start, length) 从字符串中提取子字符串
        INSTR(string, substring, start_position, occurrence)返回子字符串在字符串中第一次出现的位置，如果没有找到则返回 0

        UPPER(string) 将字符串转换为大写。
        lower(string) 将字符串转换为小写。
        initcap(string) 将字符串的每个单词的首字母大写，其余字母小写
        CONCAT(string1, string2) 拼接2个字符串, 拼接多个使用拼接符( 比如 string1|| string2||string3 )
        length(string) 字符串的长度（字符数）

去除空格函数 trim

LTRIM(string, character) 从字符串的左侧删除指定的字符（默认为空格）,即ltrim(string)
RTRIM(string, character) 从字符串的右侧删除指定的字符（默认为空格）即rtrim(string)

trim(string)从字符串的两端删除空格

完整: TRIM([LEADING | TRAILING | BOTH] character FROM string)

参数

填充函数 lpad 和rpad

LPAD(string, length, character) 左填充函数

在字符串的左侧填充指定的字符，直到达到指定的长度

RPAD(string, length, character) 右填充函数

在字符串的右侧填充指定的字符，直到达到指定的长度

替换函数 TRANSLATE

TRANSLATE(string, from_string, to_string)

将字符串中的某些字符替换为其他字符，一对一替换

替换函数 REPLACE

REPLACE(string, substring, replace_string)

将字符串中的某个子字符串替换为另一个字符串,整体替换

日期函数

        sysdate 返回系统的当前日期和时间
        MONTHS_BETWEEN(date1, date2) 计算两个日期之间的月数差
        LAST_DAY(date) 返回指定日期所在月份的最后一天
        NEXT_DAY(date, day_of_week) 返回指定日期之后的第一个指定星期几的日期

ADD_MONTHS(date, num_months) 在指定日期上加上指定的月数
TRUNC(date, format) 截断日期到指定的精度（如年、月、日等）

转换函数

to_char

TO_CHAR(value, format)

to_date

TO_DATE(string, format)

to_number

TO_NUMBER(string, format)

示例: 去除$符号

聚合函数

sum 计算数值列的总和

max 返回某列中的最大值
min 返回某列中的最小值
avg 计算数值列的平均值
count 计算行数(非空值)的数量
listagg 将多行数据连接成一个字符串（Oracle 11g及以上版本）

LISTAGG(column_name, delimiter) WITHIN GROUP (ORDER BY column_name)

wm_concat 将多行数据连接成一个字符串（Oracle 10g及以下版本）

通用函数

NVL(value1, value2)

NVL2(value1, value2, value3)

case when 条件逻辑

DECODE(value, if1, then1, if2, then2, ..., else_value)

主要用于等值比较

COALESCE(value1, value2, ..., valueN)

返回参数列表中第一个非 NULL 的值。如果所有参数都为 NULL，则返回 NULL

GREATEST(value1, value2, ..., valueN)

返回参数列表中的最大值

LEAST(value1, value2, ..., valueN)

返回参数列表中的最小值

开窗函数

聚合开窗

sum() over

max() over

min() over

avg() over

count() over

listagg() over

wm_concat()over

位移开窗函数

lead() over

用于获取当前行之后的一行或多行数据

lad() over

用于获取当前行之前的一行或多行数据