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:

create database database_name;

1	create database database_name;

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:

drop database database_name;

1	drop database database_name;

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:

create table table_name (
    column_name1 column_type,
    column_name2 column_type,
    column_name3 column_type,
    PRIMARY KEY (pk_field1, pk_field2)
);

create table table_name (

column_name1 column_type,

column_name2 column_type,

column_name3 column_type,

PRIMARY KEY (pk_field1, pk_field2)

);

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:

create table computer_languages (
    languge_id int NOT NULL AUTO_INCREMENT,
    language_name varchar(20) NOT NULL,
    language_description varchar(255),
    language_
    PRIMARY KEY (language_id)
);

create table computer_languages (

languge_id int NOT NULL AUTO_INCREMENT,

language_name varchar(20) NOT NULL,

language_description varchar(255),

language_

PRIMARY KEY (language_id)

);

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:

insert into table_name values ('value1', 'value2', 'value3');

1	insert into table_name values ('value1', 'value2', 'value3');

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

insert into table_name (column_name1, column_name2) values ('value1', 'value2');

1	insert into table_name (column_name1, column_name2) values ('value1', 'value2');

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

insert into computer_languages (language_name, language_description) values ('Java', 'choose your own description');
insert into computer_languages (language_name, language_description) values ('PHP', 'choose your other description');
insert into computer_languages (language_name) values ('Swift');

insert into computer_languages (language_name, language_description) values ('Java', 'choose your own description');

insert into computer_languages (language_name, language_description) values ('PHP', 'choose your other description');

insert into computer_languages (language_name) values ('Swift');

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.

-- retrieve one column
select language_name from computer_languages;

-- retrieve multiple columns
select id, language_name from computer_languages;

-- retrieve all columns from computer_languages table
select * from computer_languages;

-- retrieve one column

select language_name from computer_languages;

-- retrieve multiple columns

select id, language_name from computer_languages;

-- retrieve all columns from computer_languages table

select * from computer_languages;

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:

update table_name set column_name_1 = value_1, column_name_2 = value_2 where conditions;

1	update table_name set column_name_1 = value_1, column_name_2 = value_2 where conditions;

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:

delete from <table_name> where <condition>

1	delete from <table_name> where <condition>

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

Spread the love