Database commands cheat sheet
Table of Contents generated with DocToc
- PostgreSQL
- Redis
- MongoDB
- Connect to a DB
- Switch/Use DB
- Show collections in DB
- Copy docs from remote db collection into current db
- Copy database
- Delete docs from collection that donโt match criteria
- Export collection
- Find docs that have a certain attribute defined
- Sort in reverse natural order (most recently created first)
- Neo4j
PostgreSQL
Connect via psql
$ psql -h <hostname> -p <port> -U <user> <db_name>
Create User and DB
$ psql -U <username>
DROP DATABASE <dbname>;
CREATE USER <username> WITH PASSWORD '<password>';
CREATE DATABASE <dbname> OWNER <username>;
CREATE DATABASE optimization OWNER tactical;
Drop DB
$ dropdb -U <username> <dbname>
Show schemas - psql cli
\dn
Show tables - psql cli
\dt
Show tables in a schema
\dt <schema_name>.
Quit postgres prompt
postgres-> \q
Generate dump
pg_dump -h <server_hostname> -U <username> -f db.dump <db_name>
Import .dump
psql -h <server_hostname> -U <user> <db_name> < <PATH TO DUMP>
Redis
Flush all keys
$ redis-cli -c -h $redis_endpoint -p $port flushall
MongoDB
Connect to a DB
$ mongo --host localhost -u <username> --authenticationDatabase <dbname> -p <paassword>
Switch/Use DB
> use admin;
switched to db admin
Show collections in DB
> show collections;
mycollection
system.users
system.version
Copy docs from remote db collection into current db
db.getSiblingDB("remotedb").runCommand(
{
cloneCollection: "remotedb.curriculumNodes",
from: "remote.host:27017",
query: { }
}
)
Copy database
db.copyDatabase("original-db-name", "original-db-name-backup")
Delete docs from collection that donโt match criteria
db.getCollection('usergroup').deleteMany({uuid: {$nin: ['b068c3ac-6a1d-4500-9121-18a6ef980b71']}})
Export collection
mongoexport --db test --collection traffic --out traffic.json
Find docs that have a certain attribute defined
db.getCollection('user').find({google: {$exists: true}})
Sort in reverse natural order (most recently created first)
db.getCollection('session').find({}).sort({$natural: -1})
Neo4j
Delete all nodes
match (n) detach delete n
List all nodes
match (n) return n
Count nodes
match (n:Person) return count(n) as count
Find nodes with a property value
match (n:Student) where n.name = 'Anurag' return n
Find nodes with a specific attribute and relationship attribute
match (subject)-[:IS_SUBJECT_OF*]->(curriculum)
where
curriculum.name='Philippines Curriculum' and
subject.importKey='29/05/19 philippines curriculum'
return subject
Find nodes with a specific attribute and a path (of may length 3) to a node with certain attribute
match (n)-[*0..3]->(curriculum:Curriculum)
where
curriculum.name='Philippines Curriculum' and
n.importKey='29/05/19 philippines curriculum'
detach delete n
OR
match(n { importKey:"29/05/19 philippines curriculum"}) - [* 0..3] -> (curriculum: Curriculum {uuid:"lol"})
detach delete n