sql server 第五版(2008) 实验四

use YGGL
go

-- 1.基本查询

-- (1)

select * from Employees

select * from Departments

select * from Salary

-- (2)

select Address,PhoneNumber
from Employees

select DepartmentName
from Departments

select InCome,OutCome
from Salary

select DISTINCT DepartmentID,Sex
from Employees

-- (3)

select Address, PhoneNumber
from Employees
where EmployeeID='000001'
go

select EmployeeID
from Salary
where InCome>'2000'

select Name, Address
from Employees
where Birthday>'1970-12-31'

select EmployeeID, Name
from Employees
where DepartmentID in(
select DepartmentID
from Departments
where DepartmentName='财务部')

-- (4)

select Address AS 住址, PhoneNumber AS 电话
from Employees
where Sex=0

select Name AS 姓名,Birthday AS 出生日期
from Employees
where Sex=1

-- (5)

select Name AS 姓名,
case
when Sex=1 then '男'
when Sex=0 then '女'
end as 性别
from Employees

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

-- (6)

select EmployeeID,实际收入=InCome-OutCome
from Salary

select EmployeeID,额外工作时间=12-WorkYear
from Employees

-- (7)

select COUNT(*)
from Employees

select avg(InCome)
from Salary

select min(Birthday)
from Employees

select SUM(OutCome)
from Salary

select MAX(InCome-OutCome) as 最高实际收入,MIN(InCome-OutCome) as 最低实际收入
from Salary

-- (8)

select DepartmentID
from Employees
where Name like '王%'

select EmployeeID,DepartmentID
from Employees
where Address like '中山%'

select Name,Address,Education
from Employees
where EmployeeID like '%0_'

-- (9)

select EmployeeID
from Salary
where InCome BETWEEN 2000 and 3000 

select EmployeeID
from Employees
where DepartmentID='1'or DepartmentID='2'

-- (10)

select EmployeeID as 编号,InCome as 收入
into 收入在1500元以上的员工
from Salary
where InCome>1500

select EmployeeID as 员工编号,Name as 姓名
into 男员工
from Employees
where Sex=1

-- 2.子查询

-- (1)

select *
from Employees
where DepartmentID = 
      (
         select DepartmentID
          from Departments
          where DepartmentName = '财务部'
          )

select *
from Employees
where EmployeeID in
      (
          select EmployeeID
          from Salary
          where InCome < '2500'
          )          
          
          
-- (2)

select Name
from Employees
where DepartmentID IN
        (
        select DepartmentID
        from Departments
        where DepartmentName ='财务部'
        )
        AND
        Birthday !> ALL
        (
          select Birthday
          from Employees
          where DepartmentID IN
          (
           select DepartmentID
           from Departments
           where DepartmentName = '研发部'
           )
         )
         
         
Select name
From Employees
Where DepartmentID in
      ( 
      select DepartmentID
        From Departments
        Where DepartmentName='研发部'
       )
   AND 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='财务部'
       )
       )
       )
       )
       
       
-- (3)

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 =
         (
          select DepartmentID
          from Departments
          where DepartmentName = '财务部'
          )
          )
          )
          )
          
          
select Name
from Employees
where Birthday < all
      (
       select Birthday
       from Employees
       where DepartmentID in
       (
       select DepartmentID
       from Departments
       where DepartmentName = '研发部'
       )
       ) 
  
  
       
-- 3.连接查询

-- (1)

select Employees.*,Salary.*
from Employees,Salary
where Employees.EmployeeID = Salary.EmployeeID

select Employees.*,Departments.*
from Employees,Departments
where Employees.DepartmentID = Departments.DepartmentID

--(2)

select DepartmentName
from Departments join Employees on Departments.DepartmentID = Employees.DepartmentID
where Employees.Name = '王林'

select *
from Employees join Departments on Employees.DepartmentID = Departments.DepartmentID
where Departments.DepartmentName != '财务部'

select Employees.Name,InCome
from Employees left outer join Salary on Employees.EmployeeID = Salary.EmployeeID

-- (3)

select Name,InCome,OutCome
from Employees,Salary,Departments
where Employees.EmployeeID = Salary.EmployeeID
  and Employees.DepartmentID = Departments.DepartmentID
  and DepartmentName = '财务部'
  and InCome > 2000

select Name,InCome,OutCome
from Employees,Salary,Departments
where Employees.EmployeeID = Salary.EmployeeID
  and Employees.DepartmentID = Departments.DepartmentID
  and DepartmentName = '研发部'
  and Birthday < '1976-01-01'

-- 4.使用聚合函数查询

--（1）

select AVG(InCome) as '财务部平均收入'
from Salary
where EmployeeID in
(
 select EmployeeID
  from Employees
  where DepartmentID =
  (
   select DepartmentID
   from Departments
   where DepartmentName = '财务部'
   )
   )

select MAX(InCome) as '最高收入',MIN(InCome) as '最低收入'
from Salary
where EmployeeID in
(
 select EmployeeID
  from Employees
  where DepartmentID in
  (
   select DepartmentID
    from Departments
    where DepartmentName = '财务部'
    )
 ) 

-- (2)

select AVG(InCome - OutCome) as '财务部平均实际收入'
from Salary
where EmployeeID in
(
 select EmployeeID
 from Employees
 where DepartmentID =
 (
  select DepartmentID
  from Departments
  where DepartmentName = '财务部'
  )
 )       

select MAX(InCome -OutCome) as '最高实际收入',MIN(InCome - OutCome) as '最低实际收入'
from Salary
where EmployeeID in
(
 select EmployeeID
 from Employees
 where DepartmentID in
 (
  select DepartmentID
  from Departments
  where DepartmentName = '财务部'
  )
 )
 
 
-- (3)

select COUNT(EmployeeID)
from Employees
where DepartmentID =
(
 select DepartmentID
 from Departments
 where DepartmentName = '财务部'
 )
 
 
 select COUNT(Employees.EmployeeID)
 from Employees,Salary,Departments
 where Employees.EmployeeID = Salary.EmployeeID
   and Employees.DepartmentID = Departments.DepartmentID
   and Departments.DepartmentName = '财务部'
   and Salary.InCome >2500
   
   

-- 5.查询结果分组和排序

-- (1)

select Sex,COUNT(Sex)
from Employees
group by Sex

select DepartmentName,COUNT(*)
from Employees,Departments
where Employees.DepartmentID = Departments.DepartmentID
group by DepartmentName

select Education,COUNT(*)
from Employees
group by Education

-- (2)

select Employees.DepartmentID,COUNT(*) as '人数'
from Employees,Departments
where Employees.DepartmentID = Departments.DepartmentID
group by Employees.DepartmentID
having COUNT(*) > 2

select WorkYear,COUNT(*) as '人数'
from Employees
group by WorkYear

-- （3）

select Employees.*,Salary.*
from Employees,Salary
where Employees.EmployeeID = Salary.EmployeeID
order by InCome

select Employees.*
from Employees
order by Birthday desc

select Name as 姓名 ,Sex as 性别,WorkYear as 工龄
from Employees,Salary
where Employees.EmployeeID = Salary.EmployeeID
order by InCome desc

-----视图

-- 1.创建视图

-- (1)

create view DS_VIEW
    as select * from Departments

--（2）

create view Employees_view(EmployeeID,Name,RealIncome)
    as 
    select Employees.EmployeeID,Name,InCome - OutCome
    from Employees,Salary
    where Employees.EmployeeID = Salary.EmployeeID   

select语句的限制：
  定义视图的用户必须对所参照的表或视图有查询（即可执行 select 语句）权限
  不能使用 compute 或 compute by 子句
  不能使用 order by 子句
  不能使用 into 子句
  不能在临时表或表变量上创建视图
  
  
创建视图时的注意点：
  只有在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名
  不能把规则，默认值或触发器与视图相关联
  

create view text_view(员工编号,姓名,所在部门,实际收入)
  as
  select Employees.EmployeeID,Name,DepartmentName,InCome - OutCome
  from Employees,Salary,Departments
  where Employees.EmployeeID = Salary.EmployeeID
    and Employees.DepartmentID = Departments.DepartmentID

-- 2.查询视图

-- (1)

select DepartmentName
from DS_VIEW
where DepartmentID = '3'

-- （2）

select RealIncome
from Employees_view
where Name = '王林'

在使用视图查询时，若关联的基本表中添加了新字段，则必须 重新创建视图 才能查询到新字段

select 员工编号,实际收入
from text_view
where 所在部门 = '财务部'

-- 3.更新视图

-- (1)
insert into DS_VIEW values ('6','广告部','广告业务')

select *
from DS_VIEW

select *
from Departments

insert into Employees_view values('604209','小黄','2000')
消息 4406，级别 16，状态 1，第 1 行
对视图或函数 'Employees_view' 的更新或插入失败，因其包含派生域或常量域。
   当视图所依赖的基本表有多个时，不能 向该视图插入数据，因为这将会影响多个基本表

-- (2)

update DS_VIEW
set DepartmentName = '生产车间'
where DepartmentID = '5'

select *
from DS_VIEW

select *
from Departments 

把数据改回来，方便后面用
update DS_VIEW
set DepartmentName = '市场部'
where DepartmentID = '5'

-- （3）
update Employees_view
set Name = '王浩'
where EmployeeID = '000001'

select *
from Employees_view

select *
from Employees

把数据改回来，方便后面用
update Employees_view
set Name = '王林'
where EmployeeID = '000001'

-- （4）
delete from DS_VIEW
where DepartmentID = '6'

select *
from DS_VIEW

select *
from Departments 

 当视图所依赖的基本表有多个时，不能 向该视图插入数据，因为这将会影响多个基本表

-- 4.删除视图

drop view DS_VIEW