sql语句 sum 用法
SQL provides different statements in order to select into existing table, variable, temp table in various database servers like Oracle, MySQL, PostgreSQL etc. In this tutorial we will examine how to use Select Into
SQL statement for different cases.
SQL提供了不同的语句,以便在Oracle,MySQL,PostgreSQL等各种数据库服务器中选择现有表,变量,临时表。在本教程中,我们将研究如何在不同情况下使用Select Into
SQL语句。
句法 (Syntax)
Select Into statement syntax is the same as with Select syntax. We just need to a INTO
between SELECT
and FROM
. We can also use WHERE
statement in order to filter rows.
Select Into语句的语法与Select语法相同。 我们只需要在SELECT
和FROM
之间输入INTO
。 我们还可以使用WHERE
语句来过滤行。
SELECT columns INTO newtable [IN externaldb] FROM oldtable WHERE condition;
columns
specifies the columns in the oldtable we want to copy. We can also use*
in order specify all columns.columns
指定我们要复制的旧columns
中的列。 我们还可以使用*
来指定所有列。newtable
is the new table we want to create or copy.newtable
是我们要创建或复制的新表。externaldb
is optional. We can use if we will copy from another or external database.externaldb
是可选的。 我们可以使用是否要从另一个数据库或外部数据库进行复制。oldtable
is table we want to copy from.oldtable
是我们要从中复制的表。condition
can be used if we want to filter or specify the data we want to copy. It is the same fashion with regular WHERE usage.如果我们要过滤或指定要复制的数据,则可以使用
condition
。 它与常规WHERE使用情况相同。
将所有列复制到新表中 (Copy All Columns Into New Table)
We will start with a simple and generic example. We will copy all columns from table named customer
into table names allcustomers
.
我们将从一个简单的通用示例开始。 我们将从名称为customer
表中的所有列复制到名称为allcustomers
表中。
SELECT * INTO allcustomers FROM customers;
将指定的列复制到新表中 (Copy Specified Columns Into New Table)
We can also specify the columns we want to into new table. In this example we will copy columns named id
, name
from table customer
into table named allcustomers
我们还可以指定要添加到新表中的列。 在此示例中,我们将从表customer
复制名为id
, name
列到名为allcustomers
表中
SELECT id,name INTO allcustomers FROM customers;
创建表的备份副本 (Create Backup Copy Of Table)
Another useful scenario of Select Into
statement is creating backup of a table by copying is fully another table. We will create backup of table named customers
with the name of cusotmersbackup
Select Into
语句的另一个有用方案是通过完全复制另一个表来创建表的备份。 我们将创建名称为cusotmersbackup
名为customers
的表的备份
SELECT * INTO customersbackup FROM customers;
从另一个数据库复制表 (Copy Table From Another Database)
The default behavior of Select Into
statement is copy tables in the current database. If we need to copy table into new database we need to use In
statement. In this example we will copy table named customers
into new database newdb
with the table name newcustomers
.
Select Into
语句的默认行为是当前数据库中的副本表。 如果需要将表复制到新数据库中,则需要使用In
语句。 在此示例中,我们将表名为customers
表复制到表名为newdb
新数据库newcustomers
。
SELECT * INTO newcustomers IN 'newdb' FROM customers;
仅将指定的行复制到新表中 (Copy Only Specified Rows Into New Table)
As stated previously we can copy only specified or filtered rows during Select Into
. We will use WHERE
clause with the condition we want to apply. In this example we will copy users with Country
Turkey
from table customers
into new table allcustomers
.
如前所述,在Select Into
期间,我们只能复制指定或过滤的行。 我们将WHERE
子句与要应用的条件一起使用。 在此示例中,我们将“ Turkey
Country
用户从表customers
复制到新表allcustomers
。
SELECT * INTO allcustomers FROM customers WHERE Country='Turkey';
从一个(多个)表复制到新表 (Copy From More Than One (Multiple) Table Into New Table)
We can use JOIN
with the SELECT INTO
. This will provide us to copy from multiple tables into new table. In this example we will copy from tables customers
and deals
into customer_deal
table with LEFT JOIN
on customerid
我们可以将JOIN
与SELECT INTO
。 这将为我们提供从多个表复制到新表的功能。 在这个例子中,我们将表复制customers
和deals
成customer_deal
表LEFT JOIN
的customerid
SELECT customers.name,deals.dealid INTO customer_deal FROM customers
LEFT JOIN deal ON customers.customerid = deals.customerid;
创建新的空表 (Create New Empty Table)
Another useful usage case is creating new empty table with the given table structure and columns. We will use standard syntax and filter wit 5=1
which is always false and do not copy any data from source table. In this example we will create a new table allcustomer
from table customers
.
另一个有用的用例是使用给定的表结构和列创建新的空表。 我们将使用标准语法并使用5=1
筛选器,该筛选器始终为false,并且不会从源表中复制任何数据。 在本示例中,我们将从表customers
创建一个新表allcustomer
。
SELECT * INTO allcustomers FROM customers WHERE 5=1;
翻译自: https://www.poftut.com/sql-select-into-statement-usage-tutorial/
sql语句 sum 用法