Example Queries

About Connection

Connect to Database

MySQLPostgreSQL

mysql -u USERNAME -h HOSTNAME -P PORT -p

psql -U USERNAME -h HOSTNAME -p PORT

About Database

Create Database

MySQLPostgreSQL

CREATE DATABASE `DB_NAME`;

CREATE SCHEMA "DB_NAME";

Use Database

MySQLPostgreSQL

USE `DB_NAME`;

\c "DB_NAME"

List Databases

MySQLPostgreSQL

SHOW DATABASES;

\dn

Delete Database

MySQLPostgreSQL

DROP DATABASE `DB_NAME`;

DROP SCHEMA "DB_NAME";

About User

Create User

MySQLPostgreSQL

CREATE USER `USER_NAME`@`%` IDENTIFIED BY 'USER_PASSWORD';

CREATE USER "USER_NAME" WITH ENCRYPTED PASSWORD "USER_PASSWORD";

About Grant

Grant to User

MySQLPostgreSQL

GRANT ALL PRIVILEGES ON `DB_NAME`.* TO `USER_NAME`@`%`;

FLUSH PRIVILEGES;

GRANT ALL PRIVILEGES ON DATABASE "DB_NAME" TO "USER_NAME";

About Table

Create Table

MySQLPostgreSQL

CREATE TABLE `DB_NAME`.`TABLE_NAME` (
    IDX INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(255),
    OTHER_ID INT,
    INDEX NAME_IDX(NAME),
    FOREIGN KEY (OTHER_ID) REFERENCES `OTHER_TABLE_NAME` (OTHER_TABLE_COLUMN_NAME) ON UPDATE CASCADE
);

CREATE TABLE "DB_NAME"."TABLE_NAME" (
    IDX INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(255),
    OTHER_ID INT,
    FOREIGN KEY (OTHER_ID) REFERENCES "DB_NAME"."OTHER_TABLE_NAME" (OTHER_TABLE_COLUMN_NAME) ON UPDATE CASCADE
);

CREATE INDEX "INDEX_NAME" ON "DB_NAME"."TABLE_NAME" (COLUMN_NAME);