MySql Basics

Hi friends.,

I my previous chapters i have discussed about what Mysql is and its invention. In this chapter i would tell you from where to download mysql and some links related to it. i shall also give you the exact syntax that must be used to execute Mysql commands.

Frankly, Mysql commands are very easy to understand and work. Let me start of the introduction to mysql downloads.

Use this link to download MySql.
http://dev.mysql.com/downloads/mysql/

once you have installed MySql in you PC

To start or stop MySQL use the following commands:

• scripts/mysql.server stop
• scripts/mysql.server start

INTRODUCTION TO DATABASE DESIGN:

General Database and Table Properties:

Every MySQL server has a data directory under which it manages the contents of databases and
tables. The server represents these using directories and files under the data directory as follows:

MySQL associates each database with a directory under the data directory. (This means
that the data directory is the parent of all database directories.) A database directory has
the same name as the database that it represents. For example, a database named world
corresponds to a directory named world under the data directory. MySQL uses the
database directory to manage the components of the database—that is, its tables and
indexes.

A database may be empty, or have one or more tables. Databases cannot be
nested; one database cannot contain another.

Each table in a database consists of rows and columns. A table can be empty (it can have
zero rows of data), but it must have at least one column. A table may also be indexed to
improve query performance.

Every table is associated with a format file in the database
directory that contains the definition, or structure, of the table. The format filename is the
same as the table name, plus an .frm suffix.

For example, the format file for a table named
Country in the world database is named Country.frm and is located in the world
directory under the server's data directory. Depending on the table type, the storage
engine for a table might create additional files for the table.

Storage Engines and Table Types:

1.MyISAM Tables

The MyISAM storage engine manages tables that have the following characteristics:

Each MyISAM table is represented on disk by an .frm format file, as well as an .MYD
data file and an .MYI index file. All these files are located in the database directory.

MyISAM has the most flexible AUTO_INCREMENT column handling of all the table
types.

MyISAM tables can be used to set up MERGE tables.

MyISAM tables can be converted into fast, compressed, read-only tables.

MyISAM supports FULLTEXT searching.


2.InnoDB Tables

1.The InnoDB storage engine manages tables that have the following characteristics:
InnoDB table is represented on disk by an .frm format file in the database directory,

2. InnoDB supports transactions (using the SQL COMMIT and ROLLBACK statements)
with full ACID compliance.

3. InnoDB provides auto-recovery after a crash of the MySQL server or the host where the
server runs.

4. InnoDB supports foreign keys and referential integrity, including cascaded deletes and
updates.

5. MySQL manages query contention for InnoDB tables using multi-versioning and rowlevel
locking.

3. MERGE Tables

1. A MERGE table is a collection of identically structured MyISAM tables. Each MERGE
table is represented on disk by an .frm format file and an .MRG file that lists the names of
the constituent MyISAM files. Both files are located in the database directory.

2. Logically, a query on a MERGE table acts as a query on all the MyISAM tables of which it
consists.

3. A MERGE table creates a logical entity that can exceed the maximum MyISAM table size.


4.BDB (Berkeley DB) Tables

1.Each BDB table is represented on disk by an .frm format file and a .db file that stores data
and index information. Both files are located in the database directory.

2 BDB supports transactions (using the SQL COMMIT and ROLLBACK statements) with
full ACID compliance.

3.BDB provides auto-recovery after a crash of the MySQL server or the host where the
server runs.

4. MySQL manages query contention for BDB tables using page-level locking.

5.HEAP (MEMORY) Tables

1. Each HEAP table is represented on disk by an .frm format file in the database directory.
Table data and indexes are stored in memory.

2. In-memory storage results in very fast performance.

3.HEAP table contents do not survive a restart of the server. The table structure itself
survives, but the table contains zero data rows after a restart.

4. HEAP tables use up memory, so they should not be used for large tables.

5 MySQL manages query contention for HEAP tables using table-level locking. Deadlock
cannot occur.


Identifier Syntax

When you write SQL statements, you use names to refer to databases and tables as well as to
elements of tables such as columns and (sometimes) indexes. It's also possible to create aliases,
which act as synonyms for table and column names. All of these types of names are known as
identifiers.

Parameters to create Identifiers


Identifiers for databases, tables, columns, and indexes may be unquoted or quoted. An unquoted
identifier must follow these rules:

1. An identifier may contain all alphanumeric characters, the underline character (_), and
the dollar sign ($).

2. An identifier may begin with any of the legal characters, even a digit. However, it's best
to avoid identifiers that might be misinterpreted as constants.
For example, 1e3 might be taken as a number in scientific notation, whereas 0x1 might be interpreted as a hex constant, so neither is a good choice for an identifier.

3. An identifier cannot consist entirely of digits.



An alias identifier can include any character, but should be quoted if it's a reserved word (such as
SELECT or DESC), contains special characters, or consists entirely of digits. Aliases may be
quoted within single quotes ( '), double quotes, or back ticks.


Using Qualifiers for Table and Column Names


Column and table identifiers can be written in qualified form—that is, together with the identifier
of a higher-level element, with a period (.) separator.
A table name may be qualified with the name of the database to which it belongs. For example,
the Country table in the world database may be referred to as world.Country (note the .
separating the two identifiers in the name).

Well friends i this post i have discussed some topics that are very miniute but essential to be kept in mind during writting the code in mysql. In my next post i shall move into the concepts of mysql coding such as creating a database,table etc...