MySQL二次整理(4)v1.0

聯(lián)合查詢：將多個(gè)查詢語(yǔ)句的執(zhí)行結(jié)果相合并；

UNION

SELECT clause UNION SELECT cluase；

練習(xí)：導(dǎo)入hellodb.sql生成數(shù)據(jù)庫(kù)

(1) 在students表中，查詢年齡大于25歲，且為男性的同學(xué)的名字和年齡；

(2) 以ClassID為分組依據(jù)，顯示每組的平均年齡；

(3) 顯示第2題中平均年齡大于30的分組及平均年齡；

(4) 顯示以L開(kāi)頭的名字的同學(xué)的信息；

(5) 顯示TeacherID非空的同學(xué)的相關(guān)信息；

(6) 以年齡排序后，顯示年齡最大的前10位同學(xué)的信息；

(7) 查詢年齡大于等于20歲，小于等于25歲的同學(xué)的信息；用三種方法；

練習(xí)：導(dǎo)入hellodb.sql，以下操作在students表上執(zhí)行

1、以ClassID分組，顯示每班的同學(xué)的人數(shù)；

2、以Gender分組，顯示其年齡之和；

3、以ClassID分組，顯示其平均年齡大于25的班級(jí)；

4、以Gender分組，顯示各組中年齡大于25的學(xué)員的年齡之和；

練習(xí)：導(dǎo)入hellodb.sql，完成以下題目：

1、顯示前5位同學(xué)的姓名、課程及成績(jī)；

2、顯示其成績(jī)高于80的同學(xué)的名稱及課程；

3、求前8位同學(xué)每位同學(xué)自己兩門課的平均成績(jī)，并按降序排列；

4、顯示每門課程課程名稱及學(xué)習(xí)了這門課的同學(xué)的個(gè)數(shù)；

思考：

1、如何顯示其年齡大于平均年齡的同學(xué)的名字？

2、如何顯示其學(xué)習(xí)的課程為第1、2，4或第7門課的同學(xué)的名字？

3、如何顯示其成員數(shù)最少為3個(gè)的班級(jí)的同學(xué)中年齡大于同班同學(xué)平均年齡的同學(xué)？

4、統(tǒng)計(jì)各班級(jí)中年齡大于全校同學(xué)平均年齡的同學(xué)。

*********************************MySQL存儲(chǔ)引擎***************

存儲(chǔ)表類型: 表級(jí)別的概念 不建議在同一個(gè)庫(kù)中的表上使用不同的ENGINE(引擎);

創(chuàng)建表時(shí)指定引擎:

CREATE TABLE ... ENGINE[=]STORAGE_ENGINE_NAME ...

查看表信息: SHOW TABLE STATUS

常見(jiàn)的存儲(chǔ)引擎：

MyISAM:  意外崩潰后 無(wú)法保證數(shù)據(jù)安全  不支持事物

Aria:  意外崩潰后,可以保證數(shù)據(jù)安全

InnoDB 支持事物

MRG_MYISAM 用于實(shí)現(xiàn)將兩個(gè)MyISAM表在邏輯層上連接在一起.

CSV 基于文本文件存儲(chǔ)文件 跨數(shù)據(jù)庫(kù)交換比較好 但損失數(shù)據(jù)精度

BLACKHOLE 黑洞存儲(chǔ)引擎 在級(jí)聯(lián)復(fù)制時(shí)比較有用 (后面講MySQL主從模型時(shí)的半同步模型時(shí)用到)

MEMORY 基于內(nèi)存的存儲(chǔ)引擎 不適用于持久存儲(chǔ) 但性能高 支持hash索引 通常臨時(shí)表用

PERFORMANCE_SCHEMA 兼容表的mysql接口來(lái)顯示統(tǒng)計(jì)數(shù)據(jù)的 并非真正的表 虛表(類似/proc偽文件系統(tǒng))

ARCHIVE 歸檔存儲(chǔ)引擎 做數(shù)據(jù)倉(cāng)庫(kù)用

FEDERATED 夸服務(wù)器 夸物理主機(jī) 表聯(lián)合

***************************************************************

InnoDB: InnoBase公司研發(fā) 后贈(zèng)送給MySQL公司  后被甲骨文買了

并非原版InnoDB 而是第三方二次開(kāi)發(fā)板

Percona-XtraDB, Supports transactions, row-level locking, and foreign keys

支持事物,行級(jí)鎖,外鍵

數(shù)據(jù)存儲(chǔ)于“表空間(table space)"中：自組織文件系統(tǒng)

(1) 所有InnoDB表的數(shù)據(jù)和索引存儲(chǔ)于同一個(gè)表空間中；

表空間文件：datadir定義的目錄中

文件：ibdata1, ibdata2, ...

(2) innodb_file_per_table=ON，意味著每表使用單獨(dú)的表空間文件；

數(shù)據(jù)文件（數(shù)據(jù)和索引，存儲(chǔ)于數(shù)據(jù)庫(kù)目錄）: tbl_name.ibd

表結(jié)構(gòu)的定義：在數(shù)據(jù)庫(kù)目錄，tbl_name.frm

事務(wù)型存儲(chǔ)引擎，適合對(duì)事務(wù)要求較高的場(chǎng)景中；但較適用于處理大量短期事務(wù)；

基于MVCC（Mutli Version Concurrency Control）支持高并發(fā)；支持四個(gè)隔離級(jí)別，默認(rèn)級(jí)別為REPEATABLE-READ；間隙鎖以防止幻讀；

使用聚集索引（主鍵索引）；

支持”自適應(yīng)Hash索引“；

鎖粒度：行級(jí)鎖；

總結(jié)：

數(shù)據(jù)存儲(chǔ)：表空間；

并發(fā)：MVCC，間隙鎖，行級(jí)鎖；

索引：聚集索引、輔助索引；

性能：預(yù)讀操作、內(nèi)存數(shù)據(jù)緩沖、內(nèi)存索引緩存、自適應(yīng)Hash索引、插入操作緩存區(qū)；

備份：支持熱備；

***************************************************************

MyISAM：

支持全文索引（FULLTEXT index）、壓縮、空間函數(shù)（GIS）；

不支持事務(wù)

鎖粒度：表級(jí)鎖

崩潰無(wú)法保證表安全恢復(fù)

適用場(chǎng)景：只讀或讀多寫少的場(chǎng)景、較小的表（以保證崩潰后恢復(fù)的時(shí)間較短）；

文件：每個(gè)表有三個(gè)文件，存儲(chǔ)于數(shù)據(jù)庫(kù)目錄中

tbl_name.frm：表格式定義；

tbl_name.MYD：數(shù)據(jù)文件；

tbl_name.MYI：索引文件；

特性：

加鎖和并發(fā)：表級(jí)鎖；

修復(fù)：手動(dòng)或自動(dòng)修復(fù)、但可能會(huì)丟失數(shù)據(jù)；

索引：非聚集索引；

延遲索引更新；

表壓縮；

行格式：

 {DEFAULT|DYNAMIC|FIXED|COMPRESSED|REDUNDANT|COMPACT}

***************************************************************

其它的存儲(chǔ)引擎：

CSV：將CSV文件（以逗號(hào)分隔字段的文本文件）作為MySQL表文件；

MRG_MYISAM：將多個(gè)MyISAM表合并成的虛擬表；

BLACKHOLE：類似于/dev/null，不真正存儲(chǔ)數(shù)據(jù)；

MEMORY：內(nèi)存存儲(chǔ)引擎，支持hash索引，表級(jí)鎖，常用于臨時(shí)表；

FEDERATED: 用于訪問(wèn)其它遠(yuǎn)程MySQL服務(wù)器上表的存儲(chǔ)引擎接口；

MariaDB額外支持很多種存儲(chǔ)引擎：

OQGraph、SphinxSE、TokuDB、Cassandra、CONNECT、SQUENCE、...

搜索引擎：

lucene, sphinx

lucene：Solr, Elasticsearch

***************************************************************

并發(fā)控制：

鎖：Lock

為什么要用鎖:

當(dāng)一個(gè)表中有多個(gè)并發(fā)訪問(wèn)時(shí),為防止別人讀到的數(shù)據(jù)是正在修改的數(shù)據(jù),所以存在鎖機(jī)制.

鎖類型 ：

讀鎖：共享鎖，可被多個(gè)讀操作共享；會(huì)導(dǎo)致寫?zhàn)囸I(都操作不間斷 寫操作就必須一直等待讀操作完成)

寫鎖：排它鎖，獨(dú)占鎖；

鎖粒度：

表鎖：在表級(jí)別施加鎖，并發(fā)性較低；

行鎖：在行級(jí)另施加鎖，并發(fā)性較高；(被鎖行前后的行也會(huì)被鎖定 防止插入)

鎖策略：在鎖粒度及數(shù)據(jù)安全性之間尋求一種平衡機(jī)制；

存儲(chǔ)引擎：級(jí)別以及何時(shí)施加或釋放鎖由存儲(chǔ)引擎自行決定；

MySQL Server：表級(jí)別，可自行決定，也允許顯式請(qǐng)求；

鎖類別：

顯式鎖：用戶手動(dòng)請(qǐng)求的鎖；

隱式鎖：存儲(chǔ)引擎自行根據(jù)需要施加的鎖；

顯式鎖的使用：

(1) LOCK TABLES

LOCK TABLES  tbl_name [AS alisa]  read|write, tbl_name read|write, ...

UNLOCK TABLES

(2) FLUSH TABLES 把內(nèi)存中的表寫到磁盤上 再重新打開(kāi)

FLUSH TABLES tbl_name,... [WITH READ LOCK];

UNLOCK TABLES;

(3) SELECT

[FOR UPDATE | LOCK IN SHARE MODE共享模式鎖定]

例:SELECT * FROM students WHERE StuID IN (1,2,3) FOR UPDATE;完成后既立即釋放

  事務(wù)：

事務(wù)：一組原子性的SQL查詢、或者是一個(gè)或多個(gè)SQL語(yǔ)句組成的獨(dú)立工作單元；

事務(wù)日志：在磁盤上開(kāi)辟一塊連續(xù)空間 連續(xù)寫在事物日志中,由于連續(xù)寫操作沒(méi)有尋到時(shí)間性能提升比較明顯,最終再更新至磁盤中,

如果日志空間較大,則發(fā)生意外后重啟要把日志空間中的語(yǔ)句逐一寫入磁盤數(shù)據(jù),速度較慢

未防止日志寫滿,所以事物文件分組.

innodb_log_files_in_group 事物日志組數(shù)量

innodb_log_group_home_dir  事物日志目錄

innodb_log_file_size 單個(gè)日志大小

Innodb_mirrored_log_groups    多寫  多次寫日志組(確保安全  當(dāng)然不能放在一塊硬盤上)

ACID測(cè)試：是否滿足事物

A：AUTOMICITY，原子性；整個(gè)事務(wù)中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾；

C：CONSISTENCY，一至性；數(shù)據(jù)庫(kù)總是應(yīng)該從一個(gè)一致性狀態(tài)轉(zhuǎn)為另一個(gè)一致性狀態(tài)；

I：ISOLATION，隔離性；一個(gè)事務(wù)所做出的操作在提交之前，是否能為其它事務(wù)可見(jiàn)；出于保證并發(fā)操作之目的，隔離有多種級(jí)別；

D：DURABILITY，持久性；事務(wù)一旦提交，其所做出的修改會(huì)永久保存；

自動(dòng)提交：?jiǎn)握Z(yǔ)句事務(wù)

mysql> SELECT @@autocommit;

+------------------------+

| @@autocommit |

+------------------------+

|       1       |

+------------------------+

mysql> SET @@session.autocommit=0；

手動(dòng)控制事務(wù)：

啟動(dòng)：START TRANSACTION

提交：COMMIT

回滾：ROLLBACK

事務(wù)支持savepoints：一個(gè)事物的多條語(yǔ)句中插入時(shí)間點(diǎn)  可以回歸到事物中的某一個(gè)時(shí)間點(diǎn)

SAVEPOINT identifier   插入時(shí)間點(diǎn)

ROLLBACK [WORK] TO [SAVEPOINT] identifier 回歸到指定插入點(diǎn)

RELEASE SAVEPOINT identifier 刪除指定時(shí)間點(diǎn)

***********************************

事務(wù)隔離級(jí)別：

READ-UNCOMMITTED：讀未提交 --> 臟讀；可以讀取別人尚未提交的數(shù)據(jù) (允許讀取別人尚未提交的數(shù)據(jù))

READ-COMMITTED：讀提交--> 不可重復(fù)讀；事物修改的數(shù)據(jù)未提交之前.其他事物是看不見(jiàn)的.

REPEATABLE-READ：可重復(fù)讀 --> 幻讀；數(shù)據(jù)已經(jīng)更改數(shù)據(jù),但實(shí)際讀到的是舊數(shù)據(jù)

SERIALIZABLE：串行化；

mysql> SELECT @@session.tx_isolation; 查看當(dāng)前隔離級(jí)別

+----------------------------------+

| @@session.tx_isolation |

+----------------------------------+

| REPEATABLE-READ         |

+----------------------------------+

會(huì)出現(xiàn)死鎖.

查看InnoDB存儲(chǔ)引擎的狀態(tài)信息：

SHOW ENGINE innodb STATUS;