百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

day07-1MySQL约束

2022-10-10 分类：野生技术 阅读(214) 评论(0)

MySQL约束

  • 基本介绍

约束用于确保数据库的数据满足特定的商业规则

在mysql中，约束包括：not null，unique，primary key，foreign key 和check 5种

1.primary key(主键)

字段名 字段类型 primary key

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

  • 细节说明
  1. primary key不能重复而且不能为null
  2. 一张表最多只能有一个主键，但是可以是复合主键
  3. 主键的指定方式有两种
    • 直接在字段名后面指定：字段名 primary key
    • 在表定义最后写 primary key（列名）
  4. 使用desc 表名，可以看到primary key的情况
  5. 在实际开发中，每一张表往往都会设计一个主键

例子

-- 主键的使用

-- id	name 	email
CREATE TABLE t17(
	id INT PRIMARY KEY, -- 表示id列是主键
	`name` VARCHAR(32),
	email VARCHAR(32)
);
INSERT INTO t17 VALUES(1,\'jack\',\'jack@sohu.com\');
INSERT INTO t17 VALUES(2,\'tom\',\'tom@sohu.com\');


-- 1. primary key不能重复而且不能为null
INSERT INTO t17 VALUES(NULL,\'jack\',\'jack@sohu.com\');-- 插入失败，Column \'id\' cannot be null

-- 2. 一张表最多只能有一个主键，但是可以是复合主键（比如id+name）
-- 演示复合主键
CREATE TABLE t18(
	id INT, 
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id,`name`) -- 这里就是复合主键
);

INSERT INTO t18 VALUES(1,\'tom\',\'tom@sohu.com\'); -- ok
INSERT INTO t18 VALUES(1,\'jack\',\'jack@sohu.com\');-- OK
INSERT INTO t18 VALUES(1,\'tom\',\'12345@sohu.com\'); -- false
SELECT * FROM t18;

-- 3. 主键的指定方式有两种
--  3.1直接在字段名后面指定：字段名 primary key
CREATE TABLE t19(
	id INT PRIMARY KEY, 
	`name` VARCHAR(32),
	email VARCHAR(32)
);

--  3.2在表定义最后写 primary key（列名）
CREATE TABLE t20(
	id INT, 
	`name` VARCHAR(32),
	email VARCHAR(32),
	PRIMARY KEY(id) 
);

-- 4. 使用desc 表名，可以看到primary key的情况
DESC t20; -- 查看t20的情况，显示约束的情况

2.not null(非空)

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

字段名 字段类型 not null

3.unique(唯一)

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

字段名 字段类型 unique
  • unique细节
  1. 如果没有指定not null，则unique字段可以有多个null
  2. 一张表可以有多个unique字段
-- unique的使用

CREATE TABLE t21(
	id INT UNIQUE, -- 表示id列不可重复 
	`name` VARCHAR(32),
	email VARCHAR(32)
)
INSERT INTO t21 VALUES(1,\'jack\',\'jack@sohu.com\'); -- ok
INSERT INTO t21 VALUES(1,\'tom\',\'tom@sohu.com\');  -- false，Duplicate entry \'1\' for key \'id\'

-- unique细节

-- 1. 如果没有指定not null，则unique字段可以有多个null
-- 如果一个列（字段），是 unique not null 则使用效果类似 primary key
INSERT INTO t21 VALUES(NULL,\'tom\',\'tom@sohu.com\');-- ok
SELECT * FROM t21;

-- 2. 一张表可以有多个unique字段
CREATE TABLE t22(
	id INT UNIQUE, -- 表示id列不可重复 
	`name` VARCHAR(32) UNIQUE, -- 表示name也不可以重复
	email VARCHAR(32)
)
DESC t22;

4.foreign key(外键)

  • foreign key(外键)

用于定义主表和从表之间的关系：

外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。

当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null

  • 语法
foreign key(本表字段名) references 主表名(主键名或unique字段名)

例子

-- 外键演示
-- 创建 主表 my_class
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\'),(200,\'web\');
INSERT INTO my_class VALUES(300,\'php\');
SELECT * FROM my_class;

INSERT INTO my_stu VALUES(1,\'tom\',100);
INSERT INTO my_stu VALUES(2,\'jack\',200);
INSERT INTO my_stu VALUES(3,\'hsp\',300); 
INSERT INTO my_stu VALUES(4,\'mary\',400); -- 失败，因为400号班级不存在

SELECT * FROM my_stu;
  • 外键细节说明
  1. 外键指向的主表的字段，要求是primary key 或是unique
  2. 表的类型是innodb，这样的表才支持外键
  3. 外键字段的类型要和主键字段的类型一致（长度可以不同）
  4. 外键字段的值，必须在主键字段中出现过，或者为null [前提是外键字段允许为null]
  5. 一旦建立主外键的关系，数据就不能随意删除了

5.check

  • check

用于强制行数据必须满足的条件。假定在sal列上定义了check约束，并要求sal列值在1000~2000之间，如果不在此范围，就会提示出错

提示：oracle和sql server均支持check，但是mysql5.7目前还不支持check，只做语法校验，但不会生效

  • 语法
列名 类型 check(check条件)

在mysql中实现check的功能，一般是在程序中控制或者通过触发器完成

例子

-- 演示check的使用
-- 目前mysql5.7还不支持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)
);

6.练习

商店售货系统表设计案例

现有一个商店的数据库shop_db，记录客户及其购物情况，由下面三个表组成：

  • 商品表 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

建表，在定义中要求声明[进行合理设计]

  1. 每个表的主外键
  2. 客户的姓名不能为空值
  3. 电邮不能够重复
  4. 客户的性别[男|女]
  5. 单价unitprice在1.0~9999.99之间
-- 使用约束的课堂练习

-- 1,创建数据库
CREATE DATABASE shop_db;

-- 2,商品表 goods
CREATE TABLE goods(
	 goods_id INT PRIMARY KEY, -- 商品号
	 goods_name VARCHAR(64) NOT NULL DEFAULT \'\', -- 商品名
	 unitprice DECIMAL(10,2) NOT NULL DEFAULT 0 -- 单价
		CHECK(unitprice BETWEEN 1.0 AND 9999.99), 
	 category INT NOT NULL DEFAULT 0, -- 商品类别
	 provider VARCHAR(64) NOT NULL DEFAULT \'\' -- 供应商
);

-- 3,客户表 customer
CREATE TABLE customer(
	 customer_id CHAR(8) PRIMARY KEY, -- 客户号
	 `name` VARCHAR(64) NOT NULL DEFAULT \'\', -- 姓名
	 address VARCHAR(64) NOT NULL DEFAULT \'\', -- 地址
	 email VARCHAR(64) UNIQUE NOT NULL, -- 电邮
	 sex ENUM(\'男\',\'女\') NOT NULL, -- 使用枚举类型，性别
	 card_Id CHAR(18)  -- 身份证
);

-- 4,购买表 purchase
CREATE TABLE purchase(
	 order_id INT PRIMARY KEY, -- 订单号
	 customer_id CHAR(8) NOT NULL DEFAULT \'\', -- 客户号
	 goods_id INT NOT NULL DEFAULT 0, -- 商品号
	 num INT NOT NULL DEFAULT 0, -- 购买数量 
	FOREIGN KEY(customer_id) REFERENCES  customer(customer_id),
	FOREIGN KEY(goods_id) REFERENCES  goods(goods_id)
);

DESC goods;
DESC customer;
DESC purchase;

image-20221007174116878
image-20221007174053112
image-20221007174233666

7.自增长

  • 语法
字段名 整型 primary key auto_increment

例子：在某张表中，存在一个id列（整数）

我们希望在添加记录的时候，该列从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@qq.com\',\'jack\');
INSERT INTO t24 VALUES(NULL,\'tom@qq.com\',\'tom\');
INSERT INTO t24(email,`name`) VALUES(\'hsp@qq.com\',\'hsp\');
SELECT * FROM t24;

image-20221007180523019
image-20221007175725172

  • 自增长使用细节

  1. 一般来说自增长是和primary key配合使用的

  2. 自增长也可以单独使用 [但是需要配合一个unique]

  3. 自增长修饰的字段为整数型（虽然小数也可以但是很少这样使用）

  4. 自增长默认从1开始，也可以通过如下命令来修改

    alter table 表名 auto_increment = xxx;

  5. 如果添加数据时候，给自增长字段（列）指定有值，则以指定的值为准。一旦指定了值，下一次自增长就会以指定的值开始增长。因此，如果指定了自增长，一般来说就按自增长的规则来添加数据

-- 修改默认的自增长开始值
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 =100;
INSERT INTO t25 VALUES(NULL,\'jack@qq.com\',\'jack\');
-- 指定值
INSERT INTO t25 VALUES(666,\'hsp@qq.com\',\'hsp\');
-- 一旦指定了值，下一次自增长就会以指定的值开始增长
INSERT INTO t25 VALUES(NULL,\'marry@qq.com\',\'marry\');
SELECT * FROM t25;

image-20221007183314672

来源：https://www.cnblogs.com/liyuelian/p/16766583.html
本站部分图文来源于网络，如有侵权请联系删除。

未经允许不得转载：百木园 » day07-1MySQL约束
标签：

相关推荐

  • 暂无文章

热门文章