Sql必知必会读书笔记

SQL的基本规则

1. 不同的DBMS对于SQL的拓展不同，常规方法不行时请注意查找对应DBMS文档

例如：查找前五行数据：

Sql server：  Select Top 5

Oracle：Select * from Table wehre Rownum=5

Mysql: Select * from Table Limit 5 Offset 5(指示的是从第五行起，返回五行数据)

1. 一般单行注释都是两个--添加在行头就注释了一行，多行注释/* */
2. 排序时默认的是ASC排序，其中，数据库操作系统（DBMS）在字典（dictionary）是不区分大小写的（A,a） 如果含有大量需要排序的英文（A,a）通过Order by是行不通的
3. 数据库过滤和应用过滤

数据也是可以在应用层过滤的。为此可以在客户端使用SQL语句查询到对应的数据，然后在客户端进行循环遍历查找到所需的数据。

但是这种做法是极其不好的。优化数据库可以快速有效地对数据进行过滤。而让客户端应用（开发语言）处理数据库的工作将会极大地影响应用的性能，并且使创建的应用完全不具备可伸缩性。此外如果在客户端过滤数据，服务器不得不通过网络发送多余的数据，这就极大地浪费了带宽和资源，直接降低了应用的执行速度

 

1. 空值检查

NULL（no value）它与字段包含0，空字符串或仅仅包含空格不同

确定null不能通过检查是否=null，select有一个特殊的语句进行检查空值语法如下：select * from  where 字段 IS NULL

1. Or和and共同使用的时候and的执行顺序要大于or，对于or的条件一般添加括号提升等级
2. In操作符相当于Or但是In有如下优点：

1. 在很多合法选项中直观
2. And or操作符组合时in语句更清楚直观
3. IN操作符比or执行要快
4. IN最大的优点是可以包含在其他的select语句中能够动态的创建where子句

1. 常用的通配符：like %, _ ,[]
2. 使用通配符的技巧

1. 不要过度使用通配符，如果使用其他的操作符能达到目的的，使用其他操作
2. 在确实需要用通配符时，尽量不要把搜索模式中放置通配符，否则搜索会很慢
3. 注意通配符的位

在使用like %$$的时候注意在字段的末尾填充空格，会影响查询结果。

因为使用Nvarchar的类型定义的字段在位数不是定义的长度的时候会自动在后面进行空格的填充。

1. 数据库优化之计算字段和表中列字段

客户端与服务器的格式

在Sql语句内可完成的许多转换和格式化工作都可以直接在客户端应用程序内完成，但是一般来说说，在数据库服务器上完成这些操作要比在客户端完成要快的多的多

1. 大多数DBMS都支持的数据处理函数：

1. 用于处理文本字符串(如删除或填充值，转换值为大写或小写)的文本函数
2. 用于数值数据上进行算数操作（如返回绝对值，进行代数运算）的数值函数
3. 用于处理日期和时间值并从这些值中提起特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数
4. 返回DBMS正在使用的特殊信息（如返回用户登录信息）的系统函数

1. 聚合函数

1. Count中如果使用的是具体的列名，则不会包括空值行的进行计数。

（*）星号型时会把NULL行进行计数

1. Max()函数一般用来查找出最大的数值或日期值，但是很多的DBMS用它来返回任意列中的最大值，包括文本列中最大值，在用于文本数据时，Max()返回按列排序后的最后一行。Max（）函数忽略列值为Null的行。
2. Min()函数一般用来找出最小的数值或者日期值，但许多DBMS允许将用来返回任意列中的最小值，包括文本中的最小值。在用于文本数据时，MIN()返回该排序后最前面的行。
3. Distinct的注意事项

如果指定列名，则Distinct只能用于count().distinct不能用于count(*).

类似的，distinct必须使用列名，不能用于计算或表达式。

分组函数

1. Having和where的区别

Where在数据分组前进行过滤，having在数据分组后进行过滤。

子查询的概念

1. 子查询就是嵌套在其他查询中的查询。子查询总是从内向外处理
2. 子查询的select语句只能查询单个列。

联接表(重点理解部分)

1. 可伸缩性：能适应不断增加的工作量而不失败。设计良好的数据库或应用程序成为伸缩性好。
2. 为什么使用联接

将数据分散为多个表能更好的更有效的存储，更为方便的处理，并且可伸缩性好，但是如何从多个表中获取有用的信息呢，就必须使用联接来解决这一问题。联接是一种机制，能够通过一条Select语句进行关联多张表，进行返回一组想要的数据。

1. 笛卡尔积概念：

有没有联接条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是第一表中的行数乘以第二个表中行数。

1. 联接的分类：

1. 内连接：
2. 外联接：
3. 自连接：
4. 左联接
5. 右练级

1. 联接多张表：

1. 性能考虑：DBMS在运行时关联指定的每个表，以处理联接，这种处理可能非常耗费资源，因此应该注意，不要联接不必要的表。联接的表越多，性能下降的越是厉害。
2. 可联接表的最大数目对于不同DBMS有不同限定，请查看具体DBMS文档。

创建高级联接

1. SQL除可以对列名和计算字段使用别名，还允许给表名起别名。这样的好处：

1. 缩短SQL语句
2. 允许在一条select语句中多次使用相同的表。

1. Oracle中没有AS关键字

Oracle不支持As关键字。要在Oracle中使用别名，可以不用As，简单地指定列名即可。即是Customer C，而不是Customer As C.

1. 使用不同类型的
2. 用自然连接而不是子查询

自联接通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终结果是一样的，但是许多DBMS处理联接的速度要比子查询的检索速度要快的多。

1. 如何使用联接和注意事项

1. 注意使用的联接类型。一般我们使用内联接，但是使用外联接也是有效的。
2. 关于确切的联接语法，应该查看具体的DBMS的文档语法，因为会有所不同。
3. 保证使用正确的联接条件，否则会返回不正确的数据
4. 应该总是提供联接的条件，否则的会返回的是笛卡尔积
5. 在一个联接中可以包含多个表，甚至可以对每个联接采用不同的联接类型，虽然这样做是合法的，一般也很有用，但应该在一起测试他们前面，分别测试单个的联接，这样会使排除故障更为简单。

 

组合查询

1. 组合查询的概念：

多数SQL查询只包含从一个或多个表中返回数据的单个select语句.

但是,SQL也允许执行多个查询(多条select语句),并将结果作为一个查询结果集返回。这些组合查询通常称为并或复合查询。

1. 使用场景：

1. 在一个查询中从不同的表返回结构数据
2. 对一个表执行多个查询，按一个查询返回数据

1. 如何创建组合查询：

使用UNION很简单，所要做的只是给出每条select语句，在各条语句中间放上关键字UNION

1. 使用的注意事项：

1. UNION必须由两条或两条以上的select语句组成，语句之间用关键字UNION分隔
2. UNION中每个查询必须包含相同的列，表达式或聚集函数
3. 列数据类型必须兼容:类型不必完全相同，但必须是DBMS可以隐含转换的类型。
4. UNION自动的去处重复行，如果想要带有重复项的集合可以使用UNION ALL
5. 对查询的集合结果项进行排序只需要在最后一个子句中使用Order by子句进行排序。

插入数据

1. 在某些SQL实现中跟在Insert后面的Into关键字是可选的，但是为了可移植性，还是必须带着！
2. 为了稳定性，不要使用没有明确给出列的Insert语句,在表结构发生变化时带有明确的列的插入语句也是可以使用。
3. 小心使用values,不管使用的那种列名与后面的值是一一对应的关系
4. 插入检索出的数据

Insert一般用来给表插入具有指定列值得列。Insert可以利用select语句把检索出来的数据插入到指定的表中。语法如下：

Insert Into  Customers（

列名,

......,

列名）

 

Select

列名,

........,

列名

From CustNew

把custNew表中的数据插入到customers

Insert  Select

Insert Select中的列名，事实上DBMS一点儿都不关心Select返回的列名。它使用的是列的位置,因此Select中的第一列(不管其列名)将用来填充表列中指定的第一列，第二列将用来填充表列中指定的第二列。

Insert  Select中Select语句可以使用Where进行过滤。

从一个表复制到另一个表(select into)

1. 将一个表复制到另一个表Select  Into
2. 与Insert Select将数据插入到现有已知的表不同，Select Into将数据复制到新表
3. Insert  Select与Select Into最大的区别在于前者是把数据插入到已知的表中，而后者是把表中数据导入到另一张新表。
4. Select Into语法：

Select  *  Into  CustCopy  from  Customers

这条Select语句创建一个名为CustCopy的新表,并把Customers表的整个内容复制到新表中。因为这里使用的 Select * ,所以将在CustCopy表中创建(并填充)与Customers表的每一列相同的列。要是想要复制特定列，可以指定其列名，而不使用*通配符。

 

1. Select Into使用的注意事项：

1. 任何Select选项和子句都可以使用，包括where和Group BY
2. 可利用联接从多张表中插入数据
3. 不管检索出来的数据来自多少张表,数据都只能插入到一个表中。

Update&Delete

1. Truncate&Delete

如果想从表中删除所有行，不要使用Delete。可使用Truncate Table语句，它完成相同的工作，而速度更快(因为不记录数据的变动）。

1. 更新和删除的指导原则

执行不带where的Update子句，则表中每一行的数据都将被更新。类似地，如果执行Delete语句而不带where子句，表中的所有数据都将被删除。

1. 基本准则：

1. 除非打算更新或者删除每一行，否则绝对不要使用不带where子句的update或delete语句。
2. 保证每个表都有主键。
3. 在使用Update或Delete的where子句前，应该先进行Select进行测试，保证过滤数据的正确性。
4. 使用强制实施保证引用完整的数据库
5. 有的DBMS允许数据库管理员施加约束，防止执行不带where子句的update或delete语句。
6. 错误的使用Update或Delete时，可以使用Undo撤销按钮

使用Drop Table进行删除表时

1. 使用Drop删除表时如果表与其他表存在外键关系，必须删除关系后才能进行删除表的操作。

视图(View)

1. Microsoft Access不支持视图，没有与SQL视图一致的工作方式。
2. 使用视图的好处：

1. 简化复杂的SQL操作。在编写查询后，可以方便地重用它而不必知道其基本查询细节。
2. 使用的是表的一部分而不是整个表。
3. 保护数据。可以授予用户访问表的特定部分的权限，而不是整个表的访问权限。
4. 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据
5. 视图是一种查看别处数据的一种设施。视图本身不包含数据，因此返回的数据是从其他表中检索出来的。在原有数据源上进行数据的更改，视图返回的数据将会改变。

视图的性能问题

1. 因为视图不包含数据，所以每次使用视图时，都必须处理查询执行时需要的所有检索。如果你用多个联接和过滤创建了复杂的视图或者嵌套了视图，性能可能会下降的很厉害。因此在部署大量视图时需要进行测试。

视图的规则和限制

1. 与表一样，视图的名字必须是唯一的
2. 对于可以创建的视图数量没有限制
3. 创建视图必须有足够的权限，这些权限一般由数据库管理员授予
4. 应用视图替换和简化复杂的联接
5. 创建视图时，尽可能的创建不绑定特定数据的视图

存储过程

1. 通过把处理封装在一个易用的单元中，可以简化复杂的操作
2. 由于不要求建立一系列处理步骤,因而保证了数据的一致性，如果所有开发人员和应用程序都使用同一存储过程,则所使用的代码都是相同的。
3. 存储过程通常以编译过的形式存储,所以DBMS处理命令所需的工作量很少，提高了性能。

存储过程的缺陷

1. 不同的DBMS中存储过程的语法有所不同,事实上,编写真正的可移植存储过程几乎是不可能的。
2. 一般来说,编写存储过程比编写基本SQL语句复杂,需要更高的技能,更丰富的经验。

 

事务处理

1. 使用事务，通过确保成批的SQL操作要么完全执行,要么不执行,来维护数据库的完整性。
2. 事务中使用到的名词：

1. 事务(Transaction)指一组SQL语句
2. 回退(Rollback) 指的是撤销指定SQL语句的过程
3. 提交(commit)指的是将未储存的SQL语句结果写入数据库表
4. 保留点(savepoint)指事务处理中设置的临时占位符(placeholder),

可以对它发布回退(与回退整个事务处理不同)

1. 事务处理中可以回退哪些操作语句？

事务处理用来管理Insert,Update和delete语句。不能回退Select语句（回退Select语句也没必要），也不能回退Create或Drop操作。

事务处理中可以使用这些语句，但进行回退时，这些操作也不撤销。

1. 隐式提交

一般的SQL语句都是针对数据库表直接执行和编写的。这就是所谓的隐式提交。即提交（写或者保存）操作都是自动进行的。

 

1. 事务提交中的提交：

事务提交中的提交不是隐式提交，必须通过明确的提交（commit）

1. 占位符（savepoint）的意义：

某些批量性操作中，使用简单的rollback和commit就行，但是有的需要阶段性完成的比较的事务，如果在执行过程发生错误，就不需要回退全部，只需要回退一部分，因此回退到某个节点的操作就需要占位符（placeholder）。

 

 

游标

1. SQL检索操作返回一组成为结果集的行，需要对这个结果集进行上一行，下一行，前十行等操作。这就是游标的用途，游标是一个存储在DBMS服务器上的数据库查询，它不是一条Select语句，而是被语句检索出来的结果集。在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。
2. 常见的游标的用途

1. 能够标记游标为只读，使数据能读取，但不能更新和删除。
2. 能控制可以执行的定向操作(向前，向后，第一，最后，绝对位置，相对位置等。)
3. 能标记某些列为可编辑列，某些列为不可编辑列
4. 规定范围，使游标对创建它的特定请求（存储过程）或所有请求可访问。
5. 指示DBMS对检索出的数据（而不是指出表中活动数据）进行复制，使数据在游标打开和访问期间不变化

1. 使用游标的注意事项：

1. 在使用游标前，必须声明定义它。这个过程实际上没有检索数据，

它只是定义要使用的select语句和游标选项

1. 一旦声明，就必须打开游标以供使用。这个过程用前面定义的select语句把数据实际检索出来。
2. 对于填有数据的游标，根据需要检索各行。
3. 在结束游标使用时，必须关闭游标，可能的话，释放游标
4. 声明游标后，可根据需要频可根据繁地打开和关闭游标。在游标打开时，可根据需要频繁地执行取操作。

 

 

约束

1. 主键：用来唯一标识列，并且永不变动

成为主键的条件：

1. 任意两行的主键值都不相同。
2. 每行都具有一个主键值。
3. 包含主键值的列从不修改或更新。
4. 主键值不能重用。

总结：主键就是唯一且不能改变的一列值。

1. 外键：

外键是另一表中的主键。外键是保证表的引用完整性的关键。

外键保证了引用的完整性但是删除时必须先删除外键才能对另一表中的特定的列记性删除。

1. 唯一约束：

唯一约束是保证一列中数据的唯一性。

唯一约束和主键的区别：

1. 表中包含多个唯一约束，但每个表中只允许一个主键。
2. 唯一约束列可包含Null值。
3. 唯一约束列可修改或更新
4. 唯一约束列的值可重复使用。
5. 唯一约束不可定义外键。

1. 检查约束：

检查约束用来保证一列中的数据满足一组特定的条件。

 

索引

1.索引就是目录。

触发器

1. 触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的Insert,Update和Delete操作相关联
2. 触发器能访问到的数据：

Insert操作中的新数据

Update操作中所有的新数据和旧数据

Delete操作中删除的数据

1. 触发器的常见用途

1. 保证数据一致
2. 基于某个表的变动在其他表上执行活动。
3. 进行额外验证并根据需要返回数据。例如保证某个顾客的可用资金不超限定，如果超过，则阻塞插入
4. 计算计算列的值或更新时间戳