Add fields in SQL (some can only be run in ORACLE)

Add fields in SQL (some can only be run in ORACLE)

[SQL] add field alter table table_name add column_name varchar (200) delete field alter table table_NAME drop column column_NAME modify Field Type alter table table_name alter column column_name new_data_type modify table field length (modify TABLE structure) alter TABLE table_name modify column_name varchar (40); rename sp_rename

 

Change the name of the user-created object (such as a table, column, or user-defined data type) in the current database. Syntax

Sp_rename [@ objname =] 'object _ name', [@ newname =] 'new _ name '[, [@ objtype =] 'object _ type'] -- assume that the table to be processed is named: tb -- determine whether the table to be added has a primary key if exists (select 1 from sysobjects where parent_obj = object_id ('tb') and xtype = 'pk ') the begin print table already has a primary key, and the column can only be added as a common column '-- add a column of the int type, the default value is 0 alter table tb add column name int default 0 end else begin print. No primary key exists in the table. add the primary key column 'query certain fields in the table to record the select count (*) Statement repeatedly (*), column_name from table_name group by column_name having count (*)> 1; query and modify data (for example, change 67001 to 68701,67101 to 68801) select code, ('68 '| (substr (code, 2, 1) + substr (code, 3, 1) | substr (code, 4, 2 )) as code2 from table_name where code like '67 ___ '; update table_name set code = ('68' | (substr (code, 2, 1) + substr (code, 3, 1 )) | substr (code, 4, 2), parentid = '68 'where code like '67 ___';