sql study
时间：2021-07-01 10:21:17
帮助过：6人阅读
=============================================
-- Author: lifu
-- Create date: 2017-06-14
-- Description: sql study
-- =============================================
use db_study;
--判断是否存在表
IF EXISTS (
SELECT *
FROM dbo.SysObjects
WHERE ID
= object_id(N
‘[usertbl]‘)
AND OBJECTPROPERTY(ID,
‘IsTable‘)
= 1
)
print ‘usertbl exists‘
else
BEGIN
CREATE TABLE usertbl (
id INT identity(
1,
1)
primary key,
name NVARCHAR(
20)
DEFAULT NULL,
age INT DEFAULT NULL
)
END
IF EXISTS (
SELECT *
FROM dbo.SysObjects
WHERE ID
= object_id(N
‘[stutbl]‘)
AND OBJECTPROPERTY(ID,
‘IsTable‘)
= 1
)
print ‘stutbl exists‘
else
BEGIN
CREATE TABLE stutbl(
id INT identity(
1,
1)
primary key,
name NVARCHAR(
20)
DEFAULT NULL,
age INT DEFAULT NULL,
stuID INT DEFAULT NULL
)
END
--插入数据
--INSERT INTO usertbl(name,age)VALUES(‘lee‘,28)
--INSERT INTO usertbl(name,age)VALUES(‘kite‘,32)
--INSERT INTO stutbl(name,age,stuID)VALUES(‘lee‘,28,07110511)
--INSERT INTO stutbl(name,age,stuID)VALUES(‘tom‘,19,07110512)
--判断存储过程是否存在
if exists (
select *
from dbo.sysobjects
where id
= object_id(N
‘[listUser]‘)
and OBJECTPROPERTY(id, N
‘IsProcedure‘)
= 1
)
DROP PROCEDURE listUser
ELSE
--有输入参数的存储过程
create PROCEDURE listUser
@name VARCHAR(
20)
AS
DECLARE @age INT
SET @age = 28
SELECT *
FROM usertbl
WHERE name
=@name
AND age
=@age
--执行存储过程
EXECUTE listUser
@name = ‘lee‘
--有输入输出参数的存储过程
CREATE PROCEDURE isListUser
@age INT,
@name VARCHAR(
20),
@isRight INT OUTPUT
AS
if exists (
select *
from dbo.sysobjects
where id
= object_id(N
‘[listUser]‘)
and OBJECTPROPERTY(id, N
‘IsProcedure‘)
= 1
)
SET @isRight = @age
ELSE
SET @isRight = 0
DECLARE @isRight INT
EXECUTE isListUser
28,
‘lee‘,
@isRight OUTPUT
SELECT @isRight
DROP PROCEDURE isListUser
--select 赋值
CREATE PROCEDURE getUser
AS
DECLARE @resultSet VARCHAR(
20)
SET @resultSet = (
SELECT name
FROM usertbl
WHERE age
= 28
)
SELECT @resultSet
EXECUTE getUser
DROP PROCEDURE getUser
--查看表索引
EXECUTE sp_helpindex
@objname=‘usertbl‘
--update 赋值
CREATE PROCEDURE testUpdate
AS
DECLARE @nage INT
DECLARE @nname VARCHAR(
20)
UPDATE usertbl
SET @nage = age
WHERE id
=1
SELECT @nname = name
FROM usertbl
where id
=1
PRINT @nage
PRINT @nname
EXECUTE testUpdate
--while
DECLARE @a INT
SET @a = 1
WHILE @a<10
BEGIN
SET @a = @a + 1
END
PRINT @a
END
--if
IF (
1+1=2)
BEGIN
PRINT ‘right‘
END
ELSE
BEGIN
PRINT ‘wrong‘
END
--when then
DECLARE @today INT
DECLARE @theWeek NVARCHAR
SET @today = 3
SET @theWeek = CASE
WHEN @today = 1 THEN ‘星期一‘
WHEN @today = 2 THEN ‘星期二‘
WHEN @today = 3 THEN ‘星期三‘
WHEN @today = 4 THEN ‘星期四‘
WHEN @today = 5 THEN ‘星期五‘
WHEN @today = 6 THEN ‘星期六‘
WHEN @today = 7 THEN ‘星期日‘
END
PRINT @theWeek
--游标 类似将数据一条一条读取出来 形成类似栈的结构
DECLARE @name NVARCHAR(
20)
DECLARE @age INT
DECLARE @resultSet NVARCHAR(
20)
--定义一个游标
DECLARE myCursor
CURSOR FOR
SELECT name,age
FROM usertbl
WHERE id
=1
--打开游标 指针指向第一条数据
OPEN myCursor
WHILE @@fetch_status=0
BEGIN
--读取游标 指针向前推进
FETCH NEXT from usertbl
INTO @name,
@age
SELECT @resultSet = name
FROM usertbl
WHERE name
=@name
PRINT @resultSet
END
--关闭游标
CLOSE myCursor
--摧毁游标
DEALLOCATE myCursor
--return procedure
CREATE PROCEDURE testReturn
@a INT,
@b INT
AS
BEGIN
RETURN @a + @b
END
DECLARE @c INT
EXECUTE @c = testReturn
1,
2
SELECT @c
--触发器
CREATE TRIGGER User_onUpdate
ON usertbl
FOR UPDATE
AS
DECLARE @msg NVARCHAR(
50)
--@msg 记录修改情况
SELECT @msg = N
‘姓名从“‘ + Deleted.Name
+ N
‘”修改为“‘ + Inserted.Name
+ ‘”‘ from Inserted,Deleted
PRINT @msg
--删除触发器
DROP TRIGGER User_OnUpdate
--测试触发器
UPDATE usertbl
SET name
= ‘biglee‘ WHERE id
= 1
--函数
--函数的分类：
--
-- 1）标量值函数
--
-- 2）表值函数
--
-- a:内联表值函数
--
-- b:多语句表值函数
--
-- 3）系统函数
--标量值函数
CREATE FUNCTION FUNC_sum
(
@a INT,
@b INT
)
RETURNS INT
AS
BEGIN
RETURN @a + @b
END
--调用标量值函数
DECLARE @r INT
SET @r = dbo.FUNC_sum(
100,
50)
PRINT @r
--内联表值函数
CREATE FUNCTION FUNC_usertbl
(
@age INT
)
RETURNS TABLE
AS
RETURN (
SELECT * FROM usertbl
WHERE age
<@age)
--删除函数
DROP FUNCTION FUNC_usertbl
--调用内敛表值函数
SELECT * FROM dbo.FUNC_usertbl(
30)
--多语句表值函数(可以将查询的数据集放在局部临时表中)
CREATE FUNCTION FUNC_UserTbl2
(
@myId INT
)
RETURNS @t TABLE
(
id INT PRIMARY KEY,
name NVARCHAR(
20)
DEFAULT NULL,
age INT DEFAULT NULL
)
AS
BEGIN
INSERT INTO @t SELECT * FROM usertbl
WHERE id
= @myId
RETURN
END
--调用多语句表值函数
SELECT * FROM dbo.FUNC_UserTbl2(
1)
--一、自定义函数：
--
-- 1. 可以返回表变量
--
-- 2. 限制颇多，包括
--
-- 不能使用output参数；
--
-- 不能用临时表；
--
-- 函数内部的操作不能影响到外部环境；
--
-- 不能通过select返回结果集；
--
-- 不能update，delete，数据库表；
--
-- 3. 必须return 一个标量值或表变量
--
-- 自定义函数一般用在复用度高，功能简单单一，争对性强的地方。
--
--二、存储过程
--
-- 1. 不能返回表变量
--
-- 2. 限制少，可以执行对数据库表的操作，可以返回数据集
--
-- 3. 可以return一个标量值，也可以省略return
--
-- 存储过程一般用在实现复杂的功能，数据操纵方面。
sql study
标签：星期六 赋值 数据 class 不能 out ted 功能 get
