第十九~第二十三章
第十九章
INSERT 是用来插入（或添加）行到数据库表的，有以下几种方式
· 插入完整的行
· 插入行的一部分
· 插入多行
· 插入某些查询的结果
可针对每个表或每个用户利用MySQL的安全机制禁止使用INSERT语句
插入完整的行，要求表名和插入新行的值
INSERT INTO customers
VALUES(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
这种格式下，每个列必须提供一个值，次序也应该与定义列的次序相同，没有值则填NULL（假定该列允许为空值），若某列由MySQL自动增量，也指定为NULL，但这种语法不安全（表的结构以后可能会变动），应尽量避免使用
更安全的方法
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES('Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
VALUES中的值以之前指定的次序（不需要按照原有表的次序）填写，这种方法即使表的结构改变也能正确工作，因为cust_id的值是不必要的，所以可以不用出现
若满足以下条件之一在INSERT语句中可以省略
· 该列定义为允许NULL值（无值或者空值）
· 在表定义中给出默认值，表示如果不给出值，将使用默认值
若数据检索更重要，可以在INSERT和INTO之间添加关键字LOW_PRIORITY降低INSERT的优先级
插入多行可以使用多条INSERT语句多次插入，或者，只要每条INSERT语句中的列名和次序相同，可以如下形式
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES(
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046'
'USA'
),
(
'M, Martian',
'42 Galaxy Way',
'New York',
'NY',
'11213',
'USA'
);
每组值用一对圆括号括起来，用逗号分隔，该方法比使用多条INSERT语句更快
插入检索出的数据，由一条INSERT语句和SELECT语句组成，比如我们想从一个表中合并客户列表到customers表中，不需要每次读取一行再插入（假设有一个名为custnew的表，结构与customers相同，要注意custnew表中的cust_id不能与customers中的重复或者省略这列值在导入到另一个表时有MySQL生成）
INSERT INTO customers(cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_email,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
若这个表为空，则不插入，这是合法的，也可以在INSERT和SELECT省略cust_id这一列，有MySQL生成值
INSERT和SELECT中的列名不一定要相同，MySQL使用的是列的位置，SELECT中的第一列将用来填充INSERT指定的第一列……这对于从使用不同列名的表导入数据是非常有用的
在INSERT SELECT中SELECT语句可包含WHERE子句来过滤插入的数据
第二十章
更新（修改）表中的数据可以使用UPDATE，可以更新特定的行或者更新所有行
UPDATE主要由更新的表、列名和新的值、确定更新行的过滤条件三部分组成，注意WHERE子句的重要性
如客户10005现在有了电子邮件地址，需要更新
UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
若没有WHERE子句，将会更新所有行
更新更多的列
UPDATE customers
SET cust_name = 'The Fudds',
cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;
在UPDATE中也可以使用子查询
在更新多行时，为避免一行或多行出现错误而取消整个操作（取消后之前更新的行恢复到原有值）可以写作UPDATE IGNORE customers…
为删除某个列的值，可以将其设为NULL（前提是允许为NULL）
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
用DELETE删除特定行或者所有行，同样不要省略WHERE子句
DELETE FROM customers
WHERE cust_id = 10006;
若不加WHERE子句，将删除表中每个客户
DELETE删除整行而不是列，删除列使用UPDATE，但DELETE不删除表本身
删除所有行用TRUNCATE TABLE语句，速度更快（实际是删除原来的表并重新生成一个表，而不是逐行删除）
更新和删除的原则
· 除非确实打算修改每行，否则必须使用WHERE子句
· 保证每个表都有主键，尽可能像WHERE子句那样使用
· 可以先使用SELECT来测试使用的WHERE是否过滤正确的行，保证正确性
· 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联数据的行
· MySQL没有撤销按钮，小心使用UPDATE和DELETE
第二十一章
可以用CREATE TABLE来创建表，事实上，使用交互式工具时，界面工具生成相应的MySQL语句并执行
创建一个新表需要给出表的名字，表列的名字和定义，或者可以给出更多信息
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(5) NULL,
cust_zip char(10) NULL,
cust_country char(50) NULL,
cust_contact char(50) NULL,
custy_email char(255) NULL,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
缩进的大小和各列之间的空格没有规定
表名紧跟在CREATE TABLE后，表的定义在圆括号之中，每列的定义以列名开始，后跟列的数据类型，表的主键可以在创建表时指定
在创建新表时，指定的表名必须不存在，若要防止意外覆盖已有的表，先手工删除该表，可以在表名后给出IF NOT EXISIT，检查表名是否存在并在表名不存在时创建
如果指定了NOT NULL，则在插入时必须有值，NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL
要注意NULL不是空串，NULL值是没有值，如果指定’’(两个单引号，中间没有字符)，这在NOT NULL列中是允许的
如果主键使用多个列，则这些列的组合值必须唯一，在创建表时用逗号分隔各列
CREATE TABLE orderitems
(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL,
item_price decimal(8,2)NOT NULL,
PRIMARY KEY(order_num, order_item)
) ENGINE=InnoDB;
主键也可以在创建表之后定义，只能使用不允许NULL的列
AUTO_INCREMENT告诉MySQL，本列每增加一行时自动增量，这样给每个行分配一个唯一的cust_id，每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如：通过使它成为主键）
覆盖AUTO_INCREMENT，可以简单地在INSERT语句中指定一个值，只要之前没有出现过即可，该值将代替自动生成的值，后续的增量将开始使用该手工插入的值
SELECT last_insert_id()
该语句返回最后一个AUTO_INCREMENT值
如果插入行时没有给出值，MySQL允许指定此时使用的默认值
CREATE TABLE orderitems
(
order_num int NOT NULL,
order_item char(10) NOT NULL,
prod_id char(10) NOT NULL,
quantity int NOT NULL DEFAULT 1,
item_price decimal(8,2)NOT NULL,
PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;
在不给出数量的情况下使用数量1，但不允许使用函数作为默认值，只支持常量
MySQL有一个具体管理和处理数据的内部引擎 ，在使用CREATE TABLE或者SELECT等语句时，由该引擎来处理请求，MySQL有多种引擎，具有不同的功能和特性，如果忽略ENGINE=语句，则使用默认引擎（很可能是MyISAM）
· InnoDB是一个可靠的事务处理引擎，不支持全文本搜索
· MEMORY在功能上等同于MyISAM，但数据存储在内存上（不是磁盘）中，速度很快，适合于临时表
· MyISAM性能极高，支持全文本搜索，但不支持事务处理
引擎类型可以混用，如一个表用MyISAM，另一个用InnoDB，但有一个缺陷，外键不能跨引擎，即用一个引擎的表不能引用使用不同引擎的表的外键
可使用ALTER TABLE更新表
给vendors表增加一个名为vend_phone的列，必须明确其数据类型
ALTER TABLE vendors
ADD vend_phone CHAR(20);
删除刚刚添加的列
ALTER TABLE vendors
DROP COLUMN vend_phone;
还可以定义外键
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num)
REFERENCES orders (order_num);
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products
FOREIGN KEY (prod_id)
REFERENCES products (prod_id);
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (cust_id)
REFERENCES customers (cust_id);
ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id)
REFERENCES vendors (vend_id);
如果是对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔
复杂的表结构更改一般需要手动删除过程，应该在使用ALTER TABLE改动表前做好备份，数据库表的更改不能撤销
删除表（整个表而不是其内容），不能撤销，永久删除
DROP TABLE customers2;
重命名表
RENAME TABLE customers2 TO customers;
对多个表重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
第二十二章
视图是虚拟的表，不同的是视图只包含使用时动态检索数据的查询
SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num
AND prod_id = 'TNT2';
如果把上述整个查询包装成一个名为productcustomers的虚拟表就会方便很多
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
作为视图，不包含表中的任何列或数据，它包含的是一个SQL查询
可以用与表基本相同的方式利用它们，可以执行SELECT操作，过滤排序数据，联结到其他表或者视图，等等
使用视图可以简化操作，在编写查询后可以多次重用，它仅使用表的一部分，保护数据
视图的使用规则和限制
· 命名要唯一，但数目没有限制
· 视图可以嵌套，可以从其他视图中检索数据的查询来构造一个视图
· ORDER BY可以用在视图中，如果该视图的SELECT语句中也含有ORDER BY，则原有的ORDER BY会被覆盖
视图用CREATE VIEW语句来创建
用SHOW CREATE VIEW viewname; 来查看创建视图的语句
用DROP VIEW viewname; 删除视图
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW，如果要更新的视图不存在，则第2条更新语句会创建一个视图，若存在，则会替换
视图最常见的应用之一是隐藏复杂的SQL，这通常会涉及联结
CREATE VIEW productcustomers AS
SELECT cust_name, cust_contact, prod_id
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = orders.order_num;
这个视图返回已订购了任意产品的所有客户的列表
执行
SELECT * FROM productcustomers
将列出订购了任意产品的客户
为了检索产品TNT2的客户
SELECT cust_name, cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';
可以用视图重新格式化检索出的数据
CREATE VIEW vendorlocations AS
SELECT Contact(RTrim(vend_name), ' (', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
这条语句使用与以前的SELECT语句相同的查询创建视图，为了检索出以创建所有邮件标签的数据
SELECT *
FROM vendorlocations;
可以用视图过滤不想要的数据，比如过滤没有电子邮件的客户
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
现在可以像使用其他表一样使用该视图
SELECT *
FROM customeremaillist;
如果在使用视图时也有WHERE子句，则和视图中的WHERE子句会自动组合
视图对于简化计算字段的使用特别有用
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;
为检索订单20005的详细内容
SELECT *
FROM orderitemexpanded
WHERE order_num = 20005;
视图是可更新的，可以使用INSERT、UPDATE和DELETE，更新一个视图将更新其基表，如果对视图增加或删除行，实际是对其基表增加或删除行，但并非所有视图都可以更新，如果视图定义中有如下操作，则不能进行视图更新
· 分组（GROUP BY和HAVING）
· 联结
· 子查询
· 并
· 聚集函数（Min()，Count()，Sum()等）
· DISTINCT
· 导出（计算）列
第二十三章
存储过程简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合
使用存储过程的优点：简单、安全、高性能
MySQL把执行存储过程称为调用，使用CALL接受存储过程的名字以及需要传递给它的任意此参数
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);
存储过程可以显示结果，也可以不显示结果
创建一个返回产品平均价格的存储过程
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END;
如果存储过程接受参数，可以在()中列出来，BEGIN和END用来限定存储过程体，这个创建的过程不返回数据，只是为以后使用而创建
在命令行的模式下，由于代码里有两个分号，会出现错误，可以临时更改分隔符，除了\以外，任何字符都可以用作语句分隔符
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END //
DELIMITER ;
使用这个过程
CALL productpricing();
存储过程实际上是一种函数
存储过程创建以后，被保存在服务器上供使用，直至被删除，删除语句如下
DROP PROCEDURE productpricing;
注意最后没有()
如果不确定要删除的过程是否存在，使用DROP PROCEDURE IF EXISTS
一般存储过程并不显示结果，而是把结果传给指定的变量
以下是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;
SELECT语句中，INTO子句和FROM子句顺序可调换
此存储过程接受3个参数，每个参数必须具有指定的类型，这里使用十进制，关键字OUT指出相应的参数用来从存储过程中传出一个值，MySQL还支持IN（传递给存储过程）、INOUT（传入和传出），SELECT语句检索值，并通过INTO保存到相应变量
调用此存储过程
CALL productpricing(@pricelow,
@pricehigh,
@priceaverage);
为了显示检索出的产品的平均价格
SELECT @priceaverage;
获得三个值
SELECT @pricehigh, @pricelow, @priceaverage;
使用IN和OUT参数，ordertotal接受订单号并返回该订单的合计
CREATE PROCEDURE ordertotal(
IN onumber INT,
OUT ototal DECIMAL(8,2)
)
BEGIN
SELECT Sum(item_price*quantity)
FROM order_items
WHERE order_num = onumber
INTO ototal;
END;
INTO子句可以跟在SELECT之后
调用新的存储过程
CALL ordertotal(20005, @total);
显示合计
SELECT @total;
为了得到另一个订单的合计显示，再次调用存储过程，重新显示变量
CALL ordertotal(20009, @total);
SELECT @total;
建立智能存储过程
CREATE PROCEDURE ordertotal(
IN onumber INT
IN taxable BOOLEAN
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
DECLARE total DECIMAL(8,2);
DECLARE taxrate INT DEFAULT 6;
SELECT Sum(item_price*quantity)
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;
IF语句检查是否需要增加营业税到局部变量total，最后用另一个SELECT语句将total保存到ototal
COMMENT值不是必须的，如果给出将在SHOW PROCEDURE STATUS的结果中显示
试验此存储过程
CALL ordertotal(20005, 0, @total);
SELECT @total;
CALL ordertotal(20005, 1, @total);
SELECT @total;
BOOLEAN值非零（包括负数）为真，0为假
IF还支持ELSEIF和ELSE（前者使用THEN子句，后者不使用）
为显示用来创建一个存储过程的CREATE语句
SHOW CREATE PROCEDURE ordertotal;
为了获得包括何时、由谁创建等详细信息的存储过程列表
SHOW PROCEDURE STATUS
上述语句列出所有存储过程，可以用LIKE过滤
SHOW PROCEDURE STATUS LIKE 'ordertotal';