表的内外連接配接
内連接配接
内連接配接實際上就是利用where子句對兩種表形成的笛卡兒積進行篩選。
eg：
select ename,dname
from emp,dept
where emp.deptno=dept.deptno
and ename=’smith’;
外連接配接
1. 左外連接配接：聯合查詢中，左側的表完全顯示
文法：select 字段
from 表名1 left join 表名2 on 連接配接條件
舉例，建兩個簡單的表：
create table stu(
id int,
name varchar(20)
);
create table exam(
id int,
grade int
);
插入資料：
insert into stu values(1,’hello’),(2,’kitty’),(3,’test’),(4,’kate’);
insert into exam values(1,20),(2,60),(3,90),(10,100);
查詢所有學生的成績，如果這個學生沒有成績，也要講學生的個人資訊顯示出來
select stu.id,name,grade
from stu left join exam
on stu.id = exam.id;
2. 右外連接配接：聯合查詢中，右側的表完全顯示
文法：
select 字段
from 表名1 right join 表名2 on 連接配接條件
舉例：
select grade,exam.id
from stu right join exam
on stu.id=exam.id
索引
提高資料庫的性能，查詢速度的提高，是索引的一大優點。但是它是以插入、更新、删除的速度為代價的，這些寫操作，增加了大量的IO。是以他它價值，在于提高一個海量資料的檢索速度。
常見索引分為：主鍵索引(primary key)，唯一索引(unique)，普通索引(index)，全文索引(fulltext)
索引的基本原理
那麼，索引是通過什麼機制使查詢速度變得如此快呢？
其實加上索引時，會改變我們資料的結構，可能有人都猜出來是二叉樹，舉例如下圖：
補充說明：
- 占用磁盤空間
- 當添加一條記錄，除了添加到表中，還要維護二叉樹，速度有影響，但不大。
- 當我們添加一個索引，不能夠解決所有查詢問題，需要分别給字段建立索引；
- 索引是以空間換時間
建立索引
1.建立主鍵索引
方式一：在建立表時，直接在字段名後指定primary key。
eg：create table 表名(id int primary key,name varchar(20));
方式二：在建立表的最後，指定某列或某幾列為主鍵索引。
eg：create table 表名(id int, name varchar(20),primary key);
方式三：建立表以後再添加主鍵。
eg：create table 表名(id int, name varchar(20));
alter table 表名 add primary key(id)；
主鍵索引的特點：
1. 一個表中，多有一個主鍵索引，可以使用複合主鍵
2. 主鍵索引的效率高（主鍵不可重複）
3. 建立主鍵索引的列，它的值不能為null，且不能重複
4. 主鍵索引的列基本上是int
2.建立唯一索引
方式一：在表定義時，在某列後直接指定unique唯一屬性。
create table 表名(id int primary key, name varchar(20) unique);
方式二：建立表時，在表的後面指定某列或某幾列為unique。
create table 表名(id int primary key, nam varchar(20), unique(name));
方式三：建立以後再添加唯一鍵。
create table 表名(id int primary key, name varchar(20));
alter table add unique(name);
唯一索引的特點：
1. 一個表中可以有多個唯一索引
2. 查詢效率高。
3. 如果在某一列上建立一個唯一索引，必須保證這列不能有重複資料。
4. 如果一個唯一索引上指定not null，等價于主鍵索引
3.建立普通索引
方式一：在表的定義最後，指定某列為索引
create table 表名(id int primary key, name varchar(20), index(name));
方式二：建立表完以後指定某列為普通索引
create table 表名(id int primary key, name varchar(20));
alter table add index(name);
方式三：建立一個索引名為idx_name的索引
create table 表名(id int primary key, name varchar(20));
create index idx_name on 表名(name);
普通索引的特點：
1. 一個表中可以有多個普通索引
2. 如果某列需要建立索引，但是該列存在重複資料，此時隻能建立普通索引
4.建立全文索引
當對文章字段或有大量文字的字段進行檢索時，會使用到全文索引。MySQL提供全文索引機制，但是有要求，要求表的存儲引擎必須是MyISAM，而且預設的全文索引支援英文，不支援中文
建立全文索引
-
建表：
create table articles(
id int not null primary key auto_increment,
title varchar(200),
body text,
FULLTEXT(title,body)
)engine=MyISAM;
-
添加資料
insert into articles (title,body)
values (‘MySQL Tutorial’,’DBMS stands for DataBase …’),
(‘Optimizing MySQL’,’In this tutorial we will show …’),
(‘MySQL vs. YourSQL’,’In the following database comparison …’), (‘MySQL Security’,’When configured properly, MySQL …’);
-
查詢有沒有database資料
(1)如果使用如下查詢方式，雖然查詢出資料，但是沒有使用到全文索引
(2)使用全文索引
通過explain 來分析這個sql語句
4. 删除索引
方法一：删除主鍵
alter table 表名 drop primary;
方法二：其他索引的删除
alter table 表名 drop index 索引名;
方法三：
drop index 索引名 on 表名;
drop index empno on emp;
建立索引的原則
1、比較頻繁查詢的字段應該建立索引來提高查詢效率
2、唯一性太差的字段不适合單獨建立索引
3、更新非常頻繁的字段不适合建立索引
4、字段根本不會出現在where字句中，該字段不應該建立索引
事務
事務就是一組dml語句組成，這些語句在邏輯上存在相關性，這一組dml語句具有原子性，要麼全部成功，要麼全部失敗。MySQL提供一種機制，保證我們達到這樣的效果。事務還規定不同的用戶端看到的資料是不相同的。
我們具體使用一下：
1. 建立一個表account：
2. 開啟事務：start transaction;
3. 設定儲存點：savepoint aaa;
4. 插入資料等基本操作
5.如果需要，可以回到儲存點
6. 繼續對表進行操作
7. 設定儲存點
8. 回到儲存點
9. 送出事務：commit;
注意：
- 事務一旦送出，則不可以回退。若沒設定儲存點，則回退到最開始的時候。
- 如果一個事務被送出了，則不可以回退（commit）
- 可以選擇回退到哪個儲存點
- InnoDB支援事務，MyISAM不支援事務
- 開始事務可以使 start transaction 也可以是 set autocommit = 0;
事務的隔離級别
當MySQL表被多個線程或者用戶端開啟各自事務操作資料庫中的資料時，MySQL提供了一種機制，可以讓不同的事務在操作資料時，具有隔離性，進而保證資料的一緻性。
| 隔離級别 | 髒讀 | 不可重複讀 | 幻讀 | 加鎖讀 |
| 讀未送出 | 是 | 是 | 是 | 不加鎖 |
| 讀已送出 | 否 | 是 | 是 | 不加鎖 |
| 可重複讀 | 否 | 否 | 否 | 不加鎖 |
| 可串行化 | 否 | 否 | 否 | 加鎖 |
如何設定事務的隔離級别 ？
文法：set session transaction isolation level read uncommitted;
預設的是可重複讀，自動避免了髒讀、不可重複讀、幻讀。
| 隔離級别 | 解釋 |
| 髒讀 | 一個用戶端（事務）會讀取到另外一個用戶端（事務）沒有送出的修改資料 |
| 不可重複讀 | 同一個查詢在同一個事務中多次進行，由于其他送出事務所做的修改或删除，每次傳回不同的結果集，此時發生不可重複讀。 |
| 幻讀 | 同一個查詢在同一個事務中多次進行，由于其他送出事務所做的插入操作，每次傳回不同的結果集，此時發生幻讀 |
示例：
開啟兩個用戶端：
其一插入資料：
另一個發生幻讀：
事務的ACID特性
- 原子性(Atomicity)：原子性是指事務是一個不可分割的工作機關，事務中的操作要麼都發生，要麼都不發生。
- 一緻性(Consistency):事務必須使資料庫從一個一緻性狀态變到另外一個一緻性狀态。
- 隔離性(Isolation)：事務的隔離性是多個使用者并發通路資料庫時，資料庫為每一個使用者開啟的事務，不能被其他事務的操作資料 所幹擾，多個并發事務之間要互相隔離。
- 持久性(Durability)：持久性是指一個事務一旦被送出，它對資料庫中的資料的修改就是永久性的，接下來即使資料庫發生故障也 不應該對其有任何影響。
視圖
視圖是一個虛拟表，其内容由查詢定義。同真實的表一樣，視圖包含一系列帶有名稱的列和行資料。視圖的資料變化會影響到基表，基表的資料變化也會影響到視圖。
建立視圖
文法：
create view 視圖名
as select語句
示例：
create view ename_dname_v
as
select ename,dname
from emp,dept
where emp.deptno = dept.deptno;
删除視圖
視圖和表的差別
- 表要占用磁盤空間，視圖不需要
- 視圖不能添加索引
- 使用視圖可以簡化查詢
- 視圖可以提高安全性