mysql 表外連接

外連接

1.左外連接（如果左側(cè)的表完全顯示我們就說是左外連接）

2.右外連接（如果右側(cè)的表完全顯示我們就說是右外連接）

3.使用左連接（顯示所有人的成績，如果沒有成績，也要顯示該人的姓名和id號，成績顯示為空）

select.from表1 left join表2 on條件 ［表1：就是左表表2：就是右表］

4.使用右外連接（顯示所有成績，如果沒有名字匹配，顯示空）

select . from 表1 right join 表2 on條件［表1：就是左表表2：就是右表］

mysql 約束

基本介紹

約束用于確保數(shù)據(jù)庫的數(shù)據(jù)滿足特定的商業(yè)規(guī)則。在mysql中，約束包括：

not null、 unique，primary key，foreign key，和check 五種.

primary key(主鍵)-基本使用

primary key（主鍵）-細(xì)節(jié)說明

1. primary key能重復(fù)而且能null。
2. 一張表最多只能有一個主鍵，但可以是復(fù)合主鍵

主鍵的指定方式有兩種

1. 直接在字段名后指定：字段名primakry key

2. 在表定義最后寫 primary key（列名）；

3. 使desc表，可以看到primary key的情況.

4. 提醒：在實際開發(fā)中，每個表往往都會設(shè)計一個主鍵

not null(非空)

unique(唯一)

unique 細(xì)節(jié)（注意）：

1. 如果沒有指定 not null，則 unique 字段可以有多個null
2. 一張表可以有多個unique字段

foreign key(外鍵)

用于定義主表和從表之間的關(guān)系：

外鍵約束要定義在從表上，主表則必須具有主鍵約束或是unique約束.，

當(dāng)定義外鍵約束后，要求外鍵列數(shù)據(jù)必須在主表的主鍵列存在或是為null

foreign key（外鍵）一細(xì)節(jié)說明

1. 外鍵指向的表的字段，要求是primary key或者是unique
2. 表的類型是innodb，這樣的表才支持外鍵
3. 外鍵字段的類型要和主鍵字段的類型一致（長度可以不同）
4. 外鍵字段的值，必須在主鍵字段中出現(xiàn)過，或者為null［前提是外鍵字段允許為null］
5. 一旦建立主外鍵的關(guān)系，數(shù)據(jù)不能隨意刪除了

check

用于強制行數(shù)據(jù)必須滿足的條件，假定在sal列上定義了check約束，并要求sal列值在10002000之間如果不再10002000之間就會提示出錯。

提示： oracle 和 sql server均支持check，但是mysql5.7目前還不支持check，只做語法校驗，但不會生效。check.sql在mysql中

實現(xiàn)check的功能，一般是在程序中控制或者通過觸發(fā)器完成。

自增長

1. 一般來說自增長是和primary key配合使用的

2. 自增長也可以單獨使用［但是需要配合一個unique］

3. 自增長修飾的字段為整數(shù)型的（雖然小數(shù)也可以但是非常非常少這樣使用）

4. 自增長默認(rèn)從1開始，你也可以通過如下命令修改alter table表名 auto_increment =新的始值；

5. 如果你添加數(shù)據(jù)時，給自增長字段（列）指定的有值，則以指定的值為準(zhǔn)，如果指定了自增長，一般來說，就按照自增長的規(guī)則來添加數(shù)據(jù)

mysql 索引

索引的原理

沒有索引為什么會慢？因為全表掃描

使用索引為什么會快？形成一個索引的數(shù)據(jù)結(jié)構(gòu)，比如二叉樹索

引的代價

1. 磁盤占用
2. 對dml（update delete insert）語句的效率影響

在我們項目一般是中，select多 （update，delete，insert）少

索引的類型

1. 主鍵索引，主鍵自動的為主索引用（類型Primary key）
2. 唯一索引（UNIQUE）
3. 普通索引（INDEX）
4. 文索引（FULLTEXT）［適用于MyISAM］

一般開發(fā)，不使用mysql自帶的全文索引，而是使用：全文搜索Solr和 ElasticSearch（ES）

索引使用

添加索引

1. create [UNIQUE] index index_name on tbl_name (col_name [(length)][ASC | DESC],…);
2. alter table table_name ADD INDEX [index name] (index col name,.)
3. 添加主鍵（索引）

ALTER TABLE表名ADD PRIMARY KEY(列名，.)；

刪除索引

1. DROP INDEX index_name ON tbl_name;
2. alter table table_name drop index index_name;
3. 刪除主索引比較特別：

alter table t_b drop primary key;

1. 查詢索引（三種方式）
2. show index(es) from table_name;
3. show keys from table_name;
4. desc table_Name;

小結(jié)

較頻繁的作為查詢條件字段應(yīng)該創(chuàng)建索引

select * from emp where empno = 1

唯一性太差的字段不適合單獨創(chuàng)建索引，即使頻繁作為查詢條件

select * from emp where sex = '男

更新非常頻繁的字段不適合創(chuàng)建索引

select * from emp where logincount = 1

不會出現(xiàn)在WHERE子句中字段不該創(chuàng)建索引

mysql 事務(wù)

什么是事務(wù)

事務(wù)用于保證數(shù)據(jù)的一致性，它由一組相關(guān)的dml語句組成，該組的dml語句要么全部成功，要么全部失敗。

如：轉(zhuǎn)賬就要用事務(wù)來處理，用以保證數(shù)據(jù)的一致性。

事務(wù)和鎖

當(dāng)執(zhí)行事務(wù)操作時（dml語句），mysql會在表上加鎖，防止其它用戶改表的數(shù)據(jù).這對用戶來講是非常重要的

mysql 數(shù)據(jù)庫控制臺事務(wù)的幾個重要操作

1. start transaction －開始一個事務(wù)
2. savepoint保存點名－設(shè)置存點
3. rollback to保存點名－回退事務(wù)
4. rollback-回退全部事務(wù)
5. commit-提交事務(wù)，所有的操作生效，不能回退

回退事務(wù)

在介紹回退事務(wù)前，先介紹一下保存點（savepoint）.

保存點是事務(wù)中的點.用于取消部分事務(wù)，當(dāng)結(jié)束事務(wù)時（commit），

會自動的刪除該事務(wù)所定義的所有保存點當(dāng)執(zhí)行回退事務(wù)時，通過指定保存點可以回退到指定的點

提交事務(wù)

使用commit語句可以提交事務(wù).當(dāng)執(zhí)行了commit語句子后，會確認(rèn)事務(wù)的變化、結(jié)束事務(wù)、刪除保存點、釋放鎖，數(shù)據(jù)生效。

當(dāng)使用commit語句結(jié)束事務(wù)子后，其它會話［其他連接］將可以查看到事務(wù)變化后的新數(shù)據(jù)［所有數(shù)據(jù)就正式生效.］

事務(wù)細(xì)節(jié)討論

1. 如果不開始事務(wù)，默認(rèn)情況下，dml操作是自動提交的，不能回滾
2. 如果開始一個事務(wù)，你沒有創(chuàng)建保存點.你可以執(zhí)行rollback，默認(rèn)就是回退到你事務(wù)開始的狀態(tài)
3. 你也可以在這個事務(wù)中（還沒有提交時），創(chuàng)建多個保存點.比如：savepoint aaa；執(zhí)行 dml， savepoint bbb；
4. 你可以在事務(wù)沒有提交前，選擇回退到哪個保存點
5. mysql的事務(wù)機制需要innodb的存儲引擎才可以使用，myisam不好使.
6. 開始一個事務(wù) start transaction， set autocommit=off；

mysql 事務(wù)隔離級別

事務(wù)隔離級別介紹

多個連接開啟各自事務(wù)操作數(shù)據(jù)庫中數(shù)據(jù)時，數(shù)據(jù)庫系統(tǒng)要負(fù)責(zé)隔離操作，以保證各個連接在獲取數(shù)據(jù)時的準(zhǔn)確性。（通俗解釋）

如果不考慮隔離性，可能會引發(fā)如下問題：

• 臟讀
• 不可重復(fù)讀
• 幻讀

查看事務(wù)隔離級別

1. 臟讀（dirty read）：當(dāng)一個事務(wù)讀取另一個事務(wù)尚未提交的改變（update，insert，delete）時，產(chǎn)生臟讀
2. 不可重復(fù)讀（nonrepeatable read）：同一查詢在同一事務(wù)中多次進(jìn)行，由于其他提交事務(wù)所做的修改或刪除，每次返回不同的結(jié)果集，此時發(fā)生不可重復(fù)讀
3. 幻讀（phantom read）：同一查詢在同一事務(wù)中多次進(jìn)行，由于其他提交事務(wù)所做的插入操作，每次返回不同的結(jié)果集，此時發(fā)生幻讀

事務(wù)隔離級別

設(shè)置事務(wù)隔離級別

查看當(dāng)前會話隔離級別

select ＠＠tx isolation；

查看系統(tǒng)當(dāng)前隔離級別

select ＠＠global.tx isolation；

設(shè)置當(dāng)前會話隔離級別

set session transaction isolation level repeatable read；

設(shè)置系統(tǒng)當(dāng)前隔離級別

set global transaction isolation level repeatable read；

mysql 默認(rèn)的事務(wù)隔離級別是 repeatable read，

一般情況下，沒有特殊要求，沒有必要修改（因為該級別可以滿足絕大部分項目需求）

mysql事務(wù) ACID

事務(wù)的 acid 特性

原子性（Atomicity）

原子性是指事務(wù)是一個不可分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生。

一致性（Consistency）

事務(wù)必須使數(shù)據(jù)庫從一個一致性狀態(tài)變換到另外一個一致性狀態(tài)

隔離性（Isolation）

事務(wù)的隔離性是多個用戶并發(fā)訪問數(shù)據(jù)庫時，數(shù)據(jù)庫為每一個用戶開啟的事務(wù)，不能被其他事務(wù)的操作數(shù)據(jù)所干擾，多個并發(fā)事務(wù)之間要相互隔離。

持久性（Durability）

持久性是指一個事務(wù)一旦被提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的，接下來即使數(shù)據(jù)庫發(fā)生故障也不應(yīng)該對其有任何影響

mysql 表類型和存儲引擎

基本介紹

​ MySQL的表類型由存儲引擎（Storage Engines）決定

主要包括MyISAM、innoDB、Memory等

1. MySQL數(shù)據(jù)表主要支持六種類型，分別是：CSV、Memory、ARCHIVE, MRG_MYISAM、 MYISAM、 InnoBDB。
2. 這六種又分為兩類，一類是”事務(wù)安全型”（transaction-safe），比如：InnoDB；

其余都屬于第二類，稱為”非事務(wù)安全型”（non-transaction-safe）［mysiam 和 memory］

主要的存儲引擎/表類型特點

細(xì)節(jié)說明

1. MylSAM不支持事務(wù)、也不支持外鍵，但其訪問速度快，對事務(wù)完整性沒有要求
2. InnoDB存儲引擎提供了具有提交、回滾和崩潰恢復(fù)能力的事務(wù)安全。

但是比起MylSAM存儲引擎，InnoDB寫的處理效率差一些并且會占用更多的磁盤空間以保留數(shù)據(jù)和索引。

MEMORY存儲引擎使用存在內(nèi)存中的內(nèi)容來創(chuàng)建表。

每個MEMORY表只實際對應(yīng)一個磁盤文件。MEMORY類型的表訪問非常得快，

因為它的數(shù)據(jù)是放在內(nèi)存中的，并且默認(rèn)使用HASH索引。但是一旦MySQL服務(wù)關(guān)閉，表中的數(shù)據(jù)就會丟失掉，表的結(jié)構(gòu)還在。

如何選擇表的存儲引擎

如果你的應(yīng)用不需要事務(wù)，處理的只是基本的CRUD操作，

那么MylSAM是不二選擇，速度快

1. 如果需要支持事務(wù)，選擇InnoDB。
2. Memory存儲引擎就是將數(shù)據(jù)存儲在內(nèi)存中，由于沒有磁盤I./O的等待，速度極快。

但由于是內(nèi)存存儲引擎，所做的任何修改在服務(wù)器重啟后都將消失。

（經(jīng)典用法用戶的在線狀態(tài)（）.）

修改存儲引擎

視圖(view)

基本概念

​ 視圖是一個虛擬表，其內(nèi)容由查詢定義。同真實的表一樣，

視圖包含列，其數(shù)據(jù)來自對應(yīng)的真實表（基表）

視圖和基表關(guān)系的示意圖

視圖的基本使用

1. create view 視圖名 as select語句
2. alter view 視圖名 as select語句 -更新成新的視圖
3. SHOW CREATE VIEW
4. drop view視圖名1，視圖名2

視圖的細(xì)節(jié)

1. 創(chuàng)建視圖后，到數(shù)據(jù)庫去看，對應(yīng)視圖只有一個視圖結(jié)構(gòu)文件(形式: 視圖名.frm)

2. 視圖的數(shù)據(jù)變化會影響到基表，基表的數(shù)據(jù)變化也會影響到視圖[insert update delete ]

3. 視圖中可以再使用視圖 , 比如從 view01 視圖中，選出 字段 做出新視圖

視圖最佳實踐

安全。一些數(shù)據(jù)表有著重要的信息。有些字段是保密的，不能讓用戶直接看到。

這時就可以創(chuàng)建一個視圖，在這張視圖中只保留一部分字段。

這樣，用戶就可以查詢自己需要的字段，不能查看保密的字段。

​ 性能。關(guān)系數(shù)據(jù)庫的數(shù)據(jù)常常會分表存儲，使用外鍵建立這些表的之間關(guān)系。

這時，數(shù)據(jù)庫查詢通常會用到連接（JOIN）。這樣做不但麻煩，效率相對也比較低。

如果建立一個視圖，將相關(guān)的表和字段組合在一起，就可以避免使用JOIN查詢數(shù)據(jù)。

​ 靈活。如果系統(tǒng)中有一張舊的表，這張表由于設(shè)計的問題，即將被廢棄。

然而，很多應(yīng)用都是基于這張表，不易修改。這時就可以建立一張視圖，

視圖中的數(shù)據(jù)直接映射到新建的表。這樣，就可以少做很多改動，也達(dá)到了升級數(shù)據(jù)表的目的。

Mysql 管理

mysql中的用戶，都存儲在系統(tǒng)數(shù)據(jù)庫mysql中 user 表中

其中user表的重要字段說明：

1.host: 允許登錄的“位置”，

localhost表示該用戶只允許本機登錄，也可以指定ip地址，比如：192.168.1.100

2. user: 用戶名；

3. authentication＿string：

密碼，是通過mysql的password（）函數(shù)加密之后的密碼。

創(chuàng)建用戶

create user ‘用戶名’ ‘＠’ ‘允許登錄位置’ identified by ’密碼’

說明：創(chuàng)建用戶，同時指定密碼

刪除用戶

drop user ‘用名’ ‘＠’ ‘允許登錄位置’；

用戶修改密碼

修改自己的密碼：

set password = password（＇密碼＇）；

修改他人的密碼（需要有修改用戶密碼權(quán)限）：

set password for ＇用戶名＇＠＇登錄位置＇ = password（＇密碼＇）；

mysql 中的權(quán)限

給用戶授權(quán)

基本語法：

grant權(quán)限列表on庫.對象名 to ‘用戶名’ ‘＠’ ‘登錄位置’ 【identified by＇密碼＇】

說明：

1. 權(quán)限列表，多個權(quán)限用逗號分開

2. grant select on …

3. grant select， delete， create on …

4. grant all 【privileges】 on… //表示賦予該用戶在該對象上的所有權(quán)限

特別說明

＊.＊:代表本系統(tǒng)中的所有數(shù)據(jù)庫的所有對象（表，視圖，存儲過程）

庫.＊：表示某個數(shù)據(jù)庫中的所有數(shù)據(jù)對象（表，視圖，存儲過程等）

identified by可以省略，也可以寫出

如果用戶存在，就是修改該用戶的密碼。

如果該用戶不存在，就是創(chuàng)建該用戶！

回收用戶授權(quán)

基本語法：

​ revoke 權(quán)限列表 on 庫**.**對象名 from ＇用戶名’ ‘＠‘ ‘登錄位置＇；

權(quán)限生效指令

如果權(quán)限沒有生效，可以執(zhí)行下面命令

​ 基本語法：

​ FLUSH PRIVILEGES；

細(xì)節(jié)說明

在創(chuàng)建用戶的時候，如果不指定Host，則為％，

％表示表示所有IP都有連接權(quán)限create user xxx；

你也可以這樣指定create user ‘xxx’ ‘＠’ ’19168.1.％’

表示 xxx用戶在192.168.1.＊的ip可以登錄mysql

予該用戶在該對象上的所有權(quán)限

特別說明

＊.＊:代表本系統(tǒng)中的所有數(shù)據(jù)庫的所有對象（表，視圖，存儲過程）

庫.＊：表示某個數(shù)據(jù)庫中的所有數(shù)據(jù)對象（表，視圖，存儲過程等）

identified by可以省略，也可以寫出

如果用戶存在，就是修改該用戶的密碼。

如果該用戶不存在，就是創(chuàng)建該用戶！

回收用戶授權(quán)

基本語法：

​ revoke 權(quán)限列表 on 庫**.**對象名 from ＇用戶名’ ‘＠‘ ‘登錄位置＇；

權(quán)限生效指令

如果權(quán)限沒有生效，可以執(zhí)行下面命令

​ 基本語法：

​ FLUSH PRIVILEGES；

細(xì)節(jié)說明

在創(chuàng)建用戶的時候，如果不指定Host，則為％，

％表示表示所有IP都有連接權(quán)限create user xxx；

你也可以這樣指定create user ‘xxx’ ‘＠’ ’19168.1.％’

表示 xxx用戶在192.168.1.＊的ip可以登錄mysql

在刪除用戶的時候，如果host不是％，需要明確指定 ’用戶’ ’＠’ host值＇