DQL：
數(shù)據(jù)查詢語言（凡是帶有 select 關(guān)鍵字的都是查詢語句）
select...
DML：
數(shù)據(jù)操作語言（凡是對表中的數(shù)據(jù)進(jìn)行增刪改的都是 DML）
insert 增 delete 刪 update 改
DDL：
數(shù)據(jù)定義語言（凡是帶有 create、drop、alter 的都是 DDL）
主要操作的是表的結(jié)構(gòu)，不是表的數(shù)據(jù)
TCL：
事務(wù)控制語言（包括：事務(wù)提交 commit、事務(wù)回滾 rollback）
DCL：
數(shù)據(jù)控制語言（授權(quán) grant、撤銷權(quán)限 revoke）
desc 表名 查看表的結(jié)構(gòu)
select 命令
select * from 表名; 查看表的信息
select version(); 查看當(dāng)前使用的版本號
select database(); 查看當(dāng)前使用的數(shù)據(jù)庫
\c 用來終止一條命令的輸入
source 地址\xxx.sql
xxx.sql 這種文件被稱為 sql 腳本文件
批量的執(zhí)行 sql 語句，可以使用 sql 腳本文件
PRI UNI MUL
# 如果鍵是PRI，則列是主鍵或多列主鍵中的列之一。
# 如果鍵是UNI，則該列是唯一索引的第一列。(唯一索引允許多個空值，但可以通過檢查Null字段來判斷該列是否允許空。)
# 如果鍵為MUL，則該列是非唯一索引的第一列，其中允許在列中多次出現(xiàn)給定值。
select 字段名 from 表名
若查詢多個字段，用逗號隔開即可
查詢所有字段
不建議使用 *，因為它會先將 * 轉(zhuǎn)為所有字段，效率低，可讀性差，建議寫上所有字段。
select 字段名 as 別名 from 表名
只是將 指定字段 顯示的查詢結(jié)果字段名顯式為別名（select 語句永遠(yuǎn)不會進(jìn)行修改操作）
as 可以省略，用空格代替
若別名含有空格，可以用單引號（或雙引號，但在oracle數(shù)據(jù)庫中使用不了）引起來，字符串標(biāo)準(zhǔn)的使用單引號括起來
字段可以使用數(shù)字表達(dá)式
select 字段 from 表名 where 條件;
= 等于
<> 或 != 不等于
< 小于 <= 小于等于
'>' 大于 >= 大于等于
between ... and ... 表示兩個值之間（閉區(qū)間） 等同于 >= and <=
is null 為 null （is not null 不為空，數(shù)據(jù)庫中 null 代表什么都沒有）
and 并且 or 或者 （若兩者同時出現(xiàn)，and 的優(yōu)先級 比 or的優(yōu)先級高，可加括號避免）
in 包含，相當(dāng)于多個 or（not in 不在這幾個值當(dāng)中的數(shù)據(jù)）
in(具體值，具體值，......) 不是區(qū)間
not 取非，主要用在 is 或 in 中
like 模糊查詢，支持 % 或 下劃線匹配
% 匹配任意個字符
下劃線，一個下劃線只能匹配任意一個字符（若想匹配下劃線，可以加上\轉(zhuǎn)義字符）
select ename from emp where ename like '%K' 找出以 K 結(jié)尾的名字
select 字段名 from 表名 order by 字段名 默認(rèn)是升序
select 字段名 from 表名 order by 字段名 **desc ** 指定降序（Descending order）
select 字段名 from 表名 order by 字段名 asc 指定升序（Ascending order）
多個字段排序
select 字段名 from 表名 order by 字段名 控制字符，字段名 控制字符，...
只有前者相同的情況下，才會輪到后者排序
根據(jù)字段的位置進(jìn)行排序
select 字段名 from 表名 order by 字段名的列位置（不建議在開發(fā)中寫，不健壯，列的順序容易修改）
select ... from ... where ... order by ...
執(zhí)行順序 from -> where -> select -> order by（排序總是在最后）
在處理使用Mysql時，數(shù)據(jù)表采用utf8字符集，使用中發(fā)現(xiàn)中文不能直接按照拼音排序
select * from musician_ordered order by convert(name using gbk) collate gbk_chinese_ci;
對name字段進(jìn)行g(shù)bk編碼，然后，對編碼后的內(nèi)容根據(jù)gbk_chinese_ci進(jìn)行整理排序
這樣得到的結(jié)果，英文是排在中文前面的，而且是根據(jù)拼音排序的
gbk_chinese_ci：按照普通的字母順序排，不區(qū)分大小寫
gbk_bin：按照二進(jìn)制順序排，區(qū)分大小寫
一個輸入對應(yīng)一個輸出，和其對應(yīng)的是多行處理函數(shù)（多個輸入，對應(yīng)一個輸出）
lower 轉(zhuǎn)換小寫
select lower(字段名) from 表名
upper 轉(zhuǎn)換大寫
select upper(字段名) from 表名
substr 取子串
select substr(字段名，起始下標(biāo)（從1開始），截取的長度)
length 長度
select length(字段名) from 表名
concat 進(jìn)行拼接
select concat(字段名，字段名) from 表名
trim 去空格
select 字段名 from 表名 where 字段名 = trim(' % ')
str_to_date 將字符串轉(zhuǎn)換成日期
date_format 格式化日期
format 設(shè)置千分位
round 四舍五入
若 select 后面接字面量/字面值，則生成一個表，其中的數(shù)據(jù)全部變?yōu)檫@個字面量，字段名為 這個字面量。
select round(數(shù)值，保留幾個小數(shù)) from 表名
若為 -1 例如：1236.567 -> 1240 保留到十位
若為 -2 例如：1236.567 -> 1200 保留到百位
rand 生成隨機(jī)數(shù)
select rand() from 表名 生成 [0-1）的隨機(jī)數(shù)
例如：select round(rand()100, 0) from 表名 生成100以內(nèi)的隨機(jī)數(shù)
ifnull 可以將 null 轉(zhuǎn)換成一個具體值
數(shù)據(jù)庫中只要 NULL 參與運(yùn)算，結(jié)果就為 NULL
ifnull（數(shù)據(jù)，被當(dāng)作哪個值） 如果數(shù)據(jù)為 NULL，則將其當(dāng)作后面指定的值
case ... when ... then ... when ... then ... else ... end 類似于 if-else 語句
輸入多行，最終輸出一行
count 計數(shù)
sum 求和
avg 平均值
max 最大值
min 最小值
分組函數(shù)在使用的時候必須先進(jìn)行分組，然后才能用；
如果你沒有對數(shù)據(jù)進(jìn)行分組，整張表默認(rèn)為一組。
分組函數(shù)自動忽略 NULL， 不需要提前對 NULL 進(jìn)行處理
count（*） 統(tǒng)計表當(dāng)中的總行數(shù)。（不可能有一行記錄所有的列都為 NULL）
分組函數(shù)不能直接使用在 where 子句中因為 where 子句執(zhí)行的時候，還沒有進(jìn)行分組
所有的分組函數(shù)可以組合起來一起用 （select min(),max(),... from 表名）
在實際的應(yīng)用中，可能需要先進(jìn)行分組，然后對每一組的數(shù)據(jù)進(jìn)行操作
select ... from ... group by ...
select ... from ... where ... group by ... order by ...
執(zhí)行順序：from -> where -> group by -> select -> order by
在一條 select 語句當(dāng)中，如果有 group by 語句的話，select 后面只能跟：參加分組的字段以及分組函數(shù)，其他的一律不能跟
多個字段聯(lián)合分組
select xxx from xxx group by 字段1，字段2，...
使用 having 可以對分完組之后的數(shù)據(jù)進(jìn)一步過濾，having 不能單獨(dú)使用（單獨(dú)使用沒有意義），必須和 group by 聯(lián)合使用(很耗資源，盡量少用)
優(yōu)化策略：where 和 having，優(yōu)先選擇 where，where 實在完成不了了，再選擇 having
select Colle, max(Credit) MC from course group by Colle having MC >= 4 order by MC desc;
執(zhí)行順序 from -> group by -> having -> select -> order by (為啥 having 后面可以使用別名？（MYSQl可以這么做是因為MYSQL用的是臨時表，在having前已經(jīng)產(chǎn)生了數(shù)據(jù)，所以你可以用別名，但SQL Sever不可以，SQL是在having后才Select）因為mysql對此作了擴(kuò)展。在mysql 5.7.5之前的版本，ONLY_FULL_GROUP_BY sql mode默認(rèn)不開啟。在5.7.5或之后的版本默認(rèn)開啟。)
select distinct 字段名，... from 表名把查詢結(jié)果去除重復(fù)記錄
需要放在所有字段的最前面
若 distinct 后接多個字段，表示多個字段聯(lián)合起來去重
內(nèi)連接
等值連接
非等值連接
自連接
外連接
左外連接（左連接）
右外連接（右連接）
全連接
若將兩張表進(jìn)行連接查詢，且沒有任何條件的限制，會發(fā)生笛卡爾現(xiàn)象
最終查詢結(jié)果的條數(shù)，是兩張表條數(shù)的乘積（匹配次數(shù)也是）
如何避免？連接時加上條件，滿足這個條件的記錄被篩選出來
但匹配次數(shù)不會減少，加上條件只是為了避免笛卡爾積現(xiàn)象，并為了篩選出有效的記錄例：select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno（SQL92)這種寫法可以提高效率，不會導(dǎo)致字段名在兩個表中都找
通過笛卡爾積現(xiàn)象可以得出，表的連接次數(shù)越多效率越低，需要盡量減少表的連接次數(shù)
案例：查詢每個員工所在部門的名稱，顯示員工名和部門名？
emp e 和 dept d 表進(jìn)行連接。條件是：e.deptno = d.deptno
SQL92語法：（結(jié)構(gòu)不夠清晰，表的連接條件和后期進(jìn)一步篩選的條件，都放到了 where 子句中）
select e.ename, d.dnamefrom emp e, dept dwhere e.deptno = d.deptno;
SQL99語法：（表連接的條件是獨(dú)立的，連接之后，如果還需要進(jìn)一步篩選，再往后繼續(xù)添加 where 子句）
select e.ename, d.dnamefrom emp e(inner) join # 連接 dept don e.deptno = d.deptno; #（連接條件）where ... # 篩選條件select ...from ...inner join ...on ... #（非等值條件）
技巧：把一張表看成兩張表
select ...from ...right (outer) join # 表示把 join 右邊的這張表看成主表（主要是為了將這張表的數(shù)據(jù)全部查詢出來，捎帶著關(guān)聯(lián)查詢左邊的表 ...on ... # 篩選條件select ...from ...left (outer) join ...on ... # 篩選條件
思考：外連接的查詢結(jié)果條數(shù) >= 內(nèi)連接的查詢結(jié)果條數(shù)
select ...(a)from ...join ...(b)on ...(a 和 b 的連接條件)join ...(c)on ...(a 和 c 的連接條件 或 b 和 c 連接條件)....... （一條 SQL 中內(nèi)連接和外連接可以混合）SELECT stu.*,sc.c_id,sc.score,co.c_name,te.t_nameFROM stu JOIN sc on stu.s_id=sc.s_idJOIN coon sc.c_id=co.c_idJOIN teon co.t_id=te.t_id;
select 語句中 嵌套 select 語句，被嵌套的 select 語句稱為 子查詢。
select ...(select) # 此處子查詢的結(jié)果只能返回一條結(jié)果，若多余一條則報錯from ...(select) # 可以將其查詢結(jié)果當(dāng)作一張臨時表（調(diào)用派生表的時候需要定義別名）where ...(select)union 的效率要高一些（相對多表連接，每連接一次新表，則匹配的次數(shù)滿足笛卡爾積，成倍的翻...，但是 union 可以減少匹配的次數(shù)，還可以完成兩個結(jié)果集的拼接（乘法變成了加法））
注意：
union 進(jìn)行結(jié)果集合并的時候，要求兩個結(jié)果集的列數(shù)相同
兩個結(jié)果集的數(shù)據(jù)類型也需要相同（oracle），但 MySQL 中可以不相同
select ... from ... where ...unionselect ... from ... where ...
將查詢結(jié)果集的一部分取出來。（通常使用在分頁查詢當(dāng)中）
limit startIndex, length （起始下標(biāo)，長度） 起始下標(biāo)從 0 開始
缺省用法：limit 5 這是取前 5
limit 在 order by 之后執(zhí)行
limit (pageNo - 1) * pageSize, pageSize // (頁號 - 1)*記錄條數(shù) 記錄條數(shù)select (5) ...from (1) ...where (2) ...group by (3) ...having (4) ...order by (6) ...limit (7) ...
建表屬于 DDL 語句，DDL 包括：create drop alter
create table 表名 { 字段名1 數(shù)據(jù)類型 default ..., # default 可以指定該字段名的數(shù)據(jù)類型 字段名2 數(shù)據(jù)類型, 字段名3 數(shù)據(jù)類型, ...};表名建議以 t_ 或者 tbl_ 開始，可讀性強(qiáng)，見名知意。
字段名：見名知意
表名和字段名都屬于標(biāo)識符
varchar（最長 255）
可變長度的字符串，比較智能，節(jié)省空間，會根據(jù)實際的數(shù)據(jù)長度動態(tài)分配空間
優(yōu)缺點
優(yōu)點：節(jié)省空間
缺點：需要動態(tài)分配空間，速度慢
char（最長 255）
定長字符串，不管實際的數(shù)據(jù)長度是多少，分配固定長度的空間取存儲數(shù)據(jù)，使用不恰當(dāng)?shù)臅r候，可能會導(dǎo)致空間的浪費(fèi)
優(yōu)缺點
優(yōu)點：不需要動態(tài)分配空間，速度塊
缺點：使用不當(dāng)可能會導(dǎo)致空間的浪費(fèi)
varchar 和 char 一般根據(jù)實際情況進(jìn)行選擇，若字段固定長度一般選擇 char，長度不一則選擇 varchar
int（最長 11）
數(shù)字中的整數(shù)型（ java中的 int）
bigint
數(shù)字中的長整型（java 中的 long）
float
單精度浮點型數(shù)據(jù)
double
雙精度浮點型數(shù)據(jù)double (M,D) M：表示有效數(shù)字的最大位數(shù) D：表示小數(shù)點后的位數(shù)
date（不包括具體時間）
短日期類型
datetime（包括具體時間）
長日期類型
clob（Character Large Object）
字符大對象，最多可以存儲 4G 的字符串
例如：存儲一篇文章，一個說明
超過 255 個字符的都要采用 CLOB 字符大對象來存儲
blob（Binary Large Object）
二進(jìn)制大對象
專門用來存儲圖片、聲音、視頻等流媒體數(shù)據(jù)
需要用 IO 流，來往 BLOB 類型的字段上插入數(shù)據(jù)
drop table 表名,...; # 當(dāng)這張表不存在的時候會報錯drop table if exists 表名,...; # 如果這張表存在的話，刪除(若后面存在已經(jīng)被刪除的表，會有警告，但其他表也會被刪除)
create table 表名1 as select *(字段...) from 表名2;將一個查詢結(jié)果當(dāng)作一張表新建，實現(xiàn)表的快速復(fù)制（數(shù)據(jù)也同樣拷貝了）
insert into 表名(字段名 1，字段名 2，字段名 3，...) value(值1，值2，值3，...)# 字段名和值 要一一對應(yīng)（數(shù)量要對應(yīng)，數(shù)據(jù)類型要對應(yīng)）# 若字段名都省略的話，相當(dāng)于都寫上了# 沒有給其他字段指定值的話，默認(rèn)值為 NULL （除非 創(chuàng)建表的時候指定了默認(rèn)值）
# 插入多條數(shù)據(jù)insert into 表名(字段名 1，字段名 2，字段名 3，...) values(...),(...),(...),...# 很少用 要求查詢結(jié)果的結(jié)構(gòu)和表的結(jié)構(gòu)相同insert into 表名1 (select * from 表名0);
format(數(shù)字,'格式');# select ename, format(sal, '$999,999') as sal from emp; 顯示千分位將字符串 varchar 類型轉(zhuǎn)換成 date 類型
%Y 年(4位)%y 年(2位)%m 月%d 日%H 時(00-23)%h 時(00-12)%i 分%s 秒# insert into t_user(id,name,birth) values(2,'jack',str_to_date('01-12-1990','%d-%m-%Y');str_to_date('字符串日期'，'日期格式')注意：若提供的字符串日期格式為 '%Y-%m-%d' 則不需要 str_to_date 轉(zhuǎn)換 # insert into t_user(id,name,birth) values(2,'jack','1990-10-05');常用于 insert 語句，因為插入的時候需要一個日期類型的數(shù)據(jù)將日期轉(zhuǎn)換成字符串
date_format(日期類型數(shù)據(jù),'日期格式');通常使用在查詢?nèi)掌诜矫?，設(shè)置展示的日期格式# 若不使用該函數(shù)，而直接查詢 date 類型的數(shù)據(jù)# SQL 會將日期進(jìn)行默認(rèn)的格式化：'%Y-%m-%d'date 是短日期，僅含年月日信息 （默認(rèn)格式：**%Y-%m-%d**）
datetime 是長日期，含有年月日 時分秒信息（默認(rèn)格式：**%Y-%m-%d %H:%i:%s**）
可以獲取當(dāng)前系統(tǒng)的時間，并且獲取的時間是 datetime 類型的
update 表名 set 字段名1 = 值1，字段名2 = 值2，字段名3 = 值3 ... where 條件;
注意：若沒有條件限制將會導(dǎo)致所有數(shù)據(jù)全部更新。
delete from 表名 where ...; # (DML)# 表中的數(shù)據(jù)被刪除了，但是這個數(shù)據(jù)在硬盤上的真實存儲空間不會被釋放# 缺點：刪除效率比較低# 優(yōu)點：支持回滾(start transaction -> ... -> rollback)，后悔了可以再恢復(fù)數(shù)據(jù)?。?！注意：若沒有條件，會刪除整張表的數(shù)據(jù)。
truncate table 表名; # (DDL)# 刪除效率比較高，表被一次截斷，物理刪除# 優(yōu)點：不支持回滾# 缺點：快速
create drop alter
什么是對表結(jié)構(gòu)的修改？添加一個字段刪除一個字段修改一個字段
constraint
在創(chuàng)建表的時候，可以給表中的字段加上一些約束，來保證這個表中數(shù)據(jù)的完整性、有效性！
作用：保證表中的數(shù)據(jù)有效！
非空約束：not null
唯一性約束：unique
主鍵約束：primary key（簡稱 PK）
外鍵約束：foreign key（簡稱 FK）
檢查約束：check （mysql 不支持，oracle 支持）
not null 約束的字段不能為 NULL （只有列級約束）
ERROR 1364 (HY000): Field 'name' doesn't have a default value# 若讓 not null 約束的字段為空，則會報該錯誤unique 約束的字段不能重復(fù)，但是可以為 NULL
ERROR 1062 (23000): Duplicate entry 'lisa' for key 't_user2.name'# 若讓 unique 約束的字段重復(fù)，則會報該錯誤# 但若有多個 NULL，不是表示重復(fù)，因為 NULL 表示什么都沒有
create table 表名 ( 字段名1 類型1 unique, # 列級約束 字段名2 類型2 unique, # 列級約束);create table 表名( 字段名1 類型1, 字段名2 類型2, unique(字段名1,字段名2) # 表級約束 需要給多個字段聯(lián)合起來添加某一個約束的時候使用);
create table 表名( 字段名1 類型1 not null unique, # mysql 當(dāng)中，這樣使用會使得該字段自動變成主鍵 PRIMARY KEY (oracle 不一樣) 字段名2 類型2);primary key
主鍵約束：一種約束
主鍵字段：該字段上添加了主鍵約束，該字段就叫做主鍵字段
主鍵值：主鍵字段中的每一個值都叫做主鍵值
主鍵值是每一行記錄的唯一標(biāo)識（身份證號）
任何一張表都應(yīng)該有主鍵（否則無效）
not null + unique （主鍵值不能為 NULL，同時也不能重復(fù)）
可以使用表級約束
create table 表名( id int, name varchar(255), # primary key(id) 單一主鍵 primary key(id,name) # 復(fù)合主鍵?。。。ㄩ_發(fā)中不建議使用） # 表級約束 若包含兩個字段，則表示兩者聯(lián)合判斷主鍵（這樣算一個主鍵）);
一張表，主鍵約束只能添加 1 個
主鍵值（一般都是數(shù)字，或者定長的）
建議使用：int bigint char
不建議使用：varchar
自然主鍵
主鍵值是一個自然數(shù)，和業(yè)務(wù)沒關(guān)系
業(yè)務(wù)主鍵
主鍵值和業(yè)務(wù)緊密關(guān)聯(lián)
實際開發(fā)中使用自然主鍵多，還是業(yè)務(wù)主鍵多？
自然主鍵使用比較多，因為主鍵只要做到不重復(fù)就行，不需要有意義，業(yè)務(wù)主鍵不好，因為主鍵一旦和業(yè)務(wù)掛鉤，那么當(dāng)業(yè)務(wù)發(fā)生變動的時候，可能會影響到主鍵值，所以盡量使用自然主鍵
create table 表名( id int(11) primary key auto_increment, # 主鍵自增 從 1 開始 # 每次在上一個記錄的基礎(chǔ)上 加1 name varchar(255));foreign key
外鍵約束：一種約束（foreign key）
外鍵字段：該字段上添加了外鍵約束
外鍵值：外鍵字段當(dāng)中的每一個值
業(yè)務(wù)背景 設(shè)計數(shù)據(jù)庫表，來描述“班級和學(xué)生”的信息：
# 方案一：t_studentno(pk) name classno classname-----------------------------------------------------1 lisi 101 上海理工大學(xué)計科一班2 zhangsan 101 上海理工大學(xué)計科一班3 wangwu 102 上海理工大學(xué)計科二班4 zhaoliu 102 上海理工大學(xué)計科二班5 qing 103 上海理工大學(xué)計科三班缺點：數(shù)據(jù)冗余，空間浪費(fèi)# 方案二：t_class（父表）classno(pk) classname-------------------------------------101 上海理工大學(xué)計科一班102 上海理工大學(xué)計科二班103 上海理工大學(xué)計科三班t_student(子表)no(pk) name cno(fk)----------------------------------1 lisi 1012 zhangsan 1013 wangwu 1024 zhaoliu 1025 qing 103
drop table if exists t_student; # 先刪除子表drop table if exists t_class; # 在刪除父表create table t_class( # 先創(chuàng)建父表 classno int(11) primary key, classname varchar(255))engine = InnoDB default charset = gbk;create table t_student( # 再創(chuàng)建子表 no int(11) primary key, name varchar(255), cno int(11), foreign key(cno) references t_class(classno) # 添加了外鍵約束 表名：這個字段的值只能取自父表的對應(yīng)字段的值（可以為NULL）)engine=InnoDB default charset=gbk;# 先插入父表數(shù)據(jù)insert into t_class values(101, '上海理工大學(xué)計科一班');insert into t_class values(102, '上海理工大學(xué)計科二班');insert into t_class values(103, '上海理工大學(xué)計科三班');# 再插入子表數(shù)據(jù)insert into t_student values(1, 'lisi', 101);insert into t_student values(2, 'zhangsan', 101);insert into t_student values(3, 'wangwu', 102);insert into t_student values(4, 'zhaoliu', 102);insert into t_student values(5, 'qing', 103);select classno,classname from t_class;select no,name,cno from t_student;解決中文亂碼
**set names gbk; **相當(dāng)于是告訴 MySQL 服務(wù)器軟件，我們在當(dāng)前命令行下輸入的內(nèi)容是GBK編碼。當(dāng)命令窗口關(guān)閉或退出 MySQL 服務(wù)后，它再輸入中文就會出現(xiàn)問題
注意
外鍵引用的父表中字段不一定是主鍵，但要求是 unique 的
外鍵可以為 NULL
存儲引擎是 MySQL 中特有的一個術(shù)語，其他數(shù)據(jù)庫中沒有（Oracle中有，但不叫這個名字）
實際上存儲引擎是一個表存儲/組織數(shù)據(jù)的方式
不同的存儲引擎，表存儲數(shù)據(jù)的方式不同
create table t_product( id int(11) primary key, name varchar(255))engine = InnoDB default charset = utf8; # 指定存儲引擎 和 默認(rèn)字符編碼方式（MySQL默認(rèn)字符集為 utf8）
show engines \Gmysql> show engines \G*************************** 1. row *************************** Engine: MEMORY Support: YES Comment: Hash based, stored in memory, useful for temporary tablesTransactions: NO XA: NO Savepoints: NO*************************** 2. row *************************** Engine: MRG_MYISAM Support: YES Comment: Collection of identical MyISAM tablesTransactions: NO XA: NO Savepoints: NO*************************** 3. row *************************** Engine: CSV Support: YES Comment: CSV storage engineTransactions: NO XA: NO Savepoints: NO*************************** 4. row *************************** Engine: FEDERATED Support: NO Comment: Federated MySQL storage engineTransactions: NULL XA: NULL Savepoints: NULL*************************** 5. row *************************** Engine: PERFORMANCE_SCHEMA Support: YES Comment: Performance SchemaTransactions: NO XA: NO Savepoints: NO*************************** 6. row *************************** Engine: MyISAM Support: YES Comment: MyISAM storage engineTransactions: NO XA: NO Savepoints: NO*************************** 7. row *************************** Engine: InnoDB Support: DEFAULT Comment: Supports transactions, row-level locking, and foreign keysTransactions: YES XA: YES Savepoints: YES*************************** 8. row *************************** Engine: BLACKHOLE Support: YES Comment: /dev/null storage engine (anything you write to it disappears)Transactions: NO XA: NO Savepoints: NO*************************** 9. row *************************** Engine: ARCHIVE Support: YES Comment: Archive storage engineTransactions: NO XA: NO Savepoints: NO # 版本不同 支持情況不同show engines \g# 表格形式使用三個文件表示每個表
格式文件 - 存儲表結(jié)構(gòu)的定義（mytable.frm)
數(shù)據(jù)文件 - 存儲表行的內(nèi)容（mytable.MYD)
索引文件 - 存儲表上的索引（mytable.MYI）
索引是一本書的目錄，縮小掃描范圍，提高查詢效率的一種機(jī)制
對于一張表來說，只要是主鍵，或者加有 unique 約束的字段上會自動創(chuàng)建索引
MyISAM 存儲引擎特點
可被轉(zhuǎn)換為壓縮、只讀表來節(jié)省空間
但不支持事務(wù)機(jī)制，安全性低
MySQL 默認(rèn)的存儲引擎，同時也是一個重量級的存儲引擎
InnoDB 管理的表具有下列主要特征：
每個 InnoDB 表在數(shù)據(jù)庫目錄中以 .frm 格式文件表示
InnoDB 表空間 tablespace 被用于存儲表的內(nèi)容（表空間是一個邏輯名稱，表空間用于存儲數(shù)據(jù)+索引）
提供一組用來記錄事務(wù)性活動的日志文件
用 COMMIT（提交）、SAVAPOINT 及 ROLLBACK（回滾）支持事務(wù)處理
提供全 ACID 兼容
在 MySQL 服務(wù)器崩潰后提供自動恢復(fù)
多版本（MVCC）和行級鎖定
支持外鍵及引用的完整性，包括級聯(lián)刪除和更新
InnoDB 存儲引擎特點
支持事務(wù)，以保證數(shù)據(jù)的安全
效率不是很高，并且不能壓縮，不能轉(zhuǎn)換為只讀表，不能很好的節(jié)省空間
使用 MEMORY 存儲引擎的表，其數(shù)據(jù)存儲在內(nèi)存中，且行的長度固定
MEMORY 存儲引擎管理的表具有下列特征：
在數(shù)據(jù)庫目錄內(nèi)，每個表均以 .frm 格式的文件表示
表數(shù)據(jù)及索引被存儲在內(nèi)存中（目的就是查詢快！）
表級鎖機(jī)制
不能包含 TEXT 或 BLOB 字段
MEMORY 存儲引擎以前被稱為 HEAP 引擎
優(yōu)缺點：
優(yōu)點：查詢效率是最高的，不需要和硬盤交互
缺點：不安全，關(guān)機(jī)之后數(shù)據(jù)消失。因為數(shù)據(jù)和索引都是在內(nèi)存當(dāng)中
內(nèi)存中的數(shù)據(jù)是通過電容這種電子元件在承載，電容充放電都是需要時間的，所以可以大膽猜測，即使斷電了，內(nèi)存中的數(shù)據(jù)全部消失也是需要時間的
transaction
一個事務(wù)就是一個完整的業(yè)務(wù)邏輯。
是一個最小的工作單元，不可再分。
只有 DML 語句才有事務(wù)這一說insertdeleteupdate
只要你的操作一旦涉及到數(shù)據(jù)的增、刪、改，那么就一定要考慮安全問題
假設(shè)所有的業(yè)務(wù)，只要一條 DML 語句就能完成，還有必要存在事務(wù)機(jī)制嗎？正是因為做某件事的時候，需要多條 DML 語句共同聯(lián)合起來才能完成，所以需要事務(wù)的存在。如果任何一件復(fù)雜的事情都能用一條 DML 語句搞定，那么事務(wù)則沒有存在的價值了
事務(wù)本質(zhì)上就是批量的 DML 語句同時成功，或者同時失敗！
InnoDB 存儲引擎：提供一組用來記錄事務(wù)性活動的日志文件
事務(wù)開啟了：insertinsertdeleteinsertupdateupdate事務(wù)結(jié)束了！
在事務(wù)的執(zhí)行過程中，每一條 DML 的操作都會記錄到 “事務(wù)性活動的日志文件” 中。
在事務(wù)的執(zhí)行過程中，我們可以提交事務(wù)，也可以回滾事務(wù)
提交事務(wù)
清空事務(wù)性活動的日志文件，將數(shù)據(jù)全部徹底持久化到數(shù)據(jù)表中
提交事務(wù)標(biāo)志著事務(wù)的結(jié)束，并且是一種全部成功的結(jié)束
回滾事務(wù)
將之前所有的 DDL 操作全部撤銷，并且清空事務(wù)性活動的日志文件
回滾事務(wù)標(biāo)志著事務(wù)的結(jié)束，并且是一種全部失敗的結(jié)束
提交事務(wù)：commit; 語句
回滾事務(wù)：rollback; 語句（回滾永遠(yuǎn)都是只能回滾到上一次的提交點?。?/p>
注意：在 MySQL 當(dāng)中默認(rèn)的事務(wù)行為是怎樣的？
MySQL 默認(rèn)情況下是支持自動提交事務(wù)的
即每執(zhí)行一條 DML 語句，則提交一次（這種自動提交的方式是不符合開發(fā)習(xí)慣的，因為一個業(yè)務(wù)通常是需要多條 DML 語句共同執(zhí)行才能完成的，為了保證數(shù)據(jù)的安全，必須要求同時成功后再提交，而不能執(zhí)行一條就提交一條）
如何關(guān)閉？ start transaction;
start transaction;delete ...insert ...update ...roll back; # 使得上述 DML 命令全部失敗(commit; # 使得上述 DML 命令全部成功) 均清空事務(wù)性活動的日志文件，標(biāo)志著事務(wù)的結(jié)束A ：原子性
事務(wù)是最小的工作單元，不可再分
C：一致性
在同一個事務(wù)當(dāng)中，所有操作必須同時成功，或者同時失敗，以保證數(shù)據(jù)的一致性
I：隔離性
A 事務(wù) 和 B 事務(wù)之間具有一定的隔離
D：持久性
事務(wù)最終結(jié)束的一個保障：事務(wù)提交，就相當(dāng)于將沒有保存到硬盤上的數(shù)據(jù)保存到硬盤上
讀未提交 read uncommitted（最低的隔離級別）
事務(wù) A 可以讀取到事務(wù) B未提交的數(shù)據(jù)
存在的問題：臟讀現(xiàn)象（Dirty Read）—— 讀到了臟數(shù)據(jù)
這種隔離級別一般都是理論上的，大多數(shù)的數(shù)據(jù)庫隔離級別都是二檔起步
讀已提交 read committed
事務(wù) A只能讀取到事務(wù) B提交之后的數(shù)據(jù)
解決了什么問題：解決了臟讀的現(xiàn)象
存在的問題：不可重復(fù)讀取數(shù)據(jù)
在事務(wù)開啟之后，第一次讀到的數(shù)據(jù)是 3 條，當(dāng)前事務(wù)還沒有結(jié)束，第二次再去讀取，可能讀到的數(shù)據(jù)變成 4 條，3 不等于 4 稱為不可重復(fù)讀取數(shù)據(jù)
這種隔離級別是比較真是的數(shù)據(jù)，每一次讀到的數(shù)據(jù)是絕對的真實
Oracle 數(shù)據(jù)庫默認(rèn)的隔離級別是：read committed
可重復(fù)讀repeatable read
提交之后也讀不到，永遠(yuǎn)讀取的都是剛開啟事務(wù)時的數(shù)據(jù)
可重復(fù)讀取
不管是多久，每一次在事務(wù) A 中讀取到的數(shù)據(jù)都是已知的，即使事務(wù) B 將數(shù)據(jù)已經(jīng)修改，并且提交了，事務(wù) A 讀取到的數(shù)據(jù)還是沒有發(fā)生改變。
解決了什么問題：解決了不可重復(fù)讀取數(shù)據(jù)
存在的問題：可能會出現(xiàn)幻影讀
每一次讀取到的數(shù)據(jù)都是幻象，不真實
MySQL 中默認(rèn)的事務(wù)隔離級別就是這個！
序列化/串行化 serializable（最高的隔離級別）
效率最低，解決了所有的問題
表示事務(wù)排隊，不能并發(fā)！
類似于 synchronized 線程同步（事務(wù)同步）
每一次讀取到的數(shù)據(jù)都是最真是的，并且效率是最低的
查看事務(wù)隔離級別
MySQL 8.0+select @@transaction_isolation; # 查看當(dāng)前會話隔離級別select @@global.transaction_isolation; # 查看系統(tǒng)當(dāng)前隔離級別MySQL 5.0+select @@tx_isolation; # 查看當(dāng)前會話隔離級別select @@global.tx_isolation; # 查看系統(tǒng)當(dāng)前隔離級別
設(shè)置事務(wù)隔離級別
# 設(shè)置全局事務(wù)隔離級別set global transaction isolation level ...;# 設(shè)置當(dāng)前會話事務(wù)隔離級別set session transaction isolation level ...;MySQL 在單個事務(wù)中 select 的查詢結(jié)果可以看到臨時的變化例如：start transaction 會取消自動提交例如：start transactioninsert into t_user2 values(1,2,'a');select * from t_user2; # 此時 可以看到查詢結(jié)果中有了數(shù)據(jù)（但這個數(shù)據(jù)只是臨時的）， # 但實際上沒有在沒有 commit 或 rollback 的情況下話，實際表中數(shù)據(jù)未變化commit; # 此時 實際表才有數(shù)據(jù) 事務(wù)結(jié)束（DML 語句全部成功），清空事務(wù)性活動的日志文件，并將數(shù)據(jù)徹底持久化在表中若存在其他事務(wù)，使用 select 還需要根據(jù) 事務(wù)隔離級別 做具體變換
MySQL 中 若連續(xù)使用兩次 start transaction，第一次 不 commit 也不 rollback例如：start transaction;insert into t_user2 values(1,2,'a');start transaction;insert into t_user2 values(2,3,'b');select * from t_user2; # 此時 兩個操作后的結(jié)果都能看到commit; # 將 第一個操作后的結(jié)果 也更新到表中了索引是在數(shù)據(jù)庫表的字段上添加的，是為了提高查詢效率存在的一種機(jī)制
一張表的一個字段可以添加一個索引，也可以多個字段聯(lián)合起來添加索引
索引相當(dāng)于一本書的目錄，是為了縮小掃描范圍而存在的一種機(jī)制
MySQL 在查詢方面主要是兩種方式：
全表掃描
根據(jù)索引檢索
注意：
在 MySQL 當(dāng)中索引是需要排序的，底層是一個 B-Tree 數(shù)據(jù)結(jié)構(gòu)
遵循左小右大原則存放，采用中序遍歷方式遍歷取數(shù)據(jù)
在任何數(shù)據(jù)庫當(dāng)中主鍵上都會自動添加索引對象，另外在 MySQL 當(dāng)中，一個字段上如果有 unique 約束的話，也會自動創(chuàng)建索引對象
在任何數(shù)據(jù)庫當(dāng)中，任何一張表的任何一條記錄在硬盤存儲上都有一個硬盤的物理存儲編號
在 MySQL 當(dāng)中，索引是一個單獨(dú)的對象，不同的存儲引擎以不同的形式存在
在 MyISAM 存儲引擎中，索引存儲在一個 .MYI 文件中
在 InnoDB 存儲引擎中，索引存儲在一個邏輯名稱叫做 tablespace 的表空間當(dāng)中
在 MEMORY 存儲引擎中，索引存儲在內(nèi)存當(dāng)中
不管索引存儲在哪里，索引在 MySQL 當(dāng)中都是以一個樹的形式存在（自平衡二叉樹：B-Tree）
select * from t_user where id = 101;# MySQL 發(fā)現(xiàn) id 字段上有索引對象，所以會通過索引對象 idIndex 進(jìn)行查找# 通過 101 得到物理編號：0x6666，此時馬上 SQL 語句轉(zhuǎn)換select * from t_user where 物理編號 = 0x6666;
實現(xiàn)原理：縮小掃描的范圍（形成樹），避免全表掃描
數(shù)據(jù)量龐大
該字段經(jīng)常出現(xiàn)在 where 后面，以條件的形式存在，即該字段總是被掃描
該字段有很少的 DML 操作（因為 DML 之后，索引需要重新排序）
建議不要隨意添加索引，因為索引也是需要維護(hù)的，太多的話反而會降低系統(tǒng)的性能。
建議通過主鍵查詢 或者 通過 unique 約束的字段進(jìn)行查詢，效率比較高
# 創(chuàng)建索引create index 索引名(自己起) on 表名(字段名);# 刪除索引drop index 索引名(已存在) on 表名;# 查詢是否采用索引explain select 字段 from 表名 where 字段名 ... ;select * from emp where ename like '%T';
# ename 上即使添加了索引，也不會走索引？
# 因為模糊匹配當(dāng)中以 '%' 開頭了，就會走模糊匹配的路線去查詢（全表查詢，需要知道第一個字母）
# 盡量避免模糊查詢的時候以 '%' 開頭（優(yōu)化策略）
select * from emp where ename = 'KING' or job = 'MANAGER';
# 如果 or 兩邊有一個字段沒有索引，那么另一個字段上的索引就會失效
create index emp_job_sal_index on emp(job,sal);
# 復(fù)合索引：兩個字段或者更多的字段聯(lián)合起來添加一個索引
# 若使用復(fù)合索引的時候，沒有使用左側(cè)的列查找，索引就會失效
create index emp_sal_index on emp(sal);
select * from emp where sal+1 = 800;
# 在 where 中，索引列若參加了運(yùn)算，索引失效
select * from emp where lower(ename) = 'smith';
# 若在 where 當(dāng)中索引列使用了函數(shù)，索引會失效
# 若使用 類型轉(zhuǎn)換 也會失效
單一索引
一個字段上添加索引
復(fù)合索引
兩個字段或者更多的字段上添加索引
主鍵索引
主鍵上添加索引
唯一性索引
具有 unique 約束的字段上添加索引
注意：唯一性比較弱的字段上添加索引用處不大
view：站在不同的角度去看待同一份數(shù)據(jù)
# 創(chuàng)建視圖對象 create view 視圖名(自己起) as select ...;# 刪除視圖對象 drop view 視圖名(已存在) # 注意： 只有 DQL 語句才能以 view 的形式創(chuàng)建 create view 視圖名 as DQL語句;
方便，簡化開發(fā)，利于維護(hù)
可以將一條復(fù)雜的 DQL 語句以視圖對象的形式新建，可以大大地簡化開發(fā)，并且利于后期的維護(hù)，因為修改的時候也只需要修改一個位置，只需要修改視圖對象所映射的 SQL 語句
使用視圖可以像使用 table 一樣，對視圖的 CRUD 也會更新到原表當(dāng)中
視圖不是在內(nèi)存當(dāng)中，視圖對象也是以文件形式存儲在硬盤上的，不會消失
還有一些特定的其他結(jié)構(gòu)，這些結(jié)構(gòu)會使得視圖不可更新。更具體地講，如果視圖包含以下結(jié)構(gòu)中的任何一種，它就是不可更新的：1. 聚合函數(shù) SUM()、MIN()、MAX()、COUNT() 等。2. DISTINCT 關(guān)鍵字。3. GROUP BY 子句。4. HAVING 子句。5. UNION 或 UNION ALL 運(yùn)算符。6. 位于選擇列表中的子查詢。7. FROM 子句中的不可更新視圖或包含多個表。8. WHERE 子句中的子查詢，引用 FROM 子句中的表。9. ALGORITHM 選項為 TEMPTABLE（使用臨時表總會使視圖成為不可更新的）的時候。ERROR 1471 (HY000): The target table t_user2_view of the INSERT is not insertable-intoERROR 1288 (HY000): The target table t_user2_view of the DELETE is not updatableDatabase Administrator 數(shù)據(jù)庫管理員
導(dǎo)出
在 windows 的 dos 命令窗口中：
# 導(dǎo)出數(shù)據(jù)庫
mysqldump 數(shù)據(jù)庫名>文件路徑名 -uroot -p
# 導(dǎo)出數(shù)據(jù)庫當(dāng)中的指定表
mysqldump 表名>文件的路徑名 -uroot -p
Enter password:xxxxxxx
導(dǎo)入
需要先登錄到 MySQL 數(shù)據(jù)庫服務(wù)器上
然后創(chuàng)建數(shù)據(jù)庫
create database 數(shù)據(jù)庫名;
使用數(shù)據(jù)庫
use 數(shù)據(jù)庫名;
初始化數(shù)據(jù)
source 文件路徑名;
數(shù)據(jù)庫表的設(shè)計依據(jù)。教你怎么進(jìn)行數(shù)據(jù)庫表的設(shè)計。
設(shè)計數(shù)據(jù)庫表的時候，按照以上的范式進(jìn)行，可以避免表中數(shù)據(jù)的冗余，空間的浪費(fèi)
要求任何一張表必須有主鍵，每一個字段原子性不可再分
最核心，最重要的范式，所有表的設(shè)計都需要滿足。
學(xué)生編號 學(xué)生姓名 聯(lián)系方式
--------------------------------------------
1001 張三 zs@123.com,13599999999
1002 李四 ls@123.com,15999999999
1003 王五 ww@123.com,13588888888
以上是學(xué)生表，不滿足第一范式
# 第一：沒有主鍵
# 第二：聯(lián)系方式可以分為郵箱地址和電話號碼
故更改為：
學(xué)生編號 學(xué)生姓名 郵箱地址 電話號碼
--------------------------------------------
1001 張三 zs@123.com 13599999999
1002 李四 ls@123.com 15999999999
1003 王五 ww@123.com 13588888888
建立在第一范式的基礎(chǔ)之上，要求所有非主鍵字段完全依賴主鍵，不要產(chǎn)生部分依賴
學(xué)生編號 學(xué)生姓名 教師編號 教師姓名
----------------------------------------
1001 張三 001 王老師
1002 李四 002 趙老師
1003 王五 001 王老師
1001 張三 002 趙老師
# 多對多關(guān)系
這張表描述了學(xué)生和老師的關(guān)系（1個學(xué)生可能有多個老師，1個老師也可能有多個學(xué)生）
不滿足第一范式？修改：
學(xué)生編號+教師編號（PK） 學(xué)生姓名 教師姓名
----------------------------------------
1001 001 張三 王老師
1002 002 李四 趙老師
1003 001 王五 王老師
1001 002 張三 趙老師
學(xué)生編號和教師編號，兩個字段聯(lián)合做主鍵，滿足了第一范式，但是不滿足第二范式！
# '張三’ 依賴 1001，'王老師’ 依賴 001，產(chǎn)生了部分依賴：
# 缺點：數(shù)據(jù)冗余，空間浪費(fèi)。'張三’重復(fù)，'王老師’重復(fù)，'趙老師’重復(fù)
修改：使用三張表來表示多對多的關(guān)系?。?！
學(xué)生表：
學(xué)生編號（PK） 學(xué)生姓名
-------------------------
1001 張三
1002 李四
1003 王五
教師表：
教師編號（PK） 教師姓名
--------------------------
001 王老師
002 趙老師
學(xué)生教師關(guān)系表
id（PK） 學(xué)生編號（PK） 教師編號（PK）
--------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
口訣：多對多，三張表，關(guān)系表兩個外鍵?。?！
建立在第二范式的基礎(chǔ)之上，要求所有非主鍵字段直接依賴主鍵，不要產(chǎn)生傳遞依賴
學(xué)生編號（PK） 學(xué)生姓名 班級編號 班級名稱
---------------------------------------------
1001 張三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 趙六 03 一年三班
# 一對多關(guān)系
以上表的設(shè)計是描述：班級和學(xué)生的關(guān)系（一個班級可以有多個學(xué)生）
1. 滿足第一范式：有主鍵
2. 滿足第二范式：主鍵不是復(fù)合主鍵，沒有產(chǎn)生部分依賴。主鍵是單一主鍵
3. 是否滿足第三范式？
不滿足！ '一年一班’ 依賴 '01’，'01’ 依賴 '1001’，產(chǎn)生了傳遞依賴
修改：
班級表：
班級編號（PK） 班級名稱
-------------------------
01 一年一班
02 一年二班
03 一年三班
學(xué)生表：
學(xué)生編號（PK） 學(xué)生姓名 班級編號（FK）
---------------------------------------
1001 張三 01
1002 李四 02
1003 王五 03
1004 趙六 03
口訣：一對多，兩張表，多的表加外鍵！
一對多
一對多，兩張表，多的表加外鍵！
多對多
多對多，三張表，關(guān)系表兩個外鍵！
一對一
在實際的開發(fā)中，可能存在一張表字段太多，過于龐大，這時候就需要拆分表
t_user
id login_name login_pwd real_name email address ...
---------------------------------------------------------------------
1 zhangsan 123 張三 zs@123.com
2 lisi 567 李四 lisi@123.com
...
這樣龐大的表建議拆分為兩張：
t_login 登錄信息表
id(PK) login_name login_pwd
-------------------------------
1 zhangsan 123
2 lisi 567
t_user 用戶詳細(xì)信息表
id(PK) real_name email address... login_id(FK+unique)
-----------------------------------------------------------------------
100 張三 zs@123.com 1
200 李四 lisi@123.com 2
口訣：一對一，外鍵唯一！
數(shù)據(jù)庫設(shè)計三范式是理論上的
實踐和理論有的時候是有偏差的
最終的目的都是為了滿足客戶的需求，有的時候會拿冗余換執(zhí)行速度
因為在 sql 中，表和表之間連接次數(shù)越多，效率越低（笛卡爾積）
有的時候可能會存在冗余，但是為了減少表的連接次數(shù)，這樣做也是合理的，并且對于開發(fā)人員來說，sql 語句的編寫難度也會降低
聯(lián)系客服
