MySQL 必知必会 -MySQL Crash Course [英]Ben Forta 刘晓霞 钟鸣 人民邮电出版社
原书 ISBN: 0672327120 中文版 ISBN: 9787115191120
分类：计算机/数据库/MySQL
配套网站：http://forta.com/books/0672327120/
21.创建和操纵表
1.创建表 CREATE TABLE
创建表的方法：1.使用具有交互式创建和管理表的工具 2.表也可以直接用 MySQL 语句操纵
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(5) NULL ,
cust_zip char(10) NULL ,
cust_country char(50) NULL ,
cust_contact char(50) NULL ,
cust_email char(255) NULL ,
PRIMARY KEY (cust_id)
) ENGINE=InnoDB;
PRIMARY KEY 指定主键
创建新表时，指定的表名必须不存在
2>.使用 NULL 值
每个表列或者是 NOT NULL列，或者是 NULL 列。若 有 NOT NULL 关键字，会阻止插入没有值的列。NULL 为默认设置
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_id int NOT NULL ,
PRIMARY KEY (order_num)
) ENGINE=InnoDB;
3>.主键再介绍
主键值必须唯一，如果使用多个列作为主键，则这些列的组合值必须唯一
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 值得列不能作为唯一标识
4>.使用 AUTO_INCREMENT
确定编号时，使本列每当增加一行时自动增量，每次执行一个 INSERT 操作时，MySQL 自动对该列增量
每个表只允许一个 AUTO_INCREMENT 列，而且它必须被索引
覆盖 AUTO_INCERMENT，可以人工指定一个值，后续的增量将开始使用该手工插入的值
确定AUTO_INCERMENT 值，可以使用 last_insert_id() 函数，此语句返回最后一个 AUTO_INCREMENT 值
SELECT last_insert_id()
5>.使用默认值 DEFAULT
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int 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;
未给出该列数据时，使用该默认值。且只支持常量
6>.引擎类型 ENGINE
如果省略 ENGINE 语句，则使用默认引擎
InnoDB，是一个可靠的事务处理引擎，它不支持全文本搜索
MEMORY，在功能上等同于 MyISAM，但由于数据存储在内存中，速度很快
MyISAM，是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理
注意：外键不能跨引擎
2.更新表 ALTER TABLE
为更新表定义，可使用 ALTER TABLE 语句。但理想情况下，当表中存储数据后，该表就不应该再被更新
为使用 ALTER TABLE 更改表结构，应给出以下信息：要更改的表名、所要更改的列表
ALTER TABLE vendors ADD vend_phone CHAR(20);
给表 vendors 表中增加一个名为 vend_phone 的列
要删除刚添加的列：
ALTER TABLE vendors DROP COLUMN vend_phone;
ALTER TABLE 的一个常见用途是定义外键
ALTER TABLE orderitems ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders (order_num);
复杂的表结构更改一般需要手动删除过程，其涉及以下步骤：1>.用新的列布局创建一个新表 2>.使用 INSERT SELECT 语句从旧表复制数据到新表 3>.检验包含所需数据的新表 4>.重命名旧表 5>.用旧表原来的名字重命名新表 6>.根据需要，重新创建触发器、存储过程、索引和外键
使用 ALTER TABLE 要即为小心，应在进行改动前做一个完整的备份
3.删除表 DROP TABLE
DROP TABLE customers2;
删除 customers2 表，注意删除表不能撤销
4.重命名表 RENAME TABLE
RENAME TABLE customers2 TO customers;
重命名多个表：
RENAME TABLE backup_customers TO customers, backup_vendors TO vendors, backup_products TO products;
22.使用视图
1.视图
视图是虚拟的表，视图只包含使用时动态检索数据的查询
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';
也可以：
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
把整个查询包装成一个名为 productcustomers 的虚拟表，则可以进行如上检索。作为视图，它不包含表中应该有的任何列或数据，它包含的是一个 SQL 查询
1>.为什么使用视图
视图的常见应用：重用 SQL 语句、简化复杂的 SQL 操作、使用表的组成部分而不是整个表、保护数据、更改数据格式和表示
视图创建后，可以用与表基本方式相同的方式使用它们
视图本身不包含数据，其返回的数据是从其他表中检索出来的。每次使用视图时，都必须处理查询执行时所需的任一个检索
2>.视图的规则和限制
2.使用视图
视图用 CREATE VIEW 语句来创建
使用 SHOW CREATE VIEW viewname; 来查看创建视图的语句
用 DROP 删除视图，其语法为 DROP VIEW viewname;
更新视图时，可以先用 DROP 再用 CREATE，也可以直接用 CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第二条更新语句会创建一个视图；如果要更新的视图存在，则第二条更新语句会替换原有视图
1>.利用视图简化复杂的联结
一个应用是隐藏复杂的 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;
可以列出视图中检索的数据
SELECT cust_name, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
用 WHERE 语句，从视图中检索特定数据
2>.用视图重新格式化检索出的数据
另一个用途是重新格式化检索出的数据
SELECT Contact(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
计算列中返回供应商名和位置
如果要经常使用此结果，可以创建一个视图：
CREATE VIEW vendorlocations AS
SELECT Contact(RTrim(vend_name), '(', RTrim(vend_country), ')') AS vend_title FROM vendors ORDER BY vend_name;
使用如下语句即可显示结果
SELECT * FROM vendorlocations;
3>.用视图过滤不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NULL;
可以使用 WHERE 语句，在视图中过滤数据
4>.使用视图与计算字段
视图对于简化计算字段的使用非常有用
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price
FROM orderitems WHERE order_num = 20005;
==>
CREATE VIEW orderitemsexpanded AS
SELECT prod_id, quantity, item_price, quantity*item_price AS expanded_price FROM orderitems;
SELECT * FROM orderitemsexpanded WHERE order_num = 20005;
视图可以简化复杂的数据处理
5>.更新视图
视图是可更新的，即可以对它们使用 INSERT、UPDATE、DELETE。注意：更新一个视图将更新其基表
但并非所有视图都可以更新，如果 MySQL 不能正确的确定被更新的基数据，则不允许更新。即如果视图定义中有如下操作就不能更新视图：分组 (GROUP BY 和 HAVING)、联结、子查询、并、聚集函数 (Min()、Count()、Sum())、DISTINCT、导出列
一般来说，应该将视图用于检索、而不用于更新
23.使用存储过程
1.存储过程
存储过程，是为以后的使用而保存的一条或多条 MySQL 语句的集合，可将其视为批文件
2.为什么要使用存储过程
简单、安全、高性能
很多数据库管理员限制存储过程的创建权限，允许用户使用存储过程，但不允许创建
3.使用存储过程
1>.执行存储过程 CALL
MySQL 称存储过程的执行为调用，CALL 接受存储过程的名字以及需要传递给它的任意参数
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
2>.创建存储过程 CREATE PROCEDURE
CREATE PROCEDURE productpricing()
BEGIN SELECT Avg(prod_price) AS priceaverage FROM products;
END;
创建一个名为 productpricing 的存储过程，若存储过程接受参数，需将其在括号内列举出来
创建时不会调用存储过程，仅为创建
若用的是 mysql 命令行实用程序，如果要解释存储过程自身内的; 字符，则他们最终不会成为存储过程的成分，其解决办法是临时更改命令行实用程序的语句分隔符
DELIMITER //
CREATE PROCEDURE productpricing()
BEGIN SELECT Avg(prod_price) AS priceaverage FROM products;
END //
DELIMITER ;
在两代码间，即可使用 // 作为新的语句结束分隔符，除 \ 外，任何符号都可以用作语句分隔符
CALL productpricing();
即可调用此存储过程
3>.删除存储过程 DROP PROCEDURE
DROP PROCEDURE productpricing;
如该过程不存在，会产生一个错误
DROP PROCEDURE IF EXISTS productpricing;
4>.使用参数
变量 (variable) 内存中一个特定的位置，用来临时存储数据
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;
MySQL 支持 IN、OUT、INOUT 类型的参数
CALL productpricing(@pricelow, @pricehigh, @priceaverage);
在调用时，这条语句不显示任何数据，它返回以后可以显示的变量，为显示这些变量：
SELECT @pricelow, @pricehigh, @priceaverage;
另一个例子：
CREATE PROCEDURE ordertotal(IN onumber INT, OUT ototal DECIMAL(8,2))
BEGIN
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO ototal;
END;
调用：
CALL ordertotal(20005, @total);
显示：
SELECT @total;
5>.建立智能存储过程
-- Name: ordertotal
-- Parameters: onumber = order number
-- taxable = 0 if not taxable, 1 if taxable
-- ototal = order total variable
CREATE PROCEDURE ordertotal(
IN onumber INT,
IN taxable BOOLEAN,
OUT ototal DECIMAL(8,2)
) COMMENT 'Obtain order total, optionally adding tax'
BEGIN
-- Declare variable for total
DECLARE total DECIMAL(8,2);
-- Declare tax percentage
DECLARE taxrate INT DEFAULT 6;
-- Get the order total
SELECT Sum(item_price*quantity) FROM orderitems WHERE order_num = onumber INTO total;
-- Is this taxable?
IF taxable THEN
-- Yes, so add taxrate to the total
SELECT total+(total/100*taxrate) INTO total;
END IF;
-- And finally, save to out variable
SELECT total INTO ototal;
END;
调用：
CALL ordertotal(20005, 0, @total); SELECT @total;
CALL ordertotal(20005, 1, @total); SELECT @total;
另外，MySQL 中的 IF 语句还支持 ELSEIF 和 ELSE 子句
6>.检查存储过程
显示用来创建一个存储过程的 CREATE 语句，可以使用 SHOW CREATE PROCEDURE 语句
SHOW CREATE PROCEDURE ordertotal;
为获得何时、有谁创建等详细信息的存储过程列表，可以使用 SHOW PROCEDURE STATUS，其输出的是所有存储过程
可以用 LIKE 限制其输出：
SHOW PROCEDURE STATUS LIKE 'ordertotal';
24.使用游标
1.游标
MySQL 检索返回一组称为结果集的行，这些都是与 SQL 语句相匹配的行，使用简单的 SELECT 语句，无法得到第一行、下一行、前 n 行，也不存在每次一行地处理所有行的简单方法
游标 (cursor) 是一个存储在 MySQL 服务器上的数据库查询，不是一条 SELECT 语句，而是该语句检索出来的结果集
其主要用于交互式应用
MySQL 中的游标只能用于存储过程和函数
2.使用游标
声明/定义游标，此过程没有检索数据，仅是定义要使用的 SELECT 语句
声明后必须打开游标以供使用，此过程用 SELECT 把数据实际检索出来
对于填有数据的游标，根据需要取出各行
在结束游标使用时，必须关闭游标
1>.创建游标 DECLARE
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR FOR SELECT order_num FROM products;
END;
游标仅存在与存储过程中，存储过程处理完成后，游标就会消失。
2>.打开和关闭游标 OPEN CURSOR
OPEN ordernumbers;
CLOSE ordernumbers;
使用过的游标，不需要再次声明
CREATE PROCEDURE processorders()
BEGIN
-- Declare the cursor
DECLARE ordernumbers CURSOR FOR SELECT order_num FROM products;
-- Open the cursor
OPEN ordernumbers;
-- Close the cursor
CLOSE ordernumbers;
END;
3>.使用游标数据 FETCH
25.使用触发器
1.触发器
2.创建触发器
3.删除触发器
4.使用触发器
1>.INSERT 触发器
2>.DELETE 触发器
3>.UPDATE 触发器
4>.关于触发器的进一步介绍
26.管理事务处理
1.事务处理
2.控制事务处理
1>.使用 ROLLBACK
2>.使用 COMMIT
3>.使用保留点
4>.更改默认的提交行为
27.全球化和本地化
1.字符集和校对顺序
2.使用字符集和校对顺序
28.安全管理
1.访问控制
2.管理用户
1>.创建用户账号
2>.删除用户账号
3>.设置访问权限
4>.更改口令
29.数据库维护
1.备份数据
2.进行数据库维护
3.诊断启动问题
4.查看日志文件
30.改善性能
1.改善性能
1329
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
