Using MySQL Database

mysql_cover

MySQL is a popular and open-source relational database application. I recently used MySQL to query some database for my project. I will go step by step to install MySQL on windows and show a short demo using a newly created database.

Installing mysql server for windows

mysql_install

mysql_installation

You need to follow instructions based on the requirement of your computer in order to install MySQL.

root user and password required

MySQL database can be connected using either command line or MySQL workbench

For my small project I used MySQL workbench. However, I will show short commmand lines to connect using command prompt as well.

  • open a command prompt from windows
  • Locate the path where you have installed the MySQL community.
  • This is located usually on the C:\programfiles\MysQL\MysQL Server \bin

Run this command to get access to MySQL database. However, I got an error because I noticed that I had Stopped the MySQL server before.

C:\Program Files\MySQL\MySQL Server 8.0\bin>ls
'ls' is not recognized as an internal or external command,
operable program or batch file.

server_status After starting the MySQL server again and using the same command it works. We need to use the same root password assigned prior installation.

C:\Program Files\MySQL\MySQL Server 8.0\bin>mysql.exe -u root -p
Enter password: ********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Accessing MySQL Workbench

In my experience with the project, MySQL workbench is a great alternative of command-line access. The GUI workbench allows to perform all types of querries, navigate, and check server status.

Navigator section you can select the Administration to check server satus, logs, connection, users and privieleges,etc. On the Schemas section you can view all the database created.

Creating new database

You can see several options on the Menu bar. I will select Create a new schema on the connected server

Now, write down the prefered name for this schema. here, I will use ‘ex_1’ and Click ‘Apply’

You can see the newly created schema on the SCHEMAS section

Performing SQL Querries

I will perfrom some of the querries on the existing database.

  • Select employees who has worked more than 50 hours
use ex_1;

SELECT DISTINCT a.employeeNumber
  • Updating the records and phone number by specific Employee Number 
    UPDATE employee
SET OfficePhone = '818-531-1670', position='HR'
WHERE employeeNumber =16;

We can perform different types of querries on this GUI MySQL workbench. I think it is a great platform for the database administrator to use datbase on day to day basis.