MySql数据库·字段高级约束

手机游戏开发者 · 2024-9-11 19:04:20

一、高级约束分为：

主键 (primary key)

表头值不允许重复，且不允许赋NULL值
表中只能有一个主键表头
通常把表中唯一标识记录的表头设置为主键[记录编号表头]

自增长（auto_increment）

插入记录时，如果不给表头赋值表头通过自加1的计算结果赋值
要想让表头有自增长表头必须有主键设置才可以
自增长总是根据最后1条件记录表头的值+1结果赋值，除非通过truncate table这种方式清理表记录，才会从头开始计算。

复合主键

表中的多个表头一起做主键，称为复合主键
多条记录主键的值不允许同时相同
复合主键必须同时一起创建，并同时删除

外键

外键字段的值必须在参考表字段值范围内
表存储（参照字段的表以及当前表）引擎必须要设置ENGINE=INNODB
设置外键的字段类型必须要与原表的参考字段要保持一致
被参照字段必须要是索引类型的一种(通常是 primary key)
未设置同步更新on update cascade，原表中被参考的字段不能被修改
未设置同步删除on delete cascade，原表中被参考的字段不能被删除

二、约束关键词：

关键词名称说明PRIMARY KEY设置字段为主键用于保证表头的值具有唯一性并且非空。
Key字段标志为：标志为PRIAUTO_INCREMENT设置字段自增长通常与主键字段连用
Extra字段标志为：auto_incrementFOREIGN KEY设置字段为外键保证数据的一致性，外键表头的值必须在参考表中表头值里选择
Key字段标志为：标志为MUL三、语法格式：

创建主键语法格式：

格式1：
CREATE TABLE 库名.表名(
字段名1 数据类型 PRIMARY KEY auto_increment
字段名2 数据类型约束条件1，约束条件2，约束条件3..
...
)；

格式2：
CREATE TABLE 库名.表名(
字段名1 数据类型约束条件1，约束条件2，约束条件3..
...,
PRIMARY KEY(字段名1，字段2...)
)；

删除主键语法格式：

ALTER TABLE 库名.表名 DROP PRIMARY KEY;
添加主键语法格式：

ALTER TABLE 库名.表名 ADD 字段名数据类型 PRIMARY KEY auto_increment 位置命令;
创建外键语法格式：

CREATE TABLE 库名.表名(
字段名1 数据类型约束条件1，约束条件2，约束条件3..
字段名2 数据类型约束条件1，约束条件2，约束条件3..
...
FOREIGN KEY(字段名1)  # 指定外键
REFERENCES 库.表(表头名)  #指定参考的表头名
ON UPDATE CASCADE  #同步更新
ON DELETE CASCADE  #同步删除
) engine=innodb；
删除外键语法格式：

ALTER TABLE 库名.表名 DROP FOREIGN KEY 外键名(非字段名);

四、使用示例：

复合主键应用：

# 创建复合主键mysql> drop table db1.t39;create table db1.t39( cip varchar(15), port smallint, status enum("deny","allow"), primary key(cip,port) # 同时设定多个主键);# 插入记录验证mysql> insert into db1.t39 values ("1.1.1.1",22,"deny");# 插入二个主键的值同时相同会产生报错mysql> insert into db1.t39 values ("1.1.1.1",22,"deny"); mysql> insert into db1.t39 values ("1.1.1.1",80,"deny"); # 删除复合主键mysql> alter table db1.t39 drop primary key;# 查看表头mysql> desc db1.t39;+--------+----------------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+----------------------+------+-----+---------+-------+| cip | varchar(15) | NO | | NULL | || port | smallint(6) | NO | | NULL | || status | enum('deny','allow') | YES | | NULL | |+--------+----------------------+------+-----+---------+-------+3 rows in set (0.00 sec)

主键与auto_increment连用

mysql> create table db1.t39 ( 行号 int auto_increment, 姓名 char(10), 班级 char(7), 住址 char(10), primary key(行号));# 查看表头mysql> desc db1.t39;+--------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+----------------+| 行号 | int(11) | NO | PRI | NULL | auto_increment || 姓名 | char(10) | YES | | NULL | || 班级 | char(7) | YES | | NULL | || 住址 | char(10) | YES | | NULL | |+--------+----------+------+-----+---------+----------------+4 rows in set (0.01 sec)

清除表记录，从头开始计算

# truncate删除行,再添加行从1开始mysql> truncate table db1.t39; Query OK, 0 rows affected (2.66 sec)# 插入记录mysql> insert into db1.t39( 姓名,班级,住址)values( "lucy","nsd2107","bj");Query OK, 1 row affected (0.04 sec)mysql> insert into db1.t39( 姓名,班级,住址)values( "lucy","nsd2107","bj");Query OK, 1 row affected (0.30 sec)# 查看记录mysql> select * from db1.t39;+--------+--------+---------+--------+| 行号 | 姓名 | 班级 | 住址 |+--------+--------+---------+--------+| 1 | lucy | nsd2107 | bj || 2 | lucy | nsd2107 | bj |+--------+--------+---------+--------+2 rows in set (0.01 sec)

给已有表添加行号字段（通常把表中唯一标识记录的字段作为主键就是行号字段）

# 没加行号前查看mysql> select * from db1.t3; +--------+---------+--------+| 姓名 | 班级 | 住址 |+--------+---------+--------+| qwer | nsd2107 | bj || rtyu | nsd2107 | bj |+--------+---------+--------+2 rows in set (0.01 sec)# 添加自增长表头idmysql> alter table db1.t3 add id int primary key auto_increment first; 添加后查看mysql> select * from db1.t3; +--------+--------+---------+--------+| id | 姓名 | 班级 | 住址 |+--------+--------+---------+--------+| 1 | qwer | nsd2107 | bj || 2 | rtyu | nsd2107 | bj |+--------+--------+---------+--------+2 rows in set (0.01 sec)

创建带有外键的数据表

# 首先创建存储员工信息的员工表# 员工表 yg# 员工编号姓名# yg_id name mysql> create table db1.yg( yg_id int primary key auto_increment, name char(16) ) engine=innodb;# 创建工资表并指定外键表头# 工资表 gz# 员工编号工资# gz_id paymysql> create table db1.gz( gz_id int, pay float, foreign key(gz_id) references db1.yg(yg_id) on update cascade on delete cascade)engine=innodb;# 查看工资表外键mysql> show create table db1.gz \G*************************** 1. row *************************** Table: gzCreate Table: CREATE TABLE `gz` ( `gz_id` int(11) DEFAULT NULL, `pay` float DEFAULT NULL, KEY `gz_id` (`gz_id`), CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) # `gz_ibfk_1`是外键名 REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin1

删除外键

# 删除工资表的外键mysql> alter table db1.gz drop FOREIGN KEY gz_ibfk_1;

给已存在的数据表中的字段添加外键

mysql> alter table db1.gz add foreign key(gz_id) references db1.yg(yg_id) on update cascade on delete cascade;# 查看外键mysql> show create table db1.gz \G*************************** 1. row *************************** Table: gzCreate Table: CREATE TABLE `gz` ( `gz_id` int(11) DEFAULT NULL, `pay` float DEFAULT NULL, KEY `gz_id` (`gz_id`), CONSTRAINT `gz_ibfk_1` FOREIGN KEY (`gz_id`) REFERENCES `yg` (`yg_id`) ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB DEFAULT CHARSET=latin11 row in set (0.00 sec)

MySql数据库·字段高级约束

所属分类: 问答交流

新帖推荐: 30日

推荐作品