Databases

MYSQL / MARIADB

Login

# Activate the MySQL CLI (as user with proper permission)
mysql;

# Authenticate into the MySQL CLI
mysql -u MyUser -p;

# Authenticate into a specific database
mysql -u MyUser -p MyDatabase;

Movement and checks

# Switch to a different database
USE MyDatabase;

# Show all databases;
SHOW DATABASES;

# Show all tables within the database
SHOW TABLES;

# Show information about the contents of a table
DESCRIBE users;

# Show indexes and additional information for a table
SHOW INDEXES FROM dogs;

# Exit the MySQL CLI
exit

# List created users
SELECT user FROM mysql.user;

# See privileges for the user johan
SHOW GRANTS FOR johan;

# Create a database
CREATE DATABASE mydb;

# Create a user with a password
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password';

# Give rights to a user on the database
GRANT ALL PRIVILEGES ON mydb.* TO 'admin'@'localhost' WITH GRANT OPTION;

Postgres

# Log into the Postgres CLI
psql

# List all databases
\l

# Connect to a database
-d mydatabase -U databaseuser –W

# Switch to the new database connection
\c mydatabase databaseuser

# List all tables
\dt

# Quit the Postgres CLI
\q