【数据库笔记】MySQL必知必会：chapter 19-22 插入数据 (INSERT语句) | 更新和删除数据 (UPDATE/DELETE语句) | 创建和操纵表 | 使用视图

最新推荐文章于 2022-05-18 15:55:57 发布

阳光快乐普信男

最新推荐文章于 2022-05-18 15:55:57 发布

阅读量717

点赞数

分类专栏： MySQL必知必会文章标签： mysql

原文链接：https://book.douban.com/subject/3354490/

版权

MySQL必知必会专栏收录该内容

8 篇文章 1 订阅

订阅专栏

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
导出（计算）列

阳光快乐普信男

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
【数据库笔记】MySQL必知必会：chapter 19-22 插入数据 (INSERT语句) | 更新和删除数据 (UPDATE/DELETE语句) | 创建和操纵表 | 使用视图

文章目录19.1 数据插入19.2 插入完整的行19.3 插入多个行19.4 插入检索出的数据19.1 数据插入select 语句是最常用的SQL语句（占据了chapter 1-17！）。但是，其他3个经常使用的SQL语句也要学习。INSERT 插入（或添加）行UPDATE 更新行DELETE 删除行插入及系统安全可针对每个表或每个用户，利用MySQL的安全机制禁止使用insert语句（chapter 28）。19.2 插入完整的行把数据插入表中的最简单的方法是使用基本的ins.
复制链接

扫一扫