sql 函数的使用

目录

mysql 

postsql

sql server 

---

mysql 

•  isnull( body ,null)  查询内容为空，输出后边的信息
• IFNULL(expression_1,expression_2); expression_1为null输出expression_2
• IF(condition, value_if_true, value_if_false) condition是一个表达式
• 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;

• distinct 还可以和on配合使用(组内去重) 可以根据某些字段过滤取一条 可以排序选择第一或者最后
• eg: select distinct on(file1,file2,file3)  file1,fiile4 from youtable order by file1,file2,file3,file4 desc;

• 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

• 获取当前月份天数  day(last_day('20200202'))

• eg:select  day(last_day(datetimefile)) from table

• extract()函数从日期/时间值中提取年、月、日等字段
  语法：EXTRACT(field FROM source)
  field字段：

  字段值	时间戳	间隔
  CENTURY	世纪	世纪数
  DAY	一月中的第几天 (1-31)	天数
  DECADE	年份除以 10 得到的十年	与时间戳相同
  DOW	星期几，星期日 (0) 至星期六 (6)	不适用
  DOY	一年中的第几天，范围从 1 到 366	不适用
  EPOCH	自 1970-01-01 00:00:00 UTC 以来的秒数	间隔内的总秒数
  HOUR	小时 (0-23)	小时数
  ISODOW	基于 ISO 8601 的星期几，星期一 (1) 至星期日 (7)	不适用
  ISOYEAR	基于 ISO 8601 的一年中的第几周	不适用
  MICROSECONDS	秒字段，包括小数部分，乘以 1000000	与时间戳相同
  MILLENNIUM	几千年	千年数
  MILLISECONDS	秒字段，包括小数部分，乘以 1000	与时间戳相同
  MINUTE	分钟 (0-59)	分钟数
  MONTH	月份，1-12	月数，取模 (0-11)
  QUARTER	一年中的季度	季度数
  SECOND	秒	秒数
  TIMEZONE	与 UTC 的时区偏移量，以秒为单位	不适用
  TIMEZONE_HOUR	时区偏移量的小时部分	不适用
  TIMEZONE_MINUTE	时区偏移量的分钟部分	不适用
  WEEK	ISO 8601 周编号的一年中的第几周	不适用
  YEAR	年份	与时间戳相同

  source:source是TIMESTAMP或INTERVAL类型的值。如果传递一个DATE值，该函数会将其转换为一个TIMESTAMP值
  返回值：返回一个双精度的值
  eg：SELECT EXTRACT(YEAR FROM TIMESTAMP '2016-12-31 13:30:15'); --提取年份
  结果：2016
  SELECT EXTRACT(QUARTER FROM TIMESTAMP '2016-12-31 13:30:15');--季度
  季度：4

• limit  num1 offset num2  offset表示偏移量，limit表示取出的数量

• eg: limit 1 offse 2 取出第三条数据

• insert ignore 插入时，重复数据忽略

• insert into table(fiel1,fiel2)
  select fiel1,fiel2 from table1
  插入时可以select查询语句进行插入

• REPLACE INTO 更新插入，检测到主键或唯一性索引键重复就删除原记录后重新插入

• 创建索引的两种方式区别
  使用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可以创建多个，分割

• 全文索引的使用：SELECT * FROM youtable WHERE MATCH(file) AGAINST('content');
  注：需要对file创建全文索引   CREATE FULLTEXT INDEX idx_content ON youtable (file);

• 修改表结构
  增加字段到某个字段后 alter table youtable add newfile varchar(15) after file1;
  可以多个命令： alter table youtable  add column newfile varchar(15) after file1,
                                                             change column job profession varchar(10);

• 通过其他表创建新表
  复制表结构到另一张表 ：CREATE TABLE tb_name like tb_name_old
  查询数据创建新的表：CREATE TABLE tb_name AS SELECT * FROM tb_name_old WHERE options

• 触发器创建  可以用于学号增加前几位或者记录

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

• 删除后重置自增主键
  eg:alter table youtable auto_increment =1
  也可以用一句话删除并重置自增id
  truncate table youtable;

• 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 两位数字表示的年份
  %% 直接值“%”

•  TIMESTAMPDIFF(datepart,start_time, submit_time)      datepart 格式：

• datepart	缩写	意义
  year	yy  | yyyy	年
  quarter	qq  |  q	季度
  month	mm  |  m	月
  week	ww  |  w	周
  day	dd  |  d	日
  hour	hh	小时
  minute	mi  |  n	分钟
  second	ss  |  s	秒
  millisecond	ms	毫秒

  
  eg:select DATEDIFF(year,'2010-12-31','2019-01-01')

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指开始到最后
• 
• 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')  或者 json_extract_path_text(val::json, 'name')::DECIMAL 函数也可以查询
  (val::json)->>'name' 和上边方法效果一样  

• eg:
  
  
  jsonb_build_object(val) 查询结果可以去掉""号
  UPDATE test SET val = jsonb_set(val::jsonb, '{CodeFreezeStatus}', '"NewValue"'::jsonb);可以使用这个反向修改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 等函数使用

SQL查询的一般执行顺序是：

1. from
2. where
3. group by
4. having
5. select
6. distinct
7. order by
8. limit

• 注：sql server中查询同时聚合函数和字段需要都在group中，mysql不需要 
• eg:select count(id),name from table group by id
• 通常建议or关键词用union代替 or会使索引失效，查询速度慢
• row_number通常用于连续数据查询或者筛选数据
• group by 多用于数量统计