--*********************批处理*********************

--[在一个批处理中存有一个语法错误，则所有的语句都无法通过编译]

USE flowershopdb

SELECT * FROM tb_category

SELECT * FROM tb_detailed

SELECT * FROM tb_product

SELECT * FROM tb_shopping

SELECT * FROM tb_user

INSERT tb_user(u_name,u_pass) VALUES('user3','')

DELETE tb_user WHERE u_id=4

UPDATE tb_user SET u_name='sa',u_pass='' WHERE u_id=2

GO

--*********************视图*********************

CREATE VIEW v_cd

AS

--内连接[不建议使用联合查询]

SELECT c.c_id,d.d_id,c.c_name,d.d_name,d.d_remark FROM tb_category c

INNER JOIN tb_detailed d

ON c.c_id=d.c_id

GO

SELECT * FROM v_cd

--*********************变量*********************

--局部变量

--如果事先不对变量赋值，变量将保持NULL的状态

--声明

DECLARE @name nvarchar(10)

DECLARE @age int

DECLARE @num int

--或者

DECLARE @name nvarchar(10),

        @age int,

        @num int

--赋值

--1 set

SET @name = '哈哈'

--2 select

SELECT @age = 5

SELECT @num=c_id FROM tb_category WHERE c_id=1000

--打印

PRINT @name

PRINT @age

PRINT @num

--变量作为条件

SELECT * FROM tb_category WHERE c_id>@num

GO

--全局变量

PRINT  'SQLServer的版本'+@@VERSION

PRINT  '服务器名称: '+@@SERVERNAME

PRINT '最后一次放生的错误号'+CONVERT(varchar(5),@@ERROR)

GO

--*********************结构语句*********************

--IF ELSE语句

DECLARE @price MONEY

SELECT  @price=AVG(p_price) FROM tb_product

IF (SELECT  p_price FROM tb_product WHERE p_id=3000)>@price

    BEGIN

        SELECT '高价'

        SELECT p_name,p_price FROM tb_product WHERE p_id=3000

    END

ELSE

    BEGIN

        SELECT '低价'

        SELECT p_name,p_price FROM tb_product WHERE p_id=3000

    END

GO

--IF EXISTS()语句 --条件

IF EXISTS(SELECT AVG(p_price) FROM tb_product)

PRINT 'haha'

IF EXISTS(SELECT p_price FROM tb_product WHERE p_price>100)

PRINT 'haha'

DECLARE @price MONEY

SELECT  @price=AVG(p_price) FROM tb_product

IF EXISTS(SELECT  p_name,p_price FROM tb_product WHERE p_price>@price)

    BEGIN

        SELECT '高价',p_name,p_price FROM tb_product WHERE p_price>@price

    END

ELSE

    BEGIN

        SELECT '低价',p_name,p_price FROM tb_product WHERE p_price<@price

    END

GO

--WHILE()语句 [WHILE(1=1)条件永远为真]

--*********************事务*********************

BEGIN TRANSACTION

/*--定义变量，用于累计事务执行过程中的错误--*/

DECLARE @errorSum INT

SET @errorSum=0  --初始化为0，即无错误

/*--转账product：百合花多10支,剑兰少10支*/

UPDATE tb_product SET p_number=p_number+10

   WHERE p_name LIKE '百合%'

SET @errorSum=@errorSum+@@error

UPDATE tb_product SET p_number=p_number-10

   WHERE p_name LIKE '剑兰%'

SET @errorSum=@errorSum+tb_product  --累计是否有错误

IF @errorSum>0

BEGIN

    ROLLBACK TRANSACTION

    SELECT '失败'

END

ELSE

BEGIN

    COMMIT TRANSACTION

    SELECT '成功'

End

--*********************分页语句*********************

DECLARE @pageSize int,

        @pageIndex int

SELECT  @pageSize=3,

        @pageIndex=1

BEGIN

SELECT TOP (@pageSize) p_id,p_price,p_name FROM tb_product

WHERE p_id NOT IN

    (

    SELECT TOP ((@pageIndex-1)*@pageSize) p_id FROM tb_product ORDER BY p_id DESC

    )

ORDER BY p_id DESC

END

--*********************存储过程*********************

--PROCEDURE 简写 PROC

--EXECUTE 简写 EXEC

--系统存储过程

--列出服务器上的所有数据库。

EXEC sp_databases

--显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本

EXEC sp_helptext 'v_cd'          --视图

EXEC sp_helptext 'proGetPageData'--存储过程

EXEC sp_helptext 'f'             --触发器

--查看某个表的所有信息

EXEC sp_help 'tb_product'

--自定义存储过程

--编写分页存储过程[采用子查询]

--@pageSize 页容量[一页显示几条数据]

--@pageIndex 第几页

ALTER PROCEDURE proGetPageData

@pageSize int=3,

@pageIndex int=1

AS

DECLARE @sqlStr varchar(300)

SET @sqlStr='select top '+
             str(@pageSize)+' * from tb_product where p_id not in(select top '+
             str((@pageIndex-1)*@pageSize)+
             ' p_id from tb_product order by p_id)order by p_id'

PRINT @sqlStr

EXEC(@sqlStr)

--调用存储过程[三种方式]

EXEC proGetPageData                             --调用含有默认参数的

EXECUTE proGetPageData 3,1                     --调用传参的

EXEC proGetPageData @pageSize=3,@pageIndex=2 --调用传值的

EXEC proGetPageData @pageSize=10             --pageSize自定义，pageIndex采用默认值

--采用输出参数的存储过程

--@pageCount 总页数 @rowCount 总行数

ALTER PROC [dbo].[proGetPageData2]

@pageIndex int=1,

@pageSize int=3,

@pageCount int OUTPUT,

@rowCount int OUTPUT

AS

BEGIN

    DECLARE @sqlStr nvarchar(300),@sqlCount nvarchar(300)

    SET @sqlCount = 'SELECT @rowCount=COUNT(p_id),@pageCount=CEILING((COUNT(p_id)+0.0)/'+ CAST(@pageSize AS VARCHAR)+') FROM tb_product'

    PRINT @sqlCount

    EXEC SP_EXECUTESQL @sqlCount,N'@rowCount INT OUTPUT,@pageCount INT OUTPUT',@rowCount OUTPUT,@pageCount OUTPUT

    SET @sqlStr='select top '+
                 str(@pageSize)+' * from tb_product where p_id not in(select top '+
                 str((@pageIndex-1)*@pageSize)+
                 ' p_id from tb_product order by p_id)order by p_id'

    PRINT @sqlStr

    EXEC(@sqlStr)

END

DECLARE @pc int

DECLARE @rc int

EXEC [proGetPageData2] 1,3,@pc OUTPUT,@rc OUTPUT

SELECT @pc,@rc

--简单查询语句存储过程

CREATE PROCEDURE proGetPageData_user

@u_id int

AS

BEGIN

    SELECT * FROM tb_user WHERE u_id=@u_id

END

EXEC proGetPageData_user 1

--*********************函数*********************

--系统函数

SELECT LEN('')

SELECT DATALENGTH('')

SELECT GETDATE()

--自定义函数

--分页函数[子查询]

--内联表值函数[返回值为表格]

CREATE FUNCTION funGetPageData(@pageSize int,@pageIndex int)

RETURNS TABLE

AS

RETURN

(

    SELECT TOP (@pageSize) p_id,p_price,p_name FROM tb_product

    WHERE p_id NOT IN(SELECT TOP ((@pageIndex-1)*@pageSize) p_id FROM tb_product ORDER BY p_id DESC )

    ORDER BY p_id DESC

)

--标量值函数[返回值为int]

CREATE FUNCTION demo(@a int,@b int)

RETURNS int

AS

BEGIN

    RETURN (@a+@b)

END

--调用函数

SELECT * FROM dbo.funGetPageData(3,1)

PRINT dbo.demo(1,2)

--*********************触发器*********************

--tb_product表更新触发器

--IF UPDATE(列名)检查是否更新了某一列，用于 insert 或 update，不能用于 delete。例：

CREATE TRIGGER f

ON tb_product

FOR UPDATE

AS

    IF UPDATE(p_name) or UPDATE(p_remark)

        PRINT 'tb_product表name和remark更新了'

--游标

declare @d_name varchar(max)

declare @d_remark varchar(max)

declare @d_price varchar(20)

declare @d_number varchar(4)

declare @count int

set @count = 0

declare yb cursor

for(select p_name,p_remark,p_price,p_number from tb_product)

open yb

fetch next from yb into @d_name,@d_remark,@d_price,@d_number

while (@@FETCH_STATUS = 0)

begin

    print '花名: '+@d_name

    print '备注: '+@d_remark

    print '价格: '+@d_price

    print '编号: '+@d_number

    print '--------------------------'

    set @count = @count + 1

    fetch next from yb into @d_name,@d_remark,@d_price,@d_number

end

close yb

deallocate yb

print '总数：'+cast(@count as varchar)

select count(*) from tb_product

--临时表

select * from tb_product

create table #wyk(pname varchar(200))

insert into #wyk values('啊啊')

select * from #wyk

drop table #wyk