Standard SQL Syntax Rules

MySQL statements can span single or multiple lines, with each statement terminated by a semicolon. The database engine treats SQL keywords case-insensitively, though convention dictates uppercase keywords for readability. Code examples through out this guide follow lowercase conventions.

Supported comment styles:

# single-line comment
-- single-line comment (space required after --)
/* multi-line
   comment */

SQL Statement Categories

The SQL language divides into four primary classifications:

• DDL (Data Definition Language): Manages structural objects including databases, tables, and columns
• DML (Data Manipulation Language): Handles data insertion, updates, and deletion
• DQL (Data Query Language): Retrieves information from database tables
• DCL (Data Control Language): Controls user access permissions and security settings

Database Management (DDL)

Working with Databases

-- Display all available databases
show databases;

-- Create a new database
create database company_db;
create database if not exists company_db;

-- Remove an existing database
drop database company_db;
drop database if exists company_db;

-- Switch to a specific database
use company_db;

-- Identify the currently active database
select database();

Table Operations

-- List all tables within the current database
show tables;

-- Examine table structure
desc employees;

-- Define a new table
create table department (
    dept_id int,
    dept_name varchar(50),
    created_at timestamp
);

-- Remove a table completely
drop table employees;
drop table if exists employees;

-- Rename an existing table
alter table department rename to dept_info;

-- Add a new column
alter table department add manager_id int;

-- Change column data type
alter table department modify manager_id varchar(20);

-- Rename column and modify its type simultaneously
alter table department change manager_id lead_id varchar(30);

-- Remove a column permanently
alter table department drop lead_id;

Data Manipulation (DML)

-- Insert values into specific columns
insert into employees(id, name, salary) values(1, 'Alice', 5000);

-- Insert complete row (all columns)
insert into employees values(2, 'Bob', 6000, '2024-01-15');

-- Bulk insert multiple rows
insert into employees(id, name, salary) values
    (3, 'Carol', 5500),
    (4, 'David', 4800),
    (5, 'Eve', 5200);

insert into employees values
    (6, 'Frank', 5800, '2024-02-20'),
    (7, 'Grace', 6200, '2024-03-10');

-- Update existing records
update employees set salary = 6500 where id = 2;
update employees set department = 'Sales', salary = 7000 where id = 1;

-- Remove specific records
delete from employees where id = 5;

Data Querying (DQL)

Complete Query Structure

select [distinct] column_names
from table_references
where filtering_conditions
group by grouping_column
having group_filtering_conditions
order by sort_column [asc|desc]
limit offset, row_count;

Retrieving Data

-- Fetch all columns
select * from employees;

-- Retrieve specific columns
select name, salary from employees;

-- Eliminate duplicate results
select distinct department from employees;

-- Apply column aliases
select employee_name as name, monthly_salary as salary from payroll;
select employee_name name, monthly_salary salary from payroll;  -- as keyword optional

Filtering with Conditions

select column_list from table_name where expression;

Comparison and logical operators available:

Operator	Purpose
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
=	Equality comparison
<> or !=	Inequality
between x and y	Inclusive range check
in(value1,value2,...)	Match against multiple values
is null	Null value check
is not null	Non-null value check
and or &&	Conjunction
or or `
not or !	Negation

Pattern Matching

Wildcard characters enable flexible text searches:

• _ — Matches exactly one character
• % — Matches zero or more characters

-- Find names starting with 'J'
select * from employees where name like 'J%';

-- Find names ending with 'son'
select * from employees where name like '%son';

-- Find names containing 'an' anywhere
select * from employees where name like '%an%';

-- Find names with second character 'a'
select * from employees where name like '_a%';

Organizing Results

-- Sort by single column
select * from employees order by hire_date asc;
select * from employees order by hire_date desc;

-- Multi-level sorting
select * from employees order by department asc, salary desc;

Aggregate Functions

These functions perform calculations across grouped rows, ignoring null values:

Function	Purpose
count()	Total row count
max()	Highest value
min()	Lowest value
sum()	Arithmetic total
avg()	Mean value

select count(employee_id) from staff;
select max(salary), min(salary) from employees;
select avg(performance_score) from reviews;

Grouping Results

When grouping, the select clause should only include aggregate functions and the grouping column—other columns produce meaningless output.

select grouping_column, aggregate_function(column)
from source_table
[where pre_group_condition]
group by grouping_column
[having post_group_condition];

-- Calculate average salary per department
select department, avg(salary) from staff group by department;

-- Grouping with multiple aggregates
select department, avg(salary), count(*) from staff group by department;

-- Filter groups based on computed values
select department, avg(salary), count(*) from staff
where performance_rating > 3
group by department
having count(*) > 5;

Pagination

MySQL uses zero-based indexing for result sets.

-- Formula: offset = (page_number - 1) * rows_per_page
select * from employees limit offset, row_count;

-- Retrieve first three records
select * from employees limit 0, 3;

-- Display three records per page (page 1)
select * from employees limit 0, 3;

-- Display three records per page (page 2)
select * from employees limit 3, 3;

-- Display three records per page (page 3)
select * from employees limit 6, 3;

MySQL Column Data Types

Numeric Types

Type	Storage	Description	Example
TINYINT	1 byte	Tiny integer	age tinyint
SMALLINT	2 bytes	Small integer	quantity smallint
MEDIUMINT	3 bytes	Medium integer	code mediumint
INT	4 bytes	Standard integer	user_id int
BIGINT	8 bytes	Large integer	transaction_id bigint
FLOAT	4 bytes	Single-precision	rating float
DOUBLE	8 bytes	Double-precision	price double(10,2)
DECIMAL	Variable	Exact decimal	balance decimal(15,2)

Date and Time Types

Type	Storage	Description
DATE	3 bytes	Calendar date (YYYY-MM-DD)
TIME	3 bytes	Time of day or duration
YEAR	1 byte	Year value (4 digits)
DATETIME	8 bytes	Date combined with time
TIMESTAMP	4 bytes	Auto-updating timestamp

String Types

Type	Storage Range	Description
CHAR(n)	0-255 bytes	Fixed-length, padded storage
VARCHAR(n)	0-65535 bytes	Variable-length, space-efficient
TINYTEXT	0-255 bytes	Short text content
TEXT	0-65535 bytes	Standard text storage
MEDIUMTEXT	0-16MB	Medium-length text
LONGTEXT	0-4GB	Extended text data
TINYBLOB	0-255 bytes	Binary data (tiny)
BLOB	0-65KB	Binary large object
MEDIUMBLOB	0-16MB	Binary medium object
LONGBLOB	0-4GB	Binary large object

CHAR allocates the full specified length regardless of content, while VARCHAR adjusts storage dynamically. For instance, storing "John" uses 10 bytes in CHAR(10) but only 4 bytes plus overhead in VARCHAR(10).