Mastering your SQL commands (Part 1)

Table Of Contents

• Introduction
• Creating your database
• Adding information
• Retrieving information
• Updating information
• Removing information

Introduction

One of the key elements of a dynamic website is a database that feeds data to it. Structured query language (SQL) is a language used to read and write data out and into a relational database. A relational database is simply a database whose tables can link to each other through a special column known as a primary key. This is used to match a record from one table to a related record in another (or other) tables. The most popular relational databases includes MySQL, PostgreSQL, SQLite and Oracle.

Let's pretend you are hired by a bookstore manager. The manager tasks you with the duty of creating a website to assist in tracking of their book inventories. During your planning, you come up with a database structure consisting of four tables:

Creating the Database

First, youre going to create the database and tables in MySQL. Start up your MySQL client at the command line, or use another program you feel comfortable with.

shell$ mysql

That command will bring up the MySQL monitor, which should look like this (if you’re using the
command-line client):

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.11-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql>

You may be required to provide a username and password to access the MySQL Client. If so just add -u username -p at the end of the mysql command, and MySQL will prompt you for the password:

shell$ mysql -u username -p

Enter the following commands to create the database,which we will call “BookInventory”:

mysql> CREATE DATABASE BookInventory;

Now that you’ve created the database, you need to set it as the active database for the remainder of your
queries:

mysql> USE BookInventory;

Next, we create the tables.The basic SQL statement to create a table in MySQL is:

CREATE TABLE tablename (column_definitions);

We are going to be created four tables to hold the book informations:

mysql> CREATE TABLE Names (book_id int PRIMARY KEY AUTO_INCREMENT, name varchar(100) );
mysql> CREATE TABLE Editions (book_id int PRIMARY KEY AUTO_INCREMENT, edition varchar(200), make_id int );
mysql> CREATE TABLE New_Books (book_id int PRIMARY KEY AUTO_INCREMENT,
book_id int, editionyear int, price decimal(10,2), color varchar(200), description
text );
mysql> CREATE TABLE Used_Books (book_id int PRIMARY KEY AUTO_INCREMENT,
edition_id int, modelyear int, price decimal(10,2), color varchar(200), certified tinyint, warranty tinyint, description text );

So, you simply use the MySQL CREATE TABLE command, followed by the name of the table,
and then list the column names and types in parentheses. Also note that an auto-incrementing primary
key is added to each table, to help you uniquely identify rows after when added.

Adding Information

With your tables created, you can add data to them using the following syntax:

INSERT INTO tablename (column_list) VALUES (new_values);

Now, lets apply that to seed our Names table:

INSERT INTO Names (book_id, name) VALUES (1, ‘Javascript’), (2, ‘Dart’), (3,‘CSS’), (4, ‘PHP’), (5, ‘Python’);

In MySQL, you can specify acomma-delimited set of column values, instead of just one set, to populate multiple rows at once.
Normally, you might leave out the make_id column when inserting into the Names table—the autoincremented ID would automatically assign them a number. In this case, you specify the ID so the rows
you’ll soon add to other tables will have valid foreign keys.

Use the following to add data to the rest of the tables:

INSERT INTO Editions (book_id, edition, name_id) VALUES (1, ‘1st’, 1), (2,
‘4th’, 2), (3, ‘8th’, 3), (4, ‘5th’, 4), (5, ‘6th’, 5);
INSERT INTO New_Books (book_id, editionyear, color, description) VALUES
(1, 2012, ‘Red’, ‘First Edition Javascript, must learn!’),
(NULL, 2008, ‘Dark Brown’, ‘Last of the 2008 stock! Must sell!’),
(4, 2015, ‘Radiant Red’, ‘Learn PHP’),
(5, 2018, ‘Orange’, ‘Learn Modern Python’),
INSERT INTO Used_Books (book_id, editionyear, color, price, certified,
warranty, description) VALUES
(1, 2000, ‘White’, 200, 0, 0, ‘Good condition; One owner’),
(2, 2003, ‘Dark Green’, 540, 0, 1, ‘Excellent condition,’),
(3, 2004, ‘Yellow’, 175, 1, 1, ‘Limited edition;’),
(4, 1993, ‘Blue’, 1500, 0, 0, ‘In decent shape, still precious’);

Retrieving Information

You have seen how to populate the tables with data. However most times you will also want to read information out of the database. In such cases, you will use the SELECT statement. A simplified form of SELECT looks something like this:

SELECT [column_names] FROM [table] WHERE [criteria]

If you wanted to retrieve a list of the colors of your used books, for example, you would use something like this:

SELECT color FROM Used_Books;

which would return the following:

Or perhaps, you want a listing of all the warranty status for books older than the 2005 edition year:

SELECT warranty, edition year FROM Used_Books WHERE modelyear < 2002;
Running this query shows the following:

Updating Information

To update information you use the UPDATE statement. The generic form of an UPDATE statement looks like this:

UPDATE [table] SET [column]=[value] WHERE [criteria]

Now, lets update the color tuple in Used_Books
UPDATE Used_Vehicles SET color='Grey' WHERE book_id=1;
In this case, the Book ID of 1 corresponds to the automatically incremented ID assigned to the used 1st edition Javascript you added in a previous INSERT statement.

Deleting Information

Making of sales require the removal of
some vehicles from your database. In this situation, you will use the DELETE statement:

DELETE FROM [table] WHERE [criteria];

That used 1st edition Javascript that had its incorrect color fixed in the previous example sold today. To
remove if from the Used_Books table, you would simply do the following:

DELETE FROM Used_Books WHERE vehicle_id=1;

With that, we have covered the basics of SQL queries. Remember that this post is the first of three post in the series. Simply save this page for future references.

Follow me on twitter at UbahTheBuilder

Later