数据库知识

数据和信息:

Data数据:任何描述事物的文字或符号都可以称为数据。数据需要保存，保存的介质有内存或硬盘。 内存中的数据是临时的，随着系统的关闭，数据也会消失。 硬盘中的数据是永久，就算系统关闭，数据依然保留。excel等文件保存数据是一种保存到硬盘中的途径，

数据库ataBase，称为数据库，简称DB

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

数据库管理系统

DataBase Manager System，简称DBMS

通常所说的数据库，其实是指数据库管理系统，如MySQL、SQLServer、Oracle等。 是一种操作和管理数据库的大型软件，用于建立、使用和维护数据库。

 总结

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

常见的数据库管理系统

关系型数据库

关系型数据库是主流的数据库类型。数据通过行row，列column的形式(表格)保存，

每行称为一条记录，每列称为一个字段。

字段通常为Java中某个实体类的属性，通过这个类创建的对象，就是一条记录。

如class Employee，有员工编号、部门、工资等属性。

 对应数据库中有一张Employee员工表，这张表中有员工编号、姓名、部门等字段。

优点:

易于维护：数据都是以表的结构存储，格式一致

使用方便：SQL语句通用，可用于不同的关系型数据库

支持复杂查询：可以通过SQL语句在多个表之间查询出复杂数据

缺点:

读写性能差，在处理海量数据时， 频繁读写效率略低

表结构不易改动，灵活度稍欠佳 高并发读写时，硬盘I/O决定了读写速度

非关系型数据库

数据通过对象的形式保存，对象可以是一个键值对、文档、图片等。

非关系型数据库的特点

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

MySQL8.x的使用

• show databases;查看所有数据库
• use数据库名;切换到指定数据库
• create database数据库名;创建指定数据库
• drop database 数据库名;删除指定数据库
• show tables;在某个数据库中，查看其中的所有表

数据库管理系统图形化管理工具:navicat datagrip sqlyog

保存时输入表名 每张表需要一个编号"id"列，通常设置为主键，目的是为了区分每条记录。

主键列中的数据不能重 复，通常还需将主键列设置为自增列。

由于mysql中大小写不敏感，字段名使用全部小写字母，多个单词用_隔开

数据类型和所占长度根据实际情况选择 如果某列数据必须要填写，将"不是null"勾选

如果某个字段有默认值，可以在设计表的时候设置，字符串用单引号引起来

最好加上注释

SQL:Structrued Query Language结构化查询语言:用于操作关系型数据库的一门语言，可以来创建，维护数据库和数据

操作数据库:

创建数据库:create database 数据库名;

切换数据库:use 数据库名;

删除数据库:drop database 数据库名;

操作数据表

创建数据表:

create table 表名(

字段名1 数据类型 [字段特征],

字段名2 数据类型[字段特征],

字段名n 数据类型[字段特征]

)

修改数据表:

修改表时，要保证不影响现有数据

对表重命名:alter table 旧表名 rename to 新表名;

添加新字段:alter table 表名 add column 字段名1 数据类型 [字段特征];

修改字段:alter table 表名 change 旧字段名 新字段名 数据类型[字段特征];

删除字段:alter table 表名 drop 字段名;

添加约束

添加唯一约束:alter table 表名 add unique(字段名);

添加主键约束:alter table 表名 add primary key (字段名);

添加默认约束:alter table 表名 alter 字段名 set default'默认值';

添加外键约束:alter table 从表表名 add foreign key(从表外键字段)references 主表表名(主表主键字段)

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

建表的同时添加约束:

 

 删除数据表

drop table 表名

如果要删除有外键关联的表，要先删除从表，再删除主表。

数据完整性

数据完整性是指数据精确可靠。不能保存无意义或无效的数据。 如不合理的年龄性别、全为空的记录、重复记录等。

为了保证保存在数据库中的数据是完整数据，就要在设计数据表的时候添加一些约束或字段特征来保证 数据的完整性。

MySQL中常见的数据类型:

 

 约束

约束字段的特征	关键字	概念
非空约束	不写或null	是否允许该字段为空
主键约束	primary key	主键(primary key)。也可以称为主关键字、主码。用于区分表中的每条记 录的一个字段。通常ID为主键约束
唯一约束	unique	保证该字段不能重复
默认值约束	default	添加记录时，不赋值，让其使用默认值
外键约束	foreign key references	在主从关系的表中，给从表中的某个字段添加外键约束后，该字段的值只 能来自于主表中的某个主键字段

操作数据

数据的操作是指数据的增删改查，检查CURD

数据添加insert:

数据添加时，都是整行添加。不能只给一个字段添加数据。

如果只给某个字段添加数据，实际是修改。

给所有字段赋值

insert into 表名 values('值1','值2'...)

表名后无需添加字段名，添加时保证值的顺序和数量与表中字段的顺序和数量一致

遇到自增字段，不能省略不写，可以使用0、null或default让其填充自增值

遇到有默认值的字段，不能省略不写，使用default让其填充默认值

遇到允许为空的字段，不能省略不写，使用null让其设置为空

给指定字段赋值:

insert into 表名(字段1,字段2...)values('值1','值2'...)

• 至少需要将所有非空字段写出来
• 可以不用写出自增字段、有默认值的字段和允许为空的字段
• 表名后的字段顺序要和值的顺序一致

批量添加:

可以用insert into 语句添加多条记录

2. insert into 表名[(字段1,字段2...)]values
3. ('值1','值2'...)

复制代码

数据修改update

修改单个字段的所有值:

update 表名 set 字段='值';

修改多个字段的所有值:

update 表名 set 字段='值',字段='值'...;

根据条件修改(where子句)

update 表名 set 字段='值',字段='值'...where条件;

指定值:

使用=指定某个字段的值

update 表名 set 字段='值',字段='值'...where 字段='值';

指定范围:

使用>、、>=、表示范围，使用and、&&、or、||将多个条件关联

使用"字段 between A and B"表示字段在[A,B]范围内

使用!=或<>表示不等于

指定集合:

某个字段的值在某个集合中时: 字段 in ('值1','值2'...)

某个字段的值不在某个集合中时: 字段 not in ('值1','值2'...)

空值匹配:

某个字段为空: 字段 is null

某个字段不为空: 字段 is not null

模糊查询:

%表示未知长度字符串

_表示一个字符串

数据删除delete

删除所有

delete from 表名;或:truncate table 表名;

delete会保留自增列删除前的值，删除后再添加时，自动从删除前的值开始自增

truncate会重置自增列的值。删除所有数据时效率更高

如果要删除主从关系且设置了外键的表中的数据。从表中如果有数据，不能直接删除主表中相关联 的数据，先删除从表数据，再删除主表数据。

 条件删除:

delete from 表名 where 条件

删除时的条件同修改时的条件语句。

数据查询select

查询所有字段

select*from 表名;

查询指定字段:

select 字段名 from 表名；

实际开发中，很少使用*查询，因为*查询的效率远低于查询指定字段

字段重命名

select 字段1 as'自定义名称',字段1'自定义名称'...from 表名;

查询指定条数

-- 查询前N条记录

select*from 表名 limit N;

-- 查询从索引N开始的M条记录:

select*from 表名 limit N,M;

-- 每页显示size条记录,第page页

select*from 表名 limit (page-1)*size,size;

条件查询:

select*from 表名 where 条件;

查询时的条件同修改时的条件语句。

排序

select*from 表名 where 条件 order by 排序字段 排序规则,排序字段 排序规则...

排序默认是升序 排序规则不写或写asc表示升序 排序规则写desc表示降序

排序时如果有条件，排序写在条件语句之后 多字段排序时，在order by 之后写多个字段及排序规则，用逗号隔开，按字段顺序排序

统计函数(聚合函数)

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

 数字相关函数

 字符串相关函数

 时间相关函数:

 分组:

select 分组字段,统计函数 from 表名 group by 分组字段 having 统计函数条件

按指定的字段进行分组，会将该字段值相同的记录归纳中同一组中。

分组通常配合统计函数使用。

如果统计函数作为条件，不能使用where，而要使用having子句，将整个having子句放在分组之后。

group_concat()函数

将分组后的数据拼接成字符串

group_concat(字段或字符串,字段或字符串...)

-- 根据图书类型分组，查看每组下的图书名和作者

select group_concat (book_name,'--',book_author)from book_info group by type_id

实际业务对应SQL操作:

登录:登录通常是使用用户名和密码查询用户表

select*from 用户表 where用户名=?and 密码=?

如果没有查询到数据，即null，说明用户不存在或输入有误。

如果查询到数据，返回查询到的内容。

注册:注册通常是使用用户名和密码添加一条记录，注册时一般还会验证要注册的用户是否存在。

insert into 用户表 values(注册信息1,注册信息2..)

充值:充值消费都是将数组进行更新

update 用户表 set 余额=余额+/-值 where 主键=值

2. cerate table userinfo(
3. userid int not null primary key auto_increment,
4. username varchar(20)not null,
5. password varchar(20)not null,
6. balance double not null
7. )
8. -- 注册
9. -- 监测要注册的用户是否存在
10. select*from userinfo where username='admin';
11. -- 如果上一条没有查询到数据，才进行添加
12. insert into usernifo values(null,'admin','123123',0);
13. -- 登录
14. select*from userinfo where username='admin'and password='123123';
15. -- 充值
16. -- 获取注册时自动生成的id
17. update userinfo set balance=balance+100 where userid=1

复制代码

删除:

逻辑删除:不删除数据，只是不显示数据

查询所有未删除的数据: 

select*from 用户表 where 是否删除=0

假设要删除小王，只需要将是否删除的值改为1

update 用户表 set 是否删除=1 where 用户编号=小王编号

物理删除

真是删除:

delete from 用户表 where 用户编号=小王编号

连接查询

交叉连接，笛卡尔积

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

集合A：{a,b}

集合B：{1,2,3}

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

内连接

在交叉连接的基础上，筛选出关联的数据。
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中的数据

嵌套查询

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

-- 查询价格最低的图书信息
select * from book_info where book_price = (select min(book_price) from book_info)
-- 按类型分组，查询每组中图书价格大于平均价格的图书
select * from book_info bi inner join 
(select type_id,avg(book_price) as avg from book_info group by type_id)temp
on bi.type_id = temp.type_id  where book_price > avg

Jar:

以.jar为后缀的文件，称为Java的归档文件。保存的是Java的字节码.class文件

在Java程序中导入某个.jar文件后，就能使用其中的.class文件。

在Java项目中使用.jar文件

1.创建一个Java项目，在其中最好新建一个文件夹，将.jar文件保存在里面

2.在.jar文件上右键，点击“add as library”

JDBC:

Java DataBase Connectivity Java数据库连接

用于Java程序连接不同的数据库。

实际是Java中定义的相关数据库连接时所需的接口的集合，不同的数据库对其进行了实现。

核心接口

• Connection：用于设置连接的数据库的地址、账号、密码

• PreparedStatement：用于预处理、执行SQL语句

• ResultSet：用于保存查询后的数据

以上接口都来自于java.sql包中

单表查询

 SQL注入

在构造sql语句时，如果使用字符串拼接的方式构造可变的sql语句，可能会造成sql注入的风险，导致执行不是预期的sql语句

 在构造sql语句时，使用?占位，通过preparestatement方法防止sql注入的风险

简化JDBC

DBUtil数据库工具类:

 

 数据库设计:

实体类关系模型:

实体Entity：一张表就是一个实体。

关系Relationship：实体与实体之间的关系。

实体关系模型也称为ER模型。 用图形表示ER模型时，这个图称为ER图。

用矩形表示实体，用椭圆形表示实体的属性，用菱形表示实体之间的关系，用直线连接各个图形。

实体之间的关系:

一对一:

实体A与实体B之间唯一对应。

ER图:

 在数据库中创建表的过程:

可以使用一张表实现。但是后期维护拓展时较为不便。

国家名称和领导人姓名需要添加唯一约束。

 创建后两张表是独立的，并不能体现一对一关系，可以通过以下方式体现一对一。

方式一(建议使用)： 在国家表中添加领导人编号字段，将其设置为唯一；或在领导人表中添加国家编号字段，将其设置 为唯一。

 一对多/多对一

一对多：一个实体A对应多个实体B，一个实体B不能对应多个实体A。 如一个人有多辆车，一辆车不能对应多个人。

多对一：多个实体B对应一个实体A，多个实体A不能对应一个实体B。 如多个学员对应一个训练他们的教官，多个教练不能对应一个学员

在数据库中创建表的过程:

 多对多

一个实体A可以对应多个实体B，一个实体B也可以对应多个实体A。

如一个学生可以学习多门课程，一门课程可以对应多个学习它的学生。

如一个医生可以有多个病人，一个病人可以看多个医生。

 ER图

在数据库创建表的过程

•  创建学生表

  • 创建课程表

  • 体现多对多关系的表:成绩表

   总结

  一对一：创建各自的实体表，在任意一张表中添加另一表的主键字段，将其设置为唯一 一对多/多对一：先创建主表(一)，再创建从表(多)，在从表中添加主表的主键字段，外键可选 多对多：创建各自的实体表，再创建第三张表:"关系表"，在关系表中添加两个实体表中的主键字段，外 键可选。

   视图View

  视图可以当做数据库中的一个临时表，保存一些较为复杂的查询后的结果。 之后可以直接通过该视图查询数据，不需要再次编写复杂的sql语句。

  视图同时可以隐藏一些查询细节，定制查询数据。

  创建视图:

  create view 视图名 as

  查询的sql语句;

  使用视图:

  select*from 视图名;

  修改视图中的数据，会直接修改原始表中的数据

  删除视图:

  drop view 视图名;

  事务transaction

  事务是由一组sql语句组成的执行单元，这些sql之间一般都互相依赖。 如A给B转账

  1.update 表 set money = money-1000 where id=1

• 2.update 表 set money = money+1000 where id=2

  以上两句sql组成了一个转账的事务。一个事务要么全部执行，要么全部不执行

  事务的特性ACID

  Atomicity原子性

  事务是最小的执行单元，要么全部执行，要么全部不执行。

  Consistency一致性

  事务执行前后，必须让所有数据保持一致状态。(总体数据守恒)

  Isolation隔离性

  多个事务并发执行时应该互相隔离，互不影响。

  Durability持久性

  事务一旦提交，对数据的改变是永久的

  事务的使用

  提交commit

  回滚rollback

  mysql中的事务默认是自动提交的。 如果关闭了事务自动提交，在执行某个事务途中，如果出错，可以使用rollback进行回滚，让数据回到 事务执行之前的状态。

• 查看事务自动提交开启状态：select @@autocommit 1表示开启了自动提交 0表示关闭了自动提 交

• 设置事务不自动提交：set @@autocommit=0

  如果不出错，通过commit提交事务后，无法进行回滚

  手动提交/回滚事务

  1.关闭事务自动提交:set @@autocommit=0

• 2.开启事务:start transaction

• 3.事务要执行的sql

• 4.没有提交之前，可以使用rollback回滚

  5.如果要提交，使用commit，一旦提交成功，无法rollback

   

  事务并发可能出现的问题

   在同一时刻同时执行多个事务时，称为事务并发。

   查看事务隔离级别

  select@@transaction_isolation

  查看事务隔离级别