The last article briefly introduced how to write an interface, in writing the interface, we will use a relational database such as MySQL; Database visual management tools such as Sequel Ace; Key-value databases such as Redis; Interface debugging tools such as Postman; Here are their functions and easy to use:

MySQL

MySQL is one of the most popular and useful relational databases in Web applications. Databases are used to store and manage data. Relational databases have the following main features:

  1. Data is stored in the form of a table, which has a fixed data format and uses DDL statements to modify the table structure.
  2. Use structured query language (SQL) to do data read and write
  3. Data consistency is maintained through transactions

In business, we can use MySQL to store user information, commodity information, order information and so on. Use transactions to ensure data consistency during user actions, such as locking inventory and coupons when placing orders and locking balances when withdrawing cash. A few important terms to know before you start:

  1. A database is a collection of multiple tables. A database can contain multiple tables. A table contains multiple rows of data with the same structure
  2. A table can have multiple columns and rows. It looks like an Excel table. A row is a single piece of data, and a column is the same type of data
  3. Column: A data table has multiple columns. When creating columns, you need to define data types for the columns. There are several types of data types: integer, floating point, string, time and date, BLOB and TEXT, and enumeration
  4. A table can contain only one primary key. A primary key can be used to query data

The installation

Here is an example of using Homebrew to install [email protected] in MacOS.

The brew install [email protected]Copy the code

After successful installation, the console will output the usage method:

If you need to have [email protected] first in your PATH, run:
    ZSHRC (source ~/.zshrc) to invoke the mysql directive from the console
    echo 'the export PATH = "/ usr/local/opt/[email protected] / bin: $PATH"' >> ~/.zshrc
To connect run:
    # Execute this line to enter the mysql console
    mysql -uroot
Copy the code

Mysql console:

View all databases
mysql> show databases;

# + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
# | Database |
# + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +
# | information_schema |
# | localDatabase |
# | mysql |
# | performance_schema |
# | sys |
# + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- +

Create a database named test_database
mysql> create database test_database;

Select test_database
use test_database;
Copy the code

At this point, we have created and selected test_Database, and we are ready to create tables in the library

Create a table

CREATE TABLE `test_user_tbl` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'Username',
  `pwd` varchar(20) NOT NULL DEFAULT ' ' COMMENT 'password'.PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Copy the code

This statement creates the tablespace test_user_TBL and defines the data structure for the three columns in the table:

  • A primary keyidWhen data is inserted
  • fieldusername, a string of length 20 (varchar), cannot be NULL (NOT NULL) DEFAULT empty string (DEFAULT “), remarksThe user name(COMMENT ‘username’)
  • fieldpwd.

Add and delete

-- Insert 'username username PWD password' data
mysql> INSERT INTO test_user_tbl 
    -> (username, pwd)
    -> VALUES
    ->(" username ", "password ");Select * from username where id=1;
mysql> UPDATE test_user_tbl SET username='Updated username' WHERE id=1;

Select * from data where id=1
mysql> SELECT * from test_user_tbl WHERE id=1;

Select * from data where id=1
mysql> DELETE FROM test_user_tbl WHERE id=1;
Copy the code

These are some of the basic operations, but there are a variety of other SQL syntactic statements that can be executed on the mysql console. We use the MySQL library to connect to and manipulate MySQL in the backend application:

Sequelize is used to connect to the MySQL database in NodeJS

const Sequelize = require("sequelize");
const sequelize = new Sequelize("test_user_tbl"."root"."", {
  host: "localhost".dialect: "mysql".pool: {
    max: 5.min: 0.idle: 10000,}});Copy the code

Matters needing attention in project practice

  1. Consider character setsCHARSET=utf8mb4Instead ofutf8, the former is compatible with more rare characters, emoji and so on
  2. Note that transactions are used to ensure data consistency, with different isolation levels for different requirements
  3. It is recommended that sensitive information such as passwords and mobile phone numbers be encrypted and stored in a database for desensitization to avoid information leakage
  4. Configure backup rules for distributed databases to ensure data security

Learning resources

Mysql Application Guide [official documentation] [docs.oracle.com/en-us/iaas/…]

Sequel Ace

Using the console to manipulate MySQL is not intuitive or convenient, since the table looks like a table, we need the visualization tool to actually see a table. MacOS recommends Sequel Ace, a free tool

  • Establish a connection: Open the software and set host, username and password to establish a connection (database optional port 3306 by default).

  • Top button: Select/Create database

  • Click the + sign in the lower left corner to create the data table, enter the table name and add edit column information to the right.

  • Click the left list to choose to view additional tables, and click the upper right TAB to view table structure, content, relationships, table information, and so on.

  • Mainly in the content can easily view the data in the table, also can double click to edit the data (often used in the development environment)

Redis

Redis is a very fast non-relational (NoSQL) in-memory key-value database that stores mappings between keys and five different types of values.

The key type can only be string. The value supports five data types: string, list, set, ordered set, and hash.

Redis has the following key advantages:

  • High read/write performance
  • Rich data types – support strings, lists, sets, ordered sets, hash tables
  • Atomicity – Either complete success or complete failure
  • It can be persistent, distributed and data backup

Redis works like a Map data structure in JS (except that the key must be a string and the value must conform to the supported data structure). In business, we can use Redis to store users’ authentication tokens. When users access with tokens, they can quickly take them out of Redis and compare them. You can also use ordered sets, lists, and so on

The installation

Official website Installation Tutorial

Brew Services start Redis
redis-server

Go to the Redis console
redis-cli
Copy the code

Basic use of console

Set key to usertoken and value to my-token127.0.0.1:6379 > SET usertoken my - tokenSelect * from usertoken where key = usertoken127.0.0.1:6379 > GET usertoken# output "my - token"

Check whether the key is usertoken127.0.0.1:6379 > EXISTS usertokenDelete the record where the key is usertoken127.0.0.1:6379 > DEL usertokenCopy the code

Connect and use in NodeJS

Mainly through the official library Node-Redis

import { createClient } from 'redis';

(async() = > {// Configure redis connection information
  const client = createClient({
    url: 'redis: / / 127.0.0.1:6379'
  });

  // Listen for redis error
  client.on('error'.(err) = > console.log('Redis Client Error', err));

  / / connect to redis
  await client.connect();

  / / set the key
  await client.set('key'.'value');
  // Get the value of the key
  const value = await client.get('key'); }) ();Copy the code

Postman

Once the interface is written, we can use Postman to simulate requests and debug the interface. $. Ajax, AXIos and other request libraries:

  1. Select the request method first. There are 15 request methods, the most common being GET and POST.
  2. Set the REQUEST URL, request parameters (including Query String and Request Body), and request header according to the input box on the interface. Note that user information needs to be carried by cookie or request header token.
  3. Click Send to initiate the request and view the result.

Through the test of the interface, you can request the way, written interface document for the client to call.

Six, the front-end need to understand the back-end knowledge, computer network knowledge and derivative interview questions