【程序员的SQL金典】笔记(第6章~第11章)

 

 

第六章 索引与约束

 

1.索引用来提高数据的检索速度，而约束则用来保证数据的完整性。

 

2.创建索引

创建索引的SQL 语句是CREATE INDEX，其语法如下：

CREATE INDEX 索引名ON 表名(字段1, 字段2,……字段n)

其中【索引名】为被创建的索引的名称，这个名称必须是唯一的；【表名】为要创建索引的表；【字段1, 字段2,……字段n】为组成这个索引的字段列表，允许一到多个。

 

3.删除索引

语法

DROP INDEX 表名.索引名

 

4.非空约束

在定义数据表的时候，默认情况下所有字段都是允许为空值的，如果需要禁止字段为空，那么就需要在创建表的时候显示指定。指定一个字段为空的方式就是在字段定义后增加NOT NULL

 

5.唯一约束

①唯一约束又称为UNIQUE 约束，它用于防止一个特定的列中两个记录具有一致的值.

唯一约束分为单字段唯一约束与复合唯一约束两种类型.

如果希望一个字段在表中的值是唯一的，那么就可以将唯一约束设置到这个字段上，设置方式就是在字段定义后增加UNIQUE

eg:单字段唯一约束例子

CREATE TABLE T_Person (FNumber VARCHAR(20) UNIQUE, FName VARCHAR(20),FAge INT)

 

 

②.复合唯一约束:

语法如下：

CONSTRAINT 约束名 UNIQUE(字段1,字段2……字段n)

这里的“字段1,字段2……字段n”为组成约束的多个字段，如果只有一个字段则可以看做是单字段唯一约束定义的另外一种形式。通过这种形式定义的唯一约束由于有一个确定的名称，所以可以很容易的通过这个名字来删除这个约束。(约束的字段全部重复了才会报错,否则是允许的)

eg:

CREATE TABLE T_Person (FNumber VARCHAR(20), FDepartmentNumber VARCHAR(20), FName VARCHAR(20),FAge INT, CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))

 

 

 

③.可以在一个表中添加多个复合唯一约束，只要为它们指定不同的名称即可。

eg:

CREATE TABLE T_Person (FNumber VARCHAR(20), FDepartmentNumber VARCHAR(20), FName VARCHAR(20),FAge INT, CONSTRAINT unic_1 UNIQUE(FNumber,FDepartmentNumber) , CONSTRAINT unic_2 UNIQUE(FDepartmentNumber, FName))

 

④.在已经存在的数据表添加新的约束，语法如下：

ALTER TABLE 表名 ADD CONSTRAINT 唯一约束名 NIQUE(字段1,字段2……字段n)

 

⑤.删除已经创建好的复合唯一约束，语法如下：

ALTER TABLE 表名 DROP CONSTRAINT 唯一约束名

 

6.CHECK约束

①.创建表时在字段定义后添加CHECK 表达式就可以为这个字段添加CHECK约束，几乎所有字段中都可以添加CHECK约束，也就是一张表中可以存在多个CHECK 约束。可以在CHECK 条件中使用任意有效的SQL表达式，CHECK约束对于插入、更新等任何对数据进行变化的操作都进行检查。

eg:

CREATE TABLE T_Person ( FNumber VARCHAR(20) CHECK (LENGTH(FNumber)>12), FName VARCHAR(20), FAge INT CHECK(FAge >0), FWorkYear INT CHECK(FWorkYear>0))

 

②.以上这种方式是直接在列定义中使用CHECK约束,该缺点是不能引用其他列,会报错.如要使用其他列,必须在CREATE TABLe 语句的末尾使用CONSTRAINT 关键字定义它。语法:

CONSTRAINT 约束名 CHECK(约束条件)

eg:

CREATE TABLE T_Person ( FNumber VARCHAR(20), FName VARCHAR(20), FAge INT, FWorkYear INT , CONSTRAINT ck_1 CHECK(FWorkYear< FAge))

 

③在已经存在的数据表中添加CHECK约束,语法:

ALTER TABLE 表名 ADD CONSTRAINT CHECK约束名 CHECK(SQL约束条件)

 

④删除已经存在的CHECK约束,语法:

ALTER TABLE 表名 DROP CONSTRAINT CHECK约束名

 

7.主键约束

①每张表只能有一个主键约束.

在 CREATE TABLE 语句中定义主键约束非常简单,只要在字段定义后添加PRIMARY KEY 关键字即可。

eg:单一字段主键

CREATE TABLE T_Person (FNumber VARCHAR(20) PRIMARY KEY, FName VARCHAR(20),FAge INT)

 

②复合主键(联合主键)

由多个字段来组成主键，这样的主键被称为复合主键或者联合主键。复合主键的定义和复合唯一约束的定义类似

eg:

CREATE TABLE T_Person (FNumber VARCHAR(20), FName VARCHAR(20),FAge INT, CONSTRAINT pk_1 PRIMARY KEY(FNumber,FName))

 

 

③在已经存在的数据表中添加主键约束

前提是该字段必须有非空约束,否则系统会报错

语法:

ALTER TABLE 表名 ADD CONSTRAINT 主键约束名 PRIMARY KEY(字段1,字段2,..字段n)

 

④删除已存在的主键约束

语法:

ALTER TABLE 表名 DROP CONSTRAINT 主键约束名;

 

8.外键约束

①在创建表的时候就添加外键约束，其定义方式和复合主键类似，语法如下：FOREIGN KEY 外键字段 REFERENCES 外键表名(外键表的主键字段)

eg:

CREATE TABLE T_AUTHOR ( FId VARCHAR(20) PRIMARY KEY, FName VARCHAR(100), FAge INT, FEmail VARCHAR(20) ); CREATE TABLE T_Book ( FId VARCHAR(20) PRIMARY KEY, FName VARCHAR(100), FPageCount INT, FAuthorId VARCHAR(20) , FOREIGN KEY (FAuthorId) REFERENCES T_AUTHOR(FId) );

 

②在已经存在的表上添加外键约束

语法:

ALTER TABLE 表名1

ADD CONSTRAINT 外键约束名

FOREIGN KEY (约束字段1) REFERENCES 表名2(约束字段2)

 

 

 

 

第七章 表连接

1.JOIN

JOIN 关键字来使用表连接。表连接有多种不同的类型，被主流数据库系统支持的有交叉连接（CROSS JOIN）、内连接（INNER JOIN）、外连接（OUTTER JOIN），另外在有的数据库系统中还支持联合连接（UNION JOIN）

 

2.内连接(INNER JOIN)

内连接组合两张表，并且基于两张表中的关联关系来连接它们。使用内连接需要指定表中哪些字段组成关联关系，并且需要指定基于什么条件进行连接。内连接的语法如下：

INNER JOIN table_name

ON condition

其中table_name 为被关联的表名，condition则为进行连接时的条件。

eg:

SELECT FNumber,FPrice FROM T_Order INNER JOIN T_Customer ON FCustomerId= T_Customer.FId WHERE T_Customer.FName='TOM'

"INNER JOIN"的INNER可写可不写

 

3.不等值连接

等值连接是最常用的连接，因为它指定的连接条件是一个表中的一个字段必须等于另一个表中的一个字段。处理等值连接，还存在另外一种不等值连接，也就是在连接的条件中可以使用小于（<）、大于（>）、不等于（<>）等运算符，而且还可以使用LIKE、BETWEEN AND等运算符，甚至还可以使用函数。

eg:

SELECT T_Order.FNumber,T_Order.FPrice, T_Customer.FName,T_Customer.FAge FROM T_Order INNER JOIN T_Customer ON T_Order.FPrice< T_Customer.FAge*5 and T_Order.FCustomerId=T_Customer.FId

 

 

4.交叉连接

①.与内连接比起来，交叉连接非常简单，因为它不存在ON子句。交叉连接会将涉及到的所有表中的所有记录都包含在结果集中。可以采用两种方式来定义交叉连接，分别是隐式的和显式的。

 

②.隐式的连接只要在SELECT语句的FROM语句后将要进行交叉连接的表名列出即可.

eg:

SELECT c.FId, c.FName, c.FAge, o.FId, o.FNumber, o.FPrice FROM T_Customer c, T_Order o

 

 

③.交叉连接的显式定义方式为使用CROSS JOIN关键字，其语法与INNER JOIN类似.

eg:

SELECT T_Customer.FId, T_Customer.FName, T_Customer.FAge, T_Order.FId, T_Order.FNumber, T_Order.FPrice FROM T_Customer CROSS JOIN T_Order

 

 

5.自连接

到目前为止，我们讲解的连接都是在不同的数据表之间进行的，其实参与连接的表完全可以是同一样表，也就是表与其自身相连接，这样连接就被称为自连接。自连接并不是独立于交叉连接、内连接、外连接等这些连接方式之外的另外一种连接方式，而只是这些连接方式的一种特例，也就是交叉连接、内连接、外连接等连接方式中只要参与连接的表是同一张表，那么它们就可以被称为自连接。

eg:连接同一张表必须使用别名,否则会报错

SELECT o1.FNumber,o1.FPrice,o1.FTypeId, o2.FNumber,o2.FPrice,o2.FTypeId FROM T_Order o1 INNER JOIN T_Order o2 ON o1.FTypeId=o2.FTypeId and o1.FId<o2.FId

 

6.外部连接

外部连接主要就是用来解决这种空值匹配问题的。

外部连接分为三种类型：右外部连接（RIGHT OUTER JOIN）、左外部连接（LEFT OUTER JOIN）和全外部连接（FULLOUTER JOIN）。

 

左外部连接还返回左表中不符合连接条件的数据；

左外部连接还返回右表中不符合连接条件的数据；

全外部连接还返回左表中不符合连接条件的数据以及右表中不符合连接条件的数据，它其实是左外部连接和右外部连接的合集。

这里的左表和右表是相对于JOIN关键字来说的，位于JOIN关键字左侧的表即被称为左表，而位于JOIN关键字右侧的表即被称为右表。

 

①左外部连接

在左外部连接中，左表中所有的记录都会被放到结果集中，无论是否在右表中存在匹配记录。

eg:

SELECT o.FNumber,o.FPrice,o.FCustomerId, c.FName,c.FAge FROM T_Order o LEFT OUTER JOIN T_Customer c ON o.FCustomerId=c.FId WHERE o.FPrice>=150

 

 

②右外部连接

与左外部连接正好相反，在右外部连接中不管是否成功匹配连接条件都会返回右表中的所有记录。

eg:

SELECT o.FNumber,o.FPrice,o.FCustomerId, c.FName,c.FAge FROM T_Order o RIGHT OUTER JOIN T_Customer c ON o.FCustomerId=c.FId

 

③全外部连接

即使在右表中不存在匹配连接条件的数据，左表中的所有记录也将被放到结果集中，同样即使在左表中不存在匹配连接条件的数据，右表中的所有记录也将被放到结果集中。

eg:

SELECT o.FNumber,o.FPrice,o.FCustomerId, c.FName,c.FAge FROM T_Order o FULL OUTER JOIN T_Customer c ON o.FCustomerId=c.FId

 

 

 

 

 

第八章 子查询

1.单值子查询

单值子查询的语法和普通的SELECT 语句没有什么不同，唯一的限制就是子查询的返回值必须只有一行记录，而且只能有一个列。这样的子查询又被称为标量子查询，标量子查询可以用在SELECT语句的列表中、表达式中、WHERE 语句中等很多场合。

如果一个子查询返回值不止一行记录或者有多个列的话都不能当作标量子查询使用，否则会出错。

eg:

SELECT 1 AS f1,2,(SELECT MIN(FYearPublished) FROM T_Book),(SELECT MAX(FYearPublished) FROM T_Book) AS f4

 

2.列值子查询

与标量子查询不同，列值子查询可以返回一个多行多列的结果集。这样的子查询又被称为表子查询，表子查询可以看作一个临时的表，表子查询可以用在SELECT 语句的FROM子句中、INSERT语句、连接、IN 子句等很多场合。

eg:

SELECT T_Reader.FName,t2.FYear,t2.FName ,t2.F3 FROM T_Reader, (SELECT FYearPublished AS FYear,FName,1+2 as F3 FROM T_Book WHERE FYearPublished < 1800) t2

 

3.SELECT列表中的标量子查询

eg:

SELECT FId,FName, ( SELECT MAX(FYearPublished) FROM T_Book WHERE T_Book. FCategoryId= T_Category.FId ) FROM T_Category

 

 

 

子查询中引用了外部查询中的字段(所以不能单独执行)。这种引用了外部查询中字段的子查询被称为相关子查询。

 

4.WHERE子句的标量子查询

标量子查询不仅可以用在SELECT 语句的列表中，它还可以用在WHERE 子句中，而且实际应用中子查询很多的时候都是用在WHERE子句中的。

eg:

SELECT T_Category.FId, T_Book. FName,T_Book.FYearPublished FROM T_Category INNER JOIN T_Book ON T_Category.FId=T_Book.FCategoryId WHERE T_Book.FYearPublished= ( SELECT MIN(T_Book.FYearPublished) FROM T_Book WHERE T_Book.FCategoryId=T_Category.FId )

 

5.集合运算符与子查询

①IN运算符

eg:

SELECT * FROM T_Reader WHERE FYearOfJoin IN ( select FYearPublished FROM T_Book )

 

②ANY和SOME运算符

SOME的用法和功能和ANY一模一样。ANY必须和其他的比较运算符共同使用，而且必须将比较运算符放在ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是ANY在英文中所表示的意义。

注意，和IN 运算符不同，ANY 运算符不能与固定的集合相匹配(即IN(220,110,330);在中取值)

eg:

SELECT * FROM T_Book WHERE FYearPublished <ANY ( SELECT FYearOfBirth FROM T_Reader )

 

③ALL运算符

ALL在英文中的意思是“所有”，ALL运算符要求比较的值需要匹配子查询中的所有值。ALL运算符同样不能单独使用，必须和比较运算符共同使用。

ALL 运算符同样不能与固定的集合相匹配.

另外需要注意的就是，当使用ALL 运算符的时候，如果带匹配的集合为空，也就是子查询没有返回任何数据的时候，不论与什么比较运算符搭配使用ALL的返回值将永远是true。

eg:

SELECT * FROM T_Book WHERE FYearPublished <ALL ( SELECT FYearOfJoin FROM T_Reader WHERE FProvince = 'JiangSu' )

 

④EXISTS运算符

EXISTS运算符是单目运算符，它不与列匹配，因此它也不要求待匹配的集合是单列的。EXISTS运算符用来检查每一行是否匹配子查询，可以认为EXISTS就是用来测试子查询的结果是否为空，如果结果集为空则匹配结果为false，否则匹配结果为true。

eg:

SELECT * FROM T_Category WHERE EXISTS ( SELECT * FROM T_Book WHERE T_Book. FCategoryId = T_Category.FId AND T_Book. FYearPublished<1950 )

 

 

6.在其他类型SQL语句中的子查询应用

①子查询在INSERT语句中的应用

eg:

INSERT INTO T_ReaderFavorite2(FCategoryId,FReaderId) SELECT FCategoryId, (CASE WHEN FReaderId<=10 THEN FReaderId ELSE FReaderId- FCategoryId END ) FROM T_ReaderFavorite

 

②子查询在UPDATE 语句中的应用

eg:

UPDATE T_Book b1 SET b1.FYearPublished=2005 WHERE ( SELECT COUNT(*) FROM T_Book b2 WHERE b1. FCategoryId=b2. FCategoryId )>3

 

③子查询在DELETE 语句中的应用

eg:所有同类书本书超过3 本的图书删除

DELETE FROM T_Book b1 WHERE ( SELECT COUNT(*) FROM T_Book b2 WHERE b1. FCategoryId=b2. FCategoryId )>3

 

 

 

 

第九章 主流数据库的SQL语法差异性以及解决方案(P219~)

有空再看吧,关于数据库之间差异的

 

 

 

 

第十章 高级话题

1.SQL注入漏洞攻防

①过滤敏感字符

过滤敏感字符的思路非常简单，由于恶意攻击者一般需要在输入框中输入的文本一般含有or、and、select、delete之类的字符串片段，所以在拼接SQL之前检查用户提交的文本中是否含有这些敏感字符串，如果含有则终止操作。

示例实现代码:(有很大缺陷)

string user=txtUser.getText(); string password = txtPassword.getText(); //校验是否含有敏感字符 if(user.contains("or","and","select","delete")) { ShowMessage("可能存在注入漏洞攻击！"); return; } if(password.contains("or","and","select","delete")) { ShowMessage("可能存在注入漏洞攻击！"); return; } rs = ExuecuteQuery("SELECT (FPassword='"+password+"') AS PwdCorrect FROM T_User WHERE FUser='"+password+"'"); if(rs.getBool("PwdCorrect ")==true) { ShowMessage("密码正确"); } else { ShowMessage("密码错误"); }

 

②使用参数化SQL

Java、C#等语言提供了参数化SQL机制，使用参数化SQL开发人员为在运行时才能确定的参数值设置占位符，在执行的时候再指定这些占位符所代表的值。

示例实现代码:

string user=txtUser.getText(); string password = txtPassword.getText(); query = CreateQuery("SELECT (FPassword=:password) AS PwdCorrect FROM T_User WHERE FUser=:user"); query.SetParameter(":password ",password); query.SetParameter(":user", user); if(rs.getBool("PwdCorrect ")==true) { ShowMessage("密码正确"); } else { ShowMessage("密码错误"); }

在执行时Java、C#会直接将参数化SQL以及对应的参数值传递给DBMS，在DBMS中会将参数值当成一个普通的值来处理而不是将它们拼接到参数化SQL中，因此从根本上避免了SQL注入漏洞攻击。

 

2.SQL调优

在使用DBMS 时经常对系统的性能有非常高的要求：不能占用过多的系统内存和CPU 资源、要尽可能快的完成的数据库操作、要有尽可能高的系统吞吐量。如果系统开发出来不能满足要求的所有性能指标，则必须对系统进行调整，这个工作被称为调优。

 

①SQL调优的基本原则

“二八原理”是一个普遍的真理，特别是在计算机的世界中表现的更加明显，那就是20%的代码的资源消耗占用了80%的总资源消耗。SQL 语句也是一种代码，因此它也符合这个原理。在进行SQL 调优的时候应该把主要精力放到这20%的最消耗系统资源的SQL语句中，不要想把所有的SQL语句都调整到最优状态。

 

②索引

索引是数据库调优的最根本的优化方法，很多优化手法都是围绕索引展开的，可以说索引是一切优化手法的“内功”，而所有的优化手法都是由索引衍化出来的招式而已。

根据索引的顺序与数据表的物理顺序是否相同，可以把索引分成两种类型：聚簇索引，数据表的物理顺序与索引顺序相同；非聚簇索引，数据表的物理顺序与索引顺序不相同

当创建聚簇索引时要需要每一个表只能有一个聚簇索引，因为表中数据的

物理顺序只能有一个，而非聚集索引则可以创建多个。

(索引占据一定的存储空间,降低插入,更新和删除的速度,还会造成存储碎片问题.所以创建索引时要考虑其必要性)

 

③表扫描和索引查找

一般地，系统访问数据库中的数据，可以使用两种方法：全表扫描和索引查找。

 

3.优化手法

①创建必要的索引

 

②使用预编译查询

程序中通常是根据用户的输入来动态执行SQL 语句，这时应该尽量使用参数化SQL，这样不仅可以避免SQL注入漏洞攻击，最重要数据库会对这些参数化SQL执行预编译，这样第一次执行的时候DBMS会为这个SQL语句进行查询优化并且执行预编译，这样以后再执行这个SQL 的时候就直接使用预编译的结果，这样可以大大提高执行的速度

 

③调整WHERE 子句中的连接顺序

DBMS 一般采用自下而上的顺序解析WHERE 子句，根据这个原理,表连接最好写在其他WHERE条件之前，那些可以过滤掉最大数量记录。

 

④SELECT语句中避免使用*

即使确实需要检索所有列，也不要使用SELECT *，因为这是一个非常低效的方法.DBMS在解析的过程中，会将*依次转换成所有的列名，这意味着将耗费更多的时间。

 

⑤尽量将多条SQL语句压缩到一句SQL中

每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。

 

⑥用Where子句替换HAVING 子句

避免使用HAVING 子句，因为HAVING 只会在检索出所有记录之后才对结果集进行过滤。如果能通过WHERE 子句限制记录的数目，那就能减少这方面的开销。HAVING 中的条件一般用于聚合函数的过滤，除此而外，应该将条件写在WHERE 子句中。

 

⑦使用表的别名

当在 SQL 语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减少那些由列名歧义引起的语法错误。

 

⑧用EXISTS替代IN

在查询中，为了满足一个条件，往往需要对另一个表进行联接，在这种情况下，使用EXISTS而不是IN 通常将提高查询的效率，因为IN 子句将执行一个子查询内部的排序和合并。

 

⑨用表连接替换EXISTS

通常来说，表连接的方式比EXISTS 更有效率，因此如果可能的话尽量使用表连接替换EXISTS。

 

⑩避免在索引列上使用计算

在 WHERE 子句中，如果索引列是计算或者函数的一部分，DBMS 的优化器将不会使用索引而使用全表扫描。

例如下面的SQL语句用于检索月薪的12倍大于两万五千元的员工：

SELECT *FROM T_Employee WHERE FSalary * 12 >25000;

由于在大于号左边的是FSalary与12 的成绩表达式，这样DBMS 的优化器将不会使用字段FSalary的索引，因为DBMS必须对T_Employee表进行全表扫描，从而计算FSalary * 12 的值，然后与25000 进行比较。将上面的SQL语句修改为下面的等价写法后DBMS将会使用索引查找，从而大大提高了效率：

SELECT *FROM T_Employee WHERE FSalary >25000/12;

 

 

①①用UNION ALL 替换UNION

当 SQL 语句需要UNION 两个查询结果集合时，即使检索结果中不会有重复的记录，如果使用UNION这两个结果集同样会尝试进行合并，然后在输出最终结果前进行排序。

因此，如果检索结果中不会有重复的记录的话，应该用UNION ALL替代UNION，这样效率就会因此得到提高

 

①②避免隐式类型转换造成的全表扫描

 

①③防止检索范围过宽

如果DBMS 优化器认为检索范围过宽，那么它将放弃索引查找而使用全表扫描。下面是几种可能造成检索范围过宽的情况：

使用IS NOT NULL或者不等于判断，可能造成优化器假设匹配的记录数太多。使用LIKE 运算符的时候，"a%"将会使用索引，而"a%c"和"%c"则会使用全表扫描，因此"a%c"和"%c"不能被有效的评估匹配的数量。

 

4.事务

①.事务的定义:事务是形成一个逻辑工作单位的数据库操作的汇集

事务(Transaction)是能以整体的原子操作形式完成的一系列操作，事务能保证一个“全有或者全无”的命题的成立，即操作或者全部成功或者全部失败，不会出现部分成功、部分失败的情况。事务以一种可靠的、简洁的方式来解决系统运行中的各种异常问题。

 

②事务的特性:

事务具有4 个基本特性，即原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)和持久性(Durability)。简称ACID 特性。

●原子性：一个事务中所有的数据库操作，是一个不可分割的整体，这些操作要么全部执行，要么全部无效果。

●一致性：一个事务独立执行的结果，将保持数据库的一致性，即数据不会因事务的执行而被破坏。在事务执行过程中，可以违反一致性原则，并产生一个临时的不一致状态。比如在转账过程中，会出现暂时的数据不一致的情况。当事务结束后，系统又回到一致的状态。不过临时的一致性不会导致问题，因为原子性会使得系统始终保持一致性。

●隔离性：在多个事务并发执行的时候，系统应该保证与这些事务先后单独执行时的结果一样，即并发执行的任务不必关心其他事务。对于每一个事务来讲，那一刻看起来好像只有它在修改数据库一样。事务系统是通过对后台数据库数据使用同步协议来实现隔离性的。同步协议使一个事务与另外一个事务相分离。如果事务对数据进行了锁定，可以使并发的事务无法影响该数据，直到锁定解除为止。

●持久性：一个事务一旦完成全部操作以后，它对数据库的所有操作将永久地反映在数据库中。持久性保证了系统在操作的时候免遭破坏。持久性主要是为了解决机器故障、突然断电、硬盘损坏等问题而出现的。为了达到持久性，系统一般都保留了一份日志。一旦出现故障，就可以通过日志将数据重建。

 

③事务的隔离

数据库加锁，以防止多个组件读取数据，通过锁住事务所用的数据，能保证在打开锁之前，只有本事务才能访问数据。这样就避免了交叉存取的问题。

由于锁将其他并发的事务排除在数据库更新之外，所以这会导致性能的严重下降。为了提高性能，事务将锁分为两种类型：只读锁和写入锁。只读锁是非独占的，多个并发的事务都能获得只读锁；写入锁是独占的，任意时间只能有一个事务可以获得写入锁。

 

④事务的隔离级别

事务的隔离级别分为四种：READ_UNCOMMITED 、READ_COMMITED 、REPEATABLE_READ、SERIALIZABLE。要理解这些隔离级别的差异必须首先弄清如下几个概念：脏读、不可重复读、幻影读取。

 

脏读:读取了未提交的数据的方法叫脏读问题.即A事务在提交过程中回滚了,但还没回滚前数据被B读取了.这就叫脏读.

 

不可重复读:当一个用户从数据库中读取数据的时候，另外一个用户修改了这条数据，所以数据发生了改变，当再次读取的时候就出现了不可重复读取问题

 

幻影读取:幻影读取指的是在两次数据库操作读取操作之间，一组新的数据会出现在数据库中。(如A从数据检索到了一些数据.B通过INTSERT语句插入了一些新的数据,A再次查询的时候,新的数据就会出现)

 

使用READ_UNCOMMITED 级别，会导致脏读问题、幻影读取问题和不可重复读取问题。在需要敏感计算任务的事务中，这样的模式是不太适合的；

使用READ_COMMITED 级别，可以解决脏读问题，但是还会有幻影读取问题和不可重复读取问题。这种级别一般用于制作报表。这种模式是大部分系统的默认级别；

使用REPEATABLE_READ 级别，可以解决脏读问题和不可重复读取问题，但是会有幻影读取问题；

使用SERIALIZABLE 级别可以解决脏读问题、幻影读取问题和不可重复读取问题。这是最严格级别的隔离级别；

 

⑤事务的使用

⑴自动增长字段

MSSQLServer 中设定一个字段为自动增长字段非只要在表定义中指定字段为IDENTITY 即可，格式为IDENTITY(startvalue,step)，其中的startvalue参数值为起始数字，step参数值为步长，即每次自动增长时增加的值。

eg:SQL语句创建T_Person表，其中主键FId为自动增长字段，并且设定100 为起始数字，步长为3：

CREATE TABLE T_Person ( FId INT PRIMARY KEY IDENTITY(100,3), FName VARCHAR(20), FAge INT );

 

⑵使用SEQUENCE对象

??百度

 

5.业务主键与逻辑主键

业务主键是指采用业务数据中的某个字段做为主键

逻辑主键，也称代理逐渐，是指不采用任何业务数据做为主键，而是采用一个没有业务意义的不重复值做主键

逻辑主键常用的生成策略有:自动增长字段和UUID

使用自动增长字段的优势在于速度比较快，根据统计UUID 算法要比自动增长字段的生成速度慢约30 倍；使用自动增长字段的劣势在于进行表合并的时候会存在冲突的问题,而UUID不会

 

6.NULL的学问

①NULL并不代表没有值而是表示值未知

 

②IS NULL运算符可以检索到<NULL>的数据

eg:

SELECT * FROM T_Employee WHERE FSalary<5000 OR FSalary IS NULL

 

 

③NULL和计算字段

如果NULL 值出现在任何计算字段中，那么计算结果永远是NULL

 

④NULL和字符串

如果NULL值出现在任何和字符串相关计算字段中，那么计算结果永远是NULL。

 

⑤NULL和函数

如果NULL 值出现在普通函数中，那么计算结果永远是NULL。

 

⑥NULL和聚合函数

和普通的函数不同，如果NULL值出现在聚合函数中，那么NULL值将会被忽略。

 

7.开窗函数

①开窗函数简介

与聚合函数一样，开窗函数也是对行集组进行聚合计算，但是它不像普通聚合函数那样每组只返回一个值，开窗函数可以为每组返回多个值，因为开窗函数所执行聚合计算的行集组是窗口。

 

②开窗函数的调用格式为：

函数名(列) OVER(选项)

OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用OVER关键字来区分这两种用法。

OVER关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果OVER关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。

eg:

SELECT FCITY , FAGE , COUNT(*) OVER() FROM T_Person WHERE FSALARY<5000

 

 

③PARTITION BY 子句

开窗函数的OVER关键字后括号中的可以使用PARTITION BY 子句来定义行的分区来供进行聚合计算。与GROUP BY 子句不同，PARTITION BY 子句创建的分区是独立于结果集的，创建的分区只是供进行聚合计算的，而且不同的开窗函数所创建的分区也不互相影响。还有,同一个SELECT语句中可以同时使用多个开窗函数.互不干扰.

eg:显示每一个人员的信息以及所属城市的人员数.COUNT(*) OVER(PARTITION BY FCITY)表示对结果集按照FCITY进行分区，并且计算当前行所属的组的聚合计算结果。

SELECT FName,FCITY , FAGE , FSalary, COUNT(*) OVER(PARTITION BY FCITY) FROM T_Person

 

④高级开窗函数

⑴.ROW_NUMBER(),RANK()和DENSE_RANK()

RANK()和DENSE_RANK()函数都可以用于计算一行的排名，不过对于并列排名的处理方式不同；ROW_NUMBER()函数计算一行在结果集中的行号，同样可以将其当成排名函数。

eg:MMS SQL 不支持ORDER BY

SELECT FName, FSalary,FAge, RANK() OVER(ORDER BY FAGE), DENSE_RANK() OVER(ORDER BY FAGE), ROW_NUMBER() OVER(ORDER BY FAGE) FROM T_Person;

 

⑵RATIO_TO_REPORT()

RATIO_TO_REPORT()函数用于计算当前行的某个列的值在当前窗口中所占的百分比，它等价于colname/SUM(colname)。

SELECT FName, FSalary,FAge, RATIO_TO_REPORT(FSalary) OVER(), FSalary/SUM(FSalary) OVER(), RATIO_TO_REPORT(FSalary) OVER(PARTITION BY FAge) FROM T_Person;

 

⑶NTILE()

NTILE()函数用来将窗口中的行按照某个列的值进行区域的平均分割，然后返回当前行所在的区域编号。NTILE()函数接受一个整数类型的值，这个值表示把结果集分割成的份数，注意必须在NTILE()函数后的OVER()子句中显式的使用ORDER BY关键字来指定排序规则。

eg:

SELECT FName, FSalary,FAge, NTILE(3) OVER(ORDER BY FSalary) FROM T_Person;

 

⑤WITH子句与子查询

，SQL提供了WITH子句用于为子查询定义一个别名，这样就可以通过这个别名来引用这个子查询了，也就是实现“一次定义多次使用”。

格式:

WITH 别名AS

(

    子查询

)

还可以在WITH语句中为子查询中的列定义别名，定义的方式就是在子查询别名后列出参数名列表。

eg:

WITH person_tom(F1,F2,F3) AS ( SELECT FAge,FName,FSalary FROM T_Person WHERE FName='TOM' ) SELECT * FROM T_Person WHERE FAge=person_tom.F1 OR FSalary=person_tom.F3

 

 

 

 

 

第十一章 案例讲解(P272~完)

1.案例数据库视图

 

2.报表

②数据库中的数据是以关系表的形式保存的，非技术人员很难看懂这些表中数据的意思，必须将其转换为业务人员可以看得懂的形式，这就是报表(用自己的话说就是select检索出来的表格)

 

②COALESCE()

COALESCE()函数支持多个参数，该函数返回参数中的第一个非空值，这样COALESCE(f1,f2)就可以实现“如果f1为空则将f2做为返回值”这样的空值处理逻辑了。

eg:

SELECT salebill.FNumber, COALESCE(person.FName,'没有开单人'), salebill.FMakeDate FROM T_SaleBill salebill LEFT OUTER JOIN T_Person person ON salebill.FBillMakerId=person.FId;