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;
	

---

感谢观看！