This is the 27th day of my participation in the August Genwen Challenge.More challenges in August

1, MySQL installation

Website: www.mysql.com/

2. Persistence of application data

Data (variables) written to an application program are created and stored in memory while the program is running and destroyed when the program’s process exits. So we need persistent storage for data that needs to be kept for a long time, such as in hard disk files.

Use simple text to persist data

For small amounts of data that are simple and don’t require much complex manipulation, we can use things like XML, JSON, YML… Such formats are stored in a text file.

// C3-0-1 users.json[{"id": 1."username": "Kobe Bryant"
  },
  {
    "id": 2."username": "James"}]Copy the code

4. Use MYSQL for data persistence

If the data volume is large, more data manipulation needs (find, sort…) At this time, we need to use some professional data management software (database) to store these data.

  • MySQL
  • Oracle
  • MSSQL
  • Redis
  • MongoDB
  • SQLLite
  • …

5. Introduction to basic concepts of mysql

5-1, database (MySQL) terminology

C/S

MySQL uses C/S mode (client/server). The client initiates a connection request to the MySQL server to complete various operations on the database data.

structure

'XX database ' = {
  "User table": [ // A collection of records with the same properties
    / / record
    {
      // Field: id, value: 1
      "id": 1.// username, value: "DaHai"
      "username": "DaHai"
    },
    {
      "id": 2."username": "zMouse"}]."Merchandise List": [],... }Copy the code

5-2, SQL

Structured Query Language (SQL) : a Language used by users to manage databases (such as inserting, updating, querying, deleting data, and creating databases and table structures).

In the 5-2-1 s, classification,

Data Definition Language (DDL)

Database structure operations: CREATE TABLE, DROP TABLE.

DQL: Data Query Language:

Data query: SELECT.

Data Manipulation Language (DML)

Data operations: INSERT, UPDATE, DELETE.

Transaction Control Language (TCL)

Transaction control: COMMIT, ROLLBACK.

Data Control Language (DCL) :

The database uses user roles: GRANT, DENY, and REVOKE.

6. Connect to mysql in Node.js

  • www.npmjs.com/package/@my…
  • www.npmjs.com/package/mys…

6-1, Node MySQL 2

In the 6-1-1 s, installation,

npm i mysql2
Copy the code

The callback version

const mysql = require('mysql2');
Copy the code

Based on the Promise version

const mysql = require('mysql2/promise');
Copy the code

The 6-1-2, link,

const connection = await mysql.createConnection({
  host: 'localhost'.user: 'root'.database: 'test'
});
Copy the code

7. Common mysql application scenarios

7-1. Add data

MySQL > INSERT INTO table (s);

INSERT INTOTable name (field name 1, field name 2,... 'field name N')VALUES(' one ', 'two',... N ` ` value);Copy the code

Tips:

  • SQL keywords (e.g., SELECT, INTO, etc.)

    • INSERT INTO `users` (`from`) VALUES ('1');
//equivalentinsert into `users` (`from`) values ('1');
Copy the code

  • You are advised to use ‘ ‘to reference field names and table names to avoid errors caused by SQL keywords in field names and table names:

    • INSERT INTO users (from) VALUES ('1');	//❎,fromFor `SQL` keywordINSERT INTO users (`from`) VALUES ('1');	// ✅
Copy the code

7-2. Query data

MySQL uses the SELECT statement to query data from the specified table:

SELECT'Field name one', 'field name two'... 'field name N'FROM'table name' [WHERE[condition]ORDER BY'Field name one' [ASC | DESC]],... [' field name N '[ASC | DESC] [LIMIT number] [OFFSETThe number of]Copy the code

Tips:

  • SELECT * ...; In the*Represents a wildcard character for all fields in the table, but is not recommended (performance, ambiguity, unintuitive…). .
  • []Indicates that it is optional.

7-2-1, where clause

Use the WHERE clause to query data conditionally.

The operator describe Use cases
= Is equal to the SELECT * FROM ‘users’ WHERE’ gender ‘=’ male ‘;
The < > and! = Is not equal to SELECT * FROM `users` WHERE `gender` = ”;
> Is greater than SELECT * FROM `users` WHERE `age` > 20;
< Less than SELECT * FROM `users` WHERE `age` < 20;
> = Greater than or equal to SELECT * FROM `users` WHERE `age` >= 20;
< = Less than or equal to SELECT * FROM `users` WHERE `age` <= 20;

7-2-2. Combination conditions

You can also specify multiple conditions using AND AND OR.

The name of the describe Use cases
AND and SELECT * FROM ‘users’ WHERE’ gender ‘=’ male ‘AND’ age ‘> 20;
OR or SELECT * FROM `users` WHERE `age` < 10 OR `age` > 20;

In the 7-2-3 s, sorting,

Use ORDER BY to sort the queried data according to the specified rules.

The name of the describe Use cases
ASC Ascending order (from small to large), default SELECT * FROM `users` WHERE `age` > 20 ORDER BY `age` ASC;
DESC Descending order (from most to least) SELECT * FROM `users` WHERE `age` > 20 ORDER BY `age` DESC;
ASC, DESC combination SELECT * FROM `users` WHERE `age` > 20 ORDER BY `age` DESC, `id` ASC;

7-2-4. Limit the position and number of queries

Use limit and offset to limit the maximum number of query records and the starting position.

The name of the describe Use cases
LIMIT n The largest article n. SELECT * FROM `users` WHERE `age` > 20 ORDER BY `age` ASC LIMIT 1;
LIMIT n OFFSET m Query n entries from m (LIMIT first, OFFSET last) SELECT * FROM `users` WHERE `age` > 20 ORDER BY `age` DESC LIMIT 1 OFFSET 5;
LIMIT m,n Query n entries from m (OFFSET first, LIMIT second) SELECT * FROM ‘users’ WHERE’ age ‘> 20 ORDER BY’ age ‘DESC LIMIT 5,1;

Tips:

OFFSET indicates the OFFSET (skipped or ignored). The default value is 0.

7-3. Update data

MySQL uses the UPDATE statement to UPDATE data from the specified table:

UPDATE 'table name'SET'field name one'=Field value one... 'field name N'=Field value N [WHEREConditions]Copy the code

Tips:

Updates strongly recommend using WHERE conditions for constraints, even if unconditional, to avoid ignoring the resulting full update.

7-4. Delete data

MySQL deletes data from a specified table using the DELETE statement:

DELETE
FROM'table name' [WHEREConditions]Copy the code

Tips:

Delete It is strongly recommended to use where conditions for constraints, even if unconditional, to avoid full deletes resulting from neglect.

7-5, and function

MySQL also has a number of built-in functions:

Reference: dev.mysql.com/doc/refman/…