从《MySQL必知必会》中对MySQL的理解

      MySQL是一个开源的DBMS，是目前主流的关系型数据库DBMS之一。在《MySQL必知必会》中，作者通过简单实用的订单实例简而明要地介绍了MySQL，这里简单汇总。

查询

      检索+过滤+排序，用法：

SELECT columnname, ... 

               FROM tablename, ...

               [WHERE ...[BETWEEN min AND max] | [AND] | [OR]] -- 过滤行

               [UNION ...]

               [GROUP BY ...] -- 分组

               [HAVING ...] -- 分组过滤

               [ORDER BY ... DESC|ASC] -- 排序

               [LIMIT recordnumber OFFSET rowindex] 

单个table中的查询

用已知值过滤

在WHERE子句中结合AND|OR|IN|NOT IN

IN VS OR

1)简洁；2）一般更快；3）可用于动态建立WHERE子句，即可作用在SELECT子句上。

NOT关键字：可对IN、BETWEEN、EXISTS子句取反。

通配符过滤（匹配关键词）

      LIKE操作符

表明其后的搜索模式是利用通配符匹配。

用法：WHERE columnname LIKE 'search pattern'

      ‘%’通配符

表示任何字符出现任意次数。

%可出现在搜索模式的任何位置，且可用多个%，但不能匹配到NULL。

WHERE columnname LIKE 'abc%'：在columnname列中匹配以abc开始的行。

      ‘_’通配符

表示匹配任意一个字符，使用方法同%。

      通配符使用原则

通配符一般比其他明确限定范围的的搜索要慢：

1）不要过度依赖通配符，如果其他操作符能够达到相同的目的，则使用其他操作符；

2）尽量避免将通配符置于搜索模式开始处，因为这样是最慢的；

用正则表达式过滤

用于匹配文本串

用法：WHERE columnname REGEXP [BINARY] 'search pattern'（其中采用BINARY关键字表明匹配区分大小写），可以采用OR关键字匹配多个搜索模式。

LIKE与REGEXP的区别

1）LIKE后的搜索模式针对整个列；

2）REGEXP后的搜索模式针对列中的值，采用^、$也可以针对整个列。

跨表查询

联结

内部联结、外部联结、自联结。

如果SELECT语句的WHERE子句中嵌入多层SELECT子查询，一般可以用联结替代。但是，不能过度依赖联结，联结的表越多性能下降越厉害。

分组查询

GROUP BY

说明：

1）GROUP BY子句可以包含任意数目的列，即分组嵌套；

2）SELECT语句中的每个列都必须在GROUP BY子句中给出，GROUP BY子句中列出的每个列都必须是检索列或有效表达式，但不能是聚集函数，且不能用别名；

3）如果分组列中有NULL值，则NULL将作为一个分组返回；

4）GROUP BY子句必须在WHERE子句后，ORDER BY子句前。

WHERE与HAVING的区别

WHERE过滤行，HAVING过滤分组；WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤，HAVING不能使用别名，除此之外，HAVING用法与WHERE一样。

ORDER BY与GROUP BY的区别

1）ORDER BY对产生的输出进行排序；GROUP BY分组行，但输出可能不是分组的顺序，如果一定要确认顺序，则需要用ORDER BY对其分组结果排序；

2）ORDER BY可以使用未SELECT的列；GROUP BY只能使用SELECT的列或表达式列，而且必须使用每个选择列、表达式；

3）ORDER BY不一定需要；如果与聚集函数一起使用列（或表达式），GROUP BY必须使用。

SELECT子查询

即SELECT查询中嵌套SELECT查询，子SELECT查询可以出现在WHERE子句中（一般与IN关键字联用），也可以作为其上一级SELECT查询的计算字段。

组合查询

      可简化复杂的WHERE子句，简化从多个表中检索数据的工作。

UNION|UNION ALL

任何具有多个WHERE子句的SELECT查询有时可以作为一个组合查询给出。

全文本搜索

      MyISAM引擎支持全文本搜索，InnoDB不支持。

用法：WHERE Match(columnname) Against('search pattern' [WITH QUERY EXPANSION] | [IN BOOLEAN MODE])

      使用全文本搜索时，MySQL不需要分别查看每个行，不需要分别分析和处理每个词，MySQL创建指定列中各词的一个索引，搜索可以针对这些词进行，这样可以快速有效地决定哪些词匹配，哪些词不匹配，它们匹配的频率等。

与通配符搜索、正则表达式搜索的区别：

1）性能：通配符、正则表达式要求尝试匹配表中所有行，且极少使用表索引，当行数较多时，搜索较为耗时；

2）明确控制：通配符、正则表达式很难做到匹配什么同时不匹配什么的情形；

3）智能化的结果：通配符、正则表达式不能提供一种智能化的选择结果。

      FULLTEXT(columnname)

即使没有定义FULLTEXT，也可以使用布尔方式，但这是一种较为缓慢的操作，其性能随着数据量的增加而降低。所以，尽量在FULLTEXT下使用布尔方式来进行索引。在布尔方式中，不按等级值降序排序返回的行。

全文本搜索使用注意点

1）在索引全文本数据时，短词（具有3个或以下字符的词，该数值可以更改）被忽略且从索引中排除；

2）MySQL自带有一个内建的非用词列表，这些词在索引全文本数据时忽略，该列表可以修改；

3）许多词出现频率很高，搜索它们没有用处，因为返回太多的结果。因此，MySQL要求一条50%原则，如果一个词出现在50%以上的行中，则将它作为一个非用词忽略。50%原则不用于布尔方式；

4）如果表中的行数少于3行，全文本搜索不返回结果，因为每个词或者不出现，或者至少出现在50%的行中；

5）忽略词中的单引号；如don't索引为dont；

6）不具有词分隔符的语言（包括日语和汉语）不能恰当地返回全文本搜索结果；

7）仅在MyISAM数据库引擎中支持全文本搜索。

基于查询预处理

计算字段

可采用AS对其重命名；

预处理函数：Concat(...)、RTrim(...)、LTrim(...)、Trim(...)；

只有SELECT语句知道哪些是列、哪些是计算ans字段，这些对客户端是透明的。

数据处理函数

文本处理函数

Left(string, len) Right(string,len)

Length(string)

Locate(substring,string) , Locate(substring, string, pos)

Lower(string) Upper(string)

LTrim(string) RTrim(string) Trim(string)

SubString(substring, string) SubString(substring, string,pos) 

Soundex(string)

（其中pos从1开始）

日期和时间处理函数

AddDate(date,INTERVAL expr unit)

AddTime(time,INCREMENTAL)

CurDate()

CurTime()

Date(dateandtime)

DateDiff(date1, date2)

Date_Format(NOW(),'%Y-%m-%d %H:%i:%s %f')

Now()

Time(dateandtime)

DayOfWeek(dateandtime|date)

Year(dateandtime|date) Month(dateandtime|date) Day(dateandtime|date) Hour(dateandtime|time) Minute(dateandtime|time) Second(dateandtime|time) 

数值处理函数

Abs(integer|decimal) Mod(dividend, divisor)

Pi()

Sin(angle) Cos(angle) Tan(angle)

Exp(integer) Power(basenumber, powernumber) Sqrt(integer)

Rand()

聚集函数

SUM(columnname ) COUNT(*|columnname ) AVG(columnname) MAX(columnname ) MIN(columnname )，MAX、MIN主要用于数值和日期，如果作用于文本，需要其先排好序。另外，利用标准的算术操作，所有的聚集函数都可以用来执行多个列上的计算。可采用DISTINCT关键字聚集不同的值。一个SELECT语句中可采用多个聚集函数。

Table增删改 

创建

用法：

CREATE TABLE [IF NOT EXISTS] tablename

(

      columnname datatype NULL|NOT NULL [DEFAULT defaultvalue] [AUTO_INCREMENT] 

      ...

      PRIMARY KEY (columnname)

      ...

) ENGINE=[MyISAM|InnoDB|...]

关于引擎

InnoDB：支持事务处理，但不支持全文本搜索；

MyISAM：一个性能极高的引擎，支持全文本搜索，但不支持事务；

MEMORY：在功能上等同于MyISAM，数据存储在内存而非磁盘，速度很快，特别适合于临时表。

更新

      尽量避免ALTER TABLE，最好在设计表时一次性设计好，如果使用，请留意数据备份。

用法：

ALTER TABLE tablename

[ADD newcolumnname ...]

[,...]

[DROP COLUMN columnname];

用ALTER TABLE定义外键

ALTER TABLE tablename 

ADD CONSTRAINT constraintname FOREIGN KEY (columnnameforfk) REFERENCES anothertablename (columnnameofprimarykey);

删除

用法

DROP TABLE tablename;

重命名：RENAME TABLE oldname TO newname [,];

Table记录的增删改

增

基本用法：INSERT INTO tablename(...) VALUES(...);

可以插入完整的行、行的一部分、多行、某些查询的结果。

更新

基本用法： UPDATE tablename SET columnname='newvalue',... WHERE ...

可更新指定的行或所有行，如果没有指定WHERE子句则更新所有行；可用SELECT语句结果作为新值更新数据；采用NULL可以删除某个列的值（如果该列允许NULL）。

删除

基本用法：DELETE FROM tablename WHERE ...

可以删除指定的行或所有行，如果没有指定WHERE子句则删除所有行。如果需要删除所有行，采用TRUNCATE TABLE tablename;更快。TRUNCATE TABLE是删除原来的表再重新创建一个表，而DELETE是逐行删除。

视图

      视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索的数据，主要用于数据检索。

可以用与Table基本相同的方式利用它们。但视图不能索引，也不能有关联的触发器或默认值。

为什么使用视图？

1）重用SQL语句；

2）简化复杂的SQL操作，封装SQL操作；

3）使用表的组成部分而不是整个表，配合授权可以保护数据；

4）更改数据格式和表示，对底层表的数据进行预处理，对客户端透明。 

创建

用法

CREATE VIEW viewname AS SELECT ...；

删除

用法：DROP VIEW viewname；

更新

用法

可以先用DROP再用CREATE，或采取另一种做法：CREATE OR REPLACE VIEW。

变量

声明

局部变量

一般用于存储过程、函数中，作用域为其声明所在的存储过程或函数。

声明：DECLARE variableName TYPE [DEFAULT value];

示例：

DECLARE var INT DEFAULT 0;

用户变量

不用声明，直接赋值，作用域为客户端当前连接。

赋值

用法

SET varableName=value|expression;

SELECT columnName1,columnName2,...  FROM tableName WHERE condition INTO varableName1,varableName2,...;

用户变量赋值与局部变量与唯一不同的是，用户变量需要带上‘@’符号。

示例

/* 局部变量赋值 */
SET localVarable1=1;
-- Table orders中包括order_num、cust_id等column
SET localVarable2=(SELECT cust_id FROM orders WHERE order_num=20005);
SELECT order_num,cust_id,...  FROM orders WHERE order_num=20005 INTO localVarable1,localVarable2,...;

/* 用户变量赋值 */
SET @userVarable1=1;
SET @userVarable2=(SELECT cust_id FROM orders WHERE order_num=20005);
SELECT order_num,cust_id,...  FROM orders WHERE order_num=20005 INTO @userVarable1,@userVarable2,...;

流程控制语句

IF

用法：

IF condition THEN 
sql statements;
[ELSEIF condition THEN
sql statements;]
[ELSE 
sql statements;]
END IF;

示例：

CREATE DEFINER=`testdb_user`@`%` PROCEDURE `testNum`(IN a INT)
BEGIN
	IF a<0 THEN
		SELECT 'negative';
	ELSEIF a>0 THEN
		SELECT 'Positive';
	ELSE
		SELECT 'zero';
	END IF;
END

WHILE

      满足条件循环。

用法：

WHILE condition DO
         sql statements;
END WHILE;

示例：

CREATE PROCEDURE testWhile (IN a INT)
BEGIN
	DECLARE count INT DEFAULT 0;
    WHILE count!=a DO
		SET count = count + 1;
	END WHILE;
    SELECT count;
END

LOOP

用法：

loop_label:LOOP
         sql statements;
END LOOP loop_label;

示例：

CREATE DEFINER=`testdb_user`@`%` PROCEDURE `testLoop`(IN max INT)
BEGIN
	DECLARE count INT DEFAULT 0;
    DECLARE num INT DEFAULT 0;
    testlup:LOOP
		SET count = count + 1;
        IF count=max THEN
			LEAVE testlup; -- 退出LOOP，相当于break
		END IF;
        IF Mod(count, 2) THEN
			ITERATE testlup;-- 跳出本次循环，执行下一次循环,相当于continue
		END IF;
        SET num = num + 1;
	END LOOP testlup;
    SELECT count, num;
END

REPEAT

      满足条件退出循环，至少执行一次循环语句。

用法：

REPEAT
sql statements;
UNTIL condition END REPEAT;

示例：

CREATE DEFINER=`testdb_user`@`%` PROCEDURE `testRepeat`(IN a INT)
BEGIN
	DECLARE count INT DEFAULT 0;
    REPEAT
		SET count = count + 1;
	UNTIL count=a END REPEAT;
    SELECT count;
END

CASE

用法：

基于case值：

CASE case_value
WHEN when_value THEN 
sql statements;
[WHEN when_value THEN 
sql statements;]…
[ELSE 
sql statements;]
END CASE;
 基于case条件：

CASE
WHEN condition THEN 
sql statements;
[WHEN condition 
THEN sql statements;]…
[ELSE 
sql statements;]
END CASE;

示例：

CREATE DEFINER=`testdb_user`@`%` PROCEDURE `testCaseValue`(IN a INT)
BEGIN
	CASE a
		WHEN 1 THEN 
			SELECT 'case value is 1';
		WHEN 2 THEN 
			SELECT 'case value is 2';
		ELSE
			SELECT 'other case value';
	END CASE;
END

CREATE DEFINER=`testdb_user`@`%` PROCEDURE `testCaseCondition`(IN a INT)
BEGIN
	CASE
		WHEN a<0 THEN 
			SELECT 'negative';
		WHEN a>0 THEN 
			SELECT 'Positive';
		ELSE
			SELECT 'zero';
	END CASE;
END

存储过程

为什么要使用存储过程？

1）封装复杂的处理逻辑；

2）便于维护；

3）提高性能。

执行

CALL procedurename(...);

创建

CREATE PROCEDURE procedurename([IN|OUT|INOUT paraname type...)

BEGIN

...

END;

删除

DROP PROCEDURE [IF EXISTS] procedurename;

游标

      用于遍历SELECT检索的结果集，只能用于存储过程和函数。

使用步骤：

1）声明游标；

2）打开游标，用声明中的SELECT语句把数据检索出来；

3）用FETCH遍历各行；

4）关闭游标，释放相关内存及资源。

注意：DECLARE语句定义的局部变量必须在定义任意游标或句柄之前定义，而句柄必须在游标之后定义。

CREATE PROCEDURE processorders1()
BEGIN
	
	-- 声明局部变量
	DECLARE done BOOLEAN DEFAULT 0;
	DECLARE o INT;
	DECLARE t DECIMAL(8,2);
	
	-- 声明游标
	DECLARE ordernumbers CURSOR
	FOR
	SELECT order_num FROM orders;
	
	-- 声明02000异常处理
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
	
	-- 创建存储结果的table
	CREATE TABLE IF NOT EXISTS ordertotals1
		(order_num INT, total DECIMAL(8,2));
	
	-- 打开游标
	OPEN ordernumbers;
	
	-- 循环遍历，用FETCH语句取出各行数据
	REPEAT 
		FETCH ordernumbers INTO o;
		CALL ordertotal(o, 1, t);
		INSERT INTO ordertotals1(order_num, total) VALUES(o, t);
	UNTIL done END REPEAT; -- done为1时退出循环
	
	-- 关闭游标，释放相关内存和资源
	CLOSE ordernumbers;	
	
END;

触发器

为什么要使用触发器？

1）触发器可以在某个事件发生时自动执行；

2）可以用来检验数据格式；

3）可以用来创建审计跟踪；

相关说明：

1）只有表才支持触发器，视图、临时表都不支持；

2）触发器只能作用于INSERT、DELETE、UPDATE操作；

3）每个表最多支持6个触发器，每条INSERT、DELETE、UPDATE的之前和之后；

4）单一触发器不能与多个事件或多个表关联；

5）创建触发器需要SUPER权限，但是触发器的执行是自动的，如果INSERT、DELETE、UPDATE语句能执行，则相应的触发器也能执行；

6）5.6版本是支持在触发器中调用存储过程，但是不支持返回SELECT语句结果集。

创建触发器

CREATE TRIGGER triggerName BEFORE|AFTER INSERT|DELETE|UPDATE ON tableName 
	FOR EACH ROW 
	[single sql statement;]
	[
		BEGIN 
			mutiple sql statements;
		END
	]

删除触发器

DROP TRIGGER triggerName;

使用触发器

      在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行。在BEFORE INSERT触发器中，可通过修改NEW中的值来变更待插入的值。对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后包含新的自动生成值。

      在DELETE触发器代码内，可引用一个名为OLD的虚拟表，访问被删除的行。OLD中的值全都是只读的，不能更新。

      在UPDATE触发器代码内，可引用一个名为OLD的虚拟表，访问更新前的值，可引用一个名为NEW的虚拟表，访问新更新的值。在在BEFORE UPDATE触发器中，可通过修改NEW中的值来变更待更新的值。OLD中的值全都是只读的，不能更新。（虚拟表的用法：INSERT+DELETE）

事务

      MySQL中，只有InnoDB引擎支持事务，在创建表时需要明确ENGINE=InnoDB，这样才能使用事务。

事务处理用来管理INSERT、UPDATE、DELETE语句，不能回退SELECT、CREATE、DROP。

用法：

START TRANSACTION;

      sql statements

COMMIT|ROLLBACK;

可以细化指定回退点：

SAVEPOINT labelName;

...

ROLLBACK TO labelName;

异常处理

声明CONDITION

      DECLARE conditionname CONDITION FOR {SQLSTATE sqlstate_code| MySQL error code| condition_name};

提高异常代码可读性。Condition优先顺序：MYSQL错误码->SQLSTATE->命名条件（即当有多个相同错误类型的Handler捕获时，MYSQL错误码最优先）

声明HANDLER

      DECLARE {CONTINUE | EXIT} HANDLER FOR {SQLSTATE sqlstate_code| MySQL error code| condition_name}|declaredConditionname handler_actions;

捕获异常，且进行处理。

获取异常信息

      GET DIAGNOSTICS CONDITION 1 varcharVariableName1=RETURNED_SQLSTATE,varcharVariableName2= MESSAGE_TEXT;

异常错误信息包括：CLASS_ORIGIN | SUBCLASS_ORIGIN | RETURNED_SQLSTATE | MESSAGE_TEXT | MYSQL_ERRNO | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | CATALOG_NAME | SCHEMA_NAME | TABLE_NAME | COLUMN_NAME | CURSOR_NAM

示例

      第一次执行CALL testexception()，完成exceptiontable数据的添加；第二次执行CALL testexception()，由于添加的数据主键重复，则出现异常：

-- 创建exceptiontable
CREATE TABLE exceptiontable(id INT NOT NULL,PRIMARY KEY(id));

CREATE PROCEDURE testexception()
BEGIN
	-- 声明用于获取异常信息的局部变量
	DECLARE p1 VARCHAR(255);
    DECLARE p2 VARCHAR(255);
    
	-- 声明CONDITION
	DECLARE repeat_pk CONDITION FOR 1062;
	
	/* 
	  声明HANDLER
	  用三种类型：MYSQL错误码、SQLSTATE、命名条件同时捕获添加数据时的‘重复主键’异常
	*/
	DECLARE CONTINUE HANDLER FOR SQLWARNING  SET @X1= 1;
	DECLARE CONTINUE HANDLER FOR NOT FOUND  SET @X1= 2;
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SELECT 'ERROR';
	DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SELECT 'Duplicate key';
	-- 这里用到了CONDITION name，也可以直接用1062代替repeat_pk
	DECLARE CONTINUE HANDLER FOR repeat_pk GET DIAGNOSTICS CONDITION 1 p1=RETURNED_SQLSTATE,p2= MESSAGE_TEXT; 
	
	INSERT INTO exceptiontable SELECT 99;
	
	SELECT p1,p2;	
END

字符集和校对顺序

SHOW CHARACTER SET：显示当前MySQL版本支持的所有字符集；

SHOW COLLATION：显示当前MySQL版本支持的所有校对。

SHOW VARIABLES LIKE 'character%'：显示server、database、connection等不同层次所采用的字符集：

SHOW VARIABLES LIKE 'collation%'：显示server、database、connection等不同层次所采用的校对：

      实际上，字符集很少是服务器范围（甚至是数据库范围）的设置，不同的表，甚至不同的列都可能需要不同的字符集，而且都可以在创建表时指定：

CREATE TABLE mytable
(
	column1 INT,
	column2 VARCHAR(10),
	-- 在column3列指定字符集和校对
	column3 VARCHAR(10) CHARACTER SET latin1 COLLATE latin1_general_ci
) 
-- 在mytable级指定默认的字符集和校对
DEFAULT CHARACTER SET hebrew COLLATE hebrew_general_ci;

      当然，也可以在SELECT语句的ORDER BY、GROUP BY、HAVING等子句中指定COLLATE来临时指定校对：

-- 在通常不区分大小写的表上进行区分大小写
SELECT * FROM customers ORDER BY lastname,firstname COLLATE latin1_general_cs;

安全管理

数据库维护

备份

日志

调优

参考：

1.mysql异常处理：http://blog.csdn.net/seteor/article/details/17791855

2.mysql入门很简单：http://blog.csdn.net/kimsoft/article/details/6763981

3.存储过程与函数区别：http://blog.csdn.net/wangsifu2009/article/details/6725213

4.mysql日志详细解析：http://www.cnblogs.com/wangkongming/p/3684950.html

5.MySQL主从复制：http://blog.itpub.net/26355921/viewspace-1273303/

本文主要参考《MySQL必知必会》。