本文详细介绍了数据库设计的过程，包括需求分析、逻辑分析、物理设计和维护设计四个步骤。讲解了一对一、一对多和多对多的关系实现，并提供了具体的SQL示例。此外，还深入探讨了多表查询，包括内连接、外连接和子查询的用法，以及事务处理的重要性。最后，通过转账操作的例子展示了事务的原子性和一致性。
数据库得设计
数据库设计得概念：
数据库得设计就是根据业务得具体需求，结合我们所选用得DBMS，为这个业务系统构造出最有得数据存储模型，建立数据库中表结构以及表与表之间得关联关系得过程
简单来说，就是需要那些表，表里边有那些字段，表和表之间是什么关系
数据库设计得步骤
1.需求分析(数据是什么？数据具有那些属性？数据与属性得特点是什么)
2.逻辑分析(通过ER图(实体图)对数据库进行逻辑建模，不需要考虑我们所选用得数据库管理系统)
3.物理设计(根据数据库自身得特点把逻辑设计转化为物理设计)
4.维护设计(1.对新得需求进行见表2.表优化)
表关系
一对一
例如：人和身份证
一对一得关系多用于表拆分，将一个实体中经常使用得字段放一张表，不经常使用得字段放入另一张表，用于提升查询性能
实现：
在任意一方加入外键，关联另一方主键，并设置外键为唯一(UNIQUE)
一对多(多对一)
例如：一个班级多个学生 多个学生对应一个班级
实现方式：在多的一方建立外键，指向一的一方的主键
多对对
例如：商品和订单 一个商品对应多个订单 一个订单对应多个商品
实现方式：建立第三章中间表，中间表至少包含两个外键，分别关联两方主键
/*
多对多：
* 如：订单 和 商品
* 一个商品对应多个订单，一个订单包含多个商品
实现方式：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
*/
-- 删除表
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_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 FOREIGN KEY (order_id) REFERENCES tb_order(id);
ALTER TABLE tb_order_goods ADD CONSTRAINT fk_goods FOREIGN KEY (goods_id) REFERENCES tb_goods(id);
多表查询
-笛卡尔积：取A，B集合所有组合情况
多表查询：
从多张表查询数据
连接查询
内连接：相当于查询AB交集数据
外连接：
左外连接：相当于查询A表所有数据和交集部分数据
右外连接：相当于查询B表所有数据和交集部分数据
子查询
多表查询示例：
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
# 创建部门表
CREATE TABLE dept(
did INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(20)
);
# 创建员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dep_id INT,
FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
);
-- 添加部门数据
INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
-- 添加员工数据
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),
('sx','男',2500,'2011-02-14',null);
select * from emp;
-- 多表查询
select * from emp , dept;
-- 会查询出24条数据
-- 笛卡尔积 ： 有AB两个集合 ；去两个集合所有元素两两组合的情况
-- 需要取出无效数据，emp.dep.id=dept.did
select * from emp , dept WHERE emp.dep_id =dept.did; 内连接：
1.内连接查询语法
--隐式内连接
SELECT 字段列表 FROM 表1,表2.. where 条件;
--显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;
内连接相当于查询AB交集数据
-- 内连接
-- 隐式内连接
SELECT
emp.NAME,
emp.salary,
dept.dname
FROM
emp,
dept
WHERE
emp.dep_id = dept.did;
-- 显式内连接
SELECT * from emp INNER JOIN dept ON emp.dep_id=dept.did;
外连接：
1.外连接查询语法
--左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;
--右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;
左外连接：相当于查询A表所有数据和交集部分数据
右外连接：相当于查询B表所有数据和交集部分数据
-- 左外连接
SELECT * FROM emp left JOIN dept on emp.dep_id = dept.did;
-- 右外连接
SELECT * FROM emp RIGHT JOIN dept on emp.dep_id = dept.did;
子查询
子查询概念：查询中嵌套查询，称嵌套查询为子查询
子查询根据查询结果不同，作用不同：
单行单列
单行单列：作为条件，使用= != > <等进行条件判断
SELECT 字段列表 FROM 表 WHERE 字段名 =(子查询);
-- 子查询
-- 工资大于李四的
-- 1.查询李四的工资
SELECT salary FROM emp where name ='李四';
-- 2.大于3600的信息
SELECT * from emp WHERE salary > 3600;
SELECT * from emp WHERE salary > (SELECT salary FROM emp where name ='李四');
多行单列
多行单列：作为条件值使用in等关键字进行t条件判断
SELECT 字段列表 FROM WHERE 字段名 IN (子查询)
-- 查询财务部和市场部所有员工信息
-- 查询财务部的所有员工信息
SELECT did from dept where dname = '财务部' OR dname = '市场部';
SELECT * FROM emp WHERE dep_id in(SELECT did from dept where dname = '财务部' OR dname = '市场部');
多行多列
多行多列：作为虚拟表
SELECT 字段列表 FROM (子查询) WHERE 条件;
-- 查询入职日期是'2011-11-11'之后的员工信息和部门信息
-- 员工和部门的信息
SELECT * from emp LEFT JOIN dept ON emp.dep_id =dept.did;
-- 查询员工入职大于 2011-11-11
SELECT * from emp where join_date>'2011-11-11';
SELECT * from (SELECT * from emp where join_date>'2011-11-11') emp_new LEFT JOIN dept ON emp_new.dep_id =dept.did;
事务
事务简介
数据库的事务是一种机制，一个操作序列，包含了一组数据库操作命令
事务把所有命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么同时成功，要么同时失败
事务是一个不可分割的工作逻辑单元
开启事务
START TRANSACTION 或者 BEGIN；
提交事务
COMMIT;
回滚事务
ROLLBACK;
案例：张三找李四借500
DROP TABLE IF EXISTS account;
-- 创建账户表
CREATE TABLE account(
id int PRIMARY KEY auto_increment,
name varchar(10),
money double(10,2)
);
-- 添加数据
INSERT INTO account(name,money) values('张三',1000),('李四',1000);
UPDATE account set money = 1000;
select * from account;
-- 错误演示
-- 转帐操作 要么都成功 要么都失败
-- 1.查询李四账余额是否大于500
-- 2.李四-500
UPDATE account SET money=money-500 WHERE name ='李四';
如果中间有异常 则下面sql不执行了 李四减去500 而张三没有+500 500就不见了
-- 3.张三+500
UPDATE account SET money=money+500 WHERE name ='张三';
-- 正确写法
-- 转帐操作 要么都成功 要么都失败
-- 开启事务
BEGIN;
-- 1.查询李四账余额是否大于500
-- 2.李四-500
UPDATE account SET money=money-500 WHERE name ='李四';
如果中间有异常 则下面sql不执行了 李四减去500 而张三没有+500 500就不见了
-- 3.张三+500
UPDATE account SET money=money+500 WHERE name ='张三';
-- 回滚事务
ROLLBACK;
-- 提交事务
COMMIT;
事务的四大特征：
原子型(Atomicity)：事务是不可分割的最小操作单元，要么同时成功，要么同时失败
一致性(CONsistency):事务完成时,必须使所有的数据都保持一致状态
隔离性(Islation)：多个事务之间，操作的可见性
持久性(Durability)：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
MYSQl事务默认自动提交
-- 查看事务的默认提交方式
SELECT @@autocommit;
-- 1 自动提交 0 手动提交
-- 修改事务提交
set @@autocommit=0;
638
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
