从MYSQL到JDBC（一）
一、什么是MYSQL
Mysql是最流行的关系型数据库管理系统，在WEB应用方面MySQL是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。
关系型数据库（RDBMS），是和非关系型数据库并行的概念，后者不在本文讨论范畴。而关系型数据库就是由二维表（数据表）及其之间的联系所组成的一个数据组织。
二、RDBMS 术语
在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：
数据库:数据库是一些关联表的集合。.
数据表:表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列:一列(数据元素) 包含了相同的数据, 例如邮政编码的数据。
行：一行（=元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
参照完整性:参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
三、数据库设计范式
我们可以简单地把数据库理解为数据按照特定规范存储在一起的集合。正如现实生活中仓库管理员要根据货物的性质（易碎、怕潮、进出货是否频繁）等来设计仓库布局，数据库设计也要考虑减少数据冗余和提高查询效率。通常人们在设计数据库需要遵照下列三个范式：
1、无重复的列
数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性。
比如：
|username | height | grade |三个字段，但字段grade下又分为math_grade,chinese_grade，则不符合要求，因为grade必须是不可再分的。满足第一范式的数据库则为关系型数据库。
2、属性完全依赖于主键
当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。如果存在不符合第二范式的情况，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。
比如：|username | height | age | weather|，其中weather是不依赖于主键username的，所以不满足第二范式。
又如：|studentname | teachername | studentage | teacherage|，其中studentage又studentname决定，teacherage由teachername 决定，存在多个主键，但其他字段不是由所有主键决定的，只是由其中一个主键决定的，所以不满足第二范式。
3、属性不能传递依赖于主属性（属性不依赖于其它非主键属性）
比如：|studentname | school | school_address | school_tel|，其中school_address 、school_tel不是由学生直接决定的，而是由studentname到school再到school_address 、school_tel间接决定。
此外还有BC范式等，但在实际设计数据库的过程中只需要满足三大范式即可，因为范式是把双刃剑，有利也有弊。
范式的目标是消除数据冗余、减少操作异常（更新异常、插入异常、删除异常）和让数据结构趋于合理，但范式越多，需要建立的数据表也越多，查询时需要连接的表也增多，直接导致查询复杂度（查询语句变长）和查询时间升高。
四、MYSQL常用语句
基本操作如创建与销毁，单表的增删改查（SELECT,DELETE,UPDATE,SELECT）在此不赘述，在这里记下可能忘记的操作。
1、like语句：
WHERE 子句中可以使用等号 (=) 来设定获取数据的条件，如 "runoob_author = 'Sanjay'"。
但是有时候我们需要获取 runoob_author 字段含有 "jay" 字符的所有记录，这时我们就需要在 WHERE 子句中使用 SQL LIKE 子句。
SQL LIKE 子句中使用百分号(%)字符来表示任意字符，类似于UNIX或正则表达式中的星号 (*)。
如果没有使用百分号(%), LIKE 子句与等号（=）的效果是一样的。
语法
以下是SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：
SELECT field1,field2,...fieldN FROM table_name1,table_name2...WHERE field1 LIKE condition1[AND[OR]]filed2='somevalue'
2、union语句
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
SELECT expression1,expression2,...expression_n
FROM tables[WHERE conditions]UNION[ALL|DISTINCT]SELECT expression1,expression2,...expression_n
FROM tables[WHERE conditions];
参数
expression1, expression2, ... expression_n: 要检索的列。
tables:要检索的数据表。
WHERE conditions:可选， 检索条件。
DISTINCT:可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
ALL:可选，返回所有结果集，包含重复数据。
3、order by 和group by
order by：设定你想按哪个字段哪中方式来进行排序，再返回搜索结果。即按照某个字段升序降序等。
SELECT field1,field2,...fieldN table_name1,table_name2...ORDER BY field1,[field2...][ASC[DESC]]
group by：根据一个或多个列对结果集进行分组。在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。“SELECT name,COUNT(*)FROM employee_tbl GROUP BY name;”表示将数据表按名字进行分组，并统计每个人有多少条记录。
4、连接的使用：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
举例如下：
--------------------------------------------
表A记录如下：
aID aNum
1 a20050111
2 a20050112
3 a20050113
4 a20050114
5 a20050115
表B记录如下:
bID bName
1 2006032401
2 2006032402
3 2006032403
4 2006032404
8 2006032408
--------------------------------------------
1.left join
sql语句如下:
select * from A
left join B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
5 a20050115 NULL NULL
（所影响的行数为 5 行）
结果说明:
left join是以A表的记录为基础的,A可以看成左表,B可以看成右表,left join是以左表为准的.
换句话说,左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: A.aID = B.bID).
B表记录不足的地方均为NULL.
--------------------------------------------
2.right join
sql语句如下:
select * from A
right join B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
NULL NULL 8 2006032408
（所影响的行数为 5 行）
结果说明:
仔细观察一下,就会发现,和left join的结果刚好相反,这次是以右表(B)为基础的,A表不足的地方用NULL填充.
--------------------------------------------
3.inner join
sql语句如下:
select * from A
innerjoin B
on A.aID = B.bID
结果如下:
aID aNum bID bName
1 a20050111 1 2006032401
2 a20050112 2 2006032402
3 a20050113 3 2006032403
4 a20050114 4 2006032404
结果说明:
很明显,这里只显示出了 A.aID = B.bID的记录.这说明inner join并不以谁为基础,它只显示符合条件的记录.
5、外键与级联操作
如果A表的一个字段m是B表的主键字段，则字段m就叫做A表的外键：因为A表的某一条记录可由m来唯一确定它在B表的对应记录。外键的作用是建立表与表之间的关系，保证数据的完整性。如A表为：学号|学生姓名|班级|住址|年龄|，B表为：学号|选课编号，C表为：课程编号|课程名称|学分
则学号为A表的主键，学号和选课编号为B表的联合主键，课程编号为C表的主键。
由B表的学号字段可唯一确定A表的一条数据，由B表的选课编号可唯一确定C表的一条数据，则称学号和选课编号都是B表的外键，分别对应A表和B表。外键数据完整性体现在：它可以设置父表更新时子表也更新，父表删除时如果子表有匹配的项，删除失败（或者父表更新时子表也更新，父表删除时子表匹配的项也删除等），更新可以保证数据一致性，删除限制可以保证数据的完整性，不会出现垃圾数据。
MySQL支持外键的存储引擎只有InnoDB，在创建外键的时候，要求父表必须有对应的索引，子表在创建外键的时候也会自动创建对应的索引。在创建索引的时候，可以指定在删除、更新父表时，对子表进行的相应操作，包括RESTRICT、NO ACTION、SET NULL和CASCADE。其中RESTRICT和NO ACTION相同，是指在子表有关联记录的情况下父表不能更新；CASCADE表示父表在更新或者删除时，更新或者删除子表对应记录；SET NULL则是表示父表在更新或者删除的时候，子表的对应字段被SET NULL。
创建数据库：
Create database test;
创建两个表，其中第一个表的”id”是第二个表（userinfo）的外键：
CREATE TABLE `user` (
`id` int(4) NOT NULL,
`sex` enum('f','m') DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `userinfo` (
`sn` int(4) NOT NULL AUTO_INCREMENT,
`userid` int(4) NOT NULL,
`info` varchar(20) DEFAULT NULL,
PRIMARY KEY (`sn`),
KEY `userid` (`userid`),
CONSTRAINT `userinfo_ibfk_1` FOREIGN KEY (`userid`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
注意：
1、存储引擎必须使用InnoDB引擎；
2、外键必须建立索引；
3、外键绑定关系这里使用了“ ON DELETE CASCADE ” “ON UPDATE CASCADE”，意思是如果外键对应数据被删除或者更新时，将关联数据完全删除或者相应地更新。更多信息请参考MySQL手册中关于InnoDB的文档；
好，接着我们再来插入数据测试：
INSERT INTO `user` (`id`,`sex`)
VALUES ('1', 'f'), ('2', 'm'), ('3', 'f');
INSERT INTO `userinfo` (`sn`,`userid`,`info`)
VALUES ('1', '1', '2005054dsf'),
('2', '1', 'fdsfewfdsfds'),
('3', '1', 'gdsgergergrtre'),
('4', '2', 'et34t5435435werwe'),
('5', '2', '435rtgtrhfghfg'),
('6', '2', 'ret345tr4345'),
('7', '3', 'fgbdfvbcbfdgr'),
('8', '3', '45r2343234were'),
('9', '3', 'wfyhtyjtyjyjy');
我们先看一下当前数据表的状态：
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user |
| userinfo |
+----------------+
2 rows in set (0.00 sec)
User表中的数据：
mysql> select * from user;
+----+------+
| id | sex |
+----+------+
| 1 | f |
| 2 | m |
| 3 | f |
+----+------+
3 rows in set (0.00 sec)
Userinfo表中的数据：
mysql> select * from userinfo;
+----+--------+-------------------+
| sn | userid | info |
+----+--------+-------------------+
| 1 | 1 | 2005054dsf |
| 2 | 1 | fdsfewfdsfds |
| 3 | 1 | gdsgergergrtre |
| 4 | 2 | et34t5435435werwe |
| 5 | 2 | 435rtgtrhfghfg |
| 6 | 2 | ret345tr4345 |
| 7 | 3 | fgbdfvbcbfdgr |
| 8 | 3 | 45r2343234were |
| 9 | 3 | wfyhtyjtyjyjy |
+----+--------+-------------------+
9 rows in set (0.00 sec)
下面我们就要试验我们的级联删除功能了。
我们将删除user表中id为2的数据记录，看看userinf表中userid为2的相关子纪录是否会自动删除：
执行删除操作成功！
mysql> delete from `user` where `id`='2';
Query OK, 1 row affected (0.03 sec)
看看user表中已经没有id为2的数据记录了！
mysql> select * from user;
+----+------+
| id | sex |
+----+------+
| 1 | f |
| 3 | f |
+----+------+
2 rows in set (0.00 sec)
再看看userinfo表中已经没有userid为2的3条数据记录了，对应数据确实自动删除了！
mysql> select * from userinfo;
+----+--------+----------------+
| sn | userid | info |
+----+--------+----------------+
| 1 | 1 | 2005054dsf |
| 2 | 1 | fdsfewfdsfds |
| 3 | 1 | gdsgergergrtre |
| 7 | 3 | fgbdfvbcbfdgr |
| 8 | 3 | 45r2343234were |
| 9 | 3 | wfyhtyjtyjyjy |
+----+--------+----------------+
6 rows in set (0.00 sec)
更新的操作也类似，因为我们在前面建表的时候已经定义外键删除、更新操作都是CASCADE，所以在这里可以直接测试数据。
将user表中原来id为1的数据记录更改为id为4，执行如下：
mysql> update user set id=4 where id='1';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
现在去看看两个表中是数据是否发生了变化：
mysql> select * from user;
+----+------+
| id | sex |
+----+------+
| 3 | f |
| 4 | f |
+----+------+
2 rows in set (0.00 sec)
mysql> select * from userinfo;
+----+--------+----------------+
| sn | userid | info |
+----+--------+----------------+
| 1 | 4 | 2005054dsf |
| 2 | 4 | fdsfewfdsfds |
| 3 | 4 | gdsgergergrtre |
| 7 | 3 | fgbdfvbcbfdgr |
| 8 | 3 | 45r2343234were |
| 9 | 3 | wfyhtyjtyjyjy |
+----+--------+----------------+
6 rows in set (0.00 sec)
比较原来的表可以发现它们的确已经更新成功了，测试完成！！！这也就实现了用外键对多个相关联的表做同时删除、更新的操作，从而保证了数据的一致性。
6、索引
索引用于快速找出在某个列中有一特定值的行。不使用索引，MySQL必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。如果一个表有1000行，这比顺序读取至少快100倍。注意如果你需要访问大部分行，顺序读取要快得多，因为此时我们避免磁盘搜索。
例如对下面这样的一个student表：
mysql>SELECT * FROM student
+——+———+———+———+———+
| id | name | english | chinese | history |
+——+———+———+———+———+
| 12 | Tom | 66 | 93 | 67 |
| 56 | Paul | 78 | 52 | 75 |
| 10 | Marry | 54 | 89 | 74 |
| 4 | Tina | 99 | 83 | 48 |
| 39 | William | 43 | 96 | 52 |
| 74 | Stone | 42 | 40 | 61 |
| 86 | Smith | 49 | 85 | 78 |
| 37 | Black | 49 | 63 | 47 |
| 89 | White | 94 | 31 | 52 |
+——+———+———+———+———+
这样，我们试图对它进行一个特定查询时，就不得不做一个全表的扫描，速度很慢。
例如，我们查找出所有english成绩不及格的学生：
mysql>SELECT name,english FROM student WHERE english<60;
+———+———+
| name | english |
+———+———+
| Marry | 54 |
| William | 43 |
| Stone | 42 |
| Smith | 49 |
| Black | 49 |
+———+———+
其中，WHERE从句不得不匹配每个记录，以检查是否符合条件。对于这个较小的表也许感觉不到太多的影响。但是对于一个较大的表，例如一个非常大的学校，我们可能需要存储成千上万的记录，这样一个检索的所花的时间是十分可观的。
如果，我们为english列创建一个索引：
mysql>ALTER TABLE student ADD INDEX (english) ;
+——————-+
| index for english |
+——————-+
| 42 |
| 43 |
| 49 |
| 49 |
| 54 |
| 66 |
| 78 |
| 94 |
| 99 |
+——————-+
如上表，此索引存储在索引文件中，包含表中每行的english列值，但此索引是在 english的基础上排序的。现在，不需要逐行搜索全表查找匹配的条款，而是可以利用索引进行查找。
假如我们要查找分数小于60的所有行，那么可以扫描索引，结果得出5行。然后到达分数为66的行，及Tom的记录，这是一个比我们正在查找的要大的值。
索引值是排序的，因此在读到包含Tom的记录时，我们知道不会再有匹配的记录，可以退出了。
如果查找一个值，它在索引表中某个中间点以前不会出现，那么也有找到其第一个匹配索引项的定位算法，而不用进行表的顺序扫描（如二分查找法）。
五、数据库存储引擎
1、什么是存储引擎呢？
存储引擎说白了就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）。
在Oracle 和SQL Server等数据库中只有一种存储引擎，所有数据存储管理机制都是一样的。而MySql数据库提供了多种存储引擎。用户可以根据不同的需求为数据表选择不同的存储引擎，用户也可以根据自己的需要编写自己的存储引擎。
2、MySql的存储引擎
（1）MyISAM：这种引擎是mysql最早提供的。这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种：
静态MyISAM：如果数据表中的各数据列的长度都是预先固定好的，服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的，所以这种表存取和更新的效率非常高。当数据受损时，恢复工作也比较容易做。
动态MyISAM：如果数据表中出现varchar、xxxtext或xxxBLOB字段时，服务器将自动选择这种表类型。相对于静态MyISAM，这种表存储空间比较小，但由于每条记录的长度不一，所以多次修改数据后，数据表中的数据就可能离散的存储在内存中，进而导致执行效率下降。同时，内存中也可能会出现很多碎片。因此，这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
压缩MyISAM：以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储，但是这种表压缩之后不能再被修改。另外，因为是压缩数据，所以这种表在读取的时候要先时行解压缩。
但是，不管是何种MyISAM表，目前它都不支持事务，行级锁和外键约束的功能。
（2）MyISAM Merge引擎：这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
（3）InnoDB：InnoDB表类型可以看作是对MyISAM的进一步更新产品，它提供了事务、行级锁机制和外键约束的功能。
（4）memory(heap)：这种类型的数据表只存在于内存中。它使用散列索引，所以数据的存取速度非常快。因为是存在于内存中，所以这种类型常应用于临时表中。
（5） archive：这种类型只支持select 和 insert语句，而且不支持索引。常应用于日志记录和聚合分析方面。
2、存储引擎的选择
MySQL有多种存储引擎，MyISAM和InnoDB是其中常用的两种。
MyISAM是MySQL的默认存储引擎，基于传统的ISAM类型，支持全文搜索，但不是事务安全的，而且不支持外键。每张MyISAM表存放在三个文件中：frm 文件存放表格定义；数据文件是MYD (MYData)；索引文件是MYI (MYIndex)。 InnoDB是事务型引擎，支持回滚、崩溃恢复能力、多版本并发控制、ACID事务，支持行级锁定（InnoDB表的行锁不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，如like操作时的SQL语句），以及提供与Oracle类型一致的不加锁读取方式。InnoDB存储它的表和索引在一个表空间中，表空间可以包含数个文件。
主要区别：
MyISAM是非事务安全型的，而InnoDB是事务安全型的。
MyISAM锁的粒度是表级，而InnoDB支持行级锁定。
MyISAM支持全文类型索引，而InnoDB不支持全文索引。
MyISAM相对简单，所以在效率上要优于InnoDB，小型应用可以考虑使用MyISAM。
MyISAM表是保存成文件的形式，在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦。
InnoDB表比MyISAM表更安全，可以在保证数据不会丢失的情况下，切换非事务表到事务表（alter table tablename type=innodb）。
应用场景：
MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能。
参考自：
1、http://www.runoob.com/mysql/mysql-tutorial.htmlhttp://blog.csdn.net/m8396017/article/details/51355211
2、http://www.cnblogs.com/pcjim/articles/799302.html
3、http://blog.csdn.net/sunzhenhua0608/article/details/8948120
4、http://doc.mysql.cn/mysql5/refman-5.1-zh.html-chapter/
5、http://www.cnblogs.com/ggjucheng/archive/2012/11/04/2754128.html
6、http://www.cnblogs.com/lina1006/archive/2011/04/29/2032894.html
