SQLServer第五章：类型转换与函数
1.cast
cast(表达式 as 数据类型)
select 100.0 +cast('1000' as int) -- 1100.0 默认把字符串转换成浮整形
2.convert 万能转换
convert(数据类型，表达式)
select 100.0 + convert(int,'1000')-- 1100.0 默认把字符串转换成整形
3.select * from student order by convert (int,bid) desc --bid列按照降序来排列，bid是varchar类型
4.print convert(varchar(10),getdate(),120) --日期转换varchar(10)表示显示的字符长度，120表示style,可以在SSMS 帮助中查看详情
5.cast不可以转换日期类型，convert可以通用
--space(N)表示N个空格，是SQL的函数--convert(varchar(N),值) 表示万能转换为字符串 select SPACE(5) +CONVERT (varchar(2),23)+SPACE(5) +'你好' --把23转字符串
-------------------------------------------------------------定义变量，循环添加数据
create table a( number varchar(10) ) --数据库里面的批量循环更新数据 declare @i int --sql里面的定义一个变量用 declare set @i = 1 --给变量设置一个值用 set while @i<=100 --while 条件循环 begin insert into a values('高：'+convert(varchar,@i)) --convert万能传换，这里int转varchar，@i是参数 set @i=@i +1 end select number from a
*************这里来了解一下变量与输出***********
declare @y int,@y2 varchar(10) --定义两个变量 --给变量赋值的两种形式(set和select) 相对来说set才是赋值，select是存放在虚拟表 set @y=1 --同时对多个变量同时赋值时。不允许出现这样的语法： set @a='ABC',@b='EFG' ，会报错 set @y2='' ---而select可以同时对多变量赋值，set只能一个一个赋值。，set='select的一个初始值' select @y=2020,@y2=number from a --存放值在虚拟表，返回值是多个值不会报错，会将查询到的最后一个值返回给变量 --输出值的两种形式(select和print) select @y,@y2 --只能查询。以虚拟表显示出来。 print @y --print是输出（返回值） ，值以最长截取8000以内的字符串类型，返回给客户端。输出的值必须能隐式转换为字符串类型。 --判断闰年 if(@y%4=0 and @y%100!=0 or @y%400=0) --这个是判断每年的2月用的,成立就是28天,@i = 2018这样子判断 select convert(varchar,@y)+'是闰年' else select convert(varchar,@y)+'不是闰年'
示例一：@@error(返回上一个语句的错误号) 0正确 非0错误 如：if(@@ERROR = 0)
示例二：@@rowcount(返回上一个sql语句受影响的行数) 如：if(@@rowcount = 1)
示例三：@@identity(返回上一个标识值) 如：print @@identity
示例四：@@servername(服务器的名称) @@servicename(sqlserver服务名称) 如：print @@servername 和 print @@servicename
**********************************************************************额外一些SQL语句后台代码会用到*****************************************************************************
--新添加一列 alter table a add Test4 int --创建一个为int 类型的test4的列,注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。 insert into a values('可不填',1) select * from a --删除某一列 alter table a drop column Test4; --删除后数据会丢失，尽量不用执行删除操作。 select * from a
函数
优点：
(1)模块化编程
(2)执行效率高
(3)减少网络流量
(4)提高安全性
函数分类：
系统函数
自定义函数：
(1)标量函数：函数的返回值只有一个
(2)表值函数：函数的返回为一个表
函数注意事项：一个函数最多只能返回一个值，如果返回很多值，可以用表值函数
系统函数：
--字符串函数：返回最左侧字符串的ascii码值 select ASCII('abcd') --返回数字对应字符： select CHAR(97) --去掉左边空格，右边空格 select LTRIM(' aaaaaaaaaaa ') select RTRIM(' bbbbbbbbbbb ') select RTRIM( LTRIM(' ccccccccccc ')) --从左边开始截取指定数量的字符串： select LEFT('abcd',2) --从右边的开始截取指定数量的字符串： select right('abcd',2) --从1的位置开始，截取3位 select SUBSTRING('500231199606061258',7,8) --求字符串长度，不计最后空格 select LEN(' ab c d ') --d后面都空格不计 -------------------------日期函数： select GETDATE() /*获取时间:getDate()*/ select datename(YEAR,GETDATE()) --年 select datename(MONTH,GETDATE()) --月 select datename(DAY,GETDATE()) --日 select datename(HOUR,GETDATE()) --时 select datename(MINUTE,GETDATE()) --分 select datename(SECOND,GETDATE()) --秒 --获取指定的日期部份： select YEAR(GETDATE()) select month(GETDATE()) select day(GETDATE()) --添加时间到指定的日期部份： select DATEADD(YEAR,3,GETDATE()) --计算时间差： select DATEDIFF(YEAR,'2019-6-24',GETDATE()) --相差几年 select DATEDIFF(MONTH,'2019-6-24',GETDATE())--相差几个月
自定义函数：
create function fun_函数名(形参)
returns 返回值
as
方法体......
go
返回单个值：标量函数
返回一个表：表值函数
--示例一：定义函数，查找用户传入数字中的最大值(有参有返)
--有参：用户传入两个数字
--有返：最大值
if exists(select * from sys.objects where name='fun_getmax') begin drop function fun_getmax end go create function fun_getmax(@num1 int,@num2 int) returns int --指定返回值的类型 as begin declare @max int if(@num1 > @num2) begin set @max = @num1 end else begin set @max = @num2 end return @max --函数返回值 end go --调用：两个数做比较返回最大值 select dbo.fun_getmax(10,20) select dbo.fun_getmax(50,20)
下面需要创建一个简单的表做演示
create table cardInfo ( cardId int primary key identity, cardNumber varchar(20) unique not null, --学号：约束unique【唯一的】和主键一样有唯一标识，表示内容不能有重复数据 cardName varchar(20) , cj1 int , cj2 int , ) insert into cardInfo values('S25303','张三',20,20),('S25304','李四',60,50),('S25305','王五',50,80)
--示例二：定义函数，根据用户输入的学号，查询它的平均成绩
if exists(select * from sys.objects where name='fun_getavgbyno') begin drop function fun_getavgbyno end go create function fun_getavgbyno(@stuno varchar(10)) returns float as begin declare @avg float select @avg=(cj1+cj2)/2 from cardInfo where cardNumber=@stuno --安输入的学号查询 return @avg end go --调用： select dbo.fun_getavgbyno('S25305')
在创建一个表做多表联接查询演示
create table stuinfo ( stuId int primary key identity, stuNumber varchar(20) unique not null ) insert into stuinfo values('S25303'),('S25304'),('S25305')
--示例三：定义函数，根据用户输入的学号，查询它的：姓名，性别，笔试成绩，机试成绩(表值函数)
if exists(select * from sys.objects where name='fun_getstuinfo') begin drop function fun_getstuinfo end go create function fun_getstuinfo(@no varchar(10)) returns table as return select cardNumber, cardName, cj1, cj2 from cardInfo left join stuinfo on stuinfo.stuNumber=cardInfo.cardNumber where cardInfo.cardNumber=@no go --调用; select * from dbo.fun_getstuinfo('S25303') select cardNumber as 学号,cardName as 姓名 from dbo.fun_getstuinfo('S25303')
--示例四：定义函数，查询所有学生的：姓名，性别，笔试成绩，机试成绩(表值函数)
if exists(select * from sys.objects where name='fun_getstuinfo') begin drop function fun_getstuinfo end go create function fun_getstuinfo() returns table as return select cardNumber, cardName, cj1, cj2 from cardInfo left join stuinfo on stuinfo.stuNumber=cardInfo.cardNumber go --调用： select * from dbo.fun_getstuinfo() select * from dbo.fun_getstuinfo() where cardName='张三'
