SQL CRUD Tutorial

I defined what is SQL in this post, but what’s the deal with CRUD acronym? It is mainly used in computer programming and stands from create read update delete, the main four basic operations you can perform against a database. Depending on the preferences of each developer, CRUD is also spelled as create retrieve (instead of read) modify (instead of update) and destroy (instead of delete).

The term CRUD was first popularized by James Martin in his 1983 book Managing the Data-base Environment.

In SQL, the corresponding operations for CRUD functionality are is follow:

C – insert
R – select
U – update
D – delete

From a learning perspective, this tutorial is not probably the ideal one, but from a reference perspective it make sense. To give a high value to this page, I will go thought all sql commands a developer need to know when design a database system.

Create database statement

The create database statement is used to create a new database and has the following syntax:

Drop database statement

The drop database statement is used to drop (delete) an existing database. It also will delete all the information store in the database. Here is the syntax:

The previously two statements (create and drop database) can be executed only with administrator privilege on database server.

Create table statement

The create table statement is used to create a new tables in a database. It’s syntax is as follow:

In the create table statement you specify, the table name, the columns name and the type for each column. These are mandatory info to create a sql table. Optionally you can provide information about the primary key, and specify if the field is nullable or not.  Next example, illustrate a real example to create a table with three columns: a numerical primary key which value is automatically incremented by the database and two character string columns:

Upon creation there is no data into the table. You have to use the insert sql statement to add data to the table.

Short note about AUTO_INCREMENT, as default it’s value start with 1 and increases by 1.

Delete table statement

The delete table statement drops a table from the database. It has the following syntax:

drop table table_name;

Insert statement

The syntax for the insert statement is:

or you can specify the column in which to insert the data,

Now, as for the real example lets insert three records into computer_languages table:

Note that I did not include in this example the first column, language_id, as it is declared as AUTO_INCREMENT.

Select statement

With select statement you can retrieve data from a database table. According to your needs you can retrieve:

  • one column,
  • multiple columns,
  • all columns of the table.

Another way to retrieve all columns from a database’s table is to list the name of each column, separated by comma.

In a select statement you can use the where clause to reduce the amount of returned data. The where clause works with the following type of operators:

  • comparison operators: =, !=, <, >, <=, >=
  • BETWEEN and LIKE operators.

Update statement

The update statement is used to update data in your table. It has the next syntax:

The where clause can use the same operators as described in the select clause.

Delete statement

The delete statement is used to remove records from your table and has the following syntax:

Be aware that not mention the where clause will delete all records of the table.

Spread the love

Leave a Reply