sql server常用操作之增删改

if exists (select * from sysdatabases where name='w') --查询有没有数据库 w ,有则删除，无则不执行。
drop database w         --删除;数据库;名称.            
go

create database stuinfo  
go
use stuinfo;


CREATE TABLE student
(
    id  varchar(50)     primary key,
    name    varchar(50),
    age int,
    sex     char(2)
);


--插入数据
insert into student values('20111103090101','曹操',20,'男');
--批量插入
insert into student
select '20111103090102','张辽',20,'男' union
select  '20111103090103','徐晃',20,'男' union
select '20111103090104','郭嘉',20,'男' union
select '20111103090105','曹仁',20,'男'
--批量插入
insert into student values
('20111103090106','孙权',21,'男'),
('20111103090107','周瑜',22,'男'),
('20111103090108','鲁肃',21,'男'),
('20111103090109','张郃',23,'男');

select * from student2

--修改
update student set name='孙权1' from student where id='20111103090106'
--替换
update student set name=REPLACE(sex,'男','孙权') from student where id='20111103090106'
--把student表的内容复制(创建)到新表中
select * into student1 from student
select id into student2 from student

--把student表内容插入到其他表中
insert into student1 select * from student
insert into student2(id) select id from student

--删除student2表的内容
delete from student2 

select * from id1

--修改表名
sp_rename 'student2','id1'
--修改字段名
sp_rename 'student2.id','id1'

--修改字段属性
alter table id1 alter column id1 varchar(9)

--增加字段
alter table id1 add id2 int

--删除字段
alter table id1 drop column id2

--查表结构
sp_help id1

----读取数据库中的所有表信息（包括系统表）
select * from sysobjects 

--读取数据库中的用户建的表信息（包括系统表）,和n名字为id1的表信息
select * from sysobjects where xtype='u' and name='id1'