SQL簡明數(shù)據(jù)分析教程

SQL與MySQL簡介

數(shù)據(jù)庫基礎(chǔ)

從SQL的角度來看,數(shù)據(jù)庫就是一個(gè)以某種有組織的方式存儲的數(shù)據(jù)集合政溃。我們可以采用數(shù)據(jù)庫對數(shù)據(jù)進(jìn)行有效的存儲與管理艇劫,并運(yùn)用數(shù)據(jù)庫進(jìn)行合理的處理與分析,使其轉(zhuǎn)化為有價(jià)值的數(shù)據(jù)信息崩掘。

理解數(shù)據(jù)庫的一種簡單辦法是將其想象為一個(gè)存放數(shù)據(jù)的文件柜七嫌, 往文件柜里存放數(shù)據(jù)資料時(shí),先在文件柜中創(chuàng)建文件呢堰,然后將相關(guān)的數(shù)據(jù)資料放入特定的文件中抄瑟,這種存儲某種特定類型數(shù)據(jù)的結(jié)構(gòu)化的文件就稱為表凡泣。每個(gè)表都有唯一的表名(在同一數(shù)據(jù)庫中不能有兩個(gè)相同的表名)枉疼。表由列組成,每一列存儲著某種特定的信息鞋拟,并且具有相應(yīng)的數(shù)據(jù)類型骂维。表中的數(shù)據(jù)是按行存儲的,所保存的每個(gè)記錄存儲在自己的行內(nèi)贺纲,并且應(yīng)盡量保證每一行都有一列(或幾列)能夠唯一標(biāo)識該行的主鍵航闺。表中的任何列都可以作為主鍵,只要它滿足以下條件:任意兩行主鍵值不同猴誊、主鍵列不允許NULL值潦刃,且習(xí)慣上不更新或重用主鍵值。

什么是SQL

SQL(發(fā)音為字母S-Q-L或sequel)的全稱為“Structured Query Language” (結(jié)構(gòu)化查詢語言)懈叹,是一種專門用來與數(shù)據(jù)庫溝通的語言乖杠,用以查詢關(guān)系數(shù)據(jù)庫表的內(nèi)容,以及插入澄成、更新和刪除數(shù)據(jù)胧洒。SQL簡潔易學(xué),功能強(qiáng)大墨状,靈活使用其語言元素卫漫,可以進(jìn)行非常復(fù)雜和高級的數(shù)據(jù)庫操作轩触。

SQL不是某個(gè)特定數(shù)據(jù)庫供應(yīng)商專有的語言贯溅,幾乎所有主要的DBMS(數(shù)據(jù)庫管理系統(tǒng))都支持SQL怠惶,因此掌握該語言使你幾乎能與所有數(shù)據(jù)庫打交道澡为。標(biāo)準(zhǔn)SQL由ANSI標(biāo)準(zhǔn)委員會管理日川,從而稱為ANSI SQL。所有主要的DBMS即使有自己的擴(kuò)展(提供執(zhí)行特定操作的額外功能或簡化方法)进胯,也都支持ANSI SQL像鸡。本教程主要使用標(biāo)準(zhǔn)SQL,提供的SQL示例代碼在MySQL 5.7.19環(huán)境下測試通過漏策,然而本教程所探討的概念也適用于其他SQL環(huán)境派哲。

下載與安裝MySQL

SQL不是一個(gè)應(yīng)用,而是一種語言掺喻。因此芭届,為了學(xué)習(xí)SQL,我們還需要一個(gè)支持SQL語句執(zhí)行的應(yīng)用程序感耙。實(shí)際上褂乍,數(shù)據(jù)的所有存儲、檢索即硼、管理和處理都是由數(shù)據(jù)庫軟件——DBMS(數(shù)據(jù)庫管理系統(tǒng))完成的逃片。較為流行的DBMS包括MySQL、 Oracle只酥、 Microsoft SQL Sever褥实、 PostgreSQL等,以下主要介紹MySQL裂允。

MySQL是一個(gè)開源的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)损离,由瑞典MySQL AB公司開發(fā),目前為 Oracle 旗下產(chǎn)品绝编,是世界上最受歡迎的數(shù)據(jù)庫管理系統(tǒng)之一僻澎。MySQL官網(wǎng)下載地址為: https://dev.mysql.com/downloads/mysql/。在Windows環(huán)境下以MSI方式安裝步驟如下:

  1. 在官網(wǎng)https://dev.mysql.com/downloads/windows/installer/5.7.html 下載最新5.7社區(qū)版的安裝包之后十饥,雙擊MSI安裝文件窟勃,出現(xiàn)安裝協(xié)議界面,同意協(xié)議逗堵,并單擊【next】秉氧;

  2. 選擇安裝類型。默認(rèn)安裝【developer Default】(包含有MySQL Workbench)砸捏,此外谬运,可以根據(jù)需要選擇 【server only】(僅安裝服務(wù)器)、 【client only】(僅安裝客戶端)垦藏、 【full】 (安裝全部功能) 或者 【custom】 (自定義安裝)梆暖,右側(cè)的【Setup Type Description】給出了每一種安裝類型所包含的安裝項(xiàng)目。


  3. Check Requirements掂骏。如果提示電腦上缺少相關(guān)組件(如python)轰驳,可以根據(jù)提示下載安裝,也可以選擇直接單擊【next】進(jìn)入下一步;


  4. 選擇【Execute】開始正式安裝级解,并且可以看到安裝進(jìn)度冒黑;


  5. 安裝完成后根據(jù)提示點(diǎn)擊【next】按鈕進(jìn)入如下產(chǎn)品配置面板,選擇【next】勤哗;


  6. 根據(jù)需要選擇服務(wù)器類型(默認(rèn)為 【Developer Machine】)抡爹,TCP/IP默認(rèn)端口為3306;


  7. 進(jìn)入【賬戶和角色】面板芒划。根據(jù)提示設(shè)置root賬戶密碼冬竟,并可根據(jù)需要添加用戶;


  8. 之后可根據(jù)安裝向?qū)П3帜J(rèn)選項(xiàng)民逼,選擇【next】及【execute】直到出現(xiàn)以下界面表示配置完成泵殴,并根據(jù)需要選擇是否啟動MySQL Workbench及MySQL Shell。


使用MySQL

有兩種方式使用MySQL拼苍。在安裝MySQL時(shí)會自帶一個(gè)名為mysql命令行實(shí)用程序笑诅,這是一個(gè)純文本工具,可以用來執(zhí)行任何SQL語句疮鲫。另外吆你,MySQL 官方發(fā)布了一個(gè)名為MySQL Workbench的可視化管理工具,用戶可以在安裝MySQL時(shí)一起選擇安裝棚点,也可以獨(dú)立下載安裝早处。較為流行的MySQL數(shù)據(jù)庫可視化管理工具包括MySQL Workbench、 Navicat瘫析、 phpMyAdmin、Sequel Pro等默责。在Windows平臺學(xué)習(xí)SQL時(shí)贬循,推薦使用MySQL Workbench。

(1)mysql命令行實(shí)用程序

  • 有兩種方式使用mysql命令行實(shí)用程序桃序。第一種方式是從開始菜單欄中打開MySQL 5.7 Command Line Client杖虾,輸入密碼,出現(xiàn)如下界面表示數(shù)據(jù)庫連接成功:


  • 第二種方式:如果已經(jīng)將MySQL Sever安裝目錄下的bin子目錄加入到了windows的環(huán)境變量中媒熊,可以直接在命令行中輸入mysql -u root -p 命令奇适,回車后輸入密碼即可連接成功。如下圖所示:


  • 數(shù)據(jù)庫連接成功后芦鳍,在mysql>提示下輸入U(xiǎn)SE database 打開數(shù)據(jù)庫嚷往,例如USE world就是打開world數(shù)據(jù)庫。
  • 在mysql>提示下輸入SQL語句柠衅,每條語句須以分號(;)結(jié)束皮仁。結(jié)果將顯示在屏幕上。
  • 輸入\h可以顯示可能用到的命令列表,輸入\s可以顯示狀態(tài)信息(如MySQL版本信息)贷祈。
  • 輸入\q或quit可以退出程序趋急。

(2)可視化管理工具 MySQL Workbench

盡管我們可以在命令提示符下通過一行行的輸入或者通過重定向文件來執(zhí)行mysql語句,但該方式效率較低势誊, 且由于沒有執(zhí)行前的語法自動檢查呜达, 輸入失誤造成的一些錯(cuò)誤的可能性會大大增加。使用MySQL Workbench 可以通過可視化的方式直接管理數(shù)據(jù)庫中的內(nèi)容, 并且 MySQL Workbench 的 SQL 腳本編輯器支持語法高亮以及輸入時(shí)的語法檢查粟耻,方便我們學(xué)習(xí)SQL闻丑。可通過以下操作使用MySQL Workbench:

  • 運(yùn)行MySQL Workbench勋颖。界面左下角列出了可用的MySQL數(shù)據(jù)庫連接嗦嗡,可直接點(diǎn)擊打開,輸入密碼便可連接侥祭。如果沒有在此列出,可選擇【MySQL Connections】右側(cè)的加號按鈕茄厘,創(chuàng)建新的連接矮冬。


  • 登陸成功后的Workbench界面概覽如下圖所示:



    其中,區(qū)域1顯示的是數(shù)據(jù)庫服務(wù)器中已經(jīng)創(chuàng)建的數(shù)據(jù)庫列表次哈。區(qū)域2是關(guān)于數(shù)據(jù)庫的操作列表胎署。區(qū)域3是sql的編輯器和執(zhí)行環(huán)境,區(qū)域4是執(zhí)行結(jié)果的列表窑滞。

  • 輸入SQL語句后琼牧,點(diǎn)擊Execute(帶有閃電圖片) 或使用快捷鍵【ctrl+enter】運(yùn)行SQL,將結(jié)果顯示在下面哀卫。如下圖所示:


導(dǎo)入樣例表

完成前面的安裝與設(shè)定工作后巨坊,MySQL中已經(jīng)有一個(gè)內(nèi)建的范例數(shù)據(jù)庫world。但這個(gè)數(shù)據(jù)庫比較簡單此改,為了更好地練習(xí)SQL語句趾撵,我們還需要做最后一項(xiàng)準(zhǔn)備工作:導(dǎo)入樣例表。后續(xù)教程將會基于此樣例表編寫各式SQL語句共啃。

(1)樣例表描述
本教程采用著名暢銷書Sams Teach Yourself SQL in 10 Minutes 一書中所提供的樣例表占调。樣例表描述的是一個(gè)隨身物品推銷商使用的訂單錄入系統(tǒng),下圖顯示了5張表之間的關(guān)系:

其中移剪,Customers表存儲所有顧客信息究珊;Vendors表存儲銷售產(chǎn)品的供應(yīng)商,每個(gè)供應(yīng)商在這個(gè)表中都有一個(gè)記錄挂滓,包含了供應(yīng)商名字苦银、地址啸胧、所在城市等數(shù)據(jù)元素,其中使用vend_id作為其主鍵幔虏;Products表包含產(chǎn)品目錄纺念,每行一個(gè)產(chǎn)品,并且借助vend_id(供應(yīng)商的唯一ID)與供應(yīng)商相關(guān)聯(lián)想括;Orders表存儲顧客訂單陷谱,每個(gè)訂單都有唯一編號(order_num列),且根據(jù)cust_id列關(guān)聯(lián)到相應(yīng)的顧客瑟蜈;OrderItems表則存儲每個(gè)訂單中的實(shí)際物品烟逊,每個(gè)訂單的每個(gè)物品一行。對于Orders表的每一行铺根,在OrderItems表中有一行或多行與之對應(yīng)宪躯。每個(gè)訂單物品由訂單號加訂單物品(第一個(gè)物品、第二個(gè)物品等)唯一標(biāo)識位迂。訂單物品用order_num列與其相應(yīng)的訂單相關(guān)聯(lián)访雪。此外,每個(gè)訂單物品還包含該物品的產(chǎn)品ID(把物品關(guān)聯(lián)到Products表)掂林。上圖中表之間的連線便說明了表之間的關(guān)系臣缀。

(2)導(dǎo)入樣例表

http://www.forta.com/books/0672336073/ 下載適用于MySQL的SQL腳本,含有兩個(gè)文件:

  • create.txt包含創(chuàng)建5個(gè)數(shù)據(jù)庫表(包括定義所有主鍵和外鍵約束)的SQL語句泻帮。
  • populate.txt包含用來填充這些表的SQL INSERT語句精置。

導(dǎo)入步驟如下:

  • 根據(jù)上述教程,運(yùn)行MySQL Workbench锣杂,并連接到MySQL脂倦。新建一個(gè)數(shù)據(jù)庫,點(diǎn)擊【Create a New Schema】按鈕蹲堂,出現(xiàn)如下對話框狼讨。輸入新建數(shù)據(jù)庫的名稱,選擇【apply】柒竞。


  • 在彈出的確認(rèn)窗口中選擇【Apply】及【Finish】。然后在SCHEMAS一欄中雙擊創(chuàng)建成功的tjsql播聪,表示選中該數(shù)據(jù)庫朽基,可以看到tjsql一欄變?yōu)楹隗w。


  • 將create.txt中的內(nèi)容復(fù)制粘貼到SQL窗口中离陶,并選擇執(zhí)行稼虎,用以創(chuàng)建5個(gè)數(shù)據(jù)庫表;
    同樣招刨,將populate.txt中的內(nèi)容復(fù)制粘貼到SQL窗口中并執(zhí)行霎俩,用以填充5個(gè)數(shù)據(jù)庫表。
  • 使用SELECT * FROM Customers; 語句測試結(jié)果如下圖表示導(dǎo)入成功。


查看數(shù)據(jù)庫和表

(1)查看數(shù)據(jù)庫

在MySQL中可以建立許多數(shù)據(jù)庫打却,當(dāng)不知道可以使用哪些數(shù)據(jù)庫時(shí)杉适,可用MySQL的SHOW命令顯示當(dāng)前可用的數(shù)據(jù)庫列表:

SHOW DATABASES;

顯示當(dāng)前服務(wù)器的所有數(shù)據(jù)庫

在MySQL Workbench中輸出結(jié)果為:

包含在這個(gè)列表中的可能是MySQL內(nèi)部使用的數(shù)據(jù)庫(如information_schema)。當(dāng)然柳击,你自己的數(shù)據(jù)庫列表可能看上去與這里的不一樣猿推。

(2)選擇數(shù)據(jù)庫
在執(zhí)行任何數(shù)據(jù)庫操作前,都需要選擇一個(gè)數(shù)據(jù)庫捌肴,才能讀取其中的數(shù)據(jù)蹬叭。方法是使用USE關(guān)鍵字:

USE tjsql;

選擇使用tjsql數(shù)據(jù)庫

USE 語句并不返回任何結(jié)果。如果是在MySQL Workbench中運(yùn)行該語句状知,可以在SCHEMAS一欄看到被選中的數(shù)據(jù)庫名稱變?yōu)楹隗w(與雙擊該數(shù)據(jù)庫名稱等效)秽五;如果是在mysql 命令行實(shí)用程序中執(zhí)行該語句,則會顯示輸出“Database changed” 表示數(shù)據(jù)庫選擇成功饥悴。

(3)查看數(shù)據(jù)表
進(jìn)入到某個(gè)數(shù)據(jù)庫后坦喘,我們可以使用 SHOW TABLES;來顯示該數(shù)據(jù)庫有多少個(gè)數(shù)據(jù)表:

SHOW TABLES;

顯示當(dāng)前數(shù)據(jù)庫下所有的表

例如,選擇tjsql數(shù)據(jù)庫后運(yùn)行該語句的輸出為:

同樣铺坞,SHOW 也可以用來顯示表列:

SHOW COLUMNS FROM customers;

顯示customers表每個(gè)字段的數(shù)據(jù)類型起宽、是否允許 NULL 、鍵信息济榨、默認(rèn)值以及其他信息

輸出為:


此外坯沪,MySQL還支持用 DESCRIBE 作為 SHOW COLUMNS FROM 的一種快捷方式。例如擒滑,DESCRIBE customers; 與SHOW COLUMNS FROM customers; 有著相同的輸出腐晾,兩者完成同樣的功能。

MySQL所支持的其他 SHOW 語句還有:

  • SHOW STATUS :用于顯示廣泛的服務(wù)器狀態(tài)信息丐一;
  • SHOW CREATE DATABASE和SHOW CREATE TABLE :用來顯示創(chuàng)建特定數(shù)據(jù)庫或表的MySQL語句藻糖;
  • SHOW GRANTS :用來顯示授予用戶的安全權(quán)限;
  • SHOW ERRORS 和 SHOW WARNINGS :用來顯示服務(wù)器錯(cuò)誤或警告消息库车。

SELECT基礎(chǔ)查詢

在第一部分,我們介紹了數(shù)據(jù)庫和SQL的概念洋满,以及MySQL安裝和使用的一些相關(guān)知識牺勾,并導(dǎo)入了準(zhǔn)備好的樣例表翻具,完成了SQL環(huán)境的基本搭建裆泳。在第二部分晾虑,我們將會介紹SELECT語句的基礎(chǔ)知識,并用它來進(jìn)行一些基本的SQL查詢笙隙。

檢索數(shù)據(jù)

在數(shù)據(jù)分析過程中竟痰,SELECT語句是最經(jīng)常使用的SQL語句坏快。它的用途是從一個(gè)或多個(gè)表中檢索數(shù)據(jù)。為此祥得,至少需要給出兩條信息——想選擇什么级及,以及從什么地方選擇饮焦。
(1)檢索單個(gè)列

最基礎(chǔ)的SELECT 語句如下所示:

SELECT prod_name
FROM Products;

從 Products 表中檢索一個(gè)名為prod_name 的列

在上述語句中,所需的列名在 SELECT 關(guān)鍵字之后給出, FROM關(guān)鍵字指出從其中檢索數(shù)據(jù)的表名锋爪。返回的數(shù)據(jù)沒有過濾其骄,也沒有經(jīng)過排序拯爽。輸出如下所示:

(2)檢索多個(gè)列
從一個(gè)表中檢索多個(gè)列逼肯,只需在 SELECT 關(guān)鍵字后給出多個(gè)列名篮幢,列名之間以逗號分隔:

SELECT prod_id, prod_name, prod_price
FROM Products;

從 Products 表中檢索prod_id, prod_name, prod_price三個(gè)列

(3)檢索所有列
使用星號(*)通配符可以檢索所有列而不必逐個(gè)列出所有的列名:

SELECT *
FROM Products;

返回 Products 表中所有列

注:使用通配符的好處是比較方便,且能檢索出名字未知的列搜锰。但通常會降低檢索和應(yīng)用程序的性能蛋叼,除非確實(shí)需要表中的每個(gè)列,否則最好別使用*通配符薯嗤。

(4)檢索不同的行
如前所述骆姐,SELECT語句會返回所有匹配的行捏题。如果不希望相同的記錄重復(fù)出現(xiàn)带射,可使用 DISTINCT 關(guān)鍵字:

SELECT DISTINCT vend_id
FROM Products;

返回 Products 表中不同(唯一)的vend_id 行

該語句只返回三行不重復(fù)值的vend_id窟社。如下所示:


與之相對的灿里,如果不指定DISTINCT關(guān)鍵字匣吊,只輸入SELECT vend_id FROM Products;則會返回9行(分別對應(yīng)Products 表中9種產(chǎn)品):

注:不能部分使用 DISTINCT關(guān)鍵字社痛。DISTINCT作用于所有的列褥影,不僅僅是跟在其后的那一列凡怎。例如,指定SELECT DISTINCT vend_id, prod_price房匆,除非指定的兩列完全相同,否則所有的行都會被檢索出來岳链。

(5)限制結(jié)果

若只需要返回第一行或前幾行,可使用 LIMIT 子句:

SELECT prod_name
FROM Products
LIMIT 5;

只返回 Products 表中前5行記錄

除了使用LIMIT關(guān)鍵字指定返回的行數(shù)之外,還可以使用OFFSET 關(guān)鍵字指定從哪一行開始檢索:

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;

返回 Products 表中從第5行起的5行數(shù)據(jù)

可以發(fā)現(xiàn)引矩,在這個(gè)例子中只返回了4行,這是因?yàn)樵赑roducts表中只有9種產(chǎn)品膳帕,而第一個(gè)被檢索的產(chǎn)品稱為第0行,最后一個(gè)被檢索的產(chǎn)品是第8行,所以最終只返回了4行數(shù)據(jù)。

注:MySQL還支持簡化版的“LIMIT 4O FFSET 3”,即“LIMIT 3,4”,表示從行3開始取4行。

排序檢索數(shù)據(jù)

這個(gè)部分會簡單介紹如何使用 SELECT 語句的 ORDER BY 子句對檢索出來的數(shù)據(jù)在一個(gè)或多個(gè)列上進(jìn)行排序。

(1)按單個(gè)列排序
使用ORDER BY子句進(jìn)行排序方法如下:

SELECT prod_name
FROM Products
ORDER BY prod_name;

檢索 Products 表的prod_name列,并按prod_name列字母順序排序

與之對比褥符,若不指定順序趟大,輸入“SELECT prod_name FROM Products”語句則輸出結(jié)果為:


如果不排序叽讳,數(shù)據(jù)一般以它在底層表中出現(xiàn)的順序顯示邑狸,這有可能是數(shù)據(jù)最初添加到表中的順序她紫。但如果數(shù)據(jù)進(jìn)行過更新或刪除硬萍,則這個(gè)順序?qū)艿紻BMS重用回收存儲空間的方式的影響助赞。因此群叶,關(guān)系數(shù)據(jù)庫設(shè)計(jì)理論認(rèn)為赎离,如果不明確規(guī)定排序順序,則不應(yīng)該假定檢索出的數(shù)據(jù)的順序有任何意義。

(2)按多個(gè)列排序

要按多個(gè)列排序鸡岗,只需指定多個(gè)列名揣苏,列名之間用逗號分開即可:

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price, prod_name;
檢索Products 表中的3個(gè)列坑质,先按價(jià)格排序吃沪,然后按名稱排序

注: ORDER BY子句允許選用非檢索的列作為排序依據(jù)降铸。

(3)按列位置排序
ORDER BY支持按相對列位置進(jìn)行排序:

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY 2, 3;
先按SELECT清單中第二個(gè)列prod_price(價(jià)格)進(jìn)行排序俊嗽,然后按第三個(gè)列prod_name(名稱)排序

按列相對位置排序好處在于不用重新輸入列名绍豁,但需要特別注意每個(gè)列名的確切位置邪铲,以免在對SELECT清單進(jìn)行更改時(shí)錯(cuò)用列名排序被饿。

(4)指定排序方向
排序時(shí),默認(rèn)為升序排序(從A到Z)挠说,但可以指定DESC關(guān)鍵字降序排序(從Z到A):

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;

對prod_price列降序排序蛙奖,對prod_name列仍按升序排序琐脏。

從上述例子中可以看出,DESC關(guān)鍵字只作用于直接位于其前面的列名财著。如果想在多個(gè)列上進(jìn)行降序排序廉嚼,必須對每一列指定DESC關(guān)鍵字。

使用 ORDER BY 和 LIMIT 的組合阳仔,能夠找出一個(gè)列中最高或最低的值忘渔。下面的例子演示如何找出最昂貴物品的值:

SELECT prod_price
FROM Products
ORDER BY prod_price DESC
LIMIT 1;

注:需要注意ORDER BY子句的位置椅棺。在給出 ORDER BY 子句時(shí)含滴,應(yīng)該保證它位于 FROM 子句之后诱渤。如果使用 LIMIT關(guān)鍵字 ,則它須位于 ORDER BY之后谈况。一般來說勺美,ORDER BY 子句必須是SELECT 語句中的最后一條子句。

過濾數(shù)據(jù)

本節(jié)會介紹如何使用SELECT語句的WHERE子句指定搜索條件過濾返回的數(shù)據(jù)碑韵。

(1)使用WHERE子句

在 SELECT 語句中赡茸,WHERE子句在FROM子句之后給出,返回滿足指定搜索條件的數(shù)據(jù):

SELECT prod_name, prod_price
FROM Products
WHERE prod_price = 3.49;

從products表中檢索兩個(gè)列祝闻,但只返回prod_price值為3.49的行

除了上述例子的相等條件判斷占卧,WHERE子句還支持以下條件操作符:


例如,使用<>操作符(或!=)進(jìn)行不匹配檢查:

SELECT vend_id, prod_name
FROM Products
WHERE vend_id <> 'DLL01';

從Products表中列出所有不是供應(yīng)商DLL01制造的產(chǎn)品

再如治筒,使用BETWEEN操作符可匹配處于某個(gè)范圍內(nèi)的值屉栓。需要指定范圍的端點(diǎn)值:

SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5.99 AND 9.49;

檢索價(jià)格在5.99美元和9.49美元之間的所有產(chǎn)品(包括5.99美元和9.49美元)

此外,還有一個(gè)特殊的IS NULL子句耸袜,可用來檢查具有 NULL 值的列:

SELECT cust_name
FROM CUSTOMERS
WHERE cust_email IS NULL;

返回沒有郵件地址為NULL的顧客友多。

(2)組合WHERE子句

SQL還允許使用AND或OR操作符組合出多個(gè)WHERE子句,例如使用AND操作符:

SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id = 'DLL01' AND prod_price <= 4;

檢索由供應(yīng)商DLL01制造且價(jià)格小于等于4美元的所有產(chǎn)品的名稱和價(jià)格(需同時(shí)滿足兩個(gè)條件)

同理堤框,可使用OR操作符檢索匹配任一條件的行:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

檢索由供應(yīng)商DLL01或供應(yīng)商BRS01制造的所有產(chǎn)品的名稱和價(jià)格(只需滿足任一條件即可)

(3)求值順序

WHERE子句允許包含任意數(shù)目的AND和OR操作符以進(jìn)行復(fù)雜域滥、高級的過濾纵柿。但需要注意求值順序:AND操作符優(yōu)先級高于OR操作符,但可以使用圓括號明確地指定求值順序:

SELECT prod_name, prod_price
FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = ‘BRS01’)
AND prod_price <= 10;

選擇由供應(yīng)商DLL01或BRS01制造的启绰,且價(jià)格在10美元及以下的所有產(chǎn)品

(4)IN操作符

IN操作符用來指定條件范圍昂儒,范圍中的每個(gè)條件都可以進(jìn)行匹配。條件之間用逗號分隔:

SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

檢索由供應(yīng)商DLL01和BRS01制造的所有產(chǎn)品

注:IN操作符完成了與OR相同的功能委可,但與OR相比渊跋,IN操作符有如下優(yōu)點(diǎn):

  • 有多個(gè)條件時(shí),IN操作符比一組OR操作符更清楚直觀且執(zhí)行得更快着倾;
  • 在與其他AND和OR操作符組合使用IN時(shí)拾酝,求值順序更容易管理;
  • IN操作符的最大優(yōu)點(diǎn)是可以包含其他SELECT語句卡者,能動態(tài)地建立WHERE子句蒿囤。

(4)NOT操作符

NOT操作符用來否定跟在它之后的條件:

SELECT vend_id,prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name;

用來檢索除了供應(yīng)商DLL01之外制造的所有產(chǎn)品

上面的例子也可以使用<>操作符來完成。但在更復(fù)雜的子句中崇决,NOT是非常有用的材诽。例如,在與IN操作符聯(lián)合使用時(shí)恒傻,NOT可以非常簡單地找出與條件列表不匹配的行:

SELECT vend_id,prod_name
FROM Products
WHERE vend_id NOT IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name;

用來檢索除了供應(yīng)商DLL01和BRS01之外制造的所有產(chǎn)品

注:和多數(shù)其他 DBMS允許使用 NOT 對各種條件取反不同脸侥,MySQL支持使用 NOT 對 IN 、 BETWEEN 和EXISTS子句取反盈厘。

(5)LIKE操作符

使用LIKE操作符和通配符可以進(jìn)行模糊搜索湿痢,以便對數(shù)據(jù)進(jìn)行復(fù)雜過濾。最常使用的通配符是百分號( % )扑庞,它可以表示任何字符出現(xiàn)任意次數(shù):

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

檢索產(chǎn)品名含有[bean bag]字段的所有產(chǎn)品

另一個(gè)有用的通配符是下劃線(_)。它的用途與%一樣拒逮,但只匹配單個(gè)字符罐氨,而不是多個(gè)或0個(gè)字符:

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '_ inch teddy bear';

檢索產(chǎn)品名含有[ inch teddy bear]字段的產(chǎn)品,且[ inch teddy bear]字段前有且只能有一個(gè)字符

注:通配符搜索只能用于文本字段(串)滩援,非文本數(shù)據(jù)類型字段不能使用通配符搜索栅隐。

2.4 函數(shù)

這部分將會介紹什么是計(jì)算字段以及MySQL支持的部分?jǐn)?shù)據(jù)處理函數(shù)。

(1)計(jì)算字段
有時(shí)候從數(shù)據(jù)庫中直接檢索出來的數(shù)據(jù)并不是我們想要的玩徊,我們希望得到的是經(jīng)過轉(zhuǎn)換租悄、計(jì)算或格式化過的數(shù)據(jù)。例如恩袱,在物品訂單表中存儲的是物品的價(jià)格和數(shù)量泣棋,但我們需要的是每個(gè)物品的總價(jià)格(用價(jià)格乘以數(shù)量即可)。這時(shí)候計(jì)算字段就可以排上用場了畔塔。計(jì)算字段并不實(shí)際存在于數(shù)據(jù)庫表中潭辈,而是運(yùn)行時(shí)在SELECT語句內(nèi)創(chuàng)建的鸯屿。

SELECT prod_id,
       quantity,
       item_price,
quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008;

求得訂單號20008中每個(gè)物品的數(shù)量、單價(jià)以及總價(jià)格(單價(jià)乘數(shù)量)

在該例中把敢,quantity*item_price 表示對檢索出的數(shù)據(jù)進(jìn)行算術(shù)計(jì)算寄摆, AS expanded_price指示SQL創(chuàng)建了一個(gè)包含指定計(jì)算結(jié)果的名為expanded_price的計(jì)算字段。其中expanded_price叫做別名修赞,用AS關(guān)鍵字賦予婶恼,客戶端應(yīng)用可以像使用其他列一樣引用這個(gè)新計(jì)算列。

SQL支持+柏副、-勾邦、*、/(加減乘除)四種基本算術(shù)操作符搓扯。此外检痰,圓括號可用來區(qū)分優(yōu)先順序。

除了算術(shù)運(yùn)算锨推,我們還經(jīng)常使用拼接字段铅歼。例如,vendors 表的兩個(gè)列 vend_name 和 vend_country分別存儲了供應(yīng)商的名字和位置信息换可,假如要生成一個(gè)供應(yīng)商報(bào)表椎椰,需要按照 name(location) 這樣的格式列出供應(yīng)商的信息,即把vend_name和 vend_country兩個(gè)列拼接起來沾鳄。在MySQL中慨飘,可使用Concat() 函數(shù)來拼接兩個(gè)列:

SELECT Concat(vend_name,’(’,vend_country,’)’)
 AS vend_title
FROM Vendors
ORDER BY vend_name;

將vend_name和 vend_country兩個(gè)列拼接成 name(location) 的格式,并叫做別名vend_title

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

與大多數(shù)其他計(jì)算機(jī)語言一樣译荞,SQL支持利用用函數(shù)來處理數(shù)據(jù)瓤的。上述用來拼接兩個(gè)列的Concat()就是函數(shù)的一個(gè)例子 。大多數(shù)SQL實(shí)現(xiàn)支持4種類型的函數(shù):文本處理函數(shù)吞歼、日期和時(shí)間處理函數(shù)圈膏、數(shù)值處理函數(shù)以及返回DBMS正使用的特殊信息(如返回用戶登錄信息,檢查版本細(xì)節(jié))的系統(tǒng)函數(shù)篙骡。一下簡要介紹前三種數(shù)據(jù)處理函數(shù)稽坤。

  • 文本處理函數(shù):用于處理文本串(如刪除或填充值,轉(zhuǎn)換值為大寫或小寫)的文本函數(shù)糯俗。例如將文本轉(zhuǎn)換為大寫的Upper() 函數(shù):
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;

將vend_name列轉(zhuǎn)換為大寫

下表列出了一些常用的文本處理函數(shù)尿褪。

上表中的SOUNDEX 需要做進(jìn)一步的解釋。 SOUNDEX 是一個(gè)將任何文本串轉(zhuǎn)換為描述其語音表示的字母數(shù)字模式的算法得湘。 SOUNDEX 考慮了類似的發(fā)音字符和音節(jié)杖玲,使得能對串進(jìn)行發(fā)音比較而不是字母比較。

例如淘正,Customers表中有一個(gè)顧客Kids Place天揖,其聯(lián)系名為Michelle Green夺欲。但如果這是 錯(cuò)誤的輸入,此聯(lián)系名實(shí)際上應(yīng)該是Michael Green今膊,如果使用SOUNDEX()函數(shù)進(jìn)行搜索些阅,它就有匹配所有發(fā)音類似于Michael Green的聯(lián)系名:

SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green');

匹配所有發(fā)音類似于Michael Green的聯(lián)系名

  • 日期和時(shí)間處理函數(shù):用于處理日期和時(shí)間值并從這些值中提取特定成分(例如,返回兩個(gè)日期之差斑唬,檢查日期有效性等)的日期和時(shí)間函數(shù)市埋。目前為止,我們都是用比較數(shù)值和文本的 WHERE 子句過濾數(shù)據(jù)恕刘,但數(shù)據(jù)經(jīng)常需要用日期進(jìn)行過濾缤谎。

下表列出了一些常用的日期和時(shí)間處理函數(shù)。

需要注意MySQL使用的日期格式必須為yyyy-mm-dd褐着,例如2017年1月1日坷澡,應(yīng)寫成2017-01-01。一般如果要對日期進(jìn)行比較含蓉,需使用Date()函數(shù):

SELECT cust_id, order_num,order_date
FROM Orders
WHERE Date(order_date) BETWEEN '2012-01-01' AND '2012-01-31';

匹配2012年1月份下的所有訂單

還有另外一種辦法是使用Year()和Month()函數(shù):

SELECT cust_id, order_num,order_date
FROM Orders
WHERE Year(order_date) = 2012 AND Month(order_date) = 1;

匹配2012年1月份下的所有訂單

  • 數(shù)值處理函數(shù):用于在數(shù)值數(shù)據(jù)上進(jìn)行算術(shù)操作(如返回絕對值频敛,進(jìn)行代數(shù)運(yùn)算)的數(shù)值函數(shù)。這些函數(shù)主要用于代數(shù)馅扣、三角或幾何運(yùn)算斟赚,因此沒有串或日期時(shí)間處理函數(shù)的使用那么頻繁。但在主要DBMS的函數(shù)中差油,數(shù)值函數(shù)是最一致最統(tǒng)一的函數(shù)拗军,而文本處理函數(shù)、日期和時(shí)間處理函數(shù)在不同的DBMS中差別卻很大蓄喇。以下是一些常用的數(shù)值處理函數(shù):

(3)聚集函數(shù)

有時(shí)我們實(shí)際需要的是表中數(shù)據(jù)的匯總信息发侵,而不是實(shí)際數(shù)據(jù)本身,比如確定表中行數(shù)妆偏、表列的最大值器紧、最小值和平均值等。為此楼眷,MySQL給出了5個(gè)聚集函數(shù):

  • AVG()函數(shù):返回所有列的平均值,也可以用來返回特定列或行的平均值熊尉。如:
SELECT AVG(prod_price) AS avg_price
FROM Products;

返回Products表中所有產(chǎn)品的平均價(jià)格

  • COUNT()函數(shù):確定表中行的數(shù)目或符合特定條件的行的數(shù)目罐柳。COUNT()函數(shù)有兩種使用方式。第一種是使用COUNT(*)對表中行的數(shù)目進(jìn)行計(jì)數(shù)狰住,不管表列中包含的是空值(NULL)還是非空值:
SELECT COUNT(*) AS num_cust
FROM Customers;     

返回Customers表中顧客的總數(shù)

另一種方式是使用COUNT(column)對特定列中具有值的行進(jìn)行計(jì)數(shù)张吉,忽略NULL值:

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

返回Customers表中具有電子郵件地址的顧客的總數(shù)

在此例子中,使用COUNT(cust_email)對cust_email列中有值的行進(jìn)行計(jì)數(shù)催植,輸出結(jié)果為3表示5個(gè)顧客中只有3個(gè)顧客有電子郵件地址肮蛹,忽略NULL值勺择。

  • MAX()函數(shù):返回指定列中的最大值,且要求指定列名:
SELECT MAX(prod_price) AS max_price
FROM Products;

返回Products表中最貴物品的價(jià)格

注:MAX()一般用來找出最大的數(shù)值或日期值伦忠,在用于文本數(shù)據(jù)時(shí)省核,如果數(shù)據(jù)按相應(yīng)的列排序,則 MAX() 返回最后一行昆码。

  • MIN()函數(shù):與MAX()函數(shù)相反气忠,返回指定列的最小值。
  • SUM()函數(shù):返回指定列值的和(總計(jì))赋咽。例如:
SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num = 20005;

返回訂單中所有物品價(jià)錢之和

在使用以上聚集函數(shù)時(shí)旧噪,如果只想聚集不同值,可指定 DISTINCT 參數(shù)去重:

SELECT AVG(DISTINCT prod_price) AS price_avg,
        MAX(DISTINCT prod_price) AS price_max
FROM Products
WHERE vend_id = 'DLL01';

返回產(chǎn)品的平均價(jià)格及最高價(jià)格脓匿,但只考慮各個(gè)不同的價(jià)格

從本例中可以看出淘钟,使用了DISTINCT后,排除了多個(gè)具有相同的較低價(jià)格的物品之后陪毡,price_avg相對比較高米母。但是將DISTINCT用于MIN()和MAX()并不會改變一個(gè)列中的最小值和最大值。此外缤骨,DISTINCT不能用于COUNT(*)爱咬。
注:與DISTINCT參數(shù)相對的是ALL參數(shù),但ALL參數(shù)不需要指定绊起,因?yàn)檫@是默認(rèn)的精拟。

2.5 分組數(shù)據(jù)

本小節(jié)將會介紹如何使用GROUP BY 子句和 HAVING 子句分組數(shù)據(jù)。

(1)使用GROUP BY 子句創(chuàng)建分組

GROUP BY 子句能把數(shù)據(jù)分為多個(gè)邏輯組虱歪,并使用聚集函數(shù)對每個(gè)組進(jìn)行聚集計(jì)算蜂绎,以解決諸如返回每個(gè)供應(yīng)商提供的產(chǎn)品數(shù)目、返回只提供單項(xiàng)產(chǎn)品的供應(yīng)商所提供的產(chǎn)品或返回提供10個(gè)以上產(chǎn)品的供應(yīng)商等問題笋鄙∈υ妫考察以下例子:

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;       

vend_id為產(chǎn)品供應(yīng)商的ID,num_prods為計(jì)算字段(用COUNT(*)函數(shù)建立)萧落,GROUP BY 子句將根據(jù)vend_id進(jìn)行數(shù)據(jù)分組

從輸出中可以看到践美,供應(yīng)商BRS01有3個(gè)產(chǎn)品,供應(yīng)商DLL01有4個(gè)找岖,供應(yīng)商FNG01有2個(gè)產(chǎn)品陨倡。使用GROUP BY 子句時(shí),應(yīng)注意以下問題:

  • GROUP BY子句可以包含任意數(shù)目的列许布,但每一列都必須是檢索列或有效的表達(dá)式(但不能是聚集函數(shù))兴革,且不能使用別名。
  • 除聚集計(jì)算語句外,SELECT語句中的每一列都必須在GROUP BY子句中給出杂曲。
  • 如果分組列中包含具有NULL值的行庶艾,則NULL將作為一個(gè)分組返回。如果列中有多行NULL值擎勘,它們將分為一組

(2)使用HAVING 子句過濾分組

除了能用GROUP BY分組數(shù)據(jù)外咱揍,SQL還允許過濾分組,規(guī)定包括哪些分組货抄,排除哪些分組述召。例如,在上例中蟹地,我們已使用GROUP BY子句根據(jù)供應(yīng)商進(jìn)行數(shù)據(jù)分組积暖,并返回每個(gè)供應(yīng)商有多少個(gè)產(chǎn)品。現(xiàn)如果只想返回有三個(gè)產(chǎn)品以上的供應(yīng)商:

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id
HAVING COUNT(*) >= 3;

GROUP BY子句根據(jù)供應(yīng)商進(jìn)行數(shù)據(jù)分組怪与,HAVING 子句過濾并返回三個(gè)產(chǎn)品以上的供應(yīng)商

從以上例子可以看出夺刑,HAVING子句和WHERE子句功能相似,有關(guān)WHERE的所有用法(包括通配符條件和帶多個(gè)操作符的子句)都適用于HAVING分别。唯一的差別是遍愿,WHERE過濾行,而HAVING過濾分組耘斩。

當(dāng)然沼填,我們也可以在一條語句中同時(shí)使用WHERE和HAVING子句:

SELECT vend_id, COUNT(*) AS num_prods
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT(*) >= 3;

和上述例子相比,多增加了一條WHERE子句括授,過濾所有prod_price至少為4的行

加上WHERE子句坞笙,輸出結(jié)果少了一行,因?yàn)楣?yīng)商DLL01銷售4個(gè)產(chǎn)品荚虚,但價(jià)格都在4以下薛夜。

至此,回顧一下SELECT語句中子句的順序:

子查詢

本小節(jié)將會介紹什么是子查詢版述,如何使用它們梯澜。

(1)利用子查詢進(jìn)行過濾

首先考察以下問題:本教程中使用的數(shù)據(jù)庫表都是關(guān)系表,其中關(guān)于訂單的信息存儲在兩個(gè)表中:Orders表存儲訂單編號渴析、客戶ID晚伙、訂單日期;OrderItems表存儲各訂單的具體包含物品俭茧;顧客的實(shí)際信息存儲在Customers表中咆疗。

現(xiàn)在,假如需要列出訂購物品RGAN01的所有顧客恢恼,應(yīng)該怎樣檢索?下面列出了具體步驟:

  1. 檢索包含物品RGAN01的所有訂單的編號胰默;
  2. 檢索具有前一步驟列出的訂單編號的所有顧客的ID场斑;
  3. 檢索前一步驟返回的所有顧客ID的顧客信息漓踢。

上述每個(gè)步驟都可以單獨(dú)作為一個(gè)查詢來執(zhí)行÷┮可以把一條SELECT語句返回的結(jié)果用于另一條SELECT語句的WHERE子句喧半。第一條SELECT語句較為簡單:

SELECT order_num
FROM OrderItems
WHERE prod_id = 'RGAN01';

對prod_id為RGAN01的所有訂單物品,檢索其order_num列

現(xiàn)在我們知道了訂單編號青责,只需查詢與訂單20007和20008相關(guān)的顧客ID:

SELECT cust_id
FROM Orders
WHERE order_num IN (20007,20008);

檢索與訂單20007和20008相關(guān)的顧客ID

現(xiàn)在挺据,結(jié)合這兩個(gè)查詢,把第一個(gè)查詢變?yōu)樽硬樵儯?/p>

SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
                        FROM OrderItems
                        WHERE prod_id = 'RGAN01');

檢索訂單中包含物品RGAN01的顧客ID

現(xiàn)在得到了訂購物品RGAN01的所有顧客的ID脖隶,第三步是檢索這些顧客ID的顧客信息:

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN ('1000000004','1000000005');

檢索這些顧客ID為1000000004和1000000005的顧客信息

仿照上例扁耐,把其中的WHERE子句轉(zhuǎn)換為子查詢:

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                     FROM orders
                     WHERE order_num IN (SELECT order_num
                                            FROM OrderItems
                                            WHERE prod_id = 'RGAN01'));

檢索訂購物品RGAN01的所有顧客

為了執(zhí)行上述SELECT語句,MySQ實(shí)際上必須執(zhí)行三條SELECT語句产阱。最里邊的子查詢返回訂單號列表婉称,此列表用于其外面的子查詢的WHERE子句。外面的子查詢返回顧客ID列表构蹬,此顧客ID列表用于最外層查詢的WHERE子句王暗。最外層查詢返回所需的數(shù)據(jù)。

可見庄敛,在WHERE子句中使用子查詢能夠編寫出功能很強(qiáng)且很靈活的SQL語句。對于能嵌套的子查詢的數(shù)目沒有限制,不過在實(shí)際使用時(shí)由于性能的限制酿秸,不能嵌套太多的子查詢爽雄。并且使用子查詢并不總是執(zhí)行這種類型的數(shù)據(jù)檢索的最有效的方法,后續(xù)學(xué)習(xí)連接(JOIN)時(shí)我們還會遇到這個(gè)例子隐绵。

(2)作為計(jì)算字段使用子查詢

使用子查詢的另一方法是創(chuàng)建計(jì)算字段之众。假如需要顯示Customers表中每個(gè)顧客的訂單總數(shù),其中訂單與相應(yīng)的顧客ID存儲在Orders表中依许。執(zhí)行這個(gè)操作棺禾,要遵循下面的步驟:

  1. 從Customers表中檢索顧客列表;
  2. 對于檢索出的每個(gè)顧客峭跳,統(tǒng)計(jì)其在Orders表中的訂單數(shù)目膘婶。
    實(shí)現(xiàn)如下:
SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
        FROM Orders
        WHERE Orders.cust_id = Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;

SELECT COUNT(*)對表中的行進(jìn)行計(jì)數(shù),并且通過提供一條WHERE子句來過濾某個(gè)特定的顧客ID蛀醉,僅對該顧客的訂單進(jìn)行計(jì)數(shù)悬襟。最外層外層查詢對每個(gè)顧客執(zhí)行COUNT(*)。

注意到子查詢中的WHERE子句與前面使用的WHERE子句稍有不同拯刁,因?yàn)樗褂昧送耆薅忻∣rders.cust_id和Customers.cust_id)脊岳,將Orders表中的cust_id和當(dāng)前正從Customers表中檢索的cust_id進(jìn)行比較。它的語法是用一個(gè)句點(diǎn)分隔表名和列名。當(dāng)有可能混淆列名時(shí)割捅,就必須使用完全限定列名來避免歧義奶躯。

JOIN和UNION查詢

我們知道,作為關(guān)系數(shù)據(jù)庫管理系統(tǒng)(RDBMS)一部分的關(guān)系數(shù)據(jù)庫亿驾,是用相互之間存在關(guān)系的表來組織數(shù)據(jù)的嘹黔。下圖展示了本教程所使用的五張表之間的關(guān)系:


我們曾在“導(dǎo)入樣例表”小節(jié)中對這五張表的內(nèi)容及其關(guān)系的進(jìn)行了詳細(xì)地描述。雖然可以建一張更大的表來存儲所有的訂單和客戶細(xì)節(jié)莫瞬,但是使用五張表有其優(yōu)點(diǎn):

  • 第一個(gè)優(yōu)點(diǎn)是節(jié)省存儲空間儡蔓。同一供應(yīng)商生產(chǎn)的每個(gè)產(chǎn)品,其供應(yīng)商信息都是相同的疼邀,對每個(gè)產(chǎn)品重復(fù)此信息既浪費(fèi)時(shí)間又浪費(fèi)存儲空間喂江;
  • 另一個(gè)優(yōu)點(diǎn)是容易進(jìn)行變更與修正。如果供應(yīng)商信息發(fā)生變化檩小,例如供應(yīng)商遷址或電話號碼變動开呐,只需修改一次即可;
  • 第三個(gè)優(yōu)點(diǎn)是规求,數(shù)據(jù)不重復(fù)使得處理數(shù)據(jù)和生成報(bào)表更簡單筐付。如果有重復(fù)數(shù)據(jù)(即每種產(chǎn)品都存儲供應(yīng)商信息),則很難保證每次輸入該數(shù)據(jù)的方式都相同阻肿。

簡而言之瓦戚,關(guān)系表的設(shè)計(jì)就是要把信息分解成多個(gè)表,各表通過某些共同的值互相關(guān)聯(lián)丛塌,從而更有效地存儲以及更方便地處理较解。但在實(shí)際當(dāng)中,經(jīng)常有必要一次性跨多個(gè)表來訪問相關(guān)數(shù)據(jù)赴邻。為了完成這一任務(wù)印衔,SQL查詢語句使用JOIN語句來指定多個(gè)表之間的關(guān)系。

JOIN連接

(1)內(nèi)連接

連接(JOIN)使數(shù)據(jù)庫用戶能夠從2個(gè)或多個(gè)表中選擇適當(dāng)?shù)牧欣蚜病O旅娴腟QL查詢給出了一個(gè)最常見類型的連接示例:內(nèi)連接(inner join).

SELECT vend_name, prod_name, prod_price
FROM Vendors AS V
INNER JOIN Products AS P
      ON V.vend_id = P.vend_id;     

從Vendors和Products兩張表中返回各個(gè)供應(yīng)商所提供的產(chǎn)品和價(jià)格

上面語句中奸焙,INNER JOIN將Vendors和Products兩表關(guān)聯(lián),關(guān)聯(lián)需要一個(gè)或多個(gè)字段作為連接橋梁彤敛。例子中的橋梁就是vend_id与帆,我們使用on語句,將Vendors表的vend_id字段和Products的id字段匹配墨榄。

這里需要注意的是玄糟,因?yàn)樽侄慰赡苤孛孕枰褂猛耆薅员苊馄缌x袄秩。此外阵翎,表Vendors和Products分別使用AS關(guān)鍵字定義了別名V和P逢并。用別名來代替完整的表明以提高查詢的可讀性。

在“子查詢”該小節(jié)中郭卫,我們曾用以下子查詢語句來檢索訂購物品RGAN01的所有顧客:

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_id IN (SELECT cust_id
                     FROM orders
                     WHERE order_num IN (SELECT order_num
                                            FROM OrderItems
                                            WHERE prod_id = 'RGAN01'));

現(xiàn)在我們使用連接三個(gè)表來執(zhí)行相同查詢:

SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
 AND OrderItems.order_num = Orders.order_num
 AND prod_id = 'RGAN01';

檢索訂購物品RGAN01的所有顧客

這個(gè)查詢中的返回?cái)?shù)據(jù)需要使用3個(gè)表筒狠。但在這里,我們沒有在嵌套子查詢中使用它們箱沦,而是使用了兩個(gè)WHERE子句條件來連接表。第三個(gè)WHERE子句條件過濾產(chǎn)品RGAN01的數(shù)據(jù)雇庙。

(2)外連接

如上所述谓形,在所有連接類型中,Inner Join(內(nèi)連接)最常見疆前,可以縮寫成Join寒跳,找的是兩張表共同擁有的字段。但假設(shè)我們想對每個(gè)顧客下的訂單進(jìn)行計(jì)數(shù)竹椒,包括那些至今尚未下訂單的顧客童太,這就需要包含那些在相關(guān)表中沒有關(guān)聯(lián)行的行。這種連接稱為外連接(OUTER JOIN)胸完。

外連接語法與內(nèi)連接類似书释,但必須使用RIGHT或LEFT關(guān)鍵字指定包括其所有行的表(LEFT指出的是OUTER JOIN左邊的表,而RIGHT指出的是OUTER JOIN右邊的表)赊窥。下面的例子使用LEFT OUTER JOIN從FROM子句左邊的表(Customers表)中選擇所有行:

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
 ON Customers.cust_id = Orders.cust_id;

檢索包括沒有訂單顧客在內(nèi)的所有顧客

輸出結(jié)果中爆惧,cust_id為1000000002那一行的Order_num為空,就是因?yàn)閏ust_id無法匹配上锨能,返回了Null扯再。如果改成Inner Join,則不會返回整個(gè)cust_id為1000000002所在行址遇。這是Inner Join和Left Join的區(qū)別熄阻。

另外一種基本的外連接形式是RIGHT OUTER JOIN。它們之間的唯一差別是所關(guān)聯(lián)的表的順序倔约。換句話說秃殉,調(diào)整FROM或WHERE子句中表的順序,左外聯(lián)結(jié)可以轉(zhuǎn)換為右外聯(lián)結(jié)跺株。如A LEFT JOIN B 等價(jià)于 B RIGHT JOIN A因此复濒,這兩種外聯(lián)結(jié)可以互換使用,哪個(gè)方便就用哪個(gè)乒省。

UNION組合查詢

多數(shù)SQL查詢只包含從一個(gè)或多個(gè)表中返回?cái)?shù)據(jù)的單條SELECT語句巧颈。但是,SQL也允許利用UNION操作符來組合多條SELECT語句袖扛,并將結(jié)果作為一個(gè)查詢結(jié)果集返回砸泛。這些組合查詢通常稱為并(union)或復(fù)合查詢(compound query)十籍。

主要有兩種情況需要使用組合查詢:

  • 在一個(gè)查詢中從不同的表返回結(jié)構(gòu)數(shù)據(jù);
  • 對一個(gè)表執(zhí)行多個(gè)查詢唇礁,按一個(gè)查詢返回?cái)?shù)據(jù)勾栗。

使用UNION很簡單,只需在各條SELECT語句之間放上關(guān)鍵字UNION:

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

檢索Illinois盏筐、Indiana和Michigan等美國幾個(gè)州的所有顧客的報(bào)表围俘,以及包括不管位于哪個(gè)州的所有的Fun4All。

第一條SELECT把Illinois琢融、Indiana界牡、Michigan等州的縮寫傳遞給IN子句,檢索出這些州的所有行漾抬。第二條SELECT利用簡單的相等測試找出所有Fun4All宿亡。兩條SELECT語句之間用UNION關(guān)鍵字分隔。

上述UNION語句從查詢結(jié)果集中自動去除了重復(fù)的行纳令,例如Indiana州有一個(gè)Fun4All單位挽荠,所以兩條SELECT語句都返回該行。使用UNION時(shí)平绩,重復(fù)的行會被自動取消圈匆。但如果想返回所有的匹配行,可使用UNION ALL代替UNION:

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name = 'Fun4All';

檢索Illinois捏雌、Indiana和Michigan等美國幾個(gè)州的所有顧客的報(bào)表臭脓,以及包括不管位于哪個(gè)州的所有的Fun4All。不取消重復(fù)的行腹忽。

使用組合查詢時(shí)需要注意的是:

  • UNION中的每個(gè)查詢必須包含相同的列来累、表達(dá)式或聚集函數(shù)(不過,各個(gè)列不需要以相同的次序列出)窘奏;
  • 在上述簡單的例子中嘹锁,可使用WHERE子句代替UNION完成同樣的功能。但對于較復(fù)雜的過濾條件着裹,或者從多個(gè)表(而不是一個(gè)表)中檢索數(shù)據(jù)的情形领猾,使用UNION可能會使處理更簡單。且如果需要每個(gè)條件的匹配行全部出現(xiàn)(包括重復(fù)行)骇扇,就必須使用UNION ALL摔竿。

數(shù)據(jù)管理

毫無疑問,SELECT是在作數(shù)據(jù)庫內(nèi)分析時(shí)最常用的SQL語句少孝,因此继低,我們在第二部分和第三部分著重介紹了SELECT語句的相關(guān)用法。在第四部分中稍走,我們將繼續(xù)介紹其他3個(gè)常用的SQL語句袁翁。

插入數(shù)據(jù)

INSERT用來將一行或多行插入到數(shù)據(jù)庫表柴底。插入有幾種方式:

  • 插入完整的行;
  • 插入行的一部分粱胜;
  • 插入某些查詢的結(jié)果柄驻。

(1)插入完整的行
基本的INSERT語法要求指定表名和插入到新行中的值,例如:

INSERT INTO Customers
VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

這個(gè)例子將一個(gè)新顧客插入到Customers表中焙压。存儲到表中每一列的數(shù)據(jù)在VALUES子句中給出鸿脓。如果某列沒有值,如上面的cust_contact和cust_email列涯曲,則應(yīng)該使用NULL值(如果表允許對該列指定空值)答憔。

雖然這種語法很簡單,但高度依賴于表中列的定義次序掀抹,并不安全,應(yīng)該盡量避免使用心俗。更安全的寫法應(yīng)在表名后的括號里明確給出列名傲武。如下所示:

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
 VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

(2)插入部分行
如果已經(jīng)在表名后的括號里明確給出了列名,則可以只插入部分行:

INSERT INTO Customers(cust_id,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email)
 VALUES('1000000006',
       'Toy Land',
       '123 Any Street',
       'New York',
       'NY',
       '11111',
       'USA',
       NULL,
       NULL);

但需要注意省略的列必須滿足以下條件:該列定義為允許NULL值或已給出默認(rèn)值城榛。否則MySQL將產(chǎn)生錯(cuò)誤消息揪利,相應(yīng)的行不能成功插入。

(3)插入檢索出的數(shù)據(jù)
INSERT一般用來給表插入具有指定列值的行狠持。但可以利用它將SELECT語句的結(jié)果插入表中疟位,這就是所謂的INSERT SELECT。顧名思義喘垂,它是由一條INSERT語句和一條SELECT語句組成的甜刻。假如想把另一表中的顧客列合并到Customers表中。不需要每次讀取一行再將它用INSERT插入正勒,可以如下進(jìn)行::

INSERT INTO Customers(cust_id,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                   cust_state,
                      cust_zip,
                      cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM CustNew;

這個(gè)例子使用INSERT SELECT從CustNew中將所有數(shù)據(jù)導(dǎo)入Customers得院。SELECT中列出的每一列對應(yīng)于Customers表名后所跟的每一列。為簡單起見章贞,這個(gè)例子在 INSERT 和SELECT 語句中使用了相同的列名祥绞。但其實(shí)MySQL使用的是列的位置,SELECT 中的第一列將用來填充表列中指定的第一個(gè)列鸭限,第二列對應(yīng)第二列蜕径,以此類推。這對于從使用不同列名的表中導(dǎo)入數(shù)據(jù)是非常有用的败京。

更新和刪除數(shù)據(jù)

(1)更新數(shù)據(jù)

為了更新表中的數(shù)據(jù)兜喻,可使用 UPDATE 語句∩穆螅基本的UPDATE語句由三部分組成:

  • 要更新的表虹统;
  • 列名和它們的新值弓坞;
  • 確定要更新哪些行的過濾條件。

例如车荔,客戶1000000005現(xiàn)在有了電子郵件地址渡冻,因此他的記錄需要更新,語句如下:

UPDATE Customers
SET cust_email = 'kim@thetoystore.com'
WHERE cust_id = '1000000005';

UPDATE語句總是以要更新的表名開始忧便,SET命令用來將新值賦給被更新的列族吻,最后以WHERE子句結(jié)束。沒有WHERE子句珠增,DBMS將會用這個(gè)電子郵件地址更新Customers表中的所有行超歌,這不是我們希望的。

要更新多個(gè)列時(shí)蒂教,只需要使用一條SET命令巍举,每個(gè)“列=值”對之間用逗號分隔:

UPDATE Customers
SET cust_contact = 'Sam Roberts',
    cust_email = 'sam@toyland.com'
WHERE cust_id = '1000000006';

要?jiǎng)h除某個(gè)列的值時(shí),可設(shè)置它為NULL(假如表定義允許NULL值):

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '1000000005';

注:更新數(shù)據(jù)時(shí)一定要細(xì)心凝垛,不要省略WHERE子句懊悯,否則稍不注意,就會更新表中的所有行梦皮。

(2)刪除數(shù)據(jù)

使用DELETE語句可從一個(gè)表中刪除數(shù)據(jù)炭分,包括從表中刪除特定的行甚至所有行。例如剑肯,從Customers表中刪除一位顧客:

DELETE FROM Customers
WHERE cust_id = '1000000006';

DELETE FROM 指定從中刪除數(shù)據(jù)的表名捧毛。WHERE 子句過濾要?jiǎng)h除的行。如果省略 WHERE 子句让网,它將刪除表中每個(gè)客戶呀忧。

DELETE 刪除整行而不是刪除列。如需刪除指定的列溃睹,可使用上述 UPDATE 語句荐虐。

注:刪除數(shù)據(jù)時(shí)同樣需要小心,因?yàn)槿绻麍?zhí)行 DELETE 語句而不帶 WHERE 子句丸凭,表的所有數(shù)據(jù)都將被刪除且不可恢復(fù)福扬。此外,在對 UPDATE 或 DELETE 語句使用 WHERE 子句前惜犀,應(yīng)該先用 SELECT 進(jìn)行測試铛碑,以保證它過濾的是正確的記錄。

小結(jié)

SQL作為當(dāng)前使用最為廣泛的數(shù)據(jù)庫語言虽界,已經(jīng)成為數(shù)據(jù)工程師的必備技能之一汽烦。這份學(xué)習(xí)指南的第一部分介紹了數(shù)據(jù)庫、SQL和MySQL的基本概念莉御,完成了SQL環(huán)境的基本搭建撇吞。第二部分和第三部分介紹了如何使用SELECT語句進(jìn)行數(shù)據(jù)查詢俗冻,包括排序、過濾牍颈、分組迄薄、子查詢等基本方法、函數(shù)的使用以及如何使用JOIN和UNION查詢一次性跨多個(gè)表來訪問相關(guān)數(shù)據(jù)煮岁。最后一部分還介紹了數(shù)據(jù)管理的基本操作讥蔽,包括插入、更新和刪除數(shù)據(jù)等画机。

本文作者: heming
本文鏈接: SQL簡明數(shù)據(jù)分析教程
版權(quán)聲明: 本博客所有文章除特別聲明外冶伞,均采用 CC BY-NC-SA 3.0 許可協(xié)議。轉(zhuǎn)載請注明出處步氏!

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 序言:七十年代末响禽,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子荚醒,更是在濱河造成了極大的恐慌芋类,老刑警劉巖,帶你破解...
    沈念sama閱讀 206,013評論 6 481
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件腌且,死亡現(xiàn)場離奇詭異,居然都是意外死亡榛瓮,警方通過查閱死者的電腦和手機(jī)铺董,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 88,205評論 2 382
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來禀晓,“玉大人精续,你說我怎么就攤上這事〈饫粒” “怎么了重付?”我有些...
    開封第一講書人閱讀 152,370評論 0 342
  • 文/不壞的土叔 我叫張陵,是天一觀的道長凫乖。 經(jīng)常有香客問我确垫,道長,這世上最難降的妖魔是什么帽芽? 我笑而不...
    開封第一講書人閱讀 55,168評論 1 278
  • 正文 為了忘掉前任删掀,我火速辦了婚禮,結(jié)果婚禮上导街,老公的妹妹穿的比我還像新娘披泪。我一直安慰自己,他們只是感情好搬瑰,可當(dāng)我...
    茶點(diǎn)故事閱讀 64,153評論 5 371
  • 文/花漫 我一把揭開白布款票。 她就那樣靜靜地躺著控硼,像睡著了一般。 火紅的嫁衣襯著肌膚如雪艾少。 梳的紋絲不亂的頭發(fā)上卡乾,一...
    開封第一講書人閱讀 48,954評論 1 283
  • 那天,我揣著相機(jī)與錄音姆钉,去河邊找鬼说订。 笑死,一個(gè)胖子當(dāng)著我的面吹牛潮瓶,可吹牛的內(nèi)容都是我干的陶冷。 我是一名探鬼主播,決...
    沈念sama閱讀 38,271評論 3 399
  • 文/蒼蘭香墨 我猛地睜開眼毯辅,長吁一口氣:“原來是場噩夢啊……” “哼埂伦!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起思恐,我...
    開封第一講書人閱讀 36,916評論 0 259
  • 序言:老撾萬榮一對情侶失蹤沾谜,失蹤者是張志新(化名)和其女友劉穎,沒想到半個(gè)月后胀莹,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體基跑,經(jīng)...
    沈念sama閱讀 43,382評論 1 300
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 35,877評論 2 323
  • 正文 我和宋清朗相戀三年描焰,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了媳否。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 37,989評論 1 333
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡荆秦,死狀恐怖篱竭,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情步绸,我是刑警寧澤掺逼,帶...
    沈念sama閱讀 33,624評論 4 322
  • 正文 年R本政府宣布,位于F島的核電站瓤介,受9級特大地震影響吕喘,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜刑桑,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,209評論 3 307
  • 文/蒙蒙 一兽泄、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧漾月,春花似錦病梢、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,199評論 0 19
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽觅彰。三九已至,卻和暖如春钮热,著一層夾襖步出監(jiān)牢的瞬間填抬,已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 31,418評論 1 260
  • 我被黑心中介騙來泰國打工隧期, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留飒责,地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 45,401評論 2 352
  • 正文 我出身青樓仆潮,卻偏偏與公主長得像宏蛉,于是被迫代替她去往敵國和親。 傳聞我的和親對象是個(gè)殘疾皇子性置,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 42,700評論 2 345

推薦閱讀更多精彩內(nèi)容