What are good resources for taking the step up from entry level MySQL?

Question

I've sort of slipped into the area of web development by chance, and because of this I lack training in the handling of databases (mostly MySQL in my case). I've come to know the basic stuff (SELECT, WHERE and even JOIN) but I'm often frustrated when I need to do more advanced queries.

The problem for me is not finding resources describing HAVING, it's figuring out that HAVING is what I'm looking for. I can do most of the things I need to do using "regular" code once I've retrieved the data, but that defeats the whole point.

Where would be a good place to start to move from the basics into slightly more advanced stuff?

I'd prefer online resources but books are good too.

Answer 1

I highly recommend Rebecca Riordan's Designing Effective Database Systems for background on database design. Not only how to use things like HAVING but why (or why not). Available through Safari online, as well: http://my.safaribooksonline.com/0321290933

Answer 2

Well, what you suffer is lack of experience (with no offense). Personally, I jumped into it and have been learning ever since (that is since 1998). There are a lot of things you just know over time, but with each project there are new obstacles to master.

One of the things that help a lot is looking at queries when you use phpMyAdmin. You can figure out a lot from there. After a while you get around to the client since it's faster than all the clicking, but there is nothing wrong with frontends to begin with. Also, I'd seriously recommend learning what the different indexes do and what each is used for, then develop a habit of running EXPLAIN on your statements (often) and go from there.

I know it sounds super cheesy and elitist, but the MySQL manual is a really great resource. Chances are you will work with technologies/languages where the documentation really blows and you will look back and know what I mean.

Reasons as of why the manual is awesome are, they explain everything and they also do it really well. On top of that, the manual also has comments on each page which provide a couple pointers where the manual left off to early (doesn't happen often, IMHO).

In the beginning my other favorite resources to learn used to be Devshed and Sitepoint, but I haven't been there in a long while. Read the tutorials (even e.g. when you are not building what they are building, just look at the code). Last I remember, they also offered pretty active forums/communities with a lot of knowledgable people.

And then, there's always opensource. Use the force, read source. If you are using an app and they do something and you want to figure out how they did, read the source and learn from it.

As for books, all the cookbooks are pretty good. In general I recommend all the books with an animal on the cover (O'Reilly). They have never let me down.

Answer 3

In addition to a book or two about database concepts (the one Raintree suggested is good), check out SQL for MySQL Developers. It is an incredibly detailed manual and covers all the rules, concepts etc that you need to know. Once you get through those two books, you'll have a solid foundation to do pretty much anything you want, and enough knowledge to know what else is possible and how to figure out how to do it.

Answer 4

MySQL cookbook, 2nd Edition is quite nice. It's written by Paul Dubois, one of the primary contributors to the MySQL Reference Manual.