第十三单元 流程控制和函数

1. 选择结构

If(...) 
Begin
​
语句块
​
End
​
else if(...)
Begin
​
语句块
​
End
​
Else
​
Begin
​
语句块
​
End;
 

 

注意事项

语法中begin..end相当于C#中的{}

执行语句只有一条时，begin..end可以省略

()可以省略，但是如果条件内包含select语句 ，()不能省略

数据库中使用=表示相等判断

 

2. Case 结构

-- 用于条件是判断相等的时候才可以使用这种语法
case 字段
    when 条件1  then 结果
    when 条件2  then 结果
    else 结果
end

或者

case
    when 条件1  then 结果
    when 条件2  then 结果
    else 结果
end
例：

select Id,NickName,Mobile Age,
case Sex 
when 0 then '男'
when 1 then '女'
when 2 then '保密'
when 3 then '未知' 
end 性别
 from UserInfo

 

 

select *,
 case  
 when Age<=10 then '幼年'
 when Age between 11 and 25 then '少年'
 when Age between 26 and 40 then '青年'
 when Age between 41 and 59 then '中年'
 else '老年'
 end 人群
  from UserInfo

 

 

 

3. While 循环

while
begin
    -- 语句块
end
-- 求1-100的和,碰到3的倍数跳过,如果和大于100则跳出循环
 declare @i int=1;
 declare @sum int=0;
 
 while(@i<=100)
 begin
    
    if(@i%3=0) --3的倍数
    begin
        set @i=@i+1; -- 循环变量递增      
        continue;--结束本次循环,进入下一次循环
    end;
    set @sum=@sum+@i;
    if(@sum>=100)
    begin
        break; -- 如果和大于100则跳出循环
    end;
​
    set @i=@i+1; -- 循环变量递增
 end;
​
 select @sum,@i;

 

注意事项

while后面的()可以省略

但是如果循环条件内含有select语句，()不能省略

可以使用break和continue关键字在循环内部控制循环的执行

关键字	退出条件
return	从查询或者过程中无条件退出
break	退出while 或者 if else 中最里面的循环
continue	重新开始 while 循环。在 continue 关键字之后的任何语句都将被忽略

 

4. waitfor流程控制语句

等到特定的时间，执行语句.

-- waitfor delay 延迟时间

print 'hello';
 waitfor delay '0:0:3';-- 等3秒
 print 'world';

 

基本上用不上，一般查询都会要求高效查询。

 

 

5. 函数

1. 系统函数

CAST (expression AS data_type):强制转换

 --字符串转换为int
select CAST ('100.1' AS decimal(10,2))
-- 100.10

 

Left(字符串,字符数量):返回字符串从左开始数 指定数量的字符串

select left('hello world',7)
-- hello w

 

Right(字符串,字符数量):返回字符串从右开始数 指定数量的字符串

select right('hello world',7);
-- o world

 

LTrim(要去除空格的字符串):去除字符串左边的空格

--去除左右两边的空格
 select ltrim(rtrim('  hello world   a   '))
 -- hello world    a

 

RTrim(要去除空格的字符串):去除字符串右边的空格

str(数值,转换后的字符总长度,小数位数):将数值数据转换为字符数据

select str(3.1415926,4,2)  -- 3.14
select str(3.1415926)  --    3

 

SubString(字符串,截取位置,截取长度):返回从指定位置截取指定长度的字符串

select SUBSTRING('hello world',1,5);
-- hello
​
declare @mobile varchar(12)='15211112222';
select left(@mobile,3)+'****'+right(@mobile,4)
-- 152****2222

 

注意:截取位置最小值是1

Lower(要转换的字符):把字母全部转换为小写字母

select LOWER('hElLo WorLD');
-- hello world

 

Concat(字符串1,字符串2.....):将所有字符串拼接成一个新的字符串

select concat('abc','cbd');
-- abccbd

 

Upper(要转换的字符):把小写字母全部转换为大写字母

select Upper('hElLo WorLD');
-- HELLO WORLD

 

Replace(字符串,要替换的字符/字符串,替换后的字符/字符串):替换字符/字符串

select replace('abc123tyuu','123','456')
-- abc456tyuu

 

Len(要计算的字符):计算字符个数

select len('abc123tyuu')

 

abs(数值):求绝对值，即把负数变为正数

Sqrt(数值):求平方根。数值必须是非负数

round(数值,有效数位数):四舍五入函数

select round(5.78106,2)
-- 5.78

 

rand():随机返回一个0-1之间的浮点型小数

GetDate():获取当前数据库系统的日期和时间

Year(指定日期):返回指定日期中的年份值

Month(指定日期):返回指定日期中的月份值

Day(指定日期):返回指定日期是一个月中的第几天

DateDiff(日期类型,开始日期,结束日期):返回两个日期之差

DateAdd(日期类型,增量,增量表达式):增加指定的日期

日期类型：year/month/day/hour/minute等

增量：正数表示增加时间，负数表示减少时间

增量表达式：要增加日期的变量或字段

select DateAdd(yyyy,4,'2000-02-02')
-- 2004-02-02

 

 

2. 自定义函数

sql server 自定义函数分为三种类型：标量函数（Scalar Function）、内嵌表值函数（Inline Function）、多声明表值函数（Multi-Statement Function）

标量函数：标量函数是对单一值操作，返回单一值。

内嵌表值函数：内嵌表值函数的功能相当于一个参数化的视图。它返回的是一个表，内联表值型函数没有由BEGIN-END 语句括起来的函数体。

多声明表值函数：它的返回值是一个表，但它和标量型函数一样有一个用BEGIN-END 语句括起来的函数体，返回值的表中的数据是由函数体中的语句插入的。

它可以进行多次查询，对数据进行多次筛选与合并，弥补了内联表值型函数的不足。

create function f_sum(@endNum int) returns int
as
begin
    declare @i int=1;
    declare @sum int=0;
    while(@i<=@endNum)
    begin
        set @sum=@sum+@i;
        set @i=@i+1;
    end;

    return @sum;
end;
go
--调用自定义函数
select dbo.f_sum(100)

 

结合查询语句：

use step2;
-- 查询每个班级的总分
create function f_sumScore(@classId int) returns int
begin
    declare @sum int;
    select @sum =sum(skillScore) from StudentInfo a 
    join StudentScore b on a.stuId=b.stuId where ClassId=@classId
    return @sum;
end
    
-- 每个班的总分
select *,dbo.f_sumScore(ClassId) as 总分 from StudentInfo 

 

6. 作业

用户信息表(UserInfo)

字段名	描述	类型	主键	非空	唯一	默认值	Check
Id	编号	BigInt	√
NickName	姓名	varchar		√			长度2-10
Mobile	手机号	Varchar		√	√		长度11
Sex	性别(0:男,1:女,2:保密,3:未知)	tinyint		√		0	0-3
Age	年龄	tinyint		√		10
CreateTime	添加时间	datetime		√		Getdate()

订单表(OrderInfo)

字段名	描述	类型	非空	默认值	Check	外键
Id	编号	bigint
UserId	下单用户编号	bigint				用户表(Id),联级更,删
TotalPrice	订单总价	Decimal(10,2)	√	0	>=1
TotalCount	商品总数量	Int	√	1	>=1
CreateTime	下单时间	datetime	√	Getdate()
OrderState	状态(0:未支付,1:未发货,2:待收货,3:待评价,4:已完成)	tinyint		0	0-4

 

1. 创建数据库,名为:第十五单元练习数据库

2. 正确建立表结构,注意表约束

3. 每张表插入10条测试数据

4. 查询学生表,将性别显示为男,女,未知,保密

5. 查询订单表,将订单状态显示为未支付,未发货,待收货,待评价,已完成

6. 显示用户表的姓名(去除空格),年龄,性别,人群类别(年龄:0-10小孩,10-20 少年,20-35青年,35-60 中年,60以上老年)

7. 查询用户信息,将手机号中间4位用*号表示,如1384843

8. 查看最近3个月的订单数据

9. 查看最近2017年以后的订单数据,统计每个用户的订单数量

10. 查询手机尾号相同的用户信息

11. 查询近一年的用户数据,并将复姓为”令狐”的姓氏替换为”令”

12. 自定义一个函数,传入三角形三条边的值,并输出三角形的形状(普通三角形,直角三角形,等腰直角三角形,等边三角形,等腰三角形),如,传入:3,4,5 输出 直角三角形（输出文字即可）

13. 创建函数，输出1-100的偶数和

14. 输出1000以内的水仙花数.

视频配套链接：SQL Server高级编程 - 网易云课堂 (163.com)