MySQL必知必會2

来源:https://www.cnblogs.com/train99999/archive/2019/07/24/11241504.html
-Advertisement-
Play Games

使用數據處理函數 函數 與其他大多數電腦語言一樣,SQL支持利用函數來處理數據。函數一般是在數據上執行的,他給數據的轉換和處理提供了方便,在前一章中用來去掉尾空格的RTrim()就是一個函數的例子 文本處理函數 輸入: SELECT vend_name,Upper(vend_name) AS ve ...


使用數據處理函數


函數

與其他大多數電腦語言一樣,SQL支持利用函數來處理數據。函數一般是在數據上執行的,他給數據的轉換和處理提供了方便,在前一章中用來去掉尾空格的RTrim()就是一個函數的例子

文本處理函數

輸入: SELECT vend_name,Upper(vend_name) AS vend_name_upcase FROM vendors ORDER BY vend_name;

分析: 將列 vend_name_upcase轉換為大寫

示例:

mysql> SELECT NAME,UPPER(NAME) AS name_upcase FROM account ORDER BY NAME;
+------+-------------+
| NAME | name_upcase |
+------+-------------+
| aaa  | AAA         |
| aest | AEST        |
| bbb  | BBB         |
| ccc  | CCC         |
| ccc  | CCC         |
| ddd  | DDD         |
| ddd  | DDD         |
| t57L | T57L        |
| test | TEST        |
| tsdf | TSDF        |
+------+-------------+

常用的文本處理函數

函數 說明
Left() 返回串左邊的字元
Length() 返回串的長度
Locate() 找出串的一個子串
Lower() 將串轉為小寫
LTrim() 去掉串左邊的空格
Right() 返回串右邊的字元
RTrim() 去掉串右邊的空格
Soundex() 返回串的SOUNDEX的值
SubString() 返回子串的字元
Upper() 將串轉換為大寫

日期和時間處理函數

日期和時間採用相應的數據類型和特殊的格式存儲,以便於能快速和有效的排序或過濾,並且節省物理存儲空間

常用日期和時間處理函數

函數 說明
AddDate() 增加一個日期(天、周等)
AddTime() 增加一個時間(時、分等)
CurDate() 返回當前日期
CurTime() 返回當前時間
Date() 返回日期時間的日期部分
DateDiff() 計算兩個日期之差
Date_Add() 高度靈活的日期運算函數
Date_Format() 返回一個格式化的日期或時間串
Day() 返回一個日期的天數部分
DayOfWeek() 對於一個日期、返回對應的星期幾
Hour() 返回一個時間的小時部分
Minute() 返回一個時間的分鐘部分
Month() 返回一個日期的月份部分
Now() 返回當前日期和時間
Second() 返回一個時間的秒部分
Time() 返回一個日期時間的時間部分
Year() 返回一個日期的年份部分

彙總數據


聚集函數 運行在行組上,計算和返回單個值的函數

AVG函數

輸入: SELECT AVG(prod_price) AS avg_price FROM products;

分析: 此SELECT語句返回值avg_price,它包含products表中所有產品的平均價格

只用於單個列 AVG()只能用來確定特定數值的平均值,而且列名必須作為函數參數給出

NULL值 AVG()函數忽略列值的NULL的行

COUNT函數

輸入 SELECT COUNT(*) AS num_cust FROM customers;

分析 利用COUNT對所有行計數,不管行中各列有什麼值。包含NULL值

輸入 SELECT COUNT(cust_email) AS num_cust FROM customers;

分析 使用COUNT對cust_email列中有值的行進行計數,不包含NULL值

聚集不同的值

輸入 SELECT AVG(DISTINCT prod_price) AS avg_price FROM products WHERE vend_id = 1003;

分析 排除掉相同的prod_price的值


分組數據


創建分組

輸入 SELECT vend_id,COUNT(*) AS num_prods FROM proucts GROUP BY vend_id;

分析 上面的SELECT語句指定了兩個列,vend_id包含產品供應商的ID,num_prods為計算欄位。GROUP BY 子句指示MySQL按vendid排序並分組數據。

過濾分組

輸入:

SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;

分析 HAVING子句,他過濾 count(*)>=2的那些分組

HAVING和WHERE的差別:HAVING用於分組後過濾,WHERE用於分組前過濾

分組和排序

輸入

 SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal; 

輸出

mysql>  SELECT order_num,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;
+-----------+------------+
| order_num | ordertotal |
+-----------+------------+
|     20006 |      55.00 |
|     20008 |     125.00 |
|     20005 |     149.87 |
|     20007 |    1000.00 |
+-----------+------------+

使用子查詢

利用子查詢進行過濾

輸入

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

輸出

+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

分析

為了執行上述SELECT語句;MySQL實際上必須執行3條SELECT語句,最裡邊的子查詢返回訂單號列表,此列表用於其外面的子查詢的WHERE子句。外面的子查詢返回客戶ID列表,此客戶ID列表用於最外層的WHERE子句,最外層的查詢確實返回所需的數據

連接表

外鍵

外鍵為某個表中的一列,他包含另一個表的主鍵值

創建聯結

輸入

SELECT vend_name,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id
ORDER BY vend_name,prod_name;

分析

最大的差別是在於所指定的兩個列(prod_name和prod_price)在一個表中,二另一個列(vend_name)在另一個表中

現在來看FROM子句。與以前的SELECT語句不一樣,這條語句的FROM子句列出了兩個表,分別是vendors表和products表。他們就是這條SELECT語句聯結的兩個表的名字。這兩個表用WHERE子句正確聯結,WHER子句指示MySQL匹配vendors表中的vend_id和products表中的vend_id

**內部聯結*

輸入

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id=products.vend_id;

分析 此語句中的SELECT域前面的SELECT語句相同,但FROM子句不同,這裡,兩個表之間的關係是FROM子句的組成部分,以INNER JOIN指定,在使用這種語法時,聯結條件用特定的ON子句而不是WHERE子句給出

創建高級聯結

使用表別名

輸入

SELECT cust_name,cust_contact
FROM customers AS c,orders AS o,orderitems AS oi
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id='TNT2';

自聯結

輸入

SELECT p1.prod_id,p1.prod_name
FROM products AS p1,products AS p2
WHERE p1.vend_id=p2.vend_id
AND p2.prod_id='DTNTR'

自然聯結

無論何時對錶進行聯結,應該至少有一個列出現在不止一個表中(被聯結的列)。標準的聯結返回所有數據,甚至相同的列多次出現。自然聯結排除多次出現,使每個列只返回一次

輸入

SELECT c.*,o.order_num,o.order_date,
oi.prod_id,oi.quantity,oi.item_price
FROM customers AS c,orders AS o,orderitems AS OI
WHERE c.cust_id=o.cust_id
AND oi.order_num=o.order_num
AND prod_id='FB';

外部聯結

輸入

SELECT customers.cust_id,orders.order_num
FORM customers LEFT OUTER JOIN orders
ON customers.cust_id = orders.cust_id;

組合查詢


多數SQL查詢都只包含從一個或多個表中返回數據的單條SELECT語句。MySQL也允許執行多個查詢(多條SELECT語句),並將結果作為單個查詢結果集返回

創建組合查詢

使用UNION

UNION的使用很簡單。所需做的只是給出每條SELECT語句,在各條語句之間加上關鍵字UNION

輸入

SELECT vend_id,prod_id,prod_price 
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id,prod_id,prod_price
FROM products
WHERE vend_id IN(1001,1002)

分析 UNION指示MySQL執行兩條語句,並把輸出組合成單個查詢結果集


全文本搜索


兩個最常用的引擎為MyISAM和InnoDB,前者支持全文本搜索,後者不支持

使用全文本搜索,必須索引被搜索的列,而且要隨著數據的改變不斷的重新索引

在對錶列進行適當的設計後,MySQL會自動進行所有索引和重新索引

在索引之後,SELECT可與Match()和Against()一起使用以實際執行搜索

啟用全文本搜索支持

一般在創建時啟用全文本搜索,CREATE TABLE語句接收FULLTEXT子句,他給出被索引的一個逗號分隔的列表。

創建表

CREATE TABLE productnotes(
note_id INT NOT NULL AUTO_INCREMENT,
prod_id CHAR(10) NOT NULL,
note_date DATETIME NOT NULL,
note_text TEXT NULL,
PRIMARY KEY(note_id),
FULLTEXT(note_text)
)ENGINE=MYISAM;

分析 這些列中有一個名為note_text的列,為了進行全文本搜索,MySQL根據子句FULLTEXT(note_text)的指示對他進行索引。這裡的FULLTEXT索引單個列,如果需要也可以指定多個列

在定義之後MySQL自動維護該索引,在增加、更新、或刪除行時,索引隨之自動更新

進行全文本搜索

在索引之後,使用兩個函數Match()和Against()執行全文本搜索,其中Match()指定被搜索的列,Against()指定要使用的搜索表達式

輸入

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against("rabbit");

分析 此SELECT語句檢索單個列note_text。由於WHERE子句,一個全文本搜索被執行。Match(note_text)指示MySQL針對指定的列進行搜索,Against('rabbit')指定詞rabbit作文搜索文本。

使用查詢擴展

查詢擴展用來設法放寬所返回的全文本搜索結果的範圍,考慮下麵的情況。你想找出所有提到anvils的註釋。只有一個註釋包含詞anvils,但你還想找出可能與你的搜索有關的其他行,即使他們不包含詞anvils

這也是擴展的一項任務,在使用查詢擴展時,MySQL對數據和索引進行兩遍掃描來完成搜索:

首先,進行一個基於全文本的搜索,找出與搜索條件匹配的所有行

其次,MySQL檢查這些匹配並選擇所有有用的詞

在其次,MySQL再次進行全文搜索,這次不僅使用原來的條件,而且還使用所有有用的詞

利用查詢擴展,能找出可能相關的結果,即使他們並不精確包含所查找的詞

使用查詢擴展

SELECT note_text 
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

布爾文本搜索

MySQL支持全文本搜索的另一種形式,稱為布爾方式,即使沒有全文本搜索也可以使用,但這是一種非常緩慢的操作

輸入

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);

分析 此全文本搜索檢索包含詞heavy的所有行,其中使用了關鍵字IN BOOLEAN MODE,但實際上沒有指定布爾操作符,因此,其結果與沒有指定布爾方式的結果相同

為了匹配包含heavy但不包含任意以rope開始的詞的行可以使用以下查詢
輸入

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);

全文本布爾操作符

布爾操作符 說明
+ 包含,詞必須存在
- 排除,詞必須不出現
> 包含,而且增加等級值
< 包含,且減少等級值
() 把片語成子表達式(允許這些子表達式作為一個組被包含、排除、排列等)
~ 取消一個詞的排序值
* 詞尾的通配符
"" 定義一個短語(與單個詞的列表不一樣,他匹配整個短語以便包含或排除這個短語)

插入數據


插入完整的行

輸入

INSERT INTO customers(cust_name,
                      cust_contact,
                      cust_email,
                      cust_address,
                      cust_city,
                      cust_state

)VALUES('pEP e.lAPew',
        NULL,
        NULL,
        '100 Main Street',
        'los Angeles',
        'CA'
)

插入多個行

INSERT INTO customers(
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country
)VALUES(
'Pep E. LaPew',
    '100 Main Street',
    'Los Angeles',
    'CA',
    '90046',
    'USA'
),
(
'M.Martian',
    '42 Galaxy Way',
    'New Your',
    'NY',
    '11213',
    'USA'
);

更新和刪除數據


輸入

UPDATE customers
SET cust_email='[email protected]'
WHERE cust_id=10005;

更新多個列

UPDATE customers
SET cust_name='The Fudds',
    cust_email='[email protected]'
WHERE cust_id=10005;

刪除數據

DELETE FROM customers 
WHERE cust_id = 10006;

您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 本文主要進行詳細講解CentOS7.5系統的安裝過程,以及CentOS系統初始化技術。我並不想將這篇文章變成一個教程,儘管我將詳細的進行每一步的講解,enjoy! ...
  • nano是一個字元終端的文本編輯器,有點像DOS下的editor程式。它比vi/vim要簡單得多,比較適合Linux初學者使用。某些Linux發行版的預設編輯器就是nano。 nano命令可以打開指定文件進行編輯,預設情況下它會自動斷行,即在一行中輸入過長的內容時自動拆分成幾行,但用這種方式來處理某 ...
  • 1.網路設置 裝好CentOS7後,我們一開始是上不了網的 這時候,可以輸入命令dhclient,可以自動獲取一個IP地址,再用命令ip addr查看IP 不過這時候獲取的IP是動態的,下次重啟系統後,IP地址也會變化,還是建議設置成靜態IP 2.配置YUM本地源 參考 https://www.cn ...
  • 公司的Linux伺服器都是通過一臺JumpServer跳轉的。個人使用Jumpserver(開源跳板機系統)時,有時候由於需要上傳、下載文件很不方便。而由於配置關係,一般情況無法使用SecureCRT直接通過ssh連接到伺服器。所以個人設置了/etc/ssh/sshd_config。允許我的電腦(電... ...
  • 1. sysctl -w net.ipv4.tcp_syncookies=1 #啟用使用syncookiessysctl -w net.ipv4.tcp_synack_retries=1 #降低syn重試次數sysctl -w net.ipv4.tcp_syn_retries=1 #降低syn重試次 ...
  • linux啟動順序流程圖: 啟動第一步--載入 BIOS 當你打開電腦電源,電腦會首先載入 BIOS 信息,BIOS 信息是如此的重要,以至於電腦必須在最開始就找到它。這是因為 BIOS 中包含了 CPU 的相關信息、設備啟動順序信息、硬碟信息、記憶體信息、時鐘信息、PnP 特性等等。在此之後, ...
  • 安裝依賴 解壓 安裝nginx 配置Tomcat伺服器 upstream tomcats{ server localhost:8080 weight=3; # weigh表示權重,越大訪問的機率越多 server localhost:8880 weight=6; } location / { # 這 ...
  • 恢復內容開始 1.用JDBC設置aa的balance值為1500 2.用JDBC添加姓名cc,balance為3000 3.用JDBC刪除id為3的數據 4.用JDBC創建一個student表 5.用JDBC查詢account表中所有數據 6.用JDBC查詢account表中所有數據 7.JDBC工 ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...