《MySQL必知必会》阅读笔记（二）——增删改

2019独角兽企业重金招聘Python工程师标准>>>

第19章  插入数据

如果数据检索是最重要的，在INSERT和INTO之间添加关键字LOW_PRIORITY，可降低INSERT语句的优先级，也适用于UPDATE和DELETE语句

从一个新表中导入数据到另一个表中
INSERT INTO p1(.......)  SELECT  (......)  FROM p2;

若不能保证主键值不重复，可省略此列，MySQL会自动生成新值

导入时使用的是列的位置而不是列名

第20章  更新和删除数据

基本的UPDATE语句由3部分构成：要更新的表，列名和它们的新值，确定更新行的过滤条件

UPDATE customers SET cust_name = 'The Fudds', cust_email = 'elmer@fudd.com' WHERE cust_id = 1005;

DELETE删除的是整行，删除指定的列则使用UPDATE语句

DELETE FROM customers WHERE cust_id = 1006;

第21章  创建和操纵表

创建表的两种方法：直接用MySQL语句操纵，使用具有交互式创建和管理表的工具（其内部也是使用MySQL语句操纵）

CREATE TABLE osc_customers
{ 
   id     int(11)    NOT NULL AUTO_INCREMENT,
   user   int(16)    NOT NULL,
   email  char(255)  NULL,
   PRIMARY KEY (id)
}ENGINE=InnoDB

AUTO_INCREMENT  键值自增，每个表只允许一个且它必须被索引

NULL，允许缺值

PRIMARY KEY   指定主键，可指定多个，主键不能为null

表名后给出IF NOT EXISTS表示仅在一个表不存在时创建它

DEFAULT  指定默认值

引擎类型

MySQL具有多个引擎，它们各自有不同的功能特性，为不同的任务选择合适的引擎能获得良好的功能

不指定则使用默认引擎

InnoDB：可靠的事物处理引擎，不支持全文本搜索

MyISAM：性能极高的引擎，支持全文本搜索，不支持事物处理

MEMORY：在功能上等同于MyISAM，但由于数据存储在内存中，速度很快（特别适合于临时表）

引擎完整列表及其特性：http://dev.mysql.com/doc/refman/5.7/en/storage_engines.html 误

外键不能跨引擎：即一个引擎的表不能引用具有使用不同引擎的表的外键

更新表

使用ALTER TABLE更新表

给表增加一个列

ALTER TABLE vendors ADD vend_phone CHAR(20);

删除一个列

ALTER TABLE vendors DROP COLUMN vend_phone;

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

删除表

DROP TABLE tableName;

重命名表

RENAME TABLE tableName1 TO tableName2;

第22章  使用视图

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

1. 重用SQL语句

2. 简化复杂的SQL操作，在编写查询后，可以方便的重用它而不必知道它的基本查询细节

3. 使用表的组成部分而不是整个表

4. 保护数据。可以给用户表的特定部分的访问权限而不是整个表的访问权限

5. 更改数据格式和表示

创建视图：CREATE VIEW

删除视图：DROP VIEW

第23章  使用存储过程

经常会有一个完整的操作需要多条语句才能完成，存储过程就是将多条操作语句封装在容易使用的单元中，简化复杂的操作

可简化对变动的管理。提高性能，使用存储过程比使用单条SQL语句要快

存在一些只能用在单个请求中的MySQL中的元素和特性，存储过程可以使用它们来编写更强的代码

执行存储过程

MySQL称存储过程的执行为调用，语句为CALL，接受存储过程的名字以及需要传递给它的任意参数

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

创建存储过程

CREATE PROCEDURE productpricing()
BEGIN
     SELECT Avg(prod_price) AS priceaverage
     FROM products;
END;

mysql命令行实用程序分隔符默认为 ; 

可临时更改，DELIMITER //     定义分隔符为//

DELIMITER //

CREATE PROCEDURE productpricing()
BEGIN
     SELECT Avg(prod_price) AS priceaverage
     FROM products;
END//

DELIMITER ;

删除存储过程    DROP PROCEDURE productpricing;

使用参数

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT Min(prod_price) INTO pl FROM products;
    SELECT Max(prod_price) INTO ph FROM products;
    SELECT Avg(prod_price) INTO pa FROM products;
END;

OUT：指出相应的参数用来从存储过程中传出一个值（返回给调用者）

IN：传递给存储过程

调用该存储过程，调用时并不显示任何数据

CALL productpricing(@pricelow, @pricehigh, @priceaverage);

 显示数据

SELECT @pricehigh, @pricelow, @priceaverage;

使用IN和OUT参数

CREATE PROCEDURE ordertotal(
       IN onumber INT,
       OUT ototal DECIMAL(8,2)
)COMMENT ‘This is a test'
BEGIN
    SELECT Sum(item_price*quanity) FROM orderitems 
           WHERE order_num = onumber INTO ototal;
END;

调用该存储过程

CALL ordertotal(20005,@total);

存储过程中注释  --

COMMENT关键字：非必需，如果给出，将在SHOW PROCEDURE STATUS的结果中显示

检查所有存储过程：SHOW PROCEDURE STATUS

第24章  使用游标

游标：是一种能从包括多条数据的结果集中每次提取一条记录的机制，充当指针的作用，用于对查询数据库所返回的记录进行遍历，以便进行相应的操作

只能用于存储过程

使用游标

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

书上的例子，具体参考https://my.oschina.net/githubhty/blog/909052

CREATE PROCEDURE processorders()
BEGIN
       -- Declare local variables
       DECLARE o INT;
       DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;

       -- Open the cursor
       OPEN ordernumbers;

       -- Get order number
       FETCH ordernumbers INTO o;

       -- Close the cursor
       CLOSE ordernumbers;
end;

第25章  使用触发器

触发器：使某些语句在事件发生时自动执行

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

INSERT触发器

在INSERT触发器代码内，可引用一个名为NEW的虚拟表，访问被插入的行

在BEFORE INSERT触发器中，NEW中的值可以被更新（允许更改被插入的值）

对于AUTO_INCREMENT列，NEW在INSERT执行之前包含0，在INSERT执行之后自动包含新的自动生成值

DELETE触发器

可以引用一个名为OLD的虚拟表，访问被删除的行

OLD中的值全都是只读的，不能被更新

CREATE TRIGGER deleteorder BERORE DELETE ON orders FOR EACH ROW
BEGIN
    INSERT INTO archive_orders(order_num, order_date, cust_id)
    VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

在任意订单被删除前将执行此触发器。它使用一条INSERT语句将OLD中的值（要被删除的订单）保存到一个名为archive_orders的存档表中

使用BEGIN和END语句标记触发体，可包含多条SQL语句

相对于AFTER DELETE，使用BEFORE DELETE的优点为：如果因为某种原因触发体中的语句没有成功执行（如上述例子订单没有存档），DELETE语句也不会执行

UPDATE触发器

在UPDATE触发器代码中，可以引用一个名为OLD的虚拟表访问UPDATE执行之前的值，引用名为NEW的虚拟表访问UPDATE执行之后的值

在BEFORE UPDATE触发器中，NEW中的值可以被更新

下面的例子保证州名缩写总是大写

CERATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

第26章  管理事物处理

事务处理：保证成批的MySQL操作要么完全全部执行，要么完全不执行

事物（transaction）：指一组SQL语句

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

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

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

控制事务处理

START TRANSACTION;

...

ROLLBACK;

一般的SQL语句都是隐含提交即提交操作是自动进行的，但是在事务处理块中，提交不会隐含进行。需使用COMMIT语句进行明确提交

START TRANSACTION;

...

COMMIT;

简单的ROLLBACK和COMMIT语句就可以写入或撤销整个事务处理。但是更复杂的事务处理可能需要部分提交或回退，可使用保留点进行占位

START TRANSACTION;
...
SAVEPOINT delete1;
...
ROLLBACK TO delete1;

更改默认的提交行为，autocommit值为0指示MySQL不会自动更改，直至autocommit被设置为真为止

SET autocommit=0;

 

转载于:https://my.oschina.net/u/4057396/blog/3043461