資料庫之MySQL查詢

来源:https://www.cnblogs.com/yifchan/archive/2019/07/06/sql-1-2.html
-Advertisement-
Play Games

資料庫之MySQL查詢，內容包括 查詢，條件查詢，排序，聚合函數，分組，分頁，鏈接查詢，自關聯，子查詢，總結。 ...


查詢

創建資料庫、數據表

-- 創建資料庫
create database python_test1 charset=utf8;

-- 使用資料庫
use python_test1;

-- students表
create table students(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    height decimal(5,2),
    gender enum('男','女','中性','保密') default '保密',
    cls_id int unsigned default 0,
    is_delete bit default 0
);

-- classes表
create table classes (
    id int unsigned auto_increment primary key not null,
    name varchar(30) not null
);

 

準備數據

-- 向students表中插入數據
insert into students values
(0,'小明',18,180.00,2,1,0),
(0,'小月月',18,180.00,2,2,1),
(0,'彭於晏',29,185.00,1,1,0),
(0,'劉德華',59,175.00,1,2,1),
(0,'黃蓉',38,160.00,2,1,0),
(0,'鳳姐',28,150.00,4,2,1),
(0,'王祖賢',18,172.00,2,1,1),
(0,'周傑倫',36,NULL,1,1,0),
(0,'程坤',27,181.00,1,2,0),
(0,'劉亦菲',25,166.00,2,2,0),
(0,'金星',33,162.00,3,3,1),
(0,'靜香',12,180.00,2,4,0),
(0,'郭靖',12,170.00,1,4,0),
(0,'周傑',34,176.00,2,5,0);

-- 向classes表中插入數據
insert into classes values (0, "python1班"), (0, "python2班");

查詢所有欄位

select * from 表名;
例：
select * from students;

查詢指定欄位

select 列1,列2,... from 表名;
例:
select name from students;

使用 as 給欄位起別名

select id as 序號, name as 名字, gender as 性別 from students;

可以通過 as 給表起別名

-- 如果是單表查詢 可以省略表明
select id, name, gender from students;

-- 表名.欄位名
select students.id,students.name,students.gender from students;

-- 可以通過 as 給表起別名 
select s.id,s.name,s.gender from students as s;

消除重覆行
在select後面列前使用distinct可以消除重覆的行

select distinct 列1,... from 表名;
例：
select distinct gender from students;

 

條件查詢

使用where子句對錶中的數據篩選，結果為true的行會出現在結果集中

語法如下：

select * from 表名 where 條件;
例：
select * from students where id=1;

where後面支持多種運算符，進行條件的處理

  • 比較運算符
  • 邏輯運算符
  • 模糊查詢
  • 範圍查詢
  • 空判斷

比較運算符

  • 等於: =
  • 大於: >
  • 大於等於: >=
  • 小於: <
  • 小於等於: <=
  • 不等於: != 或 <>

例1：查詢編號大於3的學生

select * from students where id > 3;

例2：查詢編號不大於4的學生

select * from students where id <= 4;

例3：查詢姓名不是“黃蓉”的學生

select * from students where name != '黃蓉';

例4：查詢沒被刪除的學生

select * from students where is_delete=0;

邏輯運算符

  • and
  • or
  • not

例5：查詢編號大於3的女同學

select * from students where id > 3 and gender=0;

例6：查詢編號小於4或沒被刪除的學生

select * from students where id < 4 or is_delete=0;

模糊查詢

  • like
  • %表示任意多個任意字元
  • _表示一個任意字元

例7：查詢姓黃的學生

select * from students where name like '黃%';

例8：查詢姓黃並且“名”是一個字的學生

select * from students where name like '黃_';

例9：查詢姓黃或叫靖的學生

select * from students where name like '黃%' or name like '%靖';

範圍查詢

  • in表示在一個非連續的範圍內

例10：查詢編號是1或3或8的學生

select * from students where id in(1,3,8);
  • between ... and ...表示在一個連續的範圍內

例11：查詢編號為3至8的學生

select * from students where id between 3 and 8;

例12：查詢編號是3至8的男生

select * from students where (id between 3 and 8) and gender=1;

空判斷

  • 註意：null與''"是不同的
  • 判空is null

例13：查詢沒有填寫身高的學生

select * from students where height is null;

判非空is not null
例14：查詢填寫了身高的學生

select * from students where height is not null;

例15：查詢填寫了身高的男生

select * from students where height is not null and gender=1;

優先順序

  • 優先順序由高到低的順序為：小括弧，not，比較運算符，邏輯運算符
  • and比or先運算，如果同時出現並希望先算or，需要結合()使用

 

排序

為了方便查看數據，可以對數據進行排序

語法：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]


說明

  • 將行數據按照列1進行排序，如果某些行列1的值相同時，則按照列2排序，以此類推
  • 預設按照列值從小到大排列（asc）
  • asc從小到大排列，即升序
  • desc從大到小排序，即降序

例1：查詢未刪除男生信息，按學號降序

select * from students where gender=1 and is_delete=0 order by id desc;

例2：查詢未刪除學生信息，按名稱升序

select * from students where is_delete=0 order by name;

例3：顯示所有的學生信息，先按照年齡從大-->小排序，當年齡相同時 按照身高從高-->矮排序

select * from students order by age desc,height desc;

 

聚合函數

為了快速得到統計數據，經常會用到如下5個聚合函數

總數

  • count(*)表示計算總行數，括弧中寫星與列名，結果是相同的

例1：查詢學生總數

select count(*) from students;

最大值

  • max(列)表示求此列的最大值

例2：查詢女生的編號最大值

select max(id) from students where gender=2;

最小值

  • min(列)表示求此列的最小值

例3：查詢未刪除的學生最小編號

select min(id) from students where is_delete=0;

求和

  • sum(列)表示求此列的和

例4：查詢男生的總年齡

select sum(age) from students where gender=1;

-- 平均年齡
select sum(age)/count(*) from students where gender=1;

 

平均值

  • avg(列)表示求此列的平均值

例5：查詢未刪除女生的編號平均值

select avg(id) from students where is_delete=0 and gender=2;

 

分組

  • 分組一般要和聚合結合使用才有意義；
  • having是對分組之後的數據進行計算；

group by

  1. group by的含義:將查詢結果按照1個或多個欄位進行分組，欄位值相同的為一組
  2. group by可用於單個欄位分組，也可用於多個欄位分組
select * from students;
+----+-----------+------+--------+--------+--------+-----------+
| id | name      | age  | height | gender | cls_id | is_delete |
+----+-----------+------+--------+--------+--------+-----------+
|  1 | 小明      |   18 | 180.00 | 女     |      1 |           |
|  2 | 小月月    |   18 | 180.00 | 女     |      2 |          |
|  3 | 彭於晏    |   29 | 185.00 | 男     |      1 |           |
|  4 | 劉德華    |   59 | 175.00 | 男     |      2 |          |
|  5 | 黃蓉      |   38 | 160.00 | 女     |      1 |           |
|  6 | 鳳姐      |   28 | 150.00 | 保密   |      2 |          |
|  7 | 王祖賢    |   18 | 172.00 | 女     |      1 |          |
|  8 | 周傑倫    |   36 |   NULL | 男     |      1 |           |
|  9 | 程坤      |   27 | 181.00 | 男     |      2 |           |
| 10 | 劉亦菲    |   25 | 166.00 | 女     |      2 |           |
| 11 | 金星      |   33 | 162.00 | 中性   |      3 |          |
| 12 | 靜香      |   12 | 180.00 | 女     |      4 |           |
| 13 | 周傑      |   34 | 176.00 | 女     |      5 |           |
| 14 | 郭靖      |   12 | 170.00 | 男     |      4 |           |
+----+-----------+------+--------+--------+--------+-----------+

select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+
group by示例

根據gender欄位來分組，gender欄位的全部值有4個'男','女','中性','保密'，所以分為了4組 當group by單獨使用時，只顯示出每組的第一條記錄, 所以group by單獨使用時的實際意義不大

group by + group_concat()

  1. group_concat(欄位名)可以作為一個輸出欄位來使用，
  2. 表示分組之後，根據分組結果，使用group_concat()來放置每一組的某欄位的值的集合
select gender from students group by gender;
+--------+
| gender |
+--------+
| 男     |
| 女     |
| 中性   |
| 保密   |
+--------+

select gender,group_concat(name) from students group by gender;
+--------+-----------------------------------------------------------+
| gender | group_concat(name)                                        |
+--------+-----------------------------------------------------------+
| 男     | 彭於晏,劉德華,周傑倫,程坤,郭靖                                 |
| 女     | 小明,小月月,黃蓉,王祖賢,劉亦菲,靜香,周傑                        |
| 中性   | 金星                                                       |
| 保密   | 鳳姐                                                       |
+--------+-----------------------------------------------------------+


select gender,group_concat(id) from students group by gender;
+--------+------------------+
| gender | group_concat(id) |
+--------+------------------+
| 男     | 3,4,8,9,14       |
| 女     | 1,2,5,7,10,12,13 |
| 中性   | 11               |
| 保密   | 6                |
+--------+------------------+
group by + group_concat()示例

group by + 集合函數

  1. 通過group_concat()的啟發，我們既然可以統計出每個分組的某欄位的值的集合，那麼我們也可以通過集合函數來對這個值的集合做一些操作
select gender,group_concat(age) from students group by gender;
+--------+----------------------+
| gender | group_concat(age)    |
+--------+----------------------+
| 男     | 29,59,36,27,12       |
| 女     | 18,18,38,18,25,12,34 |
| 中性   | 33                   |
| 保密   | 28                   |
+--------+----------------------+


分別統計性別為男/女的人年齡平均值
select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| 男     |  32.6000 |
| 女     |  23.2857 |
| 中性   |  33.0000 |
| 保密   |  28.0000 |
+--------+----------+

分別統計性別為男/女的人的個數
select gender,count(*) from students group by gender;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
+--------+----------+
group by + 集合函數示例

group by + having

  1. having 條件表達式：用來分組查詢後指定一些條件來輸出查詢結果
  2. having作用和where一樣，但having只能用於group by
select gender,count(*) from students group by gender having count(*)>2;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
+--------+----------+

group by + with rollup

  1. with rollup的作用是：在最後新增一行，來記錄當前列里所有記錄的總和
select gender,count(*) from students group by gender with rollup;
+--------+----------+
| gender | count(*) |
+--------+----------+
| 男     |        5 |
| 女     |        7 |
| 中性   |        1 |
| 保密   |        1 |
| NULL   |       14 |
+--------+----------+


select gender,group_concat(age) from students group by gender with rollup;
+--------+-------------------------------------------+
| gender | group_concat(age)                         |
+--------+-------------------------------------------+
| 男     | 29,59,36,27,12                            |
| 女     | 18,18,38,18,25,12,34                      |
| 中性   | 33                                        |
| 保密   | 28                                        |
| NULL   | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |
+--------+-------------------------------------------+
group by + with rollup示例

 

分頁

當數據量過大時，在一頁中查看數據是一件非常麻煩的事情

語法

select * from 表名 limit start,count

說明

  • 從start開始，獲取count條數據

例1：查詢前3行男生信息

select * from students where gender=1 limit 0,3;

示例：分頁

  • 已知：每頁顯示m條數據，當前顯示第n頁
  • 求總頁數：此段邏輯後面會在python中實現
    • 查詢總條數p1
    • 使用p1除以m得到p2
    • 如果整除則p2為總數頁
    • 如果不整除則p2+1為總頁數
  • 求第n頁的數據
select * from students where is_delete=0 limit (n-1)*m,m

 

連接查詢

當查詢結果的列來源於多張表時，需要將多張表連接成一個大的數據集，再選擇合適的列返回

mysql支持三種類型的連接查詢，分別為：

  • 內連接查詢：查詢的結果為兩個表匹配到的數據
  • 右連接查詢：查詢的結果為兩個表匹配到的數據，右表特有的數據，對於左表中不存在的數據使用null填充
  • 左連接查詢：查詢的結果為兩個表匹配到的數據，左表特有的數據，對於右表中不存在的數據使用null填充

語法

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

 

例1：使用內連接查詢班級表與學生表

select * from students inner join classes on students.cls_id = classes.id;

例2：使用左連接查詢班級表與學生表

--此處使用了as為表起別名，目的是編寫簡單
select * from students as s left join classes as c on s.cls_id = c.id;

例3：使用右連接查詢班級表與學生表

select * from students as s right join classes as c on s.cls_id =
您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • IIS下配置Php+Mysql+zend的圖文教程(完整版)
    網上有很多關於PHP在IIS下配置的教程,但都是一些很理性化的東西,我從裡面整理出來這個教程 發出來為了方便參考,,有什麼問題也可以大家一起交流,,如果有什麼不對的地方,請指正.. 下麵的教程都是在windows 2000下實現的,其他系統請自己參照修改, 操作系統目錄:c:\winnt PHP預設 ...
  • Linux(二)各種實用命令
    繼續Linux命令學習，沒有什麼捷徑，每個命令都去敲幾遍就熟悉了，第二篇學習的是一些比較實用類的命令，主要是從開發的角度進行學習，並不深入，話不多說，開始！ 一、系統管理類 1.1 stat --stat 顯示指定文件的相關信息,比ls命令顯示內容更多 1.2 who --顯示線上登錄用戶 1.3 ...
  • SQL Server溫故系列(1)：SQL 數據操作 CRUD 之增刪改合
    《SQL Server溫故系列》，sql，crud。毋庸置疑，開發者最常用的資料庫技術就是 SQL 了，即便是 ORM 大行其道的今天也常常需要寫 SQL 語句。而 SQL 語句中最常用的就是增刪改查了，本系列就先對增刪改查語句來個系統的回顧吧！ ...
  • SQL Server溫故系列(0)：導航目錄
    《SQL Server溫故系列》，sqlserver教程，tsql教程，sql教程，SQL Server 系列博客導航目錄 ...
  • 資料庫之MySQL高級
    python用於操作數據的pymysql模塊，及相關的視圖，觸發器，函數，存儲過程，事務，SQL註入等相關拓展。 ...
  • Oracle資料庫---異常處理
    Oracle異常處理在PL/SQL語句書寫時，需要處理的異常-- 不做異常處理時DECLARE v_name emp.ename%TYPE; v_sal emp.sal%TYPE;BEGIN SELECT ename,sal INTO v_name,v_sal FROM emp WHERE empn ...
  • 資料庫之MySQL與Python交互
    資料庫之MySQL與Python交互，內容包括 準備數據，SQL演練，資料庫的設計，Python 中操作 MySQL 步驟，pymysql增刪改查，參數化。 ...
  • 資料庫的存儲引擎
    [TOC] 資料庫的存儲引擎 什麼是存儲引擎? MySQL中的數據用各種不同的技術存儲在文件（或者記憶體）中。這些技術中的每一種技術都使用不同的存儲機制、 索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。通過選擇不同的技術，你能夠獲得額外的速度或者功能， 從而改善你的應用的整體功能。在MySQL ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類