常用sql语句

2019独角兽企业重金招聘Python工程师标准>>>

添加一列 
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('')， 

转载于:https://my.oschina.net/u/2395098/blog/534929