Basic SQL Statements Guide

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.)

Defining How Your Data Is Stored

These statements are part of the SQL Data Definition Language - DDL.

CREATE DATABASE

Used to create a new, empty database.

DROP DATABASE

Used to completely destroy an existing database.

USE

Used to select a default database for subsequent statements.

CREATE TABLE

Used to create a new table, which is where your data is actually stored.

ALTER TABLE

Used to modify an existing table's definition (e.g., add/remove columns, change types).

DROP TABLE

Used to completely destroy an existing table and all its data.

DESCRIBE (or DESC)

Shows the structure of a table (columns, data types, etc.).

Manipulating Your Data

These statements are part of the SQL Data Manipulation Language - DML.

• SELECT: Used when you want to read (or select) your data from one or more tables.

• INSERT: Used when you want to add (or insert) new rows of data into a table.

• UPDATE: Used when you want to change (or update) existing data in a table.

• DELETE: Used when you want to remove (or delete) existing rows of data from a table.

• 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.

Transactions

These statements are part of the SQL Transaction Control Language - TCL.

• START TRANSACTION (or BEGIN): Used to begin a new transaction, allowing multiple SQL statements to be treated as a single atomic unit.

• COMMIT: Used to save all changes made during the current transaction, making them permanent.

• ROLLBACK: Used to discard all changes made during the current transaction, reverting the database to its state before the transaction began.

A Simple Example Sequence

This example demonstrates several of the statements in action:

-- 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;

Common Query: Counting Rows

To count the number of records in a table:

SELECT COUNT(*) FROM mytable; -- Or SELECT COUNT(1) FROM mytable;

(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}