Table of Contents
SQL is a standardized language for database management. This article is a “cheat sheet” of sorts, helping you with the sematics of database engines such as MySQL and PostgreSQL.
MySQL
MySQL is one of the most popular and oldest database software still around today in modern hosting. MariaDB is a community-developed fork of MySQL which works almost exactly the same, but with additional security and stability improvements.
Basic operation
To enter the MySQL prompt, run mysql.
Creating a user
CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Creating a databse
CREATE DATABASE database_name;
Granting permissions
use database_name;
GRANT ALL ON database_name.* TO 'username'@'localhost';
Listing existing databases
SHOW DATABASES;
Deleting a database
DROP DATABASE database_name;
PostgreSQL
PostgreSQL is a more extensible SQL database, common in use with lots of server software like PeerTube and Matrix Synapse.
Basic operation
The psql prompt is only accessible through the postgres user, so firstly switch to using it:
sudo su postgres
Creating a user
User creation in PostgreSQL is done outside of the psql prompt. The createuser command is used, by the postgres user:
createuser --pwprompt username
Deleting a user
dropuser username
Dumping a database
pg_dump -U username database_name > file.sql
Restoring from a file
psql -U username database_name < file.sql
Some PostgreSQL operations are performed by the postgres user, such as:
Some operations are done from the prompt. Run this command to enter the PostgreSQL prompt:
psql
Listing existing databases
Simply run \l in the psql prompt to list existing databases.
\l
Creating a database
Just like in regular SQL, the CREATE DATABASE command is used, but the syntax for ownership is different:
CREATE DATABASE database_name
OWNER username;
Deleting a database
Just like regular SQL, run DROP DATABASE:
DROP DATABASE database_name