Mysql和JDBC

也可以根据MySQL数据库配置文件my.ini设定

MySQL数据管理

创建外键

constraint 外键名 foreign key (外键列名) references 从表名称 (从表列名)

在删除外键时，要先删除从表和索引

事务

将一组SQL语句放在同一批次执行，如果一个SQL语句出错，则该批次内的所有SQL都会被取消执行

MySQL事务处理只支持InnoDB和BDB数据表类型

ACID原则

• 原子性（Atomic）：事务中的所有操作要么全部完成，要么全部不完成
• 一致性（Consist）：一个事务前后数据的完整性保持一致
• 隔离性（Isolated）：一个事务不会被另一个事务操作数据所干扰，多个并发事务之间相互隔离
• 持久性（Durable）：事务一旦被提交，对数据的改变是永久的

 索引

索引作用

• 提高查询速度
• 确保数据唯一
• 加速表与表之间的连接
• 减少分组和排序的时间
• 全文检索字段进行搜索优化

分类

• 主键索引（primary key）
• 唯一索引（unique）
• 常规索引（index）
• 全文索引（fulltext）

主键索引

作用：唯一标识一条记录，只能有一个

唯一索引

作用：避免某一数据列中的值重复，可以有多个

常规索引

作用：快速定位特定数据，index和key都可以设置常规索引

全文索引

作用：快速定位特定数据，只能用于char，varchar和text数据列类型 ，MySQL5.6前只有MyISAM支持全文索引，MySQL5.6之后，MyISAM和InnoDB均支持全文索引，全文索引通过match()函数完成

SELECT *FROM student WHERE MATCH(studentname) AGAINST(‘love‘);

索引的数据结构

• hash类型的索引：查询单条快，范围查询慢
• btree类型的索引：b+树，层数越多，数据量指数级增长
• InnoDB支持事务支持行级锁，支持B-tree，fulltext索引，不支持hash索引
• MyISAM不支持事务，支持表级锁，支持B-tree，fulltext索引，不支持Hash索引

MySQL备份

MySQL数据库备份保证数据不丢失，数据转移

备份方法：

• mysqldump备份工具
• 数据库管理工具备份sqlyog
• 直接拷贝数据库文件和相关配置文件

-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
-- 导入
1. 在登录mysql的情况下： -- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件

数据库设计3大范式

第一范式（1st NF）:确保每一列的原子性（不可再分的最小数据单元）

第二范式（2nd NF）:每一个表只描述一件事，必须先满足第一范式

第三范式（3rd NF）:数据表中的每一列数据都和主键直接相关，不能间接相关，必须先满足满足第二范式

JDBC

 开发JDBC前要导入JDBC数据库驱动

public class Jdbcdemo {
    public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&SSL=true&serverTimezone=UTC";
        String username="root";
        String password="123456";
        //1.加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //2.获取连接
        Connection connection= DriverManager.getConnection(url,username,password);
        //3.创建statement
        Statement statement=connection.createStatement();
        String sql="select * from users";
        //4.执行sql
        ResultSet resultSet=statement.executeQuery(sql);
        //5.打印结果集
        while(resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("name"));
        }
        //6.关闭连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

对象说明

DriverManager类讲解

JDBC程序中的DriverManager用于加载驱动和创建数据库的连接，常用的两个方法

//加载驱动
DriverManager.registerDriver(new Driver());//不推荐使用，会使驱动程序注册两次，内存中会有两个Driver对象，且这样程序会依赖mysql的api
//创建于数据库的连接
DriverManager.getConnnection(url,user,password);

使用Class.forName(com.mysql.jdbc.Driver)来加载驱动。这样只有一个驱动对象，且不依赖具体驱动

数据库URL

 Connection类

它用于代表数据库的连接，客户端与数据库的所有交互都是通过Connection对象完成的，常用方法有

• createStatement：创建向数据库发送SQL的statement的对象
• prepareStatement(sql)：创建向数据库发送预编译sql的prepareStatement对象
• setAutoCommit(boolean autoCommit)：设置事务是否自动提交
• commit()：在连接上提交事务
• rollback()：回滚事务

Statement类

它用于向数据库发送sql语句，常用方法有：

• executeQuery(String sql)：用于向数据库发送查询语句
• executeUpdate(String sql)：用于向数据库发送insert，update或delete语句
• execute(String sql)：用于向数据库发送任意sql语句
• addBatch(String sql)：把sql语句放到一个批处理中
• executeBatch()：向数据库发送一批sql语句执行

ResultSet类

它代表sql语句执行的结果，其封装结果时采用类似于表格的方式，ResultSet维护了一个指向表数据行的游标，初始时，游标在第一行之前，调用next()方法是游标指向具体行。

使用get方法获取数据：

• 获取任意类型数据
  • getObject(String columnname)
  • getObject(int index)
• 获取指定类型数据源
  • getSting(String columnname)
  • getString(int index)

其还提供了对结果集滚动的方法：

• next()：移动到下一行
• previous()：移动到前一行
• absolute(int row)：移动到指定行
• beforeFirst()：移动到最前面
• afterLast()：移动到最后面

 statement对象

JDBC中的statement对象用于向数据库发送SQL语句

public class Jdbcdemo {
    public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&SSL=true&serverTimezone=UTC";
        String username="root";
        String password="123456";
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //创建链接
        Connection connection=DriverManager.getConnection(url,username,password);
        //创建statement
        Statement statement=connection.createStatement();
        //插入,executeUpdate()返回一个整数，代表数据表中几行数据发生了变化
        //String sql="insert into users(id,name,password,email,birthday)values(4,‘zzy‘,2020,‘1780@qq.com‘,‘2020-11-09‘)";
        //int num=statement.executeUpdate(sql);
        //System.out.println(num);
        //删除
        //String sql1="delete from users where id=4";
        //int num1=statement.executeUpdate(sql1);
        //System.out.println(num1);
        //更新
        //String sql2="update users set name=‘dwx‘ where id=1";
        //int num3=statement.executeUpdate(sql2);
        //System.out.println(num3);
        //查询
        String sql3="select * from users where id=1";
        ResultSet resultSet=statement.executeQuery(sql3);
        while(resultSet.next()){
            System.out.println(resultSet.getObject("name"));
        }
    }
}

preparedStatement对象

PrepareStatement是Statement的子类，实例对象可以通过调用Connection.preparedStatement()获得，preparedStatement可以避免SQL注入问题，Statement会使数据库频繁编译sql，可能造成数据库缓冲区溢出。PreparedStatement可对SQL进行预编译，从而提高数据库执行效率。并且PreperedStatement对于sql中的参数，允许使用占位符进行替换。

public class Jdbcdemo {
    public static void main(String[] args) throws Exception {
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&SSL=true&serverTimezone=UTC";
        String username="root";
        String password="123456";
        //加载驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //创建链接
        Connection connection=DriverManager.getConnection(url,username,password);
        //插入SQL命令，SQL中参数使用？作为占位符
        //String sql="insert into users(id,name,password,email,birthday) values(?,?,?,?,?)";
        //PreparedStatement preparedStatement=connection.prepareStatement(sql);
        //为sql语句中的参数赋值
        //preparedStatement.setInt(1,5);
        //preparedStatement.setString(2,"dddy");
        //preparedStatement.setString(3,"53653");
        //preparedStatement.setString(4,"9090@qq.com");
        //preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));
        //int num=preparedStatement.executeUpdate();
        //System.out.println(num);
        //删除
        String sql2="delete from users where id=?";
        PreparedStatement preparedStatement1=connection.prepareStatement(sql2);
        preparedStatement1.setInt(1,2);
        int num2=preparedStatement1.executeUpdate();
        System.out.println(num2);
    }
}

PreparedStatement可以防止SQL注入，原理是在执行时，参数会用引号包起来，并把参数中的引号当作转义字符，从而避免了参数直接作为条件

事务

隔离性问题

• 脏读：一个事务读取了另一个事务未提交的数据
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取的结果不同
• 幻读（虚读）：在一个事务内读取到了别的事务插入的数据，导致前后读取行不一样

Connection对象会自动提交sql语句，如要让多条sql在一个事务中执行，执行如下操作

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

数据库连接池

数据库连接池负责分配管理和释放数据库连接，允许应用程序重复使用一个现有的数据库连接，而不是重新创建一个。

• 数据库连接池在初始化时，会根据最小数据库连接数量来创建数据库连接
• 最大数据库连接数量限定了连接池能够拥有的最大连接数
• 当应用程序请求的连接数量超过最大连接数时，请求会被放入等待队列中

编写连接池需要实现java.sql.DataSource接口

常见的连接池技术有DBCP和C3P0，创建数据源后直接从数据源获取连接

DBCP连接池

Tomcat的连接池就是采用DBCP连接池来实现的

C3P0连接池

Spring使用它，其与DBCP的区别是

• DBCP没有自动回收空闲连接的功能
• C3P0可以自动回收空闲连接

Mysql和JDBC

标签：love   描述   lock   完整性   bcp   允许   tin   连接数量   load