一、基础查询

1、检索数据（简单的select语句），这是查询操作的起点

2、排序数据

3、过滤数据（where），过滤的条件：and, or, exist, in, not, any, some, all, 通配符

4、创建计算字段，使用数据处理函数，如（文本处理、日期时间处理、数值处理），聚集函数

5、分组数据（gruop），过滤分组（having）。

6、使用子查询，常用用途：利用子查询进行过滤（在where field_name in后），作为计算字段使用子查询，用子查询检查存在性（在where exists后）

如何建立（测试）子查询：最保险的办法是用类似于SQL Server处理子查询的方式逐步编写SQL代码，即从内层到外层。

7、联结数据，为什么使用联结：根据第三范式，分解数据为多个表能更有效地存储。而检索存储在多个表的数据则要用联结。

8、组合查询（Union）

select 子句顺序：

select，from，where（行过滤），group by，having（组过滤），order by

二、高级查询

1、不同检索方式的转换：

1）union 与 临时表

union 操作可以借助临时表实现。通过向临时表插入、更新多次数据。

临时表：declare @t table(...) 或 create table 或 with cr as ( select ...)...

2）子查询 与 临时表

用临时表代替子查询可以起到简化的作用：例如select * from table_name where field_name in ( 子查询），如果换成临时表，就可以缩短这个语句，也可以换成自定义函数（返回结果集）。

如果涉及的数据少,那如果用临时表,可能花时间最多的是临时表的创建和DROP。而复杂的子查询往往速度不如用临时表.

3）子查询 与 联结

通常联结操作都可以用子查询实现。相关子查询应转换为联结。

4）比较 子查询、联结、并 的效率

当一个检索可以用多种方式实现时，应考察一下时间，决定用哪种

select语句前加：

declare @d datetime

set @d=getdate()

并在select语句后加：

select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

2、相关子查询

1）非相关子查询是独立于外部查询的子查询，子查询总共执行一次，执行完毕后将值传递给外部查询。

2）相关子查询的执行依赖于外部查询的数据，外部查询执行一行，子查询就执行一次。

所以避免出现相关子查询

三、其他语法

1、插入：有以行插入，插入检索出的数据（1）insert into table_name1(field_list...) select field_list... from table_name2（2）select field_list... into table_name1 from table_name2

2、删除和更新

3、创建和操纵表：create, alert, drop table

4、视图：视图为虚拟的表。它包含的不是数据而是根据需要检索数据的查询。

5、T-SQL（sql server的程序设计语言）存储过程、游标、触发器等用到。可产生并操作变量、数据集等，支持分支、循环。

6、存储过程。与自定义函数区别：T-SQL调用函数可以返回值数据集，但是调用存储过程就不行（只能单值）

7、游标：可灵活操作T-SQL检索出来的结果集。

8、触发器：触发器是数据库响应Delete, Insert, Update语句而自动执行的一条T-SQL语句（或括在as begin 和end之间的一组语句）

9、事务处理

四、范式

不符合范式会出现：插入异常、删除异常、更新异常。都是冗余数据造成的，有点像设计坏味，重复代码。

第一范式

每个列和记录包含一个而且只包含一个值的表

例外：telNos属性中有值： 23421423,23413412,12341234

解决方法：新建一个（关系）表，把telnos分出去

第二范式

一个第一范式的表并且每个非主键列都可以从构成主键的全部的列得到。

该范式只针对复合主键的情况

什么是依赖：如果A属性 决定了 B属性，则B依赖A，A -> B

所以可以表示为，非主键列部分依赖于复合主键，即可能有非主键列只依赖复合主键中的一个。

例外：如果主键有a, b，非主键c依赖于b

解决方法：则新建一个表，b为新表主键，把c移到该表，b为原来表的外键

第三范式

一个已经是第一和第二范式的表，并且所有的非主键列的值都只能可以从主码列得到，而不从其它的列得到

例外：主键为a，非主键b, c，其中 b->c

解决方法：新建一个表，b为新表主键，把c移到新表，b外旧表的外键

第二范式的原则：对键的完全依赖（避免部分依赖）

第三范式的原则：除键外不依赖其它属性。

五、hibernate相关

1、数据关联

级联（关联）操作（增删改查）以前是用 级联关系（如外键）+存储过程来实现

有了hibernate后，它提供一些自己的级联功能，如<ont-to-one> 的 cascade属性设置为 all则在多种情况都级联

sql的级联关系只是约束，hibernate的级联包括级联插入等，这样就不需要用存储过程来实现级联操作，而直接可以操作多表了。

2、大数据量操作

Hibernate Session 缓存的两大作用

（1）减少访问数据库的频率。提高数据访问性能

（2）保证缓存中的对象与数据库中的相关记录保持同步。位于缓存中的对象被称为持久化对象。

在项目中使用Hibernate进行大数据量处理的一些总结，

1) 在处理大数据量时，会有大量的数据缓冲保存在Session的一级缓存中，这缓存大太时会严重显示性能，所以在使用Hibernate处理大数据量的，可以使用session.clear()或者session. Evict(Object) 在处理过程中，清除全部的缓存或者清除某个对象。

2) 对大数据量查询时，慎用list()或者iterator()返回查询结果，

1. 使用List()返回结果时，Hibernate会所有查询结果初始化为持久化对象，结果集较大时，会占用很多的处理时间。

2. 而使用iterator()返回结果时，在每次调用iterator.next()返回对象并使用对象时，Hibernate才调用查询将对应的对象初始化，对于大数据量时，每调用一次查询都会花费较多的时间。当结果集较大，但是含有较大量相同的数据，或者结果集不是全部都会使用时，使用 iterator()才有优势。

3. 对于大数据量，使用qry.scroll()可以得到较好的处理速度以及性能。而且直接对结果集向前向后滚动。

3) 对于关联操作，Hibernate虽然可以表达复杂的数据关系，但请慎用，使数据关系较为简单时会得到较好的效率，特别是较深层次的关联时，性能会很差。

4) 对含有关联的PO（持久化对象）时，若default-cascade="all"或者 “save-update”，新增PO时，请注意对PO中的集合的赋值操作，因为有可能使得多执行一次update操作。

5) 在一对多、多对一的关系中，使用延迟加载机制，会使不少的对象在使用时方会初始化，这样可使得节省内存空间以及减少数据库的负荷，而且若PO中的集合没有被使用时，就可减少互数据库的交互从而减少处理时间。

6) 对于大数据量新增、修改、删除操作或者是对大数据量的查询，与数据库的交互次数是决定处理时间的最重要因素，减少交互的次数是提升效率的最好途径，所以在开发过程中，请将show_sql设置为true，深入了解Hibernate的处理过程，尝试不同的方式，可以使得效率提升。

7) Hibernate是以JDBC为基础，但是Hibernate是对JDBC的优化，其中使用Hibernate的缓冲机制会使性能提升，如使用二级缓存以及查询缓存，若命中率较高明，性能会是到大幅提升。

8) Hibernate可以通过设置hibernate.jdbc.fetch_size，hibernate.jdbc.batch_size等属性，对Hibernate进行优化。

六、优化性能

1、调整内存分配、缓冲区大小（调整配置）、索引、分区等方式调整数据库性能（SQL Server 2005 有一个Database Engine Tuning Advisor提供索引、分区的建议）

2、总是有不止一种方法编写一条select语句，应该试验 联结、并、子查询等，找出最佳方法。

3、把请求适当分解为更小的请求，如：当select语句有一系列 复杂的or条件时，转换为用多条select 语句和连接它们的Union语句，会提高性能

4、应该总是使用正确的数据类型

5、决不检索比需求要多的数据，即不要用select *（除非真的需要每个列）

6、必须建立索引。如何建：分析使用的select 语句以找出重复的where 和order by 子句。如果一个简单的where子句返回结果花的时间太长，则可使用其中的列作为索引。

7、Like很慢，最好使用freetext 或 contains 进行全文本搜索。

8、数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非。所以当表的使用和内容变化时，理想的优化和配置也要改变。

七、《SQL语言艺术》笔记

1、制定计划：为性能而设计

关系是指表内不同字段之间存在“关系”

规范化很重要

第一范式：确保原子性

第二范式：检查对键的完全依赖

第三范式：检查属性独立性

空值对程序逻辑是危险的，会出现三值逻辑（如在用not in('blue','black',null)时）。必须使用空值的话，一定要清楚它在特定情况下的影响。

限用boolean字段（其实没有这个类型，很多时候用flag来实现），用completion_date completion_by 字段来实现判断。

子类型：如员工分合同工和固定工，则合同工和固定工为员工的子类型，把员工中涉及固定工（合同工）的信息分到相应的表，即用三个表，而不是一个表。合同工和固定工的主键的总数为员工的主键数。

表中有些字段出现了空值，表明需要引入子类型。

约束要明确声明，不要出现隐含约束（如某个字符串字段的值隐含为数字），语义要一致。数据语义属于DBMS，别放在应用程序中处理

2、发动战争：高效访问数据库

有时使用临时表效率会低，因为插入数据到临时表的操作耗时，不如直接用查询和union操作。

在合理范围内，利用每次数据库访问完成尽可能多的工作

采用临时表的主要理由，是用来支持大量面向表的操作，而避免逐行操作。

如：一次完成多个更新

如：采SQL用进攻式编程（不要每次操作都检查返回码，而是一次把操作都做完，最后检查，或不检查，因为有时的检查时不需要的，因为前提如果不满足后续自然就不执行，就算不检查返回码一样可以连在一起执行）

尽量使用dbms的函数，而不是自定义函数（优化器对自定义函数的代码无能为力）

SQL是声明性语言，所以设法使你的代码超越业务过程的规格说明（不要拘泥于分步骤进行操作，可以混合完成多个步骤）

3、战略部署：建立索引

因对外键建索引，因为外键连接的速度通常慢。

避免在函数中使用索引，因为索引不起效，可以对函数进行索引

4、机动灵活：思考SQL语句

SQL查询可分为两层操作，第一层是关系操作的“核”，它负责找出我们要操作的数据集，第二层是“非关系操作层”（如排序），它对有限的数据结果集进行“精雕细刻”从而产生用户期望的最终结果。

优化器是借助关系理论的，所以只对关系操作优化。因此SQL艺术应注重减小“非关系操作层”的厚度。如果中间查询的结果放在临时存储设备，则不利于优化器优化。总而言之在关系操作层完成尽量多的工作。

尽早过滤掉不需要的数据

SQL是一种声明性语言，是用来表达“要做什么”，而非“如何来做”的。理论上讲，从“要做什么”到“如何来做”的任务就是由优化器来完成的。

5、了如指掌：理解物理实现

6、锦囊妙计：认识经典SQL模式

小结果集，直接条件：运用索引

小结果集，间接条件：使用连接，如果要使用子查询，则尽可能使用非关联子查询（即子查询中的元素不与外层查询元素有关联，不然每次外层查询执行都会导致子查询走一遍，效率低）

大结果集：

优秀SQL编程的困难，多半在于解决问题的方式：不要将“一个问题”转换成对数据库的“一系列查询”，而是要转换成“少数查询”。程序用大量中间变量保存从数据库读出的值，然后根据变量进行简单判定，最后再把它们作为其他查询的输入……这样做是错误的。糟糕的sql编程有个显著特点，就是在sql查询之外存在大量代码，以循环的方式对返回数据进行加、减、乘、除之类的处理。这样做毫无价值、效率低下，这里工作应该交给sql聚合函数

10、集中兵力：应付大数据量

当数据量线性增长时，排序操作会导致非线性增长。所以有时为了调优，要调整分配给排序的内存数量。

总结：不同数据库操作对数据量增加的敏感程度不同。要预先考虑查询对不同数据量的执行方式。

避免“关联子查询”，因为“关联子查询”在计算每一天返回记录时都被调用一次。尽量减少关联子查询对外层查询元素的依赖性

总结：为了降低查询对数据量增加的敏感度，应该在较深层的查询中只操作必要的数据，将辅助性的join操作留在外层（精）

通过分区提高性能。

数据清除

数据仓库（有空看看事实表、维度表）

全书总结：

影响查询性能的重要因素包括：

表的数据量

表有哪些索引

存储特性（例如分区），和索引同样重要

查询条件的质量

结果集的大小

首先了解你的上下文（问题）和数据，然后开始行动，查询时一定要尽快去除多余的数据，一定要尽量保证优化器的自由，避免语句内部存在依赖性而限制了表的访问顺序。

优化器通常在以下情况无法高效工作：

1、通过很多语句，分别读取数据片段。从应用的角度这些sql语句是相关的，但是sql引擎不知道，它只可以优化单个语句，而无法对整体处理优化。

2、随便使用sql方言提高的各种非关系特性

记住，到大量的数据读取工作已经完成时，才应用非关系特性（在作更新或删除之前，必须先读取数据）。非关系特性是基于有限集合的操作（换言之，就是数组），不同于关系理论中的无限集合。