MySQL Tutorial

MySQL Tutorial MySQL Features MySQL Database Introduction MySQL Environmental Setup MySQL Data Types MySQL variable MySQL Advance table Query MySQL database queries MySQL Entity-Relationship Model MySQL Table Query MySQL Operators MySQL logical conditions MySQL Queries MySQL Clauses Clustered vs Non-Clustered Index MySQL Full text index MySQL Descending Index MySQL Invisible Index MySQL Composite Index MySQL Prefix index MySQL Index MySQL Create index MySQL Drop Index MySQL Show index MySQL Unique index MySQL Table MySQL Variable MySQL View MySQL Constraints MySQL Command Line Client Basic Queries MySQL Stored Procedure MySQL IF Statement MySQL Subquery MySQL Triggers

MySQL Join

MySQL Join MySQL CROSS JOIN MySQL DELETE JOIN MySQL EQUI JOIN MySQL INNER JOIN MySQL Union MySQL NATURAL JOIN MySQL RIGHT JOIN MySQL SELF JOIN MySQL UPDATE JOIN

MySQL Function

MySQL Function MySQL AVG() Function MySQL SUM() Function MySQL String() Function MySQL Advance() Function MySQL Aggregate() Function MySQL COALESCE() Function MySQL Control Flow Function MySQL COUNT() Function MySQL Date And Time Function MySQL GREATEST() Function MySQL ISNULL() Function MySQL LEAST() Function MySQL Math() Function MySQL MAX() Function MySQL MIN() Function MySQL find_in_set() function MySQL ASIN() Function MySQL CEIL() function MySQL CEILING() function MySQL TAN() Function MySQL Truncate() Function MySQL FLOOR() function MySQL LN() function MySQL LOG2() function MySQL LOG10() function MySQL MOD() function MySQL PI() function MySQL POW() function MySQL RADIANS() function MySQL RAND() function MySQL ROUND() function MySQL Character Length Function MySQL Current Date Function MySQL Date Add Function MySQL Date Format Function MySQL Datediff Function MySQL Day Function MySQL Elt Function MySQL Export Set Function MySQL Field Function MySQL Format Function MySQL From Base64 Function MySQL Hex Function MySQL Insert Function MySQL Instr Function MySQL Length Function MySQL CONCAT() function MySQL FIND_IN_SET() function MySQL LIKE() function MySQL LOAD_FILE() function MySQL LOCATE() function MySQL LOG() function MySQL MONTHNAME() function MySQL NOW() function MySQL PERIOD_ADD() function MySQL PERIOD_DIFF() function MySQL POWER() function MySQL QUARTER() function MySQL REVERSE() function MySQL RIGHT() Function MySQL RPAD() function MySQL RTRIM() function MySQL SEC_TO_TIME() function MySQL SOUNDEX() function

Questions

Which Clause is Similar to Having Clause in MySQL

Misc

MySQL Error 1046 - No Database Selected Failed to Start MySQL Service Unit MySQL Service Unit not Found Import MySQL Connector Mudule not Found Error No Module Named MySQL Joins Available in MySQL MySQL Docs MySQL Download For Windows 7 64 Bit MySQL Error Code 1064 MySQL Export MySQL History MySQL Host MySQL Import MySQL Drop All Tables MySQL Drop MySQL Error Code 1175 MySQL Events MySQL Except MYSQL Foreign Key Constraint MySQL If Exists MySQL IndexOf MySQL List All Tables json_extract in MySQL

MySQL Data Types

Introduction of the Data types

In the data file, we have several information such as names, numbers, marks, and other information. This information specifies categories such as numbers, characters, and decimal values. The number shows numerical data type, and the text shows string data type. MySQL data type identifies the data category such as integer, string, float, and other.

MySQL data type mostly uses numbers, dates, images, files, and text information in the table. MySQL creates a column in the database table. Each column contains a specific type of information. The data assign MySQL data type with required size.

MySQL data type includes various specifications to operate the data of a web application.

  • The data type stores value with categorization in the data table.
    • The data type decides if the value is an index in the table or not.
    • MySQL data type allows storing specific memory size information.
    • The data type provides either constant size or customizable size data.
MySQL Data Types

MySQL assists SQL data types in many formats. Following is the data type category, and its description is given below.

  1. Numeric data type: This data type specifies a numerical value and number.
  2. String data type: This data type specifies a character, binary data, enum, and text.
  3. Date and time data type: This data type allows data, time, and year.
  4.  Spatial data type: It is allowed to geographical data and geometrical information.
  5.  JSON data type: This data type stores the JSON file.
  6. Boolean data type:  MySQL database does not carry a Boolean value. The database uses a small integer data type [TINYINT(1)] as a Boolean data type. It holds the data in a similar way as that of numerical data types.

Syntax

MySQL assigns any data type with a column in the interfaces. Most data type is required to create table and alter table. You modify and operate table data with the help of respective data types. The basic syntax of MySQL data type is given below.

Column_name data_type (size)

Mostly, the data type is used to create a new table. The data type syntax is displayed below.

 CREATE TABLE table_name (
 column_name DATA_TYPE, column_name  DATA_TYPE (size)
 ); 

Here each column assigns the required data type. The table uses either similar data types or mixed data types to stores several data.

Numerical Data types

The numerical data type is used to store the numerical, Boolean value. It works for arithmetic operations and decides Boolean conditions. The following categories are classified as per data size and data format.

Numerical  Data TypesFunction and description
INTThe signed integer value limit is -2147483648 to 2147483647. The unsigned integer value range is from 0 to 4294967295. The range of integer data types is up to 11 digits.
MEDIUMINTThe signed medium integer value limit is between -8388608 and 8388607. The unsigned medium integer value limit is 0 to 16777215. The range of medium integer data types is up to 9 digits.
BIGINTThe signed significant integer value is between -9223372036854775808 and 9223372036854775807.  The unsigned extensive integer value range is between 0 and 18446744073709551615. The range of large integer data types is up to 20 digits.
SMALLINTThe signed small integer value range is between -32768 and 32767. The unsigned small integer value range is 0 to 65535. The range of small integer data types is equal to 5 digits.
TINYINTThe signed tiny integer value range is between -128 and 127. The unsigned tiny integer value limit is between 0 and 255. The range of small integer data types is equal to 4 digits.
FLOATThis data type stores precision floating-point value. This data type does not carry unsigned values. The float requires display length and the number of decimals.
DOUBLEThe data type stores double-precision and floating-point numbers. The double data type does not carry unsigned values. The double data type requires display length and the number of decimals.
DECIMALThis data type stores fixed floating-point values. The float requires display length and the number of decimals. It does not support unsigned value.
BITThis data type stores bit values. The bit value range is from 1 to 64.

The following example displays numerical data type working procedures.

Examples of numerical data type

1)  Example: The numerical data type's example is shown below.

Create table with column and its data type. Execute below query.

 mysql> create table datatypes(
     ->     number INT AUTO_INCREMENT PRIMARY KEY,
     ->     name VARCHAR(255),
     ->     mark FLOAT(5, 2),
     ->     mobile BIGINT); 

Query OK, 0 rows affected, 1 warning (1.16 sec)

Output

Execute the query given below to get output.

mysql> describe datatypes;
MySQL Data Types

You will get a table with four columns. The four columns store information of different data types.

2)  Example: Insert a value of string data types.

See the int, float, bigint data type's value in the table. Execute the below query.

 mysql> insert into datatypes(number, name, mark, mobile)
     -> values(1, 'mysql', 83.38, 1098234567); 

Query OK, 1 row affected (0.24 sec)

Output

Execute query given below to get output.

mysql> select * from datatypes;
MySQL Data Types

You will get the table with four columns and its value. The four columns store different types of information. You can store numbers, decimal numbers, and large-size numerical values as per data requirements.

String Data Types

 The string data type stores characters, text, large text, binary data (images), and enum.  

String  Data TypesFunction and description
CHARThis data type stores non-binary characters or strings. A stable length string supports the data type. The CHAR does not need to define the size of the string. This data type length is from 1 to 255 characters.
VARCHAR(Size)This data type stores non-binary characters or strings. The data type supports variable-length string. The VARCHAR(25) defines the size of the string. This data type length is from 1 to 255 characters.
BINARYThe data type stores binary characters or strings. The data type supports fixed-length string. This data type length is from 1 to 255 characters.
VARBINARYThis data type stores binary characters or strings. The data type supports variable-length string. It defines the size of the string.
TEXTThis data type stores non-binary string or text. It supports 64KB data, such as 65 535 characters.
TINYTEXTThis data type stores non-binary small text or messages. It supports 255 Bytes data, such as 255 characters.
MEDIUMTEXTThe data type stores non-binary medium text. It supports 16MB data, such as 16,777,215 characters.
LONGTEXTThe data type stores non-binary long text or message. It supports 4 GB data such as 4,294,967,295 characters.
BLOBThe data type stores small binary files and images. It supports 64 KB data.
MEDIUMBLOBThis data type stores binary medium size files and images. It supports 16 MB of data.
LONGBLOBThe data type stores binary long-size files and images. It supports 4 GB of data.
TINYBLOBThe data type stores binary smallest size files and images. It supports 255 Bytes data.
ENUMThis data type stores a stable list of values. Enum data type either null or store value. The data type is set in all columns of the table.

The following example displays the working procedure of the string data type.

 Examples of string data type

1)  Example: The string data type's example shows below.

Create table with column and its data type. Execute below query.

 mysql> create table datatypes(
     -> number INT NOT NULL PRIMARY KEY,
     -> name VARCHAR(45) NOT NULL,
     -> password BINARY(20),
     -> COMMENT TINYTEXT ); 

Query OK, 0 rows affected, 1 warning (1.16 sec)

Output

Execute the below query to get output.

mysql> describe datatypes;
MySQL Data Types

You will get a table with four columns. The four columns store information of different data types.

2)  Example: Insert a value of string data types.

See the varchar, binary, tinytext data type's value in the table. Execute below query.

 mysql> insert into datatypes(number, name, password, COMMENT)
 values(1, 'mysql', '1010',' good to learn'); 

Query OK, 1 row affected (0.24 sec)

Output

Execute below query to get output.

mysql> select * from datatypes;
MySQL Data Types

You will get the table with four columns and its value. The four columns store different types of information. You can store text, binary value, characters, or string using string data type.

Date and Time Datatypes

This data type stores value of date and time. The combination of data and time is displayed using MySQL data types. You can see below the MySQL date and time data types with its function.

Data and Time  Data TypesFunction and description
DATEThe "date" data type stores and displays only data. It stores the date in the year – month – day format. Syntax: yyyy – mm – dd Example: 2021 – 0 3 – 15
DATETIMEThe "datetime" data type stores and displays date and time together. It stores the date in the following format. Year (YYYY) – month (MM) – date(DD)  hour (hh) : minute (mm) : second (ss) Example: 2021 – 03 – 15 8:30:45  
TIMEThe "time" data type stores and displays time. It stores the time in hour (hh) : minute (mm) : second (ss) format. Example: 8:30:45
TIMESTAMPThe "timestamp" data type stores date and time together without using a hyphen. It stores in the following format. Year (YYYY) month (MM) date(DD) hour (hh) minute (mm) second (ss) Example: 2021031583045
YEARThe "year" data type stores and displays only years. This data type stores the year in (YYYY) or (YY) format. Example: 2021 or 21

The date and time data types example is shown below.

Examples of date and time data type

1)  Example: Create a table with a column and its data type. Execute the below query.

mysql> create table datatypes (time TIME, date DATE, year YEAR, dateandtime DATETIME);

Query OK, 0 rows affected (0.47 sec)

Output

Execute the below query to get output.

mysql> describe datatypes;
MySQL Data Types

You will get a table with four columns. The four columns store the information of the date, time, and year.

2)  Example: Insert a value of date and time data types.

See the time, date, year, and datetime data type's value in the table. Execute the below query.

mysql> insert into datatypes (time, date, year, dateandtime) values ('07:30:34', '2021-02-25', '2021', '2021-03-15 8:30:45');

Query OK, 1 row affected (0.11 sec)

Output

Execute the below query to get output.

mysql> select * from datatypes;
MySQL Data Types

You will get the table with four columns and its value. The column stores date and time in a different form. The table displays date and time in various ways. You can store either time, year, and date or combine date and time.

Spatial Data types

The spatial data type stores geographical data and geometrical information. You can see below the MySQL spatial data types with its function. This data type represents roads, lakes, towns, and many places. The earth-related data is stored and operated by using this data type.

The geographical data types show any place like road, lake, etc. The geometry data types show buildings, houses, etc.

Spatial  Data TypesFunction and description
GEOMETRYThis data type stores multidimensional, special, and non – linear values
GEOMETRYCOLLECTIONThis data type stores collection of geometry values. The geometry has a multidimensional shape.
LINESTRINGThis data types stores curve values. The curve value means one or more than one point value.
MULTILINESTRINGThis data type stores collection of curve values.
MULTIPOINTThis data type stores collection of point values. The point value means value multiple of X – Y coordination.
MultipolygonThis data type stores collection of polygon values. These are complex two-dimensional shapes and multiple polygons.
POINTThis data type stores point value. The point value means the value of X – Y coordination.
POLYGONThis data type stores polygon values. The polygon shape means triangle, pentagon, hexagon, and other two-dimensional shapes.

MySQL interface creates any data with the help of these data types. This data type helps to categorize a web application's information. The data type helps to manage data such as searching, sorting, and update.