orcl常用语句
(1)数据筛选:
select * from table where column = '字段值' order by column[desc]
(2)更新数据:
update table set column =字段值where …
(3)删除数据:
delete from table where …
(4)添加数据:
insert into table ( column 1, column 2, column 3…)values(值1,值2,值3…)
(5)添加字段:
alter table crm_dict add STATUS VARCHAR2(8) default 'USE';
(6)违反约束性条件
select * from user_cons_columns c where c.constraint_name like 'SYS_C0058841';
(7)备份
insert into table2 select * from table1;/*复制信息到已存在的表*/
create table table2 as select * from table1;/*新建表并复制信息*/
(8)闪回时间点查询
select * from table
as of timestamp to_timestamp('2020-05-07 00:21:41','yyyy-mm-dd hh24:mi:ss')
where …;
(9)获取纯数字列
Select * from table where regexp_like(column,'^[0-9]+$')
SQL常用语句一览
(1)数据记录筛选:
select * from table where column = '字段值' order by column[desc]
select * from table where column like'%'字段值'%' order by column[desc]
select top10* from table where column orderby字段名[desc]
select * from table where column in ('值1','值2','值3')
select * from table where column between 值1 and 值2
(2)更新数据记录:
update table set column =字段值where条件表达式
update table set column 1=值1, column 2=值2…… column n=值n where 条件表达式
(3)删除数据记录:
delete from table where 条件表达式
delete from table (将数据表所有记录删除)
(4)添加数据记录:
insert into table ( column 1, column 2, column 3…)values(值1,值2,值3…)
insert into table select * from 源数据表 (把源数据表的记录添加到目标数据表)
(5)添加字段:
alter table crm_dict add STATUS VARCHAR2(8) default 'USE'
(7)删除表:
当你不再需要该表时, 用 drop table ;
当你仍要保留该表,但要删除所有记录时, 用 truncate table;
当你要删除部分记录时(always with a WHERE clause), 用 delete table;
清空一列数据时 update product p set p.code=null;