MySQL必知必会学习笔记（七）

十九、插入数据INSERT

1插入整行

更安全的插入整行的方式为：
指定列名及值，列名和表中次序不同也可以。

我们还可以省略列，但必须满足以下某个条件：

1. 该列允许NULL值
2. 表定义时给出了默认值
   大多数情况下，数据检索是最重要的（SELECT语句），我们可以使用INSERT LOW_PRIOROTY INTO来降低语句优先级，此方法同样适用于UPDATE和DELETE。

2插入多个行

3插入检索出的数据

这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上， MySQL甚至不关心SELECT返回的列名。它使用的是列的位置。

二十、更新和删除数据

1更新数据

使用UPDATE

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

如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，UPDATE IGNORE customers...。
为了删除某列的值，可以将值设置为NULL.

2删除数据

使用DELETE

SELECT FROM customers
WHERE cust_id=10006;

如果想要删除表中所有行，可以使用TRUNCATE TABLE，它实际上是删除原来的表，重新创建一个

3更新和删除原则

1. 除非是要删除或更新所有行，否则一定要使用WHERE
2. 在使用UPDATE和DELETE之前，最好先用SELECT检查内容是否正确
3. 不允许删除与其他表相关联的行

二十一、创建和操纵表

使用CREATE TABLE

1创建表

使用CREATE TABLE，如果仅想在一个表不存在时创建，要在表名后面加IF NOT EXISTS

关于NULL值

允许NULL值存在的列允许在插入数据时不给出该列的值，每个表列的状态为NULL或者NOT NULL。
不要把NULL值和空串混淆，如果指定‘’，这在NOT NULL列中允许，空串是一个有效值，不是无值。

主键

主键值唯一，如果使用多个列，必须保证这些列的组合值唯一，在表创建时用如下语句定义：

PRIMARY KEY(vend_id)

PRIMARY KEY(order_num, order_item)

主键中只能使用不允许NULL值的列

自动更新

使用AUTO_INCREMENT自动增量，但也可以在INSERT时指定值，只要这个值是唯一的，就可以覆盖，后续的增量将开始使用手工插入的值。
有时候我们需要知道语句最后一个自动增量的值，这时可以使用SELECT last_insert_id()来获得。

指定默认值

CREATE TABLE orderitems
(...
 quantity int NOT NULL DEFAULT 1,
 ...
)ENGINE=InnoDB;

MySQL不允许使用函数作为默认值

引擎类型

• InnoDB是一个可靠的事务处理引擎，不支持全文本搜索
• MyISAM是高性能引擎，支持全文本搜索，但不支持事务处理
• EMORY等同于MyISAM，处理速度很快，适用于临时表

更新表

ALTER TABLE vendors
ADD vend_phone CHAR(20);

要删除刚刚增加的列

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. 重建索引、外键等

2删除表

DROP TABLE customers2；

3重命名表

RENAME TABLE customers2 TO customers,
             backup_products TO products;

二十二、使用视图

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'; 

我们现在可以把整个查询包装成名为productcustomers的虚拟表，则可以轻松检索出相同的数据：

SELECT cust_name, cust_contact,
FROM productcustomers
WHERE prod_id='TNT2';

视图定义如下：

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;

这个名为productcustomers的视图联结了三个表，如果执行SELECT * FROM productcustomers，将会列出订购了任意产品的客户。我们需要注意，prod_id包含在了视图的SELECT语句中。

• 利用视图，我们可以一次编写基础的SQL，然后多次使用。
• ORDER BY可以用在视图中，但如果从该视图检索数据SELECT中也含有ORDER BY，那么该视图中的ORDER BY将被覆盖。
• 使用SHOW CREATE VIEW viewname可以查看创建视图语句
• 使用DROP VIEW viewname删除视图

2使用视图过滤数据

CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;

在发送电子邮件到邮件列表时，需要排除没有电子邮件地址的用户。这里的WHERE子句过滤了cust_email列中具有NULL值的那些行，使他们不被检索出来。

3使用视图与计算字段

视图对于简化计算字段的使用特别有用

CREATE VIEW orderitemsexpanded AS
SELECT order_num.
       pord_id,
       quantity,
       item_price
       quantity*item_price AS expanded_price
FROM orderitems;

SELECT *
FROM orderitemsexpanded
WHERE order_num=20005;

需要注意，order_num包含在VIEW的SELECT语句中。

4更新视图

更新视图将更新其基表，如果你对视图增加或删除行，实际上是对其基表增加或删除行。
但是在以下情况中，试图不可以更新：