9.增删改操作

目录

一、插入操作

1、为表的所有字段插入数据

2、为表的指定字段插入数据

3、同时插入多条记录

4、将查询结果插入表中：

二、更新操作

三、删除操作

四、练习题

一、插入操作

在使用数据库之前，数据库中必须要有数据，MYSQL中使INSERT语向数据库表中插入新的数据记录。可以插入的方式有:插入完整的记录、插入记录的一部分、 插入多条记录插入另一个查询的结果，下面将分别介绍这些内容。

注意：

首先要use db进入目标数据库中。

其次要确保数据库中有要操作的表。

1、为表的所有字段插入数据

使用基本的 INSERT 语句插入数据，要求指定表名称和插入到新记录中的值。

基本语法格式为:

INSERT INTO table_name (column list) VALUES (value list);

table_name指定要插入数据的表名，column list指定要插入数据的那些列 value list指定每个列应对应插入的数据。

注意，使用该语句时字段列和数据值的数量必须相同。

本章将使用样例表person，创建语句如下：

create table person 
( 
id int unsigned not null auto_increment, 
name char(40) not null default '', 
age int not null default 0, 
info char(50) null, 
primary key (id) 
); 

向表中所有字段插入值的方法有两种：一种是指定所有字段名，另一种是完全不指定字段名。

举例1：

在person表中，插入一条新记录，id值为1，name值为Green，age值为21，info值为 Lawyer，

SQL语句如下：

执行插入操作之前，使用SELECT语句查看表中的数据：

select * from person;

结果显示当前表为空，没有数据，接下来执行插入操作：

insert into person (id ,name, age , info)
values (1,'Green', 21, 'Lawyer'); 

语句执行完毕，查看执行结果：

select * from person;

在插入数据时，指定了person表的所有字段，因此将为每一个字段插入新的值。 INSERT语句后面的列名称顺序可以不是person表定义时的顺序。 即插入数据时，不需要按照表定义的顺序插入，只要保证值的顺序与列字段的顺序相同就可以。

举例2：在person表中，插入一条新记录，id值为2，name值为Suse，age值为22， info值为dancer，

SQL语句如下：

insert into  person (age ,name, id , info) 
values (22, 'Suse', 2, 'dancer'); 

语句执行完毕，查看执行结果：

select * from person;

SELECT * FROM person;

由结果可以看到，INSERT语句成功插入了一条记录。使用INSERT插入数据时， 允许列名称列表 column list 为空，此时，值列表中需要为表的每一个字段指定值， 并且值的顺序必须和数据表中字段定义时的顺序相同。

举例：

在person表中，插入一条新记录，id值为3，name值为Mary，age值为24，info值为Musician，

SQL语句如下：

insert into person
values (3,'Mary', 24, 'Musician');

语句执行完毕，查看执行结果：

select * from person;

可以看到插入记录成功。数据库中增加了一条 id 为3的记录，其他字段值为指定的插入值。

本例的INSERT语句中没有指定插入列表，只有一个值列表。 在这种情况下，值列表为每一个字段列指定插入值，并且这些值的顺序必须和 person表中字段定义的顺序相同。

虽然使用INSERT 插入数据时可以忽略插入数据的列名称，但是值如果不包含列名称，

那VALUES 关键字后面的值不仅要求完整而且顺序必须和表定义时列的顺序相同。

如果表的结构被修改，对列进行增加、删除或者位置改变操作，这些操作将使得用这种方式插入数据时的顺序也同时改变。如果指定列名称，则不会受到表结构改变的影响。

2、为表的指定字段插入数据

为表的指定字段插入数据，就是在INSERT语句中只向部分字段中插入值，

而其他字段的值为表定义时的默认值。

举例：在person表中，插入一条新记录，name值为Willam，age值为20，info值为sports man，

SQL语句如下：

insert into person (name,age,info)
values ('Willam', 20, 'sports man');

提示信息表示插入一条记录成功。使用SELECT查询表中的记录，查询结果如下：

select * from person;

可以看到插入记录成功。如查询结果显示，该 id 字段自动添加了一个整数值 4。

在这里id 字段为表的主键，不能为空，系统会自动为该字段插入自增的序列值。

在插入记录时，如果某些字段没有指定插入值，MySQL将插入该字段定义时的默认值。

下面例子说明在没有指定列字段时，插入默认值。

举例：在person表中，插入一条新记录，name值为laura，age值为25，SQL语句如下：

insert into person (name,age)
values ('Laura', 25);

语句执行完毕，查看执行结果，

select * from person;

可以看到，在本例插入语句中，没有指定 info 字段值，查询结果显示，info字段在定义时默认为NULL，因此系统自动为该字段插入空值。

3、同时插入多条记录

INSERT语句可以同时向数据表中插入多条记录，插入时指定多个值列表，

每个值列表之间用逗号分隔开，基本语法格式如下:

INSERT INTO table_name (column list) VALUES (value list1),(value list2),...,(value listn);

value listl,value list2,...value listn;表示第1,2,... n个插入记录的字段的值列表.

举例：在person表中，在name、age和info字段指定插入值，同时插入3条新记录，

SQL语句如下：

insert into person (name,age,info)
values ('Evans',27, 'secretary'),
('Dale',22, 'cook'),
('Edison',28, 'singer');

语句执行完毕，查看执行结果：

select * from person;

由结果可以看到，INSERT语句执行后，person 表中添加了3条记录，其name和age字段分别为指定的值，id字段为MySQL添加的默认的自增值。 使用INSERT同时插入多条记录时，MySQL会返回一些在执行单行插入时没有的额外信息， 这些包含数的字符串的意思分别是:

Records:表明插入的记录条数。

Duplicates: 表明插入时被忽略的记录，原因可能是这些记录包含了重复的主键值，

Warnings:表明有问题的数据值，例如发生数据类型转换。

举例：在person表中，不指定插入列表，同时插入2条新记录，SQL语句如下：

INSERT INTO person 
VALUES (9,'Harry',21, 'magician'), 
(NULL,'Harriet',19, 'pianist');

语句执行结果如下：

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

语句执行完毕，查看执行结果：

select * from person;

由结果可以看到，INSERT 语句执行后，person 表中添加了2条记录，

与前面介绍单个INSERT语法不同，person 表名后面没有指定插入字段列表，因此，VALUES 关键字后面的多个值列表，都要为每一条记录的每一个字段列指定插入值， 并且这些值的顺序必须和 person表中字段定义的顺序相同，带有AUTO_INCREMENT属性

的id 字段插入NULL值，系统会自动为该字段插入唯一的自增号。

4、将查询结果插入表中：

INSERT语句用来给数据表插入记录时指定插入记录的列值。INSERT还可以将SELECT语句查询的结果插入到表中，如果想要从另外一个表中合并个人信息到 person 表，不需要把每一条记录的值一个一个输入，只需要使用一条INSERT语和一条SELECT语句组成的组合语句，即可快速地从一个或多个表中向一个表中插入多个行。基本语法格式如下:

INSERT INTO table_name1 (column list1)

cSELECT (olumn list2)   FROM table_name2  WHERE condition

table_name1指定待插入数据的表;column listl 指定待插入表中要插入数据的哪些列

table_name2指定插入数据是从哪个表中查询出来的:

column list2指定数据来源表的查询列该列表必须和column list1列表中的字段个数相同，数据类型相同:

condition指定SELECT语句的查询条件。

举例：

从person_old表中查询所有的记录，并将其插入到person表中。

首先，创建一个名为person_old的数据表，其表结构与person结构相同，SQL语句如下：

CREATE TABLE person_old
(
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(40) NOT NULL DEFAULT '',
age INT NOT NULL DEFAULT 0,
info CHAR(50) NULL,
PRIMARY KEY (id)
);

向person_old表中添加两条记录：

insert into person_old
values (11,'Harry',20, 'student'), 
(12,'Beckham',31, 'police');

现在peson_old表中现在有两条记录。接下来将person_old表中所有的记录插入person表中，SQL语句如下：

insert into person(id, name, age, info)
select id, name, age, info FROM person_old;

查看执行结果：

select * from person;

由结果可以看到,INSERT语句执行后 person表中多了两条记录 这两条记录和 person_old表中的记录完全相同，数据转移成功。这里的 id 字段为自增的主键，在插入的时候要保证该字段值的唯一性，如果不能确定，可以插入的时候忽略该字段，只插入其他字段的值。这个例子中使用的person_old表和 person 表的定义相同。事实上,MySOL不关心SELECT 返回的列名，它根据列的位置进行插入，SELECT的第1列对应待插入表的第1列， 第2列对应待插入表的第2列，等等。即使不同结果的表之间也可以方便地转移数据。

二、更新操作

更新操作：

表中有数据之后，接下来可以对数据进行更新操作，MySQL中使用UPDATE语句更新表中的记录，可以更新特定的行或者同时更新所有的行。基本语法结构如下:

UPDATE table_name

SET column_name1 = value1,column_name2=value2......column_namen=valuen

WHERE (condition);

column_name1,column_name2......,column_namen 为指定更新的字段的名称;

valuel.value2......valuen 为相对应的指定字段的更新值;

condition 指定更新的记录需要满足的条件更新多个列时，每个“列-值”对之间用逗号隔开，最后一列之后不需要逗号。

举例：在person表中，更新id值为11的记录，将age字段值改为15，将name字段值改为LiMing，

SQL语句如下：

update person 
set age = 15, name='LiMing'
where id = 11;

更新操作执行前可以使用SELECT语句查看当前的数据：

select * from person where id=11; 

由结果可以看到更新之前，id等于11的记录的name字段值为harry，age字段值为20， 更新之后可以看到id等于11的记录中的name和age字段的值已经成功被修改为指定值。

保证UPDATE以WHERE子句结束，通过WHERE子指定被更新的记录所需要满足的条件，

如果忽略WHERE子句，MySOL将更新表中所有的行。

举例：在person表中，更新age值为19~22的记录，将info字段值都改为student，

SQL语句如下：

更新操作执行前可以使用SELECT语句查看当前的数据：

select * from person where age between 19 and 22;

update person 
set info='student'
where age between 19 and 22;

语句执行完毕，查看执行结果：

select * from person where age between 19 and 22;

由结果可以看到，UPDATE 执行后，成功将表中符合条件的6条记录的info字段值都改为student。

三、删除操作

删除数据：

从数据表中删除数据使用DELETE语句

DELETE语允许WHERE子指定删除条件DELETE语句基本语法格式如下:

DELETE FROM table_name [WHERE <condition>];

table_name指定要执行删除操作的表;

[WHERE<condition>] 为可选参数，指定删除条件，

如果没有WHERE子句，DELETE语句将删除表中的所有记录。

举例：在person表中，删除id等于11的记录，SQL语句如下：

执行删除操作前，使用SELECT语句查看当前id=11的记录：

select * from person where id=11;

可以看到，现在表中有id=11的记录，下面使用DELETE语句删除该记录，语句执行结果如下：

delete from person where id = 11;

语句执行完毕，查看执行结果：

select * from person where id=11;

查询结果为空，说明删除操作成功

举例2：在person表中，使用DELETE语句同时删除多条记录，在前面UPDATE语句中将 age字段值在19~22之间的记录的info字段值修改为student，在这里删除这些记录，

SQL语句如下：

执行删除操作前，使用SELECT语句查看当前的数据：

select * from person where age between 19 and 22;

可以看到，这些age字段值在19~22之间的记录存在表中。下面使用DELETE删除这些

记录：

delete from person where age between 19 and 22;

语句执行完毕，查看执行结果：

select * from person where age between 19 and 22;

查询结果为空，删除多条记录成功。

举例3：删除person表中所有记录，SQL语句如下：

执行删除操作前，使用SELECT语句查看当前的数据：

select * from person;

结果显示person表中还有5条记录，执行DELETE语句删除这5条记录：

delete from person;

语句执行完毕，查看执行结果：

select * from person;

查询结果为空，删除表中所有记录成功，现在person表中已经没有任何数据记录。

如果想删除表中的所有记录，还可以使用TRUNCATE TABLE语句，TRUNCATE将直接 删除原来的表并重新创建一个表，其语法结构为TRUNCATE TABLE table_name。

TRUNCATE直接删除表而不是删除记录，因此执行速度比DELETE快。

四、练习题

创建数据表pet，并对表进行插入、更新与删除操作，pet 表结构如表8.3所示。

(1)首先创建数据表pet，使用不同的方法将表8.4中的记录插入到pet表中。

(2)使用UPDATE语将名称为 Fang的狗的主人改为Kevin。

3)将没有主人的宠物的owner 字段值都改为 Duck。

(4)删除已经死亡的宠物记录。

(5)删除所有表中的记录。

use demo1;
drop table pet;
create table pet (
    name varchar(20)  not null comment '宠物名称',
    owner varchar(20) comment '宠物主人',
    species varchar(20) not null comment '种类',
    sex char(1) not null  comment '性别',
    birth year not null comment '出生日期',
    death year comment '死亡日期'
);

insert into pet(name, owner, species, sex, birth, death) values
('Fluffy', 'Harold', 'cat', 'f', '2003', '2010'),
('Claws', 'Gwen', 'cat', 'm', '2004', null),
('Buffy', null, 'dog', 'f', '2009', null),
('Fang', 'Benny', 'dog', 'm', '2000', null),
('Bowser', 'Diane', 'dog', 'm', '2003', '2009'),
('Chirpy', null, 'bird', 'f', '2008', null);

update pet set owner = 'Kevin' where name = 'Fang';

update pet set owner = 'Duck' where owner is null;

delete from pet where death is not null;

delete from pet;

select * from pet;