MySQL，你只需看這一篇文章就夠了！

lr1descent發表於2024-11-03
MySql

MySQL--DAY03

一個案例

查詢每一個員工的所在部門名稱？要求顯示員工名和部門名。

mysql> select * from emp;
+-------+--------+-----------+------+------------+---------+---------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | SAL     | COMM    | DEPTNO |
+-------+--------+-----------+------+------------+---------+---------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  800.00 |    NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 | 1600.00 |  300.00 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 | 1250.00 |  500.00 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 | 2975.00 |    NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 | 1250.00 | 1400.00 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 | 2850.00 |    NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |    NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 5000.00 |    NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 | 1500.00 |    0.00 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-05-23 | 1100.00 |    NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |  950.00 |    NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 | 3000.00 |    NULL |     20 |
|  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |    NULL |     10 |
+-------+--------+-----------+------+------------+---------+---------+--------+

mysql> select * from dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+

從emp表中取ename，從dept表中取dname，沒有條件限制最終查詢結果是？

ENAME				DNAME
-------------------------------------------
SMITH				ACCOUNTING	無效記錄
SMITH				RESEARCH		有效記錄
SMITH				SALES			無效記錄
SMITH				OPERATIONS	無效記錄

ALLEN				ACCOUNTING
ALLEN				RESEARCH
ALLEN				SALES
ALLEN				OPERATIONS

.....
56條記錄。

加個條件是為了達到4選1，也是為了資料的有效性。

	select 
		e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;

加條件只是為了避免笛卡爾積現象，只是為了查詢出有效的組合記錄。匹配的次數一次都沒有少，還是56次。

insert相關內容

insert插入多條資料

	mysql> desc t_user;
	+-------------+-------------+------+-----+---------+-------+
	| Field       | Type        | Null | Key | Default | Extra |
	+-------------+-------------+------+-----+---------+-------+
	| id          | int(11)     | YES  |     | NULL    |       |
	| name        | varchar(32) | YES  |     | NULL    |       |
	| birth       | date        | YES  |     | NULL    |       |
	| create_time | datetime    | YES  |     | NULL    |       |
	+-------------+-------------+------+-----+---------+-------+

一次可以插入多條記錄：

語法：insert into t_user(欄位名1, 欄位名2) values(), (), (), ();

		insert into t_user(id,name,birth,create_time) values
		(1,'zs','1980-10-11',now()), 
		(2,'lisi','1981-10-11',now()),
		(3,'wangwu','1982-10-11',now());
		
		mysql> select * from t_user;
        +------+--------+------------+---------------------+
        | id   | name   | birth      | create_time         |
        +------+--------+------------+---------------------+
        |    1 | zs     | 1980-10-11 | 2020-03-19 09:37:01 |
        |    2 | lisi   | 1981-10-11 | 2020-03-19 09:37:01 |
        |    3 | wangwu | 1982-10-11 | 2020-03-19 09:37:01 |
        +------+--------+------------+---------------------+

快速建表（瞭解即可）

原理：

​ 將一個查詢結果當做一張表新建！！！！！

​ 這個可以完成表的快速複製！！！！

​ 表建立出來，同時表中的資料也存在了！！！

mysql> create table emp2 as select * from emp;

create table mytable as select empno,ename from emp where job = 'MANAGER';

將查詢結果插入到一張表當中？（瞭解即可）

	create table dept_bak as select * from dept;
	mysql> select * from dept_bak;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	+--------+------------+----------+

	insert into dept_bak select * from dept; //很少用！

	mysql> select * from dept_bak;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	+--------+------------+----------+

快速刪除表中的資料？

【truncate比較重要，必須掌握】

	//刪除dept_bak表中的資料
	delete from dept_bak; //這種刪除資料的方式比較慢。

	mysql> select * from dept_bak;
	Empty set (0.00 sec)

delete語句刪除資料的原理？（delete屬於DML語句！！！）

表中的資料被刪除了，但是這個資料在硬碟上的真實儲存空間不會被釋放！！！

  • 缺點：刪除效率比較低。
  • 優點：支援回滾，後悔了可以再恢復資料！！！

truncate語句刪除資料的原理？

這種刪除效率比較高，表被一次截斷，物理刪除。

  • 缺點：不支援回滾。
  • 優點：快速。

用法：

truncate table dept_bak; //（這種操作屬於DDL操作。）

大表非常大，上億條記錄？？？？

刪除的時候，使用delete，也許需要執行1個小時才能刪除完！效率較低。

可以選擇使用truncate刪除表中的資料。只需要不到1秒鐘的時間就刪除結束。效率較高。

但是使用truncate之前，必須仔細詢問客戶是否真的要刪除，並警告刪除之後不可恢復！

truncate是刪除表中的資料，表還在！

刪除表操作？

drop table 表名; // 這不是刪除表中的資料，這是把表刪除。

增刪改表結構

定義

新增一個欄位，刪除一個欄位，修改一個欄位！！！

對錶結構的修改需要使用：alter

屬於DDL語句

DDL包括：create drop alter

​ 第一：在實際的開發中，需求一旦確定之後，表一旦設計好之後，很少的

進行表結構的修改。因為開發進行中的時候，修改表結構，成本比較高。

修改表的結構，對應的java程式碼就需要進行大量的修改。成本是比較高的。

這個責任應該由設計人員來承擔！

​ 第二：由於修改表結構的操作很少，所以我們不需要掌握，如果有一天

真的要修改表結構，你可以使用工具！！！！

修改表結構的操作是不需要寫到java程式中的。實際上也不是java程式設計師的範疇。

約束（非常重要！！！）

定義

約束對應的英語單詞：constraint

在建立表的時候，我們可以給表中的欄位加上一些約束，來保證這個表中資料的完整性、有效性！！！

約束的作用就是為了保證：表中的資料有效！！

約束分類

  • 非空約束：not null
  • 唯一性約束: unique
  • 主鍵約束: primary key （簡稱PK）
  • 外來鍵約束：foreign key（簡稱FK）
  • 檢查約束：check（mysql不支援，oracle支援）

我們這裡重點學習四個約束：

  • not null
  • unique
  • primary key
  • foreign key

非空約束（not null）

非空約束not null約束的欄位不能為NULL。

	drop table if exists t_vip;
	create table t_vip(
		id int,
		name varchar(255) not null  // not null只有列級約束，沒有表級約束！
	);
	insert into t_vip(id,name) values(1,'zhangsan');
	insert into t_vip(id,name) values(2,'lisi');

	insert into t_vip(id) values(3);
	ERROR 1364 (HY000): Field 'name' doesn't have a default value

小插曲：

xxxx.sql這種檔案被稱為sql指令碼檔案。

​ sql指令碼檔案中編寫了大量的sql語句。

​ 我們執行sql指令碼檔案的時候，該檔案中所有的sql語句會全部執行！

​ 批次的執行SQL語句，可以使用sql指令碼檔案。

​ 在mysql當中怎麼執行sql指令碼呢？

​ mysql> source D:\course\03-MySQL\document\vip.sql

​ 你在實際的工作中，第一天到了公司，專案經理會給你一個xxx.sql檔案，

​ 你執行這個指令碼檔案，你電腦上的資料庫資料就有了！

唯一約束（unique）

唯一性約束unique約束的欄位不能重複，但是可以為NULL。

	drop table if exists t_vip;
	create table t_vip(
		id int,
		name varchar(255) unique,
		email varchar(255)
	);
	insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
	insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
	insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com');
	select * from t_vip;

	insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
	ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

	insert into t_vip(id) values(4);
	insert into t_vip(id) values(5);
	+------+----------+------------------+
	| id   | name     | email            |
	+------+----------+------------------+
	|    1 | zhangsan | zhangsan@123.com |
	|    2 | lisi     | lisi@123.com     |
	|    3 | wangwu   | wangwu@123.com   |
	|    4 | NULL     | NULL             |
	|    5 | NULL     | NULL             |
	+------+----------+------------------+

name欄位雖然被unique約束了，但是可以為NULL。

新需求：name和email兩個欄位聯合起來具有唯一性！！！！

	drop table if exists t_vip;
	create table t_vip(
		id int,
		name varchar(255) unique,  // 約束直接新增到列後面的，叫做列級約束。
		email varchar(255) unique
	);
	這張表這樣建立是不符合我以上“新需求”的。
	這樣建立表示：name具有唯一性，email具有唯一性。各自唯一。

	以下這樣的資料是符合我“新需求”的。
	但如果採用以上方式建立表的話，肯定建立失敗，因為'zhangsan'和'zhangsan'重複了。
	insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
	insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');

	怎麼建立這樣的表，才能符合新需求呢？
		drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255),
			email varchar(255),
			unique(name,email) // 約束沒有新增在列的後面，這種約束被稱為表級約束。
		);
		insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
		insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
		select * from t_vip;

		name和email兩個欄位聯合起來唯一！！！
		insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
		ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'

	什麼時候使用表級約束呢？
		需要給多個欄位聯合起來新增某一個約束的時候，需要使用表級約束。

unique 和 not null可以聯合嗎？
	drop table if exists t_vip;
	create table t_vip(
		id int,
		name varchar(255) not null unique
	);

	mysql> desc t_vip;
	+-------+--------------+------+-----+---------+-------+
	| Field | Type         | Null | Key | Default | Extra |
	+-------+--------------+------+-----+---------+-------+
	| id    | int(11)      | YES  |     | NULL    |       |
	| name  | varchar(255) | NO   | PRI | NULL    |       |
	+-------+--------------+------+-----+---------+-------+

	在mysql當中，如果一個欄位同時被not null和 unique約束的話，
	該欄位自動變成主鍵欄位。（注意：oracle中不一樣！）

	insert into t_vip(id,name) values(1,'zhangsan');

	insert into t_vip(id,name) values(2,'zhangsan'); //錯誤了：name不能重複

	insert into t_vip(id) values(2); //錯誤了：name不能為NULL。

主鍵約束（primary key）

相關術語

  • 主鍵約束：就是一種約束。
  • 主鍵欄位：該欄位上新增了主鍵約束，這樣的欄位叫做：主鍵欄位
  • 主鍵值：主鍵欄位中的每一個值都叫做：主鍵值。

主鍵定義

主鍵值是每一行記錄的唯一標識。

主鍵值是每一行記錄的身份證號！！！

記住：任何一張表都應該有主鍵，沒有主鍵，表無效！！

主鍵的特徵：not null + unique（主鍵值不能是NULL，同時也不能重複！）

例項

怎麼給一張表新增主鍵約束呢？

		drop table if exists t_vip;
		// 1個欄位做主鍵，叫做：單一主鍵
		create table t_vip(
			id int primary key,  //列級約束
			name varchar(255)
		);
		insert into t_vip(id,name) values(1,'zhangsan');
		insert into t_vip(id,name) values(2,'lisi');

		//錯誤：不能重複
		insert into t_vip(id,name) values(2,'wangwu');
		ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'

		//錯誤：不能為NULL
		insert into t_vip(name) values('zhaoliu');
		ERROR 1364 (HY000): Field 'id' doesn't have a default value

可以這樣新增主鍵嗎，使用表級約束？

		drop table if exists t_vip;
		create table t_vip(
			id int,
			name varchar(255),
			primary key(id)  // 表級約束
		);
		insert into t_vip(id,name) values(1,'zhangsan');

		//錯誤
		insert into t_vip(id,name) values(1,'lisi');
		ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

表級約束主要是給多個欄位聯合起來新增約束？

		drop table if exists t_vip;
		// id和name聯合起來做主鍵：複合主鍵！！！！
		create table t_vip(
			id int,
			name varchar(255),
			email varchar(255),
			primary key(id,name)
		);
		insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
		insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');

		//錯誤：不能重複
		insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
		ERROR 1062 (23000): Duplicate entry '1-lisi' for key 'PRIMARY'

在實際開發中不建議使用：複合主鍵。建議使用單一主鍵！

因為主鍵值存在的意義就是這行記錄的身份證號，只要意義達到即可，單一主鍵可以做到。

複合主鍵比較複雜，不建議使用！！！

一個表中主鍵約束能加兩個嗎？

	drop table if exists t_vip;
	create table t_vip(
		id int primary key,
		name varchar(255) primary key
	);
	ERROR 1068 (42000): Multiple primary key defined
	結論：一張表，主鍵約束只能新增1個。（主鍵只能有1個。）

主鍵值建議使用：

  • int
  • bigint
  • char

等型別。

不建議使用：varchar來做主鍵。主鍵值一般都是數字，一般都是定長的！

主鍵除了：單一主鍵和複合主鍵之外，還可以這樣進行分類？

  • 自然主鍵：主鍵值是一個自然數，和業務沒關係。
  • 業務主鍵：主鍵值和業務緊密關聯，例如拿銀行卡賬號做主鍵值。這就是業務主鍵！

在實際開發中使用業務主鍵多，還是使用自然主鍵多一些？

  • 自然主鍵使用比較多，因為主鍵只要做到不重複就行，不需要有意義。
  • 業務主鍵不好，因為主鍵一旦和業務掛鉤，那麼當業務發生變動的時候，可能會影響到主鍵值，所以業務主鍵不建議使用。儘量使用自然主鍵。

在mysql當中，有一種機制，可以幫助我們自動維護一個主鍵值？

		drop table if exists t_vip;
		create table t_vip(
			id int primary key auto_increment, //auto_increment表示自增，從1開始，以1遞增！
			name varchar(255)
		);
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		insert into t_vip(name) values('zhangsan');
		select * from t_vip;

		+----+----------+
		| id | name     |
		+----+----------+
		|  1 | zhangsan |
		|  2 | zhangsan |
		|  3 | zhangsan |
		|  4 | zhangsan |
		|  5 | zhangsan |
		|  6 | zhangsan |
		|  7 | zhangsan |
		|  8 | zhangsan |
		+----+----------+

外來鍵約束（foreign key）

相關術語

  • 外來鍵約束：一種約束（foreign key）
  • 外來鍵欄位：該欄位上新增了外來鍵約束
  • 外來鍵值：外來鍵欄位當中的每一個值。

案例分析

業務背景：請設計資料庫表，來描述“班級和學生”的資訊？

第一種方案：班級和學生儲存在一張表中？？？

		t_student
		no(pk)				name			classno			classname
		----------------------------------------------------------------------------------
		1					jack			100			北京市大興區亦莊鎮第二中學高三1班
		2					lucy			100			北京市大興區亦莊鎮第二中學高三1班
		3					lilei			100			北京市大興區亦莊鎮第二中學高三1班
		4					hanmeimei	 	100			北京市大興區亦莊鎮第二中學高三1班
		5					zhangsan		101			北京市大興區亦莊鎮第二中學高三2班
		6					lisi			101			北京市大興區亦莊鎮第二中學高三2班
		7					wangwu			101			北京市大興區亦莊鎮第二中學高三2班
		8					zhaoliu			101			北京市大興區亦莊鎮第二中學高三2班

分析以上方案的缺點：

  1. 資料冗餘，空間浪費！！！！
  2. 這個設計是比較失敗的！

第二種方案：班級一張表、學生一張表？？

		t_class 班級表
		classno(pk)			classname
		------------------------------------------------------
		100					北京市大興區亦莊鎮第二中學高三1班
		101					北京市大興區亦莊鎮第二中學高三1班
	
		t_student 學生表
		no(pk)				name				cno(FK引用t_class這張表的classno)
		----------------------------------------------------------------
		1					jack				100
		2					lucy				100
		3					lilei				100
		4					hanmeimei			100
		5					zhangsan			101
		6					lisi				101
		7					wangwu				101
		8					zhaoliu				101

當cno欄位沒有任何約束的時候，可能會導致資料無效。可能出現一個102，但是102班級不存在。

所以為了保證cno欄位中的值都是100和101，需要給cno欄位新增外來鍵約束。

那麼：cno欄位就是外來鍵欄位。cno欄位中的每一個值都是外來鍵值。

注意：
t_class是父表
t_student是子表

​ 刪除表的順序？
​ 先刪子，再刪父。

​ 建立表的順序？
​ 先建立父，再建立子。

​ 刪除資料的順序？
​ 先刪子，再刪父。

​ 插入資料的順序？
​ 先插入父，再插入子。

思考：子表中的外來鍵引用的父表中的某個欄位，被引用的這個欄位必須是主鍵嗎？

不一定是主鍵，但至少具有unique約束。

測試：外來鍵可以為NULL嗎？

外來鍵值可以為NULL。

儲存引擎

定義

什麼是儲存引擎，有什麼用呢？

儲存引擎是MySQL中特有的一個術語，其它資料庫中沒有。（Oracle中有，但是不叫這個名字）

儲存引擎這個名字高階大氣上檔次。

實際上儲存引擎是一個表儲存/組織資料的方式。

不同的儲存引擎，表儲存資料的方式不同。

給表指定“儲存引擎”

可以在建表的時候給表指定儲存引擎。

CREATE TABLE `t_student` (
  `no` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `cno` int(11) DEFAULT NULL,
  PRIMARY KEY (`no`),
  KEY `cno` (`cno`),
  CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

在建表的時候可以在最後小括號的")"的右邊使用：

  • ENGINE來指定儲存引擎。
  • CHARSET來指定這張表的字元編碼方式。

結論：
mysql預設的儲存引擎是：InnoDB
mysql預設的字元編碼方式是：utf8

建表時指定儲存引擎，以及字元編碼方式。

	create table t_product(
		id int primary key,
		name varchar(255)
	)engine=InnoDB default charset=gbk;

檢視MySQL支援的儲存引擎

show engines \G

*************************** 1. row ***************************
      Engine: FEDERATED
     Support: NO
     Comment: Federated MySQL storage engine
Transactions: NULL
          XA: NULL
  Savepoints: NULL
*************************** 2. row ***************************
      Engine: MRG_MYISAM
     Support: YES
     Comment: Collection of identical MyISAM tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 3. row ***************************
      Engine: MyISAM
     Support: YES
     Comment: MyISAM storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 4. row ***************************
      Engine: BLACKHOLE
     Support: YES
     Comment: /dev/null storage engine (anything you write to it disappears
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 5. row ***************************
      Engine: CSV
     Support: YES
     Comment: CSV storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 6. row ***************************
      Engine: MEMORY
     Support: YES
     Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 7. row ***************************
      Engine: ARCHIVE
     Support: YES
     Comment: Archive storage engine
Transactions: NO
          XA: NO
  Savepoints: NO
*************************** 8. row ***************************
      Engine: InnoDB
     Support: DEFAULT
     Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
          XA: YES
  Savepoints: YES
*************************** 9. row ***************************
      Engine: PERFORMANCE_SCHEMA
     Support: YES
     Comment: Performance Schema
Transactions: NO
          XA: NO
  Savepoints: NO

MySQL中常見的儲存引擎

MyISAM儲存引擎

特徵

它管理的表具有以下特徵：

  1. 使用三個檔案表示每個表：
  • 格式檔案 — 儲存表結構的定義（mytable.frm）
  • 資料檔案 — 儲存錶行的內容（mytable.MYD）
  • 索引檔案 — 儲存表上索引（mytable.MYI）：索引是一本書的目錄，縮小掃描範圍，提高查詢效率的一 種機制。
  1. 可被轉換為壓縮、只讀表來節省空間

提示一下：

對於一張表來說，只要是主鍵，或者加有unique約束的欄位上會自動建立索引。

特點
  1. 可被轉換為壓縮、只讀表來節省空間。這是這種儲存引擎的優勢！！！！
  2. MyISAM不支援事務機制，安全性低。

InnoDB儲存引擎

這是mysql預設的儲存引擎，同時也是一個重量級的儲存引擎。

InnoDB支援事務，支援資料庫崩潰後自動恢復機制。

InnoDB儲存引擎最主要的特點是：非常安全。

特徵
  • 每個 InnoDB 表在資料庫目錄中以.frm 格式檔案表示
  • InnoDB 表空間 tablespace 被用於儲存表的內容（表空間是一個邏輯名稱。表空間儲存資料+索引。）
  • 提供一組用來記錄事務性活動的日誌檔案
  • 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滾)支援事務處理
  • 提供全 ACID 相容
  • 在 MySQL 伺服器崩潰後提供自動恢復
  • 多版本（MVCC）和行級鎖定
  • 支援外來鍵及引用的完整性，包括級聯刪除和更新
特點

InnoDB最大的特點就是支援事務：以保證資料的安全。效率不是很高，並且也不能壓縮，不能轉換為只讀，不能很好的節省儲存空間。

MEMORY儲存引擎

MEMORY 儲存引擎以前被稱為HEAP 引擎。

使用 MEMORY 儲存引擎的表，其資料儲存在記憶體中，且行的長度固定。

這兩個特點使得 MEMORY 儲存引擎非常快。

特徵
  • 在資料庫目錄內，每個表均以.frm 格式的檔案表示。
  • 表資料及索引被儲存在記憶體中。（目的就是快，查詢快！）
  • 表級鎖機制。
  • 不能包含 TEXT 或 BLOB 欄位。
特點
  1. MEMORY引擎優點：查詢效率是最高的。不需要和硬碟互動。
  2. MEMORY引擎缺點：不安全，關機之後資料消失。因為資料和索引都是在記憶體當中。

事務(非常重要！！！)

什麼是事務？

一個事務其實就是一個完整的業務邏輯。是一個最小的工作單元。不可再分。

什麼是完整的業務邏輯？

假設轉賬，從A賬戶向B賬戶中轉賬10000.

將A賬戶的錢減去10000（update語句）

將B賬戶的錢加上10000（update語句）

這就是一個完整的業務邏輯。

以上的操作是一個最小的工作單元，要麼同時成功，要麼同時失敗，不可再分。

這兩個update語句要求必須同時成功或者同時失敗，這樣才能保證錢是正確的。

只有DML語句才會有事務這一說，其它語句和事務無關！！！

insert

delete

update

只有以上的三個語句和事務有關係，其它都沒有關係。

因為只有以上的三個語句是資料庫表中資料進行增、刪、改的。

只要你的操作一旦涉及到資料的增、刪、改，那麼就一定要考慮安全問題。

資料安全第一位！！！

假設所有的業務，只要一條DML語句就能完成，還有必要存在事務機制嗎？

正是因為做某件事的時候，需要多條DML語句共同聯合起來才能完成，

所以需要事務的存在。如果任何一件複雜的事兒都能一條DML語句搞定，

那麼事務則沒有存在的價值了。

到底什麼是事務呢？

說到底，說到本質上，一個事務其實就是多條DML語句同時成功，或者同時失敗！

事務：就是批次的DML語句同時成功，或者同時失敗！

事務是怎麼做到多條DML語句同時成功和同時失敗的呢？

InnoDB儲存引擎：提供一組用來記錄事務性活動的日誌檔案

事務開啟了：

insert

insert

insert

delete

update

update

update

事務結束了！

在事務的執行過程中，每一條DML的操作都會記錄到“事務性活動的日誌檔案”中。

在事務的執行過程中，我們可以提交事務，也可以回滾事務。

提交事務？

清空事務性活動的日誌檔案，將資料全部徹底持久化到資料庫表中。

提交事務標誌著，事務的結束。並且是一種全部成功的結束。

回滾事務？

將之前所有的DML操作全部撤銷，並且清空事務性活動的日誌檔案

回滾事務標誌著，事務的結束。並且是一種全部失敗的結束。

怎麼提交事務，怎麼回滾事務？

  • 提交事務：commit; 語句
  • 回滾事務：rollback; 語句（回滾永遠都是隻能回滾到上一次的提交點！）

事務對應的英語單詞是：transaction

測試一下，在mysql當中預設的事務行為是怎樣的？

mysql預設情況下是支援自動提交事務的。（自動提交）

什麼是自動提交？

每執行一條DML語句，則提交一次！

這種自動提交實際上是不符合我們的開發習慣，因為一個業務通常是需要多條DML語句共同執行才能完成的，為

了保證資料的安全，必須要求同時成功之後再提交，所以不能執行一條就提交一條。

怎麼將mysql的自動提交機制關閉掉呢？

先執行這個命令：start transaction;

	演示事務：
		---------------------------------回滾事務----------------------------------------
		mysql> use bjpowernode;
		Database changed
		mysql> select * from dept_bak;
		Empty set (0.00 sec)

		mysql> start transaction;
		Query OK, 0 rows affected (0.00 sec)

		mysql> insert into dept_bak values(10,'abc', 'tj');
		Query OK, 1 row affected (0.00 sec)

		mysql> insert into dept_bak values(10,'abc', 'tj');
		Query OK, 1 row affected (0.00 sec)

		mysql> select * from dept_bak;
		+--------+-------+------+
		| DEPTNO | DNAME | LOC  |
		+--------+-------+------+
		|     10 | abc   | tj   |
		|     10 | abc   | tj   |
		+--------+-------+------+
		2 rows in set (0.00 sec)

		mysql> rollback;
		Query OK, 0 rows affected (0.00 sec)

		mysql> select * from dept_bak;
		Empty set (0.00 sec)


		---------------------------------提交事務----------------------------------------
		mysql> use bjpowernode;
		Database changed
		mysql> select * from dept_bak;
		+--------+-------+------+
		| DEPTNO | DNAME | LOC  |
		+--------+-------+------+
		|     10 | abc   | bj   |
		+--------+-------+------+
		1 row in set (0.00 sec)

		mysql> start transaction;
		Query OK, 0 rows affected (0.00 sec)

		mysql> insert into dept_bak values(20,'abc
		Query OK, 1 row affected (0.00 sec)

		mysql> insert into dept_bak values(20,'abc
		Query OK, 1 row affected (0.00 sec)

		mysql> insert into dept_bak values(20,'abc
		Query OK, 1 row affected (0.00 sec)

		mysql> commit;
		Query OK, 0 rows affected (0.01 sec)

		mysql> select * from dept_bak;
		+--------+-------+------+
		| DEPTNO | DNAME | LOC  |
		+--------+-------+------+
		|     10 | abc   | bj   |
		|     20 | abc   | tj   |
		|     20 | abc   | tj   |
		|     20 | abc   | tj   |
		+--------+-------+------+
		4 rows in set (0.00 sec)

		mysql> rollback;
		Query OK, 0 rows affected (0.00 sec)

		mysql> select * from dept_bak;
		+--------+-------+------+
		| DEPTNO | DNAME | LOC  |
		+--------+-------+------+
		|     10 | abc   | bj   |
		|     20 | abc   | tj   |
		|     20 | abc   | tj   |
		|     20 | abc   | tj   |
		+--------+-------+------+
		4 rows in set (0.00 sec)

事務的四個特性？

A：原子性

說明事務是最小的工作單元。不可再分。

C：一致性

所有事務要求，在同一個事務當中，所有操作必須同時成功，或者同時失敗，

以保證資料的一致性。

I：隔離性

A事務和B事務之間具有一定的隔離。

教室A和教室B之間有一道牆，這道牆就是隔離性。

A事務在操作一張表的時候，另一個事務B也操作這張表會那樣？？？

D：永續性

事務最終結束的一個保障。事務提交，就相當於將沒有儲存到硬碟上的資料儲存到硬碟上！

事務的隔離性？

A教室和B教室中間有一道牆，這道牆可以很厚，也可以很薄。這就是事務的隔離級別。

這道牆越厚，表示隔離級別就越高。

事務和事務之間的隔離級別有哪些呢？4個級別

  1. 讀未提交：read uncommitted（最低的隔離級別）《沒有提交就讀到了》

    什麼是讀未提交？

    事務A可以讀取到事務B未提交的資料

​ 存在的問題？

​ 髒讀現象！(Dirty Read)

​ 我們稱讀到了髒資料。

​ 這種隔離級別一般都是理論上的，大多數的資料庫隔離級別都是二檔起步！

  1. 讀已提交：read committed《提交之後才能讀到》

    什麼是讀已提交？

    事務A只能讀取到事務B提交之後的資料

​ 解決了什麼問題？

​ 解決了髒讀的現象

​

​ 存在什麼問題？

​ 不可重複讀取資料

​ 不可重複讀取資料是什麼意思？

​ 在事務開啟之後，第一次讀到的資料是3條，當前事務還沒有結束，可能第二次再讀取的時候，讀到的資料是 4條，3不等於4。這種現象稱為不可重複讀取。

​ 舉個例子：

示例 1：銀行賬戶餘額讀取
假設有兩個事務 事務 A 和 事務 B，操作同一個銀行賬戶的餘額。

初始狀態：賬戶餘額為 1000 元。

步驟：

1. 事務 A 開始，並讀取賬戶餘額，得到結果：1000 元。
2. 事務 B 開始，並將餘額增加到 2000 元，然後提交事務。
3. 事務 A 再次讀取賬戶餘額，此時得到結果：2000 元。

不可重複讀現象：
事務 A 在同一事務中，兩次讀取賬戶餘額，第一次是 1000 元，第二次卻是 2000 元。
因為隔離級別為“讀已提交”，所以事務 A 能夠讀取到事務 B 提交的更改，從而導致讀取結果不一致。

​ 這種隔離級別是比較真實的資料，每一次讀到的資料是絕對的真實。

​ oracle資料庫預設的隔離級別是：read committed

  1. 可重複讀：repeatable read《提交之後也讀不到，永遠讀取的都是剛開啟事務時的資料》

    什麼是可重複讀取？

    事務A開啟之後，不管是多久，每一次在事務A中讀取到的資料都是一致的。即使事務B將資料已經修改，並且提交了，事務A讀取到的資料還是沒有發生改變，這就是可重複讀。

​ 可重複讀解決了什麼問題？

​ 解決了不可重複讀取資料。

​

​ 可重複讀存在的問題是什麼？

​ 可以會出現幻影讀。

​ 每一次讀取到的資料都是幻象。不夠真實！

​

​ 早晨9點開始開啟了事務，只要事務不結束，到晚上9點，讀到的資料還是那樣！

​ 讀到的是假象。不夠絕對的真實。

​ mysql中預設的事務隔離級別就是這個！！！！！！！！！！！

  1. 序列化/序列化：serializable（最高的隔離級別）

    這是最高隔離級別，效率最低。解決了所有的問題。這種隔離級別表示事務排隊，不能併發

    synchronized，執行緒同步（事務同步）。每一次讀取到的資料都是最真實的，並且效率是最低的。

怎麼驗證隔離級別？

檢視隔離級別：
SELECT @@transaction_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql預設的隔離級別

被測試的表t_user
驗證：read uncommited
mysql> set global transaction isolation level read uncommitted;
事務A												事務B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
select * from t_user;
													start transaction;
													insert into t_user values('zhangsan');
select * from t_user;




驗證：read commited
mysql> set global transaction isolation level read committed;
事務A												事務B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
													start transaction;
select * from t_user;
													insert into t_user values('zhangsan');
select * from t_user;
													commit;
select * from t_user;






驗證：repeatable read
mysql> set global transaction isolation level repeatable read;
事務A												事務B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
													start transaction;
select * from t_user;
													insert into t_user values('lisi');
													insert into t_user values('wangwu');
													commit;
select * from t_user;





驗證：serializable
mysql> set global transaction isolation level serializable;
事務A												事務B
--------------------------------------------------------------------------------
use bjpowernode;
													use bjpowernode;
start transaction;
													start transaction;
select * from t_user;
insert into t_user values('abc');
													select * from t_user;

相關文章