mysql操作進階

来源:https://www.cnblogs.com/zyling/archive/2019/11/28/11945804.html
-Advertisement-
Play Games

# ### part1 單表查詢# sql 查詢語句的完整語法 ''' select .. from .. where .. group by .. having .. order by .. limit .. ''' # 一.where 條件的使用 """功能:對錶中的數據進行篩選過濾""" "" ...


# ### part1 單表查詢
# sql 查詢語句的完整語法
''' select .. from .. where .. group by .. having .. order by .. limit .. '''

# 一.where 條件的使用
"""功能:對錶中的數據進行篩選過濾"""

"""
語法:
1.判斷的符號:
= > < >= <= != <> 不等於
2.拼接條件的關鍵字
and or not
3.查詢的區間範圍值 between
between 小值 and 大值 [小值,大值] 查詢兩者之間這個範圍的所有數據
4.查詢具體某個值的範圍 in
in(1,-9,-10,"a") 指定範圍
5.模糊查詢 like "%" 通配符
like "%a" 匹配以a結尾的任意長度的字元串
like "a%" 匹配以a開頭的任意長度的字元串
like "%a%" 匹配含有a字母的任意長度字元串
like "_a" 個數一共2個字元,必須以a結尾,前面這個字元隨意
like "a__" 個數一共3個字元,必須以a開頭,後面這個兩字元隨意
"""
# (1) 單條件的查詢
# 查詢部門是sale的所有員工姓名:
select emp_name from employee where post = "sale";

# (2) 多條件的查詢
# 部門是teacher,收入大於10000的所有數據
select * from employee where post = "teacher" and salary > 10000;

# (3) 關鍵字between .. and
# 收入在1萬到2萬之間的所有員工姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 收入不在1萬到2萬之間的所有員工姓名和收入
select emp_name,salary from employee where salary not between 10000 and 20000;

# (4) null關鍵字 在搜索的時候,要用is進行判定,不能用=
# 查詢 post_comment 是空的NULL 所有數據
select * from employee where post_comment = NULL 數據是空,搜索不到
select * from employee where post_comment is NULL
select * from employee where post_comment is not NULL

update employee set post_comment = "" where id = 1
select * from employee where post_comment = '';

# (5) 關鍵字 in 的查詢
# 查詢收入是 3000 或 5000 或者 4000 或者 8000 所有員工姓名和收入
select emp_name,salary from employee where salary=3500 or salary=5000 or salary=8300 or salary=4000;
# 用in優化,在小括弧裡面寫上可能的值
select emp_name,salary from employee where salary in (3500,5000,8300,4000);
# 不在括弧中的值,搜索出來
select emp_name,salary from employee where salary not in (3500,5000,8300,4000);

# (6) 關鍵字 like 模糊查詢
# (1) % 通配符
select emp_name,age,post from employee where emp_name like "%on";
# (2) _ 通配符
select emp_name,age,post from employee where emp_name like "a_e_";

# (7) concat
select concat("姓名:",emp_name,"薪資:",salary) as aaa from employee;
# concat_ws(拼接的符號,參數1,參數2,參數3 ... )
select concat_ws(" : ",emp_name,salary) as bbb from employee;
# 可以在sql中使用四則運算(+ - * /)
select concat_ws(" : ",emp_name, salary * 12 ) as bbb from employee;

# 二.group by 子句 分組,分類
"""group by 對數據進行分類, by 後面接的欄位,就是select要搜索的欄位"""
select sex from employee group by sex;
select post from employee group by post;
# group_concat 按照分組形式進行欄位的拼接
select group_concat(emp_name),post from employee where id>1 group by post;

# 聚合函數
# 統計總數 count *所有
select count(*) from employee
# 統計最大值 max
select max(salary) from employee
# 統計最小值 min
select min(salary) from employee
# 統計平均值 avg
select avg(salary) from employee
# 統計總和 sum
select sum(salary) from employee

# 一般來說 使用時 分組 + 聚合函數 配合使用
# 1. 查詢部門名以及各部門的平均薪資
select post , avg(salary) from employee group by post;
# 2. 查詢部門名以及各部門的最高薪資
select post , max(salary) from employee group by post;
# 3. 查詢部門名以及各部門的最低薪資
select post , min(salary) from employee group by post;
# 4. 查詢公司內男員工和女員工的個數
select sex,count(*) from employee group by sex
# 5. 查詢部門名以及部門包含的所有員工名字
select group_concat(emp_name) , post from employee group by post
select emp_name,post from employee group by post,emp_name

# 三.having 查詢數據之後在進行過濾,一般是配合group by使用, 主要用分組後過濾
# 找出各部門的平均薪資,並且大於10000以上的所有部門
select post,avg(salary) from employee group by post having avg(salary) > 10000;
# 1.查詢各崗位內包含的員工個數小於2的崗位名,員工名,個數
select post,group_concat(emp_name),count(*) from employee group by post having count(*) < 2
# 2.查詢各崗位平均薪資小於10000的崗位名、平均工資
select post,avg(salary) from employee group by post having avg(salary) < 10000
# 3.查詢各崗位平均薪資大於10000且小於20000的崗位名、平均工資
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000

# 四.order by 排序 , 按照什麼欄位進行排序
# 預設值asc 升序排序
# 按照desc 降序排序
select * from employee order by age (預設升序)
select * from employee order by age desc (降序)

# 1. 查詢所有員工信息，先按照age升序排序，如果age相同則按照hire_date降序排序
select emp_name,sex,age,hire_date,post from employee order by age,hire_date desc
# 2. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc
# 3. 查詢各崗位平均薪資大於10000的崗位名、平均工資,結果按平均薪資升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc

# 五.limit 限制查詢的條數 (數據分頁)
limit m,n m代表從第幾條開始查詢,n代表查詢幾條 m=0 代表的是第一條
select * from employee limit 0,5 從第一條開始查,查5條
select * from employee limit 5,5 從第六條開始查,查5條
# 只查詢一條數據
select * from employee limit 1
# 想要瞬間得到數據表中,最後一條數據
select * from employee order by id desc limit 1
# 拿到最後三條數據
select * from employee order by id desc limit 3

# 六.(瞭解) 可以使用正則表達式查詢數據 (不推薦使用,不好用效率不高)
select * from employee where emp_name regexp ".*on$" # .*? 的?號不識別
select * from employee where emp_name regexp "^程";
select * from employee where emp_name regexp "^程.*金";


# ### part2 多表查詢
# 內連接:(內聯查詢 inner join ) : 兩表或者多表滿足條件的所有數據查詢出來[兩個表之間共同具有的數據]
"""
# 兩表查詢
select 欄位 from 表1 inner join 表2 on 條件
# 多表查詢
select 欄位 from 表1 inner join 表2 on 條件 inner join 表3 on 條件


"""

# 基本語法 inner join on 接的表與表之間的必要連接條件
select * from employee inner join department on employee.dep_id = department.id
# 用as 起別名 (推薦)
select * from employee as e inner join department as d on e.dep_id = d.id
# 可以省略as
select * from employee e inner join department d on e.dep_id = d.id

# where 實現的就是內聯查詢
select * from employee,department where employee.dep_id = department.id
select * from employee as e,department as d where e.dep_id = d.id

# 外連接
# (1) 左連接 (左聯查詢 left join ) : 以左表為主,右表為輔,完整查詢左表所有數據,右表沒有的數據補NULL
""" select 欄位 from 表1 left join 表2 on 條件 """
select * from employee left join department on employee.dep_id = department.id

# (2) 右連接 (右聯查詢 right join) : 以右表為主,左表為輔,完整查詢右表所有數據,左表沒有的數據補NULL
""" select 欄位 from 表1 right join 表2 on 條件 """
select * from employee right join department on employee.dep_id = department.id
# (3) 全連接 (union) 所有數據全都合併起來
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id

# ### part3 子查詢
"""
子查詢: 嵌套查詢
(1) 子查詢是查詢的語句當中又嵌套的另外一條sql語句,用括弧()抱起來,表達一個整體
(2) 一般應用在from 子句後面表達一張表,或者 where 子句後面表達一個條件
(3) 速度從快到慢 單表查詢速度最快 -> 聯表查詢 -> 子查詢
"""

# (1)找出平均年齡大於25歲以上的部門
# 普通的where 相當於內聯查詢
select
d.id,d.name
from
employee e,department d
where
e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;

# (2) inner join
select
d.id,d.name
from
employee e inner join department d on e.dep_id = d.id
group by
d.id,d.name
having
avg(e.age) > 25;

# (3) 子查詢
# 1.先選出平均年齡大於25歲的部門id
select dep_id from employee group by dep_id having avg(age) > 25;
# 2.通過部門id,找部門名字
select name from department where id in (201,202)
# 3.綜合拼接:
select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25)


# (2)查看技術部門員工姓名
# 1.普通where查詢

select
e.name
from
employee e ,department d
where
e.dep_id = d.id and d.name = "技術"


# 2.inner join 實現
select
e.name
from
employee e inner join department d on e.dep_id = d.id
where
d.name = "技術"

# 3.子查詢
# 1.找技術部門對應id
select id from department where name = "技術"

# 2.通過id找員工姓名
select name from employee where employee.dep_id = ?

# 3.綜合拼接
select name from employee where employee.dep_id = (select id from department where name = "技術")

# (3)查看哪個部門沒員工

# 聯表寫法
select
d.id,d.name
from
employee e right join department d on e.dep_id = d.id
where
e.dep_id is NULL

# 子查詢
# 1.先查詢,員工都在哪些部門
select dep_id from employee group by dep_id => (200,201,202,204)
# 2.把不在部門列表中的數據找出來
select from department where id not in (1)
# 3.綜合拼接
select id,name from department where id not in (select dep_id from employee group by dep_id)

# (4)查詢大於平均年齡的員工名與年齡
# 假設平均年齡是18歲
select name,age from employee where age > ?
# 找平均年齡
select avg(age) from employee
# 綜合拼裝
select name,age from employee where age > (select avg(age) from employee)

# (5)把大於其本部門平均年齡的員工名和姓名查出來
# employee
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id || dep_id | avg(age) |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
# department
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技術 |
| 201 | 人力資源 |
| 202 | 銷售 |
| 203 | 運營 |
+------+--------------+
# 1.先計算平均年齡
select dep_id,avg(age) from employee group by dep_id
+--------+----------+
| dep_id | avg(age) |
+--------+----------+
| 200 | 18.0000 |
| 201 | 43.0000 |
| 202 | 28.0000 |
| 204 | 18.0000 |
+--------+----------+
# 2.把子查詢查出來的數據和employee作拼接,聯合成一張更大的表,做一次單表查詢;
select
*
from
employee as t1 inner join (1) as t2 on t1.dep_id = t2.dep_id

# 3.綜合拼接
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id

# 4.把額外的比較的條件加進去
select
*
from
employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id
where
t1.age > t2.avg_age

# (6)查詢每個部門最新入職的那位員工 # 利用上一套數據表進行查詢;
# 1.找每個部門最大的入職時間
select post,max(hire_date) as max_date from employee group by post

# 2.把子查詢查出來的數據和employee聯合成一張更大的表,做一次單表查詢
select
from
employee as t1 inner join (1) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date

# 3.綜合拼接
select
t1.emp_name,t1.hire_date
from
employee as t1 inner join (select post,max(hire_date) as max_date from employee group by post) as t2 on t1.post = t2.post
where
t1.hire_date = t2.max_date


# (7)帶EXISTS關鍵字的子查詢
"""
exists 關鍵字表達存在
如果內層sql 能夠查到數據, 返回True , 外層sql執行查詢語句
如果內層sql 不能查到數據, 返回False, 外層sql不執行查詢語句
"""
select * from employee where exists (select * from employee where id = 1)




"""
子查詢總結:
子查詢可以單獨作為一個子句,也可以作為一個表或者某個欄位
一般用在from where select 子句後面
通過查詢出來的臨時表,可以跟任意的表重新拼接,組成更大的表,在通過篩選達成自己的目的
"""

 

 

 

 

 

 

 

 

 


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

-Advertisement-
Play Games
更多相關文章
  • Linux系統學習 十二、VSFTP服務—簡介與原理
    1、簡介與原理 互聯網誕生之初就存在三大服務：WWW、FTP、郵件 FTP主要針對企業級，可以設置許可權，對不同等級的資料針對不同許可權人員顯示。 但是像網盤這樣的基本沒有許可權劃分。 簡介： FTP（File Transfer Protocol）文件傳輸協議，用於網上的控制文件的雙向傳輸。 “下載”文件 ...
  • Linux系統學習 十一、DHCP伺服器—相關文件、配置文件、伺服器配置
    2、DHCP伺服器相關文件 安裝SHCP伺服器 yum install dhcp 對應的埠 埠號： ipv4 udp67、udp68（不推薦改埠） ipv6 udp546、udp547（暫時還沒生效） 2、相關文件 服務名：dhcpd （d：daeman守護進程） 主配置文件：/etc/dhc ...
  • 乙太網驅動的流程淺析(一)-Ifconfig主要流程【原創】
    乙太網驅動的流程淺析(一) Ifconfig主要流程 Author：張昺華 Email：[email protected] Time：2019年3月23日星期六 此文也在我的個人公眾號以及《Linux內核之旅》上有發表： "乙太網驅動流程淺析（一） ifconfig主要流程" 很喜歡一群人在研究技術， ...
  • 7 打開文件
    7. vi--終端中的編輯器¶ 目標¶ vi簡介 打開和新建文件 三種工作模式 常用命令 分屏命令 常用命令速查圖 7.1 vi簡介¶ 7.1.1 學習vi目的¶ 在工作中，要對伺服器上的文件進行簡單的修改，可以使用ssh遠程登錄到伺服器上，並且使用vi進行快遞的編輯即可 常見需要修改的文件包括： ...
  • 6 Ubuntu軟體安裝
    6 軟體安裝¶ 6.1 通過apt 安裝/卸載軟體¶ apt是Advanced Packaging Tool,是Linux下的一款安裝包管理工具 可以在終端中方便的安裝/卸載/更新軟體包 # 1.安裝軟體 $ sudo apt install 軟體包 # 2.卸載軟體 $ sudo apt remo ...
  • 5 其他命令-學習目標以及find命令的基本使用
    其他命令¶ 目標¶ 查找文件 find 軟鏈接 in 打包和壓縮 tar 軟體安裝 apt-get 01.查找文件¶ find命令功能非常強大，通常在特定的目錄下搜索符合條件的文件 序號命令作用 01 find [路徑] -name'*.py' 查找指定路徑下拓展名是.py的文件，包括子目錄 如果省 ...
  • [apue] 神奇的 Solaris pipe
    說到 pipe 大家可能都不陌生，經典的pipe調用配合fork進行父子進程通訊，簡直就是Unix程式的標配。 然而Solaris上的pipe卻和Solaris一樣是個奇葩(雖然Solaris前途黯淡，但是不妨礙我們從它裡面挖掘一些有價值的東西)， 有著和一般pipe諸多的不同之處，本文就來說說So ...
  • Linux系統學習 十、DHCP伺服器—介紹和原理
    介紹： DHCP服務作用（動態主機配置協議） 為大量客戶機自動分配地址、提供幾種管理 減輕管理和維護成本、提高網路配置效率 可分配的地址信息主要包括： 網卡的IP地址、子網掩碼 對應的網路地址、廣播地址 預設網關地址 DNS伺服器地址 引導文件、TFTP伺服器地址 原理： 1、客戶端尋找伺服器（發送 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類