Simple Must know SQL and NOSQL hacks

PostgreSQL Hacks

Install

https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

sudo apt-get update

sudo apt-get install postgresql postgresql-contrib

sudo -i -u postgres

psql

Exit out of the PostgreSQL prompt by typing: \q

createdb test1

 \connect test1

CREATE TABLE table_name (

    column_name1 col_type (field_length) column_constraints,

    column_name2 col_type (field_length),

    column_name3 col_type (field_length)

);

MySQL Hacks

mysql -u root -p myDatabase

show databases;

use myDatabase;

show tables;

select * from table;

Take MySQL dump
mysqldump -u [uname] -p db_name > db_backup.sql

restore data dump
mysql -u root -p devengoDev < db_backup.sql

Reset MySQL Password
https://help.ubuntu.com/community/MysqlPasswordReset

sudo /etc/init.d/mysql stop
sudo /usr/sbin/mysqld --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
SET PASSWORD FOR root@'localhost' = PASSWORD('password');
UPDATE mysql.user SET Password=PASSWORD('newpwd') WHERE User='root';
FLUSH PRIVILEGES;
sudo /etc/init.d/mysql stop
sudo /etc/init.d/mysql start

MongoDB Hacks
Setup
https://docs.mongodb.com/v3.0/tutorial/install-mongodb-on-ubuntu/

sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv EA312927
echo "deb http://repo.mongodb.org/apt/ubuntu trusty/mongodb-org/3.2 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-3.2.list
sudo apt-get update
sudo apt-get install -y mongodb-org
sudo service mongod start
sudo service mongod stop

mongodump -d myDatabase -o ~/backups/first_backup
mongorestore -d myDatabase ~/backups/first_backup

use mydb
show dbs
db.dropDatabase()
db.createCollection("mycollection")
show collections
db.createCollection("mycol", { capped : true, autoIndexId : true, size : 
   6142800, max : 10000 } )
db.mycol.insert({
   title: 'MongoDB Overview', 
   description: 'MongoDB is no sql database',
   by: 'tutorials point',
   url: 'http://www.tutorialspoint.com',
   tags: ['mongodb', 'database', 'NoSQL'],
   likes: 100
})
db.movie.insert({"name":"tutorials point"})
db.COLLECTION_NAME.drop()
db.mycol.find().pretty()
db.mycol.find(
   {
      $and: [
         {key1: value1}, {key2:value2}
      ]
   }
).pretty()
db.mycol.find(
   {
      $or: [
         {key1: value1}, {key2:value2}
      ]
   }
).pretty()
db.mycol.find({"likes": {$gt:10}, $or: [{"by": "tutorials point"},
   {"title": "MongoDB Overview"}]}).pretty()
{
   "_id": ObjectId(7df78ad8902c),
   "title": "MongoDB Overview", 
   "description": "MongoDB is no sql database",
   "by": "tutorials point",
   "url": "http://www.tutorialspoint.com",
   "tags": ["mongodb", "database", "NoSQL"],
   "likes": "100"
}
db.mycol.update({'title':'MongoDB Overview'},{$set:{'title':'New MongoDB Tutorial'}})
db.mycol.update({'title':'MongoDB Overview'},{$set:{'title':'New MongoDB Tutorial'}},{multi:true})
db.mycol.remove({'title':'MongoDB Overview'})
db.mycol.find({},{"title":1,_id:0}).limit(2)
db.mycol.find({},{"title":1,_id:0}).sort({"title":-1})
db.mycol.ensureIndex({"title":1})

db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : 1}}}])
{
   "result" : [
      {
         "_id" : "tutorials point",
         "num_tutorial" : 2
      },
      {
         "_id" : "Neo4j",
         "num_tutorial" : 1
      }
   ],
   "ok" : 1
}

Mongo Clustering

Mongo Sharding