My-Sql Advanced Commands

My-Sql Command Prompt

My-Sql Command Prompt

Advanced My-Sql Commands( Asks in Interview )

  1. To clear console              —     \! clear
  2. To delete procedure         —         drop prcedure <procedureName>
  3. Show all stored procedure   —         show procedure status
  4. To get 2nd highest salary       —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 1,1
  5. To get 3rd highest salary       —SELECT DISTINCT(Salary) FROM employee ORDER BY Salary DESC LIMIT 2,1
  6. To convert string into datetime type     —SELECT STR_TO_DATE(yourdatefield, ‘%m/%d/%Y’) FROM <yourtable>
  7. To check event scheduler is ON/OFF       —select @@event_scheduler
  8. To start event-scheduler           —     set GLOBAL event_scheduler=ON
  9. To delete duplicate records from table

—   delete from table1 USING table1, table1 as vtable

WHERE table1.ID<vtable.ID AND table1.field_name=vtable.field_name;

So, These are the some Advanced My-SQL features , which may help you…

Have   a   Good   Day………!!!

Advertisements

How To Create “EVENT” In My-Sql

My-Sql  Event with Scheduling Logic

My-Sql Event with Scheduling Logic

 

To Create  Event in My-Sql .

To create event in My-SQL , we have to follow a Pattern which is written below..

1. Declare Delimiter

2. Define Name of the event

3. Define when to schedule

4. Start with “DO”

5. Then “Begin”

6. Define Business logic

(Like variable declaration, job which you want to schedule through your Event, any condition…So mainly it is the body of your Event)

7. Then Declare end of your event like “END <delimiter>”

8. Change Delimiter to normal Delimiter.

Example:- Here I tried to call a stored procedure in this event which is scheduled after every 4-Hour, and passed the parameter for that stored procedure by taking two date parameter, and also with some additional parameters, I tried to use loop and if-else condition also in this event…     (Tested and executed Event)

……………………………………………………………………………………………………………….

delimiter $$

CREATE EVENT ue_schedule_test

ON SCHEDULE

EVERY 4 HOUR

DO

BEGIN

DECLARE to_temp TEXT(25);

DECLARE from_temp TEXT(25);

DECLARE pv_temp TEXT(20);

DECLARE done INT DEFAULT FALSE;

DECLARE curs1 CURSOR FOR SELECT name FROM data ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN curs1;

read_loop: LOOP

FETCH curs1 INTO pv_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL -4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO from_temp;

SELECT DATE_FORMAT(DATE_ADD(convert_tz(CURDATE(),’SYSTEM’,’+00:00′),INTERVAL 4 HOUR),’%Y-%m-%d %H:%i:%S’) INTO to_temp;

DELETE FROM availibility WHERE Date=from_temp;

CALL usp_availability_test(from_temp,to_temp,pv_temp,’Total’,’+00:00′,’custom’);

IF done THEN

LEAVE read_loop;

END IF;

END LOOP;

CLOSE curs1;

END $$

delimiter ;

………………………………………………………………………………………………..