sql server view视图 if语句 sql server视图查询

转载

编程小匠人 2023-11-03 07:42:27

文章标签 sql 字段 Group Server 文章分类 SQL Server 数据库

SQL Server 实验四数据库查询和视图

这是一个系列，需几个文档一起看

--1、基本查询
--（2）
Select Address,PhoneNumber from Employees
--查询Employees中部门编号和性别，要求用Distinct消除重复行
Select distinct DepartmentID,sex from Employees
--（3）
--查询EmployeeID为000001的雇员地址和电话
Select Address,PhoneNumber from Employees
Where EmployeeID='000001'
--查询月收入高于2000元的员工编号
Select EmployeeID from Salary
Where InCome>2000
--查询1970年以后出生的员工的姓名和住址
Select Name,Address from Employees
Where Birthday>'1970'
--查询所有财务部员工的编号和姓名
Select EmployeeID,Name from Employees
Where DepartmentID=
(Select DepartmentID from Departments
Where DepartmentName='财务部')
--（4）
Select Address As 住址,PhoneNumber As 电话 from Employees
Where Sex=0
--查询Employees中男员工的姓名和出生日期，要求将各列标题用中文示
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 姓名,性别=
Case
	When Sex=1 Then '男'
	When Sex=0 Then '女'
	end 
from Employees

--查询Employees员工的姓名、住址和收入水平，2000元以下显示低收入，2000到3000显示中等收入，3000以上显示高收入
Select E.Name 姓名,E.Address 住址,收入水平=
	Case
		When S.Income<2000 Then '低收入'
		When S.InCome between 2000 and 3000 Then '中等收入'
		When S.InCome>3000 Then '高收入'
		end
	from Employees as E,Salary as S
	Where E.EmployeeID=S.EmployeeID
--（6）
Select EmployeeID,实际收入=Income-Outcome from Salary
--（7）
Select COUNT(*)as 员工总数 from Employees
--计算salary员工月收入平均数
Select AVG(Income) 平均工资 from Salary
--获得Employees中年龄最大的员工编号
Select EmployeeID from Employees
WHere Birthday=
(Select MAX(Birthday) from Employees)
--计算salary中所有员工的总支出
Select SUM(Outcome) 总支出 from Salary
--查询财务部雇员的最高和最低实际收入
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='财务部'))
--（8）
Select departmentID from Employees Where Name like '文%'
--查询所有其住址中含中山的雇员员工编号和部门编号
Select EmployeeID as 雇员编号,departmentID as 部门编号 from Employees
Where Address like '%中山%'
--查找员工编号中倒数第二个数字为0的员工的姓名、住址和学历
Select Name,Address,education from Employees
Where Substring(EmployeeID,5,1)='0'
--substring用于截取部分，字符，起始位置，长度
--（9）
Select EmployeeID from Salary
Where Income between 2000 and 3000
--找出所有在部门1或2工作的雇员的员工编号
Select EmployeeID from Employees
Where DepartmentID between 1 and 2
--（10）
Select employeeID as 编号,Income as 收入 
Into 收入在1500以上的员工
from salary
Where income>1500
--创建由employees创建的男员工表，包括编号和姓名
Select EmployeeID as 编号,Name as 姓名 Into 男员工表 from Employees
Where Sex=1
Select * from 男员工表
--2、子查询
--（1）
Select * from Employees
Where DepartmentID=
(
Select DepartmentID from Departments
Where DepartmentName='财务部')
--查找所有收入在2500以下的雇员的情况
Select * from dbo.Departments
Select * from dbo.Employees
Select * from dbo.Salary 
Select * from Employees
Where EmployeeID in
(
Select EmployeeID from Salary
Where InCome<2500)
--（2）
Select Name from Employees
Where DepartmentID =
(Select DepartmentID from Departments
Where DepartmentName='财务部')
and Birthday>=any(
Select birthday from Employees
Where DepartmentID =
(Select DepartmentID from Departments
Where DepartmentName='研发部'))
--查找研发部比所有财务部雇员收入都高的雇员的姓名
Select Name from Employees Where EmployeeID in (
Select EmployeeID from Salary Where EmployeeID in(
Select EmployeeID from Employees Where DepartmentID in(
Select DepartmentID from Departments Where DepartmentName='研发部'))
and 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.DepartmentID=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 Employees.* from Employees Join Departments on Employees.DepartmentID=Departments.DepartmentID
Where DepartmentName!='财务部'
--查找所有员工的月收入
Select * from Employees left outer Join Salary on Employees.EmployeeID=Salary.EmployeeID
--（3）
--查找财务部收入在2000以上的员工姓名及其薪水详情
Select Name,Income,OutCome from Employees,Salary,Departments
Where Employees.employeeID=Salary.EmployeeID
and employees.DepartmentID=Departments.DepartmentID
and DepartmentName='财务部'
and InCome>2000
--查找研发部在1976年以前出生的雇员姓名及其薪水详情
Select Name,Income,OUtcome from Employees,Salary,Departments
Where Employees.DepartmentID=Departments.DepartmentID
and Employees.EmployeeID=Salary.EmployeeID
and Employees.Birthday<'1976'
--4、聚合函数
--（1）
Select AVG(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='财务部'))
--（3）
Select COUNT(EmployeeID) from Employees Where DepartmentID=
(Select DepartmentID from Departments Where DepartmentName='财务部')
--5、查询结果分组和排序
--（1）
Select Sex, COUNT(Sex) '人数' from Employees
group by Sex
--按部门列出该部门的员工人数
Select Departments.DepartmentName,count(Employees.EmployeeID) '部门人数' from Departments join Employees 
on Departments.DepartmentID=Employees.DepartmentID
Group by DepartmentName
--按员工学历分组排列出本、硕、专人数
Select Education,COUNT(*) '人数' from Employees
group by Education
--（2）
Select Employees.DepartmentID,COUNT(*) '人数' from Employees,Departments
Where Employees.DepartmentID=Departments.DepartmentID
Group by Employees.DepartmentID
having COUNT(*)>2 
--按员工工作年份分组，统计各个年份的人数
Select WorkYear,COUNT(*) '人数' from Employees
Group by WorkYear
--（3）
Select Employees.*,Salary.* from Employees,Salary
Where Employees.EmployeeID=Salary.EmployeeID
Order by InCome
--将员工信息按出生日期从小到大排列
Select * from Employees
Order by Birthday
--查询员工姓名、性别和工龄，要求按收入从大到小排列
Select Name,workyear,sex from Employees join Salary on Employees.EmployeeID=Salary.EmployeeID
order by InCome desc

--实验四（2）、视图的使用
--1、创建视图
Create View DS_VIEW
	As Select * from Departments
Select * from DS_VIEW
--（2）
Create VIEW Employee_view(EmployeeID,Name,RealIncome)
	As
	Select EMployees.EmployeeID,Name,Income-Outcome
		from Employees,Salary
		Where Employees.EmployeeID=Salary.EmployeeID
--创建视图时Select语句有哪些限制

--查询视图时，如其关联的基本表中添加了新字段，则必须重新创建视图才能查询到新字段
--如果与视图关联的表或视图被删除，则改视图不能再被使用

--创建视图时有哪些注意点
--创建视图包含员工编号、姓名、所在部门和实际收入
Create VIEW view1(EmployeeID,Name,Department,RealIncome)
	As
	Select E.EmployeeID,E.Name,D.departmentName,S.Income-S.OutCome
	from Employees E,Departments D,Salary S
	Where E.DepartmentID=D.DepartmentID
	and E.EmployeeID=S.EmployeeID
Select * from view1
--2、查询视图
--（1）
Select departmentName from DS_VIEW Where DepartmentID=3
--（2）
Select RealIncome from Employee_view Where Name='王林'
--若视图关联了某表中所有字段，而此时该表添加了新的字段，视图中能否查找到该字段
--创建一个视图并查询视图中的字段
Select * from view1
--3、更新视图
--（1）
Insert into DS_VIEW values('6','广告部','广告业务')
--（2）
Update DS_VIEW Set DepartmentName='生产车间' Where DepartmentID='5'
--（3）
Update Employee_view Set Name='王浩' Where EmployeeID='000001' 
--（4）
Delete from DS_VIEW Where DepartmentID='1'
--4、删除视图
Drop VIEW DS_VIEW

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。