sql study

-- =============================================
-- Author:  lifu
-- Create date: 2017-06-14
-- Description:  sql study
-- =============================================
CREATE DATABASE db_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
--
--　　　存储过程一般用在实现复杂的功能，数据操纵方面。



--事务=======================================================================
IF EXISTS (
    SELECT  * 
    FROM dbo.SysObjects 
    WHERE ID = object_id(N'[moneytbl]') 
    AND OBJECTPROPERTY(ID, 'IsTable') = 1
) 
print 'moneytbl exists'
else
BEGIN
CREATE TABLE moneytbl (
    id INT identity(1,1) primary key,
    total INT DEFAULT NULL
)
END

--添加约束
alter table moneytbl add
constraint CK_totalMoney check(total>=1)

INSERT INTO moneytbl(total)VALUES(2000)
INSERT INTO moneytbl(total)VALUES(3000)

CREATE PROCEDURE PR0C_Transfer 
    @t INT,
    @id1 INT,
    @id2 INT
AS
    BEGIN TRANSACTION
    BEGIN try
    UPDATE moneytbl SET total = total - @t WHERE id = @id1
    UPDATE moneytbl SET total = total + @t WHERE id = @id2
    END try
    BEGIN catch
        SELECT Error_number() AS ErrorNumber,  --错误代码
               Error_severity() AS ErrorSeverity,  --错误严重级别，级别小于10 try catch 捕获不到
               Error_state() AS ErrorState ,  --错误状态码
               Error_Procedure() AS ErrorProcedure , --出现错误的存储过程或触发器的名称。
               Error_line() AS ErrorLine,  --发生错误的行号
               Error_message() AS ErrorMessage  --错误的具体信息
               IF(@@trancount>0) --全局变量@@trancount，事务开启此值+1，他用来判断是有开启事务
                     PRINT 'wrong'
                     ROLLBACK TRANSACTION  ---由于出错，这里回滚到开始，第一条语句也没有插入成功。
    END catch
    /*--根据是否有错误，确定事务是提交还是回滚--*/
    IF (@@TRANCOUNT  > 0)
        BEGIN
            PRINT 'ok'
            COMMIT TRANSACTION
        END 
        
DROP PROCEDURE PR0C_Transfer

EXECUTE PR0C_Transfer 4000,1,2
--查看转账完成后的情况
SELECT * FROM moneytbl

--事务回滚至保存点
--SAVE TRANSACTION savePointName
--ROLLBACK TRANSACTION savePointName

--set xact_abort on/off
--指定是否回滚当前事务，为on时如果当前sql出错，回滚整个事务，为off时如果sql出错回滚当前sql语句，其它语句照常运行读写数据库。


--事务设置隔离级别
--set tran isolation level low    
--set tran isolation level read committed   




--锁===========================================================================================

--设置死锁处理的优先级别
--set deadlock_priority  low<normal<high    还可以使用数字来处理标识级别：-10到-5为low，-5为normal，-5到10为high


--减少死锁的发生，提高数据库性能
--    1.按照同一顺序访问数据库资源，上述例子就不会发生死锁啦
--    2.保持是事务的简短，尽量不要让一个事务处理过于复杂的读写操作。事务过于复杂，占用资源会增多，处理时间增长，容易与其它事务冲突，提升死锁概率。
--    3.尽量不要在事务中要求用户响应，比如修改新增数据之后在完成整个事务的提交，这样延长事务占用资源的时间，也会提升死锁概率。
--    4.尽量减少数据库的并发量。
--    5.尽可能使用分区表，分区视图，把数据放置在不同的磁盘和文件组中，分散访问保存在不同分区的数据，减少因为表中放置锁而造成的其它事务长时间等待。
--    6.避免占用时间很长并且关系表复杂的数据操作。
--    7.使用较低的隔离级别，使用较低的隔离级别比使用较高的隔离级别持有共享锁的时间更短。这样就减少了锁争用。

--查看锁活动情况
select * from sys.dm_tran_locks
--查看事务活动情况
dbcc opentran


--SQL高级=====================================================
--查询前50条记录
SELECT TOP 50 PERCENT * FROM usertbl
--like
SELECT * FROM usertbl WHERE name LIKE '%te'
SELECT * FROM usertbl WHERE name NOT LIKE '%te'
--通配符   _
SELECT * FROM usertbl WHERE name LIKE '__te'  -- _ 代表一个字符
--IN
SELECT * FROM usertbl WHERE name IN ('lee','tom','kite')  --匹配字符
--between
SELECT * FROM usertbl WHERE age BETWEEN 25 AND 35   --范围选择
--别名
SELECT name AS stuName FROM stutbl AS st WHERE st.id = 1
--inner join  连接查询2张表的全部内容
SELECT u.id,u.name,u.age,s.name,s.age,s.stuID
FROM usertbl AS u 
INNER JOIN stutbl AS s
ON u.id = s.id
--left join  连接查询第一张表的全部内容加上第二张表的符合条件的部分
SELECT u.id,u.name,u.age,s.stuID
FROM usertbl AS u
LEFT JOIN stutbl AS s
ON u.name = s.name
--right join 连接查询第二张表的全部内容加上第一张表的符合条件的部分
SELECT s.id,s.name,s.age,u.name,s.stuID
FROM usertbl AS u
RIGHT JOIN stutbl AS s
ON u.name = s.name
--full join 连接查询第一张表与第二张表，并取1次满足条件的部分
SELECT u.id,u.name,u.age,s.stuID
FROM usertbl AS u
FULL JOIN stutbl AS s
ON u.name = s.name
--union  合并查询去除重复的记录   如果不需要去除重复记录则加ALL  查询的列必须相同
SELECT id,name,age FROM usertbl
UNION ALL
SELECT id,name,age FROM stutbl
--select column_name(s) into 选定列表复制
SELECT * INTO #usertbl FROM usertbl
SELECT * FROM #usertbl
--删除临时表
if object_id('tempdb..#usertbl') is not null 
Begin
    drop table #usertbl
End
--insert into select
CREATE TABLE #stutbl
(
    id INT,
    name NVARCHAR(20),
    age INT
)

INSERT INTO #stutbl
(id,name,age) 
SELECT id,name,age
FROM stutbl

SELECT * FROM #stutbl

if object_id('tempdb..#stutbl') is not null 
Begin
    drop table #stutbl
End

--创建数据库
CREATE DATABASE databaseName

--约束
--在 SQL 中，我们有如下约束：
--
--    NOT NULL - 指示某列不能存储 NULL 值。
--    UNIQUE - 保证某列的每行必须有唯一的值。
--    PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
--    FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
--    CHECK - 保证列中的值符合指定的条件。
--    DEFAULT - 规定没有给列赋值时的默认值。

--删除约束(统一)
ALTER TABLE usertbl
DROP CONSTRAINT constraintName(约束名称)
--------------------------------------------------------
--unique
--添加约束
ALTER TABLE usertbl
ADD UNIQUE (age)
--------------------------------------------------------
--primary key
ALTER TABLE usertbl
ADD PRIMARY KEY (id)
--------------------------------------------------------
--foreign key 必须选择另外一张表的主键
CREATE TABLE stuStudy
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    stu_tId INT FOREIGN KEY REFERENCES stutbl(id)
)
--添加约束
ALTER TABLE stuStudy
ADD FOREIGN KEY (stu_tId)
REFERENCES stutbl(id)
--------------------------------------------------------
--check
CREATE TABLE stuAge
(
    id INT IDENTITY(1,1) PRIMARY KEY,
    age INT CHECK (age > 0)
)
INSERT INTO stuAge(age)VALUES(-10) 
--添加约束
ALTER TABLE stuAge
ADD CHECK (age >0)
--------------------------------------------------------
--default
--添加约束
ALTER TABLE usertbl
ALTER COLUMN age SET DEFAULT 20
--删除约束
ALTER TABLE usertbl
ALTER COLUMN age DROP DEFAULT 
--------------------------------------------------------


--create index  创建索引
CREATE UNIQUE INDEX index_name
ON TABLE table_name (column_name)

--drop
DROP INDEX table_name.index_name
DROP TABLE table_name
DROP DATABASE database_name

--视图
CREATE VIEW user_view 
AS
SELECT *
FROM usertbl

DROP VIEW user_view

SELECT * FROM user_view

--date
PRINT GETDATE()

--is null || is not null
SELECT * FROM usertbl WHERE age IS NOT NULL

--Null 函数
--isnull()  判断为null返回指定的值  这里为0
SELECT id*(1+ISNULL(age,0))
FROM usertbl