Mysql数据库操作基础及Node中使用Mysql

数据结构化

实现数据共享

可以减少冗余数据

数据独立性高

数据统一管理与控制

数据库介绍

MySQL 数据库

Oracle

Orcale数据库管理系统由甲骨文公司开发，在数据库领域一直处于领先地位

商业收费，大型系统，淘宝、京东就使用了 Oracle 数据库

SqlServer 数据库

由微软公司开发，只能在Windows上运行。.net开发程序员

DB2

由IBM公司开发，在金融行业中使用的比较多。IBM的服务器，买服务器送数据库

商业收费

MS SQL Server

以上这些数据库都是使用 SQL 语言进行操作

SQL 语言就是用于 关系型数据库 的一个操作语言

利用这个 SQL 语言可以增删改查库和表，以及增删改查表数据

表 就是 关系

表与表之间就是关系

MongoDB

非关系型数据库

没有表，这个数据库中存储的数据都在集合中，类似于 JavaScript 对象，其实就是 json 格式的数据

集合没有结构，集合就是一个数组

也可以往集合中插入数据

MongoDB是由10gen公司开发的一个介于关系型数据库和非关系型数据库之间的产品，是非关系型数据库中功能最丰富，最像关系型数据库的。他支持的数据结构非常松散，是类似json的格式，所以可以存储比较复杂的数据结构类型。MongoDB数据库管理系统最大的特点就是它支持的查询语言非常强大，语法类似于面向对象的查询语言。它还是一个开源的数据库，对于大数据量、高并发的互联网应用，支持非常不错。操作非关系型数据库不需要使用SQL语言。

关系型数据库存储结构

根据表结构存储记录数据

根据业务设计表结构

数据库管理系统

数据库服务器

数据库

数据表

记录

oracle、sqlserver、DB2、MySQL 都是关系型数据库。

安装和配置 MySQL 数据库

官方网站：http://www.mysql.com/

启动 和 停止MySQL服务：

通过Windows的运行，输入services.msc找到MySQL服务

通过DOS命令启动MySQL服务（使用管理员权限打开终端）

登录MySQL数据库:

mysql -h localhost -P 3306 -u root -p

-h：主机名

-P：端口

-u：用户名

-p：密码

mysql默认连接localhost和3306，所以可以省略-h和-P:

mysql -u root -p

在命令行中输入“help;”或者“h”，就会显示出MySQL的帮助信息。

数据库的存储结构

一个数据库服务器上可以有多个数据库

一个数据库中可以有多个表

一个表一定要有表结构

所有的数据存储在表中，所以有了数据库之后，要先设计你的表结构

一张表中存储多条记录

记录按照表结构的形式进行存储

创建数据库和表

创建数据库

CREATE DATABASE [IF NOT EXISTS] db_name

查看数据库

SHOW DATABASES;

显示数据库创建语句

SHOW CREATE DATABASE db_name;

删除数据库

DROP DATABASE [IF EXISTS] db_name;

选择数据库

USE db_name;

查看当前使用的数据库

SELECT database();

数据类型

整数类型

浮点型

在MySQL数据库中，存储小数都是使用浮点数和定点数来表示的。

浮点数有两种：

单精度浮点数（FLOAT）

双精度浮点数(DOUBLE)

定点数（DECIMAL）[ˈdesɪml]

时间和日期类型

如果插入的数值不合法，系统会自动将对应的零值插入到数据库中。

YEAR

使用4位字符串或数字表示，范围为‘1901‘ ~ ‘2155’或1901~2155

例如，输入‘2016‘或者2016，插入到数据库的值均为2016

DATE

DATE类型用来表示日期值，不包含时间部分。

可以使用“YYYY-MM-DD“或‘YYYYMMDD‘字符串表示

例如，输入‘2016-10-01‘或’20161001‘插入到数据库的日期都是2016-10-01

TIME

TIME类型用于表示时间值，它的显示形式一般为HH:MM:SS,其中HH 表示小时，MM表示分，SS表示秒

可以使用下面三种方式指定时间的值：

以“D HH：MM：SS“字符串格式表示。其中，D表示日，可以取0-34之间的值，插入数据时，小时的值等于（D*24+HH）

例如，输入‘2 11:30:50‘，插入数据库的日期为 59:30:50

以‘HHMMSS‘字符串格式或者HHMMSS数字格式表示

例如：输入‘345454‘或345454，插入数据库的日期为34:54:54

使用CURRENT_TIME或NOW()输入当前系统时间

DATETIME

指定DATETIME类型的值：

以‘YYYY-MM-DD HH:MM:SS‘或者’YYYYMMDDHHMMSS‘字符串或数字都可以。

使用NOW来输入当前系统的日期和时间

TIMESTAMP

TIMESTAMP类型显示形式和DATETIME相同，但取值范围比DATETIME小。

输入CURRENT_TIMESTAMP输入系统当前日期和时间

输入NULL时，系统会自动输入当前日期和时间

无任何输入时，系统会输入系统当前日期和时间

标记。利用它可以保存表中某条记录的最后修改时间。自动维护。

CHAR和VARCHAR

当数据为CHAR(4)类型时，不管插入值的长度是多少，所占用的存储空间都是4个字节。而VARCHAR（4）所对应的数据所占用的字节数为实际长度加1.

总结：

字符长度不固定的类型使用VARCHAR 查询的时候要计算字节的长度

字符串长度固定的使用CHAR 查询速度快。

VARCAHR比CHAR省空间

CHAR比VARCHAR省时间

TEXT类型

表示大文本数据，例如：文章内容、评论等

基本操作

查看表结构

查看当前数据库中的所有表： show tables;

查看表结构: desc table_name;

查看建表语句: show create table table_name;

修改数据表

增加列: ALTER TABLE table_name ADD colum datatype;

修改列: ALTER TABLE table_name MODIFY colum datatype;

删除列: ALTER TABLE table_name DROP colum;

修改表名: rename TABLE table_name to new_table_name;

修改列名: ALTER TABLE table_name change colum_name new_colum_name datatype;

删除数据表

DROP TABLE table_name;

表的约束

表的约束条件都是针对表中字段进行限制，从而保证数据表中数据的正确性和唯一性。

主键约束

每个数据表中最多只能有一个主键约束，定义为PRIMARY KEY 的字段不能有重复值且不能为NULL值。也就是非空而且唯一

语法：字段名 数据类型 PRIMARY KEY

非空约束

非空约束指的是字段的值不能为NULL，在MySQL中，非空约束是通过NOT NULL 定义的。

语法：字段名 数据类型 NOT NULL;

唯一约束

唯一约束用于保证数据表中字段的唯一性，即表中字段的值不能重复出现。

语法：字段名 数据类型 UNIQUE;

默认约束

默认约束用于给数据库中的字段指定默认值，即当在表中插入一条记录时，如果没有给这个字段赋值，数据库系统会为这个字段插入默认值。

语法：字段名 数据类型 DEFAULT 默认值;

设置表的字段值自动增加

如果想为表中插入的新纪录自动生成唯一ID。可以使用AUTO_INCREMENT实现

语法：字段名 数据类型 AUTO_INCREMENT;

添加、更新与删除数据

添加数据

为表中所有字段添加数据

 INSERT INTO 表名  
 VALUES(列1值,列2值,...)

注意：

values中的值必须与表中的字段一一对应。

插入的数据应与字段中的数据类型相同

数据的大小应该在列的规定范围内，例如不能将一个长度为80的字符串插入到长度为40个列中

字符和日期型数据应该包含在单引号中

如果要插入一个空值，不指定或者使用NULL

按照指定列添加数据：

 INSERT INTO 表名(列1名, 列2名,...)  
 VALUES(列1值, 列2值,...)

注意: values中的值必须与列声明中的列一一对应

同时添加多条记录

  INSERT INTO employee
  VALUES (value1,value2,value3...),
          (value1,value2,value3...),
          (value1,value2,value3),
          ...;

更新全部数据：

  UPDATE 表名
  SET 列名=值, 列名=值[,列名=值]

按条件更新：

  UPDATE 表名
  SET 列名=值, 列名=值[,列名=值]
  WHERE 条件;

总结：

UPDATE语句可以用新值更新原有表中行的列。

SET字句指定要修改哪些列和要给与哪些值

WHERE需要给定一个条件，表示要更新符号该条件的行，没有WHERE字句，则更新所有行

条件可以使用的运算符：

  -- 比较运算符 > < <= >= = <>  大于、小于、大于(小于等于)、不等于
  BETWEEN…AND -- 显示在某一区间的值
  IN(set) -- 显示在in列表中的值，例：in(100,200)
  LIKE -- ‘张pattern’ 模糊查询%
  IS NULL -- 判断是否为空

  -- 逻辑运算符 AND 多个条件同时成立
  OR  多个条件任一成立
  NOT 不成立，例：WHERE NOT(salary>100)

删除数据：

删除全部数据

DELETE FROM 表名;

根据条件删除：

 DELETE FROM 表名  WHERE 条件;

初始化

runcate初始化数据表

truncate table_name;

truncate和delete的区别：

delete会一条一条的删

truncate先摧毁整张表，再创建一张和原来的表结构一模一样的表

拿拆迁举例子

truncate在效率上比delete高

truncate只能删除整表的数据，也就是格式化。

truncate会把自增id截断恢复为1

总结：

如果不使用WHERE语句，将删除表中所有数据

DELETE不能删除某一列的值，（可使用UPDATE）

使用DELETE语句仅仅删除记录，不删除表本身，如果要删除表，使用DROP TABLE语句

删除表中所有数据也可以使用truncate table_name语句

单表查询

简单查询

SELECT [DISTINCT] *|{colum1, colum2, colum3...} FROM table_name;

SELECT指定查询哪些列的数据

column指定列名

号表示查询所有列

FROM 指定查询哪种表

DISTINCT 可选，指查询结果时，是否去除重复数据

查询表中所有数据：

SELECT * FROM 表名;

按照指定列查询表中所有数据：

SELECT 列名,列名[,列名] 
FROM 表名;

根据条件查询数据：

 SELECT * FROM 表名  
 WHERE 条件;

在WHERE字句中经常使用的运算符

LIKE语句中，% 代表零个或多个任意字符，_代表一个字符，例如：name LIKE '_a%';

多表查询：

  -- 多表查询
  -- 找到表 articles 中 user_id 等于 users 表中 id 的
  -- 多表查询可以起别名
  SELECT a.id as article_id, a.title, a.time
  FROM articles as a
  INSERT JOIN users as u
  ON a.user_id=u.id

查询总记录数：

  -- 查询表中的总记录数据
  SELECT COUNT(id) as count FROM articles;

聚合函数

在实际开发中，经常需要对某些数据进行统计，例如统计某个字段的最大值，最小值，平均值等，为此，MySQL提供了一些函数来实现这些功能。

COUNT（列名）返回某一列，行的总数

COUNT（列名）返回某一列，行的总数

SUM()函数返回满足WHERE条件的行的和

  SELECT SUM(列名) {, SUM(列名)...} FROM table_name
    [WHERE where_definition]

注意：SUM仅对数值起作用，否则报错; 对多列求和，“,”不能少。

MAX()/MIN()函数返回满足WHERE条件的一列的最大/最小值

  SELECT MAX(列名) FROM table_name
    [WHERE where_definition];

对查询结果排序

  SELECT colum1, colum2, colum3..
      FROM table_name
      ORDER BY colum ASC|DESC;

ORDER BY 指定排序的列，排序的列表即可以是表中的列名，也可以是SELECT语句后指定的列名.

ASC 升序，DESC 降序

ORDER BY 字句应该位于SELECT 语句的结尾

分组查询

  SELECT colum1, colum2, ...
    FROM 表名
    LIMIT [OFFSET, ] 记录数

LIMIT表示从哪一条记录开始往后【不包含该记录】，以及一共查询多少记录

OFFSET表示偏移量:

如果为0则表示从第一条记录开始

如果为5则表示从第6条记录开始

使用场景：分页查询

分页查询一个例子

  -- 仅仅取了前 10 条
  SELECET * FROM articles LIMIT 10
  -- 跳过一条取一条
  SELECET * FROM articles LIMIT 1, 1

为表和字段区别名

为表取别名

  SELECT 表别名.id,表别名.name... FROM 表名 AS 表别名
    WHERE 表别名.id = 2..

为字段取别名

  SELECT 字段名 [AS] 别名 [,字段名 [AS] 别名,...] FROM 表名;

多表操作

实际开发中业务逻辑比较复杂，可能有几十到几百张表不等，所以我们就需要对多张表来进行查询操作，对两张以上的表进行操作，就是多表操作。

外键

为了保证数据的完整性，将两张表之间的数据建立关系，因此就需要在成绩表中添加外键约束。

外键是指引用另一个表中的一列或多列，被引用的列应该具有主键约束或唯一约束。

外键用于建立和加强两个表数据之间的链接。

为表添加外键约束

创建表的时候添加外键:

  CREATE TABLE department(
      id INT PRIMARY KEY auto_increment,
      name VARCHAR(20) NOT NULL
  );
  CREATE TABLE employee(
      id INT PRIMARY KEY auto_increment,
      name VARCHAR(20) NOT NULL,
      dept_id INT,
      FOREIGN KEY (id) REFERENCES department(id)
  );

表已经存在，通过修改表的语句增加外键:

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

删除外键约束

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

操作关联表

关联关系:

多对一

多对多

一对一

使用 Node 操作 MySQL 数据库

安装：

$ npm install --save mysql

使用连接池操作 MySQL 数据库

修改安装目录下 my.ini 文件中的: max_connections=1000 默认是 max_connections=151

重启服务器

连接池

封装过程：

  const mysql = require('mysql');

  // 使用连接，提高操作数据库效率
  // 创建一个连接池，池子存放的连接数量是 100 个
  const pool = mysql.createPool({
    connectionLimit: 100,
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'personal'
  });

  for (let i = 0; i < 1000; i++) {
    // 从池子中拿一个可用的连接
    pool.getConnection((err, connection) => {
      if (err) {
        throw err;
      }
      connection.query('INSERT INTO `feedback`(`message`, `name`, `email`, `date`) VALUES(?, ?, ?, ?)', [
        '今天的雾霾很醇厚',
        '校长',
        'xiaozhang@abc.com',
        '2016-11-17 09:31:00'
      ], (err, stat) => {
        // 尽早的释放回连接池
        // 只要操作数据库的回调函数被执行，说明这个连接的任务完成了
        connection.release();
        if (err) {
          throw err;
        }
        console.log(`第${i+1}个任务完成了`);
      });
    });
  }

封装：db.js

  const mysql = require('mysql');
  
  const pool = mysql.createPool({
    connectionLimit: 100,
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'personal'
  });

  // rest 参数
  // 作为函数参数的最后一个参数出现，以 ... 开头，后面跟一个名字
  // rest 参数就代替了 arguments
  exports.query = function (sql, ...values) {
    let callback;
    let params = [];
    if (values.length === 3) {
      params = values[0];
      callback = values[1];
    } else if (values.length === 2) {
      callback = values[0];
    }
    pool.getConnection((err, connection) => {
      if (err) {
        return callback(err);
      }
      // 如果传递了两个参数，则第二个参数就是 callback
      // 也就是说这种情况下，params 就是 callback
      // 后面的 参数就忽略不计了
      // 如果真的传递了三个参数，那就是一一对应
      connection.query(sql, params, (err, result) => {
        connection.release();
        if (err) {
          return callback(err);
        }
        callback(null, result);
      });
    });
  };

promise 版

  const mysql = require('mysql');

  const pool = mysql.createPool({
    connectionLimit: 100,
    host: 'localhost',
    user: 'root',
    password: 'root',
    database: 'personal'
  });

  exports.query = (sql, params = []) => {
    return new Promise((resolve, reject) => {
      pool.getConnection((err, connection) => {
        if (err) {
          return reject(err);
        }
        connection.query(sql, params, (err, result) => {
          connection.release();
          if (err) {
            return reject(err);
          }
          resolve(result);
        });
      });
    });
  };

调用示例：

  const db = require('./db')

  db.query('SELECT 1 + 1 as solution');
    .then(rows => {
      // use rows
      return db.query('INSERT INTO table_name VALUES(?, ?, ?)', ['值1', '值2', '值3'])
    })
    .then(rows => {
      // use rows
    })
    .catch(err => {
      // handle error
    });