数据库应用

简单数据库设计和应用

一、数据库设计
1.E-R图描述Company数据库的概念模型。

2.Company数据库的逻辑结构设计。

CREATE DATABASE Company;
USE Company;

（1）部门表
表名：DEPA
结构：共有3列，具体如下所示

列名	数据类型	描述	约束
dno	integer	部门编号	主键
dname	varchar2(30)	部门名称	唯一、非空
addr	varchar2(60)	部门地址	唯一、非空
dname	varchar2(30)	部门名称	允许为空

CREATE TABLE DEPA(
     dno INTEGER PRIMARY KEY,
     dname VARCHAR(30) UNIQUE NOT NULL,
     addr VARCHAR(60)
);
DESCRIBE DEPA;

（2）员工表
表名：EMPL
结构：共有6列，具体如下所示

列名	数据类型	描述	约束
eno	integer	员工编号	主键
ename	char(8)	姓名	非空
sex	char(2)	性别	仅为男或女
hiredate	date	入职时间	允许为空
salary	decimal(7,2)	薪资	1500至5000
dno	integer	所在部门编号	是引用depa的外键

CREATE TABLE EMPL(
     eno INTEGER PRIMARY KEY,
     ename CHAR(8) NOT NULL,
     sex CHAR(2) CHECK (sex IN('男','女')),
     hiredate DATE,
     salary DECIMAL(7,2) CHECK(salary>=1500 AND salary<=5000),
     dno INTEGER
);
ALTER TABLE EMPL ADD CONSTRAINT FK_ID FOREIGN KEY(dno) REFERENCES DEPA(dno);
DESCRIBE EMPL;

（3）项目表
表名：PROJ
结构：共有2列，具体如下所示

列名	数据类型	描述	约束
pno	integer	项目编号	主键
pname	varchar2(30)	项目名称	允许为空

CREATE TABLE PROJ(
     pno INTEGER PRIMARY KEY,
     pname VARCHAR(30)
);
DESC PROJ;

（4）工作表
表名：JOB
结构：共有3列，具体如下所示

列名	数据类型	描述	约束
eno	integer	员工编号	员工编号和项目编号是联合主键，且员工编号是引用empl的外键。
pno	integer	项目编号	项目编号是引用proj的外键
days	integer	天数	默认值为0

CREATE TABLE JOB(
     eno INTEGER,
     pno INTEGER,
     days INTEGER DEFAULT 0,
     PRIMARY KEY(eno,pno)
);
ALTER TABLE JOB ADD CONSTRAINT FK_EN FOREIGN KEY(eno) REFERENCES EMPL(eno);
ALTER TABLE JOB ADD CONSTRAINT FK_PN FOREIGN KEY(pno) REFERENCES PROJ(pno);
DESC JOB;

3.Company数据库的物理结构设计。
创建表空间和用户，并为用户授予相应的权限；

INSERT INTO mysql.user (HOST,USER,PASSWORD) VALUES ('localhost','new_user',PASSWORD('123456'));
FLUSH PRIVILEGES;

二、数据导入
从EXCEL表导入数据到DEPA表、EMPL表、PROJ表、JOB表。
右键表，导入，从外部导入就可以了。

三、数据编辑
1.添加记录语句 INSERT
（1）向DEPA表添加14号部门，客户中心，地址为开发区紫光路2号。
（2）向DEPA表添加 15号部门，技术支持部，地址未详。

INSERT INTO DEPA(dno,dname,addr) VALUES
(14,'客户中心','开发区紫光路2号'),
(15,'技术支持部','地址未详');

（3）向EMPL表添加1401号男员工，张山，入职时间1977年9月1日，工资4050元，14号部门。

INSERT INTO EMPL(eno,ename,sex,hiredate,salary,dno) VALUES
(1401,'张山','男','1997-9-1',4050,14);

（4）向EMPL表添加1402号员工，何宜，入职时间不祥，工资不祥，15号部门。

INSERT INTO EMPL(eno,ename,dno) VALUES(1402,'何宜',15);

（5）向JOB表添加1401号员工，参与了104号项目35天。
（6）向JOB表添加1402号员工，参与了103号项目20天。

INSERT INTO JOB(eno,pno,days) VALUES (1401,104,35),(1402,103,20);

（7）向PROJ表添加105号项目，历史学院档案库管理系统。
（8）向PROJ表添加106号项目，ATM机管理系统。

INSERT INTO PROJ(pno,pname) VALUES
(105,'历史学院档案库管理系统'),
(106,'ATM机管理系统');

2.修改记录语句 UPDATE
（1）把DEPA表中’客户中心’的地址改为’大连甘井子区红岭路’。

UPDATE DEPA SET addr='大连甘井子区红岭路' WHERE dname='客户中心';

（2）把DEPA表中15号部门的地址用沈阳东郊路120号填充。

UPDATE DEPA SET addr='沈阳东郊路120号' WHERE dno=15;

（3）把EMPL表中何宜的入职时间、工资用1980年12月20日，3000元填充。

UPDATE EMPL SET hiredate='1980-12-20',salary=3000 WHERE ename='何宜';

（4）把EMPL表中14号部门的张山的部门号改为13号。

UPDATE empl SET dno=13 WHERE dno=14 AND ename='张山';

（5）把JOB表中参与104号项目的每人增加5天。

UPDATE job SET days=days-5 WHERE pno=104;

（6）把JOB表中参与102号项目的每人天数乘以系数0.8。

UPDATE job SET days=days*0.8 WHERE pno=102;

（7）把PROJ表中105号项目名称改为理想公司管理系统。
（8）把PROJ表中106号项目名称置为空。

UPDATE proj SET pname='理想公司管理系统' WHERE pno=105;
UPDATE proj SET pname='' WHERE pno=106;

3.删除记录语句 DELETE
（1）删除15号部门的信息。

SET FOREIGN_KEY_CHECKS = 0;
DELETE FROM depa WHERE dno=15;
SET FOREIGN_KEY_CHECKS = 1;

（2）删除地址为空的部门。

DELETE FROM depa WHERE addr='';

（3）删除入职于1980年后，且工资低于1500的员工

DELETE FROM EMPL WHERE salary<1500 AND hiredate>1980;

（4）删除属于14号部门的员工。

DELETE FROM empl WHERE dno=14;

（5）删除JOB表中参与104号项目的员工。

DELETE FROM job WHERE pno=104;

（6）删除JOB表中天数低于5天的员工。

DELETE FROM job WHERE days<5;

（7）删除PROJ表中名称为空的项目。

DELETE FROM PROJ WHERE pname='';

（8）删除PROJ表中104号项目。

DELETE FROM proj WHERE pno=104;

4. 导出数据到EXCEL表(注意：若数据有修改，在提交作业前应导出数据一并提交，以便下次实验继续使用修改后的数据。)
   （1）导出DEPA表到EXCEL文件。
   （2）导出EMPL表到EXCEL文件。
   （3）导出PROJ表到EXCEL文件。
   （4）导出JOB表到EXCEL文件。

四、数据查询
(一) 单表查询
1.简单的SELECT查询
（1）查询雇员表Empl中所有员工的详细信息，并且列名用汉字表示。

SELECT eno AS 员工编号,ename 姓名,sex 性别,hiredate 入职时间,salary 性别,dno 所在部门编号 FROM empl;

（2）查询项目表Proj中所有项目的详细信息，并且列名用汉字表示。

SELECT pno 项目编号,pname 项目名称 FROM PROJ;

2.使用WHERE子句进行查询
（1）查询雇员表Empl中李强的员工编号。

SELECT eno FROM empl WHERE ename='李强';

（2）查询项目表Proj中项目编号为“101”的项目名称。

SELECT pname FROM proj WHERE pno=101;

3.使用DISTINCT关键字去掉重复行
（1）查询雇员表Empl中出现的所有部门编号，要求无重复。

SELECT DISTINCT dno FROM empl;

（2）查询工作表Job中参与过项目开发的所有员工编号，要求无重复。

SELECT DISTINCT eno FROM job;

（3）查询工作表Job中出现的所有项目编号，要求无重复。

SELECT DISTINCT pno FROM job;

4.使用ORDER BY子句对查询结果进行排序
（1）查询雇员表Empl中所有员工的详细信息，结果按员工姓名降序排列。

SELECT * FROM empl ORDER BY ename DESC;

（2）查询工作表Job中的工作信息，结果按工作天数升序排列。

SELECT * FROM job ORDER BY days ASC;

5.使用LIKE子句进行模糊查询
（1）查询所有姓王的员工的编号及姓名，并且以员工编号及员工姓名作为列名。

SELECT eno AS 员工编号,ename AS 员工姓名 FROM empl WHERE ename LIKE '王%';

（2）查询雇员表Empl中姓名以“伟”字结尾的员工信息。

SELECT * FROM empl WHERE ename LIKE '%伟';

（3）查询项目表Proj中名称以“系统”结尾的项目信息。

SELECT * FROM proj WHERE pname LIKE '%系统';

(二)分组查询与聚集函数的使用
1.使用聚集函数MIN、MAX进行查询
（1）查询雇员表Empl中员工的最低工资，并且列名用“最低工资”表示。

SELECT MIN(salary)AS 最低工资 FROM empl;

（2）查询雇员表Empl中员工的最高工资，并且列名用“最高工资”表示。

SELECT MAX(salary)AS 最高工资 FROM empl;

（3）查询雇员表Empl中员工的最近入职时间，并且列名用“最近入职时间”表示。

SELECT MAX(hiredate)AS 最近入职时间 FROM empl;

2.使用聚集函数COUNT进行查询
（1）查询雇员表Empl中所有员工的总人数，并且列名用“员工总数”表示。

SELECT COUNT(ename)AS 员工总数 FROM empl;

（2）查询部门表Depa中部门的总数，并且列名用“部门总数”表示。

SELECT COUNT(dname)AS 部门总数 FROM depa;

（3）查询项目表Proj中项目的总数，并且列名用“项目总数”表示。

SELECT COUNT(pname)AS 项目总数 FROM proj;

3.使用分组子句GROUP BY与聚集函数MIN、MAX进行查询
（1）查询每个部门中员工的最早入职时间，显示部门编号和最早入职时间。

SELECT MIN(hiredate),dno FROM empl GROUP BY dno;

（2）分别统计雇员表Empl中各部门员工的最高工资，并且列名分别用“部门编号”及“最高工资”表示。

SELECT dno AS 部门编号,MAX(salary)AS 最高工资 FROM empl GROUP BY dno;

4.使用分组子句GROUP BY与聚集函数SUM、AVG进行查询
（1）分别统计雇员表Empl中各部门员工的工资总额，并且列名分别用“部门编号”及“各部门工资总额”表示。

SELECT dno AS 部门编号,SUM(salary)AS 各部门工资总额 FROM empl GROUP BY dno;

（2）分别统计雇员表Empl中各部门员工工资的平均数，并且列名分别用“部门编号”及“各部门工资平均数”表示。

SELECT dno AS 部门编号,AVG(salary)AS 各部门工资平均数 FROM empl GROUP BY dno;

（3）分别统计工作表Job中每名员工参与开发项目的总工作天数，并且列名分别用“员工编号”及“总工作天数”表示（不考虑员工中途参与开发的情况）。

SELECT eno AS 员工编号,SUM(days)AS 总工作天数 FROM job GROUP BY eno;

5.使用分组子句GROUP BY与聚集函数COUNT进行查询
（1）统计员工人数多于4人的部门信息，并且列名分别用“部门编号”及“员工人数”表示。

SELECT dno AS 部门编号,COUNT(eno)AS 员工人数 FROM empl GROUP BY dno HAVING COUNT(eno)>4;

（2）分别统计工作表Job中各项目的参与开发的员工人数，并且列名分别用“项目编号”及“员工人数”表示。

SELECT pno AS 项目编号,COUNT(eno)AS 员工人数 FROM job GROUP BY pno;

（3）分别统计工作表Job中每名员工参与开发的项目总数，并且列名分别用“员工编号”及“参与项目总数”表示。

SELECT eno AS 员工编号,COUNT(pno)AS 参与项目总数 FROM job GROUP BY eno;

(三)多表连接查询
1.对两张数据表使用内连接进行查询
（1）查询姓名为“王一伟”的员工所在部门的名称，并且列名用汉字表示。

SELECT depa.dname AS 部门名称 FROM depa JOIN empl ON depa.`dno`=empl.`dno` WHERE empl.`ename`='王一伟';

（2）查询编号为“1002”的员工姓名及该员工所在部门的名称，并且列名用汉字表示。

SELECT empl.`ename` AS 员工姓名,depa.dname AS 部门名称 FROM depa JOIN empl ON depa.`dno`=empl.`dno` WHERE empl.`eno`=1002;

（3）查询编号为“1102”的员工姓名及该员工参与过的所有项目的编号，并且列名用汉字表示。

SELECT empl.`ename` AS 员工姓名,job.`pno` AS 项目编号 FROM empl JOIN job ON empl.`eno`=job.`eno` WHERE empl.`eno`=1102;

2.对多张数据表使用内连接进行查询
（1）查询姓名为“陈晨”的员工参与过的所有项目的名称，并且列名用汉字表示。

SELECT pname AS 项目名称 FROM proj JOIN job ON proj.`pno`=job.`pno` WHERE job.`eno`=(SELECT empl.eno FROM empl JOIN job ON job.`eno`=empl.`eno` WHERE empl.`ename`='陈晨');

（2）查询编号为“1202”的员工姓名及该员工参与过的所有项目的名称，并且列名用汉字表示。

(SELECT ename AS 员工姓名 FROM empl WHERE empl.`eno`=1202) UNION (SELECT pname AS 项目名称 FROM proj JOIN job ON proj.`pno`=job.`pno` WHERE job.`eno`=1202);

（3）查询编号为“102”的项目名称及参与了该项目开发的所有员工的姓名，并且列名用汉字表示。

SELECT pname AS 项目名称 FROM proj WHERE pno=102 UNION (SELECT empl.ename FROM empl JOIN job ON empl.`eno`=job.`eno` WHERE job.`pno`=102);

3.对两张数据表使用左连接进行查询
（1）使用左连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。

SELECT empl.*,job.`pno` FROM empl LEFT JOIN job ON empl.`eno`=job.`eno`;

（2）使用左连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。

SELECT empl.*,depa.`dname` FROM empl LEFT JOIN depa ON empl.`dno`=depa.`dno`;

（3）使用左连接查询所有部门的详细信息，并列出各部门的所有员工姓名，如果某部门刚刚成立，没有任何员工，则对应的员工姓名项以空值表示。

SELECT depa.*,empl.`ename` FROM depa LEFT JOIN empl ON depa.`dno`=empl.`dno`;

4.对两张数据表使用右连接进行查询
（1）使用右连接查询所有员工的基本信息及参与项目开发情况，如果某员工参与过项目开发，则列出该员工所参与的所有项目的编号，否则该项以空值表示。

SELECT empl.*,job.`pno` FROM job RIGHT JOIN empl ON empl.`eno`=job.`eno`;

（2）使用右连接查询所有员工的基本信息及所在部门名称，如果某员工尚未分配到任何部门，则该员工的部门名称项以空值表示。

SELECT empl.*,depa.`dname` FROM depa RIGHT JOIN empl ON depa.`dno`=empl.`dno`;

（3）使用右连接查询所有部门的详细信息，并列出各部门的所有员工姓名，如果某部门刚刚成立，没有任何员工，则对应的员工姓名项以空值表示。

SELECT depa.*,empL.`ename` FROM empl RIGHT JOIN depa ON depa.`dno`=empl.`dno`;

(四)子查询
1.单表单值子查询
（1)查询工资最高的雇员的姓名与入职时间。

SELECT ename,hiredate FROM empl WHERE salary=(SELECT MAX(salary) FROM empl);

（2)查询所有项目中工作天数最少的员工编号。

SELECT eno FROM job WHERE days=(SELECT MIN(days) FROM job) GROUP BY pno;

（3)查询1983年之前入职，且工资低于平均工资的员工姓名。

SELECT ename FROM empl WHERE hiredate<'1983' AND salary<(SELECT AVG(salary) FROM empl);

2.多表单值子查询
（1)查询编号为1001的员工的部门名。

SELECT dname FROM depa WHERE dno=(SELECT dno FROM empl WHERE eno=1001);

（2)查询研发部的员工数。

SELECT COUNT(eno) FROM empl WHERE dno=(SELECT dno FROM depa WHERE dname='研发部');

（3)查询陈晨所在的项目编号。

SELECT pno FROM job WHERE eno=(SELECT eno FROM empl WHERE ename='陈晨');

3.多表多值子查询
（1)查询1985年以后入职的员工在所在项目的工作天数。

SELECT days FROM job WHERE eno=ANY(SELECT eno FROM empl WHERE hiredate<'1985');

（2)查询参与项目天数超过40天的员工姓名。

SELECT ename FROM empl WHERE eno=ANY(SELECT eno FROM job WHERE days>40);

（3)查询工资低于2000的员工的所属部门。

SELECT dname FROM depa WHERE dno=ANY(SELECT dno FROM empl WHERE salary<2000);

4.多层嵌套子查询
（1)查询财务部中，收入高于平均工资的员工。

SELECT ename FROM empl WHERE salary>(SELECT AVG(salary) FROM empl) AND dno IN (SELECT dno FROM depa WHERE dname='财务部');

（2)查询“中国移动结算管理系统”项目的参与部门。

SELECT dname FROM depa WHERE dno=ANY(SELECT dno FROM empl WHERE eno=ANY(SELECT eno FROM job WHERE pno=ANY(SELECT pno FROM proj WHERE pname='中国移动结算管理系统')));

（3)查询研发部门雇员参加的项目名称。

SELECT pname FROM proj WHERE pno=ANY(SELECT pno FROM job WHERE eno=ANY(SELECT eno FROM empl WHERE dno=ANY(SELECT dno FROM depa WHERE dname='研发部')));