数据库概述
1）什么是数据库
存储数据的仓库，本质上就是存储数据的文件系统。
数据会按照特定的格式存储起来，用户可以对该仓库的数据进行增加，修改，删除及查询操作。
2）数据库的优点
数据库是按照特定的格式将数据存储在文件中，通过SQL语句可以方便的对大量数据进行增、删、改、查操作，数据库是对大量的信息进行管理的高效的解决方案。
3）数据库管理系统（DBMS）
数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于创建、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。
4）数据库管理系统、数据库和表的关系
数据库管理系统可以管理多个数据库，每个数据库中可以有多张数据库表。
5）常见数据库（dbms管理系统）
-
MYSQL：开源免费的数据库，小型的数据库。已经被Oracle收购了，MySQL6.x版本也开始收费。
-
Oracle：收费的大型数据库，Oracle公司的产品。
-
PostgreSQL：一个功能强大的开源对象关系型数据库系统。
介绍：https://blog.csdn.net/qq_40223688/article/details/89451616
-
DB2：IBM公司的数据库产品。收费的，常应用在银行系统中。
-
SQLServer：MicroSoft 公司收费的中型的数据库。C#、net等语言常使用。
-
SyBase：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner。
-
SQLite: 嵌入式的小型数据库，应用在手机端。
企业常用：MYSQL Oracle DB2（银行）
数据库表设计三范式
第一范式（1NF），原子性，列或者字段不能再分
第二范式（2NF），唯一性，一张表存储一类数据
第三范式（3NF），直接性，不存在传递依赖
事务技术
数据库的隔离级别
read uncommitted 读未提交 脏读、不可重复读、幻读都会发生
read committed(oracle默认) 读已提交 避免脏读的发生
repeatable read(mysql默认) 重复读 避免脏读和不可重复的读的发生
serializable 串行化 避免所有问题的发生
查询mysql的隔离级别
show variables like "%isolation%"; -- 结果：repeatable read
设置事务的隔离级别
set global transaction isolation level 级别字符串;
-
读未提交 (read uncommitted)：最低级别，以上情况均无法保证)
-
读已提交 (read committed)：可避免脏读情况发生
-
可重复读 (repeatable read)：可避免脏读、不可重复读情况的发生。 mysql的默认隔离级别
-
串行化 (serializable)：可避免脏读、不可重复读、幻读(虚读)情况的发生。
使用serializable隔离级别，一个事务没有执行完，其他事务的SQL执行不了
事务的概念
事务定义
在实际的业务开发中，有些业务操作要多次访问数据库。一个业务要发送多条SQL语句给数据库执行。需要将多次访问数据库的操作视为一个整体来执行，要么所有的SQL语句全部执行成功。如果其中有一条SQL语句失败，就进行事务的回滚，所有的SQL语句全部执行失败。
事务，就是一组操作数据库的动作集合。事务是现代数据库理论中的核心概念之一。如果一组处理步骤或者全部发生或者一步也不执行，称该组处理步骤为一个事务。当所有的步骤像一个操作一样被完整地执行，称该事务被提交。由于其中的一部分或多步执行失败，导致没有步骤被提交，则事务必须回滚到最初的系统状态。
事务技术的作用：
可以控制一件完整事情的多个步骤。只要有一个步骤出现了错误就算整件事情是失败了，只要所有步骤全部成功才控制当前这件事情是成功的。
事务的特点：
- 原子性：一个事务中所有对数据库的操作是一个不可分割的操作序列，事务中的操作要么都发生，要么都不发生
- 一致性：事务前后数据的完整性必须保持一致
- 隔离性：一个事务的执行，不受其他事务的干扰，即并发执行的事务之间互不干扰，事务之间数据相互隔离
- 持久性：一个事务一旦提交，它对数据库中数据的改变就是永久的
事务的提交方式和回滚规则
事务的提交方式：
默认情况下，数据库处于自动提交模式。每一条语句处于一个单独的事务中，在这条语句执行完毕时，如果执行成功则隐式的提交事务，如果执行失败则隐式的回滚事务。
对于正常的事务管理，是一组相关的操作处于一个事务之中，因此必须关闭数据库的自动提交模式。spring 会将底层连接的【自动提交特性】设置为 false 。也就是在使用 spring 进行事务管理的时候，spring 会将【是否自动提交】设置为false，等价于JDBC中的 connection.setAutoCommit(false);，在执行完之后在进行提交 connection.commit();。
事务的回滚规则：
指示spring事务管理器回滚一个事务的推荐方法是在当前事务的上下文内抛出异常。spring事务管理器会捕捉任何未处理的异常，然后依据规则决定是否回滚抛出异常的事务。
默认配置下，spring只有在抛出的异常为运行时unchecked异常时才回滚该事务，也就是抛出的异常为RuntimeException的子类(Errors也会导致事务回滚)，而抛出checked异常则不会导致事务回滚。
可以明确的配置在抛出那些异常时回滚事务，包括checked异常。也可以明确定义那些异常抛出时不回滚事务。
事务并发会产生的问题
| 术语 | 含义 |
|---|---|
| 脏读 | 一个事务读取到了另一个事务中尚未提交的数据 |
| 不可重复读 | 一个事务中两次读取的数据内容不一致 |
| 幻读 | 一个事务中两次读取的数据的数量不一致 |
第一类丢失更新
在没有事务隔离的情况下，两个事务都同时更新一行数据，但是第二个事务却中途失败退出回滚了， 导致对数据的两个修改都失效了。
例如：
张三的工资为5000，事务A中获取工资为5000，事务B获取工资为5000，汇入100，并提交数据库，工资变为5100；
随后，事务A发生异常，回滚了，恢复张三的工资为5000，这样就导致事务B的更新丢失了。
脏读
一个事务读取到了另一个事务中尚未提交的数据
脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
例如：
张三的工资为5000，事务A中把他的工资改为8000，但事务A尚未提交。
与此同时，事务B正在读取张三的工资，读取到张三的工资为8000。
随后，事务A发生异常，回滚了事务，张三的工资又回滚为5000。
最后，事务B读取到的张三工资为8000的数据即为脏数据，事务B做了一次脏读。
不可重复读
一个事务中两次读取的数据内容不一致
是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。
例如：
在事务A中，读取到张三的工资为5000，操作没有完成，事务还没提交。
与此同时，事务B把张三的工资改为8000，并提交了事务。
随后，在事务A中，再次读取张三的工资，此时工资变为8000。在一个事务中前后两次读取的结果并不致，导致了不可重复读。
第二类丢失更新
不可重复读的特例。
有两个并发事务同时读取同一行数据，然后其中一个对它进行修改提交，而另一个也进行了修改提交。这就会造成第一次写操作失效。
例如：
在事务A中，读取到张三的存款为5000，操作没有完成，事务还没提交。
与此同时，事务B存入1000，把张三的存款改为6000，并提交了事务。
随后，在事务A中，存储500，把张三的存款改为5500，并提交了事务，这样事务A的更新覆盖了事务B的更新。
幻读
一个事务中两次读取的数据的数量不一致
是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
例如：
目前工资为5000的员工有10人，事务A读取到所有的工资为5000的人数为10人。
此时，事务B插入一条工资也为5000的记录。
这时，事务A再次读取工资为5000的员工，记录为11人。此时产生了幻读。
不可重复读和幻读的区别
不可重复读的重点是修改，同样的条件，你读取过的数据，再次读取出来发现值不一样了
幻读的重点在于新增或者删除，同样的条件，第 1 次和第 2 次读出来的记录数不一样
JDBC的事务操作
进行事务操作：
// 开启一个事务(关闭自动提交)
start transaction;
// 提交事务并关闭当前的手动提交
commit;
// 事务回滚并关闭当前的手动提交
rollback;
结论：可以将一个需要操作多次数据库的业务放入到一个事务中处理，方便管理该业务对数据库数据的每一次影响。
数据库函数
为了简化操作，数据库提供了大量的函数给程序员使用（比如你想输入当前时间，可以调用now()函数）
函数可以出现的位置：插入语句的values()中，更新语句中，删除语句中，查询语句中。
字符串函数
函数：concat(s1,s2...sn) -- 常用。效果同使用 || 拼接字符串
描述：字符串 s1，s2 等多个字符串合并为一个字符串
实例：select concat("abc","-","def")
函数：trim(str) | ltrim(str) | rtrim(str) -- 常用
描述：字符串去掉两侧的空格 | 字符串去掉左侧的空格 | 字符串去掉右侧的空格
实例：select trim(" 数据 ")
函数：replace(s,s1,s2) -- 常用
描述：将字符串s中的s1字符替换成s2字符
实例：select replace("abc","b","x");
函数：substr(s, start, length) -- 常用
描述：从字符串 s 的 start 位置截取长度为 length 的子字符串
注意：索引从1开始(包含头)
实例：select substr("abcdefg",2,3);
函数：ucase(s) | upper(s)
描述：将字符串转换为大写
实例：select ucase("aaa")
函数：lcase(s) | lower(s)
描述：将字符串转换为小写
实例：select lcase("aaa")
函数：char_length(str)
描述：返回字符串 str 的字符数
实例：select char_length("hello,树先生");
函数：length(str)
描述：返回字符串 s 的字节数
（一个中文字符占3个字节 中文标点占3个字节 英文字符和英文标点都占一个字节）
实例：select length("hello,树先生");
函数：locate(s1,s)
描述：从字符串 s 中获取 s1 的开始位置
注意：索引从1开始
实例：select locate("l","hello");
函数：strcmp(str1,str2)
描述：比较字符串大小，左大于右边返回1，左等于右边返回0，左小于于右边返回-1，
实例：select strcmp("a","b");
数值函数
描述：四舍五入
函数：round(x) -- 常用。四舍五入成整数
函数：round(x,d) -- x指要处理的数，d是指保留几位小数
实例：round(1.5)
函数：ceil(x) | floor(x) --常用
描述：向上（下）取整
实例：ceil(1.2) floor(1.2)
函数：truncate(x,y)
描述：将X按照D长度在小数点后按照位数直接进行截取。-- MySQL数据库
实例：truncate(1.23456,2);
函数：substring(string [from int] [for int])
描述：抽取子字串。-- Postgresql数据库
实例：substring('Thomas' from 2 for 3)
函数：abs(x)
描述：返回 x 的绝对值
实例：abs(-1) -- 负变正
函数：mod(x,y)
描述：返回x mod y的结果，取余
实例：mod(10,9) -- 余数：1
描述：返回 0 到 1 的随机数
-- MySQL
函数：rand()
-- Postgresql
函数：random()
-- Oracle
函数：dbms_random.value()
函数：dbms_random.value(a,b) -- a为下限，b为上限，生成下限到上限之间的随机数，但不包含上限
聚合函数
常用于group by从句的select查询中
avg(col) -- 返回指定列的平均值
count(col) -- 返回指定列中非null值的个数
min(col) -- 返回指定列的最小值
max(col) -- 返回指定列的最大值
sum(col) -- 返回指定列的所有值之和
-- 返回由属于一组的列值使用逗号进行隔开拼接组合而成的结果
group_concat(col) -- MySQL
wm_concat() -- Oracle
string_agg(字段,',') -- Postgresql
CASE表达式
相当于java中swtich语句
语法
SELECT
CASE [字段,值]
WHEN 判断条件1
THEN 希望的到的值1
WHEN 判断条件2
THEN 希望的到的值2
ELSE 前面条件都没有满足情况下得到的值
END as 别名
FROM table_name;
MySQL数据库
菜鸟教程：https://www.runoob.com/mysql/mysql-functions.html
日期函数
函数：now() | curdate() | curtime() --常用
描述：获取系统当前日期时间、日期、时间
实例：select now() select curdate() select curtime()
函数：adddate(date,n) | subdate(date,n) --常用
描述：计算起始日期 date 加（减） n 天的日期
实例：select adddate(now(),7); select subdate(now(),7)
函数：datediff(d1,d2) --常用
描述：计算日期之间相隔的天数
实例：select datediff(now(),"1989-10-02");
函数：date_format(d,f) --常用
描述：按表达式f的要求显示日期 d
实例：select date_format(now(),"%Y-%m-%d %H:%i:%s %p");
函数：year(date) | month(date) | day(date)
描述：从日期中选择出年、月、日
实例：select year(now()) select month(now()) select day(now())
函数：last_day(date)
描述：返回月份的最后一天
实例：select last_day(now())
函数：quarter(date)
描述：返回日期 date 是第几季节，返回 1 到 4
实例：select quarter(now())
IF表达式
相当于java中三元运算符
语法
SELECT IF(1 > 0,'真','假') from 表名;
Oracle数据库
decode函数
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
-- 含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
......
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
MySQL和Postgresql数据库没有decode函数，可用CASE表达式替代。
日期函数
-- 获取系统当前时间
sysdate
-- 实例
select sysdate from dual;
-- 获取系统当前时间并格式化为字符串
to_char(sysdate,'yyyy-MM-dd HH24:mi:ss')
-- 把字符串解析成时间
to_date('2015-08-12 15:53:37','yyyy-MM-dd HH24:mi:ss)
-- 当前时间减去7分钟
select sysdate - interval '7' minute from dual
-- 表示当前时间减去7小时
select sysdate - interval '7' hour from dual
-- 表示当前时间减去7年
select sysdate - interval '7' year from dual
-- 时间转换中字符的意义
表示year的：
y 表示年的最后一位
yy 表示年的最后2位
yyy 表示年的最后3位
yyyy 用4位数表示年
表示month的：
mm 用2位数字表示月
mon 用简写形式， 比如：11月 或者 nov
month 用全称， 比如：11月 或者 november
表示day的：
dd 表示当月第几天
ddd 表示当年第几天
dy 当周第几天，简写， 比如：星期五 或者 fri
day 当周第几天，全称， 比如：星期五 或者 friday
表示hour的：
hh HH HH12 表示小时 12进制
hh24 HH24 表示小时 24小时
表示minute的：
mi 2位数表示分钟
表示second的：
ss 2位数表示秒 60进制
表示季度的：
q 1位数表示季度 (1-4)
表示周的：
w 表示当月月的第几周
ww 用来表示当年第几周 w用来表示当月第几周。
Postgresql数据库
日期函数
-- 返回当前实际的时间，即使在同一个 SQL 语句中也可能返回不同的值
clock_timestamp()
-- 获取当前事务开始的完整时间，包括时区，秒也保留到了6位小数。在同一个事务期间，多次调用相同的函数将会返回相同的值
now()
current_timestamp
transaction_timestamp()
-- 实例。得到的结果：'2014-12-24 09:28:31.545145+08'
select now();
-- 只显示当前的时间，不包括日期
current_time
-- 实例
select current_time;
-- 只显示当前的日期，不包括小时等信息
current_date
-- 实例
select current_date;
-- 获取系统当前时间戳并格式化为字符串
to_char(clock_timestamp(),'yyyy-MM-dd HH24:mi:ss')
-- 把时间戳转换成字符串
to_timestamp('2014-12-24 09:28:31', 'yyyy-MM-dd HH24:mi:ss')
索引
将数据进行排序整理的过程就称为索引。根据索引去查，提高查询效率，减少耗时时间。
索引是帮助数据库高效获取排好序的数据结构
索引的分类、优缺点、创建原则
索引的分类
-
主键（约束）索引（约束 + 索引）
主键就是一个索引，在索引的基础上具有约束（非空和唯一）行为
主键约束 + 提高查询效率
-
唯一（约束）索引
唯一约束 + 提高查询效率
-
普通索引
仅提高查询效率
-
组合（联合）索引
多个字段组成索引提高查询效率
索引的优缺点
-
优点：提高查询效率
-
缺点：索引占用磁盘空间
添加记录、更新、修改时，也会更新索引，会间接影响数据库的效率。
索引的创建原则
-
经常使用where条件搜索的字段
-
经常使用表连接的字段（内连接、外连接） 外键
-
经常排序的字段 order by
注意：索引本身会占用磁盘空间，不是所有的字段都适合增加索引
常见索引失效情况
-
没有查询条件，或者查询条件没有建立索引
-
like查询以%开头
-
where子句使用or时，只要条件有一个没有索引，那么都会进行全表扫描
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
-
where子句在索引列上进行运算或使用函数
-
where子句使用 !=、<，>操作符或 not in、is not null判断时
-
where子句使用字符串时没有使用单引号，导致数据类型隐形转换
-
使用复合索引作为条件时，单独引用复合索引里非第一位置的索引列
索引的数据结构
B+树 由B树演变而来，将所有的数据都存储在了叶子节点中，非叶子节点值存放索引加指针
MySQL索引语法
创建索引
① 创建表时指定【常用】
-- 创建教师表
CREATE TABLE teacher(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主键索引
name VARCHAR(32),
email VARCHAR(40) UNIQUE, -- 唯一索引
sex VARCHAR(5),
birthday DATE,
INDEX(`name`) -- 普通索引
);
② 直接创建
-- 创建普通索引
create index 索引名 on 表名(字段);
-- 创建唯一索引
create unique index 索引名 on 表名(字段);
-- 创建普通联合索引
create index 索引名 on 表名(字段1,字段2);
-- 创建唯一联合索引
create unique index 索引名 on 表名(字段1,字段2);
-- 创建学生表
CREATE TABLE student(
id INT,
name VARCHAR(32),
email VARCHAR(40)
);
-- name字段设置普通索引
CREATE INDEX name_idx ON student(name);
-- email字段设置唯一索引
CREATE UNIQUE INDEX telephone_uni_idx ON student(email);
③ 修改表时指定
-- 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
alter table 表名 add primary key(字段); -- 默认的索引名叫：primary
-- 添加唯一索引（除了NULL外，NULL可以出现多次）
alter table 表名 add unique(字段); -- 默认的索引名叫：字段名
-- 添加普通索引
alter table 表名 add index(字段); -- 默认的索引名叫：字段名
-- 指定id为主键索引
ALTER TABLE student ADD PRIMARY KEY(id);
-- 指定name为普通索引
ALTER TABLE student ADD INDEX(name);
-- 指定email为唯一索引
ALTER TABLE student ADD UNIQUE(email);
删除索引
-- 直接删除
drop index 索引名 on 表名;
-- 修改表时删除
alter table 表名 drop index 索引名;
数据库性能调优
MySQL性能调优
查询耗时语句
查看数据库的累计操作
-- 查询累计操作数据影响次数
show global status like 'Innodb_rows%';
-- 结果：
Innodb_rows_deleted 0
Innodb_rows_inserted 611
Innodb_rows_read 857
Innodb_rows_updated 0
数据库自带日志记录：慢查询日志
作用：记录查询耗时语句
-- 查看慢查询日志开启情况。默认慢查询是关闭的
show variables like '%slow_query_log%';
-- 查看慢查询时间配置。默认10秒耗时临界点会记录
show variables like '%long_query_time%';
开启慢查询日志
set global slow_query_log = on;
设置慢查询sql的时间阈值
-- 全局配置（下次生效...）
set global long_query_time=3;
-- 立即生效
set session long_query_time=3;
优化方案
由自带的慢查询日志或者开源的慢查询系统定位到具体出问题的 SQL，然后使用explain等工具来逐步调优，最后经过测试达到效果后上线。
-
索引的优化， 建立索引，并避免索引失效的情况
-
SQL 语句的优化
-
表的优化，分表 水平分割（按行） 垂直分割(按列)
索引的优化
-
尽量使用短索引
-
经常在where子句使用的列，最好设置索引
-
有多个列where或者order by子句的，应该建立复合索引
-
只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
-
尽量避免索引失效的情况
SQL 语句优化
-
应指定查询需要的字段，避免查询表中的所有字段（即避免使用 select * from table）
-
大部分情况，连接查询效率远大于子查询，除非子查询效率确实大于连接查询的特殊情况，否则尽量使用连接查询
-
多表连接查询时，尽量小表驱动大表，即小表 join 大表
表的优化
-
表的字段尽可能用NOT NULL限制
-
字段长度固定的表查询会更快
-
将大表分成小表；按时间或一些标志，水平分割（按行）、垂直分割(按列)
MySQL的分区及读写分离
mysql 的分表是一张表分成很多表后，每一个小表都是完整的一张表，都对应三个文件，一个数据文件，一个索引文件，一个表结构文件。分表后，单表的并发能力提高了，磁盘 I/O 性能也提高了。
mysql的分区是一张大表进行分区后，他还是一张表，将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。
mysql分表和分区的联系：都能提高mysql的性能，在高并发状态下都有一个良好的表现。
读写分离：实际的应用中，绝大部分情况都是读远大于写。所有的写操作都必须对应到 Master，读操作可以在 Master 和 Slave
机器上进行，Slave 与 Master 的结构完全一样，一个 Master 可以有多个Slave，所有的写操作都是先在 Master 上操作，然后同步更新到 Slave上。可以有效的提高 DB 集群的每秒查询率。
当读压力很大的时候，可以考虑添加 Slave 机器的方式解决，但是当 Slave机器达到一定的数量就得考虑分库了。当写压力很大的时候，就必须得进行分库操作。
MySQL数据库的存储引擎
将B+Tree的数据结构保存到物理磁盘：根据数据库的存储引擎来决定的
MySQL存储引擎的不同，那么索引文件保存的方式也有所不同，常见的有二种存储引擎MyISAM和InnoDB。
MySQL 5.5 及更高版本，默认的存储引擎是 InnoDB。在 5.5 版本之前，MySQL 的默认存储引擎是 MyISAM。
-
InnoDB 存储引擎（聚集索引–索引和数据是在一起的）
特点：行锁设计、支持事务、支持外键、支持非锁定读
使用 next-key-locking 的策略避免幻读现象
提供插入缓冲、二次写、自适应哈希索引、预读
采用聚集的方式存储表中数据
-
MyISAM 存储引擎（非聚集索引–索引和数据是分离的）
不支持事务、表锁设计，支持全文搜索
缓冲池只缓存索引文件，不缓存数据文件
MyISAM 存储引擎表由 MYD（存放数据文件）和 MYI（存放索引文件）组成。
扫一扫
597
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
