数据库一 —— 数据表结构

一、数据表的关联关系

1、一对一关联关系(One To One)

(1)在A表中能够找到唯一的对应关系；同时在B表中的一条数据，在A表中也能够找到一条与之对应的数据，那么A表和B表就呈现了一对一关联关系。

(2)解决一对一关联关系的方案是分别在对方的数据表中创建一个字段，用于存储对方表的主键。

2、多对一关联关系(Many To One)

(1)在A表中的一条数据，则B表中能够找到唯一的对应关系；在B表中的一套数据，在A表中能够找到多条对应关系，那么站在A表的角度，A表和B表就形成了多对一关联关系。

(2)员工与部门，学生与班级形成了点典型的多对一关联关系。

(3)解决多对一关联关系的方案是在多的一方数据表中创建以创建一个字段用于存储一的一方的主键。

3、一对多关联关系(One To Many)

(1)在A表中存在一条数据，在B表中能够找到多条与之对应的数据；但是在B表中的一条数据，在A表中只能够找到到一条与之对应的数据，那么此时A表和B表就形成了一对多的映射关系。本质上，一对多关联关系和多对一关联关系指的是一种关联情况，区别在于看待问题的角度不同。

(2)部分与员工、班级与学生均形成了典型的一对多关联关系。

(3)要结局一对多和多对一的方案是一致的，也就是在多的一方数据表中创建字段，用来存储一的一方的主键

4、多对多关联关系(Many To Many)

(1)在A表中的一条数据在B表中存在多条数据与之对应，同时在B表中的一条数据在A表中也存在多条数据与之对应，那么此时A表和B表之间就形成了多对多关联关系。

(2)一套房屋可以属于多个人，一个人也可以拥有多套房屋，房屋和人就形成了典型的多对多映射关系。

(3)解决多对多关联关系的方案是，需要第三方数据表来实现两张表的关联，即中间表有两个字段，分别存储双方的主键。中间的表建立一个双方主键的联系。—— ..._id和..._id两个主键对应两列数据，形成对应关系，谁拥有谁，谁对应几个就写几个。

5、表间的关联关系，既可以发生在两张数据表中，也可以发生在一张数据表中(自身映射关系，行政区划信息表，部门信息表，功能菜单表等自身形成了一对多或者多对多的关系)。

二、使用DQL实现数据库表查询

1、select语法结构：

select

        [distinct] 显示字段列表

from 

        表结构[ [join]/[left join]/[right join] 表结构on连接条件]

[where

        筛选条件

group by

        分组条件

having

        分组后筛选条件        

order by

        排序字段[ASC/DESC]

limit

        开始记录下表，连续查询条数]

2、select语句执行顺序：

1. from: 准备结果所需要的数据源，说明来自哪里
2. on: 对需要链接的表根据所设置的连接条件进行拼接
3. join: 根据所设置的连接条件，对其他表进行连接
4. where: 对准备好的数据进行条件筛选(where本质上后面所携带的是一个boolean 运算条件，对数据源的每一条数据都进行boolean运算，当运算为true时，该记录会 被筛选出来，否则false该记录会被放弃) —— 在数据库中判断相等用=号
5. group by: 会根据所给定的分组条件对筛选完毕的数据进行分组(将一个完整的数据，按照分组条件变为若干段独立的小数据表) —— 分组前拆
6. having: 对分组完毕的数据进行进一步的筛选 —— 分组后拆

注：如果不需要筛选信息，不需要用到where，则说明之后的group by和having也没有了

     7.  select: 对筛选出来的数据进行设置，设置需要显示的字段 

select * 表示显示所有的字段

注：为了提高SQL的执行效率，不建议使用通配符*，因为数据库引擎在执行时，需要再次检索所有的的字段信息。

     8.  distinct: 对设置完毕的数据进行去重处理

     9.  order by: 对去重完毕的数据进行排序

     10.   limit: 对排序完毕的数据根据截取条件进行截取 —— 限定需要显示的数据。

如果需要对数据库名字进行改正，使用：

select name as 名字,telephone as 联系电话 from st_student;

①在SQL中，进行且运算除了使用” && ”之外，还可以使用关键字 “ and ”

②在SQL中，进行或运算除了使用” | | ”之外，还可以使用关键字 “ or ”

③在SQL中，有一个between ... and ... 的查询方式，选定一个区间。虽然他可以执行区间，但是在最终执行时，仍然会变为“and”进行查询，少用！！！相较于“and”效率较低！

④在SQL中，在进行或运算时，使用的时等值判断，那么可以使用in( )实现: 但是！！！in( )在执行的时候，也需要变成多个or的等值判断进行连接，因此效率较低！！！！！

⑤要想实现一个目标的部分信息查询，则需要模糊查询进行，like关键字来实现，需要借助于通配符 注！！！模糊查询只适用于信息量少的数据库表中才能用！！！

_: 表示一次匹配一个字符              ‘ 张_ ’ 表示张某

%: 表示一次匹配多个字符              ‘ 张% ’ 表示张某某某

查询名字里面有铭的人的名字            ‘ %铭% ’

⑥对数据进行排序，可以使用order by实现，其中desc表示降序，asc表示升序。

⑦如果需要对最终获得的数据进行截取显示，可以使用limit实现，limit需要两个参数，开始记录的下标，以及连续查询条数，需要特备注意的是，开始记录的下标是从0开始的！！！和数组一样，按照下标进行截取！！！limit关键字是实现对数据库分页显示的核心！！！

⑧查询每个分类的人数，则需要先按照分的类进行分组，然后统计人数，得到最终结果，分组效果使用group by，可以把一张完整的表进行切割几个表，然后在各个表中进行操作，分组后，最终在select中出现的字段，必须要在group by中也必须出现，分完组之后，需要进一步筛选，那么就需要用having实现。

三、数据库事务( Transaction )

1、什么是事务

   事务是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行，要么全部都不执行，是一个不可再分的工作单元。数据库事务存在的意义就是为了提高数据库的安全性，避免因为误操作而导致数据错误。事务类似于复制文件。

2、数据库事务的特点

(1)可以把一个完整的操作逻辑变成一个不可再分割的整体。

(2)在数据被永久改变之前，是可以预览数据的变化。

(3)能够确保数据读取的一致性

(4)数据库会为每一条记录进行加锁，称之为行级锁。当某个事物对某条记录进行update 和delete操作时，数据库就会立刻对该记录进行上锁，如果有其他相同操作，那就只 有当前操作结束后才能够进行。

3、数据库事务的开启和结束

(1)   开启事务

• 当登陆数据库时，会自动开启事务
• 当上一个事务结束后，就会立刻开启一个新的事务
• 关闭事务
• 提交事务(让数据库按照事务的结果修改数据库):
• 执行TCL操作commit，能够实现事务的提交
• 执行DDL操作时，会自动提交事务

（2）回滚事务(将当前事务的操作放弃):

执行TCL操作的rollback，能够实现事务的回滚

4、MySQL默认每一次的DML操作后，都会自动提交事务，如果需要修改，则可以使用： set autocommit = true/false;

5、事务的ACID原则: (每一个事务都应该遵循的原则)

(1)原子性( Atomicity )

事务的全部操作，要么提交数据库全部成功执行，要么回滚全部不执行，是一个不可再分的最小单位。

(2)一致性( Consistency )

事务执行完毕后，要么提交事务，永久修改数据库数据，要么回滚撤销所有操作。无论何种情况，最终事务的数据必须和数据库的数据保持一致。

(3)隔离性( Isolation )

 多个事务之间的操作相互隔离互不干扰，事务执行的中间结果对其他事物是透明的。

(4)持久性( Durability )

对于已经提交的事务，必须保证该事务对于数据库的改变是永久的，即使数据库出现故障，也不能丢失。

6、事务并发所引起的问题

(1)脏读取：

当一个事务正在访问数据库，并且对数据进行修改，但是并没有对修改进行提交。这时另外一个事务也访问了这个数据，然后使用该数据，因为这个数据还是一个没有被提交的数据。因此另外一个事务所读取到的数据就是脏数据，基于该数据的操作就是错误的。

(2)不可重复读取：

在一个事务之内，对一个数据进行反复读取时，出现数据不一致的问题。在一个事务还没有结束时，读取到一个数据后，另一个事务也有可能会访问该数据，并且进行修改提交。由于第二个事务的修改，导致当前事务在第二次读取数据时‘=，数据发生了改变，称之为不可重复读取。

(3)幻读取：

第一个事务在读取数据时，获得N条数据，之后第二个事务进行数据的插入或删除，并且提交操作。当第一个事务再次进行相同的查询时，发现读取到的数据条数多于或少于第一次读取的数据量。

7、解决事务并发问题可以通过事务隔离级别实现，常用的隔离级别有:

(1)读取未提交( Read Uncommitted )

事务中的数据修改和删除，即使没有提交，对于其他事物也可以查询到。允许你读取到没有提交的数据。

脏读取：不可阻止

不可重复读取：不可阻止

幻读取：不可阻止

(2)读取已提交( Read Committed )

事务只读取到已提交的数据，但是在数据执行期间，读取commit到的数据有可能有新的提交。(一定程度可以解决一下脏读取)

脏读取：可以阻止

不可重复读取：不可阻止

幻读取：不可阻止

(3)可重复读取( Repeatable Read )

一个事务中的一个select操作每一次获得的数据都是一致的。

脏读取：可以组织

不可重复读取：可以阻止

幻读取：不可阻止

(4)可序列化( Serializable )

强制事务排序，解决幻读取问题，次级别会在每个读取的数据上加锁，谁都得排队，会导致大量的锁竞争和超时，性能明显下降。

脏读取：可以组织

不可重复读取：可以阻止

幻读取：可以阻止

8、如果需要查看MySQL的隔离级别，可以使用如下SQL

show variables like ‘transaction_isolation’;

9、如果需要修改当前数据库的隔离级别，可以使用如下SQL

set session transaction isolation level serializable;