Oracle数据库常见的增删改查操作语句大全

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/FJeKin/article/details/72864804
回收站:
show recyclebin (命令行语句)
清空回收站:
purge recyclebin
回收站还原已删除表:
flashback table table_name to before dorp
获取系统时间:
sysdate
平均值:
avg
条件为真:where 1=1 条件为假:where 1=2
分页:rownum 索引:rowid
左右模糊查询(INSTR相比LIKE效率高):

INSTR(column_name,value) > 0 或 LIKE '%value%'

INSTR(column_name,value) = 0 或 NO LIKE '%value%'

日期精确到日:
TRUNC(date)
数字精度截取(截取时不进行四舍五入。number截取的数字,num_digits截取精度,默认为0):
TRUNC(number,num_digits)
NVL函数(expr1为空,返回expr2,否则返回expr1):
NVL(expr1,expr2)
求总和:sum(column_name)求个数:count(column_name)
where和having的区别:
1、where不能放在group by后面
2、having通常跟group by一起使用,放在group by后面,此时作用相当于where

3、where后面的条件中不能有聚集函数,比如SUM(),AVG()等,而having可以


字段默认系统时间
create table users(id number(5) primary key,birthday date default sysdate)
创建视图(只读):
create view view_name(view_column1,view_column2) as select column1,column2 from table_name with read only;
修改视图:
create or replace view view_name...
创建索引:
create index 索引名 on 表名(字段,...)

删除表内容(相比delete速度快,不可带where):
truncate table
删除字段:
alter table table_name drop column_name datatype;
删除表:
drop table table_name;
彻底删除表:
drop table table_name purge;
删除表中多余的重复记录,重复记录是根据单个字段来判断,只留有rowid最小的记录
delete from table_name where column_name in (select column_name from table_name group by column_name having count(column_name) > 1) and rowid not in (select min(rowid) from table_name group by column_name having count(column_name ) > 1);
删除索引:

drop index 索引名


更改字段数据类型:
alter table table_name modify column_name datatype;
修改字段名:
alter table table_name rename column column_name to new_column_name;
修改表名:
rename table_name to new_table_name;
多表关联更新:
update unicom_card_detail u set u.zone = (select d.zone from unicom_device_info d where d.deviceip = u.ip) where EXISTS (select 1 from unicom_device_info d where d.deviceip = u.ip);
已有表添加字段:
alter table table_name add column_name datatype;

查询表中多余的重复数据(多个字段)
select column_name1,column_name2 from table_name group by column_name1,column_name2 having count(*) > 1;
查询表有多少条数据:
select count(primary_key) from table_name;
查询表中某字段有多少个不重复的数据:
select count(distinct column_name) from table_name;
查看表结构(命令行语句):
desc table_name;
查看序列当前值和下一个值:
select seq_name.currval from dual;
select seq_name.nextval from dual;
左连接查询(以左表为基础,加上左表和右表匹配后的数据):
select t.column_name1,t.column_name2,u.column_name1 from table_name1 t left join table_name2 u on t.column_name1 = u.column_name1 where ...

右连接查询(以右表为基础,加上左表和右表匹配后的数据):

select t.column_name1,t.column_name2,u.column_name1 from table_name1 t right join table_name2 u on t.column_name1 = u.column_name1 where ...


最近整理的常用Oracle数据库常用语句,笔者用的是Oracle客户端PL/SQL工具,需要注意一点是,文章用到两种语句窗口:SQL窗口和Command窗口,如果SQL窗口不能执行那就有命令行窗口,反之,同理。后面有必要会持续更新,记录下来。如有笔误或建议,请及时联系笔者,万分感谢!欢迎加QQ群一起探讨学习成长:583138104







展开阅读全文

没有更多推荐了,返回首页