MySQL Tutorials - Herong's Tutorial Examples
∟Datatypes and Data Literals
∟Introduction of Datatype
This section provides quick introductions of datatypes supported by SQL: Character String, Bit String, Exact Number, Approximate Number, Date and Time.
Like all other computer languages, SQL deals with data. So let's first look at how
SQL defines data.
Datatype: A group of data that shares some common characteristics and operations.
SQL defines the following datatypes:
- Character String - A sequence of characters from a predefined character set.
- Bit String - A sequence of bit values: 0 or 1.
- Exact Number - A numeric value who's precision and scale need to be preserved.
Precision and scale can be counted at decimal level or binary level. The decimal
precision of a numerical value is the total number of significant digits in decimal
form. The decimal scale of a numerical value is the number of fractional digits in
decimal form. For example, the number 123.45 has a precision of 5 and a scale of 2.
The number 0.012345 has a precision of 6 and a scale of 6.
- Approximate Number - A numeric value who's precision needs to be preserved, and
scale floated to its exponent. An approximate number is always expressed in scientific
notation of "mantissa"E"exponent". Note that an approximate number has two precisions:
mantissa precision and exponent precision. For example, the number 0.12345e1 has
a mantissa precision of 5 and exponent precision of 1.
- Date and Time - A value to represent an instance of time. A date and time value
can be divided into many portions and related them to a predefined calendar system
as year, month, day, hour, minute, second, second fraction, and time zone. A date
and time value also has a precision, which controls the number of digits of the
second fraction portion. For example: 1999-1-1 1:1:1.001 has precision of 3 on the
second fraction portion.
- JSON - A value to represent JSON (JavaScript Object Notation) document defined
by RFC 7159 at tools.ietf.org/html/rfc7159.
JSON Data type was introduced since MySQL 5.7.
- Spatial Data - A value to represent a Geographic element defined in
"OpenGIS Implementation Specification for Geographic information - Simple feature access - Part 2: SQL
option" at opengeospatial.org/standards/sfs.
Spatial Data type was introduced since MySQL 5.7.
Table of Contents
About This Book
Introduction of SQL
MySQL Introduction and Installation
Introduction of MySQL Programs
PHP Programs and MySQL Server
Perl Programs and MySQL Servers
Java Programs and MySQL Servers
►Datatypes and Data Literals
►Introduction of Datatype
Data Binary Representations
Data Literals
Data Literal Evaluation
Character String Literal Evaluation Examples
Hex String Literal Evaluation Examples
Numeric 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
Window Functions for Statistical Analysis
Use Index for Better Performance
Transaction Management and Isolation Levels
Locks Used in MySQL
Defining and Calling Stored Procedures
Variables, Loops and Cursors Used in Stored Procedures
System, User-Defined and Stored Procedure Variables
MySQL Server Administration
Storage Engines in MySQL Server
InnoDB Storage Engine - Primary and Secondary Indexes
Performance Tuning and Optimization
Bulk Changes on Large Tables
MySQL Server on macOS
Installing MySQL Server on Linux
Connection, Performance and Second Instance on Linux
Archived Tutorials
References
Full Version in PDF/EPUB