Database basic command for MySQL & PostgreSQL

How to connect AWS-RDS & GCP database of PostgreSQL using terminal or CMD & basic of PostgreSQL & MySQL.

PostgreSQL

helps you easily comprehend PostgreSQL. Through several real-world examples, you’ll quickly become a professional in PostgreSQL. You can then use this knowledge to create apps using PostgreSQL.

• syntax format

psql -h HOSTNAME — port=5432 -U USERNAME DATABASENAME -W
example: psql -h 1.1.1.1 -p=5432 -u root -w

• h = host or RDS Endpoint
  -p = 5432 pre-define port for postgresql
  -u user name for database access
  -w password of the user ( not heat with the single command it will ask after hitting this syntax & passwords are not visible )

Command :

$ sudo -u postgres psql
- for login into the primary user

$ CREATE DATABASE dbname;
- for creating a database

$ create user test with password ‘test123’;
- for creating a user with a password

$ GRANT ALL PRIVILEGES ON DATABASE databasename to dbusername;
- give access to DB with the user

$ DROP DATABASE [IF EXISTS] database_name;
- for deleting the full database

MySQL

• syntax format
  mysql -h [ hostname or IP ] -u [ database username ] -p
  example: mysql -h 1.1.1.1 -u root -p
  Note: password in a new line (invisible form)

Command :

$ create database DATABASE_NAME;
- create a database in MYSQL

$ create user ‘USER_NAME’@’localhost’ identified by ‘PASSWORD’;
$ create user ‘USER_NAME’@’%’ identified by ‘PASSWORD’;
- create a user with a password

$ grant all privileges on DATABASE_NAME.* TO ‘USER_NAME’@’localhost’;
$GRANT ALL ON mydb.* TO ‘myuser’@’%’;
- assign privileges in MYSQL

$ SHOW DATABASES;
- show database table with name

$ SELECT user FROM mysql.user;
- show all available users.

$ SHOW TABLES;
- show all tables in MYSQL

$ SELECT user FROM mysql.user;
- show all MySQL database user lists.

$ DROP USER ‘USER_NAME’@’localhost’;
- delete a user in MySQL