簡易SQL語法

Select :
SELECT 欄位
FROM 資料表
[LIMIT 索引數, 資料筆數]
(LIMIT 是分頁用的, 索引數從零開始, 資料筆數是每頁多少筆資料)

Distinct ( 取不重複的值 ):
SELECT DISTINCT 欄位
FROM 資料表

Where :
SELECT 欄位
FROM 資料表
WHERE 條件

Inner Join:
SELECT 欄位1, 欄位2, ... , 欄位n
FROM 資料表1
INNER JOIN 資料表2
ON 資料表1.依據欄位 = 資料表2.依據欄位
[WHERE 條件]
(結合表格 1 的依據欄位跟表格 2 依據欄位相等的資料)

And/Or :
SELECT 欄位
FROM 資料表
WHERE 簡單條件 {[ANDOR] 簡單條件}+

In :
SELECT 欄位
FROM 資料表
WHERE 欄位 IN ('值1', '值2', ...)

Between :
SELECT 欄位
FROM 資料表
WHERE 欄位 BETWEEN 值1 AND 值2

Like :
SELECT 欄位
FROM 資料表
WHERE 欄位 LIKE {模式}
( _ 底線代表一字元, % 符號代表零或多)

Order By :
SELECT 欄位
FROM 資料表
[WHERE 條件]
ORDER BY 欄位 [ASC, DESC]

Count (計算筆數):
SELECT COUNT(欄位)
FROM 資料表
[WHERE 條件]

MySQL支援的數值函數:
COUNT, SUM, MAX, MIN, AVG
MySQL支援的字串函數: LOWERUPPER
SUBSTRING(欄位名, 1起算的起始索引, 字數)
INSTR(搜尋欄位, 搜尋字串)
CONCAT(值1,值2, ... ,值n)
TRIM

MySQL支援的日期時間函數:
NOW
YEAR
MONTH
DAYOFMONTH
DAYOFWEEK
DAYOFNAME

DATE_FORMAT(欄位, 模式)
( %Y 年份4碼, %y 年份2碼, %M 字串月份, %m 數字月份, %D 字串天 %d 數字天,
%W 字串星期幾, %w 數字星期幾, %H 字串2碼小時24時制, %h 字串2碼小時12時制,
%i 分鐘, %s 秒數, %p AM或PM, ...)

Group By :
SELECT 欄位1, SUM(欄位2)
FROM 資料表
GROUP BY 欄位1

Having (函數條件):
SELECT 欄位1, SUM(欄位2 )
FROM 資料表
GROUP BY 欄位1
HAVING 函數條件

Alter :
(修改資料表--操作時會鎖定表且占用大量硬碟空間, 除非必要否則不要使用)
ALTER TABLE 資料表
ADD 欄位 資料類型 屬性 ...
[FIRST/AFTER 欄位];

ALTER TABLE 資料表
DROP COLUMN 欄位;

ALTER TABLE 資料表
CHANGE COLUMN 欄位 資料類型 屬性 ...;

ALTER TABLE 資料表
RENAME AS 資料表新名稱;

Create Table :
CREATE TABLE "表格名"("欄位 1" "欄位 1 資料種類","欄位 2" "欄位 2 資料種類"",... )

Drop Table :
DROP TABLE "表格名"

Truncate Table (清空表格但保留架構):
TRUNCATE TABLE "表格名"

Insert Into
INSERT INTO "表格名" ("欄位1", "欄位2", ...)
VALUES ("值1", "值2", ...)

Update
UPDATE "表格名"
SET "欄位1" = [新值]
WHERE {條件}

Delete From
DELETE FROM "表格名"
WHERE {條件}

SQL資料類型

建立欄位:
欄位名 欄位類型 [屬性]
例:
username VARCHAR(30) NOT NULL
user_id INTEGER AUTO_INCREMENT

常用 SQL 資料類型:
SMALLINT -- 16 位元整數
INT -- 4 位元整數, -2147483648 ~ 2147483647
BIGINT -- 8 位元整數, -9223372036854775808 ~9223372036854775807
FLOAT -- 4 位元浮點數, 精度低到 6 位, 範圍介於 1x10 的正負 37 次方
DOUBLE PRECISION-- 8 位元浮點數, 精度低到 15 位, 範圍介於 1x10 的 -307 次方~ 1x10 的 308 次方
REAL -- 在 MySQL 中為 FLOAT 的別名
NUMERIC(p,d) -- 10進位數字, 指定精度 p 與小數位數 d (皆為正整數), 非近似值, 效能不佳
DECIMAL(p,d) -- 10進位數字, 指定精度 p 與小數位數 d (皆為正整數), 非近似值, 效能極差

SQL 日期時間類型:
DATE -- 日期
TIME -- 時間
TIMESTAMP -- 日期時間 (MySQL 為 DATETIME )

常用字元 SQL 資料類型:
CHAR(n) -- 固定長度字串, 一般狀況下最大允許值為 255
VARCHAR(n) -- 可變長度字串, 一般最大允許值為 255
TEXT -- 非 ANSI SQL 標準, 用來儲存大量文字資料
NCHAR(n) -- CHAR(n)的變體, 對於不能指定整個資料庫或表的"預設字元集"的 server, 用於輸入 unicode 字元
NVARCHAR(n) -- VARCHAR(n)的變體, 對於不能指定整個資料庫或表的"預設字元集"的 server, 用於輸入 unicode 字元

常用二進位 SQL 資料類型:
BLOB -- MySQL, Oracle 使用 -- MySQL中最大可到 216 bytes, 更大檔案可用 MEDIUMBLOB( 2 的 24次方 bytes) 和 LONGBLOB( 2 的 32次方 bytes); Oracle 的 BLOB 則可到 4GB
image -- MS SQL server 使用 -- 最大 2GB
bytea -- PostgreSQL 使用

欄位屬性:
NOT NULL -- 不允許為空
DEFAULT value -- 給定預設值
AUTO_INCREMENT -- 自動遞增
PRIMARY KEY -- 主鍵
UNIQUE -- 唯一值

MySQL資料庫操作-使用者管理與資料庫建立

登入MySQL:
mysql --user=資料庫用戶名 -p
(參數p 代表需要輸入密碼)

查看資料庫:
SHOW DATABASES;

切換資料庫:
USE 資料庫;

查看目前資料庫有哪些資料表:
SHOW TABLES;

查看資料表:
DESCRIBE 資料表;

查看排序整理(collation):
SHOW COLLATION;


用戶:
MySQL 的用戶資料存放在 mysql.user 資料表裡面
可以對它直接做查詢及修改
如:
SELECT User,Host FROM mysql.user

建立用戶:
GRANT 權限
ON 資料庫 . 表
TO '使用者名稱' @ '域名'
IDENTIFIED BY '密碼欄位'
WITH GRANT OPTION;
(如果有WITH GRANT OPTION表示允許用戶對其他用戶進行授權)
例:
GRANT CREATE, DROP, SELECT, UPDATE, INSERT, DELETE
ON dbMember.*
TO 'edward' @ 'localhost'
IDENTIFIED BY 'userpassword'

刪除用戶:
DROP USER '使用者名稱' @ '域名'

刪除特定權限:
REVOKE 權限
ON 資料庫 . 表
FROM '使用者名稱' @ '域名'

建立資料庫:
CREATE DATABASE 資料庫名稱
DEFAULT CHARACTER SET 預設字元集
DEFAULT COLLATE 排序整理名稱;
例:
CREATE DATABASE dbMember
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;

建立資料表:
CREATE TABLE 資料表名稱(
欄位名 欄位類型 [屬性],
......
[索引]
)[屬性];
例:
CREATE TABLE MyMem(
user_id INTEGER AUTO_INCREMENT PRIMARY KEY,
user_name VARCHAR(50) NOT NULL,
user_email VARCHAR(200) NOT NULL,
birthday DATE,
INDEX (user_name)
)ENGINE= InnoDB;

在已存在的表建立索引:
CREATE INDEX 索引名稱 ON 資料表名稱(欄位名稱);

外鍵:
(設定外鍵, 資料表都必須使用支援SQL Transcation 的 InnoDB 引擎, 否則會被忽略)
FOREIGN KEY (欄位名稱) REFERENCES 另一資料表(另一資料表的欄位) ON DELETE CASCADE;
設定外鍵的用意在於子表的紀錄必須對應到父表, 是一種驗證機制 (如討論區的回應一定是跟主題中的某筆記錄相關)
ON DELETE CASCADE表示"級聯刪除"(cascading delete), 當父表的紀錄被刪除時, 一併刪除在子表中外鍵設為該資料的相關記錄

刪除: (一旦刪除, 救不回來, 不應賦予一般使用者此權限)
DROP DATABASE 資料庫;
DROP TABLE 資料表;