数据库设计和优化

函数应用：

select * from employee order by deptid desc,salary asc  【排序】

select a.ename,a.salary,a.deptid

 from emp a,

    (select deptd,avg(salary) avgsal from emp group by deptid ) b

 where a.deptid=b.deptid and a.salary>b.avgsal 【关联查询，查出工资大于平均工资】

group by 分组函数：
select count(buyid),sum(monfy) from XX group by buyId ; 统计每个人购买次数和金额

7.求出发帖最多的人：

select authorid,count(*) total from articles  group by authorid

having total=(select max(total2) from (select count(*) total2 from articles group by authorid) as t);

调用存储过程代码：

public static void main(String[] args) {

// TODO Auto-generated method stub

Connection cn = null;

CallableStatement cstmt = null;

try {

//这里最好不要这么干，因为驱动名写死在程序中了

Class.forName("com.mysql.jdbc.Driver");

//实际项目中，这里应用DataSource数据，如果用框架，

//这个数据源不需要我们编码创建，我们只需Datasource ds = context.lookup()

//cn = ds.getConnection();

cn = DriverManager.getConnection("jdbc:mysql:///test","root","root");

cstmt = cn.prepareCall("{call insert_Student(?,?,?)}");

cstmt.registerOutParameter(3,Types.INTEGER);

cstmt.setString(1, "wangwu");

cstmt.setInt(2, 25);

cstmt.execute();

//get第几个，不同的数据库不一样，建议不写

System.out.println(cstmt.getString(3));

}

Union和Union All的区别之一在于对重复结果的处理。UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录

分页语句

取出sql表中第31到40的记录（以自动增长ID为主键）

mysql方案：select * from t order by id limit 30,10

oracle方案：select * from (select rownum r,* from t where r<=40) where r>30

having：

union

存储过程:

触发器:

索引:

１．分类拆分数据量大的表。对于经常使用的表（如某些参数表或代码对照表），由于其使用频率很高，要尽量减少表中的记录数量。

２．索引设计。在索引设计中，索引字段应挑选重复值较少的字段；在对建有复合索引的字段进行检索时，应注意按照复合索引字段建立的顺序进行。 

索引建立的规则：

a.索引的字段必须是经常用来做查询条件的字段

b.若索引是多个字段组合的情况，要把经常作为查询条件的字段作为第一个。否则当通过单个字段查询时，数据库不会通过索引查询。

c.索引的字段必须有足够的区分度，用一个经常相同的字段做索引，效率就会很低。

3.对表进行水平划分

如每年都会有大量数据产生，则可以按年份建立多张表。

4.对表进行垂直划分

若表中某些字段很长，占用空间较大，检索表的时候在这里进行大量I/0操作，则可以将该字段独立出一张表与原表建立1对1关系。

5.选择适当的字段类型，如尽量使用VARCHAR而不是CHAR。

6.文件图片等大文件使用硬盘存储，数据库中只储存文件路径。

7.外键要表示清楚，实际工作中很少建立外键。

8.集中批量操作优于频繁读写操作。

9.选择合适的引擎。

通俗地理解三个范式
　　通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就

必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解)：
　　第一范式：1NF是对属性的原子性约束，要求属性具有原子性，不可再分解；
　　第二范式：2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性；

　　第三范式：3NF是对字段冗余性的约束，即任何字段不能由其他字段派生出来

第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）

数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。

  第二范式（2NF）：

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

 第三范式的要求如下：

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

所以第三范式具有如下特征：
         1，每一列只有一个值
         2，每一行都能区分。
         3，每一个表都不包含其他表已经包含的非主关键字信息。

例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。

第一范式（1NF）：字段具有原子性,不可再分。所有关系型数据库系统都满足第一范式）

数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。

  第二范式（2NF）：

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。

要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的惟一标识。这个惟一属性列被称为主关键字或主键。第二范式（2NF）要求实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。简而言之，第二范式就是非主属性非部分依赖于主关键字。

 第三范式的要求如下：

满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。

所以第三范式具有如下特征：
         1，每一列只有一个值
         2，每一行都能区分。
         3，每一个表都不包含其他表已经包含的非主关键字信息。

例如，帖子表中只能出现发帖人的id，而不能出现发帖人的id，还同时出现发帖人姓名，否则，只要出现同一发帖人id的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。

优化：

表的设计具体需要注意的问题：

      1、数据行的长度不要超过8020个字节，如果超过这个长度的话物理页中这条数据将会占用两行从而造成存储碎片，降低查询效率。

      2、能够使用数字类型的字段尽量选用数字类型而不要用字符串类型，因为这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接的时候会逐个比较字符串中的每一个字符，而对应数字类型而言只需要比较一次就够了。

       3、对于不可变字符类型char和可变字符类型varchar，都是8000字节，char查询速度快，但是耗存储空间，varchar查询速度相对慢一些，但是节省存储空间。在设计字段的时候可以灵活选择。

       4、字段的最大长度在满足可能的需要的前提下，应怪尽可能设的短一些，这样能够提高查询的效率，而且在建立索引的时候也可以减少资源的消耗。

二、查询的优化。

      在保证功能实现的前提下，尽量减少对数据库的访问次数；通过搜索参数，尽量减少对表的访问行数，最小化结果集，从而减轻网络负担；

提高数据库运行效率的办法
　　在给定的系统硬件和系统软件条件下，提高数据库系统的运行效率的办法是：
 (1) 在数据库物理设计时，降低范式，增加冗余, 少用触发器, 多用存储过程。
 (2) 当计算非常复杂、而且记录条数非常巨大时(例如一千万条)，复杂计算要先在数据库外面。
 (3) 发现某个表的记录太多，例如超过一千万条，则要对该表进行水平分割。水平分割的做法是，

以该表主键PK的某个值为界线，将该表的记录水平分割为两个表。若发现某个表的字段太多，例如超过八十个，则垂直分割该表，将原来的一个表分解为两个表。
 (4) 对数据库管理系统DBMS进行系统优化，即优化各种系统参数，如缓冲区个数。
 (5) 在使用面向数据的SQL语言进行程序设计时，尽量采取优化算法。
　　总之，要提高数据库的运行效率，必须从数据库系统级优化、数据库设计级优化、程序实现级优化，这三个层次上同时下功夫。

SQL 语句优化

WHERE子句中的连接顺序．

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾

 SELECT子句中避免使用 ‘ * ‘
用TRUNCATE替代DELETE
　　当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是
　　恢复到执行删除命令之前的状况)

　　而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

 用Where子句替换HAVING子句

　　 避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.
用EXISTS替代IN,DISTINCT

　　在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率

使用表的别名(Alias)

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