Mysql——內外連線，事物，索引

表的內外連線：

表的連線分為內連和外連

內連線：

內連線實際上就是利用where語句對倆個表形成的笛卡兒積進行篩選，我們前面學的所有連線都是內連結

語法：select 欄位名 from 表1 inner join 表2 on 連線條件 and 其他條件

例：顯示SMITH的名字和部門名稱

• select ename，dname from EMP，DEPT where EMP.deptno = DEPT.depton and ename = 'SMITH';
• select ename,dname from EMP inner join DEPT on EMP.deptno = DEPT.depton and ename = 'SMITH';

外連線：

外連線分為左外連線和右外連線

• 左外連線：如果聯合查詢，左側的表完全顯示，即左外連線
• 語法：select 欄位名 from 表名1 left join 表名2 on 連線條件
• 右外連線：如果聯合查詢，右側的表完全顯示，即右外連線
• 語法：select 欄位名 from 表名1 right join 表名2 on 連線條件

建立倆張表：

//學生表

• create table stu(id int,name varchar(30));
• insert into stu values(1,'jack'),(2,'tom'),(3,'kity'),(4,'nono');

//成績表：

• create table exam(id int,grade int);
• insert into exam values(1,56),(2,76),(11,28);

查詢所有學生的成績，如果這個學生沒有成績，也要將學生的個人資訊顯示出來

• select* from stu left join exam on stu.id = exam.id; 
• //當右邊的表沒有匹配項時，左邊表中的資料也會顯示出來

查詢所有成績，即使沒有對應的學生資訊，也要將成績顯示出來

• select* from stu right join exam on stu.id = exam.id;

索引：

• 提高資料庫的效能，索引是物美價廉的東西了。不用加記憶體，不用改程式，不用調sql，只要執行正確的create index，查詢速度就可能提高成百上千倍。但是天下沒有免費的午餐，查詢速度的提高是以插入，刪除，更新的速度為代價的，這些操作增加了大量的IO，所以他的價值在於，提高一個海量資料的檢索速度
• 常見的索引分為：主鍵索引，唯一索引，普通索引，全文索引

索引的基本原理：

對於沒有索引的表，我們查詢時，他會掃描整個表；而新增了索引以後，索引會形成一顆二叉樹

索引說明：

• 佔用磁碟空間
• 當新增一條記錄，除了新增到表中，還要維護二叉樹，速度有影響，但不大 
• 當我們新增一個索引，不能夠解決所有查詢問題時，需要分別給欄位建立索引：select* from EMP where ename='abcde';
• 索引是以空間換時間

建立索引：

1.主鍵索引：

  1）第一種方式：在建表的時候，直接在欄位後面指定primary key

• create table user1(id int primary key ,name varchar(30));

  2）第二種方式：在建立表的最好，指定某列為主鍵索引

• create table user2(id int,name varchar(30),primary key(id));

  3）第三種方式：建立表以後再新增索引

• create table user3(int id,name varchar(30));
• alter table user3 add primary key(id);

主鍵索引的特點：

1. 一個表中，最多隻有一個主鍵索引，當然可以使用複合主鍵
2. 主鍵索引效率高（主鍵不可重複）
3. 建立主鍵索引的列，它的值不能為null，且不能重複
4. 主鍵索引的列基本上是int

2.唯一索引的建立：

1）第一種方式：在表定義是，在某列後直接指定unique唯一屬性

• create table user4(id int primary key,name varchar(30) unique);

2）第二種方式：建立表示，在表後指定

• create table user5(id int primary key ,name varchar(30),unique);

3）第三種方式：表建立好以後新增

• create table user6(id int primary key,name varchar(30));
• alter table user6 add unique(name);

唯一索引的特點：

1. 一個表中可以有多個唯一索引
2. 查詢效率高
3. 如果在某一列建立唯一索引，必須保證這列資料不能重複
4. 如果一個唯一索引上指定了no nul，等價於主鍵索引

3.普通索引的建立

1）第一種方式：在表的定義最後，指定某列為索引

• create table user7(id int primary key,
• name varchar(20),
• email varchar(30),
• index(name)         //——在表定義的最後，指定為索引
• );

2）第二種方式：建立完表以後指定

• create table user8(id int primary key, name varchar(30),email varchar(30));
• alter table user8 add index(name);//——建立完表以後指定某列值為普通索引

3）第三種方式：建立一個名字叫 idx_name 的索引

• create table user9(id int primary key,name varchar(20),email varchar(30));
• create index idx_name on user9(name);

普通索引的特點：

1. 一個表可以有多個普通索引，普通索引在實際開發中用的比較多
2. 如果某列需要建立索引，但是該列有重複的值，那麼我們就應該使用普通索引

4.全文索引的建立

當對文章欄位或者大量文字的欄位進行檢索時，需要用到全文索引。預設全文索引支援英文，不支援中文

如果對中文進行全文檢索，可以使用sphinx的中文版

建立全文索引：

• create table articles(id int primary key,
• title varchar(200),
• boby text
• fulltext (title,boby)
• )engine=MyISAM;

如何使用全文索引：

• select * from articles where match（title，boby）against（‘database’);

 

查詢索引：

方法1：show keys from 表名；

• show keys from goods\G

方法2：show index from 表名；

方法3：desc 表名

刪除索引：

方法1：刪除主鍵：alter table 表名 drop primary；

方法2：刪除其他索引：alter table 表名 drop index 索引名；

• alter table user10 drop index idx_name

方法3：drop index 索引名 on 表名

• drop index name on user 8；

建立索引的原則：

• 比較頻繁作為查詢條件的欄位應該建立索引
• 唯一性太差的欄位不適合作為索引，即使頻繁作為查詢條件
• 更新非常頻繁的欄位不適合做為建立索引
• 不會出現在where子句中的欄位不該建立索引

 

事務：

事務是由一組dml語句組成的，在邏輯上具有相關性。這一組dml語句要麼全部成功，要麼全部失敗，是一個整體

事物的使用：

• 開始一個事物：start transaction；/ set autocommit = 0；
• 做一個儲存點：savepoint  儲存點名
• 回滾到儲存點：rollback to 儲存點名

 

注：

• 沒有設定儲存點也可以回滾，只是會回滾到事務的開始，直接使用rollback（前提是還沒有提交）
• 如果一個事務被提交（commit）了，則不可以回退
• 可以選擇回退到哪個儲存點

事務的隔離級別：

• 髒讀：一個客戶端（事務）讀取另一個客戶端（事務）沒有提交的修改資料
• 幻讀：同一個查詢在同一個事務裡多次進行，由於其它提交事務所做出的插入操作,每次返回不同的結果集，此時發生幻讀
• 不可重複讀：同一個查詢在同一個事務中多次進行，由於其他提交事務所做的修改/刪除，每次返回不同的結果集

如何設定事務的隔離級別：

• set session transaction isolation level read uncommitted； 

 檢視當前的隔離級別：

• select @@tx_isolation

MySQL預設的隔離級別是可重複讀，一般情況下不要修改

事務的ACID特性

• 原子性：事務是一個不可分割的工作單位，事務中的操作要麼全都發生，要麼全不發生
• 一致性：事務必須使資料庫從一個一致性狀態變到另一個一致性狀態
• 隔離性：多個使用者併發訪問資料庫時，資料庫為每一個使用者開啟的事務，不能被其他事務的運算元據感干擾，多個事務直接要相互隔離
• 永續性：事務一旦被提交，他對資料庫的修改時永久的