技術文章 » 《MySQL必知必會》知識彙總二

《MySQL必知必會》知識彙總二

2022-12-04 21:00:42

六、用萬用字元進行過濾

本章介紹什麼是萬用字元、如何使用萬用字元以及怎樣使用LIKE操作符進行通配搜尋

LIKE操作符

  • 百分號（%）萬用字元
select prod_id,prod_name
from products
where prod_name like 'jet%';

%表示任何字元出現任意次數,也就是0個、1個或者多個字元

%可以在匹配字元的任意位置使用，例如 '%jet'、'%jet%'、'j%t'

  • 下劃線（_）萬用字元
select prod_id,prod_name
from products
where prod_name like '_ ton anvil';

_只匹配單個字元而不是多個字元

  • 使用萬用字元的技巧

萬用字元處理比其他搜尋花費時間更長，因此不建議過度使用萬用字元。

不建議將萬用字元置於搜尋模式的開始處，這樣搜尋起來是最慢的

格外注意萬用字元的位置

七、用正規表示式進行搜尋

本章將學習如何在where子句內使用正規表示式更好的控制資料過濾

  • 基本字元匹配
select prod_name
from products
where prod_name regexp '1000'
order by prod_name;

select prod_name
from products
where prod_name like'1000'
order by prod_name;

注意：LIKE與REGEXP

like不會返回1000這個值，而regexp會返回1000這個值

select prod_name
from products
where prod_name regexp '.000'
order by prod_name;

其中 . 表示匹配任意一個字元

注意：正規表示式匹配不區分大小寫，如果想要區分，使用關鍵字BINARY位於regexp之後

  • 進行OR匹配
select prod_name
from products
where prod_name regexp '1000|2000'
order by prod_name;

其中 | 表示匹配其中一個，相當於使用or操作符

  • 匹配幾個字元之一
select prod_name
from products
where prod_name regexp '[123]Ton'
order by prod_name;

其中[123]表示匹配1或2或3

注意：如果不想匹配123，可以使用[^123]來匹配字元1、2、3以外的值

  • 匹配範圍

當想要匹配[0123456789]時，可以寫成[0-9]，就表示匹配0到9中的任意一個

  • 匹配特殊字元

上述使用了 . [] | - 等進行匹配，那麼如果想要匹配這些字元呢？

select prod_name
from products
where prod_name regexp '\\.'
order by prod_name;

其中 \\. 匹配 . ，這種處理就是所謂的跳脫

注意：

為了匹配反斜槓（\）需要使用\\\

  • 匹配字元類
  • 匹配多個範例

*： 0個或者多個匹配

+： 1個或者多個匹配

?： 0個或者1個匹配

{n}: 指定數目的匹配

{n, }: 不少於指定數目的匹配

{n,m}: 匹配數目的範圍（m不超過255）

select prod_name
from products
where prod_name regexp '\\([0-9] sticks?\\)'
order by prod_name;

將匹配到 TNT (1 stick) 、 TNT(5 sticks)

  • 定位符

^ : 文字的開始

$ : 文字的結尾

[[:: 詞的開始

[[:>:]] 詞的結尾

select prod_name
from products
where prod_name regexp '^[0-9\\.]'
order by prod_name;

注意：

^不僅可以指定串的開始，還可以否定該集合

當通過^與$配合使用，可以是regexp與like達到相同的效果，因為like匹配整個串而regexp匹配子串

八、建立計算欄位

本章介紹什麼是計算欄位以及如何建立計算欄位

  • 計算欄位

與前面各章介紹的列不同，計算欄位並不實際存在於資料庫表中，而是執行時在select語句內建立的，也就是通過各種函數生產的欄位

  • 拼接欄位
select Concat(vend_name,'(',vend_country,')')
from vendors
order by vend_name;

於是顯示出了資料庫中沒有的，有Concat()函數生成的新欄位，這就是計算欄位！

Concat()函數就是將會多個欄位拼接起來

select Concat(vend_name,'(',RTrim(vend_country),')')
from vendors
order by vend_name;

還可以使用RTrim()函數進一步處理該計算欄位

注意：

RTrim()表示去掉右邊的空格

LTrim()表示去掉左邊的空格

Trim()表示去掉兩邊的空格

使用別名

通過as 在表名、列名後即可賦予別名

  • 執行計算欄位
SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num = 20005;

其中quantity*item_price AS expanded_price將計算產品的數量×產品的單價並使用別名顯示出該產品的總價

注意：

除了*乘法運算，欄位之間還可以使用+、-、/等運算

還可以通過Now()函數返回當前日期

九、使用資料處理常式

Upper()、Soundex()、文書處理常式、日期和時間處理常式、數值處理常式

十、彙總資料

聚集函數：AVG()、COUNT()、MAX()、MIN()、SUM()、

注意：

count(*)返回表中行的數目，不管表列中包含的是空值還是非空值

count(column)對指定的列進行計數並返回，會忽略null值

這兩章都是介紹一些處理欄位的函數，有相關需求時，直接去查表即可

十一、分組資料

本章將介紹如何分組，並介紹GROUP BY 和HAVING如何使用

  • 建立分組
select vend_id,count(*) as num_prods
from products
group by vend_id;

通過vend_id進行分組，並統計每組中有多少條資料

注意：

除聚集函數計算語句外，select語句中的每個列都必須在group by 子句中給出

group by 必須出現在 where 之後，order by 之前

  • 過濾分組
select cust_id,count(*) as orders
from orders
group by cust_id
having count(*)>=2;

通過having子句，可以對分組後的資料進行過濾，一般都是作用於select中的分組函數上

注意：HAVING和WHERE的差別

where在資料分組前進行過濾，having在資料分組後進行過濾

  • 分組和排序

只需要知道order by在group by 之後使用

  • select子句順序

select->from->where->group by->having->order by->limit

十二、使用子查詢

本章介紹什麼是子查詢以及如何使用它們

  • 利用子查詢進行過濾

需求：列出訂購物品TNT的所有客戶資訊

此時有張訂單資訊表、訂貨id-客戶id表、客戶資訊表

可以分三步進行查詢，先通過訂單資訊表查詢TNT商品的訂單id，在通過訂單id查詢客戶id，再通過客戶id查詢客戶資訊

將上述三個步驟合為一步：

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 = 'TNT2'));

注意：

實際在使用過程中由於效能限制，不建議巢狀太多的子查詢

  • 作為計算欄位使用子查詢

需求：需要顯示customers表中每個客戶的訂單總數

分兩步進行查詢，從customers表中檢索出客戶id，根據客戶id去訂單表中查詢訂單並統計數目

select cust_name,cust_state,(select count(*)
                            from orders
                            where orders.cust_id=customers.cust_id) as orders
from customers
order by cust_name;

其中orders是一個計算欄位，對於從customers表中查詢的每個客戶都執行依次

也就是在customers表中查詢的customers.cust_id去order表中做查詢

注意：

where子句中必須使用全限定列名