MySQL and database programming for beginners

MySQL's community manager shares tips and resources for beginners.
451 readers like this.
A maze

Opensource.com

Dave Stokes has been using MySQL for more than 15 years and has served as its community manager since 2010. At All Things Open this year, he'll give a talk about database programming for newbies with MySQL.

In this interview, he previews his talk and shares a few helpful resources, required skills, and common problems MySQL beginners run into.

What resources would you recommend for beginners that are eager to explore MySQL?

The online MySQL manual is a fantastic resource. There are also many books (check you local used book store) and online resources. One of the great things about open source software is that you can try it and have only lost your opportunity cost. I do recommend that if you are downloading MySQL that you also pick up the free MySQL Workbench program to go with it, as it is an amazing tool.

MySQL works on Windows, Linux, and OS X platforms. You can get Docker images too. It runs great on older hardware, so pull that old laptop out of the closet and give it a try.

Do you need specific skills to learn and master (My)SQL?

Structured Query Language (the "SQL" in MySQL) is a fairly straightforward and easy-to-learn language. Sadly, it is not as widely taught as it should be. Many plug away with no concept of sets, relational theory, or data and then wonder why their database queries stink. The SQL code is fairly easy to master. MySQL is also fairly easy to learn and takes only a little time and effort to get some amazing results. 

And now, MySQL supports a new protocol that allows you to use MySQL without SQL. The hard work is done behind the scenes, and the developer can do all the CRUD (Create/Replace/Update/Delete) in their language of choice without writing a single SQL query.

Part of your session will be about protecting data against SQL injections. Does the growing Internet of Things present new security challenges for MySQL?

SQL Injections are often the result of sloppy programming standards. Never trust what an end user gives you, as it is too easy to embed something nasty. Scrub, scrub, scrub user data before processing. Many developers just want to grab data and shove it away in a data store with no schema—no rigor applied to checking the data—and worry about it later. This could lead to them swallowing the equivalent of a ticking time bomb. Multiply this by the constantly increasing amounts of data to be stored, and poor practices turn your data warehouse into a digital landfill.

Can you give us some insight to the N+1 problem ahead of your talk? Is this an important topic for SQL newbies?

The N+1 pattern is sub-optimal database access. Imagine you are making breakfast and make a separate trip to the store for each ingredient. It would be more cost-effective and faster to get all the ingredients in one trip. Object-oriented development has taught many to treat each bit of data as a unique item, and that makes it easy to miss how databases can do a loft of heavy lifting in one swoop. Too many developers do tiny, incremental iterations. It is important to know your data to be able to process it efficiently. Newbies can often lose sight of the big picture and get fixated on a line of code.

Do you have advice for database programmers that want to make a career out of it?

Databases have been going through a revolution recently. Many SQL databases now have NoSQL features. Disks are no longer just spinning metal platters. Tiny scripting languages now need to access relational data, and the database manufacturers are adding new features all the time. You have to keep learning the new stuff while reinforcing the old stuff—very challenging.

Data itself has been growing rapidly, and not just because of the IoT. Your cell phone is tracked as you move between cell towers. Your pacemaker sends your doctor information on your heart. More and more data is piling up faster and faster. The best base is to properly architect your data with good normalization. That's how airlines can handle millions of transaction a minute.

Nobody goes around saying, "Dang it! The database is just too fast! Can you slow it down?" Bad designs are reinforced by poor performance as your data grows. You have to plan for the future.

To close this off, what's the best SQL joke you ever heard?

The Little Bobby Drop Tables xkcd is a classic.

Then there is the DBA who walks into a bar, sees two tables, and asks, "May I join you?"

An optimist sees the glass as half full. The pessimist sees it as half full. The DBA realizes that the drink is gone and the ice has melted.

Avatar
Former Opensource.com and Open Organization moderator/ambassador.

Comments are closed.