mysql 單表查詢

来源:https://www.cnblogs.com/guokaifeng/archive/2019/07/13/11179948.html
-Advertisement-
Play Games

單表查詢 [TOC] 準備數據 只是把查詢結果按照自己想要的方式返回,不對數據做修改 簡單查詢 指定欄位查詢 select id ,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee; ...


目錄

單表查詢

準備數據

  • 只是把查詢結果按照自己想要的方式返回,不對數據做修改

#準備表和記錄
company.employee  #設計表結構
    員工id      id                  int             
    姓名        emp_name            varchar
    性別        sex                 enum
    年齡        age                 int
    入職日期     hire_date           date
    崗位        post                varchar
    職位描述     post_comment        varchar
    薪水        salary              double
    辦公室       office              int
    部門編號     depart_id           int

#建表和數據準備
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一個部門一個屋子
depart_id int
);


#查看表結構
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| emp_name     | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入數據
#三個部門：教學，銷售，運營
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','雞湯導師',7300.33,401,1), #以下是教學部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龍','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是銷售部門
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('張野','male',28,'20160311','operation',10000.13,403,3), #以下是運營部門
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬銀','female',18,'20130311','operation',19000,403,3),
('程咬銅','male',18,'20150411','operation',18000,403,3),
('程咬鐵','female',18,'20140512','operation',17000,403,3)
;

#ps：如果在windows系統中，插入中文字元，select的結果為空白，可以將所有字元編碼統一設置成gbk

簡單查詢

  • 指定欄位查詢

  • select id ,emp_name,sex,age,hire_date,post,post_comment,salary,office,depart_id from employee;

  • select emp_name,salary from employee;

  • 查看所有欄位

  • select * from employee;

  • 避免重覆distinct

  • select distinct post from employee;

  • 通過四則運算查詢

  • select emp_name,salary*12 from employee;

  • select emp_name,salary*12 as Annual_salary from employee;

  • select emp_name,salary*12 Annual_salary from employee;

  • 定義顯示格式 concat() 函數用於連接字元串

  • select concat('姓名:',emp_name,'年薪:',salary*12) as Annual_salary from employee;

  • concat_ws() 第一個參數為分隔符

  • select concat_ws(':',emp_name,salary*12) as Annual_salary from employee;

  • 結合CASE語句
  • select( case when emp_name = 'jingliyang' then emp_name when emp_name = 'alex' then concat(emp_name,'_bigsb') else concat(emp_name, '棒棒噠') end ) as new_name from employee;

比較運算符

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

範圍篩選 between

  • between 80 and 100 值在80到100之間

  • 在一個模糊的範圍里 between 10000 and 20000 salary 是1w-2w之間的所有人的名字

  • select emp_name from employee where salary between 10000 and 20000;

多選 in

  • 欄位名 in (值1,值2，值3)
  • select * from employee where salary in (20000,30000,3000,19000,18000,17000);

模糊查詢 like

  • 通配符可以是%或**_** %表示任意多字元 _表示一個字元

  • select * from employee where emp_name like '程%';

​

邏輯運算符：在多個條件直接可以使用邏輯運算符 and or not

  • 邏輯運算 - 條件的拼 (與 and) (或 or) (非 not)

  • select * from employee where emp_name not in ('程咬金','程咬銀','程咬銅');

  • select * from employee where emp_name not like ('程%');

  • select emp_name,salary*12 from employee where post='operation' and emp_name like '程%';
    • 查看崗位是operation且名字是程開頭的員工姓名、年薪
  • select * from employee where salary = 20000 or salary = 30000;

  • select * from employee where emp_name = '丫丫' or emp_name = '丁丁' or emp_name = '星星' or emp_name = '格格'or salary = 20000;

身份運算 - 關於null is null /is not null

  • 查看崗位描述為NULL的員工信息

  • select * from employee where post_comment is not null;

正則匹配 regexp

  • 更加細緻的匹配的時候使用 select * from 表 where 欄位 regexp 正則表達式

  • select emp_name,salary*12 from employee where post='teacher' and emp_name regexp '^jin.*'

  • select * from employee where emp_name regexp '^j[a-z]{5}'

分組 group by

#單獨使用GROUP BY關鍵字分組
    mysql> select post from employee group by post;
    +--------------+
    | post         |
    +--------------+
    | operation    |
    | sale         |
    | teacher      |
    | 雞湯導師     |
    +--------------+
    4 rows in set (0.02 sec)
    註意：我們按照post欄位分組，那麼select查詢的欄位只能是post，想要獲取組內的其他相關信息，需要藉助函數

#GROUP BY關鍵字和GROUP_CONCAT()函數一起使用
    mysql> select post,group_concat(emp_name) from employee group by post;#按照崗位分組，並查看組內成員名
    +--------------+---------------------------------------------------------+
    | post         | group_concat(emp_name)                                  |
    +--------------+---------------------------------------------------------+
    | operation    | 程咬鐵,程咬銅,程咬銀,程咬金,張野                        |
    | sale         | 格格,星星,丁丁,丫丫,歪歪                                |
    | teacher      | 成龍,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex   |
    | 雞湯導師     | egon                                                    |
    +--------------+---------------------------------------------------------+
    4 rows in set (0.04 sec)

    mysql> select post,group_concat(emp_name) as emp_members from employee group by post;
    +--------------+---------------------------------------------------------+
    | post         | emp_members                                             |
    +--------------+---------------------------------------------------------+
    | operation    | 程咬鐵,程咬銅,程咬銀,程咬金,張野                        |
    | sale         | 格格,星星,丁丁,丫丫,歪歪                                |
    | teacher      | 成龍,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex   |
    | 雞湯導師     | egon                                                    |
    +--------------+---------------------------------------------------------+
    4 rows in set (0.00 sec)

#GROUP BY與聚合函數一起使用
    select post,count(id) as count from employee group by post;#按照崗位分組，並查看每個組有多少人
    +--------------+-------+
    | post         | count |
    +--------------+-------+
    | operation    |     5 |
    | sale         |     5 |
    | teacher      |     7 |
    | 雞湯導師     |     1 |
    +--------------+-------+
    4 rows in set (0.02 sec)

  • select * from employee group by post;

  • 分組會把在group by後面的這個欄位，也就是post欄位中的每一個不同的項都保留下來

  • 並且把值是這一項的的所有,行歸為一組,但是只顯示第一個分組匹配上值的所有數據,且一般預設倒序顯示,數值的預設從小到大排序.

聚合 (把很多行的同一個欄位進行一些統計,最終的到一個結果)

#強調：聚合函數聚合的是組的內容，若是沒有分組，則預設一組
示例：
# count(欄位/*) 統計這個欄位有多少項
    select count(id) from employee;
    +-----------+
    | count(id) |
    +-----------+
    |        18 |#結果
    +-----------+
    1 row in set (0.00 sec)
    
    select count(*) from employee where depart_id=1;
    +----------+
    | count(*) |
    +----------+
    |        8 |#結果
    +----------+
    1 row in set (0.00 sec)
    
    
# max(欄位)    統計這個欄位對應的數值的最大值(薪資)   
    select max(salary) from employee;
    +-------------+
    | max(salary) |
    +-------------+
    |  1000000.31 |#結果
    +-------------+
    1 row in set (0.03 sec)
    
# min(欄位)    統計這個欄位對應的數值的最小值(薪資)    
    select min(salary) from employee;
    +-------------+
    | min(salary) |
    +-------------+
    |     1000.37 |#結果
    +-------------+
    1 row in set (0.00 sec)

    
# avg(欄位)   統計這個欄位對應的數值的平均值(薪資)   
    select avg(salary) from employee;
    +--------------+
    | avg(salary)  |
    +--------------+
    | 64844.568889 |#結果
    +--------------+
    1 row in set (0.00 sec)
    
# sum(欄位)   統計這個欄位對應的數值的和
    mysql> select sum(salary) from employee;
    +-------------+
    | sum(salary) |
    +-------------+
    |  1167202.24 |#結果
    +-------------+
    1 row in set (0.00 sec)
    
    select sum(salary) from employee where depart_id=3;
    +-------------+
    | sum(salary) |
    +-------------+
    |    84000.13 |#結果
    +-------------+
    1 row in set (0.00 sec)

分組+聚合

一 concat()函數
1、功能：將多個字元串連接成一個字元串。
2、語法：concat(str1, str2,...)　　
返回結果為連接參數產生的字元串，如果有任何一個參數為null，則返回值為null。
3、語法：concat(str1, seperator,str2,seperator,...)
返回結果為連接參數產生的字元串並且有分隔符，如果有任何一個參數為null，則返回值為null。

二 concat_ws()函數
1、功能：和concat()一樣，將多個字元串連接成一個字元串，但是可以一次性指定分隔符（concat_ws就是concat with separator）

2、語法：concat_ws(separator, str1, str2, ...)

說明：第一個參數指定分隔符。需要註意的是分隔符不能為null，如果為null，則返回結果為null。
 
三 group_concat()函數
1、功能：將group by產生的同一個分組中的值連接起來，返回一個字元串結果。
2、語法：group_concat( [distinct] 要連接的欄位 [order by 排序欄位 asc/desc  ] [separator '分隔符'] )
說明：通過使用distinct可以排除重覆值；如果希望對結果中的值進行排序，可以使用order by子句；separator是一個字元串值，預設為一個逗號。
1. 查詢崗位名以及崗位包含的所有員工名字
mysql> select post,group_concat(emp_name) from employee group by post;
+--------------+---------------------------------------------------------+
| post         | group_concat(emp_name)                                  |
+--------------+---------------------------------------------------------+
| operation    | 程咬鐵,程咬銅,程咬銀,程咬金,張野                          |
| sale         | 格格,星星,丁丁,丫丫,歪歪                                 |
| teacher      | 成龍,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex   |
| 雞湯導師      | egon                                                    |
+--------------+---------------------------------------------------------+


2. 查詢崗位名以及各崗位內包含的員工個數
mysql> select post,count(emp_name) from employee group by post ;
+--------------+-----------------+
| post         | count(emp_name) |
+--------------+-----------------+
| operation    |               5 |
| sale         |               5 |
| teacher      |               7 |
| 雞湯導師     |               1 |
+--------------+-----------------+


3. 查詢公司內男員工和女員工的個數
select sex, count(id) from employee group by sex;
+--------+-----------+
| sex    | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+


4. 查詢崗位名以及各崗位的平均薪資
select post,avg(salary) from employee group by post;
+--------------+---------------+
| post         | avg(salary)   |
+--------------+---------------+
| operation    |  16800.026000 |
| sale         |   2600.294000 |
| teacher      | 151842.901429 |
| 雞湯導師     |   7300.330000 |
+--------------+---------------+


5. 查詢崗位名以及各崗位的最高薪資
select post,max(salary) from employee group by post;
+--------------+-------------+
| post         | max(salary) |
+--------------+-------------+
| operation    |    20000.00 |
| sale         |     4000.33 |
| teacher      |  1000000.31 |
| 雞湯導師     |     7300.33 |
+--------------+-------------+


6. 查詢崗位名以及各崗位的最低薪資
select post,min(salary) from employee group by post;
+--------------+-------------+
| post         | min(salary) |
+--------------+-------------+
| operation    |    10000.13 |
| sale         |     1000.37 |
| teacher      |     2100.00 |
| 雞湯導師     |     7300.33 |
+--------------+-------------+


7. 查詢男員工與男員工的平均薪資，女員工與女員工的平均薪資
select sex,avg(salary) from employee group by sex;
+--------+---------------+
| sex    | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+

HAVING過濾

#HAVING與WHERE不一樣的地方在於!!!!!!
1. 執行優先順序從高到低：where > group by > having 
2. Where 發生在分組group by之前，因而Where中可以有任意欄位，但是絕對不能使用聚合函數。
3. Having發生在分組group by之後，因而Having中可以使用分組的欄位，無法直接取到其他欄位,可以使用聚合函數

  • having 條件 # 過濾 組

  • select count(id) as '人數',post from employee group by post having count(*) > 3; 找出部門人數大於3的部門

  • select post '均薪資>10000部門' from employee group by post having avg(salay)>10000; 平均薪資大於10000的部門

  • select * from employee having age>18 年齡大於18歲的

  • ORDER BY 查詢排序

    • order by 某一個欄位 asc; 預設是升序asc(Ascending升序) 從小到大
    • select emp_name,age from employee order by age; 年齡按照從小到大排序

  • order by 某一個欄位 desc; 指定降序排列desc(Descending)從大到小
  • select emp_name,age from employee order by age desc; 年齡按照從大到小排序

  • select emp_name,hire_date from employee order by hire_date desc; 入職日期從早到晚排序

  • order by 第一個欄位 asc,第二個欄位 desc;指定先根據第一個欄位升序排列，在第一個欄位相同的情況下，再根據第二個欄位排列
  • select emp_name,age,salary from employee order by age,salary desc;

  • LIMIT 限制查詢的記錄數

    • 取前n個 limit n == limit 0,n
    • select emp_name,salary from employee order by salary desc limit 3; 找出薪資最高的三個人,從多到少排序

  • 分頁 limit m,n 從m+1開始取n個
  • 員工展示的網頁,18個員工,每一頁展示5個員工
  • limit n offset m == limit m,n 從m+1開始取n個
    • select * from employee order by id desc limit 0,5;
    • select * from employee order by id desc limit 5,5;
    • select * from employee order by id desc limit 10,5;
    • select * from employee order by id desc limit 15,5;


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • Linux磁碟系統——磁碟系統簡介
    Linux磁碟系統——磁碟系統簡介 摘要：本文主要學習了Linux系統中有關磁碟的一些知識。 文件系統 說明 硬碟是用來存儲數據的，可以將其想象成柜子，只不過柜子是用來存儲衣物的。新買來的硬碟，通常要對其進行分區並格式化。分區就如同把一個大櫃按照要求分割成幾個小柜子。格式化就好比在每個小柜子中打入隔 ...
  • 關於匯流排的總結
    單機上的系統匯流排 1. 匯流排(bus)是一種 通信系統 ，用於攜帶各種類型的數據， 在各組件之間傳遞數據 (可以理解為快遞員)。 2. 在大概念上分三種匯流排：系統匯流排、本地匯流排和外部匯流排。 系統匯流排：電腦內部主要設備之間的連接。 內部匯流排：直接或幾乎直接和CPU相連的設備之間的連接匯流排，比如STA ...
  • Linux配置網卡
    1、網卡設置 1.1、 橋接模式 1.1.1、編輯網卡 （1）輸入命令：vi /etc/sysconfig/network-scripts/ifcfg-eth0 （2）進入編輯模式(按i鍵) （3）編輯網卡配置 （4）按esc鍵，回到命令模式 （5）按shift+: 組合鍵，輸入wq，再按回車鍵，保 ...
  • linux常用查看命令
    一般查看 cat -n 文件名 n 代表顯示行號 分頁查看 more 文件名 more -50 文件名 -50 代表一頁顯示50行 向下翻頁 空格 向上翻頁 b more +/搜索值 文件名 查找文件指定區域 動態查看 tail -f 文件名 一般用於查看日誌文件 退出 ctrl + c ...
  • CentOS中對MySql的root用戶重置密碼
    由於一般的修改ini文件來完成無密碼登錄資料庫容易造成較大風險，所以使用修改啟動服務參數的方式來更新user表，達到無密碼登陸的目的。 1.關閉系統下運行的mysql服務 （1）殺掉進程號達到關閉目的 先使用ps -ef|grep mysql查找進程號，然後使用kill+ PID的方式殺掉進程。 （ ...
  • 關於使用巨集將csv批量轉換成xls的分享
    最近在使用遇到一個問題需要把csv格式的文件轉成xls，隨便新建一個excel，然後打開，選擇“開發工具”，找到下圖“巨集”，如果跟下圖一樣的話就需要先啟用巨集，啟用之後可以直接把下麵的代碼直接複製到代碼區，修改一個路徑就好。 以下代碼試講文件名為1.csv的文件，轉化為2.xls ok,今天的分享就到 ...
  • MySQL編譯安裝及啟動
    前言：源碼預編譯MySQL資料庫，使用時cmake 方式，MySQL資料庫官方出的資料庫編譯命令，和普通源碼安裝軟體不同 （configure）。 CMake是一個跨平臺的安裝（編譯）工具，可以用簡單的語句來描述所有平臺的安裝(編譯過程)。cmake所做的事其實就 是告訴編譯器如何去編譯鏈接源代碼。 ...
  • Redis中的Stream數據類型作為消息隊列的嘗試
    Redis的List數據類型作為消息隊列，已經比較合適了，但存在一些不足，比如只能獨立消費，訂閱發佈又無法支持數據的持久化，相對前兩者，Redis Stream作為消息隊列的使用更為有優勢。 相信球迷小伙伴們對文字直播這個東西都不陌生，時常在想，這個功能是怎麼實現的？ 具體說就是用什麼技術實現最為合 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類