自學SQL，只是個人筆記
SELECT
取出table_name中所有的columns
SELECT * FROM table_name;
取出table_name中的column1和column2
SELECT column1,column2
FROM table_name;
SELECT DISTINCT
取出相異的colunm1,column2資料。(a,a)(a,b)也是相異。
SELECT DISTINCT column1,column2
FROM table_name;
計算相異的column1數量。
SELECT COUNT(DISTINCT column1)
FROM table_name;
SELECT COUNT(*) AS table2
FROM (SELECT DISTINCT column1 FROM table_name);
WHERE Clause
取出column1和colum2，如果符合condition。
SELECT column1, column2,
FROM table_name
WHERE condition;
ORDER BY
根據column1對table_name中的所有資料做排序(升降)
SELECT * FROM table_name
ORDER BY column1 (DESC,ASC);
SELECT * FROM table_name
WHERE column1='xxx'
ORDER BY column2 DESC;
INSERT INTO
插入一筆新資料到table_name中
INSERT INTO table_name
VALUES(column1_value,column2_value...);
沒有指定的column會是NULL
INSERT INTO table_name(column1,column2)
VALUES(calumn1_value,column2_value);
判斷是否為NULL
不能用等於 不能用等於 不能用等於.......
SELECT * FROM table_name
WHERE column1 IS NOT NULL;
UPDATE
要小心使用 要小心使用 要小心使用...(沒加條件會update整張table的資料，會GG)
UPDATE table_name
SET column1='xxx',column2='xxx'
WHERE condition;
DELETE FROM
要小心使用 要小心使用 要小心使用...(沒加條件會delete掉整張table的資料，真的會GG)
DELETE FROM table_name
WHERE condition;
SELECT TOP,LIMIT,ROWNUM
取出前幾筆資料，在對大量數據做測試時好用。
SQL Server / MS Access Syntax:
SELECT TOP [3 | 30 PERCENT] *
FROM table_name;
MySQL Syntax:
SELECT * FROM table_name
LIMIT 3;
Oracle Syntax:
SELECT * FROM table_name
WHERE ROWNUM<=3;
加入條件時:
SQL Server / MS Access Syntax:
SELECT TOP 3 * FROM table_name
WHERE condition;
MySQL Syntax:
SELECT * FROM table_name
WHERE condition
LIMIT 3;
Oracle Syntax:
SELECT * FROM table_name
WHERE condition AND ROWNUM<=3
