首页 > 数据库 >33.MySQL数据库【三】约束条件

33.MySQL数据库【三】约束条件

时间：2024-05-31 09:22:58浏览次数：15  
标签：约束条件 varchar name 33 id int MySQL fk 主键

约束条件

  • 限制表中的数据，保证数据的准确性和可靠性而存在的限制规则

【一】非空约束（not null）

限制指定字段不能为空

# 建表
create table eg(
	name varchar(32) not null,
	hobby varchar(32)
);
# name字段不能为空

【二】唯一性约束（unique）

限制字段具有唯一性，不能重复，但能为空

# 建表
create table eg(
    id int(16) unique,
	name varchar(32) not null,
	hobby varchar(32)
);
# 每个id字段不能重复，但能空着

【三】组合使用

1）not null 和 unique 单独使用（表级约束）

# 建表
create table eg(
    id int(16) unique,
	name varchar(32) not null,
	hobby varchar(32)
);
# 每个id字段不能重复，但能空着
# name字段不能为空

2）not null 和 unique 同时使用（列级约束）

# 建表
create table eg_1(
    id int(16) unique not null,
	name varchar(32) not null,
	hobby varchar(32)
);
# id字段不能重复，也不能空
# name字段不能空

# 查看表
mysql> desc eg_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | NO   |     | NULL    |       |
| hobby | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

【四】主键约束PK（primary key）

1）相关术语

  • 主键约束：添加的那个约束规则

  • 主键字段：添加了主键规则的字段

  • 主键值：主键字段对应的值

2）主键的作用

  • 添加主键primary key的字段既不能重复也不能空，其不仅有not null、unique 的作用，还会自动添加”索引—index“

3）主键根据个数分类

1.单一主键

给一个字段添加一个主键约束

  • 列级约束

    # 建表
create table pk_1(
	id int(16) primary key,
	name varchar(32)
);
# 查看表
mysql> desc pk_1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

  • 表级约束

    # 建表
create table pk_2(
	id int(16),
	name varchar(32),
    constraint pk_2_id_pk primary key(id)
);
# 查看表
mysql> desc pk_2;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

2.复合主键

给多个字段联合添加一个主键约束

# 创建
create table pk_3(
	id int(16),
	name varchar(32),
    constraint pk_3_id_name_pk primary key(id,name)
);
# 查看表
mysql> desc pk_3;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int         | NO   | PRI | NULL    |       |
| name  | varchar(32) | NO   | PRI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

4）主键根据性质分类

  • 自然主键：主键若是一个自然数，并且这个自然数与业务无任何关系，这种主键称之为自然主键

  • 业务主键：主键值若和当前表中的业务紧密相关，这种主键值被称之为业务主键；若业务发送改变，业务主键往往也会受影响

5）自动生成主键

  • auto_increment：自增主键，在MySQL中自动生成主键值
  • 默认从1开始递增
# 创建表
create table pk_4(
	id int primary key auto_increment,
	name varchar(32)
);
# 插入数据
insert into pk_4(name) values('a'),('b'),('c');
# 查看表数据
mysql> select * from pk_4;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

补：

  • 手动插入不存在的id后，新数据id将在最后的id基础 +1 ，并作为该数据的id
  • 若手动插入的id在现有id之前，表中数据会自动按顺序排列

6）重置主键起始位置

1.清空表数据

  • 将原本的表数据全部清空

    truncate 表名；

2.指定主键起始位置

  • 使用表修改语句将带有 AUTO_INCREMENT 的字段数字指定为自定义位置

  • 只能从当前现有id的最后一位之后的id位置开始，不能从之前的id位置开始

    alter table 表名 AUTO_INCREMENT=指定的位置;

    # 指定位置
mysql> alter table pk_4 AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
# 插入
mysql> insert into pk_4(name) values('a'),('b'),('c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
# 查看表数据
mysql> select * from pk_4;
+----+------+
| id | name |
+----+------+
| 10 | a    |
| 11 | b    |
| 12 | c    |
+----+------+
3 rows in set (0.00 sec)

【五】外键约束FK（foreign key）

1）相关术语

  • 外键约束：添加的外键约束的规则
  • 外键字段：添加了外键规则的字段
  • 外键值：外键字段对应的值

2）外键语法

-- 先创建表1
create table 表名1(
	字段名1 字段类型1 约束条件1 comment 注释1,
	字段名2 字段类型2 约束条件2 comment 注释2,
);

-- 再创建表2
-- 在表2中给指定外键关系
create table 表名2(
	字段名1 字段类型1 约束条件1 comment 注释1,
	字段名2 字段类型2 约束条件2 comment 注释2,
    -- foreign key 自己的外键字段  references 被关联的表名 需要关联的外键字段 字段名1
    foreign key(在表名2中显示的字段名，字段名2) references 表名1(表明1中需要建立外键关系的字段名)
);

3）按关系分类

0.级联更新和级联删除

  • 添加级联更新和级联删除时需要在外键约束后面添加

  • 级联更新：on update cascade

  • 级联删除：on delete cascade

1.一对多关系

外键字段建在“多”的一方

# 先建部门表
create table fk_1_dep(
	id int primary key auto_increment comment '编号',
	dep_name varchar(32) comment '部门名称'
);
# 再建员工表
create table fk_1_emp(
	id int primary key auto_increment comment '编号',
	name varchar(32) comment '姓名',
    age int comment '年龄',
    dep_id int comment '部门编号',
    foreign key(dep_id) references fk_1_dep(id)
);

# 创建部门
insert into fk_1_dep(dep_name) values('语文'),('数学'),('英语');
# 创建员工(无外键)
insert into fk_1_emp(name,age) values('a',20);
# 创建员工(有外键)
insert into fk_1_emp(name,age,dep_id) values('b',25,2),('c',35,1);
# 查看表数据
mysql> select * from fk_1_emp;
+----+------+------+--------+
| id | name | age  | dep_id |
+----+------+------+--------+
|  1 | a    |   20 |   NULL |
|  2 | b    |   25 |      2 |
|  3 | c    |   35 |      1 |
+----+------+------+--------+
3 rows in set (0.00 sec)

2.多对多关系

需要单独开设第三张表专门存储关系

# 书名表
create table fk_2_book(
	id int primary key auto_increment comment '编号',
	title varchar(32) comment '书名',
    price float(10,2) comment '价格'
);
# 作者表
create table fk_2_author(
	id int primary key auto_increment comment '编号',
	name varchar(32) comment '作者名',
    sex enum('man','woman') comment '性别'
);
# 外键约束表
create table fk_2(
	id int primary key auto_increment comment '编号',
    book_id int,
    author_id int,
    foreign key(book_id) references fk_2_book(id)
    on update cascade   # 级联更新
    on delete cascade,  # 级联删除
    foreign key(author_id) references fk_2_author(id)
    on update cascade   # 级联更新
    on delete cascade  # 级联删除
);

# 创建书名
insert into fk_2_book(title,price) values('语文书',30.5),('数学书',30.5),('英语书',30.5);
# 创建作者
insert into fk_2_author(name,sex) values('a','man'),('b','man'),('c','woman'),('d','woman');
# 创建关联表
insert into fk_2(book_id,author_id) values(1,2),(2,1),(2,4),(3,3);
# 查看表数据
mysql> select * from fk_2_book;
+----+-----------+-------+
| id | title     | price |
+----+-----------+-------+
|  1 | 语文书    | 30.50 |
|  2 | 数学书    | 30.50 |
|  3 | 英语书    | 30.50 |
+----+-----------+-------+
3 rows in set (0.00 sec)

mysql> select * from fk_2_author;
+----+------+-------+
| id | name | sex   |
+----+------+-------+
|  1 | a    | man   |
|  2 | b    | man   |
|  3 | c    | woman |
|  4 | d    | woman |
+----+------+-------+
4 rows in set (0.00 sec)

mysql> select * from fk_2;
+----+---------+-----------+
| id | book_id | author_id |
+----+---------+-----------+
|  1 |       1 |         2 |
|  2 |       2 |         1 |
|  3 |       2 |         4 |
|  4 |       3 |         3 |
+----+---------+-----------+
4 rows in set (0.00 sec)

3.一对一关系

推荐建在查询频率较高的一方

# 用户信息
create table fk_3_userinfo(
    id int primary key auto_increment,
    age int comment '年龄',
    sex enum('man','woman') comment '性别',
    phone bigint comment '电话',
);
# 用户
create table fk_3_user(
    id int primary key auto_increment,
    user_id int unique,
    name varchar(32) comment '名称',
    foreign key(user_id) references fk_3_userinfo(id) 
    on update cascade   # 级联更新
  	on delete cascade  # 级联删除
);

# 创建用户信息
insert into fk_3_userinfo(age,sex,phone) values(20,'man',5568),(22,'woman',2225);
# 创建用户 关联
insert into fk_3_user(user_id,name) values(2,'a'),(1,'b');
# 查看表数据
mysql>  select * from fk_3_user;
+----+---------+------+
| id | user_id | name |
+----+---------+------+
|  1 |       2 | a    |
|  2 |       1 | b    |
+----+---------+------+
2 rows in set (0.00 sec)

mysql> select * from fk_3_userinfo;
+----+------+-------+-------+-------+
| id | age  | sex   | phone | hobby |
+----+------+-------+-------+-------+
|  1 |   20 | man   |  5568 | NULL  |
|  2 |   22 | woman |  2225 | NULL  |
+----+------+-------+-------+-------+
2 rows in set (0.00 sec)

【六】查看当前表的约束条件

# 查看所有数据库
show databases;
# 切换到表结构的数据库
use information_schema;
# 查看当前的所有表
show tables;
# TABLE_CONSTRAINTS为专门用来存储字段约束的信息
desc table_constraints;
# 查看指定表的约束信息
select constraint_name from table_constraints where table_name='表名';

标签：约束条件,varchar,name,33,id,int,MySQL,fk,主键
From： https://www.cnblogs.com/Mist-/p/18223779

相关文章

  • 35.MySQL数据库【五】多表查询和子查询
    多表查询和子查询【一】概念子查询：将一条SQL语句的查询结果加括号当做另外一条SQL语句的查询结果链表查询：先将多张表拼接到一起，形成一张大表，让后基于单表查询获取数据1）创建数据#建表createtabledep( idintPRIMARYKEYAUTO_INCREMENT,namevarchar(20));......
  • MySQL Shell 调用外部脚本
    使用CLI工具时，打错命令很容易纠正。不过，在MySQlShell中工作时，命令可能会更长、更复杂。以下是运行MySQLDocumentStore的一个例子。db.scores.find("year(date)=2023").fields(['lastName','firstName','round(avg(score),2)asavg','count(score)asnum......
  • 【docker】docker 下 MySQL偶然无法访问
    1.概述我有一个环境遇到了docker下MySQL偶然无法访问，具体情况如下可以被看到可以telnet通畅，这个jar是我自己研发的，主要这台机器是别人的没有装telnet但是任务还是报错所在机器telnet通但是无法登录，同事说遇到过比如网络卡或者不稳定，需要重启一下mysql我先去......
  • MySQL 数据库中简单的创表语句以及增删改查语句
    假设我们有一个学生表students，包含以下列：id（学生编号）、name（学生姓名）、age（学生年龄），以下是创建这个表的SQL语句：CREATETABLEstudents(  idINTPRIMARYKEY,  nameVARCHAR(50),  ageINT);在这个创建语句中：•CREATETABLE：用于创建新表。•students：表......
  • MySQL数据库中的分组函数ROLLUP
    ROLLUP是GROUPBY子句的扩展。ROLLUP选项允许包含表示小计的额外行，通常称为超级聚合行，以及总计行。通过使用ROLLUP选项，可以使用单个查询生成多个分组集。MySQL中的ROLLUP是用于 产生汇总输出的修饰符，包括表示超级汇总(较高级别)汇总操作的额外行。它使我们能够使用单个查询在......
  • MySQL全文索引实现简单版搜索引擎
    1前言只有Innodb和myisam存储引擎能用全文索引（innodb支持全文索引是从mysql5.6开始的）char、varchar、text类型字段能创建全文索引（fulltextindextype）全文索引的基于关键词的，如何区分不同的关键词了，就要用到分词（stopword）英文单词用空格，逗号进行分词；中文分词不方便（一个句......
  • 【scau数据库实验一】mysql_navicat_数据库定义实验、基本命令
    实验一开始之前，如果还有不会使用navicat建议花五分钟补课哦~补课地址：【scau数据库实验先导】mysql_navicat_数据库新建、navicat的使用-CSDN博客实验目的：理解和掌握数据库DDL语言，能够熟练地使用SQL DDL语句创建、修改和删除数据库、模式和基本表。实验报告要求：实验报......
  • MySQL编译安装
    在正式安装MySQL5.7.28之前，需要做一些准备工作。比如：安装MySQL的依赖包、卸载MariaDB、编译工具cmake的安装以及boost支持包的准备。卸载MariaDBrpm-qa|grepmariadbrpm-qmariadbmariadb-5.5.52-1.el7.x86_64yum-yremovemariadb*安装所需依赖包yum-yi......
  • mysql高级篇学习（数据表的设计方法，索引优化）
    使用docker安装mysql安装docker#yum包更新到最新yumupdate#卸载旧的dockeryumremovedocker\docker-client\docker-client-latest\docker-common\docker-latest\......
  • MySQL数据库-基础
    MySQL数据库P1数据库的作用2022/6/191.简介MySQL是一个[关系型数据库管理系统]由瑞典[MySQLAB](https://baike.baidu.com/item/MySQLAB/2620844)公司开发，属于Oracle旗下产品。MySQL是最流行的关系型数据库管理系统之一，在WEB应用方面，MySQL是最好的RDBMS(Relat......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99