Mysql 基礎(chǔ)
SQL分類：
DDL—數(shù)據(jù)定義語言(create，alter，drop)
DML—數(shù)據(jù)操縱語言(select，delete，update，insert)
DCL—數(shù)據(jù)控制語言(grant，revoke)
DDL操作:
數(shù)據(jù)庫連接：
# mysql -h 127.0.0.1 -u root -p
常用參數(shù)：
-A 不預(yù)讀數(shù)據(jù)庫信息，提高連接和切換數(shù)據(jù)庫速度,使用--disable-auto-rehash代替
--default-character-set 使用的默認(rèn)字符集
-e 執(zhí)行命令并退出
-h 主機(jī)地址
-p 連接到服務(wù)器時使用的密碼
-P 連接的端口號
創(chuàng)建數(shù)據(jù)庫:
create database test1 ;
查看數(shù)據(jù)庫:
show databases;
選擇數(shù)據(jù)庫:
use mysql;
刪除數(shù)據(jù)庫:
drop database test1;
創(chuàng)建表:
create table emp( ename varchar(20), hiredate date, sal decimal(10,2), deptno int(2) );
查看表定義:
desc emp;
查看創(chuàng)建表SQL:
show create table emp ;
更新表名:
alter table emp rename users;
刪除表:
drop table emp;
修改表字段:
alter table emp modify ename varchar(30);
增加表字段:
alter table emp add column age int(3);
修改表字段:
alter table emp change age age int(4);
刪除表字段:
alter table emp drop column age;
change和modify:
1 前者可以修改列名稱,后者不能.
2 change需要些兩次列名稱.
字段增加修改 add/change/modify/ 添加順序:
1 add 增加在表尾.
2 change/modify 不該表字段位置.
3 修改字段可以帶上以下參數(shù)進(jìn)行位置調(diào)整(frist/after column_name);
alter table emp change age age int(2) after ename;
alter table emp change age age int(3) first;
DML語句:
插入記錄:
//指定字段,
//自增,默認(rèn)值等字段可以不用列出來,沒有默認(rèn)值的為自動設(shè)置為NULL
insert into emp (ename,hiredate,sal,deptno) values ('jack','2000-01-01','2000',1);
//可以不指定字段,但要一一對應(yīng)
insert into emp values ('lisa','2010-01-01','8000',2);
批量記錄:
insert into emp values ('jack chen','2011-01-01','18000',2),('andy lao','2013-01-01','18000',2);
更新記錄:
update emp set sal="7000.00" where ename="jack";
update emp e,dept d set e.sal="10000",d.deptname=e.ename where e.deptno=d.deptno and e.ename="lisa";
刪除記錄:
//請仔細(xì)檢查where條件,慎重
delete from emp where ename='jack';
查看記錄:
//查看所有字段
select * from emp;
//查詢不重復(fù)記錄
select distinct(deptno) from emp ;
select distinct(deptno),emp.* from emp ;
//條件查詢
//比較運算符: > < >= <= <> != ...
//邏輯運算符: and or ...
select * from emp where sal="18000" and deptno=2;
排序
//desc降序,asc 升序(默認(rèn))
select * from emp order by deptno ;
select * from emp order by deptno asc;
select * from emp order by deptno desc,sal desc;
限制記錄數(shù):
select * from emp limit 1;
select * from emp limit 100,10;
select * from emp order by deptno desc,sal desc limit 1;
聚合:
函數(shù):count():記錄數(shù) / sum(總和); / max():最大值 / min():最小值
select count(id) from emp ;
select sum(sal) from emp ;
select max(sal) from emp ;
select min(sal) from emp ;
group by
分組
select * from emp group by deptno;
//分組統(tǒng)計
select count(deptno) as count from emp group by deptno;
select count(deptno) as count,deptno from emp group by deptno;
select count(deptno) as count,deptno,emp.* from emp group by deptno;
having
對分組結(jié)果二次過濾
select count(deptno) as count,deptno from emp group by deptno having count > 2;
with rollup
對分組結(jié)果二次匯總
select count(sal),emp.* from emp group by sal, deptno with rollup ;
表連接:
- 內(nèi)連接:關(guān)聯(lián)表都要有匹配記錄
- 外連接:關(guān)聯(lián)表會選出對應(yīng)不匹配的記錄
內(nèi)連接:
select * from emp as e,dept as d where e.deptno=d.deptno;
select * from emp as e inner join dept as d on e.deptno=d.deptno;
左外連接:包含左表的全部記錄和右表不存在的記錄
select * from emp as e left join dept as d on e.deptno=d.deptno;
右外連接:包含右表的全部記錄和左表不存在的記錄
select * from emp as e right join dept as d on e.deptno=d.deptno;
子查詢:
//=, !=
select * from emp where deptno = (select deptno from dept where deptname="技術(shù)部");
select * from emp where deptno != (select deptno from dept where deptname="技術(shù)部");
//in, not in
//當(dāng)需要使用里面的結(jié)果集的時候必須用in();
select * from emp where deptno in (select deptno from dept where deptname="技術(shù)部");
select * from emp where deptno not in (select deptno from dept where deptname="技術(shù)部");
//exists , not exists
//當(dāng)需要判斷后面的查詢結(jié)果是否存在時使用exists();
select * from emp where exists (select deptno from dept where deptno > 5);
select * from emp where not exists (select deptno from dept where deptno > 5);
記錄聯(lián)合:
union:返回去重之后的結(jié)果
select ename from emp union select ename from emp;
union all:返回所有結(jié)果
select ename from emp union all select ename from emp;
DCL語句:
添加權(quán)限:
grant select,insert on test.* to 'db_user_1'@'localhost' identified by '123456';
flush privileges;
回收權(quán)限:
revoke insert on test.* from 'db_user_1'@'localhost';
后面會具體描述
Mysql數(shù)據(jù)類型
整數(shù)類型
指定寬度:
指定顯示的寬度為5,不影響實際數(shù)據(jù)
create table t1 (id int , id2 int(5));
zerofill:
采用零填充,不足5位采用0填充
配合數(shù)據(jù)寬度
create table t2 (id int , id2 int(5) zerofill);
unsigned:
create table t3 (id int , id2 int(5) unsigned);
auto_increment:
只用于整數(shù)類型
產(chǎn)生唯一標(biāo)識
值從1開始,逐行增加
一個表中最多只能存在一個自增列
自增列應(yīng)該定義為not null
自增列應(yīng)該這只為 primary key 或者 unique
id int not null auto_increment primary key
浮點數(shù)類型
定點數(shù)類型
定點數(shù)類型.png
float , double , decimal 特點:
1.(m,d)表示方式:m指的是整數(shù)位,d指的是小數(shù)位(又稱作精度和標(biāo)度)
2.float/double四舍五入丟失精度,decimal會截斷數(shù)據(jù)并輸出warning
3.如果不指定精度,float/double采用操作系統(tǒng)默認(rèn),decimal則是(10,0)
位類型
位類型.png
1 存放位字段值
2 指定存放多位二進(jìn)制的長度,默認(rèn)為1(范圍:1~64)
3 讀取需要bin()/hex(),普通的select讀取結(jié)果為null
4 插入的值會轉(zhuǎn)化為二進(jìn)制碼,如果長度運行則正常處理,否則插入失敗
create table t6 (id bit(1));
select bin(id) from t6;
日期和時間類型
當(dāng)前系統(tǒng)日期
timestamp:返回yyyy-mm-dd hh:mm:ss 寬度19
timestamp:不適合存放久遠(yuǎn)日期,超出范圍則會采用零值填充
//不同格式的顯示零值格式
d date, t time,dt datetime
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2016-11-25 | 14:52:44 | 2016-11-25 14:52:44 |
+------------+----------+---------------------+
//默認(rèn)值的體現(xiàn)
id1 timestamp
+---------------------+
| id1 |
+---------------------+
| 2016-11-25 14:55:45 |
+---------------------+
//timestamp字段只能有一個"CURRENT_TIMESTAMP"
+-------+-----------+------+-----+---------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+---------------------+-----------------------------+
| id1 | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| id2 | timestamp | NO | | 0000-00-00 00:00:00 | |
+-------+-----------+------+-----+---------------------+-----------------------------+
//timestamp和時區(qū)相關(guān):SYSTEM 指的是和主機(jī)時區(qū)保持一致
show variables like "%_zone";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
//修改時區(qū)
set time_zone="+9:00"/在 [mysqld] 之下加 default-time-zone=timezone
年份
year:默認(rèn)為4位格式.1901~2155和0000. 2位的已經(jīng)不推薦,高版本已經(jīng)不支持了.
timestamp和datetime區(qū)別:
- timestamp支持范圍小(1970-01-01 08:00:01到2038年某個點)
- 表中第一個timestamp字段,會默認(rèn)采用當(dāng)前系統(tǒng)時間.如果更新其他字段,該字段沒有賦值的話,則該字段會自動更新.如果指定字段不滿足規(guī)格,則采用零值填充
- timestamp查詢和插入都會受到當(dāng)?shù)貢r區(qū)影響
datetime支持范圍寬度大(1000-01-01 00:00:00到9999-12-31 23:23:59)
字符串類型
char和varchar:
- char定長，效率高,在創(chuàng)建字段的時候就已經(jīng)指定,一般用于固定長度的表單提交數(shù)據(jù)存儲
- char在檢索的時候回去掉尾部的空格
- varchar是動態(tài)長度
- varchar在檢索的時候回保留尾部的空格
- varchar將實際內(nèi)容單獨存儲在聚簇索引之外，內(nèi)容開頭用1到2個字節(jié)表示實際長度(長度>255時需要2個字節(jié))
- 字符類型若為gbk，每個字符最多占2個字節(jié)，字符類型若為utf8，每個字符最多占3個字節(jié)。
枚舉類型:
- 枚舉在集合中取值,只能取一個
- 如果值不存在則采用第一個值為默認(rèn)
- 如果插入NULL不會出錯,會將NULL寫入表
- 成員個數(shù)在1255占用1個字節(jié),在25565535占用2個字節(jié),節(jié)約資源
create table `t8` (
`gender` enum('m','f') default null
) engine=innodb default charset=utf8
集合類型
集合類型和枚舉很像,但是支持多值選擇
最多可以保存64個成員,每8個成員占1個字節(jié)
重復(fù)值只會插入一次,如果查出集合范圍則插入為null
create table t9 (col set ('a','b','c','d'));
Mysql運算符
算術(shù)運算符
比較運算符
- NULL值不能參與"=","!=","<==>","<>"等場景比較
select * from t1 where id is null;
select * from t1 where id is not null;
- between 比較為閉區(qū)間
a between min and max ==> a >=min and a<=max
select * from emp where deptno between 1 and 3;
- in 在集合中匹配
select * from emp where deptno in (1,2,3);
- like 這個在大量數(shù)據(jù)的情況下慎重選擇，會影響查詢性能。
select id2 from t1 where id2 like "%03%";
- regexp 這個在大量數(shù)據(jù)的情況下慎重選擇，會影響查詢性能。
select * from t1 where id2 regexp '0001';
邏輯運算符
位運算符
運算符優(yōu)先級
很多情況下，我們可直接采用“（）”進(jìn)行運算符調(diào)整，邏輯清晰，閱讀性強(qiáng)。
常用函數(shù)
字符串函數(shù)
數(shù)值函數(shù)
日期和時間函數(shù)
日期間隔類型：
流程函數(shù)
其他函數(shù)
MySQL管理工具和應(yīng)用程序
工欲善其事，必先利其器。幾乎每個開發(fā)人員都有最鐘愛的 MySQL 管理工具，它幫助開發(fā)人員在許多方面支持包括 PostgreSQL，MySQL，SQLite，Redis，MongoDB 等在內(nèi)的多種數(shù)據(jù)庫；提供各種最新的特性，包括觸發(fā)器、事件、視圖、存儲過程和外鍵，支持導(dǎo)入、數(shù)據(jù)備份、對象結(jié)構(gòu)等多種功能。
這篇文章收集了15款最佳的 MySQL 管理工具和應(yīng)用軟件，幫助你輕松快速完成工作。
Induction
Induction是一款用于理解數(shù)據(jù)關(guān)系的開源管理工具，它可用來探索行/列，運行查詢和數(shù)據(jù)可視化等方面。該工具支持多種數(shù)據(jù)庫，包括PostgreSQL，MySQL，SQLite，Redis以及MongoDB。此外，Induction還可以通過編寫添加其他新的適配器。
Pinba
Pinba 是一種MySQL存儲引擎，用于PHP實時監(jiān)控和數(shù)據(jù)服務(wù)器的MySQL只讀接口。它整理并處理通過UDP發(fā)送的數(shù)據(jù)，并以可讀的簡單報告的形式統(tǒng)計顯示多個PHP進(jìn)程。為了獲取下一代更為復(fù)雜的報告和統(tǒng)計數(shù)據(jù)，Pinba提供了原始數(shù)據(jù)的只讀接口。
DB Ninja
DbNinja是一款先進(jìn)的基于Web的MySQL數(shù)據(jù)庫管理與開發(fā)應(yīng)用程序。它是遠(yuǎn)程訪問托管服務(wù)器的必然之選。DbNinja支持所有最新的功能，包括觸發(fā)器、事件、視圖、存儲過程和外鍵等。此外,它還可以導(dǎo)入和備份數(shù)據(jù)、MySQL對象結(jié)構(gòu)以及管理用戶等。DbNinj的用戶界面功能完備且清新美觀，可安全地運用于任何瀏覽器及任何操作系統(tǒng)中。
DB Tools Manager
DBManager是一款功能強(qiáng)大的數(shù)據(jù)管理應(yīng)用程序。作為最先進(jìn)的應(yīng)用程序，DBManager內(nèi)置支持MySQL、PostgreSQL、Interbase/Firebird、 SQLite,DBF表、MSAccess,MSSQL服務(wù)器,Sybase,Oracle和ODBC數(shù)據(jù)庫引擎等一些新特性。DBManager目前擁有個人和企業(yè)兩個版本，用戶可按需選擇使用。
Dbeaver
DBeaver是一款免費的數(shù)據(jù)庫管理應(yīng)用程序，可運用于多種不同的引擎，包括MySQL，MSSQL，Oracle、SQLite、Sybase和Firebird等等。由Java編寫而成，該應(yīng)用程序適用于所有主流操作系統(tǒng)(Windows、Mac和Linux)。它能處理包括元數(shù)據(jù)編輯(表、列、鍵、索引)、自定義SQL執(zhí)行、用戶管理、多連接等在內(nèi)的所有主要任務(wù)。
SqlWave
SQLWave是一種簡單、快速且易用的MySQL客戶端。用戶可通過該工具輕松地連接到遠(yuǎn)程主機(jī)。SqlWave支持所有MySQL的最新版本，包括它用來管理數(shù)據(jù)庫結(jié)構(gòu)的所有最新功能，如工作表、視圖、存儲過程、函數(shù)、事件、外鍵和觸發(fā)器等。
MyWebSQL
MyWebSQL主要用于管理基于Web的MySQL數(shù)據(jù)庫。與桌面應(yīng)用程序的借口工作流程相似，用戶無需切換網(wǎng)頁即可完成一些簡單的操作。如果您正在操作桌面，只用登數(shù)據(jù)庫，就可以管理您的數(shù)據(jù)庫了。
MyWebSQL主要用于管理基于Web的MySQL數(shù)據(jù)庫。與桌面應(yīng)用程序的借口工作流程相似，用戶無需切換網(wǎng)頁即可完成一些簡單的操作。如果您正在操作桌面，只用登數(shù)據(jù)庫，就可以管理您的數(shù)據(jù)庫了。
Navicat
Navicat是MySQL和MariaDB數(shù)據(jù)庫管理與開發(fā)理想的解決方案。它可同時在一個應(yīng)用程序上連接MySQL和MariaDB數(shù)據(jù)庫。這種兼容前端為數(shù)據(jù)庫提供了一個直觀而強(qiáng)大的圖形界面管理、開發(fā)和維護(hù)功能，為初級MySQL和MariaDB開發(fā)人員和專業(yè)開發(fā)人員都提供了一組全面的開發(fā)工具。
SQLyog
SQLyog是一款功能最強(qiáng)大的MySQL管理工具，它綜合了MySQL工作臺、php MyAdmin和其他MySQL前端及MySQL GUI工具的特點。該款應(yīng)用程序可以同時連接任意數(shù)量級的MySQL服務(wù)器，用于測試和生產(chǎn)。所有流程僅需登錄MySQL root以收集數(shù)據(jù)，用戶無需安裝在MySQL服務(wù)器上。
Sequel Pro
Sequel Pro是基于MySQL數(shù)據(jù)庫的一種快速易用的Mac數(shù)據(jù)庫管理應(yīng)用程序。用戶可通過Sequel Pro在本地和遠(yuǎn)程服務(wù)器上直接訪問MySQL數(shù)據(jù)庫。不過，Sequel Pro的最新版本開始添加全屏支持內(nèi)置。
HeidiSQL
HeidiSQL是一種專為web開發(fā)人員設(shè)計的有用且可靠的工具，可用于當(dāng)前最受歡迎的MySQL服務(wù)器，微軟SQL數(shù)據(jù)庫和PostgreSQL。該工具可提供瀏覽和編輯數(shù)據(jù)、創(chuàng)建和編輯表格、視圖、過程、觸發(fā)器和日志安排等事件。此外，您還可以導(dǎo)出結(jié)構(gòu)和數(shù)據(jù)至SQL文件、剪貼板或其他服務(wù)器。
MyDB Studio
/)
MyDB Studio是一款免費的MySQL數(shù)據(jù)庫管理器應(yīng)用程序。該工具強(qiáng)大到您幾乎可以獲取到任何想要的功能，并能夠連接到無限量級的數(shù)據(jù)庫。通過創(chuàng)建、編輯或刪除數(shù)據(jù)庫、表格和記錄，就可以備份/恢復(fù)并導(dǎo)出為多個格式。
SQL Lite Manger
SQL Lite Manager是一款基于web的開源應(yīng)用程序，用于管理無服務(wù)器、零配置SQL Lite數(shù)據(jù)庫。該程序是用PHP寫成，除了記錄和應(yīng)用表格格上的每一步操作，還可以控制多個數(shù)據(jù)庫。SQL Lite Manager可用于查詢數(shù)據(jù)，將MySQL查詢轉(zhuǎn)化為兼容SQL Lite數(shù)據(jù)庫，并能創(chuàng)建和編輯觸發(fā)器。SQL Lite Manager有多種皮膚選項，是一個含現(xiàn)成語言文件的多語言資源。
Database Master
Database Master是一個現(xiàn)代的、強(qiáng)大的、直觀且易用的數(shù)據(jù)庫管理程序。它以一個一致而現(xiàn)代的界面適用于MongoDB、MySQL、PostgreSQL、FireBird、 SQL Lite、MS SQL Server、SQL Azure、Oracle、IBM DB2、IBM Informix、Netezza、Ingres以及EffiProz等數(shù)據(jù)庫。Database Master簡化了管理、查詢、編輯、可視化、設(shè)計和報告數(shù)據(jù)庫系統(tǒng)。用戶可以通過ODBC與OleDB連接任何數(shù)據(jù)庫系統(tǒng)，也可以訪問MS Access，MS FoxPro Database、Dbase和XML文件。
Chive
Chive由PHP搭建而成，是一款基于web的開源MySQL管理應(yīng)用程序。此款應(yīng)用程式有一個內(nèi)置的編輯器，當(dāng)運行復(fù)雜的查詢或易用的界面時，可用于快速瀏覽。
Mysql存儲引擎
Mysql默認(rèn)支持多種存儲引擎，來適應(yīng)不同的場景需求。
默認(rèn)存儲引擎：
在5.5以前的版本為MyISAM，在5.5之后為InnoDB
查看存儲支持的引擎：
show engines\G
show variables like "have_%";
創(chuàng)建表指定引擎：
create table `ai` (
`i` bigint(20) not null auto_increment,
primary key (`i`)
) engine=myisam default charset=utf8
修改表引擎:
alter table ai engine=innodb;
存儲引擎對比:
各種存儲引擎特性:
MyISAM
特點:
1 不支持事務(wù),不支持外鍵
2 訪問速度快
存儲結(jié)構(gòu):
存在3個文件,文件名和表名相同,擴(kuò)展名分別為:
.frm 存儲表定義
.myd 存儲數(shù)據(jù)
.myi 存儲索引
檢測修復(fù):
check table 檢查表
repair table 修復(fù)表
表存儲格式:
靜態(tài)表,動態(tài)表,壓縮表
靜態(tài)表:
字段都是非變長字段,存儲快,容易緩存,易恢復(fù);占用空間多;丟失尾部空格
動態(tài)表:
包含變長字段,記錄不固定,節(jié)省空間;頻繁更新和刪除會產(chǎn)生碎片,需要進(jìn)行定期整理
optimize table 整理碎片
[擴(kuò)展1](http://blog.csdn.net/andybbc/article/details/50562355)
壓縮表:
由myisampack工具創(chuàng)建,占用空間小.
使用場景：
1.選擇密集型的表。MyISAM存儲引擎在篩選大量數(shù)據(jù)時非常迅速，這是它最突出的優(yōu)點。
2.插入密集型的表。MyISAM的并發(fā)插入特性允許同時選擇和插入數(shù)據(jù)。例如：MyISAM存儲引擎很適合管理郵件或Web服務(wù)器日志數(shù)據(jù)。
InnoDB
特點:
1.更新密集的表。InnoDB存儲引擎特別適合處理多重并發(fā)的更新請求。
2.事務(wù)。InnoDB存儲引擎是支持事務(wù)的標(biāo)準(zhǔn)MySQL存儲引擎。
3.自動災(zāi)難恢復(fù)。與其它存儲引擎不同，InnoDB表能夠自動從災(zāi)難中恢復(fù)。
4.外鍵約束。MySQL支持外鍵的存儲引擎只有InnoDB。
5.支持自動增加列auto_increment屬性，并且必須是索引。如果是組合索引，也必須是第一列（myisam可以是其他列或者組合列）。
//設(shè)置自增列的初始值
alter table autoincre_demo auto_increment=10;
//查看當(dāng)前線程最后插入自增記錄值
select last_insert_id();
//在多表數(shù)據(jù)導(dǎo)入的時候，可以關(guān)閉外鍵約束提高速度。
set foreign_key_checks=0;//關(guān)閉
set foreign_key_checks=1;//開啟
存儲方式:
1 共享表空間：
- 表結(jié)構(gòu)保存在.frm文件中
- 數(shù)據(jù)保存在innodb_data_home_dir
- 索引保存在innodb_data_file_path。
2 多表空間：
- 表結(jié)構(gòu)保存在.frm文件中。
- 數(shù)據(jù)和索引單獨保存在.ibd中。
- 分區(qū)表每個分區(qū)對應(yīng)單獨的.ibd文件，文件名為“表明+分區(qū)名”，可以在創(chuàng)建* 分區(qū)的時候指定數(shù)據(jù)文件位置。數(shù)據(jù)文件沒有大小限制，不需要設(shè)置初始大小。
Memory
特點：
- Memory存儲引擎的處理速度比較快。采用的邏輯存儲介質(zhì)是系統(tǒng)內(nèi)存。
- 當(dāng)mysqld守護(hù)進(jìn)程崩潰時，數(shù)據(jù)都會丟失。
- 它要求存儲在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長度不變的格式，這意味著不能使用BLOB和TEXT這樣的長度可變的數(shù)據(jù)類型，VARCHAR是一種長度可變的類型，但因為它在MySQL內(nèi)部當(dāng)做長度固定不變的CHAR類型，所以可以使用。
索引：
Memory同時支持散列索引和B樹索引。B樹索引的優(yōu)于散列索引的是，可以使用部分查詢和通配查詢，也可以使用<、>和>=等操作符方便數(shù)據(jù)挖掘。散列索引進(jìn)行“相等比較”非?？欤菍Α胺秶容^”的速度就慢多了，因此散列索引值適合使用在=和<>的操作符中，不適合在<或>操作符中，也同樣不適合用在order by子句中。
使用場景：
目標(biāo)數(shù)據(jù)較小，而且被非常頻繁地訪問。在內(nèi)存中存放數(shù)據(jù)，所以會造成內(nèi)存的使用，可以通過參數(shù)max_heap_table_size控制Memory表的大小，設(shè)置此參數(shù)，就可以限制Memory表的最大大小。
如果數(shù)據(jù)是臨時的，而且要求必須立即可用，那么就可以存放在內(nèi)存表中。
存儲在Memory表中的數(shù)據(jù)如果突然丟失，不會對應(yīng)用服務(wù)產(chǎn)生實質(zhì)的負(fù)面影響。
Merge
特點：
MERGE存儲引擎是一組MyISAM表的組合，這些MyISAM表結(jié)構(gòu)必須完全相同。
Merge表中并沒有數(shù)據(jù)，對Merge類型的表可以進(jìn)行查詢、更新、刪除操作，這些操作實際上是對內(nèi)部的MyISAM表進(jìn)行操作。
對于插入操作，是通過INSERT_METHOD字句定義插入的表。
對于表刪除，只是刪除該merge的定義，對內(nèi)部表無影響。
merge表會產(chǎn)生兩個文件，文件以表名字開始，.frm存放表定義，.mrg文件包含組合表信息。
CREATE TABLE `payment_2006` (
`country_id` int(11) DEFAULT NULL,
`payment_date` datetime DEFAULT NULL,
`amount` decimal(15,2) DEFAULT NULL,
KEY `idx_fk_country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `payment_2007` (
`country_id` int(11) DEFAULT NULL,
`payment_date` datetime DEFAULT NULL,
`amount` decimal(15,2) DEFAULT NULL,
KEY `idx_fk_country_id` (`country_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
CREATE TABLE `payment_all` (
`country_id` int(11) DEFAULT NULL,
`payment_date` datetime DEFAULT NULL,
`amount` decimal(15,2) DEFAULT NULL,
KEY `idx_fk_country_id` (`country_id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8 INSERT_METHOD=LAST UNION=(`payment_2006`,`payment_2007`)
區(qū)別：
merge和分區(qū)表類似，但是merge引擎不能智能的將數(shù)據(jù)寫到某個數(shù)據(jù)表中，而分區(qū)表可以。
使用場景：
對于服務(wù)器日志這種信息，一般常用的存儲策略是將數(shù)據(jù)分成很多表，每個名稱與特定的時間端相關(guān)。
TokuDB
特點：
1 高性能，支持事務(wù)處理。
2 高擴(kuò)展，高壓縮比，高效寫入。
3 支持大多數(shù)在線DDL操作
使用場景：
1 日志數(shù)據(jù)，插入頻繁，存儲量大
2 歷史數(shù)據(jù)，后期不會在有寫入操作
3 在線DDL頻繁的情況
ARCHIVE
特點：
1 僅支持最基本的插入和查詢兩種功能。
2 在MySQL 5.5版以前，Archive是不支持索引，但是在MySQL 5.5以后的版本中就開始支持索引了。
3 Archive擁有很好的壓縮機(jī)制，它使用zlib壓縮庫，在記錄被請求時會實時壓縮，所以它經(jīng)常被用來當(dāng)做倉庫使用。
總結(jié)
選擇合適的數(shù)據(jù)類型
char與varchar
char是固定長度，varchar可變長度。
char插入的時候去去掉尾部空格，varchar不會。
使用建議：
text和blob
text：保存字符數(shù)據(jù)
blob：保存二進(jìn)制數(shù)據(jù)。
BLOB和TEXT值在執(zhí)行了大量的刪除或更新操作的時候，會在數(shù)據(jù)表中留下很大的"空洞"，建議定期使用 OPTIMIZE TABLE 功能對這類表進(jìn)行碎片整理.
使用合成的（synthetic）索引。一種辦法是根據(jù)其它的列的內(nèi)容建立一個散列值，并把這個值存儲在單獨的數(shù)據(jù)列中。接下來你就可以通過檢索散列值找到數(shù)據(jù)行了。但是，我們要注意這種技術(shù)只能用于精確匹配的查詢（散列值對于類似<或>=等范圍搜索操作符 是沒有用處的）。我們可以使用MD5()函數(shù)生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的應(yīng)用程序邏輯來計算散列值。(如果散列算法生成的字符串帶有尾部空格，就不要把它們存儲在CHAR或VARCHAR列中，它們會受到尾部空格去除的影響。) 用散列標(biāo)識符值查找的速度比搜索BLOB列本身的速度快很多。
在不必要的時候避免檢索大型的BLOB或TEXT值。你可以搜索索引列，決定那些需要的數(shù)據(jù)行，然后從合格的數(shù)據(jù)行中檢索BLOB或 TEXT值。
把BLOB或TEXT列分離到單獨的表中。這會減少主表中的碎片，使你得到固定長度數(shù)據(jù)行的性能優(yōu)勢。它還使你在主數(shù)據(jù)表上運行 SELECT * 查詢的時候不會通過網(wǎng)絡(luò)傳輸大量的BLOB或TEXT值。
浮點數(shù)與定點數(shù)
浮點數(shù)：包含小數(shù)部分的數(shù)值，如果插入數(shù)據(jù)超過該列的實際精度，會被四舍五入插入。
定點數(shù)：是以字符串的形式存放的，可以更加精準(zhǔn)的確保存數(shù)據(jù)。
提示：
- 浮點數(shù)存在誤差問題；
- 對貨幣等對精度敏感的數(shù)據(jù)，應(yīng)該用定點數(shù)表示或存儲；
- 編程中，如果用到浮點數(shù)，要特別注意誤差問題，并盡量避免做浮點數(shù)比較；
- 要注意浮點數(shù)中一些特殊值的處理。
日期類型
1 根據(jù)實際需要選擇滿足的最小存儲日期類型
2 如果記錄YYYY-mm-dd H:i:s類型，最好使用datetime
3 如果記錄的日期需要讓不同時區(qū)的用戶使用，最好采用timestamp
mysql 字符集
字符集比較
選擇合適的字符集
1 選擇合適的字符集，如果支持多種語言，就應(yīng)該采用Unicode字符集(常見UTT-8)。
2 涉及數(shù)據(jù)導(dǎo)入，則應(yīng)該充分考慮字符集對于現(xiàn)有數(shù)的兼容性。
3 如果只支持中文，數(shù)據(jù)量大，性能要求高，則選用中文編碼。這樣可以減少磁盤IO,數(shù)據(jù)庫Cache以及網(wǎng)絡(luò)傳輸時間
4 如果數(shù)據(jù)庫做大量字符運算，例如比較，排序等，選擇定長的字符集比變長的字符集處理速度快。
支持的字符集
查看所有字符集：
show charset ;
mysql> show charset ;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| dec8 | DEC West European | dec8_swedish_ci | 1 |
| cp850 | DOS West European | cp850_general_ci | 1 |
| hp8 | HP West European | hp8_english_ci | 1 |
| koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 |
| latin1 | cp1252 West European | latin1_swedish_ci | 1 |
| latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 |
| swe7 | 7bit Swedish | swe7_swedish_ci | 1 |
| ascii | US ASCII | ascii_general_ci | 1 |
| hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 |
| koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 |
| greek | ISO 8859-7 Greek | greek_general_ci | 1 |
| cp1250 | Windows Central European | cp1250_general_ci | 1 |
| latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 |
| armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 |
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| cp866 | DOS Russian | cp866_general_ci | 1 |
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 |
| macce | Mac Central European | macce_general_ci | 1 |
| macroman | Mac West European | macroman_general_ci | 1 |
| cp852 | DOS Central European | cp852_general_ci | 1 |
| latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| cp1251 | Windows Cyrillic | cp1251_general_ci | 1 |
| cp1256 | Windows Arabic | cp1256_general_ci | 1 |
| cp1257 | Windows Baltic | cp1257_general_ci | 1 |
| binary | Binary pseudo charset | binary | 1 |
| geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 |
+----------+-----------------------------+---------------------+--------+
Mysql的字符集包括字符集（character）和校對字符集（collation）兩個概念。每種字符串至少有一個校對規(guī)則。
字符集：存儲字符串的方式
校對字符集：定義比較字符串的方式
show collation like "%utf8%";
mysql> show collation like "%utf8%";
+--------------------------+---------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+---------+-----+---------+----------+---------+
| utf8_general_ci | utf8 | 33 | Yes | Yes | 1 |
| utf8_bin | utf8 | 83 | | Yes | 1 |
| utf8_unicode_ci | utf8 | 192 | | Yes | 8 |
| utf8_icelandic_ci | utf8 | 193 | | Yes | 8 |
| utf8_latvian_ci | utf8 | 194 | | Yes | 8 |
| utf8_romanian_ci | utf8 | 195 | | Yes | 8 |
| utf8_slovenian_ci | utf8 | 196 | | Yes | 8 |
| utf8_polish_ci | utf8 | 197 | | Yes | 8 |
| utf8_estonian_ci | utf8 | 198 | | Yes | 8 |
| utf8_spanish_ci | utf8 | 199 | | Yes | 8 |
| utf8_swedish_ci | utf8 | 200 | | Yes | 8 |
| utf8_turkish_ci | utf8 | 201 | | Yes | 8 |
| utf8_czech_ci | utf8 | 202 | | Yes | 8 |
| utf8_danish_ci | utf8 | 203 | | Yes | 8 |
| utf8_lithuanian_ci | utf8 | 204 | | Yes | 8 |
| utf8_slovak_ci | utf8 | 205 | | Yes | 8 |
| utf8_spanish2_ci | utf8 | 206 | | Yes | 8 |
| utf8_roman_ci | utf8 | 207 | | Yes | 8 |
| utf8_persian_ci | utf8 | 208 | | Yes | 8 |
| utf8_esperanto_ci | utf8 | 209 | | Yes | 8 |
| utf8_hungarian_ci | utf8 | 210 | | Yes | 8 |
| utf8_sinhala_ci | utf8 | 211 | | Yes | 8 |
| utf8_general_mysql500_ci | utf8 | 223 | | Yes | 1 |
| utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 |
| utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 |
| utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 |
| utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 |
| utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 |
| utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 |
| utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 |
| utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 |
| utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 |
| utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 |
| utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 |
| utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 |
| utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 |
| utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 |
| utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 |
| utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 |
| utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 |
| utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 |
| utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 |
| utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 |
| utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 |
| utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 |
+--------------------------+---------+-----+---------+----------+---------+
校對規(guī)則命名：以相關(guān)的字符集名開始，通常為語言名，并且以_ci（大小寫不敏感），_cs(大小寫敏感)或_bin（二元，即基于字符編碼的值比較）結(jié)束。
字符集設(shè)置
字符集和校對字符集有4個級別的默認(rèn)設(shè)置：服務(wù)器級別，數(shù)據(jù)庫級別，標(biāo)級別，字段級別。
服務(wù)器級別：
1 可在my.cnf中設(shè)置
[mysqld]
character-set-server=gbk
2 啟動選項中
mysqld --character-set-server=gbk
3 編譯的時候
cmake . -DDEFAULT_CHARSET=gbk
默認(rèn)字符集：show variables like '%character_set_server%';
默認(rèn)校對字符集：show variables like '%collation_server%';
mysql> show variables like '%character_set_server%';
+----------------------+-------+
| Variable_name | Value |
+----------------------+-------+
| character_set_server | utf8 |
+----------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like '%collation_server%';
+------------------+-----------------+
| Variable_name | Value |
+------------------+-----------------+
| collation_server | utf8_general_ci |
+------------------+-----------------+
1 row in set (0.00 sec)
數(shù)據(jù)庫級別:
可以在創(chuàng)建數(shù)據(jù)庫的時候指定字符集或者通過ALTER命令修改。如果已經(jīng)存在數(shù)據(jù)，則只對新增數(shù)據(jù)生效。
- 如果指定了字符集和校對規(guī)則，則使用指定的設(shè)置。
- 如果指定了字符集沒有指定校對規(guī)則，則使用指定的字符集的默認(rèn)的校對規(guī)則。
- 如果指定了校對規(guī)則但沒有指定字符集，則字符集使用與該校對規(guī)則關(guān)聯(lián)的字符集。
- 如果沒有指定字符集和校對規(guī)則，則使用服務(wù)器級別的設(shè)置為默認(rèn)配置。
表級別:
和數(shù)據(jù)庫級別類似
創(chuàng)建的時候指定字符集
CREATE TABLE `users` (
`ename` varchar(30) DEFAULT NULL,
`hiredate` date DEFAULT NULL,
`sal` decimal(10,2) DEFAULT NULL,
`deptno` int(2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
字段級別
和數(shù)據(jù)庫級別類似，字段字符集修改的可能比較小。
character-set-server/default-character-set：服務(wù)器字符集，默認(rèn)情況下所采用的。
character-set-database：數(shù)據(jù)庫字符集。
character-set-table：數(shù)據(jù)庫表字符集。
連接字符集和校對規(guī)則
客服端和服務(wù)器段交采用的字符集
相關(guān)參數(shù)：
character_set_client：客戶端的字符集。
character_set_results：結(jié)果字符集。
character_set_connection：連接字符集。
方式1：
可以采用以下命令同時修改，但是每次的連接數(shù)據(jù)庫都要執(zhí)行改命令：
set names utf8;
方式2：
[mysql]
character-set-server=utf8
字符集修改
/*這些只針對新紀(jì)錄生效*/
alter database character set ***
alter table tablename character set ***
索引設(shè)計和使用
Myisam和InnoDB默認(rèn)為rtree索引。
大多數(shù)MySQL索引(PRIMARY KEY、UNIQUE、INDEX和FULLTEXT)在B樹中存儲。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引。
Mysql設(shè)計索引的原則
搜索的索引列，不一定是所要選擇的列。
換句話說，最適合索引的列是出現(xiàn)在WHERE 子句中的列，或連接子句中指定的列，而不是出現(xiàn)在SELECT 關(guān)鍵字后的選擇列表中的列。使用惟一索引。
考慮某列中值的分布。對于惟一值的列，索引的效果最好，而具有多個重復(fù)值的列，其索引效果最差。使用短索引。
如果對串列進(jìn)行索引，應(yīng)該指定一個前綴長度，只要有可能就應(yīng)該這樣做。例如，如果有一個CHAR(200) 列，如果在前10 個或20 個字符內(nèi)，多數(shù)值是惟一的，那么就不要對整個列進(jìn)行索引。對前10 個或20 個字符進(jìn)行索引能夠節(jié)省大量索引空間，也可能會使查詢更快。較小的索引涉及的磁盤I/O 較少，較短的值比較起來更快。更為重要的是，對于較短的鍵值，索引高速緩存中的塊能容納更多的鍵值，因此，MySQL也可以在內(nèi)存中容納更多的值。這增加 了找到行而不用讀取索引中較多塊的可能性。利用最左前綴。
在創(chuàng)建一個n 列的索引時，實際是創(chuàng)建了MySQL可利用的n個索引。多列索引可起幾個索引的作用，因為可利用索引中最左邊的列集來匹配行。這樣的列集稱為最左前綴。不要過度索引。
每個額外的索引都要占用額外的磁盤空間，并降低寫操作的性能。在修改表的內(nèi)容時，索引必須進(jìn)行更新，有時可能需要重構(gòu)，因此，索引越多，所花的時間越長。只保持所需的索引有利于查詢優(yōu)化。考慮在列上進(jìn)行的比較類型。
索引可用于“ <”、“ < = ”、“ = ”、“ > =”、“ > ”和BETWEEN 運算。在模式具有一個直接量前綴時，索引也用于LIKE 運算。如果只將某個列用于其他類型的運算時（如STRCMP( )），對其進(jìn)行索引沒有價值。
btree索引與hash索引
** Hash索引一些其它特征： **
1 它們只用于使用=或<=>操作符的等式比較(但很快)。
2 優(yōu)化器不能使用hash索引來加速ORDER BY操作。
3 MySQL不能確定在兩個值之間大約有多少行(這被范圍優(yōu)化器用來確定使用哪個索引)。如果你將一個MyISAM表改為hash-索引的MEMORY表，會影響一些查詢。
4 只能使用整個關(guān)鍵字來搜索一行。(用B-樹索引，任何關(guān)鍵字的最左面的前綴可用來找到行)。
** BTREE索引 **
當(dāng)使用=、<=>、IN、IS NULL或者IS NOT NULL操作符時，關(guān)鍵元素與常量值的比較關(guān)系對應(yīng)一個范圍條件。
下列范圍查詢適用于 btree索引和hash索引：
SELECT * FROM t1 WHERE key_col = 1 OR key_col IN (15,18,20);
下列范圍查詢適用于btree索引
SELECT * FROM t1 WHERE key_col > 1 AND key_col < 10;
SELECT * FROM t1 WHERE key_col LIKE 'ab%' OR key_col BETWEEN 'bar' AND 'foo';
視圖
視圖
什么是視圖
視圖是一張?zhí)摂M的表。視圖并不在數(shù)據(jù)庫存在，真正的數(shù)據(jù)來源自所查詢的表，而且是在查看的時候動態(tài)生成的。
視圖是一條SELECT語句執(zhí)行后返回的結(jié)果集。所以我們在創(chuàng)建視圖的時候，主要的工作就落在創(chuàng)建這條SQL查詢語句上。
視圖優(yōu)勢：
- 簡單：使用者不用關(guān)心視圖對應(yīng)的表結(jié)構(gòu)，關(guān)聯(lián)和篩選條件，用戶接受到的是已經(jīng)處理完成的結(jié)果集合。
- 安全：使用者只能夠訪問被允許的結(jié)果集，數(shù)據(jù)信息可以嚴(yán)格控制到列。同時grant語句可以針對視圖進(jìn)行授予權(quán)限。
- 數(shù)據(jù)獨立：一旦視圖結(jié)構(gòu)確定，可以屏蔽表結(jié)構(gòu)變化對使用者的影響。
創(chuàng)建視圖
視圖的限制：from關(guān)鍵字后面不能包含子查詢。
以下的類型的視圖是不可能新的：
- 包含以下關(guān)鍵字的SQL語句：聚合函數(shù)（sum，min，max，count等），distanct，group by,having,union或者union all
- 常量視圖
- select包含子查詢
- join
- from一個不能更新的視圖
- where字句的子查詢引用了from字句中的表
聚合函數(shù)：
create or replace view payment_sum as select country_id,sum(amount) from payment_2006 group by country_id;
常量視圖：
create or replace view pi as select 10000 as 1w;
子查詢：
create view city_view as select (select country_id from payment_2006 where country_id=1);
with [casaded|local] check option 是否允許更新數(shù)據(jù)使記錄不再滿足視圖的條件。
- local 滿足本視圖
- cascded 則必須滿足所有針對該視圖的條件才可以更新。
刪除視圖
drop view user_view2;
查看視圖
show tables；
show tables like "%view%";
查看詳情：
mysql> show table status like "user_view" \G
*************************** 1. row ***************************
Name: user_view
Engine: NULL
Version: NULL
Row_format: NULL
Rows: NULL
Avg_row_length: NULL
Data_length: NULL
Max_data_length: NULL
Index_length: NULL
Data_free: NULL
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: NULL
Checksum: NULL
Create_options: NULL
Comment: VIEW
1 row in set (0.00 sec)
查看創(chuàng)建語句：
mysql> show create view user_view;
或者：
mysql> select * from information_schema.views where table_name="user_view"\G
事務(wù)控制和鎖定
11 事務(wù)控制和鎖定語句
鎖。
MyISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；
BDB存儲引擎采用的是頁面鎖（page-level locking），但也支持表級鎖；
InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認(rèn)情況下是采用行級鎖。
加鎖： lock tables;
釋放鎖： unlock tables;
鎖標(biāo)記 分為 read 和 write 下面是 兩種 鎖的區(qū)別
//如 將 table1 設(shè)為read鎖, table2 設(shè)為write鎖
lock tables [table1] read,[table2] write;
事務(wù)
原子性: 確保工作單元內(nèi)的所有操作都成功完成，否則事務(wù)將被中止在故障點，和以前的操作將回滾到以前的狀態(tài)。
一致性: 確保數(shù)據(jù)庫正確地改變狀態(tài)后，成功提交的事務(wù)。
隔離性: 使事務(wù)操作彼此獨立的和透明的。
持久性: 確保提交的事務(wù)的結(jié)果或效果的系統(tǒng)出現(xiàn)故障的情況下仍然存在。
開啟事務(wù)
start transaction 或 begin
提交事務(wù)（關(guān)閉事務(wù)）
commit
放棄事務(wù)（關(guān)閉事務(wù)）
rollback
保存點：
savepoint p1
rollback to p1
發(fā)生在折返點 p1 之前的事務(wù)被提交，之后的被忽略
事務(wù)的終止
設(shè)置"自動提交"模式 set autocommit = 0
持續(xù)更新中...
