sql 函数的使用

鲨鱼辣椒ii

已于 2024-03-26 09:46:34 修改

阅读量171

点赞数

分类专栏： sql 文章标签： sql 学习数据库

于 2023-11-06 14:20:08 首次发布

本文链接：https://blog.csdn.net/qq_43826626/article/details/134239169

版权

sql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql

postsql

sql server

mysql

isnull( body ,null) 查询内容为空，输出后边的信息
IFNULL(expression_1,expression_2); expression_1为null输出expression_2
mod(col1,col2) col1对col2的余数 eg mod(12,2) 12对2取余数
group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])；将group by产生的同一个分组中的值连接起来，返回一个字符串结果
eg select id,group_concat(distinct price order by price desc separator ';') from goods group by id;
REGEXP_LIKE(expr, pat[, match_type]) 正则表达式匹配
介绍几个常见的正则表达式模式：

^：匹配字符串的开始位置。例如，^hello会匹配以"hello"开头的字符串。

$：匹配字符串的结束位置。例如，world$会匹配以"world"结尾的字符串。

.：匹配除换行符以外的任意字符。例如，a.b会匹配"a+b"、"a@b"等。

*：匹配前面的模式零次或多次。例如，a*b会匹配"b"、"ab"、"aab"等。

+：匹配前面的模式一次或多次。例如，a+b会匹配"ab"、"aab"、"aaab"等。

?：匹配前面的模式零次或一次。例如，a?b会匹配"b"、"ab"。

[]：定义字符集合。例如，[abc]会匹配"a"、"b"、"c"中的任意一个字符。

[^]：否定字符集合。例如，[^abc]会匹配除了"a"、"b"、"c"之外的任意字符。

\d：匹配数字。等价于[0-9]。

\w：匹配字母、数字或下划线。等价于[A-Za-z0-9_]。

\s：匹配空白字符，包括空格、制表符、换行符等。

\b：匹配单词边界。例如，\btest\b会匹配单独的单词"test"。
eg select email from user where regexp_like(email, '^[a-z0-9]+[a-z0-9._-]+@[a-z0-9.-]+\\.[a-z]{2,4}$');匹配正确邮箱
LENGTH()和CHAR_LENGTH()的区别 length()计算方式字节；utf-8编码一个汉字3字节一个字母或数据一字节 char_length() 计算方式字符
substring(str,start,len) substring(str,start) str字符串,start开始下标,len截取长度前者从start截取全部后者指定长度，start正数从左往右截取，为负数从右往左截取
substring_index(str,selectstr,start) 从start开始在str中查询selectstr下标内容进行截取之后的内容
start控制返回str按selectstr分割后左侧第start个开始左侧字符串
start正数从左往右截取，为负数从右往左截取
eg select substring_index('www.baidu.com','.',1) -> www
select substring_index('www.baidu.com','.',2) -> www.baidu
select substring_index('www.baidu.com','.',-1) -> com
replace(str,'替换部分','要替换的内容')
trim('去掉的内容' from fiel) fiel列名会在左右两边去掉定义的内容(其中定义的部分都会去掉)
日期函数 year(fiel) month(fiel)
eg: year(date) = '2021' month('date') = '08' 2021-08
limit num1 offset num2 offset表示偏移量，limit表示取出的数量
eg: limit 1 offse 2 取出第三条数据
insert ignore 插入时，重复数据忽略
insert into table(fiel1,fiel2)
select fiel1,fiel2 from table1
插入时可以select查询语句进行插入
创建索引的两种方式区别
使用alter创建
主键索引 ALTER TABLE 表名 ADD Primary key (col);
唯一索引 ALTER TABLE 表名 ADD unique 索引名 (col1, col2, ...col3);
普通索引 Alter table 表名 ADD index 索引名 (col1, col2, ...,);
全文索引 ALTER TABLE 表名 ADD Fulltext 索引名 (col)
多列索引 ALTER TABLE 表名 ADD UNIQUE (col1, col2, ..., )(多列是唯一的)
使用create
普通索引 create index 索引名 on 表名(col1, col2, ..., )
唯一索引 create unique index 索引名 on 表名(col1, col2, ..., )
create不能创建主键索引
alter可以创建多个，分割
触发器创建可以用于学号增加前几位或者记录
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt

trigger_name：标识触发器名称，用户自行指定；

trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；

trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；

tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾。(在程序体中new代表新row数据 old代表老数据只读)

eg:create tigger log
after insert on employee
for each row
begin
insert into log values(new.id,new.user)
end
delete语句在mysql中不允许在删除时同时查询数据，可以对查询结果重名名
eg:delete ffrom user where id not in (
select * from (select distinct min(tt.id) over(partition by no) from user)aa)
length(field) 获取字段长度
可以配合replace获取替换后的长度length(replace(file,',',''))
可以获取某某个字符长度例如,长度 eg:length(string) - length(replace(string,',',''));
last_value()获取最后一个数据 first_value()获取第一条数据用法一致
eg: last_value(file1) over(partition by file1 order by date)
日期格式化显示 select date_format(now(),'%Y-%m-%d');
日期可以自定义组合使用，替换操作符即可
%S, %s 两位数字形式的秒（ 00,01, …, 59）
%I, %i 两位数字形式的分（ 00,01, …, 59）
%H 两位数字形式的小时，24 小时（00,01, …, 23）
%h 两位数字形式的小时，12 小时（01,02, …, 12）
%k 数字形式的小时，24 小时（0,1, …, 23）
%l 数字形式的小时，12 小时（1, 2, …, 12）
%T 24 小时的时间形式（hh:mm:ss）
%r 12 小时的时间形式（hh:mm:ss AM 或hh:mm:ss PM）
%p AM或PM
%W 一周中每一天的名称（Sunday, Monday, …, Saturday）
%a 一周中每一天名称的缩写（Sun, Mon, …, Sat）
%d 两位数字表示月中的天数（00, 01,…, 31）
%e 数字形式表示月中的天数（1, 2， …, 31）
%D 英文后缀表示月中的天数（1st, 2nd, 3rd,…）
%w 以数字形式表示周中的天数（ 0 = Sunday, 1=Monday, …, 6=Saturday）
%j 以三位数字表示年中的天数（ 001, 002, …, 366）
%U 周（0, 1, 52），其中Sunday 为周中的第一天
%u 周（0, 1, 52），其中Monday 为周中的第一天
%M 月名（January, February, …, December）
%b 缩写的月名（ January, February,…, December）
%m 两位数字表示的月份（01, 02, …, 12）
%c 数字表示的月份（1, 2, …, 12）
%Y 四位数字表示的年份
%y 两位数字表示的年份
%% 直接值“%”

postsql

如果var1和var2相等则返回null，如果不相等则返回var1
nullif(var1,var2)
第一个值为空，则为第二个值或者使用 case when then
coalesce(var1,var2) , case col is not null when col then 'empty' end
eg:select *,coalesce(col,'empty') from table
eg:select *,(case col is not null when col then 'empty' end) as name from table
在postgres中::相当于cast，可以使用::转换类型，cast用法和sqlserver中类似
eg: cast(fiel as decimal) -> val::decimal
函数() over(partition by [分组名称] order by [排序名称] rows[窗口滑动的数据范围] [操作名称])
数据范围：

取当前行和前五行：ROWS between 5 preceding and current row --共6行
取当前行和后五行：ROWS between current row and 5 following --共6行
取前五行和后五行：ROWS between 5 preceding and 5 folowing --共11行

操作名称：
当前行 - current row
之前的行 - preceding
之后的行 - following
无界限 - unbounded
表示从前面的起点 - unbounded preceding
表示到后面的终点 - unbounded following
eg:sum(col1) over(order by col2 rows 6 preceding) 前6行
row_number() over([partitionby col1] [order by col2] [desc]) 排序编号 partitionby分组 order by排序
COUNT(*) OVER(PARTITION BY col1,clo2,...... ) count()也可以进行分组后统计数量
count(distinct col1) 可以筛选数据统计数量
eg: select name,row_number() over(order by col) from table
eg:select *,row_number() over(partition by col1 order by col2) as rownum from table
对数据进行排名
rank() over(partition by col1 order by col2 desc) 序号重复会断开和 row_number使用类似，不同的是重复数据是一样编号
dense_rank() over(order by col1 desc) 序号连续的
first_value() over(order by col1 desc) 函数从当前关联窗口中第一个数据
eg:SELECT *, first_value(grade) OVER ( PARTITION BY subject ORDER BY grade DESC RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) first_grade FROM student_grade;

range是滑动行数， UNBOUNDED PRECEDING，UNBOUNDED FOLLOWING指开始到最后
distinct 筛选重复数据
lag(col,index,[isnullToObject]) over(partition by col1 order by col2)根据col字段查询index后的数据进行填充(index可为1,2,3或-1反向)，如果为空赋值 isnullToObject自定义值
lead()用法类似，和lag用法相反
两个字段的判断注意字段顺序和查询内字段顺序保持一致示例
日期处理 eg: 增加一天 mydata + interger '1' 或者 '1 day'
with name as 创建一个临时表用查询和正式表一样
eg:

with mytable as (

select * from member m

left join users u

on u.users_id = m.user_id

) select * from mytable
count()可以加条件 count(case when col != 'completed' then col else null) 统计时给NULL不计算
sum()中可以加条件 sum (case when col != 'completed' then 0 else 1 end)
sum()也可以做减法配合case
eg:sum(case when opera = 'buy' then price else -price end)
sum() over([partitionby col1] [order by col2] [desc])可以也使用开窗函数运用
sum(file1) over(partitionby col1) 根据col1分组计算汇总
sum(file1) over(order by col2) 根据col2顺序计算汇总,按照顺序计算两个数据总和
如果有orderby排序就是累加，没有就是全部的和，avg等也是同样的应用
eg:select sum(score) over(partition by job order by id) as sumAfter,*
from grade g ;
select sum(score) over(partition by job ) as sum,*
from grade g ;
union [ distinct || all ] union和union all 联合多表内容区别后者不会筛选重复数据
exists,not exists用法和in,not in用法类似条件中结果大于主表 exists效率更好需要注意使用exists时需要查询时与主表有关联
eg:select name from tab1 s where not exists (SELECT id from tab2 c inner join tab3 o on c.id= o.idand c.name = 'RED' where s.id= o.id);
字符处理 SUBSTRing( string , 1, 7) as name
字符和日期转换 to_date('dateString', 'formatString')
eg: to_char(date,'YYYY-MM-DD')
字段存储Json可以用 json_extract_path(val, 'name') val是json name是对应json字段名
如果提示没有匹配指定名称和参数类型的操作符. 您也许需要增加明确的类型转换.
字段类型不是json可以转换 json_extract_path(val::json, 'name')
(val::json)->>'name' 和上边方法效果一样
eg:

jsonb_build_object(val) 查询结果可以去掉""号
UPDATE test SET val = jsonb_set(val, '{CodeFreezeStatus}', '"NewValue"');可以使用这个反向修改json
可能还需组合使用 jsonb_set、jsonb_insert 和 jsonb_delete 函数来实现
like也可以用在表关联假如一个表A中 str字段内容是A1关联表是B中strings字段是A1,A2,A3
eg:select * from A inner join B on b.str like concat('%',A.str,'%') 注：concat是连接字符串函数
索引创建 create idx idx_name on table(file1,file2)

sql server

dateadd(day,1,w1.mydata)
计算四舍五入指定小数位转换的函数 cast、convert 和 round 写法不太一样
eg:保留两位小数
ROUND(col1/col2,2)
floor(file1)向下取整,ceiling(file1)向上取整
CAST(col1/col2 as decimal(8,2)) CAST('123' as int)
CONVERT(decimal(8,2),ROUND(col1/col2,2))
greatest(col1,col2,....)找出最大的返回
iif(condition,name1,name2)条件为true返回name1,false返回name2
power(col1,col2) 求幂 col1的col2次方
convert(type,data,111)函数 eg:CONVERT(varchar (100), GETDATE(), 23)
avg(col1/col2)函数的执行顺序group by之后使用avg先执行分组内按col1/col2求平均值，相加除分组内数量
eg select avg(rating/position) from table group query_name
inner join left join 按照条件会优先选择左右或者两者，如果要列出所有组合可以使用cross join作笛卡尔集
inner join 也可以实现cross的效果 table_help表主要是表id字段实现
eg:select * from table1 t1 inner join table_help th on th.id < 100;
group by col1,clo2...会根据col来筛选掉NULL，所以想要有NULL存在可以用没有NUll的表字段
CONCAT(str1,str2,…) 连接字符串
eg:concat("zhangsna","lisi") → zhangsnalisi concat_was(',','zhangsna','lisi') →zhangsna,lisi
charindex(expressionToFind,exprressToSearch[,start_location]) 查询字符出现的下标
expressionToFind ：目标字符串，就是想要找到的字符串，最大长度为8000 。expressionToSearch ：用于被查找的字符串。
start_location：开始查找的位置，为空时默认从第一位开始查找。
eg:select *,charindex('.com',u.mail) as idx from users u
left(col,num) right(col,nnum) col字段，偏移的数量
len(col)求col字段长度
UPPER() lower() 转大写和小写
sqlserver中json数据 JSON_VALUE(val, '$.name') 注意这个要从表中取，不能select直接查
eg: val = '{"CodeFreezeStatus":"InUse","RifFreezeStatus":"InUse"}'
SELECT JSON_VALUE(val, '$.CodeFreezeStatus') FROM test;
JSON_MODIFY(val, '$.CodeFreezeStatus', 'NewValue');可以反向修改
可能需要结合使用 JSON_MODIFY、JSON_QUERY、JSON_VALUE 等函数使用