teradata ttu_SQL语句笔记之Teradata

参考来源：

http://www.info.teradata.com/htmlpubs/db_ttu_13_10/index.html#page/SQL_Reference/B035_1

146_109A/ch01.3.095.html

http://www.teradatawiki.net/2013/10/Teradata-SAMPLE-Function.html

1.熟悉数据库中的数据

/*You use different commands to do this in Teradata than

you use in MySQL. Instead of using SHOW or DESCRIBE to get a list of columns in a table,use:*/HELPTABLE [name of table goes here; don’t include the

brackets when executing the query]

/*To get information about a single column in a table, you could write:*/HELPCOLUMN [name of column goes here; don’t include the

brackets when executing the query]

/*One thing that is missing from the information outputted by HELP is whether or not a

column is a primary or foreign key. In order to get that information, use a SHOW command:*/SHOWtable [insert name of table here; don’t include the

brackets when executing the query];

2. TOP用法代替MYSQL中的LIMIT

/*The following statement

would select the first 10 rows of the strinfo table as they are stored in the native database:*/

SELECT TOP 10 *

FROMstrinfo/***************升序***************/

SELECT TOP 10 *

FROMstrinfoORDER BY city ASC

/**************降序****************/

SELECT TOP 10 *

FROMstrinfoORDER BY city DESC

3. SMAPLE用法

/*In

addition, there is a function available in Teradata (but not MySQL) called SAMPLE that allows

you to select a random sampling of the data in a table:*/

/*The following query would retrieve 10 random rows from the strinfo table:*/

SELECT *

FROMstrinfo

SAMPLE10

/*The following query would retrieve a random 10% of the rows from the strinfo table:*/

SELECT *

FROMstrinfo

SAMPLE .10

3.MYSQL和Teradata之间的不同 。

/*1. DISTINCT and LIMIT can be used in the same query statement in MySQL, but

DISTINCT and TOP cannot be used together in Teradata

2. MySQL accepts either double or single quotation marks around strings of text in queries,

but Teradata will only accept single quotation marks

3. MySQL will accept the symbols “!=” and “<>” to indicate “does not equal” but Teradata

will only accept “<>” (other operators, like “IN”, “BETWEEN”, and “LIKE” are the

same: http://www.teradatawiki.net/2013/09/Teradata-Operators.html)*/