SQL學習筆記（基礎、高階、函數）

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 中最重要的內建日期函數：

函數	描述
NOW()	返回當前的日期和時間
CURDATE()	返回當前的日期
CURTIME()	返回當前的時間
DATE()	提取日期或日期/時間表示式的日期部分
EXTRACT()	返回日期/時間的單獨部分
DATE_ADD()	向日期新增指定的時間間隔
DATE_SUB()	從日期減去指定的時間間隔
DATEDIFF()	返回兩個日期之間的天數
DATE_FO	用不同的格式顯示日期/時間

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;

引數	描述
column_name	必需。要提取字元的欄位。
start	必需。規定開始位置（起始值是 1）。
length	可選。要返回的字元數。如果省略，則 MID() 函數返回剩餘文字。

從 「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;