Back to: Structured Query Language (SQL)
All RDBMS’s are typically set up on a server for ease of multi-user access. These databases are often queried at the backend of websites. Microsoft servers would usually use ASPX and SQLserver, Apache (Linus based) servers would use PHP and MariaSQL (a fork of MySQL, MariaSQL is often referred to as MySQL). Management of the database on an Apache server is done using web software phpMyAdmin.
The XAMPP Stack of Software is an Open-source Localhost-based server encompassing software packages that offer several functionalities usually found on servers. You can use the XAMPP Control Panel to start or deactivate all Software that comes with it.
With XAMPP, you can test projects/modifications before releasing them online. The MySQL Database forms a vital component of the XAMPP Stack, being one of the most significant features offered by it. With XAMPP, you can use phpMyAdmin to create & use your XAMPP MySQL Database in minutes.
This module will provide a full explanation of what XAMPP is and how to use it to build an XAMPP MySQL Database.
What is XAMPP?
XAMPP is an open-source web server solution stack package developed by Apache Friends. It consists of the Apache HTTP Server, MySQL database, and interpreters for PHP and Perl programming languages.
XAMPP makes it easy to create a local web server for testing purposes on your own computer, enabling web developers to easily test their applications during development.
XAMPP can be downloaded from here.
XAMPP – Apache + MariaDB + PHP + Perl
XAMPP, which stands for Cross-Platform, Apache, MySQL, PHP, and Perl, is a free platform that allows developers to test their code locally on their own computers. This platform provides the experience of having your own mini web server at home, compatible with both Windows (WAMP) and Linux (LAMP) environments.
Download XAMPP from https://apachefriends.org for your flavour of computer.https://apachefriends.org
Install the software package.
Run XAMPP and click Start in the column next to the MySQL module.
Once running you will not see anything happen on the screen – to manage databases, click the Admin action. This will launch the phpMyAdmin web application graphical user interface (GUI) that allows you to perform tasks like:
- Creating databases
- Running queries
- Adding user accounts
- Modifying databases
- Deleting databases
- Modifying tables
- Deleting tables
- Modifying records
phpMyAdmin is written in PHP and is one of the most popular tools for administering MySQL databases, especially for web hosting services.
There are some default items listed on the left that we’ll be leaving alone except for the “test” database which you can play around with or delete.
MySQL Workbench
MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. MySQL Workbench provides data modeling, SQL development, and comprehensive administration tools for server configuration, user administration, backup, and much more. MySQL Workbench is available on Windows, Linux and Mac OS X.
Again, Select your Operating System and download this free software package, I used Windows (x86, 64-bit), MSI Installer. (No need to sign up or login, just select “No thanks, start my download“). The usual choices need to be made – Next > to run the install wizard, Next > to select the default install path, Next > to install the Complete package, Next > and Install. Check the Launch…now box and Finish.
A MySQL Workbench dashboard will open and start looking for any databases it can manage – it probably will NOT find any.
Click the “+” symbol to manually add the “test” database. Name the connection
Hostname: 127.0.0.1 is an IP address that is everyone’s local machine, so your computer is the server host.
Port: is the TCP/IP port through which your DB will communicate.
Username: root – is the default username.
Password: Can (and should) be set. In XAMPP click the “Config” button next to MySQL – a text file will open
You can see the default port is 3306, you can uncomment the password requirement and enter a password here.
For now you can leave the password blank and we’ll use the default port – so no need to change anything.
Back to MySQL Workbench –> click Test Connection –> you may see a “Connection Warning” because XAMPP uses MariaDB, so continue anyway and it will work perfectly well. Now you should see the “test” database under MySQL connections.
Click it and you will be able to access your databases, monitor performance, import, export, start, stop and fully manage your SQL databases.
In the bottom left, there are two tabs, Administration and Schema. Click on Schema and you will see all of your publicly accessible databases.
Select test and you can see the Tables, Views, Stored Procedures and Functions associated. These have nothing in them yet.
Clicking the Settings button next to test:
This is mostly informational.
Click the little “i” (information) button next to Settings and you will have access to the actual database, where you can create tables, add information and so on.
Select the Tables tab –> right click anywhere in the white area below and you see the menu to Create Table...
We will create a database in the next lesson!