最近用到的SQL语句

最近在导数据，包括使用kettle将sqlserver、mysql的数据导入oracle中（使用多表导入向导），添加主键、注释，为后续导入hbase打好基础。

--table_name为要操作的表名，column_name为要操作的列名
--oracle
--找出id字段的最大值
select max(id) from table_name
--操作数据之前备份表
create table table_name_bak as select * from table_name
--对于已经建好的空表可以这样备份（需要commit）
insert into table_name_bak select * from table_name
--统计表的行数（比count(*)快但第一列不能有null值）
select count(1) from table_name
--清空表数据（慎用，快是快但难以恢复）
truncate table table_name
--为表添加注释
comment on table table_name is '要添加的表注释';
--为列添加注释
comment on column table_name.column_name is '要添加的列注释';
--重命名表（pl/sql developer通过对表的Edit是无法重命名的，会新建一张表）
alter table old_table_name rename to new_table_name 
--找出所有带CLOB字段的用户表（应尽量少的使用CLOB字段）
select distinct(t.TABLE_NAME) from all_tab_columns t 
    where t.TABLE_NAME in (select t2.TABLE_NAME from user_tables t2) 
    and t.DATA_TYPE = 'CLOB' order by t.TABLE_NAME;
--查询某一列有无重复的值，如果有则输出该列
select column_name, count(*) from table_name group by column_name having count(*) > 1    
--将一张表的数据插入到另一张表中
insert into e_cdr (select t.*, to_date('20160102', 'yyyyMMdd') as E_CDR_DATE from e_cdr_20160102 t)
--为表增加主键（主键名不能超过30个字符）
alter table table_name add constraint pk_table_name_column_name primary key (column_name)
--为某一个表增加ID字段，设置自动增长的值最后设为主键（seq_temp是自己建的sequence）
alter table table_name add id number;
update table_name t set t.id = seq_temp.nextval
alter table table_name add constraint pk_table_name_id primary key (id)
--查看用户username下前缀为perfix的表（通常相同前缀的表为一类表）
select t.TABLE_NAME from all_tables t where t.OWNER = 'username' and t.TABLE_NAME like 'perfix%'
--查看前缀为perfix的表的注释
select * from user_tab_comments t where t.TABLE_NAME like 'perfix%'
--修改列名，如果列名为uuid之类的关键字，要加双引号，如"uuid"
alter table table_name rename column column_name to new_column_name
alter table table_name rename column "uuid" to id
--查找没有主键并的表
select t.TABLE_NAME from all_tables t where t.OWNER = 'username' and t.TABLE_NAME like 'perfix%' and t.TABLE_NAME not in
(select t.table_name from user_constraints t where t.constraint_type = 'P')
--整合多表数据到一张新表中
create table table_name as(
select * from table_name1 union all
select * from table_name2 union all
...)
--查找所有列的表名、列名、数据类型及注释，按照表名、列名的字母进行排序
SELECT t1.Table_Name AS "表名称",
t2.Comments AS "名称",
t1.Column_Name AS "代码",
t1.Data_Type AS "数据类型"
FROM cols t1 left join user_col_comments t2
on t1.Table_name=t2.Table_name and t1.Column_Name=t2.Column_Name
left join user_tab_comments t3 
on t1.Table_name=t3.Table_name 
WHERE NOT EXISTS ( SELECT t4.Object_Name FROM User_objects t4
WHERE t4.Object_Type='TABLE' 
AND t4.Temporary='Y' 
AND t4.Object_Name=t1.Table_Name )
ORDER BY t1.Table_Name, t1.Column_ID;

--sqlserver
--找出某一列最长的长度
SELECT max(len(column_name)) FROM table_name
--重命名某一列（遇到过因为sqlserver表列名过长而无法导入oracle的情况）
exec sp_rename 'table_name.old_column_name', 'new_column_name', 'column'
--因为使用kettle导表没有将注释导过去，所以手动拿到注释拼成sql语句进行导入
--获取所有表的注释
select tables.name, cast(ep.[value] as varchar(500)) from sys.tables tables 
left join sys.extended_properties ep on (tables.object_id = ep.major_id and ep.minor_id = 0) order by tables.name
--获取所有的表名、列名及相应的注释
select tables.name,columns.name,cast(ep.value as varchar(500)) from 
    sys.tables tables inner join sys.columns columns on columns.object_id = tables.object_id 
    left join sys.extended_properties ep on ep.major_id = columns.object_id and ep.minor_id = columns.column_id
--查看数据库中所有表的数据量，为确保显示一致按照名称排序
select a.name, b.rows from sysobjects as a inner join sysindexes as b on a.id = b.id where (a.type = 'u') and (b.indid in (0,1)) order by a.name

--mysql
--赋予某用户读表的权限
grant select on database_name.table_name to user_name;
--撤销某用户读表的权限
revoke select on database_name.table_name from user_name;
--修复某张表
repair table table_name
--检查某个库中的所有表并自动修复（cmd执行）
--mysqlcheck -c --databases db_name -uroot -p --auto-repair