1、word數(shù)據(jù)庫實(shí)驗(yàn)報(bào)告 2012 / 2022 學(xué)年 第 二 學(xué)期學(xué) 號(hào) 姓 名 指導(dǎo)教師 成 績 一、數(shù)據(jù)庫原理第一次實(shí)驗(yàn)【一】實(shí)驗(yàn)內(nèi)容：數(shù)據(jù)庫表的建立與管理【二】、實(shí)驗(yàn)?zāi)康模簩W(xué)習(xí)數(shù)據(jù)庫及表的建立、刪除、更新等操作。注：本次實(shí)驗(yàn)題目，除了特殊要求，以T-SQL為主，并將所有語句標(biāo)注好題號(hào)，留存在查詢界面上，方便檢查?！救?、實(shí)驗(yàn)題目及其解答：1、創(chuàng)立一名為test的數(shù)據(jù)庫;Solution：CREATE DATABASE test2、在“test數(shù)據(jù)庫中新建一張部門表 “部門，輸入列：namechar，10位，IDchar，7位，manager (char,10位)各列均不能為空值。Solu

2、tion：use testCREATE TABLE 部門 (ID CHAR(7) NOT NULL, name CHAR(10) NOT NULL, manager CHAR(10) NOT NULL)結(jié)果： 3、在“test數(shù)據(jù)庫中新建一張員工表，命名為“員工。在表中輸入以下各列： namechar，10位，personIDchar，7位，Sexchar，7位，birthdaydatetime，deptIDchar，7位，各列均不能為空值。Solution：CREATE TABLE 員工 (name CHAR(10) NOT NULL, personID CHAR(7) NOT NULL,

3、sex CHAR(7) NOT NULL, birthday datetime NOT NULL, deptID CHAR(7) NOT NULL)結(jié)果： 4、修改表的操作練習(xí)：1將部門表中的列ID設(shè)為主鍵；2將員工表中personID設(shè)為主鍵，并將deptID設(shè)置為外鍵，關(guān)聯(lián)到部門表上的ID列；3在部門表中，添加列quantity(char, 5)； 4) 刪除員工表中的列sex； 5修改員工表中列name為varchar,8Solution：ALTER TABLE 部門 ADD CONSTRAINT C1 PRIMARY KEY(ID)ALTER TABLE 員工 ADD CONSTRAI

4、NT C2 PRIMARY KEY( personID )ALTER TABLE 員工 ADD CONSTRAINT C3 FOREIGN KEY(deptID) REFERENCES 部門(ID)ALTER TABLE 部門 ADD quantity CHAR(5)ALTER TABLE 員工 DROP COLUMN sexALTER TABLE 員工 ALTER COLUMN name VARCHAR(8)結(jié)果： 5、1在數(shù)據(jù)庫test中新建表scores,輸入以以下：ID char，8位 主鍵, C語言numeric(3,1) ，IT英語 numeric(3,1) ，數(shù)據(jù)庫 numeri

5、c(3,1) ，軟件根底 numeric(3,1) ，平均成績 。四門學(xué)科都不能為空，并且平均成績?yōu)樗拈T學(xué)科的平均分； 2為表scores中的四項(xiàng)成績添加default約束：使其默認(rèn)值為0； 3為表scores中的四項(xiàng)成績添加check約束：是每項(xiàng)成績?cè)?到100之間。Solution：CREATE TABLE scores (ID CHAR(8) PRIMARY KEY NOT NULL, C語言 NUMERIC(3,1) NOT NULL, IT英語 NUMERIC(3,1) NOT NULL, 數(shù)據(jù)庫 NUMERIC(3,1) NOT NULL, 軟件根底 NUMERIC(3,1) NO

6、T NULL, 平均成績 AS(C語言+IT英語+數(shù)據(jù)庫+軟件根底)/4)ALTER TABLE scores ADD CONSTRAINT C語言 DEFAULT'0'FOR C語言ALTER TABLE scores ADD CONSTRAINT IT英語 DEFAULT'0'FOR IT英語ALTER TABLE scores ADD CONSTRAINT 數(shù)據(jù)庫 DEFAULT'0'FOR 數(shù)據(jù)庫ALTER TABLE scores ADD CONSTRAINT 軟件根底 DEFAULT'0'FOR 軟件根底ALTER T

7、ABLE scores ADD CONSTRAINT CK1 CHECK(C語言>=0 AND C語言<=100);ALTER TABLE scores ADD CONSTRAINT CK2 CHECK(IT英語>=0 AND IT英語 <=100);ALTER TABLE scores ADD CONSTRAINT CK3 CHECK(數(shù)據(jù)庫>=0 AND 數(shù)據(jù)庫<=100);ALTER TABLE scores ADD CONSTRAINT CK4 CHECK(軟件根底>=0 AND 軟件根底<=100);結(jié)果： 6、刪除department

8、表，并刪除數(shù)據(jù)庫test。Solution：DROP Table 部門;DROP DATABASE test;【四】實(shí)驗(yàn)一完整的代碼：CREATE DATABASE test use testCREATE TABLE 部門 (ID CHAR(7) NOT NULL, name CHAR(10) NOT NULL, manager CHAR(10) NOT NULL)CREATE TABLE 員工 (name CHAR(10) NOT NULL, personID CHAR(7) NOT NULL, sex CHAR(7) NOT NULL, birthday datetime NOT NULL

9、, deptID CHAR(7) NOT NULL)ALTER TABLE 部門 ADD CONSTRAINT C1 PRIMARY KEY(ID)ALTER TABLE 員工 ADD CONSTRAINT C2 PRIMARY KEY( personID )ALTER TABLE 員工 ADD CONSTRAINT C3 FOREIGN KEY(deptID) REFERENCES 部門(ID)ALTER TABLE 部門 ADD quantity CHAR(5)ALTER TABLE 員工 DROP COLUMN sexALTER TABLE 員工 ALTER COLUMN name VA

10、RCHAR(8)CREATE TABLE scores (ID CHAR(8) PRIMARY KEY NOT NULL, C語言 NUMERIC(3,1) NOT NULL, IT英語 NUMERIC(3,1) NOT NULL, 數(shù)據(jù)庫 NUMERIC(3,1) NOT NULL, 軟件根底 NUMERIC(3,1) NOT NULL, 平均成績 AS(C語言+IT英語+數(shù)據(jù)庫+軟件根底)/4)ALTER TABLE scores ADD CONSTRAINT C語言 DEFAULT'0'FOR C語言ALTER TABLE scores ADD CONSTRAINT IT

11、英語 DEFAULT'0'FOR IT英語ALTER TABLE scores ADD CONSTRAINT 數(shù)據(jù)庫 DEFAULT'0'FOR 數(shù)據(jù)庫ALTER TABLE scores ADD CONSTRAINT 軟件根底 DEFAULT'0'FOR 軟件根底ALTER TABLE scores ADD CONSTRAINT CK1 CHECK(C語言>=0 AND C語言<=100);ALTER TABLE scores ADD CONSTRAINT CK2 CHECK(IT英語>=0 AND IT英語 <=100

12、);ALTER TABLE scores ADD CONSTRAINT CK3 CHECK(數(shù)據(jù)庫>=0 AND 數(shù)據(jù)庫<=100);ALTER TABLE scores ADD CONSTRAINT CK4 CHECK(軟件根底>=0 AND 軟件根底<=100);DROP Table 部門;DROP DATABASE test;二、數(shù)據(jù)庫原理第二次實(shí)驗(yàn)【一】、實(shí)驗(yàn)內(nèi)容：查詢技術(shù)的應(yīng)用【二】、實(shí)驗(yàn)?zāi)康模?、掌握SELECT語句的根本語法2、掌握連接查詢的表示3、掌握數(shù)據(jù)匯總的方法4、掌握SELECT語句的GROUP BY子句的作用和使用方法5、掌握SELECT語句的O

13、RDER BY 子句的作用和使用方法【三】、實(shí)驗(yàn)內(nèi)容1、創(chuàng)立員工管理數(shù)據(jù)庫compy及相應(yīng)的四張表：Employees：員工信息表Departments：部門信息表Salary：員工薪水信息表Purchase：員工購物信息表各表中屬性的定義可根據(jù)表中的記錄加以設(shè)定，均以編號(hào)為主鍵，例如可定義Departments表結(jié)構(gòu)如下：列名數(shù)據(jù)類型長度是否允許空值說明部門編號(hào)Char3否部門編號(hào) 主鍵部門名稱Char 20否部門名稱備注text16是備注向表中填入數(shù)據(jù)A、向Departments表中參加表2所示的記錄編號(hào)部門名稱備注1財(cái)務(wù)部null2人力資源部null3生產(chǎn)部null4采購部null5銷售

14、部nullSolution：CREATE DATABASE compyuse compyCREATE TABLE Employees( 編號(hào) CHAR(10) NOT NULL PRIMARY KEY, 姓名 VARCHAR(10) NOT NULL, 地址 CHAR(10) NOT NULL, CHAR(6), CHAR(11) NOT NULL, 部門號(hào) CHAR(3) NOT NULL, 出生日期 DATE NOT NULL, 性別 CHAR(2) NOT NULL);CREATE TABLE Departments( 部門編號(hào) CHAR(3) NOT NULL PRIMARY KEY,

15、 部門名稱 CHAR(20) NOT NULL, 備注 text);CREATE TABLE Salary( 編號(hào) CHAR(10) NOT NULL PRIMARY key, 收入 int default 0, 支出 int default 0);CREATE TABLE Purchase( 員工編號(hào) CHAR(10) NOT NULL, 商品 CHAR(4) NOT NULL , 購置數(shù)量 CHAR(4) NOT NULL, PRIMARY KEY(員工編號(hào),商品);INSERT INTO Departments VALUES('1','財(cái)務(wù)部',NULL)

16、;INSERT INTO Departments VALUES('2','人力資源部',NULL);INSERT INTO Departments VALUES('3','生產(chǎn)部',NULL) ;INSERT INTO Departments VALUES('4','采購部',NULL);INSERT INTO Departments VALUES('5','銷售部',NULL);B、向Employees表中參加表1所示的記錄編號(hào)姓名地址 部門號(hào)出生日期性別00000

17、1張曉強(qiáng)上海230027333355621956-1-11010008柳樹合肥230027313135911966-1-11002001許靜北京230026313235911972-1-11020018李可武漢230026369356411950-1-10102201張三豐西安123456363534651962-1-10102208白眉南京123456345896551955-1-11108991胡適昆明123456456892131969-1-11111006成吉思汗海口123456345814551964-1-11210678蕭然成都123456345456531967-1-11Solu

18、tion：INSERT INTO Employees VALUES('000001','張曉強(qiáng)','上海','230027','3333556','2','1956-1-1','1')INSERT INTO Employees VALUES('010008','柳樹','合肥','230027','3131359','1','1966-1-1','1

19、')INSERT INTO Employees VALUES('002001','許靜','北京','230026','3132359','1','1972-1-1','1')INSERT INTO Employees VALUES('020018','李可','武漢','230026','3693564','1','1950-1-1','0

20、')INSERT INTO Employees VALUES('102201','張三豐','西安','123456','3635364','5','1962-1-1','0')INSERT INTO Employees VALUES('102208','白眉','南京','123456','3458965','5','1955-1-1','

21、1')INSERT INTO Employees VALUES('108991','胡適','昆明','123456','4568921','3','1969-1-1','1')INSERT INTO Employees VALUES('111006','成吉思汗','海口','123456','3458145','5','1964-1-1',

22、9;1')INSERT INTO Employees VALUES('210678','蕭然','成都','123456','3454565','3','1967-1-1','1');結(jié)果： C、向Salary表中參加表3所示的記錄編號(hào)收入支出00000121001230100081582880020012569185020018198779102201206610810220829802101089913259281111006286019821067823

23、47180Solution：INSERT INTO Salary VALUES('000001','2100','123')INSERT INTO Salary VALUES('010008','1582','88')INSERT INTO Salary VALUES('002001','2569','185')INSERT INTO Salary VALUES('020018','1987','79')

24、INSERT INTO Salary VALUES('102201','2066','108')INSERT INTO Salary VALUES('102208','2980','210')INSERT INTO Salary VALUES('108991','3259','281')INSERT INTO Salary VALUES('111006','2860','198')INSERT INTO

25、Salary VALUES('210678','2347','180');結(jié)果： D、向Purchase表中參加表4所示的記錄員工編號(hào)商品購置數(shù)量000001013000001031002001015020018036108991111000001113020018015202278021020018112Solution：INSERT INTO Purchase VALUES('000001','01','3');INSERT INTO Purchase VALUES('000001

26、9;,'03','1');INSERT INTO Purchase VALUES('002001','01','5');INSERT INTO Purchase VALUES('020018','03','6');INSERT INTO Purchase VALUES('108991','11','1');INSERT INTO Purchase VALUES('000001','11',

27、'3');INSERT INTO Purchase VALUES('020018','01','5');INSERT INTO Purchase VALUES('202278','02','1');INSERT INTO Purchase VALUES('020018','11','2');2、簡單查詢 1查詢1956，1962，1964年出生的員工記錄； 2查詢性別為1的員工記錄； 3查詢名字中包含白字的所有雇員信息； 4查詢不姓李或

28、者胡，并且名字為三個(gè)字的雇員的記錄注：“姓名列的數(shù)據(jù)類型必須定義為varchar，想想為什么？ 。Solution：SELECT * FROM EmployeesWHERE 出生日期 LIKE '%1956%' or 出生日期 LIKE '%1962%' or 出生日期 LIKE '%1964%' SELECT * FROM Employees WHERE 性別='1'SELECT * FROM Employees WHERE 姓名 LIKE '%白%'SELECT * FROM Employees WHERE 姓

29、名 not LIKE'李%' AND 姓名 not LIKE'胡%' AND 姓名 LIKE'_'結(jié) 果：3、連接查詢的使用1查詢每個(gè)雇員的情況以及其薪水情況2查詢收入高于編號(hào)為102201的員工收入的職工信息Solution：SELECT Employees.*,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.編號(hào)=Salary.編號(hào)SELECT a.*,b.收入 FROM Employees AS a JOIN salary AS b ON a.編號(hào)=b.編號(hào)WHERE 收入&g

30、t;( SELECT 收入 FROM Salary WHERE 編號(hào)='102201');結(jié)果：4數(shù)據(jù)匯總1求財(cái)務(wù)部員工的平均凈收入2求財(cái)務(wù)部雇員的總?cè)藬?shù)Solution：SELECT AVG(b.收入-b.支出) AS 財(cái)務(wù)部員工的平均凈收入 FROM Employees AS a INNER JOIN Salary AS b ON a.編號(hào)=b.編號(hào)WHERE 部門號(hào)='1'SELECT COUNT(編號(hào))AS 財(cái)務(wù)部雇員的總?cè)藬?shù) FROM Employees WHERE 部門號(hào)='1'結(jié)果：5GROUP BY ，ORDER BY 子句的使用

31、1求各部門的雇員數(shù)2將各雇員的情況按薪水由低到高排列Solution：SELECT b.部門名稱 ,COUNT(a.編號(hào)) AS 部門人數(shù)FROM Employees AS a JOIN Departments AS b ON a.部門號(hào)=b.部門編號(hào) group by 部門名稱;SELECT a.*,b.收入 FROM Employees AS a INNER JOIN Salary AS b ON a.編號(hào)=b.編號(hào) order by 收入;結(jié)果：6、選做題查找比所有財(cái)務(wù)部的員工的收入都高的員工的姓名；Solution：SELECT 姓名 FROM Employees WHERE 編號(hào) I

32、N (SELECT 編號(hào) FROM Salary WHERE 收入>All(SELECT 收入 FROM Salary WHERE 編號(hào) IN(SELECT 編號(hào) FROM Employees WHERE 部門號(hào)='1')結(jié)果： 【四】實(shí)驗(yàn)二完整的代碼： CREATE DATABASE compyuse compyCREATE TABLE Employees( 編號(hào) CHAR(10) NOT NULL PRIMARY KEY, 姓名 VARCHAR(10) NOT NULL, 地址 CHAR(10) NOT NULL, CHAR(6), CHAR(11) NOT NULL

33、, 部門號(hào) CHAR(3) NOT NULL, 出生日期 DATE NOT NULL, 性別 CHAR(2) NOT NULL);CREATE TABLE Departments( 部門編號(hào) CHAR(3) NOT NULL PRIMARY KEY, 部門名稱 CHAR(20) NOT NULL, 備注 text);CREATE TABLE Salary( 編號(hào) CHAR(10) NOT NULL PRIMARY key, 收入 int default 0, 支出 int default 0);CREATE TABLE Purchase( 員工編號(hào) CHAR(10) NOT NULL, 商品

34、CHAR(4) NOT NULL , 購置數(shù)量 CHAR(4) NOT NULL, PRIMARY KEY(員工編號(hào),商品);INSERT INTO Departments VALUES('1','財(cái)務(wù)部',NULL);INSERT INTO Departments VALUES('2','人力資源部',NULL);INSERT INTO Departments VALUES('3','生產(chǎn)部',NULL) ;INSERT INTO Departments VALUES('4',&#

35、39;采購部',NULL);INSERT INTO Departments VALUES('5','銷售部',NULL);INSERT INTO Employees VALUES('000001','張曉強(qiáng)','上海','230027','3333556','2','1956-1-1','1')INSERT INTO Employees VALUES('010008','柳樹','合肥&#

36、39;,'230027','3131359','1','1966-1-1','1')INSERT INTO Employees VALUES('002001','許靜','北京','230026','3132359','1','1972-1-1','1')INSERT INTO Employees VALUES('020018','李可','武漢&#

37、39;,'230026','3693564','1','1950-1-1','0')INSERT INTO Employees VALUES('102201','張三豐','西安','123456','3635364','5','1962-1-1','0')INSERT INTO Employees VALUES('102208','白眉','南京&

38、#39;,'123456','3458965','5','1955-1-1','1')INSERT INTO Employees VALUES('108991','胡適','昆明','123456','4568921','3','1969-1-1','1')INSERT INTO Employees VALUES('111006','成吉思汗','海

39、口','123456','3458145','5','1964-1-1','1')INSERT INTO Employees VALUES('210678','蕭然','成都','123456','3454565','3','1967-1-1','1');INSERT INTO Salary VALUES('000001','2100','1

40、23')INSERT INTO Salary VALUES('010008','1582','88')INSERT INTO Salary VALUES('002001','2569','185')INSERT INTO Salary VALUES('020018','1987','79')INSERT INTO Salary VALUES('102201','2066','108')INSERT

41、 INTO Salary VALUES('102208','2980','210')INSERT INTO Salary VALUES('108991','3259','281')INSERT INTO Salary VALUES('111006','2860','198')INSERT INTO Salary VALUES('210678','2347','180');INSERT INTO Purch

42、ase VALUES('000001','01','3');INSERT INTO Purchase VALUES('000001','03','1');INSERT INTO Purchase VALUES('002001','01','5');INSERT INTO Purchase VALUES('020018','03','6');INSERT INTO Purchase VALUES('

43、108991','11','1');INSERT INTO Purchase VALUES('000001','11','3');INSERT INTO Purchase VALUES('020018','01','5');INSERT INTO Purchase VALUES('202278','02','1');INSERT INTO Purchase VALUES('020018','

44、;11','2');SELECT * FROM EmployeesWHERE 出生日期 LIKE '%1956%' or 出生日期 LIKE '%1962%' or 出生日期 LIKE '%1964%' SELECT * FROM Employees WHERE 性別='1'SELECT * FROM Employees WHERE 姓名 LIKE '%白%'SELECT * FROM Employees WHERE 姓名 not LIKE'李%' AND 姓名 not LI

45、KE'胡%' AND 姓名 LIKE'_'SELECT Employees.*,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.編號(hào)=Salary.編號(hào)SELECT a.*,b.收入 FROM Employees AS a JOIN salary AS b ON a.編號(hào)=b.編號(hào)WHERE 收入>( SELECT 收入 FROM Salary WHERE 編號(hào)='102201');SELECT AVG(b.收入-b.支出) AS 財(cái)務(wù)部員工的平均凈收入 FROM Employ

46、ees AS a INNER JOIN Salary AS b ON a.編號(hào)=b.編號(hào)WHERE 部門號(hào)='1'SELECT COUNT(編號(hào))AS 財(cái)務(wù)部雇員的總?cè)藬?shù) FROM Employees WHERE 部門號(hào)='1'SELECT b.部門名稱 ,COUNT(a.編號(hào)) AS 部門人數(shù)FROM Employees AS a JOIN Departments AS b ON a.部門號(hào)=b.部門編號(hào) group by 部門名稱;SELECT a.*,b.收入 FROM Employees AS a INNER JOIN Salary AS b ON a.

47、編號(hào)=b.編號(hào) order by 收入;SELECT 姓名 FROM Employees WHERE 編號(hào) IN (SELECT 編號(hào) FROM Salary WHERE 收入>All(SELECT 收入 FROM Salary WHERE 編號(hào) IN(SELECT 編號(hào) FROM Employees WHERE 部門號(hào)='1')三、數(shù)據(jù)庫原理第三次實(shí)驗(yàn)【一】、實(shí)驗(yàn)內(nèi)容：數(shù)據(jù)庫的表中數(shù)據(jù)的操作?！径?、實(shí)驗(yàn)?zāi)康模?、學(xué)習(xí)SQL語句進(jìn)行表中的數(shù)據(jù)的插入、更新和刪除；2、學(xué)會(huì)含有子查詢語句的數(shù)據(jù)更新操作；2、理解表中列的屬性對(duì)表中數(shù)據(jù)的影響?！救?、實(shí)驗(yàn)準(zhǔn)備試用上次實(shí)驗(yàn)創(chuàng)立的

48、員工管理數(shù)據(jù)庫compy及相應(yīng)的四張表具體見實(shí)驗(yàn)2：Employees：員工信息表Departments：部門信息表Salary：員工薪水信息表Purchase：員工購物信息表【四】、實(shí)驗(yàn)題目及其解答：1、 向Employees表中參加一列：年齡，其值為“當(dāng)前時(shí)間與“出生日期差值提示：年齡的計(jì)算可以使用getdate及year兩個(gè)日期函數(shù)，具體使用參考聯(lián)機(jī)叢書Transact SQL參考。Solutionuse compyALTER TABLE Employees ADD 年齡 AS datediff (year,出生日期,getdate()；2、數(shù)據(jù)更新：1將編號(hào)為102201的雇員地址改為

49、“蘇州；2將編號(hào)為102208的雇員收入降低10；3更改salary表，如果收入和支出的差值小于2000，那么將收入提高1000；Solution：SELECT * FROM EmployeesUPDATE Employees SET 地址='蘇州' WHERE 編號(hào)='102201'SELECT * FROM EmployeesUPDATE Salary SET 收入=收入*0.9 WHERE 編號(hào)='102208'SELECT * FROM SalaryUPDATE Salary SET 收入=收入+1000WHERE abs(收入-支出)&

50、lt;2000;結(jié)果： 3、 使用SELECTINTO進(jìn)行多行插入： 將每個(gè)部門的編號(hào)，名稱，雇員數(shù)，插入到新表DepartmentStatic中。Solution：SELECT * FROM SalarySELECT Departments.部門編號(hào),Departments.部門名稱,COUNT(Employees.編號(hào)) AS 部門人數(shù) INTO DepartmentStatic FROM Departments INNER JOIN Employees on Departments.部門編號(hào)=Employees.部門號(hào) GROUP BY 部門名稱,部門編號(hào) SELECT * FROM D

51、epartmentStatic; 結(jié)果 4、含子查詢的數(shù)據(jù)更新1將地址為合肥的雇員收入增加52將收入小于平均收入的雇員的收入增加500元3將財(cái)務(wù)部的雇員收入減少100Solution：UPDATE Salary SET 收入=收入*1.05 WHERE 編號(hào) IN (SELECT 編號(hào) FROM Employees WHERE 地址='合肥')SELECT Employees.編號(hào),Employees.姓名,Employees.地址,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.編號(hào)=Salary.編號(hào)UPDAT

52、E Salary SET 收入=收入+500 WHERE 收入<(SELECT AVG(收入) FROM Salary)SELECT * FROM SalaryUPDATE Salary SET 收入=收入-100WHERE 編號(hào) IN (SELECT 編號(hào) FROM Employees WHERE 部門號(hào)=(SELECT 部門編號(hào) FROM Departments WHERE 部門名稱='財(cái)務(wù)部'); 結(jié)果： 5、刪除刪除DepartmentStatic中所有數(shù)據(jù)Solution：SELECT Employees.編號(hào),Employees.姓名,Employees.地址

53、,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.編號(hào)=Salary.編號(hào)TRUNCATE TABLE DepartmentStatic;結(jié)果： 6、選做1將總收入大于6000的部門中的雇員收入減少52刪除收入大于平均收入的雇員的信息，包括Employees, Salary中相關(guān)的數(shù)據(jù)Solution：SELECT * FROM DepartmentStaticUPDATE Salary SET 收入=收入*0.95WHERE 編號(hào) IN (SELECT 編號(hào) FROM Employees WHERE 部門號(hào) IN(SELECT

54、b.部門號(hào)FROM Salary AS a INNER JOIN Employees AS b ON a.編號(hào)=b.編號(hào)GROUP BY 部門號(hào)HAVING SUM(收入)>6000)SELECT Employees.編號(hào),Employees.姓名,Employees.部門號(hào),Salary.收入,Salary.支出FROM Employees INNER JOIN Salary ON Employees.編號(hào)=Salary.編號(hào)DELETE FROM Employees WHERE 編號(hào) IN ( SELECT 編號(hào) FROM Salary WHERE 收入>(SELECT AVG

55、(收入) FROM Salary)DELETE FROM SalaryWHERE 編號(hào) IN ( SELECT 編號(hào) FROM Salary WHERE 收入>(SELECT AVG(收入) FROM Salary)SELECT * FROM EmployeesSELECT * FROM Salary;結(jié)果： 【四】實(shí)驗(yàn)三完整的代碼：use compyALTER TABLE Employees ADD 年齡 AS datediff (year,出生日期,getdate()SELECT * FROM EmployeesUPDATE Employees SET 地址='蘇州'

56、 WHERE 編號(hào)='102201'SELECT * FROM EmployeesUPDATE Salary SET 收入=收入*0.9 WHERE 編號(hào)='102208'SELECT * FROM SalaryUPDATE Salary SET 收入=收入+1000WHERE abs(收入-支出)<2000SELECT * FROM SalarySELECT Departments.部門編號(hào),Departments.部門名稱,COUNT(Employees.編號(hào)) AS 部門人數(shù) INTO DepartmentStatic FROM Department

57、s INNER JOIN Employees on Departments.部門編號(hào)=Employees.部門號(hào) GROUP BY 部門名稱,部門編號(hào) SELECT * FROM DepartmentStaticUPDATE Salary SET 收入=收入*1.05 WHERE 編號(hào) IN (SELECT 編號(hào) FROM Employees WHERE 地址='合肥')SELECT Employees.編號(hào),Employees.姓名,Employees.地址,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.編號(hào)=

58、Salary.編號(hào)UPDATE Salary SET 收入=收入+500 WHERE 收入<(SELECT AVG(收入) FROM Salary)SELECT * FROM SalaryUPDATE Salary SET 收入=收入-100WHERE 編號(hào) IN (SELECT 編號(hào) FROM Employees WHERE 部門號(hào)=(SELECT 部門編號(hào) FROM Departments WHERE 部門名稱='財(cái)務(wù)部')SELECT Employees.編號(hào),Employees.姓名,Employees.地址,Salary.收入 FROM Employees IN

59、NER JOIN Salary ON Employees.編號(hào)=Salary.編號(hào)TRUNCATE TABLE DepartmentStatic SELECT * FROM DepartmentStaticUPDATE Salary SET 收入=收入*0.95WHERE 編號(hào) IN (SELECT 編號(hào) FROM Employees WHERE 部門號(hào) IN(SELECT b.部門號(hào)FROM Salary AS a INNER JOIN Employees AS b ON a.編號(hào)=b.編號(hào)GROUP BY 部門號(hào)HAVING SUM(收入)>6000)SELECT Employees

60、.編號(hào),Employees.姓名,Employees.部門號(hào),Salary.收入,Salary.支出FROM Employees INNER JOIN Salary ON Employees.編號(hào)=Salary.編號(hào)DELETE FROM Employees WHERE 編號(hào) IN ( SELECT 編號(hào) FROM Salary WHERE 收入>(SELECT AVG(收入) FROM Salary)DELETE FROM SalaryWHERE 編號(hào) IN ( SELECT 編號(hào) FROM Salary WHERE 收入>(SELECT AVG(收入) FROM Salary)SELECT * FROM EmployeesSELECT * FROM Salary四、數(shù)據(jù)庫原理第四次實(shí)驗(yàn)【一】、實(shí)驗(yàn)內(nèi)容：視圖練習(xí)【二】、實(shí)驗(yàn)?zāi)康模?、練習(xí)視圖的建立與使用；2、理解視圖的優(yōu)點(diǎn)和作用?！救?、實(shí)驗(yàn)準(zhǔn)備：創(chuàng)立員工管理數(shù)據(jù)庫company及相應(yīng)的四張表：Em