数据库一(自用)

数据库

数据库特点

1、降低存储数据的冗余度
2、保持数据的一致性、完整性和安全性

数据库完整性

可靠性+准确性=数据完整性

数据完整性的分类
1、实体完整性
保证一行数据的有效性
2、域完整性
保证一列数据是有效的
3、应用完整性
保证引用的编号是有效的
4、用户自定义完整性
保证自定义规则

数据完整性：
实体完整性：不能出现两条一模一样的记录，实现有两个：主键约束、唯一约束。
域完整性：保证指定列输入值得合法性，实现方式：非空约束、默认值、检查约束(check)。
引用完整性：保证从表的外键，必须引用主表中主键出现的值，实现方式：外键约束

非空约束是指，如果该列的值不能为null值。
默认值（DEFAULT）是指，如果该列的值不能做插入，那么以默认值填充该列的值。如果该列的值做
了插入，那么以插入值为准。
检查约束是指，规定改列的值得取值范围。

唯一约束（Unique）：
唯一约束是值给定列的所有值必须唯一，该列在表中每一行的值必须唯一，它和
主键约束的区别在于该列可以为空，并且可以在一张表中给多个列设置唯一约束。

主键约束（Primary key）：
主键是表中的一到多个列，主键列不能为空，也不能重复，一个表中只能有一个主键。

外键约束（foreign key）：
外键是指从表的某列与主表的某列存在依附关系。
外键约束是指在外键关联主键上强制加上一个约束，如果违反该约束，则不允许该条
数据的修改。 注意：没有建立外键约束不等于没有外键。
– 添加外键约束
– fk开始的约束表示外键约束，pk开始的约束表示主表约束
– 外键只出现在从表中
alter table t_car add constraint fk_1 foreign key(manId) references t_man(id);
含义 ：更新表 t_car 添加约束 外键(manId) 引用 t_man(id)

数据模型

在数据库的发展中，出现了许多数据模型，层次模型、网状模型、对象模型和关系模型。
关系模型使用居多。

关系模型：把世界看做由实体（Entity）和联系（Relationship）组成，实体就是指在现实世界客观存在并可相互区别的事物，
他可能是有形或者无形的、具体的或抽象的、有生命的或无生命的。

实体所具有的某一特性称为属性（Attribute）。实体可以通过若干属性来描述。

以关系型模型来创建的数据库称为关系型数据库。
关系型数据库：以表作为实体，以表之间的主外键关系作为联系的数据库结构。

表（table）是关系型数据库的核心单元，是储存数据的地方。
在表的内部，数据被分为列（column）和行（row）。填入表中的每一行，代表一个实体，也就是说表中的每一行代表
真实世界的一个事物。
行代表一个实体。列代表实体的属性，同时也限定了数据的类型。
主键：在关系型数据库表中，用一个唯一的标识符来标识每一行的标识就是主键。
外键：用来表达表与表之间的联系的标识。

RDBMS是管理、操作、维护关系型数据库的一种软件程序。

SQL是关系型数据库的标准编程语言，使用SQL语句访问和操纵数据库中的数据。

常见的关系
一对一:
在开发中,例如:person idcard
思路1:将两个实体合二为一。
思路2:在一个表上将这个表的主键设置成外键且添加外键约束。

一对多:
在开发中,在多表的一方添加一个外键,外键的名称一般为主表的名称_id,字段类型一般和主表的主键的类型保持一致。
为了保证数据的有效性和完整性,在多表的一方添加外键约束(不是必须的,也可以通过java程序来控制)
格式:
alter table 多表名称 add foreign key(外键名称) references 主表名称(主键名称)

多对多:
在开发中,一般引入一个中间表,在中间表中存放另外两张表的主键.这样就可以将多对多的关系拆分两个一对多的关系。
为了保证数据的有效性和完整性,在中间表上添加两个外键约束(不是必须的,也可以通过java程序来控制)

外键约束的特点:
1.主表中不能删除从表中已引用的数据。
2.从表中不能添加主表中不存在的数据。

数据库语言：
1、数据库定义语言（DDL）
2、数据查询语言（DQL）
3、数据操作语言（DML）
4、数据库控制语言（DCL）
5、事物控制语言（TCL）

数据库定义语言（DDL） create drop alter

创建、修改、删除数据库的内部数据结构。

查看数据库 show databases
选择要使用的数据库 use 数据库名
查看你当前使用的数据库 select databases
查看数据库使用的表 show tables
查看另一个数据库 show tables from 数据库名
创建数据库 create database 数据库的名称
导入sql文件 source 文件路径
查看表的数据结构 desc 表名

创建表：
create table 表名(
字段名 约束 primary key (主键) auto_increment(自动增长列)
…
);

– 添加地址列
alter table 表名 add 添加的列；

–修改列
ALTER TABLE 表名CHANGE 旧列 新列 ;

–删除列
ALTER TABLE 表名 DROP COLUMN 列名;

数据查询语言（DQL）

用于数据库中的数据查询。关键词 select

语句的执行顺序：
1、form 将硬盘的表文件加载到内存
2、where 将符合条件的数据行摘取出来，生成一张新的临时表
3、group by 根据列中的数据种类，，将当前临时表划分成若干个新的临时表
4、having 可以过滤group by 生成的不符合条件的临时表
5、select 对当前临时表进行整列读取
6、order by 对select生成的临时表，进行重新排序，生成新的临时表
7、limit 对最终生成的临时表的数据行，进行截取
where支持的运算符

having和where的区别：
二者都是过滤条件，where运行在分组前，因此不能执行任何聚合函数。having是运行在分组后，只能用做聚合函数。

功能关键词：
distinct 去除重复元素
like 模糊查询
"_” 表示任何单个字符
"%” 表示零个或多个任意字符

order by 排序。默认升序 或者使用asc(升序)、desc(降序)进行指定排序
in(参数，参数，参数…) 符合任意参数都可以。

group by 分组
分组后再查询列中只能出现分组列和聚合函数。

MySQL的日期格式：

1.日期是数据库本身的特色，也是数据库本身机制中的一个重要内容。
2.每个数据库处理日期是采用的机制都不一样，都有自己的一套处理机制，所以在实际开发中将日期定义为DATE类型的情况很少。
3.如果使用日期类型，java程序将不能通过。在实际开发中，一般会使用”日期字符串”创建日期。

数据操作语言（DML）

用于数据库中数据的修改、包括添加、删除、修改等。 关键词：insert delete update
添加数据：
insert into 表名(字段名) values（字段值）;

修改语句：
UPDATE 表名 SET 修改的内容 修改的条件；

删除表中的数据
delete from 表名 [where 条件];

delete from 表名；
truncate table 表名；
二者的区别：
delete 会记录日志，意味着删除后的数据还可以恢复，但是效率低。
truncate table 不会记录日志，删除后数据不能恢复，但是效率高。

多分支判读语句：
CASE WHEN 条件 THEN 返回值
WEEN 条件 THEN 返回值
…
ELSE 返回值 END

数据库控制语言（DCL）

控制数据库访问权限。

事物控制语言（TCL）

各种事务的管理能力。关键词： commit rollback

事物是用户定义的一个操作序列。事物认为，这些操作序列是一个不可分割的工作单位，
事物有四个特点：原子性，一致性，隔离性和持久性。

原子性：事务里面的操作单元不可切割,要么全部成功,要么全部失败
一致性:事务执行前后,业务状态和其他业务状态保持一致.
隔离性:一个事务执行的时候最好不要受到其他事务的影响
持久性:一旦事务提交或者回滚.这个状态都要持久化到数据库中

实现事物的步骤：
1、保证同一个业务方法中，所有的DAO使用同一个连接对象。
2、将连接对象的提交方式设置为手动提交。
然后，通过连接对象的commit()方法提交事物，通过连接对象的rollback()方法回滚事务。
Connection的中的方法：
setAutoCommit(false):手动开启事务
commit():事务提交
rollback():事务回滚

不考虑隔离性会出现的读问题
脏读:在一个事务中读取到另一个事务没有提交的数据
不可重复读:在一个事务中,两次查询的结果不一致(针对的update操作)
虚读(幻读):在一个事务中,两次查询的结果不一致(针对的insert操作)

通过设置数据库的隔离级别来避免上面的问题
read uncommitted 读未提交 上面的三个问题都会出现
read committed 读已提交 可以避免脏读的发生
repeatable read 可重复读 可以避免脏读和不可重复读的发生
serializable 串行化 可以避免所有的问题

聚合函数

count：统计行数量
count(*) 统计全部数据，count(列名) 空列不统计

sun：获取单个列的合计值
avg：计算某个列的平均值
– avg统计平均分也只统计非空列

max：计算列的最大值
min:计算列的最小值

数据处理函数：

子查询

如果子查询返回的值为单行单列，才能使用>、=、!=、<、<=、>=
如果子查询返回的值为多行单列，只能使用in或not in

多表查询

笛卡尔积现象
含义：若两张表进行连接查询的时候没有任何矫健限制，最终的查询结果总数是两张表记录的乘积，这就是笛卡尔积现象。
2.跨表查询语句格式

内连接：
只显示两个表中有对应记录的数据。

外连接：
定义：** 可以显示一个表中所有的数据，无论该记录是否有关联**
A表和B表能够完全匹配的记录查询出来之外，将其中一张表的记录无条件的查询出来，对方调用匹配的记录时，会自动模拟出null值与之匹配。

注意：外连接的查询结果条数》=内连接的查询结果条数

左外连接（左链接）：
定义：包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行
Left：让左边表无条件全部显示

右外连接（右连接）：
定义：包含左边表的全部行（不管右边的表是否存在于它们匹配的行），以及右边表中全部匹配的行。
right：让右边表无条件全部显示

全连接：
定义：包含左右两个表的全部行，不管另一边的表中是否存在与之它们匹配的行。

如何删除主表记录：两个表建立了关联之后，当删除主表记录时，如果该主表记录有从表记录所关联，那么，由于有外键约束存在，则不允许删除。
删除方法（视要求而定）：
1、级联删除：先将主表记录对应的从表记录删除，然后再删除主表记录。
2、外键置空：先将该主表记录对应的从表记录外键设置为null值，然后再删除主表记录。