1. 一些相關常識

1.1 SQL語句分類

DQL(Data QueryLanguage)：資料查詢語言（凡是帶有select關鍵字的都是查詢語句）

select…
DML(Data Manipulation Language)：資料操作語言（凡是對表當中的資料進行增删改的都是DML）

Insert delete update
DDL(Data Definition Language)：資料定義語言（凡是帶有create drop alter ）都是DDL

DDL主要操作的是表的結構，不是表的資料

create：建立，相當于增

drop：删除

alter：修改

這個增删改和DML不同，這個主要是對表的結構進行操作
TCL(Transaction Control Language)：事務控制語言

包括：

事務送出：commit

事務復原：rollback
DCL(Data Control Language)：資料控制語言

例如：授權grant、撤銷權限revoke…

2. 查詢

2.1 簡單查詢

2.1.1 查詢一個字段

select 字段名 from 表名;

其中要注意：

-select和from都是關鍵字。

-字段名和表名都是辨別符。

強調：

對于SQL語句來說，是通用的
所有的SQL語句以“;”結尾。
另外SQL語句不區分大小寫，都行。

//查詢部門名字？
select dname from dept;

2.1.2 查詢多個字段

使用逗号隔開“,”

//查詢部門編号和部門名？
select deptno,dname from dept;

2.1.3 查詢所有字段

select * from table

這種方式的缺點：

1、效率低

2、可讀性差。

在實際開發中不建議，可以自己玩沒問題。

你可以在DOS指令視窗中想快速的看一看全表資料可以采用這種方式

2.1.4 列起别名

使用

as

關鍵字起别名

給

dname

取别名

select deptno,dname as deptname from dept;
//輸出：
		+--------+------------+
		| deptno | deptname   |
		+--------+------------+
		|     10 | ACCOUNTING |
		|     20 | RESEARCH   |
		|     30 | SALES      |
		|     40 | OPERATIONS |
		+--------+------------+

注意：隻是将顯示的查詢結果列名顯示為deptname，原表列名還是叫：dname

記住：select語句是永遠都不會進行修改操作的。（因為隻負責查詢）

as關鍵字可以省略嗎？可以的

假設起别名的時候，别名裡面有空格，怎麼辦？比如：

DBMS看到這樣的語句，進行SQL語句的編譯，不符合文法，編譯報錯。

怎麼解決？

select deptno,dname 'dept name' from dept; //加單引号
select deptno,dname "dept name" from dept; //加雙引号

//結果
				+--------+------------+
				| deptno | dept name  |
				+--------+------------+
				|     10 | ACCOUNTING |
				|     20 | RESEARCH   |
				|     30 | SALES      |
				|     40 | OPERATIONS |
				+--------+------------+

注意：在所有的資料庫當中，字元串統一使用單引号括起來，單引号是标準，雙引号在oracle資料庫中用不了。但是在mysql中可以使用。

再次強調：資料庫中的字元串都是采用單引号括起來。這是标準的，雙引号不标準。

2.2 條件查詢

2.2.1 什麼是條件查詢

不是将表中所有資料都查出來，是查詢出來符合條件的。

文法格式：

select

字段1,字段2,字段3…

from

表名

where

條件;

2.2.2 查詢條件

= 等于
<>或!= 不等于
< 小于
<= 小于等于
大于
= 大于等于
between … and …. 兩個值之間, 等同于 >= and <=

注意：

使用between and的時候，必須遵循左小右大。

between and是閉區間，包括兩端的值。
is null 為 null（is not null 不為空）

注意：

在資料庫當中null不能使用等号進行衡量，需要使用is null。

因為資料庫中的null代表随機值，它不是一個值，是以不能使用等号衡量。
and 并且
or 或者

and和or同時出現的話，有優先級問題嗎？

答：and優先級比or高
in 包含，相當于多個 or （not in 不在這個範圍中）

注意：

in不是一個區間，in後面跟的是具體的值，前面的between…and才是一個區間
not 可以取非，主要用在 is 或 in 中

1.is null

2.is not null

3.in

4.not in
like 稱為模糊查詢，支援%或下劃線比對

%比對任意多個字元

下劃線：任意一個字元。

（%是一個特殊的符号，_ 也是一個特殊符号，如果要比對這兩個特殊符号，則需要轉義，即
/_ 或 /% ）

2.3 排序

文法：

select chart from xx order by 字段; // 預設是升序！！！
select chart from xx order by 字段 desc; //降序

如果要按兩個或多個字段排序，則以第一個排序為主導，隻有第一個排序的字段值相同時，才考慮用第二個字段排序

例如：

//查詢員工名字和薪資，要求按照薪資升序，如果薪資一樣的話，再按照名字升序排列。
select ename,sal from emp order by sal asc, ename asc; // sal在前，起主導，隻有sal相等的時候，才會考慮啟用ename排序。

2.4 去重 distinct

注意：原表資料不會被修改，隻是查詢結果去重

去重需要使用一個關鍵字：distinct

mysql> select distinct job from emp;
	+-----------+
	| job       |
	+-----------+
	| CLERK     |
	| SALESMAN  |
	| MANAGER   |
	| ANALYST   |
	| PRESIDENT |
	+-----------+

	// 這樣編寫是錯誤的，文法錯誤。
	// distinct隻能出現在所有字段的最前方。
	mysql> select ename,distinct job from emp;//錯誤，沒法比對

	// distinct出現在job,deptno兩個字段之前，表示兩個字段聯合起來去重。
	mysql> select distinct job,deptno from emp;
	+-----------+--------+
	| job       | deptno |
	+-----------+--------+
	| CLERK     |     20 |
	| SALESMAN  |     30 |
	| MANAGER   |     20 |
	| MANAGER   |     30 |
	| MANAGER   |     10 |
	| ANALYST   |     20 |
	| PRESIDENT |     10 |
	| CLERK     |     30 |
	| CLERK     |     10 |
	+-----------+--------+

//統計一下工作崗位的數量？
		select count(distinct job) from emp;
		+---------------------+
		| count(distinct job) |
		+---------------------+
		|                   5 |
		+---------------------+

2.5 總結語句執行順序

select
			...
		from
			...
		where
			...
		order by
			...

以上語句的執行順序必須掌握：

第一步：from

第二步：where

第三步：select

第四步：order by（排序總是在最後執行！）

3. 資料處理函數

3.1 定義

資料處理函數又被稱為單行處理函數

單行處理函數的特點：一個輸入對應一個輸出。
和單行處理函數相對的是：多行處理函數。（多行處理函數特點：多個輸入，對應1個輸出！）

3.2 常用的單行處理函數

lower 轉換小寫

upper 轉換大寫

substr 取子串

substr( 被截取的字元串, 起始下标,截取的長度)

注意：起始下标從1開始，沒有0.

//找出員工名字第一個字母是A的員工資訊？
select ename from emp where substr(ename,1,1) = 'A';

concat 函數進行字元串的拼接

length 取長度

trim 去空格

mysql> select * from emp where ename = '  KING';
		Empty set (0.00 sec)//表示沒有'  KING'

		mysql> select * from emp where ename = trim('   KING');//查詢到了，說明已經去掉了空格
		+-------+-------+-----------+------+------------+---------+------+--------+
		| EMPNO | ENAME | JOB       | MGR  | HIREDATE   | SAL     | COMM | DEPTNO |
		+-------+-------+-----------+------+------------+---------+------+--------+
		|  7839 | KING  | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL |     10 |
		+-------+-------+-----------+------+------------+---------+------+--------+

str_to_date 将字元串轉換成日期
date_format 格式化日期
format 設定千分位
case…when…then…when…then…else…end

//當員工的工作崗位是MANAGER的時候，工資上調10%，當工作崗位是SALESMAN的時候，工資上調50%,其它正常。
//（注意：不修改資料庫，隻是将查詢結果顯示為工資上調）
select ename,job, sal as oldsal,(case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;
		
		+--------+-----------+---------+---------+
		| ename  | job       | oldsal  | newsal  |
		+--------+-----------+---------+---------+
		| SMITH  | CLERK     |  800.00 |  800.00 |
		| ALLEN  | SALESMAN  | 1600.00 | 2400.00 |
		| WARD   | SALESMAN  | 1250.00 | 1875.00 |
		| JONES  | MANAGER   | 2975.00 | 3272.50 |
		| MARTIN | SALESMAN  | 1250.00 | 1875.00 |
		| BLAKE  | MANAGER   | 2850.00 | 3135.00 |
		| CLARK  | MANAGER   | 2450.00 | 2695.00 |
		| SCOTT  | ANALYST   | 3000.00 | 3000.00 |
		| KING   | PRESIDENT | 5000.00 | 5000.00 |
		| TURNER | SALESMAN  | 1500.00 | 2250.00 |
		| ADAMS  | CLERK     | 1100.00 | 1100.00 |
		| JAMES  | CLERK     |  950.00 |  950.00 |
		| FORD   | ANALYST   | 3000.00 | 3000.00 |
		| MILLER | CLERK     | 1300.00 | 1300.00 |
		+--------+-----------+---------+---------+

round 四舍五入，可以自己選擇位數，格式：```round(值,保留位數可正可負可0)

mysql> select round(1236.567, 0) as result from emp; //保留整數位。
		+--------+
		| result |
		+--------+
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		|   1237 |
		+--------+

		select round(1236.567, 1) as result from emp; //保留1個小數,裡面表是1236.6
		select round(1236.567, 2) as result from emp; //保留2個小數,裡面表是1236.57
		select round(1236.567, -1) as result from emp; // 保留到十位,按各位四舍五入，這裡個位是6,是以舍到十位變成4
		+--------+
		| result |
		+--------+
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		|   1240 |
		+--------+

		select round(1236.567, -2) as result from emp;
		+--------+
		| result |
		+--------+
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		|   1200 |
		+--------+

rand() 生成随機數

mysql> select round(rand()*100,0) from emp; // 100以内的随機數
		+---------------------+
		| round(rand()*100,0) |
		+---------------------+
		|                  76 |
		|                  29 |
		|                  15 |
		|                  88 |
		|                  95 |
		|                   9 |
		|                  63 |
		|                  89 |
		|                  54 |
		|                   3 |
		|                  54 |
		|                  61 |
		|                  42 |
		|                  28 |
		+---------------------+

ifnull 可以将 null 轉換成一個具體值

ifnull是空處理函數。專門處理空的。

在所有資料庫當中，隻要有NULL參與的數學運算，最終結果就是NULL。

**ifnull函數用法：**ifnull(資料, 被當做哪個值)

如果“資料”為NULL的時候，把這個資料結構當做哪個值。

//補助為NULL的時候，将補助當做0
select ename, (sal + ifnull(comm, 0)) * 12 as yearsal from emp;

3.3 分組函數（多行處理函數）

多行處理函數的特點：輸入多行，最終輸出一行

count 計數
sum 求和
avg 平均值
max 最大值
min 最小值

注意：

分組函數在使用的時候必須先進行分組，然後才能用。

如果你沒有對資料進行分組，整張表預設為一組。

分組函數在使用的時候需要注意哪些？

分組函數自動忽略NULL，你不需要提前對NULL進行處理
分組函數中count(*)和count(具體字段)有什麼差別？

mysql> select count(*) from emp;
			+----------+
			| count(*) |
			+----------+
			|       14 |
			+----------+

mysql> select count(comm) from emp;
			+-------------+
			| count(comm) |
			+-------------+
			|           4 |
			+-------------+

//count(具體字段)：表示統計該字段下所有不為NULL的元素的總數。
//count(*)：統計表當中的總行數，（隻要有一行資料count則++）因為每一行記錄不可能都為NULL，一行資料中有一列不為NULL，則這行資料就是有效的。

分組函數不能夠直接使用在where子句中,要用分組函數groupby才行
所有的分組函數可以組合起來一起用

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
			//+----------+----------+----------+-------------+----------+
			//| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
			//+----------+----------+----------+-------------+----------+
			//| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
			//+----------+----------+----------+-------------+----------+

3.4 分組查詢(超級重要)

在實際的應用中，可能有這樣的需求，需要先進行分組，然後對每一組的資料進行操作。

這個時候我們需要使用分組查詢，怎麼進行分組查詢呢？

select
...
from
...
group by
...

3.4.1 總結語句執行順序

select
			...
		from
			...
		where
			...
		group by
			...
		order by
			...

以上關鍵字的順序不能颠倒，需要記憶。

執行順序是什麼？

1. from

2. where

3. group by

4. select

5. order by

為什麼分組函數不能直接使用在where後面？

select ename,sal from emp where sal > min(sal);//報錯。

因為分組函數在使用的時候必須先分組之後才能使用。where執行的時候，還沒有分組。是以where後面不能出現分組函數。

select sum(sal) from emp;

這個沒有分組，為啥sum()函數可以用呢？

因為select在group by之後執行。

3.4.2 一些練習

練習：一個字段的分組

//找出每個工作崗位的工資和？
//實作思路：按照工作崗位分組，然後對工資求和。
			select 
				job,sum(sal)
			from
				emp
			group by
				job;
			
			+-----------+----------+
			| job       | sum(sal) |
			+-----------+----------+
			| ANALYST   |  6000.00 |
			| CLERK     |  4150.00 |
			| MANAGER   |  8275.00 |
			| PRESIDENT |  5000.00 |
			| SALESMAN  |  5600.00 |
			+-----------+----------+
			//以上這個語句的執行順序？
				//先從emp表中查詢資料。
				//根據job字段進行分組。
				//然後對每一組的資料進行sum(sal)

重點：

一般來說，在一條select語句當中，如果有group by語句的話，select後面隻能跟：參加分組的字段，以及分組函數，其它的一律不能跟。

練習：兩個字段聯合分組

//找出“每個部門，不同工作崗位”的最高薪資？
//技巧：兩個字段聯合成1個字段看。（兩個字段聯合分組）
		select 
			deptno, job, max(sal)
		from
			emp
		group by
			deptno, job;
		+--------+-----------+----------+
		| deptno | job       | max(sal) |
		+--------+-----------+----------+
		|     10 | CLERK     |  1300.00 |
		|     10 | MANAGER   |  2450.00 |
		|     10 | PRESIDENT |  5000.00 |
		|     20 | ANALYST   |  3000.00 |
		|     20 | CLERK     |  1100.00 |
		|     20 | MANAGER   |  2975.00 |
		|     30 | CLERK     |   950.00 |
		|     30 | MANAGER   |  2850.00 |
		|     30 | SALESMAN  |  1600.00 |
		+--------+-----------+----------+

3.4.3 使用having對分組後資料進一步過濾

使用

having

可以對分完組之後的資料進一步過濾。

having

不能單獨使用，

having

不能代替

where

，

having

必須和

group by

聯合使用

舉例：

找出每個部門最高薪資，要求顯示最高薪資大于3000的？

//找出每個部門最高薪資，要求顯示最高薪資大于3000的？
//第一步：找出每個部門最高薪資,按照部門編号分組，求每一組最大值
select deptno,max(sal) from emp group by deptno;
			+--------+----------+
			| deptno | max(sal) |
			+--------+----------+
			|     10 |  5000.00 |
			|     20 |  3000.00 |
			|     30 |  2850.00 |
			+--------+----------+
		
//第二步：要求顯示最高薪資大于3000
			select 
				deptno,max(sal) 
			from 
				emp 
			group by 
				deptno
			having
				max(sal) > 3000;

			+--------+----------+
			| deptno | max(sal) |
			+--------+----------+
			|     10 |  5000.00 |
			+--------+----------+


//思考一個問題：以上的sql語句執行效率是不是低？是,比較低，實際上可以這樣考慮：先将大于3000的都找出來，然後再分組。
			select 
				deptno,max(sal)
			from
				emp
			where
				sal > 3000
			group by
				deptno;
			
			+--------+----------+
			| deptno | max(sal) |
			+--------+----------+
			|     10 |  5000.00 |
			+--------+----------+

//優化政策：where和having，優先選擇where，where實在完成不了了,再選擇having。

優化政策：where和having，優先選擇where，where實在完成不了了,再選擇having。

那麼什麼情況是where沒辦法的？？？？一般來說是查詢條件裡必須要利用分組函數的時候

舉例：

找出每個部門平均薪資，要求顯示平均薪資高于2500的

//第一步：找出每個部門平均薪資
select deptno,avg(sal) from emp group by deptno;
				+--------+-------------+
				| deptno | avg(sal)    |
				+--------+-------------+
				|     10 | 2916.666667 |
				|     20 | 2175.000000 |
				|     30 | 1566.666667 |
				+--------+-------------+

//第二步：要求顯示平均薪資高于2500的
				select 
					deptno,avg(sal) 
				from 
					emp 
				group by 
					deptno
				having
					avg(sal) > 2500;
			
			+--------+-------------+
			| deptno | avg(sal)    |
			+--------+-------------+
			|     10 | 2916.666667 |
			+--------+-------------+

3.5 大總結語句執行順序

select

…

from

…

where

…

group by

…

having

…

order by

…

以上關鍵字隻能按照這個順序來，不能颠倒。

執行順序？

1. from

2. where

3. group by

4. having

5. select

6. order by

從某張表中查詢資料，

先經過where條件篩選出有價值的資料。

對這些有價值的資料進行分組。

分組之後可以使用having繼續篩選。

select查詢出來。

最後排序輸出！

綜合練習：

找出每個崗位的平均薪資，要求顯示平均薪資大于1500的，除MANAGER崗位之外，要求按照平均薪資降序排。

select 
			job, avg(sal) as avgsal
		from
			emp
		where
			job <> 'MANAGER'
		group by
			job
		having
			avg(sal) > 1500
		order by
			avgsal desc;

		+-----------+-------------+
		| job       | avgsal      |
		+-----------+-------------+
		| PRESIDENT | 5000.000000 |
		| ANALYST   | 3000.000000 |
		+-----------+-------------+

4. 連接配接查詢(最重要的)

4.1 什麼是連接配接查詢

從一張表中單獨查詢，稱為單表查詢。

emp表和dept表聯合起來查詢資料，從emp表中取員工名字，從dept表中取部門名字。

這種跨表查詢，多張表聯合起來查詢資料，被稱為連接配接查詢。

4.2 連接配接查詢的分類

根據表連接配接的方式分類：

内連接配接：

等值連接配接
非等值連接配接
自連接配接

外連接配接：

左外連接配接（左連接配接）
右外連接配接（右連接配接）

全連接配接

4.3 内連接配接

4.3.1 内連接配接之等值連接配接

案例：查詢每個員工所在部門名稱，顯示員工名和部門名？

emp e和dept d表進行連接配接。條件是：e.deptno = d.deptno

//SQL92文法：
	select 
		e.ename,d.dname
	from
		emp e, dept d
	where
		e.deptno = d.deptno;
	
	//sql92的缺點：結構不清晰，表的連接配接條件，和後期進一步篩選的條件，都放到了where後面。

//SQL99文法：
	select 
		e.ename,d.dname
	from
		emp e
	join
		dept d
	on
		e.deptno = d.deptno;
	

	//inner可以省略（帶着inner可讀性更好！！！一眼就能看出來是内連接配接）
	select 
		e.ename,d.dname
	from
		emp e
	inner join
		dept d
	on
		e.deptno = d.deptno; // 條件是等量關系，是以被稱為等值連接配接。

	
	//sql99優點：表連接配接的條件是獨立的，連接配接之後，如果還需要進一步篩選，再往後繼續添加where

SQL99文法：

select 
			...
		from
			a
		join
			b
		on
			a和b的連接配接條件
		where
			篩選條件

4.3.2 内連接配接之非等值連接配接

**案例：**找出每個員工的薪資等級，要求顯示員工名、薪資、薪資等級？

select 
	e.ename, e.sal, s.grade
from
	emp e
join
	salgrade s
on
	e.sal between s.losal and s.hisal; 
	// 條件不是一個等量關系，稱為非等值連接配接。

4.3.3 内連接配接之自連接配接

案例：

查詢員工的上級上司，要求顯示員工名和對應的上司名？

select 
	a.ename as '員工名', b.ename as '上司名'
from
	emp a
join
	emp b
on
	a.mgr = b.empno; 
	//員工的上司編号 = 上司的員工編号

4.4 外連接配接

4.4.1 外連接配接與内連接配接的差別

内連接配接：A和B連接配接，AB兩張表沒有主次關系，平等的

外連接配接：兩張表連接配接，産生了主次關系，左外連接配接就左邊是主表，右外連接配接就右邊是主表，此時主表的資料一個都不會丢，而次表的資料如果沒有與主表比對上的話，就會是null值

4.4.2 右外連接配接

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

// outer是可以省略的，帶着可讀性強。
select 
	e.ename,d.dname
from
	emp e 
right outer join 
	dept d
on
	e.deptno = d.deptno;

//right代表什麼：表示将join關鍵字右邊的這張表看成主表，主要是為了将這張表的資料全部查詢出來，捎帶着關聯查詢左邊的表。
//在外連接配接當中，兩張表連接配接，産生了主次關系。

4.4.3 左外連接配接

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

// outer是可以省略的，帶着可讀性強。
select 
	e.ename,d.dname
from
	dept d 
left outer join 
	emp e
on
	e.deptno = d.deptno;

4.4.4 案例

總結：

帶有right的是右外連接配接，又叫做右連接配接。

帶有left的是左外連接配接，又叫做左連接配接。

任何一個右連接配接都有左連接配接的寫法。

任何一個左連接配接都有右連接配接的寫法。

思考：外連接配接的查詢結果條數一定是 >= 内連接配接的查詢結果條數？

-正确。

案例：查詢每個員工的上級上司，要求顯示所有員工的名字和上司名？

//此時發現，是查詢每個員工的上司，是以要以有員工的那張表作為主表，如果此表放左邊就是左外連接配接
	select 
		a.ename as '員工名', b.ename as '上司名'
	from
		emp a
	left join
		emp b
	on
		a.mgr = b.empno; 


//	+--------+--------+
//	| 員工名 | 上司名 |
//	+--------+--------+
//	| SMITH  | FORD   |
//	| ALLEN  | BLAKE  |
//	| WARD   | BLAKE  |
//	| JONES  | KING   |
//	| MARTIN | BLAKE  |
//	| BLAKE  | KING   |
//	| CLARK  | KING   |
//	| SCOTT  | JONES  |
//	| KING   | NULL   |
//	| TURNER | BLAKE  |
//	| ADAMS  | SCOTT  |
//	| JAMES  | BLAKE  |
//	| FORD   | JONES  |
//	| MILLER | CLARK  |
//	+--------+--------+

4.4.5 多張表怎麼連接配接

三張表，四張表怎麼連接配接？

文法：

select 
			...
		from
			a
		join
			b
		on
			a和b的連接配接條件
		join
			c
		on
			a和c的連接配接條件
		right join
			d
		on
			a和d的連接配接條件
		
//一條SQL中内連接配接和外連接配接可以混合。都可以出現！

一條SQL中内連接配接和外連接配接可以混合。都可以出現！

舉例：

找出每個員工的部門名稱以及工資等級，要求顯示員工名、部門名、薪資、薪資等級？

select 
		e.ename,e.sal,d.dname,s.grade
	from
		emp e
	join
		dept d
	on 
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal;
	
	+--------+---------+------------+-------+
	| ename  | sal     | dname      | grade |
	+--------+---------+------------+-------+
	| SMITH  |  800.00 | RESEARCH   |     1 |
	| ALLEN  | 1600.00 | SALES      |     3 |
	| WARD   | 1250.00 | SALES      |     2 |
	| JONES  | 2975.00 | RESEARCH   |     4 |
	| MARTIN | 1250.00 | SALES      |     2 |
	| BLAKE  | 2850.00 | SALES      |     4 |
	| CLARK  | 2450.00 | ACCOUNTING |     4 |
	| SCOTT  | 3000.00 | RESEARCH   |     4 |
	| KING   | 5000.00 | ACCOUNTING |     5 |
	| TURNER | 1500.00 | SALES      |     3 |
	| ADAMS  | 1100.00 | RESEARCH   |     1 |
	| JAMES  |  950.00 | SALES      |     1 |
	| FORD   | 3000.00 | RESEARCH   |     4 |
	| MILLER | 1300.00 | ACCOUNTING |     2 |
	+--------+---------+------------+-------+

舉例：

找出每個員工的部門名稱以及工資等級，還有上級上司，要求顯示員工名、上司名、部門名、薪資、薪資等級？

select 
		e.ename,e.sal,d.dname,s.grade,l.ename
	from
		emp e
	join
		dept d
	on 
		e.deptno = d.deptno
	join
		salgrade s
	on
		e.sal between s.losal and s.hisal
	left join
		emp l
	on
		e.mgr = l.empno;
	
	+--------+---------+------------+-------+-------+
	| ename  | sal     | dname      | grade | ename |
	+--------+---------+------------+-------+-------+
	| SMITH  |  800.00 | RESEARCH   |     1 | FORD  |
	| ALLEN  | 1600.00 | SALES      |     3 | BLAKE |
	| WARD   | 1250.00 | SALES      |     2 | BLAKE |
	| JONES  | 2975.00 | RESEARCH   |     4 | KING  |
	| MARTIN | 1250.00 | SALES      |     2 | BLAKE |
	| BLAKE  | 2850.00 | SALES      |     4 | KING  |
	| CLARK  | 2450.00 | ACCOUNTING |     4 | KING  |
	| SCOTT  | 3000.00 | RESEARCH   |     4 | JONES |
	| KING   | 5000.00 | ACCOUNTING |     5 | NULL  |
	| TURNER | 1500.00 | SALES      |     3 | BLAKE |
	| ADAMS  | 1100.00 | RESEARCH   |     1 | SCOTT |
	| JAMES  |  950.00 | SALES      |     1 | BLAKE |
	| FORD   | 3000.00 | RESEARCH   |     4 | JONES |
	| MILLER | 1300.00 | ACCOUNTING |     2 | CLARK |
	+--------+---------+------------+-------+-------+

5. 子查詢

5.1 什麼是子查詢

select語句中嵌套select語句，被嵌套的select語句稱為子查詢

5.2 子查詢出現的位置

select

…(select).

from

…(select).

where

…(select).

5.3 where子句中的子查詢

案例：找出比最低工資高的員工姓名和工資？

select 
			ename,sal
		from
			emp 
		where
			sal > min(sal);
//出現錯誤
		ERROR 1111 (HY000): Invalid use of group function
		where子句中不能直接使用分組函數
		
//正确做法	
	實作思路：
		第一步：查詢最低工資是多少
			select min(sal) from emp;
			+----------+
			| min(sal) |
			+----------+
			|   800.00 |
			+----------+
		第二步：找出>800的
			select ename,sal from emp where sal > 800;
		
		第三步：合并
			select ename,sal from emp where sal > (select min(sal) from emp);
			+--------+---------+
			| ename  | sal     |
			+--------+---------+
			| ALLEN  | 1600.00 |
			| WARD   | 1250.00 |
			| JONES  | 2975.00 |
			| MARTIN | 1250.00 |
			| BLAKE  | 2850.00 |
			| CLARK  | 2450.00 |
			| SCOTT  | 3000.00 |
			| KING   | 5000.00 |
			| TURNER | 1500.00 |
			| ADAMS  | 1100.00 |
			| JAMES  |  950.00 |
			| FORD   | 3000.00 |
			| MILLER | 1300.00 |
			+--------+---------+

5.4 from子句中的子查詢

注意：from後面的子查詢，可以将子查詢的查詢結果當做一張臨時表。（技巧）

案例：找出每個崗位的平均工資的薪資等級。

//第一步：找出每個崗位的平均工資（按照崗位分組求平均值）
		select job,avg(sal) from emp group by job;
		+-----------+-------------+
		| job       | avgsal      |
		+-----------+-------------+
		| ANALYST   | 3000.000000 |
		| CLERK     | 1037.500000 |
		| MANAGER   | 2758.333333 |
		| PRESIDENT | 5000.000000 |
		| SALESMAN  | 1400.000000 |
		+-----------+-------------+t表

	//第二步：克服心理障礙，把以上的查詢結果就當做一張真實存在的表t。
	mysql> select * from salgrade; s表
	+-------+-------+-------+
	| GRADE | LOSAL | HISAL |
	+-------+-------+-------+
	|     1 |   700 |  1200 |
	|     2 |  1201 |  1400 |
	|     3 |  1401 |  2000 |
	|     4 |  2001 |  3000 |
	|     5 |  3001 |  9999 |
	+-------+-------+-------+
	//t表和s表進行表連接配接，條件：t表avg(sal) between s.losal and s.hisal;
		
		select 
			t.*, s.grade
		from
			(select job,avg(sal) as avgsal from emp group by job) t
		join
			salgrade s
		on
			t.avgsal between s.losal and s.hisal;
		
		+-----------+-------------+-------+
		| job       | avgsal      | grade |
		+-----------+-------------+-------+
		| CLERK     | 1037.500000 |     1 |
		| SALESMAN  | 1400.000000 |     2 |
		| ANALYST   | 3000.000000 |     4 |
		| MANAGER   | 2758.333333 |     4 |
		| PRESIDENT | 5000.000000 |     5 |
		+-----------+-------------+-------+

5.5 select後面出現的子查詢

這個内容不需要掌握，了解即可！！！

案例：找出每個員工的部門名稱，要求顯示員工名，部門名？

select 
		e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname 
	from 
		emp e;


	+--------+--------+------------+
	| ename  | deptno | dname      |
	+--------+--------+------------+
	| SMITH  |     20 | RESEARCH   |
	| ALLEN  |     30 | SALES      |
	| WARD   |     30 | SALES      |
	| JONES  |     20 | RESEARCH   |
	| MARTIN |     30 | SALES      |
	| BLAKE  |     30 | SALES      |
	| CLARK  |     10 | ACCOUNTING |
	| SCOTT  |     20 | RESEARCH   |
	| KING   |     10 | ACCOUNTING |
	| TURNER |     30 | SALES      |
	| ADAMS  |     20 | RESEARCH   |
	| JAMES  |     30 | SALES      |
	| FORD   |     20 | RESEARCH   |
	| MILLER |     10 | ACCOUNTING |
	+--------+--------+------------+

	//錯誤：ERROR 1242 (21000): Subquery returns more than 1 row
	select 
		e.ename,e.deptno,(select dname from dept) as dname
	from
		emp e;
	
	注意：對于select後面的子查詢來說，這個子查詢隻能一次傳回1條結果，
	多于1條，就報錯了。！

6. union合并查詢

案例：查詢工作崗位是MANAGER和SALESMAN的員工？

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
	select ename,job from emp where job in('MANAGER','SALESMAN');
	+--------+----------+
	| ename  | job      |
	+--------+----------+
	| ALLEN  | SALESMAN |
	| WARD   | SALESMAN |
	| JONES  | MANAGER  |
	| MARTIN | SALESMAN |
	| BLAKE  | MANAGER  |
	| CLARK  | MANAGER  |
	| TURNER | SALESMAN |
	+--------+----------+
	
	select ename,job from emp where job = 'MANAGER'
	union
	select ename,job from emp where job = 'SALESMAN';
	
	+--------+----------+
	| ename  | job      |
	+--------+----------+
	| JONES  | MANAGER  |
	| BLAKE  | MANAGER  |
	| CLARK  | MANAGER  |
	| ALLEN  | SALESMAN |
	| WARD   | SALESMAN |
	| MARTIN | SALESMAN |
	| TURNER | SALESMAN |
	+--------+----------+

union的效率要高一些。對于表連接配接來說，每連接配接一次新表，

則比對的次數滿足笛卡爾積，成倍的翻。。。

但是union可以減少比對的次數。在減少比對次數的情況下，

還可以完成兩個結果集的拼接。

a 連接配接 b 連接配接 c
a 10條記錄
b 10條記錄
c 10條記錄
比對次數是：1000

a 連接配接 b一個結果：10 * 10 --> 100次
a 連接配接 c一個結果：10 * 10 --> 100次
使用union的話是：100次 + 100次 = 200次。（union把乘法變成了加法運算）

union在使用的時候有注意事項嗎？

union在進行結果集合并的時候，要求兩個結果集的列數相同
結果集合并時列和列的資料類型也要一緻

//錯誤的：union在進行結果集合并的時候，要求兩個結果集的列數相同
	select ename,job from emp where job = 'MANAGER'
	union
	select ename from emp where job = 'SALESMAN';

	// MYSQL可以，oracle文法嚴格 ，不可以，報錯。要求：結果集合并時列和列的資料類型也要一緻
	select ename,job from emp where job = 'MANAGER'
	union
	select ename,sal from emp where job = 'SALESMAN';
	+--------+---------+
	| ename  | job     |
	+--------+---------+
	| JONES  | MANAGER |
	| BLAKE  | MANAGER |
	| CLARK  | MANAGER |
	| ALLEN  | 1600    |
	| WARD   | 1250    |
	| MARTIN | 1250    |
	| TURNER | 1500    |
	+--------+---------+

7. limit（非常重要）

7.1 limit作用

limit作用：将查詢結果集的一部分取出來,通常使用在分頁查詢當中。

百度預設：一頁顯示10條記錄。

分頁的作用是為了提高使用者的體驗，因為一次全部都查出來，使用者體驗差。

可以一頁一頁翻頁看。

7.2 limit的使用

完整用法：limit startIndex, length

startIndex是起始下标，length是長度。

起始下标從0開始
預設用法：limit 5; 這是取前5

舉例：

按照薪資降序，取出排名在前5名的員工？

select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 5; //取前5

	select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 0,5;

	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| KING  | 5000.00 |
	| SCOTT | 3000.00 |
	| FORD  | 3000.00 |
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	+-------+---------+

注意：

mysql當中limit在order by之後執行！！！！！！

舉例：

取出工資排名在[3-5]名的員工？

select 
		ename,sal
	from
		emp
	order by
		sal desc
	limit
		2, 3;
	
	2表示起始位置從下标2開始，就是第三條記錄。
	3表示長度。

	+-------+---------+
	| ename | sal     |
	+-------+---------+
	| FORD  | 3000.00 |
	| JONES | 2975.00 |
	| BLAKE | 2850.00 |
	+-------+---------+

舉例：

取出工資排名在[5-9]名的員工？

select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit
		4, 5;

	+--------+---------+
	| ename  | sal     |
	+--------+---------+
	| BLAKE  | 2850.00 |
	| CLARK  | 2450.00 |
	| ALLEN  | 1600.00 |
	| TURNER | 1500.00 |
	| MILLER | 1300.00 |
	+--------+---------+

8. 分頁

每頁顯示3條記錄

第1頁：limit 0,3 [0 1 2]

第2頁：limit 3,3 [3 4 5]

第3頁：limit 6,3 [6 7 8]

第4頁：limit 9,3 [9 10 11]

每頁顯示pageSize條記錄

第pageNo頁：limit (pageNo - 1) * pageSize , pageSize

public static void main(String[] args){
	// 使用者送出過來一個頁碼，以及每頁顯示的記錄條數
	int pageNo = 5; //第5頁
	int pageSize = 10; //每頁顯示10條

	int startIndex = (pageNo - 1) * pageSize;
	String sql = "select ...limit " + startIndex + ", " + pageSize;
}

*記公式：

limit (pageNo-1)pageSize , pageSize

9. 關于DQL語句的大總結

select

…

from

…

where

…

group by

…

having

…

order by

…

limit

…

執行順序？
	1.from
	2.where
	3.group by
	4.having
	5.select
	6.order by
	7.limit..

10. 對表、資料的操作

10.1 操作表

10.1.1 表的建立

建表的文法格式：(建表屬于DDL語句，DDL包括：create drop alter)

create table 表名(字段名1 資料類型, 字段名2 資料類型, 字段名3 資料類型);

create table 表名(
	字段名1 資料類型, 
	字段名2 資料類型, 
	字段名3 資料類型
);

表名：建議以t_ 或者 tbl_開始，可讀性強。見名知意。
字段名：見名知意。
表名和字段名都屬于辨別符。

舉例：建立一個學生表？

學号、姓名、年齡、性别、郵箱位址

create table t_student(
		number int,
		name varchar(32),
		sex char(1),
		age int(3),
		email varchar(255)
	);

10.1.2 表的删除

drop table t_student; // 當這張表不存在的時候會報錯！

// 如果這張表存在的話，删除
drop table if exists t_student;

10.2 mysql中的資料類型

varchar(最長255)：可變長度的字元串比較智能，節省空間。會根據實際的資料長度動态配置設定空間。

優點：節省空間

缺點：需要動态配置設定空間，速度慢。

char(最長255)，定長字元串，不管實際的資料長度是多少。配置設定固定長度的空間去存儲資料。

使用不恰當的時候，可能會導緻空間的浪費。

優點：不需要動态配置設定空間，速度快。

缺點：使用不當可能會導緻空間的浪費。

varchar和char我們應該怎麼選擇？

性别字段你選什麼？因為性别是固定長度的字元串，是以選擇char。

姓名字段你選什麼？每一個人的名字長度不同，是以選擇varchar。

int(最長11，這個是顯示寬度) 數字中的整數型，等同于java的int，儲存4位元組

int(2) 與int(11)後的括号中的字元表示顯示寬度，整數列的顯示寬度與 MySQL 需要用多少個字元來顯示該列數值，與該整數需要的存儲空間的大小都沒有關系，int類型的字段能存儲的資料上限依舊是2147483647(有符号型)和4294967295(無符号型)。
bigint 數字中的長整型，等同于java中的long，儲存8位元組
float 單精度浮點型資料
double 雙精度浮點型資料
date 短日期類型
datetime 長日期類型
clob 字元大對象，最多可以存儲4G的字元串。

比如：存儲一篇文章，存儲一個說明。

超過255個字元的都要采用CLOB字元大對象來存儲。

英文：Character Large OBject:CLOB
blob 二進制大對象

專門用來存儲圖檔、聲音、視訊等流媒體資料。

往BLOB類型的字段上插入資料的時候，例如插入一個圖檔、視訊等，你需要使用IO流才行

英文：Binary Large OBject

10.2.1 date和datetime兩個類型的差別

date是短日期：隻包括年月日資訊。

datetime是長日期：包括年月日時分秒資訊。

drop table if exists t_user;
	create table t_user(
		id int,
		name varchar(32),
		birth date,
		create_time datetime
	);

id是整數

name是字元串

birth是短日期

create_time是這條記錄的建立時間：長日期類型

mysql短日期預設格式：%Y-%m-%d

mysql長日期預設格式：%Y-%m-%d %h:%i:%s

insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

在mysql當中怎麼擷取系統目前時間？

now() 函數，并且擷取的時間帶有：時分秒資訊！！！！是datetime類型的。

insert into t_user(id,name,birth,create_time) values(2,'lisi','1991-10-01',now());

10.3 對資料進行操作

10.3.1 插入資料

10.3.1.1 文法格式

文法格式：

insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);

注意：字段名和值要一一對應。什麼是一一對應？

數量要對應，資料類型要對應。

舉例：

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'[email protected]');

insert into t_student(email,name,sex,age,no) values('[email protected]','lisi','f',20,2);

insert into t_student(no) values(3);

	+------+----------+------+------+------------------+
	| no   | name     | sex  | age  | email            |
	+------+----------+------+------+------------------+
	|    1 | zhangsan | m    |   20 | [email protected] |
	|    2 | lisi     | f    |   20 | [email protected]     |
	|    3 | NULL     | NULL | NULL | NULL             |
	+------+----------+------+------+------------------+
	insert into t_student(name) values('wangwu');
	+------+----------+------+------+------------------+
	| no   | name     | sex  | age  | email            |
	+------+----------+------+------+------------------+
	|    1 | zhangsan | m    |   20 | [email protected] |
	|    2 | lisi     | f    |   20 | [email protected]     |
	|    3 | NULL     | NULL | NULL | NULL             |
	| NULL | wangwu   | NULL | NULL | NULL             |
	+------+----------+------+------+------------------+
//注意：insert語句但凡是執行成功了，那麼必然會多一條記錄。
//沒有給其它字段指定值的話，預設值是NULL。

insert語句中的“字段名”可以省略嗎？

可以，但是前面的字段名省略的話，等于都寫上了！是以值也要都寫上！

insert into t_student values(2); //錯誤的

insert into t_student values(2, 'lisi', 'f', 20, '[email protected]');//正确的

10.3.1.2 如何給字段設定預設值

因為插入資料時字段不給值，值預設為null，如果不想預設為空則在建表時就給預設值，用

default 某值

即可

舉例：

drop table if exists t_student;
	create table t_student(
		no int,
		name varchar(32),
		sex char(1) default 'm',
		age int(3),
		email varchar(255)
	);
	
	//表的結構
	+-------+--------------+------+-----+---------+-------+
	| Field | Type         | Null | Key | Default | Extra |
	+-------+--------------+------+-----+---------+-------+
	| no    | int(11)      | YES  |     | NULL    |       |
	| name  | varchar(32)  | YES  |     | NULL    |       |
	| sex   | char(1)      | YES  |     | m       |       |
	| age   | int(3)       | YES  |     | NULL    |       |
	| email | varchar(255) | YES  |     | NULL    |       |
	+-------+--------------+------+-----+---------+-------+

	insert into t_student(no) values(1);//插入資料(沒有給所有字段指派),其他字段有自定義預設值default則用自定義的值，沒有則為null
	
	mysql> select * from t_student;//查詢表,發現sex字段有值,此值為預設值"m"
	+------+------+------+------+-------+
	| no   | name | sex  | age  | email |
	+------+------+------+------+-------+
	|    1 | NULL | m    | NULL | NULL  |
	+------+------+------+------+-------+

10.3.1.3 insert插入日期

注意：

數字格式化：

format

select ename,sal from emp;
		+--------+---------+
		| ename  | sal     |
		+--------+---------+
		| SMITH  |  800.00 |
		| ALLEN  | 1600.00 |
		| WARD   | 1250.00 |
		| JONES  | 2975.00 |
		| MARTIN | 1250.00 |
		| BLAKE  | 2850.00 |
		| CLARK  | 2450.00 |
		| SCOTT  | 3000.00 |
		| KING   | 5000.00 |
		| TURNER | 1500.00 |
		| ADAMS  | 1100.00 |
		| JAMES  |  950.00 |
		| FORD   | 3000.00 |
		| MILLER | 1300.00 |
		+--------+---------+

		//格式化數字：format(數字, '格式')
			select ename,format(sal, '$999,999') as sal from emp;
			+--------+-------+
			| ename  | sal   |
			+--------+-------+
			| SMITH  | 800   |
			| ALLEN  | 1,600 |
			| WARD   | 1,250 |
			| JONES  | 2,975 |
			| MARTIN | 1,250 |
			| BLAKE  | 2,850 |
			| CLARK  | 2,450 |
			| SCOTT  | 3,000 |
			| KING   | 5,000 |
			| TURNER | 1,500 |
			| ADAMS  | 1,100 |
			| JAMES  | 950   |
			| FORD   | 3,000 |
			| MILLER | 1,300 |
			+--------+-------+

date_format

：将date類型轉換成具有一定格式的varchar字元串類型。

drop table if exists t_user;
	create table t_user(
		id int,
		name varchar(32),
		birth date // 生日也可以使用date日期類型
	);

	create table t_user(
		id int,
		name varchar(32),
		birth char(10) // 生日可以使用字元串，沒問題。
	);

	//生日：1990-10-11 （10個字元）

	//注意：資料庫中的有一條命名規範：
	//所有的辨別符都是全部小寫，單詞和單詞之間使用下劃線進行銜接。

10.3.1.3.1 str_to_date

str_to_date

：将字元串varchar類型轉換成date類型

//插入資料？
		insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990'); // 1990年10月1日
		//出問題了：原因是類型不比對。資料庫birth是date類型，這裡給了一個字元串varchar

怎麼辦？可以使用str_to_date函數進行類型轉換。

str_to_date

函數可以将字元串轉換成日期類型date？

文法格式：

str_to_date(‘字元串日期’, ‘日期格式’)

mysql的日期格式：
		%Y	年
		%m 月
		%d 日
		%h	時
		%i	分
		%s	秒

是以要如何解決上面類型不比對的問題呢？

進行類型轉化即可

insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

//str_to_date函數可以把字元串varchar轉換成日期date類型資料，通常使用在插入insert方面，因為插入的時候需要一個日期類型的資料，需要通過該函數将字元串轉換成date。

是不是感覺非常麻煩？

好消息？

如果你提供的日期字元串是這個格式，str_to_date函數就不需要了！！！

%Y-%m-%d

insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');

可以直接進行類型轉化，隻要是年-月-日的格式

查詢的時候可以以某個特定的日期格式展示嗎？

date_format

這個函數可以将日期類型轉換成特定格式的字元串。

10.3.1.3.2 date_format

查詢的時候可以以某個特定的日期格式展示嗎？

date_format

這個函數可以将日期類型轉換成特定格式的字元串。

舉例：

select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;
+------+----------+------------+
| id   | name     | birth      |
+------+----------+------------+
|    1 | zhangsan | 10/01/1990 |
|    2 | lisi     | 10/01/1990 |
+------+----------+------------+

date_format函數怎麼用？

date_format(日期類型資料, ‘日期格式’)

這個函數通常使用在查詢日期方面，設定展示的日期格式。

mysql> select id,name,birth from t_user;
		+------+----------+------------+
		| id   | name     | birth      |
		+------+----------+------------+
		|    1 | zhangsan | 1990-10-01 |
		|    2 | lisi     | 1990-10-01 |
		+------+----------+------------+
		//以上的SQL語句實際上是進行了預設的日期格式化，自動将資料庫中的date類型轉換成varchar類型,并且采用的格式是mysql預設的日期格式：'%Y-%m-%d'

		select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;
		
		//java中的日期格式？yyyy-MM-dd HH:mm:ss SSS

10.3.2 修改資料

文法格式：

update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 條件;

注意：沒有條件限制會導緻所有資料全部更新。

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
	+------+----------+------------+---------------------+
	| id   | name     | birth      | create_time         |
	+------+----------+------------+---------------------+
	|    1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
	|    2 | jack     | 2000-10-11 | 2020-03-18 15:51:23 |
	+------+----------+------------+---------------------+

	update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;

	//更新所有？
	update t_user set name = 'abc';

10.3.3 删除資料

文法格式？

delete from 表名 where 條件;

注意：沒有條件，整張表的資料會全部删除！

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 删除所有！

11. 雜知識

11.1 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(id,name,birth,create_time) values
		(1,'zs','1980-10-11',now()), 
		(2,'lisi','1981-10-11',now()),
		(3,'wangwu','1982-10-11',now());

		文法：insert into t_user(字段名1,字段名2) values(),(),(),();

	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 |
	+------+--------+------------+---------------------+

11.2 快速建立表[把查詢結果當做表建立]

mysql> create table emp2 as select * from emp;

	//原理：
		//将一個查詢結果當做一張表建立！！！！！
		//這個可以完成表的快速複制！！！！
		//表建立出來，同時表中的資料也存在了！！！
	
	create table mytable as select empno,ename from emp where job = 'MANAGER';

11.3 将查詢結果插入到一張表當中？insert相關【了解内容】

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   |
	+--------+------------+----------+

11.4 快速删除表中的資料

有種方式是

delete

，這種方式比較慢，而

truncate

這種就非常迅速

delete與truncate的原理、優點與缺點

delete語句删除資料的原理？

delete屬于DML語句！！！

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

這種删除缺點是：删除效率比較低。

這種删除優點是：支援復原，後悔了可以再恢複資料！！！
truncate語句删除資料的原理？

這種删除效率比較高，表被一次截斷，實體删除。

這種删除缺點：不支援復原。

這種删除優點：快速。

用法：
truncate table dept_bak; （這種操作屬于DDL操作）

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

删除的時候，使用

delete

，也許需要執行1個小時才能删除完！效率較低。

可以選擇使用

truncate

删除表中的資料。隻需要不到1秒鐘的時間就删除結束。效率較高。

但是使用

truncate

之前，必須仔細詢問客戶是否真的要删除，并警告删除之後不可恢複！

truncate

是删除表中的資料，表還在！

删除表操作？

11.5 對表結構的增删改

什麼是對表結構的修改？

添加一個字段，删除一個字段，修改一個字段！！！

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

屬于DDL語句

DDL包括：create drop alter

第一：在實際的開發中，需求一旦确定之後，表一旦設計好之後，很少的進行表結構的修改。因為開發進行中的時候，修改表結構，成本比較高。

修改表的結構，對應的java代碼就需要進行大量的修改。成本是比較高的。這個責任應該由設計人員來承擔！
第二：由于修改表結構的操作很少，是以我們不需要掌握，如果有一天真的要修改表結構，你可以使用工具！！！！

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

12. 限制（非常重要）

12.1 限制的定義

限制對應的英語單詞：

constraint

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

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

12.2 限制的種類

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

我們這裡重點學習四個限制：

not null
unique
primary key
foreign key

知識點：列級限制與表級限制

列級限制：限制直接添加到列後面的，叫做列級限制
表級限制：限制沒有添加在列的後面，這種限制被稱為表級限制

舉例：

create table t_vip(
			id int,
			name varchar(255) unique,  // 限制直接添加到列後面的，叫做列級限制。
			email varchar(255) unique
		);

	create table t_vip(
				id int,
				name varchar(255),
				email varchar(255),
				unique(name,email) // 限制沒有添加在列的後面，這種限制被稱為表級限制。
			);

什麼時候使用表級限制呢？

需要給多個字段聯合起來添加某一個限制的時候，需要使用表級限制

12.3 非空限制：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');

	//此處name字段為空,違反了非空限制
	insert into t_vip(id) values(3);
	ERROR 1364 (HY000): Field 'name' doesn't have a default value

12.4 唯一性限制: 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','[email protected]');
	insert into t_vip(id,name,email) values(2,'lisi','[email protected]');
	insert into t_vip(id,name,email) values(3,'wangwu','[email protected]');
	select * from t_vip;


	//name字段為'wangwu',與之前的重複了，違反了唯一性限制
	insert into t_vip(id,name,email) values(4,'wangwu','[email protected]');
	ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'


	//唯一性限制不代表不可以為null
	insert into t_vip(id) values(4);
	insert into t_vip(id) values(5);
	+------+----------+------------------+
	| id   | name     | email            |
	+------+----------+------------------+
	|    1 | zhangsan | [email protected] |
	|    2 | lisi     | [email protected]     |
	|    3 | wangwu   | [email protected]   |
	|    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','[email protected]');
		insert into t_vip(id,name,email) values(2,'zhangsan','[email protected]');

正确做法：

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','[email protected]');
			insert into t_vip(id,name,email) values(2,'zhangsan','[email protected]');
			select * from t_vip;

			//name和email兩個字段聯合起來唯一！！！
			insert into t_vip(id,name,email) values(3,'zhangsan','[email protected]');
			ERROR 1062 (23000): Duplicate entry '[email protected]' 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。

12.5 主鍵限制:（primary key，簡稱PK）非常重要

主鍵限制的相關術語？

主鍵限制：就是一種限制。
主鍵字段：該字段上添加了主鍵限制，這樣的字段叫做：主鍵字段
主鍵值：主鍵字段中的每一個值都叫做：主鍵值。

什麼是主鍵？有啥用？

主鍵值是每一行記錄的唯一辨別。
主鍵值是每一行記錄的身份證号！！！

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

主鍵的特征：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','[email protected]');
		insert into t_vip(id,name,email) values(1,'lisi','[email protected]');

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

		//在實際開發中不建議使用：複合主鍵。建議使用單一主鍵！
		//因為主鍵值存在的意義就是這行記錄的身份證号，隻要意義達到即可，單一主鍵可以做到。
		//複合主鍵比較複雜，不建議使用！！！

一個表中主鍵限制能加兩個嗎？

不可以，主鍵是唯一的

主鍵值建議使用：

int
bigint
char等類型

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

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

在建表時主鍵後面添加：

auto_increment

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 |
		+----+----------+

12.6 外鍵限制（foreign key，簡稱FK）非常重要

外鍵限制涉及到的相關術語：

外鍵限制：一種限制（foreign key）
外鍵字段：該字段上添加了外鍵限制
外鍵值：外鍵字段當中的每一個值。

如何表述外鍵？

create table_student(
		no int primary key auto_increment,
		cno int,
		foreign key(cno) references t_class(classno ));

13. 事務(超級無敵重要)

13.1 事務的定義

一個事務其實就是一個完整的業務邏輯。

是一個最小的工作單元。不可再分。

什麼是一個完整的業務邏輯？
	假設轉賬，從A賬戶向B賬戶中轉賬10000.
	将A賬戶的錢減去10000（update語句）
	将B賬戶的錢加上10000（update語句）
	這就是一個完整的業務邏輯。
	
	以上的操作是一個最小的工作單元，要麼同時成功，要麼同時失敗，不可再分。
	這兩個update語句要求必須同時成功或者同時失敗，這樣才能保證錢是正确的。

13.2 什麼和事務有關系？

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

insert
delete
update

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

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

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

資料安全第一位！！！

13.3 事務的本質到底什麼是事務呢？

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

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

13.4 事務的工作原理

InnoDB存儲引擎：提供一組用來記錄事務性活動的日志檔案（檔案裡面就是相應操作的sql語句）

檔案内容：

事務開啟了：
insert
insert
insert
delete
update
update
update
事務結束了！（結束标志：成功送出事務和失敗復原事務都是事務結束）

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

在事務的執行過程中，我們可以送出事務，也可以復原事務。

送出事務？

清空事務性活動的日志檔案，将資料全部徹底持久化到資料庫表中。送出事務标志着，事務的結束。并且是一種全部成功的結束
復原事務？

将之前所有的DML操作全部撤銷，并且清空事務性活動的日志檔案復原事務标志着，事務的結束。并且是一種全部失敗的結束

13.5 怎麼送出事務，怎麼復原事務

送出事務：commit; 語句
復原事務：rollback; 語句（復原永遠都是隻能復原到上一次的送出點！）

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

送出事務：

先開啟事務，代碼： start transaction ，因為Mysql的預設是自動送出事務的，執行一句語句就送出，而通過代碼 start transaction 之後就停止了其預設自動送出行為，友善我們輸入多條sql語句并手動确定是否送出還是復原
開啟事務後，寫完對應的sql語句，輸入代碼： commit ，送出事務，注意送出事務後就不可以復原了，隻有未送出的時候可以復原事務
送出事務舉例：

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)

復原事務：

先開啟事務，代碼： start transaction ，因為Mysql的預設是自動送出事務的，執行一句語句就送出，而通過代碼 start transaction 之後就停止了其預設自動送出行為，友善我們輸入多條sql語句并手動确定是否送出還是復原
開啟事務後，寫完對應的sql語句，輸入代碼： rollback ，送出事務，注意送出事務後就不可以復原了，隻有未送出的時候可以復原事務
復原事務舉例：

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)

13.6 事務的特性

A：原子性

說明事務是最小的工作單元。不可再分。
C：一緻性

所有事務要求，在同一個事務當中，所有操作必須同時成功，或者同時失敗，以保證資料的一緻性。
I：隔離性

A事務和B事務之間具有一定的隔離。教室A和教室B之間有一道牆，這道牆就是隔離性。
D：持久性

事務最終結束的一個保障。事務送出，就相當于将沒有儲存到硬碟上的資料儲存到硬碟上！

13.7 事務的隔離性

簡單了解：

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

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

事務的隔離級别：

4個級别

讀未送出：read uncommitted（最低的隔離級别）《沒有送出就讀到了》

什麼是讀未送出？

事務A可以讀取到事務B未送出的資料。

這種隔離級别存在的問題就是：

髒讀現象！(Dirty Read)，我們稱讀到了髒資料。

這種隔離級别一般都是理論上的，大多數的資料庫隔離級别都是二檔起步！
讀已送出：read committed**《送出之後才能讀到》**

什麼是讀已送出？

事務A隻能讀取到事務B送出之後的資料。

這種隔離級别解決了什麼問題？

解決了髒讀的現象。

這種隔離級别存在什麼問題？

不可重複讀取資料。

什麼是不可重複讀取資料呢？

在事務開啟之後，第一次讀到的資料是3條，目前事務還沒有結束（自己的事務），可能第二次再讀取的時候，讀到的資料是4條，3不等于4，稱為不可重複讀取。

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

oracle資料庫預設的隔離級别是：read committed
可重複讀：repeatable read 《送出之後也讀不到，永遠讀取的都是剛開啟事務時的資料》

什麼是可重複讀取？

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

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

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

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

可以會出現幻影讀。

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

早晨9點開始開啟了事務，隻要事務不結束，到晚上9點，讀到的資料還是那樣！讀到的是假象。不夠絕對的真實。

mysql中預設的事務隔離級别就是這個！！！！！！！！！！！
序列化/串行化：serializable（最高的隔離級别）

這是最高隔離級别，效率最低，解決了所有的問題。

這種隔離級别表示事務排隊，不能并發！

synchronized，可以了解為線程同步（事務同步）

每一次讀取到的資料都是最真實的，并且效率是最低的。

14. 索引

14.1 什麼是索引

索引是在資料庫表的字段上添加的，是為了提高查詢效率存在的一種機制。
一張表的一個字段可以添加一個索引，當然，多個字段聯合起來也可以添加索引。
索引相當于一本書的目錄，是為了縮小掃描範圍而存在的一種機制。

對于一本字典來說，查找某個漢字有兩種方式：
	第一種方式：一頁一頁挨着找，直到找到為止，這種查找方式屬于全字典掃描。
	效率比較低。
	第二種方式：先通過目錄（索引）去定位一個大概的位置，然後直接定位到這個
	位置，做局域性掃描，縮小掃描的範圍，快速的查找。這種查找方式屬于通過
	索引檢索，效率較高。

舉例了解：

t_user
	id(idIndex)	name(nameIndex)	email(emailIndex)		address  (emailAddressIndex)
	----------------------------------------------------------------------------------
	1				zhangsan...
	2				lisi
	3				wangwu
	4				zhaoliu
	5				hanmeimei
	6				jack

	select * from t_user where name = 'jack';

以上的這條SQL語句會去name字段上掃描，為什麼？

因為查詢條件是：name=‘jack’

如果name字段上沒有添加索引（目錄），或者說沒有給name字段建立索引，MySQL會進行全掃描，會将name字段上的每一個值都比對一遍，效率比較低。

MySQL在查詢方面主要就是兩種方式：

第一種方式：全表掃描
第二種方式：根據索引檢索。

注意：

在實際中，漢語字典前面的目錄是排序的，按照a b c d e f…排序，為什麼排序呢？因為隻有排序了才會有區間查找這一說！（縮小掃描範圍其實就是掃描某個區間罷了！）

在mysql資料庫當中索引也是需要排序的，并且這個是以的排序和TreeSet資料結構相同。TreeSet（TreeMap）底層是一個自平衡的二叉樹！在mysql當中索引是一個B-Tree資料結構。

遵循左小又大原則存放。采用中序周遊方式周遊取資料。

14.2 索引的實作原理

//假設有一張使用者表：t_user

	id(PK)					name			每一行記錄在硬碟上都有實體存儲編号
	----------------------------------------------------------------------------------
	100						zhangsan					0x1111
	120						lisi						0x2222
	99						wangwu						0x8888
	88						zhaoliu						0x9999
	101						jack						0x6666
	55						lucy						0x5555
	130						tom							0x7777

提醒1：在任何資料庫當中主鍵上都會自動添加索引對象，id字段上自動有索引，因為id是PK。另外在mysql當中，一個字段上如果有unique限制的話，也會自動建立索引對象
提醒2：在任何資料庫當中，任何一張表的任何一條記錄在硬碟存儲上都有一個硬碟的實體存儲編号。
提醒3：在mysql當中，索引是一個單獨的對象，不同的存儲引擎以不同的形式存在，在MyISAM存儲引擎中，索引存儲在一個.MYI檔案中。在InnoDB存儲引擎中，索引存儲在一個邏輯名稱叫做tablespace的當中。在MEMORY存儲引擎當中索引，被存儲在記憶體當中。不管索引存儲在哪裡，索引在mysql當中都是一個樹的形式存在。（自平衡二叉樹：B-Tree）

在mysql當中，主鍵上，以及unique字段上都會自動添加索引的！！！！

14.3 什麼時候添加索引

什麼條件下，我們會考慮給字段添加索引呢？

條件1：資料量龐大（到底有多麼龐大算龐大，這個需要測試，因為每一個硬體環境不同）
條件2：該字段經常出現在where的後面，以條件的形式存在，也就是說這個字段總是被掃描。
條件3：該字段很少的DML(insert delete update)操作。（因為DML之後，索引需要重新排序。）

建議不要随意添加索引，因為索引也是需要維護的，太多的話反而會降低系統的性能。

建議通過主鍵查詢，建議通過unique限制的字段進行查詢，效率是比較高的。

14.4 索引的建立、删除

建立索引：

mysql>

create index emp_ename_index on emp(ename);

給emp表的ename字段添加索引，起名：emp_ename_index

删除索引：

mysql>

drop index emp_ename_index on emp;

将emp表上的emp_ename_index索引對象删除。

14.5 檢視SQL語句是否使用了索引進行檢索？

通過添加

explain

即可

mysql> explain select * from emp where ename = 'KING';
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
	+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
	//掃描14條記錄：說明沒有使用索引。type=ALL

	mysql> create index emp_ename_index on emp(ename);

	mysql> explain select * from emp where ename = 'KING';
	+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
	| id | select_type | table | type | possible_keys   | key             | key_len | ref   | rows | Extra       |
	+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
	|  1 | SIMPLE      | emp   | ref  | emp_ename_index | emp_ename_index | 33      | const |    1 | Using where |
	+----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+

14.6 索引的失效情況

失效的第1種情況：

select * from emp where ename like ‘%T’;

ename上即使添加了索引，也不會走索引，為什麼？

原因是因為模糊比對當中以“%”開頭了！

盡量避免模糊查詢的時候以“%”開始。

這是一種優化的手段/政策。

mysql> explain select * from emp where ename like '%T';
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第2種情況：

使用or的時候會失效，如果使用or那麼要求or兩邊的條件字段都要有索引，才會走索引，如果其中一邊有一個字段沒有索引，那麼另一個字段上的索引也會失效。是以這就是為什麼不建議使用or的原因。

mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER';
		+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
		| id | select_type | table | type | possible_keys   | key  | key_len | ref  | rows | Extra       |
		+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+
		|  1 | SIMPLE      | emp   | ALL  | emp_ename_index | NULL | NULL    | NULL |   14 | Using where |
		+----+-------------+-------+------+-----------------+------+---------+------+------+-------------+

失效的第3種情況：

使用複合索引的時候，沒有使用左側的列查找(就是沒有用上左側的列來where這種)，索引失效

什麼是複合索引？

兩個字段，或者更多的字段聯合起來添加一個索引，叫做複合索引

create index emp_job_sal_index on emp(job,sal);
		
		mysql> explain select * from emp where job = 'MANAGER';
		+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
		| id | select_type | table | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
		+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
		|  1 | SIMPLE      | emp   | ref  | emp_job_sal_index | emp_job_sal_index | 30      | const |    3 | Using where |
		+----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+
		
		mysql> explain select * from emp where sal = 800;
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第4種情況：

在where當中索引列參加了運算，索引失效。

mysql> create index emp_sal_index on emp(sal);

		explain select * from emp where sal = 800;
		+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
		| id | select_type | table | type | possible_keys | key           | key_len | ref   | rows | Extra       |
		+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+
		|  1 | SIMPLE      | emp   | ref  | emp_sal_index | emp_sal_index | 9       | const |    1 | Using where |
		+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

		mysql> explain select * from emp where sal+1 = 800;
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

失效的第5種情況：

在where當中索引列使用了函數

explain select * from emp where lower(ename) = 'smith';
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
		|  1 | SIMPLE      | emp   | ALL  | NULL          | NULL | NULL    | NULL |   14 | Using where |
		+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

14.7 索引的分類

索引是各種資料庫進行優化的重要手段。優化的時候優先考慮的因素就是索引。

索引在資料庫當中分了很多類？

單一索引：一個字段上添加索引。
複合索引：兩個字段或者更多的字段上添加索引
主鍵索引：主鍵上添加索引。
唯一性索引：具有unique限制的字段上添加索引

…

注意：唯一性比較弱的字段上添加索引用處不大。

15. 視圖

15.1 視圖的定義

view:站在不同的角度去看待同一份資料。

15.2 視圖的建立與删除

建立視圖對象：

删除視圖對象：

注意：隻有DQL語句才能以view的形式建立。

create view view_name as 這裡的語句必須是DQL語句;

15.3 用視圖做什麼

我們可以面向視圖對象進行增删改查，對視圖對象的增删改查，會導緻原表被操作！（視圖的特點：通過對視圖的操作，會影響到原表資料。）

//面向視圖查詢
	select * from dept2_view; 

	// 面向視圖插入
	insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

	// 查詢原表資料
	mysql> select * from dept2;
	+--------+------------+----------+
	| DEPTNO | DNAME      | LOC      |
	+--------+------------+----------+
	|     10 | ACCOUNTING | NEW YORK |
	|     20 | RESEARCH   | DALLAS   |
	|     30 | SALES      | CHICAGO  |
	|     40 | OPERATIONS | BOSTON   |
	|     60 | SALES      | BEIJING  |
	+--------+------------+----------+

	// 面向視圖删除
	mysql> delete from dept2_view;

	// 查詢原表資料
	mysql> select * from dept2;
	Empty set (0.00 sec)
	

	// 建立視圖對象
	create view 
		emp_dept_view
	as
		select 
			e.ename,e.sal,d.dname
		from
			emp e
		join
			dept d
		on
			e.deptno = d.deptno;

	// 查詢視圖對象
	mysql> select * from emp_dept_view;
	+--------+---------+------------+
	| ename  | sal     | dname      |
	+--------+---------+------------+
	| CLARK  | 2450.00 | ACCOUNTING |
	| KING   | 5000.00 | ACCOUNTING |
	| MILLER | 1300.00 | ACCOUNTING |
	| SMITH  |  800.00 | RESEARCH   |
	| JONES  | 2975.00 | RESEARCH   |
	| SCOTT  | 3000.00 | RESEARCH   |
	| ADAMS  | 1100.00 | RESEARCH   |
	| FORD   | 3000.00 | RESEARCH   |
	| ALLEN  | 1600.00 | SALES      |
	| WARD   | 1250.00 | SALES      |
	| MARTIN | 1250.00 | SALES      |
	| BLAKE  | 2850.00 | SALES      |
	| TURNER | 1500.00 | SALES      |
	| JAMES  |  950.00 | SALES      |
	+--------+---------+------------+

	// 面向視圖更新
	update emp_dept_view set sal = 1000 where dname = 'ACCOUNTING';

	// 原表資料被更新
	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 | 1000.00 |    NULL |     10 |
	|  7788 | SCOTT  | ANALYST   | 7566 | 1987-04-19 | 3000.00 |    NULL |     20 |
	|  7839 | KING   | PRESIDENT | NULL | 1981-11-17 | 1000.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 | 1000.00 |    NULL |     10 |
	+-------+--------+-----------+------+------------+---------+---------+--------+

15.4 視圖在實際開發中的作用

友善，簡化開發，利于維護

create view 
			emp_dept_view
		as
			select 
				e.ename,e.sal,d.dname
			from
				emp e
			join
				dept d
			on
				e.deptno = d.deptno;

假設有一條非常複雜的SQL語句，而這條SQL語句需要在不同的位置上反複使用。

每一次使用這個sql語句的時候都需要重新編寫，很長，很麻煩，怎麼辦？

可以把這條複雜的SQL語句以視圖對象的形式建立，在需要編寫這條SQL語句的位置直接使用視圖對象，可以大大簡化開發，并且利于後期的維護，因為修改的時候也隻需要修改一個位置就行，隻需要修改視圖對象所映射的SQL語句。

我們以後面向視圖開發的時候，使用視圖的時候可以像使用table一樣，可以對視圖進行增删改查等操作。視圖不是在記憶體當中，視圖對象也是存儲在硬碟上的，不會消失。

再提醒一下：

視圖對應的語句隻能是DQL語句，但是視圖對象建立完成之後，可以對視圖進行增删改查等操作。

小插曲：

增删改查，又叫做：CRUD。

CRUD是在公司中程式員之間溝通的術語。一般我們很少說增删改查。

一般都說CRUD。

C:Create（增）
		R:Retrive（查：檢索）
		U:Update（改）
		D:Delete（删）

16. 資料庫設計

16.1 資料庫設計三範式？

第一範式：要求任何一張表必須有主鍵，每一個字段原子性不可再分。

第二範式：建立在第一範式的基礎之上，要求所有非主鍵字段完全依賴主鍵，不要産生部分依賴。

第三範式：建立在第二範式的基礎之上，要求所有非主鍵字段直接依賴主鍵，不要産生傳遞依賴。

聲明：三範式是面試官經常問的，是以一定要熟記在心！

設計資料庫表的時候，按照以上的範式進行，可以避免表中資料的備援，空間的浪費。

16.2 第一範式

最核心，最重要的範式，所有表的設計都需要滿足。

必須有主鍵，并且每一個字段都是原子性不可再分。

學生編号 學生姓名 聯系方式
	------------------------------------------
	1001		張三		[email protected],1359999999
	1002		李四		[email protected],13699999999
	1001		王五		[email protected],13488888888

	//以上是學生表，滿足第一範式嗎？
	//不滿足，第一：沒有主鍵。第二：聯系方式可以分為郵箱位址和電話
	
	學生編号(pk) 學生姓名	郵箱位址			聯系電話
	----------------------------------------------------
	1001				張三		[email protected]	1359999999
	1002				李四		[email protected]	13699999999
	1003				王五		[email protected]		13488888888

16.3 第二範式

建立在第一範式的基礎之上，要求所有非主鍵字段必須完全依賴主鍵，不要産生部分依賴。

學生編号 學生姓名 教師編号 教師姓名
	----------------------------------------------------
	1001			張三		001		王老師
	1002			李四		002		趙老師
	1003			王五		001		王老師
	1001			張三		002		趙老師

	//這張表描述了學生和老師的關系：（1個學生可能有多個老師，1個老師有多個學生）
	//這是非常典型的：多對多關系！

	//分析以上的表是否滿足第一範式？
		//不滿足第一範式。
	
	//怎麼滿足第一範式呢？修改

	學生編号+教師編号(pk)		學生姓名  教師姓名
	----------------------------------------------------
	1001			001				張三			王老師
	1002			002				李四			趙老師
	1003			001				王五			王老師
	1001			002				張三			趙老師

	//學生編号 教師編号，兩個字段聯合做主鍵，複合主鍵（PK: 學生編号+教師編号）
	//經過修改之後，以上的表滿足了第一範式。但是滿足第二範式嗎？
	//不滿足，“張三”依賴1001，“王老師”依賴001，顯然産生了部分依賴。
	//産生部分依賴有什麼缺點？
	//資料備援了。空間浪費了。“張三”重複了，“王老師”重複了。
	
	//為了讓以上的表滿足第二範式，你需要這樣設計：
	//使用三張表來表示多對多的關系！！！！
	
		學生表
		學生編号(pk)		學生名字
		------------------------------------
		1001					張三
		1002					李四
		1003					王五
		
		教師表
		教師編号(pk)		教師姓名
		--------------------------------------
		001					王老師
		002					趙老師

		學生教師關系表
		id(pk)			學生編号(fk)			教師編号(fk)
		------------------------------------------------------
		1						1001						001
		2						1002						002
		3						1003						001
		4						1001						002

背口訣：

多對多怎麼設計？

多對多，三張表，關系表兩個外鍵！！！！！！！！！！！！！！！

16.4 第三範式

第三範式建立在第二範式的基礎之上，要求所有非主鍵字典必須直接依賴主鍵，不要産生傳遞依賴。

學生編号（PK） 學生姓名 班級編号  班級名稱
	---------------------------------------------------------
		1001				張三		01			一年一班
		1002				李四		02			一年二班
		1003				王五		03			一年三班
		1004				趙六		03			一年三班
	
	//以上表的設計是描述：班級和學生的關系。很顯然是1對多關系！
	//一個教室中有多個學生。

	//分析以上表是否滿足第一範式？
	//滿足第一範式，有主鍵。
	
	//分析以上表是否滿足第二範式？
	//滿足第二範式，因為主鍵不是複合主鍵，沒有産生部分依賴。主鍵是單一主鍵。
	
	//分析以上表是否滿足第三範式？
	//第三範式要求：不要産生傳遞依賴！
	//一年一班依賴01，01依賴1001，産生了傳遞依賴。
	//不符合第三範式的要求。産生了資料的備援。
	
	//那麼應該怎麼設計一對多呢？

		班級表：一
		班級編号(pk)				班級名稱
		----------------------------------------
		01								一年一班
		02								一年二班
		03								一年三班

		學生表：多

		學生編号（PK） 學生姓名 班級編号(fk)
		-------------------------------------------
		1001				張三			01			
		1002				李四			02			
		1003				王五			03			
		1004				趙六			03

背口訣：

一對多，兩張表，多的表加外鍵！！！！！！！！！！！！

16.5 總結表的設計

一對多：一對多，兩張表，多的表加外鍵！！！！！！！！！！！！
多對多：多對多，三張表，關系表兩個外鍵！！！！！！！！！！！！！！！
一對一：一對一放到一張表中不就行了嗎？為啥還要拆分表？在實際的開發中，可能存在一張表字段太多，太龐大。這個時候要拆分表。

//一對一怎麼設計？
			//沒有拆分表之前：一張表
				t_user
				
				id		login_name		login_pwd		real_name		email				address........
				---------------------------------------------------------------------------
				1			zhangsan		123				張三				zhangsan@xxx
				2			lisi			123				李四				lisi@xxx
				...
			
			//這種龐大的表建議拆分為兩張：
			
				t_login 登入資訊表
				id(pk)		login_name		login_pwd	
				---------------------------------
				1				zhangsan		123			
				2				lisi			123			

				t_user 使用者詳細資訊表
				id(pk)		real_name		email				address........	login_id(fk+unique)
				-----------------------------------------------------------------------------------------
				100			張三				zhangsan@xxx								1
				200			李四				lisi@xxx										2

口訣：一對一，外鍵唯一！！！！！！！！！！(即外鍵的限制有unique限制)

16.6 實際開發中

資料庫設計三範式是理論上的。

實踐和理論有的時候有偏差。

最終的目的都是為了滿足客戶的需求，有的時候會拿備援換執行速度。

因為在sql當中，表和表之間連接配接次數越多，效率越低。（笛卡爾積）

有的時候可能會存在備援，但是為了減少表的連接配接次數，這樣做也是合理的，
并且對于開發人員來說，sql語句的編寫難度也會降低。

面試的時候把這句話說上：他就不會認為你是初級程式員了！

17. 存儲過程和函數

17.1 存儲過程和函數概述

存儲過程和函數是事先經過編譯并存儲在資料庫中的一段 SQL 語句的集合，調用存儲過程和函數可以簡化應用開

發人員的很多工作，減少資料在資料庫和應用伺服器之間的傳輸，對于提高資料處理的效率是有好處的。

存儲過程和函數的差別在于函數必須有傳回值，而存儲過程沒有。

函數：是一個有傳回值的過程；

過程：是一個沒有傳回值的函數；

17.2 建立存儲過程

舉例：

create procedure pro_test1() 
begin
select 'Hello Mysql' ; 
end;

17.3 調用存儲過程

17.4 檢視存儲過程

-- 查詢db_name資料庫中的所有的存儲過程
 select name from mysql.proc where db='db_name'; 
 
 -- 查詢存儲過程的狀态資訊 
 show procedure status; 
 
 -- 查詢某個存儲過程的定義 
 show create procedure test.pro_test1 \G;

17.5 删除存儲過程

17.6 文法

17.6.1 變量

DECLARE

通過 DECLARE 可以定義一個局部變量，該變量的作用範圍隻能在 BEGIN…END 塊中

示例：

create procedure pro_test2() 
begin
declare num int default 5; 
select num+ 10; 
end;

SET

直接指派使用 SET，可以賦常量或者賦表達式，具體文法如下：

示例：

CREATE PROCEDURE pro_test3()
  BEGIN DECLARE NAME VARCHAR(20);
  SET NAME = 'MYSQL'; 
  SELECT NAME ; 
  END;

select … into方式進行指派

CREATE PROCEDURE pro_test5()
 BEGIN
 declare countnum int; 
 select count(*) into countnum from city;
 select countnum; 
 END;

17.6.2 if條件判斷

文法結構 :

if search_condition then statement_list
[elseif search_condition then statement_list] ...
[else statement_list] end if;

舉例：

//需求
根據定義的身高變量，判定目前身高的所屬的身材類型 
180 及以上 ----------> 身材高挑 
170 - 180 ---------> 标準身材
170 以下 ----------> 一般身材


//代碼
create procedure pro_test6()
 begin 
 declare height int default 175; 
 declare description varchar(50); 
 if height >= 180 then 
 set description = '身材高挑'; 
 elseif height >= 170 and height < 180 then 
 set description = '标準身材';
  else
  set description = '一般身材'; 
  end if; 
  select description ; 
  end;

調用結果為：

MySql學習筆記1. 一些相關常識2. 查詢3. 資料處理函數4. 連接配接查詢(最重要的)5. 子查詢6. union合并查詢7. limit（非常重要）8. 分頁9. 關于DQL語句的大總結10. 對表、資料的操作11. 雜知識12. 限制（非常重要）13. 事務(超級無敵重要)14. 索引15. 視圖16. 資料庫設計17. 存儲過程和函數

17.6.3 傳遞參數

文法格式 :

create procedure procedure_name([in/out/inout] 參數名 參數類型) ... 
IN : 該參數可以作為輸入，也就是需要調用方傳入值 , 預設 
OUT: 該參數作為輸出，也就是該參數可以作為傳回值 INOUT: 既可以作為輸入參數，也可以作為輸出參數

IN - 輸入舉例

//需求
//根據定義的身高變量，判定目前身高的所屬的身材類型

create procedure pro_test5(in height int) 
begin
declare description varchar(50) default ''; 
if height >= 180 then 
set description='身材高挑'; 
elseif height >= 170 and height < 180 then 
set description='标準身材'; 
else
set description='一般身材';
 end if; 
 select concat('身高 ', height , '對應的身材類型為:',description);
end;

OUT-輸出舉例

//需求
//根據傳入的身高變量，擷取目前身高的所屬的身材類型

create procedure pro_test5(in height int , out description varchar(100))
 begin 
 if height >= 180 then
  set description='身材高挑'; 
  elseif height >= 170 and height < 180 then 
  set description='标準身材'; 
  else
  set description='一般身材'; 
  end if; 
  end;

//調用
call pro_test5(168, @description);//拿個變量來接收它
 select @description;

小知識

@description : 這種變量要在變量名稱前面加上“@”符号，叫做使用者會話變量，代表整個會話過程他都是有作用的，這個類似于全局變量一樣。

@@global.sort_buffer_size : 這種在變量前加上 “@@” 符号, 叫做系統變量

17.6.4 case結構

文法結構 :

方式一 : 
CASE case_value 
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] 
 ... 
[ELSE statement_list] 
END CASE; 

方式二 : 
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE;

舉例：

//需求：
//給定一個月份, 然後計算出所在的季度

create procedure pro_test9(month int)
 begin 
 declare result varchar(20); 
 case
 when month >= 1 and month <=3 then 
 set result = '第一季度'; when month >= 4 and month <=6 then 
 set result = '第二季度'; when month >= 7 and month <=9 then 
 set result = '第三季度'; when month >= 10 and month <=12 then   set result = '第四季度';
 end case;  
select concat('您輸入的月份為 :', month , ' , 該月份為 : ' , result) as content ;
end;

17.6.5 while循環

文法結構:

while search_condition do 
statement_list 
end while;

舉例：

//需求
//計算從1加到n的值

create procedure pro_test8(n int)
begin 
declare total int default 0; 
declare num int default 1; 
while num<=n do 
set total = total + num; 
set num = num + 1; 
end while; 
select total; 
end;

17.6.6 repeat結構

有條件的循環控制語句, 當滿足條件的時候退出循環。while 是滿足條件才執行，repeat 是滿足條件就退出循環。

文法結構:

REPEAT 
statement_list 
UNTIL search_condition 
END REPEAT;

舉例：

//需求
//計算從1加到n的值

create procedure pro_test10(n int) 
begin declare total int default 0;
 repeat set total = total + n; 
 set n = n - 1; 
 until n=0 
 end repeat; 
 select total ; 
 end;

17.6.7 loop語句

LOOP 實作簡單的循環，退出循環的條件需要使用其他的語句定義，通常可以使用 LEAVE 語句實作，具體文法如下：

[begin_label:] LOOP 
statement_list 
END LOOP [end_label]

如果不在 statement_list 中增加退出循環的語句，那麼 LOOP 語句可以用來實作簡單的死循環。

17.6.8 leave語句

用來從标注的流程構造中退出，通常和 BEGIN … END 或者循環一起使用。

下面是一個使用 LOOP 和 LEAVE 的簡單例子 , 退出循環：

CREATE PROCEDURE pro_test11(n int) 
BEGIN 
declare total int default 0; 
ins: LOOP 
IF n <= 0 
then leave ins; 
END IF; 
set total = total + n; 
set n = n - 1; 
END LOOP ins; 
select total; 
END;

17.6.9 遊标/光标

遊标是用來存儲查詢結果集的資料類型 , 在存儲過程和函數中可以使用光标對結果集進行循環的處理。光标的使用包括光标的聲明、OPEN、FETCH 和 CLOSE，其文法分别如下:

聲明光标：

OPEN 光标：

FETCH 光标：(取光标)

CLOSE 光标：

舉例：

//初始化表
create table emp
( id int(11) not null auto_increment ,
 name varchar(50) not null comment '姓名', 
 age int(11) comment '年齡', 
 salary int(11) comment '薪水', 
 primary key(`id`) )
 engine=innodb default charset=utf8 ; 
 insert into emp(id,name,age,salary) values(null,'金毛獅王',55,3800),(null,'白眉鷹 王',60,4000),(null,'青翼蝠王',38,2800),(null,'紫衫龍王',42,1800);

//通過循環結構 , 擷取遊标中的資料
create procedure pro_test12()
begin 
DECLARE id int(11); 
DECLARE name varchar(50); 
DECLARE age int(11); 
DECLARE salary int(11); 
DECLARE has_data int default 1;

DECLARE emp_result CURSOR FOR select * from emp; 
DECLARE EXIT HANDLER FOR NOT FOUND //當光标沒有找到任何資料時候，觸發此回調握柄,必須在定義光标之後再定義此處
set has_data = 0;
open emp_result; 
repeat 
fetch emp_result into id , name , age , salary;
select concat('id為',id, ', name 為' ,name , ', age為 ' ,age , ', 薪水為: ', salary); 
until has_data = 0 
end repeat; 
close emp_result; 
end;

17.7 存儲函數

文法結構:

CREATE FUNCTION function_name([param type ... ]) 
RETURNS type 
BEGIN... END;

舉例：

//定義一個存儲過程, 請求滿足條件的總記錄數;
create function count_city(countryId int) 
returns int 
begin 
declare cnum int ;
select count(*) into cnum from city where country_id = countryId; 
return cnum; 
end;

//調用
select count_city(1); 
select count_city(2);