MySQL Tutorials - Herong's Tutorial Examples

∟Datatypes and Data Literals

∟Data Literal Evaluation Examples

Example 1 - CharStringLiterals.sql:

-- CharStringLiterals.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT 'Hello world!' AS LINE_1;
SELECT 'Loews L''Enfant Plaza' AS LINE_2;
SELECT '123'+ 0 AS LINE_3;
SELECT '0.123e-1' + 0 AS LINE_4;
SELECT 'ABC' + 1 AS LINE_5;

Output on MySQL server:

LINE_1
Hello world!
LINE_2
Loews L'Enfant Plaza
LINE_3
123
LINE_4
0.0123
LINE_5
1

Notes:

• Line 2 shows that "''" is evaluated as "'" in a string expression.
• Line 4 confirms that a character string literal can be evaluated to an approximate number in a numeric expression.
• Line 5 confirms that a character string literal will be evaluated to 0, if the string is an invalid numeric literal.

Example 2 - HexStringLiterals.sql:

-- HexStringLiterals.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT x'41424344' AS LINE_1;
SELECT x'31323334' AS LINE_2;
SELECT x'31323334' + 0 AS LINE_3;
SELECT x'01' + 0 AS LINE_4;
SELECT x'0001' + 0 AS LINE_5;
SELECT x'ff' + 0 AS LINE_6;
SELECT x'ffffffff' + 0 AS LINE_7;
SELECT x'ffffffffffffffff' + 0 AS LINE_8;

Output

LINE_1
ABCD
LINE_2
1234
LINE_3
825373492
LINE_4
1
LINE_5
1
LINE_6
255
LINE_7
4294967295
LINE_8
-1

Notes:

• Line 1 and 2 tell us that hex string literals are evaluated into character strings in a string expression.
• Line 3, 4, 5, 6 and 7 tell us that hex string literals are evaluated into numbers in numeric expression.
• Line 8 tells us that the binary representation of an integer is 8 bytes. The value of the first bit on the left marks the integer to be negative.

Example 3 - NumericLiterals.sql:

-- NumericLiterals.sql
-- Copyright (c) 1999 by Dr. Herong Yang
--
SELECT 1 AS LINE_1;
SELECT -2 AS LINE_2;
SELECT 3.3 AS LINE_3;
SELECT -4.4e+4 AS LINE_4;
SELECT 12345678901234567890 AS LINE_5;
SELECT 0.12345678901234567890 AS LINE_6;
SELECT 1234567890.1234567890 AS LINE_7;
SELECT 12345678901234567890.1234567890 AS LINE_8;
SELECT 1234567890.1234567890e+10 AS LINE_9;
SELECT 0.0000000000000000000012345678901234567890 AS LINE_10;
SELECT 1.0e+1234567890 AS LINE_11;

Output:

LINE_1
1
LINE_2
-2
LINE_3
3.3
LINE_4
-44000
LINE_5
123456789012345680000000000000
LINE_6
0.12345678901234568000
LINE_7
1234567890.1234567000
LINE_8
12345678901234567000.0000000000
LINE_9
1.2345678901235e+019
LINE_10
1.2345678901235e-021
LINE_11
1.#INF

A number of interesting notes here:

• Line 4 tells us that an approximate numeric literal could be evaluated into an integer.
• Line 5, 6, 7 and 8 tell us that exact numeric literals are evaluated upto about 17 digits.
• Line 9 tells us that an approximate numeric literal is evaluated upto about 15 digits.
• Line 10 tells us that an exact numeric literal could be converted into an proximate numeric value.
• Line 11 tells us that if the internal storage value limit is reached, an approximate numeric literal is evaluated to "1.#INF".

Table of Contents

 About This Book

 Introduction of SQL

 MySQL 4.0 Introduction and Installation

 Installing MySQL 5.5.15

 Installing MySQL 5.0.2 (Alpha)

 Introduction of MySQL 5.0 Programs

 Perl Programs and MySQL Servers

 PHP Programs and MySQL Servers

 Java Programs and MySQL Servers

►Datatypes and Data Literals

 Introduction of Datatype

 Data Binary Representations

 Data Literals

 Data Literal Evaluation

►Data Literal Evaluation Examples

 Operations and Expressions

 Character Strings and Bit Strings

 Commonly Used Functions

 Table Column Types for Different Types of Values

 Using DDL to Create Tables and Indexes

 Using DML to Insert, Update and Delete Records

 Using SELECT to Query Database

 Transaction Management and Isolation Levels

 Locks-Used-in-MySQL

 Defining and Calling Stored Procedures

 Variables, Loops and Cursors Used in Stored Procedures

 References

 Printable Copy - PDF Version