**MySQL Tutorials - Herong's Tutorial Examples** - v4.46, by Herong Yang

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

MySQL Introduction and Installation

Introduction of MySQL Programs

Perl Programs and MySQL Servers

Java Programs and MySQL Servers

Character String Literal Evaluation Examples

Hex String Literal Evaluation Examples

Numeric Literal Evaluation Examples

Character Strings and Bit Strings

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

Defining and Calling Stored Procedures

Variables, Loops and Cursors Used in Stored Procedures

System, User-Defined and Stored Procedure Variables

Storage Engines in MySQL Server

InnoDB Storage Engine - Primary and Secondary Indexes

Performance Tuning and Optimization

Installing MySQL Server on Linux