sql在插入语句使用子查询_SQL插入查询

最新推荐文章于 2024-08-01 09:46:22 发布

cunchi4221

最新推荐文章于 2024-08-01 09:46:22 发布

阅读量7.3k

点赞数 3

文章标签：数据库 mysql sql java oracle

原文链接：https://www.journaldev.com/18270/sql-insert-query

版权

sql在插入语句使用子查询

SQL INSERT Query also referred as SQL INSERT INTO is the command used for inserting data into database. SQL INSERT statement allows insertion of data into one table at a time.

SQL INSERT查询（也称为SQL INSERT INTO）是用于将数据插入数据库的命令。 SQL INSERT语句允许一次将数据插入一个表中。

SQL插入查询 (SQL Insert Query)

SQL INSERT statement inserts data into a table of the database. There are three ways to insert data into database using the INSERT INTO statement. Let’s try to understand all the ways we can use insert query in sql statements.

SQL INSERT语句将数据插入数据库的表中。有三种使用INSERT INTO语句将数据插入数据库的方法。让我们尝试了解在sql语句中使用插入查询的所有方式。

SQL插入范例 (SQL Insert Examples)

The three ways to use INSERT INTO statement are as mentioned below.

下面介绍了使用INSERT INTO语句的三种方法。

SQL列名称插入 (SQL Insert with Column Names)

In order to insert data into a table we have to specify the columns and the corresponding values as shown below.
Syntax:
```
INSERT INTO table_name (column(s)) VALUES value(s);
```
In the syntax mentioned above the values should be in the same order as the column mentioned in the INSERT INTO statement. Otherwise it might throw error or data might go into wrong columns. Here is an example of sql insert with column names.

为了将数据插入表中，我们必须指定列和相应的值，如下所示。

语法：
```
INSERT INTO table_name (column(s)) VALUES value(s);
```
在上述语法中，值的顺序应与INSERT INTO语句中提到的列的顺序相同。否则可能会引发错误，或者数据可能会进入错误的列。这是带有列名的sql插入示例。
不带列名SQL插入查询 (SQL Insert Query without Column Names)

In order to insert data into a table without specifying the columns, it is important to provide the values in the same order as the columns are present in the table. Also, this should be used only when we want to insert data into every column of the table.
Syntax:
```
INSERT INTO table_name VALUES value(s).
```
Example:
This type of insert is error prone, specially when you add another column in the table later on. The data might get corrupted because of getting inserted into wrong columns, this type of insert query should be avoided wherever possible.
为了在不指定列的情况下将数据插入表中，重要的是要提供与表中列相同的顺序的值。另外，仅当我们要将数据插入表的每一列时，才应使用此方法。

语法：
```
INSERT INTO table_name VALUES value(s).
```
范例：

这种类型的插入容易出错，尤其是稍后在表中添加另一列时。数据可能会由于插入错误的列而损坏，因此应尽可能避免这种插入查询。
SQL插入选择 (SQL INSERT INTO SELECT)

SQL INSERT statement can be used along with the SELECT statement. This is usually used when we have to insert data from one table to other table.
Syntax:
```
INSERT INTO first_table_name [column(s)] SELECT column(s) FROM second_table_name   WHERE condition;
```
Example:

SQL INSERT语句可以与SELECT语句一起使用。当我们必须将数据从一个表插入到另一表时，通常使用此方法。

语法：
```
INSERT INTO first_table_name [column(s)] SELECT column(s) FROM second_table_name   WHERE condition;
```
范例：

SQL插入多行 (SQL Insert Multiple Rows)

There are situations when multiple record needs to be inserted in the database table. Let’s try to understand how to accomplish such requirements.

在某些情况下，需要在数据库表中插入多个记录。让我们尝试了解如何实现这些要求。

Using the below mentioned syntax we can make multiple insertion in the database table.

使用下面提到的语法，我们可以在数据库表中进行多次插入。

INSERT INTO table_name [column(s)] VALUES [value(s)], [value(s)];

Example:

范例：

INSERT INTO Student (StudentId,SudentName) VALUES (1, 'John'), (2, 'Steve'), (3, 'Henry');

In the above mentioned example multiple rows will be inserted in the Student table.

在上面提到的示例中，将在Student表中插入多行。

SQL插入日期 (SQL Insert Date)

Before I conclude SQL insert tutorial, let’s see how to insert date into tables. This is slightly tricky because there are vendor specific functions to get current date and time. Also there are vendor specific functions to parse string to date. Below queries will work for inserting date in MySQL database table.

在我结束SQL插入教程之前，让我们看一下如何在表中插入日期。这有点棘手，因为有特定于供应商的功能来获取当前日期和时间。此外，还有一些特定于供应商的函数可以解析字符串至今。下面的查询将在MySQL数据库表中插入日期。

-- insert current date
insert into DateExample (name, insert_date)
values ('Pankaj', now()); 
--auto convert string to date because of standard format
insert into DateExample (name, insert_date)
values ('David', '2018-01-23'); 
-- use STR_TO_DATE function for non-standard format
insert into DateExample (name, insert_date)
values ('Lisa', STR_TO_DATE('2018-01-23', '%Y-%m-%d'));

Corresponding insert queries for Oracle database.

Oracle数据库的相应插入查询。

-- insert current date
insert into DateExample (id, name, insert_date)
values (1, 'Pankaj', sysdate);
--auto convert string to date because of standard format
insert into DateExample (id, name, insert_date)
values (2, 'David', '23-JAN-18');
-- use TO_DATE function for non-standard format
insert into DateExample (id, name, insert_date)
values (3, 'Lisa', to_date('23-01-2018','DD-MM-YYYY'));

That’s all for insert query in sql statements.

这就是sql语句中的插入查询的全部内容。

Reference: Oracle Documentation

参考： Oracle文档