Introduction to the use of SQl database

Time:2022-8-13

mySql database

SQL is a standard computer language for accessing and manipulating databases.Provides a safety mechanism for easy accessSQL statementoperate directly on the data

MySQL is the most popular relational database management system, and MySQL is one of the best RDBMS (Relational Database Management System: relational database management system) application software in WEB application.

What is a database?

A database is a warehouse that organizes, stores and manages data according to the data structure.

Each database has one or more different APIs for creating, accessing, managing, searching, and replicating the data it holds.

We can also store data in files, but reading and writing data in files is relatively slow.

So, now we use relational database management systems (RDBMS) to store and manage large data volumes. The so-called relational database is a database based on the relational model, which uses mathematical concepts and methods such as set algebra to process the data in the database.

Features of RDBMS (Relational Database Management System):

  • 1. The data is presented in the form of a table
  • 2. Each row has various record names
  • 3. Each column is the data field corresponding to the record name
  • 4. Many rows and columns form a form
  • 5. Several forms form a database

The required software is:

MySQL service
navicat

RDBMS terminology

  • database:A database is a collection of related tables.
  • data sheet:A table is a matrix of data. A table in a database looks like a simple spreadsheet.
  • List:A column (data element) contains the same type of data, such as zip code data.
  • Row:A row (=tuple, or record) is a group of related data, such as a piece of data subscribed by a user.
  • redundancy: Stores twice the data, redundancy reduces performance but improves data security.
  • primary key: The primary key is unique. A data table can only contain one primary key. You can use the primary key to query data.
  • Foreign key:Foreign keys are used to relate two tables.
  • compound key: Composite key (composite key) uses multiple columns as an index key and is generally used for composite indexes.
  • index:Use indexes to quickly access specific information in database tables. An index is a structure that sorts the values ​​of one or more columns in a database table. A catalogue similar to a book.
  • Referential integrity:Referential integrity requires that no reference to a non-existent entity be allowed in a relationship. Entity integrity is an integrity constraint that a relational model must satisfy, in order to ensure data consistency.

  • header: the name of each column;
  • Column (col): a collection of data with the same data type;
  • Row: Each row is used to describe the specific information of a record;
  • Value (value): the specific information of the row, each value must be the same data type as the column;
  • key: The value of the key is unique within the current column.

 

Database Visualizernavicatbasic use of

  1. Open and close database connections

  2. Create users, set user permissions

  3. New database, new table, design table (set field type), set Id auto-increment, date auto-insert

  • Example:
  • newusersUser table, table fields are: id, name, age, gender, address, isdel

  • Id auto-increment: set as the primary key, checkauto increment

  • Primary key: It is used to identify the uniqueness of each row of data in the data table to ensure uniqueness.

 

Perform basic operations on database using Sql statements

Inquire :

1 -- The data queried by the query SQL statement is the result set
2 -- Syntax: select * from table name where condition 
3 -- && replace || with or ! replace with not
4 select * from stuInfo; -- means query all data information
5 select id,name,gender from stuinfo; -- query according to the specified field
6 select * from stuinfo where id = 2; -- query the piece of data with id 2
7 select name, age from stuinfo where id = 3; -- query the name age id in the data with id 3
8 select * from stuinfo where id =3 or id= 5; -- query the two data with id 3 and 5
9 select * from stuinfo where id in (2,3,4,5,7); -- query those data whose id is 2 3 4 5 7

 

 

Increase :

1 -- add statement
2 -- Syntax: insert [into] tablename(field1, field2...) values ​​(value1, value2...);
3 insert into stuinfo (id,name,gender,age,phone) values (NULL,'张三','男',20,'1302012345');
4 insert into stuinfo (name,age,gender) values ​​('Li Si',21,'male');
5 insert into stuinfo values ​​(null, "Wang Wu", "Male", 21); -- If you write the value directly, it must match the field, and it will report an error
6 insert into stuinfo values ​​(null, "Wang Wu", "Male", 21, "1234666"); -- When there is no field, if you want to add a value directly, it must correspond to the field, and cannot be missing

 

 

Revise :

-- modify the statement
-- Syntax: UPDATE table name set field 1 = value 1, field 2 = value 2... where condition 
UPDATE stuinfo set gender = 'male'; -- If there is no condition behind it, it means that the gender in all the data in the table is changed to male
UPDATE stuinfo set gender = 'female' WHERE id = 3; -- change the gender in the data with id 3 to "female"
UPDATE stuinfo set gender = 'female' WHERE age = 21 and name = 'Li Si'; -- name the data table "Li Si"

 

 

 

delete :

1 -- delete statement
2 -- Syntax: DELETE FROM table name where condition  
3 DELETE FROM stuinfo where id = 7; -- delete data with id=7
4 DELETE FROM stuinfo where id = 2 or id = 8; -- delete data with id=2 and id=8
5 DELETE FROM stuinfo where id in (4,5,10); -- delete data with id 4 5 10
6 DELETE FROM stuinfo WHERE gender ='male' AND age = 20; -- delete data through multiple conditions and the relationship expressed here 
7 DELETE FROM stuinfo WHERE id > 10; -- You can also delete data with an id greater than 10 according to the characteristics of the id
8 DELETE FROM stuinfo -- if no condition is written, all data in the library will be deleted

 

 

mySQL common functions:

COUNT()function

SELECT count(*) FROM stuinfo; -- count is used to count all the data, and it will be counted according to the parameters inside
SELECT count(id) FROM stuinfo; -- Count the number of specific data items based on id
SELECT count(phone) FROM stuinfo; -- Count the specific number according to the phone field, if the data is empty, it will be ignored

 

 

MAX, MIN, AVG functions

 

SELECT MAX(age) FROM stuinfo; -- Query the maximum value in age
SELECT MIN(age) FROM stuinfo; -- Query the minimum value in age
SELECT avg(age) FROM stuinfo; -- query the average in age average

 

 

order by function

1 SELECT * FROM stuinfo order by id; -- order by is used for sorting, the default is to sort in ascending order asc is ascending order
2 SELECT * FROM stuinfo ORDER BY id desc; 
3 SELECT * FROM stuinfo ORDER BY age desc; -- desc is in descending order

 

 

limit function: mainly used for paging occasions

1 SELECT * FROM stuinfo ORDER BY id;
 2 select * from stuinfo limit 3; -- get the first 3 items in the result set
 3 SELECT * FROM stuinfo  ORDER BY id limit 3;
 4 
 5 -- limit n (index) m (quantity) The index of the data in the data table starts from 0 by default, and the index will only sort the remaining data
 6 SELECT * FROM stuinfo ORDER BY id LIMIT 3, 3;
 7 
 8 -- You can also implement a paging limit count (number) offset index (index);
 9 SELECT * FROM stuinfo ORDER BY id limit 3 offset 0; -- Query 3 items down from the position of index 0
10 SELECT * FROM stuinfo ORDER BY id limit 3 offset 3; -- Query 3 items down from the position with index 3
11 SELECT * FROM stuinfo ORDER BY id limit 3 offset 6; -- Query 3 items down from the position of index 6
12 
13 -- After the above rules can be summarized as follows:
14 int pageCount = 3 ; -- the number of bars displayed on each page
15 int pageSize = 1 ; -- current page number 1 page 1 page 2 page 2 page 3 page 3
16 
17  SELECT * FROM stuinfo ORDER BY id LIMIT pageCount offset (pageSize - 1)* pageCount;

Node.js connects to MySQL

install driver

Install in the project and directory:$ cnpm install mysql

Connect to the database

let mysql = require('mysql');
let connection = mysql.createPool({
  host     : 'localhost', // 
  user     : 'root',
  password : '123456',
  database : 'test'
});
 
connection.connect();
 
connection.query('SELECT 1 + 1 AS solution', function (error, results, fields) {
  if (error) throw error;
  console.log('The solution is: ', results[0].solution);
});

 

Description of database connection parameters:

 

Database Operations ( CURD )

Query data

var mysql  = require('mysql');  
 
var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test' 
}); 
 
connection.connect();
 
var  sql = 'SELECT * FROM websites';
//check
connection.query(sql,function (err, result) {
        if(err){
          console.log('[SELECT ERROR] - ',err.message);
          return;
        }
 
       console.log('--------------------------SELECT----------------------------');
       console.log(result);
       console.log('------------------------------------------------------------\n\n');  
});
 
connection.end();

insert data

var mysql  = require('mysql');  
 
var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test' 
}); 
 
connection.connect();
 
var  addSql = 'INSERT INTO websites(Id,name,url,alexa,country) VALUES(0,?,?,?,?)';
var addSqlParams = ['Rookie Tools', 'https://c.runoob.com','23453', 'CN'];
//increase
connection.query(addSql,addSqlParams,function (err, result) {
        if(err){
         console.log('[INSERT ERROR] - ',err.message);
         return;
        }        
 
       console.log('--------------------------INSERT----------------------------');
       //console.log('INSERT ID:',result.insertId);        
       console.log('INSERT ID:',result);        
       console.log('-----------------------------------------------------------------\n\n');  
});
 
connection.end();

update data

var mysql  = require('mysql');  
 
var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test' 
}); 
 
connection.connect();
 
var modSql = 'UPDATE websites SET name = ?,url = ? WHERE Id = ?';
var modSqlParams = ['Rookie Mobile Station', 'https://m.runoob.com',6];
//change
connection.query(modSql,modSqlParams,function (err, result) {
   if(err){
         console.log('[UPDATE ERROR] - ',err.message);
         return;
   }        
  console.log('--------------------------UPDATE----------------------------');
  console.log('UPDATE affectedRows',result.affectedRows);
  console.log('-----------------------------------------------------------------\n\n');
});
 
connection.end();

delete data

ar mysql  = require('mysql');  
 
var connection = mysql.createConnection({     
  host     : 'localhost',       
  user     : 'root',              
  password : '123456',       
  port: '3306',                   
  database: 'test' 
}); 
 
connection.connect();
 
var delSql = 'DELETE FROM websites where id=6';
//delete
connection.query(delSql,function (err, result) {
        if(err){
          console.log('[DELETE ERROR] - ',err.message);
          return;
        }        
 
       console.log('--------------------------DELETE----------------------------');
       console.log('DELETE affectedRows',result.affectedRows);
       console.log('-----------------------------------------------------------------\n\n');  
});
 
connection.end();

 

Recommended Today

It’s numb, the code is changed to multi-threaded, there are 9 major problems

foreword In many cases, in order to improve the performance of the interface, we willsingle thread synchronizationThe code to execute is changed toMulti-threaded asynchronousimplement. For example, the interface for querying user information needs to return basic user information, point information, and growth value information, while users, points, and growth value need to call different interfaces […]