MySQL Programming Interview Preparation Guide

Download PDF

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com.

Mini SQL (mSQL) is a light weight relational database management system capable of providing rapid access to your data with very little overhead. mSQL is developed by Hughes Technologies Pty Ltd.

MySQL was started from mSQL and shared the same API.

SQL, SEQUEL (Structured English Query Language), is a language for RDBMS (Relational Database Management Systems). SQL was developed by IBM Corporation.

A table is a data storage structure with rows and columns.

A column defines one piece of data stored in all rows of the table.

A row is a unit of data with related data items stored as one item in one column in a table.

A primary key is a single column or multiple columns defined to have unique values that can be used as row identifications.

A foreign key is a single column or multiple columns defined to have values that can be mapped to a primary key in another table.

An index is a single column or multiple columns defined to have values pre-sorted to speed up data retrieval speed.

A view is a logical table defined by a query statement.

Join is data retrieval operation that combines rows from multiple tables under certain matching conditions to form a single row.

Join is data retrieval operation that combines multiple query outputs of the same structure into a single output.

ISAM (Indexed Sequential Access Method) was developed by IBM to store and retrieve data on secondary storage systems like tapes.

MyISAM is a storage engine used as the default storage engine for MySQL database. MyISAM is based on the ISAM (Indexed Sequential Access Method) concept and offers fast data storage and retrieval. But it is not transaction safe.

InnoDB is a transaction safe storage engine developed by Innobase Oy (an Oracle company now).

BDB (BerkeleyDB) is transaction safe storage engine originally developed at U.C. Berkeley. It is now developed by Sleepycat Software, Inc. (an Oracle company now).

CSV (Comma Separated Values) is a file format used to store database table contents, where one table row is stored as one line in the file, and each data field is separated with comma.

A transaction is a logical unit of work requested by a user to be applied to the database objects. MySQL server introduces the transaction concept to allow users to group one or more SQL statements into a single transaction, so that the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

Commit is a way to terminate a transaction with all database changes to be saved permanently to the database server.

Rollback is a way to terminate a transaction with all database changes not saving to the database server.

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. MySQL has the following main features:

* Works on many different platforms.
* APIs for C, C++, Eiffel, Java, Perl, PHP, Python, Ruby, and Tcl are available.
* Fully multi-threaded using kernel threads. It can easily use multiple CPUs if they are available.
* Provides transactional and non-transactional storage engines.
* Uses very fast B-tree disk tables (MyISAM) with index compression.
* A very fast thread-based memory allocation system.
* Very fast joins using an optimized one-sweep multi-join.
* In-memory hash tables, which are used as temporary tables.
* SQL functions are implemented using a highly optimized class library and should be as fast as possible. Usually there is no memory allocation at all after query initialization.
* The server is available as a separate program for use in a client/server networked environment. It is also available as a library that can be embedded (linked) into standalone applications. Such applications can be used in isolation or in environments where no network is available.

MySQL is an open source database management system developed by MySQL AB, http://www.mysql.com. You can download a copy and install it on your local computer very easily. Here is how you can do this:

* Go to http://dev.mysql.com/downloads/mysql/5.0.html.
* Select the "Windows" and "Without installer" version.
* Find a mirror site and download "mysql-noinstall-5.0.24-win32.zip".
* Unzip the file, you will get a new sub-directory, ".mysql-5.0.24-win32".
* Move and rename this sub-directory to mysql.
* The installation is done and your MySQL server is ready.

If you want to start the MySQL server, you can run the "mysqld" program in a command window as shown in the following tutorial:

>cd mysqlin
>mysqld

"mysqld" will run quietly without printing any message in you command window. So you will see nothing after entering the "mysqld" command. You should trust "mysqld" and believe that MySQL server is running ok on your local computer now.

Another way to start the MySQL server is double-click mysqlinmysqld.exe on your file explorer window.

If you want to know whether your MySQL server is alive, you can use the "mysqladmin" program in a command window as shown in the following tutorial:

>cd mysqlin
>mysqladmin -u root ping
mysqld is alive

The "mysqld is alive" message tells you that your MySQL server is running ok. If your MySQL server is not running, you will get a "connect ... failed" message.

If you want to know the version number of your MySQL server, you can use the "mysqladmin" program in a command window as shown in the following tutorial:

>cd mysqlin
>mysqladmin -u root version
mysqladmin Ver 8.41 Distrib 5.0.24, for Win32 on ia32
Copyright (C) 2000 MySQL AB & MySQL Finland AB
& TCX DataKonsult AB
...
<pre>Server version 5.0.24-community
Protocol version 10
Connection localhost via TCP/IP
TCP port 3306
Uptime: 25 min 9 sec</pre>
Threads: 1 Questions: 2 Slow queries: 0 Opens: 12
Flush tables: 1 Open tables: 6
Queries per second avg: 0.001

The output in the above example tells you that the version number is 5.0.24.