SQL Commands with Examples: SELECT, INSERT, UPDATE, DELETE, JOIN and More (MySQL and MariaDB)

Applies to: MySQL 8.4 and MariaDB 11.8 (most statements are standard SQL); every example was run on MariaDB 11.8.9 and MySQL 8.4.11

SQL commands fall into four groups: commands that define the structure (DDL: CREATE, ALTER, DROP, TRUNCATE), commands that read and change data (DML: SELECT, INSERT, UPDATE, DELETE), commands for permissions (DCL: GRANT, REVOKE) and commands for transactions (TCL: START TRANSACTION, COMMIT, ROLLBACK). The examples below build a small shop database step by step, so you can paste them in order.

Open the SQL prompt on the server with sudo mariadb or mysql -u root -p, or use the SQL tab in phpMyAdmin (running SQL queries in phpMyAdmin). End every statement with ;. Keywords are not case-sensitive; writing them in capitals just makes them easier to read.

Database commands

CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
SHOW DATABASES;
USE shop;

On cPanel hosting, create the database and its user with cPanel’s Database Wizard instead; cPanel adds your username as a prefix (for example username_shop). See create a MySQL database in cPanel.

Create tables (DDL)

CREATE TABLE customers (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  city VARCHAR(50),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_id INT NOT NULL,
  total DECIMAL(10,2) NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

SHOW TABLES;
DESCRIBE customers;

Column types and keys are explained in CREATE TABLE with examples.

INSERT: add rows

INSERT INTO customers (name, email, city) VALUES ('Asha', 'asha@example.com', 'Kochi');
INSERT INTO customers (name, email, city) VALUES
  ('Ben', 'ben@example.com', 'Leeds'),
  ('Chen', 'chen@example.com', 'Kochi');
INSERT INTO orders (customer_id, total) VALUES (1, 49.90), (1, 15.00), (3, 120.00);

Text values go in single quotes. Because email is UNIQUE, a second customer with the same address is refused: ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'email' (MySQL names the key 'customers.email').

SELECT: read data

SELECT * FROM customers;
SELECT name, city FROM customers WHERE city = 'Kochi' ORDER BY name;
SELECT name FROM customers WHERE name LIKE 'A%';
SELECT * FROM customers ORDER BY id LIMIT 2;

Count and group with GROUP BY:

SELECT city, COUNT(*) AS customers FROM customers GROUP BY city;
+-------+-----------+
| city  | customers |
+-------+-----------+
| Kochi |         2 |
| Leeds |         1 |
+-------+-----------+

JOIN: combine tables

JOIN returns only rows that match in both tables. LEFT JOIN keeps every row from the first table, even without a match, which is how you find customers with no orders:

SELECT c.name, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id
ORDER BY spent DESC;
+------+--------+
| name | spent  |
+------+--------+
| Chen | 120.00 |
| Asha |  64.90 |
| Ben  |   0.00 |
+------+--------+

UPDATE and DELETE: change and remove rows

UPDATE customers SET city = 'Manchester' WHERE id = 2;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

DELETE FROM orders WHERE customer_id = 1;
Query OK, 2 rows affected

Always use a WHERE with UPDATE and DELETE: without it, every row in the table is changed or deleted. Run the same WHERE with SELECT first to see which rows it hits. Starting the client with --safe-updates blocks the mistake: DELETE FROM orders; then fails with ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.

The foreign key protects related data. Deleting a customer who still has orders fails with ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails; delete (or reassign) their orders first.

ALTER: change a table

ALTER TABLE customers ADD phone VARCHAR(20) AFTER email;
ALTER TABLE customers MODIFY city VARCHAR(80);
ALTER TABLE customers RENAME COLUMN phone TO mobile;

Transactions: undo a group of changes

START TRANSACTION;
UPDATE orders SET total = total * 2;
SELECT id, total FROM orders;     -- shows the doubled totals
ROLLBACK;                         -- or COMMIT; to keep them

After ROLLBACK our totals were back to their old values. This works for InnoDB tables, the default engine in both MySQL and MariaDB.

Users and permissions (DCL)

CREATE USER 'shopapp'@'localhost' IDENTIFIED BY 'a-strong-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shopapp'@'localhost';
SHOW GRANTS FOR 'shopapp'@'localhost';
REVOKE DELETE ON shop.* FROM 'shopapp'@'localhost';

Give applications only the rights they need, and never let a website connect as root.

Remove data and tables

TRUNCATE TABLE orders;   -- empties the table, keeps its structure
DROP TABLE orders;       -- deletes the table
DROP DATABASE shop;      -- deletes the database and all its tables

There is no undo for these. TRUNCATE refuses a table that another table points to with a foreign key (ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint). Take a backup first: back up a database with mysqldump.

When a command fails

A typo gives ERROR 1064 (42000): You have an error in your SQL syntax, followed by the text just after the mistake, for example near 'SELEC * FROM customers'. See MySQL error 1064 for more causes. To run statements from a shell script instead of the prompt, see running MySQL or MariaDB queries from the Linux command line.

Official documentation: MySQL 8.4 SQL statements and MariaDB SQL statements.