HackMD
資料庫 Database
SQL 語法介紹
CRUD 之新增C、查詢R、更新U、刪除D
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:
Eg. Redis / MongoDB
Ref:
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 二進位大型物件
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
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;
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
選擇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
Ref
- 查詢年紀在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)
