MySQL学习 -- 表中插入数据及复制表和批量插入

最新推荐文章于 2024-03-14 10:36:47 发布

Haoea！

最新推荐文章于 2024-03-14 10:36:47 发布

阅读量1.7k

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/snwown/article/details/121261767

版权

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

表中插入数据

insert语句插入数据
语法格式

insert into 表名(字段名1，字段名2，字段名3，……)
values
(值1,值2,值3,……)

要求：字段的数量和值的数量相同，并且数据类型要对应相同。
案例：往上节的数据中插入数据

mysql> insert into
    -> t_student(no,name,sex,chlass_no,birth)
    -> values(1,'zhangsan','1','gaosan1ban','1950-10-12');
Query OK, 1 row affected (0.28 sec)

mysql> select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | chlass_no  | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
+------+----------+------+------------+------------+
1 row in set (0.04 sec)

如果说只输入几个字段，那么剩下的所有字段自动插入NULL

mysql> insert into
    -> t_student(no,name,sex)
    -> values(2,'lisi','2');
Query OK, 1 row affected (0.10 sec)

mysql> select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | chlass_no  | birth      |
+------+----------+------+------------+------------+
|    1 | zhangsan | 1    | gaosan1ban | 1950-10-12 |
|    2 | lisi     | 2    | NULL       | NULL       |
+------+----------+------+------------+------------+
2 rows in set (0.00 sec)

如果当这个表存在的话，则删掉
语法：drop table if exists 表名；

mysql> show tables;
+----------------------------+
| Tables_in_switch_cloud_new |
+----------------------------+
| base_community             |
| capture_device             |
| ht_subdistrict_info        |
| scan_problem_info          |
| scan_problem_info_item     |
| t_student                  |
+----------------------------+
6 rows in set (0.00 sec)

mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.44 sec)

mysql> show tables;
+----------------------------+
| Tables_in_switch_cloud_new |
+----------------------------+
| base_community             |
| capture_device             |
| ht_subdistrict_info        |
| scan_problem_info          |
| scan_problem_info_item     |
+----------------------------+
5 rows in set (0.00 sec)

给表的某个字段一个默认值
后面加上default 后面跟上默认值

mysql> create table t_student(
    -> no bigint,
    -> name varchar(255),
    -> sex char(1) default 1,
    -> classno varchar(255),
    -> birth char(10)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> select * from t_student;
Empty set (0.00 sec)

mysql> desc t_student;
+---------+--------------+------+-----+---------+-------+
| Field   | Type         | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| no      | bigint       | YES  |     | NULL    |       |
| name    | varchar(255) | YES  |     | NULL    |       |
| sex     | char(1)      | YES  |     | 1       |       |
| classno | varchar(255) | YES  |     | NULL    |       |
| birth   | char(10)     | YES  |     | NULL    |       |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.15 sec)

有默认值的时候，不传入这个值，也是会有这个值的

mysql> insert into t_student(name) values('zhangsan');
Query OK, 1 row affected (0.05 sec)

mysql> select * from t_student;
+------+----------+------+---------+-------+
| no   | name     | sex  | classno | birth |
+------+----------+------+---------+-------+
| NULL | zhangsan | 1    | NULL    | NULL  |
+------+----------+------+---------+-------+
1 row in set (0.00 sec)

注意：当一条insert语句执行成功之后，表格当中必然会多一行记录，即使多的这一行记录当中某些字段是null，后期也没有办法在执行insert语句插入数据了，只能使用update进行更新。

mysql> insert into t_student values(1,'lisi','0','gaosan1ban','1998-10-20');
Query OK, 1 row affected (0.04 sec)

mysql> select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1    | NULL       | NULL       |
|    1 | lisi     | 0    | gaosan1ban | 1998-10-20 |
+------+----------+------+------------+------------+
2 rows in set (0.00 sec)

字段可以省略不写，但是后面的values对数量和顺序都有要求，要对得上。

mysql> insert into t_student values(2,'si','1','gaosan1ban','1998-02-20'),(3,'ttey','0','gaosan2ban','1997-04-05');
Query OK, 2 rows affected (0.06 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from t_student;
+------+----------+------+------------+------------+
| no   | name     | sex  | classno    | birth      |
+------+----------+------+------------+------------+
| NULL | zhangsan | 1    | NULL       | NULL       |
|    1 | lisi     | 0    | gaosan1ban | 1998-10-20 |
|    2 | si       | 1    | gaosan1ban | 1998-02-20 |
|    3 | ttey     | 0    | gaosan2ban | 1997-04-05 |
+------+----------+------+------------+------------+
4 rows in set (0.00 sec)

一次插入多行数据，可以直接在values后面加上(),()

表的复制

语法：

create table 表名 as select 语句;

在这里插入图片描述

mysql> create table t_student2 as select name,sex from t_student;
Query OK, 4 rows affected (0.24 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> desc t_student2;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(255) | YES  |     | NULL    |       |
| sex   | char(1)      | YES  |     | 1       |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

语法：create table 表名 as select 语句；
将查询结果当做表创建出来。

mysql> create table scan_problem_info_item1 as select * from scan_problem_info_item;
Query OK, 10 rows affected (0.22 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from scan_problem_info_item1;
+----+------+--------------------+------+
| id | type | type_name          | num  |
+----+------+--------------------+------+
|  1 | 1    | 基础信息缺失       |   23 |
|  2 | 2    | 抓拍设备异常       |   33 |
|  3 | 3    | 数据质量问题       |   45 |
|  4 | 4    | 视频设备异常       |   22 |
|  5 | 5    | 其他               |   21 |
|  6 | 8    | heosjeo            |    3 |
|  7 | 8    | sjeo_djoe          |   45 |
|  8 | 1    | 基础信息缺失       | 4556 |
|  9 | 2    | 抓拍设备异常       |   32 |
| 10 | 3    | 数据质量问题       |    1 |
+----+------+--------------------+------+
10 rows in set (0.00 sec)

mysql> insert into scan_problem_info_item1 select * from scan_problem_info_item;
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from scan_problem_info_item1;
+----+------+--------------------+------+
| id | type | type_name          | num  |
+----+------+--------------------+------+
|  1 | 1    | 基础信息缺失       |   23 |
|  2 | 2    | 抓拍设备异常       |   33 |
|  3 | 3    | 数据质量问题       |   45 |
|  4 | 4    | 视频设备异常       |   22 |
|  5 | 5    | 其他               |   21 |
|  6 | 8    | heosjeo            |    3 |
|  7 | 8    | sjeo_djoe          |   45 |
|  8 | 1    | 基础信息缺失       | 4556 |
|  9 | 2    | 抓拍设备异常       |   32 |
| 10 | 3    | 数据质量问题       |    1 |
|  1 | 1    | 基础信息缺失       |   23 |
|  2 | 2    | 抓拍设备异常       |   33 |
|  3 | 3    | 数据质量问题       |   45 |
|  4 | 4    | 视频设备异常       |   22 |
|  5 | 5    | 其他               |   21 |
|  6 | 8    | heosjeo            |    3 |
|  7 | 8    | sjeo_djoe          |   45 |
|  8 | 1    | 基础信息缺失       | 4556 |
|  9 | 2    | 抓拍设备异常       |   32 |
| 10 | 3    | 数据质量问题       |    1 |
+----+------+--------------------+------+
20 rows in set (0.00 sec)

将查询结果插入到一个表中：insert into tt2 select * from tt;

Haoea！

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySQL学习 -- 表中插入数据及复制表和批量插入

表中插入数据insert语句插入数据语法格式insert into 表名(字段名1，字段名2，字段名3，……)values(值1,值2,值3,……)要求：字段的数量和值的数量相同，并且数据类型要对应相同。案例：往上节的数据中插入数据mysql> insert into -> t_student(no,name,sex,chlass_no,birth) -> values(1,'zhangsan','1','gaosan1ban','1950-10-12')
复制链接

扫一扫