基礎(chǔ)-SQL操作，MySQL為主

內(nèi)容來(lái)自《SQL必知必會(huì)》(4th)、

基礎(chǔ)SQL語(yǔ)法，以MySQL為主

1.基本數(shù)據(jù)庫(kù)名詞

數(shù)據(jù)庫(kù)：存放數(shù)據(jù)的容器
表：同一類數(shù)據(jù)的結(jié)構(gòu)化存儲(chǔ)，表名唯一
列：同種數(shù)據(jù)類型和意義的數(shù)據(jù)占據(jù)的表的一個(gè)字段
行：表按行存儲(chǔ)
主鍵：一列或幾列來(lái)唯一的標(biāo)識(shí)每一行，不允許修改更新，不能重用
外鍵：***

2.語(yǔ)法

注釋


行內(nèi)做注釋：
SELECT prod_name -- 注釋在這里
FROM Products;

單行注釋:
#注釋在這里
SELECT prod_name
FROM Products;

多行注釋：
/* 注釋
在這里*/
SELECT prod_name
FROM Products;

SELECT 檢索


檢索單列：
SELECT prod_name 
FROM Products; 

檢索多列：
SELECT a,b,c 
FROM Products;  

檢索所有列：
SELECT * 
FROM Products;  

檢索單列去重復(fù)：
SELECT DISTINCT a 
FROM Products;  

檢索多列去重復(fù)：
SELECT DISTINCT a,b,c 
FROM Products;
(DISTINCT 不能部分作用，他作用于后面所有列)

MySQL中只查詢前五行的結(jié)果：
SELECT a 
FROM Products 
LIMIT 5; ---這就是MySQL的分頁(yè)查詢！??！   
( Oracle中：SELECT a FROM Products WHERE ROWNUM <=5; )

MySQL中從第3行起的5行數(shù)據(jù)：
SELECT a 
FROM Products 
LIMIT 5 OFFSET 3;#也就是從第4行到第8行這五行數(shù)據(jù)
MySQL支持另一種寫法:LIMIT 3,5;

ORDER BY排序檢索

單列排序
SELECT prod_name
FROM Products
ORDER BY prod_name#這個(gè)子句一定是最后最后的位置

按多個(gè)列排序：
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price, prod_name#先后順序

按列的位置排序(盡量不用):
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY 2,3#SELECT 中的第2第3列 

降序排列：
SELECT prod_id,prod_price,prod_name
FROM Products
ORDER BY prod_price DESC, prod_name# DESC 只作用于單行

WHERE過(guò)濾行(條件)

含義 操作符
等于 =
大于 >
小于 <
大于等于 >=
小于等于 <=
不等于 <>，!=
不大于 !>
不小于 !<
兩個(gè)值之間 BETWEEN
空值 IS NULL 
某列定值過(guò)濾：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE prod_price = 3.49;

范圍過(guò)濾：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

空值(無(wú)值)檢查：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE prod_price IS NULL;

條件的 AND 且
條件的 OR 或 (前一個(gè)表達(dá)式為真則后表達(dá)式不運(yùn)算)
AND 的優(yōu)先級(jí)比 OR 要高
建議不管優(yōu)先級(jí)，多用括號(hào)：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'DLL11') AND prod_price <= 4;  

多值 OR 用 IN 代替
IN 后面的括號(hào)里還可以放SQL語(yǔ)句：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01')
ORDER BY prod_name;

用 NOT 來(lái)否定條件：
SELECT prod_id,prod_price,prod_name
FROM Products
WHERE vend_id NOT IN ('DLL01', 'BRS01')

LIKE通配符用于文本字段

通配符 匹配對(duì)象
% 任何字符出現(xiàn)任意次(包括0)，除了NULL
_ 匹配單個(gè)字符(出現(xiàn)1次)比如一個(gè)數(shù)字需要兩個(gè)_
[] 字符集中的某個(gè)字符可匹配單個(gè)字符
[^XX] 表否，相當(dāng)于在WHERE后面直接加NOT 
找出所有以Fish開頭的產(chǎn)品：
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%'# 搜索是可以區(qū)分大小寫的  
#%可以放在開頭結(jié)尾也可以放在中間，可以替代各種字符，唯一不會(huì)匹配NULL  

找出所有姓名是以J或M開頭的聯(lián)系人：
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

格式輸出（計(jì)算字段）

功能 語(yǔ)法
字段拼接 + 或 ||, MySQL中用Concat()函數(shù)
去掉空格 RIRIM()去右邊，LTRIM()去左邊，TRIM()去左右兩邊
取別名(導(dǎo)出列) AS
算術(shù)運(yùn)算符 +，-，*，/ （常在做運(yùn)算后取別名） 
MySQL字段拼接，且去空格：
SELECT Concat(TRIM(vend_name), '(', TRIM(vend_country), ')')
       AS vend_title 
FROM Vendors  
ORDER BY vend_name;

計(jì)算可單獨(dú)處理：
SELECT 3*2 -- 輸出結(jié)果6
SELECT TRIM('  abc  ')--#輸出abc
SELECT Now() -- 輸出當(dāng)前日期和時(shí)間

數(shù)據(jù)處理函數(shù)

函數(shù)在不同數(shù)據(jù)庫(kù)中差異較大

常用文本處理函數(shù) 字符串操作
LEFT()，RIGHT() 左邊字符,左邊字符
LENGTH() 長(zhǎng)度
LOWER()，UPPER() 轉(zhuǎn)小寫,轉(zhuǎn)大寫
LTRIM(),RTRIM(),TRIM() 去空格
SOUNDEX() soundex 匹配發(fā)音 
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(A) = SOUNDEX('love')

還有日期和時(shí)間處理，數(shù)值的處理函數(shù)，稍后整理

5個(gè)集聚函數(shù)

函數(shù) 說(shuō)明
AVG() 某列平均值(忽略NULL)
COUNT() 某列行數(shù)(括號(hào)中為*時(shí)不忽略NULL)
MAX()、MIN() 排序后最大/小的一個(gè)值，包括文本排序，忽略NULL
SUM() 求和

聚集函數(shù)的參數(shù) DISTINCT忽略重復(fù)值

特定供應(yīng)商提供產(chǎn)品的平均價(jià)格：
SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id = 'DLL01';

具有電子郵件地址的客戶數(shù)(就是地址不為空)

訂單總額：
SELECT SUM(quantity*item_price) AS total_price
FROM OrderItems
WHERE order_num = 20005;

組合使用聚集函數(shù)：
SELECT COUNT(*) AS num_items,
       MIN(A) AS price_min,
       MAX(A) AS price_max,
       AVG(A) AS price_avg
FROM Products;

分組GROUP BY 和 HAVING

GROUP BY對(duì)每一組進(jìn)行聚集計(jì)算和其他操作
分組可以嵌套，分組后所有指定列同步計(jì)算，所有NULL單獨(dú)作為一組
SELECT中所有字段名必須在GROUP BY中給出
位置在WHERE和ORDER BY之間

分組輸出：按vend_id列分組計(jì)數(shù)
SELECT vensd_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;
可以用相對(duì)位置：GROUP BY 2，1 按選擇的第二個(gè)列分組，然后按第一個(gè)列分組

HAVING 過(guò)濾分組后的聚集行 WHERE過(guò)濾的是簡(jiǎn)單行
HAVING 在分組后執(zhí)行 而WHERE會(huì)在分組前執(zhí)行

HAVING可以代替所有的WHERE

分組情景：返回提供10個(gè)
只留下計(jì)數(shù)大于等于2的分組，而這個(gè)計(jì)數(shù)就是基于分組的，所以用HAVING：
SELECT cust_id,COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;

列出具有兩個(gè)以上產(chǎn)品且其價(jià)格大于等于4的供應(yīng)商：
SELECT vend_id, COUNT(*) AS num_prods--4顯示
FROM Products
WHERE prod_price >= 4--1取出價(jià)格大于等于4的行
GROUP BY vend_id --2對(duì)vend_id進(jìn)行分組
HAVING COUNT(*) >= 2--2取出計(jì)數(shù)大于2的分組

書寫順序和執(zhí)行順序：

SELECT--5選擇指定列
FROM--1讀表
WHERE--2條件過(guò)濾行
GROUP BY--3分組
HAVING--4分組過(guò)濾
ORDER BY--6排序后顯示

MySQL的語(yǔ)句執(zhí)行順序
MySQL的語(yǔ)句一共分為11步，如下圖所標(biāo)注的那樣，最先執(zhí)行的總是FROM操作，最后執(zhí)行的是LIMIT操作。其中每一個(gè)操作都會(huì)產(chǎn)生一張?zhí)摂M的表，這個(gè)虛擬的表作為一個(gè)處理的輸入，只是這些虛擬的表對(duì)用戶來(lái)說(shuō)是透明的，但是只有最后一個(gè)虛擬的表才會(huì)被作為結(jié)果返回。如果沒有在語(yǔ)句中指定某一個(gè)子句，那么將會(huì)跳過(guò)相應(yīng)的步驟。

下面我們來(lái)具體分析一下查詢處理的每一個(gè)階段
FROM: 對(duì)FROM的左邊的表和右邊的表計(jì)算笛卡爾積。產(chǎn)生虛表VT1
ON: 對(duì)虛表VT1進(jìn)行ON篩選，只有那些符合<join-condition>的行才會(huì)被記錄在虛表VT2中。
JOIN： 如果指定了OUTER JOIN（比如left join、 right join），那么保留表中未匹配的行就會(huì)作為外部行添加到虛擬表VT2中，產(chǎn)生虛擬表VT3, rug from子句中包含兩個(gè)以上的表的話，那么就會(huì)對(duì)上一個(gè)join連接產(chǎn)生的結(jié)果VT3和下一個(gè)表重復(fù)執(zhí)行步驟1~3這三個(gè)步驟，一直到處理完所有的表為止。
WHERE： 對(duì)虛擬表VT3進(jìn)行WHERE條件過(guò)濾。只有符合<where-condition>的記錄才會(huì)被插入到虛擬表VT4中。
GROUP BY: 根據(jù)group by子句中的列，對(duì)VT4中的記錄進(jìn)行分組操作，產(chǎn)生VT5.
CUBE | ROLLUP: 對(duì)表VT5進(jìn)行cube或者rollup操作，產(chǎn)生表VT6.
HAVING： 對(duì)虛擬表VT6應(yīng)用having過(guò)濾，只有符合<having-condition>的記錄才會(huì)被 插入到虛擬表VT7中。
SELECT： 執(zhí)行select操作，選擇指定的列，插入到虛擬表VT8中。
DISTINCT： 對(duì)VT8中的記錄進(jìn)行去重。產(chǎn)生虛擬表VT9.
ORDER BY: 將虛擬表VT9中的記錄按照<order_by_list>進(jìn)行排序操作，產(chǎn)生虛擬表VT10.
LIMIT：取出指定行的記錄，產(chǎn)生虛擬表VT11, 并將結(jié)果返回。

多表查詢(子查詢）

方法一：用IN語(yǔ)句多層嵌套：作為子查詢的SELECT只能包含單列

方法二：

數(shù)據(jù)庫(kù)表都是關(guān)系表，現(xiàn)有：
Orders表：訂單編號(hào)，客戶ID, 訂單日期
OrderItems表：訂單編號(hào)，...（等等訂單物品之類的列）
Customers表：客戶ID,...（客戶其他信息）
(分析：OrderItems表 訂單--物品；Customers表：客戶--客戶身份信息；
       Orders表：訂單--客戶)
現(xiàn)需要列出訂購(gòu)了物品RGAN01的所有顧客的身份信息（就是需要從物品--訂單--客戶--身份信息，這樣一個(gè)步驟）

SELECT cust_name, cust_contact--客戶身份信息
FROM Customers
WHERE cust_id IN （SELECT cust_id--客戶id
                   FROM Order
                   WHERE order_num IN(SELECT order_num --訂單編號(hào)
                                      FROM OrderItems
                                      WHERE prod_id = 'RGAN01'));

聯(lián)結(jié) JOIN ON

為什么要聯(lián)結(jié)？

數(shù)據(jù)分成多個(gè)表可以更高效的存儲(chǔ)和管理，那么在查詢存在在多個(gè)表中的關(guān)聯(lián)數(shù)據(jù)的時(shí)候，就要把用到聯(lián)結(jié)，如果不用聯(lián)結(jié)的話返回的結(jié)果是笛卡爾積，一般是用where語(yǔ)句實(shí)現(xiàn)等值聯(lián)結(jié)，也有join on的語(yǔ)法代替where，聯(lián)結(jié)部分改變只是
from where部分，

內(nèi)連接 FROM A INNER JOIN B ON ******

就是等值連接,只是把符合條件的行值連接起來(lái)

自連接

使用表別名使自聯(lián)結(jié)代替子查詢，表現(xiàn)出得性能比where的子查詢更好

自然聯(lián)結(jié)

第一個(gè)表用通配符，再指出其他的列，使相同的列只出現(xiàn)一次

內(nèi)連接都是自然連接，自動(dòng)去重

外連接 FROM A INNER JOIN B ON

在多表連接時(shí)需要包括其中某個(gè)表中無(wú)匹配項(xiàng)的行和字段，空值填充

左連接就是保留完整左表，右連接就是保留完整右表，左右都保留就是全外聯(lián)結(jié)
MySQL中不支持全外聯(lián)結(jié)，可以用組合查詢UNION實(shí)現(xiàn)

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id

with three tables t1, t2, t3:

SELECT * FROM t1 
LEFT JOIN t2 ON t1.id = t2.id 
LEFT JOIN t3 ON t2.id = t3.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id 
LEFT JOIN t3 ON t2.id = t3.id 
UNION 
SELECT * FROM t1 
RIGHT JOIN t2 ON t1.id = t2.id 
RIGHT JOIN t3 ON t2.id = t3.id
————————————————
版權(quán)聲明：本文為CSDN博主「xiaoxiang-chen」的原創(chuàng)文章，遵循 CC 4.0 BY-SA 版權(quán)協(xié)議，轉(zhuǎn)載請(qǐng)附上原文出處鏈接及本聲明。
原文鏈接：https://blog.csdn.net/fengzijinliang/article/details/52513050

組合查詢 UNION

將多個(gè)select查詢的結(jié)果合并在一起，在每段SELECT語(yǔ)句中間加入關(guān)鍵字UNION,他在結(jié)果中會(huì)自動(dòng)去除重復(fù)行，使用關(guān)鍵字union all就不會(huì)消除重復(fù)行，如果需要對(duì)最終結(jié)果進(jìn)行排序，就把ordered by關(guān)鍵字放在最末尾

其他的UNION有
except(檢索只在第一個(gè)表存在而第二個(gè)不存在的行)，
intersect(檢索兩個(gè)表都存在的行)
這些都可以用聯(lián)結(jié)實(shí)現(xiàn)

表中插入數(shù)據(jù) INSERT

1.插入完整的行  
INSERT INTO Customers(xxxx,
                      xxx,
                      ...,
                      x)--指定列名
VALUES('1',
       '2',
       ...,
       '10')--要對(duì)應(yīng)好各個(gè)字段的值
       
列可以省略部分列，前提是表的該列允許NULL類型的值或者他不允許NULL但是他會(huì)給出默認(rèn)值，如果不滿足就會(huì)插入失敗  

2.與SELECT FROM WHERE語(yǔ)句一起使用，實(shí)現(xiàn)查詢直接插入，也可以實(shí)現(xiàn)多行插入
INSERT INTO Customers(xxxx,
                      xxx,
                      ...,
                      x)--指定列名
SELECT  xxxx,
        xxx,
        ...,
        x
FROM Products
WHERE prod_name LIKE 'Fish%';--要對(duì)應(yīng)好各個(gè)字段的值

3.整表的復(fù)制
MySQL語(yǔ)法如下：
CREATE TABLE CustCopy(新表) AS
SELECT * 
FROM Customers
WHERE ---
GROUP BY 等等，還可以聯(lián)結(jié)

更新 UPDATE - SET - WHERE

刪除 DELETE FROM -WHERE

鎖image

用WHERE語(yǔ)句避免更新所有的行，單獨(dú)使用UPDATE就是更新所有行該列的值  
1.更新某一行某一列：
UPDATE Customers
SET cust_email = xxx
WHERE cust_id = xx--把某一行某一列值設(shè)為NULL相當(dāng)于刪除

2.用一個(gè)表的數(shù)據(jù)去更新另一個(gè)表的數(shù)據(jù),UPDATE語(yǔ)句是否支持FROM


刪除的是表的內(nèi)容就算內(nèi)容全部刪除也會(huì)留下空表
如果刪除所有行 可以使用TRUNCATE TABLE

有一些原則
1.注意考慮where語(yǔ)句
2.保證每個(gè)表都有主鍵
3.在更新刪除之前，先用select 進(jìn)行測(cè)試
4.強(qiáng)制實(shí)施，不允許刪除與其他表關(guān)聯(lián)的行
5.提前使用約束避免一些操作

表創(chuàng)建CREATE 表結(jié)構(gòu)修改ALTER 刪除表DROP

只有不允許NULL的列可以作為主鍵

創(chuàng)建表：指定列表名，列名，數(shù)據(jù)類型，值的限制
CREATE TABLE Xxxxx:
{
    x       text      NOT NULL,--不允許空值
    xx      INTEGER   ,--默認(rèn)允許NULL，DB2要求必須指定
    ...
    xxxxxxx INTEGER   NOT NULL     DEFAULT 1,--設(shè)定默認(rèn)值，常用！
}
可設(shè)置默認(rèn)值，添加數(shù)據(jù)時(shí)候不提供值就會(huì)自動(dòng)加入默認(rèn)值
常用時(shí)間作為默認(rèn)值，
**** DEFAULT  CURRENT_DATE()--這是MySQL的寫法

ALTER TABLE Vendors
ADD vend_phone CHAR(20);--增加一列，刪除用DROP

DROP TABLE CustCopy;--

ALTER TABLE之前要注意備份，該語(yǔ)句不能撤銷

復(fù)雜表結(jié)構(gòu)的刪除一般只有，先把數(shù)據(jù)copy到一個(gè)新表，檢查新表，重命名舊表或者刪除，舊表名字命名新表，再進(jìn)行一些索引外鍵等等的創(chuàng)建

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

友情鏈接更多精彩內(nèi)容