SQL Basics and Data Types

Comments

SQL (Structured Query Language) is the standard language used to manage and query relational databases. SQL provides various commands to add, update, delete, and query data. In this article, we will explore the basic structure of SQL and compare common data types used in MySQL and PostgreSQL.

1. Basic Structure of SQL

SQL consists of several core components:

DDL (Data Definition Language): Used to define database objects. Examples: CREATE, ALTER, DROP.
DML (Data Manipulation Language): Used to manipulate data. Examples: INSERT, UPDATE, DELETE, SELECT.
DCL (Data Control Language): Used to control access and permissions. Examples: GRANT, REVOKE.
TCL (Transaction Control Language): Used to manage transactions. Examples: COMMIT, ROLLBACK, SAVEPOINT.

2. SQL Data Types (MySQL vs PostgreSQL)

Each column in a database table is defined with a data type. Choosing the right data type is essential for performance and data integrity. Below is a comparison of commonly used data types in MySQL and PostgreSQL.

2.1 Numeric Data Types

Data Type	MySQL	PostgreSQL	Description
`TINYINT`	✅	❌	Small integers between 0–255.
`SMALLINT`	✅	✅	Small integers.
`MEDIUMINT`	✅	❌	Medium-sized integers in MySQL only.
`BIGINT`	✅	✅	Large integers.
`DECIMAL(p,s)`	✅	✅	Precise decimal numbers (p = precision, s = scale).
`NUMERIC(p,s)`	✅	✅	Same as DECIMAL.
`FLOAT`	✅	✅	Floating-point numbers.
`REAL`	❌	✅	Similar to FLOAT, less precision in PostgreSQL.

SQL

CREATE TABLE Customers (
    ID SERIAL PRIMARY KEY,
    Balance DECIMAL(10,2) NOT NULL
);

2.2 String Data Types

Data Type	MySQL	PostgreSQL	Description
`CHAR(n)`	✅	✅	Fixed-length string.
`VARCHAR(n)`	✅	✅	Variable-length string.
`TEXT`	✅	✅	Large text fields.

SQL

CREATE TABLE Products (
    ProductName VARCHAR(50),
    Description TEXT
);

2.3 Date and Time Data Types

Data Type	MySQL	PostgreSQL	Description
`DATE`	✅	✅	Stores year, month, and day.
`TIME`	✅	✅	Stores hour, minute, second.
`DATETIME`	✅	❌	Used in MySQL for date and time.
`TIMESTAMP`	✅	✅	Stores a Unix timestamp.

SQL

CREATE TABLE Orders (
    OrderID SERIAL PRIMARY KEY,
    OrderDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

2.4 Boolean Data Types

Data Type	MySQL	PostgreSQL	Description
`BOOLEAN`	❌ (Uses TINYINT(1))	✅	Stores TRUE or FALSE.

SQL

CREATE TABLE Users (
    ID SERIAL PRIMARY KEY,
    Name VARCHAR(50),
    Active BOOLEAN DEFAULT TRUE
);

3. Key Differences Between MySQL and PostgreSQL

Auto-Incrementing IDs:
- MySQL: Uses AUTO_INCREMENT
- PostgreSQL: Uses SERIAL
JSON Support:
- MySQL: Supports JSON data type.
- PostgreSQL: Supports JSON and JSONB (faster).
Indexing Differences:
- PostgreSQL: Offers powerful full-text search with GIN and GiST indexes.
- MySQL: Provides FULLTEXT INDEX for similar functionality.
Triggers and Procedures:
- PostgreSQL has more advanced support for triggers and stored procedures.
- MySQL supports triggers but with fewer features compared to PostgreSQL.

Both MySQL and PostgreSQL are widely used RDBMS platforms. MySQL is preferred for web-based applications due to its speed and lightweight nature, while PostgreSQL is ideal for complex applications thanks to its advanced data types, JSONB support, and indexing capabilities.

Share: Facebook X LinkedIn WhatsApp

Sending Emails with PHP (Using PHPMailer)

Modern Web Development Tools and Technologies