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需要将日期常量值转为相应类型再做计算,另外需要使用相应的日期函数计算

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,这是常犯的小错误.