MySQL-Select語句高級應用

来源:http://www.cnblogs.com/clsn/archive/2017/12/22/8087417.html
-Advertisement-
Play Games

Select高級應用,Information_schema說明 ...


1.1 SELECT高級應用

1.1.1 前期準備工作

本次測試使用的是world資料庫，由mysql官方提供下載地址：

　　　　https://dev.mysql.com/doc/index-other.html

world文件導入方法，官方說明：

　　　　https://dev.mysql.com/doc/world-setup/en/world-setup-installation.html

   下載sqlyog 軟體，用於之後的資料庫管理用：

　　　　http://www.webyog.com

創建用戶，能夠讓sqlyog登錄資料庫即可，註意許可權控制。

mysql> grant all on *.* to root@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)

   授權用戶後參看

mysql> select user,host from mysql.user where user like 'root';
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| root | 10.0.0.1  |
| root | 127.0.0.1 |
| root | localhost |
+------+-----------+
4 rows in set (0.00 sec)

1.1.2 select語法格式說明

mysql> help select;
Name: 'SELECT'
Description:
Syntax:
SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
      [PARTITION partition_list]
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    [PROCEDURE procedure_name(argument_list)]
    [INTO OUTFILE 'file_name'
        [CHARACTER SET charset_name]
        export_options
      | INTO DUMPFILE 'file_name'
      | INTO var_name [, var_name]]
    [FOR UPDATE | LOCK IN SHARE MODE]]

1.2 select中where子句使用

SELECT  *|{[DISTINCT]  column|select_expr [alias], ...]} 
[FROM [database.]table]
[WHERE conditions];

 where 條件的說明：

　　WHERE條件又叫做過濾條件，它從FROM子句的中間結果中去掉所有條件conditions不為TRUE（而為FALSE或者NULL）的行。

　　WHERE子句跟在FROM子句後面，不能在WHERE子句中使用列別名。

【示例一】where字句的基本使用

SELECT * FROM world.`city` WHERE CountryCode='CHN';
or
SELECT * FROM world.`city` WHERE CountryCode='chn';

   sql說明：從資料庫中查找是中國的城市。

 

註意：

　　WHERE中出現的字元串和日期字面量必須使用引號括起來

　　這裡，字元串字面量寫成大寫或小寫結果都一樣，即不區分大小寫進行查詢。

　　這和ORACLE不同，ORACLE中WHERE條件中的字面量是區分大小寫的

【示例二】where字句中的邏輯操作符

SELECT * FROM world.`city` 
WHERE CountryCode='chn' AND district = 'shanxi';

      sql說明： 從資料庫中查找是中國的並且是山西的城市

 

邏輯操作符介紹：

邏輯操作符

說明

and

邏輯與。只有當所有的子條件都為true時，and才返回true。否則返回false或null

or

邏輯或。只要有一個子條件為true，or就返回true。否則返回false或null

not

邏輯非。如果子條件為true，則返回false；如果子條件為false，則返回true

xor

邏輯異或。當一個子條件為true而另一個子條件為false時，其結果為true；

當兩個條件都為true或都為false時，結果為false。否則，結果為null

【示例三】：where字句中的範圍比較

SELECT * FROM world.`city` 
WHERE 
population BETWEEN 100000 AND 200000 ;

       sql說明： 從資料庫中查找人口數量在 100000-200000 之間的城市

 

【示例四】：where字句中的IN

SELECT * FROM city
WHERE countrycode IN ('CHN','JPN');

      sql說明： 查詢中國和日本的所有城市

 

【示例五】：where字句中的like

USE world;
SELECT * FROM city
WHERE countrycode LIKE 'ch%';

      sql說明： 從city表中找到國家是一ch開頭的。

 

like的語法：

　　like ‘匹配模式字元串’

　　實現模式匹配查詢或者模糊查詢：測試一個列值是否匹配給出的模式

　　　　在‘匹配模式字元串’中，可以有兩個具有特殊含義的通配字元：

        %：表示0個或者任意多個字元
        _：只表示一個任意字元

1.3 select中ORDER BY子句

1.3.1 order by 子句的作用

　　ORDER BY子句用來排序行

　　如果SELECT語句中沒有ORDER BY子句，那麼結果集中行的順序是不可預料的

語法：

SELECT  expr
FROM  table
[WHERE condition(s)]
[ORDER  BY  {column, expr, numeric_position} [Asc|DEsc]];

部分參數說明： 

參數

參數說明

Asc

執行升序排序。預設值

DEsc

執行降序排序

使用方法

ORDER BY子句一般在SELECT語句的最後面

1.3.2 order by 示例

【示例一】Order by基本使用

SELECT * FROM city
ORDER BY population;

      sql說明：將城市表按照人口數量升序排列

 

【示例二】多個排序條件

SELECT * FROM city
ORDER BY population,countrycode;

      sql說明： 按照人口和國家進行排序

 

【示例三】以select字句列編號排序

SELECT * FROM city
ORDER BY 5;

      sql說明：按照第5列進行排序

 

【示例四】desc實踐

SELECT * FROM city
ORDER BY 5 DESC;

      sql說明： 按照第列進行逆序排列

 

　　說明：NULL值的排序

　　　　在MySQL中，把NULL值當做一列值中的最小值對待。

　　　　因此，升序排序時，它出現在最前面。

1.4 LIMIT子句

特點說明：

MySQL特有的子句。

它是SELECT語句中的最後一個子句（在order by後面）。

它用來表示從結果集中選取最前面或最後面的幾行。

偏移量offset的最小值為0。

語法：

limit  <獲取的行數> [OFFSET <跳過的行數>]
或者 
limit [<跳過的行數>,] <獲取的行數>

查詢示例

SELECT * FROM city
ORDER BY 5 DEsc
LIMIT 4;

      sql說明： 獲取排序後的前4行

 

　　　　註：先按照人口數量進行降序排序，然後使用limit從中挑出最前面的4行。

　　　　　　如果沒有order by子句，返回的4行就是不可預料的。

1.5 多表連接查詢

1.5.1 傳統的連接寫法（使用where）

SELECT NAME,ci.countrycode ,cl.language ,ci.population
FROM  city ci , countrylanguage cl
WHERE ci.`CountryCode`=cl.countrycode;

       sql說明： city定別名為ci ，國家定別名問為cl，進行連表查詢，NAME是共同的鍵值，使用where條件進行連接。

 

　　註意：一旦給表定義了別名，那麼原始的表名就不能在出現在該語句的其它子句中了

1.5.2 NATURAL  JOIN子句

　　自動到兩張表中查找所有同名同類型的列拿來做連接列，進行相等連接

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city NATURAL  JOIN  countrylanguage
WHERE population > 1000000
ORDER BY population;

      sql說明：使用natural join 進行相等連接，兩個表，條件為人口大於1000000的，進行升序排列。

 

　　註意：在select子句只能出現一個連接列

1.5.3 使用using子句

SELECT NAME,countrycode ,LANGUAGE ,population
FROM  city JOIN  countrylanguage
USING(countrycode);

      sql說明：使用join進行兩表的來連接，using指定countrycode為關聯列。

 

1.5.4 集合操作

UNION [DISTINCT]
UNION ALL

 

語法：

SELECT ... 
UNION [ALL | DISTINCT] 
SELECT ... 
[UNION [ALL | DISTINCT] 
SELECT ...]

　　   ⛳ UNION用於把兩個或者多個select查詢的結果集合併成一個

  　　 ⛳ 進行合併的兩個查詢，其SELECT列表必須在數量和對應列的數據類型上保持一致

  　　 ⛳ 預設會去掉兩個查詢結果集中的重覆行

  　　 ⛳ 預設結果集不排序

  　    ⛳ 最終結果集的列名來自於第一個查詢的SELECT列表

1.5.5 分組操作及分組處理

　　“Group By”從字面意義上理解就是根據“By”指定的規則對數據進行分組，所謂的分組就是將一個“數據集”劃分成若幹個“小區域”，然後針對若幹個“小區域”進行數據處理。

Having與Where的區別

　　where 子句的作用是在對查詢結果進行分組前，將不符合where條件的行去掉，即在分組之前過濾數據，where條件中不能包含聚組函數，使用where條件過濾出特定的行。

　　having 子句的作用是篩選滿足條件的組，即在分組之後過濾數據，條件中經常包含聚組函數，使用having 條件過濾出特定的組，也可以使用多個分組標準進行分組。

1.5.6 【select高級應用】資料庫備份腳本拼接

SELECT CONCAT("mysqldump ","-uroot ","-p123 ",table_schema," ",table_name,">/tmp/",table_schema,"_",table_name,".sql") 
FROM information_schema.tables
WHERE table_schema='world'
INTO OUTFILE '/tmp/world_bak.sh'

       使用concat進行拼接數據備份腳本。

 

-- 顯示信息，可直接進行運算

SELECT CONCAT("132")；
SELECT CONCAT("132+123")；
SELECT CONCAT("132+123")；

-- 查看引擎是innodb的表

SELECT TABLE_NAME  FROM TABLES WHERE ENGINE='innodb';

SELECT CHARACTER_SET_NAME, COLLATION_NAME
FROM   INFORMATION_SCHEMA.COLLATIONS
WHERE  IS_DEFAULT = 'Yes';

 

-- 顯示每個庫下有多少表

SELECT TABLE_SCHEMA ,COUNT(*)
FROM information_schema.`TABLES`
GROUP BY TABLE_SCHEMA;

 

1.5.7 子查詢

子查詢定義

　　在一個表表達中可以調用另一個表表達式，這個被調用的表表達式叫做子查詢（subquery），我麽也稱作子選擇（subselect）或內嵌選擇（inner select）。子查詢的結果傳遞給調用它的表表達式繼續處理。

　　子查詢（inner  query）先執行，然後執行主查詢（outer  query）

　　子查詢按對返回結果集的調用方法，可分為：where型子查詢，from型子查詢及exists型子查詢。

使用子查詢原則

　　一個子查詢必須放在圓括弧中。

　　將子查詢放在比較條件的右邊以增加可讀性。

　　子查詢不包含 ORDER BY 子句。對一個 SELECT 語句只能用一個 ORDER BY 子句，並且如果指定了它就必須放在主 SELECT 語句的最後。

　　在子查詢中可以使用兩種比較條件：單行運算符(>, =, >=, <, <>, <=) 和多行運算符(IN, ANY, ALL)。

不相關子查詢

　　子查詢中沒有使用到外部查詢的表中的任何列。先執行子查詢，然後執行外部查詢

　　相關子查詢(correlated subquery)

　　子查詢中使用到了外部查詢的表中的任何列。先執行外部查詢，然後執行子查詢

　　以上兩種類型之下又可以分為：

　　行子查詢(row subquery)：返回的結果集是 1 行 N 列
　　列子查詢(column subquery)：返回的結果集是 N 行 1列 
　　表子查詢(table subquery)：返回的結果集是 N 行 N 列 
　　標量子查詢(scalar subquery)：返回1行1列一個值

子查詢示例

   創建數據表

 1 CREATE TABLE PLAYERS  
 2     (PLAYERNO      INTEGER      NOT NULL,  
 3     NAME           CHAR(15)     NOT NULL,  
 4     INITIALS       CHAR(3)      NOT NULL,  
 5     BIRTH_DATE     DATE                 ,  
 6     SEX            CHAR(1)      NOT NULL,  
 7     JOINED         SMALLINT     NOT NULL,  
 8     STREET         VARCHAR(30)  NOT NULL,  
 9     HOUSENO        CHAR(4)              ,  
10     POSTCODE       CHAR(6)              ,  
11     TOWN           VARCHAR(30)  NOT NULL,  
12     PHONENO        CHAR(13)             ,  
13     LEAGUENO       CHAR(4)              ,  
14     PRIMARY KEY    (PLAYERNO));  
15   
16 CREATE   TABLE PENALTIES  
17         (PAYMENTNO      INTEGER      NOT NULL,  
18          PLAYERNO       INTEGER      NOT NULL,  
19          PAYMENT_DATE   DATE         NOT NULL,  
20          AMOUNT         DECIMAL(7,2) NOT NULL,  
21          PRIMARY KEY    (PAYMENTNO)); 
22 
23 INSERT INTO PLAYERS VALUES (2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');  
24 INSERT INTO PLAYERS VALUES (6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');  
25 INSERT INTO PLAYERS VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way','39', '9758VB', 'Stratford', '070-347689', NULL);  
26 INSERT INTO PLAYERS VALUES (8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');  
27 INSERT INTO PLAYERS VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive','804', '8457DK', 'Eltham', '079-234857', '2513');  
28 INSERT INTO PLAYERS VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);  
29 INSERT INTO PLAYERS VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);  
30 INSERT INTO PLAYERS VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');  
31 INSERT INTO PLAYERS VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way','16', '4377CB', 'Stratford', '070-473458
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 43.Linux調試測試輸入思路
    當產品要發佈之前,都會進行反覆的測試輸入,比如:測試按鍵,遙控,觸摸等等. 當出現bug時,就還需要不停地找規律,修改程式,直到修複成功,會顯的非常麻煩 答: 可以通過之前在35.Linux-分析並製作環形緩衝區里學的,使用myprintf()保存到環形緩衝區. 當出錯時,直接可以通過緩衝區里的數據 ...
  • linux基本命令——mkdir，rmdir
    mkdir (選項)(參數) 創建文件夾-m：創建文件夾的同時，賦予其許可權-p：若創建目錄的上層不存在時，一併創建出來-v：顯示創建的過程創建多個目錄的時候，用空格隔開 rmdir (選項)(參數) 刪除文件夾-p：刪除一個文件夾之後，假如該文件夾上層也是空的，一併把上層文件夾刪掉-v：顯示刪除的過 ...
  • 3.瞭解linux系統以及搭建學習環境
    目錄: 1.linux的前世今生. 2.企業如何選擇linux系統? 3.如何在虛擬機上安裝linux系統?搭建學習環境. 1.linux的前世今生. 1).起源:先是貝爾實驗室的Unix系統,因為各家對於自己的硬體做了定製的Unix系統,但是硬體太貴,很多人用不起,出於教學目的,芬蘭的教授譚邦寧, ...
  • 2.伺服器的介紹
    目錄: 1.有哪些分類?市面上有哪些常見的類型. 2.伺服器的組成部分. 3.各個部分的功能1.有哪些分類?市面上有哪些常見的類型.互聯網公司伺服器品牌:DELL(大多數公司),HP,IBM(百度),浪潮,聯想,航天聯志. DELL:1U高度=4.45cm 2010年以前:1U 1850,1950. ...
  • Apache下載、安裝及配置（Windows版）
    一、Apache的下載 1、點擊鏈接http://httpd.apache.org/download.cgi，找到所需版本，如下圖位置： 2、點擊所需版本，選擇Windows文件格式，如下圖位置： 3、找到Downloading Apache for Windows欄，進入下載頁，如下圖位置： 4、 ...
  • Hbase的shell命令學習
    在學習Hbase的shell命令，之前先得瞭解如何進入hbase的shell命令行，通過執行如下簡單的命令回車後進入hbase的shell命令行界面 hbase shell 進入hbase命令行後，執行help然後回車，就能看到Hbase的shell命令行下有哪些命令，下麵是根據help反饋的幾類命 ...
  • (Les14 Manipulating Large Data Sets)[20171222]
    學習目標： -使用子查詢操縱數據 -展示多表插入特性 -使用多表插入 -無條件INSERT -旋轉INSERT -有條件 ALL INSERT -有條件 FIRST INSERT -合併表行 -跟蹤數據生命周期內的改變 使用子查詢操縱數據 -從另外一個表複製數據 -從內聯視圖取回數據 -基於另外一個 ...
  • Oracle修改欄位類型方法總結
    有一個表名為tb，欄位段名為name，數據類型nchar(20)。1、假設欄位數據為空，則不管改為什麼欄位類型，可以直接執行：alter table tb modify (name nvarchar2(20));2、假設欄位有數據，則改為nvarchar2(20)可以直接執行：alter table ...
一周排行
    -Advertisement-
    Play Games
  • C# + WPF 音頻播放器 界面優雅，體驗良好
    前言 本文介紹一款使用 C# 與 WPF 開發的音頻播放器，其界面簡潔大方，操作體驗流暢。該播放器支持多種音頻格式（如 MP4、WMA、OGG、FLAC 等），並具備標記、實時歌詞顯示等功能。 另外，還支持換膚及多語言（中英文）切換。核心音頻處理採用 FFmpeg 組件，獲得了廣泛認可，目前 Git ...
  • OAuth2.0授權-gitee授權碼模式
    OAuth2.0授權驗證-gitee授權碼模式 本文主要介紹如何筆者自己是如何使用gitee提供的OAuth2.0協議完成授權驗證並登錄到自己的系統，完整模式如圖 1、創建應用 打開gitee個人中心->第三方應用->創建應用 創建應用後在我的應用界面，查看已創建應用的Client ID和Clien ...
  • FastReport.OpenSource .Net下開源免費報表列印組件
    解決了這個問題：《winForm下，fastReport.net 從.net framework 升級到.net5遇到的錯誤“Operation is not supported on this platform.”》 本文內容轉載自：https://www.fcnsoft.com/Home/Sho ...
  • 如何在 ASP.NET Core Web API 方法執行前後 “偷偷“ 作一些 “壞“ 事？初識 ActionFilterAttribute
    國內文章 WPF 從裸 Win 32 的 WM_Pointer 消息獲取觸摸點繪製筆跡 https://www.cnblogs.com/lindexi/p/18390983 本文將告訴大家如何在 WPF 裡面，接收裸 Win 32 的 WM_Pointer 消息，從消息裡面獲取觸摸點信息，使用觸摸點 ...
  • 在 ASP.NET Core Web API 中使用異常篩選器捕獲和統一處理異常
    前言 給大家推薦一個專為新零售快消行業打造了一套高效的進銷存管理系統。 系統不僅具備強大的庫存管理功能，還集成了高性能的輕量級 POS 解決方案，確保頁面載入速度極快，提供良好的用戶體驗。 項目介紹 Dorisoy.POS 是一款基於 .NET 7 和 Angular 4 開發的新零售快消進銷存管理 ...
  • AbpHelper CLI積累與實戰分享
    ABP CLI常用的代碼分享 一、確保環境配置正確 安裝.NET CLI： ABP CLI是基於.NET Core或.NET 5/6/7等更高版本構建的，因此首先需要在你的開發環境中安裝.NET CLI。這可以通過訪問Microsoft官網下載並安裝相應版本的.NET SDK來實現。 安裝ABP ...
  • 記一次HttpClient使用問題分析
    問題 問題是這樣的：第三方的webapi，需要先調用登陸介面獲取Cookie，訪問其它介面時攜帶Cookie信息。 但使用HttpClient類調用登陸介面，返回的Headers中沒有找到Cookie信息。 分析 首先，使用Postman測試該登陸介面，正常返回Cookie信息，說明是HttpCli ...
  • .NET周刊【9月第3期 2024-09-15】
    國內文章 關於.NET在中國為什麼工資低的分析 https://www.cnblogs.com/thinkingmore/p/18406244 .NET在中國開發者的薪資偏低，主要因市場需求、技術棧選擇和企業文化等因素所致。歷史上，.NET曾因微軟的閉源策略發展受限，儘管後來推出了跨平臺的.NET ...
  • 我的網站集成ElasticSearch初體驗
    在WPF開發應用中，動畫不僅可以引起用戶的註意與興趣，而且還使軟體更加便於使用。前面幾篇文章講解了畫筆（Brush），形狀（Shape），幾何圖形（Geometry），變換（Transform）等相關內容，今天繼續講解動畫相關內容和知識點，僅供學習分享使用，如有不足之處，還請指正。 ...
  • 第23篇 委托的概述
    什麼是委托？ 委托可以說是把一個方法代入另一個方法執行，相當於指向函數的指針；事件就相當於保存委托的數組； 1.實例化委托的方式： 方式1：通過new創建實例： public delegate void ShowDelegate(); 或者 public delegate string ShowDe ...
所有分類