整理常用sql语句
1.表插入列
alter table XMJ_ONE add column1 NUMBER(38) default 0;
comment on column XMJ_ONE.column1 is '字段1';
2.表删除列
alter table XMJ_ONE drop column column1;
3.修改某一列的数据类型，将NUMBER(38)类型的column1列改成VARCHAR2(75)
alter table XMJ_ONE rename column column1 to column2; --将列名column1重命名为column2
alter table XMJ_ONE add column1 VARCHAR2(75); --新增列column1
update XMJ_ONE set column1=trim(column2); --将column2列的数据复制到column1列
alter table XMJ_ONE drop column column2; --删除column2列
3.1修改某一列的数据类型，支持小数
alter table dms.PHAM_DOCU_DETAIL modify (INVENTORY NUMBER(16,2));
modify支持低精度到高精度转换（如整数转换成浮点），不支持高精度到低进度转换（如浮点到整数）
4.删除表数据
delete from XMJ_ONE where 1=1（或truncate table XMJ_ONE）
5.删除表数据和表结构
drop table XMJ_ONE
6.只复制表结构：
create table new_table as (select * from old_table where 1<>1)
7.复制表结构及数据：
create table new_table as (select * from old_table)
8.复制表的指定字段：
create table new_table as (select column1，column2… from old_table where 1<>1 )
9.复制表的指定字段及数据：
create table new_table as (select column1，column2… from old_table)
10.表的某一列添加唯一性约束
alter table MP_NE_LAB_SAMP
add constraint IX_MP_NE_LAB_SAMP_NO_BAR
unique (no_bar);
11.删除某个唯一性约束
ALTER TABLE MP_NE_LAB_SAMP
DROP CONSTRAINT IX_MP_NE_LAB_SAMP_NO_BAR ;
12.查找某个表的所有约束
select * from all_constraints where table_name = 'MP_NE_LAB_SAMP';
select * from User_Cons_Columns where table_name = 'MP_NE_LAB_SAMP';
表名MP_NE_LAB_SAMP必须大写，小写查不出来
13.查询表中某个字段重复多少次
select mp.code,count(*) from XMJ_MP mp where 1=1 group by mp.code having count(*) > 1
14.添加索引
create index INDEX_NAME ON TABLE_NAME(COLUMN_NAME)。
15.查看被锁的表
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id
16.mysql查询前10条数据
select * from xmj_mp order by sv limit 10 或 select * from xmj_mp order by sv limit 0,10
17.Orcale查询前10条数据
select * from(select t.*, rownum as rn from tab t) where rn between 0 and 10
18.判断表是否存在，如果存在就删除MySQL
DROP TABLE IF EXISTS table;
19.判断表是否存在，如果存在就删除Oracle
BEGIN EXECUTE IMMEDIATE 'DROP TABLE tableName'; EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;
20.查询Oracle数据库中有某列的表（列名需大写）
SELECT table_name FROM all_tab_columns WHERE column_name = 'DOC_ID';
有某3列的表 SELECT table_name FROM all_tab_columns WHERE column_name IN ('DOC_ID', 'CONFIG_ID', 'PARSE_TIME') GROUP BY table_name HAVING COUNT(DISTINCT column_name) = 3;
