实验3 表数据插入、修改和删除 1. 准备数据样本
--向Departments表中插入数据 USE YGGL GO
INSERT INTO Departments Values('1','财务部',NULL); INSERT INTO Departments Values('2','人力资源部',NULL); INSERT INTO Departments Values('3','经理办公室',NULL); INSERT INTO Departments Values('4','研发部',NULL); INSERT INTO Departments Values('5','市场部',NULL);
--向Employees表中插入数据
INSERT INTO Employees VALUES('000001','王林','大专','1966-01-23',1,8,'中山路-1-508','83355668','2');
INSERT INTO Employees VALUES('010008','伍容华','本科','1976-03-28',1,3,'北京东路-2','83321321','1');
INSERT INTO Employees VALUES('020010','王向容','硕士','1982-12-09',1,2,'四牌楼-0-108','83792361','1');
INSERT INTO Employees VALUES('020018','李丽','大专','1960-07-30',0,6,'中山东路-2','83413301','1');
INSERT INTO Employees VALUES('102201','刘明','本科','1972-10-18',1,3,'虎距路-2','83606608','5');
INSERT INTO Employees VALUES('102208','朱俊','硕士','1965-09-28',1,2,'牌楼巷-3-106','84708817','5');
INSERT INTO Employees VALUES('108991','钟敏','硕士','1979-08-10',0,4,'中山路-3-105','83346722','5');
INSERT INTO Employees VALUES('111006','张石兵','本科','1974-10-01',1,1,'解放路-1-203','84563418','5');
INSERT INTO Employees VALUES('210678','林涛','大专','1977-04-02',1,2,'中山北路-35','83467336','3');
INSERT INTO Employees VALUES('302566','李玉珉','本科','1968-09-20',1,3,'热河路-3','58765991','4');
INSERT INTO Employees VALUES('308759','叶凡','本科','1978-11-18',1,2,'北京西路-7-52','83308901','4');
INSERT INTO Employees VALUES('504209','陈林琳','大专','1969-09-03',0,5,'汉中路-4-12','84468158','4');
--向Salary表中插入数据
INSERT INTO Salary VALUES('000001',2100.08,123.09); INSERT INTO Salary VALUES('010008',1582.62,88.03); INSERT INTO Salary VALUES('102201',2569.88,185.65); INSERT INTO Salary VALUES('111006',1987.01,79.58); INSERT INTO Salary VALUES('504209',2066.15,108.0);
INSERT INTO Salary VALUES('302566',2980.7,210.2); INSERT INTO Salary VALUES('108991',3259.98,281.52); INSERT INTO Salary VALUES('020010',2860.0,198.0); INSERT INTO Salary VALUES('020018',2347.68,180.0); INSERT INTO Salary VALUES('308759',2531.98,199.08); INSERT INTO Salary VALUES('210678',2240.0,121.0); INSERT INTO Salary VALUES('102208',1980.0,100.0);
2. 创建一个Employees3表，字段同Employees一致。使用Merge语句使Employees3表中的数据和Employees表中的数据同步。
USE YGGL GO
MERGE INTO Employees3
USING Employees ON Employees3.EmployeeID=Employees.EmployeeID --修改,两个表中都有的数据,刚Employees3根据Employess表进行修改 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
--添加,Employees表中有的,Employees3表中没有的,则向Employees表中添加 WHEN NOT MATCHED
THEN INSERT
VALUES(Employees.EmployeeID,Employees.Name,Employees.Education,Employees.Birthday,Employees.Sex,
Employees.WorkYear,Employees.Address,Employees.PhoneNumber,Employ--删除,Employees3表中有而源表Employees表中没有的,则将Employees3表中对应
ees.DepartmentID)
的记录删除 WHEN NOT MATCHED BY SOURCE
THEN DELETE;