MySQL: the basics

8 November, 2006 (11:48) | MySQL | By: clive

Structure of a database

MySQL is a relational database management system or RDBMS for short.

R for relational - data from one table is joined to data from another table to provide a unique piece of information. The data from different tables have a relationship, something in common.
DB for database - this is the storage house for the data.

a piece of information is stored in a column
a record is a complete piece of information stored across a number of columns, each containing a piece of the record
a number of columns make up a table
each row in that table is a record
a number of tables make up database

A database, undergraduates, contains 2 tables, personal_details and courses. Personal_details has 3 columns, student_number, first_name and last_name. Courses has 2 columns, english and maths. One record is stored in the database. In tabular form it would look like this:

undergraduates
personal_details			courses
student_number	first_name	last_name	english	maths
1002345	Jack	Sprat	75	10

MS for management system. This is the software that allows you to insert, edit and delete the records.

The query language

SQL or structured query language is the language used to communicate with MySQL. It is a standard database language used by most databases.

MySQL architecture

MySQL is in essence two programs operating on 2 levels:

mysqld, which is the MySQL server, runs on the machine where the database is stored. It access the database in response to requests received from the client
the client program interfaces with the client and the server, issuing requests and receiving results

There are many client programs, each with a specific purpose. mysql Is the most common. mysqldump A backup program and mysqladmin are both administrative clients. MySQL also has a client-programming library allowing for 3rd party programs in a number of languages, including C, Perl and PHP.

The benefits of a client/server architecture

The server controls access and in so doing determines the order in which requests are performed
It allows access via a network and has a security system to protect the database

Of note is the fact that MySQL refers to the entire database management system while mysql refers to a particular client program.