Mysql 데이터베이스 작업 기본 및 Node에서 Mysql 사용

데이터베이스: 데이터를 저장하는 창고입니다.

데이터 구조

데이터 공유 달성

중복 데이터 감소 가능

높은 데이터 독립성

통합 데이터 관리 및 제어

데이터베이스 소개

MySQL 데이터베이스

Oracle

Orcale 데이터베이스 관리 시스템은 Oracle Corporation에서 개발했으며 항상 데이터베이스 분야의 선두주자였습니다

상업용 과금, 대규모 시스템, Taobao 및 JD.com에서는 Oracle 데이터베이스를 사용합니다.

SqlServer 데이터베이스

는 Microsoft에서 개발했으며 Windows에서만 실행할 수 있습니다. .net 개발 프로그래머

DB2

는 IBM이 개발한 것으로 금융업계에서 널리 사용되고 있다. IBM 서버, 서버 구매하고 데이터베이스 무료

상업비

MS SQL Server

위의 데이터베이스는 SQL 언어를 사용하여 운영됩니다

SQL 언어 관계형 데이터베이스의 운영 언어

이 SQL 언어를 사용하면 데이터베이스와 테이블을 추가, 삭제, 수정할 수 있을 뿐만 아니라 테이블 데이터도 추가, 삭제, 수정할 수 있습니다.

테이블은 관계입니다

테이블과 테이블 사이에는 관계가 있습니다

MongoDB

비관계형 데이터베이스

이 데이터베이스에 저장된 데이터는 모두 컬렉션에 있습니다. 실제로 json 형식 데이터인 JavaScript 개체와 유사합니다.

컬렉션에는 구조가 없습니다. 컬렉션은 배열입니다.

컬렉션에 데이터를 삽입할 수도 있습니다.

MongoDB는 관계형 데이터베이스와 10gen이 개발한 컬렉션의 하이브리드입니다. 비관계형 데이터베이스 중 제품은 비관계형 데이터베이스 중 가장 기능이 풍부하고 관계형 데이터베이스와 가장 유사합니다. 지원하는 데이터 구조는 매우 느슨하고 json과 유사한 형식이므로 더 복잡한 데이터 구조 유형을 저장할 수 있습니다. MongoDB 데이터베이스 관리 시스템의 가장 큰 특징은 지원하는 쿼리 언어가 매우 강력하고 구문이 객체 지향 쿼리 언어와 유사하다는 것입니다. 또한 대용량 데이터와 높은 동시성을 갖춘 인터넷 애플리케이션을 지원하는 오픈 소스 데이터베이스이기도 합니다. 비관계형 데이터베이스를 운영하기 위해 SQL 언어를 사용할 필요는 없습니다.

관계형 데이터베이스 저장 구조

테이블 구조에 따른 저장 기록 데이터

업무에 따른 테이블 구조 설계

데이터베이스 관리 시스템

데이터베이스 서버

데이터베이스

데이터 테이블

기록

oracle, sqlserver, DB2, MySQL은 모두 관계형 데이터베이스입니다.

MySQL 데이터베이스 설치 및 구성

공식 웹사이트: http://www.mysql.com/

MySQL 서비스 시작 및 중지:

Windows 실행 중 services.msc를 입력하여 MySQL 서비스 찾기

DOS 명령을 통해 MySQL 서비스 시작(관리자 권한으로 터미널 열기)

MySQL 서비스에 로그인 MySQL 데이터베이스:

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

-h:hostname

-P:port

-u: 사용자 이름

-p: 비밀번호

mysql은 기본적으로 localhost 및 3306에 연결되므로 -h 및 -P를 생략할 수 있습니다.

mysql -u root -p

명령줄에 "help;" 또는 "h"를 입력하면 MySQL 도움말 정보가 표시됩니다.

데이터베이스 저장 구조

데이터베이스 서버는 여러 개의 데이터베이스를 가질 수 있습니다

데이터베이스는 여러 개의 테이블을 가질 수 있습니다

A 테이블은 테이블 구조를 가지고 있어야 한다

모든 데이터는 테이블에 저장되기 때문에 데이터베이스를 만든 후에는 먼저 테이블 구조를 설계해야 한다

하나의 테이블에 여러 개의 레코드를 저장하기

레코드는 테이블 구조 형태로 저장됩니다

데이터베이스 및 테이블 생성

데이터베이스 생성

CREATE DATABASE [IF NOT EXISTS] db_name

데이터베이스 보기

SHOW DATABASES;

데이터베이스 생성 명령문 보기

SHOW CREATE DATABASE db_name;

데이터베이스 삭제

DROP DATABASE [IF EXISTS] db_name;

데이터베이스 선택

USE db_name;

현재 사용 중인 데이터베이스 보기

SELECT 데이터베이스();

데이터 유형

정수 유형

부동 소수점 유형

MySQL 데이터베이스에서 소수는 부동 소수점 숫자와 고정 소수점 숫자를 사용하여 저장됩니다.

부동 소수점 수에는 두 가지 유형이 있습니다.

단정밀도 부동 소수점 수(FLOAT)

이배 정밀도 부동 소수점 수(DOUBLE)

고정 포인트 번호(DECIMAL) [ ˈdesˈml]

시간 및 날짜 유형

삽입된 값이 잘못된 경우 시스템은 자동으로 해당 0 값을 데이터베이스에 삽입합니다.

YEAR

는 '1901' ~ '2155' 또는 1901~2155

예를 들어 '2016'을 입력하는 4자리 문자열 또는 숫자로 표시됩니다. 또는 2016, 데이터베이스에 삽입된 값은 모두 2016

DATE

DATE 유형은 날짜 값을 나타내는 데 사용되며 시간 부분을 포함하지 않습니다.

"YYYY-MM-DD" 또는 'YYYYMMDD' 문자열 표현을 사용할 수 있습니다

예를 들어 '2016-10-01' 또는 '20161001'을 입력하면 날짜가 삽입됩니다. 데이터베이스는 2016 -10-01

TIME

TIME 유형은 시간 값을 나타내는 데 사용됩니다. 표시 형식은 일반적으로 HH:MM:SS입니다. 여기서 HH는 시간을 나타내고 MM은 분을 나타냅니다. SS는 초를 나타냅니다

time의 값은 다음 세 가지 방법으로 지정할 수 있습니다.

"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 열 데이터 유형;

열 삭제: ALTER TABLE table_name DROP colum;

테이블 이름 수정: TABLE table_name을 new_table_name으로 이름 변경;

열 이름 수정: ALTER TABLE table_name colum_name 변경 new_colum_name 데이터 유형 ;

데이터 테이블 삭제

DROP TABLE table_name;

테이블의 제약 조건

테이블은 데이터 테이블에 있는 데이터의 정확성과 고유성을 보장하기 위해 테이블의 필드를 제한합니다.

기본 키 제약 조건

각 데이터 테이블에는 최대 하나의 기본 키 제약 조건만 가질 수 있습니다. PRIMARY KEY로 정의된 필드는 중복된 값을 가질 수 없으며 NULL 값이 될 수 없습니다. 즉, null이 아니고 고유

구문: 필드 이름 데이터 유형 PRIMARY KEY

null이 아닌 제약 조건

null이 아닌 제약 조건은 필드 값이 NULL일 경우 MySQL에서는 null이 아닌 제약 조건이 NOT NULL을 통해 정의됩니다.

구문: 필드 이름 데이터 유형 NOT NULL;

Unique 제약 조건

Unique 제약 조건은 데이터 테이블에 있는 필드의 고유성을 보장하는 데 사용됩니다. 즉, 테이블에 있는 필드의 값이 반복될 수 없습니다.

구문: 필드 이름 데이터 유형 UNIQUE;

기본 제약 조건

기본 제약 조건은 데이터베이스의 필드에 대한 기본값을 지정하는 데 사용됩니다. 테이블에 기록하고, 이 필드에 할당된 값이 없으면 데이터베이스 시스템은 이 필드에 기본값을 삽입합니다.

구문: 필드 이름 데이터 유형 DEFAULT 기본값;

테이블의 필드 값이 자동으로 증가하도록 설정

새 레코드에 대한 고유 ID를 자동으로 생성하려는 경우 테이블에 삽입되었습니다. AUTO_INCREMENT를 사용하여

구문: 필드 이름 데이터 유형 AUTO_INCREMENT를 구현할 수 있습니다.

데이터 추가, 업데이트 및 삭제

데이터 추가

모든 필드에 추가 테이블 데이터

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

참고:

값의 값은 테이블의 필드와 일치해야 합니다.

삽입되는 데이터는 필드의 데이터와 동일한 형식이어야 합니다.

데이터의 크기는 해당 열의 지정된 범위 내에 있어야 합니다(예: 길이 80의 문자열). 길이가 40인 문자열에는 삽입할 수 없습니다.

열의 문자 및 날짜 데이터는 작은따옴표로 묶어야 합니다.

Null 값을 삽입하려면 지정하지 않거나 NULL

지정된 열에 따라 데이터를 추가하려면:

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

참고: 값의 값은 열 선언의 열과 일대일로 대응해야 합니다

동시에 여러 레코드 추가

  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
    });