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ù)器時(shí)使用的密碼
-P 連接的端口號(hào)
創(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)值的為自動(dòng)設(shè)置為NULL
insert into emp (ename,hiredate,sal,deptno) values ('jack','2000-01-01','2000',1);
//可以不指定字段,但要一一對(duì)應(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";
刪除記錄:
//請(qǐng)仔細(xì)檢查where條件,慎重
delete from emp where ename='jack';
查看記錄:
//查看所有字段
select * from emp;
//查詢不重復(fù)記錄
select distinct(deptno) from emp ;
select distinct(deptno),emp.* from emp ;
//條件查詢
//比較運(yùn)算符: > < >= <= <> != ...
//邏輯運(yùn)算符: 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)計(jì)
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
對(duì)分組結(jié)果二次過濾
select count(deptno) as count,deptno from emp group by deptno having count > 2;
with rollup
對(duì)分組結(jié)果二次匯總
select count(sal),emp.* from emp group by sal, deptno with rollup ;
表連接:
- 內(nèi)連接:關(guān)聯(lián)表都要有匹配記錄
- 外連接:關(guān)聯(lián)表會(huì)選出對(duì)應(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é)果集的時(shí)候必須用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é)果是否存在時(shí)使用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';
后面會(huì)具體描述
Mysql數(shù)據(jù)類型
整數(shù)類型
指定寬度:
指定顯示的寬度為5,不影響實(shí)際數(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)識(shí)
值從1開始,逐行增加
一個(gè)表中最多只能存在一個(gè)自增列
自增列應(yīng)該定義為not null
自增列應(yīng)該這只為 primary key 或者 unique
id int not null auto_increment primary key
浮點(diǎn)數(shù)類型
定點(diǎn)數(shù)類型
定點(diǎn)數(shù)類型.png
float , double , decimal 特點(diǎn):
1.(m,d)表示方式:m指的是整數(shù)位,d指的是小數(shù)位(又稱作精度和標(biāo)度)
2.float/double四舍五入丟失精度,decimal會(huì)截?cái)鄶?shù)據(jù)并輸出warning
3.如果不指定精度,float/double采用操作系統(tǒng)默認(rèn),decimal則是(10,0)
位類型
位類型.png
1 存放位字段值
2 指定存放多位二進(jìn)制的長(zhǎng)度,默認(rèn)為1(范圍:1~64)
3 讀取需要bin()/hex(),普通的select讀取結(jié)果為null
4 插入的值會(huì)轉(zhuǎn)化為二進(jìn)制碼,如果長(zhǎng)度運(yùn)行則正常處理,否則插入失敗
create table t6 (id bit(1));
select bin(id) from t6;
日期和時(shí)間類型
當(dāng)前系統(tǒng)日期
timestamp:返回yyyy-mm-dd hh:mm:ss 寬度19
timestamp:不適合存放久遠(yuǎn)日期,超出范圍則會(huì)采用零值填充
//不同格式的顯示零值格式
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字段只能有一個(gè)"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和時(shí)區(qū)相關(guān):SYSTEM 指的是和主機(jī)時(shí)區(qū)保持一致
show variables like "%_zone";
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| system_time_zone | CST |
| time_zone | SYSTEM |
+------------------+--------+
//修改時(shí)區(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年某個(gè)點(diǎn))
- 表中第一個(gè)timestamp字段,會(huì)默認(rèn)采用當(dāng)前系統(tǒng)時(shí)間.如果更新其他字段,該字段沒有賦值的話,則該字段會(huì)自動(dòng)更新.如果指定字段不滿足規(guī)格,則采用零值填充
- timestamp查詢和插入都會(huì)受到當(dāng)?shù)貢r(shí)區(qū)影響
datetime支持范圍寬度大(1000-01-01 00:00:00到9999-12-31 23:23:59)
字符串類型
char和varchar:
- char定長(zhǎng)，效率高,在創(chuàng)建字段的時(shí)候就已經(jīng)指定,一般用于固定長(zhǎng)度的表單提交數(shù)據(jù)存儲(chǔ)
- char在檢索的時(shí)候回去掉尾部的空格
- varchar是動(dòng)態(tài)長(zhǎng)度
- varchar在檢索的時(shí)候回保留尾部的空格
- varchar將實(shí)際內(nèi)容單獨(dú)存儲(chǔ)在聚簇索引之外井联，內(nèi)容開頭用1到2個(gè)字節(jié)表示實(shí)際長(zhǎng)度(長(zhǎng)度>255時(shí)需要2個(gè)字節(jié))
- 字符類型若為gbk卜壕，每個(gè)字符最多占2個(gè)字節(jié)，字符類型若為utf8烙常，每個(gè)字符最多占3個(gè)字節(jié)轴捎。
枚舉類型:
- 枚舉在集合中取值,只能取一個(gè)
- 如果值不存在則采用第一個(gè)值為默認(rèn)
- 如果插入NULL不會(huì)出錯(cuò),會(huì)將NULL寫入表
- 成員個(gè)數(shù)在1255占用1個(gè)字節(jié),在25565535占用2個(gè)字節(jié),節(jié)約資源
create table `t8` (
`gender` enum('m','f') default null
) engine=innodb default charset=utf8
集合類型
集合類型和枚舉很像,但是支持多值選擇
最多可以保存64個(gè)成員,每8個(gè)成員占1個(gè)字節(jié)
重復(fù)值只會(huì)插入一次,如果查出集合范圍則插入為null
create table t9 (col set ('a','b','c','d'));
Mysql運(yùn)算符
算術(shù)運(yùn)算符
比較運(yùn)算符
- NULL值不能參與"=","!=","<==>","<>"等場(chǎng)景比較
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 這個(gè)在大量數(shù)據(jù)的情況下慎重選擇，會(huì)影響查詢性能蚕脏。
select id2 from t1 where id2 like "%03%";
- regexp 這個(gè)在大量數(shù)據(jù)的情況下慎重選擇侦副，會(huì)影響查詢性能。
select * from t1 where id2 regexp '0001';
邏輯運(yùn)算符
位運(yùn)算符
運(yùn)算符優(yōu)先級(jí)
很多情況下驼鞭，我們可直接采用“（）”進(jìn)行運(yùn)算符調(diào)整，邏輯清晰，閱讀性強(qiáng)记餐。
常用函數(shù)
字符串函數(shù)
數(shù)值函數(shù)
日期和時(shí)間函數(shù)
日期間隔類型：
流程函數(shù)
其他函數(shù)
MySQL管理工具和應(yīng)用程序
工欲善其事固惯，必先利其器。幾乎每個(gè)開發(fā)人員都有最鐘愛的 MySQL 管理工具洛心，它幫助開發(fā)人員在許多方面支持包括 PostgreSQL细燎，MySQL，SQLite皂甘，Redis玻驻，MongoDB 等在內(nèi)的多種數(shù)據(jù)庫；提供各種最新的特性偿枕，包括觸發(fā)器璧瞬、事件、視圖渐夸、存儲(chǔ)過程和外鍵嗤锉，支持導(dǎo)入、數(shù)據(jù)備份墓塌、對(duì)象結(jié)構(gòu)等多種功能瘟忱。
這篇文章收集了15款最佳的 MySQL 管理工具和應(yīng)用軟件，幫助你輕松快速完成工作苫幢。
Induction
Induction是一款用于理解數(shù)據(jù)關(guān)系的開源管理工具访诱，它可用來探索行/列，運(yùn)行查詢和數(shù)據(jù)可視化等方面韩肝。該工具支持多種數(shù)據(jù)庫触菜，包括PostgreSQL，MySQL哀峻，SQLite涡相，Redis以及MongoDB哲泊。此外，Induction還可以通過編寫添加其他新的適配器催蝗。
Pinba
Pinba 是一種MySQL存儲(chǔ)引擎切威，用于PHP實(shí)時(shí)監(jiān)控和數(shù)據(jù)服務(wù)器的MySQL只讀接口。它整理并處理通過UDP發(fā)送的數(shù)據(jù)丙号，并以可讀的簡(jiǎn)單報(bào)告的形式統(tǒng)計(jì)顯示多個(gè)PHP進(jìn)程先朦。為了獲取下一代更為復(fù)雜的報(bào)告和統(tǒng)計(jì)數(shù)據(jù)，Pinba提供了原始數(shù)據(jù)的只讀接口槽袄。
DB Ninja
DbNinja是一款先進(jìn)的基于Web的MySQL數(shù)據(jù)庫管理與開發(fā)應(yīng)用程序烙无。它是遠(yuǎn)程訪問托管服務(wù)器的必然之選。DbNinja支持所有最新的功能遍尺，包括觸發(fā)器截酷、事件、視圖乾戏、存儲(chǔ)過程和外鍵等迂苛。此外,它還可以導(dǎo)入和備份數(shù)據(jù)、MySQL對(duì)象結(jié)構(gòu)以及管理用戶等鼓择。DbNinj的用戶界面功能完備且清新美觀三幻，可安全地運(yùn)用于任何瀏覽器及任何操作系統(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目前擁有個(gè)人和企業(yè)兩個(gè)版本爷恳，用戶可按需選擇使用。
Dbeaver
DBeaver是一款免費(fèi)的數(shù)據(jù)庫管理應(yīng)用程序象踊，可運(yùn)用于多種不同的引擎温亲，包括MySQL，MSSQL杯矩，Oracle栈虚、SQLite、Sybase和Firebird等等菊碟。由Java編寫而成节芥，該應(yīng)用程序適用于所有主流操作系統(tǒng)(Windows、Mac和Linux)逆害。它能處理包括元數(shù)據(jù)編輯(表头镊、列、鍵魄幕、索引)相艇、自定義SQL執(zhí)行、用戶管理纯陨、多連接等在內(nèi)的所有主要任務(wù)坛芽。
SqlWave
SQLWave是一種簡(jiǎn)單、快速且易用的MySQL客戶端翼抠。用戶可通過該工具輕松地連接到遠(yuǎn)程主機(jī)咙轩。SqlWave支持所有MySQL的最新版本，包括它用來管理數(shù)據(jù)庫結(jié)構(gòu)的所有最新功能阴颖，如工作表活喊、視圖、存儲(chǔ)過程量愧、函數(shù)钾菊、事件、外鍵和觸發(fā)器等偎肃。
MyWebSQL
MyWebSQL主要用于管理基于Web的MySQL數(shù)據(jù)庫煞烫。與桌面應(yīng)用程序的借口工作流程相似，用戶無需切換網(wǎng)頁即可完成一些簡(jiǎn)單的操作累颂。如果您正在操作桌面滞详，只用登數(shù)據(jù)庫，就可以管理您的數(shù)據(jù)庫了紊馏。
MyWebSQL主要用于管理基于Web的MySQL數(shù)據(jù)庫料饥。與桌面應(yīng)用程序的借口工作流程相似，用戶無需切換網(wǎng)頁即可完成一些簡(jiǎn)單的操作瘦棋。如果您正在操作桌面稀火，只用登數(shù)據(jù)庫，就可以管理您的數(shù)據(jù)庫了赌朋。
Navicat
Navicat是MySQL和MariaDB數(shù)據(jù)庫管理與開發(fā)理想的解決方案凰狞。它可同時(shí)在一個(gè)應(yīng)用程序上連接MySQL和MariaDB數(shù)據(jù)庫。這種兼容前端為數(shù)據(jù)庫提供了一個(gè)直觀而強(qiáng)大的圖形界面管理沛慢、開發(fā)和維護(hù)功能赡若，為初級(jí)MySQL和MariaDB開發(fā)人員和專業(yè)開發(fā)人員都提供了一組全面的開發(fā)工具。
SQLyog
SQLyog是一款功能最強(qiáng)大的MySQL管理工具团甲，它綜合了MySQL工作臺(tái)逾冬、php MyAdmin和其他MySQL前端及MySQL GUI工具的特點(diǎn)。該款應(yīng)用程序可以同時(shí)連接任意數(shù)量級(jí)的MySQL服務(wù)器，用于測(cè)試和生產(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è)計(jì)的有用且可靠的工具牛隅，可用于當(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是一款免費(fèi)的MySQL數(shù)據(jù)庫管理器應(yīng)用程序碘耳。該工具強(qiáng)大到您幾乎可以獲取到任何想要的功能显设，并能夠連接到無限量級(jí)的數(shù)據(jù)庫。通過創(chuàng)建辛辨、編輯或刪除數(shù)據(jù)庫捕捂、表格和記錄，就可以備份/恢復(fù)并導(dǎo)出為多個(gè)格式斗搞。
SQL Lite Manger
SQL Lite Manager是一款基于web的開源應(yīng)用程序指攒，用于管理無服務(wù)器、零配置SQL Lite數(shù)據(jù)庫僻焚。該程序是用PHP寫成允悦，除了記錄和應(yīng)用表格格上的每一步操作，還可以控制多個(gè)數(shù)據(jù)庫虑啤。SQL Lite Manager可用于查詢數(shù)據(jù)隙弛，將MySQL查詢轉(zhuǎn)化為兼容SQL Lite數(shù)據(jù)庫，并能創(chuàng)建和編輯觸發(fā)器狞山。SQL Lite Manager有多種皮膚選項(xiàng)全闷，是一個(gè)含現(xiàn)成語言文件的多語言資源。
Database Master
Database Master是一個(gè)現(xiàn)代的萍启、強(qiáng)大的总珠、直觀且易用的數(shù)據(jù)庫管理程序屏鳍。它以一個(gè)一致而現(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簡(jiǎn)化了管理来庭、查詢妒蔚、編輯、可視化月弛、設(shè)計(jì)和報(bào)告數(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)用程式有一個(gè)內(nèi)置的編輯器，當(dāng)運(yùn)行復(fù)雜的查詢或易用的界面時(shí)谴垫，可用于快速瀏覽章母。
Mysql存儲(chǔ)引擎
Mysql默認(rèn)支持多種存儲(chǔ)引擎，來適應(yīng)不同的場(chǎng)景需求翩剪。
默認(rèn)存儲(chǔ)引擎：
在5.5以前的版本為MyISAM乳怎，在5.5之后為InnoDB
查看存儲(chǔ)支持的引擎：
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;
存儲(chǔ)引擎對(duì)比:
各種存儲(chǔ)引擎特性:
MyISAM
特點(diǎn):
1 不支持事務(wù),不支持外鍵
2 訪問速度快
存儲(chǔ)結(jié)構(gòu):
存在3個(gè)文件,文件名和表名相同,擴(kuò)展名分別為:
.frm 存儲(chǔ)表定義
.myd 存儲(chǔ)數(shù)據(jù)
.myi 存儲(chǔ)索引
檢測(cè)修復(fù):
check table 檢查表
repair table 修復(fù)表
表存儲(chǔ)格式:
靜態(tài)表,動(dòng)態(tài)表,壓縮表
靜態(tài)表:
字段都是非變長(zhǎng)字段,存儲(chǔ)快,容易緩存,易恢復(fù);占用空間多;丟失尾部空格
動(dòng)態(tài)表:
包含變長(zhǎng)字段,記錄不固定,節(jié)省空間;頻繁更新和刪除會(huì)產(chǎn)生碎片,需要進(jìn)行定期整理
optimize table 整理碎片
[擴(kuò)展1](http://blog.csdn.net/andybbc/article/details/50562355)
壓縮表:
由myisampack工具創(chuàng)建,占用空間小.
使用場(chǎng)景：
1.選擇密集型的表。MyISAM存儲(chǔ)引擎在篩選大量數(shù)據(jù)時(shí)非常迅速前弯，這是它最突出的優(yōu)點(diǎn)蚪缀。
2.插入密集型的表。MyISAM的并發(fā)插入特性允許同時(shí)選擇和插入數(shù)據(jù)博杖。例如：MyISAM存儲(chǔ)引擎很適合管理郵件或Web服務(wù)器日志數(shù)據(jù)椿胯。
InnoDB
特點(diǎn):
1.更新密集的表。InnoDB存儲(chǔ)引擎特別適合處理多重并發(fā)的更新請(qǐng)求剃根。
2.事務(wù)哩盲。InnoDB存儲(chǔ)引擎是支持事務(wù)的標(biāo)準(zhǔn)MySQL存儲(chǔ)引擎。
3.自動(dòng)災(zāi)難恢復(fù)。與其它存儲(chǔ)引擎不同廉油，InnoDB表能夠自動(dòng)從災(zāi)難中恢復(fù)惠险。
4.外鍵約束。MySQL支持外鍵的存儲(chǔ)引擎只有InnoDB抒线。
5.支持自動(dòng)增加列auto_increment屬性班巩，并且必須是索引。如果是組合索引嘶炭，也必須是第一列（myisam可以是其他列或者組合列）抱慌。
//設(shè)置自增列的初始值
alter table autoincre_demo auto_increment=10;
//查看當(dāng)前線程最后插入自增記錄值
select last_insert_id();
//在多表數(shù)據(jù)導(dǎo)入的時(shí)候，可以關(guān)閉外鍵約束提高速度眨猎。
set foreign_key_checks=0;//關(guān)閉
set foreign_key_checks=1;//開啟
存儲(chǔ)方式:
1 共享表空間：
- 表結(jié)構(gòu)保存在.frm文件中
- 數(shù)據(jù)保存在innodb_data_home_dir
- 索引保存在innodb_data_file_path抑进。
2 多表空間：
- 表結(jié)構(gòu)保存在.frm文件中。
- 數(shù)據(jù)和索引單獨(dú)保存在.ibd中睡陪。
- 分區(qū)表每個(gè)分區(qū)對(duì)應(yīng)單獨(dú)的.ibd文件寺渗，文件名為“表明+分區(qū)名”，可以在創(chuàng)建* 分區(qū)的時(shí)候指定數(shù)據(jù)文件位置兰迫。數(shù)據(jù)文件沒有大小限制信殊，不需要設(shè)置初始大小。
Memory
特點(diǎn)：
- Memory存儲(chǔ)引擎的處理速度比較快汁果。采用的邏輯存儲(chǔ)介質(zhì)是系統(tǒng)內(nèi)存涡拘。
- 當(dāng)mysqld守護(hù)進(jìn)程崩潰時(shí)，數(shù)據(jù)都會(huì)丟失须鼎。
- 它要求存儲(chǔ)在Memory數(shù)據(jù)表里的數(shù)據(jù)使用的是長(zhǎng)度不變的格式鲸伴，這意味著不能使用BLOB和TEXT這樣的長(zhǎng)度可變的數(shù)據(jù)類型，VARCHAR是一種長(zhǎng)度可變的類型晋控，但因?yàn)樗贛ySQL內(nèi)部當(dāng)做長(zhǎng)度固定不變的CHAR類型汞窗，所以可以使用。
索引：
Memory同時(shí)支持散列索引和B樹索引赡译。B樹索引的優(yōu)于散列索引的是仲吏，可以使用部分查詢和通配查詢，也可以使用<蝌焚、>和>=等操作符方便數(shù)據(jù)挖掘裹唆。散列索引進(jìn)行“相等比較”非常快只洒，但是對(duì)“范圍比較”的速度就慢多了许帐，因此散列索引值適合使用在=和<>的操作符中，不適合在<或>操作符中毕谴，也同樣不適合用在order by子句中成畦。
使用場(chǎng)景：
目標(biāo)數(shù)據(jù)較小距芬，而且被非常頻繁地訪問。在內(nèi)存中存放數(shù)據(jù)循帐，所以會(huì)造成內(nèi)存的使用框仔，可以通過參數(shù)max_heap_table_size控制Memory表的大小，設(shè)置此參數(shù)拄养，就可以限制Memory表的最大大小离斩。
如果數(shù)據(jù)是臨時(shí)的，而且要求必須立即可用瘪匿，那么就可以存放在內(nèi)存表中跛梗。
存儲(chǔ)在Memory表中的數(shù)據(jù)如果突然丟失，不會(huì)對(duì)應(yīng)用服務(wù)產(chǎn)生實(shí)質(zhì)的負(fù)面影響柿顶。
Merge
特點(diǎn)：
MERGE存儲(chǔ)引擎是一組MyISAM表的組合茄袖，這些MyISAM表結(jié)構(gòu)必須完全相同。
Merge表中并沒有數(shù)據(jù)嘁锯，對(duì)Merge類型的表可以進(jìn)行查詢、更新聂薪、刪除操作家乘，這些操作實(shí)際上是對(duì)內(nèi)部的MyISAM表進(jìn)行操作。
對(duì)于插入操作藏澳，是通過INSERT_METHOD字句定義插入的表仁锯。
對(duì)于表刪除，只是刪除該merge的定義翔悠，對(duì)內(nèi)部表無影響业崖。
merge表會(huì)產(chǎn)生兩個(gè)文件，文件以表名字開始蓄愁，.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ù)寫到某個(gè)數(shù)據(jù)表中妇斤，而分區(qū)表可以。
使用場(chǎng)景：
對(duì)于服務(wù)器日志這種信息丹拯，一般常用的存儲(chǔ)策略是將數(shù)據(jù)分成很多表站超，每個(gè)名稱與特定的時(shí)間端相關(guān)。
TokuDB
特點(diǎn)：
1 高性能乖酬，支持事務(wù)處理死相。
2 高擴(kuò)展，高壓縮比咬像，高效寫入算撮。
3 支持大多數(shù)在線DDL操作
使用場(chǎng)景：
1 日志數(shù)據(jù)双肤，插入頻繁，存儲(chǔ)量大
2 歷史數(shù)據(jù)钮惠，后期不會(huì)在有寫入操作
3 在線DDL頻繁的情況
ARCHIVE
特點(diǎn)：
1 僅支持最基本的插入和查詢兩種功能茅糜。
2 在MySQL 5.5版以前，Archive是不支持索引素挽，但是在MySQL 5.5以后的版本中就開始支持索引了蔑赘。
3 Archive擁有很好的壓縮機(jī)制，它使用zlib壓縮庫预明，在記錄被請(qǐng)求時(shí)會(huì)實(shí)時(shí)壓縮缩赛，所以它經(jīng)常被用來當(dāng)做倉庫使用。
總結(jié)
選擇合適的數(shù)據(jù)類型
char與varchar
char是固定長(zhǎng)度撰糠，varchar可變長(zhǎng)度酥馍。
char插入的時(shí)候去去掉尾部空格，varchar不會(huì)阅酪。
使用建議：
text和blob
text：保存字符數(shù)據(jù)
blob：保存二進(jìn)制數(shù)據(jù)旨袒。
BLOB和TEXT值在執(zhí)行了大量的刪除或更新操作的時(shí)候，會(huì)在數(shù)據(jù)表中留下很大的"空洞"术辐，建議定期使用 OPTIMIZE TABLE 功能對(duì)這類表進(jìn)行碎片整理.
使用合成的（synthetic）索引砚尽。一種辦法是根據(jù)其它的列的內(nèi)容建立一個(gè)散列值，并把這個(gè)值存儲(chǔ)在單獨(dú)的數(shù)據(jù)列中辉词。接下來你就可以通過檢索散列值找到數(shù)據(jù)行了必孤。但是，我們要注意這種技術(shù)只能用于精確匹配的查詢（散列值對(duì)于類似<或>=等范圍搜索操作符 是沒有用處的）瑞躺。我們可以使用MD5()函數(shù)生成散列值敷搪，也可以使用SHA1()或CRC32()，或者使用自己的應(yīng)用程序邏輯來計(jì)算散列值幢哨。(如果散列算法生成的字符串帶有尾部空格赡勘，就不要把它們存儲(chǔ)在CHAR或VARCHAR列中，它們會(huì)受到尾部空格去除的影響嘱么。) 用散列標(biāo)識(shí)符值查找的速度比搜索BLOB列本身的速度快很多狮含。
在不必要的時(shí)候避免檢索大型的BLOB或TEXT值。你可以搜索索引列曼振，決定那些需要的數(shù)據(jù)行几迄，然后從合格的數(shù)據(jù)行中檢索BLOB或 TEXT值。
把BLOB或TEXT列分離到單獨(dú)的表中冰评。這會(huì)減少主表中的碎片映胁，使你得到固定長(zhǎng)度數(shù)據(jù)行的性能優(yōu)勢(shì)。它還使你在主數(shù)據(jù)表上運(yùn)行 SELECT * 查詢的時(shí)候不會(huì)通過網(wǎng)絡(luò)傳輸大量的BLOB或TEXT值甲雅。
浮點(diǎn)數(shù)與定點(diǎn)數(shù)
浮點(diǎn)數(shù)：包含小數(shù)部分的數(shù)值解孙，如果插入數(shù)據(jù)超過該列的實(shí)際精度坑填，會(huì)被四舍五入插入。
定點(diǎn)數(shù)：是以字符串的形式存放的弛姜，可以更加精準(zhǔn)的確保存數(shù)據(jù)脐瑰。
提示：
- 浮點(diǎn)數(shù)存在誤差問題；
- 對(duì)貨幣等對(duì)精度敏感的數(shù)據(jù)廷臼，應(yīng)該用定點(diǎn)數(shù)表示或存儲(chǔ)苍在；
- 編程中，如果用到浮點(diǎn)數(shù)寂恬，要特別注意誤差問題，并盡量避免做浮點(diǎn)數(shù)比較初肉；
- 要注意浮點(diǎn)數(shù)中一些特殊值的處理。
日期類型
1 根據(jù)實(shí)際需要選擇滿足的最小存儲(chǔ)日期類型
2 如果記錄YYYY-mm-dd H:i:s類型宁舰，最好使用datetime
3 如果記錄的日期需要讓不同時(shí)區(qū)的用戶使用拼卵，最好采用timestamp
mysql 字符集
字符集比較
選擇合適的字符集
1 選擇合適的字符集，如果支持多種語言蛮艰，就應(yīng)該采用Unicode字符集(常見UTT-8)腋腮。
2 涉及數(shù)據(jù)導(dǎo)入，則應(yīng)該充分考慮字符集對(duì)于現(xiàn)有數(shù)的兼容性壤蚜。
3 如果只支持中文即寡，數(shù)據(jù)量大，性能要求高袜刷，則選用中文編碼聪富。這樣可以減少磁盤IO,數(shù)據(jù)庫Cache以及網(wǎng)絡(luò)傳輸時(shí)間
4 如果數(shù)據(jù)庫做大量字符運(yùn)算，例如比較著蟹，排序等墩蔓，選擇定長(zhǎng)的字符集比變長(zhǎng)的字符集處理速度快梢莽。
支持的字符集
查看所有字符集：
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）和校對(duì)字符集（collation）兩個(gè)概念。每種字符串至少有一個(gè)校對(duì)規(guī)則奸披。
字符集：存儲(chǔ)字符串的方式
校對(duì)字符集：定義比較字符串的方式
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 |
+--------------------------+---------+-----+---------+----------+---------+
校對(duì)規(guī)則命名：以相關(guān)的字符集名開始昏名，通常為語言名，并且以_ci（大小寫不敏感）阵面，_cs(大小寫敏感)或_bin（二元轻局，即基于字符編碼的值比較）結(jié)束。
字符集設(shè)置
字符集和校對(duì)字符集有4個(gè)級(jí)別的默認(rèn)設(shè)置：服務(wù)器級(jí)別膜钓，數(shù)據(jù)庫級(jí)別嗽交，標(biāo)級(jí)別，字段級(jí)別颂斜。
服務(wù)器級(jí)別：
1 可在my.cnf中設(shè)置
[mysqld]
character-set-server=gbk
2 啟動(dòng)選項(xiàng)中
mysqld --character-set-server=gbk
3 編譯的時(shí)候
cmake . -DDEFAULT_CHARSET=gbk
默認(rèn)字符集：show variables like '%character_set_server%';
默認(rèn)校對(duì)字符集：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ù)庫級(jí)別:
可以在創(chuàng)建數(shù)據(jù)庫的時(shí)候指定字符集或者通過ALTER命令修改夫壁。如果已經(jīng)存在數(shù)據(jù)，則只對(duì)新增數(shù)據(jù)生效沃疮。
- 如果指定了字符集和校對(duì)規(guī)則盒让，則使用指定的設(shè)置。
- 如果指定了字符集沒有指定校對(duì)規(guī)則司蔬，則使用指定的字符集的默認(rèn)的校對(duì)規(guī)則邑茄。
- 如果指定了校對(duì)規(guī)則但沒有指定字符集，則字符集使用與該校對(duì)規(guī)則關(guān)聯(lián)的字符集俊啼。
- 如果沒有指定字符集和校對(duì)規(guī)則肺缕，則使用服務(wù)器級(jí)別的設(shè)置為默認(rèn)配置。
表級(jí)別:
和數(shù)據(jù)庫級(jí)別類似
創(chuàng)建的時(shí)候指定字符集
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
字段級(jí)別
和數(shù)據(jù)庫級(jí)別類似授帕，字段字符集修改的可能比較小同木。
character-set-server/default-character-set：服務(wù)器字符集，默認(rèn)情況下所采用的跛十。
character-set-database：數(shù)據(jù)庫字符集彤路。
character-set-table：數(shù)據(jù)庫表字符集。
連接字符集和校對(duì)規(guī)則
客服端和服務(wù)器段交采用的字符集
相關(guān)參數(shù)：
character_set_client：客戶端的字符集芥映。
character_set_results：結(jié)果字符集洲尊。
character_set_connection：連接字符集。
方式1：
可以采用以下命令同時(shí)修改奈偏，但是每次的連接數(shù)據(jù)庫都要執(zhí)行改命令：
set names utf8;
方式2：
[mysql]
character-set-server=utf8
字符集修改
/*這些只針對(duì)新紀(jì)錄生效*/
alter database character set ***
alter table tablename character set ***
索引設(shè)計(jì)和使用
Myisam和InnoDB默認(rèn)為rtree索引坞嘀。
大多數(shù)MySQL索引(PRIMARY KEY、UNIQUE霎苗、INDEX和FULLTEXT)在B樹中存儲(chǔ)姆吭。只是空間列類型的索引使用R-樹，并且MEMORY表還支持hash索引唁盏。
Mysql設(shè)計(jì)索引的原則
搜索的索引列内狸，不一定是所要選擇的列检眯。
換句話說，最適合索引的列是出現(xiàn)在WHERE 子句中的列昆淡，或連接子句中指定的列锰瘸，而不是出現(xiàn)在SELECT 關(guān)鍵字后的選擇列表中的列。使用惟一索引昂灵。
考慮某列中值的分布避凝。對(duì)于惟一值的列，索引的效果最好眨补，而具有多個(gè)重復(fù)值的列管削，其索引效果最差遏暴。使用短索引虹茶。
如果對(duì)串列進(jìn)行索引荸型，應(yīng)該指定一個(gè)前綴長(zhǎng)度搂橙，只要有可能就應(yīng)該這樣做。例如乳绕，如果有一個(gè)CHAR(200) 列淀歇，如果在前10 個(gè)或20 個(gè)字符內(nèi)飒箭，多數(shù)值是惟一的线婚，那么就不要對(duì)整個(gè)列進(jìn)行索引遏弱。對(duì)前10 個(gè)或20 個(gè)字符進(jìn)行索引能夠節(jié)省大量索引空間，也可能會(huì)使查詢更快塞弊。較小的索引涉及的磁盤I/O 較少漱逸，較短的值比較起來更快。更為重要的是游沿，對(duì)于較短的鍵值虹脯，索引高速緩存中的塊能容納更多的鍵值，因此奏候，MySQL也可以在內(nèi)存中容納更多的值。這增加 了找到行而不用讀取索引中較多塊的可能性唇敞。利用最左前綴蔗草。
在創(chuàng)建一個(gè)n 列的索引時(shí)，實(shí)際是創(chuàng)建了MySQL可利用的n個(gè)索引疆柔。多列索引可起幾個(gè)索引的作用咒精，因?yàn)榭衫盟饕凶钭筮叺牧屑瘉砥ヅ湫小＿@樣的列集稱為最左前綴旷档。不要過度索引模叙。
每個(gè)額外的索引都要占用額外的磁盤空間，并降低寫操作的性能鞋屈。在修改表的內(nèi)容時(shí)范咨，索引必須進(jìn)行更新故觅，有時(shí)可能需要重構(gòu)，因此渠啊，索引越多输吏，所花的時(shí)間越長(zhǎng)。只保持所需的索引有利于查詢優(yōu)化替蛉。考慮在列上進(jìn)行的比較類型贯溅。
索引可用于“ <”、“ < = ”躲查、“ = ”它浅、“ > =”、“ > ”和BETWEEN 運(yùn)算镣煮。在模式具有一個(gè)直接量前綴時(shí)姐霍，索引也用于LIKE 運(yùn)算。如果只將某個(gè)列用于其他類型的運(yùn)算時(shí)（如STRCMP( )）怎静，對(duì)其進(jìn)行索引沒有價(jià)值邮弹。
btree索引與hash索引
** Hash索引一些其它特征： **
1 它們只用于使用=或<=>操作符的等式比較(但很快)。
2 優(yōu)化器不能使用hash索引來加速ORDER BY操作蚓聘。
3 MySQL不能確定在兩個(gè)值之間大約有多少行(這被范圍優(yōu)化器用來確定使用哪個(gè)索引)腌乡。如果你將一個(gè)MyISAM表改為hash-索引的MEMORY表，會(huì)影響一些查詢夜牡。
4 只能使用整個(gè)關(guān)鍵字來搜索一行与纽。(用B-樹索引，任何關(guān)鍵字的最左面的前綴可用來找到行)塘装。
** BTREE索引 **
當(dāng)使用=急迂、<=>、IN蹦肴、IS NULL或者IS NOT NULL操作符時(shí)僚碎，關(guān)鍵元素與常量值的比較關(guān)系對(duì)應(yīng)一個(gè)范圍條件。
下列范圍查詢適用于 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ù)來源自所查詢的表，而且是在查看的時(shí)候動(dòng)態(tài)生成的矛双。
視圖是一條SELECT語句執(zhí)行后返回的結(jié)果集渊抽。所以我們?cè)趧?chuàng)建視圖的時(shí)候，主要的工作就落在創(chuàng)建這條SQL查詢語句上议忽。
視圖優(yōu)勢(shì)：
- 簡(jiǎn)單：使用者不用關(guān)心視圖對(duì)應(yīng)的表結(jié)構(gòu)懒闷，關(guān)聯(lián)和篩選條件，用戶接受到的是已經(jīng)處理完成的結(jié)果集合。
- 安全：使用者只能夠訪問被允許的結(jié)果集愤估，數(shù)據(jù)信息可以嚴(yán)格控制到列帮辟。同時(shí)grant語句可以針對(duì)視圖進(jìn)行授予權(quán)限。
- 數(shù)據(jù)獨(dú)立：一旦視圖結(jié)構(gòu)確定灵疮，可以屏蔽表結(jié)構(gòu)變化對(duì)使用者的影響织阅。
創(chuàng)建視圖
視圖的限制：from關(guān)鍵字后面不能包含子查詢。
以下的類型的視圖是不可能新的：
- 包含以下關(guān)鍵字的SQL語句：聚合函數(shù)（sum震捣，min荔棉，max，count等）蒿赢，distanct润樱，group by,having,union或者union all
- 常量視圖
- select包含子查詢
- join
- from一個(gè)不能更新的視圖
- 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 則必須滿足所有針對(duì)該視圖的條件才可以更新羡棵。
刪除視圖
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存儲(chǔ)引擎采用的是表級(jí)鎖（table-level locking）皂冰；
BDB存儲(chǔ)引擎采用的是頁面鎖（page-level locking）店展，但也支持表級(jí)鎖；
InnoDB存儲(chǔ)引擎既支持行級(jí)鎖（row-level locking）秃流，也支持表級(jí)鎖赂蕴，但默認(rèn)情況下是采用行級(jí)鎖。
加鎖： 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ù)將被中止在故障點(diǎn)概说，和以前的操作將回滾到以前的狀態(tài)。
一致性: 確保數(shù)據(jù)庫正確地改變狀態(tài)后嚣伐，成功提交的事務(wù)糖赔。
隔離性: 使事務(wù)操作彼此獨(dú)立的和透明的。
持久性: 確保提交的事務(wù)的結(jié)果或效果的系統(tǒng)出現(xiàn)故障的情況下仍然存在轩端。
開啟事務(wù)
start transaction 或 begin
提交事務(wù)（關(guān)閉事務(wù)）
commit
放棄事務(wù)（關(guān)閉事務(wù)）
rollback
保存點(diǎn)：
savepoint p1
rollback to p1
發(fā)生在折返點(diǎn) p1 之前的事務(wù)被提交放典，之后的被忽略
事務(wù)的終止
設(shè)置"自動(dòng)提交"模式 set autocommit = 0
持續(xù)更新中...
