Getting Started With MySQL

Implementing Database in MySQL – Part 1

Foreword: In this part of the series I introduce you to the use of MySQL.

By: Chrysanthus Date Published: 14 Apr 2015

Introduction

This is part 1 of my series, Implementing Database in MySQL. In this part of the series I introduce you to the use of MySQL. I have written a bigger series called, Database. The Database series is in many divisions. Each division has a number of parts. You should have read Division 1, Division 2, and Division 3, (or equivalent tutorials from elsewhere); because this series is a continuation. The previous division dealt with Efficiency in Database Design. The title of the first part of that division is, Referential Integrity in Database. To reach that division, just type the title in the Search Box of this page and click Search. This division is equivalent to division 4. This part of the series is an introductory tutorial on implementing database in the software package, called MySQL.

In the first three divisions, you learned how to design a database. After designing a database, you have to implement it. Implementing means you have to code it for a particular Database Management System (DBMS). There are many database management systems in the market. MySQL is one of them. This series deals with MySQL 5.6 and up.

Operating Systems for this Series
The code samples in this series have been tested with the Windows Operating System. However, all the features in this course are still very applicable to MySQL, used in other operating systems.

Installation
There are three software to install: the Perl Interpreter, The Pureperl MySQL API library and the MySQL server. You do not need to understand the Perl language in order to study this course.

Installing the Perl Interpreter
I use ActivePerl version 5.18. ActivePerl 5.18 is free. To download it, just search the web. During installation, allow the default settings. If you are using the Windows Operating system, make sure something like “C:\Perl\bin;” without the quotes is in the path environment variable. You can access the Windows Path Environment Variable, with the following click sequence:

Start|Control Panel|System|Advanced|Environment Variables|System Variables|Path|Edit

Installing PurePerl MySQL API Command Line Tool
The command line tool is in the API library; it works with the rest of the library. You have to download and install the whole library in order to use the tool. The library to download is in a zipped directory. You download it and you unzip it. You will then see the files, Mysql.pm and Mysqlcom.pl and the directory, Mysql. The file, Mysqlcom.pl is the command line tool.

Installation is easy: copy the files, Mysql.pm and Mysqlcom.pl and the directory, Mysql to the directory, C:/Perl/lib or similar of your computer. That is all! You can then begin to use the API (functions and variables or command line tool). Note: if you are not using the Windows Operating System, then go to the files Mysql.pm and Mysqlcom.pl of your computer, in the installed directory, and at the top of each file, type something like, #!/usr/bin/perl pushing down the rest of the content, by one line. You should know what that statement means. Do the same for all the other files in the C:/Perl/lib/Mysql directory of your computer. Begin work!

The library is free of charge. After reading the agreement, download it at the following link:

PurePerl MySQL API

Installing The MySQL Server
To install MySQL, I double clicked the downloadable file, mysql-5.5.19-win32.msi – you can use a higher version. I allowed the default settings. After the MySQL main installation, the MySQL Sever Instant Configuration Wizard Window opens presenting the second installation phase for MySQL. In this second phase, I chose or allowed the following settings in their different windows : Detailed Configuration, Developer machine, Multifunctional Database, Decision Support, Enable TCP/IP Networking, port 3306, Enable Strict Mode, Standard Character set of Latin1, Install as Windows Service-Server, Name of MySQL, Include Bin Directory in Windows Path, Modify Security Settings-Enter, Your Password.

For MySQL password, you will type the password, sql. Allow your user name as “root”, which is for the database administrator.

The MySQL DBMS Server
For you to create and use a database, you need software called a database server. The MySQL server is an example of a database server. With MySQL, you need to start the server and connect to it using a user name (ID) and a password. Then you can create and/or use your databases. When you have finished your work, you can stop the server. If you are using the Windows Operating System, and you allowed the default settings, then MySQL will always start when you start the computer (as a service). So, whenever you want to test a code sample, make sure your MySQL server is started (running).

If you are not using the windows operating system and the default setting, then you will have to consult the MySQL manual to see how to start the server. The main program for the MySQL server is, mysqld . To start the server means you have to get this program running. To stop (shutdown) the server, you will have to use the tool, mysqladmin – consult your manual for details.

You will be testing (trying) all the code samples in this series. Remember, in order to test any code, the server must be running.

Command Prompt
I use the Windows Operating system. I tell you what I do, and you can do a similar thing to your own operating system. I shall use the command prompt (DOS window) to manipulate the command line tool. In my computer I open the command prompt window with the following clicks: Start|All Programs|Accessories|Command Prompt.

UserID and Password
With the above installation, your username (userID) is, root. This means you are the Database Administrator (DBA). You have total control over the server and any database you create. You password, which is what you typed in is, sql. You could use a different password if you wanted to, but allow this one, for these tutorials.

SQL
SQL stands for Structured Query Language. It is a computer language that is used to create a database and its tables. It is also used to retrieve information from the database. Each software manufacturer such as MySQL has a slightly modified version of the standard language. In this series you will learn SQL of MySQL. Most of the things, learned in this series under SQL are applicable to other manufacturers such a Microsoft. SQL consists of a set of statements, collectively called the SQL Statements.

Note, each SQL statement ends with a semicolon.

Starting the Command Line Tool
I assume that at this point you have opened the Command Prompt (DOS Prompt) window. Type the following and press the Enter Key to go to the root directory:

    cd c:\

Go to the directory, c:\Perl\lib or similar, by typing something like the following and pressing the Enter Key:

    cd c:\Perl\lib

Type the following and press Enter to start the PurePerl MySQL API command line tool.

    Mysqlcom.pl

You should see the prompt:

    Mysql>

That is the prompt of the command line tool, and not the DOS prompt. Any SQL statement you type at this prompt and press Enter, is executed by the file, Mysqlcom.pl, which is the PurePerl MySQL API Command Line Tool.

Connecting to the Server
I assume you are now seeing the Mysql> prompt in the Command Prompt window. The syntax to initiate connection to the server is:

    connect "user", "domain", port;

The quotes may be single or double. The port is a number (integer) without quotes. In this series, your code and the server are in the same computer. So your domain is "localhost", otherwise it would be something like, "yahoo.com" or "google.com". So type the following and press the Enter Key:

    connect "root", "localhost", 3306;

where 3306 is the default MySQL port number. You should see the following:

    Password:

Now, just type the password, sql and press the Enter key.

You should get the feedback:

    Connection made.

or an error message, if connection was not made. Your connection to the server should now be complete. So, to connect to the server, you need the user name, domain name, port number and password.

Closing the Connection
Any connection opened has to be closed. Your connection should have been made. To close it, just type the following and press the Enter key:

    close

You should see:

    Connection closed.

or an error message if the connection could not be closed. That is not all; closing the connection does not mean closing the MySQL Command Line Tool (Mysqlcom.pl). You quit the command line tool; you do not really close the command line tool – see below:

Quitting the Command Line Tool
To quit the command line tool, type the following at the prompt of the command line tool and press Enter:

QUIT

You should see, Bye, confirming that you have quitted the tool; and you are now at the DOS prompt. You can type “quit” without the quotes, in lower or upper case letters.

Quitting the tool, does not mean closing (shutting down) the server – see later.

Note: You do not start MySQL with the Mysql command line tool. You have to start it with some independent means.

Well, we have seen how to connect and leave the server. Let us take a break here and continue in the next part of the series.

Chrys

Broad Network

Related Articles

Getting Started With MySQL

Implementing Database in MySQL – Part 1

Introduction

Related Links

Comments