mysql数据库实验7

3． 在xs表中查找学号为081104和学号为081221的两位同学的信息。

select  学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号='081104'
union
select  学号,姓名,专业名,性别,出生日期,总学分
from xs
where 学号='081221';

4. 一行一行地浏览KC表中满足要求的内容，要求第一行为学分大于4的第一行数据，再读取下一行，最后关闭该表。

use xscj
handler kc open;

handler kc read first
where 学分>4;

handler kc read next;

handler kc close;

5. 在xs表中查找姓名为李明和姓名为李计的两位同学的信息。

select  学号,姓名,专业名,性别,出生日期,总学分
from xs
where 姓名='李明'
union
select  学号,姓名,专业名,性别,出生日期,总学分
from xs
where 姓名='李计';

6. 一行一行地浏览xs_kc表中满足要求的内容，要求第一行为成绩大于的等于90分的第一行数据，再读取下一行，最后关闭该表。

use xscj
handler xs_kc open;

handler xs_kc read first
where 成绩>=90;

handler xs_kc read next;

handler xs_kc close;

7. 在YGGL数据库中查找Employees表中编号为020010和编号为102208的两位员工的信息。

select  employeeid,name,education,birthday,sex,workyear,address
from employees
where employeeid='020010'
union
select  employeeid,name,education,birthday,sex,workyear,address
from employees
where employeeid='102208';

8． 一行一行地浏览salary表中满足要求的内容，要求第一行为收入大于2500的第一行数据，再读取下一行，最后关闭该表。

use yggl
handler salary open;

handler salary read first
where income>=2500;

handler salary read next;

handler salary close;

9. 查询财务部雇员的最高和最低实际收入。

select max(income-outcome) as '最高实际收入',min(income-outcome) as '最低实际收入'
from salary
where employeeID in
 (select employeeID from employees 
where departmentID =
(select departmentID from departments 
where departmentName='财务部'));

10. 查找所有收入在2500元以上的员工情况。

select * from 
employees,salary
where salary.employeeid =employees .employeeid
and income>2500;

11. 查找员工编号中第三个数字为2的员工编号、姓名、学历和地址。

select employeeid,name,education,address
from employees
where employeeid like'%2__';

12. 查询所有研发部的员工的编号和姓名。

select employeeid,name
from employees
where departmentid  =(select departmentid from departments 
where departmentName='研发部');

13. 查询部门名称为财务部或研发部的员工的情况。

select *
from employees
where departmentid=
(select departmentid from departments 
where departmentName='财务部' )
union
select *
from employees
where departmentid=
(select departmentid from departments 
where departmentName='研发部' );

14. 查找比所有研发部的员工的工作时间都长的员工的信息。

select * from employees
where workyear > all
(select workyear from employees where departmentID in
(select departmentID from departments where departmentName ='研发部'));

15. 使用union语句，在Employees表中查找学历是本科和学历是硕士的员工的信息。

select  employeeid,name,education,birthday,sex,workyear,address
from employees
where education='本科'
union
select  employeeid,name,education,birthday,sex,workyear,address
from employees
where education='硕士';

16. 使用union语句，在Employees表中查找学历是本科工作时间最长的前3位和学历是硕士工作时间最长的1位员工的信息。

(select * from employees
where education='本科'
order by workyear desc
limit 3 )
union all
(select * from employees
where education='硕士'
order by workyear desc
limit 1);