Retrieving Data From a Single Table?

?

選擇語句 The SELECT Statement

?

選擇語句整體的基本用法。

?

USE sql_store;


SELECT * / 1, 2  -- *縱向篩選所有列，甚至可以是常數(shù)
FROM customers  -- 選擇表
WHERE customer_id = 1 -- 橫向篩選行
ORDER BY first_name  -- 排序


-- 單行注釋

?

選擇子句 The SELECT Clause

?

SELECT 語句可以用*號來查詢所有的列，或者我們指定想要返回的列?？捎肁S關(guān)鍵字給每列和結(jié)果設(shè)置別名，可以用DISTINCT去除重復(fù)內(nèi)容。

?

SELECT last_name, first_name, points, points+10
FROM customers

?

如果每行太長可以拆分子句，把每列放到新的一行

?

?如果想要一個清晰的描述性名稱,可以給這一列一個別名，AS語句可以命名新添加的列。如果單詞中間想用空格記得加雙引號，不加雙引號就只能像下面一樣寫成discount_factor。

?

USE store;
SELECT 
    last_name, 
    first_name, 
    points, 
    points*10+100 AS discount_factor
FROM customers

?

DISTINCT去除重復(fù)內(nèi)容，通過SELECT DISTINCT state可以在customers表格里獲取一份州的唯一列表。

?

USE store;
SELECT DISTINCT state
FROM customers

?

WHERE子句 The WHERE Clause

?

WHERE 是用于指定一個狀態(tài)，同時取出由單表中的數(shù)據(jù)，或連接多個表。

?

篩選數(shù)值大于3000的積分的顧客

SELECT*
FROM Customers
WHERE points>3000

?

篩選地點在VA的顧客

SELECT*
FROM Customers
WHERE state=''va''

?

練習(xí)：寫段查詢，得到2019年下的訂單?；赪HERE子句查詢。

?

SELECT*
FROM orders
WHERE order_date>='2019-01-01'

?

AND, OR, NOT 運算符 The AND, OR, NOT Operator

?

用邏輯運算符AND、OR、NOT對（數(shù)學(xué)和）比較運算進行組合實現(xiàn)多重條件篩選

執(zhí)行優(yōu)先級：數(shù)學(xué)→比較→邏輯

?

AND運算符表示并列關(guān)系。

?

SELECT*
FROM Customers
WHERE birth_date>'1990-01-01' AND points>1000

?

OR表示只要有一個條件是正確的，那行就會在結(jié)果集里被返回

?

SELECT*
FROM Customers
WHERE birth_date>'1990-01-01' OR (points>1000 AND
      state='VA')

?

AND和OR同時運行的話，AND優(yōu)先級在前。

?

NOT

?

SELECT*
FROM Customers
WHERE NOT(birth_date>'1990-01-01' OR points>1000)

?

?

練習(xí)

--FROM the order-items table, get the items

-- for order #6

-- where the total price is greater than 30

?

答案

SELECT *
FROM order_items
WHERE order_id=6 AND unit_price*quantity>30

?

IN運算符 The IN Operator

當有多個條件要檢查的時候，可以用IN 運算符代替它，它在WHERE子句中指定多個值。實質(zhì)是多重相等比較運算條件的簡化。

?

選出'va'、'fl'、'ga'三個州的顧客

SELECT *
FROM Customers
WHERE state='VA' OR state='GA' OR state='FL'

?

可以采用另一種簡單的方式。

SELECT *
FROM Customers
WHERE state IN('VA','GA','FL')

?

如果想知道這三個州以外的顧客，可以用NOT子句。

SELECT *
FROM Customers
WHERE state NOT IN('VA','GA','FL')

?

練習(xí)

--Return products with

-- quantity in stock equal to 49, 38, 72

選出庫存量剛好為49、38或72的產(chǎn)品

?

答案

SELECT*
FROM Products
WHERE quantity_in_stock IN(49, 38, 72)

?

BETWEEN 運算符 The BETWEEN Operator

BETWEEN 運算符用于選取介于兩個值之間的數(shù)據(jù)范圍內(nèi)的值，值可以是數(shù)字、文本或日期。

?

假設(shè)想獲得積分介于1000和3000之間的顧客

SELECT *
FROM Customers
WHERE points>=1000 AND points<=3000

?

BETWEEN 運算符的用法得出相同的結(jié)果

SELECT *
FROM Customers
WHERE points BETWEEN 1000 AND 3000

?

練習(xí)

?

--Return customers born

--between 1/1/1990 and 1/1/2000

找到出生在1990年1月1日到2000年1月1日的顧客。

?

答案

?

SELECT*
FROM Customers
WHERE birth_date BETWEEN '1990-01-01'AND '2000-01-01'

?

LIKE運算符 The LIKE Operator

?

使用LIKE運算符在與指定的模式匹配中的字段中查找值。通常在WHERE子句中使用它來搜索列中的指定模式。

?

找到姓氏首字母為b的顧客

?

SELECT*
FROM Customers
WHERE last_name LIKE 'b%'


--%any number of characters
--_single character

?

%表示b后面的字符，可以是一位，也可以是多位。

如果想找姓氏里面帶b的顧客，可以寫成%b%。

同理，如果想找姓氏里以y結(jié)尾的顧客，可以寫成%y。

_表示一個單字符，5個_表示五個字符，%可以表示任意字符。

如果想找以b開頭y結(jié)尾的五位數(shù)姓氏的顧客，可以寫成b___y

?

練習(xí)

--Get the customers whose

-- address contain TRAIL or AVENUE

-- phone numbers end with 9

?

SELECT*
FROM Customers
WHERE address LIKE '%trail%' OR
      address LIKE '%avenue%'

?

PAY ATTENTION!

• 字符加引號。
• 地址中含有trail和avenue的可能前后都含有其他字符，所以前后都需要加上%，比如：'%trail%'。
• 不要忘記第二個address LIKE。
• 為了美觀可以考慮換行。

?

SELECT*
FROM Customers
WHERE phone LIKE '%9'

?

如果想找其他所有手機號尾數(shù)不為9的顧客，可以選擇用NOT

?

SELECT*
FROM Customers
WHERE phone NOT LIKE '%9'

?

REGEXP 運算符 REGEXP Operator

?

REGEXP 是表達式regular expression的縮寫，我們可以用它搜索更復(fù)雜的模式

?

如果想找名字里含有field的顧客，可以用LIKE運算符。

?

SELECT*
FROM Customers
WHERE last_name LIKE '%field%'

?

也可以用REGEXP運算符達到同樣的效果。

?

SELECT*
FROM Customers
WHERE last_name REGEXP 'field'

?

^可以表示字符串的開頭，^field表示姓氏必須以field打頭。

?

$可以放在字符串的結(jié)尾，field$表示姓氏必須以field結(jié)尾。

?

可以用 | 搜索多個單詞。比如想查詢一些顧客，他們的姓氏包含field或者mac或者rose，可以這樣表示。

?

SELECT*
FROM Customers
WHERE last_name REGEXP 'field|mac|rose'

?

假如找姓氏里字母e前有g(shù)和i和m的顧客的話，需要寫成'[gim]e'。

?

SELECT*
FROM Customers
WHERE last_name REGEXP '[gim]e'

?

同理，假如找姓氏里字母e后有f和q和m的顧客的話，需要寫成'e[fmq]'。

?

SELECT*
FROM Customers
WHERE last_name REGEXP 'e[fmq]'

?

如果找e前面有a-h任意字母的姓氏的顧客，可以寫成'[a-h]e'。

?

SELECT*
FROM Customers
WHERE last_name REGEXP '[a-h]e'

?

總結(jié)：幾種特殊符號用法

--^ beginning
--$ end
--| logical or
--[abcd]
--[-]

?

練習(xí)

--Get the customers whose

--first names are ELKA or AMBUR

--last names end with EY or ON

--last names start with MY or contains SE

--last names contain B followed by R or U

?

答案

SELECT *
FROM Customers
WHERE last_name REGEXP 'elka|ambur'

?

SELECT *
FROM Customers
WHERE last_name REGEXP 'ey$|on$'

?

SELECT *
FROM Customers
WHERE last_name REGEXP '^my|se'

?

SELECT *
FROM Customers
WHERE last_name REGEXP 'b[ru]'

IS NULL運算符 The IS NULL Operator

IS NULL運算符用于找出空值或某些屬性缺失的記錄

?

找出電話號碼缺失的顧客。

USE sql_store;


SELECT * 
FROM customers
WHERE phone is null/is not null

注意是 IS NULL 和 IS NOT NULL 這里 NOT 不是前置于布林值，而是更符合英語語法地放在了be動詞后

?

練習(xí)

找出還沒發(fā)貨的訂單（在線商城管理員的常見查詢需求）

USE sql_store;


SELECT * 
FROM orders
WHERE shipper_id is null

回顧

3~9 節(jié)全在講WHERE子句中條件的具體寫法 :

第3節(jié)：比較運算 > < = >= <= !=第4節(jié)：邏輯運算?AND、OR、NOT5~9節(jié)：特殊的比較運算（是否符合某種條件）：IN?和?BETWEEN、LIKE?和?REGEXP、IS NULL

所以總的來說WHERE條件就是

數(shù)學(xué)運算 → 比較運算（包括特殊的比較運算）→ 邏輯運算

邏輯層次和執(zhí)行優(yōu)先級也是按照這三個的順序來的。

ORDER BY子句 The ORDER BY Clause

小結(jié)

排序語句，和?SELECT ……?很像：

可多列可以是列間的數(shù)學(xué)表達式可包括任何列，包括沒選擇的列（MySQL特性，其它DBMS可能報錯），可以是之前定義好的別名列（MySQL特性，甚至可以是用一個常數(shù)設(shè)置的列別名）任何一個排序依據(jù)列后面都可選加 DESC

注意

最好別用?ORDER BY 1, 2（表示以?SELECT ……?選中列中的第1、2列為排序依據(jù)） 這種隱性依據(jù)，因為SELECT選擇的列一變就容易出錯，還是顯性地寫出列名作為排序依據(jù)比較好

注：workbench 中扳手圖標可打開表格的設(shè)計模式，查看或修改表中各列（屬性），可以看到誰是主鍵。省略排序語句的話會默認按主鍵排序

實例

USE sql_store;


SELECT name, unit_price * 1.1 + 10 AS new_price 
FROM products
ORDER by new_price desc, product_id
-- 這兩個分別是 別名列 和 未選擇列，都用到了 MySQL 特性

練習(xí)

訂單2的商品按總價降序排列:

法1. 可以以總價的數(shù)學(xué)表達式為排序依據(jù)

SELECT *
FROM order_items 
WHERE order_id = 2
ORDER by quantity * unit_price desc
-- 列間數(shù)學(xué)表達式

法2. 或先定義總價別名，在以別名為排序依據(jù)

SELECT *, quantity * unit_price AS total_price 
FROM order_items 
WHERE order_id = 2
ORDER by total_price desc
-- 列別名

LIMIT子句 The LIMIT Clause

小結(jié)

限制返回結(jié)果的記錄數(shù)量，“前N個” 或 “跳過M個后的前N個”

實例

USE sql_store;


select * from customers
limit 3 / 300 / 6, 3

6, 3 表示跳過前6個，取第7~9個，6是偏移量，

如：網(wǎng)頁分頁 每3條記錄顯示一頁 第3頁應(yīng)該顯示的記錄就是 limit 6, 3

練習(xí)

找出積分排名前三的死忠粉

USE sql_store;


select *
from customers
order by points desc 
limit 3

回顧

SELECT 語句完結(jié)了，里面的子句順序固定要記牢，順序亂會報錯

select?from?where?+?order by?limit

縱選列，確定表，橫選行（各種條件寫法和組合要清楚熟悉），最后再進行排序和限制