MYSQL插入数据、更新和和删除数据、创建表、更新表和删除

插入数据

1）插入完整的行

案例，插入一个完整的客户到 customers 表中。

customers表的结构：

INSERT INTO customers
VALUES(NULL, 'Coyote acg.', '300 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'Y Zee', 'yZee@coyote.com');

在插入数据时，如果某个列没有值，应该使用NULL 值（假定表允许对该列指定空值）。

第一列 cust_id 可以指定为NULL。这是因为每次插入一个新行时，该列由MySQL自动增量。

上面的这种插入方法，虽然很简单，但并不安全，因为高度依赖于表中列的定义次序，不能保证下一次表结构变动后各个列保持完全相同的次序。

编写INSERT语句的更安全（不过更烦琐）的方法如下：

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('E SWDA', '3399 53rd Street', 'Chicago', 'IL', '54535', 'USA', 'E Fudd');

你会发现 cust_id 的NULL值是不必要的， cust_id 列并没有出现在列表中，所以不需要任何值。

2）插入多行

INSERT INTO customers(cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES
('E SWDA', '3399 53rd Street', 'Chicago', 'IL', '54535', 'USA', 'E Fudd'),
('A NTTR', '4399 00rd Street', 'Chicago', 'IL', '11000', 'USA', 'A SUDR');

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

3）插入检索出的数据

意思就是，它将一条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;

这个例子使用INSERT SELECT从custnew中将所有数据导入 customers。

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

更新和删除数据

1）更新数据

更新（修改）表中的数据，使用 UPDATE 语句。可采用两种方 式使用UPDATE：

• 更新表中特定行；
• 更新表中所有行。

基本的 UPDATE 语句由3部分组成：

• 要更新的表
• 列名和它们的新值
• 确定要更新的过滤条件

案例：客户10005现在有了电子邮件地址，因此他的记录 需要更新，语句如下：

UPDATE customers 
SET cust_email='XXXXX@fudd.com'
WHERE cust_id=10005;

注意：没有 WHERE子句，MySQL将会用这个电子邮件地址更新customers表中所有 行

在更新多个列时，只需要使用单个SET命令，每个“列=值”对之间 用逗号分隔（最后一列之后不用逗号）。

案例：

UPDATE customers 
SET 
cust_name = 'aobama',
cust_email='XXXXX@fudd.com'
WHERE cust_id=10005;

为了删除某个列的值，可设置它为NULL（假定表定义允许为 NULL 值）。

UPDATE customers 
SET cust_email=NULL
WHERE cust_id=10005;

2）删除数据

为了从一个表中删除（去掉）数据，使用 DELETE 语句。可以两种方式使用 DELETE：

• 从表中删除特定的行；
• 从表中删除所有行。

案例：从customers表中删除一行：

DELETE FROM customers 
WHERE cust_id=10006;

注意：如果省 略WHERE子句，它将删除表中每个客户。

删除表的内容而不是表 DELETE语句从表中删除行，甚至是 删除表中所有行。但是，DELETE不删除表本身。

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

3）更新和删除的指导原则

• 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE 子句的UPDATE或DELETE语句。
• 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围）。
• 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进 行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不 正确。

---

创建和操纵表

1）表创建基础

表创建使用 CREATE TABLE 语句，创建表时必须给出以下信息：

• 新表的名字，在关键字CREATE TABLE之后给出；
• 表列的名字和定义，用逗号分隔。

下面是创建 customers表的语句：

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;

表的主键可以在创建表时用 PRIMARY KEY关键字指定。

当某列定义时含有关键字 NOT NULL，在插入数据时试图插入没有值的列，将返回错误，且插入失败。

理解NULL

NULL值是没有值， 它不是空串。如果指定’'（两个单引号，其间没有字符），这在NOT NULL列中是允许的。空串是一个有效的值，它不是无值。NULL值用关键字NULL而不是空串指定。

2）主键

主键值必须唯一。表中的每个行必须具有唯一的主键值。创建表时，主键使用 PRIMARY KEY(要指定为主键的列名) 指定。

主键中只能使用不允许NULL值的列。允许NULL值的 列不能作为唯一标识。

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

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

3）自增 AUTO_INCREMENT

例如，创建 customers 表时，我们使用 AUTO_INCREMENT 开展主键自增。

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

cust_id      int       NOT NULL AUTO_INCREMENT,

4）指定默认值

默认值用CREATE TABLE语句的列定义中的DEFAULT关键字指定。

CREATE TABLE orderitems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL DEFAULT 1,
  item_price decimal(8,2) NOT NULL ,
  PRIMARY KEY (order_num, order_item)
) ENGINE=InnoDB;

quantity 列包含订单中每项物品的数 量。在此例子中，给该列的描述添加文本DEFAULT 1指示MySQL，在未 给出数量的情况下使用数量1。

5）引擎类型

• InnoDB是一个可靠的事务处理引擎（参见第26章），它不支持全文 本搜索；
• MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘） 中，速度很快
• MyISAM是一个性能极高的引擎，它支持全文本搜索（参见第18章）， 但不支持事务处理

6）更新表

我们使用 ALTER TABLE 来更新表。理想状态下，当表 中存储数据以后，该表就不应该再被更新。

下面的例子给表添加一个列：

ALTER TABLE vendors
ADD vend_phone CHAR(20);

删除刚刚添加的列：

ALTER ATBLE vendors
DROP COLUMN vend_phone;

7）删除表

删除表使用 DROP TABLE 语句。

例如，删除 customers 表：

DROP TABLE customers;

删除表没有确认， 也不能撤销，执行这条语句将永久删除该表。

8）重命名表

重命名表使用 RENAME TABLE 语句。

RENAME TABLE customers1 TO customers2;

重命名多个表：

RENAME TABLE customers1 TO customers2,
			 vendors1 TO vendors2;