java数据库程序设计（这里以MySQL数据库为例）

刚好复习一下这块内容，顺便对这块做个总结。

结构化查询语言（SQL）是用来定义表和完整性约束以及访问和操纵数据库的语言，它是访问关系数据库的通用语言。下面先介绍常用的描述SQL有关语句格式：

1.SQL

a.[]

表示可选项，即方括号中的内容可以根据需要进行选择；不用时，则使用系统的默认值。方括号本身不是SQL语句的一部分，所以输入时不要输入方括号本身。

b.{}

表示必选项，即大括号的内容分必须要提供。在实际操作中也不要输入大括号本身。

c.<>

表示尖括号里的内容是用户必须要提供的参数。输入时不要输入尖括号本身。

d.[,...n]

表示前面的项可重复n次，相互间用逗号隔开。

SQL的关键字不区分大小写，SQL中不区分字符型和字符串型量，而统一定义为字符串型量，字符串型常量的定界符既可以使用单引号也可以使用双引号。

1.1创建数据库

在MySQL中可以使用CREATE DATABASE语句创建数据库

命令格式：

CREATE DATABASE <数据库名>;注：数据库名名称，在数据库系统中必须是唯一的

例：

CREATE DATABASE StudentScore;

1.2表操作

1.创建表

在创建表之前首先要使用表

USE StudentScore

SQL创建表的语句为：CREATE TABLE

命令格式为：

CREATE TABLE <表名> (<字段名> <数据类型> [<字段级完整约束>]...[,<表级完整性约束>])；

参数说明：

<表名>要创建的表的名字，是合法的标识符，表的同一数据库中不允许重名

<字段名>字段名字

<数据类型>指定字段的数据类型，对有些数据类型还需同时给出其长度、小数位数

<字段级完整性约束>主要有：

NULL和NOT NULL限制字段可以为NULL（空），或则不能为空

PRIMARY KEY设置字段为主码

UNIQUE设置字段值具有唯一性

<表级完整性约束>所使用的关键字与字段级完整性约束相似

例;

#创建表Student，sNO是主码
CREATE TABLE Student (sNo CHAR(9) NOT NULL PRIMARY KEY,sName CHAR(12) NOT NULL,sex CHAR(2),age INT,dept CHAR(50));
#创建表Course，cNO是主)
CREATE TABLE Course (cNo CHAR(9) NOT NULL PRIMARY KEY,cName CHAR(30) NOT NULL,credit INT);
#创建表Score，字段组sNO和cNo是复合主码
CREATE TABLE Score (sNo CHAR(9) NOT NULL,cNo CHAR(6) NOT NULL ,grade FLOAT,PRIMARY KEY (sNo,cNo));

2.删除表

命令格式;

DROP TABLE <表名>；要删除的表的名字

例：

DROP TABLE  Student;

3.修改表结构

命令格式：

ALTER COLUMN子句  修改表中已有字段的定义

ADD COLUMN子句  增加新字段及相应的完整性约束条件

DROP COLUMN子句  在该表中删除该子句中给出的字段

DROP CONSTRAINT子句  删除指定的完整性约束条件

例：

ALTER TABLE Student ADD COLUMN phone CHAR(11);

1.3表数据操作

1.插入数据

SQL中提供向表中插入数据的语句为INSERT

命名格式：

INSERT INTO <表名> [(<字段名>,...)] VALUES (<值>,...);

表名：要添加的新纪录的表

字段名：可选项，指定待添加数据的字段

VALUES子句：指定待添加数据的具体值，当指定字段名时，VALUES子句中值的排列顺序必须和字段名的排列顺序一致；若不指定字段时，则VALUES子句中值的排列顺序必须与创建表字段时的排列顺序一致。

注意：在表定义时指定了NOT NULL 约束的字段不能取空值，否则会出错。

例：

INSERT INTO Student (sNo,sName,sex,age,dept) VALUES ('201201009','王毅','男',18,'外语');

2.修改数据

UPDATE语句用于更新表中记录

命令格式:

UPDATE <表名> SET <字段名>=<表达式> [,<字段名>=<表达式>,...] [WHERE <条件>]；

表名：要修改记录的表

SET子句：给出要修改的字段及其修改后的值

WHERE子句：指定待修改的记录应当满足的条件，WHERE子句省略时，则修改表中所有记录

例：

UPDATE Student SET dept='金融' WHERE sNo='201201009';

3.删除数据

DELETE语句用来从表中删除一条或多条记录

命令格式：

DELETE FROM <表名> [WHERE <条件>];

表名：要删除记录的表

WHERE子句：指定待删除的记录应当满足的条件，WHERE子句省略时，则删除表中所有记录

例：

DELETE FROM Student WHERE sNo='201201009';

4.数据查询

数据库查询语句SELECT是SQL的核心。

4.1简单查询

使用SELECT语句可以选择查询表中的任意字段，其中<字段名>指出要查询字段的名字，可以是一个或多个。当字段名为多个时，中间要用‘’，‘’分隔。如果要查询表中的所有字段，则用‘’*‘’替代字段名。

SELECT sNo AS 学号,sName AS 姓名 FROM Student;

4.2条件查询

当要在表中找出满足某些条件的记录时，则需要使用WHERE子句设置查询条件。WHERE子句的查询条件是一个逻辑表达式，它是由各种运算符连接构成，以下给出WHERE常用的运算符及相应的功能：

=、>、<、>=、<=、!=、<>比较大小

BETWEEN AND 、NOT BETWEEN AND确定范围

IN 、NOT IN确定集合

LIKE 、NOT LIKE字符匹配

IS NULL 、IS NOT NULL判断空值

AND 、OR、 NOT逻辑运算（多重条件查询）

其中确定范围运算符的使用格式如下：

V BETWEEN V1 AND V2等价于v>=v1 AND V<=V2;

V NOT BETWEEN V1 AND V2等价于V<V1 OR V>V2

例：

SELECT * FROM Student WHERE dept='计算机';
#在学生表中查找计算机系的所有同学

4.3多重条件查询

当查询需要指定一个或多个查询条件时，这种条件称为多重条件或复合条件，此时需要使用逻辑运算符AND、 NOT或 OR将其连接成复合的逻辑表达式。逻辑运算符的优先级由高到低为：NOT 、AND、 OR，当然可以使用括号改变其优先级。

SELECT * FROM Student WHERE dept='计算机' AND sex='男';
#在学生表中查找计算机系的所有男同学

4.4模糊查询

当查询条件不知道完全精确的值时，还可以使用LIKE 或NOR LIKE进行模糊查询，模糊查询也称为部分匹配查询。模糊查询的一般格式为：

<字段名> [NOT] LIKE <匹配串>

<字段名>必须是字符型的字段，<匹配串>可以是一个完整的字符串，也可以包含通配符的字符串，字符串中的通配符及其功能如下：

% 代表0个或多个字符，ab%表示ab后可接任意字符串  

_(下划线)  代表一个字符，a_b表示a与b之间可为任意单个字符

[]  表示在某一范围的字符。[0-9]表示0~9之间的字符

[^]  表示不是在某一范围的字符，[^0-9]表示不在0~9之间的字符

例：

SELECT * FROM Student WHERE sName LIKE '李%';
#在学生表示中查找所有姓‘’李‘’的同学

4.5常用的统计函数及统计汇总查询

在SQL中除了可以使用算术运算符*、/、+、-外，还提供了系列统计函数。通过使用这些函数可以实现对表中的数据进行汇总或求平均值等各种运算，常见统计函数如下：

AVG(<>)求字段名所在列的平均值（必须是数值型列）

SUM(<>)求字段名所在列的总和（必须是数值型列）

MAX(<>)求字段名所在列的最大值

MIN(<>)求字段名所在列的最小值

COUNT(*)统计表中记录的个数

COUNT([DISTINCT]<>)统计字段名所在列非空值的个数，DISTINCT表示不包括字段的重复值

说明：上述函数中除COUNT(*)外，其他函数在计算过程中均忽略NULL值。

例：

SELECT AVG(grade) AS 平均成绩 FROM Score;
#"平均成绩"是表达式AVG（grade）的别名

4.6 ORDER BY子句

ORDER BY 是一个可选的字句，它允许根据指定字段的值按照升序或者降序的顺序显示查询结果。其中默认值为升序排列，用ASC表示，降序排列用DESC表示。

例：

SELECT sNo,grade FROM Score WHERE cNo='c001' ORDER BY grade DESC

4.7分组查询

统计函数只能产生单一的汇总数据，使用GROUP BY子句，则可以生成分组的汇总数据。GROUP BY子句可以按关键字段的值来组织数据，关键字段值相同的为一组。一般情况下，可以根据表中的某一字段进行分组，并且要求使用统计函数，这样每一个组只能产生一个记录。

例：

SELECT cNo,COUNT(*) AS 人数 FROM Score GROUP BY cNo;
#人数是用于显示的别名

2.使用JDBC开发数据库应用程序

通过以上知识创建数据库名为StudentScore，在创建三个表分别为Score、Course、Student，如下;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
//使用JDBC开发数据库应用程序
public class JDBCText {
	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1.建立与数据库的连接
		//加载JDBC驱动程序
		Class.forName("com.mysql.jdbc.Driver");//容易抛出ClassNotFoundException异常
		//创建数据库连接
		Connection conn=DriverManager.getConnection("jdbc:mysql://localhost/StudentScore","root","root");
		//MySQL数据库的主机名和数据库名，用户名，密码
		//2.执行语句
		//创建Statement对象
		Statement stmt=conn.createStatement();
		//执行SQL语句
		String sql="SELECT sNo,sName,sex,age FROM Student WHERE dept='计算机'";
		ResultSet rs=stmt.executeQuery(sql);
		//3.处理返回结果
		/*
		 * 在使用getXXX()方法进行取值时，可以通过字段名或列号来标识要获取数据的列。以下两句作用一样
		 * String no=rs.getString("sNo");
		 * String no=rs.getString(1);
		 * 说明：在ResultSet中，字段是从左至右编号的，并且从1开始
		 */
		while(rs.next()) {
			System.out.println(rs.getString("sNo")+" "
					+rs.getString("sName")+" "
					+rs.getString("sex")+" "
					+rs.getInt("age"));
		}
		//4.关闭创建的各种对象
		/*
		 *关闭次序：
		 *关闭结果集对象
		 *关闭Statement对象
		 *关闭连接对象
		 */
		try {
			if(rs!=null) {
				rs.close();//关闭结果集对象
			}
			if(stmt!=null) {
				stmt.close();//关闭Statement对象
			}
			if(conn!=null) {
				conn.close();//关闭JDBC与数据库的连接
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

运行结果如下：

201201001 钱静 女 19
201201003 周武 男 19
201201005 李俊 男 20

3.数据库的进一步操作

1）Statement接口

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class Demo {
	private static String driver="com.mysql.jdbc.Driver";
	private static String url="jdbc:mysql://localhost/StudentScore";
	private static String user="root";//自己的数据库用户名
	private static String password="root";//自己的数据库密码
	public static void main(String[] args) throws ClassNotFoundException, SQLException  {
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		String selectSql="SELECT * FROM Student WHERE dept='计算机'";
		String insertSql="INSERT INTO Student(sNo,sName,sex,age,dept) "+
		"VALUES('201201009','王毅','男',18,'外语');";
		String updateSql="UPDATE Student SET dept='金融' WHERE sNo='201201009'";
		String delectSql="DELETE FROM Student WHERE sNo='201201009'";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,password);
			stmt=conn.createStatement();
			rs=stmt.executeQuery(selectSql);
			while(rs.next()) {
				String no=rs.getString("sNo");
				String name=rs.getString("sName");
				String sex=rs.getString("sex");
				int age=rs.getInt("age");
				String dept=rs.getString("dept");
				System.out.println(no+" "+name+" "+sex+" "+age+" "+dept);
			}
			int count=stmt.executeUpdate(insertSql);
			System.out.println("添加了"+count+"条记录到Student表中");
		    count=stmt.executeUpdate(updateSql);
		    System.out.println("修改了Student表的"+count+"条记录");
		    count=stmt.executeUpdate(delectSql);
		    System.out.println("删除了Student表的"+count+"条记录");
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				if(rs!=null) {
					rs.close();//关闭结果集对象
				}
				if(stmt!=null) {
					stmt.close();//关闭Statement对象
				}
				if(conn!=null) {
					conn.close();//关闭JDBC与数据库的连接
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

2）preparedStatement接口

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
//PreparedStatement是处理预编译语句的接口，可加快访问数据库的执行速度
public class Demo1 {
	private static String driver="com.mysql.jdbc.Driver";
	private static String url="jdbc:mysql://localhost/StudentScore";
	private static String user="root";//自己的数据库用户名
	private static String password="root";//自己的数据库密码
	public static void main(String[] args) throws ClassNotFoundException, SQLException  {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String selectSql="SELECT * FROM Student WHERE dept=?";//?为动态参数的占位符
		String insertSql="INSERT INTO Student(sNo,sName,sex,age,dept) "+
		"VALUES(?,?,?,?,?);";
		String updateSql="UPDATE Student SET dept='金融' WHERE sNo=?";
		String delectSql="DELETE FROM Student WHERE sNo=?";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,password);
			ps=conn.prepareStatement(selectSql);
			ps.setString(1,"计算机");
			rs=ps.executeQuery();
			while(rs.next()) {
				String no=rs.getString("sNo");
				String name=rs.getString("sName");
				String sex=rs.getString("sex");
				int age=rs.getInt("age");
				String dept=rs.getString("dept");
				System.out.println(no+" "+name+" "+sex+" "+age+" "+dept);
			}
			ps=conn.prepareStatement(insertSql);
			ps.setString(1,"201201009");//将字符串传给第一个参数的占位符
			ps.setString(2,"王毅");
			ps.setString(3,"男");
			ps.setInt(4,18);//将整数型18传给第四个参数的占位符
			ps.setString(5,"外语");
			int count =ps.executeUpdate();//执行SQL语句
			System.out.println("添加了"+count+"条记录到Student");
			ps=conn.prepareStatement(updateSql);
			ps.setString(1,"201201009");
			count=ps.executeUpdate();
			System.out.println("修改了Student表的"+count+"条记录");
			ps=conn.prepareStatement(delectSql);
			ps.setString(1,"201201009");
			count=ps.executeUpdate();
			System.out.println("删除了Student表的"+count+"条记录");
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				if(rs!=null) {
					rs.close();//关闭结果集对象
				}
				if(ps!=null) {
					ps.close();
				}
				if(conn!=null) {
					conn.close();//关闭JDBC与数据库的连接
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

3）获取元数据

所谓元数据就是有关数据库和表结构的信息。

1.DatabaseMetaData接口

这个接口主要用来得到关于数据库的信息。

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;

public class Demo3 {
	private static String driver="com.mysql.jdbc.Driver";
	private static String url="jdbc:mysql://localhost/StudentScore";
	private static String user="root";//自己的数据库用户名
	private static String password="root";//自己的数据库密码
	public static void main(String[] args) {
		Connection conn=null;
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,password);
			DatabaseMetaData dmd=conn.getMetaData();
			System.out.println("数据库产品："+dmd.getDatabaseProductName());
			System.out.println("数据库版本："+dmd.getDatabaseProductVersion());
			System.out.println("驱动程序："+dmd.getDriverName());
			System.out.println("数据库URL："+dmd.getURL());			
		}catch(Exception e) {
			e.printStackTrace();
		}finally{
			try {
				if(conn!=null) {
					conn.close();
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

2）ResultSetMetaData接口

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
//ResultSetMetaData接口主要用来获取结果集的结构，例如结果集的字段数量、字段的名字
public class Demo4 {
	private static String driver="com.mysql.jdbc.Driver";
	private static String url="jdbc:mysql://localhost/StudentScore";
	private static String user="root";//自己的数据库用户名
	private static String password="root";//自己的数据库密码
	public static void main(String[] args) {
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,password);
			String sql="SELECT * FROM Student WHERE dept='计算机'";
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			ResultSetMetaData rsMetaData=rs.getMetaData();
			System.out.println("总共有："+rsMetaData.getColumnCount()+"列");
			for(int i=1;i<rsMetaData.getColumnCount();i++) {
				System.out.println("列"+i+":"+rsMetaData.getColumnName(i)+","+
			rsMetaData.getColumnTypeName(i)+"("+
						rsMetaData.getColumnDisplaySize(i)+")");
			}
		}catch(Exception e) {
			e.printStackTrace();
		}
		finally {
			try {
				if(rs!=null) {
					rs.close();
				}
				if(stmt!=null) {
					stmt.close();
				}
				if(conn!=null) {
					conn.close();
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

4）事务操作

事务是保证数据库完整性与一致性的重要机制。已提交事务是指成功执行完毕的事务，未能成功执行完成的事务称为中止事务，对中止事务造成的变更需要进行撤销处理，称为事务回滚。

1.setAutoCommit()

在JDBC中，事务操作默认是自动提交的，也就是说，一个连接被创建后，就采用一种默认提交模式。即每一条SQL语句都被看做是一个事务，对数据库的更新操作成功后，系统将自动调用commit（）方法提交。若把多个SQL语句作为一个事务就要关闭这种自动提交模式，这是通过调用当前连接来实现。

2.commit()

当连接的自动提交模式被关闭后，SQL语句的执行结果将不被提交，直到用户显式调用连接的方法，从上一次方法调用后到本次方法调用之间的SQL语句被作为一个事务进行提交。

3.rollback()

当调用commit（）方法进行事务处理时，只要事务中的任何一条SQL语句没有生效，就会抛出SQLException异常。也就是说，当一个事务执行过程中出现异常而失败时，为了保证数据一致性，在处理SQLException异常时，必须将该事务回滚。JDBC中事务的回滚是调用连接的rollback（）方法完成。这个方法将取消事务，并将该事务已执行部分对数据的修改恢复到事务执行前的值。如何一个事务中包含多个SQL语句，则在事务执行过程中一旦出现SQLException异常，就应调用rollback（）方法，将事务取消并对数据进行恢复。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Demo5 {
	private static String driver="com.mysql.jdbc.Driver";
	private static String url="jdbc:mysql://localhost/StudentScore";
	private static String user="root";//自己的数据库用户名
	private static String password="root";//自己的数据库密码
	public static void main(String[] args) {
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		String selectSql1="INSERT INTO Student(sNo,sName,sex,age,dept) "+
		"VALUES('201201010','张三','男',18,'计算机');";
		String selectSql2="INSERT INTO Student(sNo,sName,sex,age,dept) "+
				"VALUES('201201011','李四','男',19,'会计');";
		String selectSql3="INSERT INTO Student(sNo,sName,sex,age,dept) "+
				"VALUES('201201001','王五','男',20,'金融');";
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,password);
			stmt=conn.createStatement();
			boolean autoCommit=conn.getAutoCommit();//获取自动提交模式
			conn.setAutoCommit(false);//设置取消自动提交模式
			stmt.executeUpdate(selectSql1);
			stmt.executeUpdate(selectSql2);
			stmt.executeUpdate(selectSql3);
			conn.commit();//执行提交操作
			conn.setAutoCommit(autoCommit);//还原自动提交模式
		}catch(Exception e) {
			e.printStackTrace();
			if(conn!=null) {
				try {
					conn.rollback();//执行回滚操作
				}catch(SQLException e1) {
					e1.printStackTrace();
				}
			}
		}
		finally {
			try {
				if(stmt!=null) {
					stmt.close();
				}
				if(conn!=null) {
					conn.close();
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}
	}
}

JDBC不但支持回滚操作，还支持部分回滚的保存点操作，所谓保存点，就是标记需回滚的位置。通过保存点，可以更好地控制事务回滚。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;

public class Demo6 {
	private static String driver="com.mysql.jdbc.Driver";
	private static String url="jdbc:mysql://localhost/StudentScore";
	private static String user="root";//自己的数据库用户名
	private static String password="root";//自己的数据库密码
	public static void main(String[] args) {
		Connection conn=null;
		Statement stmt=null;
		ResultSet rs=null;
		String selectSql1="INSERT INTO Student(sNo,sName,sex,age,dept) "+
				"VALUES('201201010','张三','男',18,'计算机');";
		String selectSql2="INSERT INTO Student(sNo,sName,sex,age,dept) "+
				"VALUES('201201011','李四','男',19,'会计');";
		String selectSql3="INSERT INTO Student(sNo,sName,sex,age,dept) "+
				"VALUES('201201012','王五','男',20,'金融');";
		boolean ynRollback=true;
		try {
			Class.forName(driver);
			conn=DriverManager.getConnection(url,user,password);
			stmt=conn.createStatement();
			boolean autoCommit=conn.getAutoCommit();
			conn.setAutoCommit(false);
			stmt.executeUpdate(selectSql1);
			Savepoint s1=conn.setSavepoint();
			stmt.executeUpdate(selectSql2);
			stmt.executeUpdate(selectSql3);
			if(ynRollback) {
				conn.rollback(s1);
			}
			conn.commit();
			conn.setAutoCommit(autoCommit);
			
		}catch(Exception e) {
			e.printStackTrace();
			if(conn!=null) {
				try {
					conn.rollback();//执行回滚操作
				}catch(SQLException e1) {
					e1.printStackTrace();
				}
			}
		}
		finally {
			try {
				if(stmt!=null) {
					stmt.close();
				}
				if(conn!=null) {
					conn.close();
				}
			}catch(Exception e) {
				e.printStackTrace();
			}
		}	
	}
}