1. 如何利用SQL的INSERT语句将数据插入表中
INSERT是用来插入(或添加)行到数据库表的。插入可以用几种方式使用:
- 插入完整的行;
- 插入行的一部分;
- 插入多行;
- 插入某些查询的结果
1.1 插入完整的行
INSERT语法要求指定表名和被插入到新行中的值。
INSERT INTO Customers
VALUES(NULL,
'Pep E. LaPew' ,
'100 Main Street' ,
'Los Angeles',
'CA',
'90046',
'USA',
NULL,
NULL);
第一列cust_id也为NULL。这是因为每次插入一个新行时,该列由MySQL自动增量。你不想给出一个值(这是MySQL的工作),又不能省略此列(必须给出每个列),所以指定一个NULL值(它被MySQL忽略,MySQL在这里插入下一个可用的cust_id值)。
INSERT语句一般不会产生输出。
虽然这种语法很简单,但并不安全,应该尽量避免使用。上面的SQL语句高度依赖于表中列的定义次序,并且还依赖于其次序容易获得的信息。即使可得到这种次序信息,也不能保证下一次表结构变动后各个列保持完全相同的次序。因此,编写依赖于特定列次序的SQL语句是很不安全的。如果这样做,有时难免会出问题。下面展示一种更安全的方式
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_emai1)
VALUES( 'Pep E. LaPew' ,
'100 Main Street',
'Los Angeles ',
'CA',
'90046',
'USA',
NULL,
NULL);
因为提供了列名,VALUES必须以其指定的次序匹配指定的列名,不一定按各个列出现在实际表中的次序。其优点:即使表的结构改变,此INSERT语句仍然能正确工作。你会发现cust_id的NULL值是不必要的,cust_id列并没有出现在列表中,所以不需要任何值。
总是使用列的列表即使用上面的第二种方式,一般不要使用没有明确给出列的列表的INSERT语句。使用列的列表能使SQL代码继续发挥作用,即使表结构发生了变化
省略列
如果表的定义允许,则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件:
- 该列定义为允许NULL值(无值或空值)
- 在表定义中给出默认值。这表示如果不给出值,将使用默认值
- 如果对表中不允许NULL值且没有默认值的列不给出值,则MySQL将产生一条错误消息,并且相应的行插入不成功。
1.2 插入多个行
可以使用多条INSERT语句来实现插入多行,一次提交它们,每条语句用一个分号结束
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES( 'Pep E. LaPew' ,
'100 Main Street',
'Los Angeles ',
'CA',
'90046',
'USA');
INSERT INTO customers(cust__name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES('M. Martian',
'42 Galaxy way',
'New York',
'NY',
'11213',
'USA');
只要每条INSERT语句中的列名(和次序)相同,单条INSERT语句也可以插入多组值,每组值用一对圆括号括起来,用逗号分隔
INSERT INTO customers(cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
VALUES( 'Pep E. LaPew' ,
'100 Main Street',
'Los Angeles ',
'CA',
'90046',
'USA');
('M. Martian',
'42 Galaxy way',
'New York',
'NY',
'11213',
'USA');
MySQL用单条INSERT语句处理多个插入比使用多条INSERT语句快
1.3 插入检索出的数据—INSERT SELECT
利用INSERT将一条SELECT语句的结果插入表中
INSERT INTO customers(cust_id,
cust_contact,
cust_emai1,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country)
SELECT cust_id,
cust_contact,
cust_emai1,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country
FROM custnew;
这条语句将插入多少行依赖于custnew表中有多少行。如果这个表为空,则没有行被插入(也不产生错误,因为操作仍然是合法的)。如果这个表确实含有数据,则所有数据将被插入到customers。这个例子导入了cust_id(假设你能够确保cust_id的值不重复)。你也可以简单地省略这列(从INSERT和SELECT中),这样MySQL就会生成新值。
事实上,MySQL甚至不关心SELECT返回的列名。它使用的是列的位置,因此SELECT中的第一列(不管其列名)将用来填充表列中指定的第一个列,第二列将用来填充表列中指定的第二个列,如此等等。这对于从使用不同列名的表中导入数据是非常有用的。
INSERT SELECT
中SELECT语句可包含WHERE子句
以过滤插入的数据。
2.如何利用UPDATE和DELETE语句更新与删除数据
2.1 更新数据
采用两种方式使用UPDATE
- 更新表中特定行
- 更新表中所有行
UPDATE语句以WHERE子句结束,它告诉MySQL更新哪一行。没有WHERE子句,MySQL将更新表中所有行。
基本的UPDATE语句由3部分组成,分别是:
- 要更新的表;
- 列名和它们的新值;
- 确定要更新行的过滤条件。
UPDATE customers
SET cust_emai1 = 'elmer@fudd.com'
WHERE cust_id = 10005;
这个例子是更新客户10005的电子邮件地址
更新多个列
更新多个列只需要使用单个SET命令,每个“列=值”对之间用逗号分隔(最后一列之后不用逗号)。
UPDATE customers
SET cust_name = 'The Fudds ' ,
cust_emai1 = 'elmer@fudd.com'
WHERE cust_id = 10005;
这个例子是更新客户10005的cust_name和cust__email列
IGNORE关键字
如果用UPDATE语句更新多行,并且在更新这些行中的一行或多行时出一个现错误,则整个UPDATE操作被取消(错误发生前更新的所有行被恢复到它们原来的值)。为即使是发生错误,也继续进行更新,可使用IGNORE关键字
UPDATE IGNORE customers
删除某一列的值
为了删除某个列的值,可设置它为NULL,(假如表定义允许NULL值)。
UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;
其中NULL用来去除cust_email列中的值
2.2 删除整行数据—DELETE
采用两种方式使用DELETE
- 从表中删除特定的行;
- 从表中删除所有行。
DELETE不需要列名或通配符。DELETE删除整行而不是删除列。为了删除指定的列,请使用UPDATE语句
。
DELETE FROM customers
WHERE cust_id = 10006;
这个例子删除客户10006所在的行
如果想从表中删除所有行,不要使用DELETE。可使用
TRUNCATE TABLE语句
,它完成相同的工作,但速度更快(TRUNCATE实际是删除原来的表并重新创建一个表,而不是逐行删除表中的数据)。
2.3 更新和删除的指导准则
UPDATE和DELETE语句全都具有WHERE子句。如果省略了WHERE子句,则UPDATE或DELETE将被应用到表中所有的行。
- 除非确实打算更新和删除每一行,否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
- 保证每个表都有主键,尽可能像WHERE子句那样使用它(可以指定各主键、多个值或值的范围)。
- 在对UPDATE或DELETE语句使用WHERE子句前,应该先用SELECT进行测试,保证它过滤的是正确的记录,以防编写的WHERE子句不正确。
- 使用强制实施引用完整性的数据库,这样MySQL将不允许删除具有与其他表相关联的数据的行。
3. 如何创建、更新、删除表
下面这些语句应该在做了备份后在进行使用
3.1 创建表—CREATE TABLE语句
下面展示两种表的创建方式:
- 使用具有交互式创建和管理表的工具
- 直接用MySQL语句操纵
3.1.1 表的创建基础
利用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_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;
表名紧跟在CREATE TABLE关键字
后面。实际的表定义(所有列)括在圆括号之中。各列之间用逗号分隔。每列的定义以列名(它在表中必须是唯一的)开始,后跟列的数据类型。表的主键可以在创建表时用PRIMARY KEY关键字
指定。这里,列cust_id指定作为主键列。如果你仅想在一个表不存在时创建它,应该在表名后给出IF NOT EXISTS
。这样做不检查已有表的模式是否与你打算创建的表模式相匹配。它只是查看表名是否存在,并且仅在表名不存在时创建它。
3.1.2 使用NULL值
允许NULL值的列也允许在插入行时不给出该列的值。不允许NULL值的列不接受该列没有值的行,换句话说,在插入或更新行时,该列必须有值。每个表列或者是NULL列
,或者是NOT NULL列
,这种状态在创建时由表的定义规定。
CREATE TABLE orders(
order_num int NOT NULL AUTO_INCREMENT,
order_date datetime NOT NULL ,
cust_idint NOT NULL ,
PRIMARY KEY (order_num)
)ENGINE=InnoDB;
不要把NULL值与空串相混淆。
NULL值是没有值,它不是空串。如果指定(两个单引号,其间没有字符),这在NOT NULL列中是允许的。空串是一个有效的值,它不是无值。 NULL值用关键字NULL而不是空串指定。
3.1.3 主键
主键值必须唯一,即表中的每个行必须具有唯一的主键值。如果主键使用单个列,则它的值必须唯一。如果使用多个列,则这些列的组合值必须唯一。创建由多个列组成的主键,应该以逗号分隔的列表给出各列名
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 decima1(8,2) NOT NULL ,
PRIMARY KEY (order_num,order_item)
)ENGINE=InnoDB;
3.1.4 使用AUTO_INCREMENT—自动增量
AUTO_INCREMENT
告诉MySQL,本列每当增加一行时自动增量。每次执行一个INSERT操作时,MySQL自动对该列增量(从而才有这个关键字AUTO_INCREMENT),给该列赋予下一个可用的值。这样给每个行分配一个唯一的标识,从而可以用作主键值。
cust_id int NOT NULL AUTO_INCREMENT,
每个表只允许一个AUTO_INCREMENT列,而且它必须被索引(如通过使它成为主键)。
确定AUTO_ INCREMENT值
让MySQL生成(通过自动增量)主键的一个缺点是你不知道这些值都是谁。
SELECT last_insert_id()
此语句返回最后一个AUTOINCREMENT值,然后可以将它用于后续的MySQL语句中
3.1.5 指定默认值—DEFAULT关键字
如果在插入行时没有给出值,MySQL允许指定此时使用的默认值。默认值用CREATE TABLE语句
的列定义中的DEFAULT关键字
指定。默认值只支持常量
CREATE TABLE orderitems
(
order_num int NOT NULL ,
order_item int NOT NULL ,
prod_idchar(10) NOT NULL ,
quantity int NOT NULL DEFAULT 1,
item_price decima1(8,2) NOT NULL ,
PRIMARY KEY (order_num,order_item)
) ENGINE=InnoDB;
3.1.6 引擎类型
为什么要发行多种引擎呢?因为它们具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。下面介绍几个引擎:
- InnoDB是一个可靠的事务处理引擎,它不支持全文本搜索;
- MEMORY在功能等同于MyISAM,但由于数据存储在内存(不是磁盘)中,速度很快(特别适合于临时表);
- MyISAM是一个性能极高的引擎,它支持全文本搜索,但不支持事务处理。
引擎类型可以混用
外键不能跨引擎
混用引擎类型有一个大缺陷:外键(用于强制实施引用完整性,)不能跨引擎,即使用一个引擎的表不能引用具有使用不同引擎的表的外键。
3.2 更新表—ALTER TABLE语句
为了使用ALTER TABLE更改表结构,必须给出下面的信息:
- 在ALTER TABLE之后给出要更改的表名(该表必须存在,否则将 出错);
- 所做更改的列表
# 给表添加一个列
ALTER TABLE vendors
ADD vend_phone CHAR(20); # 必须明确其数据类型
# 给表删除一个列
ALTER TABLE Vendors
DROP COLUMN vend_phone;
小心使用ALTER TABLE
使用ALTER TABLE要极为小心,应该在进行改动前做一个完整的备份(模式和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,可能不能删除它们。类似地,如果删除了不应该删除的列,可能会丢失该列中的所有数据。
3.3 删除表—DROP TABLE
删除整个表,删除表没有确认,也不能撤销,执行后将永久删除该表。
DROP TABLE customers2;
3.4 重命名该表—RENAME TABLE语句
对单个表进行重命名
RENAME TABLE customers2 TO customers;
对多个表进行重命名
RENAME TABLE backup_customers TO customers,
backup_vendors TO vendors,
backup_products T0 products;
参考于《MYSQL必知必会》
如果对您有帮助,麻烦点赞关注,这真的对我很重要!!!如果需要互关,请评论或者私信!