MySQL基本操作（四）创建、插入、更新、删除

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

1、插入数据 （INSERT… INTO）

1.1 数据插入

--插入多行
INSERT INTO customers(cust_name,
cust_city,
cust_country)
VALUES(
      'Qinn',
      'hangzhou',
      'China'
     ),
     (
     'Long',
     'hangzhou',
     'China'
     );

• 各个列必须以它们在表定义中出现的次序填充;
• 如果某个列没有值，应该使用NULL值;
• 必须给出VALUES的正确数目;
• VALUES中的第一个值对应于第一个指定的列名。第二个值对应于第二个列名;
• INSERT和INTO之间添加关键字LOW_PRIORITY，指示MySQL降低INSERT语句的优先级。

INSERT LOW PRIORITY INTO

1.2 数据表插入

--从另一表中合并客户列表到你的customers表
INSERT INTO customers(cust_id,
            cust_name,
            cust_email,
            cust_city)
SELECT cust_id,
       cust_name,
       cust_email,
       cust_city
FROM custnew;

不一定要求列名匹配。事实上，MySQL甚至不关心SELECT返回的列名。它使用的是列的位置，各个列必须以它们在表定义中出现的次序填充。

2、更新、删除数据

2.1更新数据（UPDATE…SET）

UPDATE IGNORE  customers
SET cust_name='Qinn',
    cust_email='aaa@qinn.com',
    cust_city=NULL
WHERE cust_id=10005;

• 省略WHERE子句表示更新表中的所有行；
• SET命令用来将新值赋给被更新的列；
• IGNORE表示错误发生前更新的所有行被恢复到它们原来的值；
• 为了删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。

2.2删除数据（DELETE…SET）

DELETE FROM customers
WHERE cust_id=10006;

• 省略WHERE子句表示删除表中的所有列；
• DELETE删除整行而不是删除列（UPDATE）；
• TRUNCATE实际是删除原来的表并重新创建一个表，而不是逐行删除表中的数据）

TRUNCATE TABLE customers;

注：在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确；MySQL没有撤销（undo）按钮。

3、创建和操纵表

3.1字段类型

int			: 整型，存储整数
double		: 浮点型, 存储浮点型数据。
			: double(4,3), 表示这个double数据最多只能有4位, 其中还需要有3位是小数部分, 9.999
char		: 字符串, 需要定义长度, 例如: char(5)
			: char(5), 每个数据占用长度固定为5, 例如: 'as' 占用长度 5	
varchar		: 可变长度字符串
			: varchar(5), 其实这里设置的是字符串最大可占用的长度, 例如: 'as' 占用长度 2
text		: 字符串类型
blob		: 字节类型
date		: 日期类型, 格式为 yyyy-MM-dd
time		: 时间类型, 格式为 hh:mm:ss
timestamp	: 时间戳类型, yyyy-MM-dd hh:mm:ss, 会自动赋值
datetime	: 时间类型, yyyy-MM-dd hh:mm:ss

3.2 创建表（CREATE TABLE）

CREATE TABLE customers IF NOT EXISTS
(
cust_id         int        NOT NULL AUTO_INCREMENT,
cust_name       char(50)   NOT NULL,
cust_address    char(50)   NOT NULL,
cust_city       char(50)   NOT NULL,
PRIMARY KEY(cust_id)
)
ENGINE=InnoDB;

• 表的主键（唯一）可以在创建表时用PRIMARY KEY关键字指定，主键中只能使用不允许NULL值得列,由多个列组成的主键，应该以逗号分隔的列表给出各列名；
• 列定义进行了恰当的缩进，以便阅读和编辑;
• 指定表名必须不存在，如果要防止意外覆盖已有的表，SQL要求首先手工删除该表，然后再重建它；
• 如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS;

3.2 使用AUTO_INCREMENT

cust_id         int        NOT NULL AUTO_INCREMENT, 

• AUTO_INCREMENT告诉MySQL，本列每当增加一行时自动增量,每个表只允许一个AUTO_INCREMENT列，而且它必须被索引（如，通过使它成为主键）;
• SELECT_last_insert_id() 返回最后一个AUTO_INCREMENT值，然后可以将它用于后续的MySQL语句。

引擎类型

ENGINE=InnoDB 

> InnoDB是一个可靠的事务处理引擎，它不支持全文本搜索；
> MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
> MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。

3.4 更新表（ALTER TABLE）

增加列

ALTER TABLE vendors
ADD vend_phone  CHAR(20); #增加一个名为vend_phone的列，必须明确其数据类型。

删除列

ALTER TABLE vendors
DROP COLUMN  vend_phone;  #删除一个名为vend_phone的列

定义外键

ALTER TABLE  orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY(order_num) REFERENCES orders(order_num);

• 更改N个表表需要N个ALTER TABLE语句
• 为了对单个表进行多个更改，可以使用单条ALTER TABLE语句，每个更改用逗号分隔。

复杂的表结构更改一般需要手动删除过程：

• 用新的列布局创建一个新表；
• 使用INSERT SELECT语句从旧表复制数据到新表。如果有必要，可使用转换函数和计算字段；
• 检验包含所需数据的新表；
• 重命名旧表（如果确定，可以删除它）；
• 用旧表原来的名字重命名新表；
• 根据需要，重新创建触发器、存储过程、索引和外键。

3.5 删除表（DROP TABLE）

删除整个表而不是其内容

DROP TABLE 表名；

3.6 重命名表（RENAME TABLE…TO）

DROP TABLE 表名1 TO 表名11，
           表名2 TO 表名22；

备份！ 数据库表的更改不能撤销！！