SQL Tutorial – 05 – Insert Update Delete

The Data Manipulation Language (DML) is used to insert, update, and delete data.

Insert Into

The INSERT INTO statement adds a new row to a table with the values listed in parenthesis after the VALUES keyword.

INSERT INTO mytable VALUES ('John', 34);

You can choose into which columns the data is inserted, by listing those columns after the table name in parenthesis. For this to work the other columns must either have a default value or allow null values.

INSERT INTO mytable (name) VALUES ('Alex');

If a column has a default value, you can use this value by typing DEFAULT. If it allows null values you can type it in as NULL.

INSERT INTO mytable (name, age) VALUES (DEFAULT, NULL);

INSERT INTO can add multiple rows in one statement by placing the values for each row in a comma separated list.

INSERT INTO mytable VALUES ('Matt',36),('Jill',28),('George',23),('Sara',31);

Select all

To display the rows in a table you can use the select statement.

SELECT * FROM mytable;

Update statement

The UPDATE statement sets a new value for one or more columns in a table. It uses the WHERE keyword to update only the data that matches a condition. Without the WHERE condition all rows will be updated. This can be useful for modifying multiple columns in one statement.

UPDATE mytable SET age = 30 WHERE name = 'John';
UPDATE mytable SET age = 27, name = 'Peter' WHERE name = 'John';

Delete From

The DELETE statement is used to delete rows. It will delete every row in the table that matches the WHERE condition.

DELETE FROM mytable WHERE name = 'Sara';

If you leave out the condition all rows will be deleted.

DELETE FROM mytable;

A faster way to clear all rows in a table is to use the TRUNCTATE TABLE statement.

TRUNCATE TABLE mytable;

Recommended reading:
Sams - Teach Yourself SQL in 10 Minutes

PVT book:
Handbook of C++ Syntax