SQL Basics

Querying Data from a Table

Query Data in Columns c1, c2 from a Table

SELECT c1, c2 FROM t;

Query All Rows and Columns from a Table

SELECT * FROM t;

Query Data and Filter Rows with a Condition

SELECT c1, c2 FROM t
WHERE condition;

Query Distinct Rows from a Table

SELECT DISTINCT c1 FROM t
WHERE condition;

Sort the Result Set in Ascending or Descending Order

SELECT c1, c2 FROM t

ORDER BY c1 ASC [DESC];

Skip Offset of Rows and Return the Next n Rows

SELECT c1, c2 FROM t
ORDER BY c1
LIMIT n OFFSET offset;

Group Rows using an Aggregate Function

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1;

Filter Groups using Having Clause

SELECT c1, aggregate(c2)
FROM t
GROUP BY c1
HAVING condition;

Modifying Data

Insert One Row into a Table

INSERT INTO t(column_list)
VALUES(value_list);

Insert Multiple Rows into a Table

INSERT INTO t(column_list)

VALUES (value_list),
(value_list), …;

Insert Rows from t2 into t1

INSERT INTO t1(column_list)
SELECT column_list
FROM t2;

Update New Value in the Column c1 for All Rows

UPDATE t
SET c1 = new_value;

Update Values in the Column c1, c2 that Match the Condition

UPDATE t
SET c1 = new_value,
c2 = new_value
WHERE condition;

Delete All Data in a Table

DELETE FROM t;

Delete Subset of Rows in a Table

DELETE FROM t
WHERE condition;

Table Management

Create a new Table with Three Columns

CREATE TABLE t (
  id INT PRIMARY KEY,
  name VARCHAR NOT NULL,
  price INT DEFAULT 0
);

Delete the Table from the Database

DROP TABLE t ;

Add a new Column to the Table

ALTER TABLE t ADD column;

Drop Column c from the Table

ALTER TABLE t DROP COLUMN c ;

Add a Constraint

ALTER TABLE t ADD constraint;

Drop a Constraint

ALTER TABLE t DROP constraint;

Rename a Table from t1 to t2

ALTER TABLE t1 RENAME TO t2;

Rename Column c1 to c2

ALTER TABLE t1 RENAME c1 TO c2 ;

Remove all Data in a Table

TRUNCATE TABLE t;