TweetFollow Us on Twitter

MySQL, Part Deux

Volume Number: 21 (2005)
Issue Number: 4
Column Tag: Programming

Getting Started

by Dave Mark

MySQL, Part Deux

In last month's column, I walked you through the process of installing and securing MySQL on your own Mac. Having PHP and MySQL installed on your local machine is a great way to learn about these two important technologies. You can build databases and script them via PHP without a net connection, and without having to wait for files to FTP each time you make a change. If you haven't already done so, go back and review the PHP and MySQL installation columns and make sure they are both up and running.

    If you are new to this column (I know we've had a bunch of new subscribers lately), go to http://www.php.com and http://www.mysql.com and go through the installation process. First install PHP, then install MySQL. If you find the instructions on those sites a bit daunting, there are a number of tutorials for each. Use google. They're pretty easy to find.

Set Up a MySQL Alias

Before we start playing with MySQL, you'll want to set up an alias, so your Unix shell knows how to find the installed mysql executable. For those of you relatively new to Unix, an alias is a shorthand way of referring to a longer command. Here's an example that I find useful. I frequently use the Unix "find" command to locate files on my hard drive. Here's a typical find command:

find . -name "*.mp3"

This command searches the current directory (".") for any files having a name that ends with ".mp3". Try this command yourself. Launch the Terminal application (you'll find it in the Utilities subfolder). When your Terminal window appears, type the command above. Obviously, your results will depend on how many mp3 files you have in the current directory.

Now type this command:

echo $SHELL

This command tells you what shell you are running. Chances are very good that you are running the bash shell (Bourne Again SHell), and the command will return this result:

/bin/bash

If you are using a different shell, try following along anyway. Worst case, you'll just have to use the long form to launch mysql. I'll show you how to do that in a bit.

Assuming you do have a compatible shell, try typing this command:

alias

This command lists your current aliases. If you've never set up any aliases, when you hit return, you'll just get your prompt back.

Now let's set up an alias for the find command. Type this command:

alias fnd='find . -name'

You've just created an alias. Every time you type fnd, the shell will substitute the string "find . -name". To check this, type the command:

alias

The shell should list your new alias:

alias fnd='find . -name'

To test your new alias, type this command:

fnd "*.mp3"

This command should behave exactly the same as your original find command. Cool, you've just created your first alias!

The alias we just created is temporary and will disappear as soon as we logout by closing the window or quitting Terminal. Rather than having to redo all your aliases each time you login or start a new Terminal session, there is a more permanent solution. Each time you login, the bash shell looks for a file in your home directory named .profile and executes, as shell commands, each line in the file.

Take some time to learn either vi or emacs, the two better-known Unix text editors, then edit your .profile and append any alias commands you'd like to have. At the very least, add this alias to your profile:

alias mysql='/usr/local/mysql/bin/mysql'

Note that when you add an alias to your .profile, the alias command won't be executed until you log out and log back in again or close your Terminal window and open a new one. One nice way to re-run your .profile is to use this command:

source .profile

Here's what happened when I ran this command, then ran the alias command to check my current list of aliases:

Dave-Marks-Computer:~ davemark$ alias
alias fnd='find / -name'
Dave-Marks-Computer:~ davemark$ source .profile
Dave-Marks-Computer:~ davemark$ alias
alias fnd='find / -name'
alias mysql='/usr/local/mysql/bin/mysql'

The first alias command only found my fnd alias. I then ran .profile and checked my aliases again and, lo and behold, my new mysql alias was added to the list. Cool!

Another command worth noting is the unalias command. For example:

Dave-Marks-Computer:~ davemark$ unalias fnd
Dave-Marks-Computer:~ davemark$ alias
alias mysql='/usr/local/mysql/bin/mysql'

I used unalias to remove the fnd alias from the list. Of course, since I added that alias to my .profile, as soon as I log back in, the alias will be back.

    Want to learn more about the bash shell? Check out Learning the bash Shell by Cameron Newham & Bill Rosenblatt from our friends at O'Reilly. They just released a 3rd Edition of the book and it looks great. It'll walk you through things like command history, command-line editing, command completion, shell programming, flow control, signal handling, etc. Lots of great stuff, very accessible.

Getting Started with MySQL

With your new, mysql, alias in place, we're ready to launch the MySQL monitor and play a bit. Start by launching mysql:

mysql -u root -p

This command executes the mysql program located in /usr/local/mysql/bin/ (take another look at the alias to see where this path came from). It starts mysql logging in as the user root and tells mysql to prompt you for a password. As a reminder, in last month's column, we added a password to the root account for obvious security reasons. Remember, MySQL maintains its own list of users. The MySQL root is not the same as your computer's root account. They just share the same name.

Here's what I saw when I started mysql:

Dave-Marks-Computer:~ davemark$ mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 4.1.8-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Building a Database and Table

Our first step is to create a database. We'll then populate the database with tables. Each table will have rows and columns and is where the actual data resides.

Our database will be called pets. Within pets, we'll create a table called dogs. As you might imagine, we could also create a table called cats and another table called fish.

Let's start by asking mysql to tell us what databases already exist:

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.07 sec)

mysql>

MySQL ships with two databases. The mysql database contains all the user access privileges. You won't add to that database. The test database is for you to play with. After you finish with this column, go ahead and add your own tables to it. For now, we're going to create a new database called pets. Start by typing this command:

mysql> create database pets
    ->

Hmmm...Notice that mysql did not process your command. Instead, it prompted you with a -> prompt. That's because mysql expects you to end each command with a semicolon (;). One of the nice things about this approach is that you can break complex commands across multiple lines. When you are ready to terminate the command, end the line with a semi.

To finish the previous command, just type a semicolon and mysql will create your pets database:

mysql> create database pets
    -> ;
Query OK, 1 row affected (0.39 sec)

mysql>

Now let's check to see if the database was actually created:

mysql> show databases;
+----------+
| Database |
+----------+
| mysql    |
| pets     |
| test     |
+----------+
3 rows in set (0.63 sec)

mysql>

Note that splitting the command over three lines would work equally well:

mysql> show
    -> databases
    -> ;
+----------+
| Database |
+----------+
| mysql    |
| pets     |
| test     |
+----------+
3 rows in set (0.00 sec)

mysql>

Cool! So now we have a pets database. Before we add a table to the database, let's see what tables already exist:

mysql> show tables;
ERROR 1046 (3D000): No database selected
mysql>

The problem here is that we haven't told mysql which database to look in. Try this command:

mysql> show tables from pets;
Empty set (0.00 sec)

mysql>

That's better. As you can see, the pets database does not yet contain any tables. Rather than have to specify a database with every command that refers to a table, we can issue this command:

mysql> use pets;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

From now on, when we refer to a table name or issue a table command, mysql will assume we are using the pets database.

Let's create a dogs table:

mysql> create table dogs( 
    -> breed varchar(60),
    -> age int(2),  
    -> dogID int(10) auto_increment primary key );
Query OK, 0 rows affected (0.39 sec)

mysql>

Tables have columns and rows. Each column represents a specific type of data you want stored in your table. You can think of the table definition as a sort of struct definition, with each field as a column header and each row as a specific instance of a struct with all the fields filled in. Our create table command defined the table as having 3 columns. The first column contains a 60 character string. The second character contains a 2-byte integer.

The third column contains a 10-byte integer that will act as the primary key to our database. You'll use this key to do lookups. We'll do that a bit later on in the column. The auto_increment tag tells mysql to assign the next higher number each time a new row is created in the table. This technique works as long as the row is added with this field set to 0. So the first dog added to the table will automatically get a dogID of 1. The next one will get a dogID of 2. And so on. As long as the row is created with a dogID of 0, the dogID field will be filled with the next available dogID.

Let's create a dog:

mysql> insert into dogs values ('poodle', 8, 0 );
Query OK, 1 row affected (0.37 sec)

mysql>

This dog will have a breed of 'poodle', an age of 8, and will get assigned a dogID of 1. Let's verify that:

mysql> select * from dogs;
+-------- +------ +------ +
| breed   | age   | dogID |
+-------- +------ +------ +
| poodle  |   8   |   1   |
+-------- +------ +------ +
1 row in set (0.33 sec)

mysql>

The select command let's you retrieve data from the table. The * is a wildcard, allowing us to retrieve all the rows in the table. Since we've only created a single row, we only get back a single row. Makes sense.

Let's add a second row to the table:

mysql> insert into dogs values ('spaniel',7,0);
Query OK, 1 row affected (0.33 sec)

mysql>

Again, we used 0 as the third argument so we benefit from the auto_increment. Let's take a look at all the rows in our table now that we've added a second row:

mysql> select * from dogs;
+-------- +------ +------ +
| breed   | age   | dogID |
+-------- +------ +------ +
| poodle  |   8   |   1   |
| spaniel |   7   |   2   |
+-------- +------ +------ +
2 rows in set (0.36 sec)

mysql>

Makes sense, right? This variation of select retrieves the data, but orders it by age:

mysql> select * from dogs order by age asc;     
+-------- +------ +------ +
| breed   |  age  | dogID |
+-------- +------ +------ +
| spaniel |   7   |   2   |
| poodle  |   8   |   1   |
+-------- +------ +------ +
2 rows in set (0.00 sec)

mysql>

The asc in the command above stands for ascending. We could have used desc if we wanted the data in descending order.

Now let's change our table data by using the update command. Before you read on, take a look at this command and see if you can figure out what it will do:

mysql> update dogs set breed='mutt' where dogID=1;
Query OK, 1 row affected (0.39 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql>

Got it? Take a look:

mysql> select * from dogs;
+-------- +------ +------ +
| breed   |  age  | dogID |
+-------- +------ +------ +
| mutt    |   8   |   1   |
| spaniel |   7   |   2   |
+-------- +------ +------ +
2 rows in set (0.01 sec)

mysql>

The update command set the breed field to the value of 'mutt' in the row with a dogID of 1. So we changed our poodle into a mutt. Cool!

Now, let's delete the spaniel:

mysql> delete from dogs where dogID=2;
Query OK, 1 row affected (0.33 sec)

mysql>

This delete command will delete all rows from the dogs table with a dogID of 2. Of course, there's only one row that fits that description. Here's the results:

mysql> select * from dogs;
+------ +------ +------ +
| breed |  age  | dogID |
+------ +------ +------ +
| mutt  |   8   |   1   |
+------ +------ +------ +
1 row in set (0.00 sec)

mysql>

As you can see, we're down to just the one row.

Here's a slightly more complex select command:

mysql> select * from dogs where age>4 AND dogID <20;
+------ +------ +------ +
| breed |  age  | dogID |
+------ +------ +------ +
| mutt  |   8   |   1   |
+------ +------ +------ +
1 row in set (0.34 sec)

mysql>

Reading the Documentation

Before we close, here are a few links to help you find your way through the official MySQL web site and documentation. For starters, you'll want to explore the top level at:

http://www.mysql.com

Now, click on the Developer Zone tab and spend a bit of time on this page. Once you have your sea legs, click on the Documentation sub-tab on the Developer Zone page. Here's the direct link:

http://dev.mysql.com/doc/

There are a number of important links on this page (See Figure 1). The second link is a link to a hyperlinked, online version of the MySQL documentation. This link is followed by various links that allow you to download the entire set of documentation to your hard drive. Start by exploring the second link, see if this form of documentation works for you. If not, download the form that works best for you.


Figure 1. The MySQL Reference Manual links

Assuming you've already installed MySQL and followed along with this column, a great place to start reading is this page:

http://dev.mysql.com/doc/mysql/en/tutorial.html

You should recognize a lot of the commands at this point and, hopefully, this column will have filled in enough of the picture so the stuff you haven't yet seen will make sense.

Until Next Month...

Try playing around with the dog table yourself. Add a bunch of rows, make the table more complex. In next month's column, we're going to use PHP to access a MySQL database and table from a web page. Using the mysql monitor is an excellent way to create your tables in the first place, and an excellent tool to check on and repair any data that gets a little futzy. But the real cool stuff happens when you mix MySQL and PHP. Fun, fun, fun!

Oh, and be sure to take a look at Ben Waldie's new Automator book on http://spiderworks.com. It totally rocks. Go, Ben!


Dave Mark is a long-time Mac developer and author and has written a number of books on Macintosh development. Dave has been writing for MacTech since its birth! Be sure to check out the new Learn C on the Macintosh, Mac OS X Edition at http://www.spiderworks.com.

 

Community Search:
MacTech Search:

Software Updates via MacUpdate

BusyContacts 1.6.4 - Fast, efficient con...
BusyContacts is a contact manager for OS X that makes creating, finding, and managing contacts faster and more efficient. It brings to contact management the same power, flexibility, and sharing... Read more
Steam 4.0 - Multiplayer and communicatio...
Steam is a digital distribution, digital rights management, multiplayer and communications platform developed by Valve Corporation. It is used to distribute a large number of games and related media... Read more
OmniGraffle Pro 7.19.3 - Create diagrams...
OmniGraffle Pro helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use... Read more
OmniGraffle 7.19.3 - Create diagrams, fl...
OmniGraffle helps you draw beautiful diagrams, family trees, flow charts, org charts, layouts, and (mathematically speaking) any other directed or non-directed graphs. We've had people use Graffle to... Read more
Hopper Disassembler 5.3.3- - Binary disa...
Hopper Disassembler is a binary disassembler, decompiler, and debugger for 32- and 64-bit executables. It will let you disassemble any binary you want, and provide you all the information about its... Read more
calibre 5.35.0 - Complete e-book library...
Calibre is a complete e-book library manager. Organize your collection, convert your books to multiple formats, and sync with all of your devices. Let Calibre be your multi-tasking digital librarian... Read more
Sound Studio 4.10.0 - Robust audio recor...
Sound Studio lets you easily record and professionally edit audio on your Mac. Easily rip vinyls and digitize cassette tapes, or record lectures and voice memos. Prepare for live shows with live... Read more
Sparkle Pro 4.0 - Visual website creator...
Sparkle Pro will change your mind if you thought building websites wasn't for you. Sparkle is the intuitive site builder that lets you create sites for your online portfolio, team or band pages, or... Read more
Dropbox 140.4.1951 - Cloud backup and sy...
Dropbox for Mac is a file hosting service that provides cloud storage, file synchronization, personal cloud, and client software. It is a modern workspace that allows you to get to all of your files... Read more
FotoMagico 6.0.5 - Powerful slideshow cr...
FotoMagico lets you create professional slideshows from your photos and music with just a few, simple mouse clicks. It sports a very clean and intuitive yet powerful user interface. High image... Read more

Latest Forum Discussions

See All

‘Horizon Chase’ China Spirit DLC Release...
Following the release of the excellent reveal of the Horizon Chase Senna Forever expansion, the game will be getting a new DLC on mobile platforms today. Today, the Horizon Chase China Spirit DLC pack will release on iOS and Android bringing in 9... | Read more »
‘PUZZLED’ from SNK and Hamster Is Out No...
Following ZED BLADE ACA NeoGeo earlier this month, SNK has brought over another game in the ACA NeoGeo series to both iOS and Android in the form of PUZZLED. SNK and Hamster originally brought the series to mobile with Samurai Shodown IV, Alpha... | Read more »
A House Full of Covid – The TouchArcade...
It’s been a rough week as both of our young children tested positive for Covid, and since recording this early on Friday my wife has tested positive now too. Thankfully the kids seemed to recover fairly quickly and are mostly back to normal, and I... | Read more »
TouchArcade Game of the Week: ‘Krispee S...
Krispee Street is a new hidden object game from Frosty Pop that is based on their popular and almost painfully sweet webcomic Krispee. This is one of the latest titles to be added to the Netflix Games catalog, which means you’ll need to log into... | Read more »
SwitchArcade Round-Up: ‘Escape Lala’, ‘B...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for January 21st, 2022. In today’s article, we’ve got a lot of new releases. A lot. There were eight on the schedule when I went to bed last night. There were twenty-four when I woke up... | Read more »
Beta Testers Needed for Huge Version 2.0...
Ya’ll remember Dungeon Raid, right? The phenomenal matching RPG hybrid that launched on mobile more than a decade ago, but was more or less abandoned by its developer only to die a slow death on the App Store before the 32-bit Appocalypse finally... | Read more »
‘Ark Legends’ Gives Players a Chance to...
It’s Airpods and Amazon gift cards galore as Melting Games opens pre-registration for Ark Legends. The upcoming mobile RPG is giving away tons of in-game goodies such as gold, energy, iron core, hero summon chest and rare iron core to players who... | Read more »
‘Nickelodeon Extreme Tennis’ Out Now on...
Nickelodeon Extreme Tennis () from Old Skull Games and Nickelodeon is this week’s new Apple Arcade release. Nickelodeon Extreme Tennis features characters from old and new Nickelodeon shows including SpongeBob, TMNT, and many more. The tennis game... | Read more »
SwitchArcade Round-Up: ‘RPGolf Legends’,...
Hello gentle readers, and welcome to the SwitchArcade Round-Up for January 20th, 2022. In today’s article, we’ve got a massive amount of new releases to check out. We’ve got summaries of all of them, from heaven to hell. We also have the lists of... | Read more »
‘Zed Blade ACA NEOGEO’ Review – Well, It...
SNK’s NEOGEO platform played host to a great many classics, both famous and under-the-radar. The Metal Slug games. The King of Fighters series. Magician Lord. Shock Troopers. Sengoku 3. NEO Turf Masters. Fatal Fury. Samurai Shodown. Twinkle Star... | Read more »

Price Scanner via MacPrices.net

Verizon’s 2022 iPad promo: $100-$310 off any...
Verizon has cellular-capable iPads on sale for $100-$310 off MSRP when purchased with an Unlimited service plan. Sale price is applied to your account monthly over a 24 or 30 month period, depending... Read more
Sunday Sale: Apple AirPods are on sale for up...
Amazon has Apple AirPods on sale for $10-$100 off MSRP today, depending on the model. All are in stock today with free delivery: – AirPods Max headphones (Blue): $449 $100 off MSRP – AirPods Max... Read more
These Apple resellers are offering 13″ M1 Mac...
Apple resellers are offering discounts on 13″ MacBook Pros with M1 Apple Silicon processors ranging up to $150 off MSRP. Here’s where to get one today: (1): Apple’s 13″ MacBook Pros with M1 Apple... Read more
Amazon lowers prices on select 13″ M1 MacBook...
Amazon has select Apple 13″ M1 MacBook Airs on sale for $150 off MSRP this weekend, starting at only $849. Their prices are the lowest available for new MacBook Airs today. Stock may come and go, so... Read more
Apple has 13″ M1 MacBook Airs back in stock s...
Apple has restocked a full line of 13″ M1 MacBook Airs, Certified Refurbished, starting at only $849 and up to $190 off original MSRP. These are the cheapest M1-powered MacBooks for sale today at... Read more
In stock and on sale! 16″ 10-Core M1 Pro MacB...
Amazon has new 16″ 10-Core/512GB M1 Pro MacBook Pros in stock today and on sale for $50 off MSRP including free shipping. Their prices are the lowest available for new M1 Pro 16″ MacBook Pro from any... Read more
Deal Alert!: 14″ M1 Pro with 10-Core CPU in s...
Amazon has the new 14″ M1 Pro MacBook Pro with a 10-Core CPU and 16-Core GPU in stock today and on sale for $2299.99 including free shipping. Their price is $200 off Apple’s standard MSRP, and it’s... Read more
Apple has 24-inch M1 iMacs (8-Core CPU/8-Core...
Apple has restocked a wide array of 24-inch M1 iMacs with 8-Core CPUs and 8-Core GPUs in their Certified Refurbished store. Models are available starting at only $1269 and range up to $260 off... Read more
Select 24″ M1 iMacs are on sale for $100 off...
Sales of Apple’s new 24″ M1 iMacs have been rare since its introduction, perhaps due to global supply issues. However, B&H is offering a $100 discount on select 24″ iMacs, and they’re in stock... Read more
M1 Mac minis are back in stock today at Apple...
Apple has M1-powered Mac minis available in their Certified Refurbished section starting at only $589 and up to $140 off MSRP. Each mini comes with Apple’s one-year warranty, and shipping is free: –... Read more

Jobs Board

Registered Nurse (RN) Employee Health PSJH -...
…is calling for a Registered Nurse (RN) Employee Health PSJH to our location in Apple Valley, CA.** We are seeking a Registered Nurse (RN) Employee Health PSJH to be Read more
Systems Administrator - Pearson (United State...
…and troubleshoot Windows operating systems (workstation and server), laptop computers, Apple iPads, Chromebooks and printers** + **Administer and troubleshoot all Read more
IT Assistant Level 1- IT Desktop Support Anal...
…providing tier-1 or better IT help desk support in a large Windows and Apple environment * Experience using IT Service Desk Management Software * Knowledge of IT Read more
Human Resources Business Partner PSJH - Provi...
…**is calling a** **Human Resources Business Partner, PSJH** **to our location in Apple Valley, CA.** **Applicants that meet qualifications will receive a text with Read more
Manager Community Health Investment Programs...
…is calling a Manager Community Health Investment Programs PSJH to our location in Apple Valley, CA.** **Qualified candidates will be invited to do a self-paced video Read more
All contents are Copyright 1984-2011 by Xplain Corporation. All rights reserved. Theme designed by Icreon.