💾:Server/Sql

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 'YOUR_PASSWORD';

Creating a databse

CREATE DATABASE YOUR_DATABASE;

Granting permissions

use YOUR_DATABASE;
GRANT ALL ON YOUR_DATABASE.* TO 'USERNAME'@'localhost';

Listing existing databases

SHOW DATABASES;

Deleting a database

DROP DATABASE YOUR_DATABASE;

PostgreSQL

PostgreSQL is a more extensible SQL database.

Creating a user

User creation in PostgreSQL is done outside of the psql prompt. The createuser command is used, by the postgres user:

sudo su postgres
createuser --pwprompt USERNAME

Basic operation

The 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:

Creating a user

createuser --pwprompt USERNAME

Deleting a user

dropuser USERNAME

Some operations are done from the prompt. Run this command to enter the PostgreSQL prompt:

psql

Creating a database

Just like in regular SQL, the CREATE DATABASE command is used, but the syntax for ownership is different:

CREATE DATABASE YOUR_DATABASE
OWNER USERNAME;

Dumping a database

pg_dump -U YOUR_USER YOUR_DATABASE > file.sql

Restoring from a file

psql -U YOUR_USER YOUR_DATABASE < file.sql