Your First NT Database

While there is an array of professional products available for purchase and installation - Oracle9i and Microsoft SQL Server to name a few - the cost of these can quickly escalate far beyond the financial means of an SME. The license alone for an Oracle solution can cost hundreds of thousands of dollars, which simply isn’t practical for smaller organizations.

Fortunately, one of the most powerful database solutions is available for free - mySQL. If you are looking to upgrade your site from having to rely upon ponderous Microsoft Access files, but can’t afford the financial investment required to take advantage of Oracle or SQL Server, mySQL is an appealing compromise. In this article, TopHosts will give you the basic knowledge you need to download, install, and configure mySQL on your NT server.

Where do I get mySQL?

mySQL can be downloaded directly from the mySQL website. It is currently available as an installable file for most versions of Windows. As a windows developer, you will also want to download the ODBC (Open DataBase Connectivity) drivers in order to ensure a smooth data transfer from your current database management system (DBMS). Developed by Microsoft, ODBC is a standard database access method that makes it possible to access any data from any application - regardless of what DBMS is handling the data.

Installing mySQL

Installation of mySQL should pose no problems - simply follow the directions in the .ZIP files, and the server and the ODBC drivers will be installed both as a service under Windows NT/2000 and as an independent application under Windows 9x. You now have an operational database system up and running!

Command or GUI?

Although mysql works well in the NT environment, it was originally designed with the unix environment in mind, so to take full advantage of wide range of powerful features mySQL has to offer, one should take the time to become familiar with the command-line interface. However, for new users, it’s easy to become confused when trying to keep track of multiple commands, in which case, there are many GUI (Graphical User Interface) tools available to make basic management of your database a little easier. While there are a wide variety of graphical clients available on the mySQL website, the friendliest tool for new users is DBTools, which is also distributed for free.

As easy to use as Microsoft Access, DBTools allows you to CREATE and DROP databases, tables, columns, indexes, and users on an unlimited amount of servers, all within a user-friendly, Windows-like environment. You can import your existing data from a wide variety of data types, including MS Access, MS FoxPro, Clipper, Dbase, Paradox, Text Files, and any other OBDC Data source.

GUI interfaces are a great way to setup your database quickly, and the drag and drop interface make visualizing relational structures more intuitive. However, in order to fully exploit the full power of mySQL, it’s advantageous to at least familiarize oneself with some basic command-line prompts.

Getting started

When creating a new database, the first thing you should do is setup your root password. Security is a high priority in any environment, especially when dealing with the Internet, and you’re going to want to limit the number of users who can modify the fundamental structure of your database. The default installation of mySQL does not have a password, but it can be setup rather easily. Open up a DOS session, and ensure that you are in the mySQL directory.

At the prompt, type in the following:

mysqladmin -u root password your_password

This gives you root access under the password your_password. You are now ready to start using mySQL!

Command or GUI?

Although mysql works well in the NT environment, it was originally designed with the unix environment in mind, so to take full advantage of wide range of powerful features mySQL has to offer, one should take the time to become familiar with the command-line interface. However, for new users, it’s easy to become confused when trying to keep track of multiple commands, in which case, there are many GUI (Graphical User Interface) tools available to make basic management of your database a little easier. While there are a wide variety of graphical clients available on the mySQL website, the friendliest tool for new users is DBTools, which is also distributed for free.

As easy to use as Microsoft Access, DBTools allows you to CREATE and DROP databases, tables, columns, indexes, and users on an unlimited amount of servers, all within a user-friendly, Windows-like environment. You can import your existing data from a wide variety of data types, including MS Access, MS FoxPro, Clipper, Dbase, Paradox, Text Files, and any other OBDC Data source.

GUI interfaces are a great way to setup your database quickly, and the drag and drop interface make visualizing relational structures more intuitive. However, in order to fully exploit the full power of mySQL, it’s advantageous to at least familiarize oneself with some basic command-line prompts.

Getting started

When creating a new database, the first thing you should do is setup your root password. Security is a high priority in any environment, especially when dealing with the Internet, and you’re going to want to limit the number of users who can modify the fundamental structure of your database. The default installation of mySQL does not have a password, but it can be setup rather easily. Open up a DOS session, and ensure that you are in the mySQL directory.

At the prompt, type in the following:

mysqladmin -u root password your_password

This gives you root access under the password your_password. You are now ready to start using mySQL!

Your first database

It is here that the power and efficiency of the command line becomes apparent. Rather than having to negotiate through a menu structure, you can create a new database with a few simple commands. From the mySQL directory, enter the following:

CREATE DATABASE my_first_dbase;

You must end all mySQL commands with a semicolon. If performed correctly, you should see an output similar to that found below:

Query OK, 1 row affected (0.01 sec)

This command created an empty database called my_first_dbase. To check this, type the following from the mysql> prompt:

+———————+

Database

|———————|

my_first_dbase

+———————+

Creating Tables

Obviously, for your database to do anything, it has to contain some tables. Like a spreadsheet, mySQL organizes data into an array of rows and columns, the sum of which is called a “table.” An E-commerce site would have a table for customer data, purchases, and inventory (among many others), with various rows and columns to specify the pertinent information for each of these three groups. These tables can be assigned a data type (numeric for example), as well as a “primary key” that allows data to be changed on many tables simultaneously according to pre-set conditions - which will be covered in a later article. For now however, let’s focus on the basics - which remain very simple. To create tables for customer data, purchases, and inventory, type in the following commands. Remember that pressing “Enter” does not execute a command unless it ends with a semicolon, which allows you to execute more than one command simultaneously.

CREATE TABLE customer_data

CREATE TABLE purchases

CREATE TABLE inventory;

Once again, verifying that these tables have been created is a simple matter:

SHOW TABLES FROM my_first_dbase;

You should see a list of tables available in the my_first_dbase database:
customer_data, purchases, and inventory

You’ve just created a very general structure for purchase tracking, in under 5 commands!