Reading:  

Beginners guide to MySQL and MariaDB


Key Data types

MySQL support different data types which is divided into three categories: string, date and time and numeric types. Data type descriptions has below conventions:

  • M indicates maximum display width for integer types. For floating-point and fixed-point types, M is total number of digits that can be stored (the precision). For string types, M is maximum length. The maximum permissible value of M depends on data type.
  • D applies to floating-point and fixed-point types and indicates number of digits following the decimal point (the scale). The maximum possible value is 30, but should be no greater than M–2.
  • Square brackets (“[” and “]”) indicate optional parts of type definitions.

String Types:

The common string datatypes in MySQL:

  • CHAR(M) - A fixed-length string between 1 and 255 characters in length. Defining a length is not required, but default is 1.
  • VARCHAR(M) - A variable-length string between 1 and 255 characters in length. You must define a length when creating a VARCHAR field.
  • BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are "Binary Large Objects" and are used to store large amounts of binary data, such as images or other types of files
  • TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of 255 characters.
  • MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length of 16777215 characters.
  • LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of 4294967295 characters.
  • ENUM - An ENUM is a string object with a value chosen from a list of permitted values that are enumerated explicitly in the column specification at table creation time.

Date and Time Types:

The common date and time datatypes in MySQL:

  • DATE - A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31.
  • DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format, between 1000-01-01 00:00:00 and 9999-12-31 23:59:59.
  • TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037.
  • TIME - Stores the time in HH:MM:SS format.
  • YEAR(M) - Stores a year in 2-digit or 4-digit format. If length is specified as 4, YEAR can be 1901 to 2155. The default length is 4.

Numeric Data Types:

The common numeric datatypes in MySQL:

  • INT - A normal-sized integer can be signed or unsigned. If signed, allowable range is from -2147483648 to 2147483647. If unsigned, allowable range is from 0 to 4294967295. A width of up to 11 digits.
  • TINYINT - A very small integer can be signed or unsigned. If signed, allowable range is from -128 to 127. If unsigned, the allowable range is from 0 to 255. A width of up to 4 digits.
  • SMALLINT - A small integer can be signed or unsigned. If signed, allowable range is from -32768 to 32767. If unsigned, allowable range is from 0 to 65535. A width of up to 5 digits.
  • MEDIUMINT - A medium-sized integer can be signed or unsigned. If signed, allowable range is from -8388608 to 8388607. If unsigned, allowable range is from 0 to 16777215. A width of up to 9 digits.
  • BIGINT - A large integer that can be signed or unsigned. If signed, allowable range is from -9223372036854775808 to 9223372036854775807. If unsigned, allowable range is from 0 to 18446744073709551615. A width of up to 20 digits.
  • FLOAT(M,D) - A floating-point number that cannot be unsigned. This is not required and will default to 10,2, where 2 is number of decimals and 10 is total number of digits (including decimals).
  • DOUBLE(M,D) - A double precision floating-point number that cannot be unsigned. You can define display length (M) and number of decimals (D). This is not required and will default to 16,4, where 4 is the number of decimals.
  • DECIMAL(M,D) - An unpacked floating-point number that cannot be unsigned. In unpacked decimals, each decimal corresponds to one byte.

Description

In this tutorial, we will cover few topics that will give you a heads on start to build your knowledge on. Topics that we will cover briefly but still providing enough information are listed below

  • Overview
  • Installing on Linux and Windows
  • Some useful admin queries for starters
  • Connection
  • Create Database
  • Drop Database
  • Select Database
  • Data Type
  • Create Table
  • Drop Table
  • Inserting and Selecting data
  • Where Clause
  • Updating and deleting data
  • Like Clause
  • Sorting Result
  • Using Joins
  • Brief introduction to Regex, Transactions and Indexes
  • Alter Command
  • Temporary Tables
  • Database Info
  • Using Sequence
  • Database Export and Import
  • Resetting MySQL/MariaDB Administrator password


Audience

Absolute beginners looking to get a sneak peak into what MySQL. Please remember that this is not a full on guide but a quick introduction to the subject.

Learning Objectives

Get to know MySQL and MariaDB

Author: Subject Coach
Added on: 23rd Jun 2015

You must be logged in as Student to ask a Question.

None just yet!