SQL查询刚開始学习的人指南读书笔记(三)值表达式

CHAPTER 5 Getting More Than Simple Columns

Intro Value  expression,it contains column names, literal values, functions, or other value

expressions

 

The type of data

The SQL Standard defines seven general categories of types of data—character,

national character, binary, numeric, Boolean, datetime, and interval.

 

The national character data type is the same as the character data type except that it draws its characters from ISO-defined foreign 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>

 

比方

SELECT OrderNumber, OrderDate, ShipDate,

CAST(ShipDate– OrderDate AS INTEGER)

AS DaysElapsed

FROM Orders

 

在MySQL中须要转为

 

SELECT OrderNumber, OrderDate, ShipDate,

CAST(ShipDate- OrderDate AS DECIMAL)

AS DaysElapsed

FROM Orders

 

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 only IBM’s DB2 and

Informix and Oracle’s Oracle support the SQL Standard operator for concatenation.

Microsoft Office Access supports & and + as concatenation

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

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

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

appropriate operator 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)

 

比方

SELECT StfLastName || ', ' || StfFirstName AS Staff,

DateHired,

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

/365 AS INTEGER)

AS YearsWithSchool

FROM Staff

ORDER BY StfLastName, StfFirstName

 

在MySQL中转为

 

SELECT CONCAT(StfLastName , ', ' , StfFirstName) AS Staff,

DateHired,

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

AS YearsWithSchool

FROM Staff

ORDER BY StfLastName, StfFirstName

 

 

That"Nothing" Value:Null

推断Value Expression是否为NULL的时候请不要使用ValueExpression = NULL,这是常犯的小错误.