插入、更新与删除数据

最新推荐文章于 2025-06-13 23:01:05 发布

原创最新推荐文章于 2025-06-13 23:01:05 发布 · 278 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

MySQL 专栏收录该内容

6 篇文章

订阅专栏

插入数据

为表的所有字段插入数据

语法格式：INSERT INTO table_name (column_list) VALUES (lavues_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.19 sec)

mysql> SELECT * FROM person;
Empty set (0.00 sec)

mysql> INSERT INTO person (id ,name, age, info)
    -> VALUES (1, 'Green', 21, 'Lawyer');
Query OK, 1 row affected (0.05 sec)

mysql> INSERT INTO person
    -> VALUES (2, 'Willam', 20, 'sports man');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM person;
+----+--------+-----+------------+
| id | name   | age | info       |
+----+--------+-----+------------+
|  1 | Green  |  21 | Lawyer     |
|  2 | Willam |  20 | sports man |
|  3 | Mary   |  24 | Musician   |
| 22 | Suse   |   2 | dancer     |
+----+--------+-----+------------+
4 rows in set (0.00 sec)

为表的指定字段插入数据

语法格式：INSERT INTO table_name (col_name_1,col_name_2,col_name_3,..,col_name_n)
		  VALUES (value_col_1,value_col_2,value_col_3,..,value_col_n);

mysql> INSERT INTO person
    -> VALUES (2, 'Willam', 20, 'sports man');
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM person;
+----+--------+-----+------------+
| id | name   | age | info       |
+----+--------+-----+------------+
|  1 | Green  |  21 | Lawyer     |
|  2 | Willam |  20 | sports man |
|  3 | Mary   |  24 | Musician   |
| 22 | Suse   |   2 | dancer     |
+----+--------+-----+------------+
4 rows in set (0.00 sec)

同时插入多条记录

插入多条记录时，MySQL会返回一些在执行单行插入是没有的额外信息，这些信息的含义如下：
Records: 2 Duplicates: 0 Warnings: 0

Records：表名插入的记录条数。
Duplicates：表名插入时被忽略的记录，原因可能是这些记录包含了重复的主键值。
Warnings：表明有问题的数据值，例如发生数据类型转换等等。

语法格式：INSERT INTO table_name (column_list) VALUES (value_list1), (value_list2),..,(value_listn);

mysql> INSERT INTO person (id, name, age, info)
    -> VALUES (22, 'Suse', 2, 'dancer'),
    -> (3, 'Mary', 24, 'Musician');
Query OK, 2 rows affected (0.10 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM person;
+----+-------+-----+----------+
| id | name  | age | info     |
+----+-------+-----+----------+
|  1 | Green |  21 | Lawyer   |
|  3 | Mary  |  24 | Musician |
| 22 | Suse  |   2 | dancer   |
+----+-------+-----+----------+
3 rows in set (0.00 sec)

mysql> INSERT INTO person (name, age, info)
    -> VALUES ('Dale', 22, 'cook'),
    -> ('Edision', 28, 'singer');
Query OK, 2 rows affected (0.04 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Willam  |  20 | sports man |
|  3 | Mary    |  24 | Musician   |
| 22 | Suse    |   2 | dancer     |
| 23 | Dale    |  22 | cook       |
| 24 | Edision |  28 | singer     |
+----+---------+-----+------------+
6 rows in set (0.00 sec)

将查询结果插入到表中

语法格式：INSERT INTO table_name1 (column_list1) SELECT (column_list2) FROM table_name2 WHERE (condition);

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.08 sec)

mysql> INSERT INTO person_old
    -> VALUES (11,'Harry',20,'student'),
    -> (12,'Beckham',31,'police');
Query OK, 2 rows affected (0.12 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)

mysql> INSERT INTO person (id,name,age,info)
    -> SELECT id,name,age,info FROM person_old;
Query OK, 2 rows affected (0.05 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Willam  |  20 | sports man |
|  3 | Mary    |  24 | Musician   |
| 11 | Harry   |  20 | student    |
| 12 | Beckham |  31 | police     |
| 22 | Suse    |   2 | dancer     |
| 23 | Dale    |  22 | cook       |
| 24 | Edision |  28 | singer     |
+----+---------+-----+------------+
8 rows in set (0.00 sec)

更新数据

在原有数据的基础上，修改原纪录中的数据。

语法结构：UPDATE table_name SET column_name1 = value, column_name2=value2,..,column_namen=valuen WHERE (condition);
//如果没condition，那就是对所有数据进行修改
mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Willam  |  20 | sports man |
|  3 | Mary    |  24 | Musician   |
| 11 | Harry   |  20 | student    |
| 12 | Beckham |  31 | police     |
| 22 | Suse    |   2 | dancer     |
| 23 | Dale    |  22 | cook       |
| 24 | Edision |  28 | singer     |
+----+---------+-----+------------+
8 rows in set (0.00 sec)

mysql> UPDATE person SET age = 15, name = 'LiMing' WHERE id = 11;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Willam  |  20 | sports man |
|  3 | Mary    |  24 | Musician   |
| 11 | LiMing  |  15 | student    |
| 12 | Beckham |  31 | police     |
| 22 | Suse    |   2 | dancer     |
| 23 | Dale    |  22 | cook       |
| 24 | Edision |  28 | singer     |
+----+---------+-----+------------+
8 rows in set (0.00 sec)

mysql> UPDATE person SET info = 'student' WHERE id BETWEEN 22 AND 24;
Query OK, 3 rows affected (0.05 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Willam  |  20 | sports man |
|  3 | Mary    |  24 | Musician   |
| 11 | LiMing  |  15 | student    |
| 12 | Beckham |  31 | police     |
| 22 | Suse    |   2 | student    |
| 23 | Dale    |  22 | student    |
| 24 | Edision |  28 | student    |
+----+---------+-----+------------+
8 rows in set (0.00 sec)

删除数据

语法格式：DELETE FROM table_name [WHERE <condition>];
[WHERE <condition>]为可选条件，若不选，则是删除所有记录。
mysql> SELECT * FROM person WHERE id = 11;
+----+--------+-----+---------+
| id | name   | age | info    |
+----+--------+-----+---------+
| 11 | LiMing |  15 | student |
+----+--------+-----+---------+
1 row in set (0.00 sec)

mysql> DELETE FROM person WHERE id = 11;
Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM person WHERE id = 11;
Empty set (0.00 sec)

//删除多条记录
mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Willam  |  20 | sports man |
|  3 | Mary    |  24 | Musician   |
| 11 | LiMing  |  15 | student    |
| 12 | Beckham |  31 | police     |
| 22 | Suse    |   2 | student    |
| 23 | Dale    |  22 | student    |
| 24 | Edision |  28 | student    |
+----+---------+-----+------------+
8 rows in set (0.00 sec)

mysql> DELETE FROM person WHERE id BETWEEN 22 AND 24;
Query OK, 3 rows affected (0.11 sec)

mysql> SELECT * FROM perso;
ERROR 1146 (42S02): Table 'ysh.perso' doesn't exist
mysql> SELECT * FROM person;
+----+---------+-----+------------+
| id | name    | age | info       |
+----+---------+-----+------------+
|  1 | Green   |  21 | Lawyer     |
|  2 | Willam  |  20 | sports man |
|  3 | Mary    |  24 | Musician   |
| 12 | Beckham |  31 | police     |
+----+---------+-----+------------+
4 rows in set (0.00 sec)

mysql> DELETE FROM person;
Query OK, 4 rows affected (0.12 sec)

//删除全部数据
mysql> SELECT * FROM person;
Empty set (0.00 sec)

为表增加计算列

什么是计算列？，就是通过别的列计算得来的列。

语法格式:col_name data_type [GENERATED ALWAYS] AS (experssion) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT conment] [NOT NULL | NULL] [[PRIMARY] KEY]


mysql> CREATE TABLE tb1
    -> (
    -> id INT NOT NULL AUTO_INCREMENT,
    -> a INT(9) DEFAULT NULL,
    -> b INT(9) DEFAULT NULL,
    -> c INT(9) GENERATED ALWAYS AS ((a+b)) VIRTUAL,
    -> PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.17 sec)

mysql> insert into tb1(a,b) VALUES (100,200);
Query OK, 1 row affected (0.04 sec)

mysql> SELECT * FROM tb1;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  100 |  200 |  300 |
+----+------+------+------+
1 row in set (0.00 sec)

mysql> UPDATE tb1 SET a = 500;
Query OK, 1 row affected (0.08 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM tb1;
+----+------+------+------+
| id | a    | b    | c    |
+----+------+------+------+
|  1 |  500 |  200 |  700 |
+----+------+------+------+
1 row in set (0.00 sec)