1、oracle sql查询某个表有多少列
SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME=UPPER('表名')
select count(*) from all_tab_colums t where 1=1 and t.table_name = '表名' and t.owner = '所属用户名'
2、查询一个表按照其中一列分组,按照另一列排序,并且增加一个排序列记录
ROW_NUMBER() OVER (PARTITION BY 表中列名 ORDER BY 表中列名 DESC) AS 新加的排序列名
3、列转行
原表
查询的sql
select user_name, '语文' COURSE , CN_SCORE as SCORE from test_tb_grade2
union select user_name, '数学' COURSE, MATH_SCORE as SCORE from test_tb_grade2
union select user_name, '英语' COURSE, EN_SCORE as SCORE from test_tb_grade2
order by user_name,COURSE
结果表
还是上边的两个表,结果表变成原表,利用行转列
select t.user_name,
sum(decode(t.course, '语文', score,null)) as CHINESE,
sum(decode(t.course, '数学', score,null)) as MATH,
sum(decode(t.course, '英语', score,null)) as ENGLISH
from test_tb_grade t
group by t.user_name
order by t.user_nam
4、查询一段时间内的每一天
select to_date('2018-09-01', 'yyyy-MM-dd') + rownum - 1 as dateday
from dual
connect by rownum <= (to_date('2019-01-21', 'yyyy-MM-dd') -to_date('2018-09-01', 'yyyy-MM-dd'));
5、不小心在oracle中drop了一张表怎么恢复
select * from user_recyclebin where original_name = ‘被删除的表名’,如果查询到了以后在执行 flaskback Table “被删除的表名” to before drop,在被删除的表明上字母一定要大些,不然查不到。
6、插入的列值中需要有 ’ ’ 的时候,需要拼接一下
select ''''||name||'''' from dual;
7、oracle中不小心delete数据怎么退回?
首先查看一下
select * from 表名 as of timestamp to_timestamp (‘2020-11-11 00:00:00’, 'yyyy-mm-dd hh24:mi:ss')
存在之后开始执行闪回
1、首先要启用行迁移:
alter table 表名 enable row movement;
2、闪回到某个时间点:
FLASHBACK TABLE 表名 TO TIMESTAMP TO_TIMESTAMP(‘2020-11-11 00:00:00’, 'yyyy-mm-dd hh24:mi:ss')