实验3 表数据的插入、修改和删除

--删除表Employees的第一行和表Salary的第一行
delete top(1) from Employees
go
delete top(1) from Salary
go

--向表Employees和表Salary中插入被删除的数据
insert into Employees Values('000001','王林','大专','1966-01-23',1,8,'中山路-1-508','83355668','2')
insert into Salary Values('000001',2100.08,123.09)

--假设有一个与Employees表结构相同的新表Employees2，可以用insert into语句将Employees表中数据添加到Employees2表中

--insert into Employees2 select * from Employees

--将编号为000001的职工收入改为2890
update Salary
set InCome = 2890
where EmployeeID = '000001'

--将所有职工收入增加100
update Salary
set InCome = InCome + 100

--删除Employees表中编号为000001的职工信息
delete from Employees
where EmployeeID = '000001'

--删除所有女性职工的信息
delete from Employees
where Sex = 0

--TRUNCATE TABLE语句在功能上与不带where字句的delete语句相同，二者均删除表中全部行。
--但TRUNCATE TABLE比delete速度快
--对于由外键约束引用的表，或者参与了索引视图的表，不能用TRUNCATE TABLE语句删除
--TRUNCATE TABLE Salary

--创建一个Employees3表
create table Employees3
( EmployeeID char(6) not null PRIMARY KEY,
Name char(10) not null,
Education char(4) not null,
Birthday date not null,
Sex bit not null default 1,
WorkYear tinyint null,
Address varchar(40) null,
PhoneNumber char(12) null,
DepartmentID char(3) not null
)
go

--使用merge语句使Employees3表中的数据和Employees表中的数据同步
merge into Employees3
using Employees on Employees3.EmployeeID = Employees.EmployeeID
when matched
then update set Employees3.Name = Employees.Name,
Employees3.Education = Employees.Education,
Employees3.Birthday = Employees.Birthday,
Employees3.Sex = Employees.Sex,
Employees3.WorkYear = Employees.WorkYear,
Employees3.Address = Employees.Address,
Employees3.PhoneNumber = Employees.PhoneNumber,
Employees3.DepartmentID = Employees.DepartmentID
when not matched
then insert values(Employees.EmployeeID,Employees.Name,Employees.Education,
Employees.Birthday,Employees.Sex,Employees.WorkYear,
Employees.Address,Employees.PhoneNumber,
Employees.DepartmentID)
when not matched by source
then delete;

Employees3表.png