mysql约束及存储引擎

最新推荐文章于 2024-07-23 10:17:04 发布

yiqiandewo.

最新推荐文章于 2024-07-23 10:17:04 发布

阅读量121

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/yiqiandewo/article/details/107299830

版权

mysql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

约束（Constraint）

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。

常见的约束

非空约束(not null) ：约束的字段不能为null
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为null 也不能重复
外键约束(foreign key)
检查约束(check)：注意oracle数据库有check约束但是mysql没有目前mysql不支持该约束

非空约束

create table t_user(
    id int,
    username varchar(255) not null,
    password varchar(255)
);

//username不能为空
insert into t_user(id, password) values(1, '123');
ERROR 1364 (HY000): Field 'username' doesn't have a default value

insert into t_user(id, username, password) values(1, 'lisi', '123');
select * from t_user;
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
|    1 | lisi     | 123      |
+------+----------+----------+

唯一性约束(unique)

唯一性约束修饰的字段具有唯一性不能重复但可以为null

给某一列添加unique

drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255) unique  //列级约束
);
insert into t_user values(1,'zhangsan');

//出现编译错误 唯一性约束 该字段与上一行字段重复 但可以为null
insert into t_user values(2,'zhangsan');
ERROR 1062 (23000) : Duplicate entry 'zhangsan' for key 'username'

insert into t_user(id) values(2);
insert into t_user(id) values(3);
insert into t_user(id) values(4);

给两个列或者多个列添加unique

drop table if exists t_user;
create table t_user(
    id int,
    usercode varchar(255),
    username varchar(255),
    //多个字段联合起来添加一个约束unique //表级约束
    unique(usercode,username)
);

insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');

select * from t_user;
+------+----------+----------+
| id   | usercode | username |
+------+----------+----------+
|    1 | 111      | zs       |
|    2 | 111      | ls       |
|    3 | 222      | zs       |
+------+----------+----------+

insert into t_user values(4,'111','zs');  //出现编译错误
ERROR 1062 (23000) : Duplicate entry '111-zs' for key 'usercode'

drop table if exists t_user;
create table t_suer(
    id int,
    usercode varchar(255) unique,
    username varchar(255) unique
);
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
ERROR 1062 (23000) : Duplicate entry '111' for key 'usercode'

注意：not null约束只有列级约束没有表级约束

主键约束

给一张表添加主键约束

drop table if exists t_user;
create table t_user(
    id int primary key,  //列级约束
    username varchar(255),
    email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(2,'ls','ls@123.com');
insert into t_user(id,username,email) values(3,'ww','ww@123.com');

select * from t_user;
+-----------------------------+
| id | username | email       |
+-----------------------------+
|  1 | zs       | zs@123.com  |
|  2 | ls       | ls@123.com  |
|  3 | ww       | ww@123.com  |
+----+----------+-------------+

//出现编译错误 主键约束 不能为null也不能重复
insert into t_user(id,username,email) values(1,'jack','jack@123.com');  
ERROR 1364 (HY000) : Field 'id' doesn't have a default value

id是主键因为添加了主键约束主键字段中的数据不能为nul 也不能重复
主键的特点：不能为null 也不能重复

主键相关的术语

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

主键的作用

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

主键的分类

根据主键字段的字段数量来划分

单一主键（推荐的常用的）
复合主键（多个字段联合起来添加一个主键约束）（复合主键不建议使用因为违背三范式）

根据主键性质来划分

自然主键：主键值最好就是一个和业务没有任何关系的自然数（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩例如：拿着银行卡的卡号做主键拿着身份证号做为主键（不推荐使用）最好不要拿着和业务挂钩的字段做为主键因为以后的业务一旦发生改变的时候主键也可能需要随着发生变化但有的时候没有办法变化因为变化可能会导致主键重复

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

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

drop table if exists t_user;
create table t_user(
    id int,
    username varchar(255),
    primary key(id)
);
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
insert into t_user(id,username) values(4,'cs');

select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | zs       |
|  2 | ls       |
|  3 | ws       |
|  4 | cs       |
+----+----------+

insert into t_user(id,username) values(3,'cx');  //出现编译错误
ERROR 1062 (23000) : Duplicate entry '3' for key 'PRIMARY'

mysql提供主键值自增

drop table if exists t_user;
create table t_user(
    id int primary key auto_increment,  //id字段自动维护一个自增的数字 从1开始 以1递增
    username varchar(255)
);

insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d');
insert into t_user(username) values('e');
insert into t_user(username) values('f');

select * from t_user;
+----+----------+
| id | username |
+----+----------+
|  1 | a        |
|  2 | b        |
|  3 | c        |
|  4 | d        |
|  5 | e        |
|  6 | f        |
+----+----------+

外键约束

外键约束的相关术语

外键约束：foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值

业务背景

请设计数据库表，用来维护学生和班级的信息

第一种方案：一张表存储所有数据

no(pk)	name	classno	classname
1	zs1	101	xxx省xxx市xxx中学高三1班
2	zs2	101	xxx省xxx市xxx中学高三1班
3	zs3	102	xxx省xxx市xxx中学高三2班
4	zs4	102	xxx省xxx市xxx中学高三2班
5	zs5	102	xxx省xxx市xxx中学高三2班

缺点：冗余不推荐

第二种方案：两张表(班级表和学生表)

t_class 班级表

cno(pk) cname
101 xxx省xxx市xxx中学高三1班
102 xxx省xxx市xxx中学高三2班

cno(pk)	cname
101	xxx省xxx市xxx中学高三1班
102	xxx省xxx市xxx中学高三2班

t_student 学生表

sno(pk)	sname	cno(该字段添加外键约束fk)
1	zs1	101
2	zs2	101
3	zs3	102
4	zs4	102
5	zs5	102

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

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

drop table if exists t_student;
drop table if exists t_class;

create table t_class(
    cno int,
    cname varchar(255),
    primary key(cno)
);

create table t_student(
    sno int,
    sname varchar(255),
    classno int,
    primary key(sno),
    foreign key(classno) references t_class(cno)
);

insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');

insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',102);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);

select * from t_class;
+-----+-----------------------------------+
| cno | cname                             |
+-----+-----------------------------------+
| 101 | xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx |
| 102 | yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy |
+-----+-----------------------------------+

select * from t_student;
+-----+-------+---------+
| sno | sname | classno |
+-----+-------+---------+
|   1 | zs1   |     101 |
|   2 | zs2   |     101 |
|   3 | zs3   |     102 |
|   4 | zs4   |     102 |
|   5 | zs5   |     102 |
|   6 | zs6   |     102 |
+-----+-------+---------+

insert into t_student values(7,'lisi',103);  //编译错误 引用的103 父表中没有该字段
ERROR 1452 (23000) : Cannot add or update a child row :aforeign key constraint fails (bjpowernode INT YT......)

外键可以为null
外键字段引用其他表的某个字段的时候被引用的字段不一定是主键但至少是具有unique约束具有唯一性不可重复

存储引擎

完整的建表语句

CREATE TABLE `t_x` (
    `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

注意：在MySQL当中凡是标识符使用飘号(`)括起来的最好别用不通用

建表的时候可以指定存储引擎也可以指定字符集

mysql默认使用的存储引擎是InnoDB方式

默认采用的字符集是UTF-8

什么是存储引擎

存储引擎这个名字只有在mysql中存在（Oracle中有对应的机制但不叫做存储引擎 Oracle中没有特殊的名字就是"表的存储方式"）

mysql支持很多存储引擎每个存储引擎都对应了一种不同的存储方

每一个存储引擎都有自己的优缺点需要在合适的时机选择合适的存储引擎

查看当前mysql支持的存储引擎

show engines \G
*************************** 1. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 9. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL

常见的存储引擎

MyISAM

      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO

MyISAM这种存储引擎不支持事务

MyISAM是mysql最常用的存储引擎但是这种存储引擎不是默认的

MyISAM采用三个文件组织一个表：

xxx.frm(存储格式的文件)
xxx.MYD(存储表中数据的文件)
xxx.MYI(存储表中索引的文件)

优点：可被压缩节省存储空间并且可以转换为只读表提高检索效率

缺点：不支持事务

InnoDB

      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES

优点：支持事务行级锁外键等这种存储引擎数据的安全得到保障

表的结构存储在xxx.frm文件中

数据存储在tablespace这样的表空间中(逻辑概念) 无法被压缩无法转换成只读

这种InnoDB存储引擎在MySQL数据库崩溃之后提供自动恢复机制

InoDB支持级联删除和级联更新

MEMORY

      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO

缺点：不支持事务数据容易丢失因为所有数据和索引都是存储在内存当中的

优点：查询速度最快

以前叫做HEPA引擎

yiqiandewo.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql约束及存储引擎

约束（Constraint）在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的合法性、有效性、完整性。常见的约束非空约束(not null) ：约束的字段不能为null唯一约束(unique)：约束的字段不能重复主键约束(primary key)：约束的字段既不能为null 也不能重复外键约束(foreign key)检查约束(check)：注意oracle数据库有check约束但是mysql没有目前mysql不支持该约束非空约束create ta
复制链接

扫一扫

专栏目录