数据库常见知识

1、事务：（Transaction） 指作为单个逻辑工作单元执行的一系列操作，而这些逻辑工作单元需要具有原子性，  一致性，隔离性和持久性四个属性，统称为ACID特性。

事务的特性ACID：

原子性（atomicity）事务中的所有元素作为一个整体提交或回滚，事务的个元素是不可分的，事务是一个完整操作。

一致性（consistency）事物完成时，数据必须是一致的，也就是说，和事物开始之前，数据存储中的数据处于一致状态。保证数据的无损。

隔离性（isolation）对数据进行修改的多个事务是彼此隔离的。这表明事务必须是独立的，不应该以任何方式以来于或影响其他事务。

持久性（durability）事务完成之后，它对于系统的影响是永久的，该修改即使出现系统故障也将一直保留，真实的修改了数据库

在SQL语言中,定义事务的语句有三条： 

BEGIN TRANSACTION 开始

COMMIT 

ROLLBACK回滚

2、视图：视图是一张虚拟表，视图的字段是由我们自定义的，视图只供查询，数据不可更改，查询数据来源于我们建立的实体表。数据库只存放视图的定义，而不存放视图对应的数据，这些数据仍存放在原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了

优势：（1）视图可以将多个复杂关联表，提取出我们需要的信息，优化查询速度。（2）有了视图机制，就可以在设计数据库应用系统时，对不同的用户定义不同的视图，使机密数据不出现在不应该看到这些数据的用户视图上。这样视图机制就自动提供了对机密数据的安全保护功能。

Create view IS_Student

AS

select Sno,Sname,Sage

from Student

where Sdept='IS'

with check option;

WITH check OPTION 表示对视图进行UPDATE、INSERT、delete操作时要保证更新、删除、或插入的行满足视图定义中的谓词条件。

3、存储过程:可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），

 优势 (1).存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。

  (2).当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。

  (3).存储过程可以重复使用,可减少数据库开发人员的工作量。

  (4).安全性高,可设定只有某此用户才具有对指定存储过程的使用权。防止sql注入。

缺点：1.如果更改范围大到需要对输入存储过程的参数进行更改，或者要更改由其返回的数据，则您仍需要更新程序集中的代码以添加参数、更新 GetValue() 调用，等等，这时候估计比较繁琐了。 2.可移植性差 

存储过程和函数的区别：1.一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。

2.对于存储过程来说可以返回参数(output)，而函数只能返回值或者表对象。

3.存储过程一般是作为一个独立的部分来执行，而函数可作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。

create proc 存储过程名

　　@参数1        数据类型 [=默认值] [output],

　　@参数2        数据类型 [=默认值] [output],      --加output表示该参数是需要在存储过程中赋值并返回的

　　as

　　SQL语句

例 ：create proc usp_StudentByGenderAge

　　@gender nvarchar(10) [='男'],

　　@age int [=30]

　　as

　　select * from MyStudent where FGender=@gender and FAge=@age

调用exec usp_StudentByGenderAge

系统存储过程一般以sp开头，用户自定义的存储过程一般以usp开头

4、索引

索引的分类：索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

创建索引CREATE INDEX indexName ON mytable(username(length)); 

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age); 

唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

5、游标：我们用SELECT这些查询语句时,都是针对的一行记录而言，如果要在查询分析器中对多行记录(即记录集)进行读取操作时,则需要使用到游标或WHILE等循环

游标使用顺序：  1、定义游标  2、打开游标  3、使用游标   4、关闭游标   5、释放游标

游标的使用：declare 游标名 cursor [LOCAL | GLOBAL][FORWARD_ONLY | SCROLL][STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS]  

    for select语句   [for  update[of 列名[，列名]]

注：LOCAL 局部游标，GLOBAL 全局游标， FORWARD_ONLY 仅向前，SCROLL 滚动，STATIC 静态，KEYSET 键集，DYNAMIC 动态，

READ_ONLY 只读 SCROLL_LOCKS 锁定游标当前行

获取游标的数据    FETCH [[NEXT | PRIOR | FIRST | LAST | 

  ABSOLUTE{ n | @nvar | RELATIVE { n | @nvar}]

 From ] 游标名 [into 变量]

  注： NEXT  下一行  PRIOR  上一行  FIRST 第一行，LAST  最后一行  ABSOLUTE n 第n行，RELATIVE n 当前位置开始的第n行，  into 变量 把当前行的各字段值赋值给变量

游标状态变量:@@fetch_status  游标状态，0 成功  -1 失败  -2 丢失； @@cursor_rows 游标中结果集中的行数，n 行数 -1 游标是动态的  0 空集游标

操作游标的当前行: current of 游标名

6、触发器：一种特殊类型的存储过程，它不同于存储过程。触发器主要是通过事件进行触发被自动调用执行的。而存储过程可以通过存储过程的名称被调用。

 触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。分为DDL(数据库定义)触发器，DML(数据库操纵)触发器

DDL触发器它们会影响多种数据定义语言语句而激发，这些语句有create、alter、drop语句。

  DML触发器分为：

    1、 after触发器（之后触发）

        a、 insert触发器

        b、 update触发器

        c、 delete触发器

     2、 instead of 触发器 （之前触发）

 其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发，且只能定义在表上。而instead of触发器表示并不执行其定义的操作（insert、update、delete）而仅是执行触发器本身。既可以在表上定义instead of触发器，也可以在视图上定义。

 创建触发器：语法

create trigger trg_name on table_name

with encrypion –-加密触发器

for update...

as

      Transact-SQL

7、范式：

第一范式（1NF）：属性不可分。 强调的是列的原子性，即列不能够再分成其他几列，也不能有重复的列。

第二范式（2NF）：满足1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。 

第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。

第三范式（3NF）：满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。属性不依赖于其它非主属性 [ 消除传递依赖 ]