最近准备学习下mysql的进阶知识,跟着B站博主的视频把mysql基础知识点复习了下,其中一些学习笔记记录在这里,方便平时查阅。
列的数据类型
数值类型
- tinyint 0~255 一个字节
- smallint 较小的数据 两个字节
- mediumint 三个字节
- int 0~2^32 四个字节 (常用)
- bigint 八个字节
- float 四个字节
- double 八个字节
- bigDecimal 字符串形式的浮点数
字符串
- char 固定大小 0~255
- varchar 可变字符串 0~65535
- tinytext 微型文本
- text 文本串
时间日期
- date YYYY-MM-DD,日期
- time HH:mm:ss,时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳
NULL
- 没有值,未知
- 不要使用NULL做计算,结果一定是NULL
数据库的字段属性(重要)
Unsigned:
- 无符号整
- 该列不能为负数
zerofill:
- 不足的位数使用0填充
Auto Increment:
- 通常用来设置唯一主键,必须是整数类型
NotNull:
- 不填值会报错
Defalut:
- 给每一行此列赋默认值
在阿里巴巴规范手册中要求,任何数据必须包含以下几列:
id 主键
version 乐观锁
is_delete 是否删除
gmt_create 创建时间
gmt_update 更新时间
InnoDB和MyISAM的区别
- InnoDB支持事务
- InnoDB支持行锁,MyISAM只支持表锁
- InnoDB支持外键索引
- InnoDB支持外键
select模版
select 字段
from 表1 别名
连接类型(left,right,inner) join 表2 别名
on 连接条件
where 条件1
group by 分组字段
having 条件2(一般是分组条件)
order by 排序字段 (desc,asc)
limit 索引,最大查询数量;
事务
-- mysql默认开启事务自动提交
set autocommit = 0; /* 关闭自动提交 */
set autocommit = 1; /* 开启自动提交 */
-- 事务开启
start transaction;
-- 执行语句
update account set account = 400.00 where name = 'B';
update account set account = 800.00 where name = 'A';
-- 提交:如果成功将会持久化
commit;
-- 回滚:所有语句都不执行,回到初始状态
rollback;
-- 保存点
savepoint name;
rollback to savepoint name;/* 可以回滚到保存点 */
release savepoint name;
-- 查看隔离级别
select @@transaction_isolation;
-
Atomicity 原子性:包含在事务当中的操作要么全不执行,要么全部执行;
-
Isolation 隔离性:表示数据库的隔离级别,两个进程/事务之间相互影响的程度,隔离级别分为”读未提交、读已提交、可重复读、串行化“。
-
读未提交(READ-UNCONMMITED):
- 此现象称为脏读。
- 两个事务都不需要提交,事务1就可以读取到2修改的值。
- 如果一个事务已经开始写数据,则另外一个事务则不允许同时进行写操作,但允许其他事务读此行数据。
-
读已提交(READ-COMMITED)
- 虽然事务1在事务2提交之后才读取到最新的值,但是事务1的完整性也受到了影响。
- 允许其他事务继续访问该行数据,但是未提交的写事务将会禁止其他事务访问该行,会对该写锁一直保持直到到事务提交。
- 此现象称为不可重复读。
-
可重复读(REPEATABLE-READ)
- 在此场景下,可重复读隔离级别防止了不可重复读和幻读。
- 如果事务2进行了更新操作并提交,事务1对同样符合条件的数据做了更新操作,那么会将事务版本号更新成当前事务版本号,造成版本号回退现象,那么此事务中的查询语句在未提交的情况下会查找到事务2更新的值。
-
Durability 持久性:事务一旦处理结束后,对数据的修改就是永久的,即使系统故障也不会丢失。
事务中的MVCC(多版本并发控制)
在InnoDB中,会在每行数据后添加两个额外的隐藏的值来实现MVCC,这两个值一个记录这行数据何时被创建,另外一个记录这行数据何时过期(或者被删除)。 在实际操作中,存储的并不是时间,而是事务的版本号,每开启一个新事务,事务的版本号就会递增。 在可重读Repeatable reads事务隔离级别下:
- SELECT时,读取创建版本号<=当前事务版本号,删除版本号为空或>当前事务版本号。
- INSERT时,保存当前事务版本号为行的创建版本号
- DELETE时,保存当前事务版本号为行的删除版本号
- UPDATE时,插入一条新纪录,保存当前事务版本号为行创建版本号,同时保存当前事务版本号到原来删除的行
数据库规范设计
糟糕的数据库设计:
- 数据冗余,浪费空间
- 数据库插入删除比较麻烦,使用了物理外键
- 程序性能差
良好的数据库设计:
- 节省内存空间
- 保证数据库完整性
- 方便开发
数据库三大范式
第一范式(1NF):要求数据库表的每一列都是不可分割的原子数据项。
保证每一列不可再拆分
在上面的表中,“家庭信息”和“学校信息”列均不满足原子性的要求,故不满足第一范式,调整如下:
第二范式(2NF):要求一张表只能描述一件事情。
在1NF的基础上,非码属性必须完全依赖于候选码(在1NF基础上消除非主属性对主码的部分函数依赖)
第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。
在上图所示的情况中,同一个订单中可能包含不同的产品,因此主键必须是“订单号”和“产品号”联合组成,
但可以发现,产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关,但是订单金额和订单时间仅与“订单号”相关,与“产品号”无关,
这样就不满足第二范式的要求,需分成两个表:
第三范式(3NF):要求每一列数据都和主键直接相关,而不能间接相关。
例如:学生id为主键,班主任id和主键直接相关,但是班主任姓名、班主任年龄都只和班主任id直接相关。所以应当拆分。
JDBC
- 通过配置文件读取,获得数据库驱动、数据库连接字符串、用户名、密码,以下可以抽象成工具类:
- 通过类加载器注册数据库驱动
- 获得数据库实例的连接
- 获得PrepareStatement/Statement对象,执行sql(Statment会有SQL注入的情况)
- 释放连接
- 后期会通过***myBatis/myBatisPlus***简化JDBC的操作
public class JDBCUtils {
private static String driver = null;
private static String url = null;
private static String user = null;
private static String password = null;
static {
InputStream dbProperties = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
Properties prop = new Properties();
try {
prop.load(dbProperties);
driver = prop.getProperty("driver");
url = prop.getProperty("url");
user = prop.getProperty("user");
password = prop.getProperty("password");
//驱动只用加载一次
Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,user,password);
}
public static void release(Connection conn, PreparedStatement ps, ResultSet resultSet){
if (conn != null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (ps != null){
try {
ps.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (resultSet != null){
try {
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
数据库连接池
- 使用池化技术降低频繁建立/关闭连接的开销资源
- 继承DataSource接口可以自己实现连接池
- 最大连接数
- 最小连接数
- 空闲连接数(超过一定时间没有连接使用就会释放连接资源)
- 超时等待时间
- 开源的数据库连接池有***DHCP***、C3P0、***DRUID(Apache提供,阿里使用)***、HIKARI(SpringBoot集成的)