MySQL學習筆記-子查詢和連接

来源:http://www.cnblogs.com/kurol/archive/2017/05/22/6891766.html
-Advertisement-
Play Games

MySQL學習筆記-子查詢和連接 使客戶端進入gbk編碼方式顯示： mysql> SET NAMES gbk; 1.子查詢 子查詢的定義： 子查詢（Subquery)是指出現在其他SQL語句內的SELECT子句。 例如： SELECT * FROM t1 WHERE col1 = (SELECT c ...


MySQL學習筆記-子查詢和連接   使客戶端進入gbk編碼方式顯示： 
mysql> SET NAMES gbk;

 

1.子查詢 子查詢的定義： 子查詢（Subquery)是指出現在其他SQL語句內的SELECT子句。 例如：   
 SELECT * FROM t1 WHERE col1 = (SELECT col2 FROM t2);

 

其中SELECT * FROM t1 稱為Outer Query / Outer Statement （外部查詢） SELECT col2 FROM t2 ， 被稱為SubQuery  （子查詢）   子查詢的條件： 子查詢指嵌套在查詢內部 ，且必須始終出現在圓括弧內。 子查詢可以包含多個關鍵字或條件，                 如：DISTINCT，GROUP BY，ORDER BY，LIMIT函數等。 子查詢的外部查詢可以是：SELECT , INSERT , UPDATE , SET 或 DO   子查詢的返回值： 子查詢可以返回標量、一行、一列或子查詢   2.使用比較運算符的子查詢 使用比較運算符的子查詢： =、>、<、>=、<=、<>、！=、 <=> 語法結構： operand（操作數）、comparison_operator （比較運算符）、subquery（子查詢） 用ANY  、SOME 或ALL修飾的比較運算符 operand comparison_operator ANY (subquery) operand comparison_operator SOME (subquery) operand comparison_operator ALL (subquery)   1.語法結構  操作數  比較運算符 any（子查詢）             操作數  比較運算符 some（子查詢）             操作數  比較運算符 all（子查詢） 2.適合於子查詢有多個結果 3.any 和some結果一致 all與any、some 相反   例，從tdb_goods表中 查詢平均價格，小數點保留2位，： 
mysql> SELECT ROUND(avg(goods_price),2) AS avg_price FROM tdb_goods;
+-----------+
| avg_price |
+-----------+
|   5391.30 |
+-----------+

 

查詢平均價格以上的商品： 
mysql> SELECT * FROM tdb_goods WHERE goods_price > 5391.30;

 

mysql> SELECT * FROM tdb_goods WHERE goods_price > (SELECT ROUND(AVG(goods_price),2) FROM tdb_goods);

 

  查詢超極本的列表，按價格升序排列： 
mysql> SELECT * FROM tdb_goods WHERE goods_cate = '超級本' ORDER BY goods_price ASC;

 

  • 查詢比超極本的最低價格高的列表，按價格降序的方式排列：
mysql> SELECT * FROM tdb_goods WHERE goods_price > ANY(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超級本') ORDER BY goods_price DESC;

 

  • 查詢比超極本的最高價格高的列表，按價格降序的方式排列：
mysql> SELECT * FROM tdb_goods WHERE goods_price > ALL(SELECT goods_price FROM tdb_goods WHERE goods_cate = '超級本') ORDER BY goods_price DESC;

 

  3.使用[NOT]IN/EXISTS引發的子查詢 子查詢形式： 1.使用IN的子查詢 2.使用[NOT]IN的子查詢   語法結構：operand comparison_operator [NOT] IN (subquery)   =ANY 運算符與IN 等效，!=ALL或<>ALL運算符與NOT IN等效 3.使用[NOT]EXISTS的子查詢(用的相對較少)   如果子查詢返回任何行，EXISTS將返回TRUE，否則為FALSE 例，查找不是超極本的商品列表： 
mysql> SELECT * FROM tdb_goods WHERE goods_cate NOT IN(SELECT goods_cate FROM tdb_goods WHERE goods_cate = '超級本');

 

    4.使用INSERT...SELECT插入記錄 例：在tdb_goods_cates表中插入tdb_goods表中的goods_cate分類； 
mysql> INSERT tdb_goods_cates(cate_name) SELECT goods_cate FROM tdb_goods GROUP BY goods_cate;
mysql> SELECT * FROM tdb_goods_cates;
+---------+---------------------+
| cate_id | cate_name           |
+---------+---------------------+
|       1 | 台式機              |
|       2 | 平板電腦            |
|       3 | 伺服器/工作站       |
|       4 | 游戲本              |
|       5 | 筆記本              |
|       6 | 筆記本配件          |
|       7 | 超級本              |
+---------+---------------------+

 

5.多表更新 多表更新： 
UPDATE table_references SET col_name1={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT}]...
[WHERE where_condition]

 

其中，table_references即連接的語法結構   連接類型： INNER JOIN，內連接    在MySQL中，JOIN，CROSS JOIN和INNER JOIN是等價的 LEFT [OUTER] JOIN，左外連接 RIGHT [OUTER] JOIN，右外連接   連接—語法結構 
table_reference
{[INNER | CROSS] JOIN |{LEFT|RIGHT} [OUTER] JOIN}
table_reference
ON conditional_expr

 

例，將tdb_goods中的goods_cate更新為tdb_goods_cate表中的對應cate_id。  
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_cates ON goods_cate = cate_name SET goods_cate = cate_id;

 

  6.多表更新之一步到位 CREATE...SELECT 創建數據表同時將查詢結果寫入到數據表（合併了CREATE 和 INSERT...SELECT兩個操作步驟） 
CREATE TABLE [IF NOT EXISTS] tbl_name
[(create_definition,...)]
select_statement

 

例：創建品牌分類數據表tdb_goods_brand，並將tdb_goods表中的brand_name寫入 
mysql> CREATE TABLE tdb_goods_brand(
    -> brand_id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    -> brand_name VARCHAR(40) NOT NULL
    -> )
    -> SELECT brand_name FROM tdb_goods GROUP BY brand_name;

 

  • 例，將tdb_goods中的brand_name更新為tdb_goods_brand表中的對應brand_id。錯誤寫法（程式分別不出哪個brand_name屬於哪個表）：
mysql> UPDATE tdb_goods INNER JOIN tdb_goods_brand ON brand_name = brand_name SET brand_name = brand_id;
ERROR 1052 (23000): Column 'brand_name' in field list is ambiguous

 

解決方法是給表起別名： 
mysql> UPDATE tdb_goods AS a INNER JOIN tdb_goods_brand AS b ON a.brand_name = b.brand_name SET a.brand_name = b.brand_id;

 

  • 查詢tdb_goods的數據表結構
mysql>  DESC tdb_goods;
+-------------+------------------------+------+-----+---------+----------------+
| Field       | Type                   | Null | Key | Default | Extra          |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(150)           | NO   |     | NULL    |                |
| goods_cate  | varchar(40)            | NO   |     | NULL    |                |
| brand_name  | varchar(40)            | NO   |     | NULL    |                |
| goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
| is_show     | tinyint(1)             | NO   |     | 1       |                |
| is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
+-------------+------------------------+------+-----+---------+----------------+

 

goods_cate和brand_name任然是varchar，現在我們修改欄位名goods_cate為cate_id，brand_name修改為brand_id,  為了節省空間，我們修改數據類型為smallant 
mysql> ALTER TABLE tdb_goods；
    -> CHANGE goods_cate cate_id SMALLINT UNSIGNED NOT NULL,
    -> CHANGE brand_name brand_id SMALLINT UNSIGNED NOT NULL;
mysql> DESC tdb_goods；
                                                                                    
+-------------+------------------------+------+-----+---------+----------------+
| Field       | Type                   | Null | Key | Default | Extra          |
+-------------+------------------------+------+-----+---------+----------------+
| goods_id    | smallint(5) unsigned   | NO   | PRI | NULL    | auto_increment |
| goods_name  | varchar(150)           | NO   |     | NULL    |                |
| cate_id     | smallint(5) unsigned   | NO   |     | NULL    |                |
| brand_id    | smallint(5) unsigned   | NO   |     | NULL    |                |
| goods_price | decimal(15,3) unsigned | NO   |     | 0.000   |                |
| is_show     | tinyint(1)             | NO   |     | 1       |                |
| is_saleoff  | tinyint(1)             | NO   |     | 0       |                |
+-------------+------------------------+------+-----+---------+----------------+

 

7.連接的語法結構 連接在MySQL在SELECT語句、多表更新、多表刪除語句中支持JOIN操作。 
table_reference
{[INNER|CROSS] JOIN|{LEFT|RIGHT}[OUTER]JOIN}
table_reference
ON conditional_expr

 

數據表參照 table_reference tbl_name [[AS] alias] | table_subquery [AS] alias 數據表可以使用tbl_name AS alias_name 或tbl_name alias_name 賦予別名 table_subquery可以作為子查詢使用FROM子句中， 這樣的子查詢必須賦予別名。   8.內連接INNER JOIN INNER JOIN，內連接 在MySQL中，JOIN，CROSS JOIN 和 INNER JOIN 是等價的 LEFT [OUTER] JOIN，左外連接 RIGHT [OUTER] JOIN，右外連接 連接條件： 使用ON關鍵字來設定連接條件，也可以使用WHERE來代替 通常使用ON關鍵字來設定連接條件 使用WHERE關鍵字進行結果集記錄的過濾   內連接和外連接的區別 內連接 ，顯示左表及右表符合連接條件的記錄，即交集 例如 插入幾條記錄 -- 分別在tdb_goods_cates和tdb_goods_brands表插入記錄 
INSERT tdb_goods_cates(cate_name) VALUES('路由器'),('交換機'),('網卡');
INSERT tdb_goods_brands(brand_name) VALUES('海爾'),('清華同方'),('神舟');

 

-- 在tdb_goods數據表寫入任意記 
INSERT tdb_goods(goods_name,cate_id,brand_id,goods_price) VALUES(' LaserJet Pro P1606dn 黑白激光印表機','12','4','1849');

 

例，查看符合內連接的記錄，表中只出現商品表和品牌表都有的記錄，這就是內連接（這裡商品表為左表，分類表為右表）： 
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods
-> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

    9.外連接OUTER JOIN 左外連接
  • 顯示左表的全部記錄及右表符合連接條件的記錄
  • 例，查看符合左外連接的記錄，表中出現商品表的全部和2表表都有的記錄，這就是左外連接（這裡商品表為左表，分類表為右表）
mysql> SELECT goods_id,cate_name,goods_price FROM tdb_goods 
LEFT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

   
  • 右外連接
 
  • 顯示右表的全部記錄及左表符合連接條件的記錄
  • 例，查看符合右外連接的記錄，表中出現品牌表的全部和2表表都有的記錄，這就是右外連接（這裡商品表為左表，分類表為右表）
mysql> SELECT goods_id,goods_name,cate_name,goods_price FROM tdb_goods 
RIGHT JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id;

 

    10.多表連接 /**     多表的連接跟兩張表的連接一樣   表的連接實質就是外鍵的逆向約束 */ 例，查看符合內連接的記錄，表中只出現商品表和品牌表都有的記錄，這就是內連接的多表連接（這裡商品表為左表，品牌表和分類表為右表）： 
mysql> SELECT goods_id,goods_name,cate_name,brand_name,goods_price FROM tdb_goods
    -> INNER JOIN tdb_goods_cates ON tdb_goods.cate_id = tdb_goods_cates.cate_id
    -> INNER JOIN tdb_goods_brand ON tdb_goods.brand_id = tdb_goods_brand.brand_id;

 

  11.無限級分類表設計 自身連接  
  • 例，查找所有分類及其父類：
mysql> SELECT s.type_id,s.type_name,p.type_name FROM tdb_goods_types AS s LEFT JOIN tdb_goods_types AS  p ON s.parent_id = p.type_id;
+---------+-----------------+-----------------+
| type_id | type_name       | type_name       |
+---------+-----------------+-----------------+
|       1 | 家用電器        | NULL            |
|       2 | 電腦、辦公      | NULL            |
|       3 | 大家電          | 家用電器        |
|       4 | 生活電器        | 家用電器        |
|       5 | 平板電視
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 【charger battery 充電 充電器 電池】不需 porting 就可充電的 charger ic。
    Origin : 今天同事問我一個關於配有 RT9458 charger ic 的手機的問題， 這手機可能要送到廠商那， 需要 porting charger ic， 看了一下，跟他說這個充電部份不需要 porting，也可以充電。 Reason : 這 RT9458 並沒有 CE (chargin ...
  • Android 工程集成React Native 0.44 註意點
    當前（2017年5月22日 ）React Native開發相當火爆，但是搜索下來，沒有最新版本0.44集成的教程，因此嘗試了一下如何集成到Android原生工程中去。本篇旨在記錄出現的問題以及應對方式。 一、集成的過程比較簡單，按照教程一步一步來即可： 中文版教程：http://reactnativ ...
  • 【charger battery 充電 充電器 電池】Vbat 1.8V，接充電器，GPIO_CHG_EN pin 竟然是 high！
    Schematic : Precondition : 1. Vbat 1.8V 2. Plugin adapter 3. Preloader doesn't enable GPIO_CHG_EN Origin : 做個小實驗， 手機上的假電池給 1.8V， 接上adapter， 照原理，手機應該停在 ...
  • build.gradle文件的註釋
    Gradle是一種依賴管理工具，基於Froovy語言，面向Java應用為主，它拋棄了基於xml的各種繁瑣配置，取而代之的是一種基於Groovy的內部領域特定（DSL）語言。 ...
  • 解決IIS發佈網站出現"System.Data.OracleClient 需要 Oracle 客戶端軟體 8.1.7 或更高版本"的問題
    解決方案步驟如下：第一步給網站根目錄添加如下數據許可權,如圖1所示 圖1 第二步，右鍵我的電腦=》管理=》打開組，給administrator添加NETWORK SERVICE 許可權，如圖2所示 如圖2所示 最後一步 重新啟動IIS ...
  • 檢查使用共用表空間的表
    檢查使用共用表空間的表 最近接手一臺新的資料庫機器，發現某些庫某些表沒有ibd文件只有frm文件，而ibdata1文件一共有20G+，估計是使用了共用表空間 存放在ibdata1文件里 於是寫了一個腳本，檢查實例下哪些庫哪些表使用了共用表空間，原理是如果有frm文件而沒有ibd文件就認為是使用了共用 ...
  • 我必須得告訴大家的MySQL優化原理
    本文轉載自http://www.jianshu.com/p/d7665192aaaf 說起MySQL的查詢優化，相信大家積累一堆技巧：不能使用SELECT *、不使用NULL欄位、合理創建索引、為欄位選擇合適的數據類型..... 你是否真的理解這些優化技巧？是否理解其背後的工作原理？在實際場景下性能 ...
  • Zabbix 3.2.6——Mysql多實例監控--Percona_Monitoring_Plugins自動發現
    mysql多實例監控實錄 系統環境： cat /etc/redhat-release CentOS Linux release 7.3.1611 (Core) 內核版本： uname -r 3.10.0-514.el7.x86_64 docker版本： docker -v Docker versio ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 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 ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類