数据库sql语句记录(珍藏版)
前言
记录一下平时工作中常用的sql语法,一点也不珍藏,有oracle的,有mysql的,两者有些语法并不通用,会有标注
mysql基础命令可以参考:[传送门]
时间
(oracle)
#当前时间:
> select sysdate from dual;
#时间格式转换,分别转换为 String/date 格式,可以互相嵌套更改格式:
> to_char(日期格式字段) / to_date(字符串格式字段)
语句:查找条件testField等于今年的数据,testField为字符串格式
> select * from
aa.test where
to_char(to_date(testField,'yyyy-mm-dd hh24:mi:ss'),'yyyy') = to_char(sysdate,'yyyy');
> select * from
dual where
sysdate >= TO_DATE(to_char(SXSJ , 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM-dd hh24:mi:ss')
(mysql)
#mysql时间比较很友好,无论是时间格式还是字符串格式都能直接比较
#当前时间:
> select NOW() from dual
#转为年月日格式时间
> DATE('2021-09-20 08:30:45')
#自定义转换格式,str可以为date,也可以为string
> DATE_FORMAT(str, '%Y-%m-%d %H:%i:%S')
语句:查找条件testField等于今年的数据,testField为字符串/时间格式
> select * from aa.test where testField= DATE_FORMAT('2021-09-01 21:01:22', '%Y-%m-%d %H:%i:%S')
分页
(oracle)
#取前十五条数据
> select b.*, rownum rn
from (select a.*
from dual a) b
where rownum <= 15
#获取从第一条到第十条的数据
> select * from
( select b.* ,rownum rn
from ( select * from aa.test ) b
where rownum <= 10 )
where rn >=1
(mysql)
#获取从第一条到第十条的数据
> select * from aa.test limit 0,10随机数
(oracle)
> select sys_guid() from dual;
#如果是程序中取值要取String格式,则如下
> select rawtohex(sys_guid()) from dual;
(mysql)
> select UUID() from dual;数值
(oracle)
#TRUNC函数用于对 值/日期 进行截断,TRUNC(n1,n2),n1表示被截断的数字,n2表示要截断到那一位
> select trunc(text) from dual;
> select sysdate,trunc(sysdate,'dd') from dual;
#四舍五入保留小数点
> select round(12.3456, 2) from dual;
#防止空值
①如果demo1为null,则返回demo2,如果两个都为null,返回null(demo1,demo2数据类型需要同类型)
> select NVL(demo1,demo2) from dual
②如果demo1为null,则返回demo3,不为null,返回demo2
> select NVL(demo1,demo2,demo3) from dual替换/截取
#获取[str]字段对应标识[substr]的下标,一般和其它函数一起使用,返回特殊的自定义值
INSTR(str,substr)
> select (instr(used_pct,'.') from dual ;
#截取[str]字段,从[pos]下标开始,[len]截取多长
SUBSTR(str,pos,len)
#替换[str]字段中的,[form_str]部分值替换为[to_str]
REPLACE(str,from_str,to_str)
#将字段testField的值 "TEST-20220902-0002" 查出来,只展示中间时间部分 "20220902"
> select testField , substr( testField, instr( testField, '-' )+1, 8 ) from aa.test条件判断
#判断[case_value]字段,满足[when_value]条件,则走[statement_listA]的语句,其他不满足的走[statement_listB]
(这个有两种写法,效果意思都是一样的,写法一)
CASE
WHEN when_value THEN
statement_listA
ELSE
statement_listB
END CASE;
(写法二)
CASE
WHEN case_value=when_value THEN
statement_listA
ELSE
statement_listB
END CASE;
#查出如果是张三的用户,全部变为李四,ikun的用户,全部变为篮球,其他不动
> SELECT
> CASE name
> WHEN '张三' THEN
> '李四'
> WHEN 'ikun' THEN
> '篮球'
> ELSE
> name
> END
> from aa.test
#mysql也可用IF(expr1,expr2,expr3)函数
#如果name值等于key1,那就返回val1,否则返回""
> select if(name='key1','val1','' ) from sys_log
(oracle)
① 如下,可提供多个判断
> decode(条件,值1,返回值1,值2,返回值2...)
②当字段或运算为值1时,返回值2,否则返回值3
> decode(字段或运算,值1,值2,值3)
#当name=ikun则返回鸡哥.当name=head,则返回黑子
> select decode(name,'ikun','鸡哥','head','黑子') tt from dual 去重复值
①(oracle)rowid方法(oracle数据的物理存储地址id)
select a.*,a.rowid from dual a where a.rowid in
(select max(rowid) from dual group by name having count(name)>1)
②group by 分组去重(常用的聚合函数:count(),sum(),avg(),max(),min())
select count(name),max(age),name from dual group by name having count(name)>1
③distinct
select distinct(name) from dual事务
#oracle视图工具(PLSQL)自动开启,每次操作都需要手动点击提交或回滚按钮
#开始事务在线编辑
> select t.rowid,t.* from dual t ; 或者
> select t.* from dual t for update ;
#mysql需要手动命令开启
#开始新的事务
> START TRANSACTION; 或 BEGIN;
> ...执行sql语句...
#提交当前事务
> COMMIT;
#回滚当前事务
> ROLLBACK; 更新查询结果
(mysql)
#根据 查出B表结果中的 b1字段 匹配A表中的a1字段, 条件满足的更新 a2=b2
> update aa.test a
> inner join
> (select * from b left join a on b.tt=a.tt) b
> on a.a1=b.b1
> set
> a.a2=b.b2查看相关表信息
(mysql)
#查看字段信息
> show full columns from aa.test
#生成创建表信息sql语句
show CREATE TABLE biz_ad查看慢日志
直接开启[传送门]()
id:联合查询中,越大的就越先执行
type:如图一
extra:如图二(key:使用到的索引字段)
图一
图二
拼接函数
(oracle)
select CONCAT(name , '吃了没') from dual;//只能拼接一个
select name || '吃了没' || '在干嘛' from dual;//可以拼接多个(mysql)
其实这个就是三个拼接函数,实现的效果不同,都分别介绍一下
1,CONCAT(str1,str2,…)函数,正常按顺序拼接展示结果,但注意:如有任何一个参数为NULL ，则返回值为 NULL
2,CONCAT_WS(separator,str1,str2,...) 函数
这是相当于concat的升级版,concat需要每一个符号顺序拼接,而这个只需要标注第一个参数,后面字段全都会自动拼接第一个参数,但注意:它不会忽略任何空字符串,然而会忽略所有的 NULL,如下案列,空串会被识别,拼接上逗号,null则不会,不拼接
GROUP_CONCAT([distinct]字段名 [order by 排序字段 asc/desc] [separator '分隔符']) 函数,一般配合 group by 使用,就是根据group by 字段分组后,同时查看其他字段的结果,如果是单独使用就是把字段全部自动拼接,默认逗号隔开.但注意:它不会忽略任何空字符串,然而会忽略所有的 NULL
批量更新
两种语句都是批量操作更新或插入,区别就是一个是先删除在重新插入替换,另一个则是直接更新
# replace into tablename (id, [字段1], [字段2], [字段3] ... ) values (1 , [val1] ),(2 , [val2] ),(3 , [val3] ) ...
#批量操作,如果字段id存在或有唯一约束冲突,执行则是先delete删除,在重新inset 插入,没有冲突或id不存在就执行插入
例子:
replace into test (id,name) values (16,'涨涨'),(13,'测试哈哈')
# insert into tablename (id, [字段1], [字段2], [字段3] ... ) values (1 , [val1] ),(2 , [val2] ),(3 , [val3] ) ... on duplicate key update 字段1=values(字段1),字段2=values(字段2),字段3=values(字段3) ... ;
#批量操作,如果字段id存在或有唯一约束冲突,执行则是先更新语句中对应字段数据,没有冲突或id不存在就执行插入
例子:
insert into test (Id,name) values (1,'张飞2'),(17,'关羽2') on duplicate key update name=values(name);插入或者更新(insertOrUpdate)
INSERT INTO ... ON DUPLICATE KEY UPDATE 是MySQL中的一种插入语句，用于在插入数据时避免主键或唯一索引冲突。当遇到重复的主键或唯一索引时，它会更新已存在的记录，而不是插入新记录。
语法如下：
INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...)
ON DUPLICATE KEY UPDATE
列1 = VALUES(列1), 列2 = VALUES(列2), 列3 = VALUES(列3), ...;例如，假设有一个名为`users`的表，包含`id`（主键）、`name`和`email`三个字段。如果我们想要插入一条新的用户记录，但如果这条数据主键已经存在 , 那就选择更新'name' 和 'email' 的值为name1,email1 ，可以使用以下语句：
INSERT INTO users (id, name, email)
VALUES (1, '张三', 'zhangsan@example.com')
ON DUPLICATE KEY UPDATE
name = 'name1', email = 'email1';冲突则删除重新插入
REPLACE INTO 是 MySQL 中的一个语句，用于将一条记录插入到表中。如果表中已经存在具有相同主键或唯一索引的记录，则该记录将被替换为新插入的记录。如果表中不存在具有相同主键或唯一索引的记录，则将插入新记录。
语法如下：
REPLACE INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);例如，假设有一个名为 students 的表，包含 id（主键）、`name` 和 age 三个字段。如果我们想要插入一条新的学生记录，可以使用以下语句：
REPLACE INTO students (id, name, age)
VALUES (1, '张三', 18);如果表中已经存在具有相同 id 的记录，那么这条记录将被替换为新插入的记录。
冲突则忽略不执行任何操作
INSERT IGNORE INTO 是 MySQL 中的一个语句，用于将一条记录插入到表中。如果表中已经存在具有相同主键或唯一索引的记录，则该记录将被忽略，不会进行插入操作。如果表中不存在具有相同主键或唯一索引的记录，则将插入新记录。
语法如下：
INSERT IGNORE INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);例如，假设有一个名为 students 的表，包含 id（主键）、`name` 和 age 三个字段。如果我们想要插入一条新的学生记录，可以使用以下语句：
INSERT IGNORE INTO students (id, name, age)
VALUES (1, '张三', 18);如果表中已经存在具有相同 id 的记录，那么这条记录将被忽略，不会进行插入操作。