INSERT INTO
INSERT INTO `表格名稱` ('欄位1', '欄位2', ...) VALUES ('值1', '值2', ...);
範例:
資料表「member」結構:
欄位名稱 | 類型 | 其它 |
_id | int | PRIMARY KEY |
name | text | |
age | int | |
datetime | timestamp |
INSERT INTO member(name, age) VALUES('Aaron', 18);
INSERT INTO `表格名稱` ('欄位1', '欄位2', ...) VALUES ('值1', '值2', ...);
欄位名稱 | 類型 | 其它 |
_id | int | PRIMARY KEY |
name | text | |
age | int | |
datetime | timestamp |
INSERT INTO member(name, age) VALUES('Aaron', 18);
SELECT是SQL語法中最基本用來將資料從資料表中選出的指令。
SELECT "欄位" FROM "表格名";
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 42 |
SELECT * FROM member;
SELECT name FROM member;
DISTINCT用從資料表中選出某個欄位資料,但重複的資料會被忽略。
SELECT DISTINCT `欄位` FROM `表格名`;
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 31 |
SELECT DISTINCT age FROM member;
age 18 31 50
有時候,並不需要取得全部資料,而是根據某些條件取得符合條件的資料,這時就可以透過WHERE指令來加上要取得的資料條件。
SELECT `欄位` FROM `表格名` WHERE 條件;
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 42 |
SELECT name FROM member WHERE age >= 40;
name Andy Jacky
透過AND或OR指令,可以在WHERE的指令當中結合多個條件來使用更複雜的條件篩選資料,使用小括號「(」和「)」可以用來改變條件的優先順序。
SELECT `欄位` FROM `表格名` WHERE 簡單條件 AND/OR 簡單條件 AND/OR ...;
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 42 |
SELECT name FROM member WHERE age >= 30 AND age < 40;
name Leon
IN指令可以直接指定欄位值來選出符合該欄位值的資料。
SELECT `欄位` FROM `表格名` WHERE `欄位` IN('值1', '值2', ...);
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 42 |
SELECT * FROM member WHERE age IN(40, 50);
_id name age 3 Andy 50
SELECT * FROM member WHERE age = 50;
BETWEEN指令可以用來透過指定一個藍衛的範圍來選出符合該範圍內的資料。
SELECT `欄位` FROM `表格名` WHERE `欄位` BETWEEN '值1' AND '值2';
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 42 |
SELECT * FROM member WHERE age BETWEEN 30 AND 40;
_id name age 2 Leon 31
LIKE指令可以設定一個模式,透過該設定的模式來選出符合該模式的資料。
SELECT `欄位` FROM `表格名` WHERE `欄位` LIKE '模式';
模式範例:
- 'A_D':所有A開頭,中間為任一字元,且Z結尾的字串,A2D、AZD、AiD皆符合。
- '123%': 所有以 '123' 開頭的字串。'123D'、'123456'、'123ZZZ123' 都符合。
- '%AR%': 所有含有'AN'這兩個字元的字串。'AARON'、'YOU ARE GOOD'、'WHO ARE YOU?' 都符合。
- '_T%': 所有第二個字母為 'T' 的字串。'NTT'、'AT&T'、'ET is coming'都符合。
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 42 |
SELECT * FROM member WHERE name LIKE 'A%';
_id name age 1 Aaron 18 3 Andy 50
ORDER BY指令可以設定將選出的資料由小到大(ascending) 或是由大到小 (descending)來排列順序。
SELECT `欄位` FROM `表格名` [WHERE '條件'] ORDER BY `欄位一` [ASC, DESC], '欄位二' [ASC, DESC], ...;
_id | name | age |
1 | Aaron | 18 |
2 | Leon | 31 |
3 | Andy | 50 |
4 | Jacky | 42 |
SELECT name, age FROM member ORDER BY age ASC;或是也可以直接用SQL中SELECT指令後面欄位的順序來決定欄位的排序:
SELECT name, age FROM member ORDER BY 2 DESC;
name age Aaron 18 Leon 31 Jacky 42 Andy 50
當需要根據某個欄位值來使用函數處理該相同欄位值的時候,就需要使用GROUP BY。也就是當選取不只一個欄位,且其中至少一個欄位有包含函數的運用時,就需要用到GROUP BY指令。在這個情況下,除了有包括函數的欄位外,其它欄位都需要將其放在GROUP BY的子句中。
SELECT `欄位1`, SUM('欄位2') FROM `表格名` GROUP BY `欄位1`;
_id | name | money |
1 | Aaron | 180 |
2 | Jacky | 320 |
3 | Leon | 310 |
4 | Jacky | 420 |
5 | Andy | 500 |
6 | Aaron | 220 |
7 | Jacky | 190 |
SELECT name, SUM(money) FROM member GROUP BY name;
name money Aaron 400 Leon 310 Jacky 930 Andy 500
修改表格中資料。
UPDATE `表格名` SET `欄位1` = 新值 WHERE '條件';
_id | name | money |
1 | Aaron | 180 |
2 | Jacky | 320 |
3 | Leon | 310 |
4 | Andy | 500 |
UPDATE member SET money = 999 WHERE name = 'Aaron';
_id | name | money |
1 | Aaron | 999 |
2 | Jacky | 320 |
3 | Leon | 310 |
4 | Andy | 500 |
從資料表中刪除資料。
DELETE FROM `表格名` WHERE '條件';
_id | name | money |
1 | Aaron | 180 |
2 | Jacky | 320 |
3 | Leon | 310 |
4 | Jacky | 420 |
5 | Andy | 500 |
6 | Aaron | 220 |
7 | Jacky | 190 |
DELETE FROM member WHERE name='Aaron' OR name='Andy';
_id | name | money |
2 | Jacky | 320 |
3 | Leon | 310 |
4 | Jacky | 420 |
7 | Jacky | 190 |