SQL Script to create a backup of a table

Posted by Mohaideen Jamil in JDBC, MySQL

 
The below are the MYSQL script, which takes a backup/copy of the entire table into a temp table. This makes sure that you have a full backup of the data and table format, These query will be quiet useful in case something goes wrong.
You can use either of these queries to create a backup.

1)
CREATE TABLE employees_backup AS ( SELECT * from employees);

2)
CREATE TABLE SCHEMA.employees_backup LIKE SCHEMA.employees;
INSERT SCHEMA.employees_backup SELECT * FROM SCHEMA.employees;

Query to properly check if a record exists

Posted by Mohaideen Jamil in MySQL

1) select 1 from table where key = value;

2) select count(1) from table where key = value;

The first alternative should give you no result or one result, the second count should be zero or one.

How to select Last One Month Records in MySQL?

Posted by Mohaideen Jamil in MySQL

SELECT * FROM <table_name> WHERE <date_field> BETWEEN DATE_SUB(NOW(), INTERVAL 1 MONTH) AND NOW();
 
Similarly, You can select records for 2 months, 3 months etc.
 

How to select Last One Year Records in MySQL?

Posted by Mohaideen Jamil in MySQL

 
SELECT * FROM <table_name> WHERE <date_field> BETWEEN DATE_SUB(NOW(), INTERVAL 1 YEAR) AND NOW();

 
Similarly, You can select records for 2 years, 3 years etc.