Skip to content

antony@notes:~/data-platform$ cat "MySQL-and-MySQL-Workbench.md"

MySQL and MySQL Workbench

2022-08-07· data-platform ·MySQL

MySQL and MySQL Workbench

[TOC]

時代的眼淚 過往的病歷表儲存

:::spoiler 示意圖

:::

傳統儲存資料的方式有哪些不方便的地方？

紙本資料的不方便：

  • 資料太多，浪費物理儲存空間
  • 大量資料太重，不利於轉移
  • 沒有備份，紙本資料遺失就永遠遺失了
  • 與資料處於不同地點，就無法立即取得（物理傳輸耗費時間）
  • 資料整理要耗費大量時間與人力整理
  • 人為錯誤（書寫、分類錯誤）
  • 時間久了，紙本資料會變的破舊

資料未正規劃

重複或缺失的資料都未整理

ER Model

實體 - 關係模型 (Entity-Relationship Model, 簡稱 E-R Model)

陳品山 (Peter P.S. Chen) 博士於 1976 年提出的一套資料庫的設計工具, 以圖形化的方式表示儲存於資料庫內的各資料項目之間的關係。

又稱為 ER Diagram (ER圖) 常用於分析師與企業內部使用者，在資訊系統設計過程中的構思及溝通討論使用

組成要件：

  • Entities 實體｜方塊圖形表示
  • 定義：通常是名詞，它可以是獨立存在的一個事物，或是一個概念上存在的 物件。（如：員工，課程，車…）
    • Attributes 屬性｜橢圓形表示
    • 定義：用來描述個體的特徵或屬性
      • 型別：
      • 一般屬性 General attributes｜簡單的資訊
      • 複合屬性 Composite attributes｜可再拆解分割
      • 多值屬性 Multivalued attributes｜一個以上的值
      • 衍生屬性 Derived attributes｜透過另一屬性計算出來
      • 鍵值屬性 Key attributes｜具備值的唯一性
    • Relationships 關係｜菱形表示
      • 定義：表示兩個實體間的關聯性

練習1

請解釋Country與City之間的關係 :::spoiler 示意圖 :::

:::spoiler 答案 by 程威繪製 :::

練習2

請解釋customer與address之間的關係

:::spoiler 示意圖

:::

:::spoiler 答案

by 程威繪製 :::

練習3

根據 正規劃後的轉診單(P.8-9) ，建立陳品山博士版本的ER圖

:::spoiler 正規劃的轉診單 圖片

:::

:::spoiler 答案

:::

MySQL Installation

佈署實作環境

下載CNT.2022.v4

在 Alpine Linux 安裝 MySQL

安裝MySQL Server 和MySQL client端 (其實在Alpine裡安裝MySQL 就是安裝Mariadb )

$ sudo apk add mysql mysql-client

檢查mariadb的狀態

$ sudo rc-service mariadb status

啟用MySQL的TCP/IP連接並綁訂服務到指定IP

$ sudo nano /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
#skip-networking     ## 將這行註解掉，代表啟用MySQL的TCP/IP連接
[galera]
bind-address=0.0.0.0 ## 取消這行的註解，代表任意IP都可以連線到我們的MySQL

先將mariadb初始化

$ sudo /etc/init.d/mariadb setup

啟動mariadb的服務

$ sudo rc-service mariadb start

進入mysql

$ sudo mysql
ERROR 2002 (HY000): Can't connect to local server through socket '/run/mysqld/mysqld.sock' (2)

噴錯提示：ERROR 2002 (HY000): Can’t connect to local server through socket ‘/run/mysqld/mysqld.sock’ (2)

:::spoiler 什麼是Socket？

:::danger

$ ls -al /run/mysqld/mysqld.sock
srwxrwxrwx 1 mysql mysql 0 May 26 11:17 /run/mysqld/mysqld.sock
> 檔案類型為：s，代表Socket 檔案

Socket 分為兩大類

  • Internet Domain Socket
    • 用於IP的port上
  • Unix Domain Socket
    • 用於process之間的溝通
    • 好處是不需要透過網路溝通，在速度上會比較快

以上兩種Socket會讓我們的MySQL產生兩種登入方式

  • mysql -S ，透過Unix Domain Socket登入
  • mysql -H <$IP> ，透過Internet Domain Socket登入 :::

設定 Mariadb 開機自動啟動

$ sudo rc-update add mariadb default
 * service mariadb added to runlevel default

檢查

$ rc-update show default | grep mariadb
              mariadb | default

將VM重啟

$ sudo reboot

檢查 Mariadb 服務啟動狀態

$ rc-status| grep mariadb
 mariadb                                                           [  started  ]

在 Alpine Linux 設定 MySQL 

$ sudo mysql_secure_installation

Enter current password for root (enter for none):
   	（輸入現有root密碼,enter）

Switch to unix_socket authentication [Y/n]
   	（ y，啟用unix_socket 身份驗證）

Change the root password? [Y/n]
   	（ y，更換 root 密碼）
	  New password: root
         Re-enter new password: root

Remove anonymous users? [Y/n]
   	（ Y，移除匿名登入）

Disallow root login remotely? [Y/n]
   	（ Y，移除遠端 root 登入權限）

Remove test database and access to it? [Y/n]
	（ Y，移除測試資料庫及帳號）

Reload privilege tables now? [Y/n]
	（ Y，重新載入權限表）

登入 MySQL

登入MySQL

$ mysql -u root -p
Enter password: root

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 16
Server version: 10.6.8-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

查看目前是誰登入

MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

登入 MySQL - Socket 

$ mysql -u root -S /run/mysqld/mysqld.sock
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

噴錯的原因是因為沒有借用最高權限root的身份執行

加sudo 後再登入一次

$ sudo mysql -u root -S /run/mysqld/mysqld.sock
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 18
Server version: 10.6.8-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

成功登入！

小結論： sudo mysql -u root -S /run/mysqld/mysqld.sock與sudo mysql 都是透過Unix Domain Socket登入

新增 bigred 帳號可以從遠端登入

建立bigred使用者並且設定最高權限

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'bigred'@'%' IDENTIFIED BY 'bigred' WITH GRANT OPTION;
Query OK, 0 rows affected (0.002 sec)

立刻生效

MariaDB [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.001 sec)

檢查

MariaDB [(none)]> SELECT user,host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| bigred      | %         |
| mariadb.sys | localhost |
| mysql       | localhost |
| root        | localhost |
+-------------+-----------+
4 rows in set (0.003 sec)

SQL Command

建立名為：test 的Database

MariaDB [(none)]> create database test;
Query OK, 1 row affected (0.000 sec)

檢查

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.002 sec)

進入名為：test 的Database

MariaDB [(none)]> use test;
Database changed

刪除名為：test 的Database

MariaDB [test]> drop database test;
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]>

抓取練習資料庫

下載練習的壓縮檔

$ wget https://downloads.mysql.com/docs/sakila-db.tar.gz

解壓縮

$ tar zxvf sakila-db.tar.gz
sakila-db/
sakila-db/sakila-data.sql
sakila-db/sakila-schema.sql
sakila-db/sakila.mwb

檢查

$ ls
bin  sakila-data.sql  sakila-db  sakila-db.tar.gz  sakila-schema.sql

:::danger :::spoiler 什麼是Schema ?

資料表中的欄位定義(名稱, 屬性, 長度, 空值, 主鍵…等)

:::

進入MySQL

$ sudo mysql

匯入sakila 的schema

MariaDB [(none)]> source sakila-schema.sql
...
Database changed
...

匯入Sakila 的Data

MariaDB [sakila]> source sakila-data.sql

:::spoiler 檢查 Schema

MariaDB [sakila]> show tables;
+----------------------------+
| Tables_in_sakila           |
+----------------------------+
| actor                      |
| actor_info                 |
| address                    |
| category                   |
| city                       |
| country                    |
| customer                   |
| customer_list              |
| film                       |
| film_actor                 |
| film_category              |
| film_list                  |
| film_text                  |
| inventory                  |
| language                   |
| nicer_but_slower_film_list |
| payment                    |
| rental                     |
| sales_by_film_category     |
| sales_by_store             |
| staff                      |
| staff_list                 |
| store                      |
+----------------------------+
23 rows in set (0.001 sec)

:::

檢查Data

MariaDB [sakila]> select count(*) from customer;
+----------+
| count(*) |
+----------+
|      599 |
+----------+
1 row in set (0.000 sec)

使用MySQL Workbench

下載 MySQL GUI工具

MySQL Workbench 下載點

DBeaver 下載點

MySQL Workbench Connect to MariaDB

在MySQL Connetion 那邊按 +

什麼是DBMS ? database management system 的縮寫，其實就是資料庫的泛稱

Stored Routine

  • Stored Function｜執行單行 SQL 指令
    • 調用指令
      • select rand();
      • select now();
    • 內建功能
      • SUM()
      • COUNT()
      • NOW()
      • RAND()
  • Stored Procedure｜執行多行 SQL 指令
    • 當要執行多個SQL 語句時
    • 可決定是否回傳值，或Result sets
    • 可以呼叫Function
    • 對 SQL 注入攻擊的一些保護
    • 呼叫方式 CALL

練習 

MariaDB [sakila]> select inventory.film_id, film.title, inventory_id, store_id from inventory inner join film on film.film_id=inventory.film_id wh
ere title="DARLING BREAKING";
+---------+------------------+--------------+----------+
| film_id | title            | inventory_id | store_id |
+---------+------------------+--------------+----------+
|     211 | DARLING BREAKING |          948 |        1 |
|     211 | DARLING BREAKING |          949 |        1 |
+---------+------------------+--------------+----------+
2 rows in set (0.000 sec)

MariaDB [sakila]> select inventory_in_stock(948);
+-------------------------+
| inventory_in_stock(948) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.001 sec)

MariaDB [sakila]> select inventory_in_stock(949);
+-------------------------+
| inventory_in_stock(949) |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.001 sec)
MariaDB [sakila]> CREATE VIEW customer_list_name_postal As select first_name, last_name, address.postal_code, address.city_id from customer inner join address on address.address_id=customer.address_id;
tags: MySQL
ELK-OverviewMySQL 基本語法