【数据库】MySQL设计

目录

【约束】 

【非空约束】

【唯一约束】

【主键约束】

【默认约束】

【约束——项目示例】

【外键约束】

【数据库设计】

【设计步骤】

【表关系】

【一对多】

【多对多】

【一对一】

---

【约束】 

【概述】：

• 约束是作用于表中列上的规则，用于限制加入表的数据
• 约束的存在保证了数据库中的数据的正确性、有效性和完整性

【分类】：

【注意】：MySQL不支持检查约束

【非空约束】

【概述】：

• 非空约束用于保证列中所有数据不能有NULL值

【语法】：

1、添加约束

-- 创建表时添加非空约束

CREATE TABLE 表名(

   列名 数据类型 NOT NULL,

   …

);

-- 建完表后添加非空约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

2、删除约束

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

【唯一约束】

【概述】：

• 唯一约束用于保证列中所有数据各不相同

【语法】：

1、添加约束

-- 创建表时添加唯一约束
CREATE TABLE 表名(
   列名 数据类型 UNIQUE [AUTO_INCREMENT],
   -- AUTO_INCREMENT: 当不指定值时自动增长
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [约束名称] UNIQUE(列名)
); 

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

2、删除约束

ALTER TABLE 表名 DROP INDEX 字段名;

【主键约束】

【概述】：

• 主键是一行数据的唯一标识，要求非空且唯一
• 一张表只能有一个主键

【语法】：

1、添加约束

-- 创建表时添加主键约束
CREATE TABLE 表名(
   列名 数据类型 PRIMARY KEY [AUTO_INCREMENT],
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
); 

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

2、删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

【默认约束】

【概述】：

• 保存数据时，未指定值则采取默认值

【语法】：

1、添加约束

-- 创建表时添加默认约束
CREATE TABLE 表名(
   列名 数据类型 DEFAULT 默认值,
   …
); 

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

2、删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

【约束——项目示例】

为表添加合适约束

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
  
);

【外键约束】

【概述】：

• 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

【语法】：

1、添加约束

-- 创建表时添加外键约束
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
); 

-- 建完表后添加外键约束
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

2、删除约束

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

例：

-- 部门表（主表）
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 emp drop FOREIGN key fk_emp_dept;

-- 建完表后，添加外键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

【数据库设计】

【概述】

• 数据库设计就是根据业务系统的具体需求，结合我们所选用的DBMS，为这个业务系统构造出最优的数据存储模型
• 建立数据库中的表结构以及表与表之间的关联关系的过程

【设计步骤】

1. 需求分析（数据是什么？数据具有哪些属性？数据与属性的特点是什么？）
2. 逻辑分析（通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统）
3. 物理设计（根据数据库自身的特点把逻辑设计转换为物理设计）
4. 维护设计（对表进行优化）

【表关系】

• 一对一：

例：1、用户和用户详情

2、一对一关系多用于表拆分，讲一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能

• 一对多(多对一)：

例：1、部门和员工

• 多对多：

例：1、商品和订单

【一对多】

【实现方法】

在多的一方建立外键，指向一的一方的主键

例：

-- 部门表（主表）
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)
		
);

【多对多】

【实现方法】

建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

例：

-- 订单表（从表）
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);

【一对一】

【实现方法】

在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)

例：

-- tb_user用户表（主表）
CREATE TABLE dept(
	id int primary key auto_increment,
	nickname varchar(20),
	age int,
    gender vachar(2),
    dep_id int UNIQUE,
    
    -- 添加外键 dep_id,关联 dept 表的id主键（在两表中任意一表中添加）
	CONSTRAINT fk_dept_emp FOREIGN KEY(dep_id) REFERENCES mep(id)
);
-- tb_user_desc用户详情表（从表）
CREATE TABLE emp(
	id int primary key auto_increment,
	city varchar(4),
	edu vachar(4),
	income int	
);