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
  • 移動開發（一）：使用.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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類