Teradata 简明教程
Teradata - Data Manipulation
本章介绍用于处理存储在 Teradata 表中的数据的 SQL 命令。
This chapter introduces the SQL commands used to manipulate the data stored in Teradata tables.
Insert Records
INSERT INTO 语句用于向表中插入记录。
INSERT INTO statement is used to insert records into the table.
Syntax
以下是 INSERT INTO 的通用语法。
Following is the generic syntax for INSERT INTO.
INSERT INTO <tablename>
(column1, column2, column3,…)
VALUES
(value1, value2, value3 …);Example
以下示例向 employee 表中插入记录。
The following example inserts records into the employee table.
INSERT INTO Employee (
EmployeeNo,
FirstName,
LastName,
BirthDate,
JoinedDate,
DepartmentNo
)
VALUES (
101,
'Mike',
'James',
'1980-01-05',
'2005-03-27',
01
);插入上述查询后，可以使用 SELECT 语句从表中查看记录。
Once the above query is inserted, you can use the SELECT statement to view the records from the table.
EmployeeNo |
FirstName |
LastName |
JoinedDate |
DepartmentNo |
BirthDate |
101 |
Mike |
James |
3/27/2005 |
1 |
1/5/1980 |
Insert from Another Table
INSERT SELECT 语句用于从另一个表中插入记录。
INSERT SELECT statement is used to insert records from another table.
Syntax
以下是 INSERT INTO 的通用语法。
Following is the generic syntax for INSERT INTO.
INSERT INTO <tablename>
(column1, column2, column3,…)
SELECT
column1, column2, column3…
FROM
<source table>;Example
以下示例向 employee 表中插入记录。在运行以下插入查询之前，创建一个名为 Employee_Bkup 的表，其列定义与 employee 表相同。
The following example inserts records into the employee table. Create a table called Employee_Bkup with the same column definition as employee table before running the following insert query.
INSERT INTO Employee_Bkup (
EmployeeNo,
FirstName,
LastName,
BirthDate,
JoinedDate,
DepartmentNo
)
SELECT
EmployeeNo,
FirstName,
LastName,
BirthDate,
JoinedDate,
DepartmentNo
FROM
Employee;执行上述查询时，将把 employee 表中的所有记录插入到 employee_bkup 表中。
When the above query is executed, it will insert all records from the employee table into employee_bkup table.
Rules
-
The number of columns specified in the VALUES list should match with the columns specified in the INSERT INTO clause.
-
Values are mandatory for NOT NULL columns.
-
If no values are specified, then NULL is inserted for nullable fields.
-
The data types of columns specified in the VALUES clause should be compatible with the data types of columns in the INSERT clause.
Update Records
UPDATE 语句用于更新表中的记录。
UPDATE statement is used to update records from the table.
Syntax
以下是 UPDATE 的通用语法。
Following is the generic syntax for UPDATE.
UPDATE <tablename>
SET <columnnamme> = <new value>
[WHERE condition];Example
下面的示例将雇员 101 的员工部门更新为 03。
The following example updates the employee dept to 03 for employee 101.
UPDATE Employee
SET DepartmentNo = 03
WHERE EmployeeNo = 101;在下面的输出中，您可以看到雇员编号为 101 的部门编号从 1 更新为 3。
In the following output, you can see that the DepartmentNo is updated from 1 to 3 for EmployeeNo 101.
SELECT Employeeno, DepartmentNo FROM Employee;
*** Query completed. One row found. 2 columns returned.
*** Total elapsed time was 1 second.
EmployeeNo DepartmentNo
----------- -------------
101 3Delete Records
DELETE FROM 语句用于更新表中的记录。
DELETE FROM statement is used to update records from the table.
Syntax
以下是 DELETE FROM 的一般语法。
Following is the generic syntax for DELETE FROM.
DELETE FROM <tablename>
[WHERE condition];Example
下面的示例从表 employee 中删除雇员 101。
The following example deletes the employee 101 from the table employee.
DELETE FROM Employee
WHERE EmployeeNo = 101;在下面的输出中，您可以看到雇员 101 已从表中删除。
In the following output, you can see that employee 101 is deleted from the table.
SELECT EmployeeNo FROM Employee;
*** Query completed. No rows found.
*** Total elapsed time was 1 second.