SQL Cheatsheet(MySQL, PostgreSQL)

SQL Cheatsheet(MySQL, PostgreSQL)

October 14, 2020
Cheatsheets
★ Star me on GitHub

Created by Cyanhall.


CLI Login

mysql -h localhost -u root -p
psql -h localhost -p 5432

import an SQL file

mysql -u username -p database_name < file.sql
psql -d database_name -U username -f file.sql

List all databases

show databases;
\l # l for list

List all tables in a database

use [db name];
show tables;
\c [db name]; # c for connect
\dt

List all users

SELECT User, Host, authentication_string FROM mysql.user;
\du

Create user and database

# Create a user
CREATE USER 'db_user'@'localhost' IDENTIFIED BY 'password';

# Create a database
CREATE DATABASE example_db;

# Grant privileges
GRANT ALL ON example_db.* TO 'db_user'@'localhost';
# Create a user
CREATE ROLE db_user LOGIN PASSWORD 'password' NOINHERIT CREATEDB;

# Create a database
CREATE DATABASE example_db;

# Grant privileges
GRANT ALL PRIVILEGES ON DATABASE example_db TO db_user;

Delete user and database

# Delete database
DROP DATABASE example_db;

# Delete user
DROP USER 'db_user'@'localhost';
# Delete database
DROP DATABASE example_db;

# Delete user
DROP USER db_user;

Quit CLI

quit
\q