数据库笔记

数据库

DataBase，简称为DB

运行在操作系统上，按一定的数据结构，保存数据的仓库。是一个电子化的文件柜。

数据永久保存在硬盘中。

数据库管理系统

DataBase Manager System，简称为DBMS

通常所说的数据库，是指数据库管理系统，如MySQL、Oracle等。

是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库。

总结

• 数据Data需要永久保存到数据库中
• 数据库DB是运行在操作系统上的一个软件
• 数据库管理系统DBMS是管理数据库的一个软件
• 学习数据库就是学习如何使用DBMS创建、使用数据仓库来管理数据

常见的数据库管理系统

关系型数据库

SQLServer
MySQL
Oracle
关系型数据库的特点

• 优点

  • 易于维护：都是使用表结构存储数据，格式一致
  • 使用方便：SQL语句通用，可用于不同关系型数据库
  • 复杂查询：可以通过SQL语句在多个表之间查询出复杂数据

• 缺点

  • 读写性能差，尤其是还是数据的高效读写
  • 固定的表结构，灵活度少欠
  • 高并发读写时，硬盘I/O决定了读写速度

非关系型数据库

Redis
非关系型数据库的特点

• 保存数据的格式多样
• 对于海量数据的读写性能高
• 不支持复杂查询

SQL语句

Structrued Query Language 结构化查询语言

用于操作关系型数据库的一门语言。可以用来创建、维护数据库和数据。

查看信息

查看所有数据库： show databases;
查看所有表： show tables;
查看表结构： desc 表名;
查看建表语言： show create table 表名;

数据库操作

创建数据库： create database 数据库名：
使用数据库： use 数据库名：
删除数据库： drop database 数据库名;

数据表操作

MySQL中常见的数据类型

整型
tinyint	短整型	对应java中的byte和short
int	整型	对应java中的int
bigint	长整型	对应java中的long

浮点型
float	单精度浮点型	对应java中的float
double	双精度浮点型	对应java中的double
decimal(宽度,精度)	指定保留的小数位数和整体宽度	如decimal(4,2) 3.1415926–>3.14

字符串
char(长度)	定长字符串	char(10)表示占10个字符，就算保存了3个字符，也占10个字符，对应java中的String
varchar(长度)	可变字符串	varchar(10)表示最多占10个字符，保存了3个字符，旧占3个字符，对应java中的String
text	文本

日期
date	日期	yyyy-MM-dd
time	时间	HH:mm:ss
datetime	日期时间	yyyy-MM-dd HH:mm:ss
timestamp(14或8)	毫秒	保存日期的毫秒数.14表示yyyyMMddHHmmss，8表示yyyyMMdd

数据完整性

字段特征	概念	关键字
非空约束	是否允许该字段为null	null表示可以为空，not null表示不可以为空
主键约束	主键(primary key)。用于区分表中的每条记录的一个字段。如果有现成的字段可以区分每条记录时，将该字段设置为主键字段;如果没有现成的字段可以区分每条记录时，通常会额外添加一个id字段用于设置为主键字段。通常一张表只有一个主键字段。	primary key
唯一约束	保证该字段值不能重复	unique
默认值约束	如果在不给该字段添加数据的情况下，保证该字段有一个默认值。	default
外键约束	在主从关系的表中，给从表的某个字段添加外键约束后，该字段的值只能来自于主表中的某个主键字段	foreign key references

操作数据表

1.创建数据表

create table 表名(
    字段名 数据类型 [字段特征],
    字段名 数据类型 [字段特征],
    ...
    字段名 数据类型 [字段特征]
)

2.删除数据表

drop table 表名;

3.修改数据表

• 对表重命名

  alter table 旧表名 rename to 新表名;
  

• 添加新字段

  alter table 表名 add column 字段名 数据类型 字段特征;
  

• 修改字段

  alter table 表名 change 旧字段名 新字段名 数据类型 字段特征;
  

• 删除字段

  alter table 表名 drop 字段名;
  

添加约束

1.添加非空约束

alter table 表名 change 旧字段名 新字段名 数据类型 not null;

2.添加主键约束

alter table 表名 add primary key(字段名);

3.添加唯一约束

alter table 表名 add unique(字段名);

4.添加默认值约束

alter table 表名 alter 字段名 set default '默认值';

5.添加外键约束

alter table 从表表名 add foreign key(从表外键字段) references 主表(主表主键字段)

添加约束的操作通常是对已存在的表进行修改和维护时使用。如果是一张新表，最好在创建表的时候设计好约束。

数据操作

增加
insert into 表名 values(值1,值2…);保证按表中字段顺序添加数据，不能缺少任何一个字段
自增字段用null
默认值字段用default
允许为空字段用null
修改
update 表名 set 字段=值,字段=值 [where 条件]
删除
delete from 表名 [where 条件];会保留自增列的值
truncate table 表名;会重置自增列的值
查询
select [字段|*] from 表名 [where 条件] [order by 字段] [having 条件]
limit N：查询前N条记录
limit N,M：从索引为N的开始查询M条记录
distinct：去重复
order by 字段1,字段2：多字段排序
group by 字段名：根据字段分组
having 统计函数条件

条件	符号
指定值	=、!=、<>
指定范围	>、<、>=、<=、 between 值 and 值
指定集合	[not] in (元素1,元素2…)
模糊查询(%表示未知长度字符串， _表示一个字符)	like ‘%值_’
空置	is [not] null
多条件	使用and、or、&、

统计函数(聚合函数)

select 统计函数(字段名) from 表名;

函数名
count(字段名)	统计数量
sum(字段名)	求和
avg(字段名)	平均
max(字段名)	最大
min(字段名)	最小

数学相关函数

函数	作用
abs(值或字段)	绝对值
pow(值或字段)	次幂
sqrt(值或字段)	开平方
round(值或字段)	四舍五入
ceil(值或字段)	向上取整
floor(值或字段)	向下取整

字符串相关函数

函数
length(字符串或字段)	得到字符串长度
trim(字符串或字段)/ltrim(字符串或字段)/rtrim(字符串或字段)	去除字符串首尾/首/尾空格
left(字符串或字段,n)/right(字符串或字段,n)	从字符串左/右开始截取n个字符
substr(字段或字符串,start)	从start开始截取至末尾
substr(字段或字符串,start,length)	从start开始截取length个字符
lcase(字符串或字段)/ucase(字符串或字段)	转换小写/大写
instr(字符串1,字符串2)/locate(字符串2,字符串1)	得到字符串2在字符串1中出现的顺序
reverse(字符串或字段)	翻转字符串
concat(字符串1,字符串2…)	拼接所有字符串
replace(字符串或字段,旧字符串,新字符串)	将字符串中的旧字符串替换为新字符串

时间相关函数

函数
now()	当前日期时间
current_date()/curdate()	当前日期
current_time()/curtime()	当前时间
year(日期)/month(日期)/day(日期)	得到年/月/日部分
datediff(时间1,时间2)	计算时间1与时间2相隔的天数
timediff(时间1,时间2)	计算时间1与时间2相隔的时分秒
TIMESTAMPDIFF(时间单位,时间1,时间2)	计算时间1与时间2之间相隔的指定时间单位

补充

if(条件,表达式1,表达式2)：条件为真，结果为表达式1，条件为假，结果为表达式2 group_concat()：拼接分组后的其他字段

嵌套查询

将查询出的结果继续嵌套使用在另一个查询语句中

-- 查询大于平均价格的图书
select * from 表 
where price >(select avg(price) from 表)

-- 根据作者分组，查询每组大于平均价格的图书
select * 
from 表 t1,(select author,avg(price) avg_price from 表)t2
where t1.author=t2.author and price>avg_price

多表查询

交叉连接、笛卡尔积

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积

集合A：{a,b}

集合B：{1,2,3}

集合A x 集合B={a1,a2,a3,b1,b2,b3}

select * from 表1,表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;

将两张表中的数据互相组合成一张新表，其中有很多无效数据。

内连接

select * from 表1,表2 where 表1.字段=表2.字段;
select * from 表1 inner join 表2 on 表1.字段=表2.字段;

-- 如查询图书类型表(类型编号、类型名称)和图书详情表(图书编号、类型编号、图书名称)
select * from 图书类型表 t1 ,图书详情表 t2 where t1.类型编号=t2.类型编号;
select * from 图书类型表 t1 inner join 图书详情表 t2 on t1.类型编号=t2.类型编号;

• 通常是通过主表的主键字段关联从表的外键字段
• 如果两张表中关联的字段名一致，一定要通过"表名.字段名"进行区分，通常给表重命名
• 如果使用inner join，带条件时需要加入where子句；如果使用,隔开各个表，带条件时使用and拼接条件
• 内连接只会显示两张表中有关联的数据

左连接

-- 在保证左表数据显示完整的情况下，关联右表中的数据，没有关联的数据用null表示
select * from 表1 left join 表2 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表，表2称为右表,会完整显示表1中的数据

右连接

-- 在保证右表数据显示完整的情况下，关联左表中的数据，没有关联的数据用null表示
select * from 表2 right join 表1 on 表1.字段=表2.字段;
-- 以上语句中表1称为左表，表2称为右表，会完整显示表2中的数据

数据库设计

实体关系模型

ER图

矩形：实体
椭圆形：实体的属性
菱形：实体之间的关系

实体关系分类

一对一：一个国家有一个领导人
1.根据两个实体创建两张表，都加上主键
2.在其中一张表中添加一个字段，保存另一张表的主键并设置唯一
一对多/多对一：一个班级有多个学生，一个学生不能有多个班级
1.先根据两个实体创建两张表，都加上主键
2.在从表中添加外键字段管理主表中的主键字段
多对多：一个学生学习多门课程，一门课程可以被多个学生学习
1.先根据两个实体创建两张表，都加上主键
2.创建第三张"关系"表，在该表中添加两个字段分别保存两个实体表中的主键

范式

第一范式1NF

字段不可再分

第二范式2NF

在满足1NF的基础上，消除部分依赖。
对于联合主键而言，所有非主属性字段必须完全依赖于主属性。

第三范式3NF

在满足2NF的基础上，消除传递依赖。

JDBC

Java提供了一套规范用于连接各种数据库。不同的数据库厂商根据该规范设计连接驱动。 java.sql包下一组接口用于操作数据库。
Connection接口 用于获取连接对象
PreparedStatement接口 用于发送sql语句，处理sql语句
ResultSet接口 用于保存查询后的结果集

加载MySQL驱动

Class.forName("com.mysql.cj.jdbc.Driver");

连接MySQL数据库字符串

String url="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai"; String username="root";
String password="root";

查询的步骤

1.获取连接对象Connection
2.构造sql语句String
3.预处理sql语句pst=conn.prepareStatement(sql)
4.给sql语句中的?赋值pst.setXXX(?顺序,值)
5.调用executeQuery()得到ResultSet结果集
6.遍历结果集rs.next()后rs.getXXX(字段顺序/字段名)
7.释放资源

增删改的步骤

1.获取连接对象Connection
2.构造sql语句String
3.预处理sql语句pst=conn.prepareStatement(sql)
4.给sql语句中的?赋值pst.setXXX(?顺序,值)
5.调用executeUpdate()得到受影响的行数
6.释放资源

事务transaction

一组sql执行单元，要么全部执行，要么全部不执行

事务的特性ACID

原子性Atomicity
事务是最小的执行单元
一致性Consistency
事务执行前后，数据整体保持一致。
隔离性Isolation
各个事务之间应当互不干涉
持久性Durability
事务一旦提交，改变是永久的

事务并发出现的问题

脏读
事务A读取到了事务B未提交的数据。
不可重复读
事务A中前后两次读取到的数据不一致。
事务A在读取过程中，事务B对数据进行了修改。
幻读
事务A在读取过程中，事务B向其中添加了数据，导致事务A读到了事务B中添加的"幻影"数据。

以上问题可以通过设置事务隔离级别解决。

事务隔离级别

隔离级别	能否出现脏读	能否出现不可重复读	能否出现幻读
Read Uncommitted未提交读RU	会	会	会
Read Committed已提交读RC(Oracle默认)	不会	会	会
Repeatable Read可重复读RR(MySQL默认)	不会	不会	会
Serializable可序列化	不会	不会	不会

事务相关指令

查看事务自动提交状态
select @@autocommit
修改事务自动提交状态
set @@autocommit=0/1 0表示不开启动， 1表示开启
手动开启事务
start transaction;
提交
commit;
回滚

rollback;
查询当前事务隔离级别
select @@transaction_isolation
设置事务隔离级别
set [session|global] transaction isolation level [Read Uncommitted | Read Committed | Repeatable Read |Serializable]

视图

定义

create view 视图名 as
sql语句;

使用

select * from 视图名;

删除视图

drop view 视图名;

触发器

定义
create trigger 触发器名
触发时机 触发条件 on 表 for each row
begin
触发器触发时执行的sql语句；
end

创建触发器

create trigger 触发器名
触发时机 触发操作 on 表名 for each row
begin
    触发时执行的sql;
end

-- 创建操作日志表
create table log(
    log_id int not null primary key auto_increment,
    log_opt varchar(20) not null,
    log_time datetime not null
)

-- 创建触发器，在向客户表中添加记录后，自动向日志表中添加记录
create trigger mytrigger
after insert on customer for each row
begin
    insert into log values(null,'添加了数据',now())
end

使用触发器

一旦创建成功触发器，无需刻意调用，在执行相应的操作时，自动执行触发器

-- 只对customer表做插入操作，会自动向log表中添加记录
insert into customer values(null,'测试插入','123123',0,null);

删除触发器

drop trigger 触发器名;

存储过程

创建
create procedure 存储过程名(in/out/inout 参数名 数据类型)
begin
sql语句;
end

调用

call 存储过程名(参数);

删除存储过程

drop procedure 存储过程名;