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
  • WPF 已知問題 監聽 WMI 事件導致觸摸失效
    示例項目結構 在 Visual Studio 中創建一個 WinForms 應用程式後，項目結構如下所示： MyWinFormsApp/ │ ├───Properties/ │ └───Settings.settings │ ├───bin/ │ ├───Debug/ │ └───Release/ ...
  • [STAThread]特性
    [STAThread] 特性用於需要與 COM 組件交互的應用程式，尤其是依賴單線程模型（如 Windows Forms 應用程式）的組件。在 STA 模式下，線程擁有自己的消息迴圈，這對於處理用戶界面和某些 COM 組件是必要的。 [STAThread] static void Main(stri ...
  • UWP WinUI3 傳入 AddHandler 的 RoutedEventHandler 類型與事件所需不匹配將拋出參數異常
    在WinForm中使用全局異常捕獲處理 在WinForm應用程式中，全局異常捕獲是確保程式穩定性的關鍵。通過在Program類的Main方法中設置全局異常處理，可以有效地捕獲並處理未預見的異常，從而避免程式崩潰。 註冊全局異常事件 [STAThread] static void Main() { / ...
  • .NET 多版本 WinForm 開源控制項庫 SunnyUI
    前言 給大家推薦一款開源的 Winform 控制項庫，可以幫助我們開發更加美觀、漂亮的 WinForm 界面。 項目介紹 SunnyUI.NET 是一個基於 .NET Framework 4.0+、.NET 6、.NET 7 和 .NET 8 的 WinForm 開源控制項庫，同時也提供了工具類庫、擴展 ...
  • .net core8 使用Swagger（附當前源碼）
    說明 該文章是屬於OverallAuth2.0系列文章，每周更新一篇該系列文章（從0到1完成系統開發）。 該系統文章，我會儘量說的非常詳細，做到不管新手、老手都能看懂。 說明：OverallAuth2.0 是一個簡單、易懂、功能強大的許可權+可視化流程管理系統。 有興趣的朋友，請關註我吧(*^▽^*) ...
  • WPF 什麼時候 VisualTreeHelper.GetDescendantBounds 將返回無窮大
    一、下載安裝 1.下載git 必須先下載並安裝git，再TortoiseGit下載安裝 git安裝參考教程：https://blog.csdn.net/mukes/article/details/115693833 2.TortoiseGit下載與安裝 TortoiseGit，Git客戶端，32/6 ...
  • C# 開源教程帶你輕鬆掌握數據結構與演算法
    前言 在項目開發過程中，理解數據結構和演算法如同掌握蓋房子的秘訣。演算法不僅能幫助我們編寫高效、優質的代碼，還能解決項目中遇到的各種難題。 給大家推薦一個支持C#的開源免費、新手友好的數據結構與演算法入門教程：Hello演算法。 項目介紹 《Hello Algo》是一本開源免費、新手友好的數據結構與演算法入門 ...
  • 第22篇 生成proto文件bat腳本
    1.生成單個Proto.bat內容 @rem Copyright 2016, Google Inc. @rem All rights reserved. @rem @rem Redistribution and use in source and binary forms, with or with ...
  • 記一次 .NET某上位機視覺程式 卡死分析
    一：背景 1. 講故事 前段時間有位朋友找到我，說他的窗體程式在客戶這邊出現了卡死，讓我幫忙看下怎麼回事？dump也生成了，既然有dump了那就上 windbg 分析吧。 二：WinDbg 分析 1. 為什麼會卡死 窗體程式的卡死，入口門檻很低，後續往下分析就不一定了，不管怎麼說先用 !clrsta ...
  • 醫學DICOM文件與PACS系統進行數據傳輸
    前言 人工智慧時代，人臉識別技術已成為安全驗證、身份識別和用戶交互的關鍵工具。 給大家推薦一款.NET 開源提供了強大的人臉識別 API，工具不僅易於集成，還具備高效處理能力。 本文將介紹一款如何利用這些API，為我們的項目添加智能識別的亮點。 項目介紹 GitHub 上擁有 1.2k 星標的 C# ...
所有分類