Java中的MySQL数据库与JDBC编程笔记

一.JDBC基础：

    JDBC的全称为Java Database Connectivity,即Java数据库连接，是一种可以执行SQL语句的Java API。程序可通过JDBC API连接到关系数据库，并使用结构化查询语句（SQL,数据库标准的查询语句）来完成对数据库的查询、更新。

为了让JDBC在不同的平台上运行，需要中间的驱动程序。JDBC可以完成三个基本的工作：建立与数据库的连接；执行SQL语句；获得SQL语句的执行结果。通过这三个功能，应用程序即可访问、操作数据库系统。

二.SQL语句：

  SQL的全称是Structured Query Language,也就是结构化查询语句，SQL是操作和检索关系数据库的标准语句，标准的SQL可用于操作任何关系数据库。

使用SQL可以完成：

  在数据库中的检索信息；对数据库的信息进行更新；改变数据库的结构；更改系统的安全设置；增加或回收用户对数据库、表的许可权限。

可分为下面几种类型：

  查询语句：主要由select关键字完成，查询语句是SQL语句中最复杂。功能最丰富的语句；

  DML(Data Manipulation Language,数据操作语句)：主要由insert、update、delete三个关键字完成；

  DDL(Data Definition Language,数据定义语句)：主要由create、alter、drop、truncate四个关键字完成；

  DCL(Data Control Language,数据控制语句)：主要由grant和revoke两个关键字完成；

  事务控制语句：主要由commit、rollback、savepoint三个关键字完成。

  SQL标识符可用于定义表名、列名，也可以用于定义变量。规则如下：

  *标识符通常必须以字母开头；

  *标识符包括字母、数字和三个特殊字符（# _ $）;

  *不要使用当前数据库系统的关键字、保留字，建议使用多个单词连缀而成，单词之间以_分割；

  *同一模式下的对象不应该同名，这里的模式指的是外模式。

三.DDL语句：

  DDL语句是操作数据库对象的语句，包括创建（create）、删除（drop）、修改（alter）数据库对象。

  1.创建表的语法：    

create table [模式名.]表名{      #可以有多个列定义     columnNamel datatype [default expr], ……}

  2.修改表结构的语法：

alter table 表名add{      #可以有多个列定义     columnNamel datatype [default expr], ……};

  3.删除表及表中的数据的格式：

drop table 表名;#删除数据表drop table 数据;

  4.truncate表：是删除表里的全部数据，但保留表结构。

truncate 表名

四.数据库约束：

  1.NOT NULL:非空约束，指定某列不能为空；

  2.UNIQUE:唯一约束，指定某列或者几列组合不能重复；

  3.PRIMARY KEY:主键，指定该列的值可以唯一地标识该条记录；

  4.FOREING KEY:外键，指定该行记录从属于主表中的一条记录，主要用于保证参照完整性；

  5.CHECK:检查，指定一个布尔表达式，用于指定对应列的值必须满足该表达式。
五、索引：

  索引是存放在模式（schema）中的一个数据库对象，虽然索引总是从属于数据表，但它和数据表一样属于数据库对象。创建索引的唯一作用就是加速对表的查询，索引通过使用快速路径访问方法来快速定位数据，从而减少了磁盘的I/O。

  索引的创建：

     自动创建：当在表上定义主键约束、唯一约束和外键约束时，系统会为数据列自动创建对应的索引；

     手动创建：可通过create index……语句创建索引

create index index_nameon table_name (colum[,colum]……);

   索引的删除：

      自动删除：数据表被删除时，该表上的索引自动被删除；

      手动删除：用户可以通过drop index……语句来删除指定数据表上的指定索引。

drop index 索引名 on 表名

六.视图：

   视图只是一个或多个数据表中数据的逻辑显示。也就是一个查询结果，所以创建视图就是建立视图名和查询语句的关联。

create or replace view 视图名assubquery

视图只是查询数据，不能修改视图里的数据，因为视图本身没有存储数据。

七、DML语句：

  DML主要操作表里的数据，使用其可插入新数据、修改已有的数据、删除不需要的数据。

  插入数据：

insert into table_name[(column[,column])]values(values [,values]);

  更新数据：

update table_nameset columnl=value1[,column2=value]……[WHERE condition];

  删除数据：

delete from table_name[WHERE condition];

  查询数据：

     select语句是查询关键字，能进行单表查询、多表连接查询、子查询。从一个或多个数据表中选出特定行、特定列的交集。

select column1，column2……from 数据源[where condition]

八、数据库函数：

   函数一般都会有一个或多个输入，函数内部会对这参数进行判断和计算，最终只有一个值作为返回值。函数可出现在SQL语句各个位置，比较常用的位置是select之后和where子句中。

function_name(arg1,arg2)例如：select char_length(teacher_name)from teacher_table;

九、分组和组函数：

   组函数就是多行函数，组函数将一组记录作为整体计算，每组记录返回一个结果，而不是每条记录返回结果。

十、多表连接查询：

   选择的数据并不是来自一个表，而是来自多个数据表，这就需要使用多表连接查询。

多表连接查询有两种规范：

   1.SQL92规范：语法是把多个数据表都放在from之后，多个表之间以逗号隔开；连接条件放在where之后。与查询条件之间用and逻辑运算符连接。如果连接条件要求两列值相等，则成为等值连接，否则称为非等值连接；如果没有任何连接条件，则称为广义笛卡尔积。支持规范：等值连接；非等值连接；外链接；广义笛卡尔积。

select column1,column2……from table1 ,table2……[where join_condition]

   2.SQL99的连接查询：查询用的多个数据表显示使用 XXX join连接，而不是直接依次排列在from之后，from后只需要放在数据表；连接条件不再放在where之后，而是提供了专门的连接条件子句。

        交叉连接（cross join）：交叉连接无需任何连接条件

select s.* ,teacher_namefrom student_table scross join teacher_table t;

                   自然连接（natural join）：自然连接（natural join）：自然连接表面上看起来也无需指定连接条件，但自然连接是有连接条件的，自然连接会以两个表中的同名列作为连接条件；如果两个表中没有同名列，则自然连接与交叉连接效果完全一样——因为没有连接条件。自然连接表面上看起来也无需指定连接条件，但自然连接是有连接条件的，自然连接会以两个表中的同名列作为连接条件；如果两个表中没有同名列，则自然连接与交叉连接效果完全一样——因为没有连接条件。

select s.* ,teacher_namefrom student_table snatural join teacher_table t;

         using子句连接：using子句可以指定一列或多列，用于显式指定两个表中的同名列作为连接条件。假设两个表中超过一列的同名列，如果使用natural join，则会把所有的同名列当成连接条件；使用using子句，就可显式指定使用哪些同名列作为连接条件。

select s.* ,teacher_namefrom student_table sjoin teacher_table tusing(teacher_id);

        on子句连接：连接条件放在on子句中指定，而且每个on子句只指定一个连接条件。

select s.* ,teacher_namefrom student_table sjoin teacher_table ton s.java_teacher=t.teacher_id;

       左、右、全外连接：这三种外链接分别使用left[outer]join、right[outer]join、full[outer]join，这三种外链接的连接条件一样通过on子句来指定，既可以是等值连接条件，也可以是非等值条件。

select s.* ,teacher_namefrom student_table s//可以是left、right、fullright join teacher_table ton s.java_teacher=t.teacher_id;

      子查询：指在查询语句中嵌套另一个查询，子查询可以支持多层嵌套。

select *from (select * from student_table) twhere t.java_teacher>1

十一.集合运算：
       select语句查询的结果十一包含多条数据的结果集，类似数学里的集合，可以进行交(intersect)、并(union)、差(minus)运算。
select 语句 union select语句
select 语句 intersec select语句
select 语句 minus select语句
十二、JDBC编程步骤：

    1.加载数据库驱动，通常使用Class类的forName()静态方法来加载驱动。

//加载驱动Class,forName(driverClass);//加载MySQL的驱动Class.forName("com.mysql.jdbc.Driver");

    2.通过DriverManager获取数据库连接：

//获取数据库连接DriverManager.getConnection(String url,String user,String pass);

    3.通过Connection对象创建Statement对象。Connection创建Statement的方法：

         createStatement(String sql):创建基本的Statement对象；

         prepareStatement(String sql):根据传入的SQL语句创建预编译的Statement对象；

         prepareStatement(String sql):根据传入的SQL语句创建CallableStatement对象。

    4.使用Statement执行SQL语句。所有的Statement都有如下三个方法执行SQL语句：

         execute():可以执行任何SQL语句，但比较麻烦；

         executeUpdate():主要用于执行DML和DDL语句。执行DML语句返回受SQL语句影响的行数，执行DDL语句返回0；

         executeQuery():只能执行查询语句，执行后返回代表查询结果的ResultSet对象。

    5.操作结果集：如果执行的SQL语句是查询语句，则执行结果将返回一个ResultSet对象，该对象保存了SQL语句查询的结果。

       next()、previous()、first()、last()、beforeFirst()、afterLast()、absolute()等移动记录指针的方法。

       getXxx()方法获取记录指针指向行、特定列的值。

   6.回收数据库资源，包括关闭ResultSet、Statement和Connection等资源。

十三、管理结果集：

    JDBC使用ResultSet来封装执行查询得到的查询结果，然后通过移动ResultSet的记录指针来取出结果的内容。JDBC还允许通过ResultSet来更新记录，并提供ResultSetMetaData来获取得到ResultSet对象的相关信息。

   1.可滚动、可更新的结果集：ResultSet使用absolute()、previous()、afterLast()等自由移动记录指针是可滚动的；默认的ResultSet是不可以更新的，如希望创建可更新的ResultSet，则必须创建Statement或PrepareStatement时传入resultSetType或者resultSetConcurrency的参数。

   2、处理Blob类型数据：Binary Long Object是二进制长对象的意思，Blob列通常用于存储大文件，典型的Blob内容是一张图片或一个声音文件，由于它们的特殊性，必须使用特殊的方式来存储。使用Blob列可以把图片、声音等文件的二进制数据保存在数据库里，可以从数据库里恢复指定文件。

   3、使用ResultSetMetaData分析结果集：当执行SQL查询后可以通过移动记录指针来遍历ResultSet的每条记录，但程序可能不清楚该resultSet里包含哪些数据列，以及每个数据列的数据类型，那么可以通过ResultSetMetaData来获取关于resultSet的描述信息。

十四、事务处理：

   1.事务的基本概念：事务是由一步或几步数据库操作序列组成的逻辑执行单元，这系列操作要么全部执行，要么全部放弃执行；

事务具备原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。

事务的组成由一组DML语句，经过这组DML语句修改后的数据将保持较好的一致性、一条DDL语句、一条DCL语句。

事务包含的操作：显示提交试用commit；自动提交执行DDL或DCL语句，或者程序正常退出；显示回滚使用rollback；自动回滚系统错误或者强行退出。

   2.JDBC的事务支持：JDBC连接的事务支持由Connection提供，Connection默认打开自动提交，即关闭事务，在这种情况下，每条SQL语句一旦执行，即便立即提交数据库，永久生效，无法对其进行回滚操作。

//关闭自动提交，开启事务conn.setAutoCommit(false);//提交事务conn.commit();//回滚事务conn.rollback();

十五、使用连接池管理连接：

   数据库连接的建立及关闭时很耗费系统资源的操作，在多层结构的应用环境中，这种资源的耗费对系统性能影响尤为明显。通过DriverManager获取连接是一个数据库连接对象均对应一个物理数据库连接，每次操作都打开一个物理连接，使用完后立即关闭连接。频繁地打开、关闭连接将造成系统性能低下。

  数据连接池的解决方案是：当应用程序启动时，系统主动建立足够的数据库连接，并将这些连接组成一个连接池。每次应用程序请求数据库连接时，无需重新打开连接，而是从连接池中取出已有的连接使用，使用完后不再关闭数据库连接，而是直接将连接归还连接池。通过连接池大大提高程序的运行效率。

   对于共享资源的情况下，有一个通用的设计模式：资源池（Resource Pool）,用于解决资源的频繁请求、释放所造成的性能下降。（注：也就是类似于线程池的那种设计思想，例如自己写的一篇对线程池的理解的文章：Android中线程的相关知识及线程池的管理工具类）；