《MySQL必知必会》读书笔记

一、增
1.INSERT INTO:
插入数据到表时，指明列名可获得更安全的数据插入。

2.可以在INSERT 和INTO 关键字之间插入LOW_PRIORITY关键字，以达到降低插入操作的优先级。

3.插入多行数据，可以使用两个INSERT，只要每个INSERT语句以；结尾，可以一次提交。
如果插入的列相同，则可以使用形如：
INSERT INTO …
VALUES(…
…
),
(…
…
);
的方式插入，且效率高于上一种方法。

4.INSERT … SELECT…：
可以插入从一个表中检索出来的数据。
此时，SELECT里面的第一项插入到INSERT的第一项，依次对应。

二、删
1.DELETE FROM 表名
WHERE 条件

2.DELETE是删除行，而不是删除列。
删除指定的列，使用UPDATE。

3.DELETE删除的是表中的行，不会删除表本身。

4.如果要删除表中的所有行，不要使用DELETE，
使用TRUNCATE TABLE会更快，因为DELETE是逐行删除，而TRNCATE TABLE是删除原来的表并建立一个新的表。

5.小心使用，mysql没得撤销，删了就删了。

三、查
1.ORDER BY 排序
ASC 升序（默认）
DESC 降序

2.LIMIT 限制输出

3.WHERE 条件过滤
BETWEEN AND
4.OR AND
优先处理AND

5.IN（x1,x2）
在(x1,x2)范围里，IN里面还可包含其他SELECT。

6.NOT 否定后面的条件

7.通配符%与_(效率低，不要过度使用)

8.REGEXP 正则表达式
不区分大小写，若要区分，加关键字BINARY

9.转义字符在mysql中需要使用两个反斜杠，mysql自己解释一个，正则表达式解释另一个。

10.mysql使用concat()函数来拼接多个字符串。

11.Rtrim()、LTrim（）、Trim（）
删除右边、左边、两边空格

12.AS关键字赋予列别名

13.SELECT Now(); 返回当前时间

14.soundex（）函数，根据发音搜索数据(我是第一次见，np)

15.GROUP BY 分组
后面的子句不能使用别名。WITH ROLLUP 可以显示所有分组及其值。

16.HAVING 过滤分组，可以替代WHERE
而WHERE 过滤行
WHERE在分组前过滤，HAVING在分组后过滤。

17.子查询，将select作为上层查询where的条件。

18.表之间的联结，使用WHERE。
内部联结（等值联结），FROM里面使用INNER JOIN,此时不使用WHERE,而是使用ON。

19.表别名很有用，但是只能用于查询执行，不能返回到客户机。

20.自联结：为一个表取两个别名，然后作为两个表来联结使用。（比子查询速度快）

21.自然联结：排除多次出现，每个列只出现一次。（自己限定）

22.外部联结（OUTER JOIN）：与内部联结不同的是，还包括没有关联的行。在使用OUTER JOIN时，必须使用RIGHT或者LEFT关键字指定包括其（指的是关键字）右边或左边所有行的表。

23.mysql不支持*=和=*的使用。

24.要提供联结条件，否则会得出笛卡尔积（即两个表行数之积）。

25.UNION可以将多个SELECT语句联合，并把输出组合成单个查询结果集。
UNION自动去除重复的行，如果要全部显示，使用UNION ALL。
使用了UNION，每个SELECT查询必须包含相同的列、表达式或聚集函数。
使用了UNION，则只能在最后一个SELECT后使用一次ORDER BY。

26.全文本搜索（仅在MyISAM数据库引擎中支持）：使用MATCH（要匹配到的列） AGAINST（‘要查询的字符串’）
全文本搜索会对结果进行排序，等级高的优先显示。
在SELECT语句中使用，会计算出一个等级值，根据等级值降序排列。
因为数据是索引（创建表的时候用：FULLTXT（列名）来指定索引）的，所以查询速度很快。

27.查询扩展：
WHERE MATCH(note_text) AGAINST(‘anvils’ WITH QUERY EXPANSION);
WITH QUERY EXPANSION 可以查询可能相关的结果。

28.布尔文本搜索：（IN BOOLEAN MODE）会忽略词中的单引号。
具体规则可查看mysql文档
全文本布尔操作符：

四、改（更新）
1.UPDATE 表名
SET 列名1 = 值1，
列名2 = 值2
WHERE 限定条件；

2.没有WHERE限定，会将列的所有值更新。

3.UPDATE语句中可以使用子查询。

4.IGNORE：
如果需要更新多行，中途若是因为一行或多行出现一个错误，则UPDATE失败，此时，不会更新数据，可以使用IGNORE来忽略发生的错误，使之继续。UPDATE IGNORE…

5.小心使用，改了就改了，撤销不了。

五、表
1.
CREATE TABLE customers
(
cust_id INT NOT NULL AUTO_INCREMENT,
cust_name CHAR(50) NOT NULL,
cust_address CHAR(50) NULL,
cust_city CHAR(50) NULL,
cust_state CHAR(50) NULL,
cust_zip CHAR(50) NULL,
cust_country CHAR(50) NULL,
cust_contact CHAR(50) NULL,
cust_email CHAR(50) NULL,
PRIMARY KEY (cust_id)
)ENGINE=INNODB;

NOT NULL指定值不允许为空。NULL为默认设置。

2.如果仅想在一个表不存在时创建，可以在表名后使用关键字IF NOT EXISTS。

3.如果要创建多个列组合的主键：PRIMARY KEY(列1，列2)。
主键不允许存在NULL值。

4.AUTO_INCREMENT：
每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（比如将这列作为主键）。
可以在INSERT语句中插入值，覆盖自动生成的值，后续的增量将开始使用手工插入的值。

5.SELECT last_insert_id():
此语句返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的mysql语句中。

6.DEFAULT：
如果未给值，使用这个关键字指定默认值。
mysql不允许使用函数作为默认值，只允许常量。

7.引擎类型：
InnoDB：一个可靠的事务处理引擎，不支持全文本搜索。
MEMORY：功能等同于MyISAM，但是数据存储在内存，速度很快（适用于创建临时表）。
MyISAM：一个性能极高的引擎，支持全文本搜索，不支持事务处理。

8.外键不能跨引擎。

8.给表添加列：
ALTER TABLE 表名
ADD 列名 数据类型；
删除列：
ALTER TABLE 表名
DROP COLUMN 列名；
小心使用，使用前做一个完整备份。

9.ALTER TABLE常见的一种用途是定义外键。

10.删除表：
DROP 表名；

11.重命名表：
RENAME TABLE 原表名1 TO 新表名1，
原表名2 TO 新表名2；

六、视图
1.CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

2.视图是虚拟的表。只包含使用时动态检索数据的查询。
（将查询的结果封装成一个虚拟的表，直接从这个表里查询，省略了表的联结等等）

3.视图简化了复杂的SQL操作，并保护了数据。

4.视图可以嵌套。

5.ORDER BY 可以在视图中使用，但如果从该视图检索数据SELECT中也有ORDER BY，那么该视图中的被覆盖。（最近的覆盖之前的）

6.创建视图：
CREATE VIEW

7.查看创建视图的语句：
SHOW CREATE TABLE 视图名；

8.删除视图：
DROP VIEW 视图名；

9.更新视图：
（1）先用DROP，再用CREATE。
（2)CREATE OR REPLACE VIEW。

10.更新一个视图将更新它的基表。
当视图定义中有以下操作，则不能更新：
（1）分组（GROUP BY、HAVING）
（2）联结
（3）子查询
（4）并
（5）聚集函数（MIN()、COUNT()、SUM()等）
（6）DISTINCT
（7）导出（计算）列

11.一般将视图用来检索，而不会更新。

七、存储过程
1.存储过程简单的来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

2.执行存储过程：
CALL 存储过程名（参数列表）；

3. 创建存储过程：
   例：
   CREATE PROCEDURE productpricing()
   BEGIN
   SELECT AVG(prod_price) AS priceaverage
   FROM products;
   END;

4.删除存储过程：
DROP PROCEDURE productpricing;
可以使用 DROP PROCEDURE IF EXISTS来防止删除不存在过程时产生的错误。

5.存储过程将结果返回给变量。
CREATE PROCEDURE productpricing(
OUT p1 DECIMAL(8,2),
OUT p2 DECIMAL(8,2),
OUT p3 DECIMAL(8,2)
)
BEGIN
SELECT MIN(prod_price)
INTO p1
FROM products;
SELECT MAX(prod_price)
INTO p2
FROM products;
SELECT AVG(prod_price)
INTO p3
FROM products;
END;
（1）OUT指出这个参数用来传出一个值，还有IN（传给存储过程），INOUT（对存储过程传入和传出）。
（2）CALL productpricing（@pricelow, @pricehigh, @priceaverage）；
mysql变量必须以@开始。

6.检索：SELECT @pricelow;

7.一个存储过程小例子：
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT SUM(quantity*item_price)
FROM orderitems
WHERE order_num = onumber
INTO ototal;
END;

CALL ordertotal(20005,@total);

SELECT @total;

8.一个综合例子（还能这么玩？）：
– Name:ordertotal
– Parameters:onumber = order number
– taxable = 0 if ont taxable, 1 if taxable
– ototal = order total VARIABLES
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
)
BEGIN
DECLARE total DECIMAL(8,2);
DECLARE taxrate INT DEFAULT 6;

SELECT SUM(quantity*item_price)
FROM orderitems
WHERE order_num = onumber
INTO total;

IF taxable THEN
	SELECT total+(total/100*taxrate)
	INTO total;
END IF;

SELECT total INTO ototal;

END;

CALL ordertotal(20005,0,@total);
SELECT @total;

9.检查存储过程：
SHOW CREATE PROCEDURE ordertotal;

10.检查所有的存储过程：
SHOW PROCEDURE STATUS;
可用LIKE ‘存储过程名’；指定查看某一个。

八、游标
1.游标是一个存储在MySQL服务器上的数据库查询，是一个被查询出来的结果集。

2.在MySQL中，游标只能用于存储过程（和函数）。

3.创建游标：
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num
FROM orders;
END;

4.打开/关闭游标：
定义游标之后，可以打开它。要在存储过程中使用，因为游标是个局部变量。
OPEN/CLOSE ordernumbers；

5.FETCH：检索当前行的某个列。
CREATE PROCEDURE processorders()
BEGIN
DECLARE o INT;

DECLARE ordernumbers CURSOR
FOR
SELECT order_num 
FROM orders;

OPEN ordernumbers;
FETCH ordernumbers INTO o;
CLOSE ordernumbers;

END;

九、触发器
1.触发器是MySQL响应DELETE、INSERT、UPDATE语句而自动执行的一条MySQL语句（或位于BEGIN和END之间的一组语句）。

2.创建触发器（可选在操作之前还是之后执行）：
CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT ‘Product added’;

3.只有表才支持触发器，视图不支持。

4.如果BEFORE触发器失败，不仅不会进行操作，这个操作的AFTER触发器也不会执行。

5.删除触发器：
DROP TRIGGER newproduct;

6.在INSERT触发器内，可以引用一个虚拟NEW表，访问被插入的行。
在DELETE触发器内，可以引用一个虚拟OLD表，访问被删除的行。
在INSERT触发器内，可以引用一个虚拟NEW表，访问新更新的值，也可以引用一个OLD表，访问更新前的值。

十、事务处理
1.事务处理：可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。防止因故障产生的不完整操作。

2.事务：一组SQL语句
回退：指撤销SQL语句的过程
提交：将未存储的SQL语句结果写入数据库表
保留点：事务处理中设置的临时占位符，你可以对它发布回退。

3.START TRANSACTION：标志事务管理开始。
ROLLBACK：回退MySQL语句。（不能回退CREATE，DROP，SELECT）

4.在事务处理块中，需要用COMMIT进行提交，否则不会保存。

5.当COMMIT或ROLLBACK执行后，事务会自动关闭。

6.上述是完全退回，想要实现部分退回，可以使用占位符。
例：SAVEPOINT point1；
ROLLBACK TO point1；

7.当COMMIT或ROLLBACK执行后，保留点会自动释放，也可以使用RELEASE SAVEPOINT来明确释放。

8.不让操作自动提交：
SET autocommit = 0；

十一、全球化和本地化
1.字符集：字母和符号的集合。
编码：某个字符集成员的内部表示。
校对：规定字符如何比较的指令。

2.SHOW CHARACTER SET：查看可用的字符集及其描述。
SHOW COLLATION：查看所支持的完整的校对列表。

3.数据库有默认的字符集和校对，创建表时可以指定字符集和校对。

4.如果需要，串可以在字符集之间进行转换。使用CAST()，CONVERT()函数。

十二、安全管理
1.慎重使用root账号，应该创建其他用户。root对于整个MySQL服务器具有完全的控制。

2.用户账号和信息都存储在名为mysql的MySQL数据库中。
要访问它，如下：
USE mysql;
SELECT user
FROM user;

3.创建用户账号：
CREATE USER ben IDENTIFIED BY ‘p@’;
IDENTIFIED BY 'p@'给出了一个口令，不一定需要。

4.重命名一个用户账号：
RENAME USER ben TO bforta；

5.删除用户账号：
DROP USER bforta；

6.新建的账号必须分配访问权限，否则只能登陆，不能查看与操作数据。
查看权限：SHOW GRANTS FOR bforta;

7.用户定义为user@host MySQL的权限用用户名和主机名结合定义。如果不指定主机名，则使用默认的主机名%（授予用户访问权限而不管主机名）。

8.设置权限：
GRANT SELECT ON crashcourse.* TO bforta;
授予用户bforta对crashcourse数据库的所有数据SELECT的访问权限。

9.撤销权限：
REVOKE SELECT ON crashcourse.* FROM bforta;

10.GRANT和REVOKE可在几个层次上控制访问权限：
❑ 整个服务器，使用GRANT ALL和REVOKE ALL；
❑ 整个数据库，使用ON database.*；
❑ 特定的表，使用ON database.table；
❑ 特定的列；
❑ 特定的存储过程。

11.权限

12.SET PASSWORD：更新用户口令。
SELECT PASSWORD FOR bforta = Password(‘n2w p@$$w0rd’);
新口令必须传递到 Password()函数进行加密。

十三、数据库维护
1.可以使用BACKUP TABLE或SELECT INTO OUTFILE转存所有数据到外部文件。
数据可以用RESTORE TABLE来复原。

2.为了保证所有数据被写到磁盘（包括索引数据），可能需要在进行备份前使用FLUSH TABLES语句刷新数据。

3.ANALYZE TABLE：用来检查表键是否正确。
CHECK TABLE：发现和修复问题。
REPAIR TABLE：修复表（谨慎使用）。
OPTIMIZE TABLE：来收回所用的空间，优化表的性能。

4.诊断启动问题
服务器启动问题通常在对MySQL配置或服务器本身进行更改时出现。MySQL在这个问题发生时报告错误，但由于多数MySQL服务器是作为系统进程或服务自动启动的，这些消息可能看不到。
在排除系统启动问题时，首先应该尽量用手动启动服务器。MySQL服务器自身通过在命令行上执行mysqld启动。下面是几个重要的mysqld命令行选项：
❑ --help显示帮助——一个选项列表；
❑ --safe-mode装载减去某些最佳配置的服务器；
❑ --verbose显示全文本消息（为获得更详细的帮助消息与–help联合使用）；
❑ --version显示版本信息然后退出。

5.错误日志。
它包含启动和关闭问题以及任意关键错误的细节。此日志通常名为hostname.err，位于data目录中。此日志名可用–log-error命令行选项更改。
❑ 查询日志。它记录所有MySQL活动，在诊断问题时非常有用。此日志文件可能会很快地变得非常大，因此不应该长期使用它。此日志通常名为hostname.log，位于data目录中。此名字可以用–log命令行选项更改。
❑ 二进制日志。它记录更新过数据（或者可能更新过数据）的所有语句。此日志通常名为hostname-bin，位于data目录内。此名字可以用–log-bin命令行选项更改。注意，这个日志文件是MySQL 5中添加的，以前的MySQL版本中使用的是更新日志。
❑ 缓慢查询日志。顾名思义，此日志记录执行缓慢的任何查询。这个日志在确定数据库何处需要优化很有用。此日志通常名为hostname-slow.log，位于data目录中。此名字可以用–log-slow-queries命令行选项更改。
在使用日志时，可用FLUSH LOGS语句来刷新和重新开始所有日志文件。

十四、改善性能
❑ 首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。

❑ 一般来说，关键的生产DBMS应该运行在自己的专用服务器上。

❑ MySQL是用一系列的默认设置预先配置的，这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES；和SHOW STATUS;。）

❑ MySQL是一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。

❑ 总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。

❑ 使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。

❑ 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快。

❑ 应该总是使用正确的数据类型。

❑ 决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）。

❑ 有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。

❑ 在导入数据时，应该关闭自动提交。你可能还想删除索引（包括FULLTEXT索引），然后在导入完成后再重建它们。

❑ 必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。

❑ 你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。

❑ 索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。）

❑ LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。

❑ 数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。

❑ 最重要的规则就是，每条规则在某些条件下都会被打破。

十五、参考文档
MySQL文档：http://dev.mysql.com/doc/