Redis

Redis Playground

https://try.redis.io/

Note: This try redis io is community version on web so few commands might not work. To use redis’s full power you can download and install on your local system


Redis is an in-memory data structure store, used as a distributed, in-memory key–value database, cache and message broker, with optional durability. Redis supports different kinds of abstract data structures, such as strings, lists, maps, sets, sorted sets, HyperLogLogs, bitmaps, streams, and spatial indices.

Installation

To install redis on windows download msi package from here
https://github.com/microsoftarchive/redis/releases/

For Linux Distr

sudo apt-get install redis-server

To install PHPRedis client use following command

sudo apt-get install php-redis
#for specific version
sudo apt-get install php7.4-redis

To verify redis installed in your system
Note: On windows you might need to add redis exe path in environment variables

redis-cli PING

To see all keys

KEYS *

DATATYPES

  • string
  • list
  • set
  • sorted set
  • hash
  1. String
    Syntax to set string value
SET key value [EX seconds] [PX milliseconds] [NX|XX]

To set only string value

SET keyname value

To check whether key exist or not

EXISTS keyname

Get key’s value

GET keyname

To delete any specific key

DEL keyname

To delete all keys

FLUSHALL

SET string with expiry

SET key value
EXPIRE key expiry-time-in-seconds
#shortcut
SET key value EX expiry-time-in-seconds

To check expiry time of any key

TTL key

SET string values using key:spaces
This is required when you want to set values related to one entity

SET key:space value

2. List or Stack

To set list use LPUSH

LPUSH key value1
LPUSH key value2
LPUSH key value3
OR
LPUSH key value1 value2 value3

To get length of list

LLEN key

To access any value of list you need to use LRANGE

LRANGE key from-index to-index

Use -1 in to-value to get all elements from list

LRANGE key 0 -1

To get specific index value
NOTE this will act like stack so LIFO algorithm will apply here i.e. index 0 will give you last inserted/pushed value

LINDEX key 0

LPUSH and RPUSH (left and right push)

LPUSH key value
RPUSH key value

LPOP and RPOP

LPOP key
RPOP key

Remove specific element from list

LREM key count value

3. Sets

Set is similar to list the main difference is it does not allow duplicate members
Order is not maintained in set

To add member in sets

SADD key value1 value2 value3

To view members of sets

SMEMBERS key

To remove member from sets

SREM key member

To get length of Sets

SCARD key

Compare Sets

SINTER set1 set2
SDIFF set1 set2
SUNION set1 set2

4. Sorted Sets

Sorted set are similar to sets the major difference is it stored members based on score i.e. sorted by score

ZADD key score member

To add multiple members

ZADD key rank1 member1 rank2 member2

To get count of sorted sets

ZCOUNT key

To See all members of sorted sets

ZRANGE key 0 -1

To get rank(index) of specific member

ZRANK key member

To get score of specific member

ZSCORE key member

To remove member from sorted sets

ZREM key member

5. Hashes

Hash is used to set field value pair it is similar to associative array

To set hash

HGET key field1 value1
HGET key field2 value2

To get field value

HGET key field

To get all field values

HGETALL key

To get length of hash key

HLEN key

To add field values at once

HMSET key field1 value1 field2 value2 field3 value3

To get values at once

HMGET key field1 field2 field2

To delete specific field from hash

HDEL key field1 field2

Assignment (set and get values of each type)

  1. Create string name age and city and set value in it
  2. Create List of fruits, weekdays, planets and set values in it
    Print length and values of list
  3. Create Sets of employees and managers
    Print length and values of Sets
  4. Create Hash record1, record2, record3 and add fields name age city in each hash
    Print length and values of each hash

Reference Links

SQL Cheat Sheet

login to database

mysql -u username -p'password' database_name

logout from database

Ctrl + D

list all database

SHOW DATABASES;

Create Database

CREATE DATABASE company;

enter database

USE database_name;

show current database (dual is dummy/virtual database provided by oracle)

SELECT DATABASE() FROM dual;

list all tables

SHOW TABLES;

list table pattern

SHOW TABLES LIKE '%table_substring%';

Create table

CREATE TABLE users(
    id INT,
    NAME VARCHAR(100) NOT NULL,
    age TINYINT NOT NULL,
    city VARCHAR(200) NOT NULL
);

Alter table

ALTER TABLE
    users MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
ALTER TABLE
    users ADD added_at DATETIME AFTER `city`,
    ADD updated_at DATETIME AFTER added_at;

show schema

DESC <table_name>;
SHOW CREATE TABLE <table_name>\G;

show running sql processes

SHOW FULL PROCESSLIST;

import database/table (RUN IN BASH TERMINAL)
NOTE: Make sure your database is present in mysql server if not create new one

mysql -u root -p'password' database_name < backup_file.sql

export database (all tables) (RUN IN BASH TERMINAL)

mysqldump -u root -p'password' database_name > backup_file.sql

export specific tables (RUN IN BASH TERMINAL)

mysqldump -u root -p'password' database_name tbl1 tbl2 tbl3 > backup_file.sql

export only schema without data

mysqldump -u root -p'password' database_name --no-data 

run sql command in terminal

mysql -u root -p'password' -e "SELECT COUNT(*) FROM database_name.table_name"

copy table

CREATE TABLE copy_of_table AS SELECT * FROM existing_table_name;

copy only table structure

CREATE TABLE copy_of_table AS SELECT * FROM existing_table_name WHERE 1 > 2;

Create new database user

CREATE USER 'user'@'hostname' IDENTIFIED BY 'PassWord';

To give remote access

GRANT ALL ON database_name.* to 'database_username'@'10.24.96.%' IDENTIFIED BY 'database_password';

CRUD SQL

SELECT * FROM users ORDER BY id DESC;

DELETE FROM users WHERE id = 3;

INSERT INTO users (id, name, age, city, added_at, updated_at) VALUES (NULL, 'sonam gupta', 18, 'gorakhpur', NOW(), NOW());

UPDATE users SET name = 'Sonam Gupta', age = 20, city = 'Gorakhpur', updated_at = NOW() WHERE id = 5;

Ref Links:

MongoDB Cheat Sheet

MongoDB is a source-available cross-platform document-oriented database program. Classified as a NoSQL database program, MongoDB uses JSON-like documents with optional schemas.

to start server (mongod is mongo deamon)

mongod

to start shell

mongo
show dbs;
create/use database

Change database

use db_name;

show current database

db;

Collections are similar to table

show collections;
db.createCollection("students");

Drop collection/table

db.students.drop();

drop database

db.dropDatabase();

insertOne

db.students.insertOne({_id: 1, name: "shailesh"});

insertMany

db.students.insertMany([{_id: 2, name: "bagde"}, {_id: 3, name: "sheldon"}]);

count document

db.students.count();

select all documents/records

db.students.find();

select with projection

db.students.find({},{_id: 0});
db.students.find({},{name:0});

exists operator

db.students.find({age: {$exists: false}});

COMPARISON OPERATORS

in operator

db.students.find({_id: {$in: [2,3]}});

not equal operator

db.students.find({_id: {$ne: 2}});

equal operator

db.students.find({_id: {$eq: 2}});

less than $lt

less than equal $lte

greather than $gt

greater than equal $gte

LOGICAL OPERATORS

$and operator

db.students.find({$and : [{_id : {$gt:1}}, {_id: {$lt: 4}}]})

$or operator

db.students.find({$or : [{name : "bagde"}, {name: "shailesh"}]})

like case insensitive

db.students.find({name: /h/i})

exact match

db.students.find({name: /^BagDe$/i})

UPDATE

db.students.updateOne({_id: 1}, {$set : {age: 23}});
db.students.updateMany({age : {$exists: false}}, { $set : {age : 25}})
db.students.updateMany({}, {$set: {address: {city: "Mumbai", state: "MH"}}})
db.students.updateOne({_id: 4}, { $set : {address : {city: "Nagpur"}}});

Unset/Delete Fields

db.students.updateMany({}, {$unset : {city: ""}});

search within sub document

db.students.find({"address.city" : "Nagpur"}).pretty();

Delete Records

db.students.deleteOne({ $or : [{ age : {$lt : 25}}, {name : "sheldon"}] })

DISTINCT

db.students.distinct(“city”)

ORDER BY

db.students.find().sort({name: -1})

LIMIT

db.students.find().limit(10)
db.students.find().skip(5).limit(10)

Aggregation

Aggregate on all documents
db.emps.aggregate([{$group: {
	_id : null,
	total : {$sum : "$salary"},
	average : {$avg: "$salary"},
	min : {$min: "$salary"},
	max : {$max: "$salary"},
	count : {$sum: 1}	
}}]).pretty()


Aggregate on group by address
db.emps.aggregate([{$group: {
	_id : { address: "$address" },
	total : {$sum : "$salary"},
	average : {$avg: "$salary"},
	min : {$min: "$salary"},
	max : {$max: "$salary"},
	count : {$sum: 1}	
}}]).pretty()

ForEach Map

db.emps.find().forEach(x => { print(x.name) })

db.emps.find({salary: { $exists: -1 } }, {"salary": 1, _id: 0}).map(x => x.salary * 0.10)

MongoDB Assignment for Practice

var docs = [
{"name":"neha","contact_number":"9833910534","address":"mumbai","salary":30000,"employee_id":98821,"role":"manager"},
{"name":"mina","contact_number":"9833910535","address":"thane","salary":32000,"employee_id":98823,"role":"sales"},
{"name":"pankaj","contact_number":"9833910536","address":"bhopal","salary":40000,"employee_id":98824,"role":"hr"},
{"name":"mareena","contact_number":"9833910537","address":"meerut","salary":45000,"employee_id":98825,"role":"support"},
{"name":"pooja","contact_number":"9833910538","address":"delhi","salary":50000,"employee_id":98826,"role":"developer"},
{"name":"namita","contact_number":"9833910539","address":"surat","salary":52000,"employee_id":98820,"role":"sales"},
{"name":"sneha","contact_number":"9833910510","address":"baroda","salary":55000,"employee_id":98827,"role":"support"},
{"name":"anjali","contact_number":"9833910511","address":"ahmedabad","salary":60000,"employee_id":98828,"role":"tester"},
{"name":"harsha","contact_number":"9833910512","address":"mumbai","salary":20000,"employee_id":98829,"role":"operations"},
{"name":"varun","contact_number":"9833910512","address":"mehsana","salary":56000,"employee_id":98831,"role":"tester"},
{"name":"preeti","contact_number":"9833910513","address":"noida","salary":87000,"employee_id":98832,"role":"developer"},
{"name":"madhu","contact_number":"9833910525","address":"bangalore","salary":22000,"employee_id":98833,"role":"sales"}
];

db.createCollection("emps");
db.emps.insertMany(docs);
db.emps.find();

var docs = [{“name”:”neha”,”contact_number”:”9833910534″,”address”:”mumbai”,”salary”:30000,”employee_id”:98821,”role”:”manager”},{“name”:”mina”,”contact_number”:”9833910535″,”address”:”thane”,”salary”:32000,”employee_id”:98823,”role”:”sales”},{“name”:”pankaj”,”contact_number”:”9833910536″,”address”:”bhopal”,”salary”:40000,”employee_id”:98824,”role”:”hr”},{“name”:”mareena”,”contact_number”:”9833910537″,”address”:”meerut”,”salary”:45000,”employee_id”:98825,”role”:”support”},{“name”:”pooja”,”contact_number”:”9833910538″,”address”:”delhi”,”salary”:50000,”employee_id”:98826,”role”:”developer”},{“name”:”namita”,”contact_number”:”9833910539″,”address”:”surat”,”salary”:52000,”employee_id”:98820,”role”:”sales”},{“name”:”sneha”,”contact_number”:”9833910510″,”address”:”baroda”,”salary”:55000,”employee_id”:98827,”role”:”support”},{“name”:”anjali”,”contact_number”:”9833910511″,”address”:”ahmedabad”,”salary”:60000,”employee_id”:98828,”role”:”tester”},{“name”:”harsha”,”contact_number”:”9833910512″,”address”:”mumbai”,”salary”:20000,”employee_id”:98829,”role”:”operations”},{“name”:”varun”,”contact_number”:”9833910512″,”address”:”mehsana”,”salary”:56000,”employee_id”:98831,”role”:”tester”},{“name”:”preeti”,”contact_number”:”9833910513″,”address”:”noida”,”salary”:87000,”employee_id”:98832,”role”:”developer”},{“name”:”madhu”,”contact_number”:”9833910525″,”address”:”bangalore”,”salary”:22000,”employee_id”:98833,”role”:”sales”}];

  1. show employees in descending order – salary
  2. show all employees from Mumbai
  3. show all employees having salary more 50000
  4. show sum of salary from emps collection
  5. show all distinct address
  6. show names of employees having max salary
  7. show employees having 2nd highest salary
  8. count employees from mumbai only
  9. show all female employees
  10. show all male employees