实验3、4-表数据插入、修改、删除和数据库的查询和视图.docxVIP

表数据插入、修改、删除和数据库的查询和视图 ///实验3 INSERT INTO Employees VALUES (000001,王林,大专,1966-02-10,1,8,中山路-1-5082), (010008,吴华,本科,1976-05-01,1,3,北京东路-21), (020010,王荣,硕士,1983-07-22,1,2,四牌楼-0-1081), (020018,李丽,大专,1960-08-11,0,6,中山东路-21), (102201,刘明,本科,1972-05-01,1,3,虎踞路-25), (102208,朱俊,硕士,1965-01-30,1,2,牌楼巷-3-1065); INSERT INTO Departments VALUES (1,财务部,NULL), (2,人力资源部,NULL), (3,经理办公室,NULL), (4,研发部,NULL), (5,市场部,NULL); INSERT INTO Salary VALUES (000001,2100.8,123.09), (010008,1582.62,88.03), (020010,2860.0,198.0), (020018,2347.68,180.0), (102201,2569.88,185.65), (102208,1980.0,100.0); USE YGGL GO INSERT INTO Employees VALUES (000001,王林,大专,1966-02-10,1,8,中山路-1-5082) INSERT INTO Salary(EmployeeID,InCome,OutCome) VALUES(000001,2100.8,123.09) create TABLE Employees2 ( 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 ) INSERT INTO Employees2 SELECT * FROM Employees UPDATE Salary SET InCome=2980 WHERE EmployeeID=000001 UPDATE Salary SET InCome=InCome+100; DELETE FROM Employees WHERE Education=000001 DELETE FROM Employees WHERE Sex=0 TRUNCATE TABLE Salary 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 ) 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, Employe