SQL中约束、数据库设计、多表查询、事务

1、约束

约束的概念和分类

• 约束的概念

约束是作用于表中列上的规则，用于限制加入表的数据。约束的存在保证了数据库中数据的正确性、有效性和完整性

• 约束的分类

 

1. 1.外键约束

• 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

1. 2.添加约束

• --创建表时添加外键约束
• CREATE TABLE 表名(
• 列名 数据类型,
• ...
• CONSTRAINT 外键名称 FOREIGN KEY(外键列名) REFERENCES 主表(主表列名)
• );

  ---

• 2、数据库设计

• 数据库设计概念

1. 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型。
2. 建立数据库中的表结构以及表与表之间的关联关系的过程。
3. 有哪些表?表里有哪些字段?表和表之间有什么关系?

• 表关系

1. 1.一对一:

• 如:用户和用户详情
• 一对一关系多用于表拆分，将一个实体中经常使用的字段放一
• 张表，不经常使用的字段放另一张表，用于提升查询性能

1. 2.一对多(多对一):

• 如:部门和员工
• 一个部门对应多个员工，一个员工对应一个部门
• 实现方式：在多的一方建立外键，指向一的一方的主键。

1. 3.多对多:

• 如:商品和订单
• 一个商品对应多个订单，一个订单包含多个商品
• 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键。

• 数据库设计的步骤

1. 需求分析（数据是什么?数据具有哪些属性?数据与属性的特点是什么)
2. 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统)
3. 物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)
4. 维护设计（1.对新的需求进行建表;2表优化)

   ---

• 3、多表查询

• 笛卡尔积:取A,B集合所有组合情况。
• 若简单的进行多表查询，例如select  *  from student , teacher ;
• 则会出现多种重复情况。
• 多表查询:从多张表查询数据

• 连接查询

• 内连接:

• 内连接查询语法
• --隐式内连接
• SELECT 字段列表 FROM 表1,表2...  WHERE 条件;
• --显示内连接
• SELECT 字段列表 FROM 表1 INNER J0IN 表2 ON 条件;
• 相当于查询AB交集数据

• 外连接:

• 左外连接:相当于查询A表所有数据和交集部分数据
• 右外连接:相当于查询B表所有数据和交集部分数据
• 外连接查询语法
• --左外连接
• SELECT 字段列表 FROM 表1  LEFT OUTER J0IN 表2 ON 条件;
• --右外连接
• SELECT 字段列表 FROM 表1  RIGHT OUTER JOIN 表2 ON 条件;
• 子查询
• 子查询根据查询结果不同,作用不同:
• 单行单列:作为条件值，使用 =  !=  >  < 等进行条件判断
• SELECT 字段列表 FROM 表 WHERE 字段名 =  (子查询);
• 多行单列:作为条件值，使用in等关键字进行条件判断
• SELECT 字段列表 FROM 表 WHERE 字段名 in (子查询);
• 多行多列:作为虚拟表
• SELECT 字段列表 FROM  (子查询)  WHERE 条件;

  ---

• 4、事务

• 事务简介

1. 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令
2. 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
3. 事务是一个不可分割的工作逻辑单元

• 例如：转账的全过程就是一个事务。账户A向账户B进行转账，A扣除金额后，B也要增加金额，这样一个事务才算完成（即同时成功）。若一方不成功，则进行事务回滚。
• --开启事务
• START TRANSACTION;或者BEGIN;
• --提交事务COMMIT;
• --回滚事务ROLLBACK;

• 事务四大特征

1. 原子性(Atomicity):事务是不可分割的最小操作单位，要么同时成功，要么同时失败。
2. 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态
3. 隔离性(lsolation):多个事务之间，操作的可见性
4. 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

• 注意；

• MySQL事务默认自动提交
• 查看事务的默认提交方式
• SELECT  @@autocommit;
• 1自动提交 0手动提交
• 修改事务提交方式
• set  @@autocommit = 0;