前端程式設計師需要了解的MySQL

資料庫的基本概念

資料庫（database）是用來組織、儲存和管理資料的倉庫。對資料庫中的資料可以進行增刪改查操作。市面上常見的資料庫有：

MySQL（使用最廣泛。流行度最高的開源免費資料庫 Community+Enterprise）
Oracle（收費）
SQL Server（收費）
Mongodb （Community+Enterprise）

MySQL、Oracle、SQL Server屬於傳統型資料庫（關係型資料庫或SQL資料庫），Mongodb屬於新型資料庫（非關係型資料庫或NoSQL資料庫）（相互彌補）

資料組織結構：資料庫（database）、資料表（table）、資料行（row）、欄位（filed）

MySQL的基本使用

使用MySQL Workbench管理資料庫

MySQL Workbench：視覺化的MySQL管理工具

連線資料庫：

主介面的組成：

建立資料庫：

建立資料庫表：

DataType資料型別:

int 整數
varchar(len) 字串
tinyint(1) 布林值

欄位的特殊標識:

PK (Primary Key)主鍵、唯標識
NN (Not Null) 不允許為空
UQ(Unique) 值唯一
Al (AutoTncrement)值自動增長

向表中寫入資料：

使用SQL管理資料庫

SQL（Structured Query Language）是結構化查詢語言，是專門存取和處理資料庫的程式語言，能夠讓我們以程式設計的形式運算元據庫裡的資料。

SQL是一門資料庫程式語言
使用SQL編寫出來的程式碼，叫做SQL語句
SQL語言只能在關係型資料庫中使用

SELECT語句

SELECT語句用於從表中查詢資料。執行的結果被儲存在一個結果表中。

SELECT * FROM 表名稱
SELECT 列名稱1,列名稱2 FROM 表名稱
-- SQL語句中的關鍵字對大小寫不敏感

INSERT INTO語句

INSERT INTO用於向資料表中插入新的資料行。

INSERT INTO table_name(列1,列2,...) VALUES(值1,值2,...)
-- 列和值要一一對應

UPDATE語句

UPDATE用於修改資料表中的資料。

-- 1、UPDATE 指定更新的表
-- 2、SET 指定列對應的新值
-- 3、WHERE 指定更新條件
UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

UPDATE user SET password ='888888' WHERE id = 4
-- 把user表中id為4的使用者密碼更新為888888

UPDATE user SET password ='123456',status=1 WHERE id = 7
-- 把user表中id為7的使用者密碼更新為888888，使用者狀態更新為1

DELETE語句

DELETE用於刪除資料表中的資料。

DELETE FROM 表名稱 WHERE 列名稱=值

WHERE子句

WHERE用於限定選擇的標準，可以使用下列運運算元：

AND和OR運運算元

AND必須同時滿足多個條件

OR只要滿足任意一個條件即可

ORDER BY語句

ORDER BY用於根據指定的列對結果進行排序。

SELECT * FROM user ORDER BY status
SELECT * FROM user ORDER BY status ASC -- 升序
SELECT * FROM user ORDER BY status DESC -- 降序

SELECT * FROM user ORDER BY status DESC,username ASC -- 多重排序

COUNT(*)函數

COUNT(*)用於返回查詢結果的總資料條數。

SELECT COUNT(*) FROM 表名稱

-- 使用AS為列設定別名
SELECT COUNT(*) as total FROM users WHERE status=0

在Expres中操作MSQL

安裝操作MySQL資料庫的第三方模組（mysql）
通過mysql模組連結到MySQL資料庫
通過mysql模組執行SQL語句

npm i mysql

const mysql = require('mysql')
//建立連結
const db = mysql.createPool({
    host: '127.0.0.1', //資料庫的IP地址
    user: 'root',      //登入賬號密碼
    password: '123456',
    database: 'my_db_01'//指定操作那個資料庫
})

//測試mysql模組能否正常工作
db.query('SELECT 1', (err, results) => {
    //這裡的SQL語句沒有任何意義
    if (err) return console.log(err.message)
    console.log(results)
})

//獲取資料
db.query('select * from users', (err, results) => {
    if (err) return console.log(err.message)
    console.log(results)
})

//插入資料
const user = { username: '趙六', password: 'admin123' }
const sqlStr = 'insert into users (username,password) values (?,?)'
//預留位置寫法，防止SQL隱碼攻擊
db.query(sqlStr, [user.username, user.password], (err, results) => {
    if (err) return console.log(err.message)
    //如果執行insert語句，返回物件包含affectedRows
    if (results.affectedRows === 1) {
        console.log('插入資料成功！')
    }
})  
//id跳躍現象：跳過的id的資料被刪除過，刪除的資料可以回滾。

//更新資料
const user = { id: 2, username: '錢七', password: '000' }
const sqlStr = 'update users set username=?,password=? where id=?'
//預留位置寫法，防止SQL隱碼攻擊
db.query(sqlStr, [user.username, user.password, user.id], (err, results) => {
    if (err) return console.log(err.message)
    //如果執行insert語句，返回物件包含affectedRows
    if (results.affectedRows === 1) {
        console.log('更新資料成功！')
    }
})

//刪除資料
const sqlStr = 'delete from users where id=?'
db.query(sqlStr, 3, (err, results) => {
    if (err) return console.log(err.message)
    //如果執行insert語句，返回物件包含affectedRows
    if (results.affectedRows === 1) {
        console.log('刪除資料成功！')
    }
})

//插入資料(簡化寫法)
const user = { username: '趙六', password: 'admin123' }
const sqlStr = 'insert into users (username,password) set ?' //屬性順序必須和表一一對應！！！
//預留位置寫法，防止SQL隱碼攻擊
db.query(sqlStr, user, (err, results) => {
    if (err) return console.log(err.message)
    //如果執行insert語句，返回物件包含affectedRows
    if (results.affectedRows === 1) {
        console.log('插入資料成功！')
    }
})
//id跳躍現象：跳過的id的資料被刪除過，刪除的資料可以回滾。

//更新資料(簡化寫法)
const user = { id: 2, username: '錢七', password: '000' }
const sqlStr = 'update users set ? where id=?'
//預留位置寫法，防止SQL隱碼攻擊
db.query(sqlStr, [user, user.id], (err, results) => {
    if (err) return console.log(err.message)
    //如果執行insert語句，返回物件包含affectedRows
    if (results.affectedRows === 1) {
        console.log('更新資料成功！')
    }
})

標記刪除：使用DELETE語句，會把真正的把資料從表中刪除掉。為了保險起見，推薦使用標記刪除的形式，來模擬刪除的動作。所謂的標記刪除，就是在表中設定類似於 status 這樣的狀態列位，來標記當前這條資料是否被刪除,當用戶執行了刪除的動作時，我們並沒有執行 DELETE 語句把資料刪除掉，而是執行了 UPDATE 語句，將這條資料對應的status欄位標記為刪除。