向表中的指定列插入数据

news/2024/11/28 7:36:39/

MySQL从小白到总裁完整教程目录:https://blog.csdn.net/weixin_67859959/article/details/129334507?spm=1001.2014.3001.5502

练习:向test02表,所有列,插入数据

复习下前面的

mysql> #插入记录
mysql> insert into test02 values(1, '张三', '男', 100.5);
Query OK, 1 row affected (0.00 sec)mysql> insert into test02 values(2, '李四', '男', 100);
Query OK, 1 row affected (0.00 sec)mysql> insert into test02 values(3, '王五', '男', 100.67);
Query OK, 1 row affected (0.02 sec)mysql> insert into test02 values(4, '赵六', '男', 99);
Query OK, 1 row affected (0.00 sec)mysql> insert into test02 values(5, '天天', '男', 9999); 
Query OK, 1 row affected, 1 warning (0.00 sec)mysql> #查看结果
mysql> select * from test02;
+------+------+--------+-------+
| id   | name | gender | score |
+------+------+--------+-------+
|    1 | 张三 | 男     | 100.5 |
|    2 | 李四 | 男     | 100.0 |
|    3 | 王五 | 男     | 100.7 |
|    4 | 赵六 | 男     |  99.0 |
|    5 | 天天 | 男     | 999.9 |
+------+------+--------+-------+
5 rows in set (0.00 sec)

新知识

语法格式:

insert into 表名(列名1, 列名2, ..., 列名n) values(列值1, 列值2, ..., 列值n);

说明：如果未提供的列，值默认是空值 null

案列:向test03表,指定的列,插入数据

首先需要查看表结构

mysql> desc test03;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(30) | YES  |     | NULL    |       |
| age     | int(3)      | YES  |     | NULL    |       |
| salary  | double(8,2) | YES  |     | NULL    |       |
| etime   | date        | YES  |     | NULL    |       |
| address | varchar(50) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)

有五列数据,插入以下数据

mysql> #根据语法、指定的列插入数据
mysql> insert into test03(name, age, salary)-> values('king', 20, 999);
Query OK, 1 row affected (0.01 sec)mysql>
mysql> insert into test03(name, age, etime)-> values('tom', 23, '2022-06-30');
Query OK, 1 row affected (0.00 sec)mysql>
mysql> insert into test03(name, address)-> values('rose', '北京');
Query OK, 1 row affected (0.01 sec)mysql>
mysql> insert into test03(age, salary, etime)-> values(19, 777, '2022-06-01');
Query OK, 1 row affected (0.00 sec)mysql>
mysql> insert into test03(name, age, salary, address)-> values('lucy', 21, 88, '上海');
Query OK, 1 row affected (0.01 sec)mysql>
mysql> #查询验证
mysql> select * from test03;
+------+------+--------+------------+---------+
| name | age  | salary | etime      | address |
+------+------+--------+------------+---------+
| king |   20 | 999.00 | NULL       | NULL    |
| tom  |   23 |   NULL | 2022-06-30 | NULL    |
| rose | NULL |   NULL | NULL       | 北京    |
| NULL |   19 | 777.00 | 2022-06-01 | NULL    |
| lucy |   21 |  88.00 | NULL       | 上海    |
+------+------+--------+------------+---------+
5 rows in set (0.00 sec)