数据库 表设计 MySQL

表设计

约束

为了保证入库数据的合理性，添加的各种规则。

约束的分类

准备测试用的表格：

CREATE TABLE emp (

id INT,  -- 员工id，主键且自增长

    ename VARCHAR(50), -- 员工姓名，非空且唯一

    joindate DATE,  -- 入职日期，非空

    salary DOUBLE(7,2),  -- 工资，非空

    bonus DOUBLE(7,2)  -- 奖金，如果没有将近默认为0

);

DROP TABLE IF EXISTS emp;

CREATE TABLE emp (

  id INT PRIMARY KEY, -- 员工id，主键且自增长

  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一

  joindate DATE NOT NULL , -- 入职日期，非空

  salary DOUBLE(7,2) NOT NULL , -- 工资，非空

  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0

);

先插入一条没有问题的数据，验证表格创建的正确性：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'张三','1999-11-11',8800,5000);

如id就不能为空。非空约束： 关键字是 NOT NULL

添加表字段、修改字段属性和删除约束三种情况的使用方式：

CREATE TABLE 表名(

       列名 数据类型 NOT NULL,

       …

    );

ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

ALTER TABLE 表名 MODIFY 字段名 数据类型;

 

验证非空约束：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);
 

唯一约束：关键字是  UNIQUE

保证列中没有相同数据。

创建表时添加唯一约束：

CREATE TABLE 表名(

       列名 数据类型 UNIQUE [AUTO_INCREMENT],

       -- AUTO_INCREMENT: 当不指定值时自动增长

       …

    );

ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;建完表后添加唯一约束：

删除唯一约束：

ALTER TABLE 表名 DROP INDEX 字段名;

验证唯一约束：

INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);
 

 

主键约束： 关键字是  PRIMARY KEY

一般是id。

创建表时添加主键约束，后面的AUTO_INCREMENT是自增，可以根据实际添加：

CREATE TABLE 表名(

       列名 数据类型 PRIMARY KEY AUTO_INCREMENT,

       …

    );

ALTER TABLE 表名 ADD PRIMARY KEY(字段名);建完表后添加主键约束：

删除主键约束：

ALTER TABLE 表名 DROP PRIMARY KEY;

检查约束： 关键字是  CHECK

保证列中的值满足某一条件。比如年龄大于1小于200。

mysql没有提供支持，实际中在代码层次做校验。

默认约束： 关键字是   DEFAULT

保存数据时，未指定值则采用默认值，比如缺考的人成绩也不能为空，可以给个0作为默认值。

创建表时添加默认约束：

CREATE TABLE 表名(

       列名 数据类型 DEFAULT 默认值,

       …

    );

ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;建完表后添加默认约束

删除默认约束：

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

验证默认约束：

INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

外键约束： 关键字是  FOREIGN KEY

两个表的数据之间建立链接的字段，这样在做删除修改的时候，关联的表就能更好保证一致性。

准备测试数据：

-- 删除表

DROP TABLE IF EXISTS emp;

DROP TABLE IF EXISTS dept;

-- 部门表

CREATE TABLE dept(

id int primary key auto_increment,

dep_name varchar(20),

addr varchar(20)

);

-- 员工表

CREATE TABLE emp(

id int primary key auto_increment,

name varchar(20),

age int,

dep_id int,

-- 添加外键 dep_id,关联 dept 表的id主键

CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)

);

添加数据：

-- 添加 2 个部门

insert into dept(dep_name,addr) values

('研发部','广州'),('销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门

INSERT INTO emp (NAME, age, dep_id) VALUES

('张三', 20, 1),

('李四', 20, 1),

('王五', 20, 1),

('赵六', 20, 2),

('孙七', 22, 2),

('周八', 18, 2);

建完表后添加外键约束：

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

删除外键约束：

ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

alter table emp drop FOREIGN key fk_emp_dept;

查看外键关系：

 

选中要查看的表，然后右键，逆向到模型。

表关系

一对多（多对一）

举例：

DROP TABLE IF EXISTS tb_emp;

  DROP TABLE IF EXISTS tb_dept;

  

  -- 部门表

  CREATE TABLE tb_dept(

   id int primary key auto_increment,

   dep_name varchar(20),

   addr varchar(20)

  );

  -- 员工表

  CREATE TABLE tb_emp(

   id int primary key auto_increment,

   name varchar(20),

   age int,

   dep_id int,

  

   -- 添加外键 dep_id,关联 dept 表的id主键

   CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)

  );

多对多

举例：

DROP TABLE IF EXISTS tb_order_goods;

  DROP TABLE IF EXISTS tb_order;

  DROP TABLE IF EXISTS tb_goods;

  

  -- 订单表

  CREATE TABLE tb_order(

   id int primary key auto_increment,

   payment double(10,2),

   payment_type TINYINT,

   status TINYINT

  );

  

  -- 商品表

  CREATE TABLE tb_goods(

   id int primary key auto_increment,

   title varchar(100),

   price double(10,2)

  );

  

  -- 订单商品中间表

  CREATE TABLE tb_order_goods(

   id int primary key auto_increment,

   order_id int,

   goods_id int,

   count int

  );

  

  -- 建完表后，添加外键

  alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);

  alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

一对一

先按照多对一去想，然后在多的一端加上唯一的约束：

create table tb_user_desc (

id int primary key auto_increment,

city varchar(20),

edu varchar(10),

income int,

status char(2),

des varchar(100)

);

create table tb_user (

id int primary key auto_increment,

photo varchar(100),

nickname varchar(50),

age int,

gender char(1),

desc_id int unique,

-- 添加外键

CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)

);