1. INSERT Statement (Creating Data)

The INSERT statement is used to add new records to a table.

Basic Syntax

CREATE TABLE employees (
    id INT,
    name VARCHAR(50)
);

INSERT INTO employees VALUES (1, 'John');
INSERT INTO employees VALUES (2, 'Alice');

In SQL, both single quotes and double quotes can be used to delimit string values.

Note: If you encounter errors when inserting Chinese characters, ensure your database was created with the utf8mb4 character set.

Single Row with Full Column Insert

By default, INSERT requires values for all columns in the exact order they were defined.

INSERT INTO employees VALUES (100, 'Sarah');
INSERT INTO employees VALUES (101, 'Mike');

Multiple Rows with Specified Column Insert

When using column specification, the number and order of values must match the specified columns, not necessarily the table definition.

INSERT INTO employees (id, name) VALUES
 (102, 'David'),
 (103, 'Emma');

Unspecified columns will default to NULL.

You can insert multiple rows in a single statement, which is more efficient than multiple separate INSERT statements.

Inserting Date and Time Values

CREATE TABLE events (created_at DATETIME);
INSERT INTO events VALUES ('2024-04-27 18:30:00');

To insert the current system timestamp, use the NOW() function:

INSERT INTO events VALUES (NOW());

2. SELECT Statement (Querying Data)

The SELECT statement retrieves data from one or more tables. Among all CRUD operations, queries tend to be the most complex.

Selecting All Columns

-- Using asterisk (*) selects all columns
-- Not recommended for production due to increased data transfer
SELECT * FROM employees;

The asterisk acts as a wildcard representing all columns.

Warning: Using SELECT * on tables with large datasets can cause performance issues and may prevent optimal index usage.

Selecting Specific Columns

-- Specify only the columns you need
SELECT id, name FROM employees;
SELECT id FROM employees;
SELECT name, id FROM employees;

The order of columns in the SELECT clause does not need to match the table definition.

Using Expressions in SELECT

You can include expressions, mathematical operations, and function calls in your SELECT clause to transform or calculate values on the fly.