MySQL--基础知识点（二）

约束

约束的概念和分类

1.约束的概念

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

2.约束的分类

约束名称	描述	关键字
非空约束	保证列中所有数据不能有null值	NOT NULL
唯一约束	保证列中所有数据各不相同	UNIQUE
主键约束	主键是一行数据的唯一标识，要求非空切唯一	PRIMARY KEY
检查约束	保证列中的值满足某一条件	CHECK
默认约束	保存数据时，未指定值则采用默认值	DEFAULT
外键约束	外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性	FOREIGN KEY

Tips：MySQL不支持检查约束

3.案列

例题：

-- 员工表
CREATE TABLE emp(
	id INT,		-- 员工id,主键且只增长
    ename VARCAHR(50),	--员工姓名， 非空并且唯一
    joindata DATE,		--入职日期， 非空
    salary DOUBLE(7,2),	--工资， 非空
    bonus DOUBLE(7,2)	--奖金， 如果没有奖金默认为0
);

答案：

-- 员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
    ename VARCAHR(50) NOT NULL UNIQUE,
    joindata DATE NOT NULL,
    salary DOUBLE(7,2) NOT NULL,
    bonus DOUBLE(7,2) DEFAULT 0
);

4.外键约束

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

语法：

​ 添加约束

– 创建表时添加外键约束

CREATE TABLE 表名(

​ 列名 数据类型，

​ …

​ [CONSTRAINT] [外键名称] FOREIGN KER(外键列名) REFERENCES 主表(主表列名)

);

– 建完表后添加外键约束

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

​ 删除外键

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

数据库设计

简介

1.数据库设计概念

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

2.数据库设计的步骤

​ ①需求分析(数据是什么？数据具有哪些属性？数据与属性的特点是什么)

​ ②逻辑分析(通过ER图对数据库进行逻辑建模，不需要考虑我们所选用的数据库管理系统)

​ ③物理设计(根据数据库自身的特点把逻辑设计转换为物理设计)

​ ④维护设计(1.对新的需求进行建表；2.表优化)

3.表关系

• 一对一

  • 如：用户 和 用户详情

• 一对多(多对一)

  • 如：部门 和 员工

    一个部门对应多个员工，一个员工对应一个部门

• 多对多

  • 如：商品 和 订单

    一个商品对应多个订单，一个订单包含多个商品

多表关系实现

表关系之一对多

• 一对多(多对一)

  • 如：部门 和 员工

    一个部门对应多个员工，一个员工对应一个部门

• 实现方式：在多个一方建立外键，指向一的一方的主键

表关系之多对多

• 多对多

  • 如：商品 和 订单

    一个商品对应多个订单，一个订单包含多个商品

• 实现方式：建立第三章中间表，中间表至少包含两个外键，分别关联两方主键

表关系之一对一

• 一对一

  • 如：用户 和 用户详情
  • 一对一关系多用于表拆分，将一个实体中经常使用的字段方一张表，不经常使用的字段放另一张表，用于提升查询性能

• 实现方式：任意一方加入外键，关联另一方的主键，并且设置外键为唯一(UNIQUE)

多表查询

建表

-- 删除之前的表 
DROP TABLE emp;
DROP TABLE dept;
 
 -- 部门表
CREATE TABLE dept(
	did INT PRIMARY KEY AUTO_INCREMENT,
	dname VARCHAR(20)
);

-- 员工表
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(20),
	gender CHAR(1),
	salary DOUBLE,
	join_date DATE,
	dep_id INT,
	-- 添加外键dep_id ,关联dept表的id主键
 FOREIGN KEY (dep_id) REFERENCES dept(did)
);

-- 添加部门
INSERT INTO dept(dname) VALUES
('研发部'),('市场部'),('财务部'),('销售部');
 
-- 添加员工，dep_id 表示员工所在的部门
 INSERT INTO emp(name,gender,salary,join_date,dep_id) VALUES
 ('孙悟空','男',7200,'2013-02-24',1),
 ('猪八戒','男',3600,'2010-12-02',2),
 ('唐僧','男',9000,'2008-08-08',2),
 ('白骨精','女',5000,'2015-10-07',3),
 ('蜘蛛精','女',4500,'2011-03-14',1),
 ('小龙马','男',2500,'2011-02-14',null);

简介

• 多表查询：从多张表查询数据
  • 连接查询
    • 内连接：相当于查询A、B交集数据
    • 外连接：
      • 左外连接：相当于查询A表所有数据和交集部分数据
      • 右外连接：相当于查询B表左右数据和交集部分数据
  • 子查询

内连接

1.内连接查询语法

– 隐式内连接

SELECT 字段列表 FROM 表1,表2,… WHERE 条件;

– 显示内连接

SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件

内连接：相当于查询A、B交集数据

外连接

1.外连接查询语法

– 左外连接

SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

– 右外连接

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

• 左外连接：相当于查询A表所有数据和交集部分数据
• 右外连接：相当于查询B表左右数据和交集部分数据

子查询

1.子查询概念：

• 查询中嵌套查询，称嵌套查询为子查询

2.子查询根据查询结果不同，作用不同：

• 单行单列：作为条件值，使用 = != > < 等进行条件判断

  SELECT 字段列表 FROM 表 WHERE 字段名 =(子查询);

• 多行单列：作为条件值，使用in等关键字进行条件判断

  SELECT 字段 M 表 WHERE 字段名 IN (子查询);

• 多行多列：作为虚拟表

  SELECT 字段列表 FROM (子查询) WHERE 条件;

事务

事务简介

• 数据库的事务是一种机制、一个操作序列，包含了一组数据库操作命令
• 事务把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据命令要么同时成功，要么同时失败
• 事务是一个不可分割的工作逻辑单元

事务操作

-- 开启事务
START TRANSACTION;
-- 或
BEGIN;
-- 提交事务
COMMIT;
-- 回滚事务
ROLLBACK;

事务四大特征

• 原子性(Atomicity)：事务是不可分割的最小操作单元，要么同时成功，要么通过是失败
• 一致性(Consistency)：事务完成时，必须使所有的数据都保持一致状态
• 隔离性(Isolation)：多个事务之间，操作的可见性
• 持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的