MySQL(六）：mysql 约束

基本介绍：约束用于确保数据库的数据满足特定的商业规则，约束包括：not null、unique、primary key、foreign key 、check五种。

一、主键的使用（primary key)

字段名 字段类型 primary key

用于唯一的表示表行的数据，当定义主键约束后，该列不能重复。

#主键的使用 primary key


	

INSERT INTO t17 VALUES(1,'tom','tom.suhu.com'),
		(2,'jack','jack.sohu.com');
		
 -- 设定为主键的列不能重复
INSERT INTO t17 VALUES(1,'grace','grace.sohu.com');

SELECT * FROM t17;

-- 主键使用的细节
-- primary key 不能重复且不能为空
INSERT INTO t17 VALUES(NULL,'grace','grace.sohu.com');

-- 一张表中最多只能有一个主键，但可以是复合主键

CREATE TABLE t18(
	id INT PRIMARY KEY,-- id是主键
	`name` VARCHAR(32) PRIMARY KEY, --错误的
	email VARCHAR(32));

-- 复合主键

CREATE TABLE t18(
	id INT,
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY (id,`name`));-- 复合主键
	
INSERT INTO t18 VALUES(1,'tom','grace.sohu.com');
INSERT INTO t18 VALUES(1,'jack','jack.suhu.com');-- 主键只有部分相同，可以成功
INSERT INTO t18 VALUES(1,'tom','xx.sohu.com'); -- 主键完全相同，不能插入

SELECT * FROM t18;

-- 主键的指定方式有两种
-- 1、直接在字段名后指定： 字段名 字段类型 primary key
-- 2、在表定义最后写primary key(字段名）

-- 查询表中的主键方法desc
DESC t17;
DESC t18;

-- 在实际开发中，每个表往往都会设计一个主键

二、非空（not null)

如果在列上定义了非空 not null，那么在插入数据时，必须为列提供数据

字段名 字段类型 not null

三、unique（唯一）

当定义了唯一约束后，该列值是不能重复的

字段名 字段类型 unique

#unique的使用

CREATE TABLE t20(
	id INT UNIQUE,
	`name` VARCHAR(32),
	email VARCHAR(32));

INSERT INTO t20 VALUES(1,'jack','jack.com');
SELECT * FROM t20
INSERT INTO t20 VALUES(1,'tom','tom.com');

-- unique细节
-- 1、如果没有指定not null,则unique 字段可以有多个null
-- 如果一个列（字段—），是unique not null约束的，那么使用效果类似于primary key
INSERT INTO t20 VALUES(NULL,'tom','tom.com');


-- 2、一张表可以有多个unique字段
CREATE TABLE t21(
	id INT UNIQUE,
	`name` VARCHAR(32) UNIQUE,
	email VARCHAR(32));
	
DESC t21;
INSERT INTO t21 VALUES(1,'jack','jack.com');

四、外键（foreign key)

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束，当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

foreign key (本表字段名） references
主表名（主键名或unique字段名）

#外键演示

-- 创建主表myclass
CREATE TABLE my_class(
	id INT PRIMARY KEY, -- 班级编号
	`name` VARCHAR(32) NOT NULL DEFAULT ' ');


-- 创建从表my_stu
CREATE TABLE my_stu(
	id INT PRIMARY KEY, -- 学生编号
	`name` VARCHAR(32) NOT NULL DEFAULT ' ',
	class_id INT,
	FOREIGN KEY(class_id) REFERENCES my_class(id));
	

INSERT INTO my_class VALUES(100,'java'),(2,'web');
SELECT * FROM my_class;


INSERT INTO my_stu VALUES(1,'jack',100);

INSERT INTO my_stu VALUES(2,'tom',200); -- 失败，因为200班级不存在
INSERT INTO my_stu VALUES(3,'cindy',2);

SELECT * FROM my_stu;

#注意
-- 1、外键指向表的字段，必须是primary或者unique类型
-- 2、表的类型是innodb，这样的表才支持外键
-- 3、外键字段的类型要和主键字段的类型一致（长度可以不同）
-- 4、外键字段的值，必须在主键字段中出现过，或者为null(前提是外键字段允许为null)
INSERT INTO my_stu VALUES(4,'mary',NULL); -- 可以，外键没有写not null
-- 5、一旦建立主外键的关系，数据不能随意删除
DELETE FROM my_class 
	WHERE id=100;  -- 不能执行，有一个外键指向了100

五、check

用于强制数据必须满足的条件，假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在这个区间内就会提示出错。
oracle和sql server均支持check名单时mysql5.7不支持，只做语法校验，但不会生效

列名 类型 check(check 条件）

#check演示

CREATE TABLE t23(
	id INT PRIMARY KEY,
	`name` VARCHAR(32),
	sex VARCHAR(6) CHECK(sex IN ('man','woman')),
	sal DOUBLE CHECK(sal>1000 AND sal <2000));
	
INSERT INTO t23 VALUES(1,'mary','man',1000);  -- 失败

INSERT INTO t23 VALUES(1,'mary','kid',2000);   -- 失败

INSERT INTO t23 VALUES(1,'mary','man',1200);   -- 成功

SELECT * FROM t23;

六、案例

DROP TABLE goods;
DROP TABLE customer;
CREATE TABLE goods(
	goods_id INT PRIMARY KEY,
	goods_name VARCHAR(32),
	unitprice DECIMAL(10,2) CHECK(unitprice >1.0 AND unitprice < 9999.99),
	category INT NOT NULL DEFAULT 0,
	provider VARCHAR(32) NOT NULL DEFAULT ' '
	);


CREATE TABLE customer(
	customer_id INT PRIMARY KEY,
	`name` VARCHAR(32) NOT NULL DEFAULT ' ',
	address VARCHAR(32) NOT NULL DEFAULT ' ',
	email VARCHAR(100) UNIQUE NOT NULL,
	-- sex varchar(32) check(sex in ('man','woman')),
	sex ENUM('man','woman') NOT NULL,
	card_id INT);


CREATE TABLE purchase(
	order_id INT UNSIGNED PRIMARY KEY,
	customer_id INT NOT NULL DEFAULT 0,
	goods_id INT NOT NULL DEFAULT 0,
	nums INT NOT NULL DEFAULT 0,
	FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
	FOREIGN KEY (goods_id) REFERENCES goods(goods_id)
	);

七、自增长

在某张表中，存在一个id列（整数），希望在添加记录的时候，该列从1开始，自动的增长

字段名 整型 primary key auto_increment

添加自增长的字段的方式
insert into XXX(字段1，字段2，..) values(null,'值‘）；
insert into XXX(字段2...) values('值1’，'值2');
insert into XXX values(null，’值1')

#演示自增长

-- 创建表
CREATE TABLE t24(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT ' ',
	`name` VARCHAR(32) NOT NULL DEFAULT ' ');

DESC t24

INSERT INTO t24 VALUES(NULL,'jack.com','jack');
INSERT INTO t24 VALUES(NULL,'tom.com','tom');


INSERT INTO t24(email,`name`) VALUES('cindy.com','cindy');
SELECT * FROM t24;

-- 细节
-- 1、auto_increment需要和primary key 或者 unique
-- 2、自增长修饰的字段为整数型（小数很少用）
-- 3、自增长默认从1开始，也可以通过alter table 表名 auto_increment= 新的开始值

CREATE TABLE t25(
	id INT PRIMARY KEY AUTO_INCREMENT,
	email VARCHAR(32) NOT NULL DEFAULT ' ',
	`name` VARCHAR(32) NOT NULL DEFAULT ' ');
ALTER TABLE t25 AUTO_INCREMENT=10;

INSERT INTO t25 VALUES(NULL,'jack.com','jack');
INSERT INTO t25 VALUES(NULL,'tom.com','tom');


INSERT INTO t25(email,`name`) VALUES('cindy.com','cindy');
SELECT * FROM t25;


-- 4、如果添加数据时，给自增长字段指定值，则以指定值为准，其后的值从指定值开始增长，一般来说，就按照自增长的规则来添加数据
INSERT INTO t25 VALUES(666,'grace','grace.com');