总结JDBC
JDBC简介
JDBC概念
什么是jdbc:
- Java Database Connectivity，java数据库连接
- 是一种用于执行SQL语句的Java API，可以为多种数据库提供统一访问，由一组Java语言编写的类和接口组成。
JDBC提供了一组标准，据此可以构建更高级的工具和接口，使数据库开发人员能够编写数据库应用程序。
有了JDBC，向各种数据库发送SQL语句就是一件很容易的事。换言之，有了JDBC API，就不必为访问MySQL数据库专门写一个程序，为访问Oracle数据库又专门写一个程序，或为访问DB2数据库又编写另一个程序等等，程序员只需用JDBC API写一个程序就够了，它可向相应数据库发送SQL调用。
同时，将Java语言和JDBC结合起来使程序员不必为不同的平台编写不同的应用程序，只须写一遍程序就可以让它在任何平台上运行，这也是Java语言“编写一次，处处运行”的优势。
比如，我们在Java代码中如果要访问MySQL，那么必须编写代码操作JDBC接口。JDBC接口是Java标准库自带的，所以可以直接编译。
而具体的JDBC驱动是由数据库厂商提供的，例如，MySQL的JDBC驱动由Oracle提供。
因此，访问某个具体的数据库，我们只需要引入该厂商提供的JDBC驱动，就可以通过JDBC接口来访问，这样保证了Java程序编写的是一套数据库访问代码，却可以访问各种不同的数据库，因为他们都提供了标准的JDBC驱动：
Java标准库自带的JDBC接口其实就是定义了一组接口，而某个具体的JDBC驱动其实就是实现了这些接口的类：
实际上，一个MySQL的JDBC的驱动就是一个jar包，它本身也是纯Java编写的。我们自己编写的代码只需要引用Java标准库提供的java.sql包下面的相关接口，由此再间接地通过MySQL驱动的jar包通过网络访问MySQL服务器，所有复杂的网络通讯都被封装到JDBC驱动中，因此，Java程序本身只需要引入一个MySQL驱动的jar包就可以正常访问MySQL服务器：
JDBC的作用
JDBC是用于执行SQL语句的Java API(Java语言通过JDBC可以操作数据库)
JDBC可以干三件事：
-
与数据库建立连接
-
将Java中拼写的SQL语句发送到数据库中执行
-
处理执行结果
JDBC在开发中的地位
在技术上，JDBC实现数据的添加、删除、修改、查询等操作，是Java应用程序与数据库通信的桥梁。
软件开发中的界面负责数据输入，并将输入的数据提交给Java程序，Java程序通过JDBC将数据保存到数据库中。JDBC也负责从数据库中获取数据，然后将数据交给Java程序，最后Java程序将数据交给界面显示，如下图所示。
JDBC API
Java程序访问数据库，使用JDBC提供的一套标准API，这套API主要是接口，由各个数据库的厂商去实现，并封装成jar包的形式提供给开发人员，如下图所示：
在实际项目开发中，我们确定要连接的数据库，在对应的数据库官方网站下载厂商已经实现的数据库连接用的驱动jar包，将驱动jar包导入到我们的项目中即可完成项目对特定数据库连接的支持。
项目支持了数据库的连接之后，就需要开发人员开发具体的程序来实现数据库数据的操作了。java是面向对象的语言，java提供了以下接口和类来支持和数据库之间的操作
| 类或接口 | 作用 |
|---|---|
java.sql.DriverManager类 | 表示数据库驱动包的管理对象 |
java.sql.Connection接口 | 表示数据库的连接对象 |
java.sql.Statement接口 | 表示发送sql给数据库执行的对象 |
java.sql.PreparedStatement接口 | java.sql.Statement的子接口，表示sql预编译对象 |
java.sql.ResultSet接口 | 表示从数据库查询返回的结果数据 |
JDBC在运行时的大致步骤如下图所示，执行的顺序如下。
(1). 由DriverManager类驱动数据库。
(2). 由Connection接口负责将Java程序和数据库建立连接。
(3). 由Statement/PreparedStatement接口将SQL语句发送到数据库中。
(4). 由ResultSet接口处理数据库返回的结果集。
DriverManager
java.sql.DriverManager用于管理一组 JDBC 驱动程序的基本服务。可以通过该管理类来建立并获取和指定数据库之间的连接。
public class DriverManager
static方法
常用的方法有：
public static Connection getConnection(String url, String user, String password):使用账号user和密码password，建立到指定数据库URL的连接，连接成功后返回新的数据库连接public static Connection getConnection(String url):尝试查找了解给定URL的驱动程序(将数据库用户信息包含在url中)public static void registerDriver(java.sql.Driver driver):向 DriverManager 注册给定驱动程序
数据库连接字符串
DriverManager类在加载数据库驱动时，需要指明url，这里的url通常称作数据库连接字符串。数据库连接字符串用于确定连接协议、连接的数据库服务器、端口号、数据库名称及其连接参数。
数据库连接字符串，其格式如下：
主协议:子协议://数据库服务器:端口号/数据库名称?连接参数key=value&key1=value2
jdbc:mysql://localhost:3306/store?username=root&password=
jdbc:mysql://127.0.0.1:3306/store
例如：
jdbc:mysql://localhost:3306/store
jdbc:mysql://localhost:3306/store?username=root&password=
Connection
java.sql.Connection用于与特定数据库的连接（会话）。 执行SQL语句并在连接的上下文中返回结果。只有在数据库连接后，才能将SQL语句发送到数据库中执行。
public interface Connection extends Wrapper, AutoCloseable
常用方法
Statement createStatement():创建一个Statement对象，用于将SQL语句发送到数据库PreparedStatement prepareStatement(String sql):创建一个PreparedStatement对象，用于将参数化的SQL语句发送到数据库。PreparedStatement prepareStatement(String sql, int autoGeneratedKeys):创建一个默认的PreparedStatement对象，该对象获取自动生成的键,autoGeneratedKeys值可以是：Statement.RETURN_GENERATED_KEYS/Statement.NO_GENERATED_KEYSvoid setAutoCommit(boolean autoCommit):将此连接的自动提交模式设置为给定状态,默认为自动提交void commit():使自上次提交/回滚以来所做的所有更改都将永久性，并释放此 Connection对象当前持有的任何数据库锁。void rollback():撤消在当前事务中所做的所有更改，并释放此 Connection对象当前持有的任何数据库锁。void rollback(Savepoint savepoint):撤消在给定的 Savepoint对象设置后进行的所有更改Savepoint setSavepoint():在当前事务中创建一个未命名的保存点，并返回代表它的新的 Savepoint对象。Savepoint setSavepoint(String name):在当前事务中创建具有给定名称的保存点，并返回代表它的新的 Savepoint对象。boolean isClosed():检索此 Connection对象是否已关闭void close():Connection释放此Connection对象的数据库和JDBC资源，而不是等待它们自动释放。 在已关闭的Connection对象上调用方法close是无操作的
Statement
java.sql.Statement用于执行静态SQL语句并返回其生成的结果的对象。默认情况下，每个Statement对象只能有一个ResultSet对象同时打开。
public interface Statement extends Wrapper, AutoCloseable
常用方法
-
boolean execute(String sql):执行给定的SQL语句 -
boolean execute(String sql, int autoGeneratedKeys)： 执行给定的 SQL 语句，并通知驱动程序所有自动生成的键都应该可用于获取。autoGeneratedKeys值可以是：Statement.RETURN_GENERATED_KEYS/Statement.NO_GENERATED_KEYS -
ResultSet getGeneratedKeys():检索由执行此Statement对象而创建的任何自动生成的密钥。 如果此Statement对象未生成任何键，则返回空的ResultSet对象 -
ResultSet getResultSet():检索当前结果为ResultSet对象。 每个结果应该只调用一次这个方法。 -
int getUpdateCount():将当前结果作为更新计数检索; 如果结果是ResultSet对象或没有更多的结果，则返回-1。 每个结果应该只调用一次这个方法。 -
int executeUpdate(String sql):执行给定的SQL语句，可以是 INSERT ， UPDATE ，或 DELETE语句，或者不返回任何内容 -
int executeUpdate(String sql, int autoGeneratedKeys):执行给定的 SQL 语句，并用给定标志通知驱动程序由此 Statement 生成的自动生成键是否可用于获取 -
ResultSet executeQuery(String sql):执行给定的SQL语句，该语句返回单个 ResultSet对象 -
boolean isClosed():如果这个Statement对象关闭，则为true; 如果它仍然开放，则为false -
void close():Statement释放此Statement对象的数据库和JDBC资源，而不是等待其自动关闭时发生。调用已关闭的Statement对象上的方法close不起作用。当一个Statement对象关闭时，其当前的ResultSet对象（如果存在）也被关闭
PreparedStatement
java.sql.PreparedStatement表示预编译的SQL语句的对象,SQL语句已预编译并存储在PreparedStatement对象中。 然后可以使用该对象多次有效地执行此语句.
public interface PreparedStatement extends Statement
常用方法
该接口继承自Statement接口，扩展了以下常用方法：
void setObject(int parameterIndex, Object x):使用给定对象设置指定参数的值ResultSet executeQuery():执行此 PreparedStatement对象中的SQL查询，并返回查询 PreparedStatement的 ResultSet对象int executeUpdate():执行在该SQL语句PreparedStatement对象，它必须是一个SQL数据操纵语言（DML）语句，如INSERT ， UPDATE或DELETE ; 或不返回任何内容的SQL语句，例如DDL语句
ResultSet
java.sql.ResultSet表示数据库结果集的数据表，通常通过执行查询数据库的语句生成。ResultSet对象保持一个光标指向其当前的数据行。 最初，光标位于第一行之前。 next方法将光标移动到下一行，并且由于在ResultSet对象中没有更多行时返回false ，因此可以在while循环中使用循环来遍历结果集。
public interface ResultSet extends Wrapper, AutoCloseable
常用方法
boolean next():将光标从当前位置向前移动一行。 ResultSet光标最初位于第一行之前; 第一次调用方法next使第一行成为当前行; 第二个调用使第二行成为当前行，依此类推。返回值true如果新的当前行有效; false如果没有更多的行Object getObject(int columnIndex):获取此的当前行中指定列的值ResultSet作为对象Object在Java编程语言。第一列是 1Object getObject(String columnLabel):获取此的当前行中指定列的值ResultSet作为对象Object在Java编程语言。使用SQL AS子句指定的列的标签void close():ResultSet发布此ResultSet对象的数据库和JDBC资源，而不是等待此事件在自动关闭时发生。boolean isClosed():检索此 ResultSet对象是否已关闭
ResultSetMetaData
java.sql.ResultSetMetaData可用于获取有关ResultSet对象中列的类型和属性的信息的对象。使用ResultSet 对象的getMetaData()获取
public interface ResultSetMetaData extends Wrapper
方法
| 返回值 | 方法名称 |
|---|---|
| String | getCatalogName(int column) 获取指定列的表目录名称。 |
| String | getColumnClassName(int column) 如果调用方法 ResultSet.getObject 从列中获取值，则返回构造其实例的 Java 类的完全限定名称。 |
| int | getColumnCount() 返回此ResultSet对象中的列数。 |
| int | getColumnDisplaySize(int column) 指示指定列的最大标准宽度，以字符为单位。 |
| String | getColumnLabel(int column) 获取用于打印输出和显示的指定列的建议标题。 |
| String | getColumnName(int column) 获取指定列的名称。 |
| int | getColumnType(int column) 获取指定列的 SQL 类型。 |
| String | getColumnTypeName(int column) 获取指定列的数据库特定的类型名称。 |
| int | getPrecision(int column) 获取指定列的指定列宽。 |
| int | getScale(int column) 获取指定列的小数点右边的位数。 |
| String | getSchemaName(int column) 获取指定列的表模式。 |
| String | getTableName(int column) 获取指定列的名称。 |
| boolean | isAutoIncrement(int column) 指示是否自动为指定列进行编号。 |
| boolean | isCaseSensitive(int column) 指示列的大小写是否有关系。 |
| boolean | isCurrency(int column) 指示指定的列是否是一个哈希代码值。 |
| boolean | isDefinitelyWritable(int column) 指示在指定的列上进行写操作是否明确可以获得成功。 |
| int | isNullable(int column) 指示指定列中的值是否可以为 null。 |
| boolean | isReadOnly(int column) 指示指定的列是否明确不可写入。 |
| boolean | isSearchable(int column) 指示是否可以在 where 子句中使用指定的列。 |
| boolean | isSigned(int column) 指示指定列中的值是否带正负号。 |
| boolean | isWritable(int column) 指示在指定的列上进行写操作是否可以获得成功。 |
使用JDBC
使用JDBC实现业务的步骤：
-
导入数据库驱动jar包
-
注册数据库驱动程序
DriverManager.registerDriver() -
建立和数据库之间的连接
DriverManager.getConnection() conn.createStatement() -
拼写SQL语句
insert into dept values(); -
向数据库发送并执行SQL语句
int count = stat.executeUpdate(sql) -
处理执行结果
-
关闭资源
导入jar包
下载 mysql-connector-java.jar
maven项目的话
<!-- https://mvnrepository.com/artifact/mysql/mysql-connector-java -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.29</version>
</dependency>
数据库连接字符串:
String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/db_name";
String username = "root";
String password = "";
注册驱动
我们Java程序需要通过数据库驱动才能连接到数据库，因此需要注册驱动。 MySQL的驱动的入口类是：com.mysql.cj.jdbc.Driver
使用
public static void main(String[] args) {
try {
//注册驱动
DriverManager.registerDriver(new Driver());
} catch (SQLException e) {
e.printStackTrace();
}
}
还可以使用以下方式注册驱动：（推荐，实现了解耦）
public static void main(String[] args) {
try {
// 此方法返回具有指定类名的此类的实例。
Class.forName("com.mysql.cj.jdbc.Driver");
// Class.forName(Driver.class.getName());
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
获取连接
public static void main(String[] args) throws ClassNotFoundException, SQLException {
Class.forName("com.mysql.cj.jdbc.Driver");
// 获取数据库连接
Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kfm","root","");
System.out.println(connection);
}
实现业务
public static void main(String[] args) {
Connection conn = null;
Statement statement = null;
try {
// 注册数据库驱动
DriverManager.registerDriver(new com.mysql.cj.jdbc.Driver());
String url = "jdbc:mysql://localhost:3306/emp";
// 获取数据库连接对象
conn = DriverManager.getConnection(url, "root", "root");
// 编写 sql 语句
String sql = "insert into dept values(50, '销售', '西安')";
// 获取执行 sql 的对象
statement = conn.createStatement();
// 执行 sql insert / delete / update --> executeUpdate select --> executeQuery()
int i = statement.executeUpdate(sql);
// 判断执行结果
if (i > 0) {
System.out.println("成功");
} else {
System.out.println("失败");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
// 关闭资源
try {
if (statement != null) {
statement.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
try {
if (conn != null) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
实现添加用户业务
数据库：
create table users(
id NUMBER(10) primary key ,
name VARCHAR2(20) ,
password VARCHAR2(20)
)
代码：
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
public class AddUserTest {
public void add() {
Connection connection = null;
Statement statement = null;
try {
// 注册驱动
// DriverManager.registerDriver(new Driver());
// 此方法返回具有指定类名的此类的实例。
Class.forName("com.mysql.cj.jdbc.Driver");
// Class.forName(Driver.class.getName());
// 获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kfm","root","");
System.out.println(connection);
// 获取发送执行sql的对象
statement = connection.createStatement();
// 定义sql语句
String sql = "insert into users(name, password) values('张三', '123456')";
// 执行sql并返回结果
int i = statement.executeUpdate(sql);
System.out.println("数据库中有" + i + "条记录被执行...");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
// 释放资源，关闭数据库连接
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
}
获取主键
public void add() {
Connection connection = null;
Statement statement = null;
try {
// 注册驱动
// DriverManager.registerDriver(new Driver());
// 此方法返回具有指定类名的此类的实例。
Class.forName("com.mysql.cj.jdbc.Driver");
// Class.forName(Driver.class.getName());
// 获取数据库连接
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kfm", "root", "");
System.out.println(connection);
// 获取发送执行sql的对象
statement = connection.createStatement();
// 定义sql语句
String sql = "insert into users(name, password) values('孙六', '123456')";
// 执行sql并返回结果 RETURN_GENERATED_KEYS标志位，表示JDBC驱动必须返回插入的自增主键
int i = statement.executeUpdate(sql, Statement.RETURN_GENERATED_KEYS);
System.out.println("数据库中有" + i + "条记录被执行...");
ResultSet keys = statement.getGeneratedKeys();
if (keys.next()) {
Object object = keys.getObject(1);
System.out.println(object);
}
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
statement.executeUpdate(sql, new String[]{"id"})也可以让jdbc驱动返回插入的自增主键
修改用户信息
@Test
public void update() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
String url = "jdbc:mysql://localhost:3306/kfm";
String username = "root";
String password = "";
// try()-with-resource 里有隐式的finally块关闭资源
try (Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();) {
String sql = "update users set password = '123' where id = 1";
int i = statement.executeUpdate(sql);
if (i > 0) {
System.out.println("修改成功，" + i + "条数据被执行");
} else {
System.out.println("修改失败");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
删除用户
@Test
public void delete() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
String url = "jdbc:mysql://localhost:3306/kfm";
String username = "root";
String password = "";
try (Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();) {
String sql = "delete from users where id = 1";
int i = statement.executeUpdate(sql);
if (i > 0) {
System.out.println("删除成功，" + i + "条数据被执行");
} else {
System.out.println("删除失败");
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
查询所有用户
增删改都会使得数据库中的数据发生变化，这种变化称为数据更新，因此在执行insert、update、delete语句时，Statement对象调用的都是executeUpdate()方法，返回的是影响行数。
@Test
public void selectAll() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
String url = "jdbc:mysql://localhost:3306/kfm";
String username = "root";
String password = "";
String sql = "select * from users";
try (Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);) {
while (resultSet.next()) {
System.out.print("id：" + resultSet.getInt("id") + "\t");
System.out.print("name：" + resultSet.getString("name") + "\t");
System.out.print("password：" + resultSet.getString("password") + "\t");
System.out.println();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
ResultSetMetaData使用
@Test
public void selectAll2() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
String url = "jdbc:mysql://localhost:3306/kfm";
String username = "root";
String password = "";
String sql = "select * from users";
try (Connection connection = DriverManager.getConnection(url, username, password);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql);) {
ResultSetMetaData metaData = resultSet.getMetaData();
// 获取列名称
for (int i = 0; i < metaData.getColumnCount(); i++) {
System.out.print(metaData.getColumnName(i + 1) + "\t");
}
System.out.println();
while (resultSet.next()) {
System.out.print(resultSet.getInt("id") + "\t");
System.out.print(resultSet.getString("name") + "\t");
System.out.print(resultSet.getString("password") + "\t");
System.out.println();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
使用PreparedStatement
PreparedStatement具有高效率的优点：如果用一条SQL语句每次都分析语法、编译、执行，显然效率低下。使用PreparedStatement接口可以将编译过的SQL语句缓存，当再次执行缓存过的SQL语句时，会忽略分析语法和编译的过程，提高了运行效率。在开发中建议使用PreparedStatement接口执行SQL语句。
PreparedStatement具有安全的优点：PreparedStatement可以防止SQL注入。SQL注入是利用SQL语句的漏洞对数据库发送有潜在威胁的SQL语句。
模拟sql注入：
从前台传入用户名(username)和密码(password)验证，使用：
String sql = "select * from users where username = " + username + " and userpass = " + password;
这样有很严重的sql注入问题。比如，我在前台输入的用户名是abc or 1=1 这时不管用户名或密码是否正确都会把数据库中的数据返回回来。
例子：
@Test
public void testLogin() {
String name = "zs";
String password = "123456123' or '1' = '1";
// String password = "123456";
if (login(name, password)) {
System.out.println("登录成功");
} else {
System.out.println("用户名或密码不正确");
}
}
public static boolean login(String name, String pass) {
Connection connection = null;
boolean flag = false;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//建立数据库连接
String url = "jdbc:mysql://localhost:3306/kfm";
String username = "root";
String password = "";
connection = DriverManager.getConnection(url, username, password);
//定义SQL语句
String sql = "select * from users where name = '" + name + "' and password = '" + pass + "'";
//创建执行sql对象
Statement statement = connection.createStatement();
//执行sql,返回影响行数
ResultSet rs = statement.executeQuery(sql);
flag = rs.next();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
//关闭连接
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
使用PreparedStatement：
@Test
public void preInsert() {
String name = "zs";
// String password = "123456123 or 1 = 1";
String password = "123456";
if (login(name, password)) {
System.out.println("登录成功");
} else {
System.out.println("用户名或密码不正确");
}
}
public static boolean login(String name, String pass) {
Connection connection = null;
boolean flag = false;
try {
//注册驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//建立数据库连接
String url = "jdbc:mysql://localhost:3306/kfm";
String username = "root";
String password = "";
connection = DriverManager.getConnection(url, username, password);
// 定义SQL语句 ? 是占位符
String sql = "select * from users where name = ? and password = ?;";
// 创建执行sql对象
PreparedStatement prepareStatement = connection.prepareStatement(sql);
// 为占位符赋值
prepareStatement.setObject(1, name);
prepareStatement.setObject(2, pass);
// 执行sql
ResultSet rs = prepareStatement.executeQuery();
flag = rs.next();
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
try {
//关闭连接
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
return flag;
}
推荐使用PreparedStatement对象
数据库设计
步骤
软件设计的步骤大致上可以分为：
需求分析、概要设计、详细设计、代码编写、运行测试、部署发行
数据库是在代码编写前完成的
数据库设计可分为这几个过程
需求分析，概念模型，物理模型，运行验证
创建概念模型
-
创建概念模型
-
将概念模型转换为物理模型
-
将物理模型转换为
SQL脚本
数据库设计范式
数据库的设计有五大设计范式。常用的有三大设计范式，称之为第一范式(1NF)，第二范式(2NF)，第三范式(3NF)，他们是逐步为严格的，满足第二范式，就必须满足先满足第一范式。满足第三范式时就必须首先满足第二范式
第一范式
第一范式要求单个表中每个列必须是原子列（即每一个列都是不可再分的最小数据单元），列不存在重复属性，每个实体的属性也不存在多个数据项。
1.原子列
2.不出现重复属性
3.不允许出现多个数据项
第二范式
第二范式是在满足第一范式的基础之上，要求数据表里的所有数据都要和该数据表的主键有完全依赖关系。
第三范式
第三范式是在满足第二范式的基础之上，每一个非主键列都直接依赖主键列，不依赖其他非主键列，即数据库中不能存在传递函数的依赖关系。
范式的优缺点
优点：
- 范式化的数据库更新起来更加的快；
- 范式化之后只有很少的重复数据，只需要修改更少的数据；
- 范式化的表更小，可以在内存中直接执行；
- 很少的冗余数据，在查询时候需要更少的distinct后者group by语句。
缺点：
- 范式化的设计会产生更多的表；
- 在查询的时候经常需要很多的表连接查询，导致查询性能降低；
事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
-
**原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
-
**一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
-
**隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，
- 包括读未提交（Read uncommitted）、
- 读提交（read committed）、
- 可重复读（repeatable read）
- 串行化（Serializable）。
-
**持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
# 在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。
[begin] / start transaction; # 开启事务
commit; # 提交事务
rollback; # 回滚事务
select @@autocommit; -- 查看事务提交方式 1 表示自动提交 0 表示手动提交
SET AUTOCOMMIT = 0; -- 用来禁止使用当前会话的自动提交
手动提交事务使用步骤：
第1种情况：开启事务 ‐> 执行SQL语句 ‐> 成功 ‐> 提交事务
第2种情况：开启事务 ‐> 执行SQL语句 ‐> 失败 ‐> 回滚事务
事务控制语句：
手动提交事务使用步骤：
第1种情况：开启事务 ‐> 执行SQL语句 ‐> 成功 ‐> 提交事务
第2种情况：开启事务 ‐> 执行SQL语句 ‐> 失败 ‐> 回滚事务
-
START TRANSACTION 显式地开启一个事务；
-
COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
-
ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
-
SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
-
RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
-
ROLLBACK TO identifier 把事务回滚到标记点；
-
SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
-
READ UNCOMMITTED:读未提交 会导致脏读 -
READ COMMITTED:读已提交 不可重复读 -
REPEATABLE READ: 可重复读 -
SERIALIZABLE:串行化# 查询隔离级别 show variables like '%isolation%'; ‐‐ 或 select @@transaction_isolation; -- 设置隔离级别 set global transaction isolation level 隔离级别; ‐‐ 如: set global transaction isolation level Serializable;
-
事务隔离级别
读未提交 （Read uncommitted）
读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。
事例：老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的账户，但是事务还没有提交，就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。
分析：实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。他看到的是老板还没提交事务时的数据。这就是脏读。因此，在这种隔离级别下，查询是不会加锁的，也由于查询的不加锁，所以这种隔离级别的一致性是最差的，可能会产生“脏读”、“不可重复读”、“幻读”。如无特殊情况，基本是不会使用这种隔离级别的。
那怎么解决脏读呢？Read committed！读提交，能解决脏读问题。
读提交（Read Committed）
读提交，顾名思义，就是只能读到已经提交了的内容
事例：程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…
分析：这就是读提交，若有事务对数据进行更新（UPDATE）操作时，读操作事务要等待这个更新操作事务提交后才能读取数据，可以解决脏读问题。但在这个事例中，出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读。
这是各种系统中最常用的一种隔离级别，也是SQL Server和Oracle的默认隔离级别。
可重复读(REPEATABLE Read)
可重复读，顾名思义，就是专门针对“不可重复读”这种情况而制定的隔离级别，自然，它就可以有效的避免“不可重复读”。而它也是MySql的默认隔离级别。
事例：程序员拿着卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。
分析：重复读可以解决不可重复读问题。应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。
什么时候会出现幻读？
事例：程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。
在这个级别下，普通的查询同样是使用的“快照读”，但是，和“读提交”不同的是，当事务启动时，就不允许进行“修改操作（Update）”了，而“不可重复读”恰恰是因为两次读取之间进行了数据的修改，因此，“可重复读”能够有效的避免“不可重复读”，但却避免不了“幻读”，因为幻读是由于“插入或者删除操作（Insert or Delete）”而产生的。
那怎么解决幻读问题？Serializable！
序列化Serializable
这是数据库最高的隔离级别，这种级别下，事务“串行化顺序执行”，也就是一个一个排队执行。这种级别下，“脏读”、“不可重复读”、“幻读”都可以被避免，但是执行效率奇差，性能开销也最大，所以基本没人会用。
大多数数据库默认的事务隔离级别是Read committed，比如Sql Server , Oracle。Mysql的默认隔离级别是Repeatable read。
第一范式：原子列，不允许重复属性，一个列不允许多个值
第二范式：表中的所有数据应该依赖于主键
第三范式：表中的非主键列不应该存在相互依赖关系
事务：原子性、一致性、隔离性、持久性
隔离级别：
读未提交：脏读、不可重复读、幻读
读已提交：解决脏读问题 Oracle sqlServer
可重复读：解决了不可重复读问题，但是不能幻读问题 mysql
串行化：一个事务另外一个事务才能执行
jdbc中事务管理
模拟转账操作，小王给媳妇转账，创建account数据表
create table account(
id int primary key auto_increment,
name varchar(20),
balance double comment '账户余额'
)
insert into account(name, balance) values('小王', 8001.89),('小王嫂', 9000.00);
/**
* 不在事务中进行转账业务
*/
public static void testTransaction() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
PreparedStatement preparedStatement = null;
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kfm", "root", "")) {
// 小王转出8000元
String sql = "update account set balance = balance - ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 8000);
preparedStatement.setObject(2, "小王");
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("小王转出成功！");
}
// 小王嫂账号增加8000元
sql = "update account set balance = balance + ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 8000);
preparedStatement.setObject(2, "小王嫂");
int i1 = preparedStatement.executeUpdate();
if (i1 > 0) {
System.out.println("小王嫂收到转账");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
// 释放资源
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
上述代码中，核心是执行两条SQL语句，第一条小王减少8000的sql语句，第二条是小王嫂余额增加8000的sql语句。如果这两条sql都执行成功，就完成了转账业务。但是如果第一条执行成功，第二条语句执行失败，转账业务就失败了。为了模拟第二条转账失败，我们人为制造一个异常，特意在第二条语句执行之前产生异常。
/**
* 不在事务中进行转账业务
*/
public static void testTransaction() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
PreparedStatement preparedStatement = null;
try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kfm", "root", "")) {
// 小王转出8000元
String sql = "update account set balance = balance - ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 8000);
preparedStatement.setObject(2, "小王");
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("小王转出成功！");
}
// 模拟产生异常，使第二条sql不能执行
int num = 1 / 0;
// 小王嫂账号增加8000元
sql = "update account set balance = balance + ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 8000);
preparedStatement.setObject(2, "小王嫂");
int i1 = preparedStatement.executeUpdate();
if (i1 > 0) {
System.out.println("小王嫂收到转账");
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
// 释放资源
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
查询account表，会发现小王余额减少了，但是小王嫂余额并没有增加，从查询结果可以知道分析总金额丢失了8000元。
因此，要想实现转账成功，就必须要求两条sql语句要么都执行，要么都不执行，因此需要事务来解决这个问题。事务是访问并可能更新数据库中各种数据项的一个程序执行单元。在关系数据库中，一个事务可以是一条sql语句，或者多条sql语句。事务的执行是由高级编程语言来执行的。在java中使用JDBC来操作事务。
- 开始事务：Connection对象的setAutoCommit(false)实现手动管理事务，setAutoCommit(true)实现自动管理事务，默认为自动提交事务。
- 执行事务：Statement接口和PreparedStatement接口调用executeUpdate()方法时就开始执行事务了。
- 提交事务：Connection对象的commit()方法提交事务。
- 回滚事务：Connection对象的rollback()方法回滚事务
/**
* 在事务中进行转账业务
*/
public static void testTransaction2() {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
PreparedStatement preparedStatement = null;
Connection connection = null;
try {
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/kfm", "root", "");
// 实现手动提交事务，默认是自动提交的
connection.setAutoCommit(false);
// 小王转出8000元
String sql = "update account set balance = balance - ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 8000);
preparedStatement.setObject(2, "小王");
// 执行事务
int i = preparedStatement.executeUpdate();
if (i > 0) {
System.out.println("小王转出成功！");
}
// 模拟产生异常，使第二条sql不能执行
int num = 1 / 0;
// 小王嫂账号增加8000元
sql = "update account set balance = balance + ? where name = ?";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, 8000);
preparedStatement.setObject(2, "小王嫂");
// 执行事务
int i1 = preparedStatement.executeUpdate();
if (i1 > 0) {
System.out.println("小王嫂收到转账");
}
// 提交事务
connection.commit();
} catch (SQLException | ArithmeticException e) {
try {
// 如果发生异常，回滚事务
connection.rollback();
System.out.println("转账业务失败，款项已原路退回");
} catch (SQLException ex) {
throw new RuntimeException(ex);
}
} finally {
// 释放资源
if (preparedStatement != null) {
try {
preparedStatement.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
if (connection != null) {
try {
connection.close();
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
}
-
开始事务、执行事务、提交事务都在try代码块部分，只有回滚事务在catch代码块部分。
-
当事务中所有sql语句都正确执行。执行提交事务语句，事务提交后事务就结束了，事务恢复到自动事务状态。
-
当事务中任意一条sql语句执行出错，会抛出异常，程序进入catch块，执行回滚事务，事务回滚后也一样就结束了，事务恢复到自动提交事务的状态。
-
上面的代码，小王将8000转出后，程序发生异常，回滚事务，回滚后所有的操作会撤销，从而保证同一个事务中多条语句要么都执行，要么都不执行。执行后数据库中两个人的余额都没有发生变化。
同一个事务中所有sql语句执行时，必须使用同一个Connection对象。
存储过程
如果实现用户的某些需求时，需要编写一组复杂的SQL语句才能实现，那么可以将这组复杂的SQL语句集编写在数据库中，由JDBC调用来执行这组SQL语句。把编写在数据库中的SQL语句集称为存储过程。
存储过程（PROCEDURE）是事先经过编译并存储在数据库中的一段 SQL 语句的集合。调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
简单说就是一组SQL语句集，功能强大，可以实现一些比较复杂的逻辑功能，类似于JAVA语言中的方法；存储过程跟触发器有点类似，都是一组SQL集，但是存储过程是主动调用的，且功能比触发器更加强大，触发器是某件事触发后自动调用；
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
定义
创建存储过程的语法格式：
create procedure pro_name (参数1,参数2,...)
begin
存储过程代码块;
end;
# 存储过程的参数分为in\out\inout三种类型
# in 输入参数，表示该参数的值必须由调用程序指定
# out 输出参数，表示该参数的值经存储过程计算后，将out参数的计算结果返回给调用程序
# inout 既是输入参数、又是输出参数，表示该参数的值既可以由调用程序指定，也可以将inout参数的计算结果返回给调用程序
# 存储过程中的语句必须包含在begin和end之间
-- 删除存储过程
DROP PROCEDURE [IF EXISTS] `proc_adder`;
-- 计算 a + b
CREATE PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
if a is null then
set a = 0;
end if;
if b is null then
set b = 0;
end if;
set sum = a + b;
# select a + b into sum
END;
或者
DROP PROCEDURE IF EXISTS `proc_adder`;
DELIMITER $$
CREATE PROCEDURE `proc_adder`(IN a int, IN b int, OUT sum int)
BEGIN
if a is null then set a = 0;
end if;
if b is null then set b = 0;
end if;
set sum = a + b;
END$$
DELIMITER;
其中，$$命令将语句的结束符从;修改成其他符号，这里使用的是$$，$$也可以使用其他符号代替，这样在过程和函数中的;就不会被 MySQL 解释成语句的结束而提示错误。
调用
调用存储过程的语法格式
call 存储过程名(参数);
#设置值
set @b = 5;
#调用
call proc_adder(2, @b, @sum);
#显示
select @sum ;
存储过程中的控制语句
if
DROP PROCEDURE IF EXISTS `proc_if`;
CREATE PROCEDURE `proc_if`(IN type int)
BEGIN
# 使用declare声明变量
DECLARE c varchar(500);
IF type = 0 THEN
set c = 'param is 0';
ELSEIF type = 1 THEN
set c = 'param is 1';
ELSE
set c = 'param is others, not 0 or 1';
END IF;
select c;
END;
验证：
set @n = 1;
call proc_if(@n);
CASE
DROP PROCEDURE IF EXISTS `proc_case`;
DELIMITER $$
CREATE PROCEDURE `proc_case`(IN type int)
BEGIN
DECLARE c varchar(500);
CASE type
WHEN 0 THEN
set c = 'param is 0';
WHEN 1 THEN
set c = 'param is 1';
ELSE
set c = 'param is others, not 0 or 1';
END CASE;
select c;
END$$
while
DROP PROCEDURE IF EXISTS `proc_while`;
CREATE PROCEDURE `proc_while`(IN n int)
BEGIN
DECLARE i int;
DECLARE s int;
SET i = 0;
SET s = 0;
WHILE i <= n DO
set s = s + i;
set i = i + 1;
END WHILE;
SELECT s;
END
JDBC实现过程的调用
创建过程：
-- 创建一个存储过程 完成 计算某个员工的年薪
CREATE PROCEDURE cal_salary(IN `no` int)
BEGIN
select sal * 12 + IFNULL(comm, 0) 'nx' from emp where empno = `no`;
END;
Java调用:
public static void main(String[] args) throws SQLException {
try {
Class.forName("com.mysql.cj.jdbc.Driver");
} catch (ClassNotFoundException e) {
throw new RuntimeException(e);
}
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/manager", "root", "");
// JDBC中调用存储过程 CallableStatement
// CallableStatement callableStatement = conn.prepareCall("{call cal_salary(?)}");
// callableStatement.setInt(1, 7788);
// ResultSet resultSet = callableStatement.executeQuery();
// if (resultSet.next()) {
// System.out.println(resultSet.getObject(1));
// }
CallableStatement callableStatement = conn.prepareCall("{call `sum`(?, ?, ?)}");
callableStatement.setInt(1, 2);
callableStatement.setInt(2, 4);
// 注入 OUT类型的参数 registerOutParameter(, 类型)
callableStatement.registerOutParameter(3, Types.INTEGER);
callableStatement.execute();
// getXXX(3)
int sum = callableStatement.getInt(3);
System.out.println(sum);
}
触发器
什么是触发器
触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器的特点
触发器具有以下特点：
- 与表相关联
触发器定义在特定的表上，这个表称为触发器表。
- 自动激活触发器
当对表中的数据执行 INSERT、UPDATE 或 DELETE 操作时，如果对表上的这个特定操作定义了触发器，该触发器自动执行，这是不可撤销的。
- 不能直接调用
与存储过程不同，触发器不能被直接调用，也不能传递或接受参数。
- 作为事务的一部分
触发器与激活触发器的语句一起做为对一个单一的事务来对待，可以从触发器中的任何位置回滚。
查看触发器
SHOW TRIGGERS;
SHOW TRIGGERS\G;
-- 在triggers表中查询触发器
SELECT * FROM information_schema.triggers WHERE trigger_name= '触发器名称';
SELECT * FROM information_schema.triggers \G
创建触发器
触发器经常用于加强数据的完整性约束和业务规则等。 触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
语法：
CREATE TRIGGER 触发器名称 触发时机 触发事件
ON 表名称
FOR EACH ROW
BEGIN
语句
END;
- 触发器名称是用来表示触发器的，由用户自定义
- 触发时机有两种值 after 和 before
- 触发事件有三种值 insert / update / delete
- 表名称标识建立触发器的表名，即在哪张表上建立触发器
- FOR EACH ROW 这句表示只要满足触发器触发条件，触发器都会被执行，也就是说带上这个参数后，触发器将监测每一行对关联表操作的代码，一旦符合条件，触发器就会被触发。
- 语句 是触发器程序体，触发器程序可以使用begin和end作为开始和结束，中间包含多条语句
UserInfo表中存储了用户名和密码，但用户的属性中还应包括年龄、电话、性别等，这些属性是不可或缺的。由于用户每次登录都未必使用年龄、电话、性别属性，多数用户是在完善个人信息时使用这些属性，为此将用户信息设计成UserInfo表和UserDetails表，UserInfo表中存储用户每次登陆都需要的属性，UserDetails表中存储用户每次登陆不需要的属性，并将两表设置为一对一的关系。UserDetails表的参考代码如下：
CREATE TABLE if not exists `UserInfo`(
id INT AUTO_INCREMENT PRIMARY KEY COMMENT '编号',
userName VARCHAR(20) comment '用户名',
userPass VARCHAR(20) comment '密码'
);
CREATE TABLE if not exists `UserDetails`(
id INT PRIMARY KEY COMMENT '编号',
age INT comment '年龄',
tel VARCHAR(20) comment '电话',
gender VARCHAR(20) comment '性别'
);
-- 添加外键约束
ALTER TABLE UserDetails ADD CONSTRAINT fk_UserInfo_UserDetails_Id FOREIGN KEY (id) REFERENCES UserInfo(id);
地点： UserInfo 事件：insert 时间：之后 after 触发器要做的事情userDetails中添加一条记录
CREATE TRIGGER add_details after insert -- 会产生一个NEW表 NEW.id
on UserInfo
BEGIN
insert into UserDetails(id) values(NEW.id);
END
如上图所示：在实现用户注册业务时，首先向UserInfo表中添加一条用户信息，接下来获取UserInfo表新增记录的主键值，最后在向UserDetails表中添加一条用户详细信息，UserDetails表中的id的值就是UserInfo表中新增记录的id的值。
在编写程序时，需要分别向UserInfo表和UserDetails表添加记录，而且这两个表的记录必须在同一个事务中执行。
使用触发器实现注册业务时，对UserInfo表实现添加用户信息，添加成功后自动激活触发器，由触发器向UserDetails表中添加用户详细信息，完成注册业务。
在触发器中可以使用两个特殊的临时表，即OLD表和NEW表。OLD表用于存储UPDATE语句和DELETE语句中影响的记录，NEW表用于存储INSERT语句和UPDATE语句影响的记录。
当用户注册时，将用户信息添加到UserInfo表，然后MYSQL会自动创建NEW表，NEW表的结构和UserInfo表结构完全相同，NEW表里面存储的记录就是向UserInfo表中新增的记录，如下图：
当删除用户时，将被删除用户的记录从UserInfo表中删除，删除成功后MYSQL会自动创建OLD表，OLD表的结构与UserInfo表结构完全相同，OLD表里面存储的记录就是被删除的UserInfo表中的记录，如下图：
当更新用户信息时，MYSQL会自动创建NEW表和OLD表，OLD表和NEW表的结构与UserInfo表的结构完全相同。OLD表中存储的是更新前的UserInfo表中的记录，NEW表中存储的是更新后的UserInfo表中的记录。例如将林冲的密码100更改为10，如下图所示：
对于INSERT语句,只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以在和NEW以及OLD同时使用。
使用触发器实现用户注册业务
DELIMITER $$
CREATE TRIGGER tr_register AFTER INSERT
ON userinfo
FOR EACH ROW
BEGIN
INSERT INTO userdetails(id) VALUES(NEW.id);
END $$
DELIMITER
新增一条数据就会触发tr_register
insert into userinfo (userName, userPass) values('张三', '123456');
在某张表的触发器中，不能再进行对此表的操作。比如：给
Userinfo表添加一个触发器tr_register,在此触发器中执行内容不能是再对Userinfo表进行操作了。否则在触发器被激活时，会报1442错误。
删除触发器
Drop trigger [触发器名称];
特别提示：我们不能对已经创建好的触发器进行修改。如果你想修改，只能先删除，再重新创建。
CREATE TRIGGER insert_log AFTER INSERT
ON books
FOR EACH ROW
BEGIN
insert into log(description) values(CONCAT(CONCAT("新增《",NEW.book_title), "》"));
END
示例
模拟银行操作，设置在INSERT 表之前检测操作者是否输入错误数据、在 UPDATE 时，记录操作者的行为 log ，以及在DELETE 时，判断删除的信息是否符合删除规则。 这三类操作都可以使用 MySQL 触发器来实现。
BEFORE INSERT： 在插入数据前，检测插入数据是否符合业务逻辑，如不符合返回错误信息。AFTER INSERT： 在表 A 创建新账户后，将创建成功信息自动写入表 B 中。BEFORE UPDATE：在更新数据前，检测更新数据是否符合业务逻辑，如不符合返回错误信息。AFTER UPDATE：在更新数据后，将操作行为记录在 log 中BEFORE DELETE：在删除数据前，检查是否有关联数据，如有，停止删除操作。AFTER DELETE：删除表 A 信息后，自动删除表 B 中与表 A 相关联的信息。
SIGNAL SQLSTATE是SQL中用于引发自定义异常或错误的一种方式。它通常与存储过程或触发器一起使用，用于在特定条件下向应用程序报告错误或异常情况；MESSAGE_TEXT可以设置错误提示信息
-- 创建客户表，记录银行客户的信息
create table customers(
customer_id BIGINT PRIMARY KEY,
customer_name VARCHAR(50),
level VARCHAR(50)
);
-- 模拟数据
Insert into customers (customer_id, customer_name, level )values('1','Jack Ma','BASIC'),('2','Robin Li','BASIC'),('3','Pony Ma','VIP');
-- 创建customer_status，用于保存 customers 表中客户的备注信息
Create table customer_status(
customer_id BIGINT PRIMARY KEY,
status_notes VARCHAR(50)
);
-- 创建一个 sales 表，这个表与 customer_id 关联。保存与客户有关的销售数据。
Create table sales(
sales_id BIGINT PRIMARY KEY,
customer_id BIGINT,
sales_amount DOUBLE
);
-- 创建一个 audit_log 表，用来记录操作员操作「卡拉云银行」客户管理系统时的操作行为。方便管理员在发生问题时，有 log 可查。
Create table audit_log(
log_id BIGINT PRIMARY KEY AUTO_INCREMENT,
sales_id BIGINT, previous_amount DOUBLE,
new_amount DOUBLE,
updated_by VARCHAR(50),
updated_on DATETIME
);
CREATE TRIGGER insert_log AFTER INSERT
ON books
FOR EACH ROW
BEGIN
insert into log(description) values(CONCAT("新增《",NEW.book_title, "》"));
END
DROP TRIGGER insert_log
insert into books(book_title, publication_date, author, price, quantity) values("JavaScript", "2023-10-26", "马老师",76.6, 79)
-- 修改图书 然后存储log 修改的图书名称 原数量 新数量
CREATE TRIGGER update_log AFTER UPDATE
ON books
FOR EACH ROW
BEGIN
insert into log(description) values(concat("修改", NEW.book_title, "的数量，原数量是", OLD.quantity, ",修改之后是：", NEW.quantity));
-- update books set quantity = 20 where id = 1;
-- 修改图书的语句 OLD 临时表存储的修改之前的值 NEW临时表存储的是修改之后的值
END;
update books set quantity = 20 where id = 1;
-- 删除图书 然后存储 删除的图书名称
CREATE TRIGGER delete_log AFTER DELETE
ON books
FOR EACH ROW
BEGIN
insert into log(description) values(concat("删除《" , OLD.book_title, "》"));
END;
delete from books where id = 1;
delete books;
CREATE TRIGGER checkBalance BEFORE INSERT
ON account
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
-- TODO 抛出异常
SIGNAL SQLSTATE '95270'
set MESSAGE_TEXT = "余额不能小于0";
END IF;
END;
CREATE TRIGGER updateBalance BEFORE UPDATE
ON account
FOR EACH ROW
BEGIN
IF NEW.balance < 0 THEN
-- TODO 抛出异常
SIGNAL SQLSTATE '95271'
set MESSAGE_TEXT = "余额不能小于0";
END IF;
END;
CREATE TRIGGER deleteBalance BEFORE DELETE
ON account
FOR EACH ROW
BEGIN
IF OLD.id = 1 THEN
-- TODO 抛出异常
SIGNAL SQLSTATE '95270'
set MESSAGE_TEXT = "骨灰级用户，不能删除";
END IF;
END;
delete from account where id = 1;
update account set balance = -1 where id = 1;
insert into account values (11, -2000 ,'小蒋')
DROP TRIGGER checkBalance
CREATE TRIGGER deleteAll AFTER DELETE
ON userInfo
FOR EACH ROW
BEGIN
delete from userDetails where id = OLD.id;
END;
-- 删除外键
ALTER TABLE UserDetails DROP FOREIGN KEY details_id;
delete from userInfo where id = 1;
- 对任何写入系统的数据都应该提前检测，以防止错误的信息被写进去。使用BEFORE INSERT 触发器来实现
DELIMITER $$
CREATE TRIGGER validate_sales_amount
BEFORE INSERT
ON sales
FOR EACH ROW
BEGIN
IF NEW.sales_amount > 10000 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = "你输入的销售总额超过 10000 元。";
END IF;
END$$
DELIMITER ;
-- 测试
Insert into sales(sales_id, customer_id, sales_amount) values('1','1','11000');
- 执行
insert之后，使用触发器将不同的值保存到不同的表中。使用AFTER INSERT触发器
DELIMITER $$
CREATE TRIGGER customer_status_records
AFTER INSERT
ON customers
FOR EACH ROW
begin
Insert into customer_status(customer_id, status_notes) VALUES(NEW.customer_id, '账户创建成功');
end$$
DELIMITER ;
-- 测试
Insert into customers (customer_id, customer_name, level )values('4','Xing Wang','VIP');
# AFTER INSERT 特别适合这种状态变更的关联写入操作。比如开户、暂停、注销等各类状态变更。
BEFORE UPDATE触发器，可以使用BEFORE UPDATE触发器在更新数据之前，先做一次业务逻辑检测，避免发生误操作
DELIMITER $$
CREATE TRIGGER validate_customer_level
BEFORE UPDATE
ON customers
FOR EACH ROW
begin
IF OLD.level='VIP' THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'VIP 级别客户不能降级为普通级别客户';
END IF;
end$$
DELIMITER ;
-- 测试
Update customers set level='BASIC' where customer_id='3';
AFTER UPDATE多用于 log 记录，在管理系统多操作者使用的环境中，管理员需要设置操作 log 记录，以便在出问题时，可以查看操作者对表编辑的操作，可追根溯源。
DELIMITER $$
CREATE TRIGGER log_sales_updates
AFTER UPDATE
ON sales
FOR EACH ROW
begin
Insert into audit_log(sales_id, previous_amount, new_amount, updated_by, updated_on) VALUES (NEW.sales_id,OLD.sales_amount, NEW.sales_amount,(SELECT USER()), NOW() );
end$$
DELIMITER ;
-- 测试
Update sales set sales_amount='9000' where sales_id='5';
BEFORE DELETE触发器会在DELETE语句执行之前调用。
DELIMITER $$
CREATE TRIGGER validate_related_records
BEFORE DELETE
ON customers
FOR EACH ROW
begin
IF OLD.customer_id in (select customer_id from sales) THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '这位客户有相关联的销售记录，不能删除。';
END IF;
end$$
DELIMITER ;
-- 测试
Delete from customers where customer_id='2';
AFTER DELETE，一旦记录被成功删除，这个触发器就会被激活。
DELIMITER $$
CREATE TRIGGER delete_related_info
AFTER DELETE
ON sales
FOR EACH ROW
begin
Delete from customers where customer_id=OLD.customer_id;
end$$
DELIMITER ;
-- 测试
Delete from sales where customer_id='2';
问题一： 大型系统必须要用存储过程和触发器吗？
回答1：
我们先要弄清楚二个问题：
1.什么是大型系统？
2.你讨论的是什么领域的应用，可以大致分为二种：互联网、企业内部
接下来给你举一些例子：
1.SAP、ERP等企业级别应用
一般情况下，会使用存储过程和触发器，减少开发成本，毕竟其业务逻辑修改频繁，而且为通用，很多时候会把一些业务逻辑编写成存储过程，像Oracle会写成包，比存储过程更强大。
另外一个原因是服务器的负载是可控，也即系统的访问人数首先是可控的，没有那么大，而且这些数据又非常关键，为此往往使用的设备也比较好，多用存储柜子支撑数据库。
2.另外一类互联网行业的
比如淘宝、知呼、微博等，数据库的压力是非常大的，也往往会最容易成为瓶颈，而且多用PC服务器支撑，用户量的增速是不可控的，同时在线访问的用户量也是不可控的，为此肯定会把业务逻辑放到其他语言的代码层，而且可以借助一些LVS等类型软硬件做负载均衡，以及平滑增减Web层的服务器，从而达到线性的增减而支持大规模的访问。
所以不管你的这个系统是否庞大，首先要分业务支持的对象，系统最可能容易出现瓶颈的地方在那？
当然也不是说互联网行业的应用就绝对不用存储过程，这个也不对，曾在阿里做的Oracle迁移MySQL系统确实用了，因为历史的原因，另外还有一些新系统也有用，比如晚上进行定期的数据统计的一些操作，不过有量上的控制。存储过程是好东西，要分场景，分业务类型来用就可以把握好。
回答2：
肯定不能一刀切的说能用或者不能用，不同类型的系统、不同的规模、不同的历史原因都会有不同的解决方案。
一般情况下，Web应用的瓶颈常在DB上，所以会尽可能的减少DB做的事情，把耗时的服务做成Scale Out，这种情况下，肯定不会使用存储过程；而如果只是一般的应用，DB没有性能上的问题，在适当的场景下，也可以使用存储过程。
至于触发器，我是知道有这东西但从来没用过。我希望风险可控，遇到问题能够快速的找到原因，尽可能不会去使用触发器。
回答3：
1.PLSQL可以大大降低parse/exec 百分比；
2.存储过程可以自动完成静态SQL variable bind；
3.存储过程大大减少了JDBC网络传输与交互，速度快；
4.oracle 中存储过程内部commit为异步写，一定程度上减少了等redo日志落地时间；
5.存储过程最大问题就是给数据库开发工作压力太大，另外架构升级时候会比较难解耦；
6.触发器不推荐使用，触发操作能在业务层解决就在业务层解决，否则很难维护，而且容易产生死锁。
问题二：为什么大家不推荐使用Mysql触发器而用存储过程
回答1：
1.存储过程和触发器二者是有很大的联系的，我的一般理解就是触发器是一个隐藏的存储过程，因为它不需要参数，不需要显示调用，往往在你不知情的情况下已经做了很多操作。从这个角度来说，由于是隐藏的，无形中增加了系统的复杂性，非DBA人员理解起来数据库就会有困难，因为它不执行根本感觉不到它的存在。
2.再有，涉及到复杂的逻辑的时候，触发器的嵌套是避免不了的，如果再涉及几个存储过程，再加上事务等等，很容易出现死锁现象，再调试的时候也会经常性的从一个触发器转到另外一个，级联关系的不断追溯，很容易使人头大。其实，从性能上，触发器并没有提升多少性能，只是从代码上来说，可能在coding的时候很容易实现业务，所以我的观点是：摒弃触发器！触发器的功能基本都可以用存储过程来实现。
3.在编码中存储过程显示调用很容易阅读代码，触发器隐式调用容易被忽略。
4.存储过程的致命伤在于移植性，存储过程不能跨库移植，比如事先是在mysql数据库的存储过程，考虑性能要移植到oracle上面那么所有的存储过程都需要被重写一遍。
回答2：
这种东西只有在并发不高的项目，管理系统中用。如果是面向用户的高并发应用，都不要使用。
触发器和存储过程本身难以开发和维护，不能高效移植。触发器完全可以用事务替代。存储过程可以用后端脚本替代。
回答3：
我觉得来自两方面的因素：
1.存储过程需要显式调用，意思是阅读源码的时候你能知道存储过程的存在，而触发器必须在数据库端才能看到，容易被忽略。
2.Mysql的触发器本身不是很好，比如after delete无法链式反应的问题。
我认为性能上其实还是触发器占优势的，但是基于以上原因不受青睐。
索引
什么是索引
首先，先假设有一张表，表中有100万条数据，这100万条数据在硬盘上是存储在数据页上的，一页数据大小为16K，存储100万条数据需要很多数据页，如下图所示。数据页上存储了数据，例如“数据页1”上存储了3和5。
其中有一条数据是id=‘79’，如果要查询这条数据，其SQL是 SELECT * FROM 表名称 WHERE id = 79。mysql需要扫描全表来查找id=79的记录。全表扫描就是从“数据页1”开始，向后逐页查询。对于少量的数据，查询的速度会很快，但是，当随着数据量的增加，性能会急剧下降。100万条数据逐页查询的时间是无法被用户接受的。
索引类似于书的目录，在一本书前面加上目录，查找内容时不必逐页翻阅就能够快速地找到所需的内容。借助索引，执行查询时不必扫描整个表就能够快速地找到所需要的数据。
一本书的内容占用很多页数，书的目录也占用书的页数，数据库中的数据页相当于书的内容页，数据页占用磁盘空间，数据库中的索引相当于书的目录页，索引也是要占用磁盘空间的，如下图所示。
对数据库中的数据查询和增删改的比例通常是10：1，也就是说对数据库操作11次，大约有10次是查询，1次是增删改，因此提升查询效率、缩短程序响应的时间是非常重要的。如果用户执行了一个查询操作，需要等待8秒中才能看到结果，是用户无法接受的。
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单
索引是MYSQL的灵魂
索引的数据结构
**不同的存储引擎支持的索引类型也不一样 **
InnoDB ： 支持 B-tree、Full-text 等索引，不支持 Hash索引；
MyISAM ： 支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory ： 支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB ： 支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive ： 不支持 B-tree、Hash、Full-text 等索引；
索引是如何提高查询效率的呢？MySQL数据库的索引使用了B-Tree数据结构存储数据，来提高查询效率的。了解B-Tree数据结构有助于理解索引提高查询效率的原理。
下图是B-Tree数据结构的示意图，分为上下两部分，下部分数据页，上部分是索引页。数据页中存储的是表中的记录，索引页存储的是索引数据。
使用索引
如果没有索引，mysql在查找相关行时就必须逐行扫描整个表，如果这个表在你筛选的列上有索引，mysql就可以快速找到大数据文件中的行而无需扫描整个文件。
MySQL可以使用索引来筛选where、ORDER by 和group by 子句中的行，也可以使用索引来连接表。如果一个列上有很多索引，mysql会选择给出一个最多筛选行的索引。
语法是：
CREATE INDEX 索引名称 ON 表名称(字段名)
创建索引
CREATE INDEX ix_UserInfo_UserName ON userinfo(userName);
修改表（添加索引）
ALTER table userinfo ADD INDEX ix_UserInfo_UserName(userName)
删除索引
DROP INDEX index_name ON table_name;
测试索引
EXPLAIN SELECT * FROM userinfo WHERE userName = '林冲';
其中，EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
Explain信息详解查看mysql文档（优化分类下）。
只有在表中的记录数量很大多时，创建索引才有意义。如果数据库中只有少量数据是没有必要创建索引的。就像一本书只有5页，就没有必要做目录一样。
MYSQL执行查询的方式是影响数据库性能的主要因素之一，你可以使用EXPLAIN命令来验证MYSQL的执行计划。
查看索引
show index from 表名;
索引的分类
索引也有多种，下面简要介绍MySQL索引的分类。
- 普通索引
这是最基本的索引，也是我们大多数情况下用到的索引。
语法：CREATE INDEX index_name ON table(column(length))
length 是可选的，表示索引的长度（如果不指定长度，则使用整个列）。
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
- 唯一索引
与普通索引类似，不同的就是索引列的值必须唯一，允许有空值。
语法：CREATE UNIQUE INDEX indexName ON table(column(length))
- 主键索引
它是一种特殊的唯一索引，不允许有空值。数据库管理系统自动为主键列添加主键索引。
- 全文索引
全文索引（FULLTEXT索引）是对CHAR、VARCHAR或TEXT列创建的索引，通常应用于对大文本字段。
例如在文章内容列搜索，就需要对文章内容列创建全文索引。
- 组合索引
平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步提升MySQL的效率，就要考虑建立组合索引。可根据查询的条件组合创建组合索引。
语法：ALTER TABLE article ADD INDEX index_title_time (title(50),time(10))。
索引选取类型
-
越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。
-
简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。
-
尽量避免NULL：应该指定列为NOT nuLL,在MySQL中， 含有空值的列很难进行查询优化，它们使得索引、索引的统计信息以及比较运算更加复杂
什么场景不适合创建索引
- 对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
- 对于那些定义为text, image和bit数据类型的列不应该增加索引。这些列的数据量要么相当大，要么取值很少。
- 当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
like语句不应该创建索引
什么样的字段适合创建索引
- 表的主键、外键必须有索引；主键是唯一的，而且经常会用来查询
- 数据量超过300的表应该有索引；
- 经常与其他表进行连接的表，在连接字段上应该建立索引；经常连接查询，需要有索引
- 经常出现在Where子句中的字段，加快判断速度，特别是大表的字段，应该建立索引。 select ……where f1 and f2 ，我们在f1或者f2上建立索引是没用的。只有两个使用联合索引才能有用
- 经常用到排序的列上，因为索引已经默认排序了。
索引优缺点
优点
索引由数据库中一列或多列组合而成，其作用是提高对表中数据的查询速度
索引的优点是可以提高检索数据的速度
缺点
索引的缺点是创建和维护索引需要耗费时间
索引可以提高查询速度，会减慢写入速度。索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
索引是一把双刃剑，可提高查询效率，但也会降低插入和更新的速度并占用磁盘空间。
选择索引的最终目的是为了使查询的速度变快，上面给出的原则是最基本的准则，但不能拘泥于上面的准则，在以后的学习和工作中进行不断的实践，根据应用的实际情况进行分析和判断，选择最合适的索引方式。
创建索引会增加写入操作的开销，因为每次插入、更新或删除数据时，索引也需要更新。因此，应根据查询需求和数据量谨慎选择创建索引。不必要的索引可能会降低性能，而且索引可能占用大量磁盘空间。
视图
什么是视图
视图是基于查询的虚拟表,是一个逻辑表，本身并不包含数据。同真实的表一样，视图包含一系列带有名称的列和行数据，在使用视图时动态生成。通俗的理解，视图就是一条SELECT语句执行后返回的结果集。
SELECT语句所查询的表称为视图的基表，而查询的结果集称为虚拟表，视图本身并不存储具体的数据，视图的数据存在于视图的基表中，基本表数据发生了改变，视图的数据也会跟着改变。
视图的作用
使用视图是为了方便复杂的查询语句。基本思路是将复杂的查询语句定义在视图内部，然后对视图进行查询，从而简化复杂的查询语句。
为什么要使用视图
因为视图的诸多优点，如下
1）简单：使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。
2）安全：使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现。
3）数据独立：一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。
总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。
创建视图
CREATE VIEW 视图名
AS
SELECT列1，列2...
FROM 表;
修改视图
ALTER VIEW 视图名 AS SELECT 语句
3、显示视图创建
SHOW CREATE VIEW 视图名;
4、查看视图
SHOW TABLES;
-- 当作普通表使用
5、 删除视图
DROP VIEW 视图名[,视图名…];
6、重命名视图
RENAME TABLE 视图名 TO 新视图名;
使用视图
需求:查询员工信息，要求显示员工编号、姓名、所属的部门。参考代码如下：
SELECT empno,ename,dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
这是两张表连接查询，如果查询涉及的表越多，查询变得越复杂。像这种复杂的查询可以使用视图来解决。
使用视图查询员工信息
定义视图
CREATE VIEW v_emp
AS
SELECT empno,ename,dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
使用视图
select * from v_emp
注意事项
-
视图的主要作用是与查询相关
-
可以通过视图插入数据，但只能基于一个基表进行插入，不能同时向多张基表插入数据。
insert into v_emp (empno,dname) values(10,‘测试’);
-
可以通过视图修改数据，但只能基于一个基表进行修改，不能同时修改多张基表的数据。
-
可以通过视图删除数据，但只能删除单表查询的视图，不能删除多表连接查询视图中的数据。
-
虽然通过视图也可以对数据进行添加、删除、修改，但不推荐这样做。
-
使用
drop view语句可以删除视图 -
SELECT语句不能引用系统或用户变量。
补充
CREATE [OR REPLACE] [ALGORITHM={UNDEFINED | MERGE | TEMPTABLE}] VIEW 视图名[(列1，列2...)] AS SELECT (列1，列2...)
[WITH [CASCADED | LOCAL] CHECK OPTION]
-
OR REPLACE如果已有同名视图则替换 -
ALGORITHM视图算法UNDEFINED：MySQL将自动选择所要使用的算法MERGE：将视图的语句与视图定义合并起来，使得视图定义的某一部分取代语句的对应部分TEMPTABLE：将视图的结果存入临时表，然后使用临时表执行语句
缺省ALGORITHM选项等同于ALGORITHM = UNDEFINED
-
CHECK OPTION: 表示修改视图时，检查插入的数据是否符合 WHERE 设置的条件-
CASCADED/LOCAL: 表示CHECK OPTION的范围 -
当不加
local或cascaded时，默认为cascaded -
LOCAL表示只需要满足当前视图的约束条件，cascaded表示还必须同时满足基表上的约束条件 -
该语句的作用：
update时，要保证数据update之后能被视图查询出来，也就是要符合where的条件
insert时,保证insert的数据能被视图查询出来
delete时,有无 with check option都一样
对于没有where字句的视图，使用with check option是多余的
-
CREATE OR REPLACE VIEW v_emp as select * from emp where sal > 1000
-- v_emp 基于 emp 查询工资大于1000的所有信息 没有加 with check option 随便修改 没有任何约束
select * from v_emp;
update v_emp set sal = 900 where empno = 7521
CREATE OR REPLACE VIEW v_emp as select * from emp where sal > 1000
update v_emp set sal = 900 where empno = 7654
CREATE OR REPLACE VIEW v_v_emp as select * from v_emp where sal > 800 with local check option;
-- v_v_emp 基于 v_emp 查询工资大于800的所有信息 local 此时修改数据 会检查 修改之后的数据 是否还能在当前视图中
-- 修改之后的数据 满足 (sal > 800)
select * from v_v_emp;
update v_v_emp set sal = 900 where empno = 7654
CREATE OR REPLACE VIEW v_v_emp as select * from v_emp where sal > 800 with CASCADED check option;
-- v_emp 基于 emp 查询工资大于1000的所有信息 CASCADED 此时修改数据 会检查 修改之后的数据 是否还能在当前视图中吗？还能在当前试图的基表中吗 v_v_emp v_emp 的约束满足 (sal > 800 sal >1000)
select * from v_v_emp;
update v_v_emp set sal = 1100 where empno = 9527
封装DBUtils
为什么要写这个类？ 之前操作太麻烦了，每次都要创建连接， 创建 Statement, 关闭很多资源
- 定位：数据库操作的工具类
- 能做什么: 提供方便操作数据库的方法
- getConnection() 拿到 Connection 对象
- executeUpdate(String sql, Object…params) 执行所有 修改
数据库连接池
数据库连接池简介
数据库连接池是个容器，负责分配、管理数据库连接(Connection)
它允许应用程序重复使用一个现有的数据库连接，而不是再重新建立一个；
释放空闲时间超过最大空闲时间的数据库连接来避免因为没有释放数据库连接而引起的数据库连接遗漏
好处
- 资源重用
- 提升系统响应速度
- 避免数据库连接遗漏
之前我们代码中使用连接是没有使用都创建一个Connection对象，使用完毕就会将其销毁。这样重复创建销毁的过程是特别耗费计算机的性能的及消耗时间的。
而数据库使用了数据库连接池后，就能达到Connection对象的复用，如下图
连接池是在一开始就创建好了一些连接（Connection）对象存储起来。用户需要连接数据库时，不需要自己创建连接，而只需要从连接池中获取一个连接进行使用，使用完毕后再将连接对象归还给连接池；这样就可以起到资源重用，也节省了频繁创建连接销毁连接所花费的时间，从而提升了系统响应的速度。
数据库连接池实现
-
标准接口：DataSource
官方(SUN) 提供的数据库连接池标准接口，由第三方组织实现此接口。该接口提供了获取连接的功能：
Connection getConnection()那么以后就不需要通过
DriverManager对象获取Connection对象，而是通过连接池（DataSource）获取Connection对象。 -
常见的数据库连接池
- DBCP
- C3P0
- Druid
我们现在使用更多的是Druid，它的性能比其他两个会好一些。
-
Druid（德鲁伊）
-
Druid连接池是阿里巴巴开源的数据库连接池项目
-
功能强大，性能优秀，是Java语言最好的数据库连接池之一
-
Driud使用
- 导入jar包 druid-1.1.12.jar
- 定义配置文件
- 加载配置文件
- 获取数据库连接池对象
- 获取连接
编写配置文件如下：
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql:///db1?useSSL=false&useServerPrepStmts=true
username=root
password=1234
# 初始化连接数量
initialSize=5
# 最大连接数
maxActive=10
# 最大等待时间
maxWait=3000
使用druid的代码如下：
/**
* Druid数据库连接池演示
*/
public class DruidDemo {
public static void main(String[] args) throws Exception {
//1.导入jar包
//2.定义配置文件
//3. 加载配置文件
Properties prop = new Properties();
prop.load(new FileInputStream("jdbc-demo/src/druid.properties"));
//4. 获取连接池对象
DataSource dataSource = DruidDataSourceFactory.createDataSource(prop);
//5. 获取数据库连接 Connection
Connection connection = dataSource.getConnection();
System.out.println(connection); //获取到了连接后就可以继续做其他操作了
//System.out.println(System.getProperty("user.dir"));
}
}
C3P0
导入jar包
c3p0-0.9.5.5.jar和mchange-commons-java-0.2.20.jar
方式一：
ComboPooledDataSource comboPooledDataSource = new ComboPooledDataSource();
comboPooledDataSource.setDriverClass("com.mysql.cj.jdbc.Driver");
comboPooledDataSource.setJdbcUrl("jdbc:mysql://localhost:3306/kfm");
comboPooledDataSource.setUser("root");
comboPooledDataSource.setPassword("");
comboPooledDataSource.setInitialPoolSize(5);
Connection connection = comboPooledDataSource.getConnection();
System.out.println(connection);
方式二：
c3p0-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<c3p0-config>
<named-config name="test">
<property name="driverClass">com.mysql.cj.jdbc.Driver</property>
<property name="jdbcUrl">jdbc:mysql://localhost:3306/kfm</property>
<property name="user">root</property>
<property name="password"></property>
<property name="maxPoolSize">10</property>
<property name="initialPoolSize">5</property>
<property name="minPoolSize">5</property>
</named-config>
</c3p0-config>
DBCP
DBCP是Apache软件基金组织下的开源连接池实现，该连接池依赖该组织下的另一个开源系统：Commons Pool。
commons-dbcp2-2.10.0.jar / commons-logging-1.2.jar / commons-pool2-2.11.1.jar
public static void main(String[] args) throws SQLException, IOException {
// 创建对象 设置属性 获取连接
BasicDataSource basicDataSource = new BasicDataSource();
basicDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
basicDataSource.setUrl("jdbc:mysql://localhost:3306/kfm");
basicDataSource.setUsername("root");
basicDataSource.setPassword("");
Connection connection = basicDataSource.getConnection();
System.out.println(connection);
}
配置文件 dbcp.properties
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/kfm
username=root
password=
initialSize=5
// 加载配置文件 加载 properties 加载成一个 Properties 对象 使用BasicDataSourceFactory 创建 DataSource对象
Properties properties = new Properties();
properties.load(new FileInputStream("jdbc04/resources/dbcp.properties"));
BasicDataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
System.out.println(connection);
DBUtils
Apache DBUtils是Java编程中的数据库操作实用工具，小巧简单实用。DBUtils封装了对JDBC的操作，简化了JDBC操作，并且使用DBUtils能极大简化JDBC编码的工作量，创建连接、结果集封装、释放资源，同时也不会影响程序的性能。
DBUtils本身并不提供获取数据库连接的方法，它是建立在JDBC API之上的一个轻量级工具库，主要提供了一些常用的数据库操作方法。因此，在使用DBUtils之前，我们需要先导入相关jar包：
commons-dbutils.jar
一般来说，获取数据库连接的方式有两种：
-
使用JDBC API中提供的DriverManager来获取连接。
Class.forName("com.mysql.cj.jdbc.Driver"); // 获取数据库连接 DriverManager.getConnection("jdbc:mysql://localhost:3306/kfm","root",""); -
使用连接池来获取连接。
dataSource.getConnection();
DBUtils核心功能介绍
Dbutils三个核心功能介绍：
- QueryRunner类，提供对sql语句（insert，update，delete）操作的API;
- ResultSetHandler接口，用于定义select操作后，怎样封装结果集;
- DBUtils类，它就是一个工具类,定义了关闭资源与事务处理的方法。
DBUtils类
DBUtils类提供如加载驱动、关闭连接、事务提交、回滚等常规工作的工具类，里面的所有方法都是静态的。主要方法如下：
-
DBUtils类提供了三个重载的关闭方法。这些方法检查所提供的参数是不是NULL，如果不是的话，它们就关闭Connection、Statement和ResultSet。
public static void close(Connection conn) throws SQLException -
这一类"quietly"方法不仅能在Connection、Statement和ResultSet为NULL情况下避免关闭，还能隐藏一些在程序中抛出的SQLException。
public static void closeQuietly(Connection conn) -
用来提交连接，然后关闭连接，并且在关闭连接时不抛出SQL异常。
public static void commitAndClose(Connection conn) throws SQLException -
装载并注册JDBC驱动程序，如果成功就返回true。使用该方法，你不需要捕捉这个异常ClassNotFoundException。
public static boolean loadDriver(String driverClassName)
QueryRunner类
QueryRunner类提供了两种使用方式，一种是基于数据源的（使用了数据库连接池），一种是不提供数据源的，则直接使用数据库的连接。
提供数据源
构造方法
(1) QueryRunner(DataSource) 创建核心类，并提供数据源，内部自己维护Connection
普通方法
(1) update(String sql , Object … params) 执行DML语句
(2) query(String sql , ResultSetHandler , Object … params) 执行DQL语句，并将查询结果封装到对象中。
提供连接
构造方法
(1) QueryRunner() 创建核心类，没有提供数据源，在进行具体操作时，需要手动提供Connection
普通方法
(1) update(Connection conn , String sql , Object … params) 使用提供的Connection，完成DML语句
(2) query(Connection conn , String sql , ResultSetHandler , Object … params) 使用提供的Connection，执行DQL语句，并将查询结果封装到对象中
提供数据源和提供连接着两种方式，他们唯一的区别在于函数的第一个参数需不需要加上连接（因为使用数据库连接池的方式，它的连接的获取是直接由数据源和DBUtlils来管理了）
批处理
batch(Connection conn, String sql, Object[][] params)
batch(String sql, Object[][] params)
查询操作
public objct query(Connection conn, String sql, ResultSetHandler<T> rsh, Object...params)
public objct query(String sql, ResultSetHandler<T> rsh, Object...params)
conn：数据库连接对象
sql：要执行的查询SQL语句
rsh：查询结果集处理器，用于将查询结果转换为Java对象
params ：可变参数，用于设置查询语句中的参数
完成表数据的增加、删除、更新操作
public int update(Connection conn, String sql, Object... params)
public int update(String sql, Object... params)
ResultSetHandler接口
-
该接口用于处理 java.sql.ResultSet，将数据按要求转换为另一种形式。
-
ResultSetHandler 接口提供了一个单独的方法：
Object handle(Result rs) {} -
ResultSetHandler 接口的实现类（构造方法不唯一，在这里只用最常见的构造方法）:
| 方法名 | 描述 |
|---|---|
| ArrayHandler() | 把结果集中的第一行数据转成对象数组（存入Object[]） |
| ArrayListHandler() | 把结果集中的每一行数据都转成一个对象数组，再存放到List中 |
| BeanHandler(Class type) | 将结果集中的第一行数据封装到一个对应的JavaBean实例中 |
| BeanListHandler(Class type) | 将结果集中的每一行数据都封装到一个对应的JavaBean实例中，存放到List里 |
| ColumnListHandler(String columnName/int columnIndex | 将结果集中某一列的数据存放到List中 |
| MapHandler() | 将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。 |
| MapListHandler() | 将结果集中的每一行数据都封装到一个Map里，然后再将所有的Map存放到List中 |
| KeyedHandler(String columnName) | 将结果集每一行数据保存到一个“小”map中,key为列名，value该列的值，再将所有“小”map对象保存到一个“大”map中 ， “大”map中的key为指定列，value为“小”map对象 |
| ScalarHandler(int columnIndex) | 通常用来保存只有一行一列的结果集 |
QueryRunner接口实现数据库的增删改查
update方法实现增(insert)，删(delete)，改(update)
建立数据库连接
/**
* 使用QueryRunner类，实现对数据库的
* insert，delete，update
* 调用QueryRunner类的方法update(Connection conn, String sql, Object... params)
* Object... params表示可变参数，Object类型，SQL语句会出现?占位符
* 数据库连接对象，自定义的工具类传递
*/
public class QueryRunnerDemo {
// 定义成员变量DataSource
static Connection conn = null;
public static void main(String[] args) throws Exception {
conn = getDataSource().getConnection();
insert();
}
/**
* 定义方法，使用Druid数据库连接池建立连接
*/
public static DataSource getDataSource() throws Exception {
Properties pro = new Properties();
pro.load(DruidExample.class.getClassLoader().getResourceAsStream("druid.properties"));
DataSource ds = DruidDataSourceFactory.createDataSource(pro);
return ds;
}
public static void insert() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "INSERT INTO classmate(name, age, job, city) VALUES(?, ?, ?, ?)";
Object[] params = {"kfm07", 24, "python开发工程师", "北京"};
System.out.println(conn);
int row = qr.update(conn, sql, params);
System.out.printf("已经有[%d]发生了变化",row);
DbUtils.closeQuietly(conn);
}
public static void delete() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "DELETE FROM classmate WHERE id <= ?";
int row = qr.update(conn, sql, 10);
System.out.printf("已经有[%d]发生了变化",row);
DbUtils.closeQuietly(conn);
}
public static void update() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "UPDATE classmate SET age = 35 WHERE id = ?";
int row = qr.update(conn, sql, 4);
System.out.println(row+"行发生了变化");
DbUtils.closeQuietly(conn);
}
}
query方法实现查(select)
BeanListHandler
将每一条记录封装到一个JavaBean，并且将JavaBean添加到List中，最后返回List
public static void select() throws Exception {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
BeanListHandler<Emp> bh = new BeanListHandler<>(Emp.class);
List<Emp> emps = null;
try{
emps = qr.query(sql,bh);
} catch (Exception e){
e.printStackTrace();
}
}
BeanHandler
将结果集中第一条记录封装到一个指定的javaBean中。
其构造方法：BeanHandler(Class type)，传递一个Class类型对象,将结果封装到该类的对象。
public static void select() throws Exception {
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp WHERE id = ?";
Object[] params = {2};
BeanHandler<Emp> bh = new BeanHandler<>(Emp.class);
Emp emp = null;
try{
emp = qr.query(sql,bh,params);
} catch (Exception e){
e.printStackTrace();
}
}
ScalarHander
处理单值查询结果,执行的select语句后,结果集只有1个。用于单数据。
通常用于处理聚合函数执行结果(一行一列)。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select name from student where id = ?";
String query = queryRunner.query(sql, new ScalarHandler<String>(1), 1);
System.out.println(query);
}
ColumnListHandler
将结果集中指定的列的字段值，封装到一个List集合中。
查询数据表结果集中的某一列数据,然后存储到List集合
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
List<String> list = qr.query(sql,new ColumnListHandler<String>("name"));
System.out.println(list);
}
ColumnListHandler后的参数
(1) 空参数: 获取就是数据表的第一列
(2) int参数: 传递列的顺序编号
(3) String参数: 传递列名
ArrayHandler
将结果集第一行数据保存到Object[]中。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
Object[] values = qr.query(sql,new ArrayHandler());
System.out.println(Arrays.toString(values));
}
ArrayListHandler
将结果集每一行数据保存到List<Object[]>中。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
List<Object[]> values = qr.query(sql,new ArrayListHandler());
for (Object[] value : values){
System.out.println(Arrays.toString(value));
}
}
KeyedHandler
将结果集每一行数据保存到一个“小”Map中，Key为列名，Value该列的值，再将所有“小”Map对象保存到一个“大”Map中，“大”Map中的Key为指定列，Value为“小”Map对象。
public static void select() throws Exception {
QueryRunner queryRunner = new QueryRunner(getDataSource());
Map<Integer, Map<String, Object>> query = queryRunner.query("select * from s_dept", new KeyedHandler<Integer>());
query.keySet().forEach(key -> {
Map<String, Object> objectMap = query.get(key);
System.out.println(objectMap);
});
}
MapHander
将结果集第一行数据封装到Map集合中，KEY是列名，VALUE为该列的值。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
Map<String, Object> map = qr.query(sql,new MapHandler());
System.out.println(map);
}
MapListHandler
将结果集每一行数据保存到Map中，Key列名 Value该列的值，再将所有Map对象保存到List集合中。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
List<Map<String, Object>> list = qr.query(sql,new MapListHandler());
for (Map<String, Object> map : list){
System.out.println(map);
}
}
创建对象,可以加入泛型，但是加入的数据类型，要和查询的列类型一致，哪个列不清楚,数据类型也不清楚，使用
List<Object>。
select语句后,结果集只有1个。用于单数据。
通常用于处理聚合函数执行结果(一行一列)。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "select name from student where id = ?";
String query = queryRunner.query(sql, new ScalarHandler<String>(1), 1);
System.out.println(query);
}
ColumnListHandler
将结果集中指定的列的字段值，封装到一个List集合中。
查询数据表结果集中的某一列数据,然后存储到List集合
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
List<String> list = qr.query(sql,new ColumnListHandler<String>("name"));
System.out.println(list);
}
ColumnListHandler后的参数
(1) 空参数: 获取就是数据表的第一列
(2) int参数: 传递列的顺序编号
(3) String参数: 传递列名
ArrayHandler
将结果集第一行数据保存到Object[]中。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
Object[] values = qr.query(sql,new ArrayHandler());
System.out.println(Arrays.toString(values));
}
ArrayListHandler
将结果集每一行数据保存到List<Object[]>中。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
List<Object[]> values = qr.query(sql,new ArrayListHandler());
for (Object[] value : values){
System.out.println(Arrays.toString(value));
}
}
KeyedHandler
将结果集每一行数据保存到一个“小”Map中，Key为列名，Value该列的值，再将所有“小”Map对象保存到一个“大”Map中，“大”Map中的Key为指定列，Value为“小”Map对象。
public static void select() throws Exception {
QueryRunner queryRunner = new QueryRunner(getDataSource());
Map<Integer, Map<String, Object>> query = queryRunner.query("select * from s_dept", new KeyedHandler<Integer>());
query.keySet().forEach(key -> {
Map<String, Object> objectMap = query.get(key);
System.out.println(objectMap);
});
}
MapHander
将结果集第一行数据封装到Map集合中，KEY是列名，VALUE为该列的值。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
Map<String, Object> map = qr.query(sql,new MapHandler());
System.out.println(map);
}
MapListHandler
将结果集每一行数据保存到Map中，Key列名 Value该列的值，再将所有Map对象保存到List集合中。
public static void select() throws Exception{
QueryRunner qr = new QueryRunner(getDataSource());
String sql = "SELECT * FROM emp";
List<Map<String, Object>> list = qr.query(sql,new MapListHandler());
for (Map<String, Object> map : list){
System.out.println(map);
}
}
创建对象,可以加入泛型，但是加入的数据类型，要和查询的列类型一致，哪个列不清楚,数据类型也不清楚，使用
List<Object>。
1764
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
