实验四、数据库的查询

实验四、数据库的查询

一、select语句的基本使用

1.查询Departments和Salary表的所有记录。

select *
from Departments,Salary;

2.查询Employees表中的所有部门号。

select distinct DepartmentID	//distinct消除重复的行
from Employees;

3.查询月收入高于2000元的员工编号。

select EmployeeID,Income
from salary
where Income>2000;

4.查询1970年以后出生的员工姓名和住址。

select name,Birthday,address
from Employees
where year(birthday)>1970;

5.查询财务部所有员工的编号和姓名。

select EmployeeID,name
from Employees,Departments
where DepartmentName="财务部" and Departments.DepartmentID=Employees.DepartmentID;

6.查询所有员工的姓名和性别，要求Sex值为1时显示为“男”，为0时显示为“女”。

select name as 姓名,
	case
	when sex='1' then '男'
	when sex='0' then '女'
	end as 性别
from Employees;

7.查询员工的姓名、住址和收入水平，2000元以下显示为低收入，2000—3000元显示为中等收入，3000元以上显示为高收入。

select name,address,
	case
	when income<2000 then '低收入'
	when income between 2000 and 3000 then '中等收入'
	when income>3000 then '高收入'
	end as 收入水平
from Employees,salary
where Employees.EmployeeID=salary.EmployeeID;

8.计算财务部员工的最高和最低实际收入。

select max(income-outcome) as 最高实际收入,min(income-outcome) as 最低实际收入
from Employees,salary,Departments
where DepartmentName="财务部" and Employees.EmployeeID=salary.EmployeeID and Departments.DepartmentID=Employees.DepartmentID;

9.找出所有姓王的员工的部门号。

select name,Employees.DepartmentID
from Departments,Employees
where name like '王%' and Departments.DepartmentID=Employees.DepartmentID;

二、子查询的使用

1.查询在研发部工作的员工的情况。

select *
from Employees
where DepartmentID=
	( select DepartmentID
	  from Departments
	  where Departmentname='研发部'
	);

2.查询研发部比市场部所有员工收入都高的员工的姓名。

select name
from Employees
where EmployeeID in
	(
	  select EmployeeID
	  from salary
	  where income>all( select income
			     	   from salary
			    	   where EmployeeID 
                    in( select EmployeeID
					  from Employees
					  where DepartmentID 
                    in ( select DepartmentID
					   from Departments
					   where Departmentname='市场部')))) 
	  and DepartmentID in (select DepartmentID
		                  from Departments											where Departmentname='研发部');

3.查询年龄比市场部所有员工年龄都大的员工姓名。

select name
from Employees
where birthday<all( select birthday
		    	  from Employees
		    	  where DepartmentID 
                in( select DepartmentID
				  from Departments
				  where Departmentname='市场部'));

三、连接查询的使用

1.查询每个员工的情况及其工作部门的情况。

select Employees.*,Departments.*
from Employees join Departments
using(DepartmentID);

2.使用内连接查找不在广告部工作的所有员工信息。

select Employees.*
from Employees inner join Departments
on(Employees.DepartmentID=Departments.DepartmentID)
where DepartmentName !='市场部';

3.使用外连接查找所有员工的月收入。

select income
from  Employees right join salary
on Employees.EmployeeID=salary.EmployeeID;

四、group by、order by和limit字句的使用

1.查找员工中男性和女性的人数。

select sex,count(sex)
from Employees
group by sex;

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

select Education,count(Education)
from Employees
group by Education;

3.将员工信息按照年龄大小排列。

select Employees.*
from Employees
order by birthday asc;

4.在order by子句中使用子查询，查询员工的姓名、性别和工龄信息，要求按实际收入从大到小排列。

select name,sex,workyear
from Employees
order by (select income-outcome 
	    from salary
	    where Employees.EmployeeID=salary.EmployeeID)  desc;

5.查询Employees表中从第3位员工开始的5个员工的信息。

select Employees.*
from Employees
limit 2,6;    //从第三行到到第八行