---
# System prepended metadata

title: Astro課程 0717 - Database
tags: [' database', astro]

---

---
title: Astro課程 0717 - Database
tags: astro, database
---

# 資料庫 Database

SQL 語法介紹
CRUD 之新增`C`、查詢`R`、更新`U`、刪除`D` 

[Why is a database always represented with a cylinder?](https://stackoverflow.com/questions/2822650/why-is-a-database-always-represented-with-a-cylinder)

## SQL
Structured Query Language
結構化查詢語言

## RDBMS
關連式資料庫


Oracle / MSSQL

免費的：
MySQL
MariaDB
PostgreSQL
SQLite (Rails預設) 缺點是什麼? (效能差、人多時資料庫可能會被鎖住)

## NoSQL 非關聯式資料庫
Key 與 Value的組合
[Ref:](https://aws.amazon.com/tw/nosql/)
Eg. Redis / MongoDB
[Ref:](https://blog.techbridge.cc/2016/06/18/redis-introduction/)

# SQL語法

## 新增資料庫
圖形介面自動產生：
```
CREATE SCHEMA `my_db` ;
```

or 手動輸入SQL (再按`閃電`按鈕執行)

```
create database my_database;
```

## 刪除資料庫

```
drop database my_database;
```


## 使用資料庫

```
use my_database;
```

# Database 與 Rails

一個Rails應用程式有很多model, 
通常model會對到table
但是有的時候
- table不一定會有model （如果不需要透過Rails去存取）
- model不一定有table (當model用來存取外部的服務或檔案)



# DDL 資料定義語言
Data Definition Language 
create 
alter
drop

## 建立資料表

英雄資料表
```
CREATE TABLE `my_database`.`heroes` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `gender` CHAR(1) NULL,
  `age` INT NULL,
  `hero_level` CHAR(1) NOT NULL,
  `hero_rank` INT NULL,
  `description` TEXT NULL,
  PRIMARY KEY (`id`));
```

## 資料欄位型態

`VARCHAR` = VARiable CHARacter 可變動的文字型態
`CHAR`
`INT`
`TEXT`
`BLOB` = Binary Large Object 二進位大型物件


![](https://i.imgur.com/hQGmqjM.png)

`PK`: Primary Key 主鍵
`NN`: Not Null 非空值
`UQ`: Unique 唯一
`AI`: Auto Increment 自動增加


## CHAR(10) 跟 VARCHAR(10) 這兩種型態有什麼差別?

CHAR(10) 放三個字的話，剩下七格填空白
VARCHAR(10) 放三個字的話，存三個字，再額外用一個byte存長度

Q: 性別欄位用什麼型態存比較好?

## 新增/刪除資料欄位


### alter table add column 在已建立好的表格增加欄位

```
ALTER TABLE `my_database`.`heroes` 
ADD COLUMN `abc` VARCHAR(45) NULL AFTER `description`;
```

### alter table drop column 在已建立好的表格刪除欄位
```
ALTER TABLE `my_database`.`heroes` 
DROP COLUMN `abc`;
```

# DML 資料操作語言
Data Manipulation Language 

insert
update
delete


## insert 新增資料

```
insert into heroes (name, hero_level, description)
values
('綺玉', 'c', '無論多強的敵人都是一拳擊敗');
```

# 

## select 查詢資料

```
select * from heroes;
```

![](https://i.imgur.com/BhP43rz.png)


選擇s級英雄
```
SELECT *
FROM heroes
WHERE hero_level = 'S' ;

```

在Rails的Model：
```
Hero.where(hero_level: 'S')
```


列出S級的女性英雄
```
select * from heroes where hero_level = 'S' and gender = 'F';
```

但全部的資料欄位太多！

## 只選取部分欄位

- 列出S級女性英雄的名字 | 名字和性別
```
mysql> select name, gender from heroes where hero_level = 'S' and gender = 'F';
+--------+
| name   |
+--------+
| 龍卷   |
+--------+


mysql> select name, gender from heroes where hero_level = 'S' and gender = 'F';
+--------+--------+
| name   | gender |
+--------+--------+
| 龍卷   | F      |
+--------+--------+
1 row in set (0.02 sec)
```

Rails
```
Hero.select(:name, :age).where(hero_level: 'S')
```



- 哪些英雄沒有填年紀?

Q: 為什麼不能用`=`要用`is`?


```
mysql> select name from heroes where age = null;
Empty set (0.03 sec)

mysql> select name from heroes where age is null;
+-----------------------+
| name                  |
+-----------------------+
| 爆滅                  |
| 金屬騎士              |
| 殭屍俠                |
| 驅動騎士              |
...
```

- 名字的開頭是背心 `'背心%'`

```
mysql> select name, age from heroes where name like '%背心%';
+-----------------+------+
| name            | age  |
+-----------------+------+
| 背心大師        | NULL |
| 背心素食派      |   26 |
| 背心黑洞        | NULL |
| 背心之虎        | NULL |
+-----------------+------+
4 rows in set (0.01 sec)
```

rails

```
Hero.where('name like "背心%"')
Hero.where(hero_level: 'S')


Hero.where(hero_level: 'S', gender: 'F')

# 新手寫法
Hero.where(hero_level: 'S')
.where( gender: 'F')

```

Q: 為什麼rails的Model裡面沒有like語法? 
[Ref](https://stackoverflow.com/questions/13758844/sql-like-operator-in-ruby-on-rails)
[Ref](https://medium.com/@shimshtein/rails-and-database-agnostic-like-11491b8aeb58)

- 查詢年紀在10到25之間 `between`

```
mysql> select name, age from heroes where age >= 10 and age <= 25 limit 10;

mysql> select name, age from heroes where age between 10 and 25 limit 10;

+-----------------------------+------+
| name                        | age  |
+-----------------------------+------+
| 童帝                         | 10  |
| 閃光弗莱士                    | 25  |
| 傑諾斯                        | 19  |
| 金屬球棒                      | 17  |
| 帥氣蒙面俠甜美假面              | 24  |
| 居合鐵                        | 23  |
| 人妖鐮鼬                      | 23  |
| 青焰                         | 19  |
| 莿槍俠                       | 24  |
| 雷光源氏                      | 24  |
+-----------------------------+------+
10 rows in set (0.01 sec)

```

- A級或者是S級的英雄 `or`, `in`
```
mysql> select name, age from heroes where hero_level = 'S' or hero_level = 'A';

mysql> select name, age from heroes where hero_level in ('S', 'A');

+-----------------------------+------+
| name                        | age  |
+-----------------------------+------+
| 爆滅                        | NULL |
| 龍卷                        |   28 |
| 銀牙                        |   81 |
| 原子武士                    |   37 |
| 童帝                        |   10 |
| 金屬騎士                    | NULL |
| KING                        |   29 |
| 殭屍俠                      | NULL |
| 驅動騎士                    | NULL |
| 豬神                        | NULL |
```

- 不等於 `<>`, `!=`

```
mysql> select name, age, hero_level from heroes where hero_level <> 'S';

mysql> select name, age, hero_level from heroes where hero_level != 'S';
+-----------------------------+------+------------+
| name                        | age  | hero_level |
+-----------------------------+------+------------+
| 帥氣蒙面俠甜美假面          |   24 | A          |
| 居合鐵                      |   23 | A          |
| 人妖鐮鼬                    |   23 | A          |
| 武士鑽                      |   37 | A          |
| 重戰車兜檔布                |   35 | A          |
...
```

- 不是S級也不是A級的英雄 `<> and <>`, `NOT IN`


```
mysql> select name, hero_level from heroes where hero_level <> 'S' And hero_level <> 'A';

mysql> select name, hero_level from heroes where hero_level NOT IN ('S','A');

+--------------------+------------+
| name               | hero_level |
+--------------------+------------+
| 埼玉               | C          |
| 地獄的吹雪          | B          |
| 睫毛               | B          |
| 山猿               | B          |
| 野角               | B          |
| 眼鏡               | B          |
| 粉紅胡蜂            | B          |
| 氣味大師            | B          |
| 家裡蹲武士          | B          |
```

- A級男性英雄的名字和年齡

```
mysql> select name, age from heroes where hero_level = 'A' AND gender = 'M';
+-----------------------------+------+
| name                        | age  |
+-----------------------------+------+
| 帥氣蒙面俠甜美假面          |   24 |
| 居合鐵                      |   23 |
| 武士鑽                      |   37 |
| 重戰車兜檔布                |   35 |
| 青焰                        |   19 |
| 魔術俠                      | NULL |
```

## update 更新資料表 `set`

SQL
```
update heroes 
set age = 10
where id = 25
```

更新前和更新後比較

```
mysql> select name, age from heroes where id = 25;
+--------------+------+
| name         | age  |
+--------------+------+
| 死亡機槍      | NULL |
+--------------+------+
1 row in set (0.00 sec)

mysql> update heroes set age = 10 where id = 25;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select name, age from heroes where id = 25;
+--------------+------+
| name         | age  |
+--------------+------+
| 死亡機槍      |   10 |
+--------------+------+
1 row in set (0.00 sec)
```

Rails
先把人找出來再更新
```
@hero = Hero.find(25)
@hero.update(age: 10)
```

## 把所有人都變老一歲!

要先把安全開關打開
```
SET SQL_SAFE_UPDATES = 0

update heroes
set age = age + 1
```


```
mysql> SET SQL_SAFE_UPDATES = 0 ;
Query OK, 0 rows affected (0.02 sec)

mysql> update heroes set age = age + 1;

Query OK, 27 rows affected (0.05 sec)
Rows matched: 68  Changed: 27  Warnings: 0
```

更新前和更新後比較 => 老了一歲！

```
mysql> select name, age from heroes where id = 25;
+--------------+------+
| name         | age  |
+--------------+------+
| 死亡機槍      |   10 |
+--------------+------+
1 row in set (0.00 sec)

mysql> update heroes set age = age + 1;
Query OK, 27 rows affected (0.03 sec)

Rows matched: 68  Changed: 27  Warnings: 0

mysql> select name, age from heroes where id = 25;
+--------------+------+
| name         | age  |
+--------------+------+
| 死亡機槍      |   11 |
+--------------+------+
1 row in set (0.00 sec)
```

- 英雄升級！更新英雄的hero_level和hero_rank

更新前後
```
mysql> select name, hero_level, hero_rank from heroes where id = 35;
+--------+------------+-----------+
| name   | hero_level | hero_rank |
+--------+------------+-----------+
| 埼玉    | C          |       388 |
+--------+------------+-----------+
1 row in set (0.00 sec)

mysql> update heroes set hero_level = 'B', hero_rank = 101 where id = 35;
Query OK, 1 row affected (0.02 sec)


mysql> select name, hero_level, hero_rank from heroes where id = 35;
+--------+------------+-----------+
| name   | hero_level | hero_rank |
+--------+------------+-----------+
| 埼玉    | B          |       101 |
+--------+------------+-----------+
1 row in set (0.00 sec)
```

- 銀牙要退休了，刪掉他的資料
```
mysql> select name, hero_level, hero_rank from heroes where name = "銀牙";
+--------+------------+-----------+
| name   | hero_level | hero_rank |
+--------+------------+-----------+
| 銀牙   | S          |         3 |
+--------+------------+-----------+
1 row in set (0.01 sec)

mysql> delete from heroes where hero_level = 'S' and hero_rank = '3' ;
Query OK, 1 row affected (0.07 sec)

mysql> select name, hero_level, hero_rank from heroes where name = "銀牙";
Empty set (0.00 sec)
```

# 其他查詢語法

## count, as

```
mysql> select count(*) from heroes ;
+----------+
| count(*) |
+----------+
|       67 |
+----------+
1 row in set (0.06 sec)

# 把欄位更名: as count_heroes 

mysql> select count(*) as count_heroes from heroes;
+--------------+
| count_heroes |
+--------------+
|           67 |
+--------------+
1 row in set (0.00 sec)

mysql> select count(*) as count_heroes from heroes where hero_level = 'A';
+--------------+
| count_heroes |
+--------------+
|           18 |
+--------------+
1 row in set (0.00 sec)
```

Rails寫法

```
Hero.count(hero_level = 'A')
```

## sum 總和

- A級英雄年紀的總和
```
mysql> select SUM(age) from heroes where hero_level = 'A' and age IS NOT NULL;
+----------+
| SUM(age) |
+----------+
|      277 |
+----------+
1 row in set (0.02 sec)
```

## avg 平均

- A級英雄年紀的平均

```
mysql> select avg(age) as A_avg_age from heroes where hero_level = 'A';
+-----------+
| A_avg_age |
+-----------+
|   25.1818 |
+-----------+
1 row in set (0.01 sec)
```

Rails寫法

```
Book.average(:price)
```

## max 最大值

```
mysql> select max(age) from heroes;
+----------+
| max(age) |
+----------+
|       38 |
+----------+
1 row in set (0.02 sec)
```

## group by 分群

- 不同等級的平均年齡
```
mysql> select hero_level, avg(age) from heroes group by hero_level;
+------------+----------+
| hero_level | avg(age) |
+------------+----------+
| S          |  26.0000 |
| A          |  25.1818 |
| B          |  24.4000 |
| C          |  21.0000 |
+------------+----------+
4 rows in set (0.03 sec)
```


- 不同等級的人數和平均年齡
```
mysql> select hero_level, count(*) as hero_number, avg(age) from heroes group by hero_level;
+------------+-------------+----------+
| hero_level | hero_number | avg(age) |
+------------+-------------+----------+
| S          |          16 |  26.0000 |
| A          |          18 |  25.1818 |
| B          |          18 |  24.4000 |
| C          |          15 |  21.0000 |
+------------+-------------+----------+
4 rows in set (0.01 sec)
```

Rails可能的寫法
```
hero.select(:hero_level, average(:age)).group_by(:hero_level)
```

## distinct挑出不同的

```
mysql> select distinct hero_level from heroes;
+------------+
| hero_level |
+------------+
| S          |
| A          |
| B          |
| C          |
+------------+
```

## order by 排序


order by hero_rank desc (反向)

```
mysql> select name, hero_level, hero_rank from heroes order by hero_rank desc;
+-----------------------------+------------+-----------+
| name                        | hero_level | hero_rank |
+-----------------------------+------------+-----------+
| 紅鼻子                      | C          |       385 |
| 嗡嗡俠                      | C          |       331 |
| 憤怒俠                      | C          |       255 |
| 炸藥俠                      | C          |       221 |
| 機件異能者                   | C          |       133 |
| 裝甲股長                     | C          |       111 |
...
```

# 多對多查詢

- 哪些怪獸被琦玉殺掉？
- 哪些怪獸被琦玉和傑諾斯殺掉？

```
mysql> select name from monsters where kill_by = 35;
+-----------------------+
| name                  |
+-----------------------+
| 疫苗人                |
| 蟹爪蘭帝              |
| 天才與筋肉兄弟         |
| 蚊少女                |
...

mysql> select name from monsters where kill_by IN {
    select id
    from heroes
    where name = '琦玉'
}

mysql> select name from monsters where kill_by IN {
    select id
    from heroes
    where name in ('琦玉', '傑諾斯')
}
```

## ER圖
![](https://i.imgur.com/Krl29A8.png)


# 交集 join

`t1`表 `left join` `t2`表
或是
`t2`表 `right join` `t1`表
結果會一樣

```
mysql> select t1.username, t2.prize, t2.country from t1 left join t2 on t1.username = t2.name order by country DESC;

mysql> select t1.username, t2.prize, t2.country from t2 right join t1 on t1.username = t2.name order by country DESC;

+----------+-------+---------+
| username | prize | country |
+----------+-------+---------+
| Aaron    | S     | Taiwan  |
| Sherly   | A     | Taiwan  |
| Andy     | S     | Korea   |
| David    | A     | Japan   |
| Jack     | C     | Italy   |
| John     | NULL  | NULL    |
| Mary     | NULL  | NULL    |
| Audrey   | NULL  | NULL    |
| Lisa     | NULL  | NULL    |
| Carl     | NULL  | NULL    |
| Daniel   | NULL  | NULL    |
| Eddie    | NULL  | NULL    |
| Jackson  | NULL  | NULL    |
| Jay      | NULL  | NULL    |
+----------+-------+---------+
```


## 怪獸被誰打倒的?
- 被S級英雄打倒的所有反派
```
mysql> 
Select m.name, m.danger_level, h.name 
From monsters as m left join heroes as h 
On m.kill_by = h.id 
Where m.kill_by is NOT NULL and h.hero_level = 'S';

+--------------------+--------------+-----------------+
| name               | danger_level | name            |
+--------------------+--------------+-----------------+
| 裝甲猩猩           | 鬼           | 傑諾斯          |
| 森林王             | 鬼           | 傑諾斯          |
| 森林族             | 虎           | 傑諾斯          |
| 蟲神               | 鬼           | 超合金黑光      |
| 大王花教父         | 鬼           | 金屬球棒        |
| 蜈蚣前輩           | 鬼           | 金屬球棒        |
| 碎犀角力士         | 虎           | 原子武士        |
| 百目章魚           | 鬼           | 龍卷            |
| 顏剝               | 鬼           | 傑諾斯          |
| 邪眼               | 鬼           | 龍卷            |
| 蜈蚣後輩           | 虎           | 金屬球棒        |
| 捕人草             | 虎           | 金屬球棒        |
| 漁福瑞             | 虎           | 背心大師        |
| 死亡彩蚴吸蟲       | 虎           | 超合金黑光      |
| 熱狗               | 狼           | 龍卷            |
| 古代王             | 龍           | 龍卷            |
+--------------------+--------------+-----------------+
16 rows in set (0.00 sec)
```