MySQL高级

1. 约束

   • 概念

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

   • 分类

     • 非空约束not null：该字段都不能为空
     • 唯一约束unique：所有数据的该字段都不能重复
     • 主键约束primary key：该字段唯一且非空
     • 检查约束check：保证该字段数据满足某一条件(MySQL不支持)
     • 默认约束default：保存数据时，未写入值则采用默认值
     • 外键约束foreign key：连接两个表，保证数据的一致性和完整性

   • 非空约束

     #建表时添加约束
     create table 表名(
     	字段名 数据类型 not null;
         ...
     );
     
     #建表后添加约束
     alter table 表名 modify 字段名 数据类型 not null;
     
     #删除约束
     alter table 表名 modify 字段名 数据类型;
     

   • 唯一约束

     #建表时添加约束
     create table 表名(
     	字段名 数据类型 unique [auto_increment];	-- auto_increment : 自动增长
         ...
     );
     
     create table 表名(
     	字段名 数据类型;
         ...
         [constraint] [约束名称] unique(字段名)
     );
     
     #建表后添加约束
     alter table 表名 modify 字段名 数据类型 unique;
     
     #删除约束
     alter table 表名 drop index 字段名;
     

   • 主键约束

     #建表时添加约束
     create table 表名(
     	字段名 数据类型 primary key [auto_increment];	-- auto_increment : 自动增长
         ...
     );
     
     create table 表名(
     	字段名 数据类型;
         ...
         [constraint] [约束名称] primary key(字段名)
     );
     
     #建表后添加约束
     alter table 表名 add primary key(字段名);
     
     #删除约束
     alter table 表名 drop primary key;
     

   • 默认约束

     #建表时添加约束
     create table 表名(
     	字段名 数据类型 default 默认值;
         ...
     );
     
     #建表后添加约束
     alter table 表名 alter 字段名 set default 默认值;
     
     #删除约束
     alter table 表名 alter 字段名 drop default;
     

   • 外键约束

     #建表时添加约束
     create table 表名(
     	字段名 数据类型;
         ...
         [constraint] [外键名称] foreign key(字段) references 主表(主表中该字段名)
     );
     
     #建表后添加约束
     alter table 表名 add constraint 外键名称 foreign key(外键字段名称) references 主表名称(主表中该字段名);
     
     #删除约束
     alter table 表名 drop foreign key 外键名称;
     

2. 数据库设计

   本文偏向应用，该部分不再赘述

   • 数据库设计简介
   • 表关系

3. 多表查询

   • 连接查询

     • 内连接查询

       -- 内连接查询 : 查询交集
       #隐式内连接
       select 字段列表 from 表1,表2... where 条件;
       
       #显式内连接
       select 字段列表 from 表1 [inner] join 表2 on 条件;
       

     • 外连接查询

       #左外连接查询：查询左表所有数据和交集部分数据
       select 字段列表 from 表1 left [outer] join 表2 on 条件;
       
       #右外连接查询：查询右表所有数据和交集部分数据
       select 字段列表 from 表2 right [outer] join 表2 on 条件;
       

   • 子查询

     #子查询：查询语句中嵌套的查询语句
     select 字段列表 from 表名 where 包含子查询的条件判断;
     
     #示例 : 查询student表中成绩高于张三的学生数据
     -- 分步查询
     select score from student where name = '张三';	-- 设查询结果为61
     select * from student where score > 61;
     -- 子查询
     select * from student where score > (select score from student where name = '张三');
     

4. 事务

   • 概述

     • 数据库的事务（Transaction）是一种机制、一个操作序列，包含了一组数据库操作命令。

     • 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败。

     • 事务是一个不可分割的工作逻辑单元。

       举例：

       张三和李四账户中各有100块钱，现李四需要转换500块钱给张三，具体的转账操作为：

       • 第一步：查询李四账户余额
       • 第二步：从李四账户金额 -500
       • 第三步：给张三账户金额 +500

       现在假设在转账过程中第二步完成后出现了异常第三步没有执行，就会造成李四账户金额少了500，而张三金额并没有多500；这样的系统是有问题的。使用事务可以解决上述问题

       在转账之前开启事务，若出现异常则回滚事务，正常执行则提交事务，写入数据

   • 语法

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

   • 事务的四大特征

     • 原子性（Atomicity）: 事务是不可分割的最小操作单位，要么同时成功，要么同时失败

     • 一致性（Consistency） :事务完成时，必须使所有的数据都保持一致状态

     • 隔离性（Isolation） :多个事务之间，操作的可见性

     • 持久性（Durability） :事务一旦提交或回滚，它对数据库中的数据的改变就是永久的

       说明：

       mysql中事务是自动提交的。

       也就是说我们不添加事务执行sql语句，语句执行完毕会自动的提交事务。

       可以通过下面语句查询默认提交方式：

       select @@autocommit;
       

       查询到的结果是1 则表示自动提交，结果是0表示手动提交。当然也可以通过下面语句修改提交方式

       set @@autocommit = 0;
       

.