MariaDB 插入&更新&删除数据

您所在的位置：网站首页 › linux删除表数据 › MariaDB 插入&更新&删除数据

MariaDB 插入&更新&删除数据

2024-05-03 08:54| 来源: 网络整理| 查看: 265

存储在系统中的数据是数据库管理系统(DBMS)的核心,数据库被设计用来管理数据的存储、访问和维护数据的完整性,MariaDB中提供了功能丰富的数据库管理语句,包括有效地向数据库中插入数据的INSERT语句,更新数据的UPDATE语句以及当数据不再使用时删除数据的DELETE语句,本小结将依次来介绍这些命令的使用方法和技巧.

MariaDB 插入数据

MariaDB中使用INSERT语句插入数据,可以插入的方式有:插入完整记录,插入记录的部分,插入多条记录,插入另一个查询的结果,废话不多说,老样子先来看一下插入语句的写法吧:

INSERT INTO 表名称(字段1,字段2,字段3,.....) VALUES(数值1,数值2,数值3....)

为了方便后续的练习,我们先来创建一个表结构,SQL语句如下:

MariaDB [lyshark]> create table person -> ( -> id int unsigned not null auto_increment, -> name char(50) not null default '', -> age int not null default 0, -> info char(50) null, -> primary key(id) -> ); Query OK, 0 rows affected (0.00 sec) ◆在所有字段插入数据◆

在person表中,插入一条新记录id=1,name=LyShark,age=22,info=Lawyer,SQL语句如下:

MariaDB [lyshark]> select * from person; Empty set (0.00 sec) MariaDB [lyshark]> insert into person(id,name,age,info) values(1,'LyShark',22,'Lawyer'); Query OK, 1 row affected (0.00 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+--------+ | id | name | age | info | +----+---------+-----+--------+ | 1 | LyShark | 22 | Lawyer | +----+---------+-----+--------+ 1 row in set (0.00 sec) MariaDB [lyshark]> ◆在指定字段插入数据◆

在person表中,插入一条新记录,name=Willam,age=18,info=sports,我们不给其指定ID,SQL语句如下:

MariaDB [lyshark]> desc person; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | char(50) | NO | | | | | age | int(11) | NO | | 0 | | | info | char(50) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec) MariaDB [lyshark]> insert into person(name,age,info) values('Willam',18,'sports man'); Query OK, 1 row affected (0.04 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | +----+---------+-----+------------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> ◆同时为表插入多条记录◆

在person表中,同时插入3条新记录,有多条只需要在每一条的后面加,即可,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | +----+---------+-----+------------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> insert into person(name,age,info) values('Evans',27,'secretary'), -> ('Dale',22,'cook'), -> ('Edison',28,'singer'); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | +----+---------+-----+------------+ 5 rows in set (0.00 sec) ◆将查询结果插入到表中◆

为了实现将另一个表中的记录插入到本表中,我们新建一个person_old表,其表结构和person相同,我们将person_old表中的内容全部迁移到person中去,SQL语句如下:

1.创建一个person_old表,并插入测试字段:

MariaDB [lyshark]> create table person_old -> ( -> id int unsigned not null auto_increment, -> name char(50) not null default '', -> age int not null default 0, -> info char(50) null, -> primary key(id) -> ); Query OK, 0 rows affected (0.01 sec) MariaDB [lyshark]> insert into person_old -> values(11,'harry',20,'student'),(12,'Beckham',33,'police'); Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0

2.接下来我们将person_old表中的内容迁移到person中去

MariaDB [lyshark]> select * from person_old; +----+---------+-----+---------+ | id | name | age | info | +----+---------+-----+---------+ | 11 | harry | 20 | student | | 12 | Beckham | 33 | police | +----+---------+-----+---------+ 2 rows in set (0.00 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | +----+---------+-----+------------+ 5 rows in set (0.00 sec) MariaDB [lyshark]> insert into person(id,name,age,info) -> select id,name,age,info from person_old; Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0 MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | harry | 20 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec) MariaDB 更新数据

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

UPDATE 表名称 SET 字段1=修改值,字段2=修改值,字段3=修改值 where (限定条件); ◆更新表中指定字段◆

修改person表中数据,将id=11的name字段的值改为xxxx,age字段改为200,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | harry | 20 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec) MariaDB [lyshark]> update person set age=200,name='xxxx' where id=11; #更新单个字段 Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | xxxx | 200 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec) ◆更新表的一个范围◆

更新person表中的记录,将1-12的info字段全部改为lyshark blog,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | LyShark | 22 | Lawyer | | 2 | Willam | 18 | sports man | | 3 | Evans | 27 | secretary | | 4 | Dale | 22 | cook | | 5 | Edison | 28 | singer | | 11 | xxxx | 200 | student | | 12 | Beckham | 33 | police | +----+---------+-----+------------+ 7 rows in set (0.00 sec) MariaDB [lyshark]> update person set info='lyshark blog' where age between 1 and 200; #指定修改的字段 Query OK, 7 rows affected (0.00 sec) Rows matched: 7 Changed: 7 Warnings: 0 MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | | 12 | Beckham | 33 | lyshark blog | +----+---------+-----+--------------+ 7 rows in set (0.00 sec) MariaDB 删除数据 ◆删除表中指定记录◆

通过id号,删除表中指定列,此处删除第id=12号,这条记录,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | | 12 | Beckham | 33 | lyshark blog | +----+---------+-----+--------------+ 7 rows in set (0.00 sec) MariaDB [lyshark]> delete from person where id=12; #通过id号，删除表中指定列 Query OK, 1 row affected (0.05 sec) MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+---------+-----+--------------+ 6 rows in set (0.00 sec) ◆删除表的一个范围◆

在person表中,删除age字段值在19-22的记录,SQL语句如下:

MariaDB [lyshark]> select * from person; +----+---------+-----+--------------+ | id | name | age | info | +----+---------+-----+--------------+ | 1 | LyShark | 22 | lyshark blog | | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 4 | Dale | 22 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+---------+-----+--------------+ 6 rows in set (0.00 sec) MariaDB [lyshark]> delete from person where age between 19 and 22; #指定范围删除 Query OK, 2 rows affected (0.00 sec) MariaDB [lyshark]> select * from person; +----+--------+-----+--------------+ | id | name | age | info | +----+--------+-----+--------------+ | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+--------+-----+--------------+ 4 rows in set (0.00 sec) ◆清空表中所有记录◆ MariaDB [lyshark]> select * from person; +----+--------+-----+--------------+ | id | name | age | info | +----+--------+-----+--------------+ | 2 | Willam | 18 | lyshark blog | | 3 | Evans | 27 | lyshark blog | | 5 | Edison | 28 | lyshark blog | | 11 | xxxx | 200 | lyshark blog | +----+--------+-----+--------------+ 4 rows in set (0.00 sec) MariaDB [lyshark]> delete from person; #清空表中所有记录 Query OK, 4 rows affected (0.00 sec) MariaDB [lyshark]> select * from person; Empty set (0.00 sec)

【本文地址】

MariaDB 插入&更新&删除数据

MariaDB 插入&更新&删除数据

今日新闻

推荐新闻