The first step to get an application (program, website etc.) to work against a database is knowing the 5 basic SQL commands. The following examples is based on MySQL, but should apply to every SQL-server. When mastering these 5 commands, you are off to a good start on SQL programming.

Create a tabel

First we have to create a new database called ‘articles’ and it will contain 2 fields – a numeric article id and text field for the article content. This is done with the CREATE TABLE-command:

CREATE TABLE `articles` (
`article_id` INT NOT NULL ,
`article_content` TEXT NOT NULL
)

Running the above code will create a database called articles with two fields article_id and article_content.

The database table is now ready for use.

Inserting into a table

To insert data into to newly created database is done with the INSERT-command. When using the INSERT-command, you must specify which table the content should be inserted to and which fields to insert it into.

The INSERT-command is used with this syntax:

INSERT INTO `table` (fields) VALUES (values);

table, fields and values must be replaced with the appropriate names, like this:

INSERT INTO `articles` (`article_id`, `article_content`) VALUES ('1', 'First article...');

This will insert an article to the articles-table, with an article id value of ’1′ and a content ‘First articles…’.

Let insert a couple more articles:

INSERT INTO `articles` (`article_id`, `article_content`) VALUES ('2', 'Second article...');
INSERT INTO `articles` (`article_id`, `article_content`) VALUES ('3', 'Third article...');

Selecting from a table

The articles-table is now holding 3 articles with id 1, 2 and 3. To retrieve article information from the table, we have to use the SELECT-command.

Selecting content from the database is done by using this syntax:

SELECT `field` FROM `table` WHERE search;

Now – let select the article content for article no. 2:

SELECT `article_content` FROM `articles` WHERE article_id='2';

This will return a result set with the article content from article no 2.

Selecting from table can of course be must more advanced than this, but this will be covered in a later blog entry.

Updating a table

Now we know how to insert and select data in the database. At some point it’s often necessary to change the content of the tables. Changing values is done with the UPDATE-command.

UPDATE `table` SET `field`=`value` WHERE search;

So, to change the content of article no 3 from ‘Third article’ to ‘yet another article’ use a command like this:

UPDATE `articles` SET `article_content`='yet another article' WHERE `article_id`='3'

This will update the article_content-field to ‘yet another article’ if the article_id is equal ’3′.

Delete from table

The last command for now is the DELETE-command. This is used, when content in the database should be deleted and is used like this:

DELETE FROM `table` WHERE search;

The article no 1 is now going to be deleted and this is done with the following line:

DELETE FROM `articles` WHERE `article_id`='1';

That’s it. Article deleted.

Ending

The 5 basic SQL command has now been explained and I hope this will be useful when starting using databases. Every command can be expanded to be much more advanced than what I have shown here.

For further reading on how to create a basic login script with PHP and MySQL-database – go here