### Basic SELECT Queries ###
SELECT column1, column2 FROM table_name;
-- Retrieve only specific columns from the table.
SELECT * FROM table_name;
-- Retrieve all columns from the table without filtering.
SELECT DISTINCT column_name FROM table_name;
-- Retrieve unique (non-duplicate) values from a specified column.
### Filtering Data ###
SELECT * FROM table_name WHERE condition;
-- Retrieve rows that meet a specific condition (e.g., age > 30).
SELECT * FROM table_name WHERE column = value;
-- Retrieve rows where a specific column matches a given value.
SELECT * FROM table_name WHERE column BETWEEN value1 AND value2;
-- Retrieve rows where the column value is within a specified range.
SELECT * FROM table_name WHERE column IN (value1, value2, ...);
-- Retrieve rows where the column value matches any value in a list.
SELECT * FROM table_name WHERE column LIKE 'pattern';
-- Pattern matching using wildcards (e.g., 'A%' for strings starting with 'A').
SELECT * FROM table_name WHERE column IS NULL;
-- Retrieve rows where the column value is NULL (empty).
SELECT * FROM table_name WHERE column IS NOT NULL;
-- Retrieve rows where the column value is NOT NULL.
### Sorting Data ###
SELECT * FROM table_name ORDER BY column ASC;
-- Sort rows by a column in ascending order (default).
SELECT * FROM table_name ORDER BY column DESC;
-- Sort rows by a column in descending order.
### Aggregation Functions ###
SELECT COUNT(column_name) FROM table_name;
-- Count the number of rows in a table or matching condition.
SELECT SUM(column_name) FROM table_name;
-- Calculate the sum of all values in a column.
SELECT AVG(column_name) FROM table_name;
-- Calculate the average value in a column.
SELECT MAX(column_name) FROM table_name;
-- Retrieve the highest value in a column.
SELECT MIN(column_name) FROM table_name;
-- Retrieve the lowest value in a column.
### Grouping Data ###
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name;
-- Group rows by a column and count rows in each group.
SELECT column_name, COUNT(*) FROM table_name GROUP BY column_name HAVING condition;
-- Group rows and filter groups using the HAVING clause.
### Joins ###
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;
-- Retrieve matching rows from both tables (common values only).
SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.column = table2.column;
-- Retrieve all rows from the left table, and matching rows from the right.
SELECT * FROM table1 RIGHT OUTER JOIN table2 ON table1.column = table2.column;
-- Retrieve all rows from the right table, and matching rows from the left.
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column;
-- Retrieve all rows from both tables, with NULL for non-matching rows.
### Modifying Data ###
INSERT INTO table_name (column1, column2) VALUES (value1, value2);
-- Add a new row to the table.
UPDATE table_name SET column1 = value1 WHERE condition;
-- Update specific rows that match a condition.
DELETE FROM table_name WHERE condition;
-- Remove rows that meet a specific condition.
### Creating Tables ###
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints
);
-- Create a new table with specified columns and constraints.
### Altering Tables ###
ALTER TABLE table_name ADD column_name datatype;
-- Add a new column to the table.
ALTER TABLE table_name DROP COLUMN column_name;
-- Remove a column from the table.
ALTER TABLE table_name MODIFY column_name datatype;
-- Change the data type of an existing column.
### Deleting Data and Tables ###
DROP TABLE table_name;
-- Delete an entire table and its data permanently.
TRUNCATE TABLE table_name;
-- Remove all rows from a table but keep the structure.
DELETE FROM table_name WHERE condition;
-- Delete specific rows from a table.
### Subqueries ###
SELECT column_name FROM table_name WHERE column_name IN (
SELECT column_name FROM another_table
);
-- Use a query inside another query to filter data.
### Constraints ###
CREATE TABLE table_name (
column_name datatype PRIMARY KEY,
column_name datatype UNIQUE,
column_name datatype NOT NULL,
column_name datatype DEFAULT value,
column_name datatype FOREIGN KEY REFERENCES other_table(column)
);
-- Define table constraints such as primary key, unique, and foreign key.
### Transactions ###
BEGIN TRANSACTION;
-- Start a new transaction.
UPDATE table_name SET column_name = value WHERE condition;
-- Modify data within a transaction.
COMMIT;
-- Save all changes made in the transaction.
ROLLBACK;
-- Undo changes made in the transaction.
### Indexes ###
CREATE INDEX index_name ON table_name (column_name);
-- Create an index to speed up data retrieval.
DROP INDEX index_name ON table_name;
-- Remove an existing index.
### Views ###
CREATE VIEW view_name AS SELECT columns FROM table_name WHERE condition;
-- Create a virtual table (view) for commonly used queries.
DROP VIEW view_name;
-- Remove an existing view.
