数据增删改之SQLServer

1.增

-- insert into 表名(字段1，字段2，....) values()
insert into Department(DepartmentName,DepartmentRemark) values('市场部','公司市场部');
insert into Department(DepartmentName,DepartmentRemark) values('软件部','公司软件部');
insert into Department(DepartmentName,DepartmentRemark) values('企划部','公司企划部');
insert into Department(DepartmentName,DepartmentRemark) values('硬件部','公司硬件部');
insert into Department(DepartmentName,DepartmentRemark) values('测试部','公司测试部');
insert into Department(DepartmentName,DepartmentRemark) values('产品部','公司产品部');
insert into Department(DepartmentName,DepartmentRemark) values('实施部','公司实施部');

--insert into Department(DepartmentName,DepartmentRemark)
--values
--('市场部','公司市场部'),
--('软件部','公司软件部'),
--('企划部','公司企划部'),
--('硬件部','公司硬件部'),
--('测试部','公司测试部'),
--('产品部','公司产品部'),
--('实施部','公司实施部');

--insert into Department(DepartmentName,DepartmentRemark)
--select '市场部','公司市场部' union
--select '软件部','公司软件部' union
--select '企划部','公司企划部' union
--select '硬件部','公司硬件部' union
--select '测试部','公司测试部' union
--select '产品部','公司产品部' union
--select '实施部','公司实施部';

insert into [Rank](RankName,RankRemark) values('初级','初级'),('中级','中级'),('高级','高级');


insert into [People]
(
DepartmentId,
RankId,
PeopleName,
PeopleSex,
PeopleBirth,
PeopleSalary,
PeoplePhone,
PeopleAddress,
PeopleAddTime
)
values(18,1,'张三','男','1997-01-01',5000,'13580000000','广州',GETDATE());

select *from [People];

2.改

--修改
--update 表名 set 字段1=值1,字段2=值2 where 条件;

select *from People;

update People set PeopleSalary=15000 
where
DepartmentId=(select top 1 DepartmentId from Department where DepartmentName='测试部') 
and
PeopleSalary<10000;

3.删

--删除
--delete from 表名 where 条件
--删除员工表所有记录
delete from People;

注：

关于删除 drop,truncate,delete

drop table 表名   --删除表对象

truncate table 表名 --删除数据，表结构依然存在，重新建立表结构

delete from 表名 --删除所有数据