MySQL is a relational database management system that you probably hear tons upon tons about but if you’re reading this, you probably aren’t sure how to even begin picking at it. After all, there aren’t even any vowels in that word – that’s overwhelming enough! Well, because I’m only barely a step ahead of you (or in some cases, probably a step behind), I can promise you that it’s really not as scary as it looks. With the foundation laid out by my previous posts, we should be able to tame this beast pretty smoothly. Today we will introduce ourselves and familiarize ourselves with some MySQL controls – not necessarily database controls like we discussed in my initial post, but we’re just going to see if we can get our database to give us some information using a few key phrases.
Just note that this WILL be a bit of a longer post – so feel free to read it in simple segments and take breaks in-between. We’ve got a lot to cover!
Getting MySQL
MySQL is managed under the GNU – that is to say, it’s an open source system, thus making it very, very popular. And that’s the story behind why you’ve heard so much about it these days. You probably want to start setting up your own MySQL server right now on your localhost (your computer) or on some other server, or maybe you’ve already got it all set up. Either way, this page details how you can install MySQL into most popular configurations. You’ll want to bookmark this page – it has quite a few nice little tutorials and discussion boards just for developers like you and I.
Playing with MySQL
To be frank with you, the best experience I’ve had working with MySQL to date is to simply use command line. Now, for people not used to a UNIX based system, PuTTY is great for connecting. For those on Linux and OS X, you can use your Terminal to access it.
If your server allows anonymous connections, you can log in simply by typing mysql. Otherwise, you might have to do a little more playing around with it. But don’t be afraid! Let’s just explore how to get on with a username and password. Here’s what we’re going to type in, with an explanation afterward:
mysql -h host.yourdomain.com -u username -p
What’s all this gibberish mean? Well, we already discussed what mysql means – we’re connecting to a MySQL database. After that, -h denotes that “after this is our host”, so it’s just meaning that we aren’t connecting to localhost (that is, your computer), and tells our shell that we want to go to host.yourdomain.com. -u means we are using a username; we would then put said username afterward as in the example. -p then means that said username has a password.
After you hit enter using said command, you should then be prompted for your password. Note that you should not be able to see said password as you type – this is simply for security’s sake.
And woot – we should be in now, provided we typed things in properly. If the above prompt is incorrect, you don’t need to retype it. Actually, just hit the “Up” arrow on your keyboard and it’ll allow you to tab through previous commands and edit them if need be.
But what if my server uses a different port?
Ports are communication channels between machines. The default port for MySQL is 3306. If you’re trying to connect to a different port because of your server configuration, you will need to get the port number from whomever manages your server. In the command line, you can then specify it using either -port or -P.
Exploring Our Databases
Now that we have a firm understanding of how databases work and are connected, it’s time to get our feet wet looking at what we’ve got.
Watching the SHOW
In order to work with a database, we’ve gotta know what’s already available. This is easy simply by typing in the following command:
SHOW DATABASES;
This will prompt MySQL to show the available databases on the host that you’ve connected to. It’ll even lay it out for you in a nice little table format.
+-----------+
| Databases |
+-----------+
| mysql |
| test |
| yourDB |
+-----------+
3 rows in set (0.00 sec)
SELECT What You Wanna Watch
Now that we see a nice little print-out similar to what’s in the example, we can now select the database we want to work with. For now we will navigate using some internal commands – that is, commands which are built into SQL itself. So go ahead and type this into your command line:
\u yourDB
The weird little backslash tells MySQL that that’s a built-in command, and it tells it to USE the database yourDB (or a different database if you specified something else as its title). If the database does not exist or you mistype the name (or both!), it will return an error that looks something like this:
ERROR 1049 (42000): Unknown Database 'yourDB'
mysql -h host.yourdomain.com -u yourusername -p yourDB
Voila! Instant connection to that database.
Showing Your Status
Sometimes you might be working with tons and tons of data, and aren’t sure if you’re still connected to your database because you’re tabbing through 15 different windows, or want to know exactly which database you’re connected to. Enter \s.
This built-in command returns the status of your current database connection (or lack there-of), including what the name of the database is that you are on and the parameters of the connection. This is especially handy if you are playing with more than one database at a time. It even tells you the MySQL version you are currently connected to.
If you find you’ve been disconnected from your database, a single \r will allow you to force a reconnect. Pretty cool, huh?
But What About the SQL?
So we dove head-first into a list of SQL commands you’ve probably forgotten by now, and gone over how to organize your information into a few spreadsheets that’ll allow you to have all the information at your fingertips to start building your database. But let’s take a moment and really look at how we’re going to get all that information from Document A into Database B.
SQL can be considered a high-level scripting language. This basically means that it’s very similar to human language and it tells a computer what to do, and as a scripting language it does not need to be compiled to be read by your computer the way that Java or C would need to be (or even, to some extent, PHP on a server). If you don’t know exactly what this means, that’s okay. Just keep reading, and keep researching it.
The way you input SQL, thus, is by doing what is known as scripting. That is, inputting it into your shell one line at a time, and ending each line with a semi-colon, ;. You might see some of your uber cool developer friends doing cool things like this with Perl or Python, and now you’ll be able to do super cool things with SQL in a very similar fashion (we’ll explore Perl and Python a bit more in another series).
You probably noticed when we had our databases show themselves that the SHOW command ended in a semi-colon. If you accidentally forgot to copy-paste that semi-colon, you were probably prompted for another line of MySQL and are probably still banging your head wondering what you are doing wrong and preparing to send me a very angry email. Well, save the email to drafts, because that semi-colon is important. That semi-colon tells MySQL that we’re done with our command and to please execute them.
This is great in that if you have a ton of commands you need to input all at once, MySQL will keep asking for more until it reads a semi-colon, allowing you to be very specific in what you’re telling it to do. So, you can curse it now, but you’ll learn to love it later!
A cool thing, too, is that MySQL will let you also edit your SQL commands with a built-in editor. To do this, just type in \e and it’ll open an editor that’s specific to whatever environment you’re developing in.
Handling Output
Given that we’re working with data, at times we will need to download or otherwise view what we’ve already got in our database. Let’s look at how to view that in our shell, and then move on how to download the information.
MySQL is nice in that it allows us to view our output a few different ways when it comes to viewing it in our shell. This is where we see how important case sensitivity is in what we want.
The first of our commands is \g. This very basic command executes the SQL SELECT command we normally would have put in, and then formats it in a nice little table for us.
The big brother of this command is \G. This makes a vertical table rather than the horizontal table from \g. This is useful in allowing us to view just the specifics for one line of information if our table is longer than the window we are viewing it in.
We can also view our table as an XML or HTML output. This is done using -xml or -html switches.
Now That We’ve Been Overloaded
MySQL is something that one will have to slowly ease into if one is not already familiar with command-line based web development, or really any shape or form of web development. However, having at least a general understanding of its components would allow one to be able to quickly and efficiently keep track of information – regardless of what line of work they are in – and I think for web designers (seeing how the market is) gives an edge against other designers in that it allows more legroom in becoming a one-stop shop – basically, the jack of all trades that managers like to look for.
While I feel some pride in how far I have come in my research on databases and such, and I myself (and a few of my colleagues) feel that my tutorials are effective in helping others learn more as well, it doesn’t hurt to do research. In a previous post in this series, I did recommend two books. The list is still growing in the resources which I am using to educate myself.
Homework Readings:
I am also working on an addition to my Delicious bookmarks which will feature several articles and sites dedicated to MySQL. The list is currently small, but growing. You can view it by clicking on this sentence.
Read the Rest of the MySQL Adventure Series!
