MYSQL AMAN MEHTA,2K15/MC/09 MySQL database is one of the most popular database that already exists. There are countless tricks, options, and functions available for MySQL developers. Many times the developer often are surrounded by many technical and detailed contents which can easily distract them from MySQL’s big picture.What follows are 12 essential tricks or things that any MySQL developer should know:1.
Table RelationshipsMajorly there are three forms of table relationships i.e one-to-one, One-to-Many (O2M), and Many-to-Many (M2M). The 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 TablesThere are many ways to join tables in MySQL like INNER, OUTER, LEFT, AND RIGHT. For example, consider user and user_pic tables where the user is a parent table with many children or pictures. As mentioned above, 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 EngineIf you like MySQL InnoDB engine and normalized database structures the I suggest setting “UPDATE=cascade” and “DELETE=restricted” when creating the table relationships.4. Cross Database QueriesBig 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 ..
AliasesAliases can be used a lot mainly in three situations: 1. When joining multiple tables together to avoid confusions and shorter queries2. When making cross-database queries.3.When there are ambiguous queries (for instance, joining two tables that have at least one field in common).6. DATE_FORMAT FunctionWhile creating more professional applications, you should use the MySQL DATE_FORMAT function wherever needed.7.
IN() Versus BETWEEN … AND .
..The noticeable difference between IN() and BETWEEN is that 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 NULLWhen you want to filter a null or valid values, use the “IS NULL” and “IS NOT NULL” functions respectively. For example, below, the query returns all users having null value for their age:”SELECT * FROM user WHERE age IS NULL”9. ORDER BY DESC, ASC, RAND()The ORDER BY keyword is used to sort the result-set in ascending ,descending order or random order.
10. Normal OperatorsThere are many normal operators but here are the main MySQL opertators which everyone should know: , != , AND, OR, . When you say A is not equal to B, you can use AB or A!=B11. LIMIT A, BIt’s best to use LIMIT in conjunction with ORDER BY. The LIMIT A, B states to 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.12.
Index, Unique, Primary KeysPrimary Key (PK) or table identifier can be a custom unique value or an auto-increment integer like the email address. The Unique key is identical to the PK, except that it accepts null values. Major use of Index key is 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.