Recipe ID: hsts-r68
We offer Linux, database design,bash scripting, Linux server management, SQL coding and more classes in self-paced video format starting at $60. Click here to learn more and register. For complete self-paced system admin training, visit our System Admin- Complete Training Bundle page.
Without a doubt, the MySQL database is the most popular database that already exists. 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 need to create a lookup table.
2. Join Tables
There are many forms of MySQL table joins: INNER, OUTER, NATURAL, LEFT, AND RIGHT. Surprising enough, I use none of them. For example, consider user and user_pic tables where the user is a parent table with many children or pictures. As mentioned before, we put FK (user_id) in the user_pic table and join them together (note that user_pk is the primary key of user table):
"SELECT * FROM user, user_pic WHERE user.user_pk=user_pic.user_id"
Now the two tables are joined together. Except for special cases where you may use other joins (like LEFT join for including NULL values), this approach will handle most of your jobs.
3. InnoDB Engine
If you are fan of MySQL InnoDB engine and normalized database structures, I suggest setting "UPDATE=cascade" and "DELETE=restricted" when creating the table relationships.
4. Cross Database Queries
Big or enterprise level databases require cross-database queries. For example, database one (company_users) has a user table and database two (company_staffs) has a staff table. Here is a one cross database query:
"SELECT * FROM company_users.user, company_staffs.staff WHERE ...."
5. Lovely Aliases
I use aliases a lot mainly in four situations:
1. When joining multiple tables together to avoid confusions and shorter queries
2. When making cross-database queries
3. When there are ambiguous queries (for instance, joining two tables that have at least one field in common)
4. When using the DATE-FORMAT function to distinguish the formatted date from Timestamp one.
6. DATE_FORMAT Function
To deliver more professional applications, you should use the MySQL DATE_FORMAT function wherever needed.
7. IN() Versus BETWEEN ... AND ...
IN() is mainly used for a few options, whereas BETWEEN is used for a range of values as seen in below queries:
"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
When you want to filter a null or valid values, use the "IS NULL" and "IS NOT NULL" functions respectively. For instance, below, the query returns all users who have the null value for their age:
"SELECT * FROM user WHERE age IS NULL"
9. ORDER BY DESC, ASC, RAND()
It is self-explanatory.
10. LIMIT A, B
It is better to use LIMIT in conjunction with ORDER BY. The LIMIT A, B means start from the row number A and take out B number of rows. Then, return the result set. It is a great tool for a large-scale application (to unload the MySQL processing task), application paginations, and search queries, so make sure to use it.
11. Handy Date Functions
There are a lot of MySQL date functions. Of particular interest (from practical perspective) are DATE_ADD(), DATE_SUB(), CURRENT_DATE(), NOW(), and DATEDIFF(), so lookup the MySQL manual for details.
12. Index, Unique, Primary Keys
The Primary Key (PK) or table identifier can be an auto-increment integer or a custom unique value like the email address. The Unique key is identical to the PK, except that it accepts null values. The Index key is mainly used for speeding up the MySQL processing time, so use it only for very important fields on your table. Otherwise, it may slow down your database response time.
13. Incremental versus Periodic Backups
There are primary two ways by which you can backup a MySQL database: incremental and periodic. For the former, you need to define a binary directory path on your MySQL.ini file. Then, the MySQL will automatically backup from any INSERT, UPDATE or DELETE statements instantly (of course, at the cost of higher processing load), while saving them in the binary log on your server. However, for the latter, you should run MySQL_dump function at different intervals (preferably daily) and save the SQL backup file afterward.
14. Normal Operators
Here are the main MySQL opertators: , != , AND, OR, . When you say A is not equal to B, you can use AB or A!=B
15. Group By
Make sure you understand Group By very well before using it. Otherwise, it may return ambiguous results, especially in multi-table join queries.
There are many other minor things that I did not cover here. However, the discussed 15 tricks would be a good starting point for mastering the giant MySQL database. Last but not least, to handle the MySQL structure designs, you should know the business processes behind the table entities, like user and transaction info, or transaction info and product info, on top of your technical knowledge. Thus, there is a long journey to go.
We offer private coding classes for beginners online and offline (at our Virginia site) with custom curriculum for most of our classes for $59 per hour online or $95 per hour in virginia. Give us a call or submit our Private Coding Classes for Beginners form to discuss your needs.