1, Basic concepts of database

1. What is a database

database is a warehouse used to organize, store and manage data.

2. Common database and classification

Common databases include MySQL database, Oracle database (charging), SQL Server database (charging) and Mongodb database.

Among them, MySQL, Oracle and SQL Server belong to traditional database (also known as relational database), while Mongodb database belongs to new database (also known as non relational database), which makes up for the defects of traditional database to a certain extent.

2, Basic syntax of SQL statements

1. SELECT statement

select * from Table name

Where, * means to select all columns.

Or:

select Column name from Table name

Note: multiple column names are separated by English commas.

2. INSERT INTO statement

This statement is used to insert a new data row into the data table. Syntax format:

insert into table_name (Column 1,Column 2,...) values (Value 1,Value 2,...)

3. UPDATE statement

This statement is used to modify data in a table. Syntax format:

update Table name set Column name = New value where Column name = Certain value

4. DELETE statement

This statement is used to delete rows in a table. Syntax format:

delete from Table name where Column name = Certain value

5. WHERE clause

This statement can be used in query, update and delete operations.

6. AND and OR operators

AND means that multiple conditions are satisfied at the same time, while OR means that any condition can be satisfied.

Example:

select * from users where id < 3 and username = 'zs'

select * from users where id < 2 or username='tony stark'

7. ORDER BY clause

This statement is used to sort the result set according to the specified column. By default, the records are sorted in ascending order. To sort the records in descending order, you can use the DESC keyword.

Example:

-- Ascending order -- 
select * from users order by status
-- Descending order -- 
select * from users order by status desc

8. ORDER BY Clause - Multi sort

select * from users order by status desc, username asc
--asc Description is in ascending order--

9. COUNT(*) function

This function is used to return the total number of data pieces of the result. Syntax format:

select count(*) from Table name

10. Alias columns using AS

select count(*) AS total from users

III. operating MySQL in Express Project

1. Install MySQL module

MySQL module is a third-party module hosted on npm. It provides in node The ability to connect and operate MySQL database in JS project.

npm install mysql

2. Configure and test MySQL module

// 1. Import database module
const mysql = require('mysql')
// 2. Establish connection to MySQL database
const db = mysql.createPool({
  host:'127.0.0.1',      // IP address of the database
  user:'root',            // Account to log in to the database
  password:'admin123',   // Password to log in to the database
  database:'my_db_01'    // Specify the database to operate on
})

// 3. Test MySQL module
db.query('select 1', (err, results) => {
  // report errors
  if(err) return console.log(err.message)
  // Can execute sql statements normally
  console.log(results)
})

If the execution result is: [rowdatapacket {'1': 1}]

It indicates that the connection is successful.

3. Query data

// Query information
const sqlStr = 'select * from users'
db.query(sqlStr, (err, results) => {
  // Query failed
  if(err) return console.log(err.message)
  // query was successful
  console.log(results)
})

4. Insert data

// insert data
// Insert data into the users table: the user name is zhangwu and the password is abc321
const user = { username: 'zhangwu', password: 'abc321'}
// Define sql statements, in which "in English" is used? As placeholder
const sqlStr = 'insert into users (username, password) values (?, ?)'
db.query(sqlStr,[user.username, user.password], (err, results) => {
  // Failed to insert data
  if(err) return console.log(err.message)
  // Insert data successfully, where results is an object
  // You can judge whether the data insertion is successful by the affectedRows property in the object
  if(results.affectedRows === 1){
    console.log('Insert data successfully!')
  }
})

Note: a convenient way to insert data:

// A convenient way to insert data
const user = { username: 'zhangwu2', password: 'abc987'}
// Define sql statements
const sqlStr = 'insert into users set ?'
db.query(sqlStr,user, (err, results) => {
  if(err) return console.log(err.message)
  if(results.affectedRows === 1){
    console.log('Insert data successfully!')
  }
})

5. Update data

// Update data
const user = { id: 6, username: 'aaa', password: '000'}
// Define sql statements
const sqlStr = 'update users set username=?, password=? where id=?'
db.query(sqlStr, [user.username, user.password, user.id], (err, results) => {
  // Failed to update data
  if(err) return console.log(err.message)
  // Data update succeeded
  if(results.affectedRows === 1){
    console.log('Data update succeeded!')
  }
})

Note: a convenient way to update data:

// A convenient way to update data
const user = { id: 6, username: 'bbb', password: '111'}
// Define sql statement
const sqlStr = 'update users set ? where id=?'
db.query(sqlStr, [user, user.id], (err, results) => {
  if(err) return console.log(err.message)
  if(results.affectedRows === 1){
    console.log('Data update succeeded!')
  }
})

6. Delete data

// Delete data with id 5
// Define sql statements
const sqlStr = 'delete from users where id=?'
db.query(sqlStr, 5, (err, results) => {
  if(err) return console.log(err.message)
  if(results.affectedRows === 1){
    console.log('Data deleted successfully!')
  }
})

7. Mark deletion

Using the DELETE statement will really DELETE the data from the table. For the sake of insurance, the form of mark deletion can be used to simulate the deletion action. For example, when a user performs a DELETE operation and executes an UPDATE statement instead of a DELETE statement, mark the status field corresponding to this data as deleted.

// Mark deletion
const sqlStr = 'update users set status=? where id=?'
db.query(sqlStr, [1, 6], (err, results) => {
  if(err) return console.log(err.message)
  if(results.affectedRows === 1){
    console.log('Mark data deleted successfully!')
  }
})

4, Web development mode

1. Web development mode of server rendering

The concept of server-side rendering: the HTML page sent by the server to the client is dynamically generated through character splicing in the server. Therefore, the client does not need to use Ajax technology to request the data of the page.

Advantages: less time-consuming front-end; Conducive to SEO.

Disadvantages: occupying server resources; It is not conducive to the separation of front and rear ends, and the development efficiency is low.

2. Web development mode with front end and back end separation

The back end is only responsible for providing API interfaces, and the front end uses the development mode of Ajax calling interfaces.

Advantages: good development experience; Good user experience; It reduces the rendering pressure on the server.

Disadvantages: not conducive to SEO.

5, Session authentication mechanism

1. What is identity authentication?

Identity authentication refers to the confirmation of user identity through certain means. For example, mobile phone verification code authentication, etc.

2. Identity authentication under different development modes

Session authentication mechanism is recommended for server-side rendering; JWT authentication mechanism is recommended for front and rear end separation.

3. Principle of Session authentication mechanism

A Cookie is a string of no more than 4KB stored in the user's browser. It consists of a name, a value and several other optional attributes used to control the validity, security and scope of use of cookies. Cookies have four features: automatic sending, domain name independence, expiration time limit and 4 KB limit. Cookies under different domain names are independent. Whenever the client initiates a request, it will automatically send all unexpired cookies under the current domain name to the server.

When the client requests the server for the first time, the server sends an authentication Cookie to the client in the form of response header, and the client will automatically save the Cookie in the browser. Then, when the client browser requests the server every time, the browser will automatically send the Cookie related to identity authentication to the server in the form of request header, and the server can verify the identity of the client.

Because cookies are stored in the browser, and the browser also provides an API for reading and writing cookies, cookies are easy to be forged and have no security. Therefore, it is not recommended that the server send important privacy data to the browser in the form of cookies.

4. Using Session authentication in Express

(1) Install express session Middleware

npm install express-session

(2) Configure middleware:

// Import express module
const express = require('express')
// Create a server instance of express
const app = express()

// Configuring Session Middleware
const session = require('express-session')
app.use(
  session({
    secret: 'keybord cat',           //The value of the secret property can be any string
    resave: false,                   //Fixed writing
    saveUninitialized: true,         //Fixed writing
  })
)

(3) Save data to session

After the express session middleware is successfully configured, you can use req Session to access and use session objects to store key user information.

// Login API interface
app.post('/api/login', (req, res) => {
  // Judge whether the login information submitted by the user is correct
  if (req.body.username !== 'admin' || req.body.password !== '000000') {
    return res.send({ status: 1, msg: 'Login failed' })
  }

  // Save the user information after successful login to the Session
  // Note: only after the middleware express session is successfully configured can the session attribute be listed through req
  req.session.user = req.body // User information
  req.session.islogin = true // User login status

  res.send({ status: 0, msg: 'Login succeeded' })
})

(4) Fetch data from session

// Interface to get user name
app.get('/api/username', (req, res) => {
  // Get the user's name from the Session and respond to the client
  if (!req.session.islogin) {
    return res.send({ status: 1, msg: 'fail' })
  }
  res.send({
    status: 0,
    msg: 'success',
    username: req.session.user.username,
  })
})

(5) Empty session

// Exit the logged in interface
app.post('/api/logout', (req, res) => {
  // TODO_04: clear Session information
  req.session.destroy()
  res.send({
    status: 0,
    msg: 'Log out successfully',
  })
})

(6) Limitations of session authentication

Session authentication mechanism can only be implemented with Cookie. Since cookies do not support cross domain access by default, when it comes to the front-end cross domain request back-end interface, many additional configurations need to be made to realize cross domain session authentication.

6, JWT authentication mechanism

JWT is the most popular cross domain authentication solution. JWT usually consists of three parts: header, Payload and Signature. English "." is used between the three separate. Among them, the Payload} part is the real user information, which is the string generated after the user information is encrypted. Header and Signature are security related parts, just to ensure the security of Token.

1. How JWT is used

After the client receives the JWT returned by the server, it usually stores it in localStorage or sessionStorage. After that, every time the client communicates with the server, it should bring the JWT string for identity authentication. The recommended practice is to put the JWT in the Authorization field of the HTTP request header.

2. Using JWT in Express

1. Install two JWT related packages:

npm install jsonwebtoken express-jwt

jsonwebtoken is used to generate JWT strings; Express JWT is used to parse JWT strings and restore them to JSON objects

2. Import JWT related packages

// Import express module
const express = require('express')
// Create a server instance of express
const app = express()

// Install and import two JWT related packages, JSON web token and express JWT
const jwt = require('jsonwebtoken')
const expressJWT = require('express-jwt')

// Call app Listen method, specify the port number and start the web server
app.listen(8888, function () {
  console.log('Express server running at http://127.0.0.1:8888')
})

3. Define secret key

When generating the JWT string, you need to use the secret key to encrypt the user's information, and finally get the encrypted JWT string. When the JWT string is parsed and restored to a JSON object, the secret key needs to be used for decryption.

// Define the secret key. It is recommended to name the key as secret key
const secretKey = 'hahahaha No1 ^_^'

// Register middleware that restores JWT string parsing to JSON objects
// Note: once the middleware express JWT is successfully configured, the parsed user information can be attached to req On the user attribute
app.use(expressJWT({ secret: secretKey }).unless({ path: [/^\/api\//] }))

4. Generate JWT string after successful login

// Login interface
app.post('/api/login', function (req, res) {
  // Put req The data in the body request body is transferred to the userinfo constant
  const userinfo = req.body
  // Login failed
  if (userinfo.username !== 'admin' || userinfo.password !== '000000') {
    return res.send({
      status: 400,
      message: 'Login failed!',
    })
  }
  // Login succeeded
  // After login is successful, call jwt.. The sign () method generates a JWT string. And send it to the client through the token attribute
  // Parameter 1: user information object
  // Parameter 2: encrypted secret key
  // Parameter 3: configure the object to configure the validity period of the current token
  // Remember: never encrypt the password into the token character
  const tokenStr = jwt.sign({ username: userinfo.username }, secretKey, { expiresIn: '30s' })
  res.send({
    status: 200,
    message: 'Login succeeded!',
    token: tokenStr, // The token string to send to the client
  })
})

5. Use req User get user information

After the express JWT middleware is successfully configured, you can use req User object to access the user information parsed from the JWT string.

// This is a privileged API interface
app.get('/admin/getinfo', function (req, res) {
  // Use req User gets the user information and sends the user information to the client using the data attribute
  console.log(req.user)
  res.send({
    status: 200,
    message: 'User information obtained successfully!',
    data: req.user, // User information to send to the client
  })
})

6. Capture the error generated after parsing JWT failed

When using express JWT to parse the Token string, if the Token string sent by the client is expired or illegal, a parsing error will be generated.

// Use the global error processing middleware to capture the errors generated after the failure of parsing JWT
app.use((err, req, res, next) => {
  // This error is caused by the failure of token parsing
  if (err.name === 'UnauthorizedError') {
    return res.send({
      status: 401,
      message: 'invalid token',
    })
  }
  res.send({
    status: 500,
    message: 'unknown error',
  })
})

......