本節(jié)用到的員工信息管理表結(jié)構(gòu)放到資源中，需要的同學(xué)自取。本節(jié)內(nèi)容以此表為示例：

面試題：innodb與myisam的區(qū)別。

外鍵，事務(wù)

一、簡(jiǎn)單查詢

語(yǔ)法：

select [ 去重關(guān)鍵字distinct] 字段 from 表格名稱;

字段：*代表所有字段

select 字段  as "字段別名"... from 表格名稱;

as可以省略不寫，后面空格直接跟別名。

 查詢所有員工的編號(hào)，姓名，薪資

select empno"員工編號(hào)",ename"員工姓名",sal"員工薪資" from emp;

二、限定查詢

語(yǔ)法：

select [ 去重關(guān)鍵字distinct] 字段 from 表格名稱 [限定語(yǔ)法]；

where >, < ,>= ,<= ,!=, between...and...,

查詢公司中工資高于2000的員工

select * from emp where sal > 2000;

查詢公司中工資1000到3000的員工

select * from emp where sal between 1000 and 3000;
select * from emp where sal > 1000 and sal < 3000;

查詢有獎(jiǎng)金的員工信息

select * from emp where comm > 0

查詢沒(méi)有獎(jiǎng)金的員工信息

select * from emp where comm IS null or comm = 0

查詢名稱中以S開頭 模糊匹配 %通配所有 _通配一位

select * from emp where ename like "s%" 以s開頭
select * from emp where ename like "%s" 以s結(jié)尾
select * from emp where ename like "%s%" 名稱中包含s
select * from emp where ename like "_o%" 第二位為o,其余無(wú)所謂

查詢1981年入職的員工信息

select * from emp where HIREDATE BETWEEN '1981-01-01' and '1981-12-31'
select * from emp where HIREDATE like '%1981%'

查詢員工編號(hào)為7499,7521的員工信息

select * from emp where EMPNO = 7499 or EMPNO = 7521
select * from emp where EMPNO in (7499,7521)

三、排序查詢

語(yǔ)法：

select [ 去重關(guān)鍵字distinct] 字段 from 表格名稱 [限定語(yǔ)法][排序條件]；

排序關(guān)鍵字：order by

升序：asc

降序：desc

查詢員工信息，根據(jù)薪資做倒序排序

select * from emp order by asl desc;

查詢員工信息，根據(jù)入職日期做降序排序， 日期一致則按薪資升序排序。

select * from emp order by hiredate desc ,sal asc；

四、多表查詢

語(yǔ)法：select [去重關(guān)鍵字DISTINCT] 字段 from 表格名稱 , 表格名稱 [限定語(yǔ)法][排序條件];

查詢所有員工信息，包含部門信息

select * from emp,dept

以上查詢方式將兩張表進(jìn)行簡(jiǎn)單堆積，查詢中有無(wú)用的冗余數(shù)據(jù)，這種現(xiàn)象稱之為笛卡爾積效應(yīng)

在查詢過(guò)程中，添加關(guān)聯(lián)條件，用來(lái)在顯示上消除笛卡爾積效應(yīng)

select * from emp,dept where emp.deptno = dept.deptno
select e.*,d.DNAME,d.loc from emp e,dept d where e.deptno = d.deptno

查詢所有員工信息，包含員工編號(hào)、員工姓名、員工薪資、領(lǐng)導(dǎo)編號(hào)、領(lǐng)導(dǎo)姓名、領(lǐng)導(dǎo)薪資

確定需要的表格：emp e1,emp e2

確定需要的字段：e1.empno '員工編號(hào)',e1.ename '員工姓名',e1.sal '員工薪資',e2.empno '領(lǐng)導(dǎo)編號(hào)',e2.ename '領(lǐng)導(dǎo)姓名',e2.sal '領(lǐng)導(dǎo)薪資'

確定需要的關(guān)聯(lián)條件：e1.mgr = e2.empno

組裝sql：

SELECT
e1.empno '員工編號(hào)',
e1.ename '員工姓名',
e1.sal '員工薪資',
e2.empno '領(lǐng)導(dǎo)編號(hào)',
e2.ename '領(lǐng)導(dǎo)姓名',
e2.sal '領(lǐng)導(dǎo)薪資'
FROM
emp e1, emp e2
WHERE
e1.mgr = e2.empno;

以上sql查詢完之后，顯示13條結(jié)果，KING這條數(shù)據(jù)沒(méi)有顯示（邊界值無(wú)法查詢），如果向解決邊界值問(wèn)題，需要使用連接查詢

五、連接查詢

語(yǔ)法：

select [去重關(guān)鍵字DISTINCT] 字段 from 表格名稱 [連接條件] 表格名稱 [限定語(yǔ)法][排序條件];
左（外）連接：left(outer) join ...on...
右（外）連接：right(outer) join ...on...
以哪個(gè)表為重點(diǎn)就哪邊連接；

 查詢所有員工信息，包含員工編號(hào)、員工姓名、員工薪資、領(lǐng)導(dǎo)編號(hào)、領(lǐng)導(dǎo)姓名、領(lǐng)導(dǎo)薪資

SELECT 
    e1.empno AS '員工編號(hào)',
    e1.ename AS '員工姓名',
    e1.sal AS '員工薪資',
    e2.empno AS '領(lǐng)導(dǎo)編號(hào)',
    e2.ename AS '領(lǐng)導(dǎo)姓名',
    e2.sal AS '領(lǐng)導(dǎo)薪資'
FROM 
    emp e1
LEFT JOIN 
    emp e2 ON e1.mgr = e2.empno;

拓展語(yǔ)法：SQL1999語(yǔ)法

交叉連接：select * from emp join dept; 類似于“，”進(jìn)行笛卡爾積， 查詢顯示56條結(jié)果

自然連接：select * from emp natural join dept; 自動(dòng)組合共同字段，查詢顯示14條結(jié)果

join...on+條件：select * from emp join dept on emp.deptno = dept.deptno; 查詢顯示14條結(jié)果

join...using(兩邊表的共同字段)：select * from emp join dept using(deptno); 查詢顯示14條結(jié)果

六、分組查詢

分組前提：需要分組的字段有重復(fù)值

語(yǔ)法：

select [去重關(guān)鍵字DISTINCT] 字段 from 表格名稱 [限定語(yǔ)法][分組條件][排序條件];
分組關(guān)鍵字：group by

注意事項(xiàng)：

1.一旦出現(xiàn)分組條件，那么select后邊只允許出現(xiàn)統(tǒng)計(jì)函數(shù)和分組字段
2.分組之后如果還想使用限定條件篩選，那么不允許使用where，需要使用having

 查詢每一個(gè)部門的平均工資

確定需要的表：emp
確定需要的字段：avg(sal)
確定需要的分組條件：group by deptno
組裝sql:select avg(sal) from emp group by deptno

優(yōu)化sql:select deptno,avg(sal) from emp group by deptno

發(fā)現(xiàn)上述sql中沒(méi)有40部門（邊界值）
確定需要的表：emp e,dept d
確定需要的字段：d.deptno,avg(sal)
確定需要的分組條件：group by d.deptno
組裝sql:

SELECT 
    d.deptno, 
    IFNULL(AVG(sal), 0) '平均工資' 
FROM 
    emp e 
RIGHT JOIN 
    dept d ON e.deptno = d.deptno 
GROUP BY 
    d.deptno;

查詢部門的平均薪資，要求顯示平均薪資高于2000的信息

SELECT 
    d.deptno, 
    IFNULL(AVG(e.sal), 0) AS '平均工資' 
FROM 
    dept d 
LEFT JOIN 
    emp e ON d.deptno = e.deptno 
GROUP BY 
    d.deptno 
HAVING 
    IFNULL(AVG(e.sal), 0) > 2000;  -- 確保在過(guò)濾時(shí)處理 NULL 值
    AVG(sal) > 2000

七、統(tǒng)計(jì)查詢

平均avg   最大值max   最小值min   統(tǒng)計(jì)數(shù)量 count   求和 sum

拓展技術(shù)：

單行函數(shù)參考單行函數(shù)文檔。

八、子查詢

where 子查詢

當(dāng)查詢的結(jié)果為單行單列或多行單列的時(shí)候

查詢比smith工資高的所有員工信息。

SELECT *
FROM emp
WHERE sal > (SELECT sal FROM emp WHERE ename = 'SMITH');

 查詢公司中和經(jīng)理一樣工資的員工信息

SELECT *
FROM emp
WHERE sal in (SELECT sal FROM emp WHERE job = 'manager')

 小貼士：

=ang        等同于       in

<ang        等同于        比最大值小的數(shù)據(jù)

>ang        等同于        比最小值大的數(shù)據(jù)

<all        等同于        比最小值小的數(shù)據(jù)

>all        等同于        比最大值大的數(shù)據(jù)

 from 子查詢

當(dāng)查詢的結(jié)果為多行多列

查詢部門編號(hào)、部門名稱、部門位置、部門人數(shù)、部門平均薪資

第一步：查詢部門單表信息(4條結(jié)果)

select * from dept

第二步：查詢員工表，得到部門人數(shù)、部門平均(3條結(jié)果)

確定需要的表格：emp e
確定需要的字段：e.deptno deptno,count(e.empno) num,avg(e.sal) sal
確定需要的分組條件：group by e.deptno
組裝sql:
select e.deptno deptno,count(e.empno) num,avg(e.sal) sal from emp e group by e.deptno

第三步：將上述sql進(jìn)行左右連接查詢

確定需要的表格：
dept d,
(select e.deptno deptno,count(e.empno) num,avg(e.sal) sal from emp e group by e.deptno) temp
確定需要的字段：d.deptno,d.dname,d.loc,temp.num,temp.sal
確定需要的關(guān)聯(lián)條件：d.deptno = temp.deptno
組裝sql:

SELECT 
    d.deptno,
    d.dname,
    d.loc,
    temp.num,
    temp.sal
FROM 
    dept d
LEFT JOIN 
    (
        SELECT 
            e.deptno AS deptno,
            COUNT(e.empno) AS num,
            AVG(e.sal) AS sal
        FROM 
            emp e
        GROUP BY 
            e.deptno
    ) temp 
ON 
    d.deptno = temp.deptno;

九、分頁(yè)查詢

為什么需要分頁(yè)查詢？

語(yǔ)法：select [去重關(guān)鍵字DISTINCT] 字段 from 表格名稱 [限定語(yǔ)法][分組條件][排序條件][分頁(yè)條件]
分頁(yè)：limit n,m
n:數(shù)據(jù)索引，從0開始
m:每一頁(yè)顯示多少條

查詢第一頁(yè)員工數(shù)據(jù)，一頁(yè)顯示10條

select * from emp limit 0,10;

當(dāng)n為0的時(shí)候，可以省略不寫：select * from emp limit 10;

第二頁(yè)：

select * from emp limit 10,10;

 事務(wù)

  數(shù)據(jù)庫(kù)事務(wù)( transaction)是訪問(wèn)并可能操作各種數(shù)據(jù)項(xiàng)的一個(gè)數(shù)據(jù)庫(kù)操作序列，這些操作要么全部執(zhí)行,要么全部不執(zhí)行，是一個(gè)不可分割的工作單位。事務(wù)由事務(wù)開始與事務(wù)結(jié)束之間執(zhí)行的全部數(shù)據(jù)庫(kù)操作組成。

事務(wù)的四大特性：

1、原子性(Atomicity)：事務(wù)中的全部操作在數(shù)據(jù)庫(kù)中是不可分割的，要么全部完成，要么全部不執(zhí)行。 

2、一致性(Consistency)：幾個(gè)并行執(zhí)行的事務(wù)，其執(zhí)行結(jié)果必須與按某一順序 串行執(zhí)行的結(jié)果相一致。 

3、隔離性(Isolation)：事務(wù)的執(zhí)行不受其他事務(wù)的干擾，事務(wù)執(zhí)行的中間結(jié)果對(duì)其他事務(wù)必須是透明的。 

4、持久性(Durability):對(duì)于任意已提交事務(wù)，系統(tǒng)必須保證該事務(wù)對(duì)數(shù)據(jù)庫(kù)的改變不被丟失，即使數(shù)據(jù)庫(kù)出現(xiàn)故障。 

臟讀 幻讀

MySQL數(shù)據(jù)庫(kù)事務(wù)測(cè)試

mysql的事務(wù)是默認(rèn)提交機(jī)制

事務(wù)提交機(jī)制有兩種：自動(dòng)提交，手動(dòng)提交

修改數(shù)據(jù)庫(kù)事務(wù)提交機(jī)制：
關(guān)閉自動(dòng)提交：set autocommit = 0；

開啟自動(dòng)提交：set autocommit = 1；

如果關(guān)閉自動(dòng)提交，那么在發(fā)生增刪改以后需要程序員提交（commit）或回滾（rollback）

  MySQL數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別

MySQL 提供了四種事務(wù)隔離級(jí)別，以確保數(shù)據(jù)的一致性和完整性。這四種隔離級(jí)別分別是：讀未提交（Read Uncommitted）、讀已提交（Read Committed）、可重復(fù)讀（Repeatable Read）和可串行化（Serializable）

讀未提交(Read Uncommitted)：
        定義:事務(wù)可以讀取其他未提交事務(wù)的更改。

        問(wèn)題:可能導(dǎo)致臟讀(Dirty Read)

        適用場(chǎng)景:對(duì)數(shù)據(jù)一致性要求不高的場(chǎng)景。

讀已提交(Read Committed):（oracle默認(rèn)級(jí)別）

        定義:事務(wù)只能讀取其他已提交事務(wù)的更改。

        問(wèn)題:避免了臟讀，但可能導(dǎo)致不可重復(fù)讀(Non-repeatable Read)

        適用場(chǎng)景:大多數(shù)數(shù)據(jù)庫(kù)系統(tǒng)的默認(rèn)隔離級(jí)別，如oracle。

可重復(fù)讀(Repeatable Read):（MySQL默認(rèn)級(jí)別）

        定義:在同一事務(wù)中多次讀取相同數(shù)據(jù)時(shí)，結(jié)果一致。

        問(wèn)題:避免了臟讀和不可重復(fù)讀，但可能導(dǎo)致幻讀(Phantompead)。

        適用場(chǎng)景:MySQL 的默認(rèn)隔離級(jí)別，適用于大部分應(yīng)用。

可串行化(serializable):
        定義:最高的隔離級(jí)別，事務(wù)按順序逐個(gè)執(zhí)行，完全隔離。
        問(wèn)題:避免了臟讀、不可重復(fù)讀和幻讀，但并發(fā)性能最差。
        適用場(chǎng)景:對(duì)數(shù)據(jù)一致性要求極高的場(chǎng)景。

臟讀（Dirty Read）‌。

   定義‌：讀取到其他事務(wù)未提交的修改數(shù)據(jù)，若該事務(wù)回滾則導(dǎo)致數(shù)據(jù)無(wú)效
   示例‌：事務(wù)A修改賬戶余額后未提交，事務(wù)B讀取到該臨時(shí)值；若事務(wù)A回滾，事務(wù)B基于臟數(shù)據(jù)操作將引發(fā)錯(cuò)誤

不可重復(fù)讀（Non-repeatable Read）‌

   定義‌：同一事務(wù)內(nèi)多次讀取同一數(shù)據(jù)，因其他事務(wù)已提交的修改導(dǎo)致結(jié)果不一致
   示例‌：事務(wù)A第一次查詢余額為1000元，事務(wù)B修改為800元并提交后，事務(wù)A再次查詢結(jié)果變?yōu)?00元

幻讀（Phantom Read）‌

   定義‌：同一事務(wù)內(nèi)兩次范圍查詢的結(jié)果行數(shù)不同，因其他事務(wù)插入或刪除數(shù)據(jù)
   示例‌：事務(wù)A首次查詢年齡>30的用戶共10人，事務(wù)B新增1人后，事務(wù)A再次查詢結(jié)果為11人

隔離級(jí)別測(cè)試：

數(shù)據(jù)庫(kù)默認(rèn)隔離級(jí)別查看： 

• 查看全局默認(rèn)隔離級(jí)別（5.7版本之前）：SELECT @@global.tx_isolation;
• 查看全局默認(rèn)隔離級(jí)別（5.7版本之后）：SELECT @@global.transaction_isolation;
• 查看當(dāng)前會(huì)話隔離級(jí)別（5.7版本之前）：SELECT @@session.tx_isolation;
• 查看當(dāng)前會(huì)話隔離級(jí)別（5.7版本之后）：SELECT @@session.transaction_isolation;

第一步：修改數(shù)據(jù)庫(kù)隔離級(jí)別

SET [GLOBAL|SESSION} TRANSACTION ISOLATION LEVEL 
[READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|SERIALIZABLE]

第二步：設(shè)置手動(dòng)提交

set autocommit = 0;

第三步：開啟事務(wù)

start transaction;

第四步：測(cè)試業(yè)務(wù)

update t_person set sal = sal-500 where id = 1;
update t_person set sal = sal+500 where id = 3;

 索引、優(yōu)化、b+tree后續(xù)再了解。

python連接數(shù)據(jù)庫(kù)

安裝模塊pymysql

第一步：導(dǎo)入模塊

import pymysql

 第二步：創(chuàng)建連接

conn = pymysql.connect
(host='locohost', user='user', password='password', port=3306,database='database')

第三步：創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象

cursor = conn.cursor()

 第四步：執(zhí)行sql語(yǔ)句

sql = "select * from emp"
#通過(guò)數(shù)據(jù)庫(kù)對(duì)象執(zhí)行sql
cont = cursor.execute(sql)
#執(zhí)行sql語(yǔ)句，返回查詢結(jié)果的行數(shù)。
result = cursor.fetchall()
#執(zhí)行 execute() 后查詢的所有結(jié)果

第五步：關(guān)閉連接

cursor.close()
conn.close()

傳參問(wèn)題

• 方式一：直接字符串拼接
  把變量（如 username、password、id ）拼接到 SQL 語(yǔ)句里，有嚴(yán)重 SQL 注入風(fēng)險(xiǎn) ，比如用戶輸入惡意內(nèi)容可篡改查詢邏輯。
  示例：

  sql = 
  "select * from t_user where username = '"
  +username+
  "' and password = '"
  +password+
  "'"
  

• 方式二：簡(jiǎn)單格式化拼接（仍有風(fēng)險(xiǎn)）
  用 % 做占位符拼接參數(shù)，看似規(guī)范但本質(zhì)還是字符串拼接，仍可能被 SQL 注入 （如輸入 lufei' or 1=1 -- 可繞過(guò)校驗(yàn) ）

  sql = "select * from t_user where username = '%s' and password = '%s'"%(username,password)
  

• 方式三：參數(shù)化查詢（推薦）
  用 %s 做占位符，但實(shí)際執(zhí)行時(shí)由數(shù)據(jù)庫(kù)驅(qū)動(dòng)自動(dòng)處理參數(shù)轉(zhuǎn)義，可有效避免 SQL 注入 ，是安全的傳參方式

  sql = "select * from t_user where username = %(name)s and password = %(pwd)s" 
  cursor.execute(sql, {"name": username, "pwd": password})
  

到此這篇關(guān)于MySQL查詢優(yōu)化與事務(wù)實(shí)戰(zhàn)指南的文章就介紹到這了,更多相關(guān)mysql查詢優(yōu)化內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論