---
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 二進位大型物件
`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;
```
選擇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圖
# 交集 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)
```
Sign in with Wallet
Connect another wallet