Database Commands

Software development, Databases

A list of frequently used database commands in both Postgres and MySQL

List Databases

MySQL

show databases

Postgres

\l

Switch Databses

MySQL

use <database>

Postgres

\c <database>

List Tables

MySQL

show tables

Postgres

\dt

Describe Table

MySQL

desc <table name>

Postgres

\d+ <table name>

Dump Database Schema

MySQL

mysqldump -u root -p --no-data <db name> > schema.sql

Postgres

pg_dump -U postgres --schema-only <db name> -f schema.sql

Export Query Results to CSV

Postgres

\copy (select * from my_table limit 10) TO '~/Downloads/export.csv' CSV HEADER

\copy makes the file go to your local machine rather than the database server file system