Mysql

mysql 基礎(chǔ)語(yǔ)法

// 數(shù)據(jù)庫(kù)

//創(chuàng)建
CREATE DATABASE IF NOT EXISTS 【指定待創(chuàng)建數(shù)據(jù)庫(kù)名稱】 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
//刪除
drop database 【要?jiǎng)h除的數(shù)據(jù)庫(kù)名稱】

表

  CREATE TABLE 表名(
        字段名 類型（長(zhǎng)度）是否可以為null comment '字段說(shuō)明',
        主鍵
);
//定義student表信息并主鍵自增！
CREATE TABLE student(
    id BIGINT(30) auto_increment not null comment  '編號(hào)', 
    name VARCHAR(20) NOT NULL DEFAULT '' comment  '學(xué)生名稱',
    birthday VARCHAR(20) NOT NULL DEFAULT '' comment  '生日',
    sex VARCHAR(10) NOT NULL DEFAULT '' comment  '性別',
    PRIMARY KEY(id)
);
刪除
drop table 表名

insert

//前面為字段名，后面為這個(gè)字段要插入的值
INSERT INTO student (name, birthday, sex) VALUES ('趙三', 1990-02-02, '男');

select

//select * from 表名 *代表顯示表中所有字段，具體應(yīng)用中應(yīng)顯示所需字段
select * from student

update

//UPDATE 【表名】set 【update列】='修改后的值'  where 條件
//修改name為趙三的學(xué)生改為王五
update student set name='王五' WHERE NAME = '趙三';

delete

//DELETE FROM 【表名稱】 WHERE 【列名稱】 = 值
DELETE FROM student WHERE NAME ='王五'
//update + delete 要注意where條件别洪，如不加 update全表系冗，delete全表

連接查詢

準(zhǔn)備數(shù)據(jù)
CREATE TABLE course(
    c_id  BIGINT(30) auto_increment not null ,
    c_name VARCHAR(20) NOT NULL DEFAULT '',
    t_id BIGINT(30) NOT NULL,
    PRIMARY KEY(c_id)
);

CREATE TABLE teacher(
    t_id BIGINT(30) auto_increment not null,
    t_name VARCHAR(20) NOT NULL DEFAULT '',
    PRIMARY KEY(t_id)
);

CREATE TABLE score(
    id BIGINT(30) auto_increment not null,
    c_id  BIGINT(30),
    s_score INT(3), 
    s_id  BIGINT(30),
    PRIMARY KEY(id)
);

//準(zhǔn)備學(xué)生數(shù)據(jù)
DELETE FROM student
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('1', '趙雷', '1990-01-01', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('2', '錢電', '1990-12-21', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('3', '孫風(fēng)', '1990-05-20', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('4', '李云', '1990-08-06', '男');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('5', '周梅', '1991-12-01', '女');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('6', '吳蘭', '1992-03-01', '女');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('7', '鄭竹', '1989-07-01', '女');
INSERT INTO `student` (`id`, `name`, `birthday`, `sex`) VALUES ('8', '王菊', '1990-01-20', '女');

//準(zhǔn)備老師數(shù)據(jù)
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('1', '張三');
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('2', '李四');
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('3', '王五');
INSERT INTO `teacher` (`t_id`, `t_name`) VALUES ('4', '見(jiàn)習(xí)老師');



//準(zhǔn)備課程數(shù)據(jù)
INSERT INTO `course` (`c_id`, `c_name`, `t_id`) VALUES ('1', '語(yǔ)文', '2');
INSERT INTO `course` (`c_id`, `c_name`, `t_id`) VALUES ('2', '數(shù)學(xué)', '1');
INSERT INTO `course` (`c_id`, `c_name`, `t_id`) VALUES ('3', '英語(yǔ)', '3');

//準(zhǔn)備測(cè)試分?jǐn)?shù)數(shù)據(jù)
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('1', '1', '1', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('2', '1', '2', '90');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('3', '1', '3', '99');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('4', '2', '1', '70');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('5', '2', '2', '60');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('6', '2', '3', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('7', '3', '1', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('8', '3', '2', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('9', '3', '3', '80');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('10', '4', '1', '50');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('11', '4', '2', '30');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('12', '4', '3', '20');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('13', '5', '1', '76');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('14', '5', '2', '87');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('15', '6', '1', '31');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('16', '6', '3', '34');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('17', '7', '2', '89');
INSERT INTO `score` (`id`, `c_id`, `s_score`, `s_id`) VALUES ('18', '7', '3', '98');

內(nèi)連接：

inner join（顯性鏈接）只連接匹配的行

image.png

進(jìn)行連接的兩個(gè)表對(duì)應(yīng)的相配字段完全相同連接久信。

//如我們要查詢記錄在課程表里每一個(gè)課程的授課老師府框，我們使用內(nèi)連接將課程中t_id 和 teacher中t_id進(jìn)行連接
SELECT * FROM teacher  t inner join course c on t.t_id = c.t_id
//等同于
SELECT * FROM teacher t join course c on t.t_id = c.t_id 
 //where，join锁蠕，隱性鏈接
SELECT * from teacher t,course c WHERE t.t_id = c.t_id
 //不過(guò)此語(yǔ)句實(shí)際上是創(chuàng)建了兩張表的笛卡爾積，所有可能的組合都會(huì)被創(chuàng)建出來(lái)懊蒸。
//在笛卡爾連接中荣倾，在上面的例子中，如果有1000顧客和1000條銷售記錄骑丸，這個(gè)查詢會(huì)先產(chǎn)生1000000個(gè)結(jié)果舌仍，然后通過(guò)正確的 ID過(guò)濾出1000條記錄妒貌。
// 這是一種低效利用數(shù)據(jù)庫(kù)資源，數(shù)據(jù)庫(kù)多做100倍的工作铸豁。 在大型數(shù)據(jù)庫(kù)中灌曙，笛卡爾連接是一個(gè)大問(wèn)題，對(duì)兩個(gè)大表的笛卡爾積會(huì)創(chuàng)建數(shù)10億或萬(wàn)億的記錄节芥。

外連接：

左外聯(lián)接返回包括左表中的所有記錄和右表中鏈接字段相等的結(jié)果記錄在刺！

image.png

//查詢老師們的授課信息，以左表 teach 為主头镊，
SELECT * from teacher t LEFT JOIN course c ON c.t_id = t.t_id
//等同于LEFT OUTER JOIN
SELECT * FROM teacher t LEFT OUTER JOIN course c ON c.t_id = t.t_id

左表獨(dú)占蚣驼，不與B共享（不連接B表內(nèi)容）

image.png

//如需求是查詢沒(méi)有授課的老師信息
SELECT * from teacher  t 
WHERE NOT EXISTS (SELECT * from course  c WHERE c.t_id = t.t_id ) 
//等同于
SELECT * from teacher t
WHERE t.t_id NOT in (SELECT t_id from course c WHERE c.t_id = t.t_id)
//推薦，（在子查詢中無(wú)需SELECT時(shí)考慮）
SELECT * from teacher t LEFT OUTER JOIN course c ON c.t_id = t.t_id WHERE c.t_id is NULL

右外鏈接拧晕，返回包括右表中的所有記錄和左表中鏈接字段相等的記錄隙姿，于LEFT JOIN相反，以右邊表為主

image.png

//查詢每老師們授課信息厂捞，以RIGHT JOIN實(shí)現(xiàn)
//右表帶出所有記錄输玷，左邊匹配的記錄為鏈接字段結(jié)果集記錄，不足以行null替補(bǔ)顯示靡馁！
SELECT * from course c RIGHT JOIN teacher t ON c.t_id = t.t_id
//等同于
SELECT * from course c RIGHT OUTER JOIN teacher t ON c.t_id = t.t_id

右表獨(dú)占欲鹏，不與A共享（不連接A表內(nèi)容）

image.png

//如之前的需求是查詢沒(méi)有授課的老師信息，現(xiàn)在以右表獨(dú)占實(shí)現(xiàn)
SELECT * from course C RIGHT OUTER JOIN teacher t ON t.t_id = c.t_id WHERE c.t_id is NULL

全外連接：兩張表FULL JOIN 全連接

image.png

mysql不支持外連接臭墨，解決辦法是使用union組合查詢赔嚎，把左連接和右連接的結(jié)果進(jìn)行合并

SELECT * from teacher a LEFT JOIN course c ON a.t_id = c.t_id union SELECT * from course c RIGHT JOIN teacher t ON t.t_id =c.t_id

兩張表沒(méi)有共有部分，左右獨(dú)立胧弛。

image.png

//需求：查詢teacher中不存在于course中結(jié)果+查詢course中不存在于teacher的結(jié)果
SELECT * from teacher a LEFT JOIN course b ON a.t_id = b.t_id WHERE b.t_id IS NULL 
union 
SELECT * from course a RIGHT JOIN teacher b ON a.t_id =b.t_id WHERE a.t_id is NULL

數(shù)據(jù)庫(kù)常用函數(shù)

一尤误、數(shù)學(xué)函數(shù)

abs(x) 返回x的絕對(duì)值
bin(x) 返回x的二進(jìn)制（oct返回八進(jìn)制，hex返回十六進(jìn)制）
ceiling(x) 返回大于x的最小整數(shù)值
exp(x) 返回值e（自然對(duì)數(shù)的底）的x次方
floor(x) 返回小于x的最大整數(shù)值
greatest(x1,x2,...,xn)返回集合中最大的值
least(x1,x2,...,xn) 返回集合中最小的值
ln(x) 返回x的自然對(duì)數(shù)
log(x,y)返回x的以y為底的對(duì)數(shù)
mod(x,y) 返回x/y的模（余數(shù)）
pi()返回pi的值（圓周率）
rand()返回０到１內(nèi)的隨機(jī)值,可以通過(guò)提供一個(gè)參數(shù)(種子)使rand()隨機(jī)數(shù)生成器生成一個(gè)指定的值结缚。
round(x,y)返回參數(shù)x的四舍五入的有y位小數(shù)的值
sign(x) 返回代表數(shù)字x的符號(hào)的值
sqrt(x) 返回一個(gè)數(shù)的平方根
truncate(x,y) 返回?cái)?shù)字x截短為y位小數(shù)的結(jié)果

二损晤、聚合函數(shù)(常用于group by從句的select查詢中)

avg(col)返回指定列的平均值
count(col)返回指定列中非null值的個(gè)數(shù)
min(col)返回指定列的最小值
max(col)返回指定列的最大值
sum(col)返回指定列的所有值之和
group_concat(col) 返回由屬于一組的列值連接組合而成的結(jié)果

三、字符串函數(shù)

ascii(char)返回字符的ascii碼值
bit_length(str)返回字符串的比特長(zhǎng)度
concat(s1,s2...,sn)將s1,s2...,sn連接成字符串
concat_ws(sep,s1,s2...,sn)將s1,s2...,sn連接成字符串红竭，并用sep字符間隔
insert(str,x,y,instr) 將字符串str從第x位置開(kāi)始尤勋，y個(gè)字符長(zhǎng)的子串替換為字符串instr，返回結(jié)果find_in_set(str,list)分析逗號(hào)分隔的list列表茵宪，如果發(fā)現(xiàn)str最冰，返回str在list中的位置
lcase(str)或lower(str) 返回將字符串str中所有字符改變?yōu)樾?xiě)后的結(jié)果
left(str,x)返回字符串str中最左邊的x個(gè)字符
length(s)返回字符串str中的字符數(shù)
ltrim(str) 從字符串str中切掉開(kāi)頭的空格
position(substr in str) 返回子串substr在字符串str中第一次出現(xiàn)的位置
quote(str) 用反斜杠轉(zhuǎn)義str中的單引號(hào)
repeat(str,srchstr,rplcstr)返回字符串str重復(fù)x次的結(jié)果
reverse(str) 返回顛倒字符串str的結(jié)果
right(str,x) 返回字符串str中最右邊的x個(gè)字符
rtrim(str) 返回字符串str尾部的空格
strcmp(s1,s2)比較字符串s1和s2
trim(str)去除字符串首部和尾部的所有空格
ucase(str)或upper(str) 返回將字符串str中所有字符轉(zhuǎn)變?yōu)榇髮?xiě)后的結(jié)果

四、日期和時(shí)間函數(shù)

curdate()或current_date() 返回當(dāng)前的日期
curtime()或current_time() 返回當(dāng)前的時(shí)間
date_add(date,interval int keyword)返回日期date加上間隔時(shí)間int的結(jié)果(int必須按照關(guān)鍵字進(jìn)行格式化),如：selectdate_add(current_date,interval 6 month);
date_format(date,fmt) 依照指定的fmt格式格式化日期date值
date_sub(date,interval int keyword)返回日期date加上間隔時(shí)間int的結(jié)果(int必須按照關(guān)鍵字進(jìn)行格式化),如：selectdate_sub(current_date,interval 6 month);
dayofweek(date) 返回date所代表的一星期中的第幾天(1~7)
dayofmonth(date) 返回date是一個(gè)月的第幾天(1~31)
dayofyear(date) 返回date是一年的第幾天(1~366)
dayname(date) 返回date的星期名稀火，如：select dayname(current_date);
from_unixtime(ts,fmt) 根據(jù)指定的fmt格式暖哨，格式化unix時(shí)間戳ts
hour(time) 返回time的小時(shí)值(0~23)
minute(time) 返回time的分鐘值(0~59)
month(date) 返回date的月份值(1~12)
monthname(date) 返回date的月份名，如：select monthname(current_date);
now() 返回當(dāng)前的日期和時(shí)間
quarter(date) 返回date在一年中的季度(1~4)凰狞，如select quarter(current_date);
week(date) 返回日期date為一年中第幾周(0~53)
year(date) 返回日期date的年份(1000~9999)
一些示例：
獲取當(dāng)前系統(tǒng)時(shí)間：select from_unixtime(unix_timestamp());
select extract(year_month from current_date);
select extract(day_second from current_date);
select extract(hour_minute from current_date);
返回兩個(gè)日期值之間的差值(月數(shù))：select period_diff(200302,199802);
在mysql中計(jì)算年齡：
select date_format(from_days(to_days(now())-to_days(birthday)),'%y')+0 as age from employee;
這樣鹿蜀，如果brithday是未來(lái)的年月日的話箕慧，計(jì)算結(jié)果為0。
下面的sql語(yǔ)句計(jì)算員工的絕對(duì)年齡茴恰，即當(dāng)birthday是未來(lái)的日期時(shí)颠焦，將得到負(fù)值。
select date_format(now(), '%y') - date_format(birthday, '%y') -(date_format(now(), '00-%m-%d') <date_format(birthday, '00-%m-%d')) as age from employee

五往枣、加密函數(shù)

aes_encrypt(str,key) 返回用密鑰key對(duì)字符串str利用高級(jí)加密標(biāo)準(zhǔn)算法加密后的結(jié)果伐庭，調(diào)用aes_encrypt的結(jié)果是一個(gè)二進(jìn)制字符串，以blob類型存儲(chǔ)
aes_decrypt(str,key) 返回用密鑰key對(duì)字符串str利用高級(jí)加密標(biāo)準(zhǔn)算法解密后的結(jié)果
decode(str,key) 使用key作為密鑰解密加密字符串str
encrypt(str,salt) 使用unixcrypt()函數(shù)分冈，用關(guān)鍵詞salt(一個(gè)可以惟一確定口令的字符串圾另，就像鑰匙一樣)加密字符串str
encode(str,key) 使用key作為密鑰加密字符串str，調(diào)用encode()的結(jié)果是一個(gè)二進(jìn)制字符串雕沉，它以blob類型存儲(chǔ)
md5() 計(jì)算字符串str的md5校驗(yàn)和
password(str) 返回字符串str的加密版本集乔，這個(gè)加密過(guò)程是不可逆轉(zhuǎn)的，和unix密碼加密過(guò)程使用不同的算法坡椒。
sha() 計(jì)算字符串str的安全散列算法(sha)校驗(yàn)和
示例：
select encrypt('root','salt');
select encode('xufeng','key');
select decode(encode('xufeng','key'),'key');#加解密放在一起
select aes_encrypt('root','key');
select aes_decrypt(aes_encrypt('root','key'),'key');
select md5('123456');
select sha('123456');

六扰路、控制流函數(shù)

mysql有4個(gè)函數(shù)是用來(lái)進(jìn)行條件操作的，這些函數(shù)可以實(shí)現(xiàn)sql的條件邏輯倔叼，允許開(kāi)發(fā)者將一些應(yīng)用程序業(yè)務(wù)邏輯轉(zhuǎn)換到數(shù)據(jù)庫(kù)后臺(tái)汗唱。
mysql控制流函數(shù)：
CASE WHEN

case when[test1] then [result]...else [default] end如果test1是真，則返回result丈攒，否則返回default哩罪。

case [test] when[val1] then [result]...else [default]end 如果test和valn相等，則返回result巡验，否則返回default际插。

IFNULL(arg1,arg2) arg1不是空返回arg1否則返回arg2

//準(zhǔn)備數(shù)據(jù)
ALTER TABLE `course` 
MODIFY COLUMN `c_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT '' AFTER `c_id`; 
INSERT INTO `course`(`c_id`, `c_name`, `t_id`) VALUES (4, NULL, 4);
//如
SELECT IFNULL(c_name,'null'),c_name from course

NULLIF(var1，var2)函數(shù)显设，如果var1==var2返回null框弛；否則返回var1！

//如 
SELECT NULLIF('1','1') from teacher

IF(expression敷硅，var1，var2) 如果expression是真愉阎，返回var1绞蹦；否則返回var2。

// if(test,t,f) 
SELECT t_name,IF(t_name='見(jiàn)習(xí)老師','見(jiàn)習(xí)老師代號(hào)為1','已轉(zhuǎn)正老師代號(hào)為2')as code_name from teacher

Mysql 引擎

簡(jiǎn)介
數(shù)據(jù)庫(kù)中的存儲(chǔ)引擎其實(shí)是對(duì)使用了該引擎的表進(jìn)行某種設(shè)置榜旦，數(shù)據(jù)庫(kù)中的表設(shè)定了什么存儲(chǔ)引擎幽七，那么該表在數(shù)據(jù)存儲(chǔ)方式、數(shù)據(jù)更新方式溅呢、數(shù)據(jù)查詢性能以及是否支持索引等方面就會(huì)有不同的“效果澡屡。

2猿挚，mysql的各種引擎：

mySQL主要有以下幾種引擎：ISAM、MyISAM驶鹉、InnoDB绩蜻、HEAP（也稱為MEMORY）、CSV室埋、BLACKHOLE办绝、ARCHIVE、PERFORMANCE_SCHEMA姚淆、 Berkeley孕蝉、Merge、Federated和Cluster/NDB等腌逢，除此以外我們也可以參照MySQL++ API創(chuàng)建自己的數(shù)據(jù)庫(kù)引擎降淮。

ISAM

該引擎在讀取數(shù)據(jù)方面速度很快，而且不占用大量的內(nèi)存和存儲(chǔ)資源搏讶；但是ISAM不支持事務(wù)處理佳鳖、不支持外來(lái)鍵、不能夠容錯(cuò)窍蓝、也不支持索引腋颠。該引擎在包括MySQL 5.1及其以上版本的數(shù)據(jù)庫(kù)中不再支持。

MyISAM

它是MySql的默認(rèn)引擎吓笙，5.5后改為InnoDB淑玫，由早期的ISAM（Indexed Sequential Access Method：有索引的順序訪問(wèn)方法）所改良。雖然性能極佳面睛，但卻不提供事務(wù)的支持絮蒿，也不支持行級(jí)鎖和外鍵。除了提供ISAM里所沒(méi)有的索引和字段管理等大量功能叁鉴，MyISAM還使用一種表格鎖定的機(jī)制來(lái)優(yōu)化多個(gè)并發(fā)的讀寫(xiě)操作土涝，但是需要經(jīng)常運(yùn)行OPTIMIZE TABLE命令，來(lái)恢復(fù)被更新機(jī)制所浪費(fèi)的空間幌墓，否則碎片也會(huì)隨之增加但壮，最終影響數(shù)據(jù)訪問(wèn)性能。MyISAM還有一些有用的擴(kuò)展常侣，例如用來(lái)修復(fù)數(shù)據(jù)庫(kù)文件的MyISAMChk工具和用來(lái)恢復(fù)浪費(fèi)空間的 MyISAMPack工具蜡饵。

機(jī)制

MyISAM強(qiáng)調(diào)了快速讀取操作，主要用于高負(fù)載的select胳施，這可能也是MySQL深受Web開(kāi)發(fā)的主要原因：在Web開(kāi)發(fā)中進(jìn)行的大量數(shù)據(jù)操作都是讀取操作溯祸，所以大多數(shù)虛擬主機(jī)提供商和Internet平臺(tái)提供商（Internet Presence Provider，IPP）只允許使用MyISAM格式。MyIASM引擎是保存了表的行數(shù)焦辅，于是當(dāng)進(jìn)行Select count(*) from table語(yǔ)句時(shí)博杖，可以直接的讀取已經(jīng)保存的值而不需要進(jìn)行掃描全表。所以筷登，如果表的讀操作遠(yuǎn)遠(yuǎn)多于寫(xiě)操作時(shí)剃根，并且不需要事務(wù)的支持的∑偷郑可以將MyIASM作為數(shù)據(jù)庫(kù)引擎的首先跟继。

存儲(chǔ)結(jié)構(gòu)

MyISAM類型的表支持三種不同的存儲(chǔ)結(jié)構(gòu)：靜態(tài)型、動(dòng)態(tài)型镣丑、壓縮型舔糖。
靜態(tài)型：指定義的表列的大小是固定（即不含有：xblob、xtext莺匠、varchar等長(zhǎng)度可變的數(shù)據(jù)類型）金吗，這樣MySQL就會(huì)自動(dòng)使用靜態(tài)MyISAM格式。使用靜態(tài)格式的表的性能比較高趣竣，因?yàn)樵诰S護(hù)和訪問(wèn)以預(yù)定格式存儲(chǔ)數(shù)據(jù)時(shí)需要的開(kāi)銷很低摇庙；但這種高性能是以空間為代價(jià)換來(lái)的，因?yàn)樵诙x的時(shí)候是固定的遥缕，所以不管列中的值有多大卫袒，都會(huì)以最大值為準(zhǔn)，占據(jù)了整個(gè)空間单匣。
動(dòng)態(tài)型：如果列（即使只有一列）定義為動(dòng)態(tài)的（xblob, xtext, varchar等數(shù)據(jù)類型）夕凝，這時(shí)MyISAM就自動(dòng)使用動(dòng)態(tài)型，雖然動(dòng)態(tài)型的表占用了比靜態(tài)型表較少的空間户秤，但帶來(lái)了性能的降低码秉，因?yàn)槿绻硞€(gè)字段的內(nèi)容發(fā)生改變則其位置很可能需要移動(dòng)，這樣就會(huì)導(dǎo)致碎片的產(chǎn)生鸡号，隨著數(shù)據(jù)變化的增多转砖，碎片也隨之增加，數(shù)據(jù)訪問(wèn)性能會(huì)隨之降低鲸伴。對(duì)于因碎片增加而降低數(shù)據(jù)訪問(wèn)性這個(gè)問(wèn)題府蔗，有兩種解決辦法：
a、盡可能使用靜態(tài)數(shù)據(jù)類型汞窗；
b姓赤、經(jīng)常使用optimize table table_name語(yǔ)句整理表的碎片，恢復(fù)由于表數(shù)據(jù)的更新和刪除導(dǎo)致的空間丟失杉辙。如果存儲(chǔ)引擎不支持 optimize table table_name則可以轉(zhuǎn)儲(chǔ)并重新加載數(shù)據(jù)模捂，這樣也可以減少碎片；
壓縮型：如果在數(shù)據(jù)庫(kù)中創(chuàng)建在整個(gè)生命周期內(nèi)只讀的表蜘矢，則應(yīng)該使用MyISAM的壓縮型表來(lái)減少空間的占用狂男。

InnoDB

該存儲(chǔ)引擎為MySQL表提供了ACID事務(wù)支持、系統(tǒng)崩潰修復(fù)能力和多版本并發(fā)控制（即MVCC Multi-Version Concurrency Control）的行級(jí)鎖;該引擎支持自增長(zhǎng)列（auto_increment）,自增長(zhǎng)列的值不能為空品腹，如果在使用的時(shí)候?yàn)榭談t自動(dòng)從現(xiàn)有值開(kāi)始增值岖食，如果有但是比現(xiàn)在的還大，則直接保存這個(gè)值; 該引擎存儲(chǔ)引擎支持外鍵（foreign key） ,外鍵所在的表稱為子表而所依賴的表稱為父表舞吭。該引擎在5.5后的MySQL數(shù)據(jù)庫(kù)中為默認(rèn)存儲(chǔ)引擎泡垃。
它的設(shè)計(jì)的目標(biāo)就是處理大數(shù)據(jù)容量的數(shù)據(jù)庫(kù)系統(tǒng)。它本身實(shí)際上是基于Mysql后臺(tái)的完整的系統(tǒng)羡鸥。Mysql運(yùn)行的時(shí)候蔑穴，Innodb會(huì)在內(nèi)存中建立緩沖池，用于緩沖數(shù)據(jù)和索引惧浴。但是存和，該引擎是不支持全文搜索的。同時(shí)衷旅，啟動(dòng)也比較的慢捐腿，它是不會(huì)保存表的行數(shù)的。當(dāng)進(jìn)行Select count(*) from table指令的時(shí)候柿顶，需要進(jìn)行掃描全表茄袖。所以當(dāng)需要使用數(shù)據(jù)庫(kù)的事務(wù)時(shí)，該引擎就是首選嘁锯。由于鎖的粒度小宪祥，寫(xiě)操作是不會(huì)鎖定全表的。所以在并發(fā)度較高的場(chǎng)景下使用會(huì)提升效率的猪钮。

MyISAM VS Innodb

大容量的數(shù)據(jù)集時(shí)趨向于選擇Innodb品山。因?yàn)樗С质聞?wù)處理和故障的恢復(fù)。Innodb可以利用數(shù)據(jù)日志來(lái)進(jìn)行數(shù)據(jù)的恢復(fù)烤低。主鍵的查詢?cè)贗nnodb也是比較快的肘交。
大批量的插入語(yǔ)句時(shí)（這里是INSERT語(yǔ)句）在MyIASM引擎中執(zhí)行的比較的快，但是UPDATE語(yǔ)句在Innodb下執(zhí)行的會(huì)比較的快扑馁，尤其是在并發(fā)量大的時(shí)候涯呻。
MyIASM引擎，B+樹(shù)的數(shù)據(jù)結(jié)構(gòu)中存儲(chǔ)的內(nèi)容實(shí)際上是實(shí)際數(shù)據(jù)的地址值腻要。也就是說(shuō)它的索引和實(shí)際數(shù)據(jù)是分開(kāi)的复罐，只不過(guò)使用索引指向了實(shí)際數(shù)據(jù)。這種索引的模式被稱為非聚集索引雄家。
Innodb引擎的索引的數(shù)據(jù)結(jié)構(gòu)也是B+樹(shù)效诅，只不過(guò)數(shù)據(jù)結(jié)構(gòu)中存儲(chǔ)的都是實(shí)際的數(shù)據(jù)，這種索引有被稱為聚集索引。

HEAP（也稱為MEMORY）

該存儲(chǔ)引擎通過(guò)在內(nèi)存中創(chuàng)建臨時(shí)表來(lái)存儲(chǔ)數(shù)據(jù)乱投。每個(gè)基于該存儲(chǔ)引擎的表實(shí)際對(duì)應(yīng)一個(gè)磁盤(pán)文件咽笼，該文件的文件名和表名是相同的，類型為.frm戚炫。該磁盤(pán)文件只存儲(chǔ)表的結(jié)構(gòu)剑刑，而其數(shù)據(jù)存儲(chǔ)在內(nèi)存中，所以使用該種引擎的表?yè)碛袠O高的插入双肤、更新和查詢效率施掏。這種存儲(chǔ)引擎默認(rèn)使用哈希（HASH）索引，其速度比使用B-+Tree型要快茅糜，但也可以使用B樹(shù)型索引七芭。由于這種存儲(chǔ)引擎所存儲(chǔ)的數(shù)據(jù)保存在內(nèi)存中，所以其保存的數(shù)據(jù)具有不穩(wěn)定性蔑赘，比如如果mysqld進(jìn)程發(fā)生異常抖苦、重啟或計(jì)算機(jī)關(guān)機(jī)等等都會(huì)造成這些數(shù)據(jù)的消失，所以這種存儲(chǔ)引擎中的表的生命周期很短米死，一般只使用一次锌历。
CSV（Comma-Separated Values逗號(hào)分隔值）
使用該引擎的MySQL數(shù)據(jù)庫(kù)表會(huì)在MySQL安裝目錄data文件夾中的和該表所在數(shù)據(jù)庫(kù)名相同的目錄中生成一個(gè).CSV文件（所以，它可以將CSV類型的文件當(dāng)做表進(jìn)行處理）峦筒，這種文件是一種普通文本文件究西，每個(gè)數(shù)據(jù)行占用一個(gè)文本行。該種類型的存儲(chǔ)引擎不支持索引物喷，即使用該種類型的表沒(méi)有主鍵列卤材；另外也不允許表中的字段為null。

BLACKHOLE（黑洞引擎）

該存儲(chǔ)引擎支持事務(wù)峦失，而且支持mvcc的行級(jí)鎖扇丛，寫(xiě)入這種引擎表中的任何數(shù)據(jù)都會(huì)消失，主要用于做日志記錄或同步歸檔的中繼存儲(chǔ)尉辑，這個(gè)存儲(chǔ)引擎除非有特別目的帆精，否則不適合使用

mysql 索引簡(jiǎn)介

索引是一種特殊的文件(InnoDB數(shù)據(jù)表上的索引是表空間的一個(gè)組成部分)例嘱，它們包含著對(duì)數(shù)據(jù)表里所有記錄的引用指針坟乾。更通俗的說(shuō)，數(shù)據(jù)庫(kù)索引好比是一本書(shū)前面的目錄蝶防，能加快數(shù)據(jù)庫(kù)的查詢速度甚侣，MySQL目前主要有以下幾種索引類型：
1.普通索引
2.唯一索引
3.主鍵索引
4.組合索引
5.全文索引

1：普通索引

是最基本的索引，它沒(méi)有任何限制间学。它有以下幾種創(chuàng)建方式：

（1）直接創(chuàng)建索引
CREATE INDEX index_name ON table(column(length))
（2）修改表結(jié)構(gòu)的方式添加索引
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
（3）創(chuàng)建表的時(shí)候同時(shí)創(chuàng)建索引
CREATE TABLE table (
id int(11)NOT NULL AUTO_INCREMENT,
title char(255) CHARACTER NOT NULL,
content text CHARACTER NULL,
time int(10) NULL DEFAULT NULL,
PRIMARY KEY(id),
INDEX index_name (title(length))
)
（4）刪除索引
DROP INDEX index_name ON table

2：唯一索引

與前面的普通索引類似殷费，不同的就是：索引列的值必須唯一印荔，但允許有空值。如果是組合索引详羡，則列值的組合必須唯一仍律。它有以下幾種創(chuàng)建方式：

（1）創(chuàng)建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length))
（2）修改表結(jié)構(gòu)
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))
（3）創(chuàng)建表的時(shí)候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    UNIQUE indexName (title(length))
);

3：主鍵索引

是一種特殊的唯一索引，一個(gè)表只能有一個(gè)主鍵实柠，不允許有空值水泉。一般是在建表的時(shí)候同時(shí)創(chuàng)建主鍵索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) NOT NULL ,
    PRIMARY KEY (`id`)
);

4：組合索引

指多個(gè)字段上創(chuàng)建的索引，只有在查詢條件中使用了創(chuàng)建索引時(shí)的第一個(gè)字段窒盐，索引才會(huì)被使用草则。使用組合索引時(shí)遵循最左前綴集合

ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);

5.全文索引

主要用來(lái)查找文本中的關(guān)鍵字，而不是直接與索引中的值相比較蟹漓。fulltext索引跟其它索引大不相同炕横，它更像是一個(gè)搜索引擎，而不是簡(jiǎn)單的where語(yǔ)句的參數(shù)匹配葡粒。fulltext索引配合match against操作使用份殿，而不是一般的where語(yǔ)句加like。它可以在create table嗽交，alter table 卿嘲，create index使用，不過(guò)目前只有char夫壁、varchar腔寡，text 列上可以創(chuàng)建全文索引。值得一提的是掌唾，在數(shù)據(jù)量較大時(shí)候放前，現(xiàn)將數(shù)據(jù)放入一個(gè)沒(méi)有全局索引的表中，然后再用CREATE index創(chuàng)建fulltext索引糯彬，要比先為一張表建立fulltext然后再將數(shù)據(jù)寫(xiě)入的速度快很多凭语。

（1）創(chuàng)建表的適合添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);
（2）修改表結(jié)構(gòu)添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content)
（3）直接創(chuàng)建索引
CREATE FULLTEXT INDEX index_content ON article(content)

缺點(diǎn)

1.雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度撩扒，如對(duì)表進(jìn)行insert似扔、update和delete。因?yàn)楦卤頃r(shí)搓谆，不僅要保存數(shù)據(jù)炒辉，還要保存一下索引文件。
2.建立索引會(huì)占用磁盤(pán)空間的索引文件泉手。一般情況這個(gè)問(wèn)題不太嚴(yán)重黔寇，但如果你在一個(gè)大表上創(chuàng)建了多種組合索引，索引文件的會(huì)增長(zhǎng)很快斩萌。
索引只是提高效率的一個(gè)因素缝裤，如果有大數(shù)據(jù)量的表屏轰，就需要花時(shí)間研究建立最優(yōu)秀的索引，或優(yōu)化查詢語(yǔ)句憋飞。

五霎苗、注意事項(xiàng)

使用索引時(shí)，有以下一些技巧和注意事項(xiàng)：

1.索引不會(huì)包含有null值的列
只要列中包含有null值都將不會(huì)被包含在索引中榛做，復(fù)合索引中只要有一列含有null值唁盏，那么這一列對(duì)于此復(fù)合索引就是無(wú)效的。所以我們?cè)?a target="_blank">數(shù)據(jù)庫(kù)設(shè)計(jì)時(shí)不要讓字段的默認(rèn)值為null检眯。

2.使用短索引
對(duì)串列進(jìn)行索引厘擂，如果可能應(yīng)該指定一個(gè)前綴長(zhǎng)度。例如轰传，如果有一個(gè)char(255)的列，如果在前10個(gè)或20個(gè)字符內(nèi)瘪撇，多數(shù)值是惟一的获茬，那么就不要對(duì)整個(gè)列進(jìn)行索引。短索引不僅可以提高查詢速度而且可以節(jié)省磁盤(pán)空間和I/O操作倔既。

3.索引列排序
查詢只使用一個(gè)索引恕曲，因此如果where子句中已經(jīng)使用了索引的話，那么order by中的列是不會(huì)使用索引的渤涌。因此數(shù)據(jù)庫(kù)默認(rèn)排序可以符合要求的情況下不要使用排序操作佩谣；盡量不要包含多個(gè)列的排序，如果需要最好給這些列創(chuàng)建復(fù)合索引实蓬。

4.like語(yǔ)句操作
一般情況下不推薦使用like操作茸俭，如果非使用不可，如何使用也是一個(gè)問(wèn)題安皱。like “%aaa%” 不會(huì)使用索引而like “aaa%”可以使用索引调鬓。

5.盡量不要在列上進(jìn)行運(yùn)算
這將導(dǎo)致索引失效而進(jìn)行全表掃描，例如

SELECT*FROM table_name WHERE YEAR (column_name)<2017;

6.盡量不使用not in和<>操作

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者

人面猴
序言：七十年代末酌伊，一起剝皮案震驚了整個(gè)濱河市腾窝，隨后出現(xiàn)的幾起案子，更是在濱河造成了極大的恐慌居砖，老刑警劉巖虹脯，帶你破解...
沈念sama閱讀 206,602評(píng)論 6贊 481
死咒
序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場(chǎng)離奇詭異奏候，居然都是意外死亡循集，警方通過(guò)查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
沈念sama閱讀 88,442評(píng)論 2贊 382
救了他兩次的神仙讓他今天三更去死
文/潘曉璐我一進(jìn)店門蔗草，熙熙樓的掌柜王于貴愁眉苦臉地迎上來(lái)暇榴，“玉大人厚棵，你說(shuō)我怎么就攤上這事“簦” “怎么了婆硬？”我有些...
開(kāi)封第一講書(shū)人閱讀 152,878評(píng)論 0贊 344
道士緝兇錄：失蹤的賣姜人
文/不壞的土叔我叫張陵，是天一觀的道長(zhǎng)奸例。經(jīng)常有香客問(wèn)我彬犯，道長(zhǎng)，這世上最難降的妖魔是什么查吊？我笑而不...
開(kāi)封第一講書(shū)人閱讀 55,306評(píng)論 1贊 279
?港島之戀（遺憾婚禮）
正文為了忘掉前任谐区，我火速辦了婚禮，結(jié)果婚禮上逻卖，老公的妹妹穿的比我還像新娘宋列。我一直安慰自己，他們只是感情好评也，可當(dāng)我...
茶點(diǎn)故事閱讀 64,330評(píng)論 5贊 373
惡毒庶女頂嫁案：這布局不是一般人想出來(lái)的
文/花漫我一把揭開(kāi)白布炼杖。她就那樣靜靜地躺著，像睡著了一般盗迟。火紅的嫁衣襯著肌膚如雪坤邪。梳的紋絲不亂的頭發(fā)上，一...
開(kāi)封第一講書(shū)人閱讀 49,071評(píng)論 1贊 285
城市分裂傳說(shuō)
那天罚缕，我揣著相機(jī)與錄音艇纺，去河邊找鬼。笑死邮弹，一個(gè)胖子當(dāng)著我的面吹牛黔衡，可吹牛的內(nèi)容都是我干的。我是一名探鬼主播腌乡，決...
沈念sama閱讀 38,382評(píng)論 3贊 400
雙鴛鴦連環(huán)套：你想象不到人心有多黑
文/蒼蘭香墨我猛地睜開(kāi)眼员帮，長(zhǎng)吁一口氣：“原來(lái)是場(chǎng)噩夢(mèng)啊……” “哼！你這毒婦竟也來(lái)了导饲？” 一聲冷哼從身側(cè)響起捞高，我...
開(kāi)封第一講書(shū)人閱讀 37,006評(píng)論 0贊 259
萬(wàn)榮殺人案實(shí)錄
序言：老撾萬(wàn)榮一對(duì)情侶失蹤，失蹤者是張志新（化名）和其女友劉穎渣锦，沒(méi)想到半個(gè)月后硝岗，有當(dāng)?shù)厝嗽跇?shù)林里發(fā)現(xiàn)了一具尸體，經(jīng)...
沈念sama閱讀 43,512評(píng)論 1贊 300
?護(hù)林員之死
正文獨(dú)居荒郊野嶺守林人離奇死亡袋毙，尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛以下內(nèi)容為張勛視角年9月15日...
茶點(diǎn)故事閱讀 35,965評(píng)論 2贊 325
?白月光啟示錄
正文我和宋清朗相戀三年型檀，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片听盖。...
茶點(diǎn)故事閱讀 38,094評(píng)論 1贊 333
活死人
序言：一個(gè)原本活蹦亂跳的男人離奇死亡胀溺，死狀恐怖裂七，靈堂內(nèi)的尸體忽然破棺而出，到底是詐尸還是另有隱情仓坞，我是刑警寧澤背零，帶...
沈念sama閱讀 33,732評(píng)論 4贊 323
?日本核電站爆炸內(nèi)幕
正文年R本政府宣布，位于F島的核電站无埃，受9級(jí)特大地震影響徙瓶，放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜嫉称，卻給世界環(huán)境...
茶點(diǎn)故事閱讀 39,283評(píng)論 3贊 307
男人毒藥：我在死后第九天來(lái)索命
文/蒙蒙一侦镇、第九天我趴在偏房一處隱蔽的房頂上張望。院中可真熱鬧织阅，春花似錦壳繁、人聲如沸。這莊子的主人今日做“春日...
開(kāi)封第一講書(shū)人閱讀 30,286評(píng)論 0贊 19
一樁弒父案闹炉，背后竟有這般陰謀
文/蒼蘭香墨我抬頭看了看天上的太陽(yáng)。三九已至江耀，卻和暖如春剩胁，著一層夾襖步出監(jiān)牢的瞬間诉植，已是汗流浹背祥国。一陣腳步聲響...
開(kāi)封第一講書(shū)人閱讀 31,512評(píng)論 1贊 262
情欲美人皮
我被黑心中介騙來(lái)泰國(guó)打工，沒(méi)想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留晾腔，地道東北人舌稀。一個(gè)月前我還...
沈念sama閱讀 45,536評(píng)論 2贊 354
代替公主和親
正文我出身青樓，卻偏偏與公主長(zhǎng)得像灼擂，于是被迫代替她去往敵國(guó)和親壁查。傳聞我的和親對(duì)象是個(gè)殘疾皇子，可洞房花燭夜當(dāng)晚...
茶點(diǎn)故事閱讀 42,828評(píng)論 2贊 345