1. 为什么需要设计数据库 #

1.1 良好的数据库设计 #

1.2 糟糕的数据库设计 #

2. 软件项目开发周期中的数据库设计 #

现实世界->信息世界->数据库模型图->数据库

3.设计数据库的步骤 #

3.1 收集信息 #

与相关人员进行交流、访谈充分了解用户需求，理解数据库需要完成的任务

3.2 标示实体(Entity) #

标识数据库要管理的关键对象或者实体，实体一般是名词

3.3 标示实体的属性(Attribute) #

3.4 标示实体之间的关系(RelationShip) #

4. 数据库ER图 #

ER图：实体关系图，简记E-R图，是指以实体、关系、属性三个基本概念概括数据的基本结构，从而描述静态数据结构的概念模式

er

4.1 ER图的实体（entity） #

4.2 ER图的属性（attribute） #

4.3 ER图的关系（relationship） #

4.4 ER图中关联关系 #

4.4.1 1对1 (1:1) #

1对1关系是指对于实体集A与实体集B，A中的每一个实体至多与B中一个实体有关系；反之，在实体集B中的每个实体至多与实体集A中一个实体有关系。

er

4.4.2 1对多（1:N） #

1对多关系是指实体集A与实体集B中至少有N(N>0)个实体有关系；并且实体集B中每一个实体至多与实体集A中一个实体有关系。

er

4.4.3 多对多（M:N） #

多对多关系是指实体集A中的每一个实体与实体集B中至少有M(M>0)个实体有关系，并且实体集B中的每一个实体与实体集A中的至少N（N>0）个实体有关系。

er

5. 数据库设计三大范式 #

5.1 不合理的表设计 #

5.2 三大范式 #

5.2.1 第一范式（1NF） #

firstpattern

5.2.2 第二范式（2NF） #

2th1

2th2

2th3

一个人同时订几个房间，就会出来一个订单号多条数据，这样子联系人都是重复的，就会造成数据冗余

5.2.3 第三范式（3NF） #

thirdpattern

5.2.4 如何更好的区分三大范式 #

5.RBAC #

5.2 ER图 #

er

5.2.1 用户表 #

字段 字段名 类型 默认
id ID int(11)
username 用户名 varchar(255)
password 密码 varchar(255)
email 邮箱 varchar(255)
phone 手机号 varchar(255)
gender 性别 tinyint
birthday 生日 datetime
addresss 地址 varchar(255)
create_time 创建时间 datetime CURRENT_TIMESTAMP
last_login 上次登录时间 datetime
status 状态 tinyint 1

5.2.2 角色表 #

字段 字段名 类型 默认
id ID int(11)
name 名称 varchar(255)
create_time 创建时间 datetime CURRENT_TIMESTAMP
status 状态 tinyint 1

5.2.3 资源表 #

字段 字段名 类型 默认
id ID int(11)
name 名称 varchar(255)
key 路径 varchar(255)
create_time 创建时间 datetime CURRENT_TIMESTAMP
status 状态 tinyint 1

5.2.4 用户角色表 #

字段 字段名 类型
user_id 用户ID int(11)
role_id 角色ID int(11)

5.2.5 角色资源 #

字段 字段名 类型
role_id 角色ID int(11)
resource_id 资源ID int(11)

5.3 数据库脚本 # 

ALTER TABLE `categories` DROP FOREIGN KEY `fk_category_parent_id`;
ALTER TABLE `articles` DROP FOREIGN KEY `fk_article_category_id`;
ALTER TABLE `user_role` DROP FOREIGN KEY `fk_user_role_user_id`;
ALTER TABLE `user_role` DROP FOREIGN KEY `fk_user_role_role_id`;
ALTER TABLE `role_resource` DROP FOREIGN KEY `fk_role_resource_role_id`;
ALTER TABLE `role_resource` DROP FOREIGN KEY `fk_role_resource_resource_id`;

DROP TABLE `users`;
DROP TABLE `carousels`;
DROP TABLE `categories`;
DROP TABLE `articles`;
DROP TABLE `navigations`;
DROP TABLE `links`;
DROP TABLE `config`;
DROP TABLE `roles`;
DROP TABLE `user_role`;
DROP TABLE `resources`;
DROP TABLE `role_resource`;

CREATE TABLE `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(255) NULL,
`password` varchar(255) NULL,
`last_login` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
`email` varchar(255) NULL,
`phone` varchar(255) NULL,
`gender` tinyint(255) NULL,
`birthday` datetime NULL,
`address` varchar(255) NULL,
PRIMARY KEY (`id`) 
);
CREATE TABLE `carousels` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NULL,
`url` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
PRIMARY KEY (`id`) 
);
CREATE TABLE `categories` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NULL,
`description` varchar(255) NULL,
`keywords` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
`parent_id` int(11) NULL,
PRIMARY KEY (`id`) 
);
CREATE TABLE `articles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`category_id` int(11) NULL,
`title` varchar(255) NULL,
`content` text NULL,
`user_id` int(255) NULL,
`keywords` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
`pv` int(255) NULL,
PRIMARY KEY (`id`) 
);
CREATE TABLE `navigations` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NULL,
`url` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
PRIMARY KEY (`id`) 
);
CREATE TABLE `links` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NULL,
`logo` varchar(255) NULL,
`url` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
PRIMARY KEY (`id`) 
);
CREATE TABLE `config` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
`logo` varchar(255) NULL,
`url` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
`keywords` varchar(255) NULL,
`description` varchar(255) NULL,
`icp` varchar(255) NULL,
`about` varchar(255) NULL,
PRIMARY KEY (`id`) 
);
CREATE TABLE `roles` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
PRIMARY KEY (`id`) 
);
CREATE TABLE `user_role` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`role_id` int(255) NOT NULL,
PRIMARY KEY (`user_id`, `role_id`) 
);
CREATE TABLE `resources` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`key` varchar(255) NULL,
`create_time` datetime NULL DEFAULT CURRENT_TIMESTAMP,
`status` tinyint(255) NULL DEFAULT 1,
PRIMARY KEY (`id`) 
);
CREATE TABLE `role_resource` (
`role_id` int(11) NOT NULL AUTO_INCREMENT,
`resource_id` int(255) NOT NULL,
PRIMARY KEY (`role_id`, `resource_id`) 
);

ALTER TABLE `categories` ADD CONSTRAINT `fk_category_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `categories` (`id`);
ALTER TABLE `articles` ADD CONSTRAINT `fk_article_category_id` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`);
ALTER TABLE `user_role` ADD CONSTRAINT `fk_user_role_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`);
ALTER TABLE `user_role` ADD CONSTRAINT `fk_user_role_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`);
ALTER TABLE `role_resource` ADD CONSTRAINT `fk_role_resource_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`);
ALTER TABLE `role_resource` ADD CONSTRAINT `fk_role_resource_resource_id` FOREIGN KEY (`resource_id`) REFERENCES `resources` (`id`);

6.事务 #

6.1 为什么需要事务 # 

create database bank;
use bank;
create table account
(
    name varchar(64),
    balance decimal(10,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into account(name,balance) values('张三',100);
insert into account(name,balance) values('李四',100);

update account set balance = balance - 10 where name = '张三';
update account set balance = balance + 10 where name = '李四';

6.2 什么是事务 #

转账过程就是一个整体，它需要两条UPDATE语句，如果任何一个出错，则整个转账业务取消，两个账户的余额都恢复到原来的数据，确保总余额不变

6.3 事务的特性 #

6.4 如何创建事务 # 

BEGIN;
update account set balance = balance - 10 where name = '张三';
ROLLBACK;

关闭自动提交后，从下一条SQL语句开始开启新的事务，需要使用COMMIT或ROLLBACK结束该事务

7.在node中使用mysql # 

npm install mysql

7.1 使用mysql # 

var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'me',
  password : 'secret',
  database : 'my_db'
});

connection.connect();

connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

connection.end();

7.2 使用mysql连接池 # 

var mysql = require('mysql');
var pool  = mysql.createPool({
  connectionLimit : 10,
  host            : 'example.org',
  user            : 'bob',
  password        : 'secret',
  database        : 'my_db'
});

pool.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

7.3 使用mysql事务 # 

connection.beginTransaction(function(err) {
  if (err) { throw err; }
  connection.query('INSERT INTO posts SET title=?', title, function (error, results, fields) {
    if (error) {
      return connection.rollback(function() {
        throw error;
      });
    }

    var log = 'Post ' + results.insertId + ' added';

    connection.query('INSERT INTO log SET data=?', log, function (error, results, fields) {
      if (error) {
        return connection.rollback(function() {
          throw error;
        });
      }
      connection.commit(function(err) {
        if (err) {
          return connection.rollback(function() {
            throw err;
          });
        }
        console.log('success!');
      });
    });
  });
});

8.锁 #

8.1 锁的分类 #

8.2 表锁 #

8.2.1 准备数据 # 

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `NAME` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `users` (`id`, `NAME`) VALUES ('1', 'a');
INSERT INTO `users` (`id`, `NAME`) VALUES ('2', 'b');
INSERT INTO `users` (`id`, `NAME`) VALUES ('3', 'c');
INSERT INTO `users` (`id`, `NAME`) VALUES ('4', 'd');


lock table 表名称 read(write),表名称2 read(write);
unlock tables;

8.2.2 加读锁 #

会话1 会话2 说明
lock table users read; 连接数据库
select * from users; select * from users; 都可以查询
select * from role; select * from role; 会话1不能查询没有锁定的表，会话2可以查询没有锁定的表或更新未锁定的表
INSERT INTO users (id, NAME) VALUES ('5', 'e'); INSERT INTO users (id, NAME) VALUES ('5', 'e'); 会话1插入或更新会提示错误，会话2插入或更新会提示等待
unlock tables; INSERT INTO users (id, NAME) VALUES ('5', 'e'); 会话1释放锁后，会话2完成插入操作

8.2.3 加写锁 #

会话1 会话2 说明
lock table users write; 连接数据库
select * from users; select * from users; 会话1可以对锁定表进行插入更新和删除操作,会话2的查询操作也会被阻塞
unlock tables; 会话1释放锁，会话2返回结果

读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都阻塞

8.3 行锁 #

8.3.1 行锁支持事务 #

8.3.1.1 并发事务处理带来的问题 #
8.3.1.1.1 更新丢失（Lost Update） # 
create table account3 (id int,balance int) ENGINE=InnoDB;
insert into account3(id,balance) values(1,100);
insert into account3(id,balance) values(2,100);

set autocommit=0;
begin;
update account3 set balance=90 where id=1;
commit;

set autocommit=0;
begin;
update account3 set balance=80 where id=1;
commit;

　　

8.3.1.1.2 脏读（Dirty Reads） #

修改日志的隔离级别

-- 支持的存储引擎
SHOW ENGINES;
-- 当前的数据库默认引擎
SHOW VARIABLES LIKE 'storage_engine';
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

start TRANSACTION;
insert into account3(id,balance) values(3,100);

start TRANSACTION;
select * from account3;
8.3.1.1.3 不可重复读（Non-Repeatable Reads） #

修改日志级别

SET SESSION TRANSACTION ISOLATION LEVEL read committed;
SET GLOBAL TRANSACTION ISOLATION LEVEL read committed;
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

会话2

start TRANSACTION;
select * from account3;

会话2

start TRANSACTION;
delete from account3 where id=1;
commit;
8.3.1.1.4 幻读（Phantom Reads） # 
SELECT @@tx_isolation;
SET SESSION TRANSACTION ISOLATION LEVEL repeatable read;
SET GLOBAL TRANSACTION ISOLATION LEVEL repeatable read;
SELECT @@global.tx_isolation;
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;

start TRANSACTION;
insert into account3(id,balance) values(5,100);
commit;

start TRANSACTION;
select * from account3;
update account3 set balance =balance-10;
commit;
8.3.1.2 事务隔离五种级别 # 
SELECT @@tx_isolation;
set session transaction isolation level
set session transaction isolation level read uncommitted;
隔离级别 脏读 非重复读 幻读
read uncommitted 允许 允许 允许
read committed 允许 允许
repeatable read 允许
serializable
8.3.2 死锁 #
会话1 会话2 说明
set autocommit=0 set autocommit=0 都关闭默认提交
update account set balance = balance -10 where name = '张三' update account2 set balance = balance -10 where name = '李四'
update account2 set balance = balance -10 where name = '李四'; update account2 set balance = balance -10 where name = '张三';
Powered by idoc. Dependence Node.js run.