SQL database

What is a database

A repository for storing and managing data is a database

What is data? Files, pictures, videos, orders, user names, passwords, etc

All this data requires a dedicated place to store deterioration management

Data storage flow diagram:

2. Two camps – database classification

  • Relational data flow library

  • MySQL

  • Oracle

  • Sql server

  • DB2

  • Non-relational databases

    • Redis key and value store database

    • HBaise column stores the database

    • Mongodb is document database oriented

    • Neo4j graphics database

    • Elasticsearch Search engine storage

      Reference: database usage ranking db-engines.com/en/ranking

3. Understand relational databases

In a relational database, there is a three-level relationship:

The database

The data table

field

The analogy of excel

  • Each column is a type of data — a field
  • Each row represents a piece of data — a record

4. Introduction of MySQL

  • Mysql > install mysql

    • The official website: www.mysql.com/downloads/

  • Integrated installation

    Since mysql, Apache, and PHP are classic developer partners, there are many integrated environments out there (a large piece of software that has all three already configured) that we can use as well. Advantage is: basically do not need to configure, use more convenient. Such integrated environments are:

    • wampserver

    • phpstudy

    • appserv

Take PHPStudy as an example, go to the official website to download and install it. It’s worth noting that when we install MySQL, our computer becomes a server again, but not a Web server, but a MySQL database server.

4.1 Learn to use SQL statements (Add, Delete, Modify, And Search)

Structured Query Language, or SQL, is used to manipulate relational databases:

  • A database query and programming language for accessing data and querying, updating, and managing relational databases.
  • .sql is the extension of the database script file

There are four types of SQL statements that are most commonly used for data operations:

Create for example: user registration

Delete For example, delete an order

For example, change the password

Search (select,read) for example: Information search

4.2 Learning SQL Commands

Add data command:

Format :insert into values(1, 2,…….) Note:

  • The order of the fields should match exactly the order of the values

  • For a string field, the value is appended with “”. For a numeric string, the value is not appended with “”.

Example:

Copy the code

Delete command delete

Format:

Copy the code

Note: Not specifying a condition will delete all data instances

Delete from stu where id=14 delete from stu where id=14Copy the code

Modify data -update

Select * from table_name where table_name = 1 and table_name = 1 where table_name = 1 and table_name = 1

The values to be modified are represented using key-value pairs

Multiple fields, to be split

If no condition is specified, all records in the current table are modified

Example:

Update stu set age=53 where id=1 update stu set age=53 where id=1 update stu set age=35 where id= "130 Id =1 // All data will be modified if no condition is specified. Update stu set age = 60Copy the code

Data query -select

Function: To retrieve data from a database

Format:

Select field name, 2,..... From table name where condition expressionCopy the code

Example:

Select id,name,phone from stu where id,name,phone from stu where id,name,phone from stu where id,name,phone from stu where id,name,phone from stuCopy the code

The where clause

Select * from stu where id, name, Select * from stu where sex=' male 'select * from stu where sex=' male' Select * from stu where age > 50 select * from stu where age > 50 select * from stu where age > 50 select * from stu where age > 50 select * from stu where age > 50 Select * from stu where age>50 and sex=' male ' Select * from stu where age>=30 and age<=60 select * from stu where age between 30 and 60Copy the code

All instances of SQL code

#insert into STu (name,age,phone) values(' stu ',19,'130358') #insert into STu (name,age,phone) values Stu (name) values(' stu ') #delete from stu where age=42 #delete from stu where age=42 # #update stu set phone='130358' where name=' stu '#update stu set name=' linziyu ', age=21, Select id,name,phone from stu where id ='14544544' where id ='14544544' #select * from stu where age=17 #select * from stu Select * from stu where age>15 Select * from stu where age=17 or age=21 select * from stu where age>10 and select * from stu where age>10 and Name =' Linda 'Copy the code

5. Operate the database in Node. js

Use the mysql package to operate mysql database

5.1 the installation package

The mysql module is a third-party module that operates on databases

NPM install mysqlCopy the code

5.2 Procedure:

To connect to the database with this package, your computer must first have MQSQL and other accompanying PHPStudy.

Steps: Three steps

Const = require(” MySQL “) 2 Var connection = mysql.createconnection ({host: ‘localhost’, // address of the database server to which you are trying to connect port: 3306,// port number user: Database: ‘gz61’ // the name of the database you want to connect to}); Connection.connect ((err) => {// If there are error objects, If (err) return console.log(‘ database connection failed ‘) // No error object indicating connection success console.log(‘mysql database connection succeeded ‘)}); The code is as follows:

var mysql = require('mysql'); Var connection = mysql.createconnection ({host: 'localhost', // address of the database server to which you want to connect port: 3306,// port number user: Database: 'gz61' // the name of the database you want to connect to}); Connection.connect ((err) => {// If there are error objects, If (err) return console.log(' database connection failed ') // No error object indicating connection success console.log('mysql database connection succeeded ')});Copy the code

5.3 Exported SQL

Var MySQL = require(' MySQL '); Var connection = mysql.createconnection ({host: 'localhost', // address of the database server to which you are trying to connect port: 3306,// port number user: 'root', // user name to connect to database server password: 'root', // password to connect to database server database: 'hreo' // name of the database you want to connect to}); Connection.connect ((err) => {// If there is an error object, If (err) return console.log(' database connection failed ') // No error object indicating connection success console.log('mysql database connection succeeded ')}); // module.exports = connection; module.exports = connection;Copy the code

Operation statement: add delete change check

Query operation

The onnection.query() method executes SQL statements

Since the above and write create SQL and so on, here directly demonstrates the execution of SQL statements

Const conn = require("/uilt/ SQL ") // execute SQL statement const sqlStr = 'select ID,name,age from table name / / conn. Query (sqlStr, correct mistakes, = > {}) conn. Query (sqlStr, (err, result) = > {the console. The log (err) / / print error If (err){res.json(' error ')} res.json(' return data ') // Return total object to front-end})Copy the code

The advanced version:

Actual development:

Receive the user name registered by the user, according to the requirements to check whether the name is occupied, if the result is found, it means occupied, if the query result is empty, it means the name can be used.

Request internal three steps:

1. Obtain parameters based on the request type

2. Join SQL statements

3. Return different data according to different SQL conditions

Post ('/register', (req, res) => {console.log(req.body); const { userName, userPwd } = req.body; // 2. Please check the name of the registered business. // // perform a query according to the user name. If the query result is found, the name is occupied. If the query result is empty, Const sqlStrselect = 'select username from user where username="${username}"' Conn.query (sqlStrselect, (err, result) => {if (err) {res.json({MSG: "server error ", code: 500}) return} // console.log(" This is :", result); If (result.length > 0) {res.json({MSG: 'registration failed, user name occupied ', code: 201}) return} const sqlStr = 'insert into user(username,password) Values ("${userName}","${userPwd}") '// Return conn.query(sqlStr, (err, result) => {if (err) {console.log(err); Res. json({MSG: 'server error ', code: 500}) return; } res.json({MSG: "register successfully ", code: 200})})})Copy the code
Add operation

The result of the query is an object that has two properties to focus on:

  • AffectedRows: number of affectedRows
  • InsertID: primary key of queried data
  • Const sqlStr =insert into heros(name,gender) values("${name}","${gender}")
const conn = require('.. /util/ SQL ') router. Post ('/addHero', (req, res) => {const {name, gender} = req.body; // sqlStr const sqlStr = 'insert into heros(name,gender) values("${name}","${gender}")' conn.query(sqlStr, (err, Result) => {// Return parameter if (err) {console.log(err); Res. json({MSG :' add failed ', code: 500})} else {console.log(' add succeeded :', result) res.json({MSG :' add succeeded ', code: 200, data: result }) } }) })Copy the code
Modify the operating

The query result is an object with the affectedRows attribute, which indicates the number of rows affected by the change operation.

const conn = require('.. /util/sql') router.post('/updataHero', (req, res) => { // console.log(req.body); Const {id, name, gender, img} = req.body; let condition = []; If (name) {condition. Push (' name="${name}" ')} if (gender) {// If (name) {condition. Push (' name=" condition}" ')} if (gender) { condition.push(`gender="${gender}""`) } if (img) { condition.push(`img="${img}"`) } // console.log(condtion); const conditionStr = condition.join(); Const sqlStr = 'update heros set ${conditionStr} where id=${id}' conn.query(sqlStr, (err, The result) = > {the if (err) {res. Json ({MSG: "correct mistakes", code: 500})} res. Json ({code: 200, MSG: 'success'})})})Copy the code
delete

Row delete type SQL statement, the query result is an object, this object has the affectedRows property

Hard to delete:

// 1. Mysql const mysql = require('mysql'); Const conn = mysql.createconnection ({// The attribute name of the object cannot be changed host: 'localhost', port: 3306, user: 'root', password: 'root', database: 'gz61' }); Connection.connect ((err) => {// If there is an error object, If (err) return console.log(' database connection failed ') // No error object indicating connection success console.log('mysql database connection succeeded ')}); SQL = 'delete from stu where id=1'; connection.query(sql,(err, result) => { if (err) throw err; If (result.affectedrows > 0) {console.log(' delete succeeded '); } else {console.log(' delete failed '); }});Copy the code

Soft delete: Set isdelete to 1

Router.get ('/delHeroById', (req, res) => {const {id} = req.query; console.log(id); Const sqlStr = 'update heros set ISDELETE =1 WHERE ID =${id}' conn.query(sqlStr, (err, Result) => {if (err) {res.json({MSG: "server error ", code: 500})} if (result.changedRows === 1) {res.json({MSG:" server error ", code: 500})} if (result.changedRows === 1) {res.json({MSG: "Delete successful ", code: 200})} else {res.json({MSG:" delete failed parameter error ", code: 201})}})Copy the code

6 Modular Encapsulation

Several functions have the same syntax format and all perform the same thing, so we can wrap it and export it from modult.exports

Encapsulate database, because write interface, need a lot of use, improve code reuse

/ / because of the four (insert, delete, update, select) operation is different SQL statements / / 1. Const mysql = require('mysql'); Const conn = mysql.createconnection ({// The attribute name of the object cannot be changed host: 'localhost', port: 3306, user: 'root', password: 'root', database: 'gz61' }); Connection.connect ((err) => {// If there is an error object, If (err) return console.log(' database connection failed ') // No error object indicating connection success console.log('mysql database connection succeeded ')}); module.exports = connectionCopy the code

After wrapping the export, import it from another page

Call the module

Const XXX = require(” module name “)

SQL > create table SQL > create table SQL > create table SQL > create table SQL > create table SQL