MySQL表的约束(下)

目录1.主键2.自增长3.唯一键4.外键5.综合案例--阅读继MySQL表的约束(上)的学习我们继续进行下的学习。1.主键主键primary key用来唯一的约束该字段里面的数据不能重复不能为空一张表中最多只能有一个主键主键所在的列通常是整数类型。mysql create table if not exists test_key( - id int unsigned primary key comment 这个是学生的学号, - name varchar(20) not null - ); Query OK, 0 rows affected (0.00 sec) mysql desc test_key; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | ---------------------------------------------------- 2 rows in set (0.00 sec) mysql show create table test_key\G *************************** 1. row *************************** Table: test_key Create Table: CREATE TABLE test_key ( id int(10) unsigned NOT NULL COMMENT 这个是学生的学号, name varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.00 sec)mysql insert into test_key values (1,张飞); Query OK, 1 row affected (0.00 sec) mysql select * from test_key; ------------ | id | name | ------------ | 1 | 张飞 | ------------ 1 row in set (0.00 sec) mysql insert into test_key values (1,刘备); ERROR 1062 (23000): Duplicate entry 1 for key PRIMARY可以看到有主键约束。这样就可以了mysql insert into test_key values (2,刘备); Query OK, 1 row affected (0.01 sec) mysql select * from test_key where id2; ------------ | id | name | ------------ | 2 | 刘备 | ------------ 1 row in set (0.00 sec)mysql update test_key set name曹老板 where id2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql select * from test_key; --------------- | id | name | --------------- | 1 | 张飞 | | 2 | 曹老板 | --------------- 2 rows in set (0.00 sec)去掉主键mysql desc test_key; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | ---------------------------------------------------- 2 rows in set (0.00 sec) mysql alter table test_key drop primary key; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql desc test_key; ---------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------- | id | int(10) unsigned | NO | | NULL | | | name | varchar(20) | NO | | NULL | | ---------------------------------------------------- 2 rows in set (0.00 sec)加上主键约束mysql insert into test_key values (2,孙权); Query OK, 1 row affected (0.00 sec) mysql alter table test_key add primary key(id); ERROR 1062 (23000): Duplicate entry 2 for key PRIMARY mysql select * from test_key; --------------- | id | name | --------------- | 1 | 张飞 | | 2 | 曹老板 | | 2 | 孙权 | --------------- 3 rows in set (0.00 sec) mysql delete from test_key where name孙权; Query OK, 1 row affected (0.00 sec) mysql alter table test_key add primary key(id); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0一张表中最多只能有一个主键但不意味着一个表中的主键只能添加给一列一个主键可以被添加到一列或者多列上复合主键mysql create table pick_course( - id int unsigned, - course_id int unsigned comment 课程编号, - score tinyint unsigned comment 这个学生课程考的分数, - primary key (id,course_id) - ); Query OK, 0 rows affected (0.00 sec) mysql desc pick_course; ----------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | course_id | int(10) unsigned | NO | PRI | NULL | | | score | tinyint(3) unsigned | YES | | NULL | | ----------------------------------------------------------- 3 rows in set (0.00 sec)mysql insert into pick_course values(1234,40,90); Query OK, 1 row affected (0.00 sec) mysql select * from pick_course; ------------------------ | id | course_id | score | ------------------------ | 1234 | 40 | 90 | ------------------------ 1 row in set (0.00 sec) mysql insert into pick_course values(1235,40,85); Query OK, 1 row affected (0.00 sec) mysql insert into pick_course values(1234,41,90); Query OK, 1 row affected (0.00 sec) mysql insert into pick_course values(1234,40,90); ERROR 1062 (23000): Duplicate entry 1234-40 for key PRIMARY mysql select * from pick_course; ------------------------ | id | course_id | score | ------------------------ | 1234 | 40 | 90 | | 1234 | 41 | 90 | | 1235 | 40 | 85 | ------------------------ 3 rows in set (0.00 sec)只有这两个主键合起来才称作为一个主键如果是单一个主键不同那么可以加入只有两个都相同才会不能加入。2.自增长auto_increment当对应的字段不给值会自动的被系统触发系统会从当前字段中已经有的最大值 1操作得到一个新的不同的值。通常和主键搭配使用作为逻辑主键。自增长的特点:任何一个字段要做自增长前提是本身是一个索引key一栏有值自增长字段必须是整数一张表最多只能有一个自增长mysql create table if not exists tt21 ( - id int unsigned primary key auto_increment, - name varchar(20) not null - ); Query OK, 0 rows affected (0.00 sec) mysql show create table tt21\G *************************** 1. row *************************** Table: tt21 Create Table: CREATE TABLE tt21 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB DEFAULT CHARSETutf8 1 row in set (0.00 sec) mysql desc tt21; ------------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ------------------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | ------------------------------------------------------------- 2 rows in set (0.00 sec)mysql insert into tt21 (name) values (a); Query OK, 1 row affected (0.00 sec) mysql insert into tt21 (name) values (b); Query OK, 1 row affected (0.00 sec) mysql insert into tt21 (name) values (c); Query OK, 1 row affected (0.00 sec) mysql insert into tt21 (name) values (d); Query OK, 1 row affected (0.00 sec) mysql select * from tt21; ---------- | id | name | ---------- | 1 | a | | 2 | b | | 3 | c | | 4 | d | ---------- 4 rows in set (0.00 sec) mysql insert into tt21 (id,name) values (1000,e); Query OK, 1 row affected (0.00 sec) mysql insert into tt21 (id,name) values (1000,e); ERROR 1062 (23000): Duplicate entry 1000 for key PRIMARY mysql insert into tt21 (name) values (e); Query OK, 1 row affected (0.00 sec) mysql select * from tt21; ------------ | id | name | ------------ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 1000 | e | | 1001 | e | ------------ 6 rows in set (0.00 sec)可以看到这个自增主键默认是从1开始。如果给了一个值比前面值都大之后就会从这个新的值开始增长为什么呢mysql show create table tt21 \G *************************** 1. row *************************** Table: tt21 Create Table: CREATE TABLE tt21 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT1002 DEFAULT CHARSETutf8 1 row in set (0.00 sec)我们可以看到这个除了在表内建立这种约束在表外这种AUTO_INCREMENT1002值是可以被设置的这个数代表我们下次插入的起始数字mysql create table tt22( - id int unsigned primary key auto_increment, - name varchar(20) not null - )auto_increment500; Query OK, 0 rows affected (0.00 sec) mysql show create table tt22; ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Table | Create Table | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | tt22 | CREATE TABLE tt22 ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(20) NOT NULL, PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT500 DEFAULT CHARSETutf8 | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 1 row in set (0.00 sec) mysql insert into tt22 (name) values (a); Query OK, 1 row affected (0.00 sec) mysql insert into tt22 (name) values (b); Query OK, 1 row affected (0.00 sec) mysql insert into tt22 (name) values (c); Query OK, 1 row affected (0.00 sec) mysql select * from tt22; ----------- | id | name | ----------- | 500 | a | | 501 | b | | 502 | c | ----------- 3 rows in set (0.00 sec)mysql create table tt23( id int unsigned auto_increment, name varchar(20) not null )auto_increment500; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a keymysql select last_insert_id(); ------------------ | last_insert_id() | ------------------ | 502 | ------------------ 1 row in set (0.00 sec) mysql insert into tt22 (name)values(d); Query OK, 1 row affected (0.00 sec) mysql select * from tt22; ----------- | id | name | ----------- | 500 | a | | 501 | b | | 502 | c | | 503 | d | ----------- 4 rows in set (0.00 sec) mysql select last_insert_id(); ------------------ | last_insert_id() | ------------------ | 503 | ------------------ 1 row in set (0.00 sec)索引在关系数据库中索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录可以根据目录中的页码快速找到所需的内容。索引提供指向存储在表的指定列中的数据值的指针然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快可快速访问数据库表中的特定信息。3.唯一键一张表中有往往有很多字段需要唯一性数据不能重复但是一张表中只能有一个主键唯一键就可以解决表中有多个字段需要唯一性约束的问题。唯一键的本质和主键差不多唯一键允许为空而且可以多个为空空字段不做唯一性比较。关于唯一键和主键的区别我们可以简单理解成主键更多的是标识唯一性的。而唯一键更多的是保证在业务上不要和别的信息出现重复。乍一听好像没啥区别我们举一个例子假设一个场景(当然具体可能并不是这样仅仅为了帮助大家理解)比如在公司我们需要一个员工管理系统系统中有一个员工表员工表中有两列信息一个身份证号码一个是员工工号我们可以选择身份号码作为主键。而我们设计员工工号的时候需要一种约束而所有的员工工号都不能重复。具体指的是在公司的业务上不能重复我们设计表的时候需要这个约束那么就可以将员工工号设计成为唯一键。一般而言我们建议将主键设计成为和当前业务无关的字段这样当业务调整的时候我们可以尽量不会对主键做过大的调整。mysql create table stu ( - id char(20) unique comment 这是一个学生的唯一键, - name varchar(32) not null - ); Query OK, 0 rows affected (0.01 sec) mysql desc stu; ----------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------- | id | char(20) | YES | UNI | NULL | | | name | varchar(32) | NO | | NULL | | ----------------------------------------------- 2 rows in set (0.00 sec)唯一键冲突mysql insert into stu (id,name) values (12345,张三); Query OK, 1 row affected (0.00 sec) mysql select * from stu; --------------- | id | name | --------------- | 12345 | 张三 | --------------- 1 row in set (0.00 sec) mysql insert into stu (id,name) values (12345,李四); ERROR 1062 (23000): Duplicate entry 12345 for key id但如果是NULL就不冲突mysql insert into stu (id,name) values (NULL,李四); Query OK, 1 row affected (0.00 sec) mysql select * from stu; --------------- | id | name | --------------- | 12345 | 张三 | | NULL | 李四 | --------------- 2 rows in set (0.00 sec) mysql insert into stu (id,name) values (NULL,李四); Query OK, 1 row affected (0.00 sec) mysql insert into stu (id,name) values (NULL,李四); Query OK, 1 row affected (0.00 sec) mysql insert into stu (id,name) values (NULL,李四); Query OK, 1 row affected (0.00 sec) mysql select * from stu; --------------- | id | name | --------------- | 12345 | 张三 | | NULL | 李四 | | NULL | 李四 | | NULL | 李四 | | NULL | 李四 | --------------- 5 rows in set (0.00 sec)NULL不参与计算mysql select NULL 1; --------- | NULL 1 | --------- | NULL | --------- 1 row in set (0.00 sec)mysql create table student( - id char(20) primary key, - name varchar(32) not null, - telphone char(20) - ); Query OK, 0 rows affected (0.01 sec) mysql desc student; -------------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------------- | id | char(20) | NO | PRI | NULL | | | name | varchar(32) | NO | | NULL | | | telphone | char(20) | YES | | NULL | | -------------------------------------------------- 3 rows in set (0.00 sec) mysql insert into student (id,name,telphone) values (123,张飞,1345678910); Query OK, 1 row affected (0.00 sec) mysql select * from student; ------------------------- | id | name | telphone | ------------------------- | 123 | 张飞 | 1345678910 | ------------------------- 1 row in set (0.00 sec) mysql insert into student (id,name,telphone) values (124,刘备,1345678911); Query OK, 1 row affected (0.00 sec) mysql insert into student (id,name,telphone) values (125,孙权,1345678911); Query OK, 1 row affected (0.00 sec) mysql select * from student; ------------------------- | id | name | telphone | ------------------------- | 123 | 张飞 | 1345678910 | | 124 | 刘备 | 1345678911 | | 125 | 孙权 | 1345678911 | ------------------------- 3 rows in set (0.00 sec)像这种情况就需要唯一键因为这个不同人电话号码不同。mysql drop table student; Query OK, 0 rows affected (0.01 sec) mysql create table student( - id char(20) primary key, - name varchar(32) not null, - telphone char(20) unique key, - qq varchar(64) unique key - ); Query OK, 0 rows affected (0.01 sec) mysql desc student; -------------------------------------------------- | Field | Type | Null | Key | Default | Extra | -------------------------------------------------- | id | char(20) | NO | PRI | NULL | | | name | varchar(32) | NO | | NULL | | | telphone | char(20) | YES | UNI | NULL | | | qq | varchar(64) | YES | UNI | NULL | | -------------------------------------------------- 4 rows in set (0.00 sec) mysql insert into student values (123,张三,1341234,23456); Query OK, 1 row affected (0.01 sec) mysql insert into student values (123,张三,1341234,23456); ERROR 1062 (23000): Duplicate entry 123 for key PRIMARY mysql insert into student values (1234,李四,1341234,23456); ERROR 1062 (23000): Duplicate entry 1341234 for key telphone mysql insert into student values (1234,李四,1341235,23456); ERROR 1062 (23000): Duplicate entry 23456 for key qq mysql insert into student values (1234,李四,1341235,23457); Query OK, 1 row affected (0.01 sec) mysql select * from student; ------------------------------- | id | name | telphone | qq | ------------------------------- | 123 | 张三 | 1341234 | 23456 | | 1234 | 李四 | 1341235 | 23457 | ------------------------------- 2 rows in set (0.00 sec)特性主键 (PRIMARY KEY)唯一键 (UNIQUE KEY)唯一性不允许重复不允许重复NULL 值不允许 NULL允许 NULL且多个 NULL 可以共存数量限制只能有 1 个可以有多个是否必须推荐有但不是必须可选主键和唯一键不冲突反而是互相补充的。4.外键外键用于定义主表和从表之间的关系外键约束主要定义在从表上主表则必须是有主键约束或unique约束。当定义外键后要求外键列数据必须在主表的主键列存在或为null。语法foreign key (字段名) references 主表(列)外键1.从表跟主表的关联关系2.产生外键约束1.关联 2.约束建立一个学生表一个班级表class 主表student 从表mysql drop table student; Query OK, 0 rows affected (0.01 sec) mysql create table if not exists student ( - id int unsigned primary key auto_increment, - name varchar(20) not null, - telphone varchar(32) not null, - class_id int - ); Query OK, 0 rows affected (0.01 sec) mysql desc student; ---------------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ---------------------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | telphone | varchar(32) | NO | | NULL | | | class_id | int(11) | YES | | NULL | | ---------------------------------------------------------------- 4 rows in set (0.01 sec) mysql create table if not exists class ( - id int primary key, - name varchar(32) not null - ); Query OK, 0 rows affected (0.01 sec) mysql desc class; ----------------------------------------------- | Field | Type | Null | Key | Default | Extra | ----------------------------------------------- | id | int(11) | NO | PRI | NULL | | | name | varchar(32) | NO | | NULL | | ----------------------------------------------- 2 rows in set (0.00 sec)mysql insert into class values (1,通信101); Query OK, 1 row affected (0.00 sec) mysql insert into class values (2,通信102); Query OK, 1 row affected (0.00 sec) mysql select * from class; --------------- | id | name | --------------- | 1 | 通信101 | | 2 | 通信102 | --------------- 2 rows in set (0.00 sec) mysql insert into student (name,telphone,class_id) values (张三,123456789,1); Query OK, 1 row affected (0.00 sec) mysql select * from student; --------------------------------- | id | name | telphone | class_id | --------------------------------- | 1 | 张三 | 123456789 | 1 | --------------------------------- 1 row in set (0.00 sec) mysql insert into student (name,telphone,class_id) values (李四,123456798,1); Query OK, 1 row affected (0.00 sec) mysql insert into student (name,telphone,class_id) values (王五,123456798,1); Query OK, 1 row affected (0.00 sec) mysql select * from class; --------------- | id | name | --------------- | 1 | 通信101 | | 2 | 通信102 | --------------- 2 rows in set (0.00 sec) mysql select * from student; --------------------------------- | id | name | telphone | class_id | --------------------------------- | 1 | 张三 | 123456789 | 1 | | 2 | 李四 | 123456798 | 1 | | 3 | 王五 | 123456798 | 1 | --------------------------------- 3 rows in set (0.00 sec) mysql insert into student (name,telphone,class_id) values (赵六,123459821,2); Query OK, 1 row affected (0.00 sec) mysql insert into student (name,telphone,class_id) values (田七,123459821,3); Query OK, 1 row affected (0.00 sec) mysql select * from student; --------------------------------- | id | name | telphone | class_id | --------------------------------- | 1 | 张三 | 123456789 | 1 | | 2 | 李四 | 123456798 | 1 | | 3 | 王五 | 123456798 | 1 | | 4 | 赵六 | 123459821 | 2 | | 5 | 田七 | 123459821 | 3 | --------------------------------- 5 rows in set (0.00 sec)可以看到只有两个班级但是却插入了一个3号班级。这在逻辑上是不被允许的。这class跟student两个是有外键之名的(关联关系)但是没有外键之实的(没有约束)//删从表 mysql drop table student; Query OK, 0 rows affected (0.00 sec) //再新建从表连接关系 mysql create table if not exists student ( - id int unsigned primary key, - name varchar(20) not null, - telphone varchar(32) unique key, - class_id int, - foreign key(class_id) references class(id) - ); Query OK, 0 rows affected (0.01 sec)mysql desc student; ------------------------------------------------------- | Field | Type | Null | Key | Default | Extra | ------------------------------------------------------- | id | int(10) unsigned | NO | PRI | NULL | | | name | varchar(20) | NO | | NULL | | | telphone | varchar(32) | YES | UNI | NULL | | | class_id | int(11) | YES | MUL | NULL | | ------------------------------------------------------- 4 rows in set (0.01 sec) mysql insert into student values (100,张三,12345,1); Query OK, 1 row affected (0.00 sec) mysql insert into student values (101,张三,12346,2); Query OK, 1 row affected (0.00 sec) //外键约束发力了 mysql insert into student values (102,李四,12347,3); ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (test_db.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (id)) //外键约束 mysql delete from class where id1; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (test_db.student, CONSTRAINT student_ibfk_1 FOREIGN KEY (class_id) REFERENCES class (id))5.综合案例--阅读有一个商店的数据记录客户及购物情况有以下三个表组成》商品goods(商品编号goods_id商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)》客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex身份证card_id)》购买purchase(购买订单号order_id,客户号customer_id,商品号goods_id,购买数量nums)要求》每个表的主外键》客户的姓名不能为空值》邮箱不能重复》客户的性别(男女)SQL:-- 创建数据库create database if not exists bit32malldefault 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));感谢你的观看期待我们下次再见