【数据库】上机2：数据库的查询和视图

庚_

已于 2023-12-21 19:25:33 修改

阅读量995

点赞数 2

分类专栏：数据库原理实验文章标签：数据库 sql

于 2022-04-18 21:18:20 首次发布

本文链接：https://blog.csdn.net/Dershy_/article/details/123935893

版权

数据库原理实验专栏收录该内容

2 篇文章 0 订阅

订阅专栏

上机2：数据库的查询和视图

实验内容
- 实验要求
- - 代码

实验内容

（针对实验一的数据库进行如下操作。注意查询结果至少有一条以上，结果要截图）

实验要求

（1）查询员工的姓名、住址和收入水平，3000以下显示低收入、3000—7000显示中等收入、7000以上显示高收入；（提示：SELECT 语句中使用CASE语句，如
（2）查询财务部年龄不低于研发部雇员年龄的雇员姓名；
（3）查询财务部收入在5000元以上的雇员姓名及其薪水情况；
（4）查询财务部雇员的最高和最低收入；
（5）创建视图Employees_view，包含员工号码、姓名、实际收入三项，创建视图DS_VIEW，包含Departments的全部列；
（6）从视图Employees_view中查询姓名为‘王琳’（也可是其他人）的员工的实际收入；
（7）可否修改视图Employees_view，为什么？
（8）创建北京地区的员工视图BJ_view，包含员工号码、姓名、教育、出生日期和部门号，并使用With CHECK OPTION子句；
（9）尝试通过BJ_view插入一条员工信息、更新一条北京地区的员工的姓名和不是北京员工的姓名，观察执行结果，并对结果进行解释。

代码

create table Departments(
    DepartmentID char(3) not null primary key,
    DepartmentName char(20) not null,
    Note varchar(400)
);

create table Employees(
    EmployeeID char(6) not null primary key ,
    Name char(10) not null ,
    Education char(4) not null ,
    Birthday date not null ,
    Sex bit not null default (1),
    Workyer int ,
    Address varchar(40),
    PhoneNumber char(12),
    DepartmentID char(3) not null ,
    foreign key (DepartmentID)references Departments(DepartmentID)
);

create table Salary(
    EmployeeID char(6) not null primary key,
    Income float not null,
    Outcome float not null
	foreign key (EmployeeID)references Employees(EmployeeID)
);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('01','研发部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('02','市场部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('03','销售部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('04','咨询部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('05','生产部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('06','财务部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('07','人力资源部',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('08','经理办公室',NULL);

insert into Departments(DepartmentID,DepartmentName,Note)
Values('09','秘书办公室',NULL);

insert  into Departments(DepartmentID,DepartmentName,Note)
Values('10','技术部',Null)

insert  into Departments(DepartmentID,DepartmentName,Note)
Values('11','安全部',Null)

delete 
from Departments
where DepartmentName='安全部'

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365480','芋圆','本科','20010101','1','20200515','北京市海淀区','18025693164','02')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365480',6500,3800)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365481','麻薯','硕士','19870205','0','20020612','上海市普陀区','18647895462','03')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365481',7500,7000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365482','红豆','博士','20020203','1','20200401',' 北京市朝阳区建国门南大街6号','18025693369','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365482',8962,6500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365483','芋头','大专','20010215','1','20200603','北京市朝阳区平乐园100号','19825693164','06')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365483',3500,2000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365484','茶冻','硕士','20010622','1','20180912','定福庄东街1号','14565693164','01')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365484',7500,3600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365485','奶盖','本科','20010711','1','20190302','北京市朝阳区惠新东街10号','17525693164','10')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365485',4500,3600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365486','布丁','本科','19830506','1','19920304','西藏中路268号上海来福士广场01-01D','18321453164','08')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365486',3246,3800)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365487','冰激凌','大专','19920331','1','20120328','西藏中路268号上海来福士广场01-01D','18167693164','09')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365487',4500,4000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365488','爆珠','本科','20010911','1','20200515','淞沪路111号万达广场C座1312号','13025693164','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365488',5000,7000)

insert 
into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165489','珍珠','本科','20010131','1','20200303','徐家汇路618号日月光中心广场泰康区1层','15921693164','05')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165489',6500,2500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165490','波霸','硕士','20031202','1','20200404','上海漕宝路3366号七宝万科广场1层','13695543164','06')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165490',8700,5600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365491','双皮奶','本科','20010314','1','20200718','上海漕宝路3366号七宝万科广场1层','13445693164','07')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365491',5400,2500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165492','绵绵冰','博士','20020214','1','2021912','南京西路1601号芮欧百货5楼','17895664164','01')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165492',9500,4500)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('123493','巧克力','本科','20030302','1','20220214','南京西路1601号芮欧百货5楼','13524593164','02')
insert into Salary(EmployeeID,Income,Outcome)
Values ('123493',5000,4000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('365494','苹果','本科','19980302','1','20200614',' 肇嘉浜路1111号美罗城B区1层','14525653164','03')
insert into Salary(EmployeeID,Income,Outcome)
Values ('365494',6500,5600)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165495','椰子','本科','20030201','1','20201009','长宁路1018号龙之梦购物中心','13425693564','10')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165495',15000,5000)

insert into Employees(EmployeeID,Name,Education,Birthday,Sex,Workyer,Address,PhoneNumber,DepartmentID)
Values('165496','麦芬','本科','20010112','1','20201203','欣宁街15号北京荟聚F1','18854193135','09')
insert into Salary(EmployeeID,Income,Outcome)
Values ('165496',8650,5460)

update Employees
set Address = '上海市普陀区121号'
where Name='麻薯';

update Employees
set Birthday = '20031202'
where Name='波霸'
-----------------------------------------------------------------查询
delete from Salary
where EmployeeID ='165496';
delete from Employees 
where EmployeeID='165496';

select *
from Employees

select PhoneNumber,Address
from Employees

select EmployeeID
from Salary
where Income>6000;

select EmployeeID,Income-OutCome 'RealCome'
from Salary

----------------查询财务部正确格式
select COUNT(*)
from Employees
where DepartmentID in 
(
	select DepartmentID
	from Departments
	where Departments.DepartmentName='财务部'
)

select Name '姓名',Birthday '出生日期'
from Employees
where Sex=0;

alter table Salary add Name char(6);

update Salary
set Name = '椰子'
where EmployeeID='165495';
update Salary
set Name = '巧克力'
where EmployeeID='123493';

-----------------------------------------------------------删库
alter table Salary drop column Name;
--drop table Salary;
--drop table Employees;
--drop table Departments;
--drop database YGGL;
----------------------------------------------------------------------------------------------------------作业2

--查询员工的姓名、住址和收入水平，3000以下显示低收入、3000—7000显示中等收入、7000以上显示高收入；

select Employees.Name as '姓名' , Address as '住址' , Income as '收入水平' ,Income = case
  when Income < 3000 then '低收入'
  when Income >=3000 and Income <=7000 then '中等收入'
  when Income >7000 then '高收入'
  end
from Employees,Salary
where Employees.EmployeeID=Salary.EmployeeID


------------------------------------学习两种日期查询方式
select GETDATE()
select year(GETDATE()),MONTH(GETDATE())as month,day(GETDATE())as'日'

--（2）查询财务部年龄不低于研发部雇员年龄的雇员姓名；
select Name as '财务部',year(GETDATE())-year(Birthday) as '年龄'
from Employees,Departments
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='财务部'

select Name as '研发部',year(GETDATE())-year(Birthday) as '年龄'
from Employees,Departments
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='研发部'

select Name as '财务部年龄不低于研发部雇员年龄的雇员姓名',year(GETDATE())-year(Birthday) as '年龄'
from Employees,Departments
where  Employees.DepartmentID=Departments.DepartmentID  and DepartmentName='财务部' and Birthday 
	< all (select Birthday
	from Employees,Departments
	where Employees.DepartmentID=Departments.DepartmentID and DepartmentName='研发部')

--（3）查询财务部收入在5000元以上的雇员姓名及其薪水情况
select Name as '财务部',Income as '薪水'
from Employees,Departments,Salary
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='财务部'and Employees.EmployeeID=Salary.EmployeeID


select Name as '姓名',Income as '薪水'
from Employees e,Salary s,Departments d
where e.EmployeeID=s.EmployeeID and Income>5000 and e.DepartmentID=d.DepartmentID and DepartmentName='财务部'

--（4）查询财务部雇员的最高和最低收入；

select Name as '财务部',Income as '薪水'
from Employees,Departments,Salary
where Departments.DepartmentID=Employees.DepartmentID and DepartmentName='财务部'and Employees.EmployeeID=Salary.EmployeeID

select max (Income) as'财务部最高薪水',min(Income) as '财务部最低薪水'
from Employees e,Departments d ,Salary s
where e.DepartmentID=d.DepartmentID and DepartmentName='财务部' and e.EmployeeID=s.EmployeeID

--（5）创建视图Employees_view，包含员工号码、姓名、实际收入三项，创建视图DS_VIEW，包含Departments的全部列；

go
create view Employees_view(EmployeeID,EmployeeName,RealIncome)
as 
select Employees.EmployeeID,Employees.Name,Salary.Income-Salary.Outcome
from Employees,Salary
where Employees.EmployeeID=Salary.EmployeeID

go 
create view DS_VIEW
as
select*
from Departments

--（6）从视图Employees_view中查询姓名为‘王琳’（也可是其他人）的员工的实际收入；
select EmployeeName as '名字',RealIncome as '真实收入'
from Employees_view
where EmployeeName='巧克力'

--（7）可否修改视图Employees_view，为什么？
update Employees_view
set EmployeeName='牛奶巧克力'
where EmployeeName='巧克力'

/*--转换后的更新
update Employees
set Name='牛奶巧克力'
where Name='巧克力'
*/
--失败的更新
update Employees_view
set EmployeeID=123
where EmployeeName='牛奶巧克力'

--（8）创建北京地区的员工视图BJ_view，包含员工号码、姓名、教育、出生日期和部门号，并使用With CHECK OPTION子句；

go
create view BJ_view(员工号码,姓名,教育,出生日期,部门号)
as select Employees.EmployeeID,Name,Education,Birthday,DepartmentID
from Employees
where Employees.Address like '北京市%'
with check option

--（9）尝试通过BJ_view插入一条员工信息
insert into BJ_view
values(1234,'牛肉汉堡','硕士','2002-02-14','03')

--更新一条北京地区的员工的姓名和不是北京员工的姓名
update BJ_view
set 姓名 = '鸡蛋汉堡'
where 姓名 ='芋圆'

update BJ_view   
set 姓名 = '巧克力'
where 姓名 = '牛奶巧克力'

select Name,Education,Birthday,Address
from Employees