2017.11.20
1.常用函数
函数名:
abs返回给定数字表达式的绝对值
ceiling返回大于或等于指定数值表达式的最小整数
floor返回小于或等于指定数值表达式的最大整数
power幂函数,返回指定表达式指定幂的值
rand返回0-1之间的随机float值
操作演示:
select abs(10),abs(-10)
结果:10 10
select power(2,3)
结果:8
select floot(45.266),ceiling(45.266)
结果:45 46
select floot(-8.44),seiling(-8.44)
结果:-9 -8
ceiling返回大于或等于指定数据表达式的最小整数
floor返回小于或等于总置顶数值表达式的最大整数
select rand()
结果:0.374631290756254
随机产生从0-10之间不重复的整数:
1.select floor(rand()*N) 取0到N-1之间 如:cast(floor(rand()*100)as int)生成0-99之间任意一整数
2.select ceiling(rand()*N) 取1-N之间 如:cast(ceiling(rand()*100)as int)生成0-100之间任意一整数
2.字符串函数:
len 返回指定字符串表达式的字符个数(不是字节)
substring 子字符串,返回表达式中指定的部分数据
upper 大写函数
lower 小写函数
char acsii代码转换函数,返回指定acs代码的字符
acsii ascii函数,返回字符串表达式中最左侧的字符的ascii代码值
str 将数字类型转换成字符数据
left 返回字符串左边开始指定个数的字符
right 返回字符串右边开始指定个数的字符
select len('abcdef') as '个数'
结果:6 elc
substring(字符串表达式,开始位置,长度)
select substring ('abcdef',2,2) ---返回b,c
select lower('DKF'),upper('abc')
结果: dkf ABC
select char(65),ascii('A')
结果: A 65
select left('abcdef',4).right('abcdef',4)
结果:abce defg
select str(89.457,6,2)
结果:89.46
(将数字数据转换成字符数据)
str(float型小数[,总长度[,小数点后保留位数的位置]])
select str(123.451) 返回: 123(123前面有空格)
select str(123.451,3) 返回:123
select str(123.451,7,3) 返回:123.451
select str(123.451,7,1) 返回:123.5
select str(123.451,5,3) 返回:123.5
select str(123.451,2) 返回:**
说明:返回由数字转换成的字符串.返回字符数不到总长度的前面补空格,超过总长度的截断小数位.如果需要截断整数位则返回**
注意在截断时遵循四舍五入总长度.它包括小数点,符号,数字及空格.默认值位10.小数点后最多保留16位.默认不保留小数点后面的数字
3.日期时间函数:
getdate 返回当前的系统时间和日期
month 返回代表指定日期月份的整数
year 返回代表指定日期年份的整数
day 返回代表指定日期的天的整数
dateadd 在指定日期加上一段时间的基础上,返回新的datetime值
datepart返回指定日期中指定部分的整数,yy代表年,mm代表月,dd代表日,dw代表星期等
datename返回指定日期中指定部分的字符串
日期部分缩写的含义:
year 年 yy,yyyy
quarter 季 qq,q
month 月 mm,m
datoryear 一年中的第几天 dy,y
day 天 dd,d
weak 星期 wk,ww
weekday 一周中第几天的数值 dw,w
hour 小数 hh
second 秒 ss,s
millisecond 毫秒 ms
select getdate()
select month('03/12/2009')
select month('2009-09-04')
select dateadd(day,20,getdate())
结果:2017-10-10 20:22:24.140
dateadd(日期部分,数字,日期)
返回给指定日期加上一个时间间隔的新的日期值
select datepart(yy,getdate()),datename(dw,getdate())
结果:2017 星期三
datename(日期部分,日期)
select datename(mm,'2017-12-11')
4.数据类型转化函数:
cast(表达式 as 数据类型)
convert(数据类型[长度],表达式[,格式])
例:select cast(123 as varchar)
返回123
select '年龄:'+cast(23 as varchar)
返回 年龄:23
select convert(varchar,gatdate())
返回 11 12 012 10:21pm
select convert(varchar,getdate(),101)
返回 11/12/2012
日期类型数据转换为字符数据类型的日期格式的部分样式表:
不带世纪位数(yy) 带世纪位数(yyyy) 标准 输入/输出
- 0/100 默认设置 mon dd yyyy hh:miAM/PM
1 101 美国 mm dd yy
2 102 ANSI yy.mm.dd
3 103 英国/法国 dd/mm/yy
4 104 德国 dd.mm.yy
5 105 意大利 dd-mm-yy
120 ODBC规范 yyyy-mm-dd hh:mi:ss(24h)
...
SQL server 2008日期格式只取年月日:
select convert(varchar(10),getdate(),120)
1.将你的身份证的出生日期提取出来19900913
select substring(199811132629,7,8)
2.将你的手机号码中间的第4位至第7位号码隐藏位****,例如138****9008
select stuff(tel,4,4,****)
补充:1.stuff:用于删除指定长度的字符串并在指定的起始点插入另一组字符
语法:stuff(字符串表达式,起始位置,结束位置,替换字符串)
select stuff(13689568956,4,8,'****####')电话号码
2.datediff:
select datediff(YEAR,'2000-12-02',getdate())
3.从你的身份证号获取年龄:
实验练习:
use students
go
--1、定义两个变量@v1定义为长度为8的字符型和整型@v2，@v1赋值“welcome”， @v2赋值200，用select输出@v1和@v2。
declare @v1 char(8),@v2 int set @v1='welcome' set @v2=100 select @v1,@v2
--2、定义两个变量@var1定义为长度为10的字符型和整型@var2，@var1赋值“beautiful”， @var2赋值100，用print输出@var1和@var2。
declare @var1 char(10),@var2 int set @var1='beautiful' set @var2=100 print '@var1为:'+@var1 print '@var2为:'+convert(char(10),@var2)
--3、查询0-100间的随机函数（返回大于或等于指定数的最小整数 ）。
select cast(ceiling(rand()*100)as int)
--4、返回当前系统日期和时间。
select getdate()
--5、返回当前系统日期（格式只取年月日，格式为：yy.mm.dd）。
select convert(varchar(10),getdate(),102)
--6、返回当前系统日期（格式只取年月日，格式为：mm/dd/yyyy）。
select convert(varchar(10),getdate(),101)
--7、在student表中，加入新的字段“sid”，给每位学生加上身份证号码。查询学生的学号、姓名，及出生年月。出生年月由身份证号的出生日期提取出来（例如：
--身份证号码362330199309013236，输出显示19930901）。
select sno,sname, substring(sid,7,8) as 出生年月 from student
--8、查询学生的学号、姓名，及2060年时的年龄（要求从身份证获取信息）。
select sno,sname,abs(DATEDIFF(YEAR,'2060-01-01',left(right(sid,12),4)))as '2060年时的年龄' from student
--9、在student表中，加入新的字段“tel”， 给每位学生加上手机号码。查询学生的学号、姓名，及手机号码，并将手机号码中间的第4位至第7位号码隐藏为****（例如：138****9008。）
select sno,sname,STUFF(tel,4,4,'****') as tel from student
--10、根据学生的成绩显示学生成绩的等级，将90~100分的显示为“优秀”，80~90的显示为“好”，70~80的显示为“中等”，60~70的显示为“及格”，其他为“不及格”。
select sno,cno,grade,
'rank'=case grade/10
when 10 then '优秀'
when 9 then '优秀'
when 8 then '好'
when 7 then '中等'
when 6 then '及格'
else '不及格'
end
from sc
--11、计算1到100之间的偶数之和。
declare @i tinyint,@sum int
set @sum=0
set @i=0
while @i<100
begin
set @i=@i+1
if(@i/2)=0
continue
else
set @sum=@sum+@i
end
select '总和'=@sum
--12、计算1+2+…+100的和。
declare
@a int,@sum int,@csum char(10)
set @a=1
set @sum=0
while @a<= 100
begin
set @sum = @sum+@a
set @a=@a+1
end
set @csum=convert(char(10),@sum)
print '1+2+…+100=' + @csum
