[SQL]课堂记录

RTRIM ---
LTRIM ---
删除字符左右的空格
RIGHT右边取字符
LEFT左边取字符
MID任意位置取字符

DECLARE @string1 char(11),@string2 char(14)
set @string1 = ' Example'
set @string2 = 'SQLSERVER'
select @string2 + @string1 as '字符串简单链接'
RTRIM(@string2) + LTRIM(@string1) as '去除空格后的连接'

select right(rtrim(customerID),2) as '客户编号',
    left(ConnectName,1) AS '客户姓氏',
    telephone AS '联系电话'
FROM Customer
//运用取字符的实例

//全局变量(两个@@标记)
p120   

//输出语句

1. print 变量或字符串
2.select 变量 as 自定义列名（字符串）



//局部变量
declare @变量名 数据类型，[------]（可以为多个）

//赋值

set 变量=所要赋值的值
(赋值时   日期型 不用加单引号)
//总的例句
declare @xb char(2),@sr datetime
set @xb='女'
set @sr=1/01/65
select *
from Seller
where sex=@xb and Birthday>=@sr


//让我蛋碎的代码
use XSGL
go
declare @a char(20),@b char(10)
set @a='陈玄风'
select *
from student
where sname=@a 
set @b=(select sno from student 
where sname=@a)
select *
from student
where sno=@b-1 or sno=@b+1

//创建标量函数的语法格式如下:
CREATE FUNCTION  TOTAL(@expr1 as int,@exper2 as int)
returns int
begin
    declare @my_total int
    select @my_total = @exper1 + @exper2
    return @my_total
end 
//传入参数 相加后返回

use sales
go
create function F_OrderInfoByID(@nA int,@nB int,@dA datetime,@dB datetime)
Returns table
as
Return(select seller,orderid,orderdate,customer,product,quantity
from v_orderinfo where orderid>=@nA and orderid<=@nB
union
select seller,orderid,orderdate,customer,product,quantity
from v_orderinfo where orderdate>=@dA and orderdate<=@dB
)

//作业1111111111111111
use sales
go
create function f_orderinfobySeller(@nIdName char(30),@dA datetime,@dB datetime)
Returns table
as
Return(select seller,orderid,orderdate,customer,product,quantity
from v_orderinfo where seller=@nIdName AND orderdate>=@dA and orderdate<=@dB
)
//作业222222222222222222

create view v_orderinfo
as
select o.orderid,o.orderdate,
o.customerid+''+c.companyname as customer,
o.saleid+''+s.salename as seller,
p.productid+''+p.productname as product,
od.quantity,c.customerid,s.saleid,p.productid
from product p
inner join orderdetail od
on p.productid=od.productid
inner join orders o
on o.orderid=od.orderid
inner join customer c
on o.customerid=c.customerid
inner join seller s
on o.saleid=s.saleid

use xsgl
go
while(select min(grade) from score) < 60
begin
    update score set grade = grade + 2

    update score set grade = 100 where grade > 100

end
print '当前修改后'
select * from score
//如果有人小于60分就所有人都加两分 
//一直到所有人都大于60分
//如果加分后有人超过一百分
//则超过一百分的人按100算

//综合例句
create function fun_findstock(@proid as char(6))
returns int
begin  
     declare @mystocks int
      select @mystocks=case
        when categoryid=1 and stocks<150 then 0
        when categoryid=1 and stocks>=150 then 1
        when categoryid=2 and stocks<100 then 0
        when categoryid=2 and stocks>=100 then 1
        when categoryid=3 and stocks<50 then 0
        when categoryid=3 and stocks>=50 then 1
end
 from Product
where productid=@proid
return @mystocks
end
go


//调用函数
declare @proid char(6),@stock int
set @proid='P03003'
set @stock=dbo.fun_findstock(@proid)
begin 
if @stock=0
 print '该产品的库存量较少，需要进货'
else
print '该产品的库存量较多，不需要进货'
end