数据库常用相关（一）
修改字段类型：
alter table 表名 modify column_name varchar2(32);
alter table 表名 modify (column_name1 varchar(20) default null,column_name2 varchar2(30));
------------------------------------------------------------------------------------------------
修改字段名：
alter table 表名 rename column 旧的字段名 to 新的字段名名;--oracle
ALTER TABLE gk_personinfo CHANGE COLUMN PLinkTel Mobile VARCHAR(20);--mysql
------------------------------------------------------------------------------------------------
删除字段：
alter table 表名 drop column 字段名;
------------------------------------------------------------------------------------------------
将一个表中的数据导入新建表:
delete from tdcustmanager.hc_hazarddgs;
insert into tdcustmanager.hc_hazarddgs select * from tdjkhc.hc_hazarddgs;
------------------------------------------------------------------------------------------------
sql navigator 获取 数据定义语言、序列
选择相应表（sequence），右键extract DDL，去掉和表空间相关的语句。
------------------------------------------------------------------------------------------------
根据出生年月算年龄
(to_number(to_char(sysdate, 'yyyy')) - to_number(to_char(PBRITHDAY, 'yyyy'))) PAGE,
------------------------------------------------------------------------------------------------
网络配置文件路径：
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
------------------------------------------------------------------------------------------------
修改某一记录多个字段的值：
update gc_columns set datatype='varchar',length=30 where obj_id=1484 and colname in ('Erythrocyturia','PH');
------------------------------------------------------------------------------------------------
特使字符&的处理：
'||'&'||'或set define off;
------------------------------------------------------------------------------------------------
--删除用户
drop user zshitsm cascade
--创建用户
create user zshitsm
identified by zshitsm
default tablespace users
temporary tablespace temp
profile DEFAULT;
grant connect to zshitsm;
grant dba to zshitsm;
grant alter any table to zshitsm;
grant create any table to zshitsm;
grant select any table to zshitsm;
grant unlimited tablespace to zshitsm;
grant update any table to zshitsm;
--数据库导出
exp userid=tdcustmanager/tdcustmanager@GC_12 file=E:\数据库备份\tdcustmanager20140108_local.dmp buffer=64000
--数据库导入
imp userid=tdcustmanager/tdcustmanager@ TDJKHC_LOCAL file=E:\数据库备份\tdcustmanager20131118_local.dmp fromuser=tms touser= tdcustmanager
su -oracle
exp userid=tdcustmanager/tdcustmanagerhello123@TDSERVER_local file=E:\数据库备份\tdcustmanager20140116_local.dmp buffer=64000
------------------------------------------------------------------------------------------------
--sqlServer导出数据存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sys_getInsertScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sys_getInsertScript]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE Proc dbo.sp_sys_getInsertScript
@TblName varchar(100),
@BackTblName varchar(100)=NULL,
@whereClause varchar(300)=NULL,
@Collist varchar(300)='*',
@isOracle char(1)='F',
@Debug char(1)='F' -- F - False T - True A - F+T P - print insert_sql result
--with encryption
As
Declare @sql varchar(8000), @sql1 varchar(2000), @sql_PK varchar(1000)
Declare @colName varchar(100), @datatype varchar(50), @NullData varchar(50)
Select @sql='',@sql1='',@sql_PK='',
@BackTblName = Case when isnull(ltrim(@BackTblName),'')='' then NULL Else @BackTblName End ,
@whereClause = case when isnull(ltrim(@whereClause),'')='' then NULL Else @whereClause End ,
@Collist = Case when isnull(ltrim(@collist),'')='' then '*' Else Replace(@collist,' ','') End
----------------------------------------------------------------------------------------------
-- 0 - Schedule Module
----------------------------------------------------------------------------------------------
L_0:
if @BackTblName is null goto L_2
----------------------------------------------------------------------------------------------
-- 1 - Build Join Criteria if @backTblName is Not NULL
----------------------------------------------------------------------------------------------
L_1:
Declare Tmp_Cur Cursor For
Select Colname,DataType
From (select convert(varchar(100),sc.name) As colname,
Case when rtrim(st.name) in ('char','nchar','ntext','nvarchar','sysname','text','varchar') then 'CH'
when rtrim(st.name) in ('binary','bit','decimal','float','int','money','numeric','real','tinyint','smallint','smallmoney','varbinary') then 'NUM'
when rtrim(st.name) in ('datetime','smalldatetime') then 'DT'
else 'OTH'
End As Datatype
FROM sysindexes si
join sysindexkeys sik On si.id=sik.id And si.indid=sik.indid
Join syscolumns sc on sik.id=sc.id and sik.colid=sc.colid
join systypes st on sc.xtype=st.xusertype
where si.id=object_id(@tblName)
And ( si.status &(2048)> 0 or si.status &(4096)=4096 And not exists (select * from sysindexes si2 where si2.id=si.id and si2.status &(2048)> 0))
) As T
Open Tmp_Cur
Fetch Next From Tmp_Cur Into @Colname,@dataType
While @@Fetch_Status = 0
BEGIN
select @sql_PK = @sql_PK + ' AND t1.'+@colname+'=t2.'+@colName
Fetch Next From Tmp_Cur Into @Colname,@dataType
END
Close Tmp_Cur
Deallocate Tmp_Cur
if @sql_PK <> ''
select @sql_PK =stuff(@sql_PK,1,4,'')
--print '1-
--'+@sql_PK
----------------------------------------------------------------------------------------------
-- 2 - Build Table column list
----------------------------------------------------------------------------------------------
L_2:
Declare Tmp_Cur Cursor For
select colname,datatype,Case datatype when 'CH' then '''NULL''' when 'DT' then '''1900-1-1''' when 'NUM' then '0' Else '''''' End As NullData
From
(select convert(varchar(100),sc.name) As colname,
Case when rtrim(st.name) in ('char','nchar','ntext','nvarchar','sysname','text','varchar') then 'CH'
when rtrim(st.name) in ('binary','bit','decimal','float','int','money','numeric','real','tinyint','smallint','smallmoney','varbinary') then 'NUM'
when rtrim(st.name) in ('datetime','smalldatetime') then 'DT'
else 'OTH'
End As Datatype,
sc.isnullable
-- Case sc.isnullable when 0 then 'Not NULL' Else ' NULL' end As NullAble,
from syscolumns sc join systypes st on sc.xtype=st.xusertype
where sc.id=object_id(@tblName)
And (CHARINDEX('*',isnull(@collist,'*'),1)>0 OR CHARINDEX(','+sc.name+',',','+@collist+',',1)>0)
And CHARINDEX('/'+sc.name+',',isnull(@collist+',',''),1)=0
) As T
Open Tmp_Cur
Fetch next From Tmp_Cur Into @colName,@datatype,@NullData
While @@fetch_Status = 0
Begin
select @sql = @sql + '+'',''+isnull('
+Case @dataType when 'NUM' then 'convert(varchar(30),t1.'+@colName+')'
When 'DT' then CASE @isOracle WHEN 'T' THEN '''TO_DATE(' + replicate('''',3)+'+convert(varchar(20),t1.'+@colName+',20)+'+ replicate('''',3) + ',' + replicate('''',2) + 'yyyy-mm-dd HH24:mi:ss' + replicate('''',2) + ')'''
ELSE replicate('''',4)+'+convert(varchar(20),t1.'+@colName+',20)+'+ replicate('''',4)
END
Else replicate('''',4)+'+Replace(rtrim(convert(varchar(4000),t1.'+@colName+')),'+replicate('''',4)+','+replicate('''',6)+')+'+ replicate('''',4)
End+','+'''NULL'''+')
',@sql1=@sql1+','+@colName
Fetch next From Tmp_Cur Into @colName,@datatype,@NullData
End
close Tmp_Cur
Deallocate Tmp_Cur
----------------------------------------------------------------------------------------------
-- 3 - Build script and execute
----------------------------------------------------------------------------------------------
L_3:
if @sql<>''
Select @sql='Select ''INSERT INTO '+@TblName+'('+stuff(@Sql1,1,1,'')+') Values( ''+ syntax+'');'' As Insert_syntax
From ( Select Replace('+stuff(@sql,1,5,'')+','''''''','''''''') As syntax
From '+@TblName+' t1
where ' +isnull('('+@WhereClause+') And ','')
+Case when @BackTblName is not null then 'not exists (select * from '+@BackTblName+' t2 where '+@sql_PK+')'
Else '1=1'
End + '
) As T'
if @Debug in ('T','A') Print @sql
if @Debug in ('F','A') exec (@sql)
if @Debug = 'P'
BEGIN
exec ('Declare Tmp_Cur Cursor For ' + @sql)
open Tmp_cur
Fetch Next From Tmp_cur Into @sql
While @@Fetch_status = 0
BEGIN
print @sql
Fetch Next From Tmp_cur Into @sql
END
Close Tmp_Cur
Deallocate Tmp_cur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------创建生成记录的的过程语句 结束--------
sp_sys_getinsertScript gc_appobjects,@whereclause = 'obj_id=2299'
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
alter table 表名 modify column_name varchar2(32);
alter table 表名 modify (column_name1 varchar(20) default null,column_name2 varchar2(30));
------------------------------------------------------------------------------------------------
修改字段名：
alter table 表名 rename column 旧的字段名 to 新的字段名名;--oracle
ALTER TABLE gk_personinfo CHANGE COLUMN PLinkTel Mobile VARCHAR(20);--mysql
------------------------------------------------------------------------------------------------
删除字段：
alter table 表名 drop column 字段名;
------------------------------------------------------------------------------------------------
将一个表中的数据导入新建表:
delete from tdcustmanager.hc_hazarddgs;
insert into tdcustmanager.hc_hazarddgs select * from tdjkhc.hc_hazarddgs;
------------------------------------------------------------------------------------------------
sql navigator 获取 数据定义语言、序列
选择相应表（sequence），右键extract DDL，去掉和表空间相关的语句。
------------------------------------------------------------------------------------------------
根据出生年月算年龄
(to_number(to_char(sysdate, 'yyyy')) - to_number(to_char(PBRITHDAY, 'yyyy'))) PAGE,
------------------------------------------------------------------------------------------------
网络配置文件路径：
# tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\db_1\NETWORK\ADMIN\tnsnames.ora
------------------------------------------------------------------------------------------------
修改某一记录多个字段的值：
update gc_columns set datatype='varchar',length=30 where obj_id=1484 and colname in ('Erythrocyturia','PH');
------------------------------------------------------------------------------------------------
特使字符&的处理：
'||'&'||'或set define off;
------------------------------------------------------------------------------------------------
--删除用户
drop user zshitsm cascade
--创建用户
create user zshitsm
identified by zshitsm
default tablespace users
temporary tablespace temp
profile DEFAULT;
grant connect to zshitsm;
grant dba to zshitsm;
grant alter any table to zshitsm;
grant create any table to zshitsm;
grant select any table to zshitsm;
grant unlimited tablespace to zshitsm;
grant update any table to zshitsm;
--数据库导出
exp userid=tdcustmanager/tdcustmanager@GC_12 file=E:\数据库备份\tdcustmanager20140108_local.dmp buffer=64000
--数据库导入
imp userid=tdcustmanager/tdcustmanager@ TDJKHC_LOCAL file=E:\数据库备份\tdcustmanager20131118_local.dmp fromuser=tms touser= tdcustmanager
su -oracle
exp userid=tdcustmanager/tdcustmanagerhello123@TDSERVER_local file=E:\数据库备份\tdcustmanager20140116_local.dmp buffer=64000
------------------------------------------------------------------------------------------------
--sqlServer导出数据存储过程
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_sys_getInsertScript]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_sys_getInsertScript]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE Proc dbo.sp_sys_getInsertScript
@TblName varchar(100),
@BackTblName varchar(100)=NULL,
@whereClause varchar(300)=NULL,
@Collist varchar(300)='*',
@isOracle char(1)='F',
@Debug char(1)='F' -- F - False T - True A - F+T P - print insert_sql result
--with encryption
As
Declare @sql varchar(8000), @sql1 varchar(2000), @sql_PK varchar(1000)
Declare @colName varchar(100), @datatype varchar(50), @NullData varchar(50)
Select @sql='',@sql1='',@sql_PK='',
@BackTblName = Case when isnull(ltrim(@BackTblName),'')='' then NULL Else @BackTblName End ,
@whereClause = case when isnull(ltrim(@whereClause),'')='' then NULL Else @whereClause End ,
@Collist = Case when isnull(ltrim(@collist),'')='' then '*' Else Replace(@collist,' ','') End
----------------------------------------------------------------------------------------------
-- 0 - Schedule Module
----------------------------------------------------------------------------------------------
L_0:
if @BackTblName is null goto L_2
----------------------------------------------------------------------------------------------
-- 1 - Build Join Criteria if @backTblName is Not NULL
----------------------------------------------------------------------------------------------
L_1:
Declare Tmp_Cur Cursor For
Select Colname,DataType
From (select convert(varchar(100),sc.name) As colname,
Case when rtrim(st.name) in ('char','nchar','ntext','nvarchar','sysname','text','varchar') then 'CH'
when rtrim(st.name) in ('binary','bit','decimal','float','int','money','numeric','real','tinyint','smallint','smallmoney','varbinary') then 'NUM'
when rtrim(st.name) in ('datetime','smalldatetime') then 'DT'
else 'OTH'
End As Datatype
FROM sysindexes si
join sysindexkeys sik On si.id=sik.id And si.indid=sik.indid
Join syscolumns sc on sik.id=sc.id and sik.colid=sc.colid
join systypes st on sc.xtype=st.xusertype
where si.id=object_id(@tblName)
And ( si.status &(2048)> 0 or si.status &(4096)=4096 And not exists (select * from sysindexes si2 where si2.id=si.id and si2.status &(2048)> 0))
) As T
Open Tmp_Cur
Fetch Next From Tmp_Cur Into @Colname,@dataType
While @@Fetch_Status = 0
BEGIN
select @sql_PK = @sql_PK + ' AND t1.'+@colname+'=t2.'+@colName
Fetch Next From Tmp_Cur Into @Colname,@dataType
END
Close Tmp_Cur
Deallocate Tmp_Cur
if @sql_PK <> ''
select @sql_PK =stuff(@sql_PK,1,4,'')
--print '1-
--'+@sql_PK
----------------------------------------------------------------------------------------------
-- 2 - Build Table column list
----------------------------------------------------------------------------------------------
L_2:
Declare Tmp_Cur Cursor For
select colname,datatype,Case datatype when 'CH' then '''NULL''' when 'DT' then '''1900-1-1''' when 'NUM' then '0' Else '''''' End As NullData
From
(select convert(varchar(100),sc.name) As colname,
Case when rtrim(st.name) in ('char','nchar','ntext','nvarchar','sysname','text','varchar') then 'CH'
when rtrim(st.name) in ('binary','bit','decimal','float','int','money','numeric','real','tinyint','smallint','smallmoney','varbinary') then 'NUM'
when rtrim(st.name) in ('datetime','smalldatetime') then 'DT'
else 'OTH'
End As Datatype,
sc.isnullable
-- Case sc.isnullable when 0 then 'Not NULL' Else ' NULL' end As NullAble,
from syscolumns sc join systypes st on sc.xtype=st.xusertype
where sc.id=object_id(@tblName)
And (CHARINDEX('*',isnull(@collist,'*'),1)>0 OR CHARINDEX(','+sc.name+',',','+@collist+',',1)>0)
And CHARINDEX('/'+sc.name+',',isnull(@collist+',',''),1)=0
) As T
Open Tmp_Cur
Fetch next From Tmp_Cur Into @colName,@datatype,@NullData
While @@fetch_Status = 0
Begin
select @sql = @sql + '+'',''+isnull('
+Case @dataType when 'NUM' then 'convert(varchar(30),t1.'+@colName+')'
When 'DT' then CASE @isOracle WHEN 'T' THEN '''TO_DATE(' + replicate('''',3)+'+convert(varchar(20),t1.'+@colName+',20)+'+ replicate('''',3) + ',' + replicate('''',2) + 'yyyy-mm-dd HH24:mi:ss' + replicate('''',2) + ')'''
ELSE replicate('''',4)+'+convert(varchar(20),t1.'+@colName+',20)+'+ replicate('''',4)
END
Else replicate('''',4)+'+Replace(rtrim(convert(varchar(4000),t1.'+@colName+')),'+replicate('''',4)+','+replicate('''',6)+')+'+ replicate('''',4)
End+','+'''NULL'''+')
',@sql1=@sql1+','+@colName
Fetch next From Tmp_Cur Into @colName,@datatype,@NullData
End
close Tmp_Cur
Deallocate Tmp_Cur
----------------------------------------------------------------------------------------------
-- 3 - Build script and execute
----------------------------------------------------------------------------------------------
L_3:
if @sql<>''
Select @sql='Select ''INSERT INTO '+@TblName+'('+stuff(@Sql1,1,1,'')+') Values( ''+ syntax+'');'' As Insert_syntax
From ( Select Replace('+stuff(@sql,1,5,'')+','''''''','''''''') As syntax
From '+@TblName+' t1
where ' +isnull('('+@WhereClause+') And ','')
+Case when @BackTblName is not null then 'not exists (select * from '+@BackTblName+' t2 where '+@sql_PK+')'
Else '1=1'
End + '
) As T'
if @Debug in ('T','A') Print @sql
if @Debug in ('F','A') exec (@sql)
if @Debug = 'P'
BEGIN
exec ('Declare Tmp_Cur Cursor For ' + @sql)
open Tmp_cur
Fetch Next From Tmp_cur Into @sql
While @@Fetch_status = 0
BEGIN
print @sql
Fetch Next From Tmp_cur Into @sql
END
Close Tmp_Cur
Deallocate Tmp_cur
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
------创建生成记录的的过程语句 结束--------
sp_sys_getinsertScript gc_appobjects,@whereclause = 'obj_id=2299'
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
邮箱:wangh_2@sina.com
