Introduction

With the constant expansion of web applications and user driven web sites it becomes a necessity to have a way to store data in an organised fashion which can be retrieved and manipulated on the fly; something which is impossible with static content. In order to achieve this, a database must be used! A database in its simplest form is the collecting of data in an organised fashion whether it is using a filing cabinet or a computerised database. Integrating a database into your web site or web application allows you to store and retrieve data using specific commands. For this article we will be using a relational database management system package called MySQL. This is open-source software available under the GNU general public licence which of course makes it free of charge! The MySQL web site can be found at http://www.mysql.com/ any word that you see underlined in this article indicates a MySQL function you can find full documentation on these functions on the MySQL web site.

TIP: To search the MySQL web site for a function, simply append the function name to the MySQL URL. E.G. http://www.mysql.com/SELECT this example would search for select. I have produced a modified version of Jesse Ruderman's Google search bookmarklet in order to search the MySQL 5.1 documentation. To use this simply highlight the underlined word then press the bookmarklet to be taken to the search results. Alternatively launch the bookmarklet with nothing highlighted to be given a prompt box asking you to enter what you wish to search. Bookmarklet: MySQL SEARCH

Understanding a database

In order to store data in a database firstly a database must be created. This database can then store many tables (imagine a filing cabinet storing many files); each table must have defined columns, and of these columns are created to store specific data (imagine a data entry form). It is possible to limit what can be inputted into these such as numeric data or character limits for example. Once data has been entered into the database it will be stored in an appropriate table, the table will then consist of rows and columns much like a spreadsheet as data is saved and displayed in a tabular form. MySQL can manage multiple databases which can contain multiple tables. Access levels can be granted to different users to provide or revoke specific privileges. MySQL uses commands/functions based on SQL (structured query language). This allows us to use keywords to specify what data we wish to return. MySQL then implements some additional features to enhance the functionality available. Keywords are generally easy to understand and often relate to an English word or phrase (E.G. SELECT, UPDATE, WHERE).

Getting Started

In this installment we will cover:

• Installing MySQL on your local machine(windows)
• Configuring your local MySQL installation(windows)
• Connecting to your local database
• Connecting to a remote database
• Entering and formatting queries
• Formatting and logging results
• Backing up a database
• Restoring a database

In order to use MySQL it is necessary for us to have it installed whether it be on our local system or on a remote web host. However in order to connect to either we must firstly have an interface to use.

Today we will be using the essentials package which is available on the MySQL web site at http://dev.mysql.com/downloads/. At the time of writing the current stable version is 5.1 and I will be discussing how to install it on the windows operating system. Firstly locate the software which you require, I will be using the 64 bit windows version but you may choose otherwise depending on your system architecture. For this article I have chosen to use the essentials package as it includes all the features needed. You can compare the differences between the windows versions at http://dev.mysql.com/doc/refman/5.1/en/windows-choosing-package.html.

Installing MySQL on your local machine

Once you have downloaded the appropriate installation executable, launch it and you will be prompted with the above window. As you can see I am installing version 5.1.30. Press Next to progress onto the next screen.

I have chosen to keep with the typical installation, however feel free to choose what components you want to install. You will need the MySQL server and data files if you are working on your local machine; however if you have a remote database available to you that you wish to use you can choose not to install these features. You will need the MySQL command line shell as this is the application that we will be using. Again if you are not using a local database you can skip the Command line utilities and server instance configuration. This article will not cover using C with MySQL however if you are developing using C you may choose to install this. Press Next to progress onto the next screen.

You will be then asked to confirm your choices, press Install if you are happy otherwise press Back to make any changes.

Your installation will start, at this point you may get an interruption from an anti virus or Windows Vista User Account Control, it will state that a program is asking to be installed or access specific files. This will be signed by MySQL AB; this is the company which operates and maintains MySQL which you can read about at http://www.mysql.com/about/.

Once installed, make sure you have the Configure the MySQL Server now (if installed) ticked and press finish. Congratulations, you have successfully installed MySQL Server/components. Next the MySQL server Instance Configuration Wizard should load. However if it doesn't launch don't worry, you can access it in the MySQL programs folder (if installed).

Configuring your local MySQL installation

We are now going to configure your installation (if you installed the server), press Next to progress onto the next screen.

You now have the option of using a standard configuration; however I will be using a detailed configuration which will allow me to customise different aspects of my installation.

I have chosen this as a developer machine; however you may choose which option you prefer. The developer machine will allow full usage however it will prevent too many resources being used. Press Next to progress onto the next screen.

As this installation is intended for development purposes and not for a specific project, I chose a multifunctional database which allows use the InnoDB and MyISAM storage engine. You can read more about storage engines at http://dev.mysql.com/doc/refman/5.1/en/storage-engines.html. Press Next to progress onto the next screen.

You must now select where you want to store the InnoDB datafile, depending on storage space you have available you may wish to change this if you have a larger capacity or faster drive available. Press Next to progress onto the next screen.

You must now set up the amount of concurrent connections that you are going to have connecting to your database at any one time. Choosing DSS allows up to 100 connections but assumes an average of 20 concurrent connections. OLTP allows up to 500 concurrent connections. However for our developer machine we are often only going to be making a single connection; therefore I set the manual setting to 5. Press Next to progress onto the next screen.

You now are given the choice to customise TCP/IP settings. I have un-ticked the checkbox in order to disable this. By disabling this it prevents remote connections to the database. You can then set the server mode, I left this box ticked (you can read more about server modes in detail at http://dev.mysql.com/doc/refman/5.1/en/server-sql-mode.html). Press Next to progress onto the next screen.

We now need to select what character set/encoding we are going to be using. I chose UTF8 as it allows different languages to be inputted (international readers). Press Next to progress onto the next screen.

You now need to decide if you want to run the server as a service, you can customise the service name by the drop down box. By installing MySQL as a service by default the server will be started automatically and will restart in even of failure. I recommend using the setting above. You also get the option to include the MySQL Bin files in the windows path. This allows us to call MySQL directly from the command line, I also recommend doing this. Press Next to progress onto the next screen.

You now have to provide a password for the root account. This is the 'master' account with full privileges (the account username will be 'root'); I do not recommend leaving this blank! The choice to enable access from root machines is un-ticked by default; I kept it this way as I had disabled TCP/IP settings but it also proves a higher security risk potential if details fall into the wrong hands. I also disabled the choice to enable an anonymous account as this database is intended for personal use. Press Next to progress onto the next screen.

Your installation configuration is now ready to be applied, press execute to start the process.
You should then receive a successful configuration message. Once completed, press finish to exit this wizard. Your have now successfully completed the configuration.
If you are installing MySQL on a different operating system or if you wish to refer back to the official documentation it can be found at http://dev.mysql.com/doc/refman/5.1/en/installing.html.

Connecting to your local database

Now that you have the server installed we can now connect to it!

I am going to be using the Windows CLI (command line interface) to initialise the connection, but you may use the MySQL CLI found in the MySQL programs folder. Firstly open a command prompt by opening the run dialog box found on the start menu and then type CMD and press [ENTER]; upon which you will be displayed a window similar to the one below.

The first command that we are going to type is going to connect to the server with our user name and password.
There are several ways of connecting to the server all of which do the same task. The MySQL connection command uses the following syntax:

mysql [host] [port] [username] [password] [database]

Each of these arguments can be passed to the mysql command as follows:

• --host=host or -hhost
• --port=port or -P
• --username=username or -uusername
• --password=password or -ppassword
• database is given simply as a string (E.G. db_name)

Note: By supplying the full password string like this it will be visible on screen, if you prefer you can simply supply the -p or -password command with no password to be presented with a hidden password entry prompt. We also don't need a command terminator as we are typing into the Windows CLI at the moment not MySQL.

So as we wish to connect to our local computer so we can eliminate the host argument (see connecting to a remote host later in this article). We want to log in as our root account to give us full privileges, so we are going to need our username and password; as we currently haven't got any databases set up we will not supply the database argument either. I will be connecting to the "root" user account on my local machine with the password of "secret" using the following command:

mysql -uroot -psecret

This connects to MySQL using the root user with a password of secret. The following commands will achieve the same result.

mysql --username=root --password=secret

mysql -uroot --password=secret

mysql --username=root -psecret

The user account 'root' is the master MySQL account which was created during the installation; this is the same user that you should be connecting as. However please note that it is not recommended to leave a root account turned on a web installation, please refer to user accounts and privileges at the MySQL web site for further information.(http://dev.mysql.com/doc/refman/5.1/en/adding-users.html)

Connecting to a remote database

(skip this if you are connecting to a local database) In order to connect to your remote database, often supplied by a hosting provider, you must know the IP address or host name of your database (and possibly the port number if it has been changed the default is 3306). You would connect to this as you would a local database just replacing the host and port arguments depending on your hosting configuration.

Disconnecting

To disconnect from the MySQL session send the QUIT (or \q)command.

QUIT

Formatting queries and results

Now that we are connected (after receiving "Welcome to the MySQL monitor." message) we are ready to send our commands however there are a few other points to note.

MySQL allows use of whitespace without affecting the commands entered. This comes in handy in making our queries as readable as possible (see the image below). Both of these queries produce the same result, however the second one is significantly easier to read.

Note: This query will not work as we do not have a database set up this is for demo purposes only!
You can see the code I used below, both of the queries entered are identical however by pressing [ENTER] the CLI allows us to continue the command on a separate line. MySQL has implemented a feature to cancel an input if your command spans multiple lines, simply issue the clear command "\c".

SELECT fld_1longlonglong, fld_2longlonglong, fld_3longlonglong, fld_4longlonglong, fld_5longlonglong, fld_6longlonglong, fld_7longlonglong FROM tbl_name WHERE fld_1longlonglong = `datavalue`;

This however also means that a special command must be sent to indicate that it is then end of the command. We can use either of the following:

• ;
• \g
• \G

The first two terminators are identical, however the third one when used within a query that returns data shows the result in a vertical table rather than horizontal. This is great to bear in mind if we are returning information from a table which has many columns as it may become unreadable.

Restoring a Database

Next we will be importing a SQL file, this is the same process that you would do if you were restoring a database backup. This file simply has all the commands necessary to make a duplicate of the database at the given backup time. Ideally we would make our own database, however making a database can be a complex subject including relationships, naming conventions, storage engines and field settings which is beyond the scope of this article.

We are going to be using a pre-prepared database available at http://dev.mysql.com/doc/#sampledb scroll down until you see the example databases and you want to go ahead and download the world database (world.sql).

Once connected to MySQL we are going to restore the world database as it is a backup of a previous database. This file is simply a list of MySQL commands which will be run to make the new database. This SQL file only contains table data and does not store database information, therefore we must first create a database to store the tables in (refer to the analogy at the start of the article). Note: To save the length of this article I will be placing multiple commands in the code snippets, you can separate these commands or issue them together lookout for the command terminators shown previously in this article. Also be aware that MySQL functions are not case sensitive so SELECT is the same as select and SeLEcT. I personally capitalise MySQL functions in order to make my queries as easy to read as possible, as queries get longer and more complex this becomes a valuable feature alongside whitespace.

To make the database we are going to issue the CREATE DATABASE command, once created we are then going to USE the database.

CREATE DATABASE db_world; USE db_world;

Time to import the backup, there are many ways of doing this, its known as batch processing (http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html). Since we are connected to MySQL already we are going to use the SOURCE command followed by our file name. I have extracted the "world.sql" file from the zip folder and placed it on my C: drive root you will need to know the absolute path to your source file. If you only provide your file name MySQL will look for source in the folder you launched your command prompt from (C:\USERS\USERNAME on vista C:\Documents and Settings\Username on XP). You cannot browse to a different directory while using the MySQL interface so make sure you supply the appropriate absolute path. After issuing this command the CLI window will issue each of the commands within the SQL file, once completed you will be returned to the mysql> prompt.

SOURCE C:\world.sql;

No you have a fully functional database at your disposal. However we do not yet know what it contains! Let's do a little bit of diving around; we are going to use the following commands:

• SHOW TABLES; - this shows the tables in the current database.
• DESCRIBE tbl_name; - this shows what fields exist in the specified table.

SHOW TABLES;

After issuing the command we now know that the database contains three tables: "city","country" and "countrylanguage". Next we are going to find out what fields these tables store. You want to issue the DESCRIBE tbl_name for each of the tables.

After issuing the DESCRIBE command for the country table you will see below that it returned a unreadable mess, to solve this simply use the \G terminator instead of ;.

DESCRIBE city;
DESCRIBE country;
DESCRIBE country \G
DESCRIBE countrylanguage;

Now we have all the information that we need in order to manipulate the tables as we wish, we have the database username, password, database name, table names and field names if you are developing in a different language such as PHP this is the information you would require to return data to your system (see http://php.net/mysql).

Logging results

We can choose to log output of the MySQL interface; we do this by sending the \T filename.txt command. In order to stop logging we issue the \t command. The below example saves a file called log.txt to the root directory of the E: hard drive. On my machine this is a spare hard drive, please note you must have permission to access this drive; you will be unable to write to your windows installation drive root on Windows Vista without launching MySQL from an elevated command prompt.

\T E:\log.txt
SHOW TABLES;
\t

E:\log.txt contents:
mysql> SHOW TABLES;
+--------------------+
| Tables_in_db_world |
+--------------------+
| city               | 
| country            | 
| countrylanguage    | 
+--------------------+
3 rows in set (0.00 sec)

mysql> \t

Backing up a database

The MySQL monitor comes with an extension called mysqldump; as you might expect dumps the necessary information from the database to be able to make an exact replica. The mysqldump command uses the following syntax:

mysqldump [username] [password] [database name] > [dump file]

To launch this you should be disconnected from the MySQL session.
The following code logs into the MySQL session with the root user account with the password secret, it then dumps the table db_world to the file db_worldbak.sql.

mysqldump -uroot -psecret db_world > db_worldbak.sql

Conclusion

After reading this article, I hope you have gained a firm understanding of how to interface with mysql on a command line level. Something which can prove useful as commands can be sent directly to the MySQL database rather than via another scripting language. Please do not stop here, http://dev.mysql.com/doc/ really is a great resource and in no time you will be making you own database. Below I have written some basic queries to search the world database and some examples for you to try. Lets see if you can understand them, if not again you know where the documentation is located.

SELECT Name, Population
FROM city
WHERE CountryCode='GBR'
ORDER BY Population ASC
LIMIT 0,5\G

In English this query SELECTS and returns the Name and Population FROM the table city WHERE the country code is GBR, the results are then filtered using ORDER BY Population ASC (ascending population) and is then LIMITed to return the first 5 results. This query shows the name and population of the least 5 populated cities in Great Britain.

And one more for the road…

SELECT
CONCAT(city.Name,' speak the ', countrylanguage.Language, ' language')AS Detail,
city.Population
FROM city, countrylanguage
WHERE city.CountryCode = countrylanguage.CountryCode
AND city.countryCode = 'GBR'
AND countrylanguage.Language = 'English'
ORDER BY city.Population ASC
LIMIT 5,5 \G

In English this query SELECTs data, it CONCATenates Name from the city table and Language from the countrylanguage table and displays the results AS Detail the query also SELECTs Population from the city table. Again this query SELECTs its data FROM the city table and the countrylanguage table, this returns data where the city's country code is the same as the country code of the country language. The data is filtered to display countries WHERE there country code is GBR AND the Language is English, this is then ORDERed BY increasing population, and results are LIMITed to show the results from 6 to 10. This query is known as an INNER JOIN, as two tables are linked together within the query. This query will display the 6th to the 10th least populated cites with the GBR country code which speak English.

How did I know what data to write those queries for you ask! By running the following SELECT query it uses the wild card character to select all the fields and return all of the data in the specified table. From the result of the query I got an understanding of what was included and I made some scenarios which I wanted to query.

SELECT * FROM tbl_name;

Here are a few functions which you may want to look at in more detail to get started:

• SELECT
• FROM
• WHERE
• LIMIT
• ORDER BY
• AND
• OR
• CONCAT
• SHOW DATABASES
• USE
• CREATE
• SHOW TABLES
• DESCRIBE
• UPDATE

Lookout for a follow up tutorial on retrieving and manipulating data. Feel free to ask any questions in the comments and I will do my best to answer them. Make sure you start a question with #Q so I can find them!

• Subscribe to the NETTUTS RSS Feed for more daily web development tuts and articles.