sql数据库中的基础知识、sql优化

1．触发器

理解：对于某个表，触发某种条件而执行的一段SQL代码， 常见的触发器有三种：分别应用于Insert , Update , Delete 事件。

          触发器是一中特殊的存储过程，主要是通过事件来触发而被执行的。它可以强化约束，来维护数据的完整性和一致性，可以跟踪数据库内的操作从而不允许未经许可的更新和变化。可以联级运算。如，某表上的触发器上包含对另一个表的数据操作，而该操作            又会导致该表触发器被触发。

实例：1先建两张数据表：Users表，Students表

           2创建触发器

CREATE TABLE [dbo].[Users](
[StudentID] [INT] IDENTITY(1,1) NOT NULL,
[Name] [VARCHAR](50) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[StudentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[Students](
[id] [INT] IDENTITY(1,1) NOT NULL,
[Name] [VARCHAR](50) NULL,
CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

--创建触发器 Insert 
CREATE trigger tg_InsertStudent
on Students --更新Students时
for INSERT --触发insert方法
as
INSERT dbo.Users(Name)VALUES ( (SELECT TOP 1 Name FROM dbo.Students ORDER BY id desc) ) --更新Students，所触发执行SQL

--当在Students表中添加数据时触发触发器，同时Users标也会更新 (向student表中添加数据时，users中也会添加数据)
INSERT dbo.Students
( Name )
VALUES ( '大雪' -- Name - varchar(50)
)

--创建触发器 Delete

CREATE TRIGGER tg_DeleteStudent
ON Students
FOR DELETE
AS
SELECT Name from deleted

Students表中数据：

删除students表数据，触发器中执行查询删除数据，结果：

 

--创建触发器Update
CREATE TRIGGER tg_UpdateStudent
ON Students
FOR UPDATE
AS
IF UPDATE(Name)
BEGIN
PRINT '修改的Name'
END;

Students表中数据：

修改students表数据，触发器中打印print，结果：

UPDATE dbo.Students SET Name='修改' WHERE id=14

 

2。存储过程

理解：存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在该程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个命令对象来调用存储过程。用分页实例说明：

ALTER PROC [dbo].[proc_PageData]  --创建存储
( --声明参数
@pageIndex INT = 1,
@pageSize INT = 10,
@icount INT = 0 OUTPUT --输出参数符合条件总记录数
)
AS
BEGIN
DECLARE @pageCount INT;
DECLARE @straccount NVARCHAR(4000);
SET @pageCount=(@pageIndex-1)*@pageSize;

WITH tt AS (
SELECT ROW_NUMBER() OVER(ORDER BY CreateDate ASC) RowId,* FROM dbo.test
)
SELECT TOP (@pageSize) * FROM tt WHERE RowId>@pageCount;

SELECT @icount=COUNT(1) FROM dbo.test;
END;
GO

https://www.cnblogs.com/doudouxiaoye/p/5804467.html

对于使用存储过程封装业务逻辑的好处，归纳起来有以下几点：

1、执行速度快。因为存储过程不需要解析。预先编译了

2、安全性。避免了sql注入(不是绝对的)，避免了暴露表结构和字段

3。索引的作用？和它的优点缺点是什么？

理解：索引就一种特殊的查询表，数据库的搜索引擎可以利用它加速对数据的检索。它很类似与现实生活中书的目录，不需要查询整本书内容就可以找到想要的数据。索引可以是唯一的，创建索引允许指定单个列或者是多个列。缺点是它减慢了数据录入的速度，同时也增加了数据库的尺寸大小。

创建表(没有主键)：

CREATE TABLE [dbo].[userT](
[ID] [INT] IDENTITY(1,1) NOT NULL,
[name] [NCHAR](10) NULL,
[userid] [INT] NULL
) ON [PRIMARY]

造数据1000000条

DECLARE @i INT = 0
DECLARE @name VARCHAR(50) = '第'
WHILE @i<1000000
BEGIN
SET @i=@i+1
INSERT INTO dbo.userT
( name, userid )
VALUES ( '第'+CONVERT(VARCHAR(50),@i), -- name - nchar(10)
@i -- userid - int
)
END

数据库中现在没有索引

创建索引

CREATE INDEX userid_Index
ON dbo.userT(userid)

表中userid上建立了索引，所以执行计划中是索引查找：

 

4。什么是事务？什么是锁？

答：事务就是被绑定在一起作为一个逻辑工作单元的SQL语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID测试，即原子性，一致性，隔离性和持久性。

锁：在DBMS中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不能使用某些数据或数据结构。当然锁还分级别的。

5。什么叫视图？游标是什么？

答：视图是一种虚拟的表，具有和物理表相同的功能。可以对视图进行增，改，查，操作，视图通常是有一个表或者多个表的行或列的子集。对视图的修改不影响基本表。它使得我们获取数据更容易，相比多表查询。

游标：是对查询出来的结果集作为一个单元来有效的处理。游标可以定在该单元中的特定行，从结果集的当前行检索一行或多行。可以对结果集当前行做修改。一般不使用游标，但是需要逐条处理数据的时候，游标显得十分重要。