How to really master MySQL?

Question

I have been using mysql for the same amount of time I have been using PHP. However I feel about 10x more confident in PHP; or any other programming language. But I just can't seem to really really grasp SQL. I mean, I can select, update, insert, delete, join, etc. but when it comes to more complex stuff I am lost. How to write a more complicated select statement, how to use indexes properly, which engine to use, etc.

For some reason I really find the mysql docs hard to follow, but the php ones easy to follow.

I am starting to think that maybe my brain is just wired in a way that makes programming make since but sql confusing. I don't know. Most people think Regex is so confusing but to me it makes more scientific sense than how to construct an sql statement. Please don't get me wrong, I am not hating on SQL, I really like it, I just want to understand it better.

Whats the best/fastest way to really master MySQL?

When I say "master" I don't mean the guru among gurus, I mean smart enough to figure out most of the questions I ask on SO myself without needing help.

Answer 1

Use it

Internalizing how relational databases work is difficult for a lot of people - but it's very valuable. As you work with SQL more, it will work its way into your brain. Keep pushing yourself.

When something seems odd, investigate

In Chrome, I set up this search engine so all I have to do is type "m [search query]" to search the docs (dev.mysql.com's search sucks): {google:baseURL}search?{google:RLZ}{google:acceptedSuggestion}{google:originalQueryForSuggestion}sourceid=chrome&ie={inputEncoding}&q=site:dev.mysql.com/doc/refman/5.1/en+%s

If the docs don't have an answer, pop into #mysql on freenode and see if someone can provide some insight.

Read!

Once you understand how relational databases do their thing, you'll want to know a bit more about what MySQL is doing with those magical queries you're writing. I would highly recommend High Performance MySQL - it's very worth your time.

Answer 2

First and foremost, get a thorough understanding of joins. Not just inner and left joins either. Know what a cross join does and a full outer join. Know the circumstances that would cause you to choose a particular type of join. Understand that they are not interchangeable and that query that uses a left join may return different results than one that uses an inner join. (One would think that would be obvious but I've read too many questions where people in decribing their problem, somewhat randomly try different joins.)

Next really understand aggregates and how they work. Mysql will let you get away with not doing group bys in a standard way. But have the discipline to completely define the group by clause properly. It will help you understand what you are doing and will make your knowldge more easily transferable to other databases.

Learn what the case statement does.

When doing complex queries learn to work in chunks. Verify at each chunk that you have the results you expect. So for instance suppose you need to write a reporting query on orders that have been returned in the last 3 months and the reason for the return as well as the contact information for the customer. First step is to get the orders returned in the last three months. Once you know you have that solid, you can add the information on the reason for return. Once you have that solid, you can add the customer who returned it. Once you have that solid, add the contact information for the person. At each stage, check your results and see if they make sense. In this case, I probably want to end up with only one record per returned order. If at any intermediate stage, the number of results goes up or down, you know you have a problme with the query. Sometimes in building blocks, you will want to see additional fields just to verify if the information is correct. I put these on a separate line and comment them out as I work through the next step (removing them at the end once I know I'm right) so they are available to see again easily if adding another wrinkle made the query go funny. You cannot do complex querying correctly without a thorough understanding of what your results should be look like. Thinking it looks OK becasue it returned some results will almost guarantee that you have wrong results a good portion of the time.

Here is a list of some basic things you should be able to do in SQL without having to think about it:

1. First a straight up select with no joins (and no select *) but with conditions on the select
2. You should know how to combine two or more tables and get records that are in all the tables
3. You should know how to combine two or more tables and get records that are in all the tables but return only one record from the table with the many side of the one-to-many relationship
4. You should be able to get the records in one table but not in an associated table
5. You should be able to Aggregate data for a report
6. You should be able to insert one record to a table
7. You should be able to update one record in a table
8. You should be able to delete one record in a table
9. You should be able to insert a group of records to a table without a cursor
10. You should be able to update a group of records in a table without a cursor
11. You should be able to delete a group of records in a table without a cursor
12. You should be able to perform multiple actions in one transaction and handle error trapping
13. You should be able to create union of records and know when to use UNION vice UNION ALL
14. You should be able to vary the data for one field based on some criteria (using CASE)

Once you feel comfortable with your basic SQL knowledge, get to know your database structure. I can write complex queries against the very complicated dbs I support much faster than other people because I understand the structure and don't have to think about where things are stored. If you understand the table structure and foreign key relationships and where lookup values are stored and what the columns mean (not just their name but what data is held in them) then you can be expert in querying that database. The first thing I do in any new job is thoroughly understand the db structure.

Answer 3

In my opinion to learn SQL (and this is really for what you're asking, MySQL-specifics can come later), you should remove yourself from the control flow of PHP-style programming. Think specifically in the paradigm of declarative languages:

SQL defines WHAT you want, not how to do it. If you can try mentally removing the latter, the former will follow in time. Abstract this layer in your mind and let MySQL (or Oracle or whatever) worry about how.

Application logic can occur in SQL vs procedural/OO code in PHP. For instance when making queries to the database for something, you can consistently join in a user table and maybe a permissions table to ensure the proper rights instead of making multiple pre-queries. Inserting can be done through a similar selection (insert into ... select ...) instead of named values to ensure data validation.

Anyway, I highly recommend focusing on the Codd primatives and understanding selection, projection, joining, union, and difference. At the end of the day, the concepts are straight-forward and you can likely accomplish 80% of what you want to do through them. The rest can follow.

Answer 4

Fastest way I found to "master" anything was to challenge myself with it. Take a set of data and write down eight to ten different things you would like to query for. A group of students you might want to find the average age of everyone grouped by the grade point average rounded to the nearest whole number. Then write the queries for them. Start with one table, then add more. Practice data joins and built in functions.