use YGGL
--创建一个名为female的用户变量,并在select语句中使用该
--局部变量查找表中所有女员工的编号和姓名
declare @female bit
set @female=0
select EmpID,EmpName from Employees
where EmpSex=@female
--定义一个变量,用于获取号码为102201的员工的电话号码
declare @phone char(12)
set @phone=(select PhoneNum from Employees where EmpID=102201)
select @phone
--定义一个变量,用于描述Salary表中000001号员工的实际收入
declare @income float
set @income=(select InCome from Salary where EmpID=000001)
select @income
--流程控制语句 begin...end,if...else,case,goto,while,continue,break,return,waitfor
--判断Employees表中是否存在编号为111006的员工,如果存在,则显示该员工信息;若不存在,则
--显示“查无此人”
if (select EmpName from Employees where EmpID=111006)is not null
select * from Employees where EmpID=111006
else (select '查无此人')
--判断姓名为“王林”的员工实际收入是否高于3000,如果是,显示其收入,否则显示‘收入不高于3000’
if(select InCome from Salary where EmpID in (Select EmpID from Employees where EmpName='王林'))>3000
select InCome from Salary where EmpID in (Select EmpID from Employees where EmpName='王林')
else select '收入不高于3000'
--假设变量X的初始值为0,每次加1,直至x变为5
declare @x int
set @x=2
while(@x<10)
begin
set @x=@x+1
end
select @x
--使用循环输出一个用“*”组成的三角形
declare @count int
set @count=1
declare @i int
set @i=1
while(@i<6)
begin
while @count<=@i
begin
print Replicate('*',@count)
set @count=@count+1
end
set @i=@i+1
end
use YGGL
--使用if语句对Employee表按部分进行分类
select * from Employees,Departments
--该if有问题
if(select DepID from Employees)=1
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'财务部' from Employees
if(select DepID from Employees)=2
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'人力资源部' from Employees
if(select DepID from Employees)=3
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'经理办公室' from Employees
if(select DepID from Employees)=4
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'研发部' from Employees
if(select DepID from Employees)=5
select EmpID,EmpName,EmpEdu,EmpBir,DepID as'市场部' from Employees
--使用case语句对Employee表按部分进行分类
select EmpID,EmpName,Addres,DepID =
case DepID
when 1 then '财务部'
when 2 then '人力资源部'
when 3 then '经理办公室'
when 4 then '研发部'
when 5 then '市场部'
end
from Employees
转载于:https://www.cnblogs.com/wangxueliang/p/9346509.html