更改数据&创建表&使用视图
十九、插入数据INSERT
1插入整行
更安全的插入整行的方式为:
指定列名及值,列名和表中次序不同也可以。
我们还可以省略列,但必须满足以下某个条件:
- 该列允许NULL值
- 表定义时给出了默认值
大多数情况下,数据检索是最重要的(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更新和删除原则
- 除非是要删除或更新所有行,否则一定要使用
WHERE
- 在使用
UPDATE
和DELETE
之前,最好先用SELECT
检查内容是否正确 - 不允许删除与其他表相关联的行
二十一、创建和操纵表
使用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);
复杂表的更改一般要手动删除过程,设计以下步骤:
- 用新的布局创建新的表
- 使用
INSERT SELECT
语句从旧表复制数据到新表 - 检验新表
- 重命名旧表
- 用旧表的名字重命名新表
- 重建索引、外键等
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更新视图
更新视图将更新其基表,如果你对视图增加或删除行,实际上是对其基表增加或删除行。
但是在以下情况中,试图不可以更新: