This is the 10th day of my participation in Gwen Challenge

Today we will start to learn database operation, and then take the previous fake data from the database.

MySQL installation

Website address: dev.mysql.com/downloads/i…

MySQL specific installation can be reference: zhuanlan.zhihu.com/p/37152572

Then install MySQL Workbench

Website address: dev.mysql.com/downloads/w…

With workbench installed, click + to connect to the database

The MySQL database has been installed successfully

SQL commands

The following simple write the SQL command used to achieve the increase, delete, change and check

use myblog;
-- show tables;
Insert -
insert into users (username,`password`,realname) values ('emier'.'666'.Star of the Sky);
- the query
select * from users;
-- Query some field data
select id, username from users;

-- Query the data that matches the conditions
select * from users where username='emier' and `password`='666';
select * from users where username='emier' or `password`='666';
-- Fuzzy query (like: if exists)
select * from users where username like '%mier%';

Sort -
select * from users where username like '%mier%' order by id;
- reverse
select * from users where username like '%mier%' order by id desc;

Update -
-- update table_name set table_name =' XXX 'WHERE table_name =' XXX
update users set realname='itmier' where username='emier';

Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
SET SQL_SAFE_UPDATES=0;

- delete
delete from users where username='emier';
-- Usually not delete, just update status
update users set state='0' where username='emier'; - soft delete

select * from users where state='1';
- is not equal to
select * from users where state <> '0';

Insert data
insert into blogs (title, content, createtime, author) values ('shock! The title D '.'I am content, content is King DD'.1623336080690.'tmier');
select * from blogs;
select * from blogs order by createtime desc;
select * from blogs where author='tmier' order by createtime desc;
select * from blogs where title like '% D % title' order by createtime desc;
-- Query version
select version()
Copy the code

SQL operation with NodeJS

index.js

npm i mysql
Copy the code
const mysql = require('mysql')

// Create a link object
const con = mysql.createConnection({
  host: 'localhost'.user: 'root'.password: 'Can't you see... '.port: '3306'.database: 'myblog'
})

// Start the connection
con.connect()

// Execute the SQL statement
const sql = 'select * from users; '
// const sql = 'select id, username from users; '
// const SQL =' update users set realname=' ha 'WHERE username='emier'; `
// const SQL = 'insert into users (username,${'password'},realname) values ('emier2','666', 'emier2'); `
// const SQL = 'insert into blogs (title, content, createTime, author) values (' blogs! Title E', 'I am content, content is king EE', 1623336080690, 'tmier'); `
con.query(sql,(err,result) = > {
  if(err){
    console.error(err)
    return
  }
  console.log(result);
})

// Close the connection
con.end()
Copy the code

Today I installed MySQL, which was surprisingly smooth, because I remember learning database when I was in college, and I encountered too many problems in installing MySQL due to environmental problems. I didn’t expect that it was very smooth and successful this time

Then today is the simple use of the next SQL add, delete, change to check a few lines of command, although not used for a long time, but knocked a few times the code, feeling is not strange ~

Today is also the first time to use Node to operate MySQL, unexpected simple ~ etc continue to update under learning ~