MySQL Chapter Three (SQL)

MySQL Chapter Three (SQL)
Documentation Version: 0.95
MySQL Version: 3.20.29

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.  

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 its best to change existing information to the new format.
   The DROP FOREIGN KEY syntax is for planned functionality. Currently
       it does nothing.  In fact, none of the FOREIGN KEY stuff does anything.
   


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. 
<P>

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

<HR WIDTH="100%">
<CENTER><H2><A NAME="Create">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] [ 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. The REFERENCE keyword is also non 
functional at this time.  

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.)  

     If you wish to start at a value other than zero simply
     insert the desired starting value with the first record you insert into
     a given table.  There is currently no other way to do this.

     NOTE:
     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.  You can't group on a BLOB either.  It is
     however possible to use the MySQL string functions to group on subsections
     of a blob.
 It is now possible to use BLOB columns in a WHERE clause.
 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 MySQL will use a fixed 
     record format internally.  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 one or two are changed to CHAR.
     When using one VARCHAR columns all CHAR columns longer than two 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:
        BIGINT [(length)] [UNSIGNED] [ZEROFILL]
        8 byte integer (if compiler supports longlong)
        BLOB
        Binary object with a maximum length of 65535
        CHAR(NUM)
        Fixed width string (1 <= NUM <= 255). See also VARCHAR
	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.
	    Unlike TIMESTAMP, DATE assumes that two digit years are 0000
	    through 0099.  This isn't very useful in most cases.  Use
	    four digit years with fields of type DATE.
	    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.
        DECIMAL (length,dec)
        An unpacked floating point number.
        DOUBLE [(length,dec)]
        double (4 or 8 bytes)  A double precision number with a max length and a 
            fixed number of decimals.  Length and decimals are for formating and 
            the calculation of max column width.  
        FLOAT  [(precision)] 
        A floating point number. FLOAT(4) and FLOAT are single precision.
            FLOAT(8) is double precision.
        FLOAT  [(length,decimals)] 
        A single precision number with a max length and a fixed number of
            decimals.  (4 bytes) 
        INT [(length)] [UNSIGNED] [ZEROFILL]
        4 byte integer
        INTEGER [(length)] [UNSIGNED] [ZEROFILL]
        4 byte integer
        LONGBLOB
        Binary object with a maximum length of 2**32
        MEDIUMBLOB
        Binary object with a maximum length of 16777216
        MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL]
        3 byte integer
        REAL [(length,dec)]
        Identical to DOUBLE  (8 bytes)
        SMALLINT [(length)] [UNSIGNED] [ZEROFILL]
        2 byte integer
        TINYBLOB
        Binary object with a maximum length of 255
        TINYINT [(length)] [UNSIGNED] [ZEROFILL]
        1 byte integer
        VARCHAR(NUM)
        Variable length string (1 <= NUM <= 255)
	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.
            
        TIMESTAMP(NUM)
        Changes automatically on insert/update (YYMMDDHHMMSS)
            or (YYYYMMDDHHMMSS) The length determines how the output is 
            formatted.  You may optionally update a TIMESTAMP field when 
            doing an INSERT.  This is only useful when you want to set an 
            arbitrary date/time for the record.  During updates you should 
            either not specify a value for your TIMESTAMP field, or specify 
            NULL as the value to insert.  Otherwise you'll likely end up with 
            an invalid value for this field.

            When using mysql with ODBC and Access you should use a value
            of 14 for NUM, as this causes MySQL to always use four digit
            years.  A value of 12 would cause MySQL to use a two digit year.
            The default is 14.

            Note that in the case of tables with multiple TIMESTAMP fields
            only the fist such field will be updated automatically.




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.

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 field will be used.  For instance, if you
want to create a unique key on a field in which you only
care if the first 40 characters are unique, you could do 
something like the following.

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

It's also a good idea 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 a field is declared to 
be the PRIMARY KEY field an index is generated.  There is no need to 
define a normal key as well.  Furthermore, specifying additional
indexes that contain the PRIMARY key will do you no good as the PRIMARY
key will cause the index to be useless.

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

Because of the way MySQL uses B-Tree's internally you do not need to
declare keys that are a prefix of another key.  The optimizer will
find any usable prefix of a key and use it to perform the search.  For
example,  if you declare the following key: 

INDEX (first, second, third, fourth)

You also have also implicitly created the following keys:

(first, second, third)
(first, second)
(first)

Declaring unnecessary keys will only take up extra space and slow down
your queries.

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 is dynamically allocated.  You do have
to be aware of what 'max_allowed_packet' has been set to in the 
server and client.  By default this is 64K for the server and 
512K for the client.  You are 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.  If you wish to add a key use the 
    ALTER TABLE command.


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.


Things to know:
 All string comparisons are case independent. (ISO_8859_1) If
     you need to do a case sensitive search use REGEXP in a HAVING 
     clause.
 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. 


You must have delete privileges to delete records.


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.

   DROP INDEX is provided for compatibility reasons.  It fools many clients
   into thinking they have actually gotten what they asked for.  Primarily this
   comes up in conjunction with the ODBC driver.


GRANT

SYNOPSIS:
  GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE,
        REFERENCES (column list), USAGE))
        ON table TO user,... [WITH GRANT OPTION]

DESCRIPTION:
   The GRANT command doesn't actually do anything right now.  It always returns
   true and is provided primarily to fool some applications that use ODBC
   into thinking that the GRANT command they just issued actually did something.
   See Chapter Five for information on the MySQL privilege system.


SELECT

SYNOPSIS:
SELECT [STRAIGHT_JOIN] [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.

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

   In MySQL versions prior to 3.21.x the WHERE clause is very limited.  The
   HAVING clause will generally work where the WHERE clause does not.  Some
   examples of things that do not work in the WHERE clause are REGEXP and the
   ! operator.  Basically you cannot use functions with WHERE, but you can   
   with HAVING.

   HAVING is essentially a WHERE that is applied to the results.  It is 
   used mainly to narrow the scope of data returned by the query.

   You must have select privileges to use SELECT.

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.
        ELT(N,a,b,c,d)
        Return a if N == 1, b if N == 2, etc.  a,b,c,d are strings.
            EXAMPLE:
            ELT(3,"First","Second","Third","Fourth")
            Would return "Third".
        FIELD(Z,a,b,c)
        Return a if Z == a, b if Z == b, etc.  a,b,c,d are strings.
            EXAMPLE:
            FIELD("Second","First","Second","Third","Fourth")
            Would return "Second".
        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 two 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.
        STRCMP()
        Returns 0 if the strings are the same.
        SUBSTRING(A,B,C)
        Get substring from A starting at B with C chars.
        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.  Note that PERIOD_DIFF only works with
       dates in the form of YYMM or YYYMM.
   TO_DAYS()
   Change a DATE (YYMMDD) to a day number.
   UNIX_TIMESTAMP([date])
   Returns a unix timestamp if called without a date.  (Seconds 
       since GMT 1970.01.01 00:00:00.)  When called with a 
       TIMESTAMP column the UNIX_TIMESTAMP function returns the TIMESTAMP.
       date may also be a DATE string, a DATETIME string or a number
       in the form YYMMDD or YYYMMDD.

   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.
  If you need to do a case sensitive search use REGEXP in a HAVING 
  clause.
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 currently write:
 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.  Not that this can be a dangerous command as the daemon
runs as root.  It's best to only grant file privileges when absolutely 
necessary.
You may use a numeric value in your ORDER BY clause to specify the column
    you wish to order on.  IE,  if you wish to sort on the second column
    specified in your SELECT query you would say "... ORDER BY 2;"
    This is also useful when you have used a function in your SELECT.
    EXAMPLE:
     SELECT Widget_Table.widget_id, Widget_Table.widget_name, Purchase_Order_Item.widget_id, sum(Purchase_Order_Item.quantity) FROM Widget_Table, Purchase_Order_Item WHERE Widget_Table.widget_id = Purchase_Order_Item.widget_id GROUP BY Widget_Table.widget_name ORDER BY 4;


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.

You may link up to fifteen tables by way of a single join.

MySQL won't use keys effectively to join tables by way of fields that
are not of identical type.  This means you should always use the same
types for fields that are intended to be used in joins.

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 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 DELETE 
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 can also use SELECT to copy entries from one table to another.  MySQL
supports a limited form of sub queries to get this done.  See the
syntax in the SYNOPSIS section above for more information.


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. IE, your are reading a fixed field size non delimited file.

With a fixed row size NULL values are output-ed as a empty strings. 

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. 
    A separator being a prefix of another separator. 
    "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. Note
that this is capitol N, not lower case. 

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. 

You must have select and insert privileges in the user table to 
use this command.

SET OPTION

SYNOPSIS:
SET OPTION SQL_VALUE_OPTON=value, ...

DESCRIPTION:
Change or set the value of a MySQL option.  Options stay in effect
for the current session only.

MySQL supports the following options(s):

    SQL_SELECT_LIMIT=value 
    The maximum number of records to return from any SELECT. If a SELECT 
        has a LIMIT clause it overrides this statement. 

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.  Note this is 5C 30, not 5C 00!

        \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.
        In version 3.20.X all calculations are done with
            doubles, which means that large ulonglong variables
            will be truncated.  This is fixed in 3.21.X



About Table And Column Names

You may only use ISO8859-2 alpha characters (Or the character set
you defined during the initial configure and build process.), 
underscore, and 0-9 in column names. 

Hyphens, spaces, hashes and other special characters can not be used
as they would make the table or column impossible to use in a 
SELECT statement.