MYSQL
基礎(chǔ)知識(shí)
1 MySQL數(shù)據(jù)庫(kù)概要
2 簡(jiǎn)單MySQL環(huán)境
3 數(shù)據(jù)的存儲(chǔ)和獲取
4 MySQL基本操作
5 函數(shù)
6 數(shù)據(jù)查詢
核心技術(shù)
7 索引
8 存儲(chǔ)過(guò)程
9 觸發(fā)器
10 視圖
11 事務(wù)
12 系統(tǒng)管理
高級(jí)技術(shù)
13 數(shù)據(jù)庫(kù)備份與還原
14 MySQL性能優(yōu)化
15 MySQL數(shù)據(jù)庫(kù)安全技術(shù)
16 MySQL日常管理
17 PHP訪問(wèn)MySQL數(shù)據(jù)庫(kù)
項(xiàng)目實(shí)戰(zhàn)
18 Java與MySQL數(shù)據(jù)庫(kù)——圖書(shū)管理系統(tǒng)
19 C語(yǔ)言與MySQL數(shù)據(jù)庫(kù)——圖書(shū)管理系統(tǒng)
大綱細(xì)節(jié) page 02
基礎(chǔ)知識(shí)
1 MySQL數(shù)據(jù)庫(kù)概要
特性
應(yīng)用環(huán)境
管理軟件
學(xué)習(xí)方法
2 簡(jiǎn)單MySQL環(huán)境
下載與安裝
啟動(dòng)連接斷開(kāi)停止
MySQL Workbench圖形化管理工具
phpMyAdmin圖形化管理工具
3 數(shù)據(jù)的存儲(chǔ)和獲取
MySQL存儲(chǔ)引擎
MySQL 數(shù)據(jù)類(lèi)型
MySQL運(yùn)算符
4 MySQL基本操作
數(shù)據(jù)庫(kù)操作
創(chuàng)建
查看
選擇
刪除
數(shù)據(jù)表操作
創(chuàng)建數(shù)據(jù)表
查看表結(jié)構(gòu)
修改表結(jié)構(gòu)
重命名表結(jié)構(gòu)
刪除表
MySQL語(yǔ)句操作
插入記錄
查詢數(shù)據(jù)庫(kù)記錄
修改記錄
刪除記錄
外鍵約束
5 函數(shù)
MySQL函數(shù)
數(shù)學(xué)函數(shù)（ABS FLOOR RAND PI TRUNCATE ROUND SQRT )
字符串函數(shù)( INSERT UPPER UCASE LEFT RTRIM SUBSTRING REVERSE FIELD)
日期和時(shí)間函數(shù)(CURDATE CURTIME NOW DATEDIFF ADDDATE SUBDATE)
條件判斷函數(shù)
系統(tǒng)信息函數(shù)
加密函數(shù)(PASSWORD MD5)
其他函數(shù)
6 數(shù)據(jù)查詢
基本查詢語(yǔ)句
單表查詢
聚合函數(shù)查詢
連接查詢
子查詢
合并查詢結(jié)果
定義表和字段的別名
使用正則表達(dá)式查詢
實(shí)踐：使用聚合函數(shù)sum對(duì)學(xué)生成績(jī)進(jìn)行匯總
核心技術(shù)
7 索引
MySQL索引概述與分類(lèi)
創(chuàng)建索引
刪除索引
8 存儲(chǔ)過(guò)程
創(chuàng)建存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)
流程控制語(yǔ)句
調(diào)用存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)
查看存儲(chǔ)過(guò)程和函數(shù)
修改存儲(chǔ)過(guò)程和函數(shù)
刪除存儲(chǔ)過(guò)程和函數(shù)
捕獲存儲(chǔ)過(guò)程中的錯(cuò)誤
實(shí)踐： 使用存儲(chǔ)過(guò)程實(shí)現(xiàn)用戶注冊(cè)
9 觸發(fā)器
創(chuàng)建MySQL觸發(fā)器
查看觸發(fā)器
應(yīng)用觸發(fā)器
刪除觸發(fā)器
10 視圖
視圖的概念與作用
創(chuàng)建視圖
查看視圖
修改視圖
更新視圖
刪除視圖
實(shí)踐：使用MySQL視圖查詢學(xué)生信息表
11 事務(wù)
MySQL事物概述（原子性 一致性 孤立性 持久性）
MySQL事物的創(chuàng)建與存在周期
MySQL行為
事物和性能
MySQL偽事物
12 系統(tǒng)管理
MySQL系統(tǒng)管理概述
數(shù)據(jù)目錄的位置
數(shù)據(jù)目錄的結(jié)構(gòu)
MySQL服務(wù)器如何提供對(duì)數(shù)據(jù)的訪問(wèn)
MySQL數(shù)據(jù)庫(kù)在文件系統(tǒng)里如何表示
MySQL數(shù)據(jù)表如何在文件系統(tǒng)里表示
SQL語(yǔ)句如何映射為數(shù)據(jù)表文件操作
操作系統(tǒng)對(duì)數(shù)據(jù)庫(kù)和數(shù)據(jù)表命名的限制
數(shù)據(jù)目錄的結(jié)構(gòu)對(duì)系統(tǒng)性能的影響
MySQL狀態(tài)文件和日志文件
高級(jí)技術(shù)
13 數(shù)據(jù)庫(kù)備份與還原
數(shù)據(jù)備份
數(shù)據(jù)還原
數(shù)據(jù)庫(kù)遷移
表的導(dǎo)出和導(dǎo)入
實(shí)踐：導(dǎo)出XML文件
14 MySQL性能優(yōu)化
優(yōu)化簡(jiǎn)介
優(yōu)化查詢
優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)
查詢高速緩存
優(yōu)化多表查詢
優(yōu)化表設(shè)計(jì)
15 MySQL數(shù)據(jù)庫(kù)安全技術(shù)
MySQL的基本安全和保護(hù)策略
用戶和權(quán)限管理
MySQL數(shù)據(jù)庫(kù)安全技術(shù)常見(jiàn)問(wèn)題
16 MySQL日常管理
連接故障恢復(fù)
日志文件管理
MySQL服務(wù)器鏡像配置
MySQL服務(wù)器優(yōu)化配置
優(yōu)化MySQL服務(wù)器
運(yùn)行多個(gè)MySQL服務(wù)器
17 PHP訪問(wèn)MySQL數(shù)據(jù)庫(kù)
PHP語(yǔ)言概述
PHP操作MySQL數(shù)據(jù)庫(kù)的步驟
使用PHP操作MySQL數(shù)據(jù)庫(kù)
PHP管理MySQL數(shù)據(jù)庫(kù)中的數(shù)據(jù)
常見(jiàn)問(wèn)題與解決方法
MySQL與PHP的應(yīng)用實(shí)例---迷你日記
項(xiàng)目實(shí)戰(zhàn)
18 Java與MySQL數(shù)據(jù)庫(kù)——圖書(shū)管理系統(tǒng)
19 C語(yǔ)言與MySQL數(shù)據(jù)庫(kù)——圖書(shū)管理系統(tǒng)
需求分析
系統(tǒng)設(shè)計(jì)
數(shù)據(jù)庫(kù)設(shè)計(jì)
C語(yǔ)言開(kāi)發(fā)數(shù)據(jù)庫(kù)程序的流程
數(shù)據(jù)庫(kù)管理模塊設(shè)計(jì)
文件引用
變量和函數(shù)定義
管理模塊設(shè)計(jì)
熟知概念 page 03
基礎(chǔ)知識(shí)
1 MySQL數(shù)據(jù)庫(kù)概要
特性
開(kāi)放的 快速的 多線程的 多用戶
應(yīng)用環(huán)境
MySQL是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一膛锭，在web應(yīng)用方面凑耻，MySQL是最好的RDBMS（Relational Database Management System）應(yīng)用軟件陶因，LAMP LNMP
管理軟件
命令行工具：mysql命令和mysqladmin命令
圖形管理工具：workbench ，MySQL Administrator视搏， MySQL Query Browser
學(xué)習(xí)方法
如何學(xué)好MySQL： 多上機(jī)練習(xí) 多編寫(xiě)SQL語(yǔ)句 牢記數(shù)據(jù)庫(kù)理論知識(shí)
MySQL學(xué)習(xí)常見(jiàn)問(wèn)題：待回答
2 簡(jiǎn)單的MySQL環(huán)境
下載與安裝
啟動(dòng)連接斷開(kāi)停止
centos6:
chkconfig --list mysqld
chkconfig --add mysqld
chkconfig mysqld on # 設(shè)置MySQL開(kāi)機(jī)自啟
service mysqld start/stop
mysqladmin -u root -p shutdown # 命令關(guān)閉MySQL
mysql -u root -h localhost -p
MySQL Workbench圖形化管理工具
phpMyAdmin圖形化管理工具
3 數(shù)據(jù)的存儲(chǔ)和獲取
MySQL存儲(chǔ)引擎
InnoDB?事務(wù)型數(shù)據(jù)庫(kù)的首選引擎径密，支持ACID事務(wù)午阵，支持行級(jí)鎖定, MySQL 5.5 起成為默認(rèn)數(shù)據(jù)庫(kù)引擎
MyISAM?MySQL 5.0 之前的默認(rèn)數(shù)據(jù)庫(kù)引擎，最為常用。擁有較高的插入底桂，查詢速度植袍，但不支持事務(wù)
Memory 所有數(shù)據(jù)置于內(nèi)存的存儲(chǔ)引擎，擁有極高的插入籽懦，更新和查詢效率于个。但是會(huì)占用和數(shù)據(jù)量成正比的內(nèi)存空間。并且其內(nèi)容會(huì)在 MySQL 重新啟動(dòng)時(shí)丟失
如何選擇存儲(chǔ)引擎：
除非需要用到某些InnoDB不具備的特性暮顺，并且沒(méi)有其他辦法可以替代厅篓，否則都應(yīng)該優(yōu)先選擇InnoDB引擎
如何設(shè)置數(shù)據(jù)表的存儲(chǔ)引擎
alter table table_name engine=innodb; (修改表引擎）
show engines; （查看系統(tǒng)支持的存儲(chǔ)引擎）
show variables like '%storage_engine%'; (查看當(dāng)前默認(rèn)的存儲(chǔ)引擎)
mysql> show create table table_name; (查看某個(gè)表使用的存儲(chǔ)引擎)
MySQL 數(shù)據(jù)類(lèi)型
數(shù)值類(lèi)型:
1 整數(shù)類(lèi)型： int tinyint bigint
2 浮點(diǎn)型：float 和double 不推薦使用浮點(diǎn)型
3 高精度型： decimal （numeric）如：amount DECIMAL(6,2)表示amount列最多存儲(chǔ)6位數(shù)字，小數(shù)位數(shù)為2位捶码。
字符類(lèi)型：
1 varchar和char 變長(zhǎng)字符和定長(zhǎng)字符
2 text和blob 存儲(chǔ)文本大數(shù)據(jù)類(lèi)型和存儲(chǔ)二進(jìn)制大數(shù)據(jù)類(lèi)型（實(shí)際很少使用羽氮，太浪費(fèi)資源）
3 varbinary和binary 存儲(chǔ)二進(jìn)制字符
4 enum和set 集合類(lèi)型，enum可枚舉65536個(gè)元素而set最多64個(gè)
日期和時(shí)間類(lèi)型：
DateTime（8個(gè)字節(jié)） TimeStamp（4）Date（3） Year（1）Time (1)
實(shí)際開(kāi)發(fā)中選擇TimeStamp作為時(shí)間類(lèi)型的字段惫恼，也可以用int
MySQL運(yùn)算符
1 算數(shù)運(yùn)算符： + - * / %
2 比較運(yùn)算符： == <==>安全的等于 档押， <>和!= 不等于 ，<= , >= ,> ,<,
IS NULL ,IS NOT NULL , IN, NOT IN, LIKE(通配符匹配)祈纯， REGEXP(正則表達(dá)式匹配)
3 邏輯運(yùn)算符的求值所得結(jié)果均為1 (TRUE)令宿、0( FALSE)，這類(lèi)運(yùn)算符有邏輯非（NOT或者!）腕窥、邏輯與（AND或者&&）粒没、邏輯或（OR或者||）、邏輯異或（XOR）油昂。
4 位操作運(yùn)算符 參與運(yùn)算符的操作數(shù)，按二進(jìn)制位進(jìn)行運(yùn)算倾贰。包括位與（&）冕碟、位或（|）、位非（~）匆浙、位異或（^）安寺、左移（<<）、右移(>>)6種首尼。
4 MySQL基本操作
數(shù)據(jù)庫(kù)操作
創(chuàng)建CREATE DATABASE db_name;
查看SHOW DATABASES;
查看某一個(gè)庫(kù)：SHOW CREATE DATABASE db_name;
選擇USE db_name;
查看當(dāng)前use了哪個(gè)庫(kù)：select database();
刪除DROP DATABASE db_name挑庶；
數(shù)據(jù)表操作
創(chuàng)建數(shù)據(jù)表
create table 表名 (
字段名1 類(lèi)型 (寬度) 約束條件,
字段名2 類(lèi)型(寬度) 約束條件,
字段名3 類(lèi)型(寬度) 約束條件,
.......
);
注 :
同一張表中，字段名不能相同
字段名和類(lèi)型必須有
寬度和約束條件為可選項(xiàng)
查看某庫(kù)有多少個(gè)表： SHOW TABLES;
查看表結(jié)構(gòu):DESC db_name;
查看表的所有字段內(nèi)容：SELECT * FROM db_name;
查看某些字段SELECT id, port FROM host;
修改表結(jié)構(gòu)
修改一列類(lèi)型
alter table tab_name modify 列名 類(lèi)型 [完整性約束條件]［first｜after 字段名］;
alter table users2 modify age tinyint default 20;
alter table users2 modify age int after id;
修改列名
alter table tab_name change [column] 列名 新列名
類(lèi)型 [完整性約束條件]［first｜after 字段名］;
alter table users2 change age Age int default 28 first;
刪除一列
alter table tab_name drop [column] 列名;
思考：刪除多列呢软能？刪一個(gè)填一個(gè)呢迎捺？
alter table users2
add salary float(6,2) unsigned not null after name,
drop addr;
修改表名
rename table 表名 to 新表名;
修該表所用的字符集
alter table student character set utf8;
刪除表
drop table tab_name;
添加主鍵，刪除主鍵
alter table tab_name add primary key(字段名稱(chēng),...)
ALTER TABLE person ADD PRIMARY KEY (id);
alter table users drop primary key;
重命名表RENAME TABLE old_table_name TO new_table_name;
刪除表DROP TABLE table_name;
插入記錄
INSERT INTO table_name ( field1, field2,...fieldN )
VALUES
( value1, value2,...valueN );
修改記錄
需要修改或更新 MySQL 中的數(shù)據(jù)查排，我們可以使用 UPDATE 命令來(lái)操作
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
你可以同時(shí)更新一個(gè)或多個(gè)字段凳枝。
你可以在 WHERE 子句中指定任何條件。
你可以在一個(gè)單獨(dú)表中同時(shí)更新數(shù)據(jù)。
當(dāng)你需要更新數(shù)據(jù)表中指定行的數(shù)據(jù)時(shí) WHERE 子句是非常有用的岖瑰。
UPDATE runoob_tbl SET runoob_title='學(xué)習(xí) C++' WHERE runoob_id=3;
刪除記錄
DELETE FROM 命令來(lái)刪除 MySQL 數(shù)據(jù)表中的記錄叛买。
DELETE FROM table_name [WHERE Clause]
如果沒(méi)有指定 WHERE 子句，MySQL 表中的所有記錄將被刪除蹋订。
你可以在 WHERE 子句中指定任何條件
您可以在單個(gè)表中一次性刪除記錄率挣。
當(dāng)你想刪除數(shù)據(jù)表中指定的記錄時(shí) WHERE 子句是非常有用的。
DELETE FROM runoob_tbl WHERE runoob_id=3;
MySQL SQL語(yǔ)句總結(jié)
庫(kù)：CREATE, DROP, SHOW, USE
表：CEATE ,DROP, DESC,SHOW,RENAME,ALTER
字段：ALTER ..CHANGE, ALTER...MODIFY, ALTER...ADD, ALTER...DROP
記錄：INSERT INTO ... ,UPDATE..SET...[WHERE...],
DELETE FROM...[WHERE...]
語(yǔ)言類(lèi)型：
數(shù)據(jù)查詢語(yǔ)言DQL (Data Query Language)
數(shù)據(jù)查詢語(yǔ)言DQL基本結(jié)構(gòu)是由SELECT子句露戒，F(xiàn)ROM子句椒功，WHERE
子句組成的查詢塊：
SELECT <字段名表>
FROM <表或視圖名>
WHERE <查詢條件>
2 .數(shù)據(jù)操縱語(yǔ)言DML（Data Manipulation Language）
數(shù)據(jù)操縱語(yǔ)言DML主要有三種形式：
(1) 插入：INSERT
(2) 更新：UPDATE
(3) 刪除：DELETE
數(shù)據(jù)定義語(yǔ)言DDL（ Data Definition Language）
數(shù)據(jù)定義語(yǔ)言DDL用來(lái)創(chuàng)建數(shù)據(jù)庫(kù)中的各種對(duì)象-----表、視圖玫锋、
索引蛾茉、同義詞、聚簇等如：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
| | | | |
表 視圖 索引 同義詞 簇
DDL操作是隱性提交的撩鹿！不能rollback
數(shù)據(jù)控制語(yǔ)言DCL（Data Control Language）
數(shù)據(jù)控制語(yǔ)言DCL用來(lái)授予或回收訪問(wèn)數(shù)據(jù)庫(kù)的某種特權(quán)谦炬，并控制
數(shù)據(jù)庫(kù)操縱事務(wù)發(fā)生的時(shí)間及效果，對(duì)數(shù)據(jù)庫(kù)實(shí)行監(jiān)視等节沦。如：
(1) GRANT：授權(quán)键思。
(2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一點(diǎn)「幔回滾
(3) COMMIT [WORK]：提交
約束
約束是一種限制吼鳞，它通過(guò)對(duì)表的行或列的數(shù)據(jù)做出限制，來(lái)確保表的數(shù)據(jù)的完整性叫搁、唯一性赔桌。
約束類(lèi)型：主鍵 外鍵 唯一 非空 自增 默認(rèn)值
關(guān)鍵字： primary key foreign key unique not null
auto_increment default
1、主鍵約束 primary key
主鍵約束 主鍵列的數(shù)據(jù)類(lèi)型不限渴逻，但此列必須是唯一并且非空疾党。當(dāng)創(chuàng)建主鍵的約束時(shí)，系統(tǒng)默認(rèn)會(huì)在所在的列和列組合上建立對(duì)應(yīng)的唯一索引惨奕。
創(chuàng)建主鍵約束
create table temp(
id int primary key,
name varchar(20)
);
刪除主鍵約束
alter table temp drop primary key;
添加主鍵約束
alter table temp add primary key(id,name);
2雪位、外鍵約束 foreign key
外鍵約束是保證一個(gè)或兩個(gè)表之間的參照完整性，外鍵是構(gòu)建于一個(gè)表的兩個(gè)字段或是兩個(gè)表的兩個(gè)字段之間的參照關(guān)系
基本模式
-- 主表
create table temp(
id int primary key,
name varchar(20)
);
-- 副表
create table temp2(
id int,
name varchar(20),
classes_id int,
foreign key(id) references temp(id)
);
多列外鍵組合梨撞，必須用表級(jí)別約束語(yǔ)法
-- 主表
create table classes(
id int,
name varchar(20),
number int,
primary key(name,number)
);
副表
create table student(
id int auto_increment primary key,
name varchar(20),
classes_name varchar(20),
classes_number int,
/表級(jí)別聯(lián)合外鍵/
foreign key(classes_name, classes_number) references classes(name, number)
);
刪除外鍵約束
alter table student drop foreign key student_id;
增加外鍵約束
alter table student add foreign key(classes_name, classes_number) references classes(name, number);
3雹洗、 唯一約束unique
唯一約束是指定table的列或列組合不能重復(fù)，保證數(shù)據(jù)的唯一性卧波。
創(chuàng)建唯一約束
創(chuàng)建表時(shí)設(shè)置时肿，表示用戶名、密碼不能重復(fù)
create table temp(
id int not null ,
name varchar(20),
password varchar(10),
unique(name,password)
);
添加唯一約束
alter table temp add unique (name, password);
修改唯一約束
alter table temp modify name varchar(25) unique;
刪除約束
alter table temp drop index name;
4港粱、非空約束 not null 與 默認(rèn)值 default
非空約束用于確保當(dāng)前列的值不為空值嗜侮，非空約束只能出現(xiàn)在表對(duì)象的列上。
創(chuàng)建非空約束
創(chuàng)建table表，ID 為非空約束锈颗，name 為非空約束 且默認(rèn)值為abc
create table temp(
id int not null,
name varchar(255) not null default 'abc',
sex char null
)顷霹；
增加非空約束
alter table temp modify sex varchar(2) not null;
取消非空約束
alter table temp modify sex varchar(2) null;
取消非空約束，增加默認(rèn)值
alter table temp modify sex varchar(2) default 'abc' null;
5 函數(shù)
MySQL函數(shù)
數(shù)學(xué)函數(shù)（ABS FLOOR RAND PI TRUNCATE ROUND SQRT )
ABS(x) 返回x的絕對(duì)值
CEIL(x) 返回大于x的最小整數(shù)值
FLOOR(x) 返回小于x的最大整數(shù)值
MOD(x,y) 返回x/y的模（余數(shù)）
SQRT(x) 返回一個(gè)數(shù)的平方根
ROUND(x,y) 返回參數(shù)x的四舍五入的有y位小數(shù)的值
TRUNCATE(x,y) 返回?cái)?shù)字x截短為y位小數(shù)的結(jié)果
PI() 返回pi的值（圓周率）
RAND()返回０到１內(nèi)的隨機(jī)值,可以通過(guò)提供一個(gè)參數(shù)(種子)使RAND()隨機(jī)數(shù)生成器生成一個(gè)指定的值击吱。
字符串函數(shù)( INSERT UPPER UCASE LEFT RTRIM SUBSTRING REVERSE FIELD)
INSERT(str,x,y,instr) 將字符串str從第x位置開(kāi)始淋淀，y個(gè)字符長(zhǎng)的子串替換為字符串instr，返回結(jié)果
LOWER(str) 返回將字符串str中所有字符改變?yōu)樾?xiě)后的結(jié)果
UPPER(str) 返回將字符串str中所有字符轉(zhuǎn)變?yōu)榇髮?xiě)后的結(jié)果
LEFT(str,x)返回字符串str中最左邊的x個(gè)字符
RIGHT(str,x) 返回字符串str中最右邊的x個(gè)字符
TRIM(str)去除字符串首部和尾部的所有空格
REVERSE(str) 返回顛倒字符串str的結(jié)果
FIELD(filed_name,str1,str2,str3,str4)可以用來(lái)對(duì)SQL中查詢結(jié)果集進(jìn)行指定順序排序
select * from ta order by field(id,4,3,2,1);
select * from 表名 order by field(id,1,4,2,3) desc;
日期和時(shí)間函數(shù)(CURDATE CURTIME NOW DATEDIFF ADDDATE SUBDATE)
CURDATE()或CURRENT_DATE() 返回當(dāng)前的日期
CURTIME()或CURRENT_TIME() 返回當(dāng)前的時(shí)間
NOW() 返回當(dāng)前的日期和時(shí)間
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7)
DAYOFMONTH(date) 返回date是一個(gè)月的第幾天(1~31)
DAYOFYEAR(date) 返回date是一年的第幾天(1~366)
DAYNAME(date) 返回date的星期名覆醇，如：
SELECT DAYNAME(CURRENT_DATE);
HOUR(time) 返回time的小時(shí)值(0~23)
MINUTE(time) 返回time的分鐘值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名朵纷，如：
SELECT MONTHNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根據(jù)指定的fmt格式，格式化UNIX時(shí)間戳ts
條件判斷函數(shù)
IF(test,t,f) 如果test是真永脓，返回t袍辞；否則返回f
IFNULL(arg1,arg2) 如果arg1不是空，返回arg1常摧，否則返回arg2
CASE WHEN[test1] THEN [result1]...ELSE [default] END如果testN是真搅吁，則返回resultN，否則返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END 如果test和valN相等落午，則返回resultN谎懦，否則返回default
CASE函數(shù)的格式有些復(fù)雜，通常如下所示：
CASE [expression to be evaluated]
WHEN [val 1] THEN [result 1]
WHEN [val 2] THEN [result 2]
WHEN [val 3] THEN [result 3]
......
WHEN [val n] THEN [result n]
ELSE [default result]
END
CASE函數(shù)還有另外一種句法溃斋，有時(shí)使用起來(lái)非常方便界拦，如下：
CASE
WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END
這種條件下，返回的結(jié)果取決于相應(yīng)的條件測(cè)試是否為真梗劫。
系統(tǒng)信息函數(shù)
DATABASE() 返回當(dāng)前數(shù)據(jù)庫(kù)名
BENCHMARK(count,expr) 將表達(dá)式expr重復(fù)運(yùn)行count次
CONNECTION_ID() 返回當(dāng)前客戶的連接ID
FOUND_ROWS() 返回最后一個(gè)SELECT查詢進(jìn)行檢索的總行數(shù)
USER()或SYSTEM_USER() 返回當(dāng)前登陸用戶名
VERSION() 返回MySQL服務(wù)器的版本
示例：
SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));
該例中,MySQL計(jì)算LOG(RAND()*PI())表達(dá)式9999999次享甸。
加密函數(shù)(PASSWORD MD5)
AES_ENCRYPT(str,key) 返回用密鑰key對(duì)字符串str利用高級(jí)加密標(biāo)準(zhǔn)算法加密后的結(jié)果，調(diào)用AES_ENCRYPT的結(jié)果是一個(gè)二進(jìn)制字符串梳侨，以BLOB類(lèi)型存儲(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類(lèi)型存儲(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ù)
1妒牙、格式化函數(shù)：FORMAT(x,n)將數(shù)字x進(jìn)行四舍五入保留n位小數(shù)
2彼哼、不同進(jìn)制數(shù)字轉(zhuǎn)換：
ASCII(s)返回字符串s的第一個(gè)字符的ASCII碼
BIN(x)返回x的二進(jìn)制編碼
3、IP地址與數(shù)字相互轉(zhuǎn)換的函數(shù)：
INET_ATON(IP)將IP轉(zhuǎn)換為數(shù)字
INET_NTOA(n)將n轉(zhuǎn)換為IP
4湘今、改變字段數(shù)據(jù)類(lèi)型的函數(shù)：CAST(x AS type)敢朱、CONVERT(x,type)兩個(gè)函數(shù)將x變成type類(lèi)型，僅僅是改變輸出，沒(méi)有改變表中的字段類(lèi)型
5拴签、改變字符集的函數(shù)CONVERT(s USING cs)函數(shù)將字符串s的字符集變成cs孝常。
6 數(shù)據(jù)查詢
SQL 主要語(yǔ)句可以劃分為一下3類(lèi)
DDL:數(shù)據(jù)定義語(yǔ)言，這些語(yǔ)句定義不同的數(shù)據(jù)段蚓哩、數(shù)據(jù)庫(kù)构灸、表、列岸梨、索引等數(shù)據(jù)庫(kù)對(duì)象喜颁。常用語(yǔ)句關(guān)鍵字主要包括create,drop,alter等
DML:數(shù)據(jù)操作語(yǔ)句，用于添加曹阔、刪除半开、更新和查詢數(shù)據(jù)庫(kù)記錄，并檢查數(shù)據(jù)完整性赃份。常用語(yǔ)句關(guān)鍵字主要包括 insert,delete,update和select等寂拆。
DCL數(shù)據(jù)控制語(yǔ)句，用于控制不同數(shù)據(jù)段直接的許可和訪問(wèn)級(jí)別的語(yǔ)句芥炭。這些語(yǔ)句定義了數(shù)據(jù)庫(kù)漓库、表、字段园蝠、用戶的訪問(wèn)權(quán)限和安全級(jí)別渺蒿。主要 的語(yǔ)句包括關(guān)鍵字grant、revoke等
單表查詢
MySQL數(shù)據(jù)庫(kù)中查詢數(shù)據(jù)通用的 SELECT 語(yǔ)法：
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N] [ OFFSET M]
1 查詢語(yǔ)句中你可以使用一個(gè)或者多個(gè)表彪薛，表之間使用逗號(hào)(,)分割茂装，并使用WHERE語(yǔ)句來(lái)設(shè)定查詢條件。
2 SELECT 命令可以讀取一條或者多條記錄善延。
3 你可以使用星號(hào)（*）來(lái)代替其他字段少态，SELECT語(yǔ)句會(huì)返回表的所有字段數(shù)據(jù)
4 你可以使用 WHERE 語(yǔ)句來(lái)包含任何條件。
5 你可以使用 LIMIT 屬性來(lái)設(shè)定返回的記錄數(shù)易遣。
6 你可以通過(guò)OFFSET指定SELECT語(yǔ)句開(kāi)始查詢的數(shù)據(jù)偏移量彼妻。默認(rèn)情況下偏移量為0。
查詢字段
SELECT * FROM fruits;
SELECT f_id, s_id, f_name, f_price FROM fruits;
SELECT f_name FROM fruits;
查詢指定記錄
SELECT 字段名1, 字段名2,..., 字段名n
FROM 表名 WHERE 查詢條件 ;
例子：
查詢價(jià)格為10.2元的水果的名稱(chēng)
SELECT f_name, f_price FROM fruits WHERE f_price=10.2;
查詢名稱(chēng)為"apple"的水果的價(jià)格
SELECT f_name, f_price FROM fruits WHERE f_name='apple';
查詢價(jià)格小于10的水果的名稱(chēng)
SELECT f_name, f_price FROM fruits WHERE f_price<10.00;
帶IN關(guān)鍵字的查詢
查詢s_id為101和102的記錄
SELECT s_id, f_name, f_price FROM fruits
WHERE s_id IN (101, 102);
查詢所有s_id不等于101也不等于102的記錄
SELECT s_id, f_name, f_price FROM fruits
WHERE s_id NOT IN (101, 102)
ORDER BY f_name;
帶BETWEEN AND 的范圍查詢
查詢價(jià)格在2.00元到10.20元之間的水果名稱(chēng)和價(jià)格
SELECT f_name, f_price FROM fruits
WHERE f_price BETWEEN 2.00 AND 10.20;
查詢價(jià)格在2.00元到10.20元之外的水果的名稱(chēng)和價(jià)格
SELECT f_name, f_price FROM fruits
WHERE f_price NOT BETWEEN 2.00 AND 10.20;
帶LIKE的字符匹配查詢
'%' 匹配任意長(zhǎng)度的字符,甚至包括零字符
查找所有以'b'開(kāi)頭的水果
SELECT f_id, f_name FROM fruits
WHERE f_name LIKE 'b%';
在fruits表中,查詢f_name中包含'g'的記錄
SELECT f_id, f_name FROM fruits
WHERE f_name LIKE '%g%';
查詢以'b'開(kāi)頭,并以'y'結(jié)尾的水果名稱(chēng)
SELECT f_name FROM fruits
WHERE f_name LIKE 'b%y';
查詢空值
查詢customers表中c_email為空的記錄c_id,c_name和c_email字段值
SELECT c_id, c_name, c_email FROM customers
WHERE c_email IS NULL;
查詢customers表中c_email不為空的記錄的c_id, c_name, c_email字段值
SELECT c_id, c_name, c_email FROM customers
WHERE c_email IS NOT NULL;
帶AND OR的多條件查詢
在fruits表中查詢s_id=101,并且f_price大于等于5的水果價(jià)格和名稱(chēng)
SELECT f_id, f_price, f_name FROM fruits
WHERE s_id='101' AND f_price >= 5;
查詢結(jié)果不重復(fù)
SELECT DISTINCT 字段名 FROM 表名
查詢fruits表中s_id字段的值,返回s_id字段且不得重復(fù)
SELECT DISTINCT s_id FROM fruits;
分組查詢
group by
(1) group by的含義:將查詢結(jié)果按照1個(gè)或多個(gè)字段進(jìn)行分組豆茫，字段值相同的為一組
(2) group by可用于單個(gè)字段分組侨歉，也可用于多個(gè)字段分組
select * from employee group by sex;
根據(jù)sex字段來(lái)分組，sex字段的全部值只有兩個(gè)('男'和'女')揩魂，所以分為了兩組
當(dāng)group by單獨(dú)使用時(shí)幽邓，只顯示出每組的第一條記錄
所以group by單獨(dú)使用時(shí)的實(shí)際意義不大
group by + group_concat()
(1)?group_concat(字段名)可以作為一個(gè)輸出字段來(lái)使用， concat： 合并火脉，連接
(2) 表示分組之后送浊，根據(jù)分組結(jié)果，使用group_concat()來(lái)放置每一組的某字段的值的集合
select sex,group_concat(name) from employee group by sex;
group by + 集合函數(shù)
(1) 通過(guò)group_concat()的啟發(fā)漾唉，我們既然可以統(tǒng)計(jì)出每個(gè)分組的某字段的值的集合故俐，那么我們也可以通過(guò)集合函數(shù)來(lái)對(duì)這個(gè)"值的集合"做一些操作
分別統(tǒng)計(jì)性別為男/女的人年齡平均值
select sex,avg(age) from employee group by sex;
select sex,count(sex) from employee group by sex;
group by + having
(1) having 條件表達(dá)式：用來(lái)分組查詢后指定一些條件來(lái)輸出查詢結(jié)果
(2) having作用和where一樣，但having只能用于group by
select sex,count(sex) from employee group by sex having count(sex)>2;
-group by + with rollup
(1) with rollup的作用是：在最后新增一行，來(lái)記錄當(dāng)前列里所有記錄的總和
select sex,group_concat(age) from employee group by sex with rollup;
+------+-------------------+
| sex | group_concat(age) |
+------+-------------------+
| 女 | 24 |
| 男 | 26,25,15 |
| NULL | 24,26,25,15 |
+------+-------------------+
使用LIMIT限制查詢記錄數(shù)量
LIMIT [位置偏移量,] 行數(shù)
顯示fruits表查詢結(jié)果的前4行
SELECT * FROM fruits LIMIT 4;
聚合函數(shù)查詢
COUNT()函數(shù)
查詢customers表中總行數(shù)
SELECT COUNT(*) AS cust_num
FROM customers;
SUM()函數(shù)
在orderitems表中查詢30005號(hào)訂單一共購(gòu)買(mǎi)的水果總量
SELECT SUM(quantity) AS items_total
FROM orderitems
WHERE o_num=30005;
AVG()函數(shù)
在fruits表中,查詢s_id=103的供應(yīng)商的水果價(jià)格的平均值
SELECT AVG(f_price) AS avg_total FROM fruits WHERE s_id=103;
MAX()函數(shù)
在fruits表中查找市場(chǎng)上價(jià)格最高的水果值
SELECT MAX(f_price) AS max_price FROM fruits;
MIN()函數(shù)
在fruits表中查找市場(chǎng)上價(jià)格最低的水果值
SELECT MIN(f_price) AS min_price FROM fruits;
連接查詢
mysql常用連接
INNER JOIN（內(nèi)連接,或等值連接）：獲取兩個(gè)表中字段匹配關(guān)系的記錄。
LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒(méi)有對(duì)應(yīng)匹配的記錄兵睛。
RIGHT JOIN（右連接）： 與 LEFT JOIN 相反，用于獲取右表所有記錄窥浪，即使左表沒(méi)有對(duì)應(yīng)匹配的記錄祖很。
語(yǔ)句：
1 INNER JOIN
SELECT a.name,a.age,b.address FROM name_age a INNER JOIN name_address b WHERE|ON a.name=b.name;
2 LEFT JOIN
SELECT a.name,a.age,b.address FROM name_age a LEFT JOIN name_address b ON a.name=b.name;
3 RIGHT JOIN
SELECT a.name,a.age,b.address FROM name_age a RIGHT JOIN name_address b ON a.name=b.name;
子查詢
合并查詢結(jié)果
定義表和字段的別名
使用正則表達(dá)式查詢
實(shí)踐：使用聚合函數(shù)sum對(duì)學(xué)生成績(jī)進(jìn)行匯總
MySQL對(duì)查詢結(jié)果排序
從表中查詢出來(lái)的數(shù)據(jù)，可能是無(wú)序的漾脂，或者其排列順序表示用戶期望的 假颇。 使用ORDER BY對(duì)查詢結(jié)果進(jìn)行排序
SELECT 字段名1，字段名2骨稿，……
FROM 表名
ORDER BY 字段名1 [ASC|DESC]笨鸡，字段名2[ASC|DESC]……
參數(shù)說(shuō)明
指定的字段名1、字段名2坦冠，是對(duì)查詢結(jié)果排序的依據(jù)
參數(shù)ASC形耗，表示按照升序進(jìn)行排序 ascend
參數(shù)DESC，表示按照降序進(jìn)行排序 descend
默認(rèn)情況下辙浑，按照ASC方式激涤，升序進(jìn)行排序
SELECT * FROM student ORDER BY grade DESC;
MySQL中，指定按照多個(gè)字段判呕，對(duì)查詢結(jié)果進(jìn)行排序
比如倦踢，查詢student表中所有記錄，按照gender和grade字段進(jìn)行排序侠草，在排序過(guò)程中辱挥，會(huì)先按照gender字段進(jìn)行排序，如果遇到gender字段相同的記錄边涕，再把這些記錄晤碘，按照grade字段進(jìn)行排序
SELECT * FROM student ORDER BY gender ASC, grade DESC;
MySQL開(kāi)發(fā)中常用的查詢語(yǔ)句總結(jié)
1、查詢數(shù)值型數(shù)據(jù):
SELECT * FROM tb_name WHERE sum > 100;
查詢謂詞:>,=,<,<>,!=,!>,!<,=>,=<
2功蜓、查詢字符串
SELECT * FROM tb_stu WHERE sname = ‘Cricode.com’
SELECT * FROM tb_stu WHERE sname like ‘Uncle%Too’
SELECT * FROM tb_stu WHERE sname like ‘%程序員’
SELECT * FROM tb_stu WHERE sname like ‘%PHP%’
3园爷、查詢?nèi)掌谛蛿?shù)據(jù)
SELECT * FROM tb_stu WHERE date = ’2011-04-08′
注:不同數(shù)據(jù)庫(kù)對(duì)日期型數(shù)據(jù)存在差異: ：
(1)MySQL: SELECT * from tb_name WHERE birthday = ’2011-04-08′
(2)SQL Server: SELECT * from tb_name WHERE birthday = ’2011-04-08′
(3)Access:SELECT * from tb_name WHERE birthday = #2011-04-08#
4、查詢邏輯型數(shù)據(jù)
SELECT * FROM tb_name WHERE type = ‘T’
SELECT * FROM tb_name WHERE type = ‘F’
邏輯運(yùn)算符:and or not
5霞赫、查詢非空數(shù)據(jù)
SELECT * FROM tb_name WHERE address <>” order by addtime desc
注:<>相當(dāng)于PHP中的!=
6腮介、利用變量查詢數(shù)值型數(shù)據(jù)
SELECT * FROM tb_name WHERE id = ‘$_POST[text]‘
注:利用變量查詢數(shù)據(jù)時(shí)肥矢，傳入SQL的變量不必用引號(hào)括起來(lái)
7端衰、利用變量查詢字符串?dāng)?shù)據(jù)
SELECT * FROM tb_name WHERE name LIKE ‘%$_POST[name]%’
完全匹配的方法”%%”表示可以出現(xiàn)在任何位置
8叠洗、查詢前n條記錄(LIMIT n 等價(jià)于 LIMIT 0,n)
SELECT * FROM tb_name LIMIT 0,$N;
limit語(yǔ)句與其他語(yǔ)句，如order by等語(yǔ)句聯(lián)合使用旅东，會(huì)使用SQL語(yǔ)句千變?nèi)f化灭抑，使程序非常靈活
SELECT * FROM table LIMIT 5,10; // 檢索記錄行 6-15
SELECT * FROM table LIMIT 95,-1; // 檢索記錄行 96-last.
9、查詢后n條記錄
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n
10抵代、查詢從指定位置開(kāi)始的n條記錄
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n
注意:數(shù)據(jù)的id是從0開(kāi)始的
11腾节、查詢統(tǒng)計(jì)結(jié)果中的前n條記錄
SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num
12、查詢指定時(shí)間段的數(shù)據(jù)
SELECT 要查找的字段 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 終止值
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18
13康吵、按月查詢統(tǒng)計(jì)數(shù)據(jù)
SELECT * FROM tb_stu WHERE month(date) = ‘$_POST[date]‘ ORDER BY date ;
注：SQL語(yǔ)言中提供了如下函數(shù)含长，利用這些函數(shù)可以很方便地實(shí)現(xiàn)按年只泼、月、日進(jìn)行查詢
year(data):返回data表達(dá)式中的公元年分所對(duì)應(yīng)的數(shù)值
month(data):返回data表達(dá)式中的月分所對(duì)應(yīng)的數(shù)值
day(data):返回data表達(dá)式中的日期所對(duì)應(yīng)的數(shù)值
14啰挪、查詢大于指定條件的記錄
SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;
15罪既、查詢結(jié)果不顯示重復(fù)記錄
SELECT DISTINCT 字段名 FROM 表名 WHERE 查詢條件
注:SQL語(yǔ)句中的DISTINCT必須與WHERE子句聯(lián)合使用虽风，否則輸出的信息不會(huì)有變化 ,且字段不能用代替
16漾肮、NOT與謂詞進(jìn)行組合條件的查詢
(1)NOT BERWEEN … AND … 對(duì)介于起始值和終止值間的數(shù)據(jù)時(shí)行查詢 可改成 <起始值 AND >終止值
(2)IS NOT NULL 對(duì)非空值進(jìn)行查詢
(3)IS NULL 對(duì)空值進(jìn)行查詢
(4)NOT IN 該式根據(jù)使用的關(guān)鍵字是包含在列表內(nèi)還是排除在列表外厂抖，指定表達(dá)式的搜索，搜索表達(dá)式可以是常量或列名克懊，而列名可以是一組常量忱辅，但更多情況下是子查詢
17、顯示數(shù)據(jù)表中重復(fù)的記錄和記錄條數(shù)
SELECT name,age,count(
) ,age FROM tb_stu WHERE age = ’19′ group by date
18谭溉、對(duì)數(shù)據(jù)進(jìn)行降序/升序查詢
SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段 DESC 降序
SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段 ASC 升序
注:對(duì)字段進(jìn)行排序時(shí)若不指定排序方式墙懂，則默認(rèn)為ASC升序
19、對(duì)數(shù)據(jù)進(jìn)行多條件查詢
SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段1 ASC 字段2 DESC …
注意:對(duì)查詢信息進(jìn)行多條件排序是為了共同限制記錄的輸出扮念，一般情況下损搬，由于不是單一條件限制，所以在輸出效果上有一些差別柜与。
20巧勤、對(duì)統(tǒng)計(jì)結(jié)果進(jìn)行排序
函數(shù)SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可實(shí)現(xiàn)對(duì)字段的求和，函數(shù)中為ALL時(shí)為所有該字段所有記錄求和,若為DISTINCT則為該字段所有不重復(fù)記錄的字段求和
如：SELECT name,SUM(price) AS sumprice FROM tb_price GROUP BY name
SELECT * FROM tb_name ORDER BY mount DESC,price ASC
21弄匕、單列數(shù)據(jù)分組統(tǒng)計(jì)
SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC
注:當(dāng)分組語(yǔ)句group by排序語(yǔ)句order by同時(shí)出現(xiàn)在SQL語(yǔ)句中時(shí)颅悉，要將分組語(yǔ)句書(shū)寫(xiě)在排序語(yǔ)句的前面，否則會(huì)出現(xiàn)錯(cuò)誤迁匠。
22签舞、多列數(shù)據(jù)分組統(tǒng)計(jì)
多列數(shù)據(jù)分組統(tǒng)計(jì)與單列數(shù)據(jù)分組統(tǒng)計(jì)類(lèi)似
SELECT?，SUM(字段1字段2) AS (新字段1) FROM 表名 GROUP BY 字段 ORDER BY 新字段1 DESC
SELECT id,name,SUM(price*num) AS sumprice FROM tb_price GROUP BY pid ORDER BY sumprice DESC
注：group by語(yǔ)句后面一般為不是聚合函數(shù)的數(shù)列柒瓣，即不是要分組的列儒搭。
23、多表分組統(tǒng)計(jì)
SELECT a.name,AVG(a.price),b.name,AVG(b.price) FROM tb_demo058 AS a,tb_demo058_1 AS b WHERE a.id=b.id GROUP BY b.type;
MySQL查詢結(jié)果復(fù)制到新表的方法(更新芙贫、插入)
1搂鲫、如果t2表中存在score值，將score更新到t1表中磺平。方法如下：
UPDATE t1,t2
SET t1.score = t2.score
WHERE t1.id = t2.id AND t2.score IS NOT NULL
這就是將查詢結(jié)果作為條件更新另一張表魂仍，當(dāng)然拐辽，t2也可以是更為復(fù)雜的一個(gè)查詢結(jié)果而不是一個(gè)具體的表。
2擦酌、將t1表的username更新至t2表俱诸，將t2表的score更新至t1表。方法如下：
UPDATE t1,t2
SET t1.score = t2.score,t2.username = t1.username
WHERE t1.id = t2.id
這個(gè)方法其實(shí)跟上面的方法類(lèi)似赊舶，可以同時(shí)更新兩個(gè)表的數(shù)據(jù)睁搭，即做表部分?jǐn)?shù)據(jù)的互相復(fù)制、更新笼平。
3园骆、將t2表的查詢結(jié)果插入到t1表中。方法如下：
INSERT INTO t1(id,username,score)
SELECT t2.id,t2.username,t2.score FROM t2 where t2.username = 'lucy'
前面兩種方式是更新表的記錄寓调，這種方式是插入一條新的記錄锌唾。其實(shí)，從腳本可以看出夺英，這個(gè)方法就是將查詢和插入兩個(gè)步驟合二為一晌涕。
核心技術(shù)
7 索引
MySQL索引概述與分類(lèi)
索引是在存儲(chǔ)引擎層實(shí)現(xiàn)的而不是在服務(wù)器層。
MyISAM存儲(chǔ)引擎使用前綴壓縮技術(shù)存儲(chǔ)表記錄痛悯，InnoDB存儲(chǔ)引擎用原數(shù)據(jù)的格式進(jìn)行存儲(chǔ)渐排。
MyISAM存儲(chǔ)引擎通過(guò)數(shù)據(jù)的物理位置引用被索引的行，InnoDB存儲(chǔ)引擎通過(guò)主鍵引用被索引的行灸蟆。
MySQL索引類(lèi)型：
1.普通索引
2.唯一索引
3.主鍵索引
4.組合索引
5.全文索引
語(yǔ)法
CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key]index_name[asc|desc]
1.unique|fulltext為可選參數(shù)驯耻，分別表示唯一索引、全文索引
2.index和key為同義詞炒考，兩者作用相同可缚，用來(lái)指定創(chuàng)建索引
3.col_name為需要?jiǎng)?chuàng)建索引的字段列，該列必須從數(shù)據(jù)表中該定義的多個(gè)列中選擇
4.index_name指定索引的名稱(chēng)斋枢，為可選參數(shù)帘靡，如果不指定，默認(rèn)col_name為索引值
5.length為可選參數(shù)瓤帚，表示索引的長(zhǎng)度描姚，只有字符串類(lèi)型的字段才能指定索引長(zhǎng)度
6.asc或desc指定升序或降序的索引值存儲(chǔ)
尖括號(hào)，用于分隔字符串戈次，字符串為語(yǔ)法元素的名稱(chēng)轩勘，SQL語(yǔ)言的非終結(jié)符。
[ ] 方括號(hào)表示規(guī)則中的可選元素怯邪。方括號(hào)中的規(guī)則部分可以明確指定也可以省略绊寻。
{ } 花括號(hào)聚集規(guī)則中的元素。在花括號(hào)中的規(guī)則部分必須明確指定。
. . . 省略號(hào)表明在規(guī)則中省略號(hào)應(yīng)用的元素可能被重復(fù)多次澄步。
創(chuàng)建索引
CREATE INDEX可對(duì)表增加普通索引或UNIQUE索引冰蘑。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
刪除索引
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
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.唯一索引
與前面的普通索引類(lèi)似祠肥，不同的就是：索引列的值必須唯一，但允許有空值梯皿。如果是組合索引仇箱，則列值的組合必須唯一。它有以下幾種創(chuàng)建方式：
（1）創(chuàng)建唯一索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))
（2）修改表結(jié)構(gòu)
ALTER table mytable ADD UNIQUE [indexName] (username(length))
（3）創(chuàng)建表的時(shí)候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE indexName (username(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)建表的時(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 ,
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)
1.索引不會(huì)包含有null值的列
2.使用短索引
3.索引列排序
4.不推薦使用like操作
5.不要在列上進(jìn)行計(jì)算
6.不使用not in操作
8 存儲(chǔ)過(guò)程
一. 存儲(chǔ)過(guò)程的定義：
存儲(chǔ)過(guò)程（Stored Procedure）是在大型數(shù)據(jù)庫(kù)系統(tǒng)中烘贴，一組為了完成特定功能的SQL 語(yǔ)句集禁添，經(jīng)編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，用戶通過(guò)指定存儲(chǔ)過(guò)程的名字并給出參數(shù)（如果該存儲(chǔ)過(guò)程帶有參數(shù)）來(lái)執(zhí)行它桨踪。
二. 存儲(chǔ)過(guò)程的優(yōu)點(diǎn)：
簡(jiǎn)化應(yīng)用開(kāi)發(fā)人員的工作老翘。
增強(qiáng)安全性。
減少數(shù)據(jù)在數(shù)據(jù)庫(kù)和應(yīng)用服務(wù)器之間的傳輸锻离。
創(chuàng)建存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
創(chuàng)建存儲(chǔ)過(guò)程
1.進(jìn)入mysql命令行
mysql>
2.用delimiter命令來(lái)把語(yǔ)句定界符從 ;變?yōu)?/铺峭。這樣就允許在程序體用;定界符傳遞到服務(wù)器，而不是被mysql自己來(lái)解釋汽纠。
mysql> delimiter //
3.創(chuàng)建存儲(chǔ)過(guò)程
mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;
mysql> CALL simpleproc(@a);
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a;
創(chuàng)建存儲(chǔ)函數(shù)
建立自定義函數(shù)的過(guò)程如下：
1.進(jìn)入mysql命令行
mysql>
2.用delimiter命令來(lái)把語(yǔ)句定界符從 ;變?yōu)?/卫键。這樣就允許在程序體用;定界符傳遞到服務(wù)器，而不是被mysql自己來(lái)解釋虱朵。
mysql> delimiter //
3.創(chuàng)建自定義函數(shù)
mysql>CREATE FUNCTION hashDiff( s1 varchar(16), s2 varchar(16))
->RETURNS INT
->BEGIN
->DECLARE diff, x INT;
->SET diff =0;
->SET x = 0;
->WHILE (x < 16 ) DO
->SET x = x+1;
->if SUBSTRING(s1, x,1)<>SUBSTRING(s2, x,1) then
->set diff=diff+ 1;
->end if;
->END WHILE;
->RETURN diff;
->END
->//
mysql>select * from test t where hashDiff(t.hashcode,'ff9880f0f680ceff') < 5;
流程控制語(yǔ)句
條件控制
IF條件：條件為真莉炉，執(zhí)行
CASE條件：匹配到，執(zhí)行
循環(huán)控制
WHILE循環(huán)：先判斷后執(zhí)行
REPEAT循環(huán)：先執(zhí)行后判斷
LOOP循環(huán)(死循環(huán))
LEAVE語(yǔ)句(離開(kāi))
ITERATE語(yǔ)句：迭代碴犬，再次循環(huán)
RETURN語(yǔ)句：返回
注意：MySQL不支持FOR循環(huán)
一絮宁、條件控制：if語(yǔ)句、case語(yǔ)句
1翅敌、IF語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE difference(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> IF p1 > p2 THEN SET p3 = 1;
-> ELSEIF p1= p2 THEN SET p3 = 2;
-> ELSE SET p3 = 3;
-> END IF;
-> END $$
mysql> DELIMITER ;
mysql> call difference(12,56,@ax);
mysql> select @ax;
2羞福、CASE語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1()
-> BEGIN
-> DECLARE v INT DEFAULT 3;
-> CASE v
-> WHEN 2 THEN SELECT v;
-> WHEN 3 THEN SELECT 0;
-> ELSE
-> BEGIN
-> END
-> END CASE;
-> END $$
mysql> DELIMITER ;
mysql> call p1();
檢索型case語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
DECLARE diff, x INT;
->SET diff =0;
->SET x = 0;
->WHILE (x < 16 ) DO
->SET x = x+1;
->if SUBSTRING(s1, x,1)<>SUBSTRING(s2, x,1) then
->set diff=diff+ 1;
->end if;
->END WHILE;
->RETURN diff;
->END
->//
mysql>select * from test t where hashDiff(t.hashcode,'ff9880f0f680ceff') < 5;
> 流程控制語(yǔ)句
條件控制
IF條件：條件為真，執(zhí)行
CASE條件：匹配到蚯涮，執(zhí)行
循環(huán)控制
WHILE循環(huán)：先判斷后執(zhí)行
REPEAT循環(huán)：先執(zhí)行后判斷
LOOP循環(huán)(死循環(huán))
LEAVE語(yǔ)句(離開(kāi))
ITERATE語(yǔ)句：迭代治专，再次循環(huán)
RETURN語(yǔ)句：返回
注意：MySQL不支持FOR循環(huán)
>> 一、條件控制：if語(yǔ)句遭顶、case語(yǔ)句
1张峰、IF語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE difference(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> IF p1 > p2 THEN SET p3 = 1;
-> ELSEIF p1= p2 THEN SET p3 = 2;
-> ELSE SET p3 = 3;
-> END IF;
-> END $$
mysql> DELIMITER ;
mysql> call difference(12,56,@ax);
mysql> select @ax;
>> 2、CASE語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1()
-> BEGIN
-> DECLARE v INT DEFAULT 3;
-> CASE v
-> WHEN 2 THEN SELECT v;
-> WHEN 3 THEN SELECT 0;
-> ELSE
-> BEGIN
-> END
-> END CASE;
-> END $$
mysql> DELIMITER ;
mysql> call p1();
檢索型case語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> CASE
-> WHEN p1>p2 THEN SET p3=1;
-> WHEN p1=p2 THEN SET p3=2;
-> ELSE SET p3 = 3;
-> END CASE;
-> END$$
mysql> DELIMITER ;
select * from test t where hashDiff(t.hashcode,'ff9880f0f680ceff') < 5;
> 流程控制語(yǔ)句
條件控制
IF條件：條件為真棒旗，執(zhí)行
CASE條件：匹配到喘批，執(zhí)行
循環(huán)控制
WHILE循環(huán)：先判斷后執(zhí)行
REPEAT循環(huán)：先執(zhí)行后判斷
LOOP循環(huán)(死循環(huán))
LEAVE語(yǔ)句(離開(kāi))
ITERATE語(yǔ)句：迭代撩荣，再次循環(huán)
RETURN語(yǔ)句：返回
注意：MySQL不支持FOR循環(huán)
>> 一、條件控制：if語(yǔ)句饶深、case語(yǔ)句
1餐曹、IF語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE difference(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> IF p1 > p2 THEN SET p3 = 1;
-> ELSEIF p1= p2 THEN SET p3 = 2;
-> ELSE SET p3 = 3;
-> END IF;
-> END $$
mysql> DELIMITER ;
mysql> call difference(12,56,@ax);
mysql> select @ax;
>> 2、CASE語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1()
-> BEGIN
-> DECLARE v INT DEFAULT 3;
-> CASE v
-> WHEN 2 THEN SELECT v;
-> WHEN 3 THEN SELECT 0;
-> ELSE
-> BEGIN
-> END
-> END CASE;
-> END $$
mysql> DELIMITER ;
mysql> call p1();
檢索型case語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> CASE
-> WHEN p1>p2 THEN SET p3=1;
-> WHEN p1=p2 THEN SET p3=2;
-> ELSE SET p3 = 3;
-> END CASE;
-> END$$
mysql> DELIMITER ;
mysql> call p2(123,321,@ax);
mysql> select @ax;
二敌厘， 循環(huán)控制：while循環(huán)台猴、repeat循環(huán)、loop循環(huán)俱两、leave語(yǔ)句饱狂、iterate語(yǔ)句
Tips：循環(huán)體結(jié)構(gòu)
①條件
②SQL語(yǔ)句體
③程序體里面需要對(duì)條件中的變量進(jìn)行處理
1、WHILE循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE do_while(x int)
-> BEGIN
-> DECLARE v INT DEFAULT 5;
-> set v=x;
-> WHILE v>0 DO
-> select v;
-> SET v=v-1;
-> END WHILE;
-> END$$
mysql> DELIMITER ;
mysql> call do_while(2);
2宪彩、REPEAT循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x=0;
-> REPEAT
-> SET @x = @x + 1;
DELIMITER ;
mysql> call difference(12,56,@ax);
mysql> select @ax;
>> 2休讳、CASE語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1()
-> BEGIN
-> DECLARE v INT DEFAULT 3;
-> CASE v
-> WHEN 2 THEN SELECT v;
-> WHEN 3 THEN SELECT 0;
-> ELSE
-> BEGIN
-> END
-> END CASE;
-> END $$
mysql> DELIMITER ;
mysql> call p1();
檢索型case語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> CASE
-> WHEN p1>p2 THEN SET p3=1;
-> WHEN p1=p2 THEN SET p3=2;
-> ELSE SET p3 = 3;
-> END CASE;
-> END$$
mysql> DELIMITER ;
mysql> call p2(123,321,@ax);
mysql> select @ax;
------
>> 二， 循環(huán)控制：while循環(huán)尿孔、repeat循環(huán)俊柔、loop循環(huán)、leave語(yǔ)句纳猫、iterate語(yǔ)句
Tips：循環(huán)體結(jié)構(gòu)
①條件
②SQL語(yǔ)句體
③程序體里面需要對(duì)條件中的變量進(jìn)行處理
1婆咸、WHILE循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE do_while(x int)
-> BEGIN
-> DECLARE v INT DEFAULT 5;
-> set v=x;
-> WHILE v>0 DO
-> select v;
-> SET v=v-1;
-> END WHILE;
-> END$$
mysql> DELIMITER ;
mysql> call do_while(2);
2竹捉、REPEAT循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x=0;
-> REPEAT
-> SET @x = @x + 1;
-> UNTIL @x > p1 END REPEAT;
-> END$$
select @ax;
>> 2芜辕、CASE語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p1()
-> BEGIN
-> DECLARE v INT DEFAULT 3;
-> CASE v
-> WHEN 2 THEN SELECT v;
-> WHEN 3 THEN SELECT 0;
-> ELSE
-> BEGIN
-> END
-> END CASE;
-> END $$
mysql> DELIMITER ;
mysql> call p1();
檢索型case語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> CASE
-> WHEN p1>p2 THEN SET p3=1;
-> WHEN p1=p2 THEN SET p3=2;
-> ELSE SET p3 = 3;
-> END CASE;
-> END$$
mysql> DELIMITER ;
mysql> call p2(123,321,@ax);
mysql> select @ax;
------
>> 二， 循環(huán)控制：while循環(huán)块差、repeat循環(huán)侵续、loop循環(huán)、leave語(yǔ)句憨闰、iterate語(yǔ)句
Tips：循環(huán)體結(jié)構(gòu)
①條件
②SQL語(yǔ)句體
③程序體里面需要對(duì)條件中的變量進(jìn)行處理
1状蜗、WHILE循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE do_while(x int)
-> BEGIN
-> DECLARE v INT DEFAULT 5;
-> set v=x;
-> WHILE v>0 DO
-> select v;
-> SET v=v-1;
-> END WHILE;
-> END$$
mysql> DELIMITER ;
mysql> call do_while(2);
2、REPEAT循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x=0;
-> REPEAT
-> SET @x = @x + 1;
-> UNTIL @x > p1 END REPEAT;
-> END$$
mysql> DELIMITER ;
mysql> CALL dorepeat(1000);
mysql> SELECT @x;
3鹉动、LOOP循環(huán)
mysql> delimiter $$
DECLARE v INT DEFAULT 3;
-> CASE v
-> WHEN 2 THEN SELECT v;
-> WHEN 3 THEN SELECT 0;
-> ELSE
-> BEGIN
-> END
-> END CASE;
-> END $$
mysql> DELIMITER ;
mysql> call p1();
檢索型case語(yǔ)句
mysql> DELIMITER $$
mysql> CREATE PROCEDURE p2(
-> IN p1 INTEGER,
-> IN p2 INTEGER,
-> OUT p3 INTEGER)
-> BEGIN
-> CASE
-> WHEN p1>p2 THEN SET p3=1;
-> WHEN p1=p2 THEN SET p3=2;
-> ELSE SET p3 = 3;
-> END CASE;
-> END$$
mysql> DELIMITER ;
mysql> call p2(123,321,@ax);
mysql> select @ax;
------
>> 二轧坎， 循環(huán)控制：while循環(huán)、repeat循環(huán)泽示、loop循環(huán)缸血、leave語(yǔ)句、iterate語(yǔ)句
Tips：循環(huán)體結(jié)構(gòu)
①條件
②SQL語(yǔ)句體
③程序體里面需要對(duì)條件中的變量進(jìn)行處理
1械筛、WHILE循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE do_while(x int)
-> BEGIN
-> DECLARE v INT DEFAULT 5;
-> set v=x;
-> WHILE v>0 DO
-> select v;
-> SET v=v-1;
-> END WHILE;
-> END$$
mysql> DELIMITER ;
mysql> call do_while(2);
2捎泻、REPEAT循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x=0;
-> REPEAT
-> SET @x = @x + 1;
-> UNTIL @x > p1 END REPEAT;
-> END$$
mysql> DELIMITER ;
mysql> CALL dorepeat(1000);
mysql> SELECT @x;
3、LOOP循環(huán)
mysql> delimiter $$
mysql> create procedure wait_s(in wait_seconds int)
-> begin
-> declare end_time datetime default now() + interval wait_seconds second; #interval是間隔類(lèi)型關(guān)鍵字
-> wait_loop:loop
-> if now() > end_time
-> then
-> leave wait_loop; #leave語(yǔ)句表離開(kāi)
-> end if;
-> end loop wait_loop;
-> end $$
mysql> delimiter ;
mysql> call wait_x(10);
……等10秒帚屉，結(jié)束……
BEGIN
-> CASE
-> WHEN p1>p2 THEN SET p3=1;
-> WHEN p1=p2 THEN SET p3=2;
-> ELSE SET p3 = 3;
-> END CASE;
-> END$$
mysql> DELIMITER ;
mysql> call p2(123,321,@ax);
mysql> select @ax;
------
>> 二诅妹， 循環(huán)控制：while循環(huán)、repeat循環(huán)劝萤、loop循環(huán)闯狱、leave語(yǔ)句煞赢、iterate語(yǔ)句
Tips：循環(huán)體結(jié)構(gòu)
①條件
②SQL語(yǔ)句體
③程序體里面需要對(duì)條件中的變量進(jìn)行處理
1、WHILE循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE do_while(x int)
-> BEGIN
-> DECLARE v INT DEFAULT 5;
-> set v=x;
-> WHILE v>0 DO
-> select v;
-> SET v=v-1;
-> END WHILE;
-> END$$
mysql> DELIMITER ;
mysql> call do_while(2);
2哄孤、REPEAT循環(huán)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x=0;
-> REPEAT
-> SET @x = @x + 1;
-> UNTIL @x > p1 END REPEAT;
-> END$$
mysql> DELIMITER ;
mysql> CALL dorepeat(1000);
mysql> SELECT @x;
3耕驰、LOOP循環(huán)
mysql> delimiter $$
mysql> create procedure wait_s(in wait_seconds int)
-> begin
-> declare end_time datetime default now() + interval wait_seconds second; #interval是間隔類(lèi)型關(guān)鍵字
-> wait_loop:loop
-> if now() > end_time
-> then
-> leave wait_loop; #leave語(yǔ)句表離開(kāi)
-> end if;
-> end loop wait_loop;
-> end $$
mysql> delimiter ;
mysql> call wait_x(10);
……等10秒，結(jié)束……
4录豺、LEAVE語(yǔ)句
作用：用來(lái)退出帶標(biāo)簽的語(yǔ)句塊或者循環(huán)
用處：用在 BEGIN ... END中或者循環(huán)中 (LOOP, REPEAT, WHILE)
mysql> DELIMITER $$
mysql> CREATE PROCEDURE small_exit(OUT p1 INTEGER,OUT p2 INTEGER)
-> BEGIN
-> SET p1 = 1;
-> SET p2 = 1;
-> block1:BEGIN
-> LEAVE block1; #離開(kāi)塊block1
-> SET p2 = 3; #已離開(kāi)朦肘，不執(zhí)行
-> END block1;
-> SET p1 = 4; #執(zhí)行
-> END$$
mysql> DELIMITER ;
mysql> call small_exit(@r1,@r2);
mysql> select @r1,@r2;
5、ITERATE語(yǔ)句
ITERATE label;
只能出現(xiàn)在循環(huán)LOOP双饥、REPEAT和WHILE 中(有標(biāo)簽)
含義：跳出本次循環(huán)媒抠，開(kāi)始一次新的循環(huán)
mysql> delimiter $$
mysql> CREATE PROCEDURE do_iterate(p1 INT)
-> BEGIN
-> label_1: LOOP
-> SET p1 = p1 + 1;
-> IF p1 < 10 THEN ITERATE label_1; #開(kāi)始下一次循環(huán)
-> END IF;
-> LEAVE label_1;
-> END LOOP label_1;
-> SET @x = p1;
-> END$$
mysql> delimiter ;
mysql> call do_iterate(1);
mysql> select @x;
變量
自定義變量：DECLARE a INT ; SET a=100; 可用以下語(yǔ)句代替：DECLARE a INT DEFAULT 100;
變量分為用戶變量和系統(tǒng)變量，系統(tǒng)變量又分為會(huì)話和全局級(jí)變量
用戶變量：用戶變量名一般以@開(kāi)頭咏花，濫用用戶變量會(huì)導(dǎo)致程序難以理解及管理
1趴生、 在mysql客戶端使用用戶變量
mysql> SELECT 'Hello World' into @x;
mysql> SELECT @x;
mysql> SET @y='Goodbye Cruel World';
mysql> select @y;
mysql> SET @z=1+2+3;
mysql> select @z;
2、 在存儲(chǔ)過(guò)程中使用用戶變量
mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql> SET @greeting='Hello';
mysql> CALL GreetWorld( );
3昏翰、 在存儲(chǔ)過(guò)程間傳遞全局范圍的用戶變量
mysql> CREATE PROCEDURE p1( ) SET @last_procedure='p1';
mysql> CREATE PROCEDURE p2( ) SELECT CONCAT('Last procedure was ',@last_procedure);
mysql> CALL p1( );
mysql> CALL p2( );
調(diào)用存儲(chǔ)過(guò)程和存儲(chǔ)函數(shù)
基本語(yǔ)法：call sp_name()
注意：存儲(chǔ)過(guò)程名稱(chēng)后面必須加括號(hào)苍匆，哪怕該存儲(chǔ)過(guò)程沒(méi)有參數(shù)傳遞
查看存儲(chǔ)過(guò)程和函數(shù)
查詢數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程和函數(shù)
select?name?from mysql.proc where db = 'xx' and?type?= 'PROCEDURE' //存儲(chǔ)過(guò)程
select?name?from mysql.proc where db = 'xx' and?type?= 'FUNCTION' //函數(shù)
show procedure status; //存儲(chǔ)過(guò)程
show function status; //函數(shù)
修改存儲(chǔ)過(guò)程和函數(shù)
使用 ALTER 語(yǔ)句可以修改存儲(chǔ)過(guò)程或函數(shù)的特性，語(yǔ)法格式如下：
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
其中棚菊，sp_name 參數(shù)表示存儲(chǔ)過(guò)程或函數(shù)的名稱(chēng)浸踩；characteristic 參數(shù)指定存儲(chǔ)函數(shù)的特性，可能的取值有:
CONTAINS SQL 表示子程序包含 SQL 語(yǔ)句统求，但不包含讀或?qū)憯?shù)據(jù)的語(yǔ)句检碗。
NO SQL 表示了程序中不包含 SQL 語(yǔ)句。
READS SQL DATA 表示子程序中包含讀數(shù)據(jù)的語(yǔ)句码邻。
MODIFIES SQL DATA 表示子程序中包含寫(xiě)數(shù)據(jù)的語(yǔ)句折剃。
SQL SECURITY { DEFINER | INVOKER } 指明誰(shuí)有權(quán)限來(lái)執(zhí)行。
DEFINER 表示只有定義者自己才能夠執(zhí)行像屋。
INVOKER 表示調(diào)用者可以執(zhí)行怕犁。
COMMENT 'string' 表示注釋信息。
mysql> ALTER PROCEDURE CountProc1
-> MODIFIES SQL DATA
-> SQL SECURITY INVOKER;
刪除存儲(chǔ)過(guò)程和函數(shù)
刪除存儲(chǔ)過(guò)程和函數(shù)可以使用 DROP 語(yǔ)句己莺，其語(yǔ)法結(jié)構(gòu)如下:
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] Sp_name
sp_name 為要移除的存儲(chǔ)過(guò)程或函數(shù)的名稱(chēng)奏甫。
IF EXISTS 子句是 MySQL 的一個(gè)擴(kuò)展。如果程序或函數(shù)不存儲(chǔ)篇恒，它可以防止發(fā)生錯(cuò)誤扶檐，產(chǎn)生一個(gè)用 SHOW WARNINGS 查看的警告。
mysql> DROP PROCEDURE CountProc;
Query OK, 0 rows affected (0.50 sec)
mysql> DROP FUNCTION CountProc2;
Query OK, 0 rows affected (0.00 sec）
捕獲存儲(chǔ)過(guò)程中的錯(cuò)誤
實(shí)踐： 使用存儲(chǔ)過(guò)程實(shí)現(xiàn)用戶注冊(cè)
9 觸發(fā)器(trigger)
觸發(fā)器(trigger)：監(jiān)視某種情況胁艰，并觸發(fā)某種操作款筑。
觸發(fā)器創(chuàng)建語(yǔ)法四要素：1.監(jiān)視地點(diǎn)(table) 2.監(jiān)視事件(insert/update/delete) 3.觸發(fā)時(shí)間(after/before) 4.觸發(fā)事件(insert/update/delete)
觸發(fā)器是一個(gè)特殊的存儲(chǔ)過(guò)程智蝠，不同的是存儲(chǔ)過(guò)程要用CALL來(lái)調(diào)用，而觸發(fā)器不需要使用CALL
也不需要手工啟動(dòng)奈梳，只要當(dāng)一個(gè)預(yù)定義的事件發(fā)生的時(shí)候杈湾，就會(huì)被MYSQL自動(dòng)調(diào)用。
創(chuàng)建MySQL觸發(fā)器
語(yǔ)法
create trigger triggerName
after/before insert/update/delete on 表名
for each row #這句話在mysql是固定的
begin
sql語(yǔ)句;
end;
創(chuàng)建一個(gè)單執(zhí)行語(yǔ)句的觸發(fā)器
CREATE TABLE account(acct_num INT ,amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @SUM=@SUM+new.amount;
首先創(chuàng)建一個(gè)account表攘须，表中有兩個(gè)字段漆撞，分別為：acct_num字段（定義為int類(lèi)型）
amount字段（定義成浮點(diǎn)類(lèi)型）；其次創(chuàng)建一個(gè)名為ins_sum的觸發(fā)器于宙，觸發(fā)的條件是向數(shù)據(jù)表account插入數(shù)據(jù)之前浮驳，對(duì)新插入的amount字段值進(jìn)行求和計(jì)算
create trigger tg1
after insert on tb_o
for each row
begin
update tb_g set num=num-3 where id=1;
end;
查看觸發(fā)器
SHOW TRIGGERS;
應(yīng)用觸發(fā)器
刪除觸發(fā)器
DROP TRIGGER [schema_name.]trigger_name
drop trigger t_afterinsert_on_tab1;
10 視圖
視圖的概念與作用
視圖是指計(jì)算機(jī)數(shù)據(jù)庫(kù)中的視圖，是一個(gè)虛擬表捞魁，其內(nèi)容由查詢定義至会。同真實(shí)的表一樣，視圖包含一系列帶有名稱(chēng)的列和行數(shù)據(jù)谱俭。但是奉件，視圖并不在數(shù)據(jù)庫(kù)中以存儲(chǔ)的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來(lái)自由定義視圖的查詢所引用的表昆著，并且在引用視圖時(shí)動(dòng)態(tài)生成县貌。簡(jiǎn)單的來(lái)說(shuō)視圖是由其定義結(jié)果組成的表。
通俗的講凑懂，視圖就是一條SELECT語(yǔ)句執(zhí)行后返回的結(jié)果集煤痕。所以我們?cè)趧?chuàng)建視圖的時(shí)候，主要的工作就落在創(chuàng)建這條SQL查詢語(yǔ)句上征候。
作用：
方便操作杭攻，增強(qiáng)可讀性祟敛；
更加安全疤坝。
創(chuàng)建視圖
CREATE VIEW view_name AS 查詢語(yǔ)句
//說(shuō)明：和創(chuàng)建表一樣，視圖名不能和表名馆铁、也不能和其他視圖名重名跑揉。視圖的功能實(shí)際就是封裝了復(fù)雜的查詢語(yǔ)句。
use zhaojd_test; //選擇一個(gè)自己創(chuàng)建的庫(kù)
create table t_product( //創(chuàng)建表
id int primary key,
pname varchar(20),
price decimal(8,2)
);
insert into t_product values(1,'apple',6.5); //向表中插入數(shù)據(jù)
insert into t_product values(2,'orange',3); //向表中插入數(shù)據(jù)
create view view_product as select id,name from t_product; //創(chuàng)建視圖
select * from view_product;
查看視圖
SHOW TABLES;顯示表的同時(shí)也顯示視圖名
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] 查看視圖詳細(xì)信息
DESCRIBE | DESC viewname; 查看視圖定義信息
修改視圖
CREATE OR REPLACE VIEW語(yǔ)句修改視圖：
實(shí)現(xiàn)思路就是：先刪除同名的視圖埠巨，然后再根據(jù)新的需求創(chuàng)建新的視圖即可历谍。
DROP VIEW view_name;
CREATE VIEW view_name as 查詢語(yǔ)句;
但是如果每次修改視圖，都是先刪除視圖辣垒，然后再次創(chuàng)建一個(gè)同名的視圖望侈，則顯得非常麻煩。于是MySQL提供了更方便的實(shí)現(xiàn)替換的創(chuàng)建視圖的語(yǔ)法勋桶，完整語(yǔ)法為：
CREATE OR REPLACE VIEW view_name as 查詢語(yǔ)句脱衙；
ALTER語(yǔ)句修改視圖：
語(yǔ)法為：
ALTER VIEW view_name as 查詢語(yǔ)句;
更新視圖
是指通過(guò)視圖侥猬，來(lái)更新、插入捐韩、刪除基本表中的數(shù)據(jù)
因?yàn)橥诉耄晥D是一個(gè)虛擬表，其中沒(méi)有數(shù)據(jù)荤胁，所以瞧预，當(dāng)通過(guò)視圖更新數(shù)據(jù)時(shí)，其實(shí)仅政，是在更新基本表中的數(shù)據(jù)垢油，如果對(duì)視圖中的數(shù)據(jù)進(jìn)行增加，或者刪除操作時(shí)圆丹，實(shí)際上是在對(duì)其基本表中的數(shù)據(jù)秸苗，進(jìn)行增加或者刪除操作.
UPDATE語(yǔ)句
使用UPDATE語(yǔ)句更新視圖
MySQL中，可以使用UPDATE語(yǔ)句运褪，更新視圖
更新view_stu視圖中，chinese字段對(duì)應(yīng)的數(shù)據(jù)值秸讹，將字段值改為100
UPDATE view_stu SET chinese=100;
刪除視圖
在刪除視圖時(shí)首先要確保擁有刪除視圖的權(quán)限棕诵。
DROP VIEW view_name [,view_name] ......
實(shí)踐：使用MySQL視圖查詢學(xué)生信息表
11 事務(wù)
MySQL事物概述（原子性 一致性 孤立性 持久性）
Mysql事務(wù)主要用來(lái)處理數(shù)據(jù)量大、數(shù)據(jù)復(fù)雜度高的數(shù)據(jù)操作牧抵，最經(jīng)典的使用場(chǎng)景是銀行的轉(zhuǎn)賬：需要先從銀行賬戶A中取出錢(qián)笛匙，然后再存入銀行賬戶B中，如果中間出現(xiàn)問(wèn)題犀变，而沒(méi)有事務(wù)的保證妹孙，那么就會(huì)出現(xiàn)B收不到錢(qián)，而A支出錢(qián)又回不到自己的賬戶的嚴(yán)重問(wèn)題获枝，那么有了事務(wù)機(jī)制蠢正，這個(gè)問(wèn)題就解決了。
數(shù)據(jù)庫(kù)事務(wù)（Database Transaction）,是指作為單個(gè)邏輯工作單元執(zhí)行的一系列操作省店，要么完全執(zhí)行嚣崭，要么完全地不執(zhí)行蜘拉。
ACID
事務(wù)必須具備ACID四個(gè)特性
原子性（Atomicity）（）
一致性（Consistency） (一切都是正確的狀態(tài)）
隔離性(Isolation)
持久性(Durability)
MySQL事物控制
事務(wù)控制語(yǔ)句：
BEGIN或START TRANSACTION；顯式地開(kāi)啟一個(gè)事務(wù)有鹿；
COMMIT旭旭；也可以使用COMMIT WORK，不過(guò)二者是等價(jià)的葱跋。COMMIT會(huì)提交事務(wù)持寄，并使已對(duì)數(shù)據(jù)庫(kù)進(jìn)行的所有修改稱(chēng)為永久性的；
ROLLBACK娱俺；有可以使用ROLLBACK WORK稍味，不過(guò)二者是等價(jià)的≤恚回滾會(huì)結(jié)束用戶的事務(wù)模庐，并撤銷(xiāo)正在進(jìn)行的所有未提交的修改；
SAVEPOINT identifier油宜；SAVEPOINT允許在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)掂碱，一個(gè)事務(wù)中可以有多個(gè)SAVEPOINT；
RELEASE SAVEPOINT identifier慎冤；刪除一個(gè)事務(wù)的保存點(diǎn)疼燥，當(dāng)沒(méi)有指定的保存點(diǎn)時(shí)，執(zhí)行該語(yǔ)句會(huì)拋出一個(gè)異常蚁堤；
ROLLBACK TO identifier醉者；把事務(wù)回滾到標(biāo)記點(diǎn)；
SET TRANSACTION披诗；用來(lái)設(shè)置事務(wù)的隔離級(jí)別撬即。InnoDB存儲(chǔ)引擎提供事務(wù)的隔離級(jí)別有READ UNCOMMITTED、READ COMMITTED呈队、REPEATABLE READ和SERIALIZABLE剥槐。
MYSQL 事務(wù)處理主要有兩種方法：
1、用 BEGIN, ROLLBACK, COMMIT來(lái)實(shí)現(xiàn)
BEGIN 開(kāi)始一個(gè)事務(wù)
ROLLBACK 事務(wù)回滾
COMMIT 事務(wù)確認(rèn)
2掂咒、直接用 SET 來(lái)改變 MySQL 的自動(dòng)提交模式:
SET AUTOCOMMIT=0 禁止自動(dòng)提交
SET AUTOCOMMIT=1 開(kāi)啟自動(dòng)提交
begin; # 開(kāi)始事務(wù)
insert into runoob_transaction_test value(5);
insert into runoob_transaction_test value(6);
commit; # 提交事務(wù)| rollback; # 回滾
12 系統(tǒng)管理
MySQL系統(tǒng)管理概述
數(shù)據(jù)目錄的位置
數(shù)據(jù)目錄的結(jié)構(gòu)
MySQL服務(wù)器如何提供對(duì)數(shù)據(jù)的訪問(wèn)
MySQL數(shù)據(jù)庫(kù)在文件系統(tǒng)里如何表示
MySQL數(shù)據(jù)表如何在文件系統(tǒng)里表示
SQL語(yǔ)句如何映射為數(shù)據(jù)表文件操作
操作系統(tǒng)對(duì)數(shù)據(jù)庫(kù)和數(shù)據(jù)表命名的限制
數(shù)據(jù)目錄的結(jié)構(gòu)對(duì)系統(tǒng)性能的影響
MySQL狀態(tài)文件和日志文件
高級(jí)技術(shù)
13 數(shù)據(jù)庫(kù)備份與還原
數(shù)據(jù)備份
備份常用操作基本命令
1才沧、備份命令mysqldump格式
格式：mysqldump -h主機(jī)名 -P端口 -u用戶名 -p密碼 –database 數(shù)據(jù)庫(kù)名 > 文件名.sql
2、備份MySQL數(shù)據(jù)庫(kù)為帶刪除表的格式
備份MySQL數(shù)據(jù)庫(kù)為帶刪除表的格式绍刮，能夠讓該備份覆蓋已有數(shù)據(jù)庫(kù)而不需要手動(dòng)刪除原有數(shù)據(jù)庫(kù)。
mysqldump --add-drop-table -uusername -ppassword -database databasename > backupfile.sql
3挨摸、直接將MySQL數(shù)據(jù)庫(kù)壓縮備份
mysqldump -hhostname -uusername -ppassword -database databasename | gzip > backupfile.sql.gz
4孩革、備份MySQL數(shù)據(jù)庫(kù)某個(gè)(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sq
5、同時(shí)備份多個(gè)MySQL數(shù)據(jù)庫(kù)
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql僅僅備6得运、僅備份份數(shù)據(jù)庫(kù)結(jié)構(gòu)
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
7膝蜈、備份服務(wù)器上所有數(shù)據(jù)庫(kù)
mysqldump –all-databases > allbackupfile.sql
8锅移、還原MySQL數(shù)據(jù)庫(kù)的命令
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
9、還原壓縮的MySQL數(shù)據(jù)庫(kù)
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
10饱搏、將數(shù)據(jù)庫(kù)轉(zhuǎn)移到新服務(wù)器
mysqldump -uusername -ppassword databasename | mysql –host=...?-C databasename
11非剃、--master-data 和--single-transaction
在mysqldump中使用--master-data=2，會(huì)記錄binlog文件和position的信息 推沸。--single-transaction會(huì)將隔離級(jí)別設(shè)置成repeatable-commited
12备绽、導(dǎo)入數(shù)據(jù)庫(kù)
常用source命令，用use進(jìn)入到某個(gè)數(shù)據(jù)庫(kù)鬓催，mysql>source d:\test.sql肺素，后面的參數(shù)為腳本文件。
13宇驾、查看binlog日志
查看binlog日志可用用命令 mysqlbinlog binlog日志名稱(chēng)|more
14倍靡、general_log
General_log記錄數(shù)據(jù)庫(kù)的任何操作，查看general_log 的狀態(tài)和位置可以用命令show variables like "general_log%" ,開(kāi)啟general_log可以用命令set global general_log=on
增量備份
小量的數(shù)據(jù)庫(kù)可以每天進(jìn)行完整備份课舍，因?yàn)檫@也用不了多少時(shí)間塌西，但當(dāng)數(shù)據(jù)庫(kù)很大時(shí)，就不太可能每天進(jìn)行一次完整備份了筝尾，這時(shí)候就可以使用增量備份雨让。增量備份的原理就是使用了mysql的binlog志。
1忿等、首先做一次完整備份：
mysqldump -h10.6.208.183 -utest2 -p123 -P3310 --single-transaction --master-data=2 test>test.sql這時(shí)候就會(huì)得到一個(gè)全備文件test.sql
在sql文件中我們會(huì)看到：
-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;是指?jìng)浞莺笏械母膶?huì)保存到bin-log.000002二進(jìn)制文件中栖忠。
2、在test庫(kù)的t_student表中增加兩條記錄贸街，然后執(zhí)行flush logs命令庵寞。這時(shí)將會(huì)產(chǎn)生一個(gè)新的二進(jìn)制日志文件bin-log.000003，bin-log.000002則保存了全備過(guò)后的所有更改薛匪，既增加記錄的操作也保存在了bin-log.00002中捐川。
3、再在test庫(kù)中的a表中增加兩條記錄逸尖，然后誤刪除t_student表和a表古沥。a中增加記錄的操作和刪除表a和t_student的操作都記錄在bin-log.000003中。
數(shù)據(jù)還原
1娇跟、首先導(dǎo)入全備數(shù)據(jù)
mysql -h10.6.208.183 -utest2 -p123 -P3310 < test.sql岩齿，也可以直接在mysql命令行下面用source導(dǎo)入
2、恢復(fù)bin-log.000002
mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123 -P3310
3苞俘、恢復(fù)部分 bin-log.000003
在general_log中找到誤刪除的時(shí)間點(diǎn)盹沈，然后更加對(duì)應(yīng)的時(shí)間點(diǎn)到bin-log.000003中找到相應(yīng)的position點(diǎn)，需要恢復(fù)到誤刪除的前面一個(gè)position點(diǎn)吃谣。
可以用如下參數(shù)來(lái)控制binlog的區(qū)間
--start-position 開(kāi)始點(diǎn) --stop-position 結(jié)束點(diǎn)
--start-date 開(kāi)始時(shí)間 --stop-date 結(jié)束時(shí)間
找到恢復(fù)點(diǎn)后乞封，既可以開(kāi)始恢復(fù)做裙。
mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123 -P3310
數(shù)據(jù)庫(kù)遷移
表的導(dǎo)出和導(dǎo)入
1 導(dǎo)出
MySQL數(shù)據(jù)庫(kù)中的數(shù)據(jù)可以導(dǎo)出成.sql文本文件、xml文件或html文件肃晚。
1.1 用SELECT…INTO OUTFILE導(dǎo)出
SELECT columnlist FROM table WHERE condition INTO OUTFILE 'filename' [OPTION]
--OPTIONS 選項(xiàng)
FIELDS TERMINATED BY 'value' /設(shè)置字段之間分隔符锚贱，單個(gè)或多個(gè)字符，默認(rèn)為'\t'/
FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /設(shè)置字段包圍分隔符关串，單個(gè)字符/
FIELDS ESCAPED BY 'value' /如何寫(xiě)入或讀取特殊字符拧廊，單個(gè)字符/
LINES STARTING BY 'value' /每行數(shù)據(jù)開(kāi)頭的字符，單個(gè)或多個(gè)/
LINES TERMINATED BY 'value' /每行數(shù)據(jù)結(jié)尾的字符悍缠，單個(gè)或多個(gè)/
（1）例子
SELECT * FROM test.person INTO OUTFILE "C:/person0.txt";
person0.txt
1 Green 21 student
2 Suse 2 dancer
3 Mary 24 Musician
4 Willam 20 student
5 Laura 0
6 Evans 27 secretary
7 Dale 22 student
8 Edison 28 cook
9 Harry 21 student
（2）例子
SELECT * FROM test.person INTO OUTFILE "C:/person1.txt"
FIELDS
TERMINATED BY ' , '
ENCLOSED BY ' " '
ESCAPED BY ' ' '
LINES
TERMINATED BY ' \r\n ';
person1.txt
"1","Green","21","student"
"2","Suse","2","dancer"
"3","Mary","24","Musician"
"4","Willam","20","student"
"5","Laura","0",""
"6","Evans","27","secretary"
"7","Dale","22","student"
"8","Edison","28","cook"
"9","Harry","21","student"
在下面的例子中卦绣，生成一個(gè)文件，各值用逗號(hào)隔開(kāi)飞蚓。這種格式可以被許多程序使用滤港。
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;
1.2 用mysqldump命令導(dǎo)出
mysqldump -T path -u root -p dbname [tables][OPTIONS]
--OPTION 選項(xiàng)
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
（1）例子
mysqldump -T C:/ test person -u root -p
語(yǔ)句執(zhí)行后生成兩個(gè)文件，person.sql（包含CREATE語(yǔ)句等）和person.txt（包含數(shù)據(jù)信息）趴拧。
1.3 用mysql命令導(dǎo)出
mysql -u root -p --execute="SELECT 語(yǔ)句" dbname>filename.txt
相比mysqldump溅漾，mysql工具導(dǎo)出的結(jié)果可讀性更強(qiáng)。
（1）例子
mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt
（2）例子著榴，使用- -vertical參數(shù)顯示結(jié)果
mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt
（3）例子添履，導(dǎo)出為html文件
mysql -u root -p --html --execute="SELECT * FROM person;" test > C:\person5.html
（4）例子，導(dǎo)出為xml文件
mysql -u root -p --xml --execute="SELECT * FROM person;" test > C:\person6.xml
2 導(dǎo)入
1.1 用LOAD DATA INFILE導(dǎo)入
LOAD DATA INFILE 'filename.txt' INTO TABLE tablename [OPTION][IGNORE number LINES]
--OPTIONS 選項(xiàng)
FIELDS TERMINATED BY 'value' /設(shè)置字段之間分隔符脑又，單個(gè)或多個(gè)字符暮胧，默認(rèn)為'\t'/
FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /設(shè)置字段包圍分隔符，單個(gè)字符/
FIELDS ESCAPED BY 'value' /如何寫(xiě)入或讀取特殊字符问麸，單個(gè)字符/
LINES STARTING BY 'value' /每行數(shù)據(jù)開(kāi)頭的字符往衷，單個(gè)或多個(gè)/
LINES TERMINATED BY 'value' /每行數(shù)據(jù)結(jié)尾的字符，單個(gè)或多個(gè)/
（1）例子
SELECT * FROM test.person INTO OUTFILE "C:/person0.txt";
use test;
DELETE FROM person;
LOAD DATA INFILE 'C:/person0.txt' INTO TABLE test.person;
Query OK, 9 rows affected
Records: 9 Deleted: 0 Skipped: 0 Warnings: 0
SELECT * FROM person; 查看導(dǎo)入的表
1.3 用mysqlimport命令導(dǎo)入
mysqlimport -u root -p dbname filename.txt [OPTIONS]
--OPTION 選項(xiàng)
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
--ignore-lines=n
實(shí)踐：導(dǎo)出XML文件
14 MySQL性能優(yōu)化
優(yōu)化簡(jiǎn)介
MySQL性能優(yōu)化就是通過(guò)合理安排資源严卖，調(diào)整系統(tǒng)參數(shù)使MySQL運(yùn)行更快席舍、更節(jié)省資源。MySQL性能優(yōu)化包括查詢速度優(yōu)化哮笆、更新速度優(yōu)化来颤、MySQL服務(wù)器優(yōu)化等。本篇博客將從查詢優(yōu)化稠肘、數(shù)據(jù)庫(kù)結(jié)構(gòu)優(yōu)化福铅、MySQL服務(wù)器優(yōu)化3個(gè)方面介紹。
MySQL數(shù)據(jù)庫(kù)優(yōu)化启具，一方面是找出系統(tǒng)瓶頸本讥，提高M(jìn)ySQL數(shù)據(jù)庫(kù)整體性能；另一方面需要合理的結(jié)構(gòu)設(shè)計(jì)和參數(shù)調(diào)整鲁冯，以提高用戶操作響應(yīng)速度拷沸；同時(shí)還要盡可能節(jié)省系統(tǒng)資源，以便系統(tǒng)可以提供更大負(fù)荷的服務(wù)薯演。例如撞芍，通過(guò)優(yōu)化文件系統(tǒng)，提高磁盤(pán)I\O的讀寫(xiě)書(shū)讀跨扮；通過(guò)優(yōu)化操作系統(tǒng)調(diào)度策略序无，提高M(jìn)ySQL在高負(fù)荷下的負(fù)載能力；優(yōu)化表結(jié)構(gòu)衡创、索引帝嗡、查詢語(yǔ)句等使查詢響應(yīng)更快.
在MySQL中使用SHOW STATUS語(yǔ)句查詢一些MySQL數(shù)據(jù)庫(kù)的性能，其語(yǔ)法為：SHOW STATUS LIKE 'value';
其中value是要查詢的參數(shù)值璃氢，一些常用的性能參數(shù)如下：
Connections：連接MySQL服務(wù)器的次數(shù)哟玷；
Uptime：MySQL服務(wù)器的上線時(shí)間；
Slow_queries：慢查詢次數(shù)一也；
Com_select：查詢操作的次數(shù)巢寡；
Com_insert：插入操作的次數(shù)；
Com_update：更新操作的次數(shù)椰苟；
Com_delete：刪除操作的次數(shù)抑月。
優(yōu)化查詢
通過(guò)對(duì)查詢語(yǔ)句的分析，可以了解查詢語(yǔ)句執(zhí)行情況舆蝴，找出查詢語(yǔ)句執(zhí)行的瓶頸谦絮，從而優(yōu)化查詢語(yǔ)句。MySQL中提供了EXPLAIN語(yǔ)句和DESCRIBE語(yǔ)句洁仗，用來(lái)分析查詢語(yǔ)句层皱。
EXPLAIN語(yǔ)句基本語(yǔ)法格式為：
EXPLAIN [EXTENDED] SELECT select_options
使用EXTENDED關(guān)鍵字，EXPLAIN語(yǔ)句將產(chǎn)生附加信息京痢。
select_options是SELECT語(yǔ)句的查詢選項(xiàng)奶甘，包括FROM WHERE子句等。通過(guò)該語(yǔ)句可以分析EXPLAIN后面的SELECT語(yǔ)句執(zhí)行情況祭椰，并且分析所查詢表的一些特征臭家。
優(yōu)化數(shù)據(jù)庫(kù)結(jié)構(gòu)
查詢高速緩存
優(yōu)化多表查詢
優(yōu)化表設(shè)計(jì)
15 MySQL數(shù)據(jù)庫(kù)安全技術(shù)
MySQL的基本安全和保護(hù)策略
用戶和權(quán)限管理
MySQL 默認(rèn)有個(gè)root用戶，但是這個(gè)用戶權(quán)限太大方淤，一般只在管理數(shù)據(jù)庫(kù)時(shí)候才用钉赁。如果在項(xiàng)目中要連接 MySQL 數(shù)據(jù)庫(kù)，則建議新建一個(gè)權(quán)限較小的用戶來(lái)連接携茂。
為 MySQL 創(chuàng)建一個(gè)新用戶：
CREATE USER username IDENTIFIED BY 'password';
以此用戶登陸的話你踩，會(huì)報(bào)錯(cuò)，因?yàn)槲覀冞€沒(méi)有為這個(gè)用戶分配相應(yīng)權(quán)限，分配權(quán)限的命令如下：
GRANT ALL PRIVILEGES ON?.?TO 'username'@'localhost' IDENTIFIED BY 'password';
授予username用戶在所有數(shù)據(jù)庫(kù)上的所有權(quán)限带膜。
如果此時(shí)發(fā)現(xiàn)剛剛給的權(quán)限太大了吩谦，如果我們只是想授予它在某個(gè)數(shù)據(jù)庫(kù)上的權(quán)限，那么需要切換到root 用戶撤銷(xiāo)剛才的權(quán)限，重新授權(quán)：
EVOKE ALL PRIVILEGES ON?.?FROM 'username'@'localhost';
GRANT ALL PRIVILEGES ON wordpress.* TO 'username'@'localhost' IDENTIFIED BY 'password';
甚至還可以指定該用戶只能執(zhí)行 select 和 update 命令：
GRANT SELECT, UPDATE ON wordpress.* TO 'username'@'localhost' IDENTIFIED BY 'password';
這樣一來(lái)，再次以u(píng)sername登陸 MySQL还惠，只有wordpress數(shù)據(jù)庫(kù)是對(duì)其可見(jiàn)的苹熏，并且如果你只授權(quán)它select權(quán)限，那么它就不能執(zhí)行delete 語(yǔ)句。
另外每當(dāng)調(diào)整權(quán)限后，通常需要執(zhí)行以下語(yǔ)句刷新權(quán)限：
FLUSH PRIVILEGES;
刪除剛才創(chuàng)建的用戶：
DROP USER username@localhost;
仔細(xì)上面幾個(gè)命令，可以發(fā)現(xiàn)不管是授權(quán)蠕趁，還是撤銷(xiāo)授權(quán)，都要指定響應(yīng)的host（即 @ 符號(hào)后面的內(nèi)容）辛馆，因?yàn)橐陨霞案衩顚?shí)際上都是在操作mysql 數(shù)據(jù)庫(kù)中的user表俺陋，可以用如下命令查看相應(yīng)用戶及對(duì)應(yīng)的host:
SELECT User, Host FROM user;
MySQL 賦予和撤銷(xiāo)用戶權(quán)限命令的簡(jiǎn)單格式可概括為：
GRANT 權(quán)限 ON 數(shù)據(jù)庫(kù)對(duì)象 TO 用戶
EVOKE 權(quán)限 ON 數(shù)據(jù)庫(kù)對(duì)象 FROM 用戶
MySQL權(quán)限表
MySQL服務(wù)器通過(guò)MySQL權(quán)限表來(lái)控制用戶對(duì)數(shù)據(jù)庫(kù)的訪問(wèn)，MySQL權(quán)限表存放在mysql數(shù)據(jù)庫(kù)里怀各，由mysql_install_db腳本初始化倔韭。這些MySQL權(quán)限表分別是user，db瓢对，table_priv寿酌，columns_priv和host。下面分別介紹一下這些表的結(jié)構(gòu)和內(nèi)容：
user權(quán)限表：記錄允許連接到服務(wù)器的用戶帳號(hào)信息硕蛹，里面的權(quán)限是全局級(jí)的醇疼。
db權(quán)限表：記錄各個(gè)帳號(hào)在各個(gè)數(shù)據(jù)庫(kù)上的操作權(quán)限。
table_priv權(quán)限表：記錄數(shù)據(jù)表級(jí)的操作權(quán)限法焰。
columns_priv權(quán)限表：記錄數(shù)據(jù)列級(jí)的操作權(quán)限秧荆。
host權(quán)限表：配合db權(quán)限表對(duì)給定主機(jī)上數(shù)據(jù)庫(kù)級(jí)操作權(quán)限作更細(xì)致的控制。這個(gè)權(quán)限表不受GRANT和REVOKE語(yǔ)句的影響埃仪。
查看用戶的授權(quán)：
mysql> show grants for tom;
GRANT 語(yǔ)法:
GRANT privileges (columns)
ON what
TO user IDENTIFIED BY "password"
WITH GRANT OPTION
權(quán)限列表:
ALTER: 修改表和索引乙濒。
CREATE: 創(chuàng)建數(shù)據(jù)庫(kù)和表。
DELETE: 刪除表中已有的記錄卵蛉。
DROP: 拋棄(刪除)數(shù)據(jù)庫(kù)和表颁股。
INDEX: 創(chuàng)建或拋棄索引。
INSERT: 向表中插入新行傻丝。
REFERENCE: 未用甘有。
SELECT: 檢索表中的記錄。
UPDATE: 修改現(xiàn)存表記錄葡缰。
FILE: 讀或?qū)懛?wù)器上的文件亏掀。
PROCESS: 查看服務(wù)器中執(zhí)行的線程信息或殺死線程忱反。
RELOAD: 重載授權(quán)表或清空日志、主機(jī)緩存或表緩存滤愕。
SHUTDOWN: 關(guān)閉服務(wù)器温算。
ALL: 所有權(quán)限，ALL PRIVILEGES同義詞该互。
USAGE: 特殊的 "無(wú)權(quán)限" 權(quán)限米者。
用 戶賬戶包括 "username" 和 "host" 兩部分韭畸，后者表示該用戶被允許從何地接入宇智。tom@'%' 表示任何地址，默認(rèn)可以省略胰丁。還可以是 "tom@192.168.1.%"随橘、"tom@%.abc.com" 等。數(shù)據(jù)庫(kù)格式為 db@table锦庸，可以是 "test." 或 ".*"机蔗，前者表示 test 數(shù)據(jù)庫(kù)的所有表，后者表示所有數(shù)據(jù)庫(kù)的所有表甘萧。
子句 "WITH GRANT OPTION" 表示該用戶可以為其他用戶分配權(quán)限萝嘁。
修改用戶口令：
1、root用戶修改普通用戶口令
set password for tom1=password('oracle');
flush privileges;
2扬卷、普通用戶修改自己密碼：
mysql -h 192.168.8.240 -utom1 -poracle
mysql> set password=password('tom1');
mysql> flush privileges;
刪除用戶
1牙言、回收用戶所有權(quán)限
revoke all on prod.* from tom2;
2、刪除用戶 drop user tom2;
摘要
創(chuàng)建用戶:
GRANT insert, update ON testdb.* TO user1@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
CREATE USER user2 IDENTIFIED BY 'password';
分配權(quán)限:
GRANT select ON testdb.* TO user2;
查看權(quán)限:
SHOW GRANTS FOR user1;
修改密碼:
SET PASSWORD FOR user1 = PASSWORD('newpwd');
SET PASSWORD = PASSWORD('newpwd');
移除權(quán)限:
REVOKE all ON?.?FROM user1;
刪除用戶:
DROP USER user1;
數(shù)據(jù)庫(kù)列表:
SHOW DATABASES;
數(shù)據(jù)表列表:
SHOW TABLES
當(dāng)前數(shù)據(jù)庫(kù):
SELECT DATABASE();
當(dāng)前用戶:
SELECT USER();
數(shù)據(jù)表結(jié)構(gòu):
DESCRIBE table1;
刷新權(quán)限:
FLUSH PRIVILEGES;
grant和revoke可以在幾個(gè)層次上控制訪問(wèn)權(quán)限
1怪得，整個(gè)服務(wù)器咱枉，使用 grant ALL 和revoke ALL
2，整個(gè)數(shù)據(jù)庫(kù)徒恋，使用on database.*
3蚕断，特點(diǎn)表，使用on database.table
4入挣，特定的列
5亿乳，特定的存儲(chǔ)過(guò)程
user表中host列的值的意義
% 匹配所有主機(jī)
localhost localhost不會(huì)被解析成IP地址，直接通過(guò)UNIXsocket連接
127.0.0.1 會(huì)通過(guò)TCP/IP協(xié)議連接径筏，并且只能在本機(jī)訪問(wèn)葛假；
::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
grant 普通數(shù)據(jù)用戶匠璧，查詢桐款、插入、更新夷恍、刪除 數(shù)據(jù)庫(kù)中所有表數(shù)據(jù)的權(quán)利魔眨。
grant select on testdb.* to common_user@’%’
grant insert on testdb.* to common_user@’%’
grant update on testdb.* to common_user@’%’
grant delete on testdb.* to common_user@’%’
或者媳维，用一條 MySQL 命令來(lái)替代：
grant select, insert, update, delete on testdb.* to common_user@’%’
grant 數(shù)據(jù)庫(kù)開(kāi)發(fā)人員，創(chuàng)建表遏暴、索引侄刽、視圖、存儲(chǔ)過(guò)程朋凉、函數(shù)州丹。。杂彭。等權(quán)限墓毒。
grant 創(chuàng)建、修改亲怠、刪除 MySQL 數(shù)據(jù)表結(jié)構(gòu)權(quán)限所计。
grant create on testdb.* to developer@’192.168.0.%’;
grant alter on testdb.* to developer@’192.168.0.%’;
grant drop on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 外鍵權(quán)限。
grant references on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 臨時(shí)表權(quán)限团秽。
grant create temporary tables on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 索引權(quán)限主胧。
grant index on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 視圖、查看視圖源代碼 權(quán)限习勤。
grant create view on testdb.* to developer@’192.168.0.%’;
grant show view on testdb.* to developer@’192.168.0.%’;
grant 操作 MySQL 存儲(chǔ)過(guò)程踪栋、函數(shù) 權(quán)限。
grant create routine on testdb.* to developer@’192.168.0.%’; -- now, can show procedure status
grant alter routine on testdb.* to developer@’192.168.0.%’; -- now, you can drop a procedure
grant execute on testdb.* to developer@’192.168.0.%’;
grant 普通 DBA 管理某個(gè) MySQL 數(shù)據(jù)庫(kù)的權(quán)限图毕。
grant all privileges on testdb to dba@’localhost’
其中夷都，關(guān)鍵字 “privileges” 可以省略。
grant 高級(jí) DBA 管理 MySQL 中所有數(shù)據(jù)庫(kù)的權(quán)限吴旋。
grant all on?.?to dba@’localhost’
MySQL grant 權(quán)限损肛，分別可以作用在多個(gè)層次上。
grant 作用在整個(gè) MySQL 服務(wù)器上：
grant select on?.?to dba@localhost; -- dba 可以查詢 MySQL 中所有數(shù)據(jù)庫(kù)中的表荣瑟。
grant all on?.?to dba@localhost; -- dba 可以管理 MySQL 中的所有數(shù)據(jù)庫(kù)
grant 作用在單個(gè)數(shù)據(jù)庫(kù)上：
grant select on testdb.* to dba@localhost; -- dba 可以查詢 testdb 中的表治拿。
grant 作用在單個(gè)數(shù)據(jù)表上：
grant select, insert, update, delete on testdb.orders to dba@localhost;
grant 作用在表中的列上：
grant select(id, se, rank) on testdb.apache_log to dba@localhost;
grant 作用在存儲(chǔ)過(guò)程、函數(shù)上：
grant execute on procedure testdb.pr_add to ’dba’@’localhost’
grant execute on function testdb.fn_add to ’dba’@’localhost’
注意：修改完權(quán)限以后 一定要刷新服務(wù)笆焰，或者重啟服務(wù)劫谅，刷新服務(wù)用：FLUSH PRIVILEGES;
MySQL數(shù)據(jù)庫(kù)安全技術(shù)常見(jiàn)問(wèn)題
16 MySQL日常管理
連接故障恢復(fù)
日志文件管理
日志文件記錄著mysql數(shù)據(jù)庫(kù)運(yùn)行期間發(fā)生的變化，如：mysql數(shù)據(jù)庫(kù)的客戶端連接狀況嚷掠、sql語(yǔ)句的執(zhí)行情況和錯(cuò)誤信息等捏检。當(dāng)數(shù)據(jù)庫(kù)遭到意外的損壞時(shí)，可以通過(guò)日志查看文件出錯(cuò)的原因不皆，并且可以通過(guò)日志進(jìn)行數(shù)據(jù)恢復(fù)贯城；也可以通過(guò)日志文件分析數(shù)據(jù)、優(yōu)化查詢等霹娄。Mysql日志管理機(jī)制比較完善能犯，它包含了以下幾種常見(jiàn)的日志文件鲫骗、分別為：錯(cuò)誤日志（-log-err）、查詢?nèi)罩荆?log）踩晶、二進(jìn)制日志（-log-bin）执泰、更新日志（-log-update）及慢查詢?nèi)罩荆?log-slow-queries）。
操作錯(cuò)誤日志
在mysql數(shù)據(jù)庫(kù)中渡蜻，錯(cuò)誤日志記錄著mysql服務(wù)器的啟動(dòng)和停止過(guò)程中的信息术吝、服務(wù)器在運(yùn)行過(guò)程中發(fā)生的故障和異常情況的相關(guān)信息、事件調(diào)度器運(yùn)行一個(gè)事件時(shí)產(chǎn)生的信息茸苇、在從服務(wù)器上啟動(dòng)服務(wù)器進(jìn)程時(shí)產(chǎn)生的信息等排苍。錯(cuò)誤日志記錄的并非全是錯(cuò)誤信息，如mysql如何啟動(dòng)InnoDB的表空間文件税弃、如何初始化自己的存儲(chǔ)引擎等信息也記錄在錯(cuò)誤日志中纪岁。
（1）啟動(dòng)錯(cuò)誤日志
錯(cuò)誤日志在默認(rèn)情況下是開(kāi)啟的，并且不能被禁止则果。錯(cuò)誤日志信息也可以自行配置，通過(guò)修改my.cnf文件即可漩氨。錯(cuò)誤日志所記錄的信息是可以通以log-error和log-warnings來(lái)定義的西壮，其中l(wèi)og-err定義是否啟用錯(cuò)誤日志的功能和錯(cuò)誤日志的位置，log-warning定義是否將警告信息也定義至錯(cuò)誤日志中叫惊。-log-error=[file-name]用來(lái)指定錯(cuò)誤日志的存放位置款青。如果沒(méi)有指定[file-name]，默認(rèn)hostname.err作為文件名霍狰，默認(rèn)存放在datadir目錄中抡草。
（2）查看錯(cuò)誤日志
Linux中直接使用vi或者gdit工具來(lái)查看
mysql> show variables like 'log_error';
（3）刪除錯(cuò)誤日志
管理員可以刪除很久之前的錯(cuò)誤日志，這樣可以保證mysql服務(wù)器上的硬盤(pán)空間蔗坯。通過(guò)show命令查看錯(cuò)誤文件所在位置康震，確認(rèn)可以刪除錯(cuò)誤日志后可以直接刪除文件。
操作二進(jìn)制日志
Mysql數(shù)據(jù)庫(kù)的二進(jìn)制文件是用來(lái)記錄所有用戶對(duì)數(shù)據(jù)庫(kù)的操作宾濒。當(dāng)數(shù)據(jù)庫(kù)發(fā)生意外時(shí)腿短，可以通過(guò)此文件查看在一定時(shí)間段內(nèi)用戶所做的操作，結(jié)合數(shù)據(jù)庫(kù)備份技術(shù)绘梦，即可實(shí)現(xiàn)用戶操作橘忱，使數(shù)據(jù)庫(kù)恢復(fù)。
（1）啟動(dòng)二進(jìn)制日志
默認(rèn)情況下是不開(kāi)啟二進(jìn)制日志文件的卸奉，通過(guò)命令查看
show variables like 'log_bin';
開(kāi)啟方式：在/etc/my.cnf下
（2）查看二進(jìn)制日志
show variables like 'log_bin';
show binary logs;
總結(jié)：開(kāi)啟二進(jìn)制文件可以實(shí)現(xiàn)如下幾個(gè)功能
①恢復(fù)（recovery）：某些數(shù)據(jù)的恢復(fù)需要二進(jìn)制日志钝诚，例如，在一個(gè)數(shù)據(jù)庫(kù)全備文件恢復(fù)后榄棵，用戶可以通過(guò)二進(jìn)制日志進(jìn)行point-in-time的恢復(fù)凝颇。
②復(fù)制（replication）：其原理與恢復(fù)類(lèi)似灵疮，通過(guò)復(fù)制和執(zhí)行二進(jìn)制日志使一臺(tái)遠(yuǎn)程的mysql數(shù)據(jù)庫(kù)與另一臺(tái)mysql數(shù)據(jù)庫(kù)進(jìn)行實(shí)時(shí)同步。
③審計(jì)（audit）：用戶可以通過(guò)二進(jìn)制日志中的信息來(lái)進(jìn)行審計(jì)圈暗，判斷是否有對(duì)數(shù)據(jù)庫(kù)進(jìn)行注入的攻擊壮韭。
（3）刪除二進(jìn)制日志
用reset master命令刪除所有日志，新日志從000001開(kāi)始編號(hào)
reset master;
（4）使用二進(jìn)制日志還原數(shù)據(jù)庫(kù)
操作慢查詢?nèi)罩?/p>
優(yōu)化mysql最重要的一部分工作就是先確定“有問(wèn)題”的查詢語(yǔ)句辑鲤。只有先找出這些查詢較慢的sql查詢盔腔，才可以進(jìn)一步分析原因并且優(yōu)化它。慢查詢?nèi)罩揪陀涗浟藞?zhí)行時(shí)間超過(guò)了特定時(shí)長(zhǎng)的查詢月褥，即記錄所有執(zhí)行時(shí)間超過(guò)最大sql執(zhí)行時(shí)間（long_query_time）或未使用索引的語(yǔ)句弛随。
（1）啟動(dòng)慢查詢?nèi)罩?/p>
默認(rèn)情況下，慢查詢?nèi)罩臼顷P(guān)閉的宁赤∫ㄍ福可以通過(guò)配置文件my.cnf來(lái)啟用
show variables like 'slow_%';
set global slow_query_log=on;
（2）刪除慢查詢?nèi)罩?/p>
set global slow_query_log=0;
MySQL服務(wù)器鏡像配置
MySQL服務(wù)器優(yōu)化配置
優(yōu)化MySQL服務(wù)器
運(yùn)行多個(gè)MySQL服務(wù)器
17 PHP訪問(wèn)MySQL數(shù)據(jù)庫(kù)
PHP語(yǔ)言概述
PHP操作MySQL數(shù)據(jù)庫(kù)的步驟
使用PHP操作MySQL數(shù)據(jù)庫(kù)
PHP管理MySQL數(shù)據(jù)庫(kù)中的數(shù)據(jù)
常見(jiàn)問(wèn)題與解決方法
MySQL與PHP的應(yīng)用實(shí)例---迷你日記
18其他小知識(shí)
在Mysql的命令行工具里面給select查詢加入一個(gè)\G參數(shù)。橫向的表結(jié)構(gòu)會(huì)轉(zhuǎn)為使用縱向表結(jié)構(gòu)輸出决左，利于閱讀愕够。
（1）select * from deal; ---橫行輸出
(2) select * from deal \G ; ---縱向輸出
\g 的作用是分號(hào)和在sql語(yǔ)句中寫(xiě)’;’是等效的
\G 的作用是將查到的結(jié)構(gòu)旋轉(zhuǎn)90度變成縱向
項(xiàng)目實(shí)戰(zhàn)
18 Java與MySQL數(shù)據(jù)庫(kù)——圖書(shū)管理系統(tǒng)
19 C語(yǔ)言與MySQL數(shù)據(jù)庫(kù)——圖書(shū)管理系統(tǒng)
需求分析
系統(tǒng)設(shè)計(jì)
數(shù)據(jù)庫(kù)設(shè)計(jì)
C語(yǔ)言開(kāi)發(fā)數(shù)據(jù)庫(kù)程序的流程
數(shù)據(jù)庫(kù)管理模塊設(shè)計(jì)
文件引用
變量和函數(shù)定義
管理模塊設(shè)計(jì)
小禮物走一走，來(lái)簡(jiǎn)書(shū)關(guān)注我
贊賞支持
? 著作權(quán)歸作者所有
寫(xiě)了 131865 字佛猛，被 20 人關(guān)注惑芭，獲得了 28 個(gè)喜歡
被以下專(zhuān)題收入，發(fā)現(xiàn)更多相似內(nèi)容
一继找、MySQL架構(gòu)與歷史 A.并發(fā)控制 1.共享鎖（shared lock遂跟，讀鎖）：共享的，相互不阻塞的 2.排他鎖（exclusive lock婴渡，寫(xiě)鎖）：排他的幻锁，一個(gè)寫(xiě)鎖會(huì)阻塞其他的寫(xiě)鎖和讀鎖 B.事務(wù) 1.事務(wù)ACID * 原子性（atomicity）一個(gè)事務(wù)必須被視為...
?ZyBlog
高級(jí)9、MySQL數(shù)據(jù)庫(kù)架構(gòu)边臼、優(yōu)化哄尔、原理詳解
一、MySQL架構(gòu)與歷史 A.并發(fā)控制 1.共享鎖（shared lock硼瓣，讀鎖）：共享的究飞，相互不阻塞的。 2.排他鎖（exclusive lock堂鲤，寫(xiě)鎖）：排他的亿傅，一個(gè)寫(xiě)鎖會(huì)阻塞其他的寫(xiě)鎖和讀鎖。 B.事務(wù) 1.事務(wù)ACID * 原子性（atomicity）一個(gè)事務(wù)必須被...
?xiaonian0430
《MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎(第2版)》書(shū)摘
MySQL技術(shù)內(nèi)幕：InnoDB存儲(chǔ)引擎(第2版) 姜承堯 第1章 MySQL體系結(jié)構(gòu)和存儲(chǔ)引擎 >> 在上述例子中使用了mysqld_safe命令來(lái)啟動(dòng)數(shù)據(jù)庫(kù)瘟栖，當(dāng)然啟動(dòng)MySQL實(shí)例的方法還有很多葵擎，在各種平臺(tái)下的方式可能又會(huì)有所不同。 >> 當(dāng)啟動(dòng)實(shí)例時(shí)半哟，MySQL數(shù)據(jù)庫(kù)...
?沉默劍士
pdf下載地址：Java面試寶典 第一章內(nèi)容介紹 20 第二章JavaSE基礎(chǔ) 21 一酬滤、Java面向?qū)ο?21 1. 面向?qū)ο蠖加心男┨匦砸约澳銓?duì)這些特性的理解 21 2. 訪問(wèn)權(quán)限修飾符public签餐、private、protected, 以及不寫(xiě)（默認(rèn)）時(shí)的區(qū)別(201...
?王震陽(yáng)
《高性能MySQL》&《MySQL技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎》筆記
《高性能MySQL》&《MySQL技術(shù)內(nèi)幕 InnoDB存儲(chǔ)引擎》筆記 第一章 MySQL架構(gòu)與歷史 MySQL的架構(gòu) 從上圖可以看出盯串，MySQL數(shù)據(jù)庫(kù)區(qū)別于其他數(shù)據(jù)庫(kù)的最重要的一個(gè)特點(diǎn)就是其插件式的表存儲(chǔ)引擎氯檐。需要注意的是，存儲(chǔ)引擎是基于表的体捏，而不是數(shù)據(jù)庫(kù)的（即同一個(gè)數(shù)據(jù)...
?xiaogmail
娘在家就在，娘不在几缭，每逢佳節(jié)不知去哪 今天清明時(shí)節(jié)雨紛紛 讓我思念故人 今天春回去看望東 讓北方下起了雨夾雪 今天你那里還好嗎 今天讓我回想往事 今天是踏青看故人 今天我想起了外婆和母親 讓我內(nèi)疚多少年 那年您唯一的心愿 我不懂您心聲 不懂事的我既然拒絕了您 今天 佳節(jié)清明...
?愛(ài)粉
人稍微活著多了點(diǎn)河泳，就真的希望有些資產(chǎn)，外在的或內(nèi)在的至少有一樣比別人強(qiáng)年栓，雖然現(xiàn)在一個(gè)人有自在的快樂(lè)拆挥，但過(guò)久了會(huì)覺(jué)得生活少了點(diǎn)味道，這是在與他人的觀察和交流中慢慢感覺(jué)到的某抓，欲求得一生平安更想得真情交融纸兔。
?一顆想變大的石子
Git的一些概念 1.本地倉(cāng)庫(kù) 工作區(qū)本地修改 暫存區(qū)本地修改可以放入暫存區(qū)，再批量提交暫存區(qū)到本地倉(cāng)庫(kù) HEADGit倉(cāng)庫(kù)在創(chuàng)建的時(shí)候會(huì)自動(dòng)給我們創(chuàng)建第一個(gè)分支master（既是分支名搪缨，又是指向改分支最新的一次提交）食拜，指向master的指針交HAED 2.遠(yuǎn)程倉(cāng)庫(kù)公共存放...
