MySQL Learning

[DevShivmohan]

Create backup MySQL database

Syntax

• mysqldump -u<user_name> -p <database_name> > <backup_file_with_sql_extension> then hit enter.
• After that enter your MySQL user password then hit enter.

Example

• Type command mysqldump -uroot -p student_db > backup.sql
• Entering password
• After that all database dump created in backup.sql file in future if we can run backup.sql file in MySQL then all data will back.

[DevShivmohan]

MySQL dump using java [for Windows]

String[] pp = new String[]{"cmd.exe", "/c", "mysqldump --host=<host_name> --user <user_name> --password=<password> --opt <database_name> > <backup_file_name_with_sql_extension>"};
Runtime.getRuntime.exec(pp);

MySQL dump using java [for Linux]

String[] pp = new String[]{"sh", "-c", "mysqldump --host=<host_name> --user <user_name> --password=<password> --opt <database_name> > <backup_file_name_with_sql_extension>"};
Runtime.getRuntime.exec(pp);

MySQL dump using java another host example

public static void main (String[] args) throws Throwable{
        String[] pp = new String[]{"sh", "-c", "mysqldump --host=12.93.218.875 --user dtrf --password=gtfrtds@123 --opt pos_db > /home/shiv/Desktop/pos_db.sql"};
        Integer waitResponse= Runtime.getRuntime().exec(pp)
                .waitFor();
        System.out.println("wait response - "+waitResponse);
        if(waitResponse!=0)
            System.out.println("Failed to dumping the database");
        else
            System.out.println("Database dump successfully");
    }

[DevShivmohan]

PostgreSQL login

• If you are facing difficulties in PostgreSQL login then refer - Link.
• For learning PostgreSQL refer this Link.

[DevShivmohan]

Fetch customer and their orders amount by sum of each customer orders

• .
• .
• 

Query -

select c.name customer_name, 
sum(o.amount) price from customer as c
join cust_order as o on c.id=o.cust_id
group by c.id;

[DevShivmohan]

Masking last 4 digit data for a column

Syntax of CONCAT() function

CONCAT(column_1,column_2,column_3,column_n);

Example of CONCAT() function

CONCAT(email_id, '*****' , name , 'XXXXX');

Syntax

select column1, column2, CONCAT(LEFT(column3, LENGTH(column3) - 4), 'xxxx') from table_name;

Example

select id,concat(left(account,length(account)-4),'xxxx') as Account from customer; -- Masking last 4 digit of string or number

select cust_id,customer_name,concat(left(email_id,3),'#####',right(email_id,4)) as email FROM spectrum_admin_db.customer_details;

output

[DevShivmohan]

Backup query drafted from Lattice laptop

use test;

create table deq(id int not null,salary int,name text);
insert into deq values(1,200,"A"),(2,20,"Aa"),(3,500,"B"),(4,2400,"C");
select * from deq;
select id,salary*0,name from deq where id%2=0 && name not like 'A%';
CREATE TABLE characters (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  bio VARCHAR(255) NOT NULL,
  FULLTEXT (name,bio) WITH PARSER ngram
);

desc characters;

INSERT INTO characters VALUES
(1,'Regulus Arcturus Black','Brother of Sirius. Used to be a Death Eater but defected.'),
(2,'Sirius Black','Best friend of James Potter and godfather of Harry.'),
(3,'Lavender Brown','Killed by a werewolf. She was a gryffindor student who dated Ron. '),
(4,'Cho Chang','Ravenclaw student who dated Cedric Diggory and Harry Potter.'),
(5,'Vincent Crabbe Sr.','Father of Crabbe and death-eater who escaped Azkaban.'),
(6,'Vincent Crabbe','Slytherin student who was best friends with Goyle and followed Draco.'),
(7,'Bartemius \"Barty\" Crouch Sr.','Head of the department of Internation Magical Cooperation. Killed by his son.'),
(8,'Bartemius \"Barty\" Crouch Jr.','Death Eater who impersonated Alastor Moody.'),
(9,'Fleur Delacour','Participated in the Triwizard tournament and married Bill Weasley.'),
(10,'Cedric Diggory','Participated in the Triwizard tournament and got killed by Voldemort.'),
(11,'Alberforth Dumbledore','Albus'' brother and owner of Hog''s Head.'),
(12,'Albus Dumbledore','Headmaster of Hogwards killed by Snape.'),
(13,'Dudley Dursley','Muggle son of Vernon and Petunia and first-cousin of Harry.'),
(14,'Petunia Dursley','Harry''s aunt and sister of Lily.'),
(15,'Vernon Dursley','Harry''s muggle uncle.'),
(16,'Argus Filch','Squib caretake of Hogwards.'),
(17,'Seamus Finnigan','Harry''s friend and member of Dumbledore''s army.'),
(18,'Nicolas Flamel','Creator of the Philosopher''s Stone.'),
(19,'Cornelius Fudge','Minister of Magic that was forced to resign.'),
(20,'Goyle Sr.','Death Eater and father of Gregory Goyle.'),
(21,'Gregory Goyle','Best friend of Crabbe. Slytherin student and dies by falling into Fiendfyre.'),
(22,'Hermione Granger','One of Harry''s best friend and marries Ron Weasley.'),
(23,'Rubeus Hagrid','Half-giant who loves Harry. He was the keeper of Keys and Grounds at Hogwards.'),
(24,'Igor Karkaroff','Highmaster of Durmstrang and reformed death-eater.'),
(25,'Viktor Krum','Participant in the Triwizard tournament. Dated Hermione.'),
(26,'Bellatrix Lestrange','Death Eater who was killed by Molly Weasley.'),
(27,'Alice Longbottom','Mother of Neville who was tortured by Bellatrix.'),
(28,'Frank Longbottom','Father of Neville who was tortured by Bellatrix.'),
(29,'Neville Longbottom','Gryffindor student who was a member of Dumbledore''s army.'),
(30,'Luna Lovegood','Ravenclaw student who was a member of Dumbledore''s army.'),
(31,'Xenophilius Lovegood','Father of Luna and editor of The Quibbler.'),
(32,'Remus Lupin','Friend of James Potter and werewolf. He married Nymphadora.'),
(33,'Draco Malfoy','Slytherin student who had many arguments with Harry.'),
(34,'Lucius Malfoy','Father of Draco and influential Death-Eater.'),
(35,'Narcissa Malfoy','Mother of Draco and sister of Bellatrix.'),
(36,'Olympe Maxime','Half-giantess and headmistress of Beauxbatons.'),
(37,'Minerva McGonagall','Professor of Transfiguration and head of Gryffindor.'),
(38,'Alastor \"Mad-Eye\" Moody','Retired auror and member of the order of the Phoenix. Killed by Voldemort.'),
(39,'Peter Pettigrew','Betrays James and Lily Potter. Follower of Voldemort.'),
(40,'Harry Potter','The boy who lived. Main character of the series.'),
(41,'James Potter','Father of Harry. Murdered by Voldemort.'),
(42,'Lily Potter','Mother of Harry. Murdered by Voldemort.'),
(43,'Quirinus Quirrell','Possessed by Voldemort. Defence against the Dark Arts professor.'),
(44,'Tom Riddle Sr.','Muggle father of Voldemort who was killed by him.'),
(45,'Mary Riddle','Muggle mother of Voldemort who was killed by him.'),
(46,'Lord Voldemort','The antagonist of the series who murdered many.'),
(47,'Rita Skeeter','Reporter at the Daily Prophet.'),
(48,'Severus Snape','Head of the Slytherin house and saved Harry in many occasions.'),
(49,'Nymphadora Tonks','Married Remus Lupin and was killed by Bellatrix.'),
(50,'Dolores Janes Umbridge','Senior undersecretary to the Ministry of Magic. Eventually sent to Azkaban.'),
(51,'Arthur Weasley','Father of the Weasleys and member of the Order of the Phoenix.'),
(52,'Bill Weasley','Oldest son of Arthur and Molly. Husband of Fleur. '),
(53,'Charlie Weasley','Second son of Arthur and Molly. Works with dragons in Romania.'),
(54,'Fred Weasley','Identical twin with George and co-owner of Weasleys'' Wizard Wheezes'),
(55,'George Weasley','Identical twin with Fred and co-owner of Weasleys'' Wizard Wheezes'),
(56,'Ginny Weasley','Marries Harry Potter and only daughter of Molly and Arthur.'),
(57,'Molly Weasley','Wife of Arthur and mother of the Weasleys. Kills Bellatrix.'),
(58,'Percy Weasley','Third son of Arthur and Molly. He is a Gryffindor prefect.'),
(59,'Ron Weasley','Harry''s best friend. Marries Hermione.'),
(60,'Dobby','House elf and friend of Harry. He is killed by Bellatrix.'),
(61,'Fluffy','Three-headed dog belonging to Rubeus Hagrid.'),
(62,'Hedwig','Harry''s owl.'),
(63,'Moaning Myrtle','Ghost at Hogwards.'),
(64,'Aragog','Acromantula belonging to Rubeus Hagrid.'),
(65,'Grawp','Giant-half brother of Hagrid.');

select * from characters;
select * from characters where match(name,bio) against('potter');

SELECT id, name, bio,
    MATCH(name,bio) 
    AGAINST('harry') as score
FROM characters 
WHERE 
    MATCH(name,bio) 
    AGAINST('harry')  ORDER BY score DESC;

create database def;
use test;
create database quiz;
use ecommerce_test;
drop table product;
create table product(barcode bigint PRIMARY KEY,item_desc text,group_name text,size varchar(50), price double,qty int,profit double,remain_qty int);

-- "C:\Users\defsh\OneDrive\Desktop\Assessment_Backend.csv"
select * from product order by qty desc limit 10;
use test;
LOAD DATA INFILE 'C:/Users/defsh/OneDrive/Desktop/Assessment_Backend.csv'
INTO TABLE product
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

create database patient;

create database ecommerce;
drop database ecommerce;

create database test;
drop database test;

use test;
create table customer(id int,name varchar(50));
create table cust_order(cust_id int,amount double);
insert into customer values(1,"Shiv1");
insert into customer values(2,"Shiv2");
insert into customer values(3,"Shiv3");
insert into customer values(4,"Shiv4");
insert into cust_order values(1,32.5),(1,56.0),(2,325.5),(2,586.0),(2,32.5),(3,56.0);

select * from customer;
select * from cust_order;

select name, sum(amount) price from customer left join cust_order
on cust_order.cust_id=customer.id
group by cust_id;

select * from cust_order ;

SELECT customer.id,customer.name, SUM(cust_order.amount) orders_price 
FROM customer 
JOIN cust_order ON customer.id = cust_order.cust_id 
GROUP BY id;
-- ORDER BY orders_price DESC;

use test;

show tables;
select * from orders;
select * from customer;
create table orders(cust_id int,price double);
insert into orders select * from cust_order;
select abs(orders.price) from orders;
insert into orders values(2,-21.758);
delete from orders where price =-25.0;

explain select customer.id,customer.name CustomerName, sum(abs(orders.price)) price from customer,orders where customer.id=orders.cust_id group by customer.id; 
select customer.id,customer.name CustomerName, sum(orders.price) price from customer,orders where customer.id=orders.cust_id group by customer.id;

create database quiz;

use test;
explain select c.name as Customer_name, count(o.price) 
as Total_order_count,sum(o.price) as total_price,max(o.price) 
as Max,min(o.price) as Min, avg(o.price) as Avg 
from customer as c left join orders as o on c.id=o.cust_id group by o.cust_id;

[DevShivmohan]

Java database connectivity problem in URLs

url=jdbc:mysql://localhost:3306/hybrisdb?characterEncoding=latin1&useConfigs=maxPerformance

https://stackoverflow.com/questions/50855622/unknown-initial-character-set-index-255-received-from-server

[DevShivmohan]

Update table and set values from comman table expression (Extract values from json_payload)

start transaction;

with cte1 as(select json_payload::json->>'data' as data1,audit_id
from idgc_audit_history_cnfg where task_code='ALERTS_EVENT_ATTRIBUTE'),
cte2 as (select data1::json->>'attributeId' as attributeId,
data1::json->>'module' as module,
data1::json->>'event' as event,
data1::json->>'subEvent' as subEvent,
data1::json->>'channel' as channel,
audit_id
from cte1),
cte3 as (select 
concat(attributeId,'-',module,'-',event,'-',subEvent,'-',channel) as taskIdentifier,
audit_id
from cte2)
update idgc_audit_history_cnfg as t set task_identifier=cte4.taskIdentifier from cte3 as cte4
where t.audit_id=cte4.audit_id;

commit transaction;