[SQL] 基本指令

程式語言：SQL
參考 W3Schools SQL

簡介：基本運用資料庫指令
分號代表指令結束

Data Type

SQLite

	實際
INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8	INTEGER
CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB	TEXT
BLOB no datatype specified	BLOB
REAL DOUBLE DOUBLE PRECISION FLOAT	REAL
NUMERIC DECIMAL(10,5) BOOLEAN DATE DATETIME	NUMERIC

CREATE 建立資料表格

CREATE TABLE games (id INTEGER, name TEXT, age INTEGER);

--primary key 獨一無二且不能為 NULL
CREATE TABLE artists(id INTERGER PRIMARY KEY, name TEXT);

--自動增加 primary key
CREATE TABLE book(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, page INTEGER)

INSERT INTO 插入新的資料

INSERT INTO games (id, name, age) VALUES (1, 'funny', 21);

SELECT 選擇資料，可指定 col

SELECT * FROM games;

SELECT name FROM games;

-- 不重覆挑選資料
SELECT DISTINCT name FROM movies;

-- 挑選同名資料的方法
SELECT albums.name,
albums.year, games.name
FROM albums, games;

UPDATE 更新資料

UPDATE games
SET age = 22 
WHERE id = 1; 

ALTER TABLE 更改表格

ALTER TABLE games 
ADD COLUMN comment TEXT;

ALTER TABLE games
DROP COLUMN name;

DELETE FROM 刪除資料

DELETE FROM game 
WHERE comment IS NULL;

WHERE 判斷式

SELECT * FROM games
WHERE rating > 8;

SELECT * FROM games
WHERE rating = 8;

SELECT * FROM games
WHERE rating != 8;

LIKE 字串判斷

• % 表示任意長度的字串
• _ 表示一個字
• [charlist] 符合這裡面的字
• [^charlist] 不符合這裡面的字

-- 內容為 a
SELECT * FROM movies
WHERE name LIKE 'a';

-- 開頭為 a
SELECT * FROM movies
WHERE name LIKE 'a%';

-- 字尾為 a
SELECT * FROM movies
WHERE name LIKE '%a';

BETWEEN 介於範圍

--開頭為 A 但不包括 J
SELECT * FROM games
WHERE name BETWEEN 'A' AND 'J';

-- 包括 2000
SELECT * FROM games WHERE year BETWEEN 1990 AND 2000;

AND/OR 條件連結

SELECT * FROM games
WHERE year BETWEEN 1990 and 2000
AND kind = 'action';

SELECT * FROM games
WHERE kind = 'action'
OR year < 1980;

ORDER BY 排序資料

--反序
SELECT * FROM games
ORDER BY rating DESC;

--正序 只顯示 3 筆
SELECT * FROM games
ORDER BY rating ASC;

LIMIT 限制顯示資料數目

SELECT * FROM games
LIMIT 3;

COUNT 計算總數

SELECT COUNT(*) FROM games; 

SELECT COUNT(*) FROM games
WHERE rating > 7; 

GROUP BY 分組

SELECT price, COUNT(*) FROM games
GROUP BY price;

SUM 加總

SELECT SUM(downloads) FROM games;

MAX 最大值

SELECT MAX(downloads) FROM games;

MIN 最小值

SELECT MIN(downloads) FROM games;

AVG 平均值

SELECT AVG(downloads) FROM games;

ROUND 四捨五入進位

SELECT price, ROUND(AVG(downloads), 2) FROM games
GROUP BY price;

JOIN ON

取交集 同 INNER JOIN ON

SELECT
 *
FROM
 albums
JOIN games ON
 albums.games_id = games.id;

LEFT JOIN ON 

取交集 & 左邊 table 無符合的則右邊為 NULL

SELECT
 *
FROM
 albums
LEFT JOIN games ON
 albums.games_id = games.id;

RIGHT JOIN ON 

取交集 & 右邊 table 無符合的則左邊為 NULL

SELECT
 *
FROM
 albums
RIGHT JOIN games ON
 albums.games_id = games.id;

FULL JOIN ON 取聯集，不符合者則另一邊為 NULL

SELECT
 *
FROM
 albums
FULL JOIN games ON
 albums.games_id = games.id;

AS 更改顯示行名

SELECT
 albums.name AS 'Album',
        albums.year,
        games.name AS 'Game'
FROM
 albums
JOIN artists ON
 albums.games_id = games.id
WHERE
 albums.year > 1980;