SQL Server 实验五 T-SQL编程

SQL Server 实验五 T-SQL编程

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

Use YGGL

--2、变量使用
--（1）
Declare @female bit
Set @female=0
Select EmployeeID,Name from Employees Where Sex=@female
--（2）
Declare @employeeid char(12)
Set @employeeid='102201'
Select PhoneNumber from Employees Where EmployeeID=@employeeid

Declare @phone char(12)
Set @phone=(Select PhoneNumber from Employees Where EmployeeID='102201')
Select @phone
--描述并查询salary表中000001号员工的实际收入
Declare @realincome float
Set @realincome=(Select income-outcome from Salary Where EmployeeID='000001')
Select @realincome
--4、
--（1）判断Employees表中是否存在111006的员工，存在则小时信息，否则显示查无此人
If EXISTS(Select Name from Employees Where EmployeeID='111006')
	Select * from Employees Where EmployeeID='111006'
Else
	Select '查无此人'
--判断王林的实际收入是否大于3000，是则显示收入，否则显示收入不高于3000
Select name, Income-Outcome as realincome into a from Employees join Salary on Employees.EmployeeID=Salary.EmployeeID
If (Select realincome from a Where Name='王林')> 3000
	Select * from a
Else
	Select '收入不高于3000'
--（2）假设变量x的初始值为0，每次加1，直至x变为5
Declare @x int
Set @x=0
While @x<5
Begin
	Set @x=@x+1
	print 'X='+convert(char(1),@x)
end
--用循环输出一个用*组成的三角形
declare @i int
declare @n int
set @n=10
set @i=1
while @i<@n
begin
print(Space((@n-@i)/2)+Replicate('*',@i))
set @i=@i+2
end 

declare @rows int --定义行数变量！
declare @i int ,@str varchar(30),@strin char(3) --定义辅助变量！
set @rows=1
set @strin='*'
while(@rows<=5)
begin
	set @i=1;
	set @str='';
	while(@i<=@rows)
		begin
			set @str=@str+@strin
			set @i=@i+1;
		end
	print @str;
	set @rows=@rows+1;
end
--（3）使用Case对Employees按部门进行分类
Select EmployeeID,Name,Address,DepartmentID=
Case DepartmentID
	When 1 Then '财务部'
	When 2 Then '人力资源部'
	When 3 Then '经理办公室'
	When 4 Then '研发部'
	When 5 Then '市场部'
end
from Employees
--使用IF语句完成上述功能
Create function change_name(@id char(10))
returns char(20)
As
begin
Declare @name char(20)
if @id=1
	Set @name='财务部'
if @id=2
	Set @name='人力资源部'
if @id=3
	Set @name='经理办公室'
if @id=4
	Set @name='研发部'
if @id=5
	Set @name='市场部'
	return @name
	end
Select EmployeeID,Name,Address,dbo.change_name(DepartmentID) DepartmentName from Employees
--5、自定义函数
--（1）给定一个DepartmentID，查询该值在Departments中是否存在，存在返回0，否则返回-1
Create function check_id(@departmentid char(3))
returns integer
As
Begin
	Declare @num int
	if Exists(Select departmentID from departments Where @departmentid=DepartmentID)
		Select @num=0
	Else
		Select @num=-1
	return @num
end
--（2）当向Employees插入一行记录时，首先调用函数check_id检索该记录的DepartmentID值，否则将该记录插入Employees中
Declare @num int
Select @num=dbo.check_id('2')
IF @num=0
	Insert zemployees values('')
--定义一个函数计算一个数的阶乘
Create function jc(@A int)
returns bigint as
  begin
    declare @R bigint,@I int
    set @R=1
    set @I=1
    while @I<=@A
       begin
            set @R=@R*@I
            set @I=@I+1
       end
    return @R
  end
  
Declare @a int
Set @a=10
Select dbo.JC(@a)
--6、内置函数使用
--（1）
Select ABS(-123)
Select RAND() --随机值
Select SQUARE(11) --平方
Select SQRT(10) --平方根
--（2）
Select COUNT(employeeID)from Employees
Where DepartmentID=
(Select DepartmentID from Departments Where DepartmentName='财务部')
--求财务部收入最高的员工姓名
Select max(Name),MAX(InCome) from Employees,Salary,Departments
Where Employees.DepartmentID=Departments.DepartmentID
and Employees.EmployeeID=Salary.EmployeeID
and Departments.DepartmentName='财务部'


Select max(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='财务部')))


Select * from Salary
Select * from Departments
--查询员工收入的平均数
Select AVG(Income) from Salary
--聚合函数如何与Group by函数一起使用
--（3）使用ASCII函数返回字符表达式最左端字符的ASCII值
Select ASCII('abc')
--（4）获得当前的日期和时间
Select GETDATE()
--查询编号000001的员工的出生年份
Select YEAR(birthday) from Employees Where EmployeeID='000001'
--使用day()函数返回指定日期时间的天数
Select DAY('2018-7-1')
--列出其他时间日期函数
month()
--使用其他类型的系统内置函数
--类型转换函数cast
Select * from Salary Where CAST(InCome as char(20))like '2____'
--类型转换函数convert
Select * from Salary Where CONVERT(char(20),InCome)like '1____'