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 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.
To enter the MySQL prompt, run
CREATE USER 'USERNAME'@'localhost' IDENTIFIED BY 'YOUR_PASSWORD';
CREATE DATABASE YOUR_DATABASE;
use YOUR_DATABASE; GRANT ALL ON YOUR_DATABASE.* TO 'USERNAME'@'localhost';
DROP DATABASE YOUR_DATABASE;
PostgreSQL is a more extensible SQL database.
User creation in PostgreSQL is done outside of the
psql prompt. The
createuser command is used, by the
sudo su postgres createuser --pwprompt USERNAME
psql prompt is only accessible through the
postgres user, so firstly switch to using it:
sudo su postgres
Some PostgreSQL operations are performed by the
postgres user, such as:
createuser --pwprompt USERNAME
Some operations are done from the prompt. Run this command to enter the PostgreSQL prompt:
Just like in regular SQL, the
CREATE DATABASE command is used, but the syntax for ownership is different:
CREATE DATABASE YOUR_DATABASE OWNER USERNAME;
pg_dump -U YOUR_USER YOUR_DATABASE > file.sql
psql -U YOUR_USER YOUR_DATABASE < file.sql