前端的後端 101

dev

⚠️ Disclaimer: This is not a tutorial.

node + express

前置準備

  1. (have Node.js installed)
  2. init git and npm
  3. npm install mysql and express

需求

  1. 建立資料庫 nodemysql
  2. 建立資料表 employee，有 id, name, designation 三個欄位
  3. CRUD 員工資料

0. 建立連線

1
2
3
4
5
6
7
8
9
10
const db = mysql.createConnection({
  host: 'localhost',
  user: 'root',
  password: ''
});

db.connect((error) => {
  if (error) throw error;
  console.log('MySQL connected!');
});

1. 監聽

1
2
3
4
5
const app = express();

app.listen('3000', () => {
  console.log('Server started on port 3000!');
});

2. 建立資料庫

1
2
3
4
5
6
7
app.get('/create_db', (req, res) => {
  let sql = 'CREATE DATABASE nodemysql';
  db.query(sql, error => {
    if (error) throw error;
    res.send('db created!');
  });
});

3. 建立資料表

1
2
3
4
5
6
7
app.get('/create_employee', (req, res) => {
  let sql = 'CREATE TABLE employee(id int AUTO_INCREMENT, name VARCHAR(255), designation VARCHAR(255), PRIMARY KEY(id))';
  db.query(sql, error => {
    if (error) throw error;
    res.send('Table "employee" created!');
  });
});

4. 新增員工資料

1
2
3
4
5
6
7
8
9
10
11
app.get('/employee', (req, res) => {
  let post = {
    name: 'John Depp',
    designation: 'Chief'
  };
  let sql = 'INSERT INTO employee SET ?';
  db.query(sql, post, error => {
    if (error) throw error;
    res.send('Employee added!');
  });
});

5. 讀取員工資料

1
2
3
4
5
6
7
8
app.get('/get_employees', (req, res) => {
  let sql = 'SELECT * FROM employee';
  db.query(sql, (error, results) => {
    if (error) throw error;
    console.log(results);
    res.send('Employees fetched!');
  });
});

6. 更新員工資料

1
2
3
4
5
6
7
8
app.get('/update_employee/:id', (req, res) => {
  let newName = 'Jake';
  let sql = `UPDATE employee SET name = '${newName}' WHERE id = ${req.params.id}`;
  db.query(sql, error => {
    if (error) throw error;
    res.send('Employee updated!');
  });
});

7. 刪除員工資料

1
2
3
4
5
6
7
app.get('/delete_employee/:id', (req, res) => {
  let sql = `DELETE FROM employee WHERE id = ${req.params.id}`;
  db.query(sql, error => {
    if (error) throw error;
    res.send('Employee deleted!');
  });
});

SQL in terminal

前置準備

  1. install MySQL via pkg
  2. install mycli (optional)

需求

  1. 建立資料庫 book_inventory
  2. 新增使用者 admin、設定權限
  3. 建立資料表，設定 foreign key

0. 打開 MySQL server

1
mysql.server start

好用 alias：

1
2
alias mysqlstart="sudo /usr/local/mysql/support-files/mysql.server start"
alias mysqlstop="sudo /usr/local/mysql/support-files/mysql.server stop"

1. 建立連線

1
2
mysql -u <username> -h <host_name> -p
> Enter Password: (輸入安裝時設定的密碼)
  • 如果在安裝時有設定密碼，指令要帶 -p
  • db_name 為 optional，可以連線後再使用 use <db_name> 選取
  • 可以使用預設帳號 root/`` (root 的預設密碼為空字串) 和 localhost
  • 連線指令參數可以參考 Connect to database
  • 如果發生 “mysql command not found” ，可以嘗試參考這個 Stack Overflow 的解決方式
  • 如果遇到 “ERROR! The server quit without updating PID file (/usr/local/mysql/data/evachngs-mac.local.pid).” 可以嘗試 sudo killall mysqld
  • 如果遇到 “Can’t connect to local MySQL server through socket” 可以嘗試加入參數 --protocol=TCP
  • ref. Connecting to the MySQL Server with the mysql Client

離開連線

進入 CLI 前最重要的一件事就是知道怎麼離開！

1
quit

2. 建立資料庫

1
CREATE DATABASE <db_name>

3. 建立資料表

1
CREATE TABLE <table_name>(<columns_info>)

操作資料表

1
2
3
4
5
6
7
8
-- 資料表重新命名
ALTER TABLE <old_table_name> RENAME <new_table_name>

-- 更動欄位資料型態
ALTER TABLE <table_name> MODIFY <column_name> <data_type>

-- 設定 foreign key
ALTER TABLE <table_name> ADD FOREIGN KEY (<key_in_table>) references <anothor_table>(<key_in_another_table>)

4. 新增使用者

1
CREATE USER '<username>'@'<host_name>' IDENTIFIED BY '<password>'

操作使用者

1
2
3
4
5
6
7
8
-- 設定權限
GRANT <privileges> on <db_name>.<table_name> TO '<username>'@'<host_name>' WITH GRANT OPTION

-- 建議在「建立新帳號」和「設定使用者權限」之後立即執行
FLUSH PRIVILEGES

-- 查看特定使用者權限
SHOW GRANTS FOR '<username>'@'<host_name>'

(完。)

下一步是要認識常用的資料型態，以及在 terminal 下 SQL 語法操作資料表 (基礎的 CRUD)。 👋🏼