文章目录
chapter 19 插入数据(INSERT语句)
19.1 数据插入
select
语句是最常用的SQL语句(占据了chapter 1-17!)。但是,其他3个经常使用的SQL语句也要学习。
- INSERT 插入(或添加)行
- UPDATE 更新行
- DELETE 删除行
插入及系统安全
可针对每个表或每个用户,利用MySQL的安全机制禁止使用insert
语句(chapter 28)。
19.2 插入完整的行
把数据插入表中的最简单的方法是使用基本的insert
语句,它要求指定表名和插入到新行中的值。
insert into customers
values(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
'NULL',
NULL);
--没有输出:insert语句一般不会产生输出
insert into customers(cust_name,
cust_address,
cust_City,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
values(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
'NULL',
NULL);
19.3 插入多个行
一条一条insert语句写,用分号隔开,或者用下面的组合:
insert into customers(cust_name,
cust_address,
cust_City,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
values(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
'NULL',
NULL),
(NULL,
'Pep E. LaPew',
'100 Main Street',
'Los Angeles',
'CA',
'90046',
'USA',
'NULL',
NULL);
提高insert的性能
此技术可以提高数据库处理的性能,因为MySQL用单条insert
语句处理多个插入比使用多条insert
语句快。
19.4 插入检索出的数据
INSERT还存在一种形式,可以利用它将一条SELECT语句的结果插入表中。这就是所谓的INSERT SELECT
。
假如你想从另一个表中合并客户列表到你的customers表。不需要每次读取一行,然后再将它用insert
插入。
insert into customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
select cust_id
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
from custnew;
chapter 20 更新和删除数据(UPDATE/DELETE语句)
20.1 更新数据
为了更新(修改)表中的数据,可使用update
语句。可采用两种方法使用update
:
- 更新表中的特定行;
- 更新表中的所有行
不要省略
where
子句
在使用update
时一定要细心,稍不注意就会更新所有行。
update
与安全
可以限制和控制update
语句的使用(chapter 28)。
基本的UPDATE语句由3部分组成,分别是:
- 要更新的表
- 列名和它们的新值
- 确定要更新行的过滤条件
举例,客户10005现在有了新的email地址,因此需要更新记录:
--更新单个列
UPDATE customer
SET cust_email = 'elmer@fudd.com'
WHERE cust_id=10005;
--更新多个列
UPDATE customer
SET cust_email = 'elmer@fudd.com',
cust_name = 'The Fudds'
WHERE cust_id=10005;
--为删除某个列的值,可设置为NULL(假如表定义允许NULL值)
UPDATE customer
SET cust_email = NULL
WHERE cust_id=10005;
20.2 删除数据
为了从一个表删除(去掉)数据,使用DELETE
语句。
- 从表中删除特定的行;
- 从表中删除所有行。
不要省略WHERE子句
在使用DELETE时一定要注意细心。因为稍不注意,就会错误地删除表中所有行。
DELETE与安全
可以限制和控制delete
语句的使用(chapter 28)。
DELETE FROM customers
WHERE cust_id = 10006;
delete
删除整行而不是删除列,删除指定的列,请使用update
语句。
小心使用:MYSQL没有撤销(undo)按钮。应该非常小心地使用UPDATE和DELETE,否则你会发现自己更新或删除了错误的数据。
chapter 21 创建和操纵表
21.1 创建表
1. 表创建基础
一般有两种创建表的方法:
- 使用具有交互式创建和管理表的工具(chapter 2)
- 表也可以直接用MYSQL语句操纵(
CREATE TABLE
)
为了利用CREATE TABLE
创建表,必须给出下列信息:
- 新表的名字,在关键字
CREATE TABLE
之后给出 - 表列的名字和定义,用逗号分隔
create table customers
(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
...
cust_email char(255) NULL,
PRIMARY KEY(cust_id)
)ENGINE=InnoDB;
2. 使用NULL值
NULL值就是没有值或缺值。
允许NULL值的列也允许在插入行时不给出该列的值。
每个表列或者是NULL
列,或者是NOT NULL
列,这种状态在创建时由表的定义规定。
如果试图插入没有值的列,将返回错误,且插入失败。
NULL为默认设置。
不要把NULL值与空串相混淆,NULL值是没有值,它不是空串。如果指定‘’(两个单引号,其间没有字符),这在NOT NULL列是允许的。空串是一个有效的值,它不是无值。NULL值用关键字而不是空串指定。
3. 主键再介绍
正如所述,主键值必须唯一。如果主键使用单个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一。
主键中不允许NULL值的列。允许NULL值的列不能作为唯一标识。
--单个列作为主键
PRIMARY KEY(vend_id)
--多个列作为主键,保证组合唯一
PRIMARY KEY(order_num,order_item)
4. 使用AUTO_INCREMENT
AUTO_INCREMENT告诉MYSQL,本列每当增加一行时自动增量。每次执行一个INSERT
操作时,MYSQL自动对该列增量,给该列赋予下一个可用的值。
这样给每个行分配一个唯一的cust_id,从而可以用作主键值。
每个表只允许一个AUTO_INCREMENT列,而且它必须被索引。
select last_insert_id();
此语句返回最后一个AUTO_INCREMENT
值,然后可以将它用于后续的MySQL语句。
5. 指定默认值
如果在插入行时没有给出值,MySQL允许指定此时使用的默认值。
默认值用CREATE TABLE语句的列定义中的DEFAULT
关键字指定。
create table orderitems
(
order_num int NOT NULL,
order_item int NOT NULL,
quantity int NOT NULL DEFAULT 1,
PRIMARY KEY(order_num,order_item)
)ENGINE=InnoDB;
--DEFAULT 1指示MySQL在未给出数量的情况下使用数量1.
不允许函数
与大多数DBMS不一样,MySQL不允许使用函数作为默认值,它支持常量。
使用默认值而不是NULL值
许多数据库开发人员使用默认值而不是NULL列,特别是对用于计算或数据分组的列更是如此。
6. 引擎类型
迄今为止,CREATE TABLE
语句都是以ENGINE=InnoDB
语句结束。
MySQL与其他DBMS不一样,它具有多种引擎。它打包多个引擎,这些引擎都隐藏在MySQL服务器内,全都能执行CREATE TABLE
和SELECT
等命令。
为什么要发行多种引擎呢?
因为它们具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。
如果省略ENGINE=
语句,则使用默认引擎(多数SQL都使用MyISAM,但不是全部。)
以下是几个需要知道的引擎:
- InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;
- MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合临时表);
- MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
外键不能跨引擎
混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
21.2 更新表
为更新表定义,可使用ALTER TABLE
语句。
在理想状态下,当表中存储数据以后,该表就不应该再被更新。 在表的设计过程中需要花费大量时间来考虑,以便后期不对该表进行大的改动。
要用ALTER TABLE
更改表结构,必须有以下信息:
- 在
ALTER TABLE
之后给出要更改的表名(该表必须存在,否则将出错) - 所做更改的列表
--为表vendors增加一列vend_phone
ALTER TABLE vendors
ADD vend_phone char(20);
--为表vendors删除刚刚增加的那一列
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);
ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY(prod_id)
REFERENCES products(prod_id);
ALTER TABLE orders
ADD CONSTRAINT fk_orderitems_orders FOREIGN KEY(cust_id)
REFERENCES customers(cust_id);
ALTER TABLE products
ADD CONSTRAINT fk_products_vendors FOREIGN KEY(vend_id)
REFERENCES vendors(vend_id);
21.3 删除表
删除整个表,而不是内容。
DROP table customers2;
21.4 重命名表
--重命名一个表
RENAME TABLE customers2 TO customers;
--重命名多个表
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products TO products;
chapter 22 使用视图
MySQL5 添加了视图的支持,因此本章适用于MySQL5及以后的版本。
22.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';
这就是视图的作用,productcustomers是一个视图。作为视图,它不包含表中应该有的任何列或数据,它包含的是一个SQL查询(与上面以正确联结表的相同查询)。
1.为什么使用视图
- 重用SQL语句;
- 简化复制的SQL操作。在编写查询后,可以方便地重用它而不必知道它的基本查询细节;
- 使用表的组成部分而不是整个表;
- 保护数据。可以给用户授予表的特定部分的访问权限而不是整个标的访问权限
- 更改数据格式和表示。视图可返回与底层表的表示和格式不同的数据。
在视图创建之后,可以用与表基本相同的方式利用它们。可以对视图执行select
操作,过滤
和排序
操作。
将视图联结
到其他视图或表,甚至能添加和更新
数据。
重要的是知道视图仅仅是用来查看存储在别处的数据的一种设施。
视图本身不包含数据,因此它们返回的数据是从其他表汇总检索出来的。
使用视图的性能可能降低,因为视图不包含数据,所以每次使用视图时,都必须处理查询执行时所需的任一个检索,性能将会下降。
22.2 使用视图
- 视图用
CREATE VIEW
语句来创建; - 使用
SHOW CREATE VIEW viewname
;来查看创建视图的语句; - 用
DROP
删除视图,其语法为DROP VIEW viewname
; - 更新视图时,可以先用
DROP
再用CREATE
,也可以直接用CREATE OR REPLACE VIEW
。如果更新的视图不存在,则第二条更新语句会创建一个视图;如果要更新的视图存在,则第2条更新语句会替换原有视图。
1.利用视图简化复杂的联结
视图的最常见的应用之一是隐藏复杂的SQL,这通常都会涉及联结。
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;
2.用视图重新格式化检索出的数据
视图的最常见的应用之一是重新格式化检索出的数据。
select Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
from vendors
order by vend_name
现在假如经常需要这个格式的结果。不必在每次需要时执行联结,创建一个视图,每次需要时使用它即可。
CREATE VIEW vendorlocations AS
select Concat(RTrim(vend_name),'(',RTrim(vend_country),')') AS vend_title
from vendors
order by vend_name
select *
from vendorlocations;
3.用视图过滤不想要的数据
create view customeremaillist AS
select cust_id, cust_name, cust_email
from customers
where cust_email is not null;
select *
from customeremaillist;
4.使用视图与计算字段
select prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
from orderitems
where order_num = 20005;
create view orderitemsexpanded AS
select prod_id,
quantity,
item_price,
quantity*item_price AS expanded_price
from orderitems;
select * from orderitemsexpanded
where order_num = 20005;
5.更新视图
如果视图定义中有以下操作,则不能进行视图的更新:
- 分组(GROUP BY 和 HAVING)
- 联结
- 子查询
- 并
- 聚集函数(MIN()、Count(),Sum()等)
- DISTINCT
- 导出(计算)列