互联网技术学院周测机试题（二）
一、需求分析
为进一步完善连锁超市经营管理，提高管理效率，减少管理成本，决定开发一套商品管理系统，用于日常的管理。本系统分为商品管理、员工管理、店铺管理，库存管理等功能模块。本次开发，主要针对员工管理功能，具备查询员工信息、删除或修改员工信息等功能，具体数据库设计如下，请你根据需求完成数据库的设计与相应功能。
二、数据字典
表1：员工信息表（employee）
CREATE TABLE employee( empid int PRIMARY KEY auto_increment, empname VARCHAR(10) NOT NULL, phone VARCHAR(11), departmentid int, address VARCHAR(50), CONSTRAINT fk_dptid FOREIGN KEY(departmentid) REFERENCES department(dptid) );
表2：员工部门表（department）
CREATE TABLE department( dptid int PRIMARY KEY auto_increment, dptname VARCHAR(20) NOT NULL );
测试数据
employee表：
INSERT INTO employee VALUES (1, '李华', '13737445685', 1, '武汉市洪山区光谷大道288号'); INSERT INTO employee VALUES (2, '张建国', '18725489687', 3, '北京市朝阳区安贞路127号'); INSERT INTO employee VALUES (3, '周晓', '13985647124', 2, '昆明市五华区滇缅大道'); INSERT INTO employee VALUES (4, '李莉', '15896582415', 1, '武汉市汉阳区人民中路瑞典小镇'); INSERT INTO employee VALUES (5, '张晓红', '13865486874', 3, '');
department：
INSERT INTO department VALUES (1, '客服部'); INSERT INTO department VALUES (2, '财务部'); INSERT INTO department VALUES (3, '后勤部');
三、功能实现
1、经过层层面试，来自昆明市五华区茭菱路128号的张华，电话号码为15987415824，成功入职到公司的后勤部，请实现本功能。
INSERT INTO employee VALUES (null, '张华', '15987415824', 3, '昆明市五华区茭菱路128号');
2、显示名字中含有“晓”的客户信息，显示姓名及所在部门名称。
SELECT empname,dptname FROM employee e JOIN department d ON e.departmentid=d.dptid WHERE empname LIKE '%晓%'
3、统计各部门的人数，显示部门名称及人数，并按部门人数升序排序。
SELECT dptname,COUNT(*) FROM employee e,department d WHERE e.departmentid=d.dptid GROUP BY dptname ORDER BY dptname DESC
4、显示“周晓”的个人信息，包括姓名，电话和所在部门。
SELECT empname,phone,dptname FROM employee e,department d WHERE e.departmentid=d.dptid
完善“张晓红”的家庭住址为“广州市白云区北京东路”。
UPDATE employee SET address='广州市白云区北京东路' WHERE empname='张晓红'
数据库完整导入代码：
-- ---------------------------- -- Table structure for department -- ---------------------------- DROP TABLE IF EXISTS `department`; CREATE TABLE `department` ( `dptid` int(11) NOT NULL AUTO_INCREMENT, `dptname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, PRIMARY KEY (`dptid`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of department -- ---------------------------- INSERT INTO `department` VALUES (1, '客服部'); INSERT INTO `department` VALUES (2, '财务部'); INSERT INTO `department` VALUES (3, '后勤部'); -- ---------------------------- -- Table structure for employee -- ---------------------------- DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `empid` int(11) NOT NULL AUTO_INCREMENT, `empname` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL, `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, `departmentid` int(11) NULL DEFAULT NULL, `address` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`empid`) USING BTREE, INDEX `fk_dptid`(`departmentid`) USING BTREE, CONSTRAINT `fk_dptid` FOREIGN KEY (`departmentid`) REFERENCES `department` (`dptid`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of employee -- ---------------------------- INSERT INTO `employee` VALUES (1, '李华', '13737445685', 1, '武汉市洪山区光谷大道288号'); INSERT INTO `employee` VALUES (2, '张建国', '18725489687', 3, '北京市朝阳区安贞路127号'); INSERT INTO `employee` VALUES (3, '周晓', '13985647124', 2, '昆明市五华区滇缅大道'); INSERT INTO `employee` VALUES (4, '李莉', '15896582415', 1, '武汉市汉阳区人民中路瑞典小镇'); INSERT INTO `employee` VALUES (5, '张晓红', '13865486874', 3, '广州市白云区北京东路'); SET FOREIGN_KEY_CHECKS = 1;
