Blog

Setting up a Database Environment Installing MySQL, PostgreSQL, etc.

Setting up a Database Environment Installing MySQL, PostgreSQL, etc.

Manually setting up a SQL database management software environment on your local computer involves installing database management system software (DBMS) such as MySQL, PostgreSQL, or any other RDBMS database software on your local computer, device, or server. Installation steps may vary slightly depending on the database user’s chosen software system, but they generally involve downloading the DBMS or RDBMS software from the official website, installing it on your local computer, manually configuring or customizing it, and ensuring it runs properly.

Setting up a Database Environment Installing MySQL, PostgreSQL, etc.

So, let’s set up the database environment for both MySQL and PostgreSQL based on different operating systems on your computer.

Setting up/Installing MySQL database software on a Windows computer.

  • Installing MySQL database software on a Windows operating system.
  • First, download the MySQL installer based on your Windows version.
  • Now manually navigate to the MySQL software download page.
  • Select the MySQL software installer here. (For the Microsoft Windows installer, select the “Windows (x86, 32-bit), MSI installer” version.)

Run the Windows (x86, 32-bit) installer.

  • After downloading the MySQL software, double-click it to run the installer.
  • During the MySQL software installation process, select MySQL Server in the MySQL product category to install the database system.
  • Remember, MySQL Workbench is a GUI tool for interacting with MySQL for Windows, which is also recommended for easy database management for a new user.

Configure the MySQL server on the local computer.

  • The MySQL software installer will guide you through setting up the MySQL server database configuration, including setting the root password for the DBMS system. (Don’t forget to add this).
  • With this, you can select Development Computer as the configuration type to optimize performance for local database use.
  • You can also configure MySQL Server as a Windows service for automatic startup.

Complete the MySQL software installation.

  • After installing MySQL on your computer, launch the MySQL Workbench application or use the command-line tool (mysql -u root -p) to connect to the MySQL Server.
  • Test the live software by connecting to the MySQL database software using the DBMS root password you set earlier.

Setting up the PostgreSQL database software on your local computer.

  • Installing the PostgreSQL DBMS software on the Microsoft Windows operating system.
  • First, download the PostgreSQL software.
  • Now go to the PostgreSQL software download page on the official website.
  • Next, download the Microsoft Windows PostgreSQL installer version from EnterpriseDB.

Run the PostgreSQL installer on your computer.

  • Properly follow the steps in the PostgreSQL installation wizard on your computer. Select the components category to install PostgreSQL, and set a password for the PostgreSQL superuser (postgresql).
  • Next, select the port number (default: 5432) and set up the installation directory on the system.

Complete the PostgreSQL installation.

After PostgreSQL installation, launch the pgAdmin (a graphical interface for PostgreSQL) version application or connect from the command line.

Use this method to connect to PostgreSQL as root or via the terminal.

psql -U postgres

Using MySQL and PostgreSQL after installation on a local computer.

After installing MySQL or PostgreSQL software on your computer, you can create new databases, tables, and apply multiple database operations using their command-line tools or graphical tools (such as MySQL Workbench for MySQL and pgAdmin for PostgreSQL) or GUI tools.

Creating a MySQL database.

MySQL.

CREATE DATABASE test_database;

Creating a PostgreSQL database.

CREATE DATABASE test_database;

Listing databases in MySQL.

MySQL.

SHOW DATABASES;

PostgreSQL.

\l

Creating tables in MySQL and PostgreSQL.

Creating a table in MySQL database software.

CREATE TABLE employe (

id INT PRIMARY KEY,

emp_name VARCHAR(110),

address VARCHAR(140),

emp_age INT

);

Creating a table in PostgreSQL database software.

CREATE TABLE employe (

id SERIAL PRIMARY KEY,

emp_name VARCHAR(130),

contact int,

age INT

);

Inserting data into MySQL and PostgreSQL.

MySQL Table Data Insert command.

INSERT INTO employe (emp_name, emp_age) VALUES (‘siddhi deora’, 21);

PostgreSQL table data insert command.

INSERT INTO employe (emp_name, age) VALUES (‘Harry deora’, 24);

Leave a Reply