In some database engines, committing is required before any rows can be added or modified to a table. MySQL, MariaDB, Oracle, MS Access, PostgreSQL, MongoDB, and many other engines have Implied Commits, which means that every table alteration will automatically be committed for you.
If the engine requires table alterations to be committed, than you can commit a statement by simply adding the COMMIT statement at the end of a set of commands.
COMMIT;
Rows can be added to a table using the INSERT INTO syntax followed by the name of the table to insert into, the columns to add information to, and the values of the specified columns.
INSERT INTO <table1>(<column1>, <column2>, ...) VALUES (<value1>, <value2>, ...);
If a column is not specified when inserting new data, its DEFAULT value will be assigned. If the column does not have a default value, it will be assigned a NULL. That is, as long as the column can contain null as its value. If it can’t, then the INSERT statement will return an error and no data will be added.
Likewise, if the number of values specified does not match the number of columns specified, the INSERT statement will also return an error. You can negate the specification of columns after the INSERT INTO keyword pair. Doing so will require you to specify a value for every column in the table. So long as a column supports “nulls”, you can specify NULL as its value.
In more cases than not, tables will include an auto-incrementing primary-key. If this is the case, you do not want to insert a value into this column because it is designed to do that for you. Each database handles primary keys slightly differently, but they all work the same.
For MySQL and MariaDB, you can do one of two things: Specify every column except the auto-incrementing column(s) before the VALUES operator…
INSERT INTO <table1>(<column2>, <column3>, ...) VALUES (<value2>, <value3>, ...);
Or use null as the value for the auto-incrementing column(s)…
INSERT INTO <table1> VALUES (null, <value2>, <value3>, ...);
In PostgreSQL, you will need to set the value for the following insert statement by using the SELECT setval() statement first.
SELECT setval(<column1>_seq, (SELECT MAX(<column1>) FROM <table1>)+1);
INSERT INTO <table1>(<column2>, <column3>, ...) VALUES (<value2>, <value3>, ...);
If you are trying to copy data from one table to another, you can use a SELECT Query instead of a VALUES operation.
INSERT INTO <table1>(<column1>, <column2>, ...)
SELECT (<column1>, <column2>, ...) FROM <table2>;
In the case that you need to insert multiple rows of data at the same time, you can use INSERT ALL to accomplish this. This essentially separates the INTO keyword into an independent clause for each row. Oracle requires that there be a SELECT * FROM DUAL query at the end for this statement to work.
INSERT ALL
INTO <table1> VALUES (<column1>, <column2>, ...)
INTO <table1> VALUES (<column1>, <column2>, ...)
SELECT * FROM DUAL;
One of the key parts of the CRUD model is updating. You can change the data of an existing record by using the UPDATE keyword.
UPDATE <table1>
SET <column2> = 'VALUE'
WHERE <condition>;
To delete a row from a table, use the DELETE FROM keywords.
DELETE FROM <table1> WHERE <condition>;
Without the WHERE clause, all of the data from that table will be deleted.
If a column has a FOREIGN KEY constraint in another table, it cannot be deleted until that dependent column is deleted first.