《MySQL必知必会》知识汇总四
本篇讲解插入、更新、删除表中数据，以及表结构、视图的相关操作
十七、插入数据
本章将介绍如何利用sql的INSERT语句将数据插入表中
- 数据插入
插入分为以下几种方式：插入完整的行、插入行的一部分、插入多行、插入某些查询结果
- 插入完整的行
INSERT INTO Customers
VALUES(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
上述sql语句将高度依赖于表中列的定义次序，某个列没有值，应该设置为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);
此时该sql指定了插入具体列名，那么values的顺序就将与指定的插入列一一对应
注意：
一般建议使用这种指定插入列的INSERT语句
不论使用上述哪种INSERT语句，values必须为每个列提供值
使用指定列的INSERT语句并不用将所有列都指定出来，可以省略列，指定列的顺序也不用与数据库表中列的顺序一致，但是省略的列必须允许为NULL值或者表定义中给出默认值
- 插入多个行
当前可以多次执行INSERT语句实现插入多个行，但是也可以使用一条INSERT语句
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语句来插入多行数据，会提高性能
- 插入检索出的数据
需求：想从另一表中合并客户列表到你的customers表。
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;
注意：
custnew表与customers表具有相同的表结构
上述sql中，不需要select返回的列名与customers的列名一致，它使用的是列的位置，select的第一列（不管其列名）将填充表列中指定的第一列
当然，select语句还可以使用where子句进行过滤数据！
十八、更新和删除数据
本章将介绍如何利用UPDATE和DELETE语句进一步操纵表数据
- 更新数据
可采用两种方式使用update：更新表中特定行、更新表中所有行
update customers
set cust_emal = 'elemer@fudd.com'
where cust_id = 10005;
注意：
不要省略where语句，否则将会更新所有行
update customers
set cust_emal = 'elemer@fudd.com',
cust_name = 'The Fudds'
where cust_id = 10005;
通过单个set命令，可以更新多个列
UPDATE IGNORE customers
set cust_emal = 'elemer@fudd.com',
cust_name = 'The Fudds'
where cust_id = 10005;
如果在更新行时，出现一行或者多行错误，则整个UPDATE操作被取消，当然可以是使用IGNORE关键字进行继续更新
- 删除数据
同样可以采用两种方式使用DELETE：从表中删除特定行、从表中删除所有行
delete from customers
where cust_id = 10006;
注意：
如果忽略了where子句，将删除所有行
更快的删除
如果想从表中删除所有行，可以使用TRUNCATE TABLE,速度更快，实际上底层是删除表后再新建一个表
- 更新和删除的指导原则
一定要使用带where的update和delete语句
使用where子句前先用select进行查看测试，防止where过滤的数据不正确
MySQL没有撤销命令，一定要小心使用！
十九、创建和操纵表
本章讲述表的创建、更改和删除的基本知识
- 表的基本创建
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;
需要给出以下信息：
新表的名字，再create table后给出
表列的名字和定义，用逗号隔开
注意：
在创建新的表时，指定的表名必须不存在，可以在表名后使用IF NOT EXISTS做判断
- 使用NULL值
null值就是没有值或者缺少值，上述创建表的sql中，cust_id、cust_name就不能为空，其他可以为空
不要将null值与空串相混淆，空串是一个有效的值，它不是无值
- 主键再介绍
PRIMARY KEY (vend_id)
或者
PRIMARY KEY (order_num,order_id)
可以使用单个列作为主键，也可以使用组合列作为主键
主键不允许使用null值
- 使用AUTO_INCREMENT
cust_id int NOT NULL AUTO_INCREMENT
这样MySQL在插入数据时，自动分配一个递增的int值
使用SELECT last_insert_id()函数将能查看最后一个自增的值
- 指定默认值
quantity int NOT NULL DEFAULT 1
表示在未给出数量的情况下使用默认数量1
- 引擎类型
ENGINE=InnoDB
还可以使用其他引擎如MyISAM
- 更新表
通过alter table来改变表结构，必须给出要更改的表名，所作更改的列
ALTER TABLE vendors
ADD vend_phone CHAR(20);
ALTER TABLE vendors
DROP COLUME vend_phone;
注意：
一定要小心使用alter table，删除了不该删除的列，会造成数据丢失
- 删除表
DROP TABLE customers2;
- 重命名表
RENAME TABLE customers2 TO customers;
二十、使用视图
本章将介绍何为视图，怎样工作的，何时使用，利用视图简化sql
需求：从三个表中检索数据
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';
如果三个表的数据都在一张表中，那么sql将变为
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id ='TNT2';
这就是视图的作用，productcustomers可以看作是一个视图，它不包含表中应该有的数据，仅包含的是一个SQL查询
- 为什么使用视图
重用SQL语句，简化复杂的SQL操作，使用表的组成部分、保护数据、更改数据格式和表示
视图本身不包含数据，返回的数据是从其他表中检索出来的
性能问题
每次使用视图之前都要处理查询后返回给视图，处理复杂多表联查会降低性能
注意：
视图不能有索引、必须有足够的访问权限
- 使用视图
使用SHOW CREATE VIEW viewname;来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;
更新视图时，可以先用DROP 再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图
- 使用视图简化复杂联结
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;
该sql创建了productcustomers视图，联结了三个表，返回订购了任意商品的客户信息
SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id ='TNT2';
因此，检索购买了TNT的客户这条sql即可满足
- 使用视图重新格式话检索的数据
CREATE VIEW vendorlocations AS
SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')')
AS vend_title
FROM vendors
ORDER BY vend_name;
- 用视图过滤不想要的数据
CREATE VIEW customeremaillist AS
SELECT cust_id, cust_name, cust_email
FROM customers
WHERE cust_email IS NOT NULL;
- 使用视图与计算字段
CREATE VIEW orderitemsexpanded AS
SELECT order_num,
prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
FROM orderitems;
- 更新视图
其实，对视图更新也就是对视图表示的基表进行更新，通过视图增加和删除一行，也是对基表进行增加和删除
但是如果视图涉及到分组、联结、子查询、并、聚集函数、DISTINCT、计算列那将不能进行更新
一般将视图用于检索而不用于更新