sql（存储过程，事务，索引，游标，触发器）

1.SqlServer中like '%_%'来匹配下划线：

--在sql server的like中下划线类似于通配符%，所以无法使用like '%_%'来匹配下划线

select * from class

where name like '%\_%' escape '\'

2.下划线的位置可以任意匹配字符

3.集合[ ]，^取反

4.去字符左右空格，获取字符第一次出现位置（patindex）

--去除右边空格，去除左边空格 LTRIM（）

SELECT RTRIM(name) + ' (' + RTRIM(name) + ')'

FROM class

ORDER BY age;

--获取字符串第一次出现位置PatIndex

select patindex( '%s%' , 'asda' )

--获取当前年份，此函数返回日期的某一部分

select DATEPART(yy,'2018-03-20 08:32:24')

--SOUNDEX是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

select * from class

where soundex(name) = soundex('wangwus')

--having 给每个分组附加条件  where给全局附加条件

select name,age from class

where name not like '%lisi%'

group by name,age

having age>7

order by age desc

5.修改表

 --1.第一种复制数据，表结构必须一样

  insert into z select * from brryxx表$

 --2.复制表结构和数据

 SELECT *INTO zxc FROM brryxx表$;

 --3.oracle复制表（MariaDB、MySQL、Oracle、PostgreSQL和SQLite）

 CREATE TABLE CustCopy AS SELECT * FROM brryxx表$

 --4.delete 如果不加where条件，则删除全部记录

 delete from tableNAme

 --5.删除某一列

 alter table zxc drop column 年份

 --6.增加一列

 ALTER TABLE Vendors ADD vend_phone CHAR(20);

6.存储过程：

--创建带返回值的存储过程（oracle，sqlserver通用） as上面写参数，as下面声明变量

CREATE PROCEDURE MailingListCount

AS

DECLARE @cnt INTEGER

SELECT @cnt = COUNT(*)

FROM class

RETURN @cnt;

--调用带返回值的存储过程（oracle版调用）

var ReturnValue NUMBER

EXEC MailingListCount(:ReturnValue);

SELECT ReturnValue;

--调用带返回值的存储过程（sqlserver版调用）

DECLARE @ReturnValue INT

EXECUTE @ReturnValue=MailingListCount;

SELECT @ReturnValue;

--调用存储过程传参数，并接受返回值

Declare @returnValue int

Declare @lyknb char(10)

set @lyknb =''

exec  @returnValue = NewOrder @lyknb

select @returnValue

7.事务：

--sqlserver 开启事务，提交事务，回滚事务,如果第一条DELETE起作用，但第二条失败，则DELETE不会提交

BEGIN TRANSACTION

  --需要写的sql

COMMIT TRANSACTION

ROLLBACK TRANSACTION

--mysql开启事务,如果第一条DELETE起作用，但第二条失败，则DELETE不会提交

start TRANSACTION

--oracle开启事务,如果第一条DELETE起作用，但第二条失败，则DELETE不会提交

set TRANSACTION

--全局变量@@error返回执行的上一个 Transact-SQL 语句的错误号,如果前一个 Transact-SQL 语句执行没有错误 则返回 0

declare   @iErrorCount   int

set @iErrorCount = 0

begin tran Tran1

   insert into t1(Id, c1) values(1,1)

    set @iErrorCount=@iErrorCount+@@error

   insert into t1(Id, c1) values(2,2)

    set @iErrorCount=@iErrorCount+@@error

if @iErrorCount=0

begin

    COMMIT TRAN Tran1  --执行事务

end

else

begin

    ROLLBACK TRAN Tran1  --回滚事务

end

--保留点

--mysql，oracle中保留点

SAVEPOINT delete1

--sqlserver中保留点

SAVE TRANSACTION delete1;

--回退至保留点

BEGIN TRANSACTION

INSERT INTO Customers(cust_id, cust_name)

VALUES('', 'Toys Emporium');

SAVE TRANSACTION StartOrder;

INSERT INTO Orders(order_num, order_date, cust_id)

VALUES(20100,'2001/12/1','');

IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;

INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20100, 1, 'BR01', 100, 5.49);

IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;

INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)

VALUES(20100, 2, 'BR03', 100, 10.99);

IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder;

COMMIT TRANSACTION

8.约束：

--插入数据检查约束

CREATE TABLE OrderItems

(

order_num INTEGER NOT NULL,

order_item INTEGER NOT NULL,

prod_id CHAR(10) NOT NULL,

quantity INTEGER NOT NULL CHECK (quantity > 0),

item_price MONEY NOT NULL

);

9.索引：

--SqlServer索引

--UNIQUE：为表或视图创建唯一索引。 唯一索引不允许两行具有相同的索引键值。 视图的聚集索引必须唯一。如果要建唯一索引的列有重复值，必须先删除重复值。

--CLUSTERED：表示指定创建的索引为聚集索引。创建索引时，键值的逻辑顺序决定表中对应行的物理顺序。 聚集索引的底层（或称叶级别）包含该表的实际数据行。

--NONCLUSTERED：表示指定创建的索引为非聚集索引。创建一个指定表的逻辑排序的索引。 对于非聚集索引，数据行的物理排序独立于索引排序。

--创建索引

CREATE INDEX Customers_ind

ON Customers (cust_id);

--查看数据库索引的使用情况

select db_name(database_id) as N'test',  --库名

        object_name(a.object_id) as N'Customers',  --表名

        b.name N'索引名称',

        user_seeks N'用户索引查找次数',

        user_scans N'用户索引扫描次数',

        last_user_seek N'最后查找时间',

       last_user_scan N'最后扫描时间',

        rows as N'表中的行数'

from sys.dm_db_index_usage_stats a join

      sys.indexes b

      on a.index_id = b.index_id

     and a.object_id = b.object_id

     join sysindexes c

      on c.id = b.object_id

where database_id=db_id('test')   ---改成要查看的数据库

 and object_name(a.object_id) not like 'sys%'

 order by user_seeks,user_scans,object_name(a.object_id)

 --清空查询缓存

 DBCC freeproccache

-- 查看sql执行计划

SELECT  *

 FROM   OrderItems  --表名

SELECT  cacheobjtype ,

        objtype ,

       usecounts ,

        sql

FROM    sys.syscacheobjects

WHERE   sql NOT LIKE '%cach%'

        AND sql NOT LIKE '"sys."'

       AND cacheobjtype LIKE '%Plan%'

10.触发器：

--创建学生表测试触发器

create table student(

    stu_id int identity(1,1) primary key,

    stu_name varchar(10),

    stu_gender char(2),

    stu_age int

)

--创建insert触发器

create trigger trig_insert

on student

after insert

as

begin

    if object_id(N'student_sum',N'U') is null--判断student_sum表是否存在

        create table student_sum(stuCount int default(0));--创建存储学生人数的student_sum表

    declare @stuNumber int;

    select @stuNumber = count(*)from student;

    if not exists (select * from student_sum)--判断表中是否有记录

        insert into student_sum values(0);

    update student_sum set stuCount =@stuNumber; --把更新后总的学生数插入到student_sum表中

end

--测试触发器trig_insert-->功能是向student插入数据的同时级联插入到student_sum表中，更新stuCount

--因为是后触发器，所以先插入数据后，才触发触发器trig_insert;

insert into student(stu_name,stu_gender,stu_age)values('吕布','男',30);

select stuCount 学生总人数 from student_sum;

insert into student(stu_name,stu_gender,stu_age)values('貂蝉','女',30);

select stuCount 学生总人数 from student_sum;

insert into student(stu_name,stu_gender,stu_age)values('曹阿瞒','男',40);

select stuCount 学生总人数 from student_sum;

--既然定义了学生总数表student_sum表是向student表中插入数据后才计算学生总数的，所以学生总数表应该禁止用户向其中插入数据

--创建insert_forbidden,禁止用户向student_sum表中插入数据

create trigger insert_forbidden

on student_sum

after insert

as

begin

    RAISERROR('禁止直接向该表中插入记录，操作被禁止',1,1)--raiserror 是用于抛出一个错误

rollback transaction

end 

--创建delete触发器

create trigger trig_delete

on student

after delete

as

begin

    select stu_id as 已删除的学生编号,stu_name stu_gender,stu_age

    from deleted

end;

delete from student where stu_id=1;

--创建update触发器

--update触发器是当用户在指定表上执行update语句时被调用被调用，

--这种类型的触发器用来约束用户对数据的修改。update触发器可以执行两种操作：

--更新前的记录存储在deleted表中，更新后的记录存储在inserted表中。

create trigger trig_update

on student

after update

as

begin

    declare @stuCount int;

    select @stuCount=count(*) from student;

    update student_sum set stuCount =@stuCount;

    select stu_id as 更新前学生编号,stu_name as 更新前学生姓名 from deleted

    select stu_id as 更新后学生编号,stu_name as 更新后学生姓名 from inserted

end

--创建instead of 触发器 

--与前面介绍的三种after触发器不同，SqlServer服务器在执行after触发器的sql代码后，

--先建立临时的inserted表和deleted表，然后执行代码中对数据库操作，最后才激活触发器中的代码。

--而对于替代(instead of)触发器,SqlServer服务器在执行触发instead of 触发器的代码时，

--先建立临时的inserted表和deleted表，然后直接触发instead of触发器，而拒绝执行用户输入的DML操作语句。

create trigger trig_insteadOf

on student

instead of insert

as

begin

    declare @stuAge int;

    select @stuAge=(select stu_age from inserted)

if(@stuAge >120)

    select '插入年龄错误' as '失败原因'

end

--查看数据库中所有的触发器

use test --数据库名

go

select * from sysobjects where xtype='TR'

--查看触发器内容

use test  --数据库名

go

exec sp_helptext 'trig_insert' --触发器名称

--查看某个表有哪些触发器

use test --数据库名

go

exec sp_helptrigger 'dbo.student'  --表名

--禁用:alter table 表名 disable trigger 触发器名称

--启用:alter table 表名 enable trigger 触发器名称

--如果有多个触发器，则各个触发器名称之间用英文逗号隔开。

--如果把“触发器名称”换成“ALL”，则表示禁用或启用该表的全部触发器。

--修改触发器语法

ALTER TRIGGER  trigger_name

     ON  table_name

     [ WITH ENCRYPTION ]

     FOR {[DELETE][,][INSERT][,][UPDATE]}

     AS

       sql_statement;

--删除触发器

--语法格式:

      DROP  TRIGGER   { trigger } [ ,...n ]

参数:

 trigger: 要删除的触发器名称

 n:表示可以删除多个触发器的占位符

巴特西

sql（存储过程，事务，索引，游标，触发器）

最新文章

热门文章