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
  • Dapr Outbox 執行流程
    Dapr Outbox 是1.12中的功能。 本文只介紹Dapr Outbox 執行流程，Dapr Outbox基本用法請閱讀官方文檔 。本文中appID=order-processor，topic=orders 本文前提知識：熟悉Dapr狀態管理、Dapr發佈訂閱和Outbox 模式。 Outbo ...
  • Advanced .Net Debugging 7：托管堆與垃圾收集
    引言 在前幾章我們深度講解了單元測試和集成測試的基礎知識，這一章我們來講解一下代碼覆蓋率,代碼覆蓋率是單元測試運行的度量值,覆蓋率通常以百分比表示，用於衡量代碼被測試覆蓋的程度，幫助開發人員評估測試用例的質量和代碼的健壯性。常見的覆蓋率包括語句覆蓋率（Line Coverage）、分支覆蓋率（Bra ...
  • C#S7.NET實現西門子PLCDB塊數據採集的完整步驟
    前言 本文介紹瞭如何使用S7.NET庫實現對西門子PLC DB塊數據的讀寫，記錄了使用電腦模擬，模擬PLC，自至完成測試的詳細流程，並重點介紹了在這個過程中的易錯點，供參考。 用到的軟體： 1.Windows環境下鏈路層網路訪問的行業標準工具（WinPcap_4_1_3.exe）下載鏈接：http ...
  • WPF控制項：密碼框綁定MVVM
    從依賴倒置原則（Dependency Inversion Principle, DIP）到控制反轉（Inversion of Control, IoC）再到依賴註入（Dependency Injection, DI）的演進過程，我們可以理解為一種逐步抽象和解耦的設計思想。這種思想在C#等面向對象的編 ...
  • Python中的私有屬性與私有方法
    關於Python中的私有屬性和私有方法 Python對於類的成員沒有嚴格的訪問控制限制，這與其他面相對對象語言有區別。關於私有屬性和私有方法，有如下要點： 1、通常我們約定，兩個下劃線開頭的屬性是私有的（private）。其他為公共的（public）； 2、類內部可以訪問私有屬性（方法）； 3、類外 ...
  • C++ 訪問說明符詳解：封裝數據，控制訪問，提升安全性
    C++ 訪問說明符 訪問說明符是 C++ 中控制類成員（屬性和方法）可訪問性的關鍵字。它們用於封裝類數據並保護其免受意外修改或濫用。 三種訪問說明符： public：允許從類外部的任何地方訪問成員。 private：僅允許在類內部訪問成員。 protected：允許在類內部及其派生類中訪問成員。 示 ...
  • static_cast與dynamic_cast到底是什麼？
    寫這個隨筆說一下C++的static_cast和dynamic_cast用在子類與父類的指針轉換時的一些事宜。首先，【static_cast，dynamic_cast】【父類指針，子類指針】，兩兩一組，共有4種組合：用 static_cast 父類轉子類、用 static_cast 子類轉父類、使用 ...
  • C語言數據結構：雙向鏈表的增刪操作
    /******************************************************************************************************** * * * 設計雙向鏈表的介面 * * * * Copyright (c) 2023-2 ...
  • @ComponentScan註解的實現，Spring掃描包的過程
    相信接觸過spring做開發的小伙伴們一定使用過@ComponentScan註解 @ComponentScan("com.wangm.lifecycle") public class AppConfig { } @ComponentScan指定basePackage，將包下的類按照一定規則註冊成Be ...
  • opensips開啟python支持
    操作系統 ：CentOS 7.6_x64 opensips版本： 2.4.9 python版本：2.7.5 python作為腳本語言，使用起來很方便，查了下opensips的文檔，支持使用python腳本寫邏輯代碼。今天整理下CentOS7環境下opensips2.4.9的python模塊筆記及使用 ...
所有分類