SQL基础复习07--实践

2022-09-27 11:37:22

SQL实践

参考教材《数据库系统：原理、设计与编程（MOOC版）》，陆鑫 张凤荔 陈安龙

使用参考教材中的3.7节的“工程项目管理系统”来使用前面学过的SQL知识。

3.7.1 项目案例——工程项目管理系统

开发一个工程项目管理系统，实现公司的工程项目管理目标。创建一个数据库ProjectDB，包含部门表Department、员工表Employee、项目表Projet和任务表Assignment。各表的字段结构设计如下：
部门表Department：
SQL基础复习07--实践

员工表Employee：
SQL基础复习07--实践

项目表Project:
SQL基础复习07--实践

任务表Assignment：
SQL基础复习07--实践

由于教材中使用的是PostgreSQL数据库，所以有数据类型serial。实际就是自增的int，在SQL Server中用另一种方式实现。可以参考下面的代码。

3.7.2 数据库的创建

创建一个新的数据库，命名为ProjectDB。

CREATE DATABASE ProjectDB;
GO

3.7.3 数据库表的定义

创建前面设计好的4个数据库表。

USE ProjectDB;
GO

创建Department表：

CREATE TABLE Department
    (DepartmentCode char(3) NOT NULL,
    DepartmentName varchar(30) NOT NULL,
    DepartmentIntro varchar(200) NULL,
    DepartmentAddr varchar(50) NULL,
    DepartmentTel varchar(20) NULL,
    CONSTRAINT Department_PK PRIMARY KEY(DepartmentCode));
GO

创建Employee表，将serial类型改为SQL Server的int IDENTITY(1,1)，是一个从1开始自增，每次增1的整数。但在我的实际开发中一般是用Guid作为主键的数据类型。这里为了学习方便先用int。

CREATE TABLE Employee
    (EmployeeID int IDENTITY(1,1) NOT NULL,
    EmployeeName varchar(10) NOT NULL,
    Gender char(2) NOT NULL DEFAULT ‘男‘,
    Department char(3) NOT NULL,
    Degree char(6) NULL CHECK (Degree IN(‘本科‘, ‘研究生‘, ‘其他‘)),
    BirthDay date NULL,
    Phone char(11) NULL,
    Email varchar(20) NOT NULL UNIQUE,
    CONSTRAINT Employee_PK PRIMARY KEY(EmployeeID),
    CONSTRAINT EMP_DEPART_FK FOREIGN KEY(Department) REFERENCES Department(DepartmentCode) ON UPDATE CASCADE);
GO

创建Project表：

CREATE TABLE Project
    (ProjectID int IDENTITY(1,1) NOT NULL,
    ProjectName varchar(50) NOT NULL,
    Department char(3) NOT NULL,
    EstimateHours int NOT NULL,
    StartDate date NULL,
    EndDate date NULL,
    CONSTRAINT Project_PK PRIMARY KEY(ProjectID),
    CONSTRAINT PROJ_DEPART_FK FOREIGN KEY(Department) REFERENCES Department(DepartmentCode) ON UPDATE CASCADE);
GO

创建Assignment表：

CREATE TABLE Assignment
    (ProjectID int NOT NULL,
    EmployeeID int NOT NULL,
    FinishedHours int NOT NULL,
    Cost int NOT NULL,
    CONSTRAINT Assignment_PK PRIMARY KEY(ProjectID, EmployeeID),
    CONSTRAINT ASSIGN_PROJ_FK FOREIGN KEY(ProjectID)
        REFERENCES Project(ProjectID)
        ON UPDATE NO ACTION
        ON DELETE CASCADE,
    CONSTRAINT ASSIGN_EMP_FK FOREIGN KEY(EmployeeID)
        REFERENCES Employee(EmployeeID)
        ON UPDATE NO ACTION
        ON DELETE NO ACTION);
GO

创建表的顺序是有讲究的，比如Assignment表的主键是复合键，是Project的主键和Employee的主键的复合键，所以它就要在Project和Employee这两个表创建之后再创建。Employee表的Department字段是外键，关联Department表的主键，则Employee表需要在Department表创建之后再创建。

3.7.4 数据的维护操作

创建完数据表之后，可以执行由INSERT INTO语句构成的数据插入SQL程序，完成对ProjectDB数据库的4个表的数据插入操作。

Department表的数据插入：

INSERT INTO Department VALUES(‘A01‘, ‘人力资源‘, NULL, ‘A区-100‘, ‘8535-6102‘);
INSERT INTO Department VALUES(‘A02‘,‘法律部‘, NULL, ‘A区-108‘, ‘8535-6108‘);
INSERT INTO Department VALUES(‘A03‘,‘会计部‘, NULL, ‘A区-201‘, ‘8535-6112‘);
INSERT INTO Department VALUES(‘A04‘,‘财务部‘, NULL, ‘A区-205‘, ‘8535-6123‘);
INSERT INTO Department VALUES(‘A05‘,‘行政部‘, NULL, ‘A区-301‘, ‘8535-6138‘);
INSERT INTO Department VALUES(‘A06‘,‘生产部‘, NULL, ‘B区-101‘, ‘8535-6152‘);
INSERT INTO Department VALUES(‘A07‘,‘市场部‘, NULL, ‘B区-201‘, ‘8535-6158‘);
INSERT INTO Department VALUES(‘A08‘,‘IT部‘, NULL, ‘C区-101‘, ‘8535-6162‘);
GO

检查一下插入的数据是否有问题：

SELECT * FROM Department;
GO

SQL基础复习07--实践

后面的检查表中数据的语句就省略了。

Employee表的数据插入：

INSERT　INTO　Employee(employeename,gender,department,degree,birthday,phone,email) VALUES(
　　‘潘振‘, ‘男‘,‘A07‘, ‘本科‘, ‘1985-12-10‘,‘139********‘,‘PZ@ABC.com‘);
INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘张志‘, ‘男‘,‘A02‘, ‘研究生‘, ‘1973-06-23‘,‘139********‘,‘ZZ@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘刘鸿‘, ‘女‘,‘A03‘, ‘本科‘, ‘1976-02-17‘,‘139********‘,‘LH@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘廖宇‘, ‘男‘,‘A04‘, ‘本科‘, ‘1989-11-13‘,‘139********‘,‘LY@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘刘梦‘, ‘女‘,‘A05‘, ‘其他‘, ‘1987-05-19‘,‘139********‘,‘LM@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘朱静‘, ‘女‘,‘A08‘, ‘本科‘, ‘1978-08-30‘,‘139********‘,‘ZJ@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘谢剑‘, ‘男‘,‘A03‘, ‘研究生‘, ‘1990-02-11‘,‘139********‘,‘XJ@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘丁成‘, ‘男‘,‘A06‘, ‘本科‘, ‘1982-09-23‘,‘139********‘,‘DC@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘严刚‘, ‘男‘,‘A07‘,‘本科‘, ‘1988-11-18‘,‘139********‘,‘YG@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘杨盛‘, ‘男‘,‘A06‘,‘本科‘, ‘1975-06-09‘,‘139********‘,‘YS@ABC.com‘);
INSERT INTO Employee (employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘王伦‘, ‘男‘,‘A01‘, ‘本科‘, ‘1968-07-30‘,‘139********‘,‘WL@ABC.com‘);
INSERT INTO Employee　(employeename,gender,department,degree,birthday,phone, email) VALUES(
　　‘汪润‘, ‘女‘,‘A04‘, ‘本科‘, ‘1965-11-19‘,‘139********‘,‘WR@ABC.com‘);
GO

SQL基础复习07--实践

Project表的数据插入：

INSERT　INTO　Project　(projectname,department,estimatehours,startdate,enddate) VALUES(
　　‘新产品推荐‘, ‘A07‘,220, ‘2014-03-12‘, ‘2014-05-08‘);
INSERT INTO Project　(projectname,department,estimatehours,startdate,enddate) VALUES(
　　‘第2季度经营分析‘, ‘A04‘,150, ‘2014-06-05‘, ‘2014-07-10‘ );
INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES(
　　‘上年度增值税上报‘, ‘A03‘, 80, ‘2014-02-12‘, ‘2014-03-01‘);
INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES(
　　‘产品市场分析‘, ‘A07‘, 135, ‘2014-03-20‘, ‘2014-05-15‘);
INSERT INTO Project (projectname,department,estimatehours,startdate,enddate) VALUES(
　　‘产品定型测试‘, ‘A06‘, 185, ‘2014-05-12‘, ‘2014-07-15‘);
GO

SQL基础复习07--实践

Assignment表的数据插入：

INSERT INTO Assignment VALUES(1,2, 50,50);
INSERT INTO Assignment VALUES(1,4, 100,50);
INSERT INTO Assignment VALUES(2,5, 60,50);
INSERT INTO Assignment VALUES(2,7, 80,50);
INSERT INTO Assignment VALUES(3,8 ,45,50);
INSERT INTO Assignment VALUES(3,9, 75,50);
INSERT INTO Assignment VALUES(4,10, 55,60);
INSERT INTO Assignment VALUES(4,11, 70,60);
INSERT INTO Assignment VALUES(5,2, 70,60);
INSERT INTO Assignment VALUES(5,12, 30,60);
GO

SQL基础复习07--实践

因为原题目中的Serial这个数据类型与我们使用的int IDENTITY(1,1)并不完全一样，所以上面的ID也与参考书上有一定区别。练习的时候要注意。

3.7.5 多表的关联查询

有了数据库，有了数据表，有了数据，这里练习一下多表的关联查询。

例3-52
管理部门希望了解各个项目参与的员工的任务工时列表。实现该信息查询处理，需要关联Employee表、Project表和Assignment表。查询输出内容应该包含项目名称、员工姓名、实际工时。

SELECT ProjectName AS 项目名称, EmployeeName AS 员工姓名, FinishedHours AS 实际工时
FROM Employee AS E, Project AS P, Assignment AS A
WHERE E.EmployeeID=A.EmployeeID AND P.ProjectID=A.ProjectID
ORDER BY P.ProjectID, A.EmployeeID;
GO

SQL基础复习07--实践

注意，由于ProjectName、EmployeeName和FinishedHours在三个表中均只有一列，没有重名，所以SELECT语句后面的ProjectName、EmployeeName和FinishedHours不必写成P.ProjectName、E.EmployeeName和A.FinishedHours。若SELECT FROM的几个表中存在SELECT的重复的列名，就需要用这种形式。例如下面这个例题。下面这个例题SELECT FROM的表中，Employee表和Assignment表都有EmployeeID这个字段。

例3-53
管理部门希望进一步了解各个参与的员工的总工时数据。实现该信息查询统计，需要关联Employee表和Assignment表，输出内容包括员工编号、员工姓名、完成总工时。

SELECT E.EmployeeID AS 员工编号, EmployeeName AS 员工姓名, SUM(FinishedHours) AS 完成总工时
FROM Employee AS E, Assignment AS A
WHERE E.EmployeeID=A.EmployeeID
GROUP BY E.EmployeeID, EmployeeName
ORDER BY E.EmployeeID;
GO

SQL基础复习07--实践

例3-54
管理部门希望了解各个项目的预计成本和当前实际发生成本，并找出哪些项目成本超出预算。需要关联Project表和Assignment表，统计输出各个项目的成本信息，其输出内容包含项目名称、预计成本、实际成本。

SELECT ProjectName AS 项目名称, (EstimateHours * Cost) AS 预计成本, SUM(FinishedHours * Cost) AS 实际成本
FROM Project, Assignment
WHERE Project.ProjectID=Assignment.ProjectID
GROUP BY ProjectName, EstimateHours, Cost;
GO

SQL基础复习07--实践

上面的多表关联SELECT查询使用Project表的主键和Assignment表的外键进行关联，找出匹配的数据进行计算。再计算实际成本数据中，按项目名进行分组求和统计。由计算结果可知，项目“上年度增值税上报”的实际成本超出预算。

为什么GROUP BY中没有FinishedHours这一项呢？因为观察Assignment表中的数据，发现不同的员工合作同一个项目，用的FinishedHours是不同的，但同一个项目的ProjectName，EstimateHours和Cost都是相同的。如果加上FinishedHours，则会按照它再次分组，每个项目将会对应两行数据，第一行是第一个员工完成它的成本，第二行是第二个员工完成它的成本。如下列代码的执行结果。但因为项目需要两个员工合作，我们实际应该输出的是两个员工的实际成本相加的成本，才是这个项目的实际成本。

SELECT ProjectName AS 项目名称, (EstimateHours * Cost) AS 预计成本, SUM(FinishedHours * Cost) AS 实际成本
FROM Project, Assignment
WHERE Project.ProjectID=Assignment.ProjectID
GROUP BY ProjectName, EstimateHours, FinishedHours, Cost;
GO

SQL基础复习07--实践

3.7.6 视图的应用

在工程项目管理系统中，为了降低程序编程人员使用查询SQL语句的复杂度，也为了系统数据的安全使用，我们应用试图功能，更好地实现数据库信息的访问。

例3-55
管理部门希望查询输出员工通讯录。为了保护员工的一些隐私信息，可以采用视图方式查询输出，输出内容包括员工编号、员工姓名、手机、邮箱。

CREATE VIEW ContactView AS
SELECT EmployeeID AS 员工编号, EmployeeName AS 员工姓名, Phone AS 电话, Email AS 邮箱
FROM Employee;
GO

SELECT * FROM ContactView
ORDER BY 员工编号;
GO

SQL基础复习07--实践

例3-56
管理部门希望找出工期超出预期的项目信息。需要关联Project表和Assignment表进行查询处理，计算各个项目的实际开展工时，并与预期工时比较，找出工期超出预期的项目信息。输出内容包括项目名称、预期工时、实际工时。

SELECT ProjectName AS 项目名称, EstimateHours AS 预期工时, SUM(FinishedHours) AS 实际工时
FROM Project, Assignment
WHERE Project.ProjectID=Assignment.ProjectID AND SUM(FinishedHours)>EstimateHours
GROUP BY ProjectName;
GO

上述代码运行发生错误，不允许将内置聚合函数作为WHERE子句的一部分。但教材上说直接使用SELECT语句无法完成上述查询操作，所以用了视图。实际上直接使用SELECT还是可以完成上述查询操作的。只需要把比较大小的聚合函数以及它所在的那一段代码放在HAVING子句中，然后在GROUP BY子句中加上EstimateHours即可。如下：

SELECT ProjectName AS 项目名称, EstimateHours AS 预期工时, SUM(FinishedHours) AS 实际工时
FROM Project, Assignment
WHERE Project.ProjectID=Assignment.ProjectID
GROUP BY ProjectName, EstimateHours
HAVING SUM(FinishedHours)>EstimateHours;
GO

SQL基础复习07--实践

但我们还是用视图实现一下。先构建一个包含该内置函数的视图，然后在视图查询SQL语句中使用WHERE子句条件，检索超期的项目。

CREATE VIEW ProjectFinishedHours AS
SELECT ProjectName AS 项目名称, EstimateHours AS 预期工时, SUM(FinishedHours) AS 实际工时
    FROM Project AS P, Assignment AS A
    WHERE P.ProjectID=A.ProjectID
    GROUP BY ProjectName, EstimateHours;
GO

SELECT * FROM ProjectFinishedHours
WHERE 实际工时>预期工时
ORDER BY 项目名称;
GO

结果与上面是一样的。

操作程序员创建好的视图感觉比直接操作数据库简单很多。比如这里WHERE 实际工时>预期工时，感觉非程序员也能理解。
这个实践到这里就结束了。我还是一个数据库新手，希望以后再学习一些数据库设计、管理和编程的知识。比如数据库规范化设计、事务管理、存储过程编程等。

SQL基础复习07--实践

上一篇：Mongodb备份还原常用命令


下一篇：在openSUSE 13.1下安装卸载openjdk以及Oracle JDK