MySql数据库·字段高级约束

2023-02-04 本文已影响0人技术老男孩

一、高级约束分为：

主键 (primary key)

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

自增长（auto_increment）

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

复合主键

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

外键

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

二、约束关键词：

关键词	名称	说明
PRIMARY KEY	设置字段为主键	用于保证表头的值具有唯一性并且非空。 `Key`字段标志为：标志为PRI
AUTO_INCREMENT	设置字段自增长	通常与主键字段连用 `Extra`字段标志为：auto_increment
FOREIGN 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)

# 添加自增长表头id
mysql> 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     pay
mysql> 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: gz
Create 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: gz
Create 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=latin1
1 row in set (0.00 sec)

MySql数据库·字段高级约束

一、高级约束分为：

主键 (primary key)

自增长（auto_increment）

复合主键

外键

二、约束关键词：

三、语法格式：

创建主键语法格式：

删除主键语法格式：

添加主键语法格式：

创建外键语法格式：

删除外键语法格式：

四、使用示例：

猜你喜欢

热点阅读