---
# System prepended metadata

title: MySQL
tags: [數據分析]

---

# 數據分析｜MariaDB
## 裝置基礎設定

#### 看IP位址
    
```
    apt install net-tools
    ifconfig
```
    
#### 裝maraidb
    
```
    sudo su
    sudo apt update
    sudo apt install mariadb-server
    netstat -aptn
    # 查看 3306port 是否有被啟動
```
    
#### 線到自己本地端的Mariadb Server
    
```
    mysql
```
    
```sql
    show databases;
```
    
#### 用戶端連線指令
    
```
    mariadb # mysql
    exit # 離開用戶端
    Ctrl + C # 離開用戶端
```
    
#### 給予一個管理者權限
    
```
    GRANT ALL
    ON *.*
    TO 'admin'@'localhost'
    IDENTIFIED BY '123456'
    WITH GRANT OPTION;
   ```
    
#### 透過管理者登入
    
```
    mariadb -uadmin -p123456
```
    
#### 查看權限
    
```sql
    show grants;
```
    
#### 修改mariadb的埠號
    
```jsx
    vi /etc/mysql/my.cnf
    
    /port (/是搜尋，如果沒有看到可以按n找下一筆)
    esc:wq
```
    
    - 資料庫存放的路徑在
    /var/lib/mysql 底下

## 資料庫結構

### 資料庫操作
    
```sql
show databases;
create database newdb ;
show databases;
use newdb;
    
```
    
#### 資料表 table 操作
        
```sql
# 顯示
show tables;
```
        
#### 顯示資料庫
        
```sql
show databases;
```
        
#### 進入
        
```sql
use newdb;
```
        

### 資料表 table 操作

#### 顯示

```sql
show tables;
```

#### 新增

```sql
create table tep_1(
 id int,
 id_2 int
);
```

#### 再顯示（確認是否新增成功）

```sql
show tables;
```

#### 查看資料表欄位

```sql
describe tep_1;
```

#### 資料操作
- 寫入資料
        
語法：insert into [資料表] values(值1,值2);
        
    
```sql
    insert into tmp_1 values(1,10);
```
    
- 搜尋資料
- 
```sql
select [欄位]] from tep_1;
select * from tep_1;
```
    

## client 指令的常見參數介紹

-u：使用者—預設當前系統使用者
-p：密碼—預設無密碼
-h：host—預設 localhost
-P：埠—預設 3306

```
mysql -uadmin -p123456 -hlocalhost -P3306
```

#### 牛刀小試：
 建立一個班級成績資料表
 座號、英文、數學、中文`
- 單行註解
    - 正確的單行註解
        
```sql
select * from tmp_1 ; # 註解
```
        
```sql
select * # 註解
from tmp_1 ;
```
        
- 錯誤的單行註解
        
select * # 註解 from tmp_1 ;
        
- 多行註解
    
```sql
/*
多行註解
多行註解
*/
```
    

### MySQL 資料型態

[參考網址](https://www.itread01.com/study/mysql-data-types.html)

- 整數 型態操作
    
```sql
create table int_test (
    i1 tinyint,
    i2 smallint ,
    i3 int ,
    i4 bigint
);
```
    
- 寫入四個正確值
        
    ```        
    insert into int_test values(1,1,1,1);
    ```
        
- 浮點位數 型態操作
    
    float(m,n)
    m 數值的總長度
    n 浮點位數的長度
    float(10,5)
    00000.00000
    
    ```sql
    create table float_test (
    	f1 float,
    	f2 float(10,3)
    );
    
    insert into float_test values(1.11111111,1.11111111);
    ```
    
- 字串 型態操作
    
    CHAR -> 儲存單一名詞、內容 (短文字)
    TEXT -> 一系列內容 (長文字)
    CHAR -> CHAR(最大長度) 、 VARCHAR(最大長度)
    CHAR(最大長度)
    CHAR(10) -> 固定長度的字串
    CHAR 儲存空間大 效率高
    'a----------'
    'abcdddddddd'
    
    VARCHAR(10) -> 非固定儲存長度
    VARCHAR 儲存空間小 效率差
    'a'
    'abcdddddddd'
    
    - `建立一個char 、varchar最大長度為10的表格`
        
        ```sql
        create table char_test (
        	c1 char(10),
        	c2 varchar(10)
        );
        
        insert into char_test values( # 成功
        	'1234567890',
        	'1234567890'
        );
        
        insert into char_test values( # 錯誤
        	'12345678901',
        	'12345678901'
        );
        ```
        
- 字串 型態操作
    
    TINYTEXT
    TEXT
    MEDIUMTEXT
    LONGTEXT
    
    - 建立一個TEXT
        
        ```sql
        create table text_test (
        	t1 TINYTEXT,
        	t2 TEXT,
        	t3 MEDIUMTEXT,
        	t4 LONGTEXT
        );
        
        insert into text_test values('','聲請意旨略以：受刑人前因強制性交等案件，經法院判刑確定並移送執行。茲受刑人業經法務部於民國113年1月5日法矯署教字第11201895390號核准假釋在案，依刑法第93條第2項之規定，在假釋中付保護管束，爰依刑事訴訟法第481條第1項第2款規定聲請裁定等語。','','');
        ```
        
- 時間 型態操作
    
    DATE
    TIME
    YEAR
    
    ```sql
    create table dt_test (
    	t1 DATE ,
    	t2 TIME,
    	t3 YEAR
    );
    
    insert into dt_test
    values( '2024-01-09',
    	'18:35:00',
    	'2022');
    
    insert into dt_test
    values( '2024!01!09',
    	'18:35',
    	'2022');
    ```
    
    DATETIME
    TIMESTAMP
    
    ```sql
    create table dt2_test (
    	t1 DATETIME ,
    	t2 TIMESTAMP
    );
    
    describe dt2_test ;
    # 寫入資料
    insert into dt2_test
    values( '2024!01!09 18:35:00',
    	'2024!01!09 18:35:00');
    # 對timestamp 寫入空值則會自動寫入系統時間
    insert into dt2_test
    values('2024!01!09 18:35:00',
    	null);
    
    insert into dt2_test  values(
    	'2024!01!09 18:35:00',
    	current_timestamp()
    );
    ```
    

### 欄位屬性

- 屬性1 非空值
    
    ```sql
    create table k_daily(
    	Date date,
    	Open float NOT NULL
    );
    
    # 從描述可以看到非空的屬性
    
    describe k_daily;
    
    # 寫入空資料
    
    insert into k_daily values(null,null);
    insert into k_daily values(null,30000);
    
    # 檢查資料
    
    select * from k_daily;
    ```
    
   
    
- 屬性2 無符號
    
    ```sql
    create table test_unsigned(
    	u1 tinyint ,
    	u2 tinyint unsigned
    );
    
    # 錯誤，不可有負值
    insert into test_unsigned values(-1,-1);
    
    # 正確，因為雖然一般範圍是(-128，127)，但因為屬性所以範圍也改為(0，255)
    [詳見](https://www.itread01.com/study/mysql-data-types.html)此
    insert into test_unsigned values(-1,200);
    ```
  
    
- 屬性3 唯一值
    
    亦即該欄位的值不可重複
    
    ```sql
    create table test_unique(
    	id1 tinyint unsigned unique,
    	id2 tinyint unsigned
    );
    
    # 正確
    insert into test_unique values(1,1);
    
    # 錯誤，因為id1已設定為唯一值，故不可再次出現value為1
    insert into test_unique values(1,1);
    
    ```
    
- 屬性4 預設值
    
    ```sql
    create table k_daily(
    	Date date,
    	Open float NOT NULL DEFAULT 3000
    );
    
    insert into k_daily values(null,null); # 不能指定null值
    
    # insert into 資料表 (指定欄位, ...)
    # values (值, ....)
    insert into k_daily(Date) values(null);
    select * from k_daily;
    ```
    

### 修改欄位

- 修改一個欄位
    
    輸入格式：
    `ALTER TABLE 資料表 MODIFY COLUMN 欄位`
    
    ```
    ALTER TABLE k_daily
    MODIFY COLUMN Open double not null default 200;
    ```
    
- 刪除資料
    - 刪除一個欄位
        
        輸入格式：
        `ALTER TABLE 資料表 DROP COLUMN 欄位`
        
        ```sql
        ALTER TABLE k_daily
        DROP COLUMN Volume;
        ```
        
    - 刪除特定資料
        
        ```sql
        DELETE from employee
        where name = 'jack';
        ```
        
    - 刪除所有資料
        
        DELETE from 資料表
        
        ```sql
        DELETE from employee;
        ```
        
    - 格式化資料表
        
        TRUNCATE TABLE 資料表
        
        ```sql
        TRUNCATE TABLE employee;
        ```
        

#### 新增資料

- 新增一個欄位
    
    輸入格式
    `ALTER TABLE 資料表 ADD COLUMN 欄位 型態`
    
    ```sql
    # 新增一個最高價欄位
    ALTER TABLE k_daily
    ADD COLUMN High ;
    ```
    
    ```sql
    # 新增一個最低價欄位 預設為0
    ALTER TABLE k_daily
    ADD COLUMN Low float DEFAULT 0;
    ```
    
    ```sql
    # 新增一個欄位在low欄位後面 [after|before]
    ALTER TABLE k_daily
    ADD COLUMN Volume float DEFAULT 0 AFTER Low;
    ```
    
- 新增欄位資料
    
    格式：
    INSERT INTO 資料表(欄位)VALUES (值)
    
    ```sql
    insert into employee values
    (null,'jack',30,'M','RD',30000,'2024-01-10');
    表格索引
    ```
    
    或是
    
    ```sql
    insert into employee
    (name,age,gender,department,salary,hire_date)
    values
    ('jack',30,'M','RD',30000,'2024-01-10');
    ```
    
- 從其他資料表複製資料過來
    
    格式：
    INSERT INTO <資料表> (欄名,欄名...)
    SELECT (對應欄位) FROM (資料表);
    
    先建立一個新資料表
    
    ```sql
    CREATE TABLE employee_new (
    	id INT PRIMARY KEY AUTO_INCREMENT,
    	name VARCHAR(50) NOT NULL,
    	age INT,
    	gender ENUM('M', 'F'),
    	department VARCHAR(50),
    	salary DECIMAL(10, 2),
    	hire_date DATE
    );
    ```
    
    從原先的資料表複製過來
    
    ```sql
    INSERT INTO employee_new
    SELECT * FROM employee;
    ```
    
- PRIMARY KEY：意即該值不可重複
    
    有兩種建立方式，分別為
    
    ```sql
    # 只有id不可重複
    CREATE TABLE employee (
    	id INT ,
    	name VARCHAR(50) NOT NULL,
    	age INT,
    	gender ENUM('M', 'F'),
    	department VARCHAR(50),
    	salary DECIMAL(10, 2),
    	hire_date DATE,
    	PRIMARY KEY(id)
    );
    ```
    
    ```sql
    # id和name要同時重複才會報錯
    CREATE TABLE employee (
    	id INT ,
    	name VARCHAR(50) NOT NULL,
    	age INT,
    	gender ENUM('M', 'F'),
    	department VARCHAR(50),
    	salary DECIMAL(10, 2),
    	hire_date DATE,
    	PRIMARY KEY(id,name)
    );
    ```
    


## 更新及刪除資料

- 更新欄位
    - 更新所有該欄位的資料
        
        語法：
        update 資料表
        set 欄位 = (值)
        
        ```sql
        UPDATE employee
        SET hire_date = '2024/01/10';
        ```
        
        把部門後面的ing刪除
        
        ```sql
        UPDATE employee
        SET department = replace (department, 'ing',' ');
        ```
        
    - 更新指定欄位的資料
        
        語法：
        UPDATE table
        SET 欄位 = 值
        WHERE 條件
        
        ```sql
        UPDATE employee
        SET gender = 'M'
        WHERE name = 'jack';
        ```
        
- 刪除欄位
    
    語法：DELETE FROM （資料表） WHERE （搜尋條件）
    
    刪除全部資料：
    
    ```sql
    DELETE FROM employee ;
    ```
    
    刪除特定資料
    
    ```sql
    DELETE FROM employee 
    WHERE gender = 'M';
    ```
    

## 資料匯入/匯出

- 資料匯入
    - 從CMD匯入
    
    ```lua
    LOAD DATA LOCAL INFILE 匯入檔案 INTO TABLE 表格名稱 FIELDS TERMINATED BY ' ,'ENCLOSED BY ' "' LINES TERMINATED BY '\r\n'
    ```
    
    - 從mysql匯入
    
    ```sql
    LOAD DATA LOCAL INFILE '/tmp/NVDA.csv'
    INTO TABLE NVDA
    FIELDS TERMINATED BY ','  # 欄位分隔
    LINES TERMINATED BY '\n' ; # 換行
    ```
    
- 資料匯出
    - 從SQL匯出
    
    ```sql
    SELECT * FROM 資料表
    INTO OUTFILE 檔案路徑
    FIELDS TERMINATED BY ' ,'
    ENCLOSED BY ' "'
    LINES TERMINATED BY '\r\n';
    ```
    
    ```sql
    SELECT * FROM NVDA
    WHERE Date > '2023-01-01'
    INTO OUTFILE '/tmp/NVDA_2023.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n';
    ```
    
    - 從CMD匯出
    
    ```sql
    mysql -uroot -p123456 -D newdb -e "select * from NVDA"
    ```
    
    sed -> 置換文字的指令
    's/\t/,/g' -> 's/被取代的文字/要取代的文字/g' -> g (global)
    \t 是縮排 \n 換行
    
    ```sql
    mysql -uroot -p123456 -D newdb -e "select * from NVDA" | sed 's/\t/,/g'
    ```
    
    - 寫到檔案中
    > 導向 -> 把標準輸出（螢幕輸出）導向到特定檔案
    
    ```lua
    mysql -uroot -p123456 -D newdb -e "select * from NVDA" | sed 's/\t/,/g'> NVDA_CMD.txt
    ```
    

## 搜尋資料

- 建立一個索引
    
    create index 欲建立的索引名稱 on 資料庫(欄位,欄位);
    
    ```lua
    # 建立單一索引
    create index future_date on future_k(date);
    
    # 建立多個索引
    create index future_date on future_k(date,time);
    ```
    
    ```
    # 容量建立索引前後 64M -> 84M
    # 耗時：0.001 sec
    # 透過 explain 指令可以查看是否使用到索引
    explain select * from future_k
    where date = '2017-01-06' and time > '10:45:00';
    ```
    
- 設定成primary key 容量跟搜尋效率的差別
    
    ```sql
    create table future_k1(
    	date date,
    	time time,
    	open smallint,
    	high smallint,
    	low smallint,
    	close smallint,
    	volume tinyint,
    	primary key (date,time)
    );
    ```
    
    ```sql
    # 解釋查詢細節
    explain select * from future_k1   where date = '2017-01-06' and time > '10:45:00';
    ```
    
    ```sql
    # 寫入資料
    LOAD DATA LOCAL INFILE '/tmp/future_k.csv'
    INTO TABLE future_k1
    FIELDS TERMINATED BY ','  # 欄位分隔
    LINES TERMINATED BY '\n' ; # 換行
    ```
    
    - 結論：
    1. primary key > index
    2. 建立primary key資料表  > 建立無key的資料表
- 搜尋唯一值
    
    語法：
    SELECT DISTINCT （欄位名） FROM （資料表）;
    
    ```sql
    select distinct age from employee_age;
    select distinct age,id from employee_age;
    ```
    
- 特殊查詢
    
    or 其中之一
    
    ```sql
    select *  from employee
    where 	department = 'Marketing'
    	or department = 'Engineering' ;
    ```
    
    in 其中之一
    
    ```sql
    select *  from employee
    where department in ('Marketing' ,'Engineering') ;
    ```
    
    between 在......區間
    
    ```sql
    select * from future_k1
    where date between '2010-01-01' and '2010-12-31' ;
    ```
    
    特殊字元
    
    %：包含0~無限個字元
    
    ```sql
    # M開頭
    select *  from employee
    where name like 'M%';
    
    # 內容包含v
    select *  from employee
    where name like '%v%';
    ```
    
    _：包含1個字元
    
    ```sql
    # M後面有5個字元
    select *  from employee
    where name like 'M_____';
    
    # M後面有3個字元
    select *  from employee
    where name like 'M___';
    ```
    
    判斷 id_number 第一個字元為英文字母
    
    - 正則表達式
        1. 使用 .（句點）匹配任何單個字符
        2. 使用 *（星號）匹配前面的字符零次或多次
        3. 使用 +（加號）匹配前面的字符一次或多次
        4. 使用 ?（問號）匹配前面的字符一次
        5. 使用 ^（插入符號）匹配字符串的開始
        6. 使用 $（美元符號）匹配字符串的結尾
        7. 使用 []（方括號）匹配方括號中的任意字符
        8. 使用 [^]（否定方括號）匹配不在方括號中的任何字符
        9. 使用 |（管道符號）匹配多個表達式之一
        10. 使用 \（反斜線）轉義特殊字符
        - 檢查台灣身分證字號格式是否正確
        
        ```sql
        SELECT * FROM member
        WHERE id_number REGEXP '^[A-Z][1-2][0-9]{8}$';
        ```
        
        - 搜尋所有以 '[gmail.com](http://gmail.com/)' 結尾的電子郵件
        
        ```sql
        SELECT * FROM member
        WHERE email REGEXP '^[a-zA-Z0-9._%+-]+@gmail.com$';
        ```
        
- 刪除索引
    
    drop index 欲刪除的索引名稱 on 資料庫(欄位,欄位);
    
- 限制筆數的用法
    
    ```sql
    select *
    from employee
    limit 2,1;
    ```
    

## 常見函數

- AVG
• 求群組中數值的平均值
• 計算員工薪資的平均值
SELECT AVG(salary) AS avg_salary FROM employee;
- COUNT
• 計算指定的欄位中值有多少列的函數
• 計算員工總數
SELECT COUNT(*) AS total_employees FROM employee;
- MAX
• 求指定欄位中最大值
• 找出最高薪資
SELECT MAX(salary) AS max_salary FROM employee;
- MIN
• 求指定欄位中最小值
• 找出最低薪資
SELECT MIN(salary) AS max_salary FROM employee;
- STDDEV
• 求群組中數值的標準差
• 計算員工薪資的標準差
SELECT STDDEV(salary) AS salary_stddev FROM employee;
- SUM
• 求數值式的總和的函數
• 計算員工薪資總和
SELECT SUM(salary) AS total_salary FROM employee;
- VARIANCE
• 求指定數值的變異數值的函數
• 計算員工薪資的方差
SELECT VARIANCE(salary) AS salary_variance FROM employee;
- CONCAT
• 連接兩個或多個字符串
• SELECT CONCAT(name, ' is in the ', department, ' department')
AS info FROM employee;
- LENGTH
• 返回字符串的長度
• SELECT name, LENGTH(name) AS name_length FROM employee;
- LOWER、LCASE
• 將字符串轉換為小寫
• SELECT LOWER(name) AS name_lower FROM employee;
- UPPER、UCASE
• 將字符串轉換為大寫
• SELECT UPPER(department) AS department_upper FROM
employee;
- SUBSTRING
• 返回字符串的一部分
• SELECT SUBSTRING(name, 1, 3) AS name_substr FROM
employee;
- REPLACE
• 替換字符串中的一部分
• SELECT REPLACE(department, 'Sales', 'Sales and Marketing') AS
dept_replaced FROM employee;
- TRIM
• 去掉字符串的空格
• SELECT TRIM(name) AS name_trimmed FROM employee;
- INSERT
• 在字符串中插入另一個字符串
• SELECT INSERT(name, 2, 0, 'a') AS name_inserted FROM
employee;
- INSTR
• 返回一個字符串在另一個字符串中的位置
• SELECT name, INSTR(name, 'oh') AS position FROM employee;
- REVERSE
• 反轉字符串
• SELECT name, REVERSE(name) AS name_reversed FROM
employee;
- LEFT
• 返回字符串的左側一部分
• SELECT LEFT(name, 3) AS name_left FROM employee;
- RIGHT
• 返回字符串的右側一部分
• SELECT RIGHT(name, 2) AS name_right FROM employee;
- SPACE
• 返回由指定數量的空格字符組成的字符串
• SELECT SPACE(5) AS space_string FROM employee;
- NOW
• 回傳當前系統日期與時間
• SELECT NOW();
- YEAR
• 回傳日期中的年份
• SELECT YEAR(hire_date) FROM employee;
- MONTH
• 回傳日期中的月份
• SELECT MONTH(hire_date) FROM employee;
- DAY
• 回傳日期中的日
• SELECT DAY(hire_date) FROM employee;
- HOUR
• 回傳時間中的小時
• SELECT HOUR(NOW());
- MINUTE
• 回傳時間中的分鐘
• SELECT MINUTE(NOW());
- SECOND
• 回傳時間中的秒數
• SELECT SECOND(NOW());
- DATEDIFF
• 回傳兩個日期之間的天數差
• SELECT DATEDIFF('2022-05-01', '2022-03-15')
- DATE_FORMAT
• 將日期或時間格式化為指定格式的字串
• SELECT DATE_FORMAT(hire_date, '%Y年%m月%d日') FROM
employee;
- CURRENT_DATE
• 回傳當前系統日期
• SELECT CURRENT_DATE();
- CURRENT_TIME
• 回傳當前系統時間
• SELECT CURRENT_TIME();
- CURRENT_TIMESTAMP
• 回傳當前系統日期與時間
• SELECT CURRENT_TIMESTAMP();
- LAST_DAY
• 取得指定日期所在月份的最後一天日期
• 找出員工入職當月的最後一天
SELECT name, hire_date, LAST_DAY(hire_date) AS
last_day_of_month FROM employee;
- ADDDATE 、SUBDATE
• 將日期加上一定的時間間隔，例如天數、周數或月數
• 將員工 Peter 的入職日期往後延長 1 年
UPDATE employee
SET hire_date = ADDDATE(hire_date, INTERVAL 1 YEAR)
WHERE name = 'Peter';

## SQL群組化

- 抓出每個部門的平均薪資

```sql
select department,avg(salary)
from employee
group by department;
```

- 抓出每個年齡層的最低薪資(10y) round(age/10)

```sql
select round(age/10) age_range,min(salary)
from employee
group by round(age/10);
```

- 抓出每個月份到職的員工人數 year(hire_date)

```sql
select month(hire_date) hire_month,count(*) employ_num
from employee
group by month(hire_date);
```

- 篩出高所得職員（<100000）
然後統計每個部門有幾位
使用where

```sql
select name, salary , count(salary) from employee
where  salary>70000
group by department;
```

- 篩出高平均所得的部門(>70000)
使用having

```sql
select department, avg(salary) from employee
group by department
having avg(salary)>70000;
```

## 外部鍵

```sql
CREATE TABLE member (
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL,
password VARCHAR(50) NOT NULL,
join_date DATE,
id_number VARCHAR(10) NOT NULL,
email VARCHAR(50) NOT NULL,
url VARCHAR(100) NOT NULL
);
```

```sql
INSERT INTO member (username, password, join_date, id_number, email, url) 
VALUES 
('john123', 'password123', '2022-01-01', 'A123456789', 'john123@gmail.com', 'https://www.example.com/john123'),
('mary456', 'password456', '2022-01-15', 'B234567890', 'mary456@gmail.com', 'https://www.example.com/mary456'),
('david789', 'password789', '2022-02-01', 'C345678901', 'david789@gmail.com', 'https://www.example.com/david789'),
('lisa111', 'password111', '2022-02-15', 'D456789012', 'lisa111@gmail.com', 'https://www.example.com/lisa111'),
('kevin222', 'password222', '2022-03-01', 'E567890123', 'kevin222@gmail.com', 'https://www.example.com/kevin222');
```

```sql
CREATE TABLE purchase (
id INT PRIMARY KEY AUTO_INCREMENT,
member_id INT NOT NULL,
product_name VARCHAR(50) NOT NULL,
price DECIMAL(8, 2) NOT NULL,
purchase_date DATE
);
```

```sql
INSERT INTO purchase (member_id, product_name, price, purchase_date)
VALUES
(1, 'T-shirt', 20.99, '2022-01-02'),
(2, 'Jeans', 39.99, '2022-01-17'),
(3, 'Sneakers', 79.99, '2022-02-03'),
(4, 'Dress', 59.99, '2022-02-18'),
(5, 'Jacket', 99.99, '2022-03-03'),
(1, 'Jeans', 29.99, '2022-03-05'),
(3, 'Dress', 89.99, '2022-03-20'),
(2, 'Handbag', 49.99, '2022-04-01');
```

- 新增一筆不存在的資料再刪除

```sql
INSERT INTO purchase (member_id, product_name, price, purchase_date)
VALUES
(7, 'Handbag', 46.99, '2022-07-01');

delete from purchase where member_id = 7;
```

- 新增外部鍵 (建立以後再重複上述動作)

```sql
ALTER TABLE purchase
ADD CONSTRAINT fk_member_id
FOREIGN KEY (member_id)
REFERENCES member(id);
```

## 子查詢

- 搜尋會員名稱

```sql
select username
from member;
```

- 搜尋購買過牛仔褲的紀錄

```sql
select  member_id
from purchase
where product_name = 'Jeans';
```

- 搜尋購買過牛仔褲的會員名稱

```sql
select username
from member
where id in (
	select  member_id
	from purchase
	where product_name = 'Jeans'
```

- 交叉查詢
    - 查詢有購買過商品的紀錄 包含會員基本資料
    
    ```sql
    select *
    from member m,purchase p
    where m.id = p.member_id;
    select 	m.username,
    				p.product_name,
    				p.price,
    				p.purchase_date
    from member m,purchase p
    where m.id = p.member_id;
    ```
    
- 內部查詢
    - 查詢有購買過商品的紀錄 包含會員基本資料
    
    ```sql
    select 	m.username,
    				p.product_name,
    				p.price,
    				p.purchase_date
    from member m
    inner join purchase p
    on m.id = p.member_id;
    ```
    
- 左(右)外部查詢
    
    ```sql
    SELECT 	m.username,
    				p.product_name,
    				p.price
    FROM member m
    LEFT JOIN purchase p
    ON m.id = p.member_id
    ```
    
- 內部查詢與左(右)外部查詢的差異
    1. 先在會員資料中輸入一筆資料
    
    ```sql
    INSERT INTO member
    (username, password, join_date, id_number, email, url)
    VALUES
    ('jack999', 'password999', '2021-01-01', 'R123456789', 'jack999@gmail.com', 'https://www.example.com/jack999');
    ```
    
    1. 再分別使用內部查詢與左外部查詢
        
        內部查詢：
        
        ```sql
        select 	m.username,
        				p.product_name,
        				p.price,
        				p.purchase_date
        from member m
        inner join purchase p
        on m.id = p.member_id;
        ```
        
        左外部查詢：
        
        ```sql
        SELECT 	m.username,
        				p.product_name,
        				p.price
        FROM member m
        LEFT JOIN purchase p
        ON m.id = p.member_id
        ```
        

## 視圖應用

語法：CREATE VIEW View名稱 AS SELECT 查詢句;

- 建立一個簡單的職員資料表
    
    ```sql
    CREATE VIEW employee_simple AS
    select name,age,gender,department
    from employee ;
    
    # 顯示表格
    show tables;
    
    # 顯示完整的表格資料
    show full tables;
    ```
    
- 實際案例
    - 牛刀小試1：建立一個 > 30歲的職員資料表
        
        ```sql
        CREATE VIEW employee_2 AS
        select *
        from employee
        where age > 30;
        ```
        
    - 牛刀小試2：future_k1 是台指期的K線資料
    日盤 08:45~13:45 夜盤 15:00~05:00
    請依照日夜盤 分成兩個View
        
        ```sql
        create view future_k1_day as
        select * from future_k1
        where time between '08:45:00' and '13:45:59';
        
        CREATE VIEW future_night AS
        select * 
        from future_k1 
        where time >= '15:00:00' or time <= '05:00:01';
        ```
        
    - 牛刀小試3：建立購買紀錄以及會員名稱的表格
        
        ```sql
        CREATE VIEW purchase_member_name AS
        select 	m.username,
        				p.product_name,
        				p.price,
        				p.purchase_date
        from member m
        inner join purchase p
        on m.id = p.member_id;
        ```
        
    

## 資料庫備份與還原

指令：`mysqldump -hlocalhost -u root -p密碼 資料庫 > 備份檔案`

- 退出mySQL，建立資料庫備份檔
    
    ```lua
    mysqldump -uroot -p123456 newdb > /tmp/newdb.bak
    ```
    
- 登入資料庫 刪除整個資料庫
    
    ```lua
    mysql 
    drop database newdb;
    ```
    
- 進入mySQL建立空資料庫
    
    ```sql
    create database newdb_2;
    ```
    
- 還原備份檔進入剛剛建好的database
    
    ```lua
    mysql -uroot -p123456 -D newdb_2 < /tmp/newdb.bak
    ```
    

## 常規變數

常規變數的定義

```sql
set @var1 = 66 ;
select select @var1;
```

搜尋結果 寫到 常規變數

DELIMITER //
CREATE PROCEDURE 預存程序([IN 帶入參數 型態,OUT 回傳參數 型態])
BEGIN
　	{自訂SQL語句}
END;
// DELIMITER

- 建立一個查詢特定部門職員的stored procedure
    
    ```sql
    DELIMITER //
    CREATE PROCEDURE GetEmployeesInDepartment(IN deptName VARCHAR(50))
    BEGIN
    	SELECT * FROM employee WHERE department = deptName;
    END;
    // DELIMITER ;
    ```
    
- 執行該procedure

	```sql
	CALL GetEmployeesInDepartment('Sales');
	```

