Skip to content

GettingStarted.md

Latest commit

 

History

History
182 lines (127 loc) · 5.54 KB

GettingStarted.md

File metadata and controls

182 lines (127 loc) · 5.54 KB
Raw

Let’s go step by step. PostgreSQL, Oracle, SQL Server, and MySQL: how to set them up and when to use each. And practical recommendations.

1. PostgreSQL

Overview:

  • Open-source, advanced relational database.
  • Known for standards compliance, extensibility, and reliability.
  • Supports JSON, geospatial data, and complex queries.

Setup Steps (Windows Example)

  1. Download

  2. Install

    • Run the installer.
    • Select components: PostgreSQL server, pgAdmin (GUI), Stack Builder (optional).
    • Set password for the default user postgres.

  3. Verify Installation

    • Open pgAdmin.
    • Connect to your server using username postgres and the password you set.

  4. Create Database

    • Right-click Databases → Create → Database.
    • Give it a name and save.

  5. Run Queries

    • Use pgAdmin query tool or connect via CLI:
    psql -U postgres -d your_database

Use Cases:

  • Applications needing complex queries and data integrity.
  • Analytics, GIS applications (PostGIS), financial systems.
  • When you prefer open-source but enterprise-grade features.

2. Oracle Database

Overview:

  • Enterprise-grade database.
  • Excellent transaction management, scalability, security, and support.
  • Often used in banks, telecoms, and large corporations.

Setup Steps (Windows Example)

  1. Download

  2. Install

    • Run the installer and follow prompts.
    • Choose desktop class for easy setup.
    • Set administrative password (SYS/SYSTEM user).

  3. Verify Installation

    • Open SQL*Plus or Oracle SQL Developer (GUI tool).
    • Connect using username SYS as SYSDBA or SYSTEM.

  4. Create Database / Schema

    • Oracle usually creates a default database on install.
    • Create schemas or tablespaces via SQL Developer or commands:
    CREATE USER myuser IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO myuser;

  5. Run Queries

    • Use SQL Developer GUI or SQL*Plus CLI.

Use Cases:

  • Large enterprise systems with high security, high concurrency, or mission-critical applications.
  • ERP, banking, telecom, government data systems.

3. Microsoft SQL Server

Overview:

  • Microsoft’s enterprise relational database.
  • Known for integration with Windows/.NET, BI tools, and management studio.

Setup Steps (Windows Example)

  1. Download

  2. Install

    • Run the installer → choose Basic or Custom.
    • Set authentication mode: Windows Authentication or Mixed Mode.
    • Create a password for sa (system admin).

  3. Install SQL Server Management Studio (SSMS)

    • Separate download from Microsoft.
    • SSMS allows GUI-based management, queries, and reports.

  4. Verify Installation

    • Open SSMS → Connect to server → Run a test query:
    SELECT @@VERSION;

  5. Create Database

    • Right-click Databases → New Database.

Use Cases:

  • Windows-centric environments.
  • Applications requiring integration with Microsoft stack.
  • Enterprise reporting, dashboards, and transactional systems.

4. MySQL

Overview:

  • Open-source, very popular for web applications.
  • Simple to set up, fast, widely supported.
  • Owned by Oracle now, with forks like MariaDB.

Setup Steps (Windows Example)

  1. Download

  2. Install

    • Run installer → choose Developer Default.
    • Set root password.
    • Choose to start MySQL as a service (recommended).

  3. Verify Installation

    • Open MySQL Workbench (GUI).
    • Connect with username root and password.

  4. Create Database

    CREATE DATABASE mydb;
USE mydb;
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    email VARCHAR(50)
);

  5. Run Queries

    • Use Workbench, CLI (mysql -u root -p), or connect via applications.

Use Cases:

  • Web apps, small to medium businesses.
  • CMS platforms (WordPress, Joomla, Drupal).
  • Applications needing speed and easy deployment.

5. Recommendations / Quick Guide

Database Strengths Ideal Use Cases Notes
PostgreSQL Open-source, ACID, JSON, GIS Analytics, financial apps, geospatial Best open-source option for complex queries
Oracle Enterprise-grade, secure, scalable Banks, telecoms, ERP Heavyweight, ideal for large enterprises
SQL Server Windows integration, BI tools, GUI Windows apps, dashboards, enterprise Great for MS ecosystem; free dev edition available
MySQL Fast, lightweight, web apps Websites, CMS, small apps Very common for web hosting and cloud apps