[MySQL课后作业]人事管理系统的SQL实践
第一题
1.假设某商业集团中有若干公司，人事数据库中有3个基本表:
职工表:EMP(E#.ENAME,AGE, SEX, ECITY)。 其属性分别表示职工工号、姓名、年龄、性别和居住城市。
工作表:WORKS(E#,C#,SALARY)。其属性分别表示职工工号、所在公司的编号和工资。
公司表:COMP(C#,CANME,CITY,MGR E#)。其属性分别表示公司编号、公司名称、公司所在城市和公司经理的工号
在3个基本表中，字段AGE和SALARY为数值型，其他字段均为字符型
创建数据库表
create database goods;
use goods;CREATE TABLE EMP (`E#` varchar(4) PRIMARY KEY,ENAME varchar(50),AGE int,SEX char(2),ECITY varchar(20)
);CREATE TABLE WORKS (`E#` varchar(10),`C#` varchar(10),SALARY decimal(8, 2),PRIMARY KEY (`E#`, `C#`)
);CREATE TABLE COMP (`C#` varchar(10) PRIMARY KEY,CNAME varchar(20),CITY varchar(20),`MEG_E#` varchar(10)
);
插入数据
INSERT INTO EMP VALUES( '0001', '张三', 56, '女', '成都' ),( '0002', '李四', 44, '男', '深圳' ),( '0003', '王五', 28, '男', '武汉' ),( '0004', '赵六', 57, '男', '成都' ),( '0005', '小红', 67, '女', '北京' ),( '0006', '小明', 70, '男', '上海' );INSERT INTO WORKS VALUES( '0001', '0001', 2800.00 ),( '0001', '0002', 1500.00 ),( '0002', '0003', 9580.00 ),( '0002', '0002', 1598.00 ),( '0003', '0001', 5500.00 ),( '0003', '0002', 2230.00 ),( '0004', '0002', 3300.00 ),( '0005', '0003', 1332.00 );INSERT INTO COMP VALUES( '0001', '联华公司', '成都', '0001' ),( '0002', '联华武汉分部', '武汉', '0002' ),( '0003', '联华重庆分部', '重庆', '0001' );
(1)检索超过50岁的男职工的工号和姓名。
SELECT `E#`,ENAME FROM EMP WHERE AGE>50 AND SEX="男";
(2)假设每个职工可在多个公司工作，检索每个职工的兼职公司数目和总工资。显示为(E#,NUM,SUM SALARY)，其属性分别表示工号、公司数目和总工资。
SELECT `E#`,COUNT(*)NUM,SUM(SALARY) AS SUM_SALARY FROM WORKS GROUP BY `E#`;
(3)检索联华公司低于本公司职工平均工资的所有职工的工号和姓名。
SELECT E.`E#`, E.ENAME FROM EMP E
JOIN WORKS W ON E.`E#` = W.`E#`
JOIN COMP C ON W.`C#` = C.`C#`
WHERE C.CNAME = '联华公司' AND W.SALARY < (SELECT AVG(W.SALARY)FROM WORKS WJOIN COMP C ON W.`C#` = C.`C#`WHERE C.CNAME = '联华公司'
);
(4)检索职工人数最多的公司的编号和名称。
SELECT C.`C#`, C.CNAME FROM COMP C
JOIN WORKS W ON C.`C#` = W.`C#`
GROUP BY C.`C#` ORDER BY COUNT(W.`E#`) DESC LIMIT 1;
(5)检索平均工资高于联华公司平均工资的公司编号和名称。
SELECT C.`C#`, C.CNAME FROM COMP C
JOIN WORKS W ON C.`C#` = W.`C#`
GROUP BY C.`C#` HAVING AVG(W.SALARY) > (SELECT AVG(W.SALARY) FROM WORKS WJOIN COMP C ON W.`C#` = C.`C#` WHERE C.CNAME = '联华公司'
);
(6)为联华公司的职工加薪 5%。
UPDATE WORKS W SET SALARY = SALARY * 1.05
WHERE W.`C#` IN (SELECT `C#` FROM COMP WHERE CNAME = '联华公司');
(7)在表 WORKS中删除年龄大于60岁的职工记录。
DELETE FROM works
WHERE works.`E#` IN ( SELECT emp.`E#` FROM emp WHERE emp.AGE > 60 );
(8)建立一个有关女职工的视图emp woman，属性包括(E#,ENAME,C#,CANME.SALARY)。然后对视图empwoman 进行操作，检索每一个女职工的总工资(假设每个职工可在多个公司兼职)。
CREATE VIEW emp_woman AS SELECT
emp.`E#`, emp.ENAME, comp.`C#`, comp.CNAME, works.SALARY FROM empJOIN works ON emp.`E#` = works.`E#`JOIN comp ON comp.`C#` = works.`C#`
WHERE emp.SEX = "女";SELECT `E#`, ENAME, sum( salary ) FROM emp_woman GROUP BY `E#`;
第二题
(1)创建表 workinfo,要求创建表的同时在id字段上创建名为 index id 的唯一性索引,且降序排列。workinfo的表结构如下图所示。
| 字段名 | 字段描述 | 数据类型 | 非空 | 主键 | 唯一 | 自增 |
|---|---|---|---|---|---|---|
| id | 编号 | INT | 是 | 是 | 是 | 是 |
| name | 职位名称 | VARCHAR(20) | 是 | 否 | 否 | 否 |
| type | 职位类型 | VARCHAR(10) | 否 | 否 | 否 | 否 |
| address | 工作地址 | VARCHAR(50) | 否 | 否 | 否 | 否 |
| wages | 工资 | INT | 否 | 否 | 否 | 否 |
| contente | 工作内容 | TINYTEXT | 否 | 否 | 否 | 否 |
| exrta | 附加信息 | TEXT | 否 | 否 | 否 | 否 |
CREATE TABLE workinfo (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(20) NOT NULL,type VARCHAR(10),address VARCHAR(50),wages INT,contents TINYTEXT,extra TEXT,PRIMARY KEY (id DESC),UNIQUE KEY index_id (id)
);
(2)使用CREATE INDEX语句为name字段创建长度为10的索引indexname。
CREATE INDEX index_name ON workinfo(name(10)); SHOW INDEX FROM WORKINFO;
(3)使用 ALTER TABLE语句在type 和 address 字段上创建名为indext的索引。
ALTER TABLE workinfo ADD INDEX indext (type, address); SHOW INDEX FROM WORKINFO;
(4)使用 ALTER TABLE语句在extra字段上创建名为 index_ext的全文索引。
ALTER TABLE workinfo ADD FULLTEXT index_ext (extra); SHOW INDEX FROM WORKINFO;
(5)删除表 workinfo 的唯一性索引 index_id。
ALTER TABLE workinfo DROP INDEX index_id;SHOW INDEX FROM WORKINFO;
第三题
3.某工厂的信息管理数据库中有如下两个关系模式。
职工(职工号,姓名，年龄，月工资,部门号，电话，办公室)
部门(部门号，部门名，负责人代码，任职时间)
创建数据库表
-- 创建职工表
CREATE TABLE `职工` (`职工号` INT PRIMARY KEY,`姓名` VARCHAR(50) NOT NULL,`年龄` INT,`月工资` DECIMAL(10, 2), `部门号` INT, `电话` VARCHAR(20),`办公室` VARCHAR(50)
); -- 创建部门表
CREATE TABLE `部门` (`部门号` INT PRIMARY KEY,`部门名` VARCHAR(50) NOT NULL,`负责人代码` INT,`任职时间` DATE
);
(1)查询每个部门月工资最高的“职工号”的 SQL语句如下。
use goods; SELECT 职工号 FROM 职工 E WHERE 月工资=(SELECT MAX(月工资)FROM 职工 M WHERE M.部门号=E.部门号);
① 请用 30 字以内的文字简要说明该査询语句对查询效率的影响。
该查询每次查询都要对整个职工表进行扫描计算，效率较低。
②对该查询语句进行修改，使它既能实现相同功能，又能提高查询效率。
SELECT E.职工号 FROM 职工 E
INNER JOIN ( SELECT 部门号, MAX(月工资) AS 最高月工资 FROM 职工 GROUP BY 部门号) AS 最高工资
ON E.部门号 = 最高工资.部门号 AND E.月工资 = 最高工资.最高月工资;
(2)假定分别在“职工”关系中的“年龄”和“月工资”字段上创建索引,如下的 SELECI查询语句可能不会促使查询优化器使用索引，从而降低查询效率，请写出既能实现相同功能又能提高查询效率的 SQL 语句。
SELECT 姓名,年龄,月工资 FROM 职工 WHERE 年龄>35 or 月工资<1000;`
SELECT 姓名, 年龄, 月工资 FROM 职工 WHERE 年龄 > 35
UNION
SELECT 姓名, 年龄, 月工资 FROM 职工 WHERE 月工资 < 1000;