觀其大綱 page 01

基礎(chǔ)知識

1 MySQL數(shù)據(jù)庫概要

2 簡單MySQL環(huán)境

3 數(shù)據(jù)的存儲和獲取

4 MySQL基本操作

5 函數(shù)

6 數(shù)據(jù)查詢

核心技術(shù)

7 索引

8 存儲過程

9 觸發(fā)器

10 視圖

11 事務(wù)

12 系統(tǒng)管理

高級技術(shù)

13 數(shù)據(jù)庫備份與還原

14 MySQL性能優(yōu)化

15 MySQL數(shù)據(jù)庫安全技術(shù)

16 MySQL日常管理

17 PHP訪問MySQL數(shù)據(jù)庫

項目實戰(zhàn)

18 Java與MySQL數(shù)據(jù)庫——圖書管理系統(tǒng)

19 C語言與MySQL數(shù)據(jù)庫——圖書管理系統(tǒng)

大綱細節(jié) page 02

基礎(chǔ)知識

1 MySQL數(shù)據(jù)庫概要

特性
應(yīng)用環(huán)境
管理軟件
學(xué)習(xí)方法

2 簡單MySQL環(huán)境

下載與安裝
啟動連接斷開停止
MySQL Workbench圖形化管理工具
phpMyAdmin圖形化管理工具

3 數(shù)據(jù)的存儲和獲取

MySQL存儲引擎
MySQL 數(shù)據(jù)類型
MySQL運算符

4 MySQL基本操作

數(shù)據(jù)庫操作

創(chuàng)建
查看
選擇
刪除

數(shù)據(jù)表操作

創(chuàng)建數(shù)據(jù)表
查看表結(jié)構(gòu)
修改表結(jié)構(gòu)
重命名表結(jié)構(gòu)
刪除表

MySQL語句操作

插入記錄
查詢數(shù)據(jù)庫記錄
修改記錄
刪除記錄
外鍵約束

5 函數(shù)

MySQL函數(shù)
數(shù)學(xué)函數(shù)（ABS FLOOR RAND PI TRUNCATE ROUND SQRT )
字符串函數(shù)( INSERT UPPER UCASE LEFT RTRIM SUBSTRING REVERSE FIELD)
日期和時間函數(shù)(CURDATE CURTIME NOW DATEDIFF ADDDATE SUBDATE)
條件判斷函數(shù)
系統(tǒng)信息函數(shù)
加密函數(shù)(PASSWORD MD5)
其他函數(shù)

6 數(shù)據(jù)查詢

基本查詢語句
單表查詢
聚合函數(shù)查詢
連接查詢
子查詢
合并查詢結(jié)果
定義表和字段的別名
使用正則表達式查詢
實踐：使用聚合函數(shù)sum對學(xué)生成績進行匯總

核心技術(shù)

7 索引

MySQL索引概述與分類
創(chuàng)建索引
刪除索引

8 存儲過程

創(chuàng)建存儲過程和存儲函數(shù)
流程控制語句
調(diào)用存儲過程和存儲函數(shù)
查看存儲過程和函數(shù)
修改存儲過程和函數(shù)
刪除存儲過程和函數(shù)
捕獲存儲過程中的錯誤
實踐： 使用存儲過程實現(xiàn)用戶注冊

9 觸發(fā)器

創(chuàng)建MySQL觸發(fā)器
查看觸發(fā)器
應(yīng)用觸發(fā)器
刪除觸發(fā)器

10 視圖

視圖的概念與作用
創(chuàng)建視圖
查看視圖
修改視圖
更新視圖
刪除視圖
實踐：使用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ù)器如何提供對數(shù)據(jù)的訪問
MySQL數(shù)據(jù)庫在文件系統(tǒng)里如何表示
MySQL數(shù)據(jù)表如何在文件系統(tǒng)里表示
SQL語句如何映射為數(shù)據(jù)表文件操作
操作系統(tǒng)對數(shù)據(jù)庫和數(shù)據(jù)表命名的限制
數(shù)據(jù)目錄的結(jié)構(gòu)對系統(tǒng)性能的影響
MySQL狀態(tài)文件和日志文件

高級技術(shù)

13 數(shù)據(jù)庫備份與還原

數(shù)據(jù)備份
數(shù)據(jù)還原
數(shù)據(jù)庫遷移
表的導(dǎo)出和導(dǎo)入
實踐：導(dǎo)出XML文件

14 MySQL性能優(yōu)化

優(yōu)化簡介
優(yōu)化查詢
優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)
查詢高速緩存
優(yōu)化多表查詢
優(yōu)化表設(shè)計

15 MySQL數(shù)據(jù)庫安全技術(shù)

MySQL的基本安全和保護策略
用戶和權(quán)限管理
MySQL數(shù)據(jù)庫安全技術(shù)常見問題

16 MySQL日常管理

連接故障恢復(fù)
日志文件管理
MySQL服務(wù)器鏡像配置
MySQL服務(wù)器優(yōu)化配置
優(yōu)化MySQL服務(wù)器
運行多個MySQL服務(wù)器

17 PHP訪問MySQL數(shù)據(jù)庫

PHP語言概述
PHP操作MySQL數(shù)據(jù)庫的步驟
使用PHP操作MySQL數(shù)據(jù)庫
PHP管理MySQL數(shù)據(jù)庫中的數(shù)據(jù)
常見問題與解決方法
MySQL與PHP的應(yīng)用實例---迷你日記

項目實戰(zhàn)

18 Java與MySQL數(shù)據(jù)庫——圖書管理系統(tǒng)

19 C語言與MySQL數(shù)據(jù)庫——圖書管理系統(tǒng)

需求分析
系統(tǒng)設(shè)計
數(shù)據(jù)庫設(shè)計
C語言開發(fā)數(shù)據(jù)庫程序的流程
數(shù)據(jù)庫管理模塊設(shè)計
文件引用
變量和函數(shù)定義
管理模塊設(shè)計

熟知概念 page 03

基礎(chǔ)知識

1 MySQL數(shù)據(jù)庫概要

特性

開放的 快速的 多線程的 多用戶

應(yīng)用環(huán)境

MySQL是最流行的關(guān)系型數(shù)據(jù)庫管理系統(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： 多上機練習(xí) 多編寫SQL語句 牢記數(shù)據(jù)庫理論知識
MySQL學(xué)習(xí)常見問題：待回答

2 簡單的MySQL環(huán)境

下載與安裝
啟動連接斷開停止

centos6:
chkconfig --list mysqld
chkconfig --add mysqld
chkconfig mysqld on # 設(shè)置MySQL開機自啟
service mysqld start/stop
mysqladmin -u root -p shutdown # 命令關(guān)閉MySQL
mysql -u root -h localhost -p

MySQL Workbench圖形化管理工具
phpMyAdmin圖形化管理工具

3 數(shù)據(jù)的存儲和獲取

MySQL存儲引擎

InnoDB 事務(wù)型數(shù)據(jù)庫的首選引擎川背，支持ACID事務(wù)泉褐，支持行級鎖定, MySQL 5.5 起成為默認數(shù)據(jù)庫引擎
MyISAM MySQL 5.0 之前的默認數(shù)據(jù)庫引擎，最為常用烁落。擁有較高的插入乘粒，查詢速度，但不支持事務(wù)
Memory 所有數(shù)據(jù)置于內(nèi)存的存儲引擎伤塌，擁有極高的插入灯萍，更新和查詢效率。但是會占用和數(shù)據(jù)量成正比的內(nèi)存空間每聪。并且其內(nèi)容會在 MySQL 重新啟動時丟失

如何選擇存儲引擎：
除非需要用到某些InnoDB不具備的特性旦棉，并且沒有其他辦法可以替代齿风，否則都應(yīng)該優(yōu)先選擇InnoDB引擎

如何設(shè)置數(shù)據(jù)表的存儲引擎
alter table table_name engine=innodb; (修改表引擎）
show engines; （查看系統(tǒng)支持的存儲引擎）
show variables like '%storage_engine%'; (查看當(dāng)前默認的存儲引擎)
mysql> show create table table_name; (查看某個表使用的存儲引擎)

MySQL 數(shù)據(jù)類型

數(shù)值類型:
1 整數(shù)類型： int tinyint bigint
2 浮點型：float 和double 不推薦使用浮點型
3 高精度型： decimal （numeric）如：amount DECIMAL(6,2)表示amount列最多存儲6位數(shù)字，小數(shù)位數(shù)為2位绑洛。

字符類型：
1 varchar和char 變長字符和定長字符
2 text和blob 存儲文本大數(shù)據(jù)類型和存儲二進制大數(shù)據(jù)類型（實際很少使用救斑，太浪費資源）
3 varbinary和binary 存儲二進制字符
4 enum和set 集合類型，enum可枚舉65536個元素而set最多64個

日期和時間類型：
DateTime（8個字節(jié)） TimeStamp（4）Date（3） Year（1）Time (1)
實際開發(fā)中選擇TimeStamp作為時間類型的字段真屯，也可以用int

MySQL運算符

1 算數(shù)運算符： + - * / %
2 比較運算符： == <==>安全的等于 脸候， <>和!= 不等于 ，<= , >= ,> ,<,
IS NULL ,IS NOT NULL , IN, NOT IN, LIKE(通配符匹配)绑蔫， REGEXP(正則表達式匹配)
3 邏輯運算符的求值所得結(jié)果均為1 (TRUE)运沦、0( FALSE)，這類運算符有邏輯非（NOT或者!）配深、邏輯與（AND或者&&）携添、邏輯或（OR或者||）、邏輯異或（XOR）篓叶。
4 位操作運算符 參與運算符的操作數(shù)烈掠，按二進制位進行運算。包括位與（&）缸托、位或（|）向叉、位非（~）、位異或（^）嗦董、左移（<<）、右移(>>)6種瘦黑。

4 MySQL基本操作

數(shù)據(jù)庫操作

創(chuàng)建CREATE DATABASE db_name;
查看SHOW DATABASES;
查看某一個庫：SHOW CREATE DATABASE db_name;
選擇USE db_name;
查看當(dāng)前use了哪個庫：select database();
刪除DROP DATABASE db_name京革；

數(shù)據(jù)表操作

創(chuàng)建數(shù)據(jù)表

create table 表名 (
字段名1 類型 (寬度) 約束條件,
字段名2 類型(寬度) 約束條件,
字段名3 類型(寬度) 約束條件,
.......
);

注 :
同一張表中，字段名不能相同
字段名和類型必須有
寬度和約束條件為可選項

查看某庫有多少個表： SHOW TABLES;
查看表結(jié)構(gòu):DESC db_name;
查看表的所有字段內(nèi)容：SELECT * FROM db_name;

查看某些字段SELECT id, port FROM host;
修改表結(jié)構(gòu)

修改一列類型
alter table tab_name modify 列名 類型 [完整性約束條件]［first｜after 字段名］;
alter table users2 modify age tinyint default 20;
alter table users2 modify age int after id;
修改列名
alter table tab_name change [column] 列名 新列名
類型 [完整性約束條件]［first｜after 字段名］;
alter table users2 change age Age int default 28 first;
刪除一列
alter table tab_name drop [column] 列名;
思考：刪除多列呢幸斥？刪一個填一個呢匹摇？
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(字段名稱,...)
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 命令來操作
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

你可以同時更新一個或多個字段廊勃。
你可以在 WHERE 子句中指定任何條件。
你可以在一個單獨表中同時更新數(shù)據(jù)经窖。
當(dāng)你需要更新數(shù)據(jù)表中指定行的數(shù)據(jù)時 WHERE 子句是非常有用的坡垫。

UPDATE runoob_tbl SET runoob_title='學(xué)習(xí) C++' WHERE runoob_id=3;

刪除記錄

DELETE FROM 命令來刪除 MySQL 數(shù)據(jù)表中的記錄。
DELETE FROM table_name [WHERE Clause]

如果沒有指定 WHERE 子句画侣，MySQL 表中的所有記錄將被刪除冰悠。
你可以在 WHERE 子句中指定任何條件
您可以在單個表中一次性刪除記錄。
當(dāng)你想刪除數(shù)據(jù)表中指定的記錄時 WHERE 子句是非常有用的配乱。

DELETE FROM runoob_tbl WHERE runoob_id=3;

MySQL SQL語句總結(jié)

庫：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...]

語言類型：

• 1. 數(shù)據(jù)查詢語言DQL (Data Query Language)
     數(shù)據(jù)查詢語言DQL基本結(jié)構(gòu)是由SELECT子句溉卓，F(xiàn)ROM子句皮迟，WHERE
     子句組成的查詢塊：
     SELECT <字段名表>
     FROM <表或視圖名>
     WHERE <查詢條件>

• 2 .數(shù)據(jù)操縱語言DML（Data Manipulation Language）
  數(shù)據(jù)操縱語言DML主要有三種形式：
  (1) 插入：INSERT
  (2) 更新：UPDATE
  (3) 刪除：DELETE

• 3. 數(shù)據(jù)定義語言DDL（ Data Definition Language）
     數(shù)據(jù)定義語言DDL用來創(chuàng)建數(shù)據(jù)庫中的各種對象-----表、視圖桑寨、
     索引伏尼、同義詞、聚簇等如：
     CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
     | | | | |
     表 視圖 索引 同義詞 簇
     DDL操作是隱性提交的尉尾！不能rollback

• 4. 數(shù)據(jù)控制語言DCL（Data Control Language）
     數(shù)據(jù)控制語言DCL用來授予或回收訪問數(shù)據(jù)庫的某種特權(quán)爆阶，并控制
     數(shù)據(jù)庫操縱事務(wù)發(fā)生的時間及效果，對數(shù)據(jù)庫實行監(jiān)視等代赁。如：
     (1) GRANT：授權(quán)扰她。
     (2) ROLLBACK [WORK] TO [SAVEPOINT]：回退到某一點“虐回滾
     (3) COMMIT [WORK]：提交

約束

約束是一種限制徒役，它通過對表的行或列的數(shù)據(jù)做出限制，來確保表的數(shù)據(jù)的完整性窖壕、唯一性忧勿。
約束類型：主鍵 外鍵 唯一 非空 自增 默認值
關(guān)鍵字： primary key foreign key unique not null
auto_increment default

1、主鍵約束 primary key
主鍵約束 主鍵列的數(shù)據(jù)類型不限瞻讽，但此列必須是唯一并且非空鸳吸。當(dāng)創(chuàng)建主鍵的約束時，系統(tǒng)默認會在所在的列和列組合上建立對應(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òu)建于一個表的兩個字段或是兩個表的兩個字段之間的參照關(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)
);
多列外鍵組合烦磁，必須用表級別約束語法
-- 主表
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,
/表級別聯(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è)置呕乎，表示用戶名、密碼不能重復(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 與 默認值 default
非空約束用于確保當(dāng)前列的值不為空值猬仁，非空約束只能出現(xiàn)在表對象的列上。

創(chuàng)建非空約束
創(chuàng)建table表先誉，ID 為非空約束湿刽，name 為非空約束 且默認值為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;
取消非空約束谆膳，增加默認值
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的絕對值
CEIL(x) 返回大于x的最小整數(shù)值
FLOOR(x) 返回小于x的最大整數(shù)值
MOD(x,y) 返回x/y的模（余數(shù)）
SQRT(x) 返回一個數(shù)的平方根
ROUND(x,y) 返回參數(shù)x的四舍五入的有y位小數(shù)的值
TRUNCATE(x,y) 返回數(shù)字x截短為y位小數(shù)的結(jié)果
PI() 返回pi的值（圓周率）
RAND()返回０到１內(nèi)的隨機值,可以通過提供一個參數(shù)(種子)使RAND()隨機數(shù)生成器生成一個指定的值叭爱。

字符串函數(shù)( INSERT UPPER UCASE LEFT RTRIM SUBSTRING REVERSE FIELD)

INSERT(str,x,y,instr) 將字符串str從第x位置開始，y個字符長的子串替換為字符串instr漱病，返回結(jié)果
LOWER(str) 返回將字符串str中所有字符改變?yōu)樾懞蟮慕Y(jié)果
UPPER(str) 返回將字符串str中所有字符轉(zhuǎn)變?yōu)榇髮懞蟮慕Y(jié)果
LEFT(str,x)返回字符串str中最左邊的x個字符
RIGHT(str,x) 返回字符串str中最右邊的x個字符
TRIM(str)去除字符串首部和尾部的所有空格
REVERSE(str) 返回顛倒字符串str的結(jié)果
FIELD(filed_name,str1,str2,str3,str4)可以用來對SQL中查詢結(jié)果集進行指定順序排序

select * from ta order by field(id,4,3,2,1);
select * from 表名 order by field(id,1,4,2,3) desc;

日期和時間函數(shù)(CURDATE CURTIME NOW DATEDIFF ADDDATE SUBDATE)

CURDATE()或CURRENT_DATE() 返回當(dāng)前的日期
CURTIME()或CURRENT_TIME() 返回當(dāng)前的時間
NOW() 返回當(dāng)前的日期和時間
DATE_FORMAT(date,fmt) 依照指定的fmt格式格式化日期date值
DAYOFWEEK(date) 返回date所代表的一星期中的第幾天(1~7)
DAYOFMONTH(date) 返回date是一個月的第幾天(1~31)
DAYOFYEAR(date) 返回date是一年的第幾天(1~366)
DAYNAME(date) 返回date的星期名买雾，如：
SELECT DAYNAME(CURRENT_DATE);
HOUR(time) 返回time的小時值(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時間戳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ù)還有另外一種句法鳍鸵，有時使用起來非常方便苇瓣，如下：
CASE
WHEN [conditional test 1] THEN [result 1]
WHEN [conditional test 2] THEN [result 2]
ELSE [default result]
END
這種條件下，返回的結(jié)果取決于相應(yīng)的條件測試是否為真偿乖。

系統(tǒng)信息函數(shù)

DATABASE() 返回當(dāng)前數(shù)據(jù)庫名
BENCHMARK(count,expr) 將表達式expr重復(fù)運行count次
CONNECTION_ID() 返回當(dāng)前客戶的連接ID

FOUND_ROWS() 返回最后一個SELECT查詢進行檢索的總行數(shù)
USER()或SYSTEM_USER() 返回當(dāng)前登陸用戶名
VERSION() 返回MySQL服務(wù)器的版本
示例：
SELECT DATABASE(),VERSION(),USER();
SELECTBENCHMARK(9999999,LOG(RAND()*PI()));
該例中,MySQL計算LOG(RAND()*PI())表達式9999999次击罪。

加密函數(shù)(PASSWORD MD5)

AES_ENCRYPT(str,key) 返回用密鑰key對字符串str利用高級加密標(biāo)準(zhǔn)算法加密后的結(jié)果，調(diào)用AES_ENCRYPT的結(jié)果是一個二進制字符串贪薪，以BLOB類型存儲
AES_DECRYPT(str,key) 返回用密鑰key對字符串str利用高級加密標(biāo)準(zhǔn)算法解密后的結(jié)果
DECODE(str,key) 使用key作為密鑰解密加密字符串str
ENCRYPT(str,salt) 使用UNIXcrypt()函數(shù)媳禁，用關(guān)鍵詞salt(一個可以惟一確定口令的字符串，就像鑰匙一樣)加密字符串str
ENCODE(str,key) 使用key作為密鑰加密字符串str画切，調(diào)用ENCODE()的結(jié)果是一個二進制字符串竣稽，它以BLOB類型存儲
MD5() 計算字符串str的MD5校驗和
PASSWORD(str) 返回字符串str的加密版本，這個加密過程是不可逆轉(zhuǎn)的霍弹，和UNIX密碼加密過程使用不同的算法丧枪。
SHA() 計算字符串str的安全散列算法(SHA)校驗和
示例：
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進行四舍五入保留n位小數(shù)
2庞萍、不同進制數(shù)字轉(zhuǎn)換：
ASCII(s)返回字符串s的第一個字符的ASCII碼
BIN(x)返回x的二進制編碼
3、IP地址與數(shù)字相互轉(zhuǎn)換的函數(shù)：
INET_ATON(IP)將IP轉(zhuǎn)換為數(shù)字
INET_NTOA(n)將n轉(zhuǎn)換為IP
4忘闻、改變字段數(shù)據(jù)類型的函數(shù)：CAST(x AS type)钝计、CONVERT(x,type)兩個函數(shù)將x變成type類型，僅僅是改變輸出齐佳，沒有改變表中的字段類型
5私恬、改變字符集的函數(shù)CONVERT(s USING cs)函數(shù)將字符串s的字符集變成cs。

6 數(shù)據(jù)查詢

SQL 主要語句可以劃分為一下3類

DDL:數(shù)據(jù)定義語言炼吴，這些語句定義不同的數(shù)據(jù)段本鸣、數(shù)據(jù)庫、表硅蹦、列荣德、索引等數(shù)據(jù)庫對象闷煤。常用語句關(guān)鍵字主要包括create,drop,alter等
DML:數(shù)據(jù)操作語句，用于添加涮瞻、刪除鲤拿、更新和查詢數(shù)據(jù)庫記錄，并檢查數(shù)據(jù)完整性署咽。常用語句關(guān)鍵字主要包括 insert,delete,update和select等近顷。
DCL數(shù)據(jù)控制語句，用于控制不同數(shù)據(jù)段直接的許可和訪問級別的語句宁否。這些語句定義了數(shù)據(jù)庫窒升、表、字段慕匠、用戶的訪問權(quán)限和安全級別饱须。主要 的語句包括關(guān)鍵字grant、revoke等

單表查詢

MySQL數(shù)據(jù)庫中查詢數(shù)據(jù)通用的 SELECT 語法：
SELECT column_name,column_name
FROM table_name
[WHERE Clause]
[LIMIT N] [ OFFSET M]

1 查詢語句中你可以使用一個或者多個表絮重，表之間使用逗號(,)分割冤寿，并使用WHERE語句來設(shè)定查詢條件。
2 SELECT 命令可以讀取一條或者多條記錄青伤。
3 你可以使用星號（*）來代替其他字段督怜，SELECT語句會返回表的所有字段數(shù)據(jù)
4 你可以使用 WHERE 語句來包含任何條件。
5 你可以使用 LIMIT 屬性來設(shè)定返回的記錄數(shù)狠角。
6 你可以通過OFFSET指定SELECT語句開始查詢的數(shù)據(jù)偏移量号杠。默認情況下偏移量為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 查詢條件 ;
  例子：
  查詢價格為10.2元的水果的名稱
  SELECT f_name, f_price FROM fruits WHERE f_price=10.2;
  查詢名稱為"apple"的水果的價格
  SELECT f_name, f_price FROM fruits WHERE f_name='apple';
  查詢價格小于10的水果的名稱
  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 的范圍查詢
  查詢價格在2.00元到10.20元之間的水果名稱和價格
  SELECT f_name, f_price FROM fruits
  WHERE f_price BETWEEN 2.00 AND 10.20;
  查詢價格在2.00元到10.20元之外的水果的名稱和價格
  SELECT f_name, f_price FROM fruits
  WHERE f_price NOT BETWEEN 2.00 AND 10.20;
• 帶LIKE的字符匹配查詢
  '%' 匹配任意長度的字符,甚至包括零字符
  查找所有以'b'開頭的水果
  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'開頭,并以'y'結(jié)尾的水果名稱
  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的水果價格和名稱
  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個或多個字段進行分組丰歌，字段值相同的為一組
  (2) group by可用于單個字段分組姨蟋，也可用于多個字段分組
  select * from employee group by sex;
  根據(jù)sex字段來分組，sex字段的全部值只有兩個('男'和'女')立帖，所以分為了兩組
  當(dāng)group by單獨使用時眼溶，只顯示出每組的第一條記錄
  所以group by單獨使用時的實際意義不大

• group by + group_concat()
  (1) group_concat(字段名)可以作為一個輸出字段來使用， concat： 合并晓勇，連接
  (2) 表示分組之后堂飞，根據(jù)分組結(jié)果，使用group_concat()來放置每一組的某字段的值的集合
  select sex,group_concat(name) from employee group by sex;

• group by + 集合函數(shù)
  (1) 通過group_concat()的啟發(fā)绑咱，我們既然可以統(tǒng)計出每個分組的某字段的值的集合绰筛，那么我們也可以通過集合函數(shù)來對這個"值的集合"做一些操作
  分別統(tǒng)計性別為男/女的人年齡平均值
  select sex,avg(age) from employee group by sex;
  select sex,count(sex) from employee group by sex;

• group by + having
  (1) having 條件表達式：用來分組查詢后指定一些條件來輸出查詢結(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的作用是：在最后新增一行描融，來記錄當(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號訂單一共購買的水果總量
  SELECT SUM(quantity) AS items_total
  FROM orderitems
  WHERE o_num=30005;
• AVG()函數(shù)
  在fruits表中,查詢s_id=103的供應(yīng)商的水果價格的平均值
  SELECT AVG(f_price) AS avg_total FROM fruits WHERE s_id=103;
• MAX()函數(shù)
  在fruits表中查找市場上價格最高的水果值
  SELECT MAX(f_price) AS max_price FROM fruits;
• MIN()函數(shù)
  在fruits表中查找市場上價格最低的水果值
  SELECT MIN(f_price) AS min_price FROM fruits;

連接查詢

• mysql常用連接
  INNER JOIN（內(nèi)連接,或等值連接）：獲取兩個表中字段匹配關(guān)系的記錄铝噩。
  LEFT JOIN（左連接）：獲取左表所有記錄，即使右表沒有對應(yīng)匹配的記錄窿克。
  RIGHT JOIN（右連接）： 與 LEFT JOIN 相反骏庸，用于獲取右表所有記錄毛甲，即使左表沒有對應(yīng)匹配的記錄。

• 語句：
  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é)果
定義表和字段的別名
使用正則表達式查詢
實踐：使用聚合函數(shù)sum對學(xué)生成績進行匯總

MySQL對查詢結(jié)果排序

從表中查詢出來的數(shù)據(jù)敞恋，可能是無序的丽啡，或者其排列順序表示用戶期望的 。 使用ORDER BY對查詢結(jié)果進行排序
SELECT 字段名1硬猫，字段名2补箍，……
FROM 表名
ORDER BY 字段名1 [ASC|DESC]，字段名2[ASC|DESC]……
參數(shù)說明
指定的字段名1啸蜜、字段名2坑雅，是對查詢結(jié)果排序的依據(jù)
參數(shù)ASC，表示按照升序進行排序 ascend
參數(shù)DESC衬横，表示按照降序進行排序 descend
默認情況下裹粤，按照ASC方式，升序進行排序
SELECT * FROM student ORDER BY grade DESC;

MySQL中蜂林，指定按照多個字段遥诉，對查詢結(jié)果進行排序
比如，查詢student表中所有記錄噪叙，按照gender和grade字段進行排序矮锈，在排序過程中，會先按照gender字段進行排序睁蕾，如果遇到gender字段相同的記錄苞笨，再把這些記錄，按照grade字段進行排序
SELECT * FROM student ORDER BY gender ASC, grade DESC;

MySQL開發(fā)中常用的查詢語句總結(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ù)庫對日期型數(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’
邏輯運算符: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ù)時渴杆，傳入SQL的變量不必用引號括起來
7射窒、利用變量查詢字符串?dāng)?shù)據(jù)
SELECT * FROM tb_name WHERE name LIKE ‘%$_POST[name]%’
完全匹配的方法”%%”表示可以出現(xiàn)在任何位置
8、查詢前n條記錄(LIMIT n 等價于 LIMIT 0,n)
SELECT * FROM tb_name LIMIT 0,$N;
limit語句與其他語句将塑，如order by等語句聯(lián)合使用殷蛇，會使用SQL語句千變?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场钉、查詢從指定位置開始的n條記錄
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $_POST[begin],$n
注意:數(shù)據(jù)的id是從0開始的
11来吩、查詢統(tǒng)計結(jié)果中的前n條記錄
SELECT * ,(yw+sx+wy) AS total FROM tb_score ORDER BY (yw+sx+wy) DESC LIMIT 0,$num
12敢辩、查詢指定時間段的數(shù)據(jù)
SELECT 要查找的字段 FROM 表名 WHERE 字段名 BETWEEN 初始值 AND 終止值
SELECT * FROM tb_stu WHERE age BETWEEN 0 AND 18
13蔽莱、按月查詢統(tǒng)計數(shù)據(jù)
SELECT * FROM tb_stu WHERE month(date) = ‘$_POST[date]‘ ORDER BY date ;
注：SQL語言中提供了如下函數(shù)，利用這些函數(shù)可以很方便地實現(xiàn)按年戚长、月盗冷、日進行查詢
year(data):返回data表達式中的公元年分所對應(yīng)的數(shù)值
month(data):返回data表達式中的月分所對應(yīng)的數(shù)值
day(data):返回data表達式中的日期所對應(yīng)的數(shù)值
14、查詢大于指定條件的記錄
SELECT * FROM tb_stu WHERE age>$_POST[age] ORDER BY age;
15同廉、查詢結(jié)果不顯示重復(fù)記錄
SELECT DISTINCT 字段名 FROM 表名 WHERE 查詢條件
注:SQL語句中的DISTINCT必須與WHERE子句聯(lián)合使用仪糖，否則輸出的信息不會有變化 ,且字段不能用代替
16、NOT與謂詞進行組合條件的查詢
(1)NOT BERWEEN … AND … 對介于起始值和終止值間的數(shù)據(jù)時行查詢 可改成 <起始值 AND >終止值
(2)IS NOT NULL 對非空值進行查詢
(3)IS NULL 對空值進行查詢
(4)NOT IN 該式根據(jù)使用的關(guān)鍵字是包含在列表內(nèi)還是排除在列表外迫肖，指定表達式的搜索锅劝，搜索表達式可以是常量或列名，而列名可以是一組常量蟆湖，但更多情況下是子查詢
17故爵、顯示數(shù)據(jù)表中重復(fù)的記錄和記錄條數(shù)
SELECT name,age,count() ,age FROM tb_stu WHERE age = ’19′ group by date
18、對數(shù)據(jù)進行降序/升序查詢
SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段 DESC 降序
SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段 ASC 升序
注:對字段進行排序時若不指定排序方式隅津，則默認為ASC升序
19诬垂、對數(shù)據(jù)進行多條件查詢
SELECT 字段名 FROM tb_stu WHERE 條件 ORDER BY 字段1 ASC 字段2 DESC …
注意:對查詢信息進行多條件排序是為了共同限制記錄的輸出，一般情況下伦仍，由于不是單一條件限制结窘，所以在輸出效果上有一些差別。
20呢铆、對統(tǒng)計結(jié)果進行排序
函數(shù)SUM([ALL]字段名) 或 SUM([DISTINCT]字段名),可實現(xiàn)對字段的求和晦鞋，函數(shù)中為ALL時為所有該字段所有記錄求和,若為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)計
SELECT id,name,SUM(price) AS title,date FROM tb_price GROUP BY pid ORDER BY title DESC
注:當(dāng)分組語句group by排序語句order by同時出現(xiàn)在SQL語句中時棺克，要將分組語句書寫在排序語句的前面悠垛，否則會出現(xiàn)錯誤。
22娜谊、多列數(shù)據(jù)分組統(tǒng)計
多列數(shù)據(jù)分組統(tǒng)計與單列數(shù)據(jù)分組統(tǒng)計類似
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語句后面一般為不是聚合函數(shù)的數(shù)列，即不是要分組的列纱皆。
23湾趾、多表分組統(tǒng)計
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ù)雜的一個查詢結(jié)果而不是一個具體的表。

• 2、將t1表的username更新至t2表歧譬，將t2表的score更新至t1表岸浑。方法如下：
  UPDATE t1,t2
  SET t1.score = t2.score,t2.username = t1.username
  WHERE t1.id = t2.id
  這個方法其實跟上面的方法類似，可以同時更新兩個表的數(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ù)

7 索引

MySQL索引概述與分類

索引是在存儲引擎層實現(xiàn)的而不是在服務(wù)器層葱轩。
MyISAM存儲引擎使用前綴壓縮技術(shù)存儲表記錄，InnoDB存儲引擎用原數(shù)據(jù)的格式進行存儲藐握。
MyISAM存儲引擎通過數(shù)據(jù)的物理位置引用被索引的行靴拱，InnoDB存儲引擎通過主鍵引用被索引的行。

• MySQL索引類型：
  1.普通索引
  2.唯一索引
  3.主鍵索引
  4.組合索引
  5.全文索引
• 語法
  CREATE TABLE table_name[col_name data type]
  [unique|fulltext][index|key]index_name[asc|desc]
  1.unique|fulltext為可選參數(shù)猾普，分別表示唯一索引袜炕、全文索引
  2.index和key為同義詞，兩者作用相同初家，用來指定創(chuàng)建索引
  3.col_name為需要創(chuàng)建索引的字段列偎窘，該列必須從數(shù)據(jù)表中該定義的多個列中選擇
  4.index_name指定索引的名稱，為可選參數(shù)溜在，如果不指定陌知，默認col_name為索引值
  5.length為可選參數(shù)，表示索引的長度掖肋，只有字符串類型的字段才能指定索引長度
  6.asc或desc指定升序或降序的索引值存儲

尖括號仆葡，用于分隔字符串，字符串為語法元素的名稱志笼，SQL語言的非終結(jié)符沿盅。
[ ] 方括號表示規(guī)則中的可選元素。方括號中的規(guī)則部分可以明確指定也可以省略纫溃。
{ } 花括號聚集規(guī)則中的元素腰涧。在花括號中的規(guī)則部分必須明確指定。
. . . 省略號表明在規(guī)則中省略號應(yīng)用的元素可能被重復(fù)多次紊浩。

創(chuàng)建索引
CREATE INDEX可對表增加普通索引或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.普通索引
  是最基本的索引揍很，它沒有任何限制。它有以下幾種創(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)建表的時候同時創(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 mytable(username(length))
  （2）修改表結(jié)構(gòu)
  ALTER table mytable ADD UNIQUE [indexName] (username(length))
  （3）創(chuàng)建表的時候直接指定
  CREATE TABLE mytable(
  ID INT NOT NULL,
  username VARCHAR(16) NOT NULL,
  UNIQUE indexName (username(length))
  );
• 3.主鍵索引
  是一種特殊的唯一索引阶界，一個表只能有一個主鍵虹钮，不允許有空值。一般是在建表的時候同時創(chuàng)建主鍵索引：
  CREATE TABLE table (
  id int(11) NOT NULL AUTO_INCREMENT ,
  title char(255) NOT NULL ,
  PRIMARY KEY (id)
  );
• 4.組合索引
  指多個字段上創(chuàng)建的索引膘融，只有在查詢條件中使用了創(chuàng)建索引時的第一個字段芙粱，索引才會被使用。使用組合索引時遵循最左前綴集合
  ALTER TABLE table ADD INDEX name_city_age (name,city,age);
• 5.全文索引
  主要用來查找文本中的關(guān)鍵字氧映，而不是直接與索引中的值相比較春畔。fulltext索引跟其它索引大不相同，它更像是一個搜索引擎岛都，而不是簡單的where語句的參數(shù)匹配律姨。fulltext索引配合match against操作使用，而不是一般的where語句加like臼疫。它可以在create table择份，alter table ，create index使用烫堤，不過目前只有char荣赶、varchar，text 列上可以創(chuàng)建全文索引鸽斟。值得一提的是拔创，在數(shù)據(jù)量較大時候，現(xiàn)將數(shù)據(jù)放入一個沒有全局索引的表中湾盗，然后再用CREATE index創(chuàng)建fulltext索引伏蚊，要比先為一張表建立fulltext然后再將數(shù)據(jù)寫入的速度快很多。
  （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)

缺點
1.雖然索引大大提高了查詢速度格粪，同時卻會降低更新表的速度躏吊，如對表進行insert、update和delete帐萎。因為更新表時比伏，不僅要保存數(shù)據(jù)，還要保存一下索引文件疆导。
2.建立索引會占用磁盤空間的索引文件赁项。一般情況這個問題不太嚴(yán)重，但如果你在一個大表上創(chuàng)建了多種組合索引，索引文件的會增長很快悠菜。
索引只是提高效率的一個因素舰攒，如果有大數(shù)據(jù)量的表，就需要花時間研究建立最優(yōu)秀的索引悔醋，或優(yōu)化查詢語句摩窃。

注意事項
1.索引不會包含有null值的列
2.使用短索引
3.索引列排序
4.不推薦使用like操作
5.不要在列上進行計算
6.不使用not in操作

8 存儲過程

• 一. 存儲過程的定義：
  　　存儲過程（Stored Procedure）是在大型數(shù)據(jù)庫系統(tǒng)中，一組為了完成特定功能的SQL 語句集芬骄，經(jīng)編譯后存儲在數(shù)據(jù)庫中猾愿，用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。
• 二. 存儲過程的優(yōu)點：
  簡化應(yīng)用開發(fā)人員的工作账阻。
  增強安全性蒂秘。
  減少數(shù)據(jù)在數(shù)據(jù)庫和應(yīng)用服務(wù)器之間的傳輸。

創(chuàng)建存儲過程和存儲函數(shù)

CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body

• 創(chuàng)建存儲過程
  1.進入mysql命令行
  mysql>
  2.用delimiter命令來把語句定界符從 ;變?yōu)?/淘太。這樣就允許在程序體用;定界符傳遞到服務(wù)器姻僧，而不是被mysql自己來解釋。
  mysql> delimiter //
  3.創(chuàng)建存儲過程
  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)建存儲函數(shù)
  建立自定義函數(shù)的過程如下：
  1.進入mysql命令行
  mysql>
  2.用delimiter命令來把語句定界符從 ;變?yōu)?/琴儿。這樣就允許在程序體用;定界符傳遞到服務(wù)器段化，而不是被mysql自己來解釋。
  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;

流程控制語句
條件控制
　　　　IF條件：條件為真造成，執(zhí)行
　　　　CASE條件：匹配到显熏，執(zhí)行
循環(huán)控制
　　　　WHILE循環(huán)：先判斷后執(zhí)行
　　　　REPEAT循環(huán)：先執(zhí)行后判斷
　　　　LOOP循環(huán)(死循環(huán))
　　　　LEAVE語句(離開)
　　　　ITERATE語句：迭代，再次循環(huán)
　　　　RETURN語句：返回
注意：MySQL不支持FOR循環(huán)

一晒屎、條件控制：if語句喘蟆、case語句
1、IF語句
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語句
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語句
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;

> 流程控制語句
條件控制
　　　　IF條件：條件為真蕴轨，執(zhí)行
　　　　CASE條件：匹配到，執(zhí)行
循環(huán)控制
　　　　WHILE循環(huán)：先判斷后執(zhí)行
　　　　REPEAT循環(huán)：先執(zhí)行后判斷
　　　　LOOP循環(huán)(死循環(huán))
　　　　LEAVE語句(離開)
　　　　ITERATE語句：迭代骇吭，再次循環(huán)
　　　　RETURN語句：返回
注意：MySQL不支持FOR循環(huán)
>> 一橙弱、條件控制：if語句、case語句
1燥狰、IF語句
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語句
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語句
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;

> 流程控制語句
條件控制
　　　　IF條件：條件為真，執(zhí)行
　　　　CASE條件：匹配到龙致，執(zhí)行
循環(huán)控制
　　　　WHILE循環(huán)：先判斷后執(zhí)行
　　　　REPEAT循環(huán)：先執(zhí)行后判斷
　　　　LOOP循環(huán)(死循環(huán))
　　　　LEAVE語句(離開)
　　　　ITERATE語句：迭代蛀缝，再次循環(huán)
　　　　RETURN語句：返回
注意：MySQL不支持FOR循環(huán)
>> 一、條件控制：if語句目代、case語句
1屈梁、IF語句
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語句
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語句
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語句构哺、iterate語句
Tips：循環(huán)體結(jié)構(gòu)
　〈苏睢①條件
　　②SQL語句體
　≌谏簟③程序體里面需要對條件中的變量進行處理
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語句
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語句
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語句边败、iterate語句
Tips：循環(huán)體結(jié)構(gòu)
　「ぶ浴①條件
　　②SQL語句體
　⌒Υ堋③程序體里面需要對條件中的變量進行處理
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語句
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語句
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語句、iterate語句
Tips：循環(huán)體結(jié)構(gòu)
　∪险帧①條件
　∠潋稹②SQL語句體
　　③程序體里面需要對條件中的變量進行處理
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語句
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語句撇簿、iterate語句
Tips：循環(huán)體結(jié)構(gòu)
　∧粼ā①條件
　　②SQL語句體
　∷奶薄③程序體里面需要對條件中的變量進行處理
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是間隔類型關(guān)鍵字
-> 　　wait_loop:loop
-> 　　　　if now() > end_time
-> 　　　　then
-> 　　　　　　leave wait_loop;　　#leave語句表離開
-> 　　　　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語句诚纸、iterate語句
Tips：循環(huán)體結(jié)構(gòu)
　∽辍①條件
　　②SQL語句體
　∑枧恰③程序體里面需要對條件中的變量進行處理
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是間隔類型關(guān)鍵字
-> 　　wait_loop:loop
-> 　　　　if now() > end_time
-> 　　　　then
-> 　　　　　　leave wait_loop;　　#leave語句表離開
-> 　　　　end if;
-> 　　end loop wait_loop;
-> end $$
mysql> delimiter ;
mysql> call wait_x(10);
……等10秒关筒，結(jié)束……
4、LEAVE語句
作用：用來退出帶標(biāo)簽的語句塊或者循環(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; #離開塊block1
-> 　　　　SET p2 = 3; #已離開杯缺，不執(zhí)行
-> 　　END block1;
-> 　　SET p1 = 4; #執(zhí)行
-> END$$
mysql> DELIMITER ;
mysql> call small_exit(@r1,@r2);
mysql> select @r1,@r2;
5蒸播、ITERATE語句
ITERATE label;
只能出現(xiàn)在循環(huán)LOOP、REPEAT和WHILE 中(有標(biāo)簽)
含義：跳出本次循環(huán)萍肆，開始一次新的循環(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; #開始下一次循環(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; 可用以下語句代替：DECLARE a INT DEFAULT 100;
變量分為用戶變量和系統(tǒng)變量廉赔，系統(tǒng)變量又分為會話和全局級變量
用戶變量：用戶變量名一般以@開頭，濫用用戶變量會導(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蜡塌、 在存儲過程中使用用戶變量
mysql> CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql> SET @greeting='Hello';
mysql> CALL GreetWorld( );
3、 在存儲過程間傳遞全局范圍的用戶變量
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)用存儲過程和存儲函數(shù)

基本語法：call sp_name()
注意：存儲過程名稱后面必須加括號勿负，哪怕該存儲過程沒有參數(shù)傳遞

查看存儲過程和函數(shù)

查詢數(shù)據(jù)庫中的存儲過程和函數(shù)

select name from mysql.proc where db = 'xx' and type = 'PROCEDURE' //存儲過程
select name from mysql.proc where db = 'xx' and type = 'FUNCTION' //函數(shù)
show procedure status; //存儲過程
show function status; //函數(shù)

修改存儲過程和函數(shù)

使用 ALTER 語句可以修改存儲過程或函數(shù)的特性馏艾，語法格式如下：
ALTER { PROCEDURE | FUNCTION } sp_name [ characteristic ... ]
其中，sp_name 參數(shù)表示存儲過程或函數(shù)的名稱奴愉；characteristic 參數(shù)指定存儲函數(shù)的特性琅摩，可能的取值有:
CONTAINS SQL 表示子程序包含 SQL 語句，但不包含讀或?qū)憯?shù)據(jù)的語句锭硼。
NO SQL 表示了程序中不包含 SQL 語句房资。
READS SQL DATA 表示子程序中包含讀數(shù)據(jù)的語句。
MODIFIES SQL DATA 表示子程序中包含寫數(shù)據(jù)的語句檀头。
SQL SECURITY { DEFINER | INVOKER } 指明誰有權(quán)限來執(zhí)行轰异。
DEFINER 表示只有定義者自己才能夠執(zhí)行岖沛。
INVOKER 表示調(diào)用者可以執(zhí)行。
COMMENT 'string' 表示注釋信息搭独。

mysql> ALTER PROCEDURE CountProc1
-> MODIFIES SQL DATA
-> SQL SECURITY INVOKER;

刪除存儲過程和函數(shù)

刪除存儲過程和函數(shù)可以使用 DROP 語句婴削，其語法結(jié)構(gòu)如下:
DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] Sp_name
sp_name 為要移除的存儲過程或函數(shù)的名稱。
IF EXISTS 子句是 MySQL 的一個擴展牙肝。如果程序或函數(shù)不存儲唉俗，它可以防止發(fā)生錯誤，產(chǎn)生一個用 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）

捕獲存儲過程中的錯誤
實踐： 使用存儲過程實現(xiàn)用戶注冊

9 觸發(fā)器(trigger)

觸發(fā)器(trigger)：監(jiān)視某種情況虫溜，并觸發(fā)某種操作。
觸發(fā)器創(chuàng)建語法四要素：1.監(jiān)視地點(table) 2.監(jiān)視事件(insert/update/delete) 3.觸發(fā)時間(after/before) 4.觸發(fā)事件(insert/update/delete)
觸發(fā)器是一個特殊的存儲過程股缸，不同的是存儲過程要用CALL來調(diào)用吼渡，而觸發(fā)器不需要使用CALL
也不需要手工啟動，只要當(dāng)一個預(yù)定義的事件發(fā)生的時候乓序，就會被MYSQL自動調(diào)用。

• 創(chuàng)建MySQL觸發(fā)器

語法
create trigger triggerName
after/before insert/update/delete on 表名
for each row #這句話在mysql是固定的
begin
sql語句;
end;

創(chuàng)建一個單執(zhí)行語句的觸發(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)建一個account表坎背，表中有兩個字段替劈，分別為：acct_num字段（定義為int類型）
amount字段（定義成浮點類型）；其次創(chuàng)建一個名為ins_sum的觸發(fā)器得滤，觸發(fā)的條件是向數(shù)據(jù)表account插入數(shù)據(jù)之前陨献，對新插入的amount字段值進行求和計算

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 視圖

• 視圖的概念與作用
  視圖是指計算機數(shù)據(jù)庫中的視圖，是一個虛擬表懂更，其內(nèi)容由查詢定義眨业。同真實的表一樣，視圖包含一系列帶有名稱的列和行數(shù)據(jù)沮协。但是龄捡，視圖并不在數(shù)據(jù)庫中以存儲的數(shù)據(jù)值集形式存在。行和列數(shù)據(jù)來自由定義視圖的查詢所引用的表慷暂，并且在引用視圖時動態(tài)生成聘殖。簡單的來說視圖是由其定義結(jié)果組成的表。
  通俗的講行瑞，視圖就是一條SELECT語句執(zhí)行后返回的結(jié)果集奸腺。所以我們在創(chuàng)建視圖的時候，主要的工作就落在創(chuàng)建這條SQL查詢語句上血久。
  作用：
  方便操作突照，增強可讀性；
  更加安全氧吐。
• 創(chuàng)建視圖
  CREATE VIEW view_name AS 查詢語句
  //說明：和創(chuàng)建表一樣讹蘑，視圖名不能和表名末盔、也不能和其他視圖名重名。視圖的功能實際就是封裝了復(fù)雜的查詢語句衔肢。

use zhaojd_test; //選擇一個自己創(chuàng)建的庫
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;顯示表的同時也顯示視圖名
  SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern'] 查看視圖詳細信息
  DESCRIBE | DESC viewname; 查看視圖定義信息
• 修改視圖

CREATE OR REPLACE VIEW語句修改視圖：
實現(xiàn)思路就是：先刪除同名的視圖庄岖，然后再根據(jù)新的需求創(chuàng)建新的視圖即可。
DROP VIEW view_name;
CREATE VIEW view_name as 查詢語句;
但是如果每次修改視圖角骤，都是先刪除視圖啥纸，然后再次創(chuàng)建一個同名的視圖设凹，則顯得非常麻煩。于是MySQL提供了更方便的實現(xiàn)替換的創(chuàng)建視圖的語法，完整語法為：
CREATE OR REPLACE VIEW view_name as 查詢語句签孔；

ALTER語句修改視圖：
語法為：
ALTER VIEW view_name as 查詢語句;

• 更新視圖
  是指通過視圖，來更新趋箩、插入着绊、刪除基本表中的數(shù)據(jù)
  因為，視圖是一個虛擬表又沾，其中沒有數(shù)據(jù)弊仪，所以，當(dāng)通過視圖更新數(shù)據(jù)時杖刷，其實励饵，是在更新基本表中的數(shù)據(jù)，如果對視圖中的數(shù)據(jù)進行增加滑燃，或者刪除操作時役听，實際上是在對其基本表中的數(shù)據(jù)，進行增加或者刪除操作.

• UPDATE語句
  使用UPDATE語句更新視圖
  MySQL中表窘，可以使用UPDATE語句典予，更新視圖
  更新view_stu視圖中，chinese字段對應(yīng)的數(shù)據(jù)值乐严，將字段值改為100
  UPDATE view_stu SET chinese=100;

• 刪除視圖
  在刪除視圖時首先要確保擁有刪除視圖的權(quán)限瘤袖。
  DROP VIEW view_name [,view_name] ......
• 實踐：使用MySQL視圖查詢學(xué)生信息表

11 事務(wù)

• MySQL事物概述（原子性 一致性 孤立性 持久性）
  Mysql事務(wù)主要用來處理數(shù)據(jù)量大、數(shù)據(jù)復(fù)雜度高的數(shù)據(jù)操作昂验，最經(jīng)典的使用場景是銀行的轉(zhuǎn)賬：需要先從銀行賬戶A中取出錢孽椰，然后再存入銀行賬戶B中，如果中間出現(xiàn)問題凛篙，而沒有事務(wù)的保證黍匾，那么就會出現(xiàn)B收不到錢，而A支出錢又回不到自己的賬戶的嚴(yán)重問題呛梆，那么有了事務(wù)機制锐涯，這個問題就解決了。

數(shù)據(jù)庫事務(wù)（Database Transaction）,是指作為單個邏輯工作單元執(zhí)行的一系列操作填物，要么完全執(zhí)行纹腌，要么完全地不執(zhí)行霎终。
ACID
事務(wù)必須具備ACID四個特性
原子性（Atomicity）（）
一致性（Consistency） (一切都是正確的狀態(tài)）
隔離性(Isolation)
持久性(Durability)

• MySQL事物控制

• 事務(wù)控制語句：
  BEGIN或START TRANSACTION；顯式地開啟一個事務(wù)升薯；
  COMMIT莱褒；也可以使用COMMIT WORK，不過二者是等價的涎劈。COMMIT會提交事務(wù)广凸，并使已對數(shù)據(jù)庫進行的所有修改稱為永久性的；
  ROLLBACK蛛枚；有可以使用ROLLBACK WORK谅海，不過二者是等價的”钠郑回滾會結(jié)束用戶的事務(wù)扭吁，并撤銷正在進行的所有未提交的修改；
  SAVEPOINT identifier盲镶；SAVEPOINT允許在事務(wù)中創(chuàng)建一個保存點侥袜，一個事務(wù)中可以有多個SAVEPOINT；
  RELEASE SAVEPOINT identifier溉贿；刪除一個事務(wù)的保存點枫吧，當(dāng)沒有指定的保存點時，執(zhí)行該語句會拋出一個異常顽照；
  ROLLBACK TO identifier；把事務(wù)回滾到標(biāo)記點闽寡；
  SET TRANSACTION代兵；用來設(shè)置事務(wù)的隔離級別。InnoDB存儲引擎提供事務(wù)的隔離級別有READ UNCOMMITTED爷狈、READ COMMITTED植影、REPEATABLE READ和SERIALIZABLE。
• MYSQL 事務(wù)處理主要有兩種方法：
  1涎永、用 BEGIN, ROLLBACK, COMMIT來實現(xiàn)
  BEGIN 開始一個事務(wù)
  ROLLBACK 事務(wù)回滾
  COMMIT 事務(wù)確認
  2思币、直接用 SET 來改變 MySQL 的自動提交模式:
  SET AUTOCOMMIT=0 禁止自動提交
  SET AUTOCOMMIT=1 開啟自動提交

begin; # 開始事務(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ù)器如何提供對數(shù)據(jù)的訪問
MySQL數(shù)據(jù)庫在文件系統(tǒng)里如何表示
MySQL數(shù)據(jù)表如何在文件系統(tǒng)里表示
SQL語句如何映射為數(shù)據(jù)表文件操作
操作系統(tǒng)對數(shù)據(jù)庫和數(shù)據(jù)表命名的限制
數(shù)據(jù)目錄的結(jié)構(gòu)對系統(tǒng)性能的影響
MySQL狀態(tài)文件和日志文件

高級技術(shù)

13 數(shù)據(jù)庫備份與還原

• 數(shù)據(jù)備份

• 備份常用操作基本命令
  1、備份命令mysqldump格式
  格式：mysqldump -h主機名 -P端口 -u用戶名 -p密碼 –database 數(shù)據(jù)庫名 > 文件名.sql
  2羡微、備份MySQL數(shù)據(jù)庫為帶刪除表的格式
  備份MySQL數(shù)據(jù)庫為帶刪除表的格式谷饿，能夠讓該備份覆蓋已有數(shù)據(jù)庫而不需要手動刪除原有數(shù)據(jù)庫。
  mysqldump --add-drop-table -uusername -ppassword -database databasename > backupfile.sql
  3妈倔、直接將MySQL數(shù)據(jù)庫壓縮備份
  mysqldump -hhostname -uusername -ppassword -database databasename | gzip > backupfile.sql.gz
  4博投、備份MySQL數(shù)據(jù)庫某個(些)表
  mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sq
  5、同時備份多個MySQL數(shù)據(jù)庫
  mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql僅僅備6盯蝴、僅備份份數(shù)據(jù)庫結(jié)構(gòu)
  mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
  7毅哗、備份服務(wù)器上所有數(shù)據(jù)庫
  mysqldump –all-databases > allbackupfile.sql
  8听怕、還原MySQL數(shù)據(jù)庫的命令
  mysql -hhostname -uusername -ppassword databasename < backupfile.sql
  9、還原壓縮的MySQL數(shù)據(jù)庫
  gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
  10虑绵、將數(shù)據(jù)庫轉(zhuǎn)移到新服務(wù)器
  mysqldump -uusername -ppassword databasename | mysql –host=... -C databasename
  11尿瞭、--master-data 和--single-transaction
  在mysqldump中使用--master-data=2，會記錄binlog文件和position的信息 翅睛。--single-transaction會將隔離級別設(shè)置成repeatable-commited
  12声搁、導(dǎo)入數(shù)據(jù)庫
  常用source命令，用use進入到某個數(shù)據(jù)庫宏所，mysql>source d:\test.sql酥艳，后面的參數(shù)為腳本文件。
  13爬骤、查看binlog日志
  查看binlog日志可用用命令 mysqlbinlog binlog日志名稱|more
  14充石、general_log
  General_log記錄數(shù)據(jù)庫的任何操作，查看general_log 的狀態(tài)和位置可以用命令show variables like "general_log%" ,開啟general_log可以用命令set global general_log=on

• 增量備份
  小量的數(shù)據(jù)庫可以每天進行完整備份霞玄，因為這也用不了多少時間骤铃，但當(dāng)數(shù)據(jù)庫很大時，就不太可能每天進行一次完整備份了坷剧，這時候就可以使用增量備份惰爬。增量備份的原理就是使用了mysql的binlog志。
  1惫企、首先做一次完整備份：
  mysqldump -h10.6.208.183 -utest2 -p123 -P3310 --single-transaction --master-data=2 test>test.sql這時候就會得到一個全備文件test.sql
  在sql文件中我們會看到：
  -- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;是指備份后所有的更改將會保存到bin-log.000002二進制文件中撕瞧。
  2、在test庫的t_student表中增加兩條記錄狞尔，然后執(zhí)行flush logs命令丛版。這時將會產(chǎn)生一個新的二進制日志文件bin-log.000003，bin-log.000002則保存了全備過后的所有更改偏序，既增加記錄的操作也保存在了bin-log.00002中页畦。
  3、再在test庫中的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中找到誤刪除的時間點栓撞，然后更加對應(yīng)的時間點到bin-log.000003中找到相應(yīng)的position點，需要恢復(fù)到誤刪除的前面一個position點。
可以用如下參數(shù)來控制binlog的區(qū)間
--start-position 開始點 --stop-position 結(jié)束點
--start-date 開始時間 --stop-date 結(jié)束時間
找到恢復(fù)點后瓤湘，既可以開始恢復(fù)瓢颅。
mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123 -P3310

• 數(shù)據(jù)庫遷移

• 表的導(dǎo)出和導(dǎo)入

1 導(dǎo)出
　　MySQL數(shù)據(jù)庫中的數(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 選項
　　FIELDS TERMINATED BY 'value' /設(shè)置字段之間分隔符挽懦，單個或多個字符，默認為'\t'/
　　FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /設(shè)置字段包圍分隔符木人，單個字符/
　　FIELDS ESCAPED BY 'value' /如何寫入或讀取特殊字符信柿，單個字符/
　　LINES STARTING BY 'value' /每行數(shù)據(jù)開頭的字符，單個或多個/
　　LINES TERMINATED BY 'value' /每行數(shù)據(jù)結(jié)尾的字符醒第，單個或多個/

（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"

在下面的例子中渔嚷，生成一個文件，各值用逗號隔開稠曼。這種格式可以被許多程序使用形病。

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 選項
--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
　　語句執(zhí)行后生成兩個文件，person.sql（包含CREATE語句等）和person.txt（包含數(shù)據(jù)信息）霞幅。

1.3 用mysql命令導(dǎo)出

mysql -u root -p --execute="SELECT 語句" dbname>filename.txt
　　相比mysqldump漠吻，mysql工具導(dǎo)出的結(jié)果可讀性更強。
（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 選項
　　FIELDS TERMINATED BY 'value' /設(shè)置字段之間分隔符扔傅，單個或多個字符耍共，默認為'\t'/
　　FIELDS [OPTIONALLY] ENCLOSEED BY 'value' /設(shè)置字段包圍分隔符，單個字符/
　　FIELDS ESCAPED BY 'value' /如何寫入或讀取特殊字符猎塞，單個字符/
　　LINES STARTING BY 'value' /每行數(shù)據(jù)開頭的字符试读，單個或多個/
　　LINES TERMINATED BY 'value' /每行數(shù)據(jù)結(jié)尾的字符，單個或多個/
（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 選項
--fields-terminated-by=value
--fields-enclosed-by=value
--fields-optionally-enclosed-by=value
--fields-escaped-by=value
--lines-terminated-by=value
--ignore-lines=n

• 實踐：導(dǎo)出XML文件

14 MySQL性能優(yōu)化

• 優(yōu)化簡介
  　MySQL性能優(yōu)化就是通過合理安排資源邢享，調(diào)整系統(tǒng)參數(shù)使MySQL運行更快鹏往、更節(jié)省資源淡诗。MySQL性能優(yōu)化包括查詢速度優(yōu)化骇塘、更新速度優(yōu)化、MySQL服務(wù)器優(yōu)化等韩容。本篇博客將從查詢優(yōu)化款违、數(shù)據(jù)庫結(jié)構(gòu)優(yōu)化、MySQL服務(wù)器優(yōu)化3個方面介紹群凶。
  　　MySQL數(shù)據(jù)庫優(yōu)化插爹，一方面是找出系統(tǒng)瓶頸，提高MySQL數(shù)據(jù)庫整體性能；另一方面需要合理的結(jié)構(gòu)設(shè)計和參數(shù)調(diào)整赠尾，以提高用戶操作響應(yīng)速度力穗；同時還要盡可能節(jié)省系統(tǒng)資源，以便系統(tǒng)可以提供更大負荷的服務(wù)气嫁。例如当窗，通過優(yōu)化文件系統(tǒng)，提高磁盤I\O的讀寫書讀寸宵；通過優(yōu)化操作系統(tǒng)調(diào)度策略崖面，提高MySQL在高負荷下的負載能力；優(yōu)化表結(jié)構(gòu)梯影、索引巫员、查詢語句等使查詢響應(yīng)更快.
  在MySQL中使用SHOW STATUS語句查詢一些MySQL數(shù)據(jù)庫的性能，其語法為：SHOW STATUS LIKE 'value';
  　　其中value是要查詢的參數(shù)值甲棍，一些常用的性能參數(shù)如下：
  Connections：連接MySQL服務(wù)器的次數(shù)简识；
  Uptime：MySQL服務(wù)器的上線時間；
  Slow_queries：慢查詢次數(shù)救军；
  Com_select：查詢操作的次數(shù)财异；
  Com_insert：插入操作的次數(shù)；
  Com_update：更新操作的次數(shù)唱遭；
  Com_delete：刪除操作的次數(shù)戳寸。

• 優(yōu)化查詢
  　通過對查詢語句的分析，可以了解查詢語句執(zhí)行情況拷泽，找出查詢語句執(zhí)行的瓶頸疫鹊，從而優(yōu)化查詢語句。MySQL中提供了EXPLAIN語句和DESCRIBE語句司致，用來分析查詢語句拆吆。
  EXPLAIN語句基本語法格式為：
  EXPLAIN [EXTENDED] SELECT select_options
  使用EXTENDED關(guān)鍵字，EXPLAIN語句將產(chǎn)生附加信息脂矫。
  select_options是SELECT語句的查詢選項枣耀，包括FROM WHERE子句等。通過該語句可以分析EXPLAIN后面的SELECT語句執(zhí)行情況庭再，并且分析所查詢表的一些特征捞奕。

優(yōu)化數(shù)據(jù)庫結(jié)構(gòu)
查詢高速緩存
優(yōu)化多表查詢
優(yōu)化表設(shè)計

15 MySQL數(shù)據(jù)庫安全技術(shù)

• MySQL的基本安全和保護策略
• 用戶和權(quán)限管理

MySQL 默認有個root用戶，但是這個用戶權(quán)限太大拄轻，一般只在管理數(shù)據(jù)庫時候才用颅围。如果在項目中要連接 MySQL 數(shù)據(jù)庫，則建議新建一個權(quán)限較小的用戶來連接恨搓。
為 MySQL 創(chuàng)建一個新用戶：
CREATE USER username IDENTIFIED BY 'password';
以此用戶登陸的話院促，會報錯筏养，因為我們還沒有為這個用戶分配相應(yīng)權(quán)限，分配權(quán)限的命令如下：
GRANT ALL PRIVILEGES ON . TO 'username'@'localhost' IDENTIFIED BY 'password';
授予username用戶在所有數(shù)據(jù)庫上的所有權(quán)限常拓。

如果此時發(fā)現(xiàn)剛剛給的權(quán)限太大了渐溶，如果我們只是想授予它在某個數(shù)據(jù)庫上的權(quán)限，那么需要切換到root 用戶撤銷剛才的權(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';
這樣一來掌猛，再次以username登陸 MySQL，只有wordpress數(shù)據(jù)庫是對其可見的眉睹，并且如果你只授權(quán)它select權(quán)限荔茬，那么它就不能執(zhí)行delete 語句。

另外每當(dāng)調(diào)整權(quán)限后竹海，通常需要執(zhí)行以下語句刷新權(quán)限：
FLUSH PRIVILEGES;

刪除剛才創(chuàng)建的用戶：
DROP USER username@localhost;
仔細上面幾個命令慕蔚，可以發(fā)現(xiàn)不管是授權(quán)，還是撤銷授權(quán)斋配，都要指定響應(yīng)的host（即 @ 符號后面的內(nèi)容）孔飒，因為以上及格命令實際上都是在操作mysql 數(shù)據(jù)庫中的user表，可以用如下命令查看相應(yīng)用戶及對應(yīng)的host:
SELECT User, Host FROM user;

MySQL 賦予和撤銷用戶權(quán)限命令的簡單格式可概括為：
GRANT 權(quán)限 ON 數(shù)據(jù)庫對象 TO 用戶
EVOKE 權(quán)限 ON 數(shù)據(jù)庫對象 FROM 用戶

MySQL權(quán)限表

MySQL服務(wù)器通過MySQL權(quán)限表來控制用戶對數(shù)據(jù)庫的訪問艰争，MySQL權(quán)限表存放在mysql數(shù)據(jù)庫里坏瞄，由mysql_install_db腳本初始化。這些MySQL權(quán)限表分別是user甩卓，db鸠匀，table_priv，columns_priv和host逾柿。下面分別介紹一下這些表的結(jié)構(gòu)和內(nèi)容：
user權(quán)限表：記錄允許連接到服務(wù)器的用戶帳號信息缀棍，里面的權(quán)限是全局級的。
db權(quán)限表：記錄各個帳號在各個數(shù)據(jù)庫上的操作權(quán)限机错。
table_priv權(quán)限表：記錄數(shù)據(jù)表級的操作權(quán)限爬范。
columns_priv權(quán)限表：記錄數(shù)據(jù)列級的操作權(quán)限。
host權(quán)限表：配合db權(quán)限表對給定主機上數(shù)據(jù)庫級操作權(quán)限作更細致的控制弱匪。這個權(quán)限表不受GRANT和REVOKE語句的影響青瀑。

• 查看用戶的授權(quán)：
  mysql> show grants for tom;
• GRANT 語法:
  GRANT privileges (columns)
  ON what
  TO user IDENTIFIED BY "password"
  WITH GRANT OPTION
• 權(quán)限列表:
  ALTER: 修改表和索引。
  CREATE: 創(chuàng)建數(shù)據(jù)庫和表萧诫。
  DELETE: 刪除表中已有的記錄斥难。
  DROP: 拋棄(刪除)數(shù)據(jù)庫和表。
  INDEX: 創(chuàng)建或拋棄索引财搁。
  INSERT: 向表中插入新行蘸炸。
  REFERENCE: 未用躬络。
  SELECT: 檢索表中的記錄尖奔。
  UPDATE: 修改現(xiàn)存表記錄。
  FILE: 讀或?qū)懛?wù)器上的文件。
  PROCESS: 查看服務(wù)器中執(zhí)行的線程信息或殺死線程提茁。
  RELOAD: 重載授權(quán)表或清空日志淹禾、主機緩存或表緩存。
  SHUTDOWN: 關(guān)閉服務(wù)器茴扁。
  ALL: 所有權(quán)限铃岔，ALL PRIVILEGES同義詞。
  USAGE: 特殊的 "無權(quán)限" 權(quán)限峭火。
  用 戶賬戶包括 "username" 和 "host" 兩部分毁习，后者表示該用戶被允許從何地接入。tom@'%' 表示任何地址卖丸，默認可以省略纺且。還可以是 "tom@192.168.1.%"、"tom@%.abc.com" 等稍浆。數(shù)據(jù)庫格式為 db@table载碌，可以是 "test." 或 ".*"，前者表示 test 數(shù)據(jù)庫的所有表衅枫，后者表示所有數(shù)據(jù)庫的所有表嫁艇。
  子句 "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ù)庫列表:
SHOW DATABASES;
數(shù)據(jù)表列表:
SHOW TABLES
當(dāng)前數(shù)據(jù)庫:
SELECT DATABASE();
當(dāng)前用戶:
SELECT USER();
數(shù)據(jù)表結(jié)構(gòu):
DESCRIBE table1;
刷新權(quán)限:
FLUSH PRIVILEGES;

grant和revoke可以在幾個層次上控制訪問權(quán)限
1歧斟，整個服務(wù)器，使用 grant ALL 和revoke ALL
2偏形，整個數(shù)據(jù)庫静袖，使用on database.*
3，特點表俊扭，使用on database.table
4队橙，特定的列
5，特定的存儲過程

user表中host列的值的意義
% 匹配所有主機
localhost localhost不會被解析成IP地址萨惑，直接通過UNIXsocket連接
127.0.0.1 會通過TCP/IP協(xié)議連接捐康，并且只能在本機訪問；
::1 ::1就是兼容支持ipv6的庸蔼，表示同ipv4的127.0.0.1

grant 普通數(shù)據(jù)用戶解总，查詢、插入姐仅、更新花枫、刪除 數(shù)據(jù)庫中所有表數(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 命令來替代：
grant select, insert, update, delete on testdb.* to common_user@’%’
grant 數(shù)據(jù)庫開發(fā)人員劳翰，創(chuàng)建表敦锌、索引、視圖佳簸、存儲過程乙墙、函數(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 臨時表權(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 存儲過程、函數(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 管理某個 MySQL 數(shù)據(jù)庫的權(quán)限崔列。
grant all privileges on testdb to dba@’localhost’
其中，關(guān)鍵字 “privileges” 可以省略旺遮。
grant 高級 DBA 管理 MySQL 中所有數(shù)據(jù)庫的權(quán)限赵讯。
grant all on . to dba@’localhost’

MySQL grant 權(quán)限，分別可以作用在多個層次上耿眉。

1. grant 作用在整個 MySQL 服務(wù)器上：
   grant select on . to dba@localhost; -- dba 可以查詢 MySQL 中所有數(shù)據(jù)庫中的表边翼。
   grant all on . to dba@localhost; -- dba 可以管理 MySQL 中的所有數(shù)據(jù)庫
2. grant 作用在單個數(shù)據(jù)庫上：
   grant select on testdb.* to dba@localhost; -- dba 可以查詢 testdb 中的表。
3. grant 作用在單個數(shù)據(jù)表上：
   grant select, insert, update, delete on testdb.orders to dba@localhost;
4. grant 作用在表中的列上：
   grant select(id, se, rank) on testdb.apache_log to dba@localhost;
5. grant 作用在存儲過程鸣剪、函數(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ù)庫安全技術(shù)常見問題

16 MySQL日常管理

• 連接故障恢復(fù)

日志文件管理

日志文件記錄著mysql數(shù)據(jù)庫運行期間發(fā)生的變化筐骇，如：mysql數(shù)據(jù)庫的客戶端連接狀況债鸡、sql語句的執(zhí)行情況和錯誤信息等。當(dāng)數(shù)據(jù)庫遭到意外的損壞時铛纬，可以通過日志查看文件出錯的原因厌均，并且可以通過日志進行數(shù)據(jù)恢復(fù)；也可以通過日志文件分析數(shù)據(jù)告唆、優(yōu)化查詢等棺弊。Mysql日志管理機制比較完善晶密，它包含了以下幾種常見的日志文件、分別為：錯誤日志（-log-err）镊屎、查詢?nèi)罩荆?log）、二進制日志（-log-bin）茄螃、更新日志（-log-update）及慢查詢?nèi)罩荆?log-slow-queries）缝驳。

• 操作錯誤日志

在mysql數(shù)據(jù)庫中，錯誤日志記錄著mysql服務(wù)器的啟動和停止過程中的信息归苍、服務(wù)器在運行過程中發(fā)生的故障和異常情況的相關(guān)信息用狱、事件調(diào)度器運行一個事件時產(chǎn)生的信息、在從服務(wù)器上啟動服務(wù)器進程時產(chǎn)生的信息等拼弃。錯誤日志記錄的并非全是錯誤信息夏伊，如mysql如何啟動InnoDB的表空間文件、如何初始化自己的存儲引擎等信息也記錄在錯誤日志中吻氧。

（1）啟動錯誤日志
錯誤日志在默認情況下是開啟的溺忧，并且不能被禁止。錯誤日志信息也可以自行配置盯孙，通過修改my.cnf文件即可鲁森。錯誤日志所記錄的信息是可以通以log-error和log-warnings來定義的，其中l(wèi)og-err定義是否啟用錯誤日志的功能和錯誤日志的位置振惰，log-warning定義是否將警告信息也定義至錯誤日志中歌溉。-log-error=[file-name]用來指定錯誤日志的存放位置。如果沒有指定[file-name]骑晶，默認hostname.err作為文件名痛垛，默認存放在datadir目錄中铸抑。
（2）查看錯誤日志
Linux中直接使用vi或者gdit工具來查看
mysql> show variables like 'log_error';
（3）刪除錯誤日志
管理員可以刪除很久之前的錯誤日志蹬碧，這樣可以保證mysql服務(wù)器上的硬盤空間。通過show命令查看錯誤文件所在位置桃移，確認可以刪除錯誤日志后可以直接刪除文件仔雷。

• 操作二進制日志

Mysql數(shù)據(jù)庫的二進制文件是用來記錄所有用戶對數(shù)據(jù)庫的操作乾胶。當(dāng)數(shù)據(jù)庫發(fā)生意外時，可以通過此文件查看在一定時間段內(nèi)用戶所做的操作朽寞，結(jié)合數(shù)據(jù)庫備份技術(shù)识窿，即可實現(xiàn)用戶操作，使數(shù)據(jù)庫恢復(fù)脑融。
（1）啟動二進制日志
默認情況下是不開啟二進制日志文件的喻频，通過命令查看
show variables like 'log_bin';
開啟方式：在/etc/my.cnf下
（2）查看二進制日志
show variables like 'log_bin';
show binary logs;
總結(jié)：開啟二進制文件可以實現(xiàn)如下幾個功能
①恢復(fù)（recovery）：某些數(shù)據(jù)的恢復(fù)需要二進制日志，例如肘迎，在一個數(shù)據(jù)庫全備文件恢復(fù)后甥温，用戶可以通過二進制日志進行point-in-time的恢復(fù)锻煌。
②復(fù)制（replication）：其原理與恢復(fù)類似，通過復(fù)制和執(zhí)行二進制日志使一臺遠程的mysql數(shù)據(jù)庫與另一臺mysql數(shù)據(jù)庫進行實時同步姻蚓。
③審計（audit）：用戶可以通過二進制日志中的信息來進行審計宋梧，判斷是否有對數(shù)據(jù)庫進行注入的攻擊。
（3）刪除二進制日志
用reset master命令刪除所有日志狰挡，新日志從000001開始編號
reset master;
（4）使用二進制日志還原數(shù)據(jù)庫

操作慢查詢?nèi)罩?/p>

優(yōu)化mysql最重要的一部分工作就是先確定“有問題”的查詢語句捂龄。只有先找出這些查詢較慢的sql查詢，才可以進一步分析原因并且優(yōu)化它加叁。慢查詢?nèi)罩揪陀涗浟藞?zhí)行時間超過了特定時長的查詢倦沧，即記錄所有執(zhí)行時間超過最大sql執(zhí)行時間（long_query_time）或未使用索引的語句。
（1）啟動慢查詢?nèi)罩?br> 默認情況下它匕，慢查詢?nèi)罩臼顷P(guān)閉的展融。可以通過配置文件my.cnf來啟用
show variables like 'slow_%';
set global slow_query_log=on;
（2）刪除慢查詢?nèi)罩?br> set global slow_query_log=0;

日志管理參考

• MySQL服務(wù)器鏡像配置
• MySQL服務(wù)器優(yōu)化配置
• 優(yōu)化MySQL服務(wù)器
• 運行多個MySQL服務(wù)器

17 PHP訪問MySQL數(shù)據(jù)庫

PHP語言概述
PHP操作MySQL數(shù)據(jù)庫的步驟
使用PHP操作MySQL數(shù)據(jù)庫
PHP管理MySQL數(shù)據(jù)庫中的數(shù)據(jù)
常見問題與解決方法
MySQL與PHP的應(yīng)用實例---迷你日記

18其他小知識

在Mysql的命令行工具里面給select查詢加入一個\G參數(shù)豫柬。橫向的表結(jié)構(gòu)會轉(zhuǎn)為使用縱向表結(jié)構(gòu)輸出告希，利于閱讀。
（1）select * from deal; ---橫行輸出
(2) select * from deal \G ; ---縱向輸出
\g 的作用是分號和在sql語句中寫’;’是等效的
\G 的作用是將查到的結(jié)構(gòu)旋轉(zhuǎn)90度變成縱向

項目實戰(zhàn)

18 Java與MySQL數(shù)據(jù)庫——圖書管理系統(tǒng)

19 C語言與MySQL數(shù)據(jù)庫——圖書管理系統(tǒng)

需求分析
系統(tǒng)設(shè)計
數(shù)據(jù)庫設(shè)計
C語言開發(fā)數(shù)據(jù)庫程序的流程
數(shù)據(jù)庫管理模塊設(shè)計
文件引用
變量和函數(shù)定義
管理模塊設(shè)計