南邮数据库实验报告

1、.数据库实验报告 （ 2012 / 2013 学年 第 二 学期）学 号 姓 名 指导教师 成 绩 一、数据库原理第一次实验【一】实验内容：数据库表的建立与管理【二】、实验目的：学习数据库及表的建立、删除、更新等操作。注：本次实验题目，除了特殊要求，以T-SQL为主，并将所有语句标注好题号，留存在查询界面上，方便检查。【三】、实验题目及其解答：1、创建一名为test的数据库;Solution：CREATE DATABASE test2、在“test”数据库中新建一张部门表 “部门”，输入列：name（char，10位），ID（char，7位），manager (char,10位)各列均不能为空

2、值。Solution：use testCREATE TABLE 部门 (ID CHAR(7) NOT NULL, name CHAR(10) NOT NULL, manager CHAR(10) NOT NULL)结果： 3、在“test”数据库中新建一张员工表，命名为“员工”。在表中输入以下各列： name（char，10位），personID（char，7位），Sex（char，7位），birthday（datetime），deptID（char，7位），各列均不能为空值。Solution：CREATE TABLE 员工 (name CHAR(10) NOT NULL, personID

3、CHAR(7) NOT NULL, sex CHAR(7) NOT NULL, birthday datetime NOT NULL, deptID CHAR(7) NOT NULL)结果： 4、修改表的操作练习：1）将部门表中的列ID设为主键；2）将员工表中personID设为主键，并将deptID设置为外键，关联到部门表上的ID列；3）在部门表中，添加列quantity(char, 5)； 4) 删除员工表中的列sex； 5）修改员工表中列name为（varchar,8）Solution：ALTER TABLE 部门 ADD CONSTRAINT C1 PRIMARY KEY(ID)ALT

4、ER 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 VARCHAR(8)结果： 5、1）在数据库test中新建表scores,输入以下列：ID （char，8位） 主键, C语言numeric(3,1) ，IT

5、英语 numeric(3,1) ，数据库 numeric(3,1) ，软件基础 numeric(3,1) ，平均成绩 。四门学科都不能为空，并且平均成绩为四门学科的平均分； 2）为表scores中的四项成绩添加default约束：使其默认值为0； 3）为表scores中的四项成绩添加check约束：是每项成绩在0到100之间。Solution：CREATE TABLE scores (ID CHAR(8) PRIMARY KEY NOT NULL, C语言 NUMERIC(3,1) NOT NULL, IT英语 NUMERIC(3,1) NOT NULL, 数据库 NUMERIC(3,1) N

6、OT NULL, 软件基础 NUMERIC(3,1) NOT NULL, 平均成绩 AS(C语言+IT英语+数据库+软件基础)/4)ALTER TABLE scores ADD CONSTRAINT C语言 DEFAULT0FOR C语言ALTER TABLE scores ADD CONSTRAINT IT英语 DEFAULT0FOR IT英语ALTER TABLE scores ADD CONSTRAINT 数据库 DEFAULT0FOR 数据库ALTER TABLE scores ADD CONSTRAINT 软件基础 DEFAULT0FOR 软件基础ALTER TABLE scores

7、 ADD CONSTRAINT CK1 CHECK(C语言=0 AND C语言=0 AND IT英语 =0 AND 数据库=0 AND 软件基础=0 AND C语言=0 AND IT英语 =0 AND 数据库=0 AND 软件基础( SELECT 收入 FROM Salary WHERE 编号=102201);结果：4数据汇总（1）求财务部员工的平均净收入（2）求财务部雇员的总人数Solution：SELECT AVG(b.收入-b.支出) AS 财务部员工的平均净收入 FROM Employees AS a INNER JOIN Salary AS b ON a.编号=b.编号WHERE 部

8、门号=1;SELECT COUNT(编号)AS 财务部雇员的总人数 FROM Employees WHERE 部门号=1;结果：5GROUP BY ，ORDER BY 子句的使用（1）求各部门的雇员数（2）将各雇员的情况按薪水由低到高排列Solution：SELECT b.部门名称 ,COUNT(a.编号) AS 部门人数FROM Employees AS a JOIN Departments AS b ON a.部门号=b.部门编号 group by 部门名称;SELECT a.*,b.收入 FROM Employees AS a INNER JOIN Salary AS b ON a.编号

9、=b.编号 order by 收入;结果：6、（选做题）查找比所有财务部的员工的收入都高的员工的姓名；Solution：SELECT 姓名 FROM Employees WHERE 编号 IN (SELECT 编号 FROM Salary WHERE 收入All(SELECT 收入 FROM Salary WHERE 编号 IN(SELECT 编号 FROM Employees WHERE 部门号=1)结果： 【四】实验二完整的代码： CREATE DATABASE compyuse compyCREATE TABLE Employees( 编号 CHAR(10) NOT NULL PRIMA

10、RY KEY, 姓名 VARCHAR(10) NOT NULL, 地址 CHAR(10) NOT NULL, 邮编 CHAR(6), 电话 CHAR(11) NOT NULL, 部门号 CHAR(3) NOT NULL, 出生日期 DATE NOT NULL, 性别 CHAR(2) NOT NULL);CREATE TABLE Departments( 部门编号 CHAR(3) NOT NULL PRIMARY KEY, 部门名称 CHAR(20) NOT NULL, 备注 text);CREATE TABLE Salary( 编号 CHAR(10) NOT NULL PRIMARY key,

11、 收入 int default 0, 支出 int default 0);CREATE TABLE Purchase( 员工编号 CHAR(10) NOT NULL, 商品 CHAR(4) NOT NULL , 购买数量 CHAR(4) NOT NULL, PRIMARY KEY(员工编号,商品);INSERT INTO Departments VALUES(1,财务部,NULL);INSERT INTO Departments VALUES(2,人力资源部,NULL);INSERT INTO Departments VALUES(3,生产部,NULL) ;INSERT INTO Depart

12、ments VALUES(4,采购部,NULL);INSERT INTO Departments VALUES(5,销售部,NULL);INSERT INTO Employees VALUES(000001,张晓强,上海,230027,3333556,2,1956-1-1,1)INSERT INTO Employees VALUES(010008,柳树,合肥,230027,3131359,1,1966-1-1,1)INSERT INTO Employees VALUES(002001,许静,北京,230026,3132359,1,1972-1-1,1)INSERT INTO Employees

13、 VALUES(020018,李可,武汉,230026,3693564,1,1950-1-1,0)INSERT INTO Employees VALUES(102201,张三丰,西安,123456,3635364,5,1962-1-1,0)INSERT INTO Employees VALUES(102208,白眉,南京,123456,3458965,5,1955-1-1,1)INSERT INTO Employees VALUES(108991,胡适,昆明,123456,4568921,3,1969-1-1,1)INSERT INTO Employees VALUES(111006,成吉思汗

14、,海口,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,123)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(10220

15、1,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 Salary VALUES(210678,2347,180);INSERT INTO Purchase VALUES(000001,01,3);INSERT INTO Purchase VALUES(000001,03,1);INSERT INTO Purchase VALUES(002001,01,

16、5);INSERT INTO Purchase VALUES(020018,03,6);INSERT INTO Purchase VALUES(108991,11,1);INSERT INTO Purchase VALUES(000001,11,3);INSERT INTO Purchase VALUES(020018,01,5);INSERT INTO Purchase VALUES(201678,02,1);INSERT INTO Purchase VALUES(020018,11,2);SELECT * FROM EmployeesWHERE 出生日期 LIKE %1956% or 出生

17、日期 LIKE %1962% or 出生日期 LIKE %1964%; SELECT * FROM Employees WHERE 性别=1;SELECT * FROM Employees WHERE 姓名 LIKE %白%;SELECT * FROM Employees WHERE 姓名 not LIKE李% AND 姓名 not LIKE胡% AND 姓名 LIKE_;SELECT Employees.*,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.编号=Salary.编号SELECT a.*,b.收入 FROM Empl

18、oyees AS a JOIN salary AS b ON a.编号=b.编号WHERE 收入( SELECT 收入 FROM Salary WHERE 编号=102201);SELECT AVG(b.收入-b.支出) AS 财务部员工的平均净收入 FROM Employees AS a INNER JOIN Salary AS b ON a.编号=b.编号WHERE 部门号=1;SELECT COUNT(编号)AS 财务部雇员的总人数 FROM Employees WHERE 部门号=1;SELECT b.部门名称 ,COUNT(a.编号) AS 部门人数FROM Employees AS

19、 a JOIN Departments AS b ON a.部门号=b.部门编号 group by 部门名称;SELECT a.*,b.收入 FROM Employees AS a INNER JOIN Salary AS b ON a.编号=b.编号 order by 收入;SELECT 姓名 FROM Employees WHERE 编号 IN (SELECT 编号 FROM Salary WHERE 收入All(SELECT 收入 FROM Salary WHERE 编号 IN(SELECT 编号 FROM Employees WHERE 部门号=1)三、数据库原理第三次实验【一】、实验

20、内容：数据库的表中数据的操作。【二】、实验目的：1、学习SQL语句进行表中的数据的插入、更新和删除；2、学会含有子查询语句的数据更新操作；2、理解表中列的属性对表中数据的影响。【三】、实验准备试用上次实验创建的员工管理数据库（compy）及相应的四张表（具体见实验2）：Employees：员工信息表Departments：部门信息表Salary：员工薪水信息表Purchase：员工购物信息表【四】、实验题目及其解答：1、 向Employees表中加入一列：年龄，其值为“当前时间”与“出生日期”差值（提示：年龄的计算可以使用getdate及year两个日期函数，具体使用参考联机丛书Transac

21、t SQL参考）。Solutionuse compyALTER TABLE Employees ADD 年龄 AS datediff (year,出生日期,getdate()；2、数据更新：1）将编号为102201的雇员地址改为“苏州”；2）将编号为102208的雇员收入降低10；3）更改salary表，如果收入和支出的差值小于2000，则将收入提高1000；Solution：SELECT * FROM EmployeesUPDATE Employees SET 地址=苏州 WHERE 编号=102201SELECT * FROM EmployeesUPDATE Salary SET 收入=收

22、入*0.9 WHERE 编号=102208SELECT * FROM SalaryUPDATE Salary SET 收入=收入+1000WHERE abs(收入-支出)2000;结果： 3、 使用SELECTINTO进行多行插入： 将每个部门的编号，名称，雇员数，插入到新表DepartmentStatic中。Solution：SELECT * FROM SalarySELECT Departments.部门编号,Departments.部门名称,COUNT(Employees.编号) AS 部门人数 INTO DepartmentStatic FROM Departments INNER J

23、OIN Employees on Departments.部门编号=Employees.部门号 GROUP BY 部门名称,部门编号 SELECT * FROM DepartmentStatic; 结果 4、含子查询的数据更新（1）将地址为合肥的雇员收入增加5（2）将收入小于平均收入的雇员的收入增加500元（3）将财务部的雇员收入减少100Solution：UPDATE Salary SET 收入=收入*1.05 WHERE 编号 IN (SELECT 编号 FROM Employees WHERE 地址=合肥)SELECT Employees.编号,Employees.姓名,Employee

24、s.地址,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.编号=Salary.编号UPDATE Salary SET 收入=收入+500 WHERE 收入6000)SELECT Employees.编号,Employees.姓名,Employees.部门号,Salary.收入,Salary.支出FROM Employees INNER JOIN Salary ON Employees.编号=Salary.编号DELETE FROM Employees WHERE 编号 IN ( SELECT 编号 FROM Salary WHER

25、E 收入(SELECT AVG(收入) FROM Salary)DELETE FROM SalaryWHERE 编号 IN ( SELECT 编号 FROM Salary WHERE 收入(SELECT AVG(收入) FROM Salary)SELECT * FROM EmployeesSELECT * FROM Salary;结果： 【四】实验三完整的代码：use compyALTER TABLE Employees ADD 年龄 AS datediff (year,出生日期,getdate()SELECT * FROM EmployeesUPDATE Employees SET 地址=苏

26、州 WHERE 编号=102201SELECT * FROM EmployeesUPDATE Salary SET 收入=收入*0.9 WHERE 编号=102208SELECT * FROM SalaryUPDATE Salary SET 收入=收入+1000WHERE abs(收入-支出)2000SELECT * FROM SalarySELECT Departments.部门编号,Departments.部门名称,COUNT(Employees.编号) AS 部门人数 INTO DepartmentStatic FROM Departments INNER JOIN Employees

27、on Departments.部门编号=Employees.部门号 GROUP BY 部门名称,部门编号 SELECT * FROM DepartmentStaticUPDATE Salary SET 收入=收入*1.05 WHERE 编号 IN (SELECT 编号 FROM Employees WHERE 地址=合肥)SELECT Employees.编号,Employees.姓名,Employees.地址,Salary.收入 FROM Employees INNER JOIN Salary ON Employees.编号=Salary.编号UPDATE Salary SET 收入=收入+

28、500 WHERE 收入6000)SELECT Employees.编号,Employees.姓名,Employees.部门号,Salary.收入,Salary.支出FROM Employees INNER JOIN Salary ON Employees.编号=Salary.编号DELETE FROM Employees WHERE 编号 IN ( SELECT 编号 FROM Salary WHERE 收入(SELECT AVG(收入) FROM Salary)DELETE FROM SalaryWHERE 编号 IN ( SELECT 编号 FROM Salary WHERE 收入(SE

29、LECT AVG(收入) FROM Salary)SELECT * FROM EmployeesSELECT * FROM Salary四、数据库原理第四次实验【一】、实验内容：视图练习【二】、实验目的：1、练习视图的建立与使用；2、理解视图的优点和作用。【三】、实验准备：创建员工管理数据库（company）及相应的四张表：Employees：员工信息表Departments：部门信息表Salary：员工薪水信息表purchase表：购买信息表【四】、实验题目及其解答：1、建立视图（1） 建立视图view1， 查询所有财务部的职工的编号、姓名和工资收入，显示前5项， 并按照收入排序。Solut

30、ion:use compyCREATE VIEW view1(编号,姓名,收入)AS (SELECT Employees.编号,姓名,收入 FROM Employees INNER JOIN Salary ON Salary.编号=Employees.编号 WHERE 部门号=(SELECT 部门编号 FROM Departments WHERE 部门名称=财务部);SELECT TOP 5 编号,姓名,收入 FROM view1ORDER BY 收入;（2） 建立视图view2，查询所有职工的职工编号、姓名和盈余；Solution:CREATE VIEW view2(编号,姓名,盈余)AS (SELECT Employees.编号,姓名,(收入-支出)AS 盈余 FROM Employees INNER JOIN Salary ON Employees.编号=Salary.编号)SELECT 编号,姓名,盈余 FROM view2（3） 在建立视图view1上建立视图view3，要求为：所有财务部