SQL，sql教程

SQL，sql教程

終於有時間繼續玩阿里的資料採礦比賽，第二賽季限定工具，不得不先重拾一下資料庫。以下筆記總結自網路教程。

SQL簡介

• SQL指結構化查詢語言 (SQL)
• SQL是一種ANSI的標準電腦語言，存在不同的版本，但不同版本都支援一些共同的關鍵詞。

SQL 文法

• 一個資料庫通常包含一個或多個表。
• SQL 對大小寫不敏感

• 某些資料庫系統要求在每條 SQL 命令的末端使用分號

• SQL DML 和 DDL

  把 SQL 分為兩個部分：資料操作語言 (DML) 和 資料定義語言 (Data Definition Language) (DDL)

  • 查詢和更新指令構成了 SQL 的 DML 部分：

    SELECT - 從資料庫表中擷取資料UPDATE - 更新資料庫表中的資料DELETE - 從資料庫表中刪除資料INSERT INTO - 向資料庫表中插入資料

  • SQL 中最重要的 DDL 語句:

    CREATE DATABASE - 建立新資料庫ALTER DATABASE - 修改資料庫CREATE TABLE - 建立新表ALTER TABLE - 變更（改變）資料庫表DROP TABLE - 刪除表CREATE INDEX - 建立索引（搜尋鍵）DROP INDEX - 刪除索引

SELECT 語句

    SELECT column_name1,column_name2 FROM table_name    SELECT * FROM table_name

SELECT DISTINCT 語句

關鍵詞 DISTINCT 用於返回唯一不同的值。

SELECT DISTINCT column_name FROM table_name

WHERE 子句

如需有條件地從表中選取資料，可將 WHERE 子句添加到 SELECT 語句。

SELECT 列名稱 FROM 表名稱 WHERE 列 運算子 值

AND & OR 運算子

AND 和 OR 可在 WHERE 子語句中把兩個或多個條件結合起來。

SELECT * FROM table_name WHERE column_name1='XXX' AND column_name2='XXX'SELECT * FROM table_name WHERE (column_name1='XXX' OR column_name2='XXX') AND column_name3='XXX'

ORDER BY 子句

ORDER BY 語句用於根據指定的列對結果集進行排序，預設按照升序對記錄進行排序，如果希望按照降序對記錄進行排序，可以使用 DESC 關鍵字。

SELECT column_name1, column_name2 FROM table_name ORDER BY column_name1 [DESC]

INSERT INTO 語句

INSERT INTO 語句用於向表格中插入新的行

INSERT INTO 表名稱 VALUES (值1, 值2,....)

也可以只在指定列插入資料，沒插入資料的列留空：

INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....)

UPDATE 語句

Update 語句用於修改表中的資料。

UPDATE 表名稱 SET 列名稱1 = 新值1，列名稱2 = 新值2 WHERE 列名稱 = 某值

DELETE 語句

DELETE 語句用於刪除表中的行

DELETE FROM 表名稱 WHERE 列名稱 = 值

TOP 子句

TOP 子句用於規定要返回的記錄的數目。

• SQL Server 的文法：

  SELECT TOP number|percent column_name(s) FROM table_name

• MySQL 文法

  SELECT column_name(s) FROM table_name LIMIT number

LIKE 操作符

LIKE 操作符用於在 WHERE 子句中搜尋列中的指定模式。

SELECT column_name(s)FROM table_nameWHERE column_name LIKE pattern

舉例：

SELECT * FROM PersonsWHERE City LIKE 'N%' —— "%" 可用於定義萬用字元（模式中缺少的字母）

萬用字元

% 替代一個或多個字元

_ 僅替代一個字元

IN 操作符

IN 操作符允許我們在 WHERE 子句中規定多個值。

SELECT column_name(s)FROM table_nameWHERE column_name IN (value1,value2,...)

BETWEEN 操作符

在 WHERE 子句中使用，作用是選取介於兩個值之間的資料範圍。

SELECT column_name(s)FROM table_nameWHERE column_nameBETWEEN value1 AND value2

JOIN

join 用於根據兩個或多個表中的列之間的關係，從這些表中查詢資料。

SELECT table1.column1, table1.column2, table2.columnFROM table1 INNER JOIN table2ON table1.column = table2.column

• 除了 INNER JOIN（內串連），我們還可以使用其他幾種串連。下面列出了可以使用的 JOIN 類型，以及它們之間的差異。

  JOIN: 如果表中有至少一個匹配，則返回行(INNER JOIN 與 JOIN 是相同的。)LEFT JOIN: 即使右表中沒有匹配，也從左表返回所有的行RIGHT JOIN: 即使左表中沒有匹配，也從右表返回所有的行FULL JOIN: 只要其中一個表中存在匹配，就返回行

UNION 和 UNION ALL 操作符

UNION 操作符用於合并兩個或多個 SELECT 語句的結果集。
請注意，UNION 內部的 SELECT 語句必須擁有相同數量的列。列也必須擁有相似的資料類型。同時，每條 SELECT 語句中的列的順序必須相同。

SELECT column_name(s) FROM table_name1UNIONSELECT column_name(s) FROM table_name2

預設地，UNION 操作符選取不同的值。如果允許重複的值，請使用 UNION ALL：

SELECT column_name(s) FROM table_name1UNION ALLSELECT column_name(s) FROM table_name2

按列拼接兩條select語句的結果

SELECT INTO 語句

SELECT INTO 語句從一個表中選取資料，然後把資料插入另一個表中。
SELECT INTO 語句常用於建立表的備份複件或者用於對記錄進行存檔。

SELECT *INTO new_table_name [IN externaldatabase] FROM old_tablename

或者只把希望的列插入新表：

SELECT column_name(s)INTO new_table_name [IN externaldatabase] FROM old_tablename

CREATE DATABASE 語句

CREATE DATABASE database_name

CREATE TABLE 語句

CREATE TABLE 語句用於建立資料庫中的表。

CREATE TABLE 表名稱(列名稱1 資料類型,列名稱2 資料類型,列名稱3 資料類型,....)

資料類型（data_type）規定了列可容納何種資料類型。下面的表格包含了SQL中最常用的資料類型：

integer(size)int(size)smallint(size)tinyint(size) 僅容納整數。在括弧內規定數位最大位元。decimal(size,d)numeric(size,d) 容納帶有小數的數字。"size" 規定數位最大位元。"d" 規定小數點右側的最大位元。char(size) 容納固定長度的字串（可容納字母、數字以及特殊字元）。在括弧中規定字串的長度。varchar(size) 容納可變長度的字串（可容納字母、數字以及特殊的字元）。在括弧中規定字串的最大長度。date(yyyymmdd) 容納日期。

NOT NULL 約束

NOT NULL 約束強制列不接受 NULL 值。
NOT NULL 約束強制欄位始終包含值。這意味著，如果不向欄位添加值，就無法插入新記錄或者更新記錄。

PRIMARY KEY 約束

PRIMARY KEY 約束唯一標識資料庫表中的每條記錄。
主鍵必須包含唯一的值。
主鍵列不能包含 NULL 值。
每個表都應該有一個主鍵，並且每個表只能有一個主鍵。

下面的 SQL 在 “Persons” 表建立時在 “Id_P” 列建立 PRIMARY KEY 約束：

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),PRIMARY KEY (Id_P))

FOREIGN KEY 約束

一個表中的 FOREIGN KEY 指向另一個表中的 PRIMARY KEY。

CHECK 條件約束

CHECK 條件約束用於限制列中的值的範圍。

下面的 SQL 在 “Persons” 表建立時為 “Id_P” 列建立 CHECK 條件約束。CHECK 條件約束規定 “Id_P” 列必須只包含大於 0 的整數。

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255),CHECK (Id_P>0))

DEFAULT 約束

DEFAULT 約束用於向列中插入預設值

下面的 SQL 在 “Persons” 表建立時為 “City” 列建立 DEFAULT 約束：

CREATE TABLE Persons(Id_P int NOT NULL,LastName varchar(255) NOT NULL,FirstName varchar(255),Address varchar(255),City varchar(255) DEFAULT 'Sandnes')

CREATE INDEX 語句

CREATE INDEX 語句用於在表中建立索引。
在不讀取整個表的情況下，索引使資料庫應用程式可以更快地尋找資料。

您可以在表中建立索引，以便更加快速高效地查詢資料。
使用者無法看到索引，它們只能被用來加速搜尋/查詢。
注釋：更新一個包含索引的表需要比更新一個沒有索引的表更多的時間，這是由於索引本身也需要更新。因此，理想的做法是僅僅在常常被搜尋的列（以及表）上面建立索引。

• 在表上建立一個簡單的索引。允許使用重複的值：

  CREATE INDEX index_nameON table_name (column_name) 可以有多個column

• 在表上建立一個唯一的索引。唯一的索引意味著兩個行不能擁有相同的索引值。

  CREATE UNIQUE INDEX index_nameON table_name (column_name1，column_name2)

DROP

DROP 語句，可以輕鬆地刪除索引、表和資料庫。

• 使用 DROP INDEX 命令刪除表格中的索引。

  DROP INDEX index_name ON table_name

• DROP TABLE 語句用於刪除表（表的結構、屬性以及索引也會被刪除）

  DROP TABLE 表名稱

• DROP DATABASE 語句用於刪除資料庫：

  DROP DATABASE 資料庫名稱

ALTER TABLE 語句

• 如需在表中添加列，請使用下列文法:

  ALTER TABLE table_nameADD column_name datatype

• 要刪除表中的列，請使用下列文法

  ALTER TABLE table_name DROP COLUMN column_name

• 要改變表中列的資料類型，請使用下列文法：

  ALTER TABLE table_nameALTER COLUMN column_name datatype

Date 函數NULL 值

NULL 值是遺漏的未知資料。
預設地，表的列可以存放 NULL 值。
IS NULL 和 IS NOT NULL 操作符。

SQL 伺服器 - RDBMS

現代的 SQL 伺服器構建在 RDBMS 之上。

• DBMS - 資料庫管理系統（Database Management System）

資料庫管理系統是一種可以訪問資料庫中資料的電腦程式。
DBMS 使我們有能力在資料庫中提取、修改或者存貯資訊。
不同的 DBMS 提供不同的函數供查詢、提交以及修改資料。

• RDBMS - 關聯式資料庫管理系統（Relational Database Management System）

關聯式資料庫管理系統 (RDBMS) 也是一種資料庫管理系統，其資料庫是根據資料間的關係來組織和訪問資料的。
20 世紀 70 年代初，IBM 公司發明了 RDBMS。
RDBMS 是 SQL 的基礎，也是所有現代資料庫系統諸如 Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及 Microsoft Access 的基礎。

SQL 函數

SQL 擁有很多可用於計數和計算的內建函數。

內建 SQL 函數的文法是：

SELECT function(列) FROM 表

• AVG 函數

AVG 函數返回數值列的平均值。NULL 值不包括在計算中。

SELECT AVG(column_name) FROM table_name

• COUNT() 函數

COUNT() 函數返回匹配指定條件的行數，NULL 不計入。

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) 文法返回表中的記錄數：

SELECT COUNT(*) FROM table_name

COUNT(DISTINCT column_name) 文法返回指定列的不同值的數目：

SELECT COUNT(DISTINCT column_name) FROM table_name

• FIRST() 函數

FIRST() 函數返回指定的欄位中第一個記錄的值。

SELECT FIRST(column_name) FROM table_name

• LAST() 函數

LAST() 函數返回指定的欄位中最後一個記錄的值。

SELECT LAST(column_name) FROM table_name

• MAX() 函數、MIN() 函數

返回一列中的最大、小值。NULL 值不包括在計算中。

SELECT MIN(column_name) FROM table_name

• SUM() 函數

SUM 函數返回數值列的總數（總額）。

SELECT SUM(column_name) FROM table_name

• GROUP BY 語句

GROUP BY 語句用於結合合計函數，根據一個或多個列對結果集進行分組。

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_name

• HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 關鍵字無法與合計函數一起使用。

SELECT column_name, aggregate_function(column_name)FROM table_nameWHERE column_name operator valueGROUP BY column_nameHAVING aggregate_function(column_name) operator value

例子：

現在我們希望尋找客戶 “Bush” 或 “Adams” 擁有超過 1500 的訂單總金額。

SELECT Customer,SUM(OrderPrice) FROM OrdersWHERE Customer='Bush' OR Customer='Adams'GROUP BY CustomerHAVING SUM(OrderPrice)>1500

• UCASE() 函數、LCASE() 函數

UCASE 函數把欄位的值轉換為大寫。

LCASE 函數把欄位的值轉換為小寫。

SELECT LCASE(column_name) FROM table_name

• MID() 函數

MID 函數用於從文字欄位中提取字元。

SELECT MID(column_name,start[,length]) FROM table_name

• LEN() 函數

LEN 函數返迴文本欄位中值的長度。

SELECT LEN(column_name) FROM table_name

• ROUND() 函數

ROUND 函數用於把數值欄位舍入為指定的小數位元。

SELECT ROUND(column_name,decimals) FROM table_name

decimals規定要返回的小數位元。

• NOW() 函數

NOW 函數返回當前的日期和時間。

SELECT NOW() FROM table_name

• FORMAT() 函數

FORMAT 函數用於對欄位的顯示進行格式化。

SELECT FORMAT(column_name,format) FROM table_name

format規定格式