现实世界->信息世界->数据库模型图->数据库
与相关人员进行交流、访谈充分了解用户需求,理解数据库需要完成的任务
标识数据库要管理的关键对象或者实体,实体一般是名词
ER图:实体关系图,简记E-R图,是指以实体、关系、属性三个基本概念概括数据的基本结构,从而描述静态数据结构的概念模式
1对1关系是指对于实体集A与实体集B,A中的每一个实体至多与B中一个实体有关系;反之,在实体集B中的每个实体至多与实体集A中一个实体有关系。
1对多关系是指实体集A与实体集B中至少有N(N>0)个实体有关系;并且实体集B中每一个实体至多与实体集A中一个实体有关系。
多对多关系是指实体集A中的每一个实体与实体集B中至少有M(M>0)个实体有关系,并且实体集B中的每一个实体与实体集A中的至少N(N>0)个实体有关系。
一个人同时订几个房间,就会出来一个订单号多条数据,这样子联系人都是重复的,就会造成数据冗余
字段 | 字段名 | 类型 | 默认 |
---|---|---|---|
id | ID | int(11) | |
username | 用户名 | varchar(255) | |
password | 密码 | varchar(255) | |
邮箱 | varchar(255) | ||
phone | 手机号 | varchar(255) | |
gender | 性别 | tinyint | |
birthday | 生日 | datetime | |
addresss | 地址 | varchar(255) | |
create_time | 创建时间 | datetime | CURRENT_TIMESTAMP |
last_login | 上次登录时间 | datetime | |
status | 状态 | tinyint | 1 |
字段 | 字段名 | 类型 | 默认 |
---|---|---|---|
id | ID | int(11) | |
name | 名称 | varchar(255) | |
create_time | 创建时间 | datetime | CURRENT_TIMESTAMP |
status | 状态 | tinyint | 1 |
字段 | 字段名 | 类型 | 默认 | |
---|---|---|---|---|
id | ID | int(11) | ||
name | 名称 | varchar(255) | ||
key | 路径 | varchar(255) | ||
create_time | 创建时间 | datetime | CURRENT_TIMESTAMP | |
status | 状态 | tinyint | 1 |
字段 | 字段名 | 类型 |
---|---|---|
user_id | 用户ID | int(11) |
role_id | 角色ID | int(11) |
字段 | 字段名 | 类型 |
---|---|---|
role_id | 角色ID | int(11) |
resource_id | 资源ID | int(11) |
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`);
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 = '李四';
转账过程就是一个整体,它需要两条UPDATE语句,如果任何一个出错,则整个转账业务取消,两个账户的余额都恢复到原来的数据,确保总余额不变
START TRANSACTION
或者 BEGIN
COMMIT
ROLLBACK
BEGIN;
update account set balance = balance - 10 where name = '张三';
ROLLBACK;
SET autocommit=0|1
0关闭 1开启关闭自动提交后,从下一条SQL语句开始开启新的事务,需要使用COMMIT或ROLLBACK结束该事务
npm install 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();
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);
});
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!');
});
});
});
});
MyISAM
存储引擎,开销小,加锁快,锁定粒度大,发生锁冲突的概率最高,并发度最低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;
会话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完成插入操作 |
会话1 | 会话2 | 说明 |
---|---|---|
lock table users write; | 连接数据库 | |
select * from users; | select * from users; | 会话1可以对锁定表进行插入更新和删除操作,会话2的查询操作也会被阻塞 |
unlock tables; | 会话1释放锁,会话2返回结果 |
读锁会阻塞写,但是不会阻塞读。而写锁则会把读和写都阻塞
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;
脏
数据,并据此作进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象的叫做脏读
。 修改日志的隔离级别
-- 支持的存储引擎
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;
修改日志级别
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;
insert
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;
repeatable read
SELECT @@tx_isolation;
set session transaction isolation level
set session transaction isolation level read uncommitted;
隔离级别 | 脏读 | 非重复读 | 幻读 |
---|---|---|---|
read uncommitted | 允许 | 允许 | 允许 |
read committed | 允许 | 允许 | |
repeatable read | 允许 | ||
serializable |
会话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 = '张三'; |