数据库，索引，锁，隔离级别，事务的一些概念
1.Sqlserver数据库相关
1.1 什么是数据库？
存储和管理数据的仓库。优点是管理和操作方便，安全，性能高，容易持久化保存，容易维护和升级。
1.2 数据库表的一个非空字段有默认值，insert时可否不赋值？
可以，会用默认值。
1.3 主键和唯一约束的异同
都不能重复，但唯一约束可以为null，当然多个null算重复(sqlserver中)，一张表中主键只能有一个，而唯一约束可以有多个。
1.4 唯一索引和唯一约束的异同
效果是一样的，唯一约束也是用唯一索引来实现效果的，但是两者的意义不同，约束是为了限制和规范数据，索引是为了提高性能，重点不一样。
1.5 Select * From (Select * From Table ) t1,必须要有别名，不然会报错。
1.6 如何将多条记录的某个字段按逗号分隔成一个字段？
用到了Sutff和For Xml Path 函数
先说ForXMLPath，可以将查询结果按XML的格式显示
select Name from liuDB.dbo.Product for xml path('')
结果是
<Name>美国加州十日游</Name> <Name>美国德州十日游</Name> <Name>美国洛杉矶德比门票</Name>
来个复杂点的
select ID,Name as ProductName from liuDB.dbo.Product for xml path('Row')
结果是
<Row> <ID>2</ID> <ProductName>美国加州十日游</ProductName> </Row> <Row> <ID>3</ID> <ProductName>美国德州十日游</ProductName> </Row> <Row> <ID>4</ID> <ProductName>美国洛杉矶德比门票</ProductName> </Row>
由此可见，该函数会将列名作为节点名，若查询时指定了列的别名，则用别名作为节点名。for xml path('Row') 里的Row会作为根节点名，如果是''，则没有根节点。
更为灵活的是，如果在列名上加上别的符号，如逗号
select ','+Name from liuDB.dbo.Product for xml path('')
则结果就是
,美国加州十日游,美国德州十日游,美国洛杉矶德比门票
Sutff 将指定字符串从指定位置删除指定长度，用新字符串填充进去。
declare @str1 nvarchar(100) declare @str2 nvarchar(100) set @str1 = 'liu yi' set @str2 ='haha' select STUFF(@str1,4,0,@str2) as newStr
结果
liuhaha yi
解释一下，将字符串liu yi,找到第4个字符的位置，删除0个字符，然后将字符串haha填充进去。
那么这一句 select STUFF((select ','+Name from liuDB.dbo.Product for xml path('')),1,1,'') as newStr ，就很简单了。结果就是
美国加州十日游,美国德州十日游,美国洛杉矶德比门票
1.8 union和union all
都是拼接查询结果，要求项的数量和类型一致。union all可能有重复值，union没有，会合并。
1.9 多表连接方式
内连接 inner join 只返回符合条件的
左连接 left join 返回左表全部信息，并返回匹配的右表内容，不匹配的为null
右连接 right join 与左连接相反
完全连接 full join 全部返回，不匹配的为null
交叉连接 corss join 返回笛卡尔积，左表x右表
1.10 什么是索引？有什么优点和缺点？
1.11 聚集索引和非聚集索引
聚集索引存放的物理顺序和列中的顺序一样。一般设置主键索引就为聚集索引。
查询时用到微软的平衡二叉树算法，即首先把书翻到大概二分之一的位置，如果要找的页码比该页的页码小，就把书向前翻到四分之一处，否则，就把书向后翻到四分之三的地方，依此类推，把书页续分成更小的部分，直至正确的页码。
一个表只能建立一个聚集索引
ID,自增列，日期这种逐渐增长的列表，适合聚集索引。
非聚集索引不重新组织表中的数据，而是对每一行存储索引列值并用一个指针指向数据所在的页面，更像是一个目录。
一个表可以拥有多个非聚集索引，每个非聚集索引会复制一份数据，因此索引越多，占的空间越大。
价格，成本，数量这种更新比较频繁的，适合非聚集索引。
非聚集索引查询其实是先查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据，但有一种情况例外，要查询的数据包含在索引列中，则直接取出，不用再查聚集索引。
主外键都适合索引。
索引对查询效率有提升，但是增删改时，因为要重构索引，所以消耗更大。
1.12 填充因子
填充因子定义：索引中叶级页的数据充满度。它的作用：当系统新建或重建索引时，在每一个索引页上预先留出一部分空间。使得系统在新增索引信息时能够保持索引页不分裂。它的目的是使索引的页分裂最小并对性能微调。
填充因子大的时候,插入或修改记录后重新索引的工作会很大,磁盘IO操作增加,性能必然降低,但其占用空间小.填充因子小的时候,索引文件占用磁盘及内存空间相对要大,但是,系统本身重新索引所需IO操作减少,性能提高,只是多占用一些存储空间. 孰轻孰重要自已决定。
1.13 脏读，不可重复读，幻读，锁，隔离级别
脏读：一个事务读到另外一个事务还没有提交的数据,未提交意味着这些数据可能会回滚，也就是可能最终不会存到数据库中，也就是不存在的数据。
不可重复读：不可重复读指的是在同一事务内，不同的时刻读到的同一批数据可能是不一样的，可能会受到其他事务的影响，比如其他事务改了这批数据并提交了，通常针对数据更新（UPDATE）操作。
幻象读：针对数据插入（INSERT）操作来说的。假设事务A根据条件O查询数据，查出N条，在执行逻辑时，事务B插入了符合条件O的M条数据，当事务A再次根据条件O查询时，发现有N+M条数据，导致前后查询出的数据记录数量不一致。
锁：
按操作来分，有读锁和写锁。
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不互相影响。
写锁（排它锁）：当前写操作还未完成时，阻断其它写锁和读锁。
按对数据操作的粒度来分，主要是行锁和表锁。
表锁：针对某个表加锁，偏向MyISAM存储引擎，开销小，加锁快，无死锁，发生锁冲突的概率最高，并发最低，偏向读。
行锁：针对某个行加锁，偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，发生锁冲突概率低，并发高，适合写。
各种情况分析：
表锁：
1. 会话1用读锁锁了表a，那么会话1可以读表a，不可以写表a（持有锁需释放），不可以读别的表；会话2可以读表a，不可以写表a（阻塞需等待1释放锁），可以读别的表；
2. 会话1用写锁锁了表a，那么会话1可以读表a，可以写表a，不能读别的表；会话2不可以读表a，不可以写表a，可以读别的表；
简易言之，就是读锁会阻塞写，写锁会读写都阻塞。
行锁：
1. 会话1 写锁锁住行a（update），会话1自己可以读到修改后的数据，会话2只能读到修改前的数据，需要会话1释放锁（提交修改），才能读取到更新后的数据；
2. 会话1 锁住了行a，会话2依然可以去修改行b，互不相干。
索引不当的情况下，行锁会升级为表锁：例如sql本来是针对某个索引(No,CardNo之类)去修改这一行的数据，按理说提交前只会锁住这一行，其他会话只要不修改这一行都不会被阻塞，但是如果因为sql的问题，导致检索时索引失效，变成全表扫描，锁行变成了锁表，导致其他会话被阻塞了。这种其实很少见，一旦发生也很隐蔽。
怎么加行锁
mysql：在事务中的select 查询语句最后加上 for update，为where条件指定的索引行加上行锁。
START TRANSACTION; SELECT * FROM student WHERE id = 2 FOR UPDATE;
sqlserver： with（rowlock）, uplock表示采用更新锁（直到提交后别的会话才能更新这条，保证rr级别）并保持到事务完成。
begin tran select * from test1 with(rowlock,updlock) where id='1';
间隙锁（Next-key锁）：
当我们按照范围而不是定值条件去检索数据（范围查找），并请求共享锁/排它锁时，存储引擎会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内，但是实际不存在的记录（称为间隙，例如自增id删了中间几行，当我按id查找大于某个数字的记录时，满足条件但是实际已经被删除的记录就叫间隙），存储引擎也会对这个间隙加锁，这种就是间隙锁。
危害：因为这些间隙都被锁了，所以当别的会话想插入这些间隙的新数据时（例如间隙范围是a>10 and a<100,但是其实a=50这条记录是被删除的，即50是间隙，但依然被锁了，有别的会话想新增一条a=50的记录），此时会被阻塞。
隔离级别
read uncommitted 未提交读，读脏数据
开启两个数据库会话a，b
在a中更新数据，
begin tran update Product set Name = '美国加州13日游' where ID=2 select * from Product
在b中设置隔离级别或用表隔离，可以查到未提交的更新数据，因为虽然a中更新操作使用了排它锁，但是b中uncommitted级别低的的读取操作不申请锁
如果b中有写操作，则会申请排它锁，会出现等待。
set transaction isolation level read uncommitted select * from Product select * from Product with(nolock)
事务隔离级别uncommitted=表隔离nolock，可以读取脏读，不可重复读，幻读的数据。
-------------------------------------------------
read committed 已提交读,是SQL SERVER默认的隔离级别，可以避免读取未提交的数据
上例中如果不设置事务隔离级别，或设置成read committed,则需要等待事务提交或回滚才能有查询结果。因为该隔离级别读操作之前首先申请并获得共享锁。
但是读操作读取完就会立刻释放共享锁，也就是说在事务中两次读取之间如果有更新操作，那么两次读取到的数据不一致，也就是说会读到不可重复读，幻读的数据
a会话开启事务读取表数据
begin tran --update Product set Name = '美国加州5日游' where ID=2 select * from Product select * from Product
此时在b会话中更新表中的数据，回到a会话执行第二次读取，会发现在同一个事务中，两次相同的读取结果不一致，这就是因为读操作完成后立刻释放共享锁，则会话b的写操作，可以执行，当a再次读取时，已经是更新后的数据了。
---------------------------------------------
repeatable read 可重复读
保证在一个事务中的两个读操作之间，其他的事务不能修改当前事务读取的数据，该级别事务获取数据前必须先获得共享锁同时获得的共享锁不立即释放一直保持共享锁至事务完成，所以此隔离级别查询完并提交事务很重要。
a会话中设置事务隔离级别，执行前3句，不包括注释
set tran isolation level repeatable read begin tran --update Product set Name = '美国加州5日游' where ID=2 select * from Product select * from Product commit
b会话中更新表数据，发现需要等待，因为a会话的共享锁一直保持，需要事务提交后才释放，在a会话中执行后2句，发现查询出来的结果没有变。但b会话的更新此时已经提交，再查，已经变了。
因为可重复读的锁会一直保持到事务提交或回滚，但是可以读到，幻读的数据
-------------------------------------------
serializable可序列化
对于前面的repeatable read能保证事务可重复读，但是事务只锁定查询第一次运行时获取的数据资源（数据行），而不能锁定查询结果之外的行，就是原本不存在于数据表中的数据。因此在一个事务中当第一个查询和第二个查询过程之间，有其他事务执行插入操作且插入数据满足第一次查询读取过滤的条件时，那么在第二次查询的结果中就会存在这些新插入的数据，使两次查询结果不一致，这种读操作称之为幻读。
a会话保持上例不变，b会话不是更新，而是往表中 插入新数据。当执行a会话后2条sql时，发现跟之前的查询比多出来新增的一条数据。
修改a会话的隔离级别为serializable，则同样的操作，查出来的数据数量一致，b会话中的插入操作要等待a会话事务提交后才执行完成。
针对脏读，不可重复读，幻读的隔离级别就这四种，还有另外两种，我没用用过，以后再说。
1.14 . 什么叫事务？
事务是数据库上按照一定逻辑顺序执行的任务，是单个的工作单元。
1.15 . 事务的特性？
原子性，一致性，隔离性，持久性。
原子性：事务必须是原子工作单元；对于其数据修改，要么全都执行，要么全都不执行。
一致性：事务在完成时，必须使所有的数据都保持一致状态，即从一个正确的状态到另一个正确的状态。
隔离性：由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。事务识别数据时数据所处的状态，要么是另一并发事务修改它之前的状态，要么是第二个事务修改它之后的状态，事务不会识别中间状态的数据。这称为可串行性，因为它能够重新装载起始数据，并且重播一系列事务，以使数据结束时的状态与原始事务执行的状态相同。
持久性：事务完成之后，它对于系统的影响是永久性的。该修改即使出现系统故障也将一直保持。
浙公网安备 33010602011771号