# SQL語法

密碼:1234

> 市面上所有商用資料庫系統皆支援ANSI SQL語法。
- 由於各廠商為突顯自家資料庫系統之獨特性，會對部份的ANSI SQL語法做些微修改，以支援自家資料庫系統所發展之各項功能

### 零. MySQL 註解
`/* 想講的話 */`

### 一. SQL指令種類

### 二. SQL資料型態
#### 1. 字串 (Character Strings)
- CHAR(n)：固定長度字元串 (n為字元個數)
- VARCHAR(n)：變動長度字元串
- BIT(n)：固定長度位元串 (n為位元個數)
- BIT VARING(n)：變動長度位元串

#### 2. 數值串(Numeric Strings)

- INT, INTEGER：整數
- DEC(m,n), DECIMAL(m,n), NUMERIC(m,n)：格式化數值 (m：總位數或精確度，n：小數位數)
- SMALLINT：短整數
- FLOAT：浮點數
- REAL：單精度實數 (32bits)
- DOUBLE PRECISION：雙精度實數 (64bits)

#### 3. 日期/時間 (Date/Time)資料型態
- DATE：一般格式為 YYYY-MM-DD
- TIME：一般格式為 HH:MM:SS
- TIMESTAMP：時間戳記，由DATE+TIME+六位以上小數秒數
- 用以記錄交易進入系統的時間順序
- INTERVAL：時間區間


### 三. 資料類型可以分為數字、字串與日期時間三個類型：

- 數字類型：包括INT、DECIMAL、NUMERIC、FLOAT等。
- 字串類型：包括CHAR、VARCHAR、TEXT等。
- 時間類型：包括DATE、DATETIME、TIMESTAMP等。

## 四. 建立資料庫的基本操作
> DDL(Data Definition Language,資料定義語言)
下面的指令可以對資料庫進行基本的操作。
- 使用指令的時候，結尾一定要加上分號（;）作為指令的結束。

#### 1. 顯示SQL中資料庫的狀況

show databases;


#### 2. 建立資料庫
> 在create database後面接上想要建立的資料庫名稱。要注意的是，資料庫的名稱不可以有空白。

create database 資料庫名稱;


#### 3. 指定當前要使用的資料庫。才可以操作該資料庫

use 想要操作的資料庫名稱;


#### 4. 查看目前選定的資料庫是哪一個

select database();


#### 5. 刪除特定名稱的料庫

drop database 想刪除DB的名稱;


#### 6. 查看特定資料庫中的所有資料表
> 記得要先 `use 資料庫;`

SHOW TABLES;


## 五. 資料表的基本操作

#### 1. 建立資料表

CREATE TABLE member(
    username VARCHAR(20),
    email VARCHAR(40),
    phone VARCHAR(20),
    age INT
);


#### 2. 檢視創建的資料表

desc 資料表名稱;


#### 3. 刪除指定的資料表

drop table 資料表名稱;


#### 4. 更改資料表內容
ALTER TABLE: 更改一個關聯 (表格) 中之某欄位的基本定義與限制。
包括：
- 增加(ADD)欄位、刪除(DROP)欄位
- 修改(ALTER/CHANGE/MODIFY…) 欄位預設值、定義、或名稱
- 更改表格名稱(RENAME)
- 更改表格類型(ENGINE)

ALTER TABLE 資料表名稱
ADD/DROP/ALTER/CHANGE/MODIFY/ENGINE/RENAME 新欄位名稱 型態;

#### - 增加欄位：

#### - 刪除欄位 :

#### - 修改欄位 1 (增改/刪除預設值)：


#### - 修改欄位 2 (更改欄位定義，不含修改欄位名)：

#### - 修改欄位 3 (更改欄位定義，含修改欄位名)：

#### - 更改表格類型：

#### - 更改表格名稱：

## 六. 針對關聯表中的部分資料進行處理
> DML(Data Manipulation Language,資料處理語言)

#### 1. 將數據資料插入資料表裡面。
> 我們可以一次插入一筆數據資料進入資料表，也可以一次插入多筆資料進入資料表


#插入部分指定的欄位資料
INSERT INTO 資料表名稱(欄位名1, 欄位名2,…) VALUES (…)


#插入全部欄位的資料
INSERT INTO 資料表名稱 VALUES 



#### 2. 根據WHERE條件刪除表中的資料

DELETE FROM 表格名 WHERE 條件


#### 3. 根據WHERE條件更改表中的屬性值

UPDATE 表格名稱
SET 欄位名稱 = 新的值
WHERE 須滿足條件



## 七. 利用DQL查詢DB的相關資料
> DQL(Data Query Language,資料查詢語言)


> 只有Select 和 From 是每次一定都要寫的，其他依需求而訂

SELECT * FROM Warriors;   //將FROM 中表格的所有資料顯示出來

### SQL相關運算子

EX. 

### 欄位名稱重複處理
> 較複雜的資料查詢可能會用到多個table,此時可能發生位於不同表格中的同名欄位被一起使用的情況
> 解法: 在使用同名欄位時，加上表格名稱

Warriors.player  //勇士隊此表格中的球員




### 聚合函數(Aggregate function)：
- COUNT(attribute_name): 計算非空屬性值個數
- SUM(attribute_name): 計算屬性中數值的總合
- AVG(attribute_name): 計算屬性中數值的平均
- MAX(attribute_name): 找出屬性中數值的最大值
- MIN(attribute_name): 找出屬性中數值的最小值

#### Ex1.


#### Ex2.


#### Ex3.


---

#### 【注意】利用聚合函數所設立之條件，不能直接寫在WHERE子句
> - Where子句是繼From子句後，第二個會被執行到的SQL查詢語句
> - 若無搭配Group By先做分群，則聚合函數僅會得到單一筆計算結果。若對此計算結果再做條件過濾是會有函數使用上的問題!!

### 欄位取別名(Alias):

表格名稱 as 別名

欄位名稱 as 別名

EX.

### 聚合函數中COUNT的用法：
- COUNT(*): 計算表格中，共有幾筆非空記錄
- COUNT(欄位名稱): 此屬性有幾筆非空值
- COUNT(DISTINCT 欄位名稱): 此屬性有幾筆不同的非空值
> COUNT(DISTINCT: 代表重複資料只算一次

EX.


## 八. 巢狀查詢
> 查詢中又包含另一個查詢



### 巢狀查詢的分類



---
#### 練習題:

EX1. 列出供應商“大勝”有供應給專案使用的零件之平均重量



EX2.列出重量大於供應商代號S2有供應給專案使用之所有零件的零件名稱


EX3. 列出重量大於供應商代號S2有供應給專案使用之任一零件的零件名稱

### [NOT] EXISTS的用法
> 回傳Boolean value
#### EXISTS:
> 此運算子在當子查詢有查詢結果產生時，會回傳TRUE給主查詢，否則便回傳FALSE。
> - 同時，子查詢若有資料產生，會暫存於記憶體以待主查詢使用。
#### NOT EXISTS:
> EXISTS的反義詞。成立回傳False、不成立回傳True

##### EX. 標準子查詢:




##### EX. 關聯子查詢:
EX.
#### Q: ，列出有供應零件'P1'的供應商名稱
- 主查詢的表格，在子查詢中會使用到，它將與子查詢之表格做合併處理，所以是有關的。
- 子查詢在此除告知主查詢它是否有回傳值，其條件式也會影響主查詢。


### 其他運算子

### EX1.

### EX2.

### EX3.

### EX4.

## 合併查詢(JOIN)
> 若使用者要查詢的資料來源，是一個以上的表格資料合併在一起的結果，則此一查詢任務需採用合併查詢

#### 合併查詢可分成：
### Inner Join (內部合併)
> 將多個表格的內容以某合併條件結合在一起，只顯示符合條件的資料


EX. 列出位於相同城市的專案名稱、供應商名稱與兩者之城市名稱。
> (學習重點：1. 內部合併; 2. 對表格取別名)


#### 寫法1

#### 寫法2

### Outer Join (外部合併)
> 將多個表格的內容以某合併條件結合在一起，而不符合條件的資料將視需求以不同方式予以呈現。
>
> 當不滿足合併條件的資料也想要查看時可用

#### 外部合併的類型可分為：
- 左外部合併 (Left Outer Join)
> 左邊表格重要,即便他不滿足合併條件也會顯示出來。而右邊表格只會顯示符合條件的資料

- 右外部合併 (Right Outer Join)
> 反之，右邊表格全顯示，左邊須符合條件才顯示

- 全外部合併 (Full Outer Join):
> 不管條件全部都顯示，但MySQL不支援全外部合併指令，但可以用 `Union` 達到相同的效果。

> OUTER 此關鍵字可寫可不寫(但老師建議要寫，為了日後的好維護)

#### EX1. 列出位於相同城市的專案名稱、城市與供應商名稱、城市，但是不符條件的專案資料也請一併列出。(學習重點：1. 左外部合併; 2. 對表格取別名)


---

#### EX2. 列出位於相同城市的專案名稱、城市與供應商名稱、城市，但是不符條件的專案資料也請一併列出。(學習重點：1. 右外部合併; 2. 對表格取別名)



---

#### EX3. 列出位於相同城市的專案名稱、城市與供應商名稱、城市，但是不符條件的專案資料也請一併列出。(學習重點：1. 全外部合併; 2. 對表格取別名)


---

## DDL(Data Definition Language,資料定義語言)
> DL主要有CREATE, DROP, ALTER三個指令，並可針
對以下不同的資料庫系統物件進行操作：
- 資料庫 (database)
- 表格 (Table)
- 景觀 (View)
- 索引 (Index)
- …

### View(景觀)


#### 建立View


#### 檢視View

#### 刪除View


#### View的優點：
- 隱藏不需要或具私密性的資料
- 同一關聯表可建立多種不同的觀點，讓使用者以不同的角度看同一份資料。
#### 景觀的缺點：
- 景觀的DML操作有諸多限制，無法提供與實際表格完全相同的操作 (畢竟它是屬於虛擬表格)。

## Index (索引)







#### 建構索引的時機：
- 表格資料量龐大
- 欲建構索引的欄位，極少有DML的操作
- 欲建構索引的欄位，經常作為查詢條件使用
#### 不適合建構索引的狀況：
- 表格資料量很少
- 欲建構索引的欄位，經常有DML的操作
- 欲建構索引的欄位，極少作為查詢條件使用

## DCL (Data Control Language,資料控制語言)




- REVOKE敘述只移除使用者的資料操作權限而非移除使用
者，該使用者資訊仍存在於資料庫系統中。
- 要完整的移除使用者，必須使用DELETE指令明確地由資
料庫系統之相關權限設定表格中，將使用者紀錄刪除。

---

### 練習題:

CREATE TABLE Supplier
       (供應商代號 CHAR(4),
        供應商名稱 CHAR(10),
        城市       CHAR(6),
        PRIMARY KEY(供應商代號)
       );

CREATE TABLE Project
       (專案代號 CHAR(4),
        專案名稱 CHAR(10),
        城市     CHAR(6),
        PRIMARY KEY(專案代號)
       );

CREATE TABLE Component
       (零件代號 CHAR(4),
        零件名稱 CHAR(10),
        顏色     CHAR(4),
        重量     INT,
        PRIMARY KEY(零件代號)
       );

CREATE TABLE Project_supp_Component
       ( 供應商代號 CHAR(4),
         零件代號 CHAR(4),
         專案代號 CHAR(4),
         數量     INT,
         PRIMARY KEY(供應商代號,專案代號,零件代號),
         FOREIGN KEY(供應商代號) REFERENCES Supplier(供應商代號) ON Delete CASCADE,
         FOREIGN KEY(專案代號) REFERENCES Project(專案代號) ON Delete CASCADE,
         FOREIGN KEY(零件代號) REFERENCES Component(零件代號) ON Delete CASCADE
       )ENGINE=INNODB;

SHOW TABLES;

Ex2. 建立的四個空表格插入其應有的資料

INSERT INTO Supplier VALUES ('S1', '大勝', '台南');
INSERT INTO Supplier VALUES ('S2', '冠軍', '高雄');
INSERT INTO Supplier VALUES ('S3', '無敵', '台中');
INSERT INTO Supplier VALUES ('S4', '一級棒', '高雄');

INSERT INTO Project VALUES ('J1', '火星', '台中');
INSERT INTO Project VALUES ('J2', '土星', '高雄');
INSERT INTO Project VALUES ('J3', '太陽', '台北');

INSERT INTO Component VALUES ('P1', '螺絲釘', '黑',14);
INSERT INTO Component VALUES ('P2', '螺帽', '黑',16);
INSERT INTO Component VALUES ('P3', '齒輪', '綠',27);
INSERT INTO Component VALUES ('P4', '板手', '藍',63);
INSERT INTO Component VALUES ('P5', '撬子', '棕',80);

INSERT INTO Project_supp_Component VALUES ('S1', 'P1', 'J2',300);
INSERT INTO Project_supp_Component VALUES ('S1', 'P1', 'J3',100);
INSERT INTO Project_supp_Component VALUES ('S1', 'P4', 'J1',500);
INSERT INTO Project_supp_Component VALUES ('S2', 'P2', 'J1',400);
INSERT INTO Project_supp_Component VALUES ('S2', 'P3', 'J3',600);
INSERT INTO Project_supp_Component VALUES ('S3', 'P1', 'J2',300);
INSERT INTO Project_supp_Component VALUES ('S4', 'P3', 'J1',200);
INSERT INTO Project_supp_Component VALUES ('S4', 'P4', 'J1',700);
INSERT INTO Project_supp_Component VALUES ('S4', 'P5', 'J2',100);

--檢視一下4個表格內的資料

SELECT * FROM Supplier;
SELECT * FROM Project;
SELECT * FROM Component;
SELECT * FROM Project_supp_Component;


#### 參考資料:
1. 杰哥程式碼: http://debussy.im.nuu.edu.tw/sjchen/Database/Final/SQL%E6%93%8D%E4%BD%9C%E7%AF%84%E4%BE%8B.txt

3. 杰哥數位教室影片: http://debussy.im.nuu.edu.tw/sjchen/DataBaseMan_Final.html

2. https://medium.com/web-design-zone/mysql資料庫的安裝與基本操作-f36a079afd85