数据库实验：数据查询和视图

一、实验内容
基本查询任务
1、查询每个雇员的姓名、地址和联系电话
2、查询employee表中部门号和性别，要求使用DISTINCT消除重复行
3、查询所有财务部的员工的姓名和联系电话
4、查询employee表中女雇员的地址和电话，使用AS子句将结果中各列 的标题指定为“地址”和“电话”
5、查询财务部雇员的最高和最低实际收入
6、找出所有收入在2000到3000元之间的员工编号

子查询的使用
1、查找在财务部工作的员工的情况
2、用子查询方法查找所有收入在2500

连接查询的使用
1、查询每个雇员的情况及其薪水情况
2、使用内连接查询名字为“王林”的员工所在部门
3、查找财务部收入在2000元以上的雇员姓名和薪水详情

GROUP BY、ORDER BY和LIMIT子句的使用
1、查询employee中男性和女性的人数
2、查找员工数超过2人的部门名称和员工数量
3、将employee表中的员工号码由大到小排列
4、返回employee表中前5

创建视图
创建DBEM数据库上的视图v_dp，包含department的全部信息
创建DBEM数据库上的视图v_em，包含员工号码、姓名和实际收入

查询视图
从v_em视图中查询姓名为“王林”的员工的实际收入

更新视图
1、向v_dp视图中插入一行数据：6，广告部，推广产品。执行完之后分 别查看视图v_dp和表department中发生的变化
2、尝试向v_em视图中插入一行数据，看看会发生什么情况
3、修改视图v_em中号码为000001的雇员的姓名为“王浩”
4、删除视图v_dp中部门号为“1

思考与练习
1、如果视图关联了一个表中的所有字段，而该表中添加了新字段，视图 中是否能查询到新字段？
2、视图v_em中无法插入和删除数据，为什么？

二、实验过程
1、use dbem;
select name,address,phone
from employee;

2、select distinct departmentID,gender
from employee;

3、select name,phone
from employee
where departmentID=1;

4、select address as ‘地址’,phone as ‘电话’
from employee
where gender=0;

5、select MAX(income-outcome) as ‘最高收入’,
MIN(income-outcome) as ‘最低收入’
from salary
where employeeID in
(select employeeID
from employee
where departmentID=1);

6、select employeeID
from salary
where income between 2000 and 3000;

子查询：
1、select *
from employee
where departmentID=1;

2、select *
from employee
where employeeID in
(select employeeID
from salary
where income<2500);

连接查询：
1、select *
from employee join salary on employee.employeeID=salary.employeeID;

2、select b.departName
from department b
inner join employee a
on a.departmentID=b.departmentID
where a.name=‘王林’;

3、select employee.name,salary.income,salary.outcome
from employee join salary on employee.employeeID=salary.employeeID
where income>2000 and departmentID=1;

子句使用：
1、select gender,count(gender)
from employee
group by gender;

2、select b.departName,count(a.employeeID)
from employee a,department b
where a.departmentID=b.departmentID
group by b.departName
having count(a.employeeID)>2;

3、select *
from employee
order by employeeID desc;

4、select *
from employee
order by employeeID
limit 0,5;

创建视图：use dbem;
create view v_dp
as select *
from department;
create view v_em(employeeID,name,realincome)
as select a.employeeID,a.name,b.income-b.outcome
from employee a,salary b
where a.employeeID=b.employeeID;

查询视图：select realincome
from v_em
where name=‘王林’;

更新视图：1、insert into v_dp
values(‘6’,‘广告部’,‘推广产品’);

视图：

2、insert into v_em
values(‘111111’,‘小李’,‘广告部’);

3、SET SQL_SAFE_UPDATES = 0;
update v_em
set name=‘王浩’
where employeeID=1;

4、delete from v_dp
where departmentID=1;

三、思考题
思考题1：试图关联表中的所有字段，表中添加了新字段后，视图中也有新字段
insert into department
values(‘7’,‘摸鱼部’,‘NULL’);


思考题2：不能对视图v_em进行删除和插入，因为修改会影响多个基表。