Skip to Content.
Sympa Menu

internetworkers - Re: [internetworkers] mySQL learning curve

internetworkers AT lists.ibiblio.org

Subject: Internetworkers: http://www.ibiblio.org/internetworkers/

List archive

Chronological Thread  
  • From: Diana Duncan <art2mis AT nc.rr.com>
  • To: "Internetworkers: http://www.ibiblio.org/internetworkers/" <internetworkers AT lists.ibiblio.org>
  • Subject: Re: [internetworkers] mySQL learning curve
  • Date: Wed, 15 Oct 2003 10:46:49 -0400

Sorry this is so late in coming, but I can't let the technical inconsistencies in this go...even though I quite like the analogy, I just feel compelled to fix the code.

On Thursday, Oct 9, 2003, at 23:41 US/Eastern, Michael D. Thomas wrote:
<snip>

To play along with this example, each row in our table would represent a
guitar.

So, let's define some columns for our guitar table. Here are some
attributes of Guitar:

* name -- the name of the guitar.
* maker_id -- the id of who made it
* type -- electric or acoustic?
* tuned -- Is it tuned?
* guitar_id -- a random number to uniquely identify the guitar. It's the
'primary key' for the guitar.

OK, first, we need to define datatypes for these columns, which Michael did further on in his post, so I'm going to put that here for reference.

CREATE TABLE guitar
(guitar_id int primary key,
name varchar(100),
maker_id int,
type varchar(100),
tuned char(3));

Let's also quickly define the guitar_maker table as follows:

CREATE TABLE guitar_maker
(guitar_maker_id int primary key,
name varchar(100));

The SELECT analogy was all correct, so let's skip down to the UPDATE


If you can tune your guitar, that's an UPDATE.

Sure. The tuned attribute is updated from false to true.

UPDATE guitar SET tuned='yes' WHERE guitar_id=2346;

Don't forget the WHERE clause, or else you'll set tuned to 'yes' for all
of the rows!

Just wanted to point out that Michael is using 'yes' and 'no' here rather than actual booleans. This is because most databases don't support boolean as a column type, for some reason.

If you can put your guitar back in its case, that's an INSERT.

Um, no. An INSERT inserts a brand new row into the database. Thus, you'd
use an INSERT when you want to put a new guitar on your rack. Here's an
insert:

INSERT INTO guitar
VALUES ('Les Paul blue and white','Les Paul', 'electric', 'classic',

false);

So this INSERT statement is inconsistent with the definitions. To do this correctly, we first have to insert 'Let Paul' as a maker, then reference the maker_id. We also need a guitar_id, and what is the 'classic'?. Also, as pointed out above, we're not using booleans. Also, it is good practice in an INSERT statement to always, always, always specify the columns, just in case the column order changes in the future. (It can happen!) So, to fix this:

INSERT INTO guitar_maker (guitar_maker_id, name)
VALUES (1, 'Les Paul');

INSERT INTO guitar (guitar_id, name, maker_id, type, tuned)
VALUES (1, 'Les Paul blue and white', 1, 'electric', 'no');


If you can dash your guitar against a speaker in Townshendesque
mock-rage,
that's a DELETE.

Yup. Remember your WHERE clause or you'll delete everything in your
table:

DELETE FROM guitar
WHERE maker='Harmony';

This deletes all the guitars made by Harmony.

Again, this is inconsistent, as there is no maker column, but there is a maker_id column. To perform this delete correctly, do the following:

DELETE FROM guitar
WHERE maker_id = (SELECT guitar_maker_id
FROM guitar_maker
WHERE name = 'Harmony');


Anyway, that's enough. I guess my point is that while SQL is not inherently difficult, even experts can make mistakes and it's maybe not the simplest thing in the world. :) I wouldn't be surprised if I made a mistake here myself!

Diana





Archive powered by MHonArc 2.6.24.

Top of Page