資料庫

資料庫：
英文單詞DataBase，簡稱DB，按照一定格式存盤資料的一些檔案的組合，
顧名思義：存盤資料的倉庫，實際上就是一堆檔案，這些檔案中存盤了具有特定格式的資料，
資料庫管理系統：
DataBaseManagement，簡稱DBMS，
資料庫管理系統是專門用來管理資料庫中資料的，資料庫管理系統可以對資料庫當中的資料進行增刪改查，
常見的資料庫管理系統：
MySQL、Oracle、MS SqlServer、DB2、sybase等....

SQL：結構化查詢語言

程式員需要學習SQL陳述句，程式員通過撰寫SQL陳述句，然后DBMS負責執行SQL陳述句，最終來完成資料庫中資料的增刪改查操作，
SQL是一套標準，程式員主要學習的就是SQL陳述句，這個SQL在mysql中可以使用，
同時在Oracle中也可以使用，在DB2中也可以使用，

三者之間的關系？
DBMS--執行--> SQL --操作--> DB

先安裝資料庫管理系統MySQL，然后學習SQL陳述句怎么寫，撰寫SQL陳述句之后，DBMS
對SQL陳述句進行執行，最終來完成資料庫的資料管理，

SQL陳述句的分類

SQL陳述句有很多，最好進行分門別類，這樣更容易記憶，
分為：
DQL：
資料查詢語言（凡是帶有select關鍵字的都是查詢陳述句）
select...

DML：
資料操作語言（凡是對表當中的資料進行增刪改的都是DML）
insert delete update
insert 增
delete 刪
update 改
這個主要是操作表中的資料data，

DDL：
資料定義語言
凡是帶有create、drop、alter的都是DDL，
DDL主要操作的是表的結構，不是表中的資料，
create：新建，等同于增
drop：洗掉
alter：修改
這個增刪改和DML不同，這個主要是對表結構進行操作，

TCL：
是事務控制語言
包括：
事務提交：commit;
事務回滾：rollback;

DCL：
是資料控制語言，
例如：授權grant、撤銷權限revoke....

MySQL常用命令

退出mysql ：exit;

查看mysql資料庫的版本號：
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.5.36 |
+-----------+

查看當前使用的是哪個資料庫:
mysql> select database();
+-------------+
| database() |
+-------------+
| bjpowernode |
+-------------+

匯入一下提前準備好的資料：bjpowernode.sql 這個檔案中是練習準備的資料庫表，
怎么將sql檔案中的資料匯入
mysql> source D:\course\03-MySQL\document\bjpowernode.sql #注意：路徑中不要有中文！！！！

資料庫表

資料庫當中最基本的單元是表：table

什么是表table？為什么用表來存盤資料呢？

姓名性別年齡(列：欄位)
---------------------------
張三男 20 ------->行（記錄）
李四女 21 ------->行（記錄）
王五男 22 ------->行（記錄）

資料庫當中是以表格的形式表示資料的，因為表比較直觀，

任何一張表都有行和列：
行（row）：被稱為資料/記錄，
列（column）：被稱為欄位，

姓名欄位、性別欄位、年齡欄位，

了解一下：
每一個欄位都有：欄位名、資料型別、約束等屬性，欄位名可以理解，是一個普通的名字，見名知意就行，
資料型別：字串，數字，日期等，后期講，
約束：約束也有很多，其中一個叫做唯一性約束，這種約束添加之后，該欄位中的資料不能重復，

查詢：select

簡單查詢

查詢一個欄位？
select 欄位名 from 表名;
# 其中要注意：select和from都是關鍵字，
# 欄位名和表名都是識別符號，

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

這種方式的缺點：
1、效率低
2、可讀性差，
在實際開發中不建議，可以自己玩沒問題，
你可以在DOS命令視窗中想快速的看一看全表資料可以采用這種方式，

條件查詢

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

查詢語法格式： select 欄位1,欄位2,欄位3.... from 表名 where 條件;

都有哪些條件？
= 等于
查詢薪資等于800的員工姓名和編號？
select empno,ename from emp where sal = 800;
查詢SMITH的編號和薪資？
select empno,sal from emp where ename = 'SMITH'; //字串使用單引號

<> 或!= 不等于
查詢薪資不等于800的員工姓名和編號？
select empno,ename from emp where sal != 800;
select empno,ename from emp where sal <> 800; // 小于號和大于號組成的不等號

< 小于
查詢薪資小于2000的員工姓名和編號？
mysql> select empno,ename,sal from emp where sal < 2000;
+-------+--------+---------+
| empno | ename | sal |
+-------+--------+---------+
| 7369 | SMITH | 800.00 |
| 7499 | ALLEN | 1600.00 |
| 7521 | WARD | 1250.00 |
+-------+--------+---------+

<= 小于等于
查詢薪資小于等于3000的員工姓名和編號？
select empno,ename,sal from emp where sal <= 3000;

> 大于
查詢薪資大于3000的員工姓名和編號？
select empno,ename,sal from emp where sal > 3000;

>= 大于等于
查詢薪資大于等于3000的員工姓名和編號？
select empno,ename,sal from emp where sal >= 3000;

between … and …. 兩個值之間, 等同于 >= and <=
查詢薪資在2450和3000之間的員工資訊？包括2450和3000
第一種方式：>= and <= （and是并且的意思，）
select empno,ename,sal from emp where sal >= 2450 and sal <= 3000;
+-------+-------+---------+
| empno | ename | sal |
+-------+-------+---------+
| 7566 | JONES | 2975.00 |
| 7698 | BLAKE | 2850.00 |
| 7782 | CLARK | 2450.00 |
| 7788 | SCOTT | 3000.00 |
| 7902 | FORD | 3000.00 |
+-------+-------+---------+
第二種方式：between … and …
select
empno,ename,sal
from
emp
where
sal between 2450 and 3000;

注意：
使用between and的時候，必須遵循左小右大，
between and是閉區間，包括兩端的值，

is null 為 null（is not null 不為空）
注意：在資料庫當中null不能使用等號進行衡量，需要使用is null因為資料庫中的null代表什么也沒有，它不是一個值，所以不能使用等號衡量，

and 并且

or 或者

and和or同時出現的話，有優先級問題嗎？
and和or同時出現，and優先級較高，如果想讓or先執行，需要加“小括號”.以后在開發中，如果不確定優先級，就加小括號就行了，

in 包含，相當于多個 or （not in 不在這個范圍中）

not 可以取非，主要用在 is 或 in 中

like 稱為模糊查詢，支持%或下劃線匹配
# %匹配任意多個字符
# 下劃線：任意一個字符，
#（%是一個特殊的符號，_ 也是一個特殊符號）

找出名字以T結尾的？
select ename from emp where ename like '%T';

找出名字以K開始的？
select ename from emp where ename like 'K%';

找出第二個字每是A的？
select ename from emp where ename like '_A%';

找出第三個字母是R的？
select ename from emp where ename like '__R%';

分組查詢

什么是分組查詢？

在實際的應用中，可能有這樣的需求，需要先進行分組，然后對每一組的資料進行操作，這個時候我們需要使用分組查詢，

語法格式：
select
...
from
...
group by
...

重點結論：在一條select陳述句當中，如果有group by陳述句的話，select后面只能跟：參加分組的欄位，以及分組函式，其它的一律不能跟，

找出“每個部門，不同作業崗位”的最高薪資？
select
deptno, job, max(sal)
from
emp
group by
deptno, job;
技巧：兩個欄位聯合成1個欄位看，（兩個欄位聯合分組）

使用having可以對分完組之后的資料進一步過濾，
having不能單獨使用，having不能代替where，having必須和group by聯合使用，

where和having，優先選擇where，where實在完成不了了，再選擇having，

將之前的關鍵字全部組合在一起，來看一下他們的執行順序？
select
...
from
...
where
...
group by
...
having
...
order by
...
以上關鍵字的順序不能顛倒，需要記憶，
執行順序是什么？
1. from
2. where
3. group by
4. having
5. select
6. order by

排序：order by

語法格式：
select
ename,sal
from
emp
order by
sal; // 默認是升序！！
指定降序： desc
指定升序： asc

以兩個欄位排序嗎？或者說按照多個欄位排序？
查詢員工名字和薪資，要求按照薪資升序，如果薪資一樣的話，再按照名字升序排列，
select
ename,sal
from
emp
order by
sal asc, ename asc; // sal在前，起主導，只有sal相等的時候，才會考慮啟用ename排序，

資料處理函式

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

單行處理函式

單行處理函式的特點：一個輸入對應一個輸出，
單行處理函式常見:
lower 轉換小寫
mysql> select lower(ename) as ename from emp;

upper 轉換大寫
mysql> select upper(name) as name from t_student;

substr 取子串（substr(被截取的字串, 起始下標,截取的長度)）
mysql> select substr(ename, 1, 1) as ename from emp;

concat函式進行字串的拼接
mysql> select concat(empno,ename) from emp;
length 取長度
mysql> select length(ename) enamelength from emp;

trim 去空格
mysql> select * from emp where ename = trim(' KING');

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;

round 四舍五入
mysql> select round(1236.567, 0) as result from emp; //保留整數位，
mysql> select round(1236.567, 1) as result from emp; //保留1個小數
mysql> select round(1236.567, 2) as result from emp; //保留2個小數
mysql> select round(1236.567, -1) as result from emp; // 保留到十位，

rand() 生成亂數
mysql> select round(rand()*100,0) from emp; // 100以內的亂數

ifnull 可以將 null 轉換成一個具體值
ifnull是空處理函式，專門處理空的，在所有資料庫當中，只要有NULL參與的數學運算，最終結果就是NULL，
注意：NULL只要參與運算，最終結果一定是NULL，為了避免這個現象，需要使用ifnull函式，ifnull函式用法：ifnull(資料, 被當做哪個值)，如果“資料”為NULL的時候，把這個資料結構當做哪個值，

分組函式（多行處理函式）

多行處理函式特點：多個輸入，對應1個輸出，

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

注意：
1. 分組函式在使用的時候必須先進行分組，然后才能用，如果你沒有對資料進行分組，整張表默認為一組，
2. 分組函式自動忽略NULL，你不需要提前對NULL進行處理，
3. 分組函式中count(*)和count(具體欄位)有什么區別？
count(具體欄位)：表示統計該欄位下所有不為NULL的元素的總數，
count(*)：統計表當中的總行數，（只要有一行資料count則++）
因為每一行記錄不可能都為NULL，一行資料中有一列不為NULL，則這行資料就是有效的，
4. 分組函式不能夠直接使用在where子句中，
5. 所有的分組函式可以組合起來一起用，

distinct

把查詢結果去除重復記錄 distinct

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

// distinct只能出現在所有欄位的最前方，
mysql> select distinct job from emp;

// distinct出現在job,deptno兩個欄位之前，表示兩個欄位聯合起來去重，
mysql> select distinct job,deptno from emp;

連接查詢

多張表聯合起來查詢資料，被稱為連接查詢，

根據表連接的方式分類： 內連接： 等值連接 非等值連接 自連接 外連接： 左外連接（左連接） 右外連接（右連接）

內連接：等值連接

SQL99語法： select ... from a inner join b on a和b的連接條件 where 篩選條件 # inner可以省略（帶著inner可讀性更好！！！一眼就能看出來是內連接）

內連接：非等值連接

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

內連接：自連接

select a.ename as '員工名', b.ename as '領導名' from emp a join emp b on a.mgr = b.empno; //員工的領導編號 = 領導的員工編號以上就是內連接中的：自連接，技巧：一張表看做兩張表

外連接

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

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

帶有right的是右外連接，又叫做右連接，
帶有left的是左外連接，又叫做左連接，
任何一個右連接都有左連接的寫法，
任何一個左連接都有右連接的寫法，

思考：外連接的查詢結果條數一定是 >= 內連接的查詢結果條數？
正確，

三張表，四張表怎么連接？
語法：
select
...
from
a
join
b
on
a和b的連接條件
join
c
on
a和c的連接條件
right join
d
on
a和d的連接條件

一條SQL中內連接和外連接可以混合，都可以出現！

子查詢

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

子查詢都可以出現在哪里呢？
select
..(select).
from
..(select).
where
..(select).

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);

from子句中的子查詢:
注意：from后面的子查詢，可以將子查詢的查詢結果當做一張臨時表，

select后面出現的子查詢（這個內容不需要掌握，了解即可！！！）
注意：對于select后面的子查詢來說，這個子查詢只能一次回傳1條結果

union合并

select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

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在進行結果集合并的時候，要求兩個結果集的列數相同，
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';

limit

limit作用：將查詢結果集的一部分取出來，通常使用在分頁查詢當中，
百度默認：一頁顯示10條記錄，
分頁的作用是為了提高用戶的體驗，因為一次全部都查出來，用戶體驗差，可以一頁一頁翻頁看

limit的使用
完整用法：limit startIndex, length
startIndex是起始下標，length是長度，
起始下標從0開始，

預設用法：limit 5; 這是取前5

注意：mysql當中limit在order by之后執行！！！！！！

分頁
每頁顯示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

總結

關于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..

表的創建

建表的語法格式：(建表屬于DDL陳述句，DDL包括：create drop alter)
create table 表名(欄位名1 資料型別, 欄位名2 資料型別, 欄位名3 資料型別);

create table 表名(
欄位名1 資料型別,
欄位名2 資料型別,
欄位名3 資料型別
);

表名：建議以t_ 或者 tbl_開始，可讀性強，見名知意，
欄位名：見名知意，
表名和欄位名都屬于識別符號，

關于mysql中的資料型別
很多資料型別，我們只需要掌握一些常見的資料型別即可，

varchar(最長255)
可變長度的字串
比較智能，節省空間，
會根據實際的資料長度動態分配空間，

優點：節省空間
缺點：需要動態分配空間，速度慢，

char(最長255)
定長字串
不管實際的資料長度是多少，
分配固定長度的空間去存盤資料，
使用不恰當的時候，可能會導致空間的浪費，

優點：不需要動態分配空間，速度快，
缺點：使用不當可能會導致空間的浪費，

varchar 和 char 我們應該怎么選擇？
性別欄位你選什么？因為性別是固定長度的字串，所以選擇char，
姓名欄位你選什么？每一個人的名字長度不同，所以選擇varchar，

int(最長11)
數字中的整數型，等同于java的int，

bigint
數字中的長整型，等同于java中的long，

float
單精度浮點型資料

double
雙精度浮點型資料

date
短日期型別

datetime
長日期型別

clob
字符大物件
最多可以存盤4G的字串，
比如：存盤一篇文章，存盤一個說明，
超過255個字符的都要采用CLOB字符大物件來存盤，
Character Large OBject:CLOB

blob
二進制大物件
Binary Large OBject
專門用來存盤圖片、聲音、視頻等流媒體資料，
往BLOB型別的欄位上插入資料的時候，例如插入一個圖片、視頻等，
你需要使用IO流才行，

創建一個學生表？
學號、姓名、年齡、性別、郵箱地址
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);

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

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

插入資料insert

插入資料insert （DML）

語法格式：
insert into 表名(欄位名1,欄位名2,欄位名3...) values(值1,值2,值3);

注意：欄位名和值要一一對應，什么是一一對應？
數量要對應，資料型別要對應，

insert陳述句中的“欄位名”可以省略嗎？可以
insert into t_student values(2); //錯誤的

// 注意：前面的欄位名省略的話，等于都寫上了！所以值也要都寫上！
insert into t_student values(2, 'lisi', 'f', 20, '[email protected]');
+------+------+------+------+--------------+
| no | name | sex | age | email |
+------+------+------+------+--------------+
| 1 | NULL | m | NULL | NULL |
| 2 | lisi | f | 20 | [email protected] |
+------+------+------+------+--------------+

一次可以插入多條記錄：
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(),(),(),();

insert插入日期
數字格式化：format

格式化數字：format(數字, '格式')
select ename,format(sal, '$999,999') as sal from emp;

str_to_date：將字串varchar型別轉換成date型別
date_format：將date型別轉換成具有一定格式的varchar字串型別，

插入資料？
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
這個函式可以將日期型別轉換成特定格式的字串，

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

date和datetime兩個型別的區別？
date是短日期：只包括年月日資訊，
datetime是長日期：包括年月日時分秒資訊，
mysql短日期默認格式：%Y-%m-%d
mysql長日期默認格式：%Y-%m-%d %h:%i:%s

修改update

語法格式：
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';

洗掉資料 delete

語法格式？
delete from 表名 where 條件;

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

delete from t_user where id = 2;

insert into t_user(id) values(2);

delete from t_user; // 洗掉所有！

快速洗掉表中的資料？【truncate比較重要，必須掌握】

//洗掉dept_bak表中的資料
delete from dept_bak; //這種洗掉資料的方式比較慢，

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

delete陳述句洗掉資料的原理？（delete屬于DML陳述句！！！）
表中的資料被洗掉了，但是這個資料在硬碟上的真實存盤空間不會被釋放！！！
這種洗掉缺點是：洗掉效率比較低，
這種洗掉優點是：支持回滾，后悔了可以再恢復資料！！！

truncate陳述句洗掉資料的原理？
這種洗掉效率比較高，表被一次截斷，物理洗掉，
這種洗掉缺點：不支持回滾，
這種洗掉優點：快速，

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

大表非常大，上億條記錄？？？？
洗掉的時候，使用delete，也許需要執行1個小時才能洗掉完！效率較低，
可以選擇使用truncate洗掉表中的資料，只需要不到1秒鐘的時間就洗掉結束，效率較高，
但是使用truncate之前，必須仔細詢問客戶是否真的要洗掉，并警告洗掉之后不可恢復！

truncate是洗掉表中的資料，表還在！

洗掉表操作？
drop table 表名; // 這不是洗掉表中的資料，這是把表洗掉，

約束

什么是約束？
約束對應的英語單詞： constraint
在創建表的時候，我們可以給表中的欄位加上一些約束，來保證這個表中資料的
完整性、有效性！！！

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

約束包括哪些？
非空約束：not null
唯一性約束: unique
主鍵約束: primary key （簡稱PK）
外鍵約束：foreign key（簡稱FK）
檢查約束：check（mysql不支持，oracle支持）

非空約束：not null

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

drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null // not null只有列級約束，沒有表級約束！
);

唯一性約束: unique

唯一性約束unique約束的欄位不能重復，但是可以為NULL，
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);

如何做到聯合唯一約束
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) // 約束沒有添加在列的后面，這種約束被稱為表級約束，
);
name和email兩個欄位聯合起來唯一
在mysql當中，如果一個欄位同時被not null和unique約束的話，該欄位自動變成主鍵欄位，

主鍵約束: primary key

主鍵約束的相關術語？
主鍵約束：就是一種約束，
主鍵欄位：該欄位上添加了主鍵約束，這樣的欄位叫做：主鍵欄位
主鍵值：主鍵欄位中的每一個值都叫做：主鍵值，

什么是主鍵？有啥用？
主鍵值是每一行記錄的唯一標識，
主鍵值是每一行記錄的身份證號！！！

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

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

怎么給一張表添加主鍵約束呢？
drop table if exists t_vip;
// 1個欄位做主鍵，叫做：單一主鍵
create table t_vip(
id int primary key, //列級約束
name varchar(255)
);

表級約束主要是給多個欄位聯合起來添加約束？
drop table if exists t_vip;
// id和name聯合起來做主鍵：復合主鍵！！！！
create table t_vip(
id int,
name varchar(255),
email varchar(255),
primary key(id,name)
);

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

主鍵值建議使用：
int
bigint
char
等型別，

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

主鍵除了：單一主鍵和復合主鍵之外，還可以這樣進行分類？
自然主鍵：主鍵值是一個自然數，和業務沒關系，
業務主鍵：主鍵值和業務緊密關聯，例如拿銀行卡賬號做主鍵值，這就是業務主鍵！

在實際開發中使用業務主鍵多，還是使用自然主鍵多一些？
自然主鍵使用比較多，因為主鍵只要做到不重復就行，不需要有意義，
業務主鍵不好，因為主鍵一旦和業務掛鉤，那么當業務發生變動的時候，
可能會影響到主鍵值，所以業務主鍵不建議使用，盡量使用自然主鍵，

在mysql當中，有一種機制，可以幫助我們自動維護一個主鍵值？
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment, //auto_increment表示自增，從1開始，以1遞增！
name varchar(255)
);

外鍵約束：foreign key

外鍵約束涉及到的相關術語：
外鍵約束：一種約束（ foreign key）
外鍵欄位：該欄位上添加了外鍵約束
外鍵值：外鍵欄位當中的每一個值，

存盤引擎

存盤引擎是MySQL中特有的一個術語，其它資料庫中沒有，（Oracle中有，但是不叫這個名字）
存盤引擎這個名字高端大氣上檔次，實際上存盤引擎是一個表存盤/組織資料的方式，
不同的存盤引擎，表存盤資料的方式不同，

怎么給表添加/指定“存盤引擎”呢？
show create table t_student;

可以在建表的時候給表指定存盤引擎，
CREATE TABLE `t_student` (
`no` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cno` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `cno` (`cno`),
CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8

在建表的時候可以在最后小括號的")"的右邊使用：
ENGINE來指定存盤引擎，
CHARSET來指定這張表的字符編碼方式，

結論：
mysql默認的存盤引擎是：InnoDB
mysql默認的字符編碼方式是：utf8

怎么查看mysql支持哪些存盤引擎呢？
命令： show engines \G

MyISAM存盤引擎

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

提示一下：
對于一張表來說，只要是主鍵，
或者加有unique約束的欄位上會自動創建索引，

MyISAM存盤引擎特點：
可被轉換為壓縮、只讀表來節省空間
這是這種存盤引擎的優勢！！！！

MyISAM不支持事務機制，安全性低，

InnoDB存盤引擎

這是mysql默認的存盤引擎，同時也是一個重量級的存盤引擎，
InnoDB支持事務，支持資料庫崩潰后自動恢復機制，
InnoDB存盤引擎最主要的特點是：非常安全，

它管理的表具有下列主要特征：
– 每個 InnoDB 表在資料庫目錄中以.frm 格式檔案表示
– InnoDB 表空間 tablespace 被用于存盤表的內容（表空間是一個邏輯名稱，表空間存盤資料+索引，）

– 提供一組用來記錄事務性活動的日志檔案
– 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滾)支持事務處理
– 提供全 ACID 兼容
– 在 MySQL 服務器崩潰后提供自動恢復
– 多版本（MVCC）和行級鎖定
– 支持外鍵及參考的完整性，包括級聯洗掉和更新

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

MEMORY存盤引擎

使用 MEMORY 存盤引擎的表，其資料存盤在記憶體中，且行的長度固定，
這兩個特點使得 MEMORY 存盤引擎非常快，

MEMORY 存盤引擎管理的表具有下列特征：
– 在資料庫目錄內，每個表均以.frm 格式的檔案表示，
– 表資料及索引被存盤在記憶體中，（目的就是快，查詢快！）
– 表級鎖機制，
– 不能包含 TEXT 或 BLOB 欄位，

MEMORY 存盤引擎以前被稱為HEAP 引擎，

MEMORY引擎優點：查詢效率是最高的，不需要和硬碟互動，
MEMORY引擎缺點：不安全，關機之后資料消失，因為資料和索引都是在記憶體當中，

事務

什么是事務？

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

什么是一個完整的業務邏輯？
假設轉賬，從A賬戶向B賬戶中轉賬10000.
將A賬戶的錢減去10000（update陳述句）
將B賬戶的錢加上10000（update陳述句）
這就是一個完整的業務邏輯，

以上的操作是一個最小的作業單元，要么同時成功，要么同時失敗，不可再分，
這兩個update陳述句要求必須同時成功或者同時失敗，這樣才能保證錢是正確的，

只有DML陳述句才會有事務這一說，其它陳述句和事務無關！！！
insert
delete
update
只有以上的三個陳述句和事務有關系，其它都沒有關系，

因為只有以上的三個陳述句是資料庫表中資料進行增、刪、改的，
只要你的操作一旦涉及到資料的增、刪、改，那么就一定要考慮安全問題，

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

事務開啟了：
insert
insert
insert
delete
update
update
update
事務結束了！

在事務的執行程序中，每一條DML的操作都會記錄到“事務性活動的日志檔案”中，
在事務的執行程序中，我們可以提交事務，也可以回滾事務，

提交事務？
清空事務性活動的日志檔案，將資料全部徹底持久化到資料庫表中，
提交事務標志著，事務的結束，并且是一種全部成功的結束，

回滾事務？
將之前所有的DML操作全部撤銷，并且清空事務性活動的日志檔案
回滾事務標志著，事務的結束，并且是一種全部失敗的結束，

提交事務、回滾事務

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

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

測驗一下，在mysql當中默認的事務行為是怎樣的？
mysql默認情況下是支持自動提交事務的，（自動提交）
什么是自動提交？
每執行一條DML陳述句，則提交一次！

這種自動提交實際上是不符合我們的開發習慣，因為一個業務
通常是需要多條DML陳述句共同執行才能完成的，為了保證資料
的安全，必須要求同時成功之后再提交，所以不能執行一條
就提交一條，

怎么將mysql的自動提交機制關閉掉呢？
先執行這個命令：start transaction;

代碼演示

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

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

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

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

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

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

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


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

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

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

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

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

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

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

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

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

事務四個特性

A：原子性
說明事務是最小的作業單元，不可再分，

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

I：隔離性
A事務和B事務之間具有一定的隔離，
教室A和教室B之間有一道墻，這道墻就是隔離性，
A事務在操作一張表的時候，另一個事務B也操作這張表會那樣？？？

D：持久性
事務最終結束的一個保障，事務提交，就相當于將沒有保存到硬碟上的資料
保存到硬碟上！

事務的隔離性

A教室和B教室中間有一道墻，這道墻可以很厚，也可以很薄，這就是事務的隔離級別，
這道墻越厚，表示隔離級別就越高，

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

讀未提交：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，執行緒同步（事務同步）
每一次讀取到的資料都是最真實的，并且效率是最低的，

查看隔離級別：SELECT @@tx_isolation
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
mysql默認的隔離級別

索引（index）

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

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

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資料結構，

遵循左小又大原則存放，采用中序遍歷方式遍歷取資料，

實作原理

假設有一張用戶表：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）

添加索引的條件

什么條件下，我們會考慮給欄位添加索引呢？
條件1：資料量龐大（到底有多么龐大算龐大，這個需要測驗，因為每一個硬體環境不同）
條件2：該欄位經常出現在where的后面，以條件的形式存在，也就是說這個欄位總是被掃描，
條件3：該欄位很少的DML(insert delete update)操作，（因為DML之后，索引需要重新排序，）

建議不要隨意添加索引，因為索引也是需要維護的，太多的話反而會降低系統的性能，
建議通過主鍵查詢，建議通過unique約束的欄位進行查詢，效率是比較高的，

索引的創建和洗掉

創建索引：
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索引物件洗掉，

是否使用索引進行檢索

1.5、在mysql當中，怎么查看一個SQL陳述句是否使用了索引進行檢索？

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

索引失效的情況

索引有失效的時候，什么時候索引失效呢？

失效的第1種情況：
select * from emp where ename like '%T';

ename上即使添加了索引，也不會走索引，為什么？
原因是因為模糊匹配當中以“%”開頭了！
盡量避免模糊查詢的時候以“%”開始，
這是一種優化的手段/策略，

失效的第2種情況：
使用or的時候會失效，如果使用or那么要求or兩邊的條件欄位都要有
索引，才會走索引，如果其中一邊有一個欄位沒有索引，那么另一個
欄位上的索引也會實作，所以這就是為什么不建議使用or的原因，

失效的第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 |
+----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+

索引分類

單一索引：一個欄位上添加索引，
復合索引：兩個欄位或者更多的欄位上添加索引，

主鍵索引：主鍵上添加索引，
唯一性索引：具有unique約束的欄位上添加索引，
.....

注意：唯一性比較弱的欄位上添加索參考處不大，

視圖

什么是視圖？

視圖創建和洗掉

創建視圖物件：
create view dept2_view as select * from dept2;

洗掉視圖物件：
drop view dept2_view;

注意：只有DQL陳述句才能以view的形式創建，
create view view_name as 這里的陳述句必須是DQL陳述句;

視圖的用途

《方便，簡化開發，利于維護》
我們可以面向視圖物件進行增刪改查，對視圖物件的增刪改查，會導致
原表被操作！（視圖的特點：通過對視圖的操作，會影響到原表資料，）

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

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

// 面向視圖洗掉
mysql> delete from dept2_view;

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

假設有一條非常復雜的SQL陳述句，而這條SQL陳述句需要在不同的位置上反復使用，
每一次使用這個sql陳述句的時候都需要重新撰寫，很長，很麻煩，怎么辦？
可以把這條復雜的SQL陳述句以視圖物件的形式新建，
在需要撰寫這條SQL陳述句的位置直接使用視圖物件，可以大大簡化開發，
并且利于后期的維護，因為修改的時候也只需要修改一個位置就行，只需要
修改視圖物件所映射的SQL陳述句，

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

再提醒一下：
視圖對應的陳述句只能是DQL陳述句，
但是視圖物件創建完成之后，可以對視圖進行增刪改查等操作，

小插曲：
增刪改查，又叫做：CRUD，
CRUD是在公司中程式員之間溝通的術語，一般我們很少說增刪改查，
一般都說CRUD，

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

DBA常用命令

重點掌握：
資料的匯入和匯出（資料的備份）
其它命令了解一下即可，

資料匯出？
注意：在windows的dos命令視窗中：
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456

可以匯出指定的表嗎？
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

資料匯入？
注意：需要先登錄到mysql資料庫服務器上，
然后創建資料庫：create database bjpowernode;
使用資料庫：use bjpowernode
然后初始化資料庫：source D:\bjpowernode.sql

資料庫設計三范式

什么是資料庫設計范式？
資料庫表的設計依據，教你怎么進行資料庫表的設計，

資料庫設計范式共有？
3個，
第一范式：要求任何一張表必須有主鍵，每一個欄位原子性不可再分，

第二范式：建立在第一范式的基礎之上，要求所有非主鍵欄位完全依賴主鍵，
不要產生部分依賴，

第三范式：建立在第二范式的基礎之上，要求所有非主鍵欄位直接依賴主鍵，
不要產生傳遞依賴，

宣告：三范式是面試官經常問的，所以一定要熟記在心！

設計資料庫表的時候，按照以上的范式進行，可以避免表中資料的冗余，空間的浪費，

第一范式
最核心，最重要的范式，所有表的設計都需要滿足，
必須有主鍵，并且每一個欄位都是原子性不可再分，

學生編號學生姓名聯系方式
------------------------------------------
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

第二范式：
建立在第一范式的基礎之上，
要求所有非主鍵欄位必須完全依賴主鍵，不要產生部分依賴，

學生編號學生姓名教師編號教師姓名
----------------------------------------------------
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

背口訣：
多對多怎么設計？
多對多，三張表，關系表兩個外鍵！！！！！！！！！！！！！！！

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

學生編號（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

背口訣：
一對多，兩張表，多的表加外鍵！！！！！！！！！！！！

總結表的設計

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

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

一對一：
一對一放到一張表中不就行了嗎？為啥還要拆分表？
在實際的開發中，可能存在一張表欄位太多，太龐大，這個時候要拆分表，
一對一怎么設計？
沒有拆分表之前：一張表
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

口訣：一對一，外鍵唯一！！！！！！！！！！

囑咐

資料庫設計三范式是理論上的，

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

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

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

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

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/499105.html

標籤：MySQL

上一篇：MySQL 關于 only_full_group_by 限制

下一篇：windows安裝mysql8.0.29（ZIP解壓安裝版本）