# 資料庫 SQL語法
###### tags: `SQL`
## CRUD
### 建立資料庫：
```sql!
$ CREATE database <nice_db>
$ DROP database <nice_db>
```
### 資料型態：
* CHAR/VARCHAR/TEXT
* INTEGER/DECIMAL
* DATE/DATETIME
* BLOB=Binary Large Object
### 建立資料表：
```sql!
CREATE TABLE "users" (
"id" INTEGER NOT NULL UNIQUE,
"name" TEXT NOT NULL,
"age" INTEGER NOT NULL,
PRIMARY KEY("id" AUTOINCREMENT)
);
```
儘量使用大寫下SQL指令
CHAR(10)沒填寫完的部分會填空，建議長度固定時使用
VARCHAR(10)會多花一個byte存長度，建議長度不固定時使用
### 追加欄位：
```sql!
ALTER TABLE heroes
ADD COLUMN super_power VARCHAR(100)
```
### 刪除欄位
```sql!
ALTER TABLE heroes
DROP COLUMN super_power
```
### 刪除資料表
```sql!
DROP TABLE users
```
### 新增資料
```sql!
INSERT INTO heroes(id, name, hero_level, age)
VALUES (300, 'KK_hero', 'S', 18)
字串要用''包起來
```
### 選擇所有檔案
```sql
SELECT *
FROM heros
```
### 撈出特定條件的所有檔案
```sql!
SELECT *
FROM heroes
WHERE hero_level = 'S'
SELECT *
FROM heroes
WHERE hero_level = 'S' AND gender = 'F'
```
### 只撈出特定欄位
```sql!
SELECT name, age
FROM heroes
WHERE hero_level = 'S' AND gender = 'F'
```
### 撈出沒填資料的欄位
```sql!
SELECT *
FROM heroes
WHERE age is NULL
```
### 相似字查詢
```sql!
SELECT *
FROM heroes
WHERE name like '%背心%'
```
### 查詢數字區間
```sql!
SELECT *
FROM heroes
WHERE age >=10 AND age <=25
SELECT *
FROM heroes
WHERE age BETWEEN 10 AND 25;
```
### OR查詢
```sql
SELECT *
FROM heroes
WHERE hero_level = 'S' OR hero_level = 'A'
SELECT *
FROM heroes
WHERE hero_level IN ('S','A')
```
### not
```sql!
SELECT *
FROM heroes
WHERE hero_level != 'S'
SELECT *
FROM heroes
WHERE hero_level <> 'S'
```
### 不是...也不是...
```sql!
SELECT *
FROM heroes
WHERE hero_level != 'S' AND hero_level != 'A'
SELECT *
FROM heroes
WHERE hero_level NOT IN ('S', 'A')
```
### 更新資料
```sql!
UPDATE heroes
SET age = 10, hero_level = 'A'
WHERE id = 25
```
所有人都+1歲：
```sql!
UPDATE heroes
SET age = age +1
```
### 刪除資料
```sql!
DELETE FROM heroes
WHERE hero_level = 'C'
```
## 進階查詢
### 總數：
```sql!
SELECT COUNT(*)
FROM heroes
WHERE hero_level = 'A'
```
### 總和：
```sql!
SELECT SUM(age)
FROM heroes
WHERE hero_level = 'S'
```
### 平均：
```sql!
SELECT AVG(age)
FROM heroes
WHERE hero_level = 'S'
```
min, max
### 分組：
```sql!
SELECT hero_leel, sum(age)
FROM heroes
GROUP by hero_level
```
```sql!
SELECT DISTINCT hero_llevel
FROM heroes
```
```sql!
SELECT *
FROM heroes
WHERE hero_level = 'S'
ORDER BY age DESC
```
```sql!
SELECT *
FROM heroes
WHERE hero_level = 'S'
ORDER BY age DESC
LIMIT 6
```
```sql!
SELECT *
FROM heroes
LIMIT 6
OFFSET 6
```
### 多個表格的查詢
ER diagram = Entity Relationship Diagram
子查詢：
```sql!
SELECT *
FROM monsters
WHERE kill_by = (
SELECT *
FROM heroes
WHERE name in ("埼玉","傑諾斯")
)
```
主鍵，獨一無二，not NULL
### 交集
inner/left/right
```sql!
SELECT *
FROM t1
INNER JOIN t2
ON t1.username = t2.name
SELECT *
FROM t1
RIGHT JOIN t2
ON t1.username = t2.name
SELECT *
FROM t1
LEFT JOIN t2
ON t1.username = t2.name
```
```sql!
SELECT h.name, m.name
FROM battle_histories as b
JOIN heroes as h
JOIN monsters as m
on b.hero_id = h.id AND b.monster_id = m.id
```
Sign in with Wallet
Connect another wallet