#插入、更新与删除数据
#插入数据
1.为表的所有字段插入数据
格式:insert into 表名 (要插入的列名) values (每列对应插入的数据)
注意:列名的个数和数据值得数量必须相同,顺序必须对应,要插入列名为空的时候需要为表每一个字段指定值,且值得顺序必须和数据表中字段定义顺序相同;可同时插入多组数据,每组数据用括号包括,两组之间用','分割
eg.
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.03 sec)
mysql> select * from person;
+----+-------+-----+--------+
| id | name | age | info |
+----+-------+-----+--------+
| 1 | Green | 21 | Lawyer |
+----+-------+-----+--------+
2.为表的指定字段插入数据
格式:insert into person (列名) values (对应列值)
注意:主键不填时系统会自动为其插入自增的序列值,非主键,会插入该字段定义时默认值
eg.
mysql> insert into person (name,age) values('Willam',20);
Query OK, 1 row affected (0.00 sec)
mysql> select * from person;
+----+--------+-----+------------+
| id | name | age | info |
+----+--------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 22 | dancer |
| 3 | Mary | 24 | Musician |
| 4 | Willam | 20 | sports man |
| 5 | Willam | 20 | NULL |
+----+--------+-----+------------+
3.将查询结果插入到表中
格式:insert into 表名1 (列名) select (列名) from 表名2 where 条件
eg.
mysql> 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
mysql> select * from person;
+----+---------+-----+------------+
| id | name | age | info |
+----+---------+-----+------------+
| 1 | Green | 21 | Lawyer |
| 2 | Suse | 22 | dancer |
| 3 | Mary | 24 | Musician |
| 4 | Willam | 20 | sports man |
| 5 | Laura | 25 | NULL |
| 6 | Evans | 27 | secretary |
| 7 | Dale | 22 | cook |
| 8 | Edison | 28 | singer |
| 9 | Harry | 21 | magician |
| 10 | Harriet | 19 | pianist |
| 11 | Harry | 20 | student |
| 12 | Beckham | 31 | police |
+----+---------+-----+------------+
#数据更新
格式:update 表名 set 字段名1 = 更新后的值,....,字段名n = 更新后的值 where 条件;
eg.
mysql> select * from person where id = 11;
+----+---------+-----+------------+
| id | name | age | info |
+----+---------+-----+------------+
| 11 | Harry | 20 | student |
+----+---------+-----+------------+
12 rows in set (0.00 sec)
mysql> update person set age = 15,name = 'LiMing' where id = 11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from person where id = 11;
+----+---------+-----+------------+
| id | name | age | info |
+----+---------+-----+------------+
| 11 | LiMing | 15 | student |
+----+---------+-----+------------+
#删除数据
格式:delete from 表名 [where <条件>];
eg.
mysql> select * from person where id = 11;
+----+--------+-----+---------+
| id | name | age | info |
+----+--------+-----+---------+
| 11 | LiMing | 15 | student |
+----+--------+-----+---------+
1 row in set (0.01 sec)
mysql> delete from person where id = 11;
Query OK, 1 row affected (0.01 sec)
mysql> select * from person where id = 11;
Empty set (0.00 sec)
MySql数据的插入、更新与删除
最新推荐文章于 2024-01-31 00:00:00 发布