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:
*/

HELP TABLE [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:
*/
HELP COLUMN [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:
*/
SHOW table [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 *
FROM strinfo
/***************升序***************/
SELECT TOP 10 *
FROM strinfo
ORDER BY city ASC
/**************降序****************/
SELECT TOP 10 *
FROM strinfo
ORDER 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 *
FROM strinfo
SAMPLE 10
/*
The following query would retrieve a random 10% of the rows from the strinfo table:
*/
SELECT *
FROM strinfo
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)
*/