MySQL

Quick start

Install MySQL database (Windows)

The following uses Mysql in Windows as an example. For more information on how to install Mysql, see the Mysql Installation tutorial

Download the Windows mysqL-8.0.16-Winx64 version.

Mysql > download

After downloading, unzip the zip package to the appropriate directory. Here I put the unzip folder in E:\mysql-8.0.16-winx64.

Configure the MySQL

Go to E:\mysql-8.0.16-winx64, create my.ini configuration file in this folder, edit my.ini, and configure the following basic information:

[mysql]
Mysql client default character set
default-character-set=utf8
 
[mysqld]
Set port 3306
port = 3306
Set mysql installation directory
basedir=E:\\mysql-8.0.16-winx64
Mysql 8+ mysql 8+ mysql 8+ mysql 8+ mysql 8+ mysql
# datadir = E: \ \ mysql - 8.0.16 - winx64 \ \ sqldata
# Maximum number of connections allowed
max_connections=20
The default character set used by the server is the 8-bit latin1 character set
character-set-server=utf8
The default storage engine to use when creating new tables
default-storage-engine=INNODB
Copy the code

Start the MySQL

Open CMD command line tool as administrator and switch directory:

CD E: \ mysql - 8.0.16 - winx64 \ binCopy the code

Initialize the database:

mysqld --initialize --console
Copy the code

After the command is executed, the system displays the default password of user root, for example:

E:\mysql-8.0.16-winx64\bin>mysqld --initialize --console 2019-07-04t14:59:179711z 0 [System] [my-013169] [Server] E:\mysql-8.0.16-winx 64\bin\mysqld.exe (mysqld 8.0.16) initializing of server in progress as process 2724 2019-07-04T14:59:182711z 0 [Warning] [My-013242] [Server] --character-set-ser ver: 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. 2019-07-04T14:59:29.346379z 5 [Note] [my-010454] [Server] A temporary password I s generated for root@localhost: [System] [my-013170] [Server] E:\mysql-8.0.16-winx 64\bin\ mysql.exe (mysqld 8.0.16) initializing of server has completedCopy the code

F9Lh2JSH8w_0 is the initial password, which you will need to log in later. You can also change the password after logging in.

Enter the following installation command:

mysqld install
Copy the code

The output might look like this:

E:\mysql-8.0.16-winx64\bin>mysqld install
Service successfully installed.

E:\mysql-8.0.16-winx64\bin>
Copy the code

Start the mysql service

To start, enter the following command:

net start mysql
Copy the code

::: tip Note that the data directory needs to be initialized in 5.7:

CD C: \ web \ mysql - 8.0.11 \ bin mysqld -- the initialize - insecureCopy the code

After the initialization, run net start mysql to start mysql.

The output might look like this:

E:\mysql-8.0.16-winx64\bin>net start mysql... The MySQL service has been started successfully.Copy the code

: : :

Log on to the MySQL

When the MySQL service is running, you can log in to the MySQL database using the MySQL client tool. First, open the command prompt and enter the following name:

Mysql -h host name -u user name -p

  • -h: specifies the name of the MySQL host to be logged in to by the client. This parameter can be omitted if you log in to the localhost (localhost or 127.0.0.1).
  • -u: indicates the login user name.
  • -p: tells the server that a password will be used to log in. You can omit this option if the password is empty.

If you want to log in to the local MySQL database, just type the following command:

E:\mysql-8.0.16-winx64\bin>mysql -u root -p Enter password: ************ Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.0.16 Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql>Copy the code

Mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql > install Windows mysql

mysql> alter user 'root'@'localhost' identified by 'helloworld';
Query OK, 0 rows affected (0.08 sec)
Copy the code

: : :

Then, you can have fun coding with mysql.

E:\mysql-8.0.16-winx64\bin>mysql -u root -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.0.16 MySQL Community Server - GPL Copyright (C) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql> use mysql Database changed mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 4 rows in the set (0.10 SEC) mysql >Copy the code

::: tip It is recommended to configure E: mysql-8.0.16-winx64\bin into the system environment variable for easy use.

Install NPM package -mysql

npm install --save mysql
Copy the code

or

yarn add mysql
Copy the code

Mysql Module Introduction

Mysql module is the engine for Node to operate mysql. It can create tables, add, delete, modify, and search mysql database in node.js environment.

Begin to use

Create a database named demo and define a table named demo_tabel. The operation log may be as follows:

C:\Users\James>mysql -u root -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 11 Server version: 8.0.16 MySQL Community Server - GPL Copyright (C) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database demo; Query OK, 1 row affected (0.12 SEC) mysql> create table demo_tabel -> (-> id int(11), -> name varchar(30), -> sex varchar(4) -> ); Query OK, 0 rows affected (0.49 SEC) mysql> show tables; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | Tables_in_demo | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | demo_table | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 row in the set (0.02 SEC) mysql>Copy the code

The sample code

Next, write a simple server. The js code, at http://localhost:3000/query/, return the data in the table. For server js in the complete sample code is as follows:

// server.js
const Koa = require('koa');
const app = new Koa();
const mysql = require('mysql')
const Router = require('koa-router')

/* If you are using a session, you need to configure connection parameters for each session. Hence the need for connection pool management sessions. * /
const pool = mysql.createPool({
  host: 'localhost'.// Database address
  user: 'root'.// The user name for logging in to the data
  password: 'helloworld'./ / password
  database: 'demo'            // The database used
})

const port = 3000
const hostName = '127.0.0.1'

const router = new Router();

const query = (sql, values) = > {
  return new Promise((resolve, reject) = > {
    pool.getConnection((error, connection) = > {
      connection.query(sql,values, (error, results) = > {
        if(error) throw error
        connection.release()
        resolve(results)
      })
    })
  }) 
}

router.get('/'.async (ctx, next) => {
  ctx.res.type = 'application/json'
  ctx.body = await query('select * from demo_table')}); app .use(router.routes()) .use(router.allowedMethods()); app.listen(port, hostName);console.log(`http://${hostName}:${port}`)
Copy the code

A link to the

  • mysql (npm package) api
  • Mysql download