MySQL——表的约束（下）

张

张建站

2026/5/20 7:33:50

10分钟阅读

目录一、自增长二、唯一键三、外键四、综合案例一、自增长自增长auto_increment没设置起始值时默认从1开始负责从设置的起始值开始。自增长有以下特点1.自增长必须搭配主键或唯一键使用2.一张表最多只能有一个自增长-- 自增长 SHOW DATABASES; USE test_key; SHOW TABLES; CREATE TABLE IF NOT EXISTS tt21( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL ); SHOW CREATE TABLE tt21; DESC tt21; INSERT INTO tt21 (name) VALUES (a); INSERT INTO tt21 (name) VALUES (b); INSERT INTO tt21 (name) VALUES (c); SELECT * FROM tt21; INSERT INTO tt21 (name) VALUES (d); SELECT * FROM tt21; INSERT INTO tt21 (id,name) VALUES (1000,e); SELECT * FROM tt21; INSERT INTO tt21 (name) VALUES (f); SELECT * FROM tt21; SHOW CREATE TABLE tt21; CREATE TABLE IF NOT EXISTS tt22( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL )AUTO_INCREMENT500; SHOW CREATE TABLE tt22; INSERT INTO tt22 (name) VALUES (a); INSERT INTO tt22 (name) VALUES (b); INSERT INTO tt22 (name) VALUES (c); SELECT * FROM tt22; SELECT id FROM tt22; SELECT id FROM tt21; CREATE TABLE IF NOT EXISTS tt23( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, id1 INT UNSIGNED UNIQUE KEY NOT NULL AUTO_INCREMENT )AUTO_INCREMENT500; SELECT * FROM tt22; SELECT LAST_INSERT_ID(); SHOW CREATE TABLE tt22;二、唯一键主键与唯一键的区别1.主键设置好不能重复不能为空2.唯一键表达了类似于主键的功能但可以为空NULL不参与运算3.主键标定某一行记录在整表中的唯一性记录的唯一性4.唯一键主要是维护某一列的唯一性5.在选择好主键后用唯一键来维护其他列的唯一性6.唯一键与主键配合起来使表的约束力更强-- 唯一键 CREATE TABLE IF NOT EXISTS stu( id CHAR(20) UNIQUE COMMENT 这个是一个学生的唯一键, name VARCHAR(32) NOT NULL ); DESC stu; INSERT INTO stu (id,name) VALUES (12345,张三); SELECT * FROM stu; INSERT INTO stu (id,name) VALUES (12345,李四); INSERT INTO stu (id,name) VALUES (NULL,李四); SELECT * FROM stu; INSERT INTO stu (id,name) VALUES (NULL,李四); INSERT INTO stu (id,name) VALUES (NULL,李四); INSERT INTO stu (id,name) VALUES (NULL,李四); SELECT * FROM stu; SELECT NULL1; -- 唯一键与主键搭配使用 CREATE TABLE IF NOT EXISTS student( id CHAR(20) PRIMARY KEY, name VARCHAR(32) NOT NULL, telephone CHAR(20) ); DESC student; INSERT INTO student (id,name,telephone) VALUES (123,张飞,12345678910); SELECT * FROM student; INSERT INTO student (id,name,telephone) VALUES (123,张飞,12345678910); INSERT INTO student (id,name,telephone) VALUES (124,刘备,12345678911); INSERT INTO student (id,name,telephone) VALUES (125,孙权,12345678911); SELECT * FROM student; SELECT * FROM student WHERE telephone 12345678911; DROP TABLE student; SHOW TABLES; CREATE TABLE IF NOT EXISTS student( id CHAR(20) PRIMARY KEY, name VARCHAR(32) NOT NULL, telephone CHAR(20) UNIQUE KEY, qq VARCHAR(64) UNIQUE KEY ); DESC student; INSERT INTO student VALUES (123,张三,1341234,23456); SELECT * FROM student; INSERT INTO student VALUES (125,李四,1341235,23456); INSERT INTO student VALUES (125,李四,1341235,23457); SELECT * FROM student; INSERT INTO student VALUES (126,王五,1341236,NULL); SELECT * FROM student; INSERT INTO student VALUES (NULL,赵六,1341237,NULL); SELECT * FROM student; DELETE FROM student WHERE qq is NULL; ALTER TABLE student MODIFY telephone CHAR(20) UNIQUE KEY NOT NULL; ALTER TABLE student MODIFY qq VARCHAR(64) UNIQUE KEY NOT NULL; DESC student;三、外键一个合格的外键要确保以下内容1.从表与主表之间的关联关系2.从表与主表之间的约束关系产生外键约束在学生表与班级表的关联当中不能出现插入的学生属于不存在的班级或删除某一班机时班级中还有学生。-- 外键 DROP TABLE student; CREATE TABLE IF NOT EXISTS student( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(20) NOT NULL, telephone VARCHAR(32) NOT NULL, class_id INT ); DESC student; CREATE TABLE IF NOT EXISTS class( id INT PRIMARY KEY, name VARCHAR(32) NOT NULL ); DESC class; INSERT INTO class VALUES (1,通信101); INSERT INTO class VALUES (2,通信102); SELECT * FROM class; INSERT INTO student (name,telephone,class_id) VALUES (张三,12345678910,1); SELECT * FROM student; SELECT * FROM class; SELECT * FROM class WHERE id 1; INSERT INTO student (name,telephone,class_id) VALUES (李四,12345678911,1); INSERT INTO student (name,telephone,class_id) VALUES (王五,12345678912,1); INSERT INTO student (name,telephone,class_id) VALUES (赵六,12345678912,2); SELECT * FROM student; INSERT INTO student (name,telephone,class_id) VALUES (田七,12345678912,3); SELECT * FROM student; SELECT * FROM class; DESC class; DESC student; DROP TABLE student; CREATE TABLE IF NOT EXISTS student( id INT UNSIGNED PRIMARY KEY, name VARCHAR(20) NOT NULL, telephone VARCHAR(32) UNIQUE KEY, class_id INT, FOREIGN KEY(class_id) REFERENCES class(id) ); DESC student; SELECT * FROM class; INSERT INTO student VALUES (100,张三,12345,1); INSERT INTO student VALUES (101,李四,12346,2); SELECT * FROM student; INSERT INTO student VALUES (102,王五,12347,3); DELETE FROM class WHERE id 1; DELETE FROM student WHERE id 100; SELECT * FROM student; DELETE FROM class WHERE id 1; SELECT * FROM class;四、综合案例有一个商店的数据记录客户及购物情况有以下三个表组成1.商品goods(商品编号goods_id商品名goods_name,单价unitprice,商品类别category,供应商provider)2.客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex身份证card_id)3.购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)要求1.每个表的主外键2.客户的姓名不能为空值3.邮箱不能重复4.客户的性别(男女)SQL-- 创建数据库 create database if not exists bit32mall default character set utf8 ; -- 选择数据库 use bit32mall; -- 创建数据库表 -- 商品 create table if not exists goods ( goods_id int primary key auto_increment comment 商品编号, goods_name varchar(32) not null comment 商品名称, unitprice int not null default 0 comment 单价单位分, category varchar(12) comment 商品分类, provider varchar(64) not null comment 供应商名称 ); -- 客户 create table if not exists customer ( customer_id int primary key auto_increment comment 客户编号, name varchar(32) not null comment 客户姓名, address varchar(256) comment 客户地址, email varchar(64) unique key comment 电子邮箱, sex enum(男,女) not null comment 性别, card_id char(18) unique key comment 身份证 ); -- 购买 create table if not exists purchase ( order_id int primary key auto_increment comment 订单号, customer_id int comment 客户编号, goods_id int comment 商品编号, nums int default 0 comment 购买数量, foreign key (customer_id) references customer(customer_id), foreign key (goods_id) references goods(goods_id) );