数据库笔记--基本应用

数据库使用过程中一些零散的应用，记录下来以便下次使用。

 

1、触发器

刚才在项目中使用到触发器，现在拿来做记录，以便后续使用。

CREATE TRIGGER ON_DELETE
ON [OSCE].[dbo].[QuestionType]
FOR DELETE
AS
BEGIN
IF EXISTS (
SELECT * FROM DELETED WHERE QT_TD> 38 AND QT_ID < 41)
ROLLBACK;
END

本触发器的意思是：创建触发器on_delete，确保数据库表 [OSCE].[dbo].[QuestionType] 中的QT_ID是39、40的这两条记录不被删除。如果删除了，则回滚。

删除触发器：drop Trigger on_delete

 

两个表：写入表和读取表，数据保持同步，两个触发器，一个插入一个更新：

USE [TABLENAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER  TRIGGER [dbo].[tgr_order_insert]
ON [dbo].[Order]
AFTER INSERT
AS
BEGIN
    --FROM table [Order]
    DECLARE @Id [uniqueidentifier];
    DECLARE @AccessionNumber [nvarchar](128);
    DECLARE @Age [int];
    DECLARE @AgeUnit [nvarchar](50);
    
    SELECT 
            @Id                   =     [Id],     
            @AccessionNumber      =     [AccessionNumber],
            @Age                  =        [Age]            
    FROM inserted;    
    
    --插入OrderQuery
    INSERT INTO [dbo].[OrderQuery]
              (
                [Id],     
                [AccessionNumber],
                [Age]
              )                      
    VALUES
               (
                @Id,     
                @AccessionNumber,
                @Age        
               ); 
                   
    EXEC [dbo].[SP_UpdateOrderQueryExtendFields] @Id
END

USE [TABLENAME]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tgr_order_update]
ON [dbo].[Order]
AFTER UPDATE
AS
BEGIN
    DECLARE @Id [uniqueidentifier];
    DECLARE @oldAccessionNumber [nvarchar](128);    
    DECLARE @oldAge [int];
    
    DECLARE @newAccessionNumber [nvarchar](128);    
    DECLARE @newAge [int];
    
    SELECT 
        @Id                       =     [Id],     
        @oldAccessionNumber       =     [AccessionNumber],
        @oldAge                   =        [Age]
    FROM Deleted;
    
    SELECT 
        @newAccessionNumber       =     [AccessionNumber],
        @newAge                  =        [Age]
    FROM inserted;
    
    DECLARE @IsUpdateOrder bit;
    SET @IsUpdateOrder=0

    --更新OrderQuery
    IF ((@oldAccessionNumber<>@newAccessionNumber)     
        OR (dbo.IsNotEqualInt(@oldAge,@newAge)=1) 
      )     
         UPDATE [dbo].[OrderQuery] SET                                                          
                AccessionNumber      = @newAccessionNumber,       
                Age                  = @newAge               
         WHERE Id = @Id;
END

 

2、索引

建立索引的目的是加快对表中记录的查找或排序。为表设置索引要付出代价的：一是增加了数据库的存储空间，二是在插入和修改数据时要花费较多的时间(因为索引也要随之变动)。数据库索引就是为了提高表的搜索效率而对某些字段中的值建立的目录 。

聚簇索引 是按照数据存放的物理位置为顺序的，而非聚簇索引就不一样了；聚簇索引能提高多行检索的速度，而非聚簇索引对于单行的检索很快。

与非聚焦索引相比，聚集索引通常提供更快的数据访问速度。

分类：
唯一索引(UNIQUE)：不允许两行具有相同的索引值（创建了唯一约束，系统将自动创建唯一索引）
主键索引：主键索引要求主键中的每个值是唯一的，（创建主键自动创建主键索引）
聚集索引(CLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，表中只能包含一个聚集索引，主键列默认为聚集索引
非聚集索引(NONCLUSTERED)：表中各行的物理顺序与键值的逻辑（索引）顺序不匹配，表中可以有249个非聚集索引

语法：
CREATE [索引类型] INDEX 索引名称
ON 表名(列名)
WITH FILLFACTOR = 填充因子值0~100
GO

create nonclustered index ix_test
on test_0607(a)
with fillfactor = 30

 

索引的填充因子

创建索引时，可以指定一个填充因子，以便在索引的每个叶级页上留出额外的间隙和保留一定百分比的空间，供将来表的数据存储容量进行扩充和减少页拆分的可能性。填充因子的值是从 0 到 100 的百分比数值，指定在创建索引后对数据页的填充比例。值为 100 时表示页将填满，所留出的存储空间量最小。只有当不会对数据进行更改时（例如，在只读表中）才会使用此设置。值越小则数据页上的空闲空间越大，这样可以减少在索引增长过程中对数据页进行拆分的需要，但需要更多的存储空间。当表中数据会发生更改时，这种设置更为适当。

填充因子越大,意味着一个索引页包含的索引记录越多,空闲空间越小.一般来说查询的效率越高,因为这样查询的时候,可以减少读取索引页的工作量和减少内存使用，但这样导致的结果是数据变动导致的索引维护的工作量增加,因为索引页的空闲空间小,如果不能在本页内完成索引调整,就会引起调整其他索引页。所以一般的选择是,数据基本不变化的（例如OLAP的场景，数据只用来分析的）,将填充因子设置到足够大,数据经常变化的（例如OLTP的场景）,将填充因子设置为足够小。

一位网友的总结看着更加清晰：

创建索引可以大大提高系统的性能：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

增加索引也有许多不利的方面：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

索引的一些总结

浅谈数据库优化之-索引

SQL Server 深入解析索引存储(上)

3、编写外键约束

表[Test].[dbo].[QuestionInfo]  有三个字段：[id],[Name],[Mid]

表[Test].[dbo].[MTest]，有三个字段：[Mid],[Mname],[Mage]

现在创建表QuestionInfo的字段Mid的外键约束，即表QuestionInfo的字段Mid是表[Test].[dbo].[MTest]主键。sql语句如下：

ALTER TABLE [Test].[dbo].[QuestionInfo] 
ADD constraint FK_MD FOREIGN KEY (Mid)
references [Test].[dbo].[MTest](Mid)

 

删除上述外键：alter table [Test].[dbo].[QuestionInfo] drop FK_MD

 

4、数据库视图

表[Test].[dbo].[QuestionInfo]  有三个字段：[id],[Name],[Mid]

表[Test].[dbo].[MTest]，有三个字段：[Mid],[Mname],[Mage]

创建QuestionInfo_MTest视图，加入[id],[Name],[Mid],[Mname],[Mage]

查询函数：

SELECT     dbo.QuestionInfo.id, dbo.QuestionInfo.Name, dbo.QuestionInfo.Mid, dbo.MTest.Mname, dbo.MTest.Mage
FROM         dbo.MTest INNER JOIN
                      dbo.QuestionInfo ON dbo.MTest.Mid = dbo.QuestionInfo.Mid

自此建立视图。

 

创建视图后供第三方进行调用，提供一个专门的用户，只具备访问某个或者某些视图的权限，脚本如下：

EXEC sp_addrole 'r_cdas';
GRANT SELECT ON v_PdfPath TO r_cdas;
EXEC sp_addlogin 'cdas','a123456@','dbname';
EXEC sp_grantdbaccess 'cdas','u_PACS';
EXEC sp_addrolemember 'r_cdas','u_PACS';
GO

第二次再增加视图到该用户权限时执行：

GRANT SELECT ON v_PdfPathNew TO r_cdas;
GO

 

5、数据库分页

此处拿sqlserver举例说明。在项目中有时为了分页显示数据，需要调用部分数据，如果逻辑处理由程序语言进行，必然会影响到其性能。鉴于数据库的强大功能，将其交给数据库是一个比较明智的选择吧。

举例说明备注：[MultiStationExamArrangement]是数据库表，[MSEA_ID]是其中的一个不会重复的字段。现在需要查询该表中10001-10050的数据。

数据库分页三种方式：

1）使用两个select和两个order。先按照某字段的排序，查找前10050条记录，形成数据集。在进行倒序查找其前50条数据。

  select top 50 * from (
  select top  10050 * from [MultiStationExamArrangement] ORDER BY [MSEA_ID])
  as temp order by MSEA_ID DESC

因为查到的数据是倒序排列，如果想得到正序，可以进一步处理（如果有必要）：

SELECT * FROM (
  select top 50 * from (select top  10050 * from [MultiStationExamArrangement] ORDER BY [MSEA_ID])as temp order by MSEA_ID DESC)
   AS TEMP order by MSEA_ID 

 

2）使用两个select，产生临时序号。首先查找所有的相关数据，并增加新的列存储记录临时序号，形成数据集。再根据数据集中的临时序号查找相应的数据。

SELECT * FROM (
  SELECT *,ROW_NUMBER() OVER(ORDER BY [MSEA_ID]) AS ROWRANK FROM [MultiStationExamArrangement] ) AS TEMP 
  WHERE ROWRANK BETWEEN 10001 AND 10050

 

3）创建临时表，产生临时ID。首先创建一个只有自增ID和你需要查找相关记录的的字段的临时列表，并将你查找记录的主键填入临时表。再查询临时表，根据临时ID查找相应的数据。此种方法较为复杂，但可扩展性比较大。

CREATE TABLE #TEMPTABLE
  (
    INDEXID INT IDENTITY(0,1) NOT NULL,
    MSEA_ID INT
  )

  INSERT INTO #TEMPTABLE(MSEA_ID) 
  SELECT MSEA_ID FROM [MultiStationExamArrangement]
  ORDER BY MSEA_ID
  
  SELECT T.* FROM [MultiStationExamArrangement] T,#TEMPTABLE P
  WHERE P.MSEA_ID = T.MSEA_ID AND P.INDEXID BETWEEN 10000 AND 10049
  
  DROP TABLE #TEMPTABLE

 

6、存储过程

SQL Server 存储过程

 　　存储过程Procedure是一组为了完成特定功能的sql语句集合，经编译后存储在数据库中，用户通过指定的存储过程的名称来执行。存储过程中可以包含逻辑控制语句和数据操作语句，它可以接受参数、输出参数、返回单个或多个结果集以及返回值。由于存储过程在创建时即在数据库服务器中进行了编译并存储在数据库中，所以存储过程运行比单个sql语句集合要快，同时由于存储过程在调用时只需要提供名称和必要的参数信息，因此在一定程度上减少了网络流量和网络负担。

　　存储过程的优点：能够实现较快的执行速度，减轻网络流量（上文已解释），可作为一种安全机制来充分利用（系统管理员可以对执行的某一个存储过程进行权限限制，从而能够实现对某些数据访问的限制，避免非授权用户对数据的访问，保证数据安全）。

1）创建语言：

create proc | procedure pro_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements

2）不带参数存储过程：

--创建存储过程
if (exists (select * from sys.objects where name = 'proc_get_student'))
    drop proc proc_get_student
go
create proc proc_get_student
as
    select * from student;

--调用、执行存储过程
exec proc_get_student;

3）带参数存储过程：

--带参存储过程
if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
    select * from student where id between @startId and @endId
go

exec proc_find_stu 2, 4;

4）带传出参数的存储过程：

if (object_id('proc_getStudentRecord', 'P') is not null)
    drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @id int, --默认输入参数
    @name varchar(20) out, --输出参数
    @age varchar(20) output--输入输出参数
)
as
    select @name = name, @age = age  from student where id = @id and sex = @age;
go

-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 7; 
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

 c#后台执行存储过程：

string strsql = "Data Source=192.168.24.53;Initial Catalog=JF_Charging_System;Persist Security Info=True;User ID=sa;Password=1";//数据库链接字符串  
string sql = "myinsert";//要调用的存储过程名  
SqlConnection conStr = new SqlConnection(strsql);//SQL数据库连接对象，以数据库链接字符串为参数  
SqlCommand comStr = new SqlCommand(sql, conStr);//SQL语句执行对象，第一个参数是要执行的语句，第二个是数据库连接对象  
comStr.CommandType = CommandType.StoredProcedure;//因为要使用的是存储过程，所以设置执行类型为存储过程  
//依次设定存储过程的参数  
comStr.Parameters.Add("@username", SqlDbType.VarChar, 10).Value = "11";  
comStr.Parameters.Add("@password", SqlDbType.VarChar, 10).Value = "11";  
comStr.Parameters.Add("@name", SqlDbType.VarChar, 10).Value = "11";  
comStr.Parameters.Add("@usertype", SqlDbType.VarChar, 10).Value = "11";  
comStr.Parameters.Add("@createpeople", SqlDbType.VarChar, 10).Value = "11";  
conStr.Open();//打开数据库连接  
MessageBox.Show(comStr.ExecuteNonQuery().ToString());//执行存储过程  
conStr.Close();//关闭连接  

语法大致如下：

DECLARE @Now datetime
    DECLARE @OrderId varchar(64)
    DECLARE @MergeId varchar(64)
    SET @Now = GETDATE() 
    DELETE [order] WHERE DATEDIFF(d,CheckInTime,@Now)>0
    SELECT @OrderId = OrderId,@MergeId = MergeId from [Order] where OrderStatus>30 
    DELETE [order] WHERE OrderStatus>30 OR MergeId = @OrderId OR OrderId = @MergeId OR MergeId = @MergeId

 

7、sqlserver中sql语句执行顺序（摘抄）

整理：sql server 中sql语句执行顺序

　　SQL 不同于与其他编程语言的最明显特征是处理代码的顺序。在大数编程语言中，代码按编码顺序被处理，但是在SQL语言中，第一个被处理的子句是FROM子句，尽管SELECT语句第一个出现，但是几乎总是最后被处处理。每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用者（客户端应用程序或者外部查询）不可用。只是最后一步生成的表才会返回 给调用者。如果没有在查询中指定某一子句，将跳过相应的步骤。

(8)SELECT (9)DISTINCT  (11)<Top Num> <select list>
(1)FROM [left_table]
(3)<join_type> JOIN <right_table>
(2)        ON <join_condition>
(4)WHERE <where_condition>
(5)GROUP BY <group_by_list>
(6)WITH <CUBE | RollUP>
(7)HAVING <having_condition>
(10)ORDER BY <order_by_list>

 

逻辑查询处理阶段简介：

1）from：对FROM子句中的前两个表执行笛卡尔积（Cartesian product)(交叉联接），生成虚拟表VT1

2）on：对VT1应用ON筛选器。只有那些使<join_condition>为真的行才被插入VT2

3）outer(join)：如 果指定了OUTER JOIN（相对于CROSS JOIN 或(INNER JOIN),保留表（preserved table：左外部联接把左表标记为保留表，右外部联接把右表标记为保留表，完全外部联接把两个表都标记为保留表）中未找到匹配的行将作为外部行添加到 VT2,生成VT3.如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表为止。

4）where：对VT3应用WHERE筛选器。只有使<where_condition>为true的行才被插入VT4.

5）group by：按GROUP BY子句中的列列表对VT4中的行分组，生成VT5.

6）cube|roolup:把超组(Suppergroups)插入VT5,生成VT6.

7)having:对VT6应用HAVING筛选器。只有使<having_condition>为true的组才会被插入VT7.

8)select:处理SELECT列表，产生VT8.

9)distinct:将重复的行从VT8中移除，产生VT9.

10)order by:将VT9中的行按ORDER BY 子句中的列列表排序，生成游标（VC10)

11)top:从VC10的开始处选择指定数量或比例的行，生成表VT11,并返回调用者。

注：

步骤10，按ORDER BY子句中的列列表排序上步返回的行，返回游标VC10.这一步是第一步也是唯一一步可以使用SELECT列表中的列别名的步骤。这一步不同于其它步骤的 是，它不返回有效的表，而是返回一个游标。SQL是基于集合理论的。集合不会预先对它的行排序，它只是成员的逻辑集合，成员的顺序无关紧要。对表进行排序 的查询可以返回一个对象，包含按特定物理顺序组织的行。ANSI把这种对象称为游标。理解这一步是正确理解SQL的基础。

因为这一步不返回表（而是返回游标），使用了ORDER BY子句的查询不能用作表表达式。表表达式包括：视图、内联表值函数、子查询、派生表和共用表达式。它的结果必须返回给期望得到物理记录的客户端应用程序。

 

　　在SQL中，表表达式中不允许使用带有ORDER BY子句的查询，而在T—SQL中却有一个例外（应用TOP选项）。所以要记住，不要为表中的行假设任何特定的顺序。换句话说，除非你确定要有序行，否则不要指定ORDER BY 子句。排序是需要成本的，SQL Server需要执行有序索引扫描或使用排序运行符。

 

2015.4.3日补充：

和sql语句的执行顺序有关的是其性能的优化，其中之一是表关联顺序。sql语句执行时是按照从右到左的顺序处理from子句中的表名，from子句中写在最后的表也即是基础表将被最先处理，因此在from子句中包含多个表的情况下，选择记录条数最少的表作为基础表，在某种程度上将会极大的提高其性能。如果有3个以上的表，则选择交叉表作为基础表。

 

8、sqlserver数据库一些常用操作：

合理使用：

where 1=1

清理查询缓存：

为什么要清除缓存，有一定开发经验的程序员都知道，把一条耗时的语句在SQLServer中查询，第一次可能很慢，第二次就很快了。这样非常影响测试的效率，甚至有些程序员竟然认为第二次查询跟第一次查询不一样，这是一种不可靠的测试，没法重现问题，把责任归咎于SQLServer，巨硬那是真真正正的躺枪。

其实，上述问题的产生，主要是因为SQLServer每次查询，都会把结果缓存下来，遇到相同的SQL语句或者类似的，会从已有的缓存中查询，缓存中不存在的，才实际访问数据库。到底内存缓存多少，你可以设置：

 

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SQLSERVER DBCC命令大全

 

9、创建临时表

网上拷贝的只做暂时记录，其正确性有待后续搜索验证：

sqlserver中说到临时表就不得不提到永久表

1、临时表与永久表相似，但临时表存储在tempdb中，当不再使用时会自动删除。临时表有两种类型：本地和全局。它们在名称、可见性以及可用性上有区别。本地临时表的名称以单个数字符号 (#) 打头；它们仅对当前的用户连接是可见的；当用户从 SQL Server 实例断开连接时被删除。全局临时表的名称以两个数字符号 (##) 打头，创建后对任何用户都是可见的，当所有引用该表的用户从 SQL Server 断开连接时被删除。

2、临时表就是用户在创建表的时候添加了“#”前缀的表，其特点是根据进程独立。只有进程的拥有者有表的访问权限，其它用户不能访问该表；

3、不同的用户进程，创建的临时表虽然“名字”相同，但是这些表之间相互并不存在任何关系；在SQLSERVER中，通过特别的命名机制保证临时表的进程独立性。

4、定单和购买意向一般不会保存在真正的“临时表”中，而是实际的普通表，之所以称之为“临时表”，只是一种叫法而已。因为随着一个用户进程的结束，真正的临时表会自动清除，而定单和购买意向数据一般是定时清除，所以一定是保存在普通表中，具备数据的持久性特征（临时表最缺乏的就是数据的持久性）。

5、真正的临时表利用了数据库临时表空间，由数据库系统自动进行维护，因此节省了表空间。并且由于临时表空间一般利用虚拟内存，大大减少了硬盘的I/O次数，因此也提高了系统效率。

 

10、常见影响数据库性能的因素

1.业务需求和技术选型

2.应用系统的开发及架构

3.数据库自身

3.1表结构的设计

3.2查询语句

3.3索引设计

3.4Mysql服务（安装、配置等）

3.5操作系统调优

3.6硬件升级（SSD、更强的CPU、更大的内存）

4.数据架构（读写分离、分库分表等）

 

 

引用：

SQL开发技巧(二)