Basic Relational Database Concepts/Design, The SQL Language, & The mySQL Database Server

 

A database is a software program which allows for storage and retrieval of information on a computer hard drive’s file system or other device.  A relational database is a database that allows for queries which typically use Structured Query Language (SQL) to store and retrieve data. Relational databases allow for more efficient queries which use less CPU power and memory allocation, as they are optimized for efficiency.  However, connecting to a database is significantly slower than just reading a simple file off of your computer’s hard drive.  The added features of a relational database make this speed decrease worthwhile in many situations.

 

One of the most popular databases used for dynamic web database applications is called mySQL.  It is commonly installed on popular web hosting provider’s Unix and Linux servers.  It is free and easy to use.

 

Data in relational databases is stored in four primary areas: databases, tables, rows, and columns.  A database is an area within the database software that contains all the tables that make up this particular set of data.  For instance, if you wanted to keep track of a web site’s data, you might create a database called “mywebsite”.

 

You’d do this by first logging in to your server through telnet or ssh, and connecting to the mysql server with the following syntax at the command line:

 

mysql –u myusername –pmypassword databasename

 

It’s kind of odd, but mysql requires no space after the –p when specifying the password.  The databasename argument is optional, and defines what database to connect to when you log in to the database.

 

After you have connected successfully, type this in to the command line to create your new database:

 

create database mywebsite;

 

Once you have created a database, you will need to create tables to store various data.  If you have already created a database, and simply want to connect to it, use this syntax:

 

connect mywebsite;

 

Where ‘mywebsite’ is the name of the database you want to connect to.

 

Here is an example of a few tables you may wish to create for an ecommerce web site.  I’ll keep it simple here, but in a real application you might need more fields to store data in.  We’ll assume you are not taking any credit card or payment information for the purposes of this example.

 

1     create table account_info (id integer auto_increment not null primary key, first_name varchar(50), last_name varchar(50), phone_number varchar(12), address1 varchar(100), address2 varchar(100), city varchar(100), state varchar(4), zip_code varchar(10));

2     create table orders (id integer auto_increment not null primary key, account_id integer, product_id integer);

3     create table products(id integer auto_increment not null primary key, product_name varchar(35), sku_number varchar(25), products_in_stock integer);

 

in the above SQL statements, we created 3 different tables.

 

To create a table, you type “create table” followed by the name of the table you want to create.  Then you type an open parentheses “(“.  Then you type in the name of each column you want in the table, followed by the data type of the column you are creating.  When you are done defining the columns you want in the table, you type a close parentheses “)” and  semicolon “;” to end the create table statement.

 

There are various column data types you can use, and I’ll cover the ones I use in building web applications.

 

SQL Column Data Types (these are the names of the data types for the mySQL database)

 

Varchar

 

The varchar column data type allows you to put x number of characters into it.  You define x when you create the table.  If you don’t put enough characers in, the data you insert is automatically trimmed, so there are no spaces left in the data when you do a query and pull it out later.  If you input more characters than the column allows, the data you put in will be truncated.

 

Char

 

The varchar column data type allows you to put x number of characters into it.  You define x when you create the table.  If you don’t put enough characers in, the data you insert is NOT automatically trimmed, as the varchar does, so there ARE spaces left in the data when you do a query and pull it out later.  If you input more characters than the column allows, the data you put in will be truncated.

 

 

Integer

Integer allows you to store an integer, which is a number without decimals.  This is a great column data type to use for storing a unique identification number for each row in a table.

 

Blob

This allows you to store binary data in a column, such as an image or a zip file.

 

Bit

This allows you to enter either a 1 or a 0, for binary true or false computations in your application.

 

Auto Increment ID Fields

You will notice in the create table statements above that the first column is an integer with some extra arguments used.  The first extra argument “auto_increment” tells the database to automatically assign a unique ID higher than the ID used in previous rows of this table.  This is very useful to do, as you want to be able to refer back to specific rows of tables when you are writing applications.  Next, you see the “primary key” argument, which works along with auto_increment.  Finally, you see the “not null” argument, which means that an application using this database will throw an error if you try to insert data into a row in this table and leave the value for this field blank.

 

Other Useful SQL Commands

 

Show

Show can be used to show all database tables in a particular database, or all databases in a particular server.

 

Use this syntax to do so:

 

“show databases”   or

“show tables”

 

Describe

Describe lets you see the structure of an existing database table by typing in this syntax:

 

“describe tablename”

 

Insert

Insert is how you input information into the database.  To do so, you specify a list of database column names in parentheses, followed by the words “ values “ followed by a list of data to insert in single quotes, inside parentheses.  Here is the syntax:

 

insert into orders (account_id, product_id) values (‘5’, ‘874’);

 

We did not need to specify a value for the id field, as it was automatically created for us by our database.

 

SQL commands are typically CaSe INSensitivE.

 

Update

Update lets us update values inside an existing row inside a table.  The syntax is as follows

 

update orders set account_id = ‘25’ where id = ‘334221’;

 

Typically, you will reference a row to be edited by the unique id that is associated with the row, that you created in the create table statement, as described above.

 

Delete

Delete lets us delete rows inside a table.  The syntax is as follows

 

delete from orders where id = ‘334221’;

 

Typically, you will reference a row to be deleted by the unique id that is associated with the row, that you created in the create table statement, as described above.

 

Select

The select statement is perhaps the most complicated but useful statement in SQL.  This is how you do queries on the database to extract data from it.  You specify which fields you want returned, and on what conditions to grab data from the table you specify.

 

Once you get data out of the database after a select statement, you access it using the particular database commands that your programming language supports.  This differs in each programming language.  This will be described for perl using the perl DBI module in the next chapters.

 

Here is an example of a select statement:

 

select id, product_id from orders where account_id = ‘25’;