PostgreSQL Commands

MySQL and PostgreSQL Commands Cheat Sheet

Install MySQL Server

sudo apt update
sudo apt install mysql-server
sudo mysql_secure_installation
        

Start MySQL Service

sudo systemctl start mysql
        

Enable MySQL Service on Boot

sudo systemctl enable mysql
        

Log in to MySQL

mysql -u root -p
        

Show Databases (MySQL)

SHOW DATABASES;
        

Create a Database (MySQL)

CREATE DATABASE dbname;
        

Use a Database (MySQL)

USE dbname;
        

Show Tables in a Database (MySQL)

SHOW TABLES;
        

Create a Table (MySQL)

CREATE TABLE tablename (
 id INT AUTO_INCREMENT PRIMARY KEY,
 column1 VARCHAR(255) NOT NULL,
 column2 INT NOT NULL
);
        

Show Table Schema (MySQL)

DESCRIBE tablename;
        

Insert Data into a Table (MySQL)

INSERT INTO tablename (column1, column2) VALUES ('value1', 10);
        

Select Data from a Table (MySQL)

SELECT * FROM tablename;
        

Update Data in a Table (MySQL)

UPDATE tablename SET column1 = 'newvalue' WHERE id = 1;
        

Delete Data from a Table (MySQL)

DELETE FROM tablename WHERE id = 1;
        

Administrative Commands (MySQL)

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'username'@'localhost';
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'localhost';
DROP USER 'username'@'localhost';
        

Backup and Restore (MySQL)

mysqldump -u root -p dbname > dbname_backup.sql
mysql -u root -p dbname < dbname_backup.sql
        

Performance and Maintenance (MySQL)

SHOW STATUS;
SHOW PROCESSLIST;
OPTIMIZE TABLE tablename;
CHECK TABLE tablename;
REPAIR TABLE tablename;
        

Security Commands (MySQL)

ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
SELECT user, host FROM mysql.user;
EXIT;
        

Additional Useful Commands (MySQL)

SELECT NOW();
SELECT VERSION();
        

Install PostgreSQL

sudo apt update
sudo apt install postgresql postgresql-contrib
        

Start PostgreSQL Service

sudo systemctl start postgresql
        

Enable PostgreSQL Service to Start on Boot

sudo systemctl enable postgresql
        

Switch to the PostgreSQL User

sudo -i -u postgres
        

Basic PostgreSQL Commands

psql
\l
CREATE DATABASE dbname;
\c dbname
\dt
CREATE TABLE tablename (
 id SERIAL PRIMARY KEY,
 column1 VARCHAR(255) NOT NULL,
 column2 INT NOT NULL
);
\d tablename
        

Data Manipulation Commands (PostgreSQL)

INSERT INTO tablename (column1, column2) VALUES ('value1', 10);
SELECT * FROM tablename;
UPDATE tablename SET column1 = 'newvalue' WHERE id = 1;
DELETE FROM tablename WHERE id = 1;
        

User Management (PostgreSQL)

CREATE USER username WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;
DROP USER username;
        

Backup and Restore (PostgreSQL)

pg_dump dbname > dbname_backup.sql
psql dbname < dbname_backup.sql
        

Performance and Maintenance (PostgreSQL)

SELECT datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit FROM pg_stat_database;
SELECT * FROM pg_stat_activity;
ANALYZE tablename;
VACUUM FULL tablename;
        

Security (PostgreSQL)

ALTER USER username WITH PASSWORD 'new_password';
\du
\q