SQL basics-SELECT, INSERT, UPDATE, DELETE queries

SQL (Structured Query Language) is the standard language for interacting with databases. You use SQL to retrieve, insert, update, and delete data in MySQL or MariaDB.

SELECT - retrieve data

The most common SQL command. Retrieves data from a table:

SELECT * FROM users;  -- all columns from users table
SELECT id, name FROM users;  -- specific columns only
SELECT COUNT(*) FROM users;  -- count rows
SELECT DISTINCT country FROM users;  -- unique values

WHERE - filter results

Filter results to match conditions:

SELECT * FROM users WHERE id = 5;
SELECT * FROM users WHERE name = 'John';
SELECT * FROM users WHERE age > 18 AND country = 'USA';
SELECT * FROM posts WHERE status = 'published' ORDER BY date DESC;

INSERT - add data

Add new rows to a table:

INSERT INTO users (name, email, age) VALUES ('John', 'john@example.com', 25);
INSERT INTO users (name, email) VALUES ('Jane', 'jane@example.com');  -- age omitted, uses default

UPDATE - modify data

Change existing data:

UPDATE users SET age = 26 WHERE id = 5;
UPDATE users SET status = 'active' WHERE created_date < '2020-01-01';

DELETE - remove data

Delete rows (be careful!):

DELETE FROM users WHERE id = 5;
DELETE FROM posts WHERE status = 'spam';  -- delete all spam posts

JOINs - combine tables

Get data from multiple tables:

SELECT users.name, posts.title FROM users
JOIN posts ON users.id = posts.user_id;  -- inner join

SELECT users.name, posts.title FROM users
LEFT JOIN posts ON users.id = posts.user_id;  -- includes users with no posts

Related: Database administration | Indexes | Slow queries

Related articles