MySQL explained.

MySQL is a relational database management system, or RDBMS. This type of system stores one or more databases, each of which can contain tables of data of various types eg:

NAME               ADDRESS      CITY          STATE

Henry J. Tillman   321 Elm St   Sacramento      CA
John Smith         122 Oak St   New York City   NY
Amy Johnson        333 Poplar   Lane Chicago    IL

Relational Databases

While it is quite useful to store data in simple tables like this, the strength of a relational database system is that you can link data between multiple tables. For example you may have a separate table of names and phone numbers.

NAME               PHONE NO.

Henry J. Tillman   713-555-2395
John Smith         212-555-9344
Amy Johnson        312-555-2904

Because this table uses the same names as the address table, you can easily find the address and phone number for a name by connecting information in the two tables. By looking at several different tables, the database server can manage complex systems of data easily.

Client-Server Systems

MySQL, like most RDBMS systems, is a client-server system. In this type of system, a server manages the actual data storage. One or more clients can connect to the server, send it data or request data, and obtain.

Client-server systems are powerful because the server can focus on data management without worrying about the user interface or other issues. Different types of clients or multiple clients can connect to the server simultaneously. The MySQL client sends a request or query to the server. This may be a request for a certain record or group of records from a database, a request to add to the database, or any of several other types of requests. The server receives the query and sends a result back to the client.

The Anatomy of MySQL

MySQL consists of both client and server components.

Hardware

The MySQL server runs on many different types of hardware. PC systems running Linux or 32- bit Windows are the most common hardware used. The hardware required for the server depends on how busy the server will be with client requests.

Server Software

The MySQL server software is available from MySQL AB at www.mysql.com/. The software is free for noncommercial use under Open Source Gnu Public Licence(GPL). Users can download the software and install it on a Windows, Linux, or other machine.

Client Software

A simple MySQL monitor, is included with the server software. You can also use any client that know the correct protocols to communicate with the server.

Programming Languages

In addition to the existing MySQL clients, you can use programming languages such as PHI? and Perl to create applications that can communicate with a MySQL server. This allows you to create custom client software for virtually any purpose.

MySQL Terminology

Databases in general, and MySQL in particular, use a few terms you may be unfamiliar with. The following is an overview of some of the MySQL terms you will encounter.

MySQL Server

The MySQL server is a software service that runs on a computer, typically a Unix or Windows machine. The MySQL server accepts requests from clients and sends results back to them.

MySQL Client

A MYSQL client is an application that sends requests to a MySQL server and works with the resulting data. A simple command-line client comes with the MySQL server software. Applications written in in Web languages such as PHP and Perl can act as customised MySQL clients.

Database

A database is a file structure for organizing data, and consists of one or more tables. The MySQL server can store any number of databases,, and clients can work with them concurrently.

Table

A table is a unit of data storage within a database. Each database can contain one or more tables. Tables are defined with a set number and type of columns, and can contain any number of rows.

Column or Field

The columns, or fields, define the types of data stored in a database table. For example, a table that stores a mailing list may include columns called Name, Address, City, and State. A table can have one or more columns. Each column is defined with the type of data it will store--for example, a number, a string of text, or a date.

Row (or Record)

Each entry in the database is a row, and each row includes a data item for each column. For example, in a mailing list table, each combination of name and address would for one row of the table. Rows can be added, removed, or modified at any time. When clients request data, it is returned in rows. Rows are also known as records.

Primary Key

In order to access a single record in a database table, the server needs a unique identifier for each row. The primary key is a column chosen to be this unique identifier. For example, the name would be an appropriate primary key for an address database. Each table can have one primary key consisting of one or more columns.

Index

The database server stores extra information about the values used in the primary key field in an index. This allows for faster searching by this field. You can also specify that other fields in a table be indexed if they will be searched on frequently. These indexes are each known as secondary keys.

Query

Each request that a MySQL client sends to a server is called a query. Queries use SQL, or Structured Query Language. A query can request one or more rows of data, or request that an action be performed--for example, adding a new row or deleting an existing row.

SQL

SQL, or Structured Query Language, is the language used in MySQL queries. SQL is a standardized language that uses commands to perform various functions on the database server. Examples of SQL commands include INSERT to add a row to a table and SELECT to find one or more rows within a table.

Other Database Systems

MySQL, it is actually just one of several database management systems, also known as DBMS that you may find useful. Many of these systems use similar languages and can be used interchangeably and you can choose whichever server fits the needs of your application. MySQL is neither the simplest nor the most sophisticated of database systems. The following are some of the other popular systems, ranging from simpler than MySQL to far more sophisticated.

Flat File Database 

The most basic of databases does not use aDBMS at all. Applications can simply store data in one or more files. This is the traditional approach used by applications such as spreadsheets. Languages such as Perl have features that allow you to use DBMS-like features to manage simple files.

The advantage of this is that it can run on many platforms and does not require a database server but it offers few of the advantages of a true database management system.

PostgreSQL

PostgreSQL is another popular DBMS and an alternative to MYSQL. It is free and open source software. PostgreSQL supports some features of the SQL language beyond what MYSQL supports, and offers features like transaction support that may make it a better choice for high-end applications. PostgreSQL can be run on most Linux and UNIX systems.

Microsoft SQL Server 

While Access is a simple desktop database, Microsoft SQL Server is a full-featured client-server DBMS. The server software runs exclusively on Windows NT, 2000 and XP systems. The advantages of SQL Server include its wide support in Windows-based applications and its reliability, which has led to its use by many corporations, banks, and even stock exchanges.

MicrosoftAccess

The Microsoft Access is by far the most popular desktop database software suite and is available as part of the Microsoft office Suite Access uses an internal DBMS that stores data in local files, and the ability to share files between networked computers is limited. Nevertheless, it is it is useful for simple applications and has a friendly user interface. Access can also work as a client for Microsoft SQL Server, and includes tools to simplify the migration of Access databases to SQL Server.

Oracle

Oracle is a high-end DBMS popular for use in corporate databases and in larger and more critical Web sites. It supports transactions and other features for high reliability, availability, and speed. Its server software runs under UNIX or Windows systems. Visit Oracle's Web site at www.oracle.com for more information

mSQL

Mini SQL from Hughes Technologies, known more popularly as mSQL, is another popular choice as the back end of Web sites. Similar to MySQL in language and functionality, mSQL's advantages are also similar to those of MySQL Its server software is lightweight can run on machines with limited resources, and is supported by many programming languages. It is commercial product, but free copies are available for educational users and nonprofit organizations.

DB2

IBM's DB2 is aDBMS with a long history in mainframe computing. The latest version, DB2 Universal Database runs under AIX, Windows, and most UNIX systems. It is another popular choice for banks and other corporations with critical data storage needs, and includes features that make it work well with Web standards such as Java and XML.

Structured Query Language (SQL)

SQL (Structured Query Language) was first developed by IBM and is now a standard maintained by the ANSI, or American National Standards Institute. Most current DBMS systems follow the SQL standard to some degree. MySQL's query language is based on this standard. The following is an overview of some of the most important commands in MySQL. You can use these commands from any MySQL client, such as the MySQL Monitor

CREATE DATABASE

The CREATE DATABASE command creates the file structure for a new database and assigns it the name you specify. This command does not by itself store any data, but you must create a database before you can use it.

SHOW DATABASES

The SHOW DATABASES command displays a list of the databases that have been created on the MySQL server. The SHOW command can also be used to display tables for a database and other categories of information.

DROP DATABASE

The DROP DATABASE command deletes an existing database. Do not expect a friendly warning when using this command: The database is deleted instantly and without confirmation immediately, without confirmation, including all tables and rows. Use this command with extreme caution.

USE

The USE command selects a database for use by other commands. The database you specify with USE must already exist. The commands that work with tables and rows of data will only work if you have selected a database using this command.

CREATE TABLE

The CREATE TABLE command creates a table within the currently selected database. To create a table, you specify the fields (columns) it will contain and a field type for each one. The command can optionally include information about the keys and indexes available in the table. This command often requires multiple lines

DROP TABLE

The DROP TABLE command deletes a table from the currently selected database. All of the data stored in the table is permanently deleted and, as with the DROP DATABASE command, this happens instantly and without confirmation. Use this command with caution.

SHOW TABLES

The SHOW TABLES command displays a list of tables available in the currently selected database. You can use this command when you are unsure of the exact name of a table within the database.

INSERT

The INSERT command inserts a new row of data into an existing table within the currently selected database. You can specify a list of fields in the INSERT command along with values for each field.

UPDATE

The UPDATE command alters one or more existing rows of data within a table. The WHERE keyword can be used with this command to specify one or rows to alter. The command specifies new values for one or more of the fields within each row. These values can be based on existing fields or constant values specified within the command.

If you do not specify a WHERE clause, this command will update all of the rows in the table. While you will sometimes wish to update all of the rows, using UPDATE without a WHERE clause can cause loss of data if you are not careful.

DELETE

The DELETE command deletes one or more rows of data within an existing table. The WHERE clause should be used to specify the rows that will be deleted. Use this command with care: If you omit the WHERE clause, all rows of data within the table are deleted.

SELECT

The SELECT command retrieves one or more rows from a database. You can specify the columns to be returned or use the wildcard to return values for all columns in the order they appear in the table definition. You can use a WHERE clause to specify one or more conditions that rows must match in order to be returned. If you do not use a WHERE clause, the specified columns from all of the rows in the database are returned.

TO DOWNLOAD MYSQL

Before you can use MySQL, you need a working MySQL server. If you do not already have access to one you can download the MySQL software from the MySQL Web site, wwwmysql.com/, and install it on a computer running UNIX, Windows, or another supported operating system. MySQL is freely available for non-commercial use.

To access the downloadable files, click the Download link from the MySQL Web site are and then click the link for the version of MySQL

The current stable release is MySQL 3.23. Under this site's Download page, several different versions of MySQL are available. The version you should download depends on your operating system. For Windows, the choice is simple a ZIP file is available with everything you need for installation.

The download for Red Hat Linux and other versions that. support RPM packages is also simple. MySQL is divided, into several packages for its different components: server software, client software, shared libraries, development files, and benchmarking utilities. You can install these using the rpm command.

If you have a different version of Linux or UNIX, the next alternative is to download the binary distribution. These are stored as tar.gz files, one for each of the five components. The installation process for these is slightly more complex, but easier than installing from source code. Binary packages are available for Linux running under Intel (PC) systems, DEC Alpha, 1A64, Sun Sparc, and S/390. System.

Additional binary versions are available for other operating systems. If one is not available for your operating system, you can download the source code and compile and install it.

From: MySQL Wiley Publishing Inc. ISBN-7645-1692-2