Table of Contents
SQL is a standardized language for database management. This article is a “cheat sheet” of sorts, helping you with the semantics of database engines such as MySQL and PostgreSQL.
Note on Encoding
While a manual server setup will normally ensure a proper locale is installed, some VPS providers and default server ISOs may need further configuration to make sure all data is encoded correctly on your server. Before installing any database engine, ensure your server is using the proper encoding locale.
Simply run the command dpkg-reconfigure locales and select the desired locale from the text menu. en_US.UTF-8 is the default for the english-speaking US.
Reboot for these changes to take effect.
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 database
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