Database Design and SQL. Introduction to MySQL

SQL tutorial

Previous page Previous     TOC Contents     Next pageNext

Lesson 3B - Introduction to MySQL

Database servers

In the previous lesson we started looking at the SQL language syntax. I used an Access database to illustrate how to test our SQL commands against a real database.

You have to understand that Access is not typical of the database environment you will probably work with in the real world.

For one thing, it's meant to work in standalone mode (with one user) or, at most, shared among 4-5 users. Access is not a database server.

Also, in Access you don't get to see the SQL code very much. It's always there but it's behind the scenes, written by the various wizards and hidden behind forms or reports or QBE queries.

For real databases (and by that I mean really big or with many users) you will normally work with a database server. A database server is software that resides on a large computer with good communications capabilities. It stores the data, handles the maintenance of the tables and responds to the demands of clients who want to manipulate the data.

The best-known server is Oracle. It can handle any database from a few dozen users to thousands of users.

Oracle's biggest competitor in the really big applications is SAP.

Then, for small to medium jobs you've got SQL Server from Microsoft, which is sort of the big brother to Access.

All these products are based on what we call the relational model. They all store the data in tables, they have primary keys and they build relationships between the tables.

And they all use the SQL language to communicate between the server and the clients. Some of the servers have modified SQL somewhat for their own use but, it's essentially the same language for everyone.

The MySQL Database Server

You may have guessed from the previous paragraph that you would have to spend a considerable amount of money to get a powerful database server. And you would be correct, if you only look at the ones mentionned.

There is an alternative. It's called MySQL. And to set the record straight, just so that you've got the jargon down, this one is always pronounced "my ess-queue-el".

The MySQL database server is part of the whole open source environment, under the GNU licence. That means that it's free! You download it, install it, use it and you can even contribute to its development if you want.

Even though it's free, that doesn't make it less capable. It's very powerful and very stable and constantly being improved by a vast army of dedicated volunteer developers.

In fact, one of the reasons why you should learn MySQL is that it's used as the server of choice by most Internet hosting companies that provide eCommerce support. By that I mean that if you have an Internet application that requires access to a database for price lists or shipping or anything else, and you don't have your in-house Web server, the database you will use is probably MySQL.

The rest of the lessons in this tutorial have already been written with Access as the database for the examples. I will update the lessons to show how to use MySQL to write the exercices.

Installing MySQL

Read this whole section before you do the downloads!

To begin the installation, you can go to the MySQL Developer Zone to download the software and to look at the documentation.

Choose the MySQL Community Server version of the MySQL database server. The Enterprise version is meant for large companies.

Note that MySQL was originally written to run on the Linux platform. But now, the Windows version is just as good as the Linux and you won't have any trouble whatsoever running on Windows.

When you get to the download area, go down to the Windows section and select the package with the installer.

Installation is fairly straightforward. It should proceed automatically with only a few questions.

When you install the server, I suggest that you stick with the default values. There is only one that could cause you some difficulty: it assumes the install drive is C:. If your main drive is other than C: you will have a few details to modify later.

If you have questions about any platform you wish to use, read the documentation.

Go to MySQL Developer Zone - Documentation. I would suggest you look at the latest version that says "HTML online " and Bookmark it in your browser. You'll come back to it over and over again! The MySQL documentation is the best I've seen for any software. The installation process is well-documented and if you go to the bottom of each section you'll see a forum with comments from users on various problems encountered.

This is the section of the docs you'll probably go to first when you install.

Setup is really straightforward. I recommend that you use all the default values. And if you're running Windows, do choose to start it as a Windows Service so that you won't have to worry about starting and stopping the server all the time. It will just run quietly in the background.

Once you've got the server installed, download and install the MySQL Workbench.

That's a GUI tool to manage connections and Windows services, etc. You will use that to test your SQL queries and also, you can use it to import and export databases on the server.

While you're at it you might as well download the latest ODBC Connector (which is 5.1.8 at the moment). You will need that to connect between Access and MySQL databases.

Download the GUI clients

Finally, you'll need a client to interface with the server.

You have to understand that, unlike Access, MySQL does not have a built-in interface for you to create tables, run queries and so on. Most people who use MySQL connect to it from a Web server through PHP or a Java program or even a Visual Basic front-end.

There are two excellent clients that you can download for free. Get them both and try them out.

The first is DBManagerFreeware/Standard Edition from DBTools Software.

The other is called SQLyog Pro.

My personal preference is DBManager, so that's what I'll use for the examples in the following lessons.

If you haven't found the database resource you're looking for,
use our Google Search box for more information.



Home | Tutorials | Contact | Sitemap | Add URL | Privacy policy