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
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.
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
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.
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.
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 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.
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:
Go to the directory, c:\Perl\lib or similar, by typing something like the following and pressing the Enter Key:
Type the following and press Enter to start the PurePerl MySQL API command line tool.
You should see the prompt:
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:
Now, just type the password, sql and press the Enter key.
You should get the feedback:
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:
You should see:
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:
To quit the command line tool, type the following at the prompt of the command line tool and press Enter:
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.
Related LinksImplementing Database in MySQL
Programming in MySQL
Backup Basics in MySQL
MySQL Access Privileges
Regular Expressions in MySQL
Date and Time in MySQL
Event in MySQL
PurePerl MySQL API Prepared Statements
More Related Links
PurePerl MySQL Command Line Tool
Major in Website Design
Perl Course - Optimized
Web Development Course