SQL—結構化查詢語言

1.SQL簡介

文章目錄

• • • 1.SQL簡介
    • • • 1.1 資料庫用例建構
    • 2.SQL總覽
    • 3.SQL詳解
    • • • 3.1資料類型
        • 3.2 文法規則
        • 3.3 函數
        • 3.4 SQL文法
        • 3.5 進階查詢
        • 3.6 視圖
    • 4.事務
    • 5.附件下載下傳

SQL是一種結構化查詢語言，是專門為資料庫而建立的操作指令集，是一門資料庫語言。

對于資料庫的互動操作離不開SQL，SQL為我們管理資料庫提供了極大的友善。

1.1 資料庫用例建構

注釋：本文所有的SQL程式，都是基于文末的自建資料庫進行，大家可以自行下載下傳擷取。
           

• 下載下傳文末的create.txt、populate.txt檔案
• 在MySQL指令行用戶端中運作“CREATE DATABASE sample;”，建立sample資料庫
• 在MySQL指令行用戶端中運作“USE sample;”，轉至sample資料庫進行後續操作
• 在MySQL指令行用戶端中運作create.txt中的SQL語句，建立資料表
• 在MySQL指令行用戶端中運作populate.txt中的SQL語句，将資料導入sample資料庫
• 在MySQL指令行用戶端中運作“SHOW TABLES;”，檢視資料表建立情況
• 在MySQL指令行用戶端中運作“

  select * from Customers;select * from orderitems;select * from orders;select * from products;select * from vendors;

  ”，檢視資料導入情況

  

2.SQL總覽

SQL語言的講解可以從定義、文法規則、資料類型、SQL文法、函數、進階查詢、視圖這七個方面展開，但是我們需要知道SQL語言的核心與難點是解決資料庫資料查詢的問題，資料查詢也是我們最常用的服務，是以我們要重點掌握這一核心技術！

3.SQL詳解

3.1資料類型

SQL能夠處理的資料類型包括以上十幾種，這些資料都可以以表格的形式存儲于資料庫中，但是資料庫的海納百川是EXCEL無法比拟的，這也是為什麼公司資料的存儲用資料庫的一個重要原因。

3.2 文法規則

文法規則是編寫SQL語句必須遵守的硬規則，如果沒有遵守，則會運作出錯；是以我們對SQL程式的檢查首先從它是否滿足文法規則開始。

3.3 函數

函數多用于對查詢到的資料進行處理，與SQL語句結合使用，是以SQL語言還具有一定的資料分析能力。

--求均值
SELECT AVG(列名) FROM 表名;
--記錄條數
SELECT COUNT(列名) FROM 表名;
--擷取查詢到的第一條記錄
SELECT FIRST(列名) FROM 表名;
--擷取查詢到的最後一條記錄
SELECT LAST(列名) FROM 表名;
--擷取查詢到的最大記錄
SELECT MAX(列名) FROM 表名;
--擷取查詢到的最小記錄
SELECT MIN(列名) FROM 表名;
--擷取字段總和
SELECT SUM(列名) FORM 表名;

--分組計算
SELECT 列名,aggregate_function(列名)
FROM 表名
WHERE 條件
GROUP BY 列名;

--分組計算并篩選符和條件的記錄
SELECT 列名,aggregate_function(列名)
FROM 表名
WHERE 條件
GROUP BY 列名
HAVING aggergate_function(列名) 操作符 值;

--将字段值大寫
SELECT UCASE(列名) FROM 表名;
--将字段值小寫
SELECT LCASE(列名) FROM 表名;
--提取字段值
SELECT MID(列名,起始位,長度) FROM 表名;
--擷取字段值長度
SELECT LEN(列名) FROM 表名;
--對字段值進行四舍五入
SELECT ROUND(列名,小數位數) FROM 表名;
--擷取目前時間
SELECT NOW() FROM 表名;
--以規定格式顯示字段
SELECT FORMAT(列名,格式) FROM 表名;

           

3.4 SQL文法

SQL文法講解的是SQL語言中的保留關鍵字，也是我們進行資料庫操作的直接工具。

SQL文法包括資料定義語言(DDL)，主要用于資料庫、資料表的建立、修改、更新、删除；以及索引、主鍵、外鍵的建立、删除；它并非對資料的直接操作。

SQL文法中的資料操作語言(DML)，是對于資料的直接操作，包括資料的增删改查，其中"查"是核心問題。

子句、屬性詞都是服務于資料操作語言(DML)的一些保留關鍵字，以進行更複雜的資料操作。

通配符是與LIKE關鍵字結合使用的，用于模糊查找。

DELETE 語句

DELETE語句用于删除表中的行

--删除orders表中的所有記錄
DELETE FROM orders;
DELETE * FROM orders;

--删除orders表中order_num為20005的記錄
DELETE FROM orders WHERE order_num = 20005;

           

UPDATE語句

UPDATE語句用于修改表中的資料

--更新某一行中的一列
UPDATE customers SET cust_country = 'CHAIN' WHERE cust_name = Fun4All;

-- 更新某一行中的若幹列
UPDATE customers SET cust_country = 'CHAIN',cust_state = 'MI' WHERE cust_name = 'Kids Place';

-- 用一表資料更新另一表資料
UPDATE customers SET A.cust_zip = B.order_date FROM customers A,orders B WHERE A.cust_id  = B.cust_id;
           

INSERT INTO 語句

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

--為所有列插入資料
INSERT INTO 表名 VALUES(值1，值2，...);

--為若幹列插入資料
INSERT INTO 表名 (列名1，列名2，...) VALUES(值1，值2，...);
           

--為所有列插入資料
INSERT INTO orderitems VALUES(20005,1,'BRO3',254,11.99);

--為若幹列插入資料
INSERT INTO orderitems (order_item,quantity) VALUES(3,199);
           

SELECT語句

SELECT語句用于從表中選取資料

--擷取某些字段
SELECT 列名 FROM 表名;
--擷取全部記錄
SELECT * FROM 表名;

--對擷取的字段去重
SELECT DISTINCT 列名 FROM 表名;

--按條件查詢資料表
SELECT 列名 FROM 表名 WHERE 列名 運算符 值;
SELECT 列名 FROM 表名 WHERE 條件1 AND 條件2;
SELECT 列名 FROM 表名 WHERE 條件1 OR 條件2;
SELECT 列名 FROM 表名 WHERE 列名 IS NULL;
SELECT 列名 FROM 表名 WHERE 列名 IS NOT NULL;

--對查詢到的資料分組
SELECT 列名 FROM 表名 ORDER BY 列名;
SELECT 列名 FROM 表名 ORDER BY 列名 DESC;
SELECT 列名 FROM 表名 ORDER BY 列名1 DESC,列名2 ASC;
SELECT 列名 FROM 表名 WHERE 列名 LIKE 模式;
SELECT 列名 FROM 表名 WHERE 列名 NOT LIKE 模式;
SELECT 列名 FROM 表名 WHERE 列名 IN (value1,value2,...);
SELECT 列名 FROM 表名 WHERE 列名 BETWEEN value1 AND value2;

--擷取前N個資料記錄
SELECT TOP 個數 列名 FROM 表名;

--重命名資料表
SELECT 列名 FROM 表名 AS 新表名;
--重命名字段
SELECT 列名 AS 新列名 FROM 表名;

--兩表查詢
SELECT 列名 FROM 表名1,表名2 WHERE 條件;

--資料表複制
SELECT 列名 INTO 新表名 [IN externaldatabase] FROM 舊表名;

SELECT 字段 （查詢字段）
FROM 表名（查詢表名）
WHERE 條件（篩選條件）
GROUP BY 字段（對結果分組）
ORDER BY 字段（按字段排序）
HAVING 條件（過濾分組條件）
LIMIT NUM （限制函數）;
           

CREATE語句

CREATE語句可以用于建立資料庫、資料表

--建立資料庫
CREATE DATABASE 資料庫名;

--建立資料表
CREATE TABLE 表名1
(
列名稱1 資料類型(size),
列名稱2 資料類型(size),
列名稱3 資料類型(size) NOT NULL,
列名稱4 資料類型(size) DEFAULT 值,
列名稱5 資料類型(size) AUTO_INCREMENT,
CHECK (列名稱>0),
UNIQUE(列名稱3),
PRIMARY KEY (列名稱4),
FOREIGN KEY (列名4) REFERENCES 表名2(列名4),
...
);
           

DROP語句

DROP語句可以删除索引、表和資料庫

--删除索引
DROP INDEX index_name ON table_name;
DROP INDEX table_name.index_name;
DROP INDEX index_name;
ALTER TABLE table_name DROP INDEX index_name;
 
--删除資料表
DROP TABLE 表名;

--删除資料庫
DROP DATABASE 資料庫名;
           

ALTER語句

ALTER TABLE語句用于在已有的表中添加、修改或删除行

--添加字段
ALTER TABLE table_name ADD column_name datatype;

--删除字段
ALTER TABLE table_name DROP COLUMN column_name;

--修改字段資料類型
ALTER TABLE table_name ALTER COLUMN column_name datatype;

           

3.5 進階查詢

進階查詢包括組合查詢、子查詢、聯結表；用于實作邏輯更複雜的資料查詢。

--組合查找
SELECT 列名1 FROM 表名1 UNION SELECT 列名2 FROM 表名2;
SELECT 列名1 FROM 表名1 UNION ALL SELECT 列名2 FROM 表名2;

--子查找
SELECT 列名1 FROM 表名1 WHERE 列名2 = (SELECT 列名3 FROM 表名2 WHERE 條件);

--聯結表：多表查找
SELECT 列名 FROM 表名1 INNER JOIN 表名2 ON 條件;
SELECT 列名 FROM 表名1 LEFT JOIN 表名2 ON 條件;
SELECT 列名 FROM 表名1 RIGHT JOIN 表名2 ON 條件;
SELECT 列名 FROM 表名1 FULL JOIN 表名2 ON 條件;
           

3.6 視圖

視圖相當于查詢資料的可視化。

視圖包含行和列，就像一個真實的表，但資料庫的設計和結構并不會受到視圖中的函數、WHERE或JOIN語句的影響。

CREATE VIEW語句

CREATE VIEW語句用于建立視圖

CREATE VIEW view_name AS SELECT 列名 FROM 表名 WHERE 條件;

DROP VIEW view_name;

UPDATE view_name SET 列名 = 值 WHERE 條件;

INSERT INTO view_name VALUES(value1,value2,...);

DELETE FROM view_name WHERE 條件;
           

4.事務

将若幹SQL語句打包在一起，共同執行一個完整的任務，這就是事務，相當于多條SQL語句的集合。

• 在MySQL中隻有使用了Innodb資料庫引擎的資料庫或表才支援事務
• 事務用來維護資料庫的完整性，保證成批的SQL語句要麼全部執行，要麼全部不執行
• 事務用來管理INSERT、UPDATE、DELETE語句

事務具有以下特性：

• 原子性：一個事務要麼全部被執行，要麼全部不執行
• 一緻性：在事務開始之前和事務結束之後，資料庫的完整性未被破壞
• 隔離性：資料庫允許多個事務同時對其資料進行讀寫和修改的能力
• 持久性：事務處理結束後，對資料的修改是永久的。

事務控制語句：

• BEGIN：開始一個事務
• ROLLBACK：復原事務
• COMMIT：送出事務
• SET AUTOCOMMIT = 0：禁止自動送出
• SET AUTOCOMMIT = 1：啟動自動送出

5.附件下載下傳

SQL操作用例