mysql 必须知道_MySQL必知必会（四）

摘自《MySQL必知必会》

19.1 数据插入

顾名思义，INSERT是用来插入(或添加)行到数据库表的。

可针对每个表或每个用户，利用MySQL的安全机制禁止使用INSERT语句。

19.2 插入完整的行

把数据插入表中的最简单的方法是使用基本的INSERT语法，它要求指定表名和被插入到新行中的值。

INSERT INTO customers VALUES(NULL, 'Pep E', '100 Main Street', 'Los Angeles', NULL, NULL);

此例子插入一个新客户到customers表。存储到每个表列中的数据在VALUES子句中给出，对每个列必须提供一个值。如果某个列没有值(如上面的cust_contact和cust_email列)，应该使用NULL值(假定表允许对该列指定空值)。各个列必须以它们在表定义中出现的次序填充。第一列cust_id也为NULL。这是因为每次插入一个新行时，该列由MySQL自动增量。你不想给出一个值(这是MySQL的工作)，又不能省略此列(如前所述，必须给出每个列)，所以指定一个NULL值(它被MySQL忽略，MySQL在这里插入下一个可用的cust_id值)。

上面的SQL语句高度依赖于表中列的定义次序。即使可得到这种次序信息，也不能保证下一次表结构变动后各个列保持完全相同的次序。因此，编写依赖于特定列次序的SQL语句是很不安全的。

编写INSERT语句的更安全的方法如下：

INSERT INTO customers(cust_name, cust_address, cust_city, cust_contact, cust_email)

VALUES('Pep E', '100 Main Street', 'Los Angeles', NULL, NULL);

因为提供了列名，VALUES必须以其指定的次序匹配指定的列名，不一定按各个列出现在实际表中的次序。其优点是，即使表的结构改变，此INSERT语句仍然能正确工作。你会发现cust_id的NULL值是不必要的，cust_id列并没有出现在列表中，所以不需要任何值。

使用这种语法，还可以省略列。这表示可以只给某些列提供值，给其他列不提供值。(事实上你已经看到过这样的例子：当列名被明确列出时，cust_id可以省略。)

省略的列必须满足以下某个条件:该列定义为允许NULL值(无值或空值)。

在表定义中给出默认值。这表示如果不给出值，将使用默认值。

如果对表中不允许NULL值且没有默认值的列不给出值，则MySQL将产生一条错误消息，并且相应的行插入不成功。

数据库经常被多个客户访问，对处理什么请求以及用什么次序处理进行管理是MySQL的任务。INSERT操作可能很耗时(特别是有很多索引需要更新时)，而且它可能降低等待处理的SELECT语句的性能。

如果数据检索是最重要的(通常是这样)，则你可以通过在INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级，如下所示：

INSERT LOW_PRIORITY INTO

顺便说一下，这也适用于UPDATE和DELETE语句。

19.3 插入多个行

INSERT可以插入一行到一个表中。但如果你想插入多个行怎么办？可以使用多条INSERT语句，甚至一次提交它们，每条语句用一个分号结束。

或者，只要每条INSERT语句中的列名(和次序)相同，可以如下组合各语句：

INSERT INTO customers(cust_name, cust_address, cust_city, cust_country)

VALUES('Pep E', '100 Main Street', 'Los Angeles', 'USA'),

('M. Martian', '42 Main Street', 'Los Angeles', 'USA');

其中单条INSERT语句有多组值，每组值用一对圆括号括起来，用逗号分隔。

19.4 插入检索出的数据

INSERT INTO customers(cust_id, cust_contact, cust_name, cust_city)

SELECT cust_id, cust_contact, cust_name, cust_city FROM custnew;

这个例子将从custnew中将所有数据导入customers。这条语句将插入多少行有赖于custnew表中有多少行。如果这个表为空，则没有行被插入(也不产生错误，因为操作仍然是合法的)。如果这个表确实含有数据，则所有数据将被插入到customers。

这个例子导入了cust_id(假设你能够确保cust_id的值不重复)。你也可以简单地省略这列(从INSERT和SELECT中)，这样MySQL就会生成新值。

为简单起见，这个例子在INSERT和SELECT语句中使用了相同的列名。但是，不一定要求列名匹配。事实上，MySQL甚至不关心SELECT返回的列名。它使用的是列的位置，因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一个列，第二列将用来填充表列中指定的第二个列，如此等等。这对于从使用不同列名的表中导入数据是非常有用的。

INSERT SELECT中SELECT语句可包含WHERE子句以过滤插入的数据。

20.1 更新数据

UPDATE customers

SET cust_email = 'jesse@fudd.com'

WHERE cust_id = 10005;

在此例子中，要更新的表的名字为customers。SET命令用来将新值赋给被更新的列。UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句，MySQL将会用这个电子邮件地址更新customers表中所有行，这不是我们所希望的。

更新多个列:

UPDATE customers

SET cust_email = 'jesse@fudd.com',

cust_name = 'The Fudds'

WHERE cust_id = 10005;

UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。

如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消(错误发生前更新的所有行被恢复到它们原来的值)。为即使是发生错误，也继续进行更新，可使用IGNORE关键字，如下所示：

UPDATE IGNORE customers…

为了删除某个列的值，可设置它为NULL(假如表定义允许NULL值):

UPDATE customers

SET cust_email = NULL

WHERE cust_id = 10005;

20.2 删除数据

DELETE FROM customers

WHERE cust_id = 10006;

DELETE FROM要求指定从中删除数据的表名。WHERE子句过滤要删除的行。在这个例子中，只删除客户10006。如果省略WHERE子句，它将删除表中每个客户。

DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列，请使用UPDATE语句。

如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据)。

20.3 更新和删除的指导原则

如果省略了WHERE子句，则UPDATE或DELETE将被应用到表中所有的行。

下面是许多SQL程序员使用UPDATE或DELETE时所遵循的习惯:除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。

保证每个表都有主键，尽可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。

在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。

使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

MySQL没有撤销(undo)按钮。应该非常小心地使用UPDATE和DELETE，否则你会发现自己更新或删除了错误的数据。

21.1 创建表

为利用CREATE TABLE创建表，必须给出下列信息：新表的名字，在关键字CREATE TABLE之后给出；

表列的名字和定义，用逗号分隔。

CREATE TABLE customers

(

cust_id int NOT NULL AUTO_INCREMENT,

cust_name char(50) NOT NULL,

cust_city char(50) NULL,

PRIMARY KEY(cust_id)

)ENGINE=InnoDB;

从上面的例子中可以看到，表名紧跟在CREATE TABLE关键字后面。实际的表定义(所有列)括在圆括号之中。各列之间用逗号分隔。每列的定义以列名(它在表中必须是唯一的)开始，后跟列的数据类型。表的主键可以在创建表时用PRIMARY KEY关键字指定。

处理现有的表:在创建新表时，指定的表名必须不存在，否则将出错。如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它，而不是简单地用创建表语句覆盖它。

如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。它只是查看表名是否存在，并且仅在表名不存在时创建它。

21.1.2 使用NULL值

允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行，换句话说，在插入或更新行时，该列必须有值。

NULL为默认设置，如果不指定NOT NULL，则认为指定的是NULL。

21.1.3 主键再介绍

正如所述，主键值必须唯一。如果主键使用单个列，则它的值必须唯一。如果使用多个列，则这些列的组合值必须唯一。

为创建由多个列组成的主键，应该以逗号分隔的列表给出各列名:

PRIMARY KEY(order_num, order_item)

主键和NULL值:主键为其值唯一标识表中每个行的列。主键中只能使用不允许NULL值的列。

21.1.4 使用AUTO_INCREMENT

在增加一个新顾客或新订单时，需要一个新的顾客ID或订单号。这些编号可以任意，只要它们是唯一的即可。

显然，使用的最简单的编号是下一个编号，所谓下一个编号是大于当前最大编号的编号。例如，如果cust_id的最大编号为10005，则插入表中的下一个顾客可以具有等于10006的cust_id。

简单吗？不见得。你怎样确定下一个要使用的值？当然，你可以使用SELECT语句得出最大的数，然后对它加1。但这样做并不可靠(你需要找出一种办法来保证，在你执行SELECT和INSERT两条语句之间没有其他人插入行，对于多用户应用，这种情况是很有可能出现的)。

cust_id int NOT NULL AUTO_INCREMENT,

AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量。每次执行一个INSERT操作时，MySQL自动对该列增量，给该列赋予下一个可用的值。这样给每个行分配一个唯一的cust_id，从而可以用作主键值。

每个表只允许一个AUTO_INCREMENT列，而且它必须被索引(如，通过使它成为主键)。

覆盖AUTO_INCREMENT: 你可以简单地在INSERT语句中指定一个值，只要它是唯一的(至今尚未使用过)即可，该值将被用来替代自动生成的值。后续的增量将开始使用该手工插入的值。

21.1.5 指定默认值

如果在插入行时没有给出值，MySQL允许指定此时使用的默认值。

quantity int NOT NULL DEFAULT 1,

不允许函数:与大多数DBMS不一样，MySQL不允许使用函数作为默认值，它只支持常量。

使用默认值而不是NULL值: 许多数据库开发人员使用默认值而不是NULL列，特别是对用于计算或数据分组的列更是如此。

21.1.6 引擎类型

与其他DBMS一样，MySQL有一个具体管理和处理数据的内部引擎。

在你使用CREATE TABLE语句时，该引擎具体创建表，而在你使用SELECT语句或进行其他数据库处理时，该引擎在内部处理你的请求。

MySQL与其他DBMS不一样，它具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT等命令。

为什么要发行多种引擎呢？因为它们具有各自不同的功能和特性，为不同的任务选择正确的引擎能获得良好的功能和灵活性。

如果省略ENGINE=语句，则使用默认引擎(很可能是MyISAM)，多数SQL语句都会默认使用它。但并不是所有语句都默认使用它，这就是为什么ENGINE=语句很重要的原因。

以下是几个需要知道的引擎：InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；

MEMORY在功能等同于MyISAM，但由于数据存储在内存(不是磁盘)中，速度很快(特别适合于临时表)；

MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

引擎类型可以混用。

外键不能跨引擎: 混用引擎类型有一个大缺陷。外键(用于强制实施引用完整性)不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

21.2 更新表

为更新表定义，可使用ALTER TABLE语句。但是，理想状态下，当表中存储数据以后，该表就不应该再被更新。在表的设计过程中需要花费大量时间来考虑，以便后期不对该表进行大的改动。

为了使用ALTER TABLE更改表结构，必须给出下面的信息：在ALTER TABLE之后给出要更改的表名(该表必须存在，否则将出错)；

所做更改的列表。

给vendors表增加一个名为vend_phone的列:

ALTER TABLE vendors

ADD vend_phone CHAR(20);

删除刚刚添加的列:

ALTER TABLE vendors

DROP COLUMN vend_phone;

ALTER TABLE的一种常见用途是定义外键。

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：用新的列布局创建一个新表；

使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；

检验包含所需数据的新表；

重命名旧表(如果确定，可以删除它)；

用旧表原来的名字重命名新表；

根据需要，重新创建触发器、存储过程、索引和外键。

使用ALTER TABLE要极为小心，应该在进行改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销，如果增加了不需要的列，可能不能删除它们。类似地，如果删除了不应该删除的列，可能会丢失该列中的所有数据。

21.3 删除表

删除表使用DROP TABLE语句即可：

DROP TABLE customer;

21.4 重命名表

使用RENAME TABLE语句可以重命名一个表：

RENAME TABLE customer TO customers;