Lab 1: Accessing MySQL/MariaDB Database in Local Machine

Objectives:

• Start XAMPP and MySQL / MariaDB
• Access local MySQL/MariaDB Database using Command Prompt.
• Connect and show in local MySQL/MariaDB. 
• Create and drop database.
• Show and describe table in database.

* this lab exercise is conducted assuming the XAMPP had been installed in local device. If the software package is not available, please download and install the XAMPP software package from https://www.apachefriends.org/download.html.
* This lab is designed for Window Environment. Some instructions may vary depends on the different environment used.

Instructions:

Visit the links based on the objectives above and complete the following tasks:

1. Complete a login to your MySQL/MariaDB server using console.
2. Create a database using MySQL/MariaDB console with your own matric number as the table name. 
3. Delete the newly created database using the drop database command.
4. Show the list of tables in database "mysql" and describe one of the tables in the database.

*Using snipping tools (in windows) or screencapture software to print screen the show database result before and after the task. Save the task title and also the screencapture in a Microsoft Document named "lab1-[matric no]".

Show and describe tables in MariaDB/MySQL Server

A. Connect to one single database

Each database in MySQL/MariaDB can consists of more than one table. If user want to access the content within a selected database. User must select a particular database in the server and inform the server that the user want to perform some operation on the selected database. The command to select the database is:

when a database is selected for operation in the MySQL / MariaDB console, the database name is indicated in the bracket [] before the ">" symbol to input the SQL. It is shown as the highlighted part of the text in Figure 1 below.

Figure 1: Select a particular database for operation in the MariaDB.

B. Show Tables

After selecting a database in the server, user can choose to view the list of tables content in the database. The command for viewing the list of tables is:

similar like the command "show databases", this command will show the entire list of tables in the selected database. The example output is shown in Figure 2 below.

Figure 2: Example output to show tables in a selected database.

C. Viewing The Metadata of a Table.

After knowing the table exist in a selected database, user can view the metadata of the table, which includes the column name, data type, primary key and etc. This will help user to understand the table and able to use the information in developing the information management system. The command to show the metadata of a table is:

The example output of the command is shown in Figure 3 below.

Figure 3: The output of describe command to show the metadata of table "help_topic" in database "mysql".

Create and Drop Database in MySQL/MariaDB

A. Create New Database

Database is the collection of tables in a relational database. The first task in learning how to use a database is to create a new database. In MySQL console, the task to create a new database is done using the following command:

The italic words in bracket [] is the name of the new database. The result of the command in action is show in Figure 1 below.

Figure 1: Create a new database using SQL 

B. Remove an Existing Database

[Beware!! This action cannot be undo. only use the given SQL when you are sure you want to remove certain database!]

Remove database is also part of important task when managing a database. An existing database in the MySQL / MariaDB database can be remove using the following command:

The italic words in bracket [] is the name of the database that the user want to remove. The result of the command in action is show in Figure 2 below.

Figure 2: Removing a database from MySQL/MariaDB

Connect and Show Databases in MySQL / MariaDB

After launching the MySQL/MariaDB console, user are able to access to the content of the database. However, one MySQL/MariaDB can consists of more than one database. User can view the list of databases through the command below:

the result of the command are shown in Figure 1 below:

Figure 1

Notice that there are only 2 databases in the server? This result does not show the actual list of the result because the user have not login to the server. Therefore, the rest of the databases are hidden until user login. To login to the MySQL server, a user require a username and password. the default username for the MySQL / MariaDB in XAMPP are "root" and no password is set.

To login to the MySQL / MariaDB server with username can done by using the command below:

The command "-u" is follow by username, while "-p" indicates the use of password to login. Figure 2 below show the command in action with the default username and password for the MySQL / MariaDB in XAMPP.

Figure 2: Login command in action.

After login to the server with username and password, the same show databases command provide a different output. the new output are shown in Figure 3 below.

Figure 3 Console result to show databases after login.

Notice the extra databases in the list compare to the result in Figure 1 before login? This is the full list of databases that user "root" can view and access.

Accessing MySQL / MariaDB console through Command Prompt

There are two ways to access to MySQL/MariaDB using Command Prompt or Shell Command.
first method is by click on the Shell button on the XAMPP Control Panel, which located on the upper right hand side of the control panel (as circled red in Figure 5).

Figure 5: Shell Button on XAMPP Control Panel

The second method required user to launch command prompt from start button (as shown in Figure 6).

Figure 6: Launching Command Prompt from Start Button (or Window Button).

To launch the command prompt press on start button (or window button) and type "cmd" or "command prompt", click on the command prompt to launch it. After launching the command prompt, the interface as in Figure 7 will appear.

Figure 7: Command Prompt in Windows

then navigate to the XAMPP folder in the local device using "cd.." and "cd" command in the command prompt. "cd.." command is use to navigate out of current folder, while "cd" is to navigate to a sub-folder. while "dir" will list the content of the current folder.

The MySQL/MariaDB console are usually located at C:\xampp\mysql\bin. The navigation to the folder is shown in Figure 8 below.

Figure 8: Navigate to the MySQL bin folder to access the MySQL console function.

After opening the Shell Console through XAMPP Control Panel or Navigated to the MySQL's bin folder using command prompt. Type "mysql" to launch the console. Figure 9 show the console in command prompt while Figure 10 show the console launched from Shell console.

Figure 9: MySQL / MariaDB console launched from Command Prompt.

Figure 10: MySQL / MariaDB console launched from Shell console in XAMPP Control Panel.

Start XAMPP and MySQL/MariaDB

Check at the lower right corner of the screen to check if the XAMPP control panel are running. If it is running, the icon of XAMPP will appear at the corner as in Figure 1.0 below. Double click the icon to launch the control panel.

Figure 1: XAMPP icon at lower right corner of window screen.

If the icon is not available, press the window buttons and search for "XAMPP Control Panel", as shown in Figure 2 below. Click the XAMPP Control Panel to launch it.

Figure 2: Searching for XAMPP Control Panel.

After launching the control panel, the interface similar to Figure 3 will appear on the screen. If the Apache and MySQL label are not highlighted it means the Apache web server and MySQL / MariaDB server is not started.

Figure 3: Interface of the XAMPP Control Panel with Apache and MySQL / MariaDB not started.

Press the start button besides the Apache Web Server and MySQL to start the services. If the services are successfully started, the label of Apache and MySQL will be highlighted as seen in Figure 4 below.

Figure 4: Interface of XAMPP Control Panel with Apache and MySQL started.