
mysql Chapter Three (SQL)

mysql Chapter Three (SQL)
Documentation Version: 0.80
mysql Version: 3.20.16

Overview

The mysql database system offers a subset of the ANSI Entry level SQL92 
specification.  

The main goals of mysql are speed and robustness.  Adding transactions
would incur a significant speed and complexity penalty.  There is 
however currently work underway to give similar functionality in 
a different way.  This will probably be done by allowing an atomic
multi-table update.

The base upon which mysql is built is a set of routines that have been
used in a highly demanding production environment for many years.  While
mysql is currently still in development it already offers a rich and 
highly useful function set.  

The mysql database system is free for most uses, but if support is 
an issue for you, that is an option as well.  Having said that,
I urge people to register mysql if they can afford it, even if the
license would not require that they do so.  Everyone benefits by
supporting this sort of product.
 

ALTER TABLE


SYNOPSIS:

ALTER [IGNORE] TABLE table_name alter_specification [, alter_specification ...]

alter_specification:
        ADD [COLUMN] create_definition
or      CHANGE [COLUMN] old_column_name create_definition
or      ALTER [COLUMN] column_name { SET default | DROP DEFAULT }
or      DROP [COLUMN] column_name
or      DROP PRIMARY KEY
or      DROP INDEX key_name
        DROP FOREIGN KEY key_name

DESCRIPTION:

The ALTER TABLE command can be used to modify a table definition.  ALTER TABLE 
works by creating a temporary table and copying all information from the 
current table to the temporary one.  When the copy is done, the old table 
is deleted and the new table is renamed. This is done in such a way that 
all updates are automatically redirected to the new table.

While ALTER TABLE is working, the old table is available for other clients. 
Table updates/writes to the table are stalled and only executed after the 
new table is ready.  If IGNORE isn't specified then the copy will be aborted 
and rolled back if there are any unique keys duplicated in the new table. 

   CHANGE column_name, DROP column_name and DROP INDEX are mysql extensions to ANSI SQL. 
   [COLUMN] is optional and may be omitted.
   The ALTER [COLUMN] construct can be used to change or remove an old 
       default.
   ADD and CHANGE take the same create_definition as CREATE TABLE. See the 
    CREATE TABLE syntax. 
   If you drop a column_name that is part of a composite key, the key part will be removed.
        If all key parts are removed then the key will be removed. 
   DROP PRIMARY KEY drops the first UNIQUE key in a table. 
   CHANGE will do it's best to change existing information to the new format.
   The DROP FOREIGN KEY syntax is for planned functionality. Currently
       it does nothing.


You can use the C API function mysql_info(&MYSQL_RESULT) to find out how 
many records were copied and how many were deleted because of duplicated keys. 

To use ALTER TABLE you must have  select, insert, delete, update, 
create and drop privileges on the table. 

CREATE TABLE

SYNOPSIS:


CREATE TABLE table_name ( create_definition,... )

Where create_definition takes the following form:
create_definition:
        column_name type [NOT NULL] [DEFAULT default_value] [ PRIMARY KEY ]
  or    column_name type [NULL] [DEFAULT default_value] [ PRIMARY KEY ]
  or    PRIMARY (KEY|INDEX) [key_name] ( column_name,... )
  or    (KEY|INDEX) [key_name] ( column_name[length],...)
  or    INDEX [key_name] ( column_name[length],...)
  or    UNIQUE ( column_name[length],...)
  or    FOREIGN (KEY|INDEX) [key_name] ( column_name[length],...) REFERENCES table_name
        [ ON DELETE (RESTRICT | CASCADE | SET NULL) ]


DESCRIPTION:


In mysql all fields have an implicit DEFAULT if declared NOT NULL.  If
you do not give a DEFAULT when using NOT NULL, one will be automatically
assigned based on type.

The FOREIGN syntax is only for compatibility. It is automatically converted to
'KEY (column_name,...)'  This may change in the future.

The mysql CREATE TABLE command does not support the SQL CHECK keyword.

You must have create privileges to create a table.


Things to know:

 A number column may have the additional keyword AUTO_INCREMENT to
     automatically get the biggest value+1 for each insert where column value
     is 0 or NULL. (IE, if you try to insert a value of zero into a numeric
     column that has the AUTO_INCREMENT attribute, you will end up having a
     value that is one greater than the highest previously used value
     inserted.)
     Note that if you use AUTO_INCREMENT you may use it on only one field
     in a table.  Note also that this field must be declared as the primary
     key, and must be numeric.
 ZEROFILL means that number is pre-zeroed to maximal length.
     Example:
     INT(5) ZEROFILL  ; value 5 is retrieved as "00005"
 Key columns and TIMESTAMP columns can't be NULL.  For key columns the
     NULL attribute is silently removed.
 You can insert NULL for fields of type TIMESTAMP and for numeric fields
     with the AUTO_INCREMENT attribute.
 BLOB columns can't be keys.  One can't group on a BLOB.
 Currently one can't use a BLOB column in a WHERE clause.
 The total columns selected can be bigger than the communication buffer
     which has a default of 16384. This is a variable which can be changed at
     runtime.  "mysqld -O net_buffer_length=100000"
 Deleted records are in a linked list and subsequent inserts will reuse old
     positions.
 Each column that is allowed to accept a NULL value takes 1 extra bit.
 If there are no VARCHAR columns and no BLOBs then a fixed record format
     will be used.  You can expect significantly better performance when the
     fixed record format is used.  It will also be unnecessary to optimize
     your tables with isamchk when the fixed record format is being used. 
 If you use variable length records and do a lot of updates you should
     run 'isamchk -r table_name' now and then on
     the table to get a better layout.
     Try 'isamchk -ei table_name' for some statistics.
 The maximum record length can be calculated as follows:
     1+ Sum of column lengths + null_columns/8 + number of variable length
     columns.
 In some cases an attribute may silently change after creation:
     VARCHAR columns with a length of 1 or 2 are changed to CHAR.
     When using one VARCHAR columns all CHAR columns longer than 2 are
     changed to VARCHARS.
 On insert/update all strings (CHAR and VARCHAR) are silently chopped/padded
     to the maximal length given by CREATE. All end spaces are also automatically
     removed.
     For example VARCHAR(10) means that the column can contain strings with
     a length up to 10 characters.
 Something/0 gives a NULL value.
 REGEXP uses the ISOLATIN1 font when using character type functions, like
     [[:ALPHA:]].

Data Types
Fields must be of one of the following data types:
        INT [(length)] [UNSIGNED] [ZEROFILL]
        4 byte integer
        INTEGER [(length)] [UNSIGNED] [ZEROFILL]
        4 byte integer
        TINYINT [(length)] [UNSIGNED] [ZEROFILL]
        1 byte integer
        SMALLINT [(length)] [UNSIGNED] [ZEROFILL]
        2 byte integer
        MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL]
        3 byte integer
        BIGINT [(length)] [UNSIGNED] [ZEROFILL]
        8 byte integer (if compiler supports longlong)
        REAL [(length,dec)]
        float  (4 bytes)
        FLOAT  [(length,dec)] (FLOAT(8) is the same as DOUBLE.)
        float  (4 bytes) 
        DOUBLE [(length,dec)]
        double (4 or 8 bytes)  A packed floating point number.
        DECIMAL (length,dec)
        An unpacked floating point number.
        CHAR(NUM)
        Fixed width string (1 <= NUM <= 255)
        VARCHAR(NUM)
        Variable length string (1 <= NUM <= 255)
        TINYBLOB
        Binary object with a maximum length of 255
        BLOB
        Binary object with a maximum length of 65535
        MEDIUMBLOB
        Binary object with a maximum length of 16777216
        LONGBLOB
        Binary object with a maximum length of 2**32
	TIME 
	Store time information. Uses the "HH:MM:SS" syntax. 
	    May be updated with either a string or number.  The mysql
	    TIME type understands at least the following syntaxes.
	    
	       HH:MM:DD
	       HHMMDD
	       HHMM
	       HH
	    
         The TIME data type is three bytes long.
	DATE 
	Store date information. Uses the "YYYY-MM-DD" syntax. 
	    May be updated with either a string or a number, though
            you should probably use a string context as times and
            dates with leading zeroes will not be dealt with correctly
            currently.  

            The mysql DATE type understands at least the following syntaxes.
	    
	       YYYY-MM-DD (Note that '-' can in fact be ANY non numeric character)
	       YY-MM-DD (Note that '-' can in fact be ANY non numeric character)
	       YYMMDD
	       YYMM
	    
	    0000-00-00 through 9999-12-31 is the valid range for this data type.
	    Two digit years <70 are assumed to be > 2000.
	    The DATE data type is four bytes long.  
	DATETIME 
        A composite of DATE and TIME.  The DATETIME type is identical
            to TIMESTAMP with the following exceptions.
            
               When a record is inserted into a table containing
                   fields of type DATETIME, the DATETIME field(s) are NOT
                   changed.
                The valid range for the DATETIME field type is 
                    '0000-01-01 00:00:00' - '9999-12-31 23:59:59' 
                    when used in a string context, and 
                    '00000000000000' - '99991231235959' when used in 
                    a numeric context.
            
         The DATETIME type is eight bytes long.
            
        TIMESTAMP(NUM)
        Changes automatically on insert/update (YYMMDDHHMMSS)
            The length determines how the output is formatted.  You may
            optionally update a TIMESTAMP field when doing an insert.



The length field specifies how many total digits the number can have, 
while the dec field specifies how many of these digits will be after
the decimal place.  These values are only used for formating and 
the calculation of maximum column width.

A key may be a prefix of a string field.

Keys

A mysql table may have up to sixteen keys, each of which may consist
of up to fifteen fields.  The maximum supported key length in the
binary distribution is 120.  You can increase the key length by
changing N_MAX_KEY_LENGTH in the file nisam.h and recompiling.
Note that longer key lengths can lead to lower performance.

Keys may optionally be given names.  In the case of the primary key, the name
will always be PRIMARY.  If no key name is given during table 
creation, the default key name is the first column name with an
optional suffix (_2, _3, etc.) to make it unique.  The key
name can be used with the ALTER TABLE
command to drop the key.

When creating a key you may optionally specify that only the 
first N places of the value will be used.  For instance, if you
wanted to create a unique key on a field in which you only
cared if the first 40 characters where unique, you might do 
something like the following.

CREATE TABLE SomeTable (composite CHAR(200), INDEX comp_idx (composite(40))) ;

You might also wish to use this option on non unique fields, 
as it will greatly decrease the size of your index, and generally
lead to very little degradation in performance.

Note that his options is only available on CHAR and VARCHAR fields.

You may have one primary key per table.  If you have a multi-field key
you do not need to create a separate index for the first field in the
key as mysql's query optimizer will make use of the multi-field key
in this case.

In general multi-field keys should be used to optimize specific 
queries.  IE, all fields for a query should appear in the multi-field
key.

Keys must either be created at the time the table is defined, or 
by use of the ALTER TABLE.
command.


BLOBS

A BLOB is a "Binary Large OBject".  

As noted above, mysql supports four BLOB types.

tinyblob        (0-255 chars)
blob            (0-65535 chars)
mediumblob      (0-16777216 chars)
longblob        (0-2147483648 chars)

Note that there may be some constraints because of the message buffer
size.  The message buffer defaults to 16384, but may be changed by
the client.  You are of course also constrained by available memory.

You can change the buffer length when starting mysqld by
use of the -O option.  But remember that this space
will be alloced by each thread.


Example:

mysqld -O max_allowed_packet=max_blob_length

The mysql WIN95 ODBC driver defines BLOB:s as LONGVARCHAR.

Binary data in BLOBS

If you wish to insert binary data into a blob you must escape the 
following characters: 
	\0
	\\
	' or "




CREATE INDEX

SYNOPSIS:

CREATE [UNIQUE] INDEX index_name ON table_name ( column_name,... ) 

DESCRIPTION:

In mysql this command checks to see if the given index was created when the 
table was.  It does not actually create an index.  It is provided for 
compatibility reasons.

DELETE

SYNOPSIS:
DELETE FROM table_name WHERE where_definition

Where where_definition takes the following form:

where_definition:
    where_expr
or  where_expr [ AND | OR ] where_expr

And where_expr is as follows:
where_expr:
    column_name [> | >= | = | <> | <= | < ] 
    column_name_or_constant
or  column_name LIKE column_name_or_constant
or  column_name IS NULL
or  column_name IS NOT NULL
or  ( where_definition )

DESCRIPTION:
Delete records from a table.

 Returns the number of records affected.
 If you do a DELETE without a WHERE clause then the table is emptied
     and regenerated. In this case DELETE returns zero for the number of records 
     affected.



You must have delete privileges to delete records.

Things to know:
 All string comparisons are case independent. (ISO_8859_1) 
 LIKE is allowed on numeric columns. 
 Compare with explicit NULL (column == NULL) is the same as if IS NULL was
     used (column IS NULL). This was done to be consistent with mSQL. 

DESCRIBE

SYNOPSIS:

(DESCRIBE | DESC) table [column] 

DESCRIPTION:

Describe a table or column.  The optional column argument may be a 
column name or a string.  If column is a string, it may contain wild-cards.
This command is similar to the SHOW
command.


DROP

SYNOPSIS:
DROP TABLE table_name [table_name ...]

DESCRIPTION:

Destroys one or more tables.

If you just want to delete everything in a table and keep the definition 
you can use the delete command.

BEWARE! DROP TABLE will completely remove the named table(s) from your
system.  There is no going back.  (Unless you have backups of course.)

You must have delete privileges to use DROP.

DROP INDEX

SYNOPSIS:

DROP INDEX index_name 

DESCRIPTION:

This command doesn't do anything.  To actually drop an
index you will have to use the alter table command.


SELECT

SYNOPSIS:
SELECT [DISTINCT | ALL] select_expression,... [ FROM tables... [WHERE
where_definition ] [GROUP BY column,...] [ ORDER BY column [ASC | DESC] ,..]
HAVING full_where_definition [LIMIT [offset,] rows] [PROCEDURE procedure_name]]
[INTO OUTFILE 'file_name' ...]


Where where_definition is:
where_definition:
    where_expr
or  where_expr [ AND | OR ] where_expr

And where where_expr is as follows:
where_expr:
    column_name [> | >= | = | <> | <= | < ]
    column_name_or_constant
or  column_name LIKE column_name_or_constant
or  column_name IS NULL
or  column_name IS NOT NULL
or  ( where_definition )

DESCRIPTION:

The SELECT statement is used to perform queries on the database.  It's really the 
heart of the SQL language.  For a good general tutorial on how the SQL 
SELECT statement works check the following URL.

You must have select privileges to use SELECT.

http://w3.one.net/~jhoffman/sqltut.htm#Basics of the SELECT Statement

Functions

The select_expression can contain the following logic functions/operators.
        + - * /
        Basic math stuff.
        %
        Modulo (like in C)
        | &
        Bit functions. (48 bits in use)
        -
        Sign.
        ( )
        Parenthesis.
        BETWEEN(A,B,C)
        Is the same as (A >= B AND A <= C).
        BIT_COUNT()
        The number of bits.
        FIELD(N,a,b,c,d)
        Return a if N == 1, b if N == 2 a,b,c,d are strings.
        IF(A,B,C)
        If A is true (!= 0 and != NULL) then return B, else return C.
        IFNULL(A,B)
        If A is not null return A, else return B.
        ISNULL(A)
        Returns 1 if A is NULL else 0.  Same as '( A == NULL ').
        NOT !
        NOT,  returns TRUE (1) or FALSE (0).
        OR, AND
        Returns TRUE (1) or FALSE (0).
        SIGN()
        Returns -1, 0 or 1 (sign of argument).
        SUM()
        Return SUM of column.
        = <> <= < >= >
        Returns TRUE (1) or FALSE (0).
        expr LIKE expr
        Returns TRUE (1) or FALSE (0).
        expr NOT LIKE expr
        Returns TRUE (1) or FALSE (0).
        expr REGEXP expr
        Check string against extended regular expr.
        expr NOT REGEXP expr
        Check string against extended regular expr.



The select_expression can also contain one or more of the following math
functions.

        ABS()
        Absolute value.
        CEILING()
        ()
        EXP()
        ()
        FLOOR()
        ()
        FORMAT(nr,NUM)
        Format number to format '#,###,###.##' with NUM decimals.
        LOG()
        Return the log of a number.
        LOG10()
        ()
        MIN(),MAX()
        Min or max value of argument.  Variable arg count. Must have 2 or more arguments,
 else this is a group function.
        MOD()
        Modulo (same as %).
        POW()
        ()
        ROUND() 
        Round to the nearest whole number.
        RAND([integer_expr])
        Returns a random float, 0 <= x <= 1.0, using integer_expr as the seed value.
        SQRT() 
        Square root of argument


The select_expression can also contain one or more of the following string
functions.

        CONCAT()
        Concatenate strings. Variable arg count.
        INTERVAL(A,a,b,c,d)
        Return 1 if A == a, 2 if A == b...  If no match return 0.  
            A,a,b,c,d... are strings.
        INSERT(org,strt,len,new)
        Replace substring org[strt...len(gth)] with new.  First position in string=1.
        LCASE(A)
        Change A to lower case.
        LEFT()
        Get a string counting from the left.
        LENGTH()
        Get the length of string.
        LOCATE(A,B)
        Return position of B substring in A.
        LOCATE(A,B,C)
        Return position of B substring in A starting at C.
        LTRIM(str)
        Remove any leading spaces from str.
        REPLACE(A,B,C)
        Replace all occurrences of B in A with C.
        RIGHT()
        Get string counting from right.
        RTRIM(str)
        Remove any trailing spaces from str.
        SUBSTRING(A,B,C)
        Get substring from A starting at B with C chars.
        STRCMP()
        Returns 0 if the strings are the same.
        UCASE(A)
        Change A to upper case.


The select_expression can also contain one or more of the 
following miscellaneous functions.

        CURDATE()
        Return the current date.
        DATABASE()
        Return the name of the currently selected database.
        FROM_DAYS()
        Change a day number to a DATE.
        NOW()
        Return the current time. In format YYYYMMDDHHMMSS or "YYYY-MM-DD HH:MM:SS" 
            depending on whether NOW() is used in a number or string context. 
        PASSWORD()
        Calculate a password string.
        PERIOD_ADD(P:N)
        Add N months to period P (of type YYMM).
        PERIOD_DIFF(A,B)
        Returns months between A,B.
        TO_DAYS()
        Change a DATE (YYMMDD) to a day number.
        USER()
        Return the current user.
        WEEKDAY()
        Get weekday for date. (0 = Monday, 1 = Tuesday...)

Select group functions:

The following functions are supported in the GROUP clause:
        AVG()
        The average of the GROUP.
        SUM()
        The SUM of the GROUP.
        COUNT()
        The number of items in the GROUP.
        MIN()
        The minimum value in the GROUP.
        MAX()
        The maximum value in the GROUP.

  where MIN() and MAX() may take a string
  or a numeric argument. These can't be used in an expression, even
  if an argument may be an expression.
  EXAMPLE:
"SUM(value/10)" is allowed,
but "SUM(value)/10" is not (yet!).

 Strings are automatically converted to numbers and numbers to strings
  when needed ( la perl).
  To get the operators = <> <= >= < > 
  to work like in the WHERE
  statement, the left side determines if the test is done by numbers
  or by strings.  All string compares are done independent of case by
  ISO8859-1.
  For example:
  "a"  "b"   ; String compare
  "a"  0     ; String compare
  0   "a"    ; numerical compare
  a  5      ; If field is a CHAR type then the compare is by
                strings, else by numeric.
A column name does not have to have a table prefix if the given
column name is unique.
 In LIKE expressions % and _ may be
preceded with \  to get character match.
 A DATE is a string with one of the following syntaxes:
  
  YYMMDD        (Year is assumed to be 2000 if YY  70)
  YYYYMMDD
  YY.MM.DD      Where '.' may be any non-numerical separator
  YYYY.MM.DD    Where '.' may be any non-numerical separator
  
 IFNULL() and IF() return number or string values according to use.
 Order and group columns may be given as column names,
 column alias  or column number in a SELECT clause.
 The HAVING clause can take any fields or aliases in the
 select_expression.  It is applied last, just before items are
 sent to the client, without any optimization. Don't use it for
 items that should be in the WHERE clause.
 NOTE: You can't write (yet):
 SELECT user,MAX(salary) FROM users GROUP BY users HAVING max(salary)>10

 Instead, use something like the following: (This is also a good example of using a column alias.)
 SELECT user,MAX(salary) AS sum FROM users GROUP BY users
HAVING sum > 10
LIMIT takes one or two numeric arguments.  A single argument represents  
the maximum number of rows to return in a result. If two arguments are given the first 
argument is the offset to the first row to return, while the second is the maximum number 
of rows to return in the result. 
 INTO OUTFILE 'filename' writes the result set to a file. The file must not exist when this
command is issued. See the LOAD DATA INFILE section below for more details .


Joins

The SQL join feature gives one the ability to define relationships
between tables and retrieve information based on these relationships.

Relationships are listed in the FROM clause of a SELECT query.  Each
relationship is separated by a comma.  

Example:

$ mysql mysql

Welcome to the mysql monitor.  Commands ends with ; or \g.
Type 'help' for help.

mysql> SELECT db.user, db.delete_priv, user.user, user.delete_priv
    -> FROM db,user WHERE db.user = user.user;
The above query will join the tables db and user
by way of the user field. It will print out something similar to
the following:

+------+-------------+------+-------------+
| user | delete_priv | user | delete_priv |
+------+-------------+------+-------------+
|mke   | N           | mke  | N           |
+------+-------------+------+-------------+

The first two fields are actually db.user and
db.delete_priv, while the last two are user.user
and user.delete_priv.

Note that we use the table names in our query to specify exactly which
fields we are referring to.

Aliases can also be used to make the identity of column names clearer.
See the next section for details.

Aliases

The mysql database engine also supports the concept of aliases both
on tables and fields.

Table aliases are a standard part of the SQL language.  Let's look
at an example.

Example:
SELECT A.user,A.select_priv,A.insert_priv,A.update_priv FROM user A

The above is an example of using a table alias to shorten your query,
By declaring an alias that is shorter than the table name.  You use
the alias in the first part of the select, and define it in the FROM
by specifying the real table name, a space and the alias.  If you
have more than one table you wish to alias, simply add a comma after
each table name/alias pair.

If your you are using aliases with a query that will have a WHERE
clause you must use the alias in the WHERE clause rather than the 
real table name.

Field aliases are a mysql specific extension.  Here's an example.

Example:
SELECT user.user AS "User Name", user.delete_priv AS "Delete" FROM user;


One nice thing about field aliases is that they allow you to specify
a more user friendly label for your output.  The result of the 
above query might end up looking something like this:

+-----------+--------+
| User Name | Delete |
+-----------+--------+
| root      | Y      |
| mke       | N      |
| dummy     | N      |
| admin     | N      |
+-----------+--------+

It's a good idea to quote your aliases, as in the above example "Delete"
would have caused a parse error without quotes.  (This is because it 
is a SQL keyword.)

INSERT INTO

SYNOPSIS:

INSERT INTO table [ (column_name,...) ] VALUES (expression,...) ||
INSERT INTO table [ (column_name,...) ] SELECT ...

DESCRIPTION:

Insert data into a table.

 An expression may make use of any previous field in the column_name list 
     (or table if no column name list is given).
 When using SELECT you may NOT specify an ORDER BY clause.
 You may use the C API function mysql_info to retrieve a string similar 
     to the following:
     @result{Records: 220 Duplicates: 1 Warnings: 1}
     
        Records indicates the number of records returned by the SELECT.
        Duplicates is the number of rows that couldn't be inserted because
            of duplicated keys.
        Warnings is a count of the number of columns that NULL in the
            SELECT query, but have been declared NOT NULL in the table you
            are inserting the results in.  The columns will be assigned
            the default value for that column in the table you are 
            inserting into.  (Remember, in mysql all NOT NULL columns
            have a default value.  If you did not declare one at table
            creation time, one was automatically assigned based on 
            the field type.)
     
 If you wish to insert a NULL into a given value you should do it 
by not specifying a value for the field you wish to leave NULL.  
Example:
INSERT INTO Customer (customer_name,customer_contact) VALUES ("Joes Wholesale","Joe Smith")

This query would create a new record in the Customer table that would
contain an automatically generated customer_id, and the values specified
in the query.  All other fields would be NULL.


You must have insert privileges to use this command.

LOAD DATA INFILE

SYNOPSIS:

LOAD DATA INFILE syntax

DESCRIPTION:

Commands to read data from a textfile. 

Example: 

LOAD DATA INFILE 'customer.tab' [REPLACE | IGNORE] INTO table Customer
[fields [terminated by ',' [optionally] enclosed by '"' escaped by '\\' ]]
[lines terminated by '\n'] [(field list)] 

To write data to a textfile, use the SELECT ... INTO OUTFILE 'customer.tab' fields 
terminated by ',' enclosed by '"' escaped by '\\' lines terminated by '\n' syntax. 

	"fields terminated by" 
	has a default value of \t. 
	"fields [optionally] enclosed by"
	has a default value of ".
	"fields escaped by" 
	has a default value of '\\'. 
	"lines terminated by" 
	has a default value of '\n'. 


"fields terminated by" and "lines terminated by" may be more than 1 character. 

If "fields terminated by" and "fields enclosed by" are both empty strings (") you end up
with a fixed row size.

With a fixed row size NULL values are output-ed as a blank string. 

If you specify "optionally" in "enclosed by" and you don't use the the fixed row size, only 
strings will be enclosed with the given character by the SELECT ... INTO statement. 

If "escaped by" is not empty then the following characters will be prefixed with the escape 
character: "escaped by", ASCII 0, and the first character in any of "fields 
terminated by", 
"fields enclosed by" and "lines terminated by". 

If REPLACE is used the new row will replace all rows which have a same unique key. If IGNORE 
is used rows will be skipped if there already exists a record with an identical unique key. If none
of the above options is used an error will be issued and the rest of the textfile
will be ignored if a duplicate key is found. 

Some scenarios that are  not supported by LOAD DATA INFILE: 
    Fixed sized rows ("FIELDS TERMINATED BY" and "FIELDS ENCLOSED BY" are 
        both empty.) and BLOB fields. 
    If some of the separators are a prefix of another. 
    "FIELDS ESCAPED BY" is empty and the data contains one or more of the separators.


All rows are read into the table. If a row has too few fields the rest of the fields are set to 
default values. 

For security reasons the textfile must either reside in the database directory or be readable 
by all. 

If "FIELDS ENCLOSED BY" is not empty then NULL is read as a NULL value. If "FIELDS ESCAPED" is 
not empty then \N is also read as a NULL value. 

When the LOAD DATA query is done you can get the following info string by using the C API 
function mysql_info(). 

@result{Records: 1 Deleted: 0 Skiped: 0 Warnings: 0} 

The Warnings value is incremented for each column that can't be stored without loss of 
precision, for each column that didn't get a value from the read text line (This happens if 
a line is too short) and for each line which has more data than can fit into the given 
columns. 

UPDATE

SYNOPSIS:
UPDATE table SET column=expression,... WHERE where_definition

Where where_definition is:
where_definition:
    where_expr
or  where_expr [ AND | OR ] where_expr

And where where_expr is as follows:
where_expr:
    column_name [> | >= | = | <> | <= | < ]
    column_name_or_constant
or  column_name LIKE column_name_or_constant
or  column_name IS NULL
or  column_name IS NOT NULL
or  ( where_definition )

DESCRIPTION:

Update one or more fields in a mysql table.
 All updates are done from left to right. If one accesses a field in the
expression it uses the current value.  

Within the UPDATE on a single table all operations
are atomic.  For example, you can increment a counter value within a table
by simply adding one to it.  Some other examples...


EXAMPLES: 
UPDATE Widget_Table SET widgets_on_hand=widgets_on_hand - 300 where widget_id=3;
This query would subtract three hundred from widgets_on_hand value for the 
widget that is identified by the value three.

DELETE FROM Purchase_Order_Item WHERE purchase_order = 456

This query would delete all records from Purchase_Order_Item that
have a value of 456 for purchase_order.  Note
that in general you NEVER want to delete data from this sort of
database.  You create databases to keep track of information,
and even bad information could become useful at some point.  It is 
far better to have some sort of status code that you use when data
has become invalid for some reason.

You would also want to delete the entry in Purchase_Order for purchase_order
number four hundred and fifty six.  It's important to be sure that when
you do delete information, you get rid of all references to that information.
You're going to end up with a corrupted database if you don't.


You must have update privileges to use this command.

SHOW

SYNOPSIS:

SHOW DATABASES [LIKE wild]
SHOW KEYS FROM table_name
SHOW TABLES [FROM database] [LIKE wild]
SHOW [COLUMNS|FIELDS] FROM table [FROM database] [LIKE wild]


DESCRIPTION:
Display information about a mysql database.  "wild" is a SQL LIKE 
style regular expression.

Example:


$ mysql WidgetDB

Welcome to the mysql monitor.  Commands ends with ; or \g.
Type 'help' for help.

mysql> SHOW fields FROM Widget_Table from WidgetDB;

6 rows in set (0.34 sec)
+--------------------+--------------+------+-----+---------+----------------+
| Field              | Type         | Null | Key | Default | Extra          |
+--------------------+--------------+------+-----+---------+----------------+
| widget_id          | mediumint(8) |      | PRI | 0       | auto_increment |
| widget_name        | char(60)     |      | MUL |         |                |
| widget_color_id    | mediumint(8) |      | MUL | 0       |                |
| widget_size_id     | mediumint(8) |      |     | 0       |                |
| widgets_on_hand    | smallint(5)  |      |     | 0       |                |
| widget_price       | float(8,2)   |      |     | 0.00    |                |
| commission_percent | float(4,2)   |      |     | 0.00    |                |
+--------------------+--------------+------+-----+---------+----------------+

mysql> 
The first two fields are fairly obvious.  Null will
contain YES if that field can be NULL, Key tells
what if any index that field has, Default tells
you the default value that will be assigned to that field if none
is provided upon an INSERT, and Extra specifies
other attributes the field has, such as AUTO_INCREMENT.

About Strings

 A string may have '  or "  around it. 
 \ is an escape character. The following escape characters are recognized:

	\0
	ASCII zero

        \n
        newline
        \t
        tab
        \r
        return
        \b
        backspace
        \'
        '
        \"
        "
        \\
        \
        \%
        %   (This is used in wild-card strings to search for '%')
        \_
        _   (This is used in wild-card strings to search for '_')

Some valid strings are: 
        'hello'
        "hello"
        '""hello""'
        "'ello"
        "'e"l"lo"
        '\'hello'
        "This\nIs\nFour\nlines"


A ' inside a string may be written as '' 

A " inside a string may be written as "" 

The following will hopefully make all this a bit clearer. 

mysql> select 'hello',"'hello'",'""hello""','''h''e''l''l''o''',"hel""lo"; 
1 rows in set (0.01 sec)

+-------+---------+-----------+-------------+--------+
| hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo |
+-------+---------+-----------+-------------+--------+
| hello | 'hello' | ""hello"" | 'h'e'l'l'o' | hel"lo |
+-------+---------+-----------+-------------+--------+

Look very closely at the select line and compare each of the results with the query.

About Numbers
	 Integers consist of a sequence of digits.
	 Floats consist of a sequence of digits with an 
	     optional decimal place represented by the 
	     period "." character.

