【基础5】SQL系统内置函数 T-SQL编程以及作业

蛋翼

于 2023-05-19 07:15:00 发布

阅读量644

点赞数

分类专栏： SQL Server 文章标签： sql 数据库 java

本文链接：https://blog.youkuaiyun.com/m0_69066786/article/details/130723836

版权

SQL Server 专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了SQL中的系统内置函数，包括字符函数、日期函数、数学函数等，如CHARINDEX、LEFT、RIGHT、SUBSTRING、REPLACE、STUFF等，并展示了如何在实际操作中使用这些函数进行字符串处理和日期操作。此外，还讲解了T-SQL编程中的变量声明、逻辑控制语句（如IF、CASE）及其在条件判断和循环中的应用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

系统内置函数

T-SQL编程

练习

系统内置函数

字符函数，取字符串里的字符

--字符函数：
--返回某个字符在一个字符串里的位置
select charindex('f','abfc')     
select * from Employees

--取子串函数， 取字符串里的几个
select left('abcedg',4)			--从左边开始取
select right('abcedg',4)        --从右侧开始倒回来取

select 姓名, left(地址,3) 城市 from Employees		--查询员工城市

--取子串，select substring(字符串，从哪个字符开始取，取几个字符)
select substring('qwerr',2,3)--从第二个开始取，取三个字符

select 姓名, substring(地址,1,3) 城市 from Employees

--替换函数 select replace(字符串,需要替换的字符,用哪个字符替换)  成批替换
select replace ('adgNbdNag','N','n')

 
--全部变为天津
update Employees set  地址='天津' where 地址 like '北京%'

--修改北京为天津
update Employees set 地址=replace(地址,substring(地址,1,2),'天津')

替换函数

--替换函数   select stuff (字符串,从哪个字符串开始取, 取几个,用来替换的字符)  字符(串)要加英文单引号   指定位置替换
	--※如果 取的字符数 为负，则返回空字符串。
	--※如果 取的字符数 的长度大于字符串，则最多可以删除到 字符串 中的最后一个字符。
	--※如果 取的字符数 为零，则不删除字符直接在指定位置插入内容。 
select stuff ('abcdesef',4,3,'d')
--如果替换位置 为 NULL，则在不插入任何内容的情况下删除字符。
select stuff ('abcdesef',4,3,null)

update Employees set 地址=stuff(地址,1,2,'北京')

删除空格/添加空格函数

--删除空格函数
select ltrim('   dfalkg')				--删除左侧空格
select rtrim('dfalkg   ')				--删除右侧空格
select len('   dfalkg')				--统计字符个数
select len(ltrim('   dfalkg'))
select len(rtrim('dfalkg   '))

select * from Employees
--空格在前面
update Employees set 姓名=' 赵飞燕' where 编号='1001'--加空格
update Employees set 姓名=ltrim(姓名) where 编号='1001' --删空格
--空格在中间
update Employees set 姓名='赵 飞燕' where 编号='1001'--加空格
update Employees set 姓名=replace(姓名,' ','') where 编号='1001'--删除空格
update Employees set 姓名=stuff(姓名,charindex(' ',姓名),1,'') where 编号='1001'--删空格

--添加空格    select 字符串+space(空格个数)+字符串
select 'abcdefg'+space(3)+'hijklmn'

--查找出姓李/王的人
select * from Employees where 姓名 like '[李,王]%'
select * from Employees where left(姓名,1)='李' or left(姓名,1)='王'
select * from Employees where substring(姓名,1,1)='李' or substring(姓名,1,1)='王'

日期函数

--日期函数
select getdate()  --取系统时间
--取系统时间的年、月、日、小时、分钟、秒、毫秒
select year(getdate()), month(getdate()) ,day(getdate()) 
select datepart(hh,getdate()),datepart(mi,getdate()),datepart(ss,getdate()),datepart(ms,getdate())
--返回数字类型
select datepart(yy,getdate()),datepart(mm,getdate()),datepart(dd,getdate()),
datepart(hh,getdate()),datepart(mi,getdate()),datepart(ss,getdate()),datepart(ms,getdate())
--返回字符类型
select datename(yy,getdate()),datename(mm,getdate()),datename(dd,getdate()),
datename(hh,getdate()),datename(mi,getdate()),datename(ss,getdate()),datename(ms,getdate())

--dw：今天是星期几，dayweek  week：今天是今年第几个周
select datepart(dw,getdate()),datepart(week,getdate())
select datename(dw,getdate()),datename(week,getdate())

--dateadd(年，在后面时间的基础上加/减几(年),时间/系统时间)
select dateadd(yy,10,getdate())
select dateadd(yy,-10,getdate())

--datediff：两日期之间的差（yy,后面的减这个，日期）
select datediff(yy,'2000-5-8',getdate())
select * from Goods
select 商品名称, datediff(yy,进货时间,getdate()) 年限 from Goods
select 商品名称, year(getdate())-year(进货时间) 年限 from Goods

数学函数

--数学函数
--四舍五入函数round(数字，保留几位)
select round(78.89,1)
select round(78.894,2)

--转换函数 convert(转换成的指定类型，需要转换的) 将一个函数运算结果从一个类型转换为另一个类型
select convert(char(3),100)
select len(convert(char(3),100))--字符个数三个
select convert(decimal(3,1),round(78.89,1)) 数值
--print '数值是：' +convert(decimal(3,1),round(78.89,1))   出错：从数据类型 varchar 转换为 numeric 时出错。
--改正：
print '数值是：' + convert(varchar(10),convert(decimal(3,1),round(78.89,1)))

--转换函数 cast  cast(需要转换的 as 指定类型)
select cast(100 as char(3))
select len(cast(100 as char(3)))
select cast(round(78.89,1) as decimal(3,1)) 数值
print '数值是：' + cast(cast(round(78.89,1) as decimal(3,1)) as varchar(10))

T-SQL编程

注释以及变量

-- 两个-是单行注释

/*
这是多行注释
这是多行注释
*/  

--变量
/*
		局部变量：1、必须以标记@作为前缀           @name
						2、局部变量的使用也是先声明，再赋值
						3、使用完后释放掉，不能永久性的保存下来。
		全局变量：1、必须以标记@@作为前缀           @@servername   @@error   @@identity   @@version
						2、全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值。
*/

--以下语句执行时，要从声明一直选中到输出语句，因为这个是局部变量，一行一行执行出现以下错误
--必须声明标量变量 "@code"。必须声明标量变量 "@name"。
-- 声明变量   
declare @code varchar(4) ,@name varchar(8),@sum int 
--变量赋值
set @code='1301'
select @name=姓名 from Employees where 编号=@code 
select @sum=sum(数量) from Sell where 售货员工编号=@code 

--输出语句
select @name 姓名, @sum 销售数量和
print  '姓名：'+@name+space(4)+'数量和：'+convert(char(3),@sum)
--在以上两行输出语句执行完之后，中间显示了：(一行受影响)
set nocount on --no count 不提示受影响了 ，如果不希望显示(n行受影响)，则每一个新建的查询文件都需要在开头执行此语句
set nocount off		--关闭之后如果需要再次显示
--全局变量    
select @@servername 服务器名称
select @@servername as 服务器名称

update Employees set 性别='女' where 编号='1001'
insert into Employees values(1001,'李四',1,'采购部','194847364','北京市建国路22号')

select @@error    --@@error 显示刚刚执行的sql语句的错误数

select @@identity		--查看刚刚插入的数据的标识列是第几列
insert into Sell values(9,5,getdate(),'1301')

select @@version--查看SQL版本编号

逻辑控制语句中的IF语句

--IF
--if 条件表达式
--SQL语句      条件成立执行这条语句

if exists (select * from sysdatabases where name='abc' )  sysobjects  全部数据库对象    sysindexes  全部索引    sysdatabases   全部数据库
drop database abc
create database abc
go

/*     if   条件表达式	如果语句块有多个SQL语句，则需要在语句前+begin ，在后面+end来表示这是一个语句块
begin
		SQL语句1
		SQL语句2
end
		else
		SQL语句2     */

declare @m int ,@n int
set @m=31
set @n=30
if @m>@n
print '较大的数是：'+convert(varchar(10),@m)
else
print '较大的数是：'+cast(@n as varchar(10))

--判断1001是否做过销售，没有销售输出没有销售 ，有销售输出数量和

declare @code varchar(4) , @name varchar(8),@sum int		--声明变量
set @code='1303'					--变量赋值
select @name=姓名 from Employees where 编号=@code

if exists (select 售货员工编号 from Sell where 售货员工编号=@code)
	begin
	select @sum=sum(数量)  from Sell where 售货员工编号=@code 				
		print  '姓名：'+@name+space(4)+'数量和：'+convert(char(3),@sum)
	end
else
	print @code+'没有销售数量'
go

/*  多重IF
				if 条件
					if 条件
					else
				else
					if 条件
					else
*/

--判断三个数哪个最大

declare @a int ,@b int ,@c int
set @a=40
set @b=60
set @c=50

if @a>@b
	if @a>@c
		print '最大的数是：'+cast(@a as varchar(3))
	else
		print '最大的数是：'+cast(@c as varchar(3))
else
	if @b>@c
		print '最大的数是：'+cast(@b as varchar(3))
	else
		print '最大的数是：'+cast(@c as varchar(3))

/*	
		if 条件
		语句
		else if 条件
		语句
		else if  条件
		·······
		else
	
*/

--判断成绩
declare @score int
set @score=59
if @score between 0 and 100     --@score >= 0 and @score <=100
	if @score >=90 
		print'优'
	else if @score >=80
		print'良'
	else if @score >=70
		print'中'
	else if @score >=60 
		print'及格'
	else 
		print'挂科'
else
	print '您输入的成绩有误，请重新输入'



--判断1001是否做过销售，没有销售输出没有销售 ，有销售输出数量和 并且判读是什么员工

declare @code varchar(4) , @name varchar(8),@sum int		--声明变量
set @code='1301'					--变量赋值
select @name=姓名 from Employees where 编号=@code

if exists (select * from Sell where 售货员工编号=@code)-- @code in (select 售货员工编号 from sell)
	begin
	select @sum=sum(数量)  from Sell where 售货员工编号=@code 
	print  '姓名：'+@name+space(4)+'数量和：'+convert(char(3),@sum)
	if @sum>=10						
		print '优秀员工'
	else if @sum>=5 
		print '合格员工'
	else
		print '不合格员工'
	end
else
	print @code+'没有销售数量'
go

case语句


-- case end 多分支语句     case必须嵌套在表达式中使用
/*			 case 
					when 条件表达式   then 结果
					when 条件表达式   then 结果
					when 条件表达式   then 结果
					when 条件表达式   then 结果
					when 条件表达式   then 结果
					else   结果
			  end						*/


select * into goods1 from Goods
select * from Goods
select * from goods1

--零售价大于5000的+500，大于三千的+300 ，其余的+100
update goods1 set 零售价=case
											when 零售价>=5000 then 零售价+500
											when 零售价>=3000 then 零售价+300
											else 零售价+100
											end

--判断1001是否做过销售，没有销售输出没有销售 ，有销售输出数量和 并且判读是什么员工

declare @code varchar(4) , @name varchar(8),@sum int		--声明变量
set @code='1301'					--变量赋值
select @name=姓名 from Employees where 编号=@code

if exists (select 售货员工编号 from Sell where 售货员工编号=@code)
	begin
	select @sum=sum(数量)  from Sell where 售货员工编号=@code 
	print  '姓名：'+@name+space(4)+'数量和：'+convert(char(3),@sum)

	print case
					when @sum>=10	then  '优秀员工'
					when @sum>=5  then '合格员工'
					else  '不合格员工'
			end
	end

while 语句

--while 循环
/*
	while  条件表达式
		循环体（如果有多条语句，前+begin后+end）

		while  条件表达式
		循环体
		if 条件
		berak
*/


--计算1~100的和
declare @a int, @sum int
set @a=0
set @sum=0

while @a<100
begin
	set @a=@a+1
	set @sum=@sum+@a
end
print '1~100的和是：'+cast(@sum as char(10))

--计算1~100的和 使用break 使跳出循环
declare @a int, @sum int
set @a=0
set @sum=0

while 1=1
begin
	set @a=@a+1
	set @sum=@sum+@a
	if @a>=100
	break
end
print '1~100的和是：'+cast(@sum as char(10))

--输出等边三角形
declare @a int ,@b varchar(20)
set @a=0
set @b=' '

while @a<=6
	begin
		set @a=@a+1
		set @b=@b+'* '
		print @b
	end

--库存小于10的，每次进5件货，大于10的不进货

select * from Goods
select * from Sell


declare @goodscode int ,@kc int ,@goodsname varchar(8)
set @goodscode=9
select @goodsname=商品名称 from Goods where  商品编号=@goodscode
if @goodscode in (select 商品编号 from Sell)
	begin
		select @kc=G.数量-sum(S.数量) from Goods G, Sell S where G.商品编号=S.商品编号 and G.商品编号=@goodscode group by 商品名称, G.数量  
		print  @goodsname+' 进货前的库存量：'+cast(@kc as char(10))
	end
else 
	begin
		select @kc=数量 from Goods where 商品编号=@goodscode
		print  @goodsname+' 进货前的库存量：'+cast(@kc as char(10))
end
	while @kc <10
	begin
			update Goods set @kc=@kc+5 where 商品编号=@goodscode 
			if @kc>10
			break
	end
	print  @goodsname+' 进货后的库存量：'+cast(@kc as char(10))

练习

use Xk
go

select * from Student 
select * from StuCou
select * from Course

set nocount on

--1、查询学生选课信息，选过的显示  StuNo StuName CouNo CouName WillOrder  同时显示选课门数，否则显示“无选课记录”
--使用  00000001   00000025  验证

declare @code varchar(8) ,@sum int 
set @code='00000001'    --00000025

if @code in(select S.StuNo  from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo)
	begin
			print '学号为：'+@code+'的学生选课信息如下：'
			select S.StuNo ,StuName ,SC.CouNo ,CouName,WillOrder from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@code
			select @sum=count(CouNo)  from (select S.StuNo ,StuName ,SC.CouNo ,CouName,WillOrder from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@code) xuanke 
			print '选课门数：'+ cast(@sum as char(1))
	end
else
	print '学号为：'+@code+'的学生没有选课'


--2、查询指定学号的 学生的选课信息，选过课的显示该学生的选课信息（同上），同时显示选课门数。
--选课门数=5，显示“已达到选课计划要求”   4---还差1门，请继续选课   3---差2   2--差3   1-差4    没有的“无选课记录”
declare @code varchar(8) ,@sum int 
set @code='00000001'    --00000002   --00000007  --00000026  --00000022  --00000025

if @code in(select S.StuNo  from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo)
	begin
			print '学号为：'+@code+'的学生选课信息如下：'
			select S.StuNo ,StuName ,SC.CouNo ,CouName,WillOrder from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@code
			select @sum=count(CouNo)  from (select S.StuNo ,StuName ,SC.CouNo ,CouName,WillOrder from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@code) xuanke 
			print '选课门数：'+ cast(@sum as char(1))

			if @sum=5
				print '已达到选课计划要求'
			else if @sum=4
				print '还差1门，请继续选课'
			else if @sum=3
				print '还差2门，请继续选课'
			else if @sum=2
				print '还差3门，请继续选课'
			else
				print '还差1门，请继续选课'

	end
else
	print '学号为：'+@code+'的学生没有选课'



--3、用case实现
declare @code varchar(8) ,@sum int ,@result varchar(20)
set @code='00000001'    --00000002   --00000007  --00000026  --00000022  --00000025

if @code in(select S.StuNo  from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo)
	begin
			print '学号为：'+@code+'的学生选课信息如下：'
			select S.StuNo ,StuName ,SC.CouNo ,CouName,WillOrder from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@code
			select @sum=count(CouNo)  from (select S.StuNo ,StuName ,SC.CouNo ,CouName,WillOrder from Student S,StuCou SC, Course C where S.StuNo=SC.StuNo and SC.CouNo=C.CouNo and S.StuNo=@code) xuanke 
			select @sum 选课门数 ,
												case  
														when @sum=5 then '已达到选课计划要求'
														when @sum=4 then '还差1门，请继续选课'
														when @sum=3 then '还差2门，请继续选课'
														when @sum=2 then '还差3门，请继续选课'
												else  '还差4门，请继续选课'   
												end as 选课结果
	end
else
	print '学号为：'+@code+'的学生没有选课'


--4、输出等边三角形
declare @a int ,@b varchar(100) ,@c int

set @a=0
set @b=''
set @c=6
while @a<@c
	begin
		set @a=@a+1
		set @b=@b+'○  '			--圈后两个字符
		print space((@c-@a)*2)+@b
	end


--尝试居中					6行，缩进(总行数-当前行数)*2个空格
--输出的图形在显示窗口不居中，但放在下面是居中的
          ★					--10
        ★  ★					--8
      ★  ★  ★				--6
    ★  ★  ★  ★			--4	
  ★  ★  ★  ★  ★			--2
★  ★  ★  ★  ★  ★	    --0
            ○  
          ○  ○  
        ○  ○  ○  
      ○  ○  ○  ○  
    ○  ○  ○  ○  ○  
  ○  ○  ○  ○  ○  ○  
--4、输出等边三角形
declare @a int ,@b varchar(100) ,@c int

set @a=0
set @b=''
set @c=6
while @a<@c
	begin
		set @a=@a+1
		set @b=@b+' ○  '			--圈后两个字符，圈前一个，这样在显示窗口里是等边的
		print space((@c-@a)*2)+@b
	end


           ○  
         ○   ○  
       ○   ○   ○  
     ○   ○   ○   ○  
   ○   ○   ○   ○   ○  
 ○   ○   ○   ○   ○   ○