SQL
SELECT
WHERE
inclusive: begin and end values are included
%: 0文字以上の任意の文字列
ch%: chから始まる文字列
%ch: chで終わる文字列
%ch%: chを含む文字列
_: 任意の1文字
IS NULL/IS NOT NULL
ORDER BY カラム名 [DESC]
デフォルトは ASC（昇順）
code:sql
-- AND
SELECT *
FROM products
WHERE Price >= 10
AND Price <= 20
-- BETWEEN (inclusive)
SELECT *
FROM products
WHERE Price BETWEEN 10 AND 20 -- same as WHERE Price >= 10 AND Price <= 20
-- OR
SELECT *
FROM products
WHERE country = 'Japan'
OR country = 'Canada;
-- IN
SELECT *
FROM products
WHERE country IN ('Japan', 'Canada'); -- same as WHERE country = 'Japan' OR country = 'Canada'
-- LIKE
SELECT *
FROM products
WHERE name LIKE 'ch%'; -- %は0文字以上の任意の文字列
code:sql
SELECT
ROUND(SUM(Price), 2) AS Total,
ROUND(AVG(Price), 2) AS Average,
MAX(Price) AS Maximum,
MIN(Price) AS Minimum,
COUNT(*) AS NumOfRecords
FROM Products;
SELECT *
FROM Products
WHERE Price = (SELECT MAX(Price) FROM Products);
code:sql
SELECT COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
(INNER) JOIN
LEFT (OUTER) JOIN
code:sql
SELECT ProductName
FROM Products
WHERE ProductID = ANY
(SELECT ProductID
FROM OrderDetails
WHERE Quantity = 10);
コメントの書き方
code:SQL
-- 1行コメント
/* 複数行の
コメント */
データ挿入
code:sql
INSERT INTO テーブル名 VALUES (値, 値...);
INSERT INTO テーブル名 (カラム名1, カラム名2...) VALUES (カラム1の値, カラム2の値...);
データ更新
code:SQL
UPDATE テーブル名
SET カラム名 = 値
WHERE 更新するレコードの条件;
-- example
UPDATE students
SET address = 'Tokyo'
WHERE id = 123;
データ削除
code:sql
DELETE FROM テーブル名
WHERE 削除するレコードの条件;
DELETE FROM students
WHERE id = 123;