实际开发中常用的SQL

1、获取时间

获取星期

select to_char(sysdate,'day') dayth from dual

获取上月 月份

select to_char(ADD_MONTHS(trunc(sysdate), -1),'yyyymm') from dual

获取上月1号

select trunc(add_months(sysdate,-1),'mm') first_day from dual;

2、Oracle行转列

listagg(string1,'|') within group (order by string) start_time；

3、字符串替换
replace('he love you','he','i') 替换结果i love you
4、字符串截取
substr('13088888888',3,8)从第3位开始，截取8位，截取子字符串08888888
5、时间与字符串转换
to_char(时间，‘yyyy/mm/dd hh24’)显示到小时

to_date(时间,'时间格式') 

6、处理字段为空的问题
在实际应用中，涉及到字段的计算。一个字段为null时，与其他字段的加减乘除结果都为null
nvl (expr1, expr2) 若expr1为null，就返回expr2，若expr1不能为null就返回expr1
nvl(字段,0) 若字段为空，返回0.
select coalesce(comm,0) from emp.将comm列中为null的值替换成0

select case when comm is not null then comm else 0 end
from emp  将comm列中为null的值替换成0

7、decode函数
decode(条件,判断值,显示值判断,值,显示值,…值n,翻译值n,缺省值)

 DECODE(s_city,'平凉','平凉市','张掖','张掖市','白银','白银市','陇南','陇南市','金昌','金昌市','天水','天水市','临夏','临夏回族自治州','酒泉','酒泉市','武威','武威市','嘉峪关','嘉峪关市','庆阳','庆阳市','定西','定西市','兰州','兰州市','甘南','甘南藏族自治州')  as city_name,
 平凉显示为平凉市。。。

8、 在where子句中引用列别名

select * from
{
select sal as salary,comm as commisstion from emp
} x
where salary<5000

将含有列别名的查询放入内嵌视图，就可以在外层查询中引用列别名。因为where子句会比select子句先执行，from比where先执行。
9、 串联多列的值
Oracle、DB2、PostgreSQL连接值时用"||",如：

select ename || ‘work as a ’ || job from emp where deptno=10

 MySQL连接时使用concat 函数，如：

select concat(ename,‘work as a ’,job) from emp where deptno=10

SQL Server 使用“+”作为串联连接符，如：

select ename+‘work as a ’+ job from emp where deptno=10

10、 限定返回行数
限定返回行数，但不关心排序，任意N行都行
DB2：使用fetch first

select * from emp fetch first 5 rows only

MySQL、hive、PostgreSQL:利用limit

select * from emp limit 5

Oracle:使用rownum

select * from emp where rownum>=5

SQL Server:使用top

select top 5 from emp

注意：Oracle 会针对查询到结果返回一个行号，从1开始。所以
rownum=5 会查询失败，因为第一行是1。
11、 查找null值

用is null或is not null

12、排序
order by sal asc 按sal顺序
order by sal desc 按sal倒序
order by 1 按第一列顺序

13、行合并
要求列数相同，及列的数据类型 必须相同

select ename,deptno from emp 
union all 
select ename,deptno from dept ;

注意：使用union也可以叠加两个结果集，但是union会排重，内部会进行一次distinct 排重操作，降低效率。union all 会返回所有结果集，效率较高，一般使用union all

14、 复制
复制数据：

insert into table_name1(c1,c2) select c1,c2 from table2；
 
 insert into pm.f_l_c_eutrancelltdd_d  select * from  pm.f_l_c_eutrancelltdd_d@wxwy 
 where start_time>=trunc(sysdate-1);

复制表结构：
DB2: create table table2 like table1
Oracle、MySQL、PostgreSQL：create table table2 as select * from table1 where 1=0
SQL Server：select * into table2 from table1 where 1=0
15、Oracle修改字段类型

alter table INTERFACE.DMP_GRID_PROBLEM_CELL  modify(ALARM_TIME varchar2(256));

修改INTERFACE用户下的表DMP_GRID_PROBLEM_CELL 的字段ALARM_TIME 为varchar2(256)

16、oracle增加字段

ALTER TABLE PM.F_5_C_N_NRCELLCU_Q ADD RRC_NsaUeMean  NUMBER;

给表PM.F_5_C_N_NRCELLCU_Q 添加一个number类型的字段RRC_NsaUeMean

COMMENT ON COLUMN PM.F_5_C_N_NRCELLCU_Q.RRC_NsaUeMean IS 'NSA 双链接平均用户数';

RRC_NsaUeMean 的备注信息为NSA 双链接平均用户数

17、查看所有被锁的表

select b.owner TABLEOWNER, b.object_name TABLENAME, c.OSUSER LOCKBY,
c.USERNAME LOGINID, c.sid SID, c.SERIAL# SERIAL
from v$locked_object a,dba_objects b, v$session c 
where b.object_id = a.object_id AND a.SESSION_ID =c.sid;

18、解锁表

alter system kill session 'SID, SERIAL';

19、查看表空间的使用情况

SELECT a.tablespace_name, 
a.bytes total, 
b.bytes used, 
c.bytes free, 
(b.bytes * 100) / a.bytes "% USED ", 
(c.bytes * 100) / a.bytes "% FREE " 
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c 
WHERE a.tablespace_name = b.tablespace_name 
AND a.tablespace_name = c.tablespace_name; 

20、查看表空间路径

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files where tablespace_name='SYSAUX'
order by file_name;

查看表空间SYSAUX的路径

21、添加数据文件

alter tablespace SYSAUX add datafile '+DATA01/wyzx/datafile/sysaux_20210430' size 10 G;

给表空间SYSAUX添加10G的数据文件

22、根据列名查找表名

 select column_name,table_name from user_tab_columns where column_name in('');

注意：只能查出当前用户下的表。

23、删掉重复项
找出主键为nrcelldu_uk，start_time都重复的数据，只留下一条数据

delete from pm.F_5_C_S_NRCELLDU_PRB_Q a 
where (a.nrcelldu_uk,a.start_time) in 
(select nrcelldu_uk,start_time from pm.F_5_C_S_NRCELLDU_PRB_Q 
group by nrcelldu_uk,start_time having count(*) > 1) 
and rowid not in (select max(rowid) 
from pm.F_5_C_S_NRCELLDU_PRB_Q group by nrcelldu_uk,start_time having count(*)>1
);
commit;

注意：操作表后顺手提交是好习惯，否则容易锁表

24、表分区操作
–删除表分区

alter table pm.f_l_c_eutrancelltdd_h drop partition 	P_1D_20200620;

–增加表分区

alter table pm.f_g_c_cell_day_BAK add partition	P_1D_20200601	VALUES LESS THAN ( to_date('	2020-06-02	','yyyy-mm-dd')) tablespace GSM_PM;

–查看表分区

SELECT * FROM useR_TAB_PARTITIONS WHERE TABLE_NAME='T_RES_CLUSTER_RRU_HISTORY' order by partition_name desc;

25、查看归档日志

SELECT A.NAME, A.TOTAL_MB / 1024, A.FREE_MB / 1024 FROM V$ASM_DISKGROUP A

26、Oracle批量生成随机数

select trunc(dbms_random.value (1, 80154)) as rmnum from dual connect by level < 1001;

作用：随机生成1000行1到80154的数