源文档
比如
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需要将日期常量值转为相应类型再做计算,另外需要使用相应的日期函数计算
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,这是常犯的小错误.