SQL函数整理笔记

自学SQL函数整理笔记

UNION,INTERSECT,MINUS,EXCEPT

UNION union 去除重复行（两个表中除了重复的行组成的新表）

UNION ALL union all 所有行（两个表中的所有行组合成的新表）

INTERSECT intersect 相交（两个表中相同的东西组合成的新表）

MINUS minus 相减 （两个表中一个表减去另外一个表组合成的新表）

以上都用法都是用在两个SELECT语句之间。

IN

SELECT * FROM EMPLOYEE WHERE NAME IN (A,B,C);

表示的是名字为A，名字为B，名字为C的行所组成的新表。

BETWEEN AND（在…与…之间）

A > 60 AND B <90 可以写成 BETWEEN 60 AND 90（但是它包括60和90）

||

||表示连接符 比如：SELECT NAME || , ||AGE HSW FROM STUDENTS；就会得到一个新表

HSW（别名）

XQ ， 22

COUNT SUM AVG MAX MIN（聚合函数）

汇总函数（COUNT SUM AVG MAX MIN）不能再WHERE语句中使用 MAX、MIN可以处理字符也可以处理字符（A——>Z）其它汇总函数只能处理数字

VARIANCE variance（方差） STDDEV stddev （标准差）

以上两用法和汇总函数一样。

ADD_MONTHS（添加x个月，x可为负数）

ADD_MONTHS add_months 函数是将给定的日期添加一个月

例如：ADD_MONTHS(ENDDATE,2) 就是将ENDDATE的日期再往后推迟两个月

SELECT ENDDATE, ADD_MONTHS(ENDDATE,2) FROM 表名；

LAST_DAY（本月最后一天）

LAST_DAY last_day函数表示指定月的最后一天

SELECT ENDDSTE, LASE_DAY(ENDDTSE) FROM 表名；

DISTINCT

DISTINCT 去重

SELECT DISTINCT 字段名 FROM 表名；

MONTHS_BETWEEN（两个账期之间的月份）

MONTHS_BETWEEN 在指定的两个日期间查找有多少个月

SELECT 日期1，日期2，MONTHS_BETWEEN(日期2,日期1) FROM 表名

给的月份循序是敏感的，位置写反容易出现负数

NEW_TIME（调整所在时间区）

NEW_TIME 调整到你所在的时间区

SELECT ENDDATE, NEW_TIME(ENDDATE,'EDT','PDT') FROM 表名

NEXT_DAY

NEXT_DAY 指定的日期在最近的（同一个星期或者上一个星期中）星期几是几号

SELECT 日期,NEXT_DAY(日期,'星期几') FROM 表名

SYSDATE （返回系统日期和时间）

SYSDATE 返回系统的日期和时间

SELECT DISTINCT SYSDATE FROM 表名

今天启动了多少项目：

SELECT DISTINCT SYSDATE FROM 表名 WHERE STARTDATE>SYSDATE;

ABS（返回绝对值）

ABS 返回给定数据的绝对值

SELECT ABS(XXX) FROM 表名

CEIL（返回最大整数）

CEIL表示给定一个参数，返回最大整数

SELECT 参数, CEIL(参数) FROM 表名

FLOOR（返回最小整数）

FLOOR表示给定一个参数，返回最小整数

SELECT 参数，FLOOR(参数) FROM 表名

EXP,LN（相当于数学中LOG函数或ln）

EXP 以E为底数的幂值 LN 自然对数（ABS(参数)） LOG(B,10)

SELECT 参数，EXP(参数) FROM 表名

MOD（求余）

MOD取模函数

SELECT A, B, MOD(A, B) FROM 表名

SIGN（判断参数为正数，负数，0）

SIGN 如果参数为正数，返回1；为负数，返回-1；为0返回0 WHERE语句中也可以使用

SELECT A， SIGN(A) FROM 表名

SQRT （求平方根）

SQRT 平方根，不能为负数，联合绝对值ABS使用

CHR （根据ASCLL码返回字母）

CHR 给定参数数字对应ASCLL码返回字母

CONCAT , ||（拼接符）

CONCAT 将两个字符串连接起来 和 ||的功能差不多，用多个词来作为别名时，需要用到引号

SELECT NAME, AGE, CONCAT(NAME, AGE) "NAME AND AGE" FROM 表名

INITCAP（参数首字母大写，其它小写）

INITCAP函数是将参数的第一个字母大写，其它字母转为小写。

SELECT NAME, INITCAP(NAME) FROM 表名

LOWER,UPPER（字母大小写转换）

LOWER 是将字母全部转为小写

UPPER 是将字母全部转为大写

SELECT NAME, LOWER(NAME), UPPER(NAME) FROM 表名 

LPAD,RPAD（往左往右扩充长度）

LPAD是将字符串往左扩充宽度，原来是15个字符长度，想要再扩充5个字符长度就得写成20。

RPAD是将字符串往右扩充宽度（表示要扩充得字符，扩充得长度，扩充得长度要显示的东西默认空格）

SELECT NAME,LPAD(LASTNAME,20,'*') FROM 表名

LTRIM,RTRIM（减去前后默认空格）

LTRIM是减去左字符默认减去空格

RTRIM是减去右字符默认减去空格

SELECT NAME,LTRIM(NAME,'XXX') FROM 表名

REPLACE（替换字符）

REPLACE表示搜索内容，搜索字符串，替换字符串（需要三个参数，第三个默认为NULL，只搜索不替换，但是搜索字符串会不见）

SELECT NAME，REPLACE(NAME, XQ, HSW) RENAME FROM 表名

SUBSTR（截取字符串）

SUBSTR表示输出字符串（需要三个参数，第一个表示目标字符串，第二个表示为参数起始位置（从1开始算，如果为负数换位绝对值从末尾开始算），第三个表示输出参数的长度（没有第三个将会输出尾部））

SELECT NAME, SUBSTR(NAME, 2, 3) RENMAE FROM 表名

数据很长的话需要用-或者，等来划分可以这样写，比如SSN列中的数据300541117

SELECT  SSN, SUBSTR(SSN,1,3)||'-'||SUBSTR(SSN,4,2)||'-'||SUBSTR(SSN,6,4) FROM 表名

TRANSLATE（替换字符，大小写敏感）

TRANSLATE有三个参数，目标字符串，源字符串、目的字符串。如果目标字符串和源字符串都有的字符将会被目的字符串所替代（大小写敏感）。

LENGTH（字符长度）

LENGTH将返回指定字符串的长度（但是需要把空格字符去掉用RTRIM）

SELECT NAME, LENGTH(RTRIM(NAME)) FROM 表名

TO_CHAR,TO_NUMBER（强转字符类型）

TO_CHAR是将数字类型转换为字符类型

TO_NUMBER是将字符类型转换为数字类型

SELECT AGE, TO_CHAR(AGE)  AGE1 FROM 表名

GREATEST,LEAST（返回值多个值中最大或最小）

GREATEST返回表达式中最大的（字符返回A——>Z中第一个字母最大的）

LEAST返回表达式中最小的

SELECT GREATEST('10','30','40','5','345') FROM 表名

USER （返回使用数据库名）

USER 返回使用数据库的名字

`SELECT USER FROM 表名`

AS（别名指定）

AS 指定别名，可有可无

STARTING WITH字句和LIKE(‘%xxx’)差不多

SELECT NAME, AGE FROM 表名 WHERE NAME STARTING WITH('Xq')

ORDER BY子句 排序 默认升序 降序DESC（可以使用多个字段）

SELECT * FROM 表名 ORDER BY 学号,XXX  升序/降序

可以前一个升序后一个降序

SELECT * FROM 表名 ORDER BY 学号 ASC, 姓名 DESC;

知道需要按第几列进行排序的时候，并知道是第一列，比如：知道第一列

SELECT * FROM 表名 ORDER BY 1；

GROUP BY子句 对数据进行分组 比如和汇总函数一起使用

SELECT SUM(XXX) COUNT(某列) FROM 表名 GROUP BY 某列

在对多个字段进行分组时，SELECT语句中不能出现除分组以外的字段名，比如：

SELECT NAME，AGE, MAX(AGE) FROM 表名 GROUP BY AGE;

(会报错，因为SELECT语句中出现了NAME，而GROUP BY 子句中没有NAME)

ROW_NUMBER() OVER() （排序或者取唯一使用）

SELECT * FROM(

​	SELECT *,ROW_NUMBER() OVER(PARTITION BY USER_ID ORDER BY TIME_ID DESC) RN FROM A

)A 

WHERE RN = 1

COUNT(DISTINCT A) OVER() ,MAX(A) OVER() ,MIN(A) OVER() ,SUM(A) OVER() (聚合函数无需GROUP BY的使用)

SELECT * FROM(

​	SELECT *,COUNT(A) OVER(PARTITION BY USER_ID ORDER BY TIME_ID DESC) RN FROM B

)A

WHERE RN>=50

TIMESTAMPDIFF()（时间相减函数）

TIMESTAMPDIFF('DAY',TO_DATE(A),TO_DATE(B)) HJX_DAYS/*时间B-时间A*/

未整理完~…