MySQL Server Forum

Author

Basics

Bodger

2005-05-31, 1:23 pm

Hello all,
This might sound so rudimentary nobody pays any attention to it, but in all
the manuals I have scoured in the last week - trying to understand the
fundamentals of My SQL - it has never been discussed - and I need to know
the following:-
(1) "What is the structure of a database" (What is the directory tree like
on the server) -
(2)"What is the relationship to a "table" to the data contained in the
database.?:
(3)" Can a database support many "Tables" or are the tables data specific
like "user_file" containing records = "User Screen Name, User E-mail, User
Join date, etc" and can a data value be inserted into the database that is
derived from a previous field( user join date + 6 months)
(4) about 25 years ago I dabbled in a database from Campbell Systems for
the Spectrum home computer where a report was a selective print out of
certain data fields - what is that analogous to in today's terminology?
I am sure that when I understand these fundamental points I shall get on
like a house on fire.
Someone dial 911!

Malcolm Dew-Jones

2005-05-31, 8:23 pm

Bodger (nobody@spamcop.net) wrote:
: Hello all,
: This might sound so rudimentary nobody pays any attention to it, but in all
: the manuals I have scoured in the last week - trying to understand the
: fundamentals of My SQL - it has never been discussed - and I need to know
: the following:-
: (1) "What is the structure of a database" (What is the directory tree like
: on the server) -

I suspect this is documented in the reference manuals somewhere, mysql has
lots of documentation online at their site.

On my system, creating a database appears to create a directory in
/var/lib/mysql. The directory name is the database name. Each table
appears to create several files within that direcotry, with a name based
on the table name.

HOWEVER, I never ever normally look at these files. I didn't even
remember where they where excep by looking around. Mysql has programs
that maintain the data, so you don't ever need to know about the files
except for your backups (and even then, mysql has commands you can use for
doing database specific backups).

: (2)"What is the relationship to a "table" to the data contained in the
: database.?:

The rows (i.e. records) of data are stored in tables. (The tables, are
stored in the files mentioned above).

: (3)" Can a database support many "Tables" or are the tables data specific
: like "user_file" containing records = "User Screen Name, User E-mail, User
: Join date, etc" and can a data value be inserted into the database that is
: derived from a previous field( user join date + 6 months)

-- Can a database support many "Tables", etc, etc

Yes, that is the whole point.

: (4) about 25 years ago I dabbled in a database from Campbell Systems for
: the Spectrum home computer where a report was a selective print out of
: certain data fields - what is that analogous to in today's terminology?

Use the mysql program to login to a specific database.

Then use the SELECT statement to choose the data you want to see from
within the tables that contain the data.

--

This space not for rent.

Bill Karwin

2005-06-02, 9:23 am

Bodger wrote:
> Hello all,
> This might sound so rudimentary nobody pays any attention to it, but in all
> the manuals I have scoured in the last week - trying to understand the
> fundamentals of My SQL - it has never been discussed - and I need to know
> the following:-
> (1) "What is the structure of a database" (What is the directory tree like
> on the server) -

MySQL supports several storage types, the most commonly used are MyISAM
and InnoDB.

MyISAM stores databases under <datadir>/<database>/. For instance, if
your datadir is C:\Program Files\MySQL\MySQL Server 4.1\data, and your
database name is "accounts", then you'll see a subdirectory called
accounts below that data directory. In the accounts directory, for each
table, there are three files. <tablename>.frm stores the structure of
the table. <tablename>.MYD stores the data. <tablename>.MYI stores the
index structures.

InnoDB is different. By default, all table structures, data, and
indexes are collected together in one file called "ibdata1" in the data
dir. Multiple databases, each of which can consist of multiple tables,
all share this file. That's the default configuration; there are
options for storing InnoDB tables in individual files.

But this is usually academic, because as another poster points out, the
database management software takes care of these details of storage for you.

> (2)"What is the relationship to a "table" to the data contained in the
> database.?:

A table belongs to one database. A table stores a set of records
(rows). All records in a given table share the same set of fields
(columns). I'm not sure if this answers your question.

> (3)" Can a database support many "Tables" or are the tables data specific
> like "user_file" containing records = "User Screen Name, User E-mail, User
> Join date, etc" and

Each table has a set of fields, and all records in that table must have
those fields. But a database can contain many tables, each with their
own distinct set of fields.

You seem to be using the term "record", but the conventional term is
"field" for what you're describing. A record is a row of values, one
value for each field in the table.

> can a data value be inserted into the database that is
> derived from a previous field( user join date + 6 months)

Certainly. There are many ways to do it, just as there are usually many
ways to do a given task in any programming language. The best method
depends on the situation, like who's doing the work, how often does it
need to be done, how many data are being inserted, etc.

> (4) about 25 years ago I dabbled in a database from Campbell Systems for
> the Spectrum home computer where a report was a selective print out of
> certain data fields - what is that analogous to in today's terminology?

Sure. There are many fancy report-generation tools out there, but
basically they all do that task in common: based on some criteria and
choices that you give it, reports are selections from one or more
tables, related and calculated in various ways.

You might want to get an introductory book on SQL and database design.
It will be a lot quicker than waiting for newsgroup people to answer all
your questions. :-) "SQL for Dummies" is a pretty good choice (I'm not
trying to be condescending--it's actually a good book for introductory
concepts).

Regards,
Bill K.

Sponsored Links