Originally published by Robert Beisert at fortcollinsprogram.robert-beisert.com

CRUD – The Things SQL Can Do

“CRUD”: “Create, Read, Update, Destroy”.

These are the four basic database operations. Logically, they’re the four basic data operations as well.

  • CREATE – The act of producing new data which has not previously been introduced to the system. This data can be produced internally (through calculations, mostly) or externally (through input).
  • READ – The act of retrieving and acting on data. This operation includes both the retrieval (which data we wish to see) and the presentation (how we want the data to look).
  • UPDATE – The act of modifying data already in the system. This operation includes locating the data in the system and replacing said data.
  • DESTROY – The act of removing data from the system. In a database, this data can be either an entire table or a single entry.

SQL does not necessarily use these exact terms. However, the terms used are both simple and easy to work with. Let’s take a quick look at some of those commands.

Create

There are two things we create inside a SQL database: tables and entries.

To create a table, we use the command CREATE TABLE, as such:

[code language=”sql”]

CREATE TABLE table_name (

field_one INTEGER NOT NULL,

field_two VARCHAR UNIQUE,

–More fields

);

[/code]

Once the table exists, we want to create new entries in that table. We do that with the INSERT command, as such:

[code language=”sql”]

INSERT INTO table_name VALUES

(e1_val1, e1_val2, …), (e2_val1, e2_val2, …);

[/code]

Read

Now that you have some data in your table, you need to find some particular piece and retrieve it. Suppose I have a table named “People” that had fields named “ID, Name, Age, Gender”. I know that somewhere in that table I have people named “Steve” and “Thomas”, but I want to know their ages and IDs. I could read that data using SELECT, like this:

[code language=”sql”]

SELECT ID, Name, Age

FROM People

WHERE Name="Steve" OR Name="Thomas";

[/code]

This would give me back a table with columns labeled ID, Name, and Age, and the only entries inside would be those of Steve and Thomas.

Most of the heavy lifting done in SQL is done in these SELECT commands.

Update

If we use the same example table from the “read” section, what do we do when Thomas’s birthday comes along and he goes from 22 to 23? When that happens, we have to UPDATE the table, like so:

[code language=”sql”]

UPDATE People

SET Age=23

WHERE Name="Thomas";

[/code]

Ideally, you have some manager handle most of the updates for you. If you have to change a LOT of birthdays, the set of commands required to update all of those values could get long.

Destroy

For one reason or another, we don’t want Steve in our database of people anymore. We have to DELETE his record, like so:

[code language=”sql”]

DELETE FROM People

WHERE Name="Steve";

[/code]

Or maybe, we just don’t need the whole “People” table anymore. We can DROP that table:

[code language=”sql”]

DROP TABLE People;

[/code]

And it is done. The People table is now gone forever – lost in time.

“SUDI”

Believe it or not, you can do a remarkable amount of work with just the commands we’ve looked at so far. The key commands that you should memorize today are:

  • SELECT value(s) FROM table(s) WHERE something_is_true
  • UPDATE table SET column=value WHERE something_is_true
  • DELETE FROM table WHERE something_is_true
  • INSERT field(s) INTO table(s) VALUES ( value(s) )

If you can get the hang of these four commands, you are halfway to total mastery (seriously – it’s not that hard).

An excellent reference on SQL Commands from w3schools.com

photo by: