SQL查詢初學者指南讀書筆記(三)值運算式介紹，初學者指南讀書筆記

SQL查詢初學者指南讀書筆記(三)值運算式介紹，初學者指南讀書筆記

CHAPTER 5Getting More Than Simple Columns

Intro Value  expression,itcontains column names, literal values, functions, orother value

expressions

 

The type of data

TheSQL Standard defines seven general categories of types of data—character,

nationalcharacter, binary, numeric, Boolean, datetime, and interval.

 

The national character data type is the same as thecharacter data type except that it drawsits charactersfrom ISO-definedforeign language character sets.

 

BOOLEAN資料類型可以使用TINYINT儲存.

 

CAST

Changing Data Types

 

data_type需要查看具體資料庫實現文檔,比如MySQL

The type for the result can be one of the following values:

• BINARY[(N)]

• CHAR[(N)]

• DATE
• DATETIME
• DECIMAL[(M[,D])]
• SIGNED [INTEGER]
• TIME
• UNSIGNED [INTEGER]

 

來源文件 <http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html#function_cast>

 

比如

SELECTOrderNumber, OrderDate, ShipDate,

CAST(ShipDate– OrderDate AS INTEGER)

ASDaysElapsed

FROMOrders

 

在MySQL中需要轉為

 

SELECTOrderNumber, OrderDate, ShipDate,

CAST(ShipDate- OrderDate AS DECIMAL)

ASDaysElapsed

FROMOrders

 

Literal Value

分為字串常量值,數值常量值和日期常量值.

 

如果使用單引號包含字串,字串中包含單引號,字串中的單引號請使用兩次以示與引用的單引號區別開來.

SQL'The Vendor"s name is: '

Displayed as The Vendor's name is:

 

Types of Expressions

 

 

CONCATENATION

SQL字串串接

 

❖ Note Of the major database systems,we found that onlyIBM’s DB2 and

Informixand Oracle’s Oracle support the SQL Standard operator for concatenation.

MicrosoftOffice Access supports & and + as concatenation

operators,Microsoft SQL Server and Ingres support +, and in MySQL you

mustuse the CONCAT function. In all the examples in the book,we use the

SQLStandard || operator. In the sample databases on the CD,we use the

appropriateoperator for each database type (Microsoft Access, Microsoft

SQLServer, and MySQL).

 

由可知SQL標準字串串接用||，但是在MySQL使用會被當作邏輯運算子。MySQL使用concat內建函數串接字串。而有的資料庫使用直觀的+串接字串.

 

DATE AND TIME ARITHMETIC

MySQL需要將日期常量值轉為相應類型再做計算,另外需要使用相應的日期Function Compute

CAST('2016-11-22'AS DATE)

CAST('03:30:25'AS TIME)

CAST('2008-09-2914:25:00' AS DATETIME)

 

比如

SELECTStfLastName || ', ' || StfFirstName AS Staff,

DateHired,

CAST(CAST('2007-10-01'- DateHired AS INTEGER)

/365 AS INTEGER)

ASYearsWithSchool

FROMStaff

ORDERBY StfLastName, StfFirstName

 

在MySQL中轉為

 

SELECTCONCAT(StfLastName , ', ' , StfFirstName) AS Staff,

DateHired,

CAST(DATEDIFF(CAST('1990-01-11'AS date) , DateHired )/365 as decimal)

ASYearsWithSchool

FROMStaff

ORDERBY StfLastName, StfFirstName

 

 

That"Nothing" Value:Null

判斷Value Expression是否為NULL的時候請不要使用ValueExpression = NULL,這是常犯的小錯誤.