MySQL(十五)DDL之常见的约束

一、常见的约束

NOT NULL:非空,该字段的值必填

UNIQUE:唯一,该字段的值不可重复

DEFAULT:默认,该字段的值不用手动插入有默认值

PRIMARY KEY:主键,该字段的值不可重复并且非空 unique+not null

FOREIGN KEY:外键,该字段的值引用了另外的表的字段

主键和唯一

1、区别:

①、一个表至多有一个主键,但可以有多个唯一

②、主键不允许为空,唯一可以为空

2、相同点

都具有唯一性

都支持组合键,但不推荐

外键:

1、用于限制两个表的关系,从表的字段值引用了主表的某字段值

2、外键列和主表的被引用列要求类型一致,意义一样,名称无要求

3、主表的被引用列要求是一个key(一般就是主键)

4、插入数据,先插入主表

5、删除数据,先删除从表

可以通过以下两种方式来删除主表的记录

#方式一:级联删除

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE CASCADE;

#方式二:级联置空

ALTER TABLE stuinfo ADD CONSTRAINT fk_stu_major FOREIGN KEY(majorid) REFERENCES major(id) ON DELETE SET NULL;

二、创建表时添加约束

create table 表名(

字段名 字段类型 not null,#非空

字段名 字段类型 primary key,#主键

字段名 字段类型 unique,#唯一

字段名 字段类型 default 值,#默认

constraint 约束名 foreign key(字段名) references 主表(被引用列)

);

注意:

列级约束: 除了外键其他类型都支持,不可以起约束名;

表级约束: 除了非空和默认其他类型都支持,可以起约束名,但对主键无效。

列级约束可以在一个字段上追加多个,中间用空格隔开,没有顺序要求

三、修改表时添加或删除约束

1、非空

添加非空

alter table 表名 modify column 字段名 字段类型 not null;

删除非空

alter table 表名 modify column 字段名 字段类型 ;

2、默认

添加默认

alter table 表名 modify column 字段名 字段类型 default 值;

删除默认

alter table 表名 modify column 字段名 字段类型 ;

3、主键

添加主键

alter table 表名 add【 constraint 约束名】 primary key(字段名);

删除主键

alter table 表名 drop primary key;

4、唯一

添加唯一

alter table 表名 add【 constraint 约束名】 unique(字段名);

删除唯一

alter table 表名 drop index 索引名;

5、外键

添加外键

alter table 表名 add【 constraint 约束名】 foreign key(字段名) references 主表(被引用列);

删除外键

alter table 表名 drop foreign key 约束名;

四、自增长列

特点:

1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1

auto_increment_increment

如果要更改起始值:手动插入值

如果要更改步长:更改系统变量

set auto_increment_increment=值;

2、一个表至多有一个自增长列

3、自增长列只能支持数值型

4、自增长列必须为一个key

(1)创建表时设置自增长列

create table 表(

字段名 字段类型 约束 auto_increment

)

(2)修改表时设置自增长列

alter table 表 modify column 字段名 字段类型 约束 auto_increment

(3)删除自增长列

alter table 表 modify column 字段名 字段类型 约束

五、实践

(1)创建数据库

CREATE DATABASE students;

(2)USE students;

(3)创建科目表,添加主键约束

CREATE TABLE major(id INT PRIMARY KEY,majorName VARCHAR(20));

(4)创建学生信息表,添加列级约束

CREATE TABLE stuinfo(

id INT PRIMARY KEY,#主键

stuName VARCHAR(20) NOT NULL UNIQUE,#非空

seat INT UNIQUE,#唯一

age INT DEFAULT 18,#默认约束

majorId INT REFERENCES major(id)#外键

);

(5)查看stuinfo中的所有索引,包括主键、外键、唯一

SHOW INDEX FROM stuinfo;

(6)添加表级约束

通用的写法

(7)修改表时添加约束,先重新创建一个表

(8)添加非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NOT NULL;

(9)添加默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT DEFAULT 18;

(10)添加主键

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN id INT PRIMARY KEY;

②表级约束

ALTER TABLE stuinfo ADD PRIMARY KEY(id);

(11)添加唯一

①列级约束

ALTER TABLE stuinfo MODIFY COLUMN seat INT UNIQUE;

②表级约束

ALTER TABLE stuinfo ADD UNIQUE(seat);

(12)添加外键

ALTER TABLE stuinfo ADD CONSTRAINT fk_stuinfo_major FOREIGN KEY(majorid) REFERENCES major(id);

(13)删除非空约束

ALTER TABLE stuinfo MODIFY COLUMN stuname VARCHAR(20) NULL;

(14)删除默认约束

ALTER TABLE stuinfo MODIFY COLUMN age INT ;

(15)删除主键

ALTER TABLE stuinfo DROP PRIMARY KEY;

(16)删除唯一

ALTER TABLE stuinfo DROP INDEX seat;

(17)删除外键

ALTER TABLE stuinfo DROP FOREIGN KEY fk_stuinfo_major;

原文发布于微信公众号 - Java后端生活(javaNotebook)

原文发表时间:2018-05-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏java工会

十个mysql语句的优化方法

1906
来自专栏极客生活

mysql 数据去重

从excel中导入了一部分数据到mysql中,有很多数据是重复的,而且没有主键,需要按照其中已经存在某一列对数据进行去重。

842
来自专栏散尽浮华

mysql操作命令梳理(2)-alter(update、insert)

在mysql运维操作中会经常使用到alter这个修改表的命令,alter tables允许修改一个现有表的结构,比如增加或删除列、创造或消去索引、改变现有列的类...

1956
来自专栏battcn

MySQL - 索引详解

索引依托于存储引擎的实现,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引,总索引长度至...

722
来自专栏小白安全

小白博客 MYSQL常用语句

用户管理: 1、新建用户: >CREATE USER name IDENTIFIED BY ‘ssapdrow’; 2、更改密码: >...

3109
来自专栏开源优测

SQL常用的基础语法

SQL常用的语法 本文主要分享SQL日常使用过程中常用的语法样式。 在sql语句中,主要的关键词有SELECT、INSERT、UPDATE、DELETE、ALE...

3158
来自专栏java达人

left join 过滤条件写在on后面和写在where 后面的区别

create table t1(id int, feild int); insert into t1 values(1 , 1); insert into t1...

25210
来自专栏PHP在线

MySQL索引类型一览 让MySQL高效运行起来

索引是快速搜索的关键。MySQL索引的建立对于MySQL的高效运行是很重要的。下面介绍几种常见的MySQL索引类型。 在数据库表中,对字段建立索引可以大大提高...

3295
来自专栏撸码那些事

MySQL——通过EXPLAIN分析SQL的执行计划

在MySQL中,我们可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序。

1004
来自专栏杨建荣的学习笔记

MySQL修改数据类型的问题总结(r10笔记第74天)

昨天快下班的时候,突然开发的同事找我说有个紧急需求,负责这个业务的DBA同事回家了,想让我帮忙看看,运行个SQL语句,几秒钟就好。我一听,就本着人道主义的精神留...

34210

扫码关注云+社区