Technology Guru

MySQL database is the most popular database that already exists to overcome the daily data collection problem. There are countless tricks, options, and functions available for MySQL developers. The developers often are surrounded by many technical and detailed contents which can easily distract them from MySQL's big picture. The problem is that they lack a checklist of all the essential techniques to master. I have developed and normalized many advance MySQL databases (along with IT consulting), which motivated me to write this article. What follows are 15 essential tricks or things that any serious MySQL developer should know: 1. Table Relationships There are three forms of table relationships: one-to-one, One-to-Many (O2M), and Many-to-Many (M2M). My analogy for O2M is it is a parent-children relationship where one parent can have many children but not the other way around. Thus, always remember putting the Foreign Key (FK) in the children (many) table. For M2M, you just...

MySQL offers the ability to use regular expressions to perform complex searches against your data. A regular expression is a tool that provides for a concise and flexible way to identify strings of text based on user-defined patterns. This article will discuss the MySQL regular expression operators, review their use and syntax, and identify the constructs and special characters that can be used in a MySQL regular expression, as well as provide a few examples of their use. MySQL Regular Expression Operators The following operators are used in MySQL to perform regular expression operations. These are used in a WHERE clause similar to the well-known and often used LIKE operator. REGEXP : The pattern matching operator for using regular expressions. NOT REGEXP : The negation of the REGEXP operator. RLIKE : A synonym for the REGEXP operator. MySQL Regular Expression Syntax The basic syntax used for MySQL regular expression operations is: -- For the...

The MySQL has many types of the storage engines and the Federated storage engine is one of them. The Federated storage engine for the MySQL RDBMS (Relational Database Management System) is a storage engine which connects the tables remotely. It treats the table of the remote database as a local data source. Now first we understand: WHAT IS A FEDERATED TABLE? A Federated Table is a table which points to a table in another MySQL database instance (mostly on another server). It can be seen as a view to this remote database table. Other RDBMS have similar concepts for example database links. WHAT CAN WE DO WITH A FEDERATED TABLE? To show what we can do with a federated table let’s assume the following condition: We have two different MySQL situated at different location and we required fetching data from both server and in these data set we required a JOIN which is normally can’t done over two different server. But by federated connection it is possible. To create ...

List of mySQL Date function: NOW()   : It will give you the current date and time. For example: MYSQL> select  NOW() ; Output: 2017-02-25 00:52:57; CURDATE()  : It will give you current date. For example : MYSQL> select  CURDATE() ; Output:  2017-02-25; UNIX_TIMESTAMP()  : It will give you the current date and time in epoch format For example: MYSQL> select  UNIX_TIMESTAMP() ; Output:  1487964177 MYSQL> select  UNIX_TIMESTAMP (‘2017-02-25 00:52:57’); Output: 1487964177 FROM_UNIXTIME () : It will give you DATE and TIME from the epoch time format. For example: MYSQL> SELECT  FROM_UNIXTIME (1447430881); Output:  ‘2015-11-13 10:08:01’ MYSQL> SELECT  FROM_UNIXTIME (1447430881) + 0; Output: 20151113100801 DATE_FORMAT () : It will let you decide in what format you want to show date. For example : MYSQL> select DATE_FORMAT(NOW(),’%m-%d-%Y’) Output: 11-04-201...