目录
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;
-
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
-
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_stmttrigger_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指开始到最后 - 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
等函数使用
SQL查询的一般执行顺序是:
- from
- where
- group by
- having
- select
- distinct
- order by
- limit
- 注:sql server中查询同时聚合函数和字段需要都在group中,mysql不需要
- eg:select count(id),name from table group by id
- 通常建议or关键词用union代替 or会使索引失效,查询速度慢
- row_number通常用于连续数据查询或者筛选数据
- group by 多用于数量统计