A quick reference for core SQL statements including DDL (CREATE, DROP), DML (INSERT, UPDATE, DELETE), and TCL (COMMIT, ROLLBACK) commands.
This guide provides a quick overview of essential SQL statements in MariaDB, categorized by their function in data definition, data manipulation, and transaction control. Find brief descriptions and links to detailed documentation for each statement, along with a simple illustrative example sequence.
(If you need a basic tutorial on how to use the MariaDB database server and execute simple commands, see A MariaDB Primer. Also see Essential Queries Guide for examples of commonly-used queries.)
These statements are part of the SQL Data Definition Language - DDL.
Used to create a new, empty database.
Used to completely destroy an existing database.
Used to select a default database for subsequent statements.
Used to create a new table, which is where your data is actually stored.
Used to modify an existing table's definition (e.g., add/remove columns, change types).
Used to completely destroy an existing table and all its data.
(or DESC)
Shows the structure of a table (columns, data types, etc.).
These statements are part of the SQL Data Manipulation Language - DML.
: Used when you want to read (or select) your data from one or more tables.
: Used when you want to add (or insert) new rows of data into a table.
: Used when you want to change (or update) existing data in a table.
: Used when you want to remove (or delete) existing rows of data from a table.
These statements are part of the SQL Transaction Control Language - TCL.
(or BEGIN): Used to begin a new transaction, allowing multiple SQL statements to be treated as a single atomic unit.
: Used to save all changes made during the current transaction, making them permanent.
: Used to discard all changes made during the current transaction, reverting the database to its state before the transaction began.
This example demonstrates several of the statements in action:
Common Query: Counting Rows
To count the number of records in a table:
(Note: This query would typically be run on an existing table, for example, before it or its database is dropped.)
This page is licensed: CC BY-SA / Gnu FDL
REPLACE: Works like INSERT, but if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
TRUNCATE TABLE: Used to quickly remove all data from a table, resetting any AUTO_INCREMENT values. It is faster than DELETE without a WHERE clause for emptying a table.
-- Create a new database
CREATE DATABASE mydb;
-- Select the new database to use
USE mydb;
-- Create a new table
CREATE TABLE mytable (
id INT PRIMARY KEY,
name VARCHAR(20)
);
-- Insert some data
INSERT INTO mytable VALUES (1, 'Will');
INSERT INTO mytable VALUES (2, 'Marry');
INSERT INTO mytable VALUES (3, 'Dean');
-- Select specific data
SELECT id, name FROM mytable WHERE id = 1;
-- Update existing data
UPDATE mytable SET name = 'Willy' WHERE id = 1;
-- Select all data to see changes
SELECT id, name FROM mytable;
-- Delete specific data
DELETE FROM mytable WHERE id = 1;
-- Select all data again
SELECT id, name FROM mytable;
-- Drop the database (removes the database and its tables)
DROP DATABASE mydb;SELECT COUNT(*) FROM mytable; -- Or SELECT COUNT(1) FROM mytable;