MySQL学习（二）

一.约束的概念及分类

1.1约束的概念

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

1.2约束的分类

#实例演示：主键约束 非空约束 默认约束 唯一约束 外键约束
create table emp(
    id int primary key auto_increment, -- 设置为主键 主键非空且唯一 主键自增长
    name varchar(50) not null , --不能设置为null
    joindate date not null ,
    salary double(7,2) not null ,
    bonus double(7,2) default 0 -- 默认设置为0
   -- constraint fk_emp_id foreign key (dep_id) references dep1 (id), 外键约束的两种方式
    foreign key emp1(dep_id) references dep1(id)
);
#自增长 auto_increment: 当列为数字类型 唯一约束
#外键约束需要先创建主表在创建从表 先添加主表外键约束元素再添加从表元素

-- 删除外键
alter table emp1 drop foreign key fk_emp_id;

-- 添加外键
alter table emp1 add constraint fK_emp_id foreign key emp1(dep_id) references dep1(id);

二.数据库设计

2.1多对一关系

• 实现方式：在多的一方建立外键，指向一的一方的1主键

2.2多对多关系

• 实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

  drop table if exists tb_order_goods;
  drop table if exists tb_order;
  drop table if exists tb_goods;
  
  -- 订单表
  create table tb_order(
      id int primary key ,
      payment double(5,2),
      payment_type tinyint,
      status tinyint
  );
  -- 商品表
  create table tb_goods(
      id int primary key ,
      title varchar(50),
      price double(6,2)
  );
  
  -- 订单商品中间表
  create table tb_order_goods(
      id int primary key auto_increment,
      order_id int,
      good_id int,
      count int
  );
  
  -- 建完表后添加外键
  alter table tb_order_goods add constraint fk1 foreign key tb_order_goods(order_id) references tb_order(id);
  alter table tb_order_goods add constraint fk2 foreign key tb_order_goods(good_id) references tb_goods(id);
  

2.3一对一关系

• 一对一关系多用于表的拆分，将一个实体经常使用字段放在一张表，不经常使用的字段放另一张表，用于提升查询性能
• 实现方式：在任意一方加入外键，关联另一方主键，并且设置外键为唯一 unique

三.多表查询

3.1内连接语法

-- 隐式内连接
select 字段列表 from 表1,表2,…… where 条件;

-- 显示内连接
select 字段列表 from 表1(inner) jion 表2 on 条件;

-- 内连接相当于查询 A B 交集数据

#实例
select * from tb_order, tb_goods where tb_order.id = tb_goods.id;
select t1.id, t2.title, t2.price from tb_order t1, tb_goods  t2 where t1.id = t2.id;  -- 表名太长可以起别名

3.2外连接语法

-- 左外连接 相当于取A表所有数据包括AB交集数据
select * from A left outer join B on 条件;

-- 右外连接 相当于取B表所有数据包括AB交集数据
select * from A right outer join A on 条件;

3.3子连接语法

• 概念：查询中的嵌套查询
• 作用不同：单行单列 多行单列 多行多列

• #单行单列：作为条件值。使用 = != > < 等进行条件查询
  #以某一列的查询结果作为条件来查询一个表的数据，结果显示单行
  select 字段列表 from 表 where 字段名 = (子查询)
  
  #多行单列：作为条件值，使用in等关键字进行条件判断
  #以某一列的查询结果作为条件来查询一个表的数据，结果显示多行
  select 字段列表 from 表 where 字段名 in (子查询)
  
  #多行多列：多为虚拟表
  #先从一张表中根据条件筛选出一张虚拟表，在从这张虚拟表中继续根据条件来进行筛选
  select 字段列表 from (子查询) where 条件;
  

四.事务

4.1事务简介

• 数据库的事务时一种机制，一个操作序列，包含一组数据库操作命令
• 事务把所有命令作为一个整体一起向系统提交或者撤销操作请求，即这一组数据库命令要么同时成功要么同时失败
• 事务时一个不可分割的工作单元

• -- 开启事务
  start transaction;
  #或者
  begin;
  
  -- 提交事务
  commit;
  
  -- 回滚事务
  rollback;
  

4.2事务的四大特征

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

4.3注意

-- MySQL事务默认自动提交
select @@autocommit;
-- 1 自动提交	0 手动提交
-- 修改事务提交方式
set @@autocommit = 0;