MySQL实验4 数据的查询

目的要求：
（1）掌握SELECT语句的基本用法
（2）掌握子查询的表示
（3）掌握连接查询的表示
（4）掌握SELECT语句的GEOUP BY子句的作用和使用方法
（5）掌握SELECT语句的ORDER BY子句的作用和使用方法
实验准备：
（1）了解SELECT语句的基本语法格式
（2）了解SELECT语句执行方法
（3）了解子查询的表示方法
（4）了解查询的表示方法
（5）了解SELECT语句的GROUP BY子句的作用和使用方法
（6）了解SELECT语句的ORDER BY子句的作用
（7）了解SELECT语句的LIMIT子句的作用
实验内容：
1.SELECT语句的基本使用
（1）用SELECT语句查询Departmrnts表的所有记录。（2分）

SELECT * FROM zhangyk_departments

（2）用SELECT语句查询Salary表的所有记录。（2分）

SELECT * FROM zhangyk_salary

（3）用SELECT语句查询Departments表的部门号和部门名称列。（2分）

SELECT departmentID,departmentName FROM zhangyk_departments

（4）查询Employees表中部门号和性别，要求使用DISTINCT消除重复行。（3分）

SELECT DISTINCT departmentID,sex 
FROM zhangyk_employees

（5）查询月收入高于2000的员工号码。（3分）

SELECT employmeeID from zhangyk_salary 
WHERE Income>2000

（6）查询所有1970以后出生的员工的姓名和住址。（3分）

SELECT name,address
FROM zhangyk_employees
WHERE brithday>='1970-01-01'
 

（7）查询所有财务部门的员工号码和姓名。（3分）

SELECT Employees,name
from zhangyk_employees e INNER JOIN zhangyk_departments d on
e.departmentID=d.departmentID

（8）查询Empoyees表中男员工的姓名和出生日期，要求个列标题用中文表示。（3分）

SELECT name 姓名,brithday 出生日期
FROM zhangyk_employees 
WHERE sex=1

（9）查询Employees员工的姓名住址和收入水平，2000以下的显示为低收入，2000`3000的显示为中等收入，3000以上的显示为高收入。（3分）

select  name,address,case when income<2000 then ;低收入;
 when income between 2000 and 3000 then ;中收入;
 when income>3000 then ;高收入;
end as ;收入水平; from Dongc_Employees inner join Dongc_Salary
on Dongc_Employees.EmployeesId=Dongc_Salary.employmeeID

（10）计算Salary表中员工月收入的平均数。（3分）

SELECT AVG(Income) from zhangyk_salary

（11）获得Employees表中的最大的员工号码。（3分）

SELECT MAX(Employees) from zhangyk_employees

（12）计算Salary表中所有员工的总支出。（3分）

SELECT SUM(Outcome)  总支出 FROM zhangyk_salary

（13）查询财务部官员的最高和最低实际收入。（3分）

SELECT MAX(Income-Outcome) 最高实际收入 ,min(Income-Outcome) 最低实际收入
FROM zhangyk_employees e ,zhangyk_salary s,zhangyk_departments d
WHERE e.employees=s.employmeeID
AND e.departmentID=d.departmentID
and departmentName='财务部'

（14）找出所有其地址含有“中山”的雇员的号码及部门号。（3分）

SELECT Employees,departmentID
from zhangyk_Employees
WHERE address LIKE '%中山%'

（15）查找员工号码中倒数第二个数字为0的姓名、地址和学历。（3分）

SELECT name,address,education
FROM zhangyk_Employees WHERE employees LIKE '%0_'

（16）找出所有部门“1”或“2”工作的雇员的号码。（3分）

SELECT employees
from zhangyk_Employees e inner JOIN zhangyk_departments d on
e.departmentID=d.departmentID
WHERE d.departmentID=1 or
d.departmentID=2

2.子查询的使用
（1）用子查询的方法查找所有收入在2500以下的雇员的情况。（5分）

SELECT * FROM zhangyk_Employees 
WHERE employees in(
SELECT employees FROM zhangyk_salary
WHERE Income<2500)

（2）用子查询的方法查找研发部比财务部所有雇员收入都高的雇员的姓名。（5分）

SELECT name
FROM zhangyk_employees e ,zhangyk_salary s,zhangyk_departments d
WHERE Income > all(
SELECT Income
from zhangyk_employees e ,zhangyk_salary s,zhangyk_departments d
WHERE e.employees=s.employmeeID
and e.departmentID=d.departmentID
and departmentName='财务部')
and e.employees= s.employmeeID
and e.departmentID=d.departmentID
and departmentName='研发部'

（3）用子查询的方法查找年龄比研发部所有雇员年龄都大的雇员的姓名。（5分）

SELECT name FROM zhangyk_employees
WHERE departmentID in(
SELECT departmentID FROM zhangyk_departments
WHERE departmentName!='研发部')
and brithday <=all(SELECT brithday FROM zhangyk_employees
WHERE departmentID in(SELECT departmentID FROM zhangyk_departments
WHERE departmentName='研发部'))

3.连接查询的使用
（1）查询每个雇员的情况及其工作部门的情况。（4分）

SELECT *
FROM zhangyk_employees e ,zhangyk_salary s,zhangyk_departments d
WHERE e.Employees=s.employmeeID
and e.departmentID=d.departmentID

（2）使用内连接的方法查找不在财务部工作的所有员工信息。（4分）

SELECT * from zhangyk_departments 
INNER JOIN zhangyk_employees on 
zhangyk_departments.departmentID=zhangyk_employees.departmentID
WHERE departmentName!='财务部'

（3）使用外连接方法查找所有员工的月收入。（4分）

SELECT Income 
from zhangyk_employees  LEFT OUTER JOIN zhangyk_salary s
on zhangyk_employees.Employees=s.employmeeID

（4）查询研发部在1966年以前出生的雇员姓名及其薪水详情。（4分）

SELECT name,income,outcome
from zhangyk_employees e ,zhangyk_salary s,zhangyk_departments d
WHERE e.Employees=s.employmeeID
and e.departmentID=d.departmentID
and departmentName ='研发部'
and brithday<'1966-01-01'

4.GROUP BY、ORDER BY和LIMIT子句的使用
（1）按部门列出在该部门工作的员工的人数。（4分）

SELECT departmentID,COUNT(departmentID) 人数
FROM zhangyk_departments
GROUP BY departmentID

（2）按员工的学历分组，列出本科、大专和硕士的人数。（4分）

SELECT education,COUNT(education) 人数
from zhangyk_employees
GROUP BY education

（3）按员工的工作年份分组，统计各个工作年份的人数，如工作1年的多少人，工作2年的多少人。（4分）

SELECT workyear,COUNT(workyear) 人数
FROM zhangyk_employees
GROUP BY workyear

（4）将员工信息按出生日期从小到大排列。（4分）

SELECT *
FROM zhangyk_employees
ORDER BY brithday DESC

（5）在ORDER BY子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列。（4分）

SELECT name,sex,workyear
from zhangyk_employees e ,zhangyk_salary s
WHERE e.Employees=s.employmeeID
ORDER BY (Income-Outcome) DESC

（6）返回Employees表中从第3位员工开始的5个员工的信息。（4分）、

SELECT * from zhangyk_employees
LIMIT 2,3

实验小结：
 注意分号为英文字符下的;在要求查询结果的属性列名用中文表示时，AS可加可不加;模糊查询时，还可用正则表达式;
 用子查询方法时,可在FROM后使用;
 在利用子查询方法时,可将问题分解,先写出框架,在具体实现.