SQL

Structured Query Language

SQL is an easy to understand language to interact with a database.

Basic SQL Commands

-- Show all databases in the system
SHOW DATABASES;

-- Switch to a specific database
USE [DATABASE];

-- Show all tables in the selected database
SHOW TABLES;

-- Retrieve all rows and columns from a table
SELECT * FROM [TABLE];

---

Relational databases

SQLite3

SQLite3 is a lightweight, single-file database suitable for small projects or embedded applications.

Key Commands:

# Open or create an SQLite database file
sqlite3 sqlite3.db

-- List all tables in the database
.tables

-- Query all rows and columns from a table
SELECT * FROM [TABLE];

MySQL

MySQL is a widely-used relational database, especially in web applications. It supports file reading, which requires proper permissions.

Key Commands:

# Log in to MySQL as a specific user (prompts for a password)
mysql -u <USER> -p

-- Read and display the contents of a file (requires MySQL file access permissions)
SELECT LOAD_FILE('/data/test.txt') AS Result;

Password Information:

• Password file location: /var/lib/mysql/mysql/user.MYD

• Hash type: mysql-sha1

---

PostgreSQL

PostgreSQL is an advanced relational database with support for robust features, including file imports.

Key Commands:

# Log in to PostgreSQL as a specific user (prompts for a password)
psql -U <USER> -W

-- List all databases in PostgreSQL
\list

-- Switch to a specific database
\c [DATABASE]

-- List all tables in the current database
\d

-- Create a table with one text column
CREATE TABLE demo (t TEXT);

-- Import file content into the table
COPY demo FROM '[FILENAME]';

-- Retrieve all data from the imported table
SELECT * FROM demo;

---