05 博客项目数据存储

[weikee94]

SQL Installation download link

GUI Workbench Installation download link

[weikee94]

Disable Safe Update Mode On MySql Workbench

SET SQL_SAFE_UPDATES = 0;

软删除

• By specifying state on the column we can actually implement what we called '软删除'

Check Sql Version

select version();

Use Selected Database

use myblog;

Check Available Databases And Tables

show databases;
show tables;

INSERT

insert into users(username, `password`, realname) values ('abcde', '12345', 'cnn name');

SELECT

like %
not equal <>

select * from users where username like '%a%' and password='123' and state <>'1' order by id desc;

UPDATE

update users set username='abcd' where id='2';

Delete

delete from blogs where id="3";

Change Root Password

-- newPass is the updated password
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newPass';

[weikee94]

Connect MySQL

const mysql = require("mysql");

// create connection
const con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
  port: "3306", // default port
  database: "myblog",
});

// start connection
con.connect();

// execute sql
const sql = "select * from users";
con.query(sql, (err, result) => {
  if (err) {
    console.error(err);
    return;
  }
  console.log(result);
});

// close connection
con.end();

Issues

1. MySQL 8.0 - Client does not support authentication protocol requested by server; consider upgrading MySQL client view more solved!

• resolve by using these two sql

  ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'
  flush privileges;