This is the 15th day of my participation in the August More Text Challenge

This is the first time I’ve written a series of articles. It’s also the first time I’ve written a series of articles in a row. Although it’s not very good, it’s probably not worth a mention in the eyes of some of the big guys, but these articles have given me some good habits. I found myself sticking to the habit of slowly like more text, in this more text also know a lot of digging friends, before many times have not a adhere to the halfway down. I hope you can also try to develop a good habit in your work and life that will benefit you for life. Everybody continues to follow liver bar on the road of own development, mutual encouragement. Come on! Aoli give!!

Relational database

Relational database: a database that uses a relational model to organize data.

A relational model is a two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and their relationships.

1.1 Introduction to Relational Databases

Example: Create a data table that stores the student’s basic information (student id, name, age, etc.) and the student’s test scores for each subject.

The form of a table

Disadvantages: Too much duplicate data (data redundancy) 2.

Use multiple tables to store data together.

Key points: the correspondence of fields

Sno in sc must correspond to sNO in student. The value of SC. Sno must be present in student

The CNO in the SC table must correspond to the CNO in the course table. The value of SC. cno must exist in the course

Disadvantages: Multiple tables Advantages: Low data coupling Each data table can be independently managed

1.2 Relationship Model

Create student and DEPT tables to store information about students, colleges, and colleges to which students belong.

Student: Student table with required fields student ID, name, gender, age, department

Dept: Department table, required field number system name

Data manipulation

2.1 Multi-table Query

Keywords: Join on

Format:

Select * from table 1

Join table 2 on link conditions

The link condition must be that a field in Table 1 = a field in Table 2

Case 1: Query the student’s basic information and display the department name

Select * from student dept; select * from student dept; select * from student dept;

Table 1 Student (select * from student; select * from student)

Dept (table 2 has 2 fields, only 1 field is displayed)

Fields: all fields of student, dept.dname

Student.sdept =dept.did

select student. *, dept.dname from student
join dept on student.sdept = dept.did
Copy the code

2.2 Adding Data

Insert into table name (1, 2,….) Values (Value 1, value 2,….)

Note: the order of the fields must match exactly the order of the values. Self-growing primary keys can be filled with NULL; MySQL will automatically fill the table with data. If each column has data, the table name can not be followed by the column name, but values must be in the correct order.

Example: Add a data entry to the student table

insert into student (sno,sname,snickname,sage,sgender,stime,sdept)
values(null.King's Sledgehammer.'car'.10000.'male'.'2020-02-20'.3)
Copy the code

2.3 Modifying Data

Format:

Update table_name set table_name = 1, table_name = 2,… Where modify condition

Alter table which (several) data field 1= value 1…

Case: Change the name of student no. 6 to Wang and gender to female

update student set sname='the king',sgender='woman'
where sno=6
Copy the code

2.4 Deleting Data

Format: delete from table name where delete condition

Example: Delete the information of the student whose student id is 4

delete from student where sno=4
Copy the code

Mysql module is used in Node

3.1 Functions of the mysql module

The mysql module is a third-party module designed to operate the mysql database. You can perform add, delete, change, and check operations.

npm i mysql
Copy the code

3.2 Basic Usage of mysql

There are 5 steps to using the MySQL module in Node:

Loading MySQL module
Create a MySQL link object
Connect to MySQL server
Execute SQL statement
Close links

1. Load the MySQL module
const mysql = require('mysql')

//2. Create a MySQL link
const conn = mysql.createConntion({
    host: '127.0.0.1'.// MySQL server address
    user: 'root'./ / user name
    password: 'root'.// The password corresponding to the user name
    database: 'study'.// The database to use
    port: 3306              // MySQL server port number, 3006 is not required
})

//3. Connect to MySQL server
conn.connect();

//4. Execute the SQL statement
// Parameter 1: the SQL statement to execute
// Parameter 2: parameter in the SQL statement. It is used to set placeholders if there are placeholders. It is optional
// Parameter 3: the callback function triggered after SQL execution is complete
// Parameter 1: error object
// Parameter 2: SQL execution result
conn.query(sql, (err, result) = > {
    // Handle errors or execution results
})

// close the link
conn.end()
Copy the code

3.3 Query — read

Execute a query type SQL statement where the result is an array, each cell is an object, and the attributes of the object are the field names of the table

Mysql > select student from student
// Display to terminal

1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.// Specify the address of the MySQL server
    user: 'root'.// User name of MySQL server
    password: 'root'.// The password corresponding to the user name
    database: 'study'.// Specify the database to use
    port: 3306           // Specify the port of the MySQL server, if the default port can not be written
});

//3. Link database
conn.connect();

//4. Execute the SQL statement
const sql = "select * from student";
/** * Function: execute SQL statement * Parameter 1: SQL statement to execute * Parameter 2: use this parameter to set placeholders when SQL statement has placeholders. Optional * parameter 3: Callback function triggered after SQL statement execution is complete. There are three parameters * err: If the SQL statement fails to be executed, information about the error object is saved in err. If the SQL statement succeeds, information about the error object is null * result: execution result of the SQL statement * fields: information about fields involved in the execution of the SQL statement is generally not */
// When the query is executed, the query result is recorded as an array.
// Each cell in the array is an object, and the subscript is the field name of the data table
conn.query(sql, (err, result, fields) = > {
    console.log(err);     // Error object
    console.log(result);  //SQL execution result
    console.log(fields);
})

// Close the link
conn.end();
Copy the code

Execution Result:

Placeholder mode:

When a placeholder is used in an SQL statement, the query method uses parameter 2

// Target: placeholders in query statements

1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.user: 'root'.password: 'root'.database: 'study'
})

//3. Link database
conn.connect();

//4. Execute the SQL statement
// Query information about all female students
// In the SQL statement? They're placeholders
const sql = "select * from student where sgender=?";
conn.query(sql, 'woman'.(err, result) = > {
    if (err) {
        return console.log(err);
    }

    result.forEach(function (item, index) {
        console.log(item); })})// close the link
conn.end();
Copy the code

3.4 Adding a vm — create

When you execute an SQL statement to add a type, the result of the query is an object that has two properties to remember

AffectedRows: number of affectedRows
InsertID: primary key of queried data

// Target: add data to student table
1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.user: 'root'.password: 'root'.database: 'study'
})

//3. Link database
conn.connect();

//4. Execute the SQL statement
const sql = 'insert into sc values(null, 'atuo', 'atuo',' male ', '2019-02-18', 1);
// When the add is performed, result is an object containing two key attributes
// affectedRows: number of affectedRows
// insertID: primary key of newly added data
//
conn.query(sql, (err, result) = > {
    if (err) {
        return console.log(err);
    }

    console.log(result);
})

// close the link
conn.end();
Copy the code

Execution Result:

Placeholder form: When data is added, a placeholder requires an object. The properties of the object are the name of the data table field and the value is the data to be written to the data table

// Target: add data to student table
1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.user: 'root'.password: 'root'.database: 'study'
})

//3. Link database
conn.connect();

//4. Execute the SQL statement
const sql = `insert into student set ? `;
// When adding data, use a placeholder that needs to be filled with an object
// The subscript of this object must be the field name of the data table
// The primary key with self-growth can be set without setting, because the system will set it automatically
const student_data = {
    sname: '蕾娜'.snickname: 'lena'.sage: 1000.sgender: 'woman'.stime: '2019-02-18'.sdept: 2
}

conn.query(sql, student_data, (err, result) = > {
    if (err) {
        return console.log(err);
    }

    console.log(result);
})

// close the link
conn.end();
Copy the code

3.5 Modification — Update

The result is an object that has the affectedRows attribute

// Goal: Change Liu Chuang's nickname to LC and age to 30

1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.user: 'root'.password: 'root'.database: 'study'
})

//3. Link database
conn.connect();

//4. Execute the SQL statement
const sql = "update student set snickname='lc',sage=30 where sno=5";
conn.query(sql, (err, result) = > {
    if (err) {
        return console.log(err);
    }

    console.log(result);
})

// close the link
conn.end();
Copy the code

Placeholder mode:

Data modification usually requires two placeholders. Placeholder 1 is the data to be modified, in the form of an object, and the properties are data table fields; Placeholder 2 is the modification condition, usually the primary key value.

// Goal: Change Liu Chuang's nickname to LC and age to 30

1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.user: 'root'.password: 'root'.database: 'study'
})

//3. Link database
conn.connect();

// placeholder mode:
const sql = "update student set ? where sno=?";
//set? Is the actual data to be modified and must be in object form
const obj = {
    snickname: 'chuangzi'.sage:28
}
const sno = 5;

When there are multiple placeholders in an SQL statement, parameter 2 needs to be in array form
// When the program parses, it populates the data in the order of the array to the corresponding placeholder position
conn.query(sql, [obj, sno], (err, result) = > {
    if (err) {
        return console.log(err);
    }

    console.log(result);
})


// close the link
conn.end();
Copy the code

3.6 Delete — delete

Execute a delete type SQL statement, and the result is an object with the affectedRows property in it.

Alter table student where sno=8

1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.user: 'root'.password: 'root'.database: 'study'
})

//3. Link database
conn.connect();

//4. Execute the SQL statement
const sql = 'delete from student where sno=8';
conn.query(sql, (err, result) = > {
    if (err) {
        return console.log(err);
    }

    console.log(result);
})

5. Close the connection
conn.end();
Copy the code

Placeholder mode:

Alter table student where sno=8

1. Load the mysql module
const mysql = require('mysql');

//2. Create a mysql link object
const conn = mysql.createConnection({
    host: '127.0.0.1'.user: 'root'.password: 'root'.database: 'study'
})

//3. Link database
conn.connect();

//4. Execute the SQL statement
const sql = 'delete from student where sno=? ';
conn.query(sql, 8.(err, result) = > {
    if (err) {
    return console.log(err);
    }

    console.log(result);
})


5. Close the connection
conn.end();
Copy the code

Past wonderful recommendation

Front-end performance optimization for actual combat

Talk about annoying regular expressions

Obtain file BLOB stream address to achieve the download function

Git Git

Easy to understand Git introduction

Git implements automatic push

How do I use Git in my work

Interview Recommendations

Front ten thousand literal classics – the foundation

Front swastika area – advanced section

More exciting details: personal homepage

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Take you to the pit Nodejs

Relational database

1.1 Introduction to Relational Databases

1.2 Relationship Model

Data manipulation

2.1 Multi-table Query

2.2 Adding Data

2.3 Modifying Data

2.4 Deleting Data

Mysql module is used in Node

3.1 Functions of the mysql module

3.2 Basic Usage of mysql

3.3 Query — read

3.4 Adding a vm — create

3.5 Modification — Update

3.6 Delete — delete

Past wonderful recommendation

Take you to the pit Nodejs

Relational database

1.1 Introduction to Relational Databases

1.2 Relationship Model

Data manipulation

2.1 Multi-table Query

2.2 Adding Data

2.3 Modifying Data

2.4 Deleting Data

Mysql module is used in Node

3.1 Functions of the mysql module

3.2 Basic Usage of mysql

3.3 Query — read

3.4 Adding a vm — create

3.5 Modification — Update

3.6 Delete — delete

Past wonderful recommendation

Related Posts

How do NextJS compare with create-React-app

Come to collect your dynamic Spring Festival Flutter version realization ~

How do I analyze memory leaks in Node.js