Mysql和JDBC

2021-10-20 12:18:11

操作数据库

结构化查询语言分类

名称	解释	命令
DDL数据定义语言	定义和管理数据对象，如数据库，数据表	create,drop ,alter
DML数据操作语言	操作数据库对象中包含的数据	insert,update,delete
DQL数据查询语言	查询数据库数据	select
DCL数据控制语言	管理数据库语言，包括管理权限和数据更改	grant,commit,rollback

MyISAM与InnoDB的区别

名称	MyISAM	InnoDB
事务处理	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，2倍左右

数据表存储位置

MySQL数据表以文件方式存储在磁盘中

位置在Mysql安装目录\data\ 目录名对应数据库名，目录下文件名对应数据表名
InnoDB类型的数据表只有一个.frm文件，以及上一级目录的ibdata1文件

设置字符集

create table 表名()charset=utf-8;

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

MySQL数据管理

创建外键

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

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

事务

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

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

ACID原则

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

索引

索引作用

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

主键索引

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

唯一索引

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

常规索引

作用：快速定位特定数据，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可以自动回收空闲连接