MySQL----（四）数据库CRUD，约束，存储引擎，事务超详细解读

一.创建表

• 建表语句：
    creat table 表名(
       字段名1 数据类型,
       字段名2 数据类型,
       …
  );

• 关于MySQL当中字段的数据类型：
  常见的：int(整数型)    bigint(长整型)   float(浮点型)
       char(定长字符串)   varchar(可变长字符串)
        date(日期类型)【对应java中java.sql.Date类型】
        BLOB(二进制大对象)【存储图片，视频等流媒体信息】
        CLOB(字符大对象)【存储较大文本，比如可以存储4G字符串】

• 注：char和varchar怎么选择？
  在实际开发中，当某个字段中的数据长度不发生改变时，是定长的，例如：性别、生日等都是采用char。当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。

补：表名在数据库中一般以：t_或者tbl_开始。

• 例子：创建学生表

//学生信息包括：学号，姓名，性别，班级编号，生日
//学号：bigint
//姓名：varchar
//性别：cahr
//班级编号：int
//生日：char
create table t_student(
    no bigint,
    name varchar,
    sex char(1),
    classno varchar(255),
    birth char(10),
);
1
2
3
4
5
6
7
8
9
10
11
12
13

二.insert语句插入数据

• 语法格式：
     insert into 表名(字段名1,字段名2,…)values(值1,值2,…)
  要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth)
values(‘1’,‘lisi’,‘gaosan1ban’,‘1950-10-12’,2);

• drop table if exists t_student;//如果这张表存在就删掉

• 需要注意：当一条insert语句执行成功之后，表格中会多出一条记录。即使多的这一行记录当中某些字段是null，后期也没有再执行insert语句插入数据，只能使用update进行更新
  1.字段可以省略不写，但后面的values对数量顺序有要求
  2.一次可以插入多行数据
  insert into t_student(no,name,sex,classno,birth)
  values(3,‘lisi’,‘1’,‘gansan1ban’,‘1952-12-14’),(4,‘laotie’,‘gansan2ban’,‘1955-12-14’);

三.表的复制

• 语法格式：
       create table 表格 as select 语句；
  //将查询结果当作表创建出来
• 将查询结果插入到一张表中：
  insert into dept1 select * from dept；
  

四.修改数据 update

语法格式：
     update 表名 set 字段名 = 值1，字段名2 = 值2，…where 条件；

注意：没有条件整张表全部更新！

五.删除数据

语法格式：
     delete from 表名 where 条件；

注意：没有条件全部删除

***对表结构的修改，使用工具完成即可，在实际开发中表一旦在设计之后，对表结构的修改很少的，修改表结构就是对之前的设计进行了否认，即使需要修改表结构，也可以直接用工具，修改表结构的语句不会出现在java代码中，出现在java代码中sql包括：insert ，delete，update，select。***

***增删改查有一个术语：CRUD操作：
Create（增） Retrieve Read（检索）Update（改）Delete（删）***

六.约束（Constraint）

1.什么是约束？常见的约束有哪些？

• 表格

    id        username（唯一性约束）      password（非空约束）

---

   

• 作用：在创建表的时候，可以给表的字段添加相关的约束，添加约束的目的是为了保证表中数据的合法性，有效性，以及完整性，

• 常见的约束:
     非空约束（not null）：约束的字段不能为null
     唯一约束（unique）：约束的字段不能重复
     主键约束（primary key）：约束的字段既不能为null，也不能重复（简称PK）
     外键约束（foreign kry）： 用来在两个表的数据之间建立链接，它可以是一列或者多列。一个表可以有一个或多个外键。
     检查约束（check）：注意，Oracle数据库中有检查约束，mysql没有

2.非空约束 （not null）

如图：

3.唯一性约束

• 唯一约束修饰的字段具有唯一性，不能重复，但可以为null。
• 案例：
  //列级约束
  
  //表级约束：多个字段联合添加约束
  

4.主键约束

• 怎么给一张表添加主键约束呢？
  根据以上测试可以得出：id是主键，因为添加了主键约束，不能为空也不能重复。（列级约束）
  

• 主键相关术语：
  主键约束：primary key
  主键字段：id字段添加primary key 之后，id叫做主键字段
  主键值：id字段中的每一个值都是主键值

• 主键有什么作用？

  • 表的设计三范式中有要求，第一范式就要求任何一张表都应该有主键
  • 主键的作用：主键值是这行记录在这张表中的唯一标识（就像一个人的身份证号一样）

• 主键的分类：

  • 根据主键字段的数量来划分：
    单一主键（推荐的，常用的）
    复合主键（多个字段联合起来添加一个主键约束）（不建议用，违背三范式）
  • 根据主键性质划分：
    自然主键：主键最好和一个与业务没有任何联系的自然数（推荐）
    业务主键：主键值和系统的业务相挂钩。例如：拿着银行卡号，身份证号码作为主键（不建议用)
    最好不要拿着和业务相挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

• 一张表的主键约束只能有一个

• 使用表级约束的方式定义主键
  

• mysql提供主键值自增(非常重要！)
  
  提示：Oracle中也提供了一个自增机制，叫做：序列（sequence）对象。

5.外键约束 foreign key（）reference 表名（）

• 关于外键约束的相关术语：
  外键约束：foreign key
  外键字段：添加有外键约束的字段
  外键值：外键字段中的每一个值
  
  

• t_student中的classno字段引用t_class中的cno字段。此时t_student表叫做子表，t_class表叫做父表。

• 删除数据的时候，先删除子表，再删除父表。
  添加数据的时候，先添加父表，再添加子表
  创建表的时候，先创建父表，再创建子表
  删除表的时候，先删除子表，再删除父表

• 外键可以为null吗？
  可以

• 外键字段引用其他表的某个字段的时候，不一定是主键，但至少具有unique约束。

• 

七.存储引擎

1.完整的建表语句

注意：在MySQL中，凡是标识符是可以使用飘号括起来，最好别用，不通用。
   建表的时候可以指定存储引擎，也可以指定字符集
   mysql默认使用的存储引擎是InnoDB方式
   默认采用的字符集是UTF8

2.什么是存储引擎？

存储引擎这个名字只有在mysql中存在（Oracle中有对应的机制，但是不叫存储引擎，没有特殊的名字，就叫做“表的存储方式”）
mysql支持很多存储引擎，每一个存储引擎都对应了一种不同的存储方式，每一种存储引擎都有自己的优缺点，需要在合适的实际选择合适的存储引擎。

3.如何查看当前mysql的存储引擎？

show engines \G

mysql5.5.36版本支持的存储引擎有九个

4.常见的存储引擎

（1）MyISAM：一个表三个文件存储
   .frm 表结构
   .MYD 表数据
   .MYI 表索引
MyISAM是mysql中最常用的存储引擎，但是这种引擎不是默认的
优点：可以被压缩，节约存储空间，并且可以转换为只读表，提高检索效率
缺点：不支持事务

（2）InnoDB:是mysql缺省搜索引擎
优点：支持事务，行级锁，外键等。这种引擎数据的安全得到保障

表结构存储在xxx.frm格式文件中。表数据在tablespace这样的表空间中（逻辑概念），无法被压缩，无法被转换成只读。这种InnoDB存储引擎在MySQL数据库崩溃后提供自动恢复机制。InnoDB支持级联删除和级联更新。

（3）MEMORY
缺点：不支持事务，数据更容易丢失，因为所有数据和索引都是存储在内存当中的
优点：查询速度很快

以前叫做HEPA引擎。

八.事务

1.什么是事务？

一个事务是一个完整的业务逻辑单元，不可再分
比如：银行账户转账，从A账户向B账户转账10000，需要执行两条unique语句

• 以上两条DML语句必须同时成功，或者同时失败，不允许出现一条成功，一条失败。
• 要想保证以上两条DML语句同时成功或者同时失败，那么就需要使用数据库的“事务机制”。

2.和事务有关的语句：只有DML语句（insert delete update）为什么？

因为这三条语句都是和数据库表当中的“数据”相关的，事务的存在是为了保证数据的完整性、安全性。

3.事务原理

4.事务特性

事务包括三大特性：ACID
A:原子性:事务是最小的工作单元，不可再分
C:一致性:事务必须保证多条DML语句同时成功或者同时失败
I:隔离性：事务A与事务B之间具有隔离
D:持久性：说的是最终数据必须持久化到硬盘中，事务才算成功的结束

5.关于事务间的隔离性

事务隔离性存在隔离级别，理论上隔离级别包括四个：
   第一级别:读未提交（read uncommitted）
       对方事务还没提交，我们当前的事务可以读取到对方未提交的数据。读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。
   第二级别:读已提交（read committed）
       对方事务提交之后的数据我可以读到。读已提交存在的问题是：不可重复读，这种隔离级别解决了脏读现象。
   第三级别:可重复读（repeatble read）
       这种隔离级别解决了不可重复读的问题，这种存在的问题是，读取到的数据是幻像，称为幻读
   第四级别:序列化读/串行化读
       解决了所有问题，效率低，需要事务排队