本篇主要内容为增删改
增加数据
基本语法简单,但是并不安全,因为这种语句高度依赖次序。应该尽量使用明确给定列名的写法,即使表结构发生变化,也能继续发挥作用
#基本语句
$ INSERT INTO Customers VALUES(
NULL,
'Pep E. LaPew',
'100 Main Street'
)
#指定列名
$ INSERT INTO customers (
cust_name,
cust_address
)
VALUES(
'Pep E. LaPew',
'100 Main Street'
);
# 使用单条INSERT语句处理多个插入比使用多条INSERT语句快
$ INSERT INTO customers (
cust_name,
cust_address
)
VALUES(
'Pep E. LaPew',
'100 Main Street'
),
(
'M. Martian',
'42 Galaxy Way'
);
# 插入查询(MySQL并不要求列名匹配,使用的是列的位置)
$ INSERT INTO customers(
cust_name,
cust_address
)
SELECT cust_name,cust_address
FROM custnew;
更新数据
UPDATE
可以更新特定行或是所有行
不要忽略WHERE子句,因为稍不注意就会更新表中所有行
$ UPDATE customers
SET cust_email='elmer@fudd.com',
cust_name='The Fudds'
WHERE cust_id=10005;
UPDATE
语句中可以使用子查询,使得能用SELECT
语句检索出的数据更新列数据
如果使用
UPDATE
更新多行,并且在更新这些行中的一行或多行出现一个错误,则整个UPDATE
操作会被取消(已更新的行会恢复到原来的值)。使用IGNORE
关键字,即使发生错误,也继续更新,用法UPDATE IGNORE customers ...
删除数据
$ DELETE FROM customers WHERE cust_id=10006;
不要忽略WHERE子句,因为稍不注意就会删除表中所有行 如果想从表中删除所有行,使用
TRUNCATE TABLE
语句速度更快(TRUNCATE
实际上是删除原来的表并重新创建空表)
更新和删除的指导原则
- 除非确实打算更新和删除每一行,否则绝对不要使用不带
WHERE
子句的UPDATE
或DELETE
子句 - 保证每张表都有主键,尽可能像
WHERE
子句那样使用它 - 在对
UPDATE
或DELETE
语句使用WHERE
子句前,应该先使用SELECT
进行测试,保证过滤的是正确的记录 - 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行
创建和操纵表
# 创建表 单主键(值是唯一的)
$ CREATE TABLE customers(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
PRIMARY KEY(cust_id)
)ENGINE=InnoDB;
# 创建表 组合主键(组合是唯一的)
$ CREATE TABLE customers(
cust_id int NOT NULL AUTO_INCREMENT,
cust_name char(50) NOT NULL,
cust_address char(50) NULL,
PRIMARY KEY(cust_id,cust_name)
)ENGINE=InnoDB;
如果仅想在一个表不存在时创建它,应该在表名后给出
IF NOT EXISTS
注意
NULL
和空串是不一样的,NULL是没有值,''
是有效的值
AUTO_INCREMENT
AUTO_INCREMENT
告诉MySQL
,本列每当增加一行时自动增量。每个表只允许一个AUTO_INCREMENT
列,而且它必须被索引
使用
SELECT last_insert_id()
获取最后一个AUTO_INCREMENT
值
指定默认值
如果在插入行时没有给出值,MySQL允许指定此时使用的默认值,使用DEFAULT
关键字指定
$ CREATE TABLE orderitems(
order_num int NOT NULL,
order_item int NOT NULL,
quantity int NOT NULL DEFAULT 1
);
与大多数DBMS不一样,MySQL不允许使用函数作为默认值
更新表
为更新表定义,可使用ALTER TABLE
语句。但是理想状态下,党表中存储数据以后,此表就不应该再被更新,在表的设计过程中需要花费大量时间来考虑,一边后期不会该表进行大的改动
# 增加
$ ALTER TABLE vendors
ADD vend_phone CHAR(20);
# 删除
$ ALTER TABLE vendors
DROP COLUMN vend_phone;
# 定义外键
$ ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREGN KEY(order_num) REFERENCES orders(order_num);
$ ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products
FOREGN KEY(prod_id) REFERENCE products(prod_id);
使用
ALTER TABLE
要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)
删除表
删除表没有确认,也不能撤销,执行这条语句将永远删除该表
DROP TABLE customers2;
重命名表
使用RENAME TABLE
语句重命名一个表
$ RENAME TABLE customers2 TO customers;
$ RENAME TABLE backup_customers TO customers
backup_vendors TO vendors;