---
title: MYSQL
description: 基本概念
img:
alt:
author:
name: Jack.hu
bio:
img:
tags:
- SQL
---

# 關聯式資料庫

### 一、3大基本概念

#### DDL(定義)

1. CREATE
2. DROP
3. ALTER

#### DML(操作)

1. insert into values\(C\)
2. select from\(R\)
3. update set from\(U\)
4. delete from\(D\)

\* 補充:replace into 行為為先檢查是否有同一筆資料(PK)，如有則去取代裡面的值，如沒有則創建新的資料。

#### DCL(授權及程序)

1. grant on to
2. revoke on from

### 二、transaction

1. lock
2. isolate

### 三、drop vs truncate vs delete

### 四、指令差異比較

#### 4.1. distinct vs group by

#### 4.2. between vs >=, <=

#### 4.3. where ... in () vs where ... or ...

#### 4.4. where vs having

### 五、內建函數差異比較

#### 5.1. char_length() vs length()

### 六、JOIN

1. inner join
2. left join
3. right join
4. full join

* mysql沒有full join，可用union模擬。

### 七、union

### 、正規三大步

1.
2.
3.

### 八、環境問題QA

### Q1
> 1.OS: MacOS M1-chip
> 2.MYSQL VERSION: 8+
> 3.FRAMEWORK: ExpressJS
>
> 4.Question：
> npm中mysql套件並未完全支援MYSQL 8+的加密方式，故需調整密碼及加密方式。

Unable to connect to the database: ConnectionError [SequelizeConnectionError]: Client does not support authentication protocol requested by server; consider upgrading MySQL client
    at /Users/jackhu/Desktop/kenkone_backup/server/node_modules/sequelize/lib/dialects/mysql/connection-manager.js:149:19
    at tryCatcher (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/promise.js:547:31)
    at Promise._settlePromise (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/promise.js:604:18)
    at Promise._settlePromise0 (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/promise.js:649:10)
    at Promise._settlePromises (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/promise.js:725:18)
    at _drainQueueStep (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/async.js:93:12)
    at _drainQueue (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/async.js:86:9)
    at Async._drainQueues (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/async.js:102:5)
    at Immediate.Async.drainQueues [as _onImmediate] (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/bluebird/js/release/async.js:15:14)
    at processImmediate (internal/timers.js:456:21) {
  parent: Error: Client does not support authentication protocol requested by server; consider upgrading MySQL client
      at Packet.asError (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/mysql2/lib/packets/packet.js:708:17)
      at ClientHandshake.execute (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/mysql2/lib/commands/command.js:28:26)
      at Connection.handlePacket (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/mysql2/lib/connection.js:408:32)
      at PacketParser.onPacket (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/mysql2/lib/connection.js:70:12)
      at PacketParser.executeStart (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/mysql2/lib/packet_parser.js:75:16)
      at Socket.<anonymous> (/Users/jackhu/Desktop/kenkone_backup/server/node_modules/mysql2/lib/connection.js:77:25)
      at Socket.emit (events.js:310:20)
      at addChunk (_stream_readable.js:286:12)
      at readableAddChunk (_stream_readable.js:268:9)
      at Socket.Readable.push (_stream_readable.js:209:10)
      at TCP.onStreamRead (internal/stream_base_commons.js:186:23) {
    code: 'ER_NOT_SUPPORTED_AUTH_MODE',
    errno: 1251,
    sqlState: '08004',
    sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client'
  },

> Answer

第一步，登入root並輸入密碼。

$ mysql -u root -p

第二步，更改密碼及加密方式。

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your new password';

第三部，離開mysql，開始運行您的應用，理論上應該就可以連接上MYSQL。

---
###### tags: `SQL`