《MYSQL必知必会》—19~21.插入、更新、删除数据；创建、更新、删除表

于 2021-04-13 23:10:14 发布
阅读量194 收藏 1
点赞数 1
分类专栏： # 《MYSQL必知必会》学习 文章标签： 插入数据 更新数据 创建表 更新表 《MYSQL必知必会》
本BLOG上原创文章未经本人许可，不得用于商业用途，转载请注明出处。
本文链接：https://blog.csdn.net/weixin_46649052/article/details/115678715
版权

文章目录

1. 如何利用SQL的INSERT语句将数据插入表中

  INSERT是用来插入(或添加）行到数据库表的。插入可以用几种方式使用:

  1. 插入完整的行;
  2. 插入行的一部分;
  3. 插入多行;
  4. 插入某些查询的结果

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操作中省略某些列。省略的列必须满足以下某个条件：

  1. 该列定义为允许NULL值（无值或空值）
  2. 在表定义中给出默认值。这表示如果不给出值,将使用默认值
  3. 如果对表中不允许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

  1. 更新表中特定行
  2. 更新表中所有行

UPDATE语句以WHERE子句结束，它告诉MySQL更新哪一行。没有WHERE子句，MySQL将更新表中所有行。

  基本的UPDATE语句由3部分组成，分别是:

  1. 要更新的表;
  2. 列名和它们的新值;
  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

  1. 从表中删除特定的行;
  2. 从表中删除所有行。

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

DELETE FROM customers
WHERE cust_id = 10006;

这个例子删除客户10006所在的行

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

2.3 更新和删除的指导准则

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

  1. 除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句。
  2. 保证每个表都有主键，尽可能像WHERE子句那样使用它（可以指定各主键、多个值或值的范围)。
  3. 在对UPDATE或DELETE语句使用WHERE子句前，应该先用SELECT进行测试，保证它过滤的是正确的记录，以防编写的WHERE子句不正确。
  4. 使用强制实施引用完整性的数据库，这样MySQL将不允许删除具有与其他表相关联的数据的行。

3. 如何创建、更新、删除表

  下面这些语句应该在做了备份后在进行使用

3.1 创建表—CREATE TABLE语句

  下面展示两种表的创建方式：

  1. 使用具有交互式创建和管理表的工具
  2. 直接用MySQL语句操纵
3.1.1 表的创建基础

  利用CREATE TABLE创建表，必须给出下列信息:

  1. 新表的名字，在关键字CREATE TABLE之后给出;
  2. 表列的名字和定义，用逗号分隔。
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 引擎类型

  为什么要发行多种引擎呢?因为它们具有各自不同的功能和特性,为不同的任务选择正确的引擎能获得良好的功能和灵活性。下面介绍几个引擎：

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

引擎类型可以混用

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

3.2 更新表—ALTER TABLE语句

  为了使用ALTER TABLE更改表结构，必须给出下面的信息:

  1. 在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将 出错);
  2. 所做更改的列表
# 给表添加一个列
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必知必会》

如果对您有帮助，麻烦点赞关注，这真的对我很重要！！！如果需要互关，请评论或者私信！
在这里插入图片描述

哎呦-_-不错
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值