mysql 表外連接

外連接

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

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

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

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

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

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

mysql 約束

基本介紹

約束用于確保數據庫的數據滿足特定的商業規則。在mysql中，約束包括：

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

primary key(主鍵)-基本使用

primary key（主鍵）-細節說明

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

主鍵的指定方式有兩種

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

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

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

4. 提醒：在實際開發中，每個表往往都會設計一個主鍵

not null(非空)

unique(唯一)

unique 細節（注意）：

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

foreign key(外鍵)

用于定義主表和從表之間的關系：

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

當定義外鍵約束后，要求外鍵列數據必須在主表的主鍵列存在或是為null

foreign key（外鍵）一細節說明

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

check

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

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

實現check的功能，一般是在程序中控制或者通過觸發器完成。

自增長

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

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

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

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

5. 如果你添加數據時，給自增長字段（列）指定的有值，則以指定的值為準，如果指定了自增長，一般來說，就按照自增長的規則來添加數據

mysql 索引

索引的原理

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

使用索引為什么會快？形成一個索引的數據結構，比如二叉樹索

引的代價

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

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

索引的類型

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

一般開發，不使用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;

小結

較頻繁的作為查詢條件字段應該創建索引

select * from emp where empno = 1

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

select * from emp where sex = '男

更新非常頻繁的字段不適合創建索引

select * from emp where logincount = 1

不會出現在WHERE子句中字段不該創建索引

mysql 事務

什么是事務

事務用于保證數據的一致性，它由一組相關的dml語句組成，該組的dml語句要么全部成功，要么全部失敗。

如：轉賬就要用事務來處理，用以保證數據的一致性。

事務和鎖

當執行事務操作時（dml語句），mysql會在表上加鎖，防止其它用戶改表的數據.這對用戶來講是非常重要的

mysql 數據庫控制臺事務的幾個重要操作

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

回退事務

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

保存點是事務中的點.用于取消部分事務，當結束事務時（commit），

會自動的刪除該事務所定義的所有保存點當執行回退事務時，通過指定保存點可以回退到指定的點

提交事務

使用commit語句可以提交事務.當執行了commit語句子后，會確認事務的變化、結束事務、刪除保存點、釋放鎖，數據生效。

當使用commit語句結束事務子后，其它會話［其他連接］將可以查看到事務變化后的新數據［所有數據就正式生效.］

事務細節討論

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

mysql 事務隔離級別

事務隔離級別介紹

多個連接開啟各自事務操作數據庫中數據時，數據庫系統要負責隔離操作，以保證各個連接在獲取數據時的準確性。（通俗解釋）

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

• 臟讀
• 不可重復讀
• 幻讀

查看事務隔離級別

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

事務隔離級別

設置事務隔離級別

查看當前會話隔離級別

select ＠＠tx isolation；

查看系統當前隔離級別

select ＠＠global.tx isolation；

設置當前會話隔離級別

set session transaction isolation level repeatable read；

設置系統當前隔離級別

set global transaction isolation level repeatable read；

mysql 默認的事務隔離級別是 repeatable read，

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

mysql事務 ACID

事務的 acid 特性

原子性（Atomicity）

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

一致性（Consistency）

事務必須使數據庫從一個一致性狀態變換到另外一個一致性狀態

隔離性（Isolation）

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

持久性（Durability）

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

mysql 表類型和存儲引擎

基本介紹

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

主要包括MyISAM、innoDB、Memory等

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

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

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

細節說明

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

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

MEMORY存儲引擎使用存在內存中的內容來創建表。

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

因為它的數據是放在內存中的，并且默認使用HASH索引。但是一旦MySQL服務關閉，表中的數據就會丟失掉，表的結構還在。

如何選擇表的存儲引擎

如果你的應用不需要事務，處理的只是基本的CRUD操作，

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

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

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

（經典用法用戶的在線狀態（）.）

修改存儲引擎

視圖(view)

基本概念

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

視圖包含列，其數據來自對應的真實表（基表）

視圖和基表關系的示意圖

視圖的基本使用

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

視圖的細節

1. 創建視圖后，到數據庫去看，對應視圖只有一個視圖結構文件(形式: 視圖名.frm)

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

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

視圖最佳實踐

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

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

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

​ 性能。關系數據庫的數據常常會分表存儲，使用外鍵建立這些表的之間關系。

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

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

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

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

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

Mysql 管理

mysql中的用戶，都存儲在系統數據庫mysql中 user 表中

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

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

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

2. user: 用戶名；

3. authentication＿string：

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

創建用戶

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

說明：創建用戶，同時指定密碼

刪除用戶

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

用戶修改密碼

修改自己的密碼：

set password = password（＇密碼＇）；

修改他人的密碼（需要有修改用戶密碼權限）：

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

mysql 中的權限

給用戶授權

基本語法：

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

說明：

1. 權限列表，多個權限用逗號分開

2. grant select on …

3. grant select， delete， create on …

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

特別說明

＊.＊:代表本系統中的所有數據庫的所有對象（表，視圖，存儲過程）

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

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

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

如果該用戶不存在，就是創建該用戶！

回收用戶授權

基本語法：

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

權限生效指令

如果權限沒有生效，可以執行下面命令

​ 基本語法：

​ FLUSH PRIVILEGES；

細節說明

在創建用戶的時候，如果不指定Host，則為％，

％表示表示所有IP都有連接權限create user xxx；

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

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

予該用戶在該對象上的所有權限

特別說明

＊.＊:代表本系統中的所有數據庫的所有對象（表，視圖，存儲過程）

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

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

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

如果該用戶不存在，就是創建該用戶！

回收用戶授權

基本語法：

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

權限生效指令

如果權限沒有生效，可以執行下面命令

​ 基本語法：

​ FLUSH PRIVILEGES；

細節說明

在創建用戶的時候，如果不指定Host，則為％，

％表示表示所有IP都有連接權限create user xxx；

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

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

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