存储在系统中的数据是数据库管理系统(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值为Lawyermysql> 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值为dancermysql> 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 manmysql> 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值为25mysql> 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字段值改为LiMingmysql> 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字段值都改为studentmysql> 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快。