Without a doubt MYSQL is a robust database management software that already exists. There are so many features, tricks and options available in it to play around that must be known to all MySQL developers. The problem is that most of the times developers are not aware of a checklist of all the cool features of MySQL which they can master. So we came up with this article to help them in their quest to excel MySQL database.
Here is the following list of tricks they can try with MySQL:
1. Table Relationships
Being a user of MySQL you must be aware that there are three forms of table relationships: One-to-One, One-to-Many, and Many-to-Many. One-to-Many relationship can be explained as such that one parent can have many children but cannot be other way round. Hence, you must not forget to put the foreign key(FK) in the child table. For many to many relationship, you just need to create a lookup table.
2. Join Tables
We’ll describe here five forms of table joins: INNER, OUTER, LEFT, RIGHT, and NATURAL. To explain it further let’s take an example of two tables: USER and USER2 tables where the USER is a parent table with many children or pictures. To remind it again, we need to put FK(user_id) in the USER2 table which is a child table and let’s join them together( note that user_pk is the primary key of the parent table USER).
Follow the following instruction:SELECT*FROM USER, USER2 WHERE USER.user_pk=USER2.user_id
Now the two tables are joined together.
3.InnoDB Engine
If you really like to use InnoDb engine and normlized structures, then you should use: “UPDATE=cascade” and “DELETE=restricted” when creating table relationships.
4.Cross Database Queries
This type of command is usually used by big level enterprises where there are multiple tables to be referred for data access. For example, database one(company_userdata) has a user table and database two(company_staffdata) has a staff table. To make a cross database query use the following command:
SELECT*FROM company_userdata.user, company_staffdata.staff WHERE….
You can complete the instruction string with the desired query or information required from the table.
5. Aliases instructions
You can use aliases in many situations but some of the common situations where aliases instruction is very useful are:
When you want to join two tables together to make shorter queries and to avoid confusions.
- When you want to make cross-database queries
- When there are ambiguous queries( for example when joining two tables having at least one field is common in the tables).
- When you want to use DATE-FORMAT function to distinguish the formatted date from the system Time stamp one.
6. DATE_FORMAT function
The DATE_FORMAT function() is used to display date/time data in different formats.
Syntax:
DATE_FORMAT(date,format)
Where date is the valid date format and format specifies the output format for the date/time.
7. IN() versus BETWEEN…AND…
IN function is used for a limited number of options, whereas BETWEEN….AND… is used for a range of values as could be seen in the following examples:
SELECT*FROM user WHERE(age=15 OR age=16 OR age=17)
or
SELECT*FROM user WHERE age IN(15,16,17)
Or
SELECT*FROM user WHERE age BETWEEN 15 AND 17
8. IS NULL or IS NOT NULL
While looking up the table data if you want to filter a null or valid values, then you should use the IS NULL or IS NOT NULL functions respectively. Taking an example the following query below will return all the user values with NULL in their age:
SELECT*FROM user WHERE age IS NULL
9. ORDER BY DESC, ASC, RAND()
This function is self explanatory but to inform you this function is used to retrieve data in ascending, descending or random order.
Syntax:
SELECT expressions
FROM tables
[WHERE condition]
ORDER BY expression[ASC|DESC];
10. LIMIT A,B
This function is mostly used in conjunction with ORDER BY. The LIMIT A,B means to start from the row number A and take out B number of following rows.