Mysql データベース操作の基本とノードでの Mysql の使用

データベース: データを保存する倉庫。

データ構造

データ共有を実現

冗長データを削減できる

高いデータ独立性

統合されたデータ管理と制御

データベースの導入

MySQLデータベース

Oracle

Orcaleデータベース管理システムはOracle Corporation Developmentによって提供されていますは、データベースの分野で常にリーダーであり続けています

商用課金、大規模システム、淘宝網とJD.comは、Microsoftによって開発された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 データベースをインストールして構成します

公式 Web サイト: 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 のヘルプ情報が表示されます。

データベースのストレージ構造

データベースサーバーは複数のデータベースを持つことができます

データベースは複数のテーブルを持つことができます

テーブルはテーブル構造を持つ必要があります

すべてのデータはテーブルに格納されるため、データベースを作成した後, まずテーブル構造を設計する必要があります

複数のレコードを 1 つのテーブルに保存します

レコードはテーブル構造の形式で保存されます

データベースとテーブルを作成します

データベースを作成します

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 データベースでは、小数点は浮動小数点数と固定小数点数を使用して格納されます。

浮動小数点数には 2 つのタイプがあります:

単精度浮動小数点数 (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 は秒を表します。

次の 3 つの方法を使用して指定できます。時間値:

「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 列データ型;

列の変更: ALTER TABLE table_name MODIFY 列のデータ型;

列の削除: ALTER TABLE table_name DROP colum;

テーブル名の変更: TABLE table_name を new_table_name に変更;

列名の変更: ALTER TABLE table_name change colum_name new_colum_name データ型;

データ テーブルを削除

DROP TABLE table_name;

テーブルの制約

テーブルの制約は、テーブル内のフィールドに対するすべての制限であり、これにより、データ テーブル内のデータの正確性と一意性が保証されます。

主キー制約

各データ テーブルには、主キー制約を 1 つだけ設定できます。PRIMARY KEY として定義されたフィールドには、重複する値を含めることはできず、NULL 値にすることもできません。つまり、非nullで一意です

構文：フィールド名データ型PRIMARY KEY

非null制約

非null制約とは、MySQLではフィールドの値をNULLにできないという意味です。 NOT NULLによる。

構文: フィールド名のデータ型 NOT NULL;

一意制約

一意制約は、データテーブル内のフィールドの一意性、つまりテーブル内のフィールドの値が繰り返し出現できないことを保証するために使用されます。

構文: フィールド名データ型 UNIQUE;

デフォルト制約

デフォルト制約は、データベース内のフィールドのデフォルト値を指定するために使用されます。つまり、このフィールドにレコードが割り当てられていない場合に使用されます。このフィールドにはデフォルト値が挿入されます。

構文: フィールド名 データ型 DEFAULT デフォルト値;

テーブルのフィールド値を自動的に増加するように設定します

テーブルに挿入された新しいレコードの一意の ID を自動的に生成したい場合。 AUTO_INCREMENT を使用すると、次のことを実現できます

構文: フィールド名データ型 AUTO_INCREMENT;

データの追加、更新、削除

データの追加

テーブル内のすべてのフィールドにデータを追加

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

注:

の値値はテーブル内の値と同じである必要があります。フィールドは 1 対 1 に対応します。

挿入されるデータはフィールド内のデータと同じ型である必要があります

データのサイズは列の指定された範囲内である必要があります。たとえば、長さ 80 の文字列を長さの列に挿入することはできません40

文字型と日付型 データは一重引用符で囲む必要があります

NULL値を挿入したい場合はNULLを指定しない、またはNULLを使用してください

指定された列に従ってデータを追加します:

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

注:valuesの値列宣言の列と 1 対 1 に対応する必要があります

複数のレコードを同時に追加します

  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(&#39;INSERT INTO `feedback`(`message`, `name`, `email`, `date`) VALUES(?, ?, ?, ?)&#39;, [
        &#39;今天的雾霾很醇厚&#39;,
        &#39;校长&#39;,
        &#39;xiaozhang@abc.com&#39;,
        &#39;2016-11-17 09:31:00&#39;
      ], (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
    });