读书笔记-MySql必知必会（四）

第19章 插入数据

提要： 本章介绍了INSERT语句。

1：

插入数据的方式：

• 插入完整的行；
• 插入行的一部分；
• 插入多行；
• 插入某些查询的结果。

2:

INSERT INTO `user` VALUES (NULL,'kobi','男',32,'旧金山');
-- 虽然这种语法很简单，但并不安全，应该尽量避免使用。

3:

INSERT INTO `user` ( NAME, sex, age, address ) VALUES ( 'li', '男', 22, '郑州' );
-- 插入行的一部分

4：

INSERT INTO `user` ( NAME, sex, age, address ) VALUES ( '一', '男', 23, '杭州' ),('er','女',12,'太原'); -- 插入多行 

5：

INSERT  LOW_PRIORITY INTO `user` ( NAME, sex, age, address ) VALUES ( 'li', '男', 22, '郑州' ); -- 降低INSERT语句的优先级

6：

INSERT INTO  name_en(`name`) SELECT `name` FROM name_zh; -- 插入查询的结果

第20章 更新和删除数据

提要： 本章主要介绍了 UPDATE 和 DELETE 语句。

1：

UPDATE `user` SET age = 21,address = '沈阳' WHERE id = 1; -- 更新

2：

UPDATE IGNORE `user` SET age = 21,address = '沈阳' WHERE id >10; -- 更新多行，当某行出现错误时继续进行

3：

UPDATE  `user` SET address = NULL WHERE id =2; -- 删除某列的值

4：

DELETE FROM  `user` WHERE id =2; -- DELETE 删除表的内容不删除表

5：

TRUNCATE TABLE name_en; -- 更快的删除：删除表再重建而不是一行行删除内容

6：

指导原则：

• 除非确实打算更新和删除每一行，否则绝对不要使用不带 WHERE子句的 UPDATE 或 DELETE 语句。
• 保证每个表都有主键，尽可能像 WHERE 子句那样使用它。
• 使用 WHERE 子句前，应该先用 SELECT 进行测试，保证它过滤的是正确的记录。
• 使用强制实施引用完整性的数据库，样MySQL将不允许删除具有与其他表相关联的数据的行。

第21章 创建和操纵表

提要： 本章介绍了表的相关操作。

1：

创建表

CREATE TABLE IF NOT EXISTS `orders` ( -- 不存在时创建
id INT NOT NULL AUTO_INCREMENT, -- 自增
city VARCHAR(32) NOT NULL, -- 不允许为NULL/''是允许的
state VARCHAR(1) DEFAULT 1, -- 指定默认值
PRIMARY KEY(id,city) -- 指定主键
)ENGINE = INNODB -- 指定引擎/InnoDB支持事务，MyISAM更快/外键不能跨引擎

SELECT LAST_INSERT_ID() ; -- 返回“客户端”AUTO_INCREMENT(INSERT或UPDATE)设置的最后一个值，当有多行时，返回第一行的值。

2：

更新表

ALTER TABLE orders ADD nums INT(10); -- 新增字段
ALTER TABLE orders DROP COLUMN nums INT(10); -- 删除字段
ALTER TABLE `course` ADD CONSTRAINT ding FOREIGN KEY(user_id) REFERENCES `user`(id);
-- 添加外键
-- 格式：ALTER TABLE 表 ADD CONSTRAINT 外键名 FOREIGN KEY(字段) REFERENCES 参考表(字段);

3：

DROP TABLE `course` -- 删除表

4：

RENAME TABLE a TO b,c TO d; -- 重命名表

第22章 视图

提要： 本章主要介绍了视图的使用。

1：

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

2：

使用视图的原因：

• 重用SQL语句；
• 简化复杂的SQL操作。
• 使用表的组成部分而不是整个表。
• 保护数据。
• 更改数据格式和表示。

3：

CREATE VIEW test_view AS SELECT * FROM `user` -- 创建视图
SHOW CREATE VIEW test_view; -- 看创建视图的语句
 DROP VIEW test_view; -- 删除视图
 CREATE or REPLACE VIEW test_view AS SELECT * FROM `order` -- 更新视图（不存在创建/存在更新）

4：

视图也可以更新，除以下情况：

• 分组（使用 GROUP BY 和 HAVING ）；
• 联结；
• 子查询；
• 并；
• 聚集函数（ Min() 、 Count() 、 Sum() 等）；
• DISTINCT；
• 导出（计算）列。

小结： 视图为虚拟的表，主要是查询数据。

第23章 使用储存过程

提要： 本章主要介绍了储存过程的定义/意义/使用等。

1：

定义：就是为以后的使用而保存的一条或多条MySQL语句的集合。可将其视为批文件，虽然它们的作用不仅限于批处理。

2：

优势：

• 通过把处理封装在容易使用的单元中，简化复杂的操作；
• 由于不要求反复建立一系列处理步骤，这保证了数据的完整性，防止错误；
• 简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，只需要更改存储过程的代码；
• 提高性能；
• 存在一些只能用在单个请求中的MySQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码；

劣势：

• 存储过程的编写比基本SQL语句复杂；
• 缺乏创建存储过程的安全访问权限；

3：

执行

CALL productpricing(@pricelow,@pricehigh,@priceave); 
-- CALL 储存过程名(@参数,@参数……);

4:

创建：

CREATE PROCEDURE usering () -- 过程名及参数
BEGIN
	SELECT AVG( age ) FROM `user`; -- 过程体
END;

注意：使用命令行工具时需要临时更改命令行的语句分隔符。

DELIMITER //

CREATE PROCEDURE usering () 
BEGIN
	SELECT AVG( age ) FROM `user`;
END //

DELIMITER ;

5:

删除：

DROP PROCEDURE usering; -- 不存在时会报错
DROP PROCEDURE  IF EXISTS usering; -- 不存在时不会报错/书上顺序写错了

6：

变量（variable） 内存中一个特定的位置，用来临时存储数据。

使用参数：

-- 查询user表中的平均年龄
-- 创建过程
CREATE PROCEDURE usering (OUT av INT(5)) 
BEGIN
	SELECT AVG( age ) INTO av FROM `user`;
END;
-- 执行过程
CALL usering(@av); -- 变量必须以@开头
-- 查看结果
SELECT @av;

-- 查询order表中指定产品指定数量的总价格
CREATE PROCEDURE ordering(IN oid INT(5),IN num INT(5),OUT sum DECIMAL(10,2))
BEGIN
	SELECT SUM(num * price) FROM `order` WHERE id = oid INTO sum;	
END;

CALL ordering(1,10,@osum);

SELECT @osum;

7：

更高级的储存过程

-- 获得order表中指定产品的总值（外部指定是否包含税）
CREATE PROCEDURE totalorder(IN oid INT, IN otaxable boolean,OUT ototal DECIMAL(10,2))
BEGIN
	DECLARE total DECIMAL(10,2); -- 定义局部变量
	DECLARE taxrate INT DEFAULT 6; -- 指定默认值
	SELECT price * number FROM `order` WHERE id = oid INTO total;
	IF otaxable THEN -- 条件判断
		SELECT total +taxrate*6/100 INTO total;
	END IF;
	SELECT total INTO ototal; -- 将结果赋值给最终值
END

CALL totalorder (2,1,@ototal);

SELECT @ototal; -- 260.36

8：

显示储存过程的相关信息：

SHOW CREATE PROCEDURE usering;

小结： 储存过程本质上就是函数。

第24章 使用游标

提要： 本章主要介绍了游标的相关信息。

游标（cursor） 是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT 语句，而是被该语句检索出来的结果集。

1：

使用步骤：

• 在使用游标前，必须声明它。这个过程实际上没有检索数据，它只是定义要使用的 SELECT 语句；
• 一旦声明后，必须打开游标以供使用。这个过程把数据实际检索出来；
• 对于填有数据的游标，根据需要取出（检索）各行；
• 在结束游标使用时，必须关闭游标。

2：

创建游标

CREATE PROCEDURE totalorder()
BEGIN
	DECLARE ordernum CURSOR
	FOR 
	SELECT number FROM `order`;
END
-- 储存过程处理结束后，游标就消失（因为它局限于存储过程）。

3：

打开/关闭

CREATE PROCEDURE totalorder()
BEGIN
	DECLARE ordernum CURSOR
	FOR 
	SELECT number FROM `order`;
	OPEN ordernum; -- 打开游标
	CLOSE ordernum; -- 关闭游标
END -- 隐性关闭游标

4：

使用游标

CREATE PROCEDURE totalorder()
BEGIN
	DECLARE a INT; -- 变量要定义在游标前面
	DECLARE ordernum CURSOR
	FOR 
	SELECT number FROM `order`;
	OPEN ordernum;
	FETCH ordernum INTO a; -- 检索出数据并存放，将指针移动到下一行
	CLOSE ordernum;
END

5：

循环使用

CREATE PROCEDURE totalorder()
BEGIN
	-- 接收变量
	DECLARE a INT; 
	-- 循环结束标识
	DECLARE done boolean DEFAULT 0; 
	-- 定义游标
	DECLARE ordernum CURSOR
	FOR 
	SELECT number FROM `order`;
	-- 定义游标的结束
	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done =1;
	-- 打开游标
	OPEN ordernum; 
	-- 重复执行
	REPEAT 
		-- 注意点
		SELECT age FROM user WHERE age > 1000; 
		-- 执行游标
		FETCH ordernum INTO a;
		-- 业务语句
		INSERT INTO `order`(name,number) VALUES('新增',a);
	-- 结束执行
	UNTIL done END REPEAT; 
	-- 关闭游标
	CLOSE ordernum; 
END;
-- 调用过程
CALL totalorder();

注意：

• DECLARE 语句的次序必须是变量-游标-句柄（暂时理解为包含CONTINUE HANDLER的语句）；
• 当注意点的语句没有查询到数据时，就会触发done=1,导致游标遍历不完整的问题。

第25章 使用触发器

提要： 本章介绍了触发器的相关内容。

触发器是MySQL响应DELETE/INSERT/UPDATE/( BEGIN 和 END 之间)语句而自动执行的一条MySQL语句。

1：

创建触发器

所需信息：

• 唯一的触发器名；
• 触发器关联的表；
• 触发器应该响应的活动（ DELETE 、 INSERT 或 UPDATE ）；
• 触发器何时执行（处理之前或之后）。

CREATE TRIGGER -- 创建触发器
newuser -- 指定触发器名
AFTER  -- 指定何时触发
INSERT -- 指定触发器响应的活动
ON `user`  -- 指定为哪个表添加触发器
FOR EACH ROW -- 为每一行
INSERT INTO `order`(name) VALUES('added'); -- 指定触发后执行的语句

• 每个表支持最多6个触发器（INSERT/UPDATE/DELETE之前和之后）；
• BEFORE失败，语句和AFTER都不会执行；
• 语句失败，AFTER不执行。

2：

DROP TRIGGER newuser; -- 删除

3：

INSERT触发器

• 在 INSERT 触发器代码内，可引用一个名为 NEW 的虚拟表，访问被插入的行；
• 在 BEFORE INSERT 触发器中， NEW 中的值也可以被更新（允许更改被插入的值）；
• 对于 AUTO_INCREMENT 列， NEW 在 INSERT 执行之前包含 0 ，在 INSERT执行之后包含新的自动生成值。

CREATE TRIGGER newuser AFTER INSERT ON `user` FOR EACH ROW INSERT INTO `order`(name) VALUES(new.id); -- 将user表中自增的id插入order表

4：

DELETE触发器

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

CREATE TRIGGER deluser BEFORE DELETE ON `user` FOR EACH ROW 
BEGIN
	INSERT INTO `order`(name) VALUES (OLD.name);
END; -- 将user表删除的数据插入order表

5：

UPDATE触发器

• 在 UPDATE 触发器代码中，你可以引用一个名为 OLD 的虚拟表访问以前（ UPDATE 语句前）的值，引用一个名为 NEW 的虚拟表访问新更新的值；
• 在 BEFORE UPDATE 触发器中， NEW 中的值可能也被更新（允许更改将要用于 UPDATE 语句中的值）；
• OLD 中的值全都是只读的，不能更新。

CREATE TRIGGER upuser BEFORE UPDATE ON `user` FOR EACH ROW SET NEW.name = UPPER(NEW.name); -- 将更新的名字都大写

第26章 管理事务处理

提要： 本章主要介绍了COMMIT 和 ROLLBACK 语句的使用。

事务处理（transaction processing） 可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。

事务（ transaction ） 指一组SQL语句；

回退（ rollback ） 指撤销指定SQL语句的过程；

提交（ commit ） 指将未存储的SQL语句结果写入数据库表；

保留点（ savepoint ） 指事务处理中设置的临时占位符（place-holder），你可以对它发布回退（与回退整个事务处理不同）。

1：

SELECT * from `user`; -- all
START TRANSACTION; -- 开启事务
DELETE FROM `user`; -- 删除
SELECT * FROM `user`; -- none
ROLLBACK; -- 回退
SELECT * FROM `user`; -- all

注意：事务处理用来管理 INSERT 、 UPDATE 和DELETE 语句。不能回退 SELECT 语句。（这样做也没有什么意
义）不能回退 CREATE 或 DROP 操作。

2:

START TRANSACTION;

DELETE FROM `user` WHERE id = 1;

COMMIT; -- 提交事务

3：

START TRANSACTION;
DELETE FROM `user` WHERE id = 4;
SAVEPOINT de1; -- 设置保留点
DELETE FROM `user` WHERE id = 5;
ROLLBACK  TO de1; -- 回退到保留点

4：

SET autocommit = 0; -- 取消自动提交/针对一个连接而不是服务器

第27章 全球化和本地化

字符集 为字母和符号的集合；

编码 为某个字符集成员的内部表示；

校对 为规定字符如何比较的指令。

使用何种字符集和校对的决定在服务器、数据库和表级进行。

1：

SHOW CHARACTER SET;  -- MYSQL支持的字符集

2：

SHOW COLLATION; --支持校对的列表

3:

-- 创建表时指定字符集和校对
CREATE TABLE my(
columnn1 INT,
columnn2 VARCHAR(1)
)DEFAULT CHARACTER SET utf8, -- 指定字符集
COLLATE utf8_general_ci; -- 指定校对

4：

-- 指定列的字符集和校对
CREATE TABLE my2(
columnn1 INT,
columnn2 VARCHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci
) ;

5：

COLLATE 子句还可以在 ORDER BY、GROUP BY 、 HAVING 、聚集函数、别名等中使用。

第28章 安全管理

访问控制 给用户提供他们所需的访问权，且仅提供他们所需的访问权。

用户账号和信息存储在名为 mysql 的数据库中，一般不需要直接访问，但是需要获得所有用户列表时例外。

-- 获得所有用户的账号
USE mysql;
SELECT user FROM `user`
-- 创建用户/任意主机访问host可用%
CREATE USER 'username' @'host' IDENTIFIED BY 'password';
-- 重命令账号
RENAME USER test_d TO user_d;
-- 删除账号
DROP USER user_d;

-- 显示用户的权限
SHOW GRANTS FOR user_d;
-- 授权
GRANT privileges -- 权限，例：SELECT/UPDATE
ON databasename.tablename -- 数据库.表/所有可用*.*
TO 
'username'[@'host'] -- 主机可省略
-- 撤销权限
REVOKE SELECT ON test.* TO user_d;
-- 更改密码/新密码需要使用PASSWORD函数加密
SET PASSWORD FOR 'username'[@'host'] = PASSWORD('newpassword');
-- 更改当前用户的密码
SET PASSWORD = PASSWORD('123456');

第29章 数据库维护

本章主要是DBA做的一些事情，不详细记录了。

第30章 改善性能

• MySQL（与所有DBMS一样）具有特定的硬件建议；
• 关键的生产DBMS应该运行在自己的专用服务器上；
• MySQL是用一系列的默认设置预先配置的，但过一段时间后可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用 SHOW VARIABLES; 和 SHOW STATUS; ）；
• MySQL一个多用户多线程的DBMS，如果某一个任务执行缓慢，则所有请求都会执行缓慢；使用 SHOW PROCESSLIST;显示所有活动进程,使用KILL杀死进程；
• 总是有不止一种方法编写同一条 SELECT 语句。应该试验联结、并、子查询等，找出最佳的方法；
• 使用 EXPLAIN 语句让MySQL解释它将如何执行一条 SELECT 语句；
• 一般来说，存储过程执行得比一条一条地执行其中的各条MySQL语句快；
• 应该总是使用正确的数据类型；
• 决不要检索比需求还要多的数据；
• 有的操作（包括 INSERT ）支持一个可选的 DELAYED 关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作；
• 在导入数据时，应该关闭自动提交；
• 必须索引数据库表以改善数据检索的性能；
• 使用 UNION 代替OR；
• 索引改善数据检索的性能，但损害数据插入、删除和更新的性能；
• LIKE 很慢。一般来说，最好是使用 FULLTEXT 而不是 LIKE；
• 最重要的规则就是，每条规则在某些条件下都会被打破。