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.

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:
 
 

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:

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.
 
 

You must have delete privileges to delete records.

 Things to know:
 
 

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

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.
 
 

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:

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.
 
 

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

Some valid strings are:
  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