Timestamp format in MySQL

This inbuilt MySQL command can be used to cast timestamp values as required.

SELECT DATE_FORMAT('field_name', '%Y-%m-%d %H:%i:%s') FROM 'table_name'

Above SQL will format timestamp in "YYYY-MM-DD HH:MM:SS" format. If you require only date you can format timestamp as following SQL.

SELECT DATE_FORMAT('field_name', '%Y-%m-%d') FROM 'table_name'

Add number of days to timestamp in MySQL

Following DATE_ADD('field_name', INTERVAL) can be used to increase the number of days in the 'field_name' in MySQL databases. 

SELECT SQL

SELECT DATE_ADD('field_name', INTERVAL 2 DAY)FROM 'table_name';

Update SQL

UPDATE 'table_name'
SET field_name= DATE_ADD('field_name' , INTERVAL 2 DAY)
WHERE 'id' = 1;

Run SQL script in Background in MySQL server

Following command can be used to run SQL script in background in MySQL server.

mysql --user=sujith --pass=123suji --database=postdb --host=localhost script_file results.out &

This will return output of script  to result.out file.

Update MySQL table appending text

Following SQL command can be used to update MySQL table with appending text "text" for field "field".

update table set field=concat('text',field);

MySQL cluster JDBC URL

Following code can be used to create a JDBC Connection to MySQL cluster. Here, cluster contains two nodes as 192.168.2.23 & 192.168.2.24.

Class.forName("com.mysql.jdbc.Driver").newInstance();

url = "jdbc:mysql:loadbalance://

                + “192.168.2.23:3306, 192.168.2.24:3306/YourDataBaseName”

                + "?"

                + “user=YourUserName&password=YourPassword”

                + "&loadBalanceConnectionGroup=first&loadBalanceEnableJMX=false&autoReconnect=true";

DriverManager.getConnection(url);

AutoCommit enabled in MySQL DB

In MySQL default engine is set to "MyISAM". In  "MyISAM" engine, auto commit doesn't work. That means, you can't change commit time in application level. And, you can't roll back connections when needed when using "MyISAM" engine.

If you need to set auto commit enabled/ disabled in application level change MySQL DB engine to "INNODB" engine. In "INNODB" engine, you can roll back connection as needed.

CREATE TABLE Test1 (
  refNo varchar(10) default NULL,
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


CREATE TABLE Test1 (
  refNo varchar(10) default NULL,
) ENGINE=INNODB DEFAULT CHARSET=latin1;

mysqldump Command Usage

It is used mysqldump command to get a backup of existing tables.

Simply, run the following command to get the backup.

mysqldump -usujith -psujith database_name table_name  > /home/sujith/backup.sql

NOTE :- This dump contains all the table structure and data in table.

MySQL outfile write permission

When unloading data from MySQL database, it is needed to given special WRITE permission for user to write files.

Refer below command.

SQL command for unload data

• select * into outfile '/tmp/testfile.txt' fields terminated by '|' from recharge where req_date<'2012-10-03';

SQL command to grant write permission

• GRANT FILE ON *.* TO 'sujith'@'localhost';
• flush privileges;

MySQL grant permission

It is needed to grant permission in MySQL server for users of the database. Below MySQL commands can be used to grant permissions.

1. grant all privileges on database.* to 'sujith'@'localhost';
2. flush privileges;

Refer MySQL documentation for more details.

MySQL days between SQL Syntax (Between Days)

In MySQL, there is a special ways to get data between two days.

select * from ret_bonus where DATE(start_time) between date_sub(curdate(), interval 5 day) and CURDATE()

Above SQL returns data between today and 5days before today. 

date_sub(curdate(), interval 5 day)

This MySQL command returns 5days before today. 

Mysql Current date & time

Use the Mysql server curdate() and curtime() functions to get current date & time values from the system.
Below SQL demos, the usage of the functions.

insert into test(cur_date,cur_time) values(curdate(),curtime());

Mysql Triggers Example

Mysql triggers are like schedulers run on table changes.  When INSERT, DELETE, UPDATE command executions, these triggers are executed automatically.

Below is a sample of SQL commands to create and test Triggers when insert happens to "test1" table.

Create below four tables and insert values into "test3" & "test4" tables.

Trigger

This Trigger is executed when insert happens into "test1" table. It gets "test1.a1" value and insert it into "test2" table. And, delete it from "test3" table. Update  "test4" b4 value.

Create Tables

CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);

Create Trigger

delimiter |

CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END;
|

Insertions

delimiter ;

INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);

INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);

Executes following SQL to run the Trigger.

INSERT INTO test1 VALUES (1), (3), (1), (7), (1), (8), (4), (4);

Shell Script to load data from a TableA to TableB in mysql

Suppose you have to select custom fields in TableA and insert those fields into TableB in mysql, then the following shell script command would be appropriate.

mysql -u user -p -D database -e 'insert into TableB select id, addr from TableA;'

Insert above code in a file and save it as ".sh" file extension. Then run it as "./Test.sh".

NOTE :- Test.sh must be stored in the server that mysql db server is running.

Encrypt, Decrypt password in Mysql

Inserting password encrypted to database using the following sql.

INSERT INTO PASSWORD(pass) VALUES(DES_ENCRYPT('user password')); 

This SQL will encrypt the user inserted password to a encrypted text. In table level, password is unreadable.

Instead of DES_ENCRYPT() method you can come up with your own function.

Selecting password from database to a string.

SELECT DES_DECRYPT(pass) FROM PASSWORD ;

This SQL will return user password as user inserted.