Re: [SLUG] HELP With MYSQL

[Kevin Waterson <kevin@xxxxxxxxxxx>]

On Sat, 4 May 2002 20:09:25 +1000
Nicholas O'Donnell <nicko@xxxxxxxxxxx> wrote:

> I would Like to learn how to write A MySQL Database but can't find any example scripts can some one please help?

mysqladmin create my_database -u <username> -p

will create a database named my_database

but then you need some tables in it, so we connect to mysql..

mysql my_database -u <username> -p


now we create some tables like this
CREATE TABLE animals(
ID INT(11) NOT NULL auto_increment,
Type varchar(127) default NULL,
Name varchar(255) default NULL,
PRIMARY KEY  (ID)
)

Ok, what we have here is a basic table for some animals eg dogs, cats, sysadmins etc.
The first row of the table will be a simple ID number for each critter that will be
unique and it will auto increment 1, 2, 3, 4, all by itself so each animal has a unique
ID number. It uses INT(11) which means the field will contain and integer(number) up to
eleven charaters long eg 99999999999.

Next we have the Type field, it will hold the Type of animal eg cat, dog.... and can be 
up to 127 characters long.

Then we have a Name field for the animals eg, Tiddles, Offenbach(my dogs name) etc.

Finally we have a Key, the manual has this to say on PRIMARY KEYs..
A PRIMARY KEY is a unique KEY with the extra constraint that all key columns must be defined as NOT NULL.
In MySQL the key is named PRIMARY. A table can have only one PRIMARY KEY. If you don't have a PRIMARY KEY and some applications ask for the PRIMARY KEY in your tables, MySQL will return the
first UNIQUE key, which doesn't have any NULL columns, as the PRIMARY KEY. 


now you have a table in your data base, and you may have many, you can put some data in it
To put data in or get data out, we use a query in sql (Structured Query Language) it trys to
be intuitive so it is relatively simple to pick up. So we want to INSERT a dog with the name of
Offenbach into our database. Here we use a INSERT

INSERT INTO animals (ID, Type, Name) VALUES ('', 'dog', 'Offenbach');

Lets enter a few more dogs
INSERT INTO animals (ID, Type, Name) VALUES ('', 'dog', 'Fido');
INSERT INTO animals (ID, Type, Name) VALUES ('', 'dog', 'Butch');
INSERT INTO animals (ID, Type, Name) VALUES ('', 'dog', 'Ralph');
INSERT INTO animals (ID, Type, Name) VALUES ('', 'dog', 'Pinata');

now we have 5 dogs in our table in the my_database database.

Lets enter some cats also..

INSERT INTO animals (ID, Type, Name) VALUES ('', 'cat', 'Tiddles');
INSERT INTO animals (ID, Type, Name) VALUES ('', 'cat', 'Ginger');
INSERT INTO animals (ID, Type, Name) VALUES ('', 'cat', 'Boots');
INSERT INTO animals (ID, Type, Name) VALUES ('', 'cat', 'Pinata');
INSERT INTO animals (ID, Type, Name) VALUES ('', 'cat', 'Football');

In our INSERT we have INSERTed our information INTO the animals table.
we have specified each table field ID, Type and Name and then given them
VALUES. Note there is no VALUE for the Id. This is because we specified
when we created the table that is was 'AUTO_INCREMENT', basically, MySQL
will do it for us and make sure the number is unique and advances by one
each time.

now we have the table 'populated' we can query the database...
Let imagine we wish to list all the animals in our table..
we can SELECT them all like this

SELECT * FROM animals;

We have used SELECT because we wish to select things. The * is a wildcard,
and means 'all'. FROM the table animals. Easy as.

But, what if we wanted only to select the dogs?
SELECT * FROM animals WHERE Type='dog';

Pretty self explanitory, you can do the same with cats.

Now we are having a party and wish to find an animal with the name Pinata.
We can do something like this

SELECT * FROM animals WHERE Name='Pinata';

This will give us all the animals with the name Pinata, as you will see,
there is a cat and a dog named Pinata, but we only want the dogs named
Pinata. So we need to be a little more specific.

SELECT * FROM animals WHERE Name='Pinata' AND Type='dog';

this will give us just the dogs named Pinata.

Well thats about it for today, Next week on MySQL 101 we will learn
how to humble Americans and how to reconcile the Israeli's and the Palistinians.

Bye
Kevin