添加一列
alter table 表名 add 列名 类型; 添加一行 insert into 表名 values ('','',''); 删除一行 delete from 表名 where 条件; 删除一列 ALTER TABLE 表名 DROP COLUMN 列名; alter table student drop constraint pk_student --去掉这个表的主键 alter table student add constraint pk_student primary key clustered (studentid, seq) --重新设置表的主键 修改字段的值 update 表名 set 字段='xxxx' where 字段='yyy' 根据一张表创建另一张表（也可以在原表上加一定的条件） select * into a_s_unit from s_unit weher id in(1,2,3,4) ; oracle: create table a_s_unit as select * from s_unit weher id in(1,2,3,4) ; 使用rename关键字来实现字段名的修改:alter table 表名 rename column旧的字段名 to 新的字段名名; 使用modify关键字来实现对数据类型的修改:alter table 表名 modify 字段名 数据类型; oralce 修改有数据的列的类型 1，建立临时列 alter table S_UNIT_INCOME add income_fee_bak decimal(18,6); 2.把修改的列的数据导入到临时列 update s_unit_income set income_fee_bak=income_fee; 3.把列的数据全部修改为空 update s_unit_income set income_fee=''; 4.修改列的类型 alter table S_UNIT_INCOME modify income_fee decimal(18,6); 5.把临时列的数据导入到修改的列 update s_unit_income set income_fee=income_fee_bak; 6.删除临时列 alter table S_UNIT_INCOME drop column income_fee_bak; 大字段修改 1，建立临时列 alter table s_project add iintro_bak varchar(4000); 2.把修改的列的数据导入到临时列 update s_project set iintro_bak=intro; 3.删除列 ALTER TABLE s_project DROP COLUMN intro; 4.把临时列命名为删除的列 ALTER TABLE s_project RENAME COLUMN iintro_bak TO intro; 增加主键约束 alter table S_PROJECT add constraint PK__S_PROJECT primary key(ID); oracle修改数据库密码 alter user 数据库名 identified by 密码; 获取当前时间 cast((Datename(year,GetDate())+'-'+Datename(month,GetDate())+'-'+Datename(day,GetDate())) as datetime) 查表 select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE='base table' 存储过程 --------------------------------------------------- declare @unitId varchar(32),@honorId varchar(32) declare project_cursor CURSOR FOR SELECT ID FROM s_honor where unit_id is null and id in(select honor_id from s_honor_author where author_type='1') OPEN project_cursor FETCH NEXT FROM project_cursor INTO @honorId WHILE @@FETCH_STATUS=0 BEGIN declare member_cursor CURSOR for select author_unit_id from s_honor_author where honor_id = @honorId and author_type='1' order by order_id OPEN member_cursor FETCH NEXT FROM member_cursor INTO @unitId update s_honor set unit_id=@unitId where id=@honorId CLOSE member_cursor DEALLOCATE member_cursor print ' honorId :'+@honorId +' unitId:'+@unitId FETCH NEXT FROM project_cursor INTO @honorId END CLOSE project_cursor DEALLOCATE project_cursor select ','+name from s_perosn for xml path('')，