MySQL数据库技术 第二版 章末 答案—单元5

admin管理员组

文章数量:1794759

MySQL数据库技术 第二版 章末 答案—单元5

MySQL数据库技术 第二版 章末 答案—单元5 以下是我个人所尝试过得答案，若有不正确的地方请告知，谢谢！

由于实在是太多内容了，原谅我省略题目内容

单元5 数据查询 ---- 实训5----P104 1、select语句的基本使用

– 1、

SELECT DISTINCT departmentID AS 员工部门号, sex AS 性别 FROM em_copy1;

– 2、

SELECT (InCome - OutCome) AS 实际收入 FROM salary;

– 3、

SELECT NAME AS 姓名, CASE WHEN sex=0 THEN "女" WHEN sex=1 THEN "男" END AS 性别 FROM employees;

– 4、

SELECT Address AS adress, PhoneNumber AS telephone FROM employees;

– 5、

SELECT AVG(InCome) AS 员工月收入平均数 FROM salary;

– 6、

SELECT SUM(OutCome) AS 员工总支出 FROM salary;

– 7、

SELECT Address AS 地址, PhoneNumber AS 电话号码 FROM employees WHERE sex=0;

– 8、

SELECT COUNT(employeeID) AS 员工总数 FROM employees;

– 9、

SELECT employeeID FROM salary WHERE ( InCome=( SELECT MAX(InCome) FROM salary ) ) OR ( InCome=( SELECT MIN(InCome) FROM salary ) );

---

2、条件查询

– 1、

SELECT employeeID FROM salary WHERE InCome>2000;

– 2、

SELECT NAME, Address, Birthday FROM employees WHERE (Birthday>="1971");

– 3、

/*方一*/ SELECT * FROM employees WHERE (WorkYear>=3) AND (sex=1) AND Education IN ("本科", "硕士", "博士"); /*方二*/ SELECT * FROM employees WHERE (WorkYear>=3) AND (sex=1) AND (Education="本科" OR Education="硕士" OR Education="博士") ;

– 4、

SELECT NAME, Address, Education FROM employees WHERE employeeID LIKE '%0_';

– 5、

SELECT employeeID, InCome FROM salary WHERE InCome BETWEEN 2000 AND 3000;

---

3、多表查询 USE yggl;

– 1、

-- 1、 ---方一 SELECT departments.DepartmentName, employees.* FROM employees JOIN departments ON ( employees.DepartmentID = departments.DepartmentID ) WHERE (NAME="王林"); -- 1、 ---方二 SELECT d.DepartmentName, e.* FROM employees AS e, departments AS d WHERE ( e.DepartmentID = d.DepartmentID AND NAME="王林" );

– 2、

SELECT employees.* FROM employees JOIN departments ON (employees.`DepartmentID`=departments.`DepartmentID`) WHERE departments.`DepartmentName` IN ("财务部", "研发部", "市场部");

– 3、

SELECT salary.`InCome` AS "薪水", employees.* FROM employees JOIN salary ON ( employees.`EmployeeID`=salary.`EmployeeID` );

– 4、

SELECT NAME AS "姓名", salary.`InCome` AS "薪水" FROM employees JOIN salary /*薪水*/ ON ( employees.`EmployeeID`=salary.`EmployeeID` ) JOIN departments/*部门*/ ON ( employees.`DepartmentID`=departments.`DepartmentID` ) WHERE ( departments.DepartmentName = "研发部" ) AND ( employees.Birthday < '1970' );

– 5、

SELECT employees.name AS "姓名", employees.Address AS "住址", CASE WHEN salary.`InCome` < 2000 THEN "低收入" WHEN ( salary.`InCome` >= 2000 AND salary.`InCome` <= 3000) THEN "中等收入" WHEN salary.`InCome` > 3000 THEN "高收入" END AS "收入水平" , salary.`InCome` AS "收入数据化" FROM employees, salary;

---

4、分类汇总与排序

– 1、

SELECT COUNT(departmentID) AS "人数" FROM employees GROUP BY departmentID;

– 2、

SELECT sex, COUNT(sex) AS "人数" FROM employees GROUP BY sex;

– 3、

SELECT employees.`DepartmentID`, departmentname AS "部门名称", COUNT(employees.`DepartmentID`) AS "员工人数" FROM departments JOIN employees ON (departments.`DepartmentID` = employees.`DepartmentID`) GROUP BY departmentname HAVING COUNT(*)>2;

– 4、

SELECT Education AS "学历", COUNT(*) AS "人数" FROM employees GROUP BY Education;

– 5、

-- 5、 --- 年龄越大，出生日期越小 SELECT * FROM employees ORDER BY Birthday ASC;

– 6、

SELECT InCome FROM salary ORDER BY InCome ASC;

– 7、

SELECT WorkYear, COUNT(*) AS 人数 FROM employees GROUP BY WorkYear ORDER BY 人数 ASC;

---

感谢观看！

本文标签： 单元数据库技术答案mySQL