Mysql database operation basics and using Mysql in Node

Database: A warehouse that stores data.

Data structure

realizes data sharing

can reduce redundant data

High data independence

Unified data management and control

Database introduction

MySQL database

Oracle

Orcale database management system is provided by Oracle Corporation Development, has always been a leader in the field of databases

Commercial charging, large-scale systems, Taobao and JD.com use Oracle database

SqlServer database

developed by Microsoft and can only run on Windows. .net development programmer

DB2

was developed by IBM and is widely used in the financial industry. IBM's server, buy the server and get the database free

Commercial charges

MS SQL Server

The above databases are all operated using SQL language

SQL language is an operating language for relational databases

Using this SQL language you can Add, delete, and modify databases and tables, as well as add, delete, and modify table data

Tables are relationships

Between tables are relationships

MongoDB

Non-relational database

There are no tables, and the data stored in this database are all collected , similar to JavaScript objects, is actually data in json format

A collection has no structure, a collection is an array

You can also insert data into a collection

MongoDB is a between relational database and non-relational database developed by 10gen. Among the products among non-relational databases, it is the most feature-rich among non-relational databases and most similar to relational databases. The data structure it supports is very loose and is in a json-like format, so it can store more complex data structure types. The biggest feature of the MongoDB database management system is that the query language it supports is very powerful, and its syntax is similar to the object-oriented query language. It is also an open source database that supports Internet applications with large amounts of data and high concurrency. You do not need to use SQL language to operate non-relational databases.

Relational database storage structure

Storage record data according to the table structure

Design the table structure according to the business

Database management system

Database server

Database

Data table

Record

oracle, sqlserver, DB2, MySQL They are all relational databases.

Install and configure the MySQL database

Official website: http://www.mysql.com/

Start and stop the MySQL service:

Run through Windows, enter services.msc to find the MySQL service

via DOS Command to start the MySQL service (open the terminal with administrator privileges)

Log in to the MySQL database:

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

-h: host name

-P: port

-u: Username

-p: Password

mysql connects to localhost and 3306 by default, so you can omit -h and -P:

mysql -u root -p

Enter "help;" or "h" in the command line, MySQL help information will be displayed.

Database storage structure

A database server can have multiple databases

A database can have multiple tables

A table must have a table structure

All data is stored in the table, so After you have a database, you must first design your table structure

Storage multiple records in one table

Records are stored in the form of table structure

Create databases and tables

Create database

CREATE DATABASE [IF NOT EXISTS] db_name

View database

SHOW DATABASES;

Show database creation statement

SHOW CREATE DATABASE db_name;

Delete database

DROP DATABASE [IF EXISTS] db_name;

Select database

USE db_name;

View the currently used database

SELECT database();

Data type

Integer type

Floating point type

In the MySQL database, decimals are stored using floating point numbers and fixed point numbers. of.

There are two types of floating point numbers:

Single precision floating point number (FLOAT)

Double precision floating point number (DOUBLE)

Fixed point number (DECIMAL) [ˈdesɪml]

Time and date types

If the inserted value is illegal, the system will automatically insert the corresponding zero value into the database.

YEAR

Using a 4-digit string or number, the range is '1901' ~ '2155' or 1901 ~ 2155

For example, if you enter '2016' or 2016, the values ​​inserted into the database will be 2016

DATE

DATE type is used to represent date values ​​and does not include the time part.

You can use "YYYY-MM-DD" or 'YYYYMMDD' string representation

For example, if you enter '2016-10-01' or '20161001', the date inserted into the database is 2016-10-01

TIME

TIME type is used to represent time values. Its display format is generally HH:MM:SS, where HH represents hours, MM represents minutes, and SS represents seconds.

You can use the following three methods to specify the time value:

With " D HH:MM:SS" string format representation. Among them, D represents the day, which can take a value between 0-34. When inserting data, the value of the hour is equal to (D*24+HH)

For example, enter '2 11:30:50', and the date inserted into the database is 59:30:50

Represented in 'HHMMSS' string format or HHMMSS number format

For example: input '345454' or 345454, the date inserted into the database is 34:54:54

Use CURRENT_TIME or NOW() to enter the current System time

DATETIME

Specify the value of DATETIME type:

Use 'YYYY-MM-DD HH:MM:SS' or 'YYYYMMDDHHMMSS' as a string or number.

Use NOW to enter the date and time of the current system

TIMESTAMP

TIMESTAMP type display format is the same as DATETIME, but the value range is smaller than DATETIME.

Enter CURRENT_TIMESTAMP to enter the current date and time of the system

When you enter NULL, the system will automatically enter the current date and time

When there is no input, the system will enter the current date and time of the system

mark. You can use it to save the last modification time of a record in the table. Automatic maintenance.

CHAR and VARCHAR

When the data is of CHAR(4) type, regardless of the length of the inserted value, the storage space occupied is 4 bytes. The number of bytes occupied by the data corresponding to VARCHAR (4) is the actual length plus 1.

Summary:

Types with variable character length need to calculate the byte length when querying using VARCHAR

The string length is fixed The use of CHAR query is fast.

VARCAHR saves space than CHAR

CHAR saves time than VARCHAR

TEXT type

represents large text data, such as article content, comments, etc.

Basic operations

View table structure

View all tables in the current database ： show tables;

View table structure: desc table_name;

View table creation statements: show create table table_name;

Modify data table

Add columns: ALTER TABLE table_name ADD colum datatype;

Modify columns: ALTER TABLE table_name MODIFY colum datatype;

Delete column: ALTER TABLE table_name DROP colum;

Modify table name: rename TABLE table_name to new_table_name;

Modify column name: ALTER TABLE table_name change colum_name new_colum_name datatype;

Delete data table

DROP TABLE table_name;

Constraints of the table

The constraints of the table are all restrictions on the fields in the table, thereby ensuring the correctness and uniqueness of the data in the data table.

Primary key constraints

There can be at most one primary key constraint in each data table. The fields defined as PRIMARY KEY cannot have duplicate values ​​and cannot be NULL values. That is, non-null and unique

Syntax: Field name Data type PRIMARY KEY

Non-null constraint

Non-null constraint means that the value of the field cannot be NULL. In MySQL, non-null constraint is defined by NOT NULL.

Syntax: field name data type NOT NULL;

Unique constraint

Unique constraint is used to ensure the uniqueness of fields in the data table, that is, the values ​​of the fields in the table cannot appear repeatedly.

Syntax: Field name data type UNIQUE;

Default constraints

Default constraints are used to specify default values ​​for fields in the database, that is, when a record is inserted into the table, if this field is not assigned a value, the database system will This field inserts a default value.

Syntax: Field name Data type DEFAULT Default value;

Set the field value of the table to automatically increase

If you want to automatically generate a unique ID for new records inserted into the table. You can use AUTO_INCREMENT to achieve

Syntax: field name data type AUTO_INCREMENT;

Add, update and delete data

Add data

Add data to all fields in the table

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

Note: The values ​​in

values ​​must be the same as those in the table The fields correspond one to one.

The inserted data should be of the same type as the data in the field

The size of the data should be within the specified range of the column, for example, a string of length 80 cannot be inserted into a column of length 40

Character and date type Data should be enclosed in single quotes

If you want to insert a null value, do not specify or use NULL

Add data according to the specified column:

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

Note: The value in values ​​must correspond one-to-one with the column in the column declaration

Add multiple records at the same time

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

Update all data:

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

Update by condition:

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

Summary:

UPDATE statement can update the columns of the rows in the original table with new values .

SET clause specifies which columns to modify and which values ​​to give

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