MySQL - 数据操作与表管理

MySQL 数据操作与表管理

插入数据（INSERT）
更新和删除数据（UPDATE/DELETE）
创建和操纵表（CREATE/ALTER/DROP）
使用视图
存储过程
游标
触发器

插入数据

使用 INSERT 插入数据，要求指定表名和插入的值。

插入完整的行：

INSERT INTO customers
VALUES(NULL, 'Pep E. LaPew', '100 Main Street', 'Los Angeles', 'CA', '90046', 'USA', NULL, 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);

插入多个行：

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 SELECT 将 SELECT 的结果插入表中。

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;

更新和删除数据

更新数据：使用 UPDATE 语句，由三部分组成：要更新的表、列名和它们的新值、确定要更新行的过滤条件。

-- 更新单个列
UPDATE customers SET cust_email = 'elmer@fudd.com' WHERE cust_id = 10005;

-- 更新多个列
UPDATE customers SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com' WHERE cust_id = 10005;

不要省略 WHERE 子句，否则会更新表中所有行。

删除数据：使用 DELETE 语句。

DELETE FROM customers WHERE cust_id = 10006;

DELETE 删除整行而不是列，要删除特定的列用 UPDATE。不要省略 WHERE 子句。

删除表的所有行：TRUNCATE TABLE 比 DELETE 更快（实际上是删除原来的表并重新建一个空表）。

TRUNCATE TABLE customers;

更新和删除的指导原则：

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

创建和操纵表

创建表：使用 CREATE TABLE，必须指定表名、列名和列的数据类型。

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 DEFAULT 'USA',
    cust_contact char(50) NULL,
    cust_email char(255) NULL,
    PRIMARY KEY (cust_id)
) ENGINE=InnoDB;

NULL 值：每个列要么是 NULL 列，要么是 NOT NULL 列。NULL 为默认设置。不要把 NULL 与空串混淆，空串是有效的值。

主键：用 PRIMARY KEY 指定。多列主键以逗号分隔的列表给出各列名。主键中只能使用不允许 NULL 值的列。

AUTO_INCREMENT：每当增加一行时自动增量，每个表只允许一个 AUTO_INCREMENT 列，且必须被索引。使用 last_insert_id() 获得最后一个 AUTO_INCREMENT 值。

指定默认值：用 DEFAULT 关键字，MySQL 不允许使用函数作为默认值，只支持常量。

引擎类型：

InnoDB：可靠的事务处理引擎，不支持全文本搜索
MyISAM：性能极高的引擎，支持全文本搜索，不支持事务处理
MEMORY：数据存储在内存中，速度很快，适合临时表

引擎类型可以混用，但外键不能跨引擎。

更新表：使用 ALTER TABLE。使用时要极为小心，应该先做完整备份。

-- 添加列
ALTER TABLE vendors ADD vend_phone CHAR(20);

-- 删除列
ALTER TABLE vendors DROP COLUMN vend_phone;

-- 定义外键
ALTER TABLE products ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id) REFERENCES vendors (vend_id);

删除表：DROP TABLE，永久删除，不能撤销。

DROP TABLE customers2;

重命名表：

RENAME TABLE customers2 TO customers;
RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors;

使用视图

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

视图的用途：

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

视图的规则和限制：

与表一样，视图必须唯一命名
视图不能索引，也不能有关联的触发器或默认值
视图可以和表一起使用（可以联结视图和表）

创建视图：

-- 创建将客户与邮件列表绑定的视图
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email FROM customers WHERE cust_email IS NOT NULL;

-- 使用视图
SELECT * FROM customeremaillist;

-- 用视图重新格式化检索出的数据
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors ORDER BY vend_name;

-- 用视图过滤不想要的数据
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;

查看视图：

SHOW CREATE VIEW productcustomers;

删除视图：

DROP VIEW productcustomers;

更新视图：CREATE OR REPLACE VIEW，如果视图存在则替换，不存在则创建。并非所有视图都可更新（使用了分组、联结、聚集函数、DISTINCT 等的视图通常不可更新）。

存储过程

存储过程就是为以后使用而保存的一条或多条 MySQL 语句的集合，可视为批文件。

创建存储过程：

CREATE PROCEDURE productpricing()
BEGIN
    SELECT Avg(prod_price) AS priceaverage FROM products;
    SELECT Min(prod_price) AS pricemin FROM products;
    SELECT Max(prod_price) AS pricemax FROM products;
END;

MySQL 命令行使用分号作为语句分隔符，如果存储过程体内有分号会导致语法错误。解决方法是临时更改分隔符：

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;

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

智能存储过程：包含业务规则和智能处理的存储过程。

游标

游标是一个存储在 MySQL 服务器上的数据库查询，检索出结果集后，应用程序可以滚动或浏览其中的数据。游标只能用于存储过程和函数。

创建游标：DECLARE 语句定义游标，OPEN 打开，FETCH 访问每一行，CLOSE 关闭。

CREATE PROCEDURE processorders()
BEGIN
    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    DECLARE ordernumbers CURSOR FOR SELECT order_num FROM orders;
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;

    CREATE TABLE IF NOT EXISTS ordertotals(order_num INT, total DECIMAL(8,2));

    OPEN ordernumbers;
    REPEAT
        FETCH ordernumbers INTO o;
        CALL ordertotal(o, 1, t);
        INSERT INTO ordertotals(order_num, total) VALUES(o, t);
    UNTIL done END REPEAT;
    CLOSE ordernumbers;
END;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' 是在游标到达末尾时设置 done 为 1 的条件处理。

触发器

触发器是 MySQL 响应以下任意语句而自动执行的一条 MySQL 语句（或位于 BEGIN 和 END 语句之间的一组语句）：DELETE、INSERT、UPDATE。其他 SQL 语句不支持触发器。

创建触发器：需要唯一的触发器名、关联的表、响应的活动（DELETE/INSERT/UPDATE）、何时执行（BEFORE/AFTER）。

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

每个表每个事件只允许一个触发器，每表最多 6 个触发器（每条 INSERT/UPDATE/DELETE 的 BEFORE 和 AFTER）。

INSERT 触发器：可以引用名为 NEW 的虚拟表访问被插入的行。BEFORE INSERT 中 NEW 的值可以被更新。AUTO_INCREMENT 列，NEW 在 INSERT 执行后包含新的自动生成值。

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

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;

UPDATE 触发器：可以引用 OLD 访问以前的值，引用 NEW 访问新更新的值。BEFORE UPDATE 中 NEW 的值可以被更新。

-- 确保州名缩写总是大写
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

删除触发器：

DROP TRIGGER newproduct;

MySQL - 数据操作与表管理