My sql
The big data era has solidified MySQL as a prominent technology which people commonly identify
with ecological data systems. MySQL stands as both a popular and widespread default database system
across industries and businesses which demonstrates its importance for all enterprise data and IT
professionals to gain basic proficiency. Entering relational systems with minimal experience allows users
to construct speedy powerful secure databases using MySQL.
1. Numeric Data Types
Binned values along with floating-point values find storage in MySQL using numeric data types. These
types provide essential functionality because scientists need them to run calculations and assess data
patterns or implement indexing systems.
1.1 INT (Integer)
Description: Stores whole numbers without decimals.
Size: 4 bytes.
Use Case: The database storage type functions best for IDs and counts among other integer-based data
points like age and product quantities.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
age INT NOT NULL
);
1.2 FLOAT (Floating-Point Number)
Description: The storage of floating points enables stores to approximate decimal values.
Size: 4 bytes.
Use Case: This type is suitable for scientific calculations when accuracy remains less important for a
solution.
Example:
CREATE TABLE measurements (
id INT PRIMARY KEY, temperature FLOAT NOT NULL);
1.3 DECIMAL (Fixed-Point Number)
Description: Additionally stores exact decimal numbers while enabling users to define the precision
settings.
Size: Variable based on precision.
Use Case: Precise applications involving currency values demonstrate the best use of this data type.
Example:
CREATE TABLE transactions (
id INT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL
);
2. String Data Types
String data types store alphanumeric text and are commonly used for names, descriptions, and textual
content.
2.1 VARCHAR (Variable-Length String)
Description: Stores variable-length text up to a specified limit.
Size: 1 byte per character + 1-2 bytes for length storage.
Use Case: Useful for storing names, addresses, and user inputs where length varies.
Example:
CREATE TABLE transactions (
id INT PRIMARY KEY,
amount DECIMAL(10,2) NOT NULL
);
2.2 CHAR (Fixed-Length String)
Description: Stores fixed-length text values.
Size: Equal to the defined length.
Use Case: The storage type works best when dealing with values that have
fixed length dimensions such as country codes or status flags.
Example:
CREATE TABLE countries (
code CHAR(3) PRIMARY KEY,
name VARCHAR(50) NOT NULL
);
2.3 TEXT (Large Text Data)
Description: Stores large amounts of text data.
Size: The business decides the maximum TEXT data length but it can stretch up to 4GB based on
the variant (TINYTEXT, TEXT, MEDIUMTEXT, LONGTEXT).
Use Case: Used for article repositories and content descriptions and user-submitted materials
storage.
Example:
CREATE TABLE blog_posts (
id INT PRIMARY KEY,
content TEXT NOT NULL
);
3. Date and Time Data Types
Values representing dates and timestamps and intervals require storage in date and time data
types.
3.1 DATE
Description: The database stores date information using 'YYYY-MM-DD' format representation
for dates.
Size: 3 bytes.
Use Case: Your database should use the DATE data type for documenting birthdates as well as
registration dates and other forms of date-related information.
Example:
CREATE TABLE employees (
id INT PRIMARY KEY,
birth_date DATE NOT NULL
);
3.2 DATETIME
Description: Stores both date and time values in 'YYYY-MM-DD HH:MM:SS' format.
Size: 8 bytes.
Use Case: Ideal for logging events and transaction timestamps.
Example:
CREATE TABLE logs (
id INT PRIMARY KEY,
event_time DATETIME NOT NULL
);
3.3 TIMESTAMP
Description: The TIMESTAMP data type functions by storing timestamp information while
converting to UTC automatically.
Size: 4 bytes.
Use Case: This data type works best for both tracking when records change and documenting
system actions.
Example:
CREATE TABLE user_activity (
id INT PRIMARY KEY,
last_login TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
References
MySQL Documentation: [Link]
W3Schools MySQL Data Types:
[Link]
GeeksforGeeks MySQL Data Types:
[Link]