SQL學習筆記（基礎、高級、函式）

SQL 基礎教程：

SELECT與SELECT DISTINCT：SELECT 陳述句用于從資料庫中選取資料；DISTINCT 關鍵詞用于回傳唯一不同的值，用法均為：

SELECT DISTINCT 列名稱 FROM 表名稱.

WHERE：WHERE陳述句有條件地從表中選取資料；用法：

SELECT 列名稱 FROM 表名稱 WHERE 列 運算子 值，如：SELECT * FROM "FIRST" WHERE age > 20

AND & OR：AND 和 OR 可在 WHERE 子陳述句中把兩個或多個條件結合起來，如果第一個條件和第二個條件都成立，則 AND 運算子顯示一條記錄，如果第一個條件和第二個條件中只要有一個成立，則 OR 運算子顯示一條記錄，用法：

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

?

ORDER BY：ORDER BY 陳述句默認按照升序對記錄進行排序，按照降序對記錄進行排序，可以使用ORDER BY DESC 關鍵字，用法：

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

INSERT INTO：INSERT INTO 陳述句用于向表中插入新記錄，第一種用法：

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

第二種用法：

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

UPDATE：UPDATE 陳述句用于更新表中已存在的記錄，用法：

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

DELETE：DELETE 陳述句用于洗掉表中的行，用法：

DELETE FROM 表名稱 WHERE 列名稱 = 值

SQL 高級教程：

SELECT TOP：SELECT TOP 子句用于規定要回傳的記錄的數目，用法：（從 “Websites” 表中選取頭兩條記錄）

SELECT * FROM Websites LIMIT 2

LIKE：LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式，用法：

①選取 name 以字母 “G” 開始的所有客戶：

SELECT * FROM Websites WHERE name LIKE 'G%'

②選取 name 以字母 “k” 結尾的所有客戶：

SELECT * FROM Websites WHERE name LIKE '%k'

③ 選取 name 包含模式 “oo” 的所有客戶：

SELECT * FROM Websites WHERE name LIKE '%oo%'

IN：IN 運算子允許在 WHERE 子句中規定多個值，用法：(選取 name 為 “Google” 或 “菜鳥教程” 的所有資訊)

SELECT * FROM Websites WHERE name IN ('Google','菜鳥教程')

BETWEEN：BETWEEN 運算子選取介于兩個值之間的資料范圍內的值，這些值可以是數值、文本或者日期，用法：

①(選取 alexa 介于 1 和 20 之間的資訊)、

SELECT * FROM Websites
WHERE alexa BETWEEN 1 AND 20

②（選取 alexa 介于 1 和 20 之間但 country 不為 USA 和 IND 的所有網站）

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND country NOT IN ('USA', 'IND')

③（選取 name 以介于 ‘A’ 和 ‘H’ 之間字母開始的所有網站）

SELECT * FROM Websites
WHERE name BETWEEN 'A' AND 'H'

④（選取 date 介于 ‘2016-05-10’ 和 ‘2016-05-14’ 之間的所有訪問記錄）

SELECT * FROM access_log
WHERE date BETWEEN '2016-05-10' AND '2016-05-14'

JOIN：JOIN 子句用于把來自兩個或多個表的行結合起來，基于這些表之間的共同欄位，用法：（“Websites” 表中的 “id” 列指向 “access_log” 表中的欄位 “site_id”，上面這兩個表是通過 “site_id” 列聯系起來的），

SELECT Websites.id, Websites.name, access_log.count, access_log.date FROM Websites INNER JOIN access_log ON Websites.id=access_log.site_id;

UNION：UNION 運算子用于合并兩個或多個 SELECT 陳述句的結果集，注意：UNION 內部的每個 SELECT 陳述句必須擁有相同數量的列，列也必須擁有相似的資料型別，同時，每個 SELECT 陳述句中的列的順序必須相同，用法：

①UNION 語法：（從 “Websites” 和 “apps” 表中選取所有不同的country）

SELECT country FROM Websites UNION SELECT country FROM apps ORDER BY country;

②UNION ALL陳述句：（從 “Websites” 和 “apps” 表中選取所有的country（也有重復的值））

SELECT country FROM Websites UNION ALL SELECT country FROM apps ORDER BY country

SELECT INTO：SELECT INTO 陳述句從一個表復制資料，然后把資料插入到另一個新表中，用法：

①復制所有的列插入到新表中

SELECT * INTO newtable FROM table1

②復制希望的列插入到新表中

SELECT column_name(s) INTO newtable FROM table1

CREATE DATABASE：CREATE DATABASE 陳述句用于創建資料庫，用法：

CREATE DATABASE my_db

CREATE TABLE：CREATE TABLE 陳述句用于創建資料庫中的表，用法：創建一個名為 “Persons” 的表，包含五列：PersonID、LastName、FirstName、Address 和 City，我們使用下面的 CREATE TABLE 陳述句

CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);

PersonID 列的資料型別是 int，包含整數，LastName、FirstName、Address 和 City 列的資料型別是 varchar，包含字符，且這些欄位的最大長度為 255 個字符，

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

①下面的 SQL 強制 “ID” 列、 “LastName” 列以及 “FirstName” 列不接受 NULL 值：

CREATE TABLE Persons 
(    
ID int NOT NULL,    
LastName varchar(255) NOT NULL,    
FirstName varchar(255) NOT NULL,    
Age int )

②在一個已創建的表的 “Age” 欄位中添加 NOT NULL 約束如下所示：

ALTER TABLE Persons MODIFY Age int NOT NULL

③在一個已創建的表的 “Age” 欄位中洗掉 NOT NULL 約束如下所示：

ALTER TABLE Persons MODIFY Age int NULL

UNIQUE約束：唯一約束是指定table的列或列組合不能重復，保證資料的唯一性，雖然唯一約束不允許出現重復的值，但是可以為多個null，同一個表可以有多個唯一約束，多個列組合的約束，在創建唯一約束的時候，如果不給唯一約束名稱，就默認和列名相同，用法：

①(在 “Persons” 表創建時在 “P_Id” 列上創建 UNIQUE 約束)；

CREATE TABLE Persons
(
Id_P int NOT NULL,
UNIQUE (Id_P)
)

②如需命名 UNIQUE 約束，并定義多個列的 UNIQUE 約束:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

PRIMARY KEY約束：主鍵約束相當于唯一約束+非空約束的組合，主鍵約束列不允許重復，也不允許出現空值；如果的多列組合的主鍵約束， 那么這些列都不允許為空值，并且組合的值不允許重復， 每個表最多只允許一個主鍵，建立主鍵約束可以在列級別創建，也可以在表級別上創建，用法：

①在 “Persons” 表創建時在 “Id_P” 列創建 PRIMARY KEY 約束：

CREATE TABLE Persons
(
Id_P int NOT NULL,
PRIMARY KEY (Id_P)
)

②命名 PRIMARY KEY 約束，以及為多個列定義 PRIMARY KEY 約束

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)

CHECK 約束：CHECK 約束用于限制列中的值的范圍，如果對單個列定義 CHECK 約束，那么該列只允許特定的值，如果對一個表定義 CHECK 約束，那么此約束會基于行中其他列的值在特定的列中對值進行限制，用法：

①在 “Persons” 表創建時在 “P_Id” 列上創建 CHECK 約束，CHECK 約束規定 “P_Id” 列必須只包含大于 0 的整數，

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CHECK (P_Id>0)
)

②命名 CHECK 約束，并定義多個列的 CHECK 約束

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

DEFAULT 約束：DEFAULT 約束用于向列中插入默認值，如果沒有規定其他的值，那么會將默認值添加到所有的新記錄，用法：在 “Persons” 表創建時在 “City” 列上創建 DEFAULT 約束，

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

DROP 陳述句：通過使用 DROP 陳述句，可以輕松地洗掉索引、表和資料庫，用法：

①DROP INDEX 陳述句用于洗掉表中的索引：

DROP INDEX table_name.index_name

②DROP TABLE 陳述句用于洗掉表（表的結構、屬性以及索引也會被洗掉）：

DROP TABLE 表名稱

③DROP DATABASE 陳述句用于洗掉資料庫：

DROP DATABASE 資料庫名稱

④僅僅洗掉表內的資料，但并不洗掉表本身:

TRUNCATE TABLE table_name

ALTER TABLE陳述句：ALTER TABLE 陳述句用于在已有的表中添加、洗掉或修改列，用法：

①在表中添加（洗掉）列

ALTER TABLE table_name
ADD（DROP） column_name datatype

②改變表中列的資料型別

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

AUTO INCREMENT陳述句：在每次插入新記錄時，自動地創建主鍵欄位的值，把 “Persons” 表中的 “ID” 列定義為 auto-increment 主鍵：

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
)

**提示：**IDENTITY 的開始值是 1，每條新記錄遞增 1，,要規定 “ID” 列以 10 起始且遞增 5，請把 identity 改為 IDENTITY(10,5)，

視圖(VIEWS)：視圖是基于 SQL 陳述句的結果集的可視化的表，視圖包含行和列，就像一個真實的表，視圖中的欄位就是來自一個或多個資料庫中的真實的表中的欄位，可以向視圖添加 SQL 函式、WHERE 以及 JOIN 陳述句，也可以呈現資料，就像這些資料來自于某個單一的表一樣，用法：

CREATE VIEW 視圖名稱 AS
SELECT 列名稱
FROM 表名稱
WHERE 選取條件

查詢視圖：

SELECT * FROM 視圖名稱

Date 函式：下面的表格列出了 MySQL 中最重要的內建日期函式：

SQL 函式：

AVG() 函式：AVG() 函式回傳數值列的平均值，用法：

從 “access_log” 表的 “count” 列獲取平均值：

SELECT AVG(count) AS CountAverage FROM access_log

選擇訪問量高于平均訪問量的 “site_id” 和 “count”：

SELECT site_id, count FROM access_log
WHERE count > (SELECT AVG(count) FROM access_log);

COUNT() 函式：回傳匹配指定條件的行數，用法：

COUNT(column_name) 函式回傳指定列的值的數目（NULL 不計入）：

SELECT COUNT(*) FROM table_name

COUNT(*) 函式回傳表中的記錄數：

SELECT COUNT(*) FROM table_name

COUNT(DISTINCT column_name) 函式回傳指定列的不同值的數目：

SELECT COUNT(DISTINCT column_name) FROM table_name

FIRST()函式 LAST()函式：回傳指定的列中第一個（最后一個）記錄的值，用法：

SELECT column_name FROM table_name
ORDER BY column_name ASC(DESC)
LIMIT 1;

MAX()函式 MIN()函式：函式回傳指定列的最大（小）值，用法：

SELECT MAX(alexa) AS max_alexa FROM Websites;
SELECT MIN(alexa) AS max_alexa FROM Websites;

SUM()函式：回傳數值列的總數，用法：

SELECT SUM(count) AS nums FROM access_log;

GROUP BY陳述句：可結合一些聚合函式來使用，用法：

①統計 access_log 各個 site_id 的訪問量：

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

② 統計有記錄的網站的記錄數量

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

HAVING子句：在 SQL 中增加 HAVING 子句原因是，WHERE 關鍵字無法與聚合函式一起使用，HAVING 子句可以讓我們篩選分組后的各組資料，用法：

想要查找總訪問量大于 200 的網站，

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

EXISTS 運算子：運算子用于判斷查詢子句是否有記錄，如果有一潭訓多條記錄存在回傳 True，否則回傳 False，用法：

查找總訪問量(count 欄位)大于 200 的網站是否存在，

SELECT Websites.name, Websites.url 
FROM Websites 
WHERE EXISTS (SELECT count FROM access_log WHERE Websites.id = access_log.site_id AND count > 200);

EXISTS 可以與 NOT 一同使用，查找出不符合查詢陳述句的記錄，

UCASE()函式 LCASE()函式：把欄位的值轉換為大（小）寫，用法：

SELECT UCASE(column_name) FROM table_name;
SELECT LCASE(column_name) FROM table_name;

MID()函式：用于從文本欄位中提取字符，用法：

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

從 “Websites” 表的 “name” 列中提取前 4 個字符：

SELECT MID(name,1,4) AS ShortTitle
FROM Websites;

LENGTH()函式：函式回傳文本欄位中值的長度，用法：

SELECT LENGTH(column_name) FROM table_name;

ROUND()函式：用于把數值欄位舍入為指定的小數位數，用法：

ROUND(X)：回傳引數X的四舍五入的一個整數，

SELECT ROUND(column_name) FROM table_name;

ROUND(X,D)： 回傳引數X的四舍五入的有 D 位小數的一個數字，如果D為0，結果將沒有小數點或小數部分，

SELECT ROUND(column_name,decimals) FROM table_name;

NOW()函式：回傳當前系統的日期和時間，用法：

SELECT NOW() FROM table_name;

FORMAT()函式：用于對欄位的顯示進行格式化，用法：

SELECT FORMAT(column_name,format) FROM table_name;

從 “Websites” 表中選取 name, url 以及格式化為 YYYY-MM-DD 的日期：

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;