存储在系统中的数据是数据库管理系统(DBMS)的核心,数据库被设计用来管理数据的存储、访问和维护数据的完整性。MySQL中提供丰富的数据管理语句,包括插入数据的INSERT、更新数据的UPDATE以及删除数据的DELETE语句。
成都创新互联公司专注于新昌网站建设服务及定制,我们拥有丰富的企业做网站经验。 热诚为您提供新昌营销型网站建设,新昌网站制作、新昌网页设计、新昌网站官网定制、微信小程序服务,打造新昌网络公司原创品牌,更为您提供新昌网站排名全网营销落地服务。
7.1、插入数据
为表的所有字段插入数据
使用基本的INSERT语句插入数据,要求指定表名称和插入到新纪录中的值,其基本语法为:
INSERT INTO tbl_name (column_list) VALUES (value_list)
在插入数据前,首先创建一张表:
mysql> 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) -> ); Query OK, 0 rows affected (0.03 sec)
在person表中,插入一条新记录,id值为1,name值为Green,age值为21,info值为Lawyer
mysql> INSERT INTO person (id ,name, age , info) -> VALUES (1,'Green', 21, 'Lawyer'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM person; +----+-------+-----+--------+ | id | name | age | info | +----+-------+-----+--------+ | 1 | Green | 21 | Lawyer | +----+-------+-----+--------+ 1 row in set (0.00 sec)
在person表中,插入一条新记录,id值为2,name值为Suse,age值为22,info值为dancer
mysql> INSERT INTO person (age ,name, id , info) -> VALUES (22, 'Suse', 2, 'dancer'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM person; +----+-------+-----+--------+ | id | name | age | info | +----+-------+-----+--------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | +----+-------+-----+--------+ 2 rows in set (0.00 sec)
为表的指定字段插入数据
在person表中,插入一条新记录,name值为Willam,age值为20,info值为sports man
mysql> INSERT INTO person (name, age,info) -> VALUES('Willam', 20, 'sports man'); Query OK, 1 row affected (0.02 sec) mysql> SELECT * FROM person; +----+--------+-----+------------+ | id | name | age | info | +----+--------+-----+------------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | +----+--------+-----+------------+ 3 rows in set (0.00 sec)
在person表中,插入一条新记录,name值为laura,age值为25
mysql> INSERT INTO person (name, age ) VALUES ('Laura', 25); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM person; +----+--------+-----+------------+ | id | name | age | info | +----+--------+-----+------------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | | 4 | Laura | 25 | NULL | +----+--------+-----+------------+ 4 rows in set (0.00 sec)
可以发现id字段在插入数据后,没有赋值时自动增加,在这里id字段为表的主键,不能为空,紫铜会自动为字段插入自增的序列值。
同时插入多条记录
INSERT语句可以同时相数据表中插入多条记录,插入时指定多个值列表,每个值列表之间用逗号分隔开,基本语法为:
INSERT INTO tbl_name (column_list) VALUES (value_list1), (value_list2),(value_list3);
在person表中,在name、age和info字段指定插入值,同时插入3条新记录
mysql> INSERT INTO person(name, age, info) -> VALUES ('Evans',27, 'secretary'), -> ('Dale',22, 'cook'), -> ('Edison',28, 'singer'); Query OK, 3 rows affected (0.02 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM person; +----+--------+-----+------------+ | id | name | age | info | +----+--------+-----+------------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | | 4 | Laura | 25 | NULL | | 5 | Evans | 27 | secretary | | 6 | Dale | 22 | cook | | 7 | Edison | 28 | singer | +----+--------+-----+------------+ 7 rows in set (0.00 sec)
在person表中,不指定插入列表,同时插入2条新记录
mysql> INSERT INTO person -> VALUES (9,'Harry',21, 'magician'), -> (NULL,'Harriet',19, 'pianist'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | | 4 | Laura | 25 | NULL | | 5 | Evans | 27 | secretary | | 6 | Dale | 22 | cook | | 7 | Edison | 28 | singer | | 9 | Harry | 21 | magician | | 10 | Harriet | 19 | pianist | +----+---------+-----+------------+ 9 rows in set (0.00 sec)
将查询结果插入数据
INSERT语句用来给数据表插入记录时,指定插入记录的列值。INSERT还可以将SELECT语句查询的结果插入到列表中,其基本语法为:
INSERT INTO tbl_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition)
从person_old表中查询所有的记录,并将其插入到person表中
首先,创建一个名为person_old的数据表,其表结构与person结构相同 mysql> 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) -> ); Query OK, 0 rows affected (0.11 sec) 向person_old表中添加两条记录 mysql> INSERT INTO person_old -> VALUES (11,'Harry',20, 'student'), (12,'Beckham',31, 'police'); Query OK, 2 rows affected (0.20 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM person_old; +----+---------+-----+---------+ | id | name | age | info | +----+---------+-----+---------+ | 11 | Harry | 20 | student | | 12 | Beckham | 31 | police | +----+---------+-----+---------+ 2 rows in set (0.00 sec) 插入数据到person表中 mysql> INSERT INTO person(id, name, age, info) -> SELECT id, name, age, info FROM person_old; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM person; +----+---------+-----+------------+ | id | name | age | info | +----+---------+-----+------------+ | 1 | Green | 21 | Lawyer | | 2 | Suse | 22 | dancer | | 3 | Willam | 20 | sports man | | 4 | Laura | 25 | NULL | | 5 | Evans | 27 | secretary | | 6 | Dale | 22 | cook | | 7 | Edison | 28 | singer | | 9 | Harry | 21 | magician | | 10 | Harriet | 19 | pianist | | 11 | Harry | 20 | student | | 12 | Beckham | 31 | police | +----+---------+-----+------------+ 11 rows in set (0.00 sec)
7.2、更新数据
表中有数据之后,可以对数据进行更新,其基本语法为:
UPDATE table_name SET col_name1=value1,col_name2=value2,..., WHERE where_condition
在person表中,更新id值为11的记录,将age字段值改为15,将name字段值改为LiMing
mysql> UPDATE person SET age = 15, name='LiMing' WHERE id = 11; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM person WHERE id=11; +----+--------+-----+---------+ | id | name | age | info | +----+--------+-----+---------+ | 11 | LiMing | 15 | student | +----+--------+-----+---------+ 1 row in set (0.00 sec)
在person表中,更新age值为19~22的记录,将info字段值都改为student
mysql> UPDATE person SET info='student' WHERE age BETWEEN 19 AND 22; Query OK, 6 rows affected (0.02 sec) Rows matched: 6 Changed: 6 Warnings: 0 mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22; +----+---------+-----+---------+ | id | name | age | info | +----+---------+-----+---------+ | 1 | Green | 21 | student | | 2 | Suse | 22 | student | | 3 | Willam | 20 | student | | 6 | Dale | 22 | student | | 9 | Harry | 21 | student | | 10 | Harriet | 19 | student | +----+---------+-----+---------+ 6 rows in set (0.00 sec)
7.3、删除数据
从数据表中删除数据使用DELETE语句,其基本语法为:
DELETE FROMN table_name [WHERE]
在person表中,删除id等于11的记录
执行删除操作前,使用SELECT语句查看当前id=11的记录 mysql> SELECT * FROM person WHERE id=11; +----+--------+-----+---------+ | id | name | age | info | +----+--------+-----+---------+ | 11 | LiMing | 15 | student | +----+--------+-----+---------+ 1 row in set (0.00 sec) 使用DELETE语句删除该记录 mysql> DELETE FROM person WHERE id = 11; Query OK, 1 row affected (0.02 sec) 语句执行完毕,查看执行结果: mysql> SELECT * FROM person WHERE id=11; Empty set (0.00 sec)
在person表中,使用DELETE语句同时删除多条记录,删除age字段在19-22的记录
执行删除操作前,使用SELECT语句查看当前的数据 mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22; +----+---------+-----+---------+ | id | name | age | info | +----+---------+-----+---------+ | 1 | Green | 21 | student | | 2 | Suse | 22 | student | | 3 | Willam | 20 | student | | 6 | Dale | 22 | student | | 9 | Harry | 21 | student | | 10 | Harriet | 19 | student | +----+---------+-----+---------+ 6 rows in set (0.00 sec) DELETE删除这些记录 mysql> DELETE FROM person WHERE age BETWEEN 19 AND 22; Query OK, 6 rows affected (0.01 sec) 查看执行结果 mysql> SELECT * FROM person WHERE age BETWEEN 19 AND 22; Empty set (0.00 sec)
删除person表中所有记录,SQL语句如下
执行删除操作前,使用SELECT语句查看当前的数据: mysql> SELECT * FROM person; +----+---------+-----+-----------+ | id | name | age | info | +----+---------+-----+-----------+ | 4 | Laura | 25 | NULL | | 5 | Evans | 27 | secretary | | 7 | Edison | 28 | singer | | 12 | Beckham | 31 | police | +----+---------+-----+-----------+ 4 rows in set (0.00 sec) 执行DELETE语句删除这4条记录 mysql> DELETE FROM person; Query OK, 4 rows affected (0.01 sec) 查看执行结果: mysql> SELECT * FROM person; Empty set (0.00 sec)
如果想删除表中的所有记录,还可以使用TRUNCATE TABLE语句,TRUNCATE将直接删除原来的表并重新创建一个表,其语法结构为TRUNCATE TABLE table_name。TRUNCATE直接删除表而不是删除记录,因此执行速度比DELETE快。