MySQL Programming

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.

If you want to create a test table in your MySQL server, you can use the "mysql" program in a command window as shown in the following tutorial:

>cd mysqlin

>mysql -u root

Welcome to the MySQL monitor. Commands end with ; or g.

Your MySQL connection id is 8 to server version: 5.0.24-

mysql> use test

Database changed

mysql> CREATE TABLE test (message VARCHAR(80));

Query OK, 0 rows affected (0.13 sec)

mysql> INSERT INTO test

-> VALUES ('Welcome to GlobalGuideLine.com');

Query OK, 1 row affected (0.03 sec)

mysql> SELECT * FROM test;

+---------------------------------+

| message     |

+---------------------------------+

| Welcome to GlobalGuideLine.come |

+---------------------------------+

1 row in set (0.00 sec)

mysql> DROP TABLE test;

Query OK, 0 rows affected (0.01 sec)

mysql> quit

Bye

MySQL comes with the following programs as administration tools for you to manage your MySQL server:

► mysqld - MySQL server daemon. You can use "mysqld" to start your MySQL server.

► mysqladmin - A command-line interface for administrators to perform server administration tasks.

► mysql - A command-line interface for end users to manage user data objects.

► mysqlcheck - A command-line interface for administrators to check and repair tables.

► mysqlshow - A command-line interface for end users to see information on tables and columns.

► mysqldump - A command-line interface for administrators or end users to export data from the server to files.

► mysqlimport - A command-line interface for administrators or end users to load data files into tables program tool to load data into tables.

"mysqld" is MySQL server daemon program which runs quietly in background on your computer system. Invoking "mysqld" will start the MySQL server on your system. Terminating "mysqld" will shutdown the MySQL server. Here is a tutorial example of invoking "mysqld" with the "--console" option:

>cd mysqlin

>mysqld --console

... 21:52:54 InnoDB: Started; log sequence number 0 43655

... 21:52:54 [Note] mysqld: ready for connections.

Version: '5.0.24-community' socket: '' port: 3306

MySQL Community Edition (GPL)

The returning message indicates that "mysqld" running now, and your MySQL server is ready to take client connections. To know about "mysqld", read other parts of this FAQ collection.

If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database:

>cd mysqlin

>mysqlcheck -u root --analyze mysql

mysql.columns_priv   Table is already up to date

mysql.db    Table is already up to date

mysql.func    Table is already up to date

mysql.help_category   Table is already up to date

mysql.help_keyword   Table is already up to date

mysql.help_relation   Table is already up to date

mysql.help_topic   Table is already up to date

mysql.host    Table is already up to date

mysql.proc    Table is already up to date

mysql.tables_priv   Table is already up to date

mysql.time_zone    Table is already up to date

mysql.time_zone_leap_second  Table is already up to date

mysql.time_zone_name  Table is already up to date

mysql.time_zone_transition Table is already up to date

mysql.time_zone_transition_type Table is already up to date

mysql.user    Table is already up to date

"mysqlshow" is a command-line interface for end users to see information on tables and columns. Here are some sample commands supported by "mysqlshow":

► "mysqlshow" - Shows all the databases.

► "mysqlshow databaseName" - Shows all the tables in the specified database.

► "mysqlshow databaseName tableName" - Shows all the columns in the specified table.

► "mysqlshow --verbose" - Shows all the databases with extra information.

► "mysqlshow --verbose my%" - Shows all the databases who's names match the pattern "my%" with extra information.

► "mysqlshow --verbose mysql time%" - Shows all the tables who's names match the pattern "time%" in "mysql" database with extra information.

If you want to show table names with "mysqlshow", you need to specify a database name. The followings tutorial exercise shows you how to get all table names that match a pattern:

If you want analyze tables with "mysqlcheck", you need to use the "--analyze" option. The following tutorial exercise shows you how to analyze all tables in "mysql" database:

>cd mysqlin

>mysqlshow --verbose mysql time%

Database: mysql Wildcard: time%

+---------------------------+----------+

|  Tables   | Columns |

+---------------------------+----------+

| time_zone    |  2 |

| time_zone_leap_second  |  2 |

| time_zone_name  |  2 |

| time_zone_transition |  3 |

| time_zone_transition_type |  5 |

+---------------------------+----------+

5 rows in set.