Pain is inevitable.Suffering is optional.
寫在前面
本文主要介紹了關于mysql資料庫的部分基礎知識，僅供初學者參考，很小一部分内容摘自網上前輩，已添加原連結供大家浏覽。文章較長，都是個人學習總結，由于時間倉促，部分内容略顯空洞，但還是希望給螢幕前的你帶來幫助(^_−)☆。
文章目錄
- 寫在前面
- 什麼是資料庫
-
- 登入資料庫
- SQL——結構化查詢語言
-
- SQL分類
-
- DDL
- DML
- DQL
-
- 常用的文法
- 模糊查詢 like
- 字段控制
- 去重與排序
- 聚合函數
- WHERE和HAVING 的差別
- 分頁查詢 LIMIT
- 限制
-
- 主鍵限制 PRIMARY KEY
- 唯一限制 UNIQUE
-
- 聯合限制
- 非空限制 NOT NULL
- 自增長限制 AUTO_INCREMENT
- 限制間的配合使用
- 枚舉限制資料 ENUM
- 外鍵限制
-
- ER圖
- 多表查詢
-
- 笛卡爾積（交叉連接配接）
- 内連接配接
- 外連接配接
- 内連接配接和外連接配接的差別
- 子查詢
- 自連接配接
- 複制表
- 并集 UNION
- 存儲過程
-
- 定義
- 文法
- delimiter $ 的含義
- 變量
-
- 變量的分類
- 參數類型
- IF條件判斷語句
- WHILE DO循環語句
-
- 其他幾種循環方式
- 控制循環的兩個關鍵字
- 觸發器 TRIGGER
-
- 觸發器的建立
-
- 插入
- 更新
- 删除
- 觸發器的删除
- 引擎InnoDB和MyISAM的差別
- 索引
-
- 索引的分類
- 建立索引
- 管理索引
- 測試索引的效率
- 視圖 view
-
- 概念
- 文法
- 視圖的意義
- 視圖資料操作的限制
什麼是資料庫
資料庫，簡而言之可視為電子化的檔案櫃——存儲電子檔案的處所，使用者可以對檔案中的資料進行新增、截取、更新、删除等操作。
MySQL 是最流行的關系型資料庫管理系統，在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關系資料庫管理系統)應用軟體之一。
本文主要介紹MySQL資料庫的一些基本知識。
登入資料庫
- 登陸資料庫：
mysql -uroot -p密碼
- 服務：系統背景程序
- 啟動服務：
net start mysql
- 停止服務：
net stop mysql
- 啟動服務：
SQL——結構化查詢語言
結構化查詢語言(Structured Query Language)簡稱SQL，是一種特殊目的的程式設計語言，是一種資料庫查詢和程式設計語言，用于存取資料以及查詢、更新和管理關系資料庫系統；同時也是資料庫腳本檔案的擴充名。
通過SQL語言可以操作所有的關系型資料庫，但是每種資料庫之間都會有一定的差異，我們稱之為“方言”。
SQL分類
-
DDL（Data Definition Language）：資料定義語言
用來定義資料庫對象：庫、表、列等
-
DML（Data Manipulation Language）：資料操作語言
用來定義資料庫記錄（增删改）
-
DCL（Data Control Language）：資料控制語言
用來定義通路權限和安全級别
-
DQL（Data Query Language）：資料查詢語言
用來查詢記錄（資料）
傳回頂部
DDL
注意SQL語言标簽不區分大小寫，但是資料庫對象名最好前後保持一緻。
- 操作資料庫
- 建立
CREATE DATABASE 資料庫名；
- 删除
DROP DATABASE 資料庫名；
- 修改
ALTER DATABASE 資料庫名 CHARACTER SET 要修改的字元集名稱;
- 查詢
- 查詢所有資料庫名稱
SHOW DATABASES;
- 查詢建立資料庫語句
SHOW CREATE DATABASE 資料庫名稱;
- 查詢所有資料庫名稱
- 建立
- 操作表
- 建立表
列類型 中文解釋 int 整型 double 浮點型 char 固定長度字元串類型 varchar 可變長度字元串類型 text 字元串類型 bolb 位元組類型 date 日期類型 yyyy-MM-dd datetime 日期時間類型 yyyy-MM-dd hh:mm:ss time 時間類型 timestamp 時間戳類型 預設為系統目前時間 有時我們會在列類型字段後面加上括号使其完整，比如double(5,2)表示最多5位，其中必須有2位小數，即最大值為999.99；char(2)表示限定兩個字元。
在建立資料庫之前，你應該想清楚資料庫結構：你需要什麼資料庫表，各資料庫表中有什麼樣的列。
下面我們來舉例說明怎樣建立一個表,比如我們要用一張表存儲所有的學生資訊：
CREATE TABLE students( sid int, sname varchar(20), sage int, ssex char(1) );這樣就建立了一個簡單的學生表，每個學生都有id,姓名，年齡，性别。
varchar 類型是可變長度的字元串類型，很适合于name列，因為列值是變長的。這些列的長度不必都相同，而且不必是20。你可以挑選從1到65535的任何長度，從中選擇一個最合理的值。如果後面你覺得長度不夠用，還可以通過SQL語句
ALTER TABLE
- 删除表
DROP TABLE 表名;
- 修改表
-
添加列
比如我們要給上面的students增加一個 班級 列：
ALTER TABLE students ADD (class varchar(10));
-
修改列類型
修改學生表中的性别列為char(2)：
ALTER TABLE students MODIFY ssex CHAR(2);
- 修改列名
修改ssex 為sex `ALTER TABLE students CHANGE ssex sex CHAR(2);` - 删除列
ALTER TABLE students DROP sid;
- 查詢表
SHOW TABLES;
DESC 表名;
注意，以上的操作都是基于選擇一個資料庫使用之上
USE 資料庫名;
即可。
傳回頂部
DML
我們先建立一個學生表：
CREATE TABLE students(
sid int,
sname varchar(20),
sage int,
ssex char(1)
);
-
給表中添加資料：
列名要和後面的值比對上，寫幾個列名，就要賦幾個值。
注意，字元類型和日期類型指派時要用引号括起來，單引号還是雙引号沒有要求。
簡寫形式：
這麼寫必須給所有的列都賦上值，不想指派的就寫null,否則會報錯。
- 删除表中資料：
- 删除表中所有資料
- 删除部分資料
-
TRUNCATE 删除資料
這麼做會删除所有記錄，先删除表，再建立表。
-
修改表中資料
比如我們要修改張三的資料：
傳回頂部
DQL
常用的文法
SELECT
selection_list /*要查詢的列名稱*/
FROM
table_list /*要查詢的表名稱*/
WHERE
condition /*行條件*/
GROUP BY
grouping_columns /*對結果分組*/
HAVING
condition /*分組後的行條件*/
ORDER BY
sorting_columns /*對結果排序*/
LIMIT
offset_start, row_count /*結果限定*/
模糊查詢 like
- 通配符
_ ：比對單個任意字元
%：比對多個任意字元
-
舉例
我們先來建立一張員工表供下面舉例使用：
-- 建表
CREATE TABLE emp(
empno INT, -- 編号
ename VARCHAR(50), -- 姓名
job VARCHAR(50), -- 職位
mgr INT, -- 上司編号
hiredate DATE, -- 入職日期
sal DECIMAL(7,2), -- 薪水
comm decimal(7,2), -- 獎金
deptno INT -- 部門編号
) ;
插入資料：
INSERT INTO emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
利用模糊查詢來操作：
-- 比如我現在想比對員工表中名字首個字母是S開頭的
SELECT name FROM emp WHERE name LIKE 'S%';
-- 比對名字第二個字母是a的
SELECT name FROM emp WHERE name LIKE '_a%';
傳回頂部
字段控制
- 修改字段的别名 AS (可以省略)
-- 比如我現在想給empno這個列名其别名id
SELECT empno AS id FROM emp;
-- 給運算字段起别名
select empno,ename,(sal+comm) as 總收入 from emp;
-- 給表起别名
SELECT empno,ename FROM emp AS 員工表;
這裡看不出起别名的用處，它更多的用在子查詢（大量的代碼嵌套）中，我們可以通過起别名來簡化代碼。
- 字段運算
- null參與的運算，結果都為null
- 上面求總收入的例子中，并不是所有的員工都有comm,如果comm為null的話，那麼sal+null就是null,顯然我們不想要這樣的結果。
- 要解決這個問題，得使用IFNULL這個字段，把null當做0來使用。
-- ifnull的作用示範
SELECT empno,ename,sal+IFNULL(comm,0) FROM emp;
傳回頂部
去重與排序
SECECT sal FROM emp ORDER BY sal;
-- 比如我現在想查詢工資的分布情況
sal
---------
800.00
950.00
1100.00
1250.00
1250.00
1300.00
1500.00
1600.00
2450.00
2850.00
2975.00
3000.00
3000.00
5000.00
-- 我們會得到重複的值,這顯然是不合理的，需要去重
SELECT DISTINCT sal FROM emp ORDER BY sal;
sal
---------
800.00
950.00
1100.00
1250.00
1300.00
1500.00
1600.00
2450.00
2850.00
2975.00
3000.00
5000.00
-- 排序預設為ASC升序排列，你也可以降序排列DESC
SELECT DISTINCT sal FROM emp ORDER BY sal DESC;
-- 不去重的情況下，比如我們要按工資大小排序來檢視所有人的資訊
SELECT * FROM emp ORDER BY sal DESC; -- *表示所有列
empno ename job mgr hiredate sal comm deptno
------ ------ --------- ------ ---------- ------- ------- --------
7839 KING PRESIDENT (NULL) 1981-11-17 5000.00 (NULL) 10
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 (NULL) 20
7902 FORD ANALYST 7566 1981-12-03 3000.00 (NULL) 20
7566 JONES MANAGER 7839 1981-04-02 2975.00 (NULL) 20
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 (NULL) 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 (NULL) 10
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7934 MILLER CLERK 7782 1982-01-23 1300.00 (NULL) 10
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 (NULL) 20
7900 JAMES CLERK 7698 1981-12-03 950.00 (NULL) 30
7369 SMITH CLERK 7902 1980-12-17 800.00 (NULL) 20
-- 我們發現工資會有重複的情況，那麼可以指定第二排序條件
SELECT * FROM emp ORDER BY sal DESC, ename ASC;
empno ename job mgr hiredate sal comm deptno
------ ------ --------- ------ ---------- ------- ------- --------
7839 KING PRESIDENT (NULL) 1981-11-17 5000.00 (NULL) 10
7902 FORD ANALYST 7566 1981-12-03 3000.00 (NULL) 20
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 (NULL) 20
7566 JONES MANAGER 7839 1981-04-02 2975.00 (NULL) 20
7698 BLAKE MANAGER 7839 1981-05-01 2850.00 (NULL) 30
7782 CLARK MANAGER 7839 1981-06-09 2450.00 (NULL) 10
7499 ALLEN SALESMAN 7698 1981-02-20 1600.00 300.00 30
7844 TURNER SALESMAN 7698 1981-09-08 1500.00 0.00 30
7934 MILLER CLERK 7782 1982-01-23 1300.00 (NULL) 10
7654 MARTIN SALESMAN 7698 1981-09-28 1250.00 1400.00 30
7521 WARD SALESMAN 7698 1981-02-22 1250.00 500.00 30
7876 ADAMS CLERK 7788 1987-05-23 1100.00 (NULL) 20
7900 JAMES CLERK 7698 1981-12-03 950.00 (NULL) 30
7369 SMITH CLERK 7902 1980-12-17 800.00 (NULL) 20
傳回頂部
聚合函數
聚合函數就是用來做縱向運算的函數，常見的有以下幾種：
| 聚合函數 | 解釋 |
|---|---|
| COUNT() | 統計指定列不為NULL的記錄行數 |
| MAX() | 計算指定列的最大值，如果指定列是字元串類型那麼使用字元串排序運算 |
| MIN() | 計算指定列的最小值，如果指定列是字元串類型那麼使用字元串排序運算 |
| SUM() | 計算指定列的數值和，如果指定列的類型不是數值類型，那麼計算結果為0 |
| AVG() | 計算指定列的平均值，如果指定列的類型不是數值類型，那麼計算結果為0 |
順便介紹分組查詢：GROUP BY()
GROUP BY()和聚合函數互相結合，可以查出很有意義的資料:
-- 查詢各部門的平均工資
SELECT deptno,AVG(sal)FROM emp GROUP BY deptno;
deptno AVG(sal)
------ -------------
10 2916.666667
20 2175.000000
30 1566.666667
-- 查詢每個部門工資大于1500的人數
SELECT deptno,COUNT(sal) FROM emp WHERE sal>1500 GROUP BY deptno;
-- where 是對分組前的條件限定 也就是說不滿足條件的 不參與分組
deptno count(sal)
------ ------------
10 2
20 3
30 2
傳回頂部
WHERE和HAVING 的差別
- WHERE：在分組之前對條件進行限定，不滿足條件，就不會參與分組。
- HAVING：在分組之後對結果進行篩選。
-- 查詢平均工資大于2000的部門
SELECT deptno,AVG(sal) AS avgsal FROM emp GROUP BY deptno HAVING avgsal>2000;
deptno avgsal
------ -------------
10 2916.666667
20 2175.000000
-- 查詢各個部門員工工資大于1500的平均工資，并且平均工資大于2000的部門
-- 思路：首先查詢各個部門，那肯定要按部門編号分組，分組的條件限定是 員工工資大于1500的材參與分組 計算出平均工資 然後再對結果進行篩選 篩選出平均工資大于2000的部門
SELECT deptno,AVG(sal) FROM emp WHERE sal>1500 GROUP BY deptno HAVING AVG(sal)>2000;
deptno avg(sal)
------ -------------
10 3725.000000
20 2991.666667
30 2225.000000
傳回頂部
分頁查詢 LIMIT
-- 文法：
LIMIT 開始的記錄索引,每一頁顯示的條數
-- 索引從0開始，開始的記錄索引 = (頁碼-1) * 每一頁顯示的條數。
SELECT * FROM emp LIMIT 0,5;
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
SELECT * FROM emp LIMIT 5,5;
empno ename job mgr hiredate sal comm deptno
------ ------ --------- ------ ---------- ------- ------ --------
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
SELECT * FROM emp LIMIT 10,5;
empno ename job mgr hiredate sal comm deptno
------ ------ ------- ------ ---------- ------- ------ --------
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
傳回頂部
限制
限制是對插入資料的一種限制，保證資料的有效性和完整性。
主要分為以下幾種：
- 主鍵限制
- 非空限制
- 唯一限制
- 自增長限制
- 外鍵限制
主鍵限制 PRIMARY KEY
特點：非空且唯一，而且一張表中隻能有一個主鍵
來看看主鍵的添加文法：
- 方式一：建表的同時添加限制
CREATE TABLE test(
userid INT,
username VARCHAR(32) PRIMARY KEY,
age INT
);
- 方式二：建表的同時在限制區域（字段聲明後）添加限制
CREATE TABLE test_1(
userid INT,
username VARCHAR(32),
age INT,
PRIMARY KEY(username)
);
- 方式三：建表之後，通過修改表結構來添加限制
CREATE TABLE test_2(
userid INT,
username VARCHAR(32),
age INT
);
ALTER TABLE test_2 add PRIMARY KEY(username);
加上了主鍵限制的字段在插入資料時就不能寫null,而且不能有重複值：
INSERT INTO test1 VALUES(01,'張三',19); -- 插入成功
INSERT INTO test1 VALUES(02,'張三',20); -- 插入失敗
-- 提示Duplicate entry '張三' for key 'PRIMARY'
INSERT INTO test1 VALUES(02,null,20);
-- 提示Column 'username' cannot be null
下面學習如何删除主鍵限制，分兩種情況：
- case 1 : 這個字段隻有主鍵限制
CREATE TABLE test(
userid INT ,
username VARCHAR(32) PRIMARY KEY,
age INT
);
ALTER TABLE test DROP PRIMARY KEY;
-- 這麼做隻删除了唯一限制，但是沒有删除非空限制
-- 删除非空限制可以通過修改字段來實作，在原來的字段聲明後加上NULL即可
ALTER TABLE test MODIFY username VARCHAR(32) NULL;
-- 這樣就解除了主鍵限制
- case 2：這個字段有自增長限制
CREATE TABLE test2(
userid INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(32) ,
age INT
);
-- 有自增長限制的字段一定是INT型，我們第一步先删除自增長限制
ALTER TABLE test2 CHANGE userid userid INT;
-- 然後再删除主鍵限制
ALTER TABLE test2 DROP PRIMARY KEY;
ALTER TABLE test2 MODIFY userid INT NULL;
傳回頂部
唯一限制 UNIQUE
被修飾的字段在插入資料時要求唯一（不限制null）：
文法與上面的類似：
CREATE TABLE test(
userid INT UNIQUE,
username VARCHAR(32) UNIQUE,
age INT
);
CREATE TABLE test(
userid INT,
username VARCHAR(32),
age INT,
UNIQUE(userid),
UNIQUE(username)
);
CREATE TABLE test(
userid INT,
username VARCHAR(32),
age INT,
);
ALTER TABLE test ADD UNIQUE(userid);
ALTER TABLE test ADD UNIQUE(username);
傳回頂部
聯合限制
值得注意的是：
ALTER TABLE test ADD UNIQUE(userid);
ALTER TABLE test ADD UNIQUE(username);
這種添加方式是給每個字段單獨添加唯一限制，此時：
INSERT INTO test values(01,'james',36); -- 插入資料成功
INSERT INTO test values(02,'james',25); -- 插入資料失敗
INSERT INTO test values(01,'curry',30); -- 插入資料失敗
-- userid和username都單獨設定了唯一限制，插入資料時，任意一個重複就會報錯
這顯然不是我們想要的結果，我們的重複資料應該是全部一樣才報錯，此時就要使用到聯合限制。
現在就可以插入部分重複的資料了：
INSERT INTO test values(01,'james',36); -- 插入資料成功
INSERT INTO test values(02,'james',25); -- 插入資料成功
INSERT INTO test values(01,'curry',30); -- 插入資料成功
INSERT INTO test values(01,'james',36); -- 插入資料失敗
如果使用主鍵限制兩個字段那就是加粗樣式聯合主鍵，這個主鍵的唯一性并不沖突。
傳回頂部
非空限制 NOT NULL
特點：被修飾的字段不能為null
CREATE TABLE test(
userid INT NOT NULL,
username VARCHAR(32) NOT NULL,
age INT NOT NULL,
);
這裡非空限制見名知意，不再贅述。
傳回頂部
自增長限制 AUTO_INCREMENT
特點：
- 被修飾的字段支援自增，一般為INT型的字段
- 被修飾的字段必須是鍵，一般是PRIMARY KEY
CREATE TABLE test(
id INT AUTO_INCREMENT PRIMARY KEY,
age INT,
username VARCHAR(32)
);
這樣的話我們在插入資料時就可以直接給userid賦NULL值，資料庫會自動幫助我們設定增長的數字來賦給userid：
INSERT INTO test VALUES(NULL,20,"張三");
INSERT INTO test VALUES(NULL,26,"李四");
INSERT INTO test VALUES(NULL,22,"王五");
id age username
------ ------ ----------
1 20 張三
2 26 李四
3 22 王五
當然你也可以指定id的值，那麼後面插入的資料的id值就會從你指定的這個數開始增長：
INSERT INTO test VALUES(10,23,"趙六");
INSERT INTO test VALUES(NULL,56,"鬼腳七");
id age username
------ ------ -----------
1 20 張三
2 21 李四
3 26 王五
10 23 趙六
11 56 鬼腳七
注意：你可以把這個自增長限制了解為隻能前進不能後退的指針，當你把這張表中以有的資料删除之後，指針并沒有後退，下次插入資料時，會從删除前的數字開始增長：
DELETE FROM test ;
INSERT INTO test VALUES(NULL,20,"張三");
id age username
------ ------ ----------
12 20 張三
如果想要更換增長的數字，就摧毀這張表，重建立立：
TRUNCATE test;
-- 摧毀這張表，重建立立一張新表
有了自增長限制，每一行資料都有一個獨有的值，那麼我們的增删改查工作就可以以id為條件來限制資料，非常友善，自增長限制通常和主鍵限制配合使用。
傳回頂部
限制間的配合使用
我們已經知道了主鍵具有限制資料非空且唯一的作用，但是主鍵隻能設定一個，那麼如果其他列也想要這種效果該怎麼辦呢？我們可以把非空限制和唯一限制來配合使用：
CREATE TABLE test(
id INT AUTO_INCREMENT PRIMARY KEY,
username varchar(20) UNIQUE NOT NULL,
age INT UNIQUE NOT NULL
);
這樣就給所有字段設定了非空且唯一的限制，任何字段的重複都是不允許的。
傳回頂部
枚舉限制資料 ENUM
這裡簡單介紹一下枚舉限制資料:
CREATE TABLE test(
id INT AUTO_INCREMENT PRIMARY KEY,
username varchar(20) ,
age INT ,
sex ENUM('男','女')
);
sex列就被限制隻能是 男 或者 女 中的一個。
傳回頂部
外鍵限制
ER圖
ER圖就是實體聯系圖。
- 用矩形表示實體
- 用橢圓表示屬性
- 用菱形表示關系
- ER圖的實體（entity）即資料模型中的資料對象，用長方體來表示，每個實體都有自己的實體成員（entity member）或者說實體對象（entity instance），實體成員（entity member）/實體執行個體（entity instance） 不需要出現在ER圖中。
- ER圖的屬性（attribute）即資料對象所具有的屬性，用橢圓形表示，屬性分為唯一屬性（ unique attribute）和非唯一屬性，唯一屬性指的是唯一可用來辨別該實體執行個體或者成員的屬性，用下劃線表示，一般來講實體都至少有一個唯一屬性。
- ER圖的關系（relationship）用來表現資料對象與資料對象之間的聯系，關系用菱形來表示。
ER圖中關聯關系有三種：
- 1對1（1:1） ：1對1關系是指對于實體集A與實體集B，A中的每一個實體至多與B中一個實體有關系；反之，在實體集B中的每個實體至多與實體集A中一個實體有關系。
- 1對多（1:N） ：1對多關系是指實體集A與實體集B中至少有N(N>0)個實體有關系；并且實體集B中每一個實體至多與實體集A中一個實體有關系。
- 多對多（M:N） ：多對多關系是指實體集A中的每一個實體與實體集B中至少有M(M>0)個實體有關系，并且實體集B中的每一個實體與實體集A中的至少N（N>0）個實體有關系。
我們可以使用ER圖來設計表，比如說我們現在要設計一個網絡商城，那麼必有的表就有：商品表，使用者表，訂單表。
這三張表之間是有一定聯系的，比如一個使用者可以有很多個訂單，每個訂單會有很多商品。
商品和訂單之間就是多對多的關系，為了處理多對多的關系，我們一般會引入一張中間表，存放這兩張表的主鍵，這樣就可以将多對多的關系拆分為兩個一對多的關系，為了保證資料的有效性和完整性，需要在中間表上添加兩個外鍵限制即可。使用者和訂單是一對多的關系，訂單和商品又是一對多的關系，在這裡，訂單表就是所謂的中間表。
開發中處理一對多的關系，我們會在多表中添加一個外鍵，名稱一般為主表的名稱_id，字段類型一般和主表的主鍵的類型保持一緻。
比如在這裡，我們往訂單表orders裡面添加外鍵user_id。
外鍵限制就是用來保證資料的有效性和完整性，多表一方叫從表，一表一方叫主表，我們通常還會在從表添加外鍵限制中去管理主表的主鍵。
- 格式:
alter table 多表名稱 add foreign key(外鍵名稱) references 一表名稱(主鍵);
- 例如:
alter table orders add foreign key(user_id) references user(id);
添加了外鍵限制之後 兩張表之間就有了聯系，有如下特點：
- 主表中不能删除從表中已經被引用的資料
- 從表中不能添加主表中不存在的資料
那麼如果我們想要删除主表中的資料怎麼辦呢？
- 級聯删除
ALTER TABLE orders ADD FOREIGN KET(user_id) REFERENCE user(id) ON DELETE CASCADE;
添加了級聯删除後，就可以直接删除主表中的資料，同時，從表中相關的資料會被一同删除
當然你也可以加上級聯更新：
ALTER TABLE orders ADD FOREIGN KET(user_id) REFERENCE user(id) ON DELETE CASCADE ON UPDATE CASCADE;
- 先把從表中相關的資料删除，在删除主表中的資料
在開發過程中，我們并不推薦使用外鍵限制，因為在後期測試工作量會很大。
我們現在來建立使用者表和訂單表來說明問題：
-- 主表
CREATE TABLE users(
id INT PRIMARY KEY AUTO_INCREMENT, -- 主表主鍵
username VARCHAR(20)
);
-- 從表
CREATE TABLE orders(
id INT PRIMARY KEY AUTO_INCREMENT, -- 從表主鍵
totalprice DOUBLE,
user_id INT
);
-- 給從表添加外鍵限制
ALTER TABLE orders ADD FOREIGN KEY(user_id) REFERENCES users(id);
-- 插入資料
INSERT INTO users VALUES (3,"張三");
INSERT INTO users VALUES (NULL,"李四");
INSERT INTO users VALUES (NULL,"王五");
INSERT INTO orders VALUES (NULL,1000,3);
INSERT INTO orders VALUES (NULL,1200,4);
INSERT INTO orders VALUES (NULL,3000,5);
INSERT INTO orders VALUES (NULL,5000,3);
INSERT INTO orders VALUES (NULL,1600,4);
-- 表結構
id username
------ ----------
3 張三
4 李四
5 王五
6 趙六
-- 表結構
id price user_id
------ ------ ---------
1 1314 3
2 1314 3
3 15 4
4 315 5
5 1014 (NULL)
傳回頂部
多表查詢
笛卡爾積（交叉連接配接）
在數學中，兩個集合X和Y的笛卡兒積（Cartesian product），又稱直積，表示為X × Y，是其第一個對象是X的成員而第二個對象是Y的一個成員的所有可能的有序對。
假設集合A={a,b}，集合B={0,1,2}，則兩個集合的笛卡爾積為{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。
在資料庫中，無條件的聯合查詢是沒有實際意義的，它會傳回這兩張表的笛卡爾積，我們用上面的使用者表和訂單表來示範一下：
-- 拼接兩個表
SELECT * FROM USER,orders;
-- 使用者表
id username
------ ----------
3 張三
4 李四
5 王五
6 趙六
-- 訂單表表
id price user_id
------ ------ ---------
1 1314 3
2 1314 3
3 15 4
4 315 5
5 1014 (NULL)
-- 笛卡爾積
id username id price user_id
------ -------- ------ ------ ---------
3 張三 1 1314 3
4 李四 1 1314 3
5 王五 1 1314 3
6 趙六 1 1314 3
3 張三 2 1314 3
4 李四 2 1314 3
5 王五 2 1314 3
6 趙六 2 1314 3
3 張三 3 15 4
4 李四 3 15 4
5 王五 3 15 4
6 趙六 3 15 4
3 張三 4 315 5
4 李四 4 315 5
5 王五 4 315 5
6 趙六 4 315 5
3 張三 5 1014 (NULL)
4 李四 5 1014 (NULL)
5 王五 5 1014 (NULL)
6 趙六 5 1014 (NULL)
簡單來講笛卡爾積就是把兩張表的列相加，行相乘，即A表中的每一行都會和B表中的每一行資料拼接，那麼自然地，B表中的每一行資料就會和A表中的每一行資料拼接。
這樣就完成了沒有條件的多表聯合查詢，我們給它加上條件，就會得到具有實際意義的資料。
傳回頂部
内連接配接
- 顯式的内連接配接
select a.*,b.* from a,b inner join b on ab的連接配接條件
inner join on 不符合條件的資料不展示,形成的中間表為兩個表經過ON條件過濾後的笛卡爾積
- 隐式的内連接配接
select a.*,b.* from a,b where 連接配接條件
從左表中取出每一條記錄，去右表中與所有的記錄進行比對；比對必須是某個條件是左表中與右表中相同，才會保留結果，否則不保留；
-- 查詢所有使用者的訂單詳情，沒有訂單顯示null
-- 左連接配接
SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=orders.`user_id`;
-- 右連接配接
SELECT user.*,orders.* FROM orders RIGHT OUTER JOIN USER ON user.`id`=orders.`user_id`;
id username id price user_id
------ -------- ------ ------ ---------
3 張三 1 1314 3
3 張三 2 1314 3
4 李四 3 15 4
5 王五 4 315 5
6 趙六 (NULL) (NULL) (NULL)
傳回頂部
外連接配接
- 左外連接配接
SELECT a.*,b.* FROM a left outer join b on 連接配接條件;
- 右外連接配接
SELECT a.*,b.* FROM a right outer join b on 連接配接條件;
outer 可以省略。
right join(右聯接) 傳回包括右表中的所有記錄和左表中聯結字段相等的記錄；
-- 查詢所有使用者的訂單詳情，沒有訂單顯示null
-- 左外連接配接
SELECT user.*,orders.* FROM USER LEFT OUTER JOIN orders ON user.`id`=orders.`user_id`;
-- 右外連接配接
SELECT user.*,orders.* FROM orders RIGHT OUTER JOIN USER ON user.`id`=orders.`user_id`;
id username id price user_id
------ -------- ------ ------ ---------
3 張三 1 1314 3
3 張三 2 1314 3
4 李四 3 15 4
5 王五 4 315 5
6 趙六 (NULL) (NULL) (NULL)
-- 查詢所有訂單的使用者詳情
-- 右連接配接
SELECT orders.*,user.* FROM USER RIGHT OUTER JOIN orders ON user.`id`=orders.`user_id`;
-- 左連接配接
SELECT orders.*,user.* FROM orders LEFT OUTER JOIN USER ON user.`id`=orders.`user_id`;
id price user_id id username
------ ------ ------- ------ ----------
1 1314 3 3 張三
2 1314 3 3 張三
3 15 4 4 李四
4 315 5 5 王五
5 1014 (NULL) (NULL) (NULL)
傳回頂部
内連接配接和外連接配接的差別
有兩種基本類型的的連接配接，inner和outer連接配接。兩種類型的主要差別在于，即使是在連接配接條件不滿足的情況下，外部連接配接也會在結果集内傳回行，而内部連接配接不會在結果集類傳回行 。
當外部連接配接不滿足連接配接條件時，通常傳回一個表中的列，但是第二個表中沒有傳回值就傳回null。
傳回頂部
子查詢
一個主查詢的條件要依賴于另外一個子查詢的結果。
-- 一. 查詢出訂單的價格大于300的所有使用者資訊。
-- 分步查詢
-- 先找出價格大于300的訂單的使用者ID 3 5
SELECT orders.`user_id` FROM orders WHERE orders.`price`>300;
-- 再通過id找出使用者資訊
SELECT user.* FROM USER WHERE user.id IN (3,5,NULL);
-- 合并-子查詢
SELECT user.* FROM USER WHERE user.id IN (SELECT orders.`user_id` FROM orders WHERE orders.`price`>300);
id username
------ ----------
3 張三
5 王五
-- 二.查詢訂單價格大于300的訂單資訊及相關使用者的資訊。
-- 内連接配接
SELECT user.*,orders.* FROM USER,orders WHERE user.id=orders.`user_id` AND orders.`price`>300;
-- 子查詢
-- 找出所有訂單價格大于三百的訂單資訊作為臨時表
SELECT user.*,tmp.* FROM USER,(SELECT * FROM orders WHERE price>300) AS tmp WHERE user.`id`=tmp.user_id;
id username id price user_id
------ -------- ------ ------ ---------
3 張三 1 1314 3
3 張三 2 1314 3
5 王五 4 315 5
傳回頂部
自連接配接
有時候我們需要在一張表中查詢出有實際意義的資料，那就需要把它适時地看作兩張表：
我們先來建立一張員工表：
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
) ;
-- 插入資料
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
-- 員工表
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
查詢emp表中所有員工的上司的姓名：
我們假設有兩張表，一張員工表，一張上司表，如果員工表的mgr=上司表中的empno，就說明這個empno對應的人名就是他的上司。
-- 查詢
SELECT a.empno AS 員工編号,a.ename AS 員工,b.empno AS 上司編号,b.ename AS 上司
FROM emp AS a,emp AS b WHERE a.mgr = b.empno ORDER BY a.`empno`;
-- 傳回
員工編号 員工 上司編号 上司
------------ ------ ------------ --------
7369 SMITH 7902 FORD
7499 ALLEN 7698 BLAKE
7521 WARD 7698 BLAKE
7566 JONES 7839 KING
7654 MARTIN 7698 BLAKE
7698 BLAKE 7839 KING
7782 CLARK 7839 KING
7788 SCOTT 7566 JONES
7844 TURNER 7698 BLAKE
7876 ADAMS 7788 SCOTT
7900 JAMES 7698 BLAKE
7902 FORD 7566 JONES
7934 MILLER 7782 CLARK
傳回頂部
複制表
開發中我們會經常用到舊表中的結構或者資料，學會怎樣複制表會很友善：
- 列名和資料都複制
-- 舉例來說比如我們要複制上面的那張員工表
CREATE TABLE myEmp AS SELECT * FROM emp;
-- 或者
CREATE TABLE myEmp AS SELECT * FROM emp WHERE TRUE;
-- 或者
CREATE TABLE myEmp AS SELECT * FROM emp WHERE 1=1;
-- 結論：隻要在WHERE後面跟上TRUE或者結果為TRUE的判斷式
-- 當然，WHERE也可以省略，預設複制列名和資料
- 隻複制列名，不複制資料
CREATE TABLE myEmp2 AS SELECT * FROM emp WHERE FALSE;
-- 或者
CREATE TABLE myEmp2 AS SELECT * FROM emp WHERE 1=2;
-- 結論：隻要在WHERE後面跟上TRUE或者結果為FALSE的判斷式
- 複制個别列名
- 複制一張臨時表
傳回頂部
并集 UNION
-- 查詢10号和20号部門的員工資訊
-- 方法一：
SELECT * FROM emp WHERE deptno = 10 OR deptno = 20;
-- 方法二：
SELECT * FROM emp WHERE deptno = 10
UNION
SELECT * FROM emp WHERE deptno = 20;
empno ename job mgr hiredate sal comm deptno
------ ------ --------- ------ ---------- ------- ------ --------
7782 CLARK MANAGER 7839 1981-06-09 2450.00 (NULL) 10
7839 KING PRESIDENT (NULL) 1981-11-17 5000.00 (NULL) 10
7934 MILLER CLERK 7782 1982-01-23 1300.00 (NULL) 10
7369 SMITH CLERK 7902 1980-12-17 800.00 (NULL) 20
7566 JONES MANAGER 7839 1981-04-02 2975.00 (NULL) 20
7788 SCOTT ANALYST 7566 1987-04-19 3000.00 (NULL) 20
7876 ADAMS CLERK 7788 1987-05-23 1100.00 (NULL) 20
7902 FORD ANALYST 7566 1981-12-03 3000.00 (NULL) 20
傳回頂部
存儲過程
定義
存儲過程（Stored Procedure）是在大型資料庫系統中，一組為了完成特定功能的SQL 語句集，存儲在資料庫中，經過第一次編譯後調用不需要再次編譯，使用者通過指定存儲過程的名字并給出參數（如果該存儲過程帶有參數）來執行它。
傳回頂部
文法
- 建立存儲過程
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
PROCEDURE `庫名`.`存儲過程名`()
/*LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'*/
BEGIN
方法體
END$$
DELIMITER ;
- 調用存儲過程
- 删除存儲過程
- 檢視存儲過程
show procedure status\G; -- 檢視所有的存儲過程狀态
show create procedure 存儲過程名\G; -- 檢視建立存儲過程的語句
傳回頂部
delimiter $ 的含義
delimiter是mysql中用來定義結束标記的，mysql用戶端中結束标記預設是分号’;’。如果一次輸入的語句較多，并且語句中間有分号，這時需要新指定一個特殊的結束符。delimiter $ 表示mysql用$表示mysql語句結束，過程結束後肯定會有一句delimiter ；恢複成預設的。
傳回頂部
變量
變量的分類
https://blog.csdn.net/jq_ak47/article/details/52087484
- 全局變量：MYSQL内置變量，存在任何一個會話當中，會話是你跟資料庫建立的一次連接配接，全局變量影響伺服器整體操作。當伺服器啟動時，它将所有全局變量初始化為預設值。這些預設值可以在選項檔案中或在指令行中指定的選項進行更改。要想更改全局變量，必須具有SUPER權限。全局變量作用于server的整個生命周期，但是不能跨重新開機。即重新開機後所有設定的全局變量均失效。要想讓全局變量重新開機後繼續生效，需要更改相應的配置檔案。
使用者變量：在用戶端連結到資料庫執行個體整個過程中使用者變量都是有效的。mysql中使用者變量不用事前申明，在用的時候直接用“@變量名”使用就可以了，select語句一般用來輸出使用者變量，比如select
@變量名,用于輸出資料源不是表格的資料。
- 會話變量：伺服器為每個連接配接的用戶端維護一系列會話變量。在用戶端連接配接資料庫執行個體時，使用相應全局變量的目前值對用戶端的會話變量進行初始化。設定會話變量不需要特殊權限，但用戶端隻能更改自己的會話變量，而不能更改其它用戶端的會話變量。會話變量的作用域與使用者變量一樣，僅限于目前連接配接。當目前連接配接斷開後，其設定的所有會話變量均失效。
- 局部變量：定義在存儲過程中或者函數中的變量，局部變量隻在目前begin/end代碼塊中有效
傳回頂部
參數類型
- IN 輸入參數
- OUT 輸出參數
- INOUT 輸入輸出參數
來看幾個執行個體：
- 案例一：根據傳進來的員工編号傳回員工的所有資訊
-- 存儲過程中可以存放一些簡單的邏輯
-- 這個存儲過程用來根據傳進來的員工編号傳回員工的所有資訊
DELIMITER $$
CREATE
PROCEDURE `mydemo`.`mypro1`(IN num INT) -- 輸入參數的定義
BEGIN
SELECT * FROM emp WHERE empno=num;
END$$
DELIMITER;
-- 查詢編号為7369的員工資訊
CALL mypro1(7369);
-- 傳回
empno ename job mgr hiredate sal comm deptno
------ ------ ------ ------ ---------- ------ ------ --------
7369 SMITH CLERK 7902 1980-12-17 800.00 (NULL) 20
- 案例二：輸出helloworld
DELIMITER $$
CREATE
PROCEDURE `mydemo`.`mypro2`(OUT mytext VARCHAR(20)) -- 輸出參數的定義
BEGIN
SET mytext = 'helloworld';
END$$
DELIMITER;
-- 我們需要定義一個使用者變量去接收程式傳回的值
SET @s; --定義
CALL mypro2(@s); -- 調用程式
SELECT @s; -- 查詢
-- 傳回
@s
------------
helloworld
- 案例三：删除指定編号的員工資訊，并傳回剩餘員工數
DELIMITER $$
CREATE
PROCEDURE mypro3(INOUT num INT) -- 形參的定義
BEGIN
DELETE FROM emp WHERE empno = num;
SELECT COUNT(*) FORM emp INTO num;
END$$
DELIMITER;
SET @num=7369;
CALL mypro3(@num);
SELECT @num;
傳回頂部
IF條件判斷語句
案例：輸入數字傳回星期
DELIMITER $$
CREATE
PROCEDURE proIf(IN num INT,OUT result VARCHAR(10)) -- 形參的定義
BEGIN
IF num=1 THEN
SET result = '星期1';
ELSEIF num=2 THEN
SET result = '星期2';
ELSEIF num=3 THEN
SET result = '星期3';
ELSEIF num=4 THEN
SET result = '星期4';
ELSEIF num=5 THEN
SET result = '星期5';
ELSEIF num=6 THEN
SET result = '星期6';
ELSEIF num=7 THEN
SET result = '星期7';
ELSE
SET result = 'undefined'
END IF;-- 結束條件語句
END$$
DELIMITER;
傳回頂部
WHILE DO循環語句
案例：求1~100的和
DELIMITER $$
CREATE
PROCEDURE sum1(IN num INT,OUT result INT) -- 形參的定義
BEGIN
DECLARE i INT DEFAULT 1; -- 定義一個局部變量預設值為1
DECLARE sum INT DEFAULT 0; -- 定義一個局部變量預設值為0
WHILE(i<=num) DO
SET sum = sum + i;
SET i = i+1;
END WHILE; -- 記得結束循環
SET result = num;
END$$
DELIMITER;
CALL myproWhile(100,@r);
SELECT @r;
傳回頂部
其他幾種循環方式
-- 第二種 loop 循環
/*loop 循環文法：
loop_name:loop
if 條件 THEN -- 滿足條件時離開循環
leave loop_name; -- 和 break 差不多都是結束訓話
end if；
end loop;
*/
-- 執行個體：
DELIMITER $$
CREATE PROCEDURE sum2(a INT)
BEGIN
DECLARE SUM INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
loop_name:LOOP -- 循環開始
IF i>a THEN
LEAVE loop_name; -- 判斷條件成立則結束循環 好比java中的 boeak
END IF;
SET SUM=SUM+i;
SET i=i+1;
END LOOP; -- 循環結束
SELECT SUM; -- 輸出結果
END $$
DELIMITER;
-- 執行存儲過程
CALL sum2(100);
-- 删除存儲過程
DROP PROCEDURE IF EXISTS sum2;
-- 第三種 repeat 循環
/*repeat 循環文法
repeat
循環體
until 條件 end repeat;
*/
-- 執行個體；
DELIMITER $$
CREATE PROCEDURE sum3(a INT)
BEGIN
DECLARE SUM INT DEFAULT 0;
DECLARE i INT DEFAULT 1;
REPEAT -- 循環開始
SET SUM=SUM+i;
SET i=i+1;
UNTIL i>a END REPEAT; -- 循環結束
SELECT SUM; -- 輸出結果
END$$
DELIMITER;
-- 執行存儲過程
CALL sum3(100);
傳回頂部
控制循環的兩個關鍵字
-
leave 相當于 break
-
iterate相當于continue
傳回頂部
觸發器 TRIGGER
觸發器是與表有關的資料庫對象，在滿足定義條件時觸發，并執行觸發器中定義的語句集合。
觸發器盡量少的使用，因為不管如何，它還是很消耗資源，如果使用的話要謹慎的使用，确定它是非常高效的：觸發器是針對每一行的；對增删改非常頻繁的表上切記不要使用觸發器，因為它會非常消耗資源。
觸發器是資料庫中的一個對象，相當于JS中的監聽器，觸發器可以監聽增删改 三個動作比如說我想監聽一張表，隻要我增删改了這張表中的資料，我就可以觸發這個觸發器，去往另外一張表中記錄一下日志。
觸發器的建立
DELIMITER $$
CREATE
/*[DEFINER = { user | CURRENT_USER }]*/
TRIGGER `庫名`.`觸發器名` BEFORE/AFTER INSERT/UPDATE/DELETE --可選擇的事件名
ON `目标表`.`<Table Name>`
FOR EACH ROW BEGIN -- 針對每一行
-- 具體實作
END$$
DELIMITER ;
-- BEFORE 行為發生之前就觸發
-- AFTER 行為發生之後觸發
比如少我們現在有一張學生表student，和一張日志表logger：
-- 建立學生表
CREATE TABLE student(sname VARCHAR(10),sage INT);
-- 插入資料
INSERT INTO student VALUES("張三",20);
INSERT INTO student VALUES("李四",30);
INSERT INTO student VALUES("王五",40);
-- 表
sname sage
------ --------
張三 20
李四 30
王五 40
-- 建立logger表來寫日志
CREATE TABLE logger(sname VARCHAR(10),sage INT,text varchar(100),time timestamp);
傳回頂部
插入
現在要求當我學生表中新插入資料時，就往日志表中寫一條記錄：
-- 建立觸發器
DELIMITER$$
CREATE
TRIGGER tri AFTER INSERT
ON student
FOR EACH ROW
BEGIN
INSERT INTO logger VALUES(new.sname,new.sage,"插入了一行資料",NULL);
END$$
DELIMITER;
-- 現在往學生表中插入一條資料
INSERT INTO student VALUES("趙六",24);
-- 學生表
sname sage
------ --------
張三 20
李四 30
王五 40
趙六 24
-- 日志
sname sage text time
------ ------ --------------------- ---------------------
趙六 24 插入了一行資料 2019-03-14 19:09:36
傳回頂部
更新
當學生表中資料發生改變，往日志表中寫一條記錄:
DELIMITER$$
CREATE
TRIGGER tri2 AFTER UPDATE
ON student
FOR EACH ROW
BEGIN
INSERT INTO logger VALUES(old.sname,old.sage,"此行資料更新",NULL);
INSERT INTO logger VALUES(new.sname,new.sage,"更新後的資料",NULL);
END$$
DELIMITER;
-- 更新資料
UPDATE student SET sname="張三三",sage=19 WHERE sname = "張三";
-- 學生表
sname sage
------ --------
張三三 19
李四 30
王五 40
趙六 24
-- 日志
sname sage text time
--------- ------ --------------------- ---------------------
趙六 24 插入了一行資料 2019-03-14 19:09:36
張三 20 此行資料更新 2019-03-14 19:26:48
張三三 19 更新後的資料 2019-03-14 19:26:48
傳回頂部
删除
當學生表中的資料被删除，往日志表中寫一條記錄：
DELIMITER$$
CREATE
TRIGGER tri3 AFTER DELETE
ON student
FOR EACH ROW
BEGIN
INSERT INTO logger VALUES(old.sname,old.sage,"此行資料删除",NULL);
END$$
DELIMITER;
-- 删除
DELETE FROM student WHERE sname='張三三';
-- 學生表
sname sage
--------- --------
李四 30
王五 40
趙六 24
-- 日志
sname sage text time
--------- ------ --------------------- ---------------------
趙六 24 插入了一行資料 2019-03-14 19:09:36
張三 20 此行資料更新 2019-03-14 19:26:48
張三三 19 更新後的資料 2019-03-14 19:26:48
張三三 19 此行資料删除 2019-03-14 19:44:45
傳回頂部
觸發器的删除
DROP TRIGGER tri;
DROP TRIGGER tri2;
DROP TRIGGER tri3;
引擎InnoDB和MyISAM的差別
這兩種都是mysql的資料庫的存儲引擎的類型，一個支援事物等資料庫的進階功能，一個不支援。兩個各有用處，各有優劣。
- InnoDB不支援FULLTEXT類型的索引。
- InnoDB中不儲存表的 具體行數，也就是說，執行select count() from table時，InnoDB要掃描一遍整個表來計算有多少行，但是MyISAM隻要簡單的讀出儲存好的行數即可。注意的是，當count()語句包含 where條件時，兩種表的操作是一樣的。
- 對于AUTO_INCREMENT類型的字段，InnoDB中必須包含隻有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯合索引。
- DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的删除。
- LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入資料後再改成InnoDB表，但是對于使用的額外的InnoDB特性（例如外鍵）的表不适用。
另外，InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能确定要掃描的範圍，InnoDB表同樣會鎖全表。簡而言之：
MyISAM類型不支援事務處理等進階處理，而InnoDB類型支援。 MyISAM類型的表強調的是性能，其執行數度比InnoDB類型更快，但是不提供事務支援，而InnoDB提供事務支援，外鍵等進階資料庫功能。
任何一種表都不是萬能的，隻用恰當的針對業務類型來選擇合适的表類型，才能最大的發揮MySQL的性能優勢.
下面來看一些常見的關于引擎的操作：
- 檢視表資訊，裡面包括使用的引擎類型，字元編碼，表結構等
mysql> show create table 表名;
- 可以執行以下指令來切換非事務表到事務（資料不會丢失），innodb表比myisam表更安全：
alter table 表名 type=innodb;
- innodb表不能用
repair table
myisamchk -r table_name
check table 表名
mysqlcheck [OPTIONS] database [tables]
-
啟動mysql資料庫的指令行中添加了以下參數可以使新釋出的mysql資料表都預設為使用事務（
隻影響到create語句。）
default-table-type=InnoDB
- 臨時改變預設表類型可以用：
set table_type=InnoDB;
傳回頂部
索引
索引在MySQL中也叫做“鍵”，即’key’，是存儲引擎用于快速查找記錄的一種資料結構。當表中的資料越來越多時，索引對于性能的影響愈發重要。索引優化是對查詢性能優化最有效的手段，它能夠輕易将查詢性能提高好幾個數量級。通俗來講，它就相當于新華字典的音序表，能夠幫助你快速的找到你想要的資料。
索引的分類
- 普通索引
- 唯一索引
- 全文索引
- 單列索引
- 多列索引
- 空間索引
傳回頂部
建立索引
- 方法一
文法：
create table 表名(
字段名 資料類型 [限制],
字段名2 資料類型2 [限制],
[unique|fulltext|spatial] index|key
[索引名](字段名[(長度)] [ASC|DESC]) -- 注意中括号裡面的是可選項
);
- 示範：建立普通索引
CREATE TABLE t1(
sid INT,
sname VARCHAR(32),
INDEX(sname) -- 給name 字段建立普通索引 注意字段不要帶引号
);
-- 查詢建表語句
SHOW CREATE TABLE t1;
-- 傳回
Table Create Table
------ --------------------------------------
t1 CREATE TABLE `t1` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(32) DEFAULT NULL,
KEY `sname` (`sname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 示範：建立唯一索引
CREATE TABLE t2(
tid INT,
tname VARCHAR(32),
UNIQUE INDEX(tname) -- 建立唯一索引,這列的值不能重複
);
-
建立全文索引
一般針對這個字段是 text類型,比如存了一篇文章
CREATE TABLE t3(
tid INT,
tname VARCHAR(32),
tlog TEXT,
FULLTEXT INDEX myIndex (tlog) -- 這個myIndex是你起的索引名,一般省略不寫
);
- 建立多列索引
CREATE TABLE t4(
tid INT,
tname VARCHAR(32),
tlog VARCHAR(100),
INDEX my_Index (tname,tlog) -- 給 tname 和 tlog 兩個列都建立索引
);
- 方式二
文法：
示範： student表已經存在，給裡面的字段添加索引
-- 建立普通索引例子
CREATE INDEX myIndex ON student (sname);
-- 建立唯一索引例子
CREATE UNIQUE INDEX myIndex ON student (sname);
-- 建立全文索引例子
CREATE FULLTEXT INDEX myIndex ON t3 (tlog);
-- 建立多列索引例子
CREATE INDEX myIndex ON student (sname,slog);
-
方式三
通過使用修改表的語句 alter table 給某個字段建立索引
文法:
示範：
-- 建立普通索引的例子
ALTER TABLE student ADD INDEX MyIndex(sname);
-- 建立唯一索引的例子
ALTER TABLE student ADD UNIQUE INDEX MyIndex(sname);
-- 建立多列索引
ALTER TABLE student ADD INDEX MyIndex(sname,slog);
傳回頂部
管理索引
- 檢視索引
show create table 表名\G;
- 删除索引
drop index 索引名 on 表名;
傳回頂部
測試索引的效率
寫一個存儲過程往表中存20000條資料。
DELIMITER$$
CREATE
PROCEDURE myinsert()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE (i<=20000) DO
INSERT INTO test VALUES(i,"這是個測試");
SET i = i+1;
END WHILE;
END$$
DELIMITER;
CALL myinsert();
-- 沒添加索引查詢
SELECT * FROM test WHERE id=20000;
-- 執行耗時 : 0.061 sec
-- 傳送時間 : 0.003 sec
-- 總耗時 : 0.065 sec
EXPLAIN SELECT * FROM test WHERE id=20000;
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ -------------
1 SIMPLE test ALL (NULL) (NULL) (NULL) (NULL) 20375 USING WHERE
-- 添加索引後查詢
CREATE INDEX index1 ON test(id);
SELECT * FROM test WHERE id=20000;
-- 執行耗時 : 0.007 sec
-- 傳送時間 : 0.015 sec
-- 總耗時 : 0.023 sec
EXPLAIN SELECT * FROM test WHERE id=20000;
id select_type TABLE TYPE possible_keys KEY key_len ref ROWS Extra
------ ----------- ------ ------ ------------- ------ ------- ------ ------ -------------
1 SIMPLE test ref index1 index1 5 const 1 USING WHERE
-- 可以看出，效率大大提升
傳回頂部
視圖 view
概念
視圖是指計算機資料庫中的視圖，是一個虛拟表，其内容由查詢定義。 同真實的表一樣，視圖包含一系列帶有名稱的列和行資料。 但是，視圖并不在資料庫中以存儲的資料值集形式存在。 行和列資料來自由定義視圖的查詢所引用的表，并且在引用視圖時動态生成。
傳回頂部
文法
- 建立
create view 視圖名稱 as 查詢語句;
-- 比如建立單表視圖
CREATE VIEW my_view1 AS SELECT * FROM emp;
-- 建立多表視圖 注意不要查詢兩張表中的同名字段，不然報錯
CREATE VIEW my_view2 AS SELECT a.字段名,b.字段名 FROM a,b WHERE a.id=b.id;
-- 注意mysql中視圖不支援封裝子查詢查出的資料
注意mysql中視圖不支援封裝子查詢查出的資料。、
視圖一旦建立，系統會在視圖對應的資料庫檔案夾下，建立一個對應的結構檔案：frm檔案
-
檢視
檢視視圖：
show tables;
desc my_view1;
show create view my_view1;
-
使用
你可以把視圖當做表來使用，視圖的本質是封裝select語句，執行視圖就是執行這些語句。
- 删除
drop view my_view1;
-
修改
視圖本身不可以修改，但是視圖的來源是可以修改的（其實就是修改select語句）
alter view 視圖名 as 新的select語句;
傳回頂部
視圖的意義
- 視圖可以節省SQL語句，将一條複雜的查詢語句，使用視圖進行儲存，以後直接對視圖進行操作。
- 資料安全，視圖操作是針對查詢語句的，如果對視圖結構進行處理（比如删除），不會影響基表的資料。
- 視圖往往是在大項目中去使用，而且是多系統中去使用，通過視圖可以隐藏關鍵資料，隻提供有需求的資料。
- 視圖可以對外提供友好的資料：不同的視圖提供不同的資料，對外提供的資料好像是經過專門的設計一樣。
- 視圖可以更好的進行權限控制。
傳回頂部
視圖資料操作的限制
視圖是可以進行資料操作的，但是有很多限制：
- 視圖插入資料
- 多表視圖不能插入資料
- 單表視圖可以插入資料（如果視圖中字段沒有基表中不能為空的字段且沒有預設值的字段，是插入不成功的）
- 視圖是可以向基表中插入資料的（視圖的操作是影響基表的）
- 視圖删除資料
- 多表視圖不能删除資料
- 單表視圖可以删除資料,也會影響到基表
- 視圖更新資料
- 單表視圖,多表視圖都可以更新資料
- 更新限制:
with check option
傳回頂部
![【MySQL索引】MySQL索引分類，90%的開發都不知道一、根據底層資料結構劃分二、根據索引字段個數劃分三、根據是否是在主鍵上建立的索引進行劃分四、根據資料與索引的存儲關聯性劃分五、其他分類六、總結[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)