数据库&MYSQL&JDBC
来源：互联网 发布：如何设计软件 编辑：程序博客网 时间：2024/06/06 18:52
第一章:概论
1，数据库术语
1. Data：数据，是数据库中存储的基本对象，是描述事物的符号记录。
2. Database：数据库，是长期储存在计算机内、有组织的、可共享的大量数据的集合。
3. DBMS：数据库管理系统，是位于用户与操作系统之间的一层数据管理软件，用于科学地组织、存储和管理数据、高效地获取和维护数据。如mysql，oracle。
4. DBS：数据库系统，指在计算机系统中引入数据库后的系统，一般由数据库、数据库管理系统、应用系统、数据库管理员（DBA）构成。
2，数据模型
数据库三级模式通过二级映象在 DBMS 内部实现这三个抽象层次的联系和转换。外模式面向应用程序， 通过外模式/模式映象与逻辑模式建立联系， 实现数据的逻辑独立性。 模式/内模式映象建立模式与内模式之间的一对一映射， 实现数据的物理独立性。
外键：如果一个关系的一个或一组属性引用(参照)了另一个关系的主键，则称这个或这组属性为外码或外键(Foreign key)。
4，完整性约束
1）实体完整性：用于标识实体的唯一性。它要求基本关系必须要有一个能够标识元组唯一性的主键，主键不能为空，也不可取重复值。【主键唯一且非空】
2）参照完整性： 用于维护实体之间的引用关系。 它要求一个关系的外键要么为空， 要么取与被参照关系对应的主键值，即外键值必须是主键中已存在的值。【外键值为空或被参照关系对应的主键值】
3）用户定义的完整性：就是针对某一具体应用的数据必须满足的语义约束。包括非空、 唯一和布尔条件约束三种情况。
5，关系的 5 种基本操作是选择、投影、并、差、笛卡尔积。
1）笛卡尔积：
将两个表中的各个元组两两组合，列数变为原来的列数之和，行数为原来行数之积；会产生很多无意义的元组
2）投影
取出一个表中的某几列，并去除重复的行或列；属性可能减少,元组可能减少。
3）选择
水平分割，选取符合条件的元组。
6，连接（join）
1）内联接（典型的联接运算，使用像 = 或 <> 之类的比较运算符）。包括条件联接和自然联接。也可以用from....where 表示
条件连接（包括等值连接）：先对两个关系进行笛卡尔积运算，再根据条件做选择运算。
自然连接：当两个关系具有公共的属性时，连接条件是两个相同属性组的分量相等，并且把重复的属性去掉（与其他连接的不同）。即先等值连接，在做投影。
2）外连接：
http://blog.csdn.net/hellowheat/article/details/4207467
3）交叉连接：笛卡尔积；所有的连接都是先做笛卡尔积，然后通过条件筛选。
三种连接的区别：内连接通过条件筛选，不符合条件的两个表中的元组都不会显示；外连接也通过条件筛选，如果是左外连接，左表中不符合条件的元组会显示一条记录，配对项中的数据为null。也就是说坐标的任何记录都会在连接表中显示。笛卡尔积完全不考虑条件，左表中的每条元组都要和右表中的元组配一下对。
7，数据库表设计3范式
1NF（First Normal Form）：(属性的原子性约束)当且仅当所有属性只包含原子值，即每个属性都是不可再分的数据项。如某一属性为联系方式，其中包括联系电话和通讯地址，则就不符合。
2NF（Second Normal Form）：(表的主键约束)当且仅当实体E满足第一范式，且每一个非键属性完全依赖主键时，满足第二范式。即要求数据库表中的每个实例或行必须可以被惟一地区分。
3NF（Third Normal Form）：(表的外键约束)当且仅当实体E是第二范式（2NF），且E中没有非主属性传递依赖时，满足第三范式。例如，存在一个部门信息表，其中每个部门有部门编号（dept_id）、部门名称、部门简介等信息。那么在员工信息表中列出部门编号后就不能再将部门名称、部门简介等与部门有关的信息再加入员工信息表中。员工编号---部门编号---（部门名称、部门简介），这就叫传递依赖。如果不存在部门信息表，则根据第三范式（3NF）也应该构建它，否则就会有大量的数据冗余。
第三章:安全与性能
1，数据库并发控制
事务是一组数据库操作序列，这些操作不可分割，要么全做，要么不做，是数据库的逻辑工作单位，相当于操作系统进程的概念。
ACID，指数据库事务正确执行的四个基本要素的缩写。
包含：原子性（Atomicity）：要么全部完成，要么全不完成；
一致性（Consistency）：我们对一个表中的某些数据进行了更新操作,但是还没有进行提交,这时另外一个用户读取表中数据.这个时候就出现了读一致性的问题:到底是读什么时候的数据呢?是更新前的还是更新后的?
隔离性（Isolation）：事务的串行化；
持久性（Durability）：对数据库作出修改后会永久保存；
其他详见hibernate悲观锁乐观所部分。
2，数据库的索引:
http://blog.csdn.net/pang040328/article/details/4164874
3，OLTP和OLAP
4,常见关系型数据库
小型数据库：access、foxbase；中型数据库：sql server 、mysql、informix；大型数据库：db2、Oracle、Sybase。
二、Mysql数据库
1，逻辑模块组成：由SQL层和存储引擎层构成
2,基本SQL语法
1)distinct：去除重复的行；当distinct用于检索多个属性时只能这样用， 如select distinct row1,row2 from table；这样的话会搜索出row1和row2均不相同的行。
实现原理类似分组，索引实现。
2)limit 1,2 选取第二行和第三行；
3)order by 按列排序；order by 和limit组合能找出第n大（小）的值，找不到返回null；
两种实现方式，一是通过索引直接返回就是有序的；二是通过mysql自带的排序算法。第二种效率很低。
4)where 除了正常关系运算外还可通过is null返回空值的行，多个条件时可以使用and or in连接；
where.....可以和join.....on.....等价转换，大于两个表时建议用第二种。
5)like进行字符串匹配，返回包含目标字串的行，有通配符%(任意多个字符)和_(任意一个字符)，使用通配符后效率低下;
6)正则表达式 regexp。关于LIKE和REGEXP的区别：LIKE匹配整个列。如果被匹配的文本仅在列值中出现，LIKE并不会找到它，相应的行也不会返回（当然，使用通配符除外）。而REGEXP在列值内进行匹配，如果被匹配的文本在列值中出现，REGEXP将会找到它，相应的行将被返回，这时一个非常重要的差别（当然，如果适应定位符号^和$，可以实现REGEXP匹配整个列而不是列的子集）。
7）全文本搜索，match和against。用来代替like和正则，效率比前两个高。
以上是字符串搜索的三种方式。
8)字符串处理函数；日期处理函数；数字处理函数；
聚集函数：运行在行组上，返回一个值。avg(),count(),max(); 聚集函数只能返回单个值，如果存在多个最大值的情况，则不能用聚集函数。
9)group by(分组) 和 having(过滤分组)
使用group by的两个要素:
(1) 出现在select后面的字段 要么是聚合函数中的,要么是group by 中的.
(2) 要筛选结果 可以先使用where 再用group by 或者先用group by 再用having
http://www.cnblogs.com/rainman/archive/2013/05/01/3053703.html
实现：分组的具体实现是先排序后分组，所以也是用索引实现，效率很高。
10）select子句循序：select、from、 where、 group by、 having、 order by、 limit
11）子查询：where子句in操作符中；
12）mysql中的join：
交叉连接：mysql中以下五句等价，前三句都属于内连接，但在普通sql中，内连接必须加on条件
- mysql> select * from table1 inner join table2;
- mysql> select * from table1 nature join table2;
- mysql> select * from table1，table2;
- mysql> select * from table1 cross join table2;
- mysql> select * from (table1, table2);
自然连接：下面两句等价，公共字段只显示一次。
- mysql> select * from table1 natural join table2;
- mysql> select * from table1 inner join table2 using (id);
外连接：外连接相对于内连接的区别在于对于无法匹配的记录外连接会虚拟一条与之匹配的记录来保全连接表中的所有记录。
实现：在MySQL 中，只有一种 Join 算法，就是大名鼎鼎的 Nested Loop Join(嵌套迭代)，他没有其他很多数据库所提供的 Hash Join，也没有 Sort Merge Join。顾名思义，Nested Loop Join 实际上就是通过驱动表的结果集作为循环基础数据，然后一条一条的通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。如果还有第三个参与 Join，则再通过前两个表的 Join 结果集作为循环基础数据，再一次通过循环查询条件到第三个表中查询数据，如此往复。
13）表别名：在函数后面加as创建新的列名；
14) sql可能出现null的一些情况：
表定义中某些列定义为null
没有Else的Case语句
左、右和完全外联接
选择Min、Max、Avg和Sum
数学运算涉及可为null的列
15)视图：查询结果组成的虚拟表，本身没有内容，内容随相关表而变化。每次用到视图时都需要执行相应查询操作，因此效率不高。19）delete删除重复字段问题 delete a from Person a join Person b on a.email=b.email and a.id>b.id;
3，存储引擎
1）MyISAM，最早的存储引擎。MyISAM不支持事务、也不支持外键，但其访问速度快，对事务完整性没有要求。支持全文本 。使用表锁技术，即某个线程写操作时锁定整张表，锁粒度大，这样高并发下性能较差；
2）InnoDB，默认存储引擎。他提供了具有提交、回滚和崩溃恢复能力的事务安全和事物的四个安全级别；通过索引实现行锁机制，增加了并发性，并提供死锁检测机制；支持外键。但是比起MyISAM存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。 不支持全文本
4）MEMORY，内存存储。每个MEMORY表在硬盘上只存一个表结构，表内容全在内存中。MEMORY类型的表访问非常得快，因为它的数据是放在内存中的，并且默认使用HASH索引。但是一旦服务器关闭，表中的数据就会丢失掉。
4，索引:类似于字典里的索引，创建索引是指在某张表的一列或者多列上建立一个索引。如果查询时的检索条件上没有索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。
1）Mysql的索引类型：B+型树索引，哈希索引，全文索引。大部分引擎都采用B-tree。
2）优点：提高检索速度，因为减小了检索过程读取的数据量；排序速度，因为索引中的数据是按键值排序后存放的，不用额外排序了；分组速度，因为分组需要先排序；
3）分类：单建索引，唯一索引（主键是一种），组合索引，全文索引。
4）创建索引：可以在建表时创建；也可以在已存在表直接创建
使用索引：使用where过滤行时，所涉及到的列会先检测有没有索引，如果有的话会自动使用加快查询速度。多列索引只适用于索引中的多个列同时被查询到的情况。
select ... lock in share mode和select ... for update分别为读取的行加入共享锁和排它锁，但使用该语句必须在事务内，事务提交后锁释放。
1,基础代码（写完善）（重要）
stmt = conn.createStatement();
String sql = "insert into dept2 values (98, 'GAME', 'BJ')";
stmt.executeUpdate(sql);
3，使用preStat（重要）
pstmt = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
pstmt.setInt(1, deptno);
pstmt.setString(2, dname);
pstmt.setString(3, loc);
pstmt.executeUpdate();
4，处理存储过程
CallableStatement cstmt = conn.prepareCall("{call p(?, ?, ?, ?)}");
cstmt.registerOutParameter(3, Types.INTEGER);
cstmt.registerOutParameter(4, Types.INTEGER);
cstmt.setInt(1, 3);
cstmt.setInt(2, 4);
cstmt.setInt(4, 5);
cstmt.execute();
System.out.println(cstmt.getInt(3));
System.out.println(cstmt.getInt(4));
5，批处理
PreparedStatement ps = conn.prepareStatement("insert into dept2 values (?, ?, ?)");
ps.setInt(1, 61);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.setInt(1, 62);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.setInt(1, 63);
ps.setString(2, "haha");
ps.setString(3, "bj");
ps.addBatch();
ps.executeBatch();
6，事务处理（重要）
conn.setAutoCommit(false);
stmt = conn.createStatement();
stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
stmt.addBatch("insert into dept2 values (53, '500', 'haha')");
stmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
7，滚动结果集
ResultSet是一个游标，调用RS的相关函数可使用滚动的结果集。
8，JDBC连接池
1）DriverManager存在的问题：每次访问数据库都需要建立连接和释放链接，效率十分低下，如果连接没有释放，还会造成内存泄露。并且没有办法控制链接的数目，如果与数据库建立的连接过多，会导致数据库崩溃。
2）连接池概念：连接可复用，连接数量可控制。
3）连接池的关键问题：
A、并发问题
public synchronized Connection getConnection（）
B、多数据库服务器和多用户
对于大型的企业级应用，常常需要同时连接不同的数据库（如连接Oracle和Sybase）。如何连接不同的数据库呢？我们采用的策略是：设计一个符合 单例模式的连接池管理类，在连接池管理类的唯一实例被创建时读取一个配置文件，其中存放数据库连接信息如用户名，密码等，根据文件信息，创建多个连接池类的实例，每一个实例都是一个特定数据库的连接池。连接池管理类实例为每个连接池实例取一个名字，通过不同的名字来管理不同的连接池。
C、事务处理
可采用每一个事务独占一个连接来实现，这种方法可以大大降低事务管理的复杂性。
D、连接池的分配与释放
使用优先级阻塞队列管理空闲连接。
E、连接池的配置与维护
连接池中到底应该放置多少连接，才能使系统的性能最佳？系统可采取设置最小连接数（minConn）和最大连接数（maxConn）来控制连接池中的连 接。最小连接数是系统启动时连接池所创建的连接数。如果创建过多，则系统启动就慢，但创建后系统的响应速度会很快；如果创建过少，则系统启动的很快，响应起来却慢。这样，可以在开发时，设置较小的最小连接数，开发起来会快，而在系统实际使用时设置较大的，因为这样对访问客户来说速度会快些。最大连接数是连 接池中允许连接的最大数目，具体设置多少，要看系统的访问量，可通过反复测试，找到最佳点。
如何确保连接池中的最小连接数呢？有动态和静态两种策略。动态即每隔一定时间就对连接池进行检测，如果发现连接数量小于最小连接数，则补充相应数量的新连接,以保证连接池的正常运转。静态是发现空闲连接不够时再去检查。
3）JDBC提供javax.sql.DataSource接口用来为所有实现连接池的类提供标准，DataSource只提供了两个getConnection方法，用来替代DriverManager类。
在DataSource实现类的构造函数中批量创建与数据库的连接，并把创建的连接加入LinkedList对象中。实现getConnection方法，让getConnection方法每次调用时，从LinkedList中取一个Connection返回给用户。当用户使用完Connection，调用Connection.close()方法时，Collection对象应保证将自己返回到LinkedList中,而不要把conn还给数据库。
在Spring中就实现了DataSource，叫做dbcp，在配置文件中配置即可使用。
9，Rowset接口
RowSet 是 JDBC 2.0 开始提供的一个扩展包的接口，RowSet 接口扩展了 ResultSet 接口，可以允许我们作很多ResultSet 不可做的事情。 RowSet 一共有 3 种具体的行集，它们是 CachedRowSet 、 JdbcRowSet 和 WebRowSet 。
区别：
（1）默认情况下，所有 RowSet 对象都是可滚动的和可更新的。而ResultSet是只能向前滚动和只读的。
（2）RowSet可以是非链接的，而ResultSet是连接的。因此利用CacheRowSet接口可以离线操作数据。
（3）RowSet接口添加了对 JavaBeans 组件模型的 JDBC API 支持。rowset 可用作可视化 Bean 开发环境中的 JavaBeans 组件。
（4）RowSet采用了新的连接数据库的方法,。
（5）CacheRowSet是可以序列化的。
（6）应该倾向于把RowSet看成是与数据库无关的东西，它只是一个代表一行行数据的对象，而ResultSet则是一个与数据库紧密联系的东西。
四。数据库调优总结
http://blog.163.com/liang8421@126/blog/static/89481957201071755815308/
1，硬件调优：
1）数据库对象的放置策略 利用数据库分区技术，均匀地把数据分布在系统的磁盘中，平衡I/O 访问，避免I/O 瓶颈：
分区技术：http://www.sunzhenghua.com/mysql-myisam-innodb-partition-range-list-hash
2）RAID (独立磁盘冗余阵列)是由多个磁盘驱动器(一个阵列)组成的磁盘系统。通过将磁盘阵列当作一个磁盘来对待，基于硬件的RAID允许用户管理多个磁盘。通过使用基于硬件的RAID， 用户在不关闭系统的情况下能够替换发生故障的驱动器。 并发访问，访问速度比单块磁盘要高，数据可靠性高。
3）并发要求CPU的处理能力；访问的数据量大小要求IO的吞吐量，访问的频繁程度要求IOPS (Input/Output Operations Per Second)。
2，软件调优
1）系统构架优化：对系统中的一些业务逻辑进行简化，减少对数据库的访问(比方可以通过缓存的方式)；建立连接池，让连接尽量得以重用。
2）建表优化：建表三范式是为了防止数据冗余，而为了提高检索效率，可以做适度冗余优化。但数据的冗余优化的前提是检索频率大大高于修改频率。
a，大表分割：一张数据量较大的表中，某些字段或某些行的访问频率明显高于其他时，可将这些行或者列单独取出建表。分割表可分为水平分割表和垂直分割表两种；水平分割适用于某些行访问率高，垂直分割适用于某些列访问平率过高。大表分割带来问题类似于表分库，维护开销增大，效率降低，比如多表join，多表事务。
b，冗余字段： 对一些要做大量重复性计算的过程而言，若重复计算过程得到的结果相同，把结果存放在单独表中，加快了访问速度。还有某个表需要经常join别的表中某个字段时，为了减少join，可以将另外那个表中的字段冗余到本表中，但这样降低了修改的效率。
c，中间表临时表：对数据量较大的表进行连接操作，并且连接操作的结果是一个小结果集建立临时表；对数据量较大的表进行频繁访问，访问的范围比较固定且比较集中的字段建中间表。
3）索引优化
为查询较多而修改较少的列建立索引。
索引类别的选择要适当：比如无重复的列选择唯一索引；多列联合查询选择组合索引；
4）Quary(查询)优化
a,选择影响较大的语 句放在前面，较弱的选择条件写在后面，这样就可以先根据较严格的条件得出数据较小的信息；
b,在排序索引中完成排序查询。
c,不使用子查询而用join,如果是用的表锁，尽量拆分Quary以减小锁定表的数量。
d，只取出自己需要的列，因为返回的数据越多，传输越慢。
e，在Mysql的sql层，有一个Query Optimizer可以实现最后的优化。通过在select前加explain，可以查看Query Optimizer优化后的执行过程
f，Query Profiler是Mysql自带的调优工具，可以获得任何一条语句占用的资源如CPU，IO数，这样可以定位调优瓶颈
g，Mysql自身也提供缓存Query Cache，优化时注意控制缓存那些热点数据，且不经常变化的数据。
5）事务处理优化
减小所粒度，比如用行锁；降低隔离级别，从默认的Repeatable read到read commited；使用乐观锁，mvcc，hibernate可以实现，@Version标签。死锁定期检测，查询show engine innodb status\G查看死锁信息。
MyISAM：缩短锁定时间和减少锁定的表，即拆分Query；读写分离
- JDBC连接MySQL数据库
- JDBC连接MySQL数据库
- JDBC连接MYSQL数据库
- JDBC连接mysql数据库
- jdbc连接Mysql数据库
- jdbc 连接mysql数据库
- JDBC连接MYSQL数据库
- jdbc操作mysql数据库
- JDBC连接MySql数据库
- jdbc 连接Mysql数据库
- jdbc 操作mysql数据库
- jdbc连MySQL数据库
- JDBC连接MySQL数据库
- JDBC连接MYSQL数据库
- JDBC连接数据库(MySQL)
- JDBC连接mySQL数据库
- jdbc连接Mysql数据库
- jdbc连接mysql数据库
- Range——插入对象insertNode
- Serializable 和 Parcelable 两种序列化
- Eclipse使用Material Design步骤详解
- android 自定义View基础(2)
- Android的线程和线程池
- 数据库&MYSQL&JDBC
- mysql -uroot -proot tc -e "select * from merchandise limit 1"这个-E的命令作用是什么，用来什么什么的？
- java的常见异常与错误总结
- libuv学习笔记（2）
- LeetCode:Trapping Rain Water
- 没有到主机的路由
- mysql免密码登录, 及使用-e 参数执行sql脚本
- 云计算基本原理
- 关闭linux命令行屏幕保护
