11.插入、更新与删除数据
插入:INSERT INTO customers VALUES (NULL, 'A', 'B', ... , 'F');
INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country) VALUES ('A','B','C','D','E','F');
更新:UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;
删除:DELETE FROM customers WHERE cust_id = 10006;
12.创建和操纵表
创造表:
CREATE TABLE orders
(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL,
quantity int NOT NULL DEFAULT 1
)ENGINE InnoDB;
AUTO_INCREMENT 表示自增
DEFAULT 表示默认值
在创造表时,可以使用的引擎如下:
InnoDB:是一个可靠的事务处理引擎不支持全文本搜索;
MEMORY: 在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
MyISAM: 是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
更新表:
增加:
ALTER TABLE vendors ADD vend_phone CHAR(20);
删除:
ALTER TABLE vendors DROP COLUMN vend_phone;
定义外键:
ALTER TABLE orders ADD CONSTRANT fk_orders_customers FOREIGN KEY(cust_id) REFERENCES customers(cust_id);
删除表:
DROP TABLE customers2;
重命名表:
RENAME TABLE customers2 to customers;
13.视图
视图是虚拟的表。与包含数据的表不一样,视图只包含使用时动态检索的数据查询。使用视图可以将复杂的MySQL代码表示方式变得简洁。
关于视图的常用语句:
创建视图:CREATE VIEW;
查看视图:SHOW CREATE VIEW viewname;
删除视图:DROP VIEW viewname;
更新视图:CREATE OR REPLACE VIEW;
例子:
CREATE VIEW vendorlocation AS
SELECT Concat(RTrim(vend_name),'(',RTrim(vend_country),')')
AS vend_title
FROM vendors
ORDER BY vend_name;
注意事项,如果存在下面的情况则不能将视图进行更新:
存在分组;
存在联结;
存在子查询;
存在聚集函数;
存在DISTINCT;
导出(计算)列。
14.存储过程
使用存储过程的目的是:
简化操作;
保证数据完整性,减少建立一些处理步骤;
简化对变动的管理。
1)创建存储过程:
CREATE PROCEDURE productpricing()
BEGIN
SELECT Avg(prod_price) AS priceaverage
FROM products;
END
2)使用存储过程:
CALL productpricing();
3)使用参数
IN 输入到存储变量中, OUT从存储变量输出,INTO 保存到相应的变量。
CREATE PROCEDURE productingpricing1(
OUT pl DECIMAL(8,2),
OUT ph DECIMAL(8,2),
OUT pa DECIMAL(8,2)
)
BEGIN
SELECT Min(prod_price) AS pricelow
INTO pl
FROM products;
SELECT Max(prod_price) AS pricehigh
INTO ph
FROM products;
SELECT Avg(prod_price) AS priceaverage
INTO pa
FROM products;
END;
4)使用存储过程
CALL productpricing1(@pricelow,@pricehigh,@priceaverage);
SELECT @pricehigh, @pricelow, @priceaverage;
5)检查存储过程
SHOW CREATE PROCEDURE productingpricing1;
15.使用游标
MySQL中的游标只能用于存储过程。
1)创建游标
CREATE PROCEDURE processorders()
BEGIN
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
END;
2)打开和关闭游标
OPEN ordernumbers;
CLOSE ordernumbers;
3)使用游标数据
CREATE PROCEDURE processorders()
BEGIN
--Declare local variables
DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;
--Declare the cursor
DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;
--Declare continue handler
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
--Open the cursor
OPEN ordernumbers;
--Loop through all rows
REPEAT
--Get order number
FETCH ordernumbers INTO o;
--End of loop
UNTIL done END REPEAT;
--Close the cursor
CLOSE ordernumbers;
END;
16.
触发器触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句)。注意视图不支持触发器,每个表最多支持6个触发器。
主要语句包括:
DELETE
INSERT
UPDATE
创建触发器的注意事项:
唯一的触发器名;
触发器关联的表;
触发器应该响应的活动(DELETE,INSERT,UPDATE);
触发器何时执行(处理之前或之后)。
1)创建触发器:
CREATE TRIGGER newproduct AFTER INSERT ON products FOR EACH ROW SELECT 'Product added';
2)删除触发器:
DROP TRIGGER newproduct;
3)使用触发器
3-1)使用INSERT触发器
在INSERT触发器代码内,可引用一个名为NEW的虚拟表,访问被插入的行;
在BEFORE INSERT触发器中,NEW中的值也可以被更新(允许更改被插入的值);
对于AUTO_INCREMENT列,NEW在INSERT执行之前包含0,在INSERT执行之后包含新的自动生成值。
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
3-2)使用DELETE触发器
在DELETE触发器代码内,你可以引用一个名为OLD的虚拟表,访问被删除的行;
OLD中的值全都是只读的,不能更新。
CREATE TRIGGER deleteorder BEFORE 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;
3-3)使用UPDATE触发器
在UPDATE触发器代码中,你可以引用一个名为OLD的虚拟表访问以前(UPDATE语句前)的值,引用一个名为NEW的虚拟表访问新更新的值;
在BEFORE UPDATE触发器中,NEW中的值可能也被更新(允许更改将要用于UPDATE语句中的值);
OLD中的值全都是只读的,不能更新。
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);