数据库
三大范式
第一范式:原子性
确保每一列的原子性,数据库表中的所有字段值都是不可分解的原子值
第二范式:唯一性
确保每一列都与主键相关,也就是说在一个数据库表中,一个表只说明一个事物,不可以把多种数据保存在同一张数据库表中。
第三范式:不存在传递依赖
确保每一列数据和主键直接相关,而不能接间相关
依赖传递:
如果A属性可以确定唯一的B属性的值,再通过B属性的值确定C属性的值,那么就称C传递依赖于A
例如:
表:学号, 姓名, 年龄, 所在学院, 学院联系电话
存在依赖传递::(学号) → (所在学院) → (学院联系电话) 。
修改为:
学生表:学号, 姓名, 年龄, 所在学院; 学院表:学院, 电话
数据库操作名词:
DML Data manipulate language 数据操作语言 :
insert update delete
DQL Data Query Language 数据查询语言:
select
DDL Data Definition Language 数据库定义语言:
create alter drop
DCL Data Control Language 数据库操作语言:
管理用户,授权
DBA: Database Administrator 数据库管理人员;
JDBC: JAVA database connectivity java数据库连接
事务的四大特征:ACID
- 原子性 atomicity:
事务是不可分割的操作序列最小单位,事务中的各项操作要么同时成功,要么同时失败.
- 一致性 consistent
一个事务操作前后,数据的总量不变.
- 隔离性 isolation
在并发情况下,多个事务之间相互隔离.隔离性分4个级别
- 持久性 duration
当事务提交或者回滚后,数据库会持久保存数据
事务的隔离级别:
- 脏读: 一个事务读到了,另一个事务没提交的数据.
事务A读取了事务B更新的数据,然后B回滚操作,那么A读取到的 数据是脏数据。
- 不可重复读: 在同一次事务中读到的数据不一样
事务 A 多次读取同一数据,事务 B 在事务A 多次读取的过程中,对数据做了更新并提交,导致事务A多次读取同一数据时,结果不一致。
- 幻读:重复查询的过程中,数据就发⽣了数量的变化(数据内容没变)
并发执行事务时可能发生的问题:
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 READ_UNCOMMITTED | √ | √ | √ |
读已提交 READ_COMMITTED | × | √ | √ |
可重复读 REPEATABLE_READ | × | × | 可能会 |
顺序读 SERIALIZABLE (串行化) | × | × | × |
4种事务隔离级别从上往下,级别越高,并发性越差,安全性就越来越高。⼀般数据默认级别是可重复读
查询&修改隔离级别:
查看隔离级别:
select @@tx_isolation
mysql8:
select @@transaction_isolation
- 设置隔离级别:
set global/session transaction level 隔离级别
数据表关系:
一对多/多对一:
原则:在多的一方创建一个字段,作为外键,指向一的一方.
多对多:
原则: 需要创建第三张表,这张表有两个字段分别为外键指向个字一的一方的主键.
一对一:
一对一可以组件成一张表
或者在任意一方创建一个字段,需要唯一约束,作为外键,指向一的一方.
或者任意一方的主键作为另一方主键的关联外键.
多表查询:
1,合并结果集:
合并结果集就是把两个select语句的查询结果合并到一起!
**注意:**被合并的两个结果:列数、列类型必须相同。
UNION: 去除重复记录,
UNION ALL:保留重复记录.
2,内连接:
语法:
select 列名
from 表1
inner join 表2
on 表1.列名=表2.列名 //外键列的关系
where…
或者:
select 列名
from 表1,表2
where 表1.列名=表2.列名 and …(其他条件)
3,外连接:
包括左外连接和右外连接,外连接的特点:查询出的结果存在不满足条件的可能,
主表数据全部显示,次表数据匹配显示,能匹配到的显示数据,匹配不到的显示null,
主表和次表不能随意调换位置,
**使用场景:**一般会作为子查询的语句使用.
语法:
– 左外联:select 列名 from 主表 left join 次表 on 主表.列名=次表.列名
– 右外联:select 列名 from 次表 right join 主表 on 主表.列名=次表.列名
4,自然查询:
自然连接是一种特殊的等值连接,他要求两个关系表中进行连
接的必须是相同的属性列(名字相同),无须添加连接条件,并且在结果中消除重复的属性列
相当于内连接去重复的列
语句:
select * from 表1 natural join 表2 ;
5,子查询:
一个select语句中包含另一个完整的select语句。
子查询就是嵌套查询,即SELECT中包含SELECT,如果一条语句中存在两个,或两个以上SELECT,那么就是子查询语句了。
子查询的作用位置:
a. where后,作为条为被查询的一条件的一部分;
b. from后,作表;
当子查询出现在where后作为条件时,还可以使用如下关键字:
a. any
b. all
子查询结果集的形式:
a. 单行单列(用于条件)
b. 单行多列(用于条件)
c. 多行单列(用于条件)
d. 多行多列(用于表)
其他功能:
多行新增:
insert into 表名(列名) values (1行列值),(2行列值),(3行列值);
多表新增:
(1)update 表1,表2 set 列名=列值 where 表1.列名=表2.列名 and 其他限定条件
(2)update 表1
inner join 表2 on 表1.列名=表2.列名
set 列名=列值
where 限定条件
多表删除:
delete 被删除数据的表 from 删除操作中使用的表
where 限定条件
注:多张表之间使用逗号间隔
日期运算函数:
now() 获得当前系统时间,
year(日期值) 获得日期值中的年份
date_add(日期,interval 计算值 计算的单位(year,week,day,month));
注:计算值大于0表示往后推日期,小于0表示往前推日期
数据库优化:
1,对查询进行优化,尽量避免使用*,会进行全表扫描,需要用到几列,就查询几列/
2,索引: (能唯一找到数据的列比如:主键列,唯一列)
首先考虑在where及order by使用建立索引的列
3,应尽量避免在where 字句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描;
最好不要给数据库留null,尽可能使用not null填充数据库
4,应尽量避免在 where 子句中使用 != 或 <> 操作符,否则引擎将放弃使用索引而进行全表扫描
5,.应尽量避免在 where 子句中使用 or 来连接条件,如果一个字段有索引,一个字段没有索引,将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or Name = ‘admin’
可以这样查询:
select id from t where num = 10
union all
select id from t where Name = ‘admin’
6,in 和 not in 也要慎用,否则会导致全表扫描,
如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
很多时候用 exists 代替 in 是一个好的选择
JDBC对象详解:
connnection 连接对象:
获取连接对象:
DriverManager.getConnection(url,username,password);
获取预处理状态通道:
PrepareStatement pps =connection.prepareStatement(sql语句);
获取状态通道:
Statement statement = connnect. createStatement();
开启事务:
connection.setAutoCommit(boolean b);
提交事务
connection. commit();
回滚事务:
connection.rollback();
connection.rollback(Savepoint savepoint);
设置回滚点:
Savepoint setSavepoint(String savepointName);
关闭连接:
connection.close();
Statement 执行sql对象
获取statement对象:
Statement statement =connection.createStatement();
执行sql查询语句,获取结果集:
ResultSet statement .executeQuery(String sql语句);
执行DML操作,返回影响的行数;
int statement .executeUpdate(String sql语句)
Statement批处理:
statement .addBatch(String sql语句);
可以追加多条.
int[] statement .executeBatch();
返回的结果为每一句addBatch()执行影响的条数的数组
PreparedStatement 预处理状态通道 执行sql语句对象:
- 获取PreparedStatement对象:
PreparedStatement prepareStatement = connnection.prepareStatement(String sql语句);
给占位符? 赋值:
void setObject(占位符位置编号,占位符的值);
占位符从1开始
执行sql语句,获取结果集:
ResultSet executeQuery();
执行DML操作,返回影响的行数;
int executeUpdate(String SQL);
PreparedStatement批处理
一条sql语句配合多次执行[set赋值(),addBatch()]操作
String SQL = "INSERT INTO Employees (id, first, last, age) VALUES(?, ?, ?, ?)"; PreparedStatement pstmt = conn.prepareStatement(SQL); pstmt.setInt( 1, 400 ); pstmt.setString( 2, "Pappu" ); pstmt.setString( 3, "Singh" ); pstmt.setInt( 4, 33 ); pstmt.addBatch(); pstmt.setInt( 1, 401 ); pstmt.setString( 2, "Pawan" ); pstmt.setString( 3, "Singh" ); pstmt.setInt( 4, 31 ); pstmt.addBatch(); //add more batches //Create an int[] to hold returned values int[] count = stmt.executeBatch();
ResultSet结果集
判断当前是否为最后一行数据:
boolean next();
获取数据:
Object getXxx(String 字段内容)
Xxx代表字段的类型
返回数据库的相关信息:
ResultSetMetaData metaData = resultSet.getMetaData();
- 得到数据表列数:
int count=metaData.getColumnCount();
- 得到每一列的名字:
String name = metaData.getColumnName(i)
(列的位置从1开始)
根据列名获取结果:
Object o =resultSet.getObject(“列名”);
获取资源文件
我们需要访问位于/WEB-INF/classes目录下的一个后缀名为properties的文本
类型文件,从里面读取我们需要的值.
ResourceBundle bundle = ResourceBundle.getBundle(“文件名省略后缀”);
String value = bundle .getString(“key”);
数据库连接池:
连接池基本的思想是在系统初始化的时候,将数据库连接作为对象存储在内存中,当用户需要访问数据库时,并非建立一个新的连接,而是从连接池中取出一个已建立的空闲连接对象。使用完毕后,用户也并非将连接关闭,而是将连接放回连接池中,以供下一个请求访问使用。而连接的建立、断开都由连接池自身来管理.
标准接口: Datasource
连接池参数:
- 最小连接数:
是数据库一直保持的数据库连接数,所以如果应用程序对数据库连接的使用量不大,将有大量的数据库资源被浪费。
- 初始化连接数:
连接池启动时创建的初始化数据库连接数量。
- 最大连接数:
是连接池能申请的最大连接数,如果数据库连接请求超过此数,后面的数据库连接请求被加入到等待队列中。
- 最大等待时间:
当没有可用连接时,连接池等待连接被归还的最大时间,超过时间则抛出异常,可设置参数为0或者负数使得无限等待(根据不同连接池配置)。
DBCP连接池:
dbcp没有自动回收空闲连接的功能,需要手动设置配置文件
- jar包:
commons-dbcp.jar
commons-pool.jar
- 硬编码方式获取连接对象:
// 硬编码 使用DBCP连接池子 BasicDataSource source = new BasicDataSource(); //设置连接的信息 source.setDriverClassName("com.mysql.jdbc.Driver"); source.setUrl("jdbc:mysql://localhost:3306/day2"); source.setUsername("root"); source.setPassword("111"); Connection connection = source.getConnection(); //回收连接: connection.close();
- 软编码方式使用:添加配置文件(配置文件需要手动加载)
文件名称: info.properties
文件位置: src下
#连接设置 driverClassName=com.mysql.jdbc.Driver url=jdbc:mysql://localhost:3306/day2 username=root password=111 #<!-- 初始化连接 --> initialSize=10 #最大连接数量 maxActive=50 #<!-- 最大空闲连接 --> maxIdle=20 #<!-- 最小空闲连接 --> minIdle=5 #<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 --> maxWait=6000
C3P0连接池:
c3p0是一个开放源代码的JDBC连接池,有自动回收空闲连接功能,不需要手动设置配置文件.
- 使用jar包:
c3p0-0.9.1.2.jar
- 配置文件:
文件位置: src
文件命名:c3p0-confifig.xml
<?xml version="1.0" encoding="utf-8"?> <c3p0-config> <!-- 默认配置,如果没有指定则使用这个配置 --> <default-config> <!-- 基本配置 --> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day2</property> <property name="user">root</property> <property name="password">111</property> <!--扩展配置--> <!-- 连接超过30秒报错--> <property name="checkoutTimeout">30000</property> <!--30秒检查空闲连接 --> <property name="idleConnectionTestPeriod">30</property> <property name="initialPoolSize">10</property> <!-- 30秒不适用丢弃--> <property name="maxIdleTime">30</property> <property name="maxPoolSize">100</property> <property name="minPoolSize">10</property> <property name="maxStatements">200</property> </default-config> <!-- 命名的配置 --> <named-config name="abc"> <property name="driverClass">com.mysql.jdbc.Driver</property> <property name="jdbcUrl">jdbc:mysql://localhost:3306/day2</property> <property name="user">root</property> <property name="password">111</property> <!-- 如果池中数据连接不够时一次增长多少个 --> <property name="acquireIncrement">5</property> <property name="initialPoolSize">20</property> <property name="minPoolSize">10</property> <property name="maxPoolSize">40</property> <property name="maxStatements">20</property> <property name="maxStatementsPerConnection">5</property> </named-config> </c3p0-config>
获取连接对象代码:
ComboPooledDataSource db=new ComboPooledDataSource("配置的命名,默认配置使用无参构造"); connection=db.getConnection();
Druid(德鲁伊)连接池:
阿里出品,淘宝和支付宝专用数据库连接池.
jar包:
druid-1.0.9.jar
druid-1.0.9-sources.jar
使用步骤:
DruidDataSource ds=new DruidDataSource(); ds.setDriverClassName(); ds.setUrl(); ds.setUsername(); ds.setPassword(); connection = ds.getConnection()