Skip to content

Latest commit

 

History

History
925 lines (817 loc) · 16 KB

14. 常用数据库的介绍与使用.md

File metadata and controls

925 lines (817 loc) · 16 KB
Raw

Node.js Web开发 数据库

⭐ by calidion

数据库的作用

  1. 持久化数据
  2. CRUD
  3. 数据分析

RDB VS noSQL

  1. RDB（关系数据库）

基于E.F.Codd的关系数据库理论的数据库

范式理论，要求数据库设计至少达到第三范式（3NF）。

代表数据库 Oracle, Mysql, MS SQL Server, PostgreSQL

  1. noSQL（ Not Only SQL）

当某一类数据的体量越来越大，使用传统的关系数据库处理时表现出来性能上的失效。 这个时候使用noSQL可以有效的避免关系数据库所带来的问题。 主要是基于KV对数据进行检索，内容形式多样，有文档，有值文本，有图(Graph)，有对象(Object),有XML等。

常见的noSQL： MongoDB, Redis，Hbase

RDB VS NOSQL

noSQL并不是与SQL相互取代的产品。 他们面向的数据通常应该是不同的。 虽然Mongodb有意义模糊了这一个界线。

数据类型 RDB NOSQL
关系数据 Y N
日志数据 N Y
线性数据 N Y
读取快 Y Y
写入快 N Y

Mysql

开源数据库，同时提供商业版本 基于GPL与商业授权两种

https://www.mysql.com/downloads/

安装

Ubuntu:

sudo apt-get install mysql-server

同时指定root密码

登录数据库服务器

  • 命令行
mysql -u root -p
  • 客户端： MYSQL Workbench

数据库操作

  • 创建数据库
create database test charset=utf8;
  • 使用数据库
use test
  • 基中test是你要创建的数据库名
drop database test;

显示数据库

show databases;

示例

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
7 rows in set (0.00 sec)

数据类型

主要有三类：1.数值 2.时间 3. 字符

  1. 数值 a. INT， TINYINT， SMALLINT， MEDIUMINT，BIGINT b. FLOAT，DOUBLE，DECIMAL
  2. 时间 a. DATE，DATETIME，TIMESTAMP，TIME，YEAR
  1. 字符 a. CHAR，VARCHAR，TEXT，BLOB，TINYTEXT，TINYBLOB，MEDIUMTEXT，MEDIUMBLOB，LONGTEXT，LONGBLOB，ENUM

表格操作

  • 创建
create table user(
   id INT NOT NULL AUTO_INCREMENT,
   username VARCHAR(20) NOT NULL,
   password VARCHAR(64) NOT NULL,
   createdAt DATETIME,
   PRIMARY KEY ( id )
);
  • 删除
drop table user;

表格字段

  1. 添加
alter table user add login_count int set default 1000;
  1. 修改类型
alter table user modify login_count bigint;
  1. 修改名称与类型
ALTER TABLE testalter_tbl CHANGE login_count i INT;
  1. 删除
alter table user drop login_count;

表格数据操作

  • 插入
 insert into user(username, password) 
 values ('username', 'password');
  • 更新
update user set username="username1", 
password="password1" where id = 1;
  • 删除
delete from user;
delete from user where id = 1;
  • 查询
  1. 查询全部
select * from user;
  1. 查询一个或者多个
select * from user where id = 1;
  1. 模糊查询
select * from user where username like "user%";
  1. 分页查询
select * from user limit 1, 10

其中1是表的offset, 10是limit

  1. 按顺序排列

  2. 从小到大

select * from user order by id asc;
  1. 从大到小
select * from user order by id desc;

数据库备份

格式：

mysqldump -h hostname -u username databasename 
> /path-to-backup/bakname.sql -p

实例：

mysqldump -h localhost -u root test > test.sql -p

数据库恢复

格式:

mysql -h hostname -u username databasename 
< /path-to-backup/bakname.sql -p

实例：

mysql -h localhost -u root test < test.sql -p

Mongodb

基于文档的noSQL数据库 试图模糊关系数据库与非关系数据库的关系 非关系数据库里最象关系数据库的产品

官网：https://www.mongodb.com/

安装

sudo apt-get install mongodb-server

登录服务器

$> mongo
MongoDB shell version: 2.6.10
connecting to: test
Welcome to the MongoDB shell.
For interactive help, type "help".
For more comprehensive documentation, see
	http://docs.mongodb.org/
Questions? Try the support group
	http://groups.google.com/group/mongodb-user
>

与SQL的术语对比

SQL Mongodb 说明
database database 数据库
table collection 表=>集合
row field 行 => 字段
join 无 不支持join

数据库操作

  1. 查看
show dbs;
  1. 创建
use dbname;

同时需要插入一些数据

db.dbname.insert({});
  1. 删除
use dbname;
db.dropDatabase()

保留的数据库名

  1. admin 用户权限数据库

  2. local 用于记录复制信息，不是真正的mongodb数据库，因为不会被复制到其它主机

  3. config 集群数据库的配置信息

集合操作

  1. 查看
show collections
show tables;
db.getCollectionNames()
  1. 插入
db.createCollection("hello")
  1. 删除
db.hello.drop()

文档操作

  1. 插入
db.hello.insert({a: 1})
db.hello.save({a: 1})
  1. 查看所有
db.hello.find()
  1. 更新

格式：

db.collection.update(
   <query>,
   <update>,
   {
     upsert: <boolean>,
     multi: <boolean>,
     writeConcern: <document>
   }
)

说明

  1. query 查询条件

  2. update 更新内容

  3. upsert 是不是需要在没有找到查询对象时插入

  4. multi 是否更新多条，默认false

  5. writeConcern 抛出异常的级别

更新文档

  1. 更新一行
db.hello.update({a: 1}, {$set: {a: 100}})
db.hello.update({a: 1}, {$inc: {a: 5}}, {multi: true})
  1. 更新多行
db.hello.update({a: 2}, {$set: {a: 101}}, {multi: true})
  1. 通过_id更新
db.hello.save({_id: ObjectId("5a1d7fda34947b8f040ac10c"), a: 1})

删除文档

db.collection.remove(
   <query>,
   {
     justOne: <boolean>,
     writeConcern: <document>
   }
)

示例 删除

db.hello.remove({a: 1})

清空集合

db.hello.remove({})

说明:

  1. query 删除的文档的条件
  2. justOne 只删除一个文档
  3. writeConcern 抛出异常的级别

常用查询操作符

  1. $gt(great than) 大于(>)
  2. $lt(less than) 小于(<)
  3. $gte(greatt than or equal) 大于等于(>=)
  4. $lte(less than or equal) 小于等于(<=)
  1. $eq (equal) 相等(=)
  2. $neq (not equal) 不相等(!=)

详情 https://docs.mongodb.com/manual/reference/operator/query/

Mongodb的数据类型

数据类型 描述
String 字符串(UTF-8)
Integer 整型数值
Boolean 布尔值
Double 双精度浮点值
Min/Max keys 将一个值与 BSON（二进制的 JSON）元素的最低值和最高值相对比
Array 用于将数组或列表或多个值存储为一个键。
数据类型 描述
Timestamp 时间戳
Object 用于内嵌文档。
Null 用于创建空值。
Symbol 符号
Date 日期时间
Object ID 对象 ID
Binary Data 二进制数据
Code 代码类型
Regular expression 正则表达式类型

分页

db.hello.find().skip(0).limit(100);

skip是offset limit是数据量

排序

db.hello.find().order({a: -1});

order函数用于排序，参数

  • 1 表示升序
  • -1 表示降序

备份

mongodump -h dbhost -d dbname -o dbdirectory
  1. -h MongDB所在服务器地址，例如：127.0.0.1，当然也可以指定端口号：127.0.0.1:27017
  2. -d 需要备份的数据库实例，例如：test
  3. -o 备份的数据存放位置，例如：c:\data\dump，当然该目录需要提前建立，在备份完成后，系统自动在dump目录下建立一个test目录，这个目录里面存放该数据库实例的备份数据。

恢复

mongorestore -h <hostname><:port> -d dbname <path>
  1. --host <:port>, -h <:port>： MongoDB所在服务器地址，默认为： localhost:27017
  2. --db , -d ： 需要恢复的数据库实例，例如：test，当然这个名称也可以和备份时候的不一样，比如test2
  1. --drop： 恢复的时候，先删除当前数据，然后恢复备份的数据。就是说，恢复后，备份后添加修改的数据都会被删除，慎用哦！
  2. <path>： mongorestore 最后的一个参数，设置备份数据所在位置，例如：c:\data\dump\test。 你不能同时指定 和 --dir 选项，--dir也可以设置备份目录。
  3. --dir： 指定备份的目录 你不能同时指定 和 --dir 选项。

Node.js的Mysql开发

Mysql数据可以使用很多种方案进行开发， 最基本的就是基于mysql包进行开发

  • 安装
npm install mysql

准备工作

  • 创建数据库用户
grant all on *.* to username identified by 'password';
  • 赋预足够的权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%' WITH GRANT OPTION;
  • 设置环境变量
export MYSQL_HOST=localhost
export MYSQL_USERNAME=username
export MYSQL_PASSWORD=password

连接数据库

var mysql = require('mysql');
var con = mysql.createConnection({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USERNAME,
  password: process.env.MYSQL_PASSWORD
});
con.connect(function(err) {
  if (err) throw err;
  console.log("Connected!");
});

创建数据库

  con.query("CREATE DATABASE mydb", function (err, result) {
    if (err) throw err;
    console.log("Database created");
  });

创建表格

  var sql = "CREATE TABLE user (username VARCHAR(255), password VARCHAR(255))";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("Table created");
  });

插入数据

  • 插入一个数据
  var sql = "INSERT INTO user (username, password) VALUES('username', 'password');";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log("1 record inserted");
  });
  • 插入多个数据
  var sql = "INSERT INTO user (username, password) VALUES ?;";
  var data = [
    ['John', 'Highway 71'],
    ['Peter', 'Lowstreet 4'],
    ['Amy', 'Apple st 652'],
    ['Hannah', 'Mountain 21'],
    ['Michael', 'Valley 345'],
    ['Sandy', 'Ocean blvd 2'],
    ['Betty', 'Green Grass 1'],
    ['Richard', 'Sky st 331'],
    ['Susan', 'One way 98'],
    ['Vicky', 'Yellow Garden 2'],
    ['Ben', 'Park Lane 38'],
    ['William', 'Central st 954'],
    ['Chuck', 'Main Road 989'],
    ['Viola', 'Sideway 1633']
  ]
  con.query(sql, [data], function (err, result) {
    if (err) throw err;
    console.log(result);
    console.log("Number of records inserted: " + result.affectedRows);
  });

返回结果

{
  fieldCount: 0,
  affectedRows: 14,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '\'Records:14  Duplicated: 0  Warnings: 0',
  protocol41: true,
  changedRows: 0
}

查询数据

  var sql = "SELECT * FROM user;";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });

排序数据

  var sql = "SELECT * FROM user ORDER BY username";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });

更新数据

  var sql = "UPDATE customers SET address = 'Canyon 123' WHERE address = 'Valley 345'";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result.affectedRows + " record(s) updated");
  });

分页数据

  var sql = "SELECT * FROM customers LIMIT 5";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });

删除表格

  var sql = "DROP TABLE aaa;";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });

Node.js的MongoDB开发

安装包

npm install mongodb

创建数据库

var MongoClient = require('mongodb').MongoClient;
var url = "mongodb://localhost:27017/mydb";

MongoClient.connect(url, function(err, db) {
  if (err) throw err;
  console.log("Database created!");
  db.close();

创建集合

  db.createCollection("user", function(err, res) {
    if (err) throw err;
    console.log("Collection created!");
    db.close();
  });

删除集合

  // 也可以使用db.dropCollection
  db.collection("user").drop(function(err, delOK) {
    if (err) throw err;
    if (delOK) console.log("Collection deleted");
    db.close();
  });

插入数据

  1. 单个
  var myobj = { name: "Company Inc", address: "Highway 37" };
  db.collection("user").insertOne(myobj, function(err, res) {
    if (err) throw err;
    console.log("Number of documents inserted: " + res.insertedCount);
    db.close();
  });
  1. 多个
  var myobj = [
    { name: 'John', address: 'Highway 71' },
    { name: 'Peter', address: 'Lowstreet 4' },
    { name: 'Amy', address: 'Apple st 652' },
    { name: 'Hannah', address: 'Mountain 21' },
    { name: 'Michael', address: 'Valley 345' },
    { name: 'Sandy', address: 'Ocean blvd 2' },
    { name: 'Betty', address: 'Green Grass 1' },
    { name: 'Richard', address: 'Sky st 331' },
    { name: 'Susan', address: 'One way 98' },
    { name: 'Vicky', address: 'Yellow Garden 2' },
    { name: 'Ben', address: 'Park Lane 38' },
    { name: 'William', address: 'Central st 954' },
    { name: 'Chuck', address: 'Main Road 989' },
    { name: 'Viola', address: 'Sideway 1633' }
  ];
  db.collection("user").insertMany(myobj, function (err, res) {
    if (err) throw err;
    console.log("Number of documents inserted: " + res.insertedCount);
    db.close();
  });

查询

  1. 找一个记录
  db.collection("user").findOne({}, function(err, result) {
    if (err) throw err;
    console.log(result.name);
    db.close();
  });
  1. 找多个记录
  db.collection("user").find({}).toArray(function(err, result) {
    if (err) throw err;
    console.log(result);
    db.close();
  });
  1. 条件查询
    var query = { address: "Park Lane 38" };
    db.collection("user").find(query).toArray(function (err, result) {
        if (err) throw err;
        console.log(result);
        db.close();
    });
  1. 正则查询
  var query = { address: /^S/ };
  db.collection("user").find(query).toArray(function(err, result) {
    if (err) throw err;
    console.log(result);
    db.close();
  });
  1. 过滤返回结果
        if (err) throw err;
        console.log(result);
        db.close();
  });
  1. 排序结果
    var mysort = { name: 1 };
    db.collection("user").find({}, { _id: false }).sort(mysort).toArray(function (err, result) {
        if (err) throw err;
        console.log(result);
        db.close();
    });
  1. 分页
    var mysort = { name: 1 };
    db.collection("user").find({}, { _id: false }).sort(mysort).skip(1).limit(3).toArray(function (err, result) {
        if (err) throw err;
        console.log(result);
        db.close();
    });

更新数据

    var myquery = { address: "Valley 345" };
    var newvalues = { name: "Mickey", address: "Canyon 123" };
    db.collection("user").updateOne(myquery, newvalues, function (err, res) {
        if (err) throw err;
        console.log(res);
        db.close();
    });

删除数据

  1. 删除一个
    var myquery = { address: 'Mountain 21' };
    db.collection("user").deleteOne(myquery, function (err, obj) {
        if (err) throw err;
        console.log("1 document deleted");
        db.close();
    });
  1. 删除多个
  var myquery = { address: /^O/ };
  db.collection("user").deleteMany(myquery, function(err, obj) {
    if (err) throw err;
    console.log(obj.result.n + " document(s) deleted");
    db.close();
  });