SQL query beginner's Guide Reading Notes (3) value expression introduction, beginner's Guide Reading Notes

SQL query beginner's Guide Reading Notes (3) value expression introduction, beginner's Guide Reading Notes

CHAPTER 5 Getting 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 doesn't that it drawsits charactersfrom ISO-definedforeign language character sets.

 

The BOOLEAN data type can be stored using TINYINT.

 

CAST

Changing Data Types

 

Data_type needs to view the specific database implementation documentation, such as MySQL

TheTypeFor the result can be one of the following values:

• BINARY [(N)]

• CHAR [(N)]

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

 

Source Document

 

For example

SELECTOrderNumber, OrderDate, ShipDate,

CAST (ShipDate-OrderDate as integer)

ASDaysElapsed

FROMOrders

 

Convert

 

SELECTOrderNumber, OrderDate, ShipDate,

CAST (ShipDate-OrderDate as decimal)

ASDaysElapsed

FROMOrders

 

Literal Value

It can be divided into String constant values, numerical constant values, and date constant values.

 

If you use single quotes to include strings, and strings contain single quotes, use single quotes twice to distinguish them from single quotes.

SQL 'the Vendor "s name is :'

Displayed as The Vendor's name is:

 

Types of Expressions

 

 

CONCATENATION

SQL String concatenation

 

Notice 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

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

Appropriateoperator for each database type (Microsoft Access, Microsoft

SQLServer, and MySQL ).

 

We can see that SQL standard String concatenation is used |, but in MySQL, it is used as a logical operator. MySQL concatenates strings using concat built-in functions. Some databases use intuitive + String concatenation strings.

 

DATE AND TIME ARITHMETIC

MySQL needs to convert the date constant value to the corresponding type for calculation. In addition, it needs to use the corresponding date function for calculation.

CAST ('2017-11-22 'as date)

CAST ('03: 30: 25' as time)

CAST ('2017-09-2008: 25: 00' as datetime)

 

For example

SELECTStfLastName | ',' | StfFirstName AS Staff,

DateHired,

CAST ('2017-10-01 '-DateHired as integer)

/365 as integer)

ASYearsWithSchool

FROMStaff

ORDERBY StfLastName, StfFirstName

 

In MySQL

 

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

DateHired,

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

ASYearsWithSchool

FROMStaff

ORDERBY StfLastName, StfFirstName

 

 

That "Nothing" Value: Null

Do not use ValueExpression = NULL when determining whether Value Expression is NULL. This is a small mistake.