MySQL Chapter Three (SQL)

Documentation Version: 0.95
MySQL Version: 3.20.29

Overview

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.

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

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.

To use ALTER TABLE you must have select, insert, delete, update, create and drop privileges on the 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) ]

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:]].

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.

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.

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)

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.

If you wish to insert binary data into a blob you must escape the following characters:

• \0
• \\
• ' or "

SYNOPSIS:

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

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.

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 )

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.

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

SYNOPSIS:

(DESCRIBE | DESC) table [column]

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.

SYNOPSIS:

DROP TABLE table_name [table_name ...]

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.

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]

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

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 )

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.

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

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;

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           |
+------+-------------+------+-------------+

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.

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      |
+-----------+--------+

SYNOPSIS:

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

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.

SYNOPSIS:

LOAD DATA INFILE syntax

Commands to read data from a textfile.

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.

SYNOPSIS:

SET OPTION SQL_VALUE_OPTON=value, ...

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

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.

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 )

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

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.

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]

Display information about a MySQL database. "wild" is a SQL LIKE style regular expression.

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

• 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 '_')

• 'hello'
• "hello"
• '""hello""'
• "'ello"
• "'e"l"lo"
• '\'hello'
• "This\nIs\nFour\nlines"

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 |
+-------+---------+-----------+-------------+--------+

• 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

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.