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 a table in it
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 using specific values for each column
INSERT INTO auth (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