Database Operations Concepts

Database Operations (CRUD)

C.R.U.D, standing for Create, Read, Update, Delete, are the four basic functions of persistent storage. Alternate words are sometimes used whe defining the four basic functions of CRUD, such as retrieve instead of read, modifiy instead of update, or destroy instead of delete. CRUD is also sometimes used to describe user interface (UI) conventions that facilitate viewing, searching, and changing information often usin computer-based forms and reports.

CRUD Image Example

We will be taking a look at SQL, which has different names for the CRUD functions, these are called Statements

Below are the names:

Operation	SQL
Create	INSERT
Read	SELECT
Update	UPDATE
Delete	DELETE

INSERT / Create

In Database Systems, Insert is used for creating and therefore, inserting new records into the table.

To insert a new record into a table you need to use the keyword INSERT INTO, and then specify the table name and the columns that you want to insert. The columns go inside of the parenthesis and then we specify a VALUES clause.

In this example, our table is named called 'Paints' and it has 3 columns (values): id, colour and size. This is how we insert a medium orange paint into the records.

                        
                            INSERT INTO Paints
VALUES (1,"orange","medium");

The table will now be updated with that record. But what if we want to insert multiple records, well lets see how to do that:

                        
                            INSERT INTO Paints
VALUES (2,"blue","large"),(3,"pink","small");

Not hard? Yup, just get the syntax right and you'll be fine. There are other ways to insert records using UNION etc but we will cover that in a later chapter.

SELECT / Read

The SELECT statement retrieves data.

To retrieve data you need to use the syntax below, its pretty straight forward.

Let's say, with the new records we inserted earlier, we now want to retrieve the data and display it in front of us.


                            SELECT * FROM Paints;

That code above would retrieve the records and displays ALL (using the * - called the Wildcard Character) the records in that table. But what if we wanted to select a specific paint colour, like blue for example?


                            SELECT * FROM Paints WHERE colour = 'blue';

This result will return only the record where colours column has blue paint. In this instance only the 2nd record will show.

UPDATE / Update

Using the UPDATE statement in SQL, changes the existing records of the table.

The best syntax for updating your records is to use the UPDATE, SET and WHERE keywords.

Let's say earlier, we inserted the wrong size of the 3rd record (pink, small) and actually, the pink tin of paint was actually large. Let's try to update the records to change that:

                        
                            UPDATE Paints
SET size = large
WHERE colour = pink;

We can now use the SELECT statement to check and see if it has changed. And most likely it has :D.

DELETE / Delete

DELETE in SQL operations refers to removing a record from table.

Using DELETE, FROM and WHERE you can target the specific record(s) for deletion.

Let's say we needed to remove a record as the item had just sold. The item having the id of 1.

                        
                            DELETE FROM Paints
WHERE id = 1;

Retrieving the results again will show that the record had been removed.

Other

You also need to create the table to perform the database operations and you can do that like this:


                            CREATE TABLE Paints;

As well as creating the table, if you ever need to permanently remove the table you need to drop the table, dropping removes the data as well as the table, whilst deleting does the same but it can be rolled back. Drop is the cleaner option if you are just practicing with databases.


                            DROP TABLE Paints;

Sources: (click on them to see them)

Database Operations Concepts (CRUD)