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;

Checks and queries

Common

# Show all databases;
SHOW DATABASES;

# Switch to database MyDatabase
USE MyDatabase;

# Show all tables within the database
SHOW TABLES;

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

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

# Exit the MySQL CLI
exit

Users

# List created users
SELECT user FROM mysql.user;

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

Select

# Show all contents of table animals
SELECT * FROM animals;

# Select the first 5 results from animals, but ordered by the breed column
SELECT * FROM animals ORDER BY breed DESC LIMIT 5;

Configuration

# Create a database
CREATE DATABASE mydb;

# Switch to database mydb and create table "syslog" in it, with specific kinds of properties/columns
USE mydb;
CREATE TABLE syslog (
    log_id BIGINT AUTO_INCREMENT,
    year YEAR,
    month TINYTEXT,
    day SMALLINT,
    time TIME,
    severity TINYTEXT,
    facility TINYTEXT,
    client TINYTEXT,
    binaryname TINYTEXT,
    messagecode MEDIUMINT UNSIGNED,
    logmessage TEXT,
    primary key(log_id)
);

# Insert a row with values into the "syslog" table, containing values for each defined column
INSERT INTO syslog (year, month, day, time, severity, facility, client, binaryname, messagecode, logmessage) VALUES ('2024', 'Mar', '19', '11:34:33', 'info', 'syslog', 'pylot', 'rsyslogd', '81615', '[origin software="rsyslogd" swVersion="8.2302.0" x-pid="114103" x-info="https://www.rsyslog.com"] start');

# Delete a table
DROP TABLE syslog;

Users and permissions

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

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

# Allow the monitoring user to login from external IP 1.2.3.4, and give him all rights on database alerts
GRANT ALL ON alerts.* to monitoring@'1.2.3.4' IDENTIFIED BY 'P455w0rd';

# Give user zoey read-only rights on database customers
GRANT SELECT ON customers.* TO 'zoey'@'%' IDENTIFIED BY 'P4ssw0rd';

Postgres

# Log into the Postgres CLI
psql

# List all databases
\l

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

# Switch to the new database connection
\c mydatabase databaseuser

# List all tables
\dt

# Quit the Postgres CLI
\q