MySQL Programming

If you want to load a data file directly into a table, you need to prepare the data file as one line per data row, and use tab character as the column delimiter. The data file name should match the target table name. The following is a good tutorial exercise on using "mysqlimport":

>cd mysqlin

>type emplinks.tab

www.GlobalGuideLine.com

www.mysql.com

>mysqlimport -u root test emplinks.tab

test.links: Records: 2 Deleted: 0 Skipped: 0 Warnings: 0

>mysql -u root -e "SELECT * FROM links" test

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

| name    |

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

| www.GlobalGuideLine.com |

| www.GlobalGuideLine.com |

| www.mysql.com   |

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

"mysql", official name is "MySQL monitor", is a command-line interface for end users to manage user data objects. "mysql" has the following main features:

* "mysql" is command line interface. It is not a Graphical User Interface (GUI).

* "mysql" supports all standard SQL Data Definition Language (DDL) commands for the server to execute.

► "mysql" supports all standard SQL Data Manipulation Language (DML) commands for the server to execute.

► "mysql" supports many of non-SQL commands that "mysql" will execute by itself.

► "mysql" provides access to the server-side help system.

► "mysql" allows command files to be executed in a batch mode.

► "mysql" allows query output to be formatted as HTML tables.

► "mysql" allows query output to be formatted as XML elements.

"mysql" offers a big list of command line options. Here are some commonly used options:

► "-?" - Displays a help message on how to use "mysql" and terminates the program.

► "-u userName" - Specifies a user name for the server to authenticate when connecting to the server.

► "-p[password]" - Specifies a password for the server to authenticate when connecting to the server.

► "-h hostName" - Specifies a host name where the MySQL server is running. The default host name "localhost".

► "-P portNumber" - Specifies a port number where the MySQL server is listening for connections. The default port number is 3306.

► "-e command" - Executes the specified command and terminates the program.

► "-t" - Specifies that the query output is displayed in text table format. This is the default display format for interactive mode.

► "-H" - Specifies that the query output is displayed in HTML table format.

► "-X" - Specifies that the query output is displayed in XML element format.

"mysql" supports only one optional command line argument, "database". But "mysql" allows the operating system to redirect input and output streams at the command line level. Here are some good examples:

► "mysql databaseName" - Starts "mysql" in interactive mode and use the specified database.

► "mysql < fileName" - Starts "mysql" in batch mode and executes all commands in the specified file.

► "mysql < fileName > fileName" - Starts "mysql" in batch mode, executes all commands in the specified file, and write output to the specified file.

Here is a tutorial exercise of how to use the command line argument to specify the database to use:

>cd mysqlin

>mysql -u root test

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

Your MySQL connection id is 4 to server version: 5.0.24

mysql> show tables;

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

| Tables_in_test |

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

| links  |

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

1 row in set (0.00 sec)

mysql> quit;

Bye

There are 4 SQL Data Definition Language (DDL) commands that are supported by "mysql". They are listed below with short descriptions:

► "CREATE dataObjectType dataObjectName" - Creates new databases, tables, views, triggers, indexes, and other data objects.

► "RENAME dataObjectType dataObjectName" - Renames existing databases, tables, views, triggers, indexes, and other data objects.

► "ALTER dataObjectType dataObjectName" - Alters properties of existing databases, tables, views, triggers, indexes, and other data objects.

► "DROP dataObjectType dataObjectName" - Drops existing databases, tables, views, triggers, indexes, and other data objects.

Here is a tutorial exercise of how to use DDL commands to create a database and a table:

>cd mysqlin

>mysql -u root

mysql> CREATE DATABASE ggl;

Query OK, 1 row affected (0.50 sec)

mysql> CREATE TABLE articles (name VARCHAR(80));

Query OK, 0 rows affected (0.25 sec)

mysql> DROP DATABASE ggl;

Query OK, 0 rows affected (0.41 sec)

There are 4 SQL Data Manipulation Language (DML) commands that are supported by "mysql". They are listed below with short descriptions:

* "INSERT INTO tableName ..." - Inserts new data rows into the specified table.

* "DELETE FROM tableName ..." - Deletes existing data rows from the specified table.

* "UPDATE tableName SET ..." - Updates existing data rows in the specified table.

* "SELECT expressionList FROM ..." - Selects rows from tables and returns rows of values resulted from the specified expression list.

Here is a tutorial exercise of how to use DML commands to insert and select data rows:

>cd mysqlin

>mysql -u root test

mysql> CREATE TABLE links (id INTEGER, name VARCHAR(80));

Query OK, 0 rows affected (0.10 sec)

mysql> INSERT INTO links VALUES (1, 'www.GlobalGuideLine.com');

Query OK, 1 row affected (0.02 sec)

mysql> SELECT * FROM links;

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

| id | name    |

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

| 1 | www.GlobalGuideLine.com |

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

1 row in set (0.04 sec)

There are many non-standard SQL commands that are supported by "mysql". Here is short list of some commonly used commands:

► "SHOW infoName" - Shows basic information of based on the specified information name.

► "SHOW infoName" - Shows basic information of based on the specified information name.

► "SET ..." - Sets new values to server or connection session variables.

► "GRANT ..." - Grants access privileges to users.

► "REVOKE ..." - Revokes access privileges from users.

► "CHECK TABLE tableName" - Checks the specified table for errors.

► "ANALYZE TABLE tableName" - Analyzes the specified table.

► "REPAIR TABLE tableName" - Repairs the specified table.

► "BACKUP TABLE tableName" - Backs up the specified table.

► "RESTORE TABLE tableName" - Restores the specified table.

► "USE databaseName" - Uses the specified database as the current database.

► "HELP topicName" - Returns help information on the specified topic.

While you are at the "mysql>" prompt, you can get help information from the server by using the "HELP" command. The tutorial exercise below shows several examples:

>cd mysqlin

>mysql -u root

mysql> HELP;

...

List of all MySQL commands:

Note that all text commands must be end with ';'

? (?) Synonym for `help'.

clear (c) Clear command.

connect ( ) Reconnect to the server.

...

mysql> HELP SHOW;

Name: 'SHOW'

Description:

SHOW has many forms that provide information about

databases, tables, columns, or status information about

the server. This section describes those following:

SHOW CREATE DATABASE db_name

SHOW CREATE FUNCTION funcname

SHOW CREATE PROCEDURE procname

SHOW CREATE TABLE tbl_name

SHOW DATABASES [LIKE 'pattern']

SHOW ENGINE engine_name {LOGS | STATUS }

...

mysql> HELP CREATE TABLE;

Name: 'CREATE TABLE'

Description:

Syntax:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name

(create_definition,...)

[table_option ...]

...

By default, "mysql" returns query output in text table format. If you want to receive query output in XML format, you need to use the "-X" command option. Here is a good tutorial exercise:

>cd mysqlin

>mysql -u root -X test

mysql> SELECT * FROM links;

<?xml version="1.0"?>

<resultset statement="SELECT * FROM links">

<row>

<field name="id">1</field>

<field name="name">www.GlobalGuideLine.com</field>

</row>

<row>

<field name="id">10</field>

<field name="name">www.GlobalGuideLine.com</field>

</row>

</resultset>

2 rows in set (0.00 sec)

Both CHAR and NCHAR are fixed length string data types. But they have the following differences:

► CHAR's full name is CHARACTER.

► NCHAR's full name is NATIONAL CHARACTER.

► By default, CHAR uses ASCII character set. So 1 character is always stored as 1 byte.

► By default, NCHAR uses Unicode character set. NCHAR data are stored in UTF8 format. So 1 character could be stored as 1 byte or upto 4 bytes.

► Both CHAR and NCHAR columns are defined with fixed lengths in units of characters.

The following column definitions are the same:

CREATE TABLE faq (Title NCHAR(80));

CREATE TABLE faq (Title NATIONAL CHAR(80));

CREATE TABLE faq (Title NATIONAL CHARACTER(80));

CREATE TABLE faq (Title CHAR(80) CHARACTER SET utf8);

CREATE TABLE faq (Title CHARACTER(80) CHARACTER SET utf8);

MySQL supports the following numeric data types:

► BIT(n) - An integer with n bits.

► BOOL same as BOOLEAN - Boolean values stored in 1 bit.

► TINYINT - A small integer stored in 1 byte.

► SMALLINT - A small integer stored in 2 bytes.

► MEDIUMINT - A medium integer stored in 3 bytes.

► INT same as INTEGER - An integer stored in 4 bytes.

► BIGINT - An integer stored in 8 bytes.

► FLOAT - A single-precision floating-point number stored in 4 bytes.

► DOUBLE same as DOUBLE PRECISION - A double-precision floating-point number stored in 8 bytes.

► REAL - Same DOUBLE by default.

► DECIMAL(m,d) - A fixed-point number with m as the precision (total number of digits) and d as the scale (number of digits after the decimal point).

► Date and Time Data Types - DATE, DATETIME, TIMESTAMP, TIME, YEAR.

MySQL supports the following date and time data types:

► DATE - A date in the range of '1000-01-01' and '9999-12-31'. Default DATE format is "YYYY-MM-DD".

► DATETIME - A date with the time of day in the range of '1000-01-01 00:00:00' and '9999-12-31 23:59:59'. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".

► TIMESTAMP - A timestamp. The range is '1970-01-01 00:00:00' to partway through the year 2037. Default DATETIME format is "YYYY-MM-DD HH:MM:SS".

► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".

► TIME - A time. The range is '-838:59:59' to '838:59:59'. Default TIME format is "HH:MM:SS".

► YEAR - A year in 4 digits in the range of 1901 and 2155. Default YEAR format is "YYYY".

There are a number of special characters that needs to be escaped (protected), if you want to include them in a character string. Here are some basic character escaping rules:

► The escape character () needs to be escaped as ().

► The single quote (') needs to be escaped as (') or ('') in single-quote quoted strings.

► The double quote (") needs to be escaped as (") or ("") in double-quote quoted strings.

► The wild card character for a single character (_) needs to be escaped as (_).

► The wild card character for multiple characters (%) needs to be escaped as (%).

► The tab character needs to be escaped as ( ).

► The new line character needs to be escaped as ( ).

► The carriage return character needs to be escaped as ( ).

Here are some examples of how to include special characters:

SELECT 'It''s Sunday!' FROM DUAL;

It's Sunday!

SELECT 'Allo, C'est moi.' FROM DUAL;

Allo, C'est moi.

SELECT 'Mon Tue Wed Thu Fri' FROM DUAL;

 Mon Tue  Wed  Thu  Fri