Siward's Short Introduction to Mysql

 Version 1.2 , 2004-10-30

 Contains
   - basic information gathered from html docs in /usr/share/doc/mysql-doc/*.html
   - a lot of info from (34811 line) reference manual in info::mysql, but not full detail
 Formatted into shapes of
   a  143 line overview of what databases are, and
   a 1430 line tutorial on how to use them.
 Intended for people (like myself) that want to
   * learn what a database is,
   * learn to use a mysql database, and
   * have a reference to look up syntax and meaning of most frequently used queries.

 This document is provided to you for free under the terms of the GNU General Public License
   by it's copyright holder Siward de Groot <siward@wanadoo.nl> (that's me).

 The creators of MySQL provide their database and it's documentation for free.
 The Debian MySQL package maintainers make it easy to install and upgrade MySQL on my computer,
   they also did it for free.
 Thanks ! :-)

 This document was created in october 2004 ;
   it pertains to MySQL version 11.16 Distrib 3.23.49, for pc-linux-gnu (i686).

 Any errors in this document are most likely due to me ;
   i welcome constructive criticism.

 ==========================================================================================
 OVERVIEW :

 DATABASE

 Databases are (usually large) collections of data stored in electronical form.
 For example, an airline company has a database of
   - flights (from where to where at which date and time, which plane and crew)
   - reservations (made by who, payment received, how many free places left),
   - salary (hours worked, other benefits, accountnumber for payment),
   etcetera.

 Data is stored in tables that each have a name, for example 'flights',
   and each row in that table consists of named fields, for example 'from', 'to',
   and each field has a value, for example 'amsterdam', 'monday', '20:30',
 Rows are central concept of databases,
   each row describes a unit of what it's table stores.
 Fields can contain names of other tables,
   for example table 'flights' could have a field telling in which table reservations for that
   flight are registered.

 Database programs (often simply called 'databases') 
   can create a new table, add a row of data, change fields in a row, delete rows, etc.
 They also provide ways to enable users to connct to a database and find data in it,
   for example, a travel agent can connect to Alitalia's database via internet
   and find out which flights leave today from Roma and how many seats are available on them.

 Mysql is a professional database program
   that is used by major companys (for example Yahoo, Slashdot, NASA),
   and was developed out of a need for a higher performance database.
 It is distributed for free use under the terms of the GNU General Public License.
 For commercial use, installation and maintenance support are available,
   as is a special version that can handle extremely huge databases ;
   contact the Mysql team for specifications and prices,
   their homepage is at http://www.mysql.com .
 Mysql is well known in the free software community for it's
   ease of use with the Apache webserver.

 In addition to the 2 meanings of 'database' mentioned above (the data and the program),
   there is yet a third one, which refers to a datatype used by a database program :
 As mentioned above, tables can reference eachother,
   and a group of tables that can be used together efficiently is a database.
 Another advantage of having this 'database' datatype is that it makes it possible to
   give tables in different databases the same name.
 The general term 'database' can be used to refer to the data, the base, and anything else :-) .

 SQL

 There are many databases, of many companys,
   and needing to learn a new way to search for data for each of them is a very bad idea,
   so a (reasonably simple) syntax for searching and otherwise using a database
     has been standardized ;
   it is called 'structured query language' (SQL)
   and is described in standard 'SQL92' (standard was made in 1992).
 Mysql conforms to SQL92 and also provides some usefull additions to it.

 Searching a database usually consists of
   specifying a table,
   selecting rows from it that satisfy some criteria,
   and presenting fields from these rows that user is interested in ;
 Sql syntax reflects this structure,
   for example, to find flights of a certain cockpit crew, one could do
   'SELECT flightnumber, from, to FROM flights WHERE pilot="Roger" AND copilot="Wilco" ;'
 As another example of SQL, here is how to tell it which group of tables to use by default :
   'USE DATABASE databasename ;'
   so you better get used to this meaning of the word 'database'.

 FAST SEARCHING

 Most common operation on a database, and also one that costs most time,
   is searching for rows that match some criterion.
 Databases have special facilities for speeding this up ;
   a field can be declared to be a 'key' to a table when that table is created,
   then database program will create a hash (or somesuch) index of values of that field in all rows,
   so subsequent searches for values of that field will be much faster.
 Hashes are not created by default because they cost memory and time to create,
   and because usually only some fields's values are used as selection criteria in a search.

 TRANSACTIONS

 Often data in a database needs to be remotely accessible by multiple connections,
   in a way that keeps data correct.
 For example, if two travel-agents simultaneously use their internet connections to
   see whether places are free on a certain flight and try to book the last seat on it,
   both must get correct results,
 Therefore databases are managed by a server,
   ( a 'server' is a program that continually runs on a computer and
     can receive requests from other programs (called 'clients'),
     to which it responds by sending them a message, thus providing a service to them )
   which makes sure data is reliably stored and changed.
 For cases where data-integrity needs to be maintained (as in the travel-agent example),
   mysql can be run in 'transaction-safe' mode ;
 In this mode a sequence of mysql commands is committed to the database as a whole,
   whithout possibility of commands from another client's connection to be executed inbetween.
 For example,
   a travel agent that arranges a flight from A to C by
   reserving seats on a flight from A to B and on a connecting flight from B to C,
   where both flights have only one not-yet-reserved seat,
   specifying data (flight, price, travelagent, etc.)
   for addition as rows to tables 'flightAB.reservations' and 'flightBC.reservations'
   will finish adding both rows before a commit from an other travelagent is started.
   (or it will fail both row-additions if an other travelagent reserved one of these seats first).
 For the user (the travelagent) this means that
   a final 'commit' command is needed (when the traveller has agreed to pay),
   and that this commit command could fail (even when retrieved data said a seat was available)
     (which is a nuisance, but is better than booking two customers for the same seat).

 It is also possible to use a database without special precautions ;
   in this case every mysql command is executed immediately ;
   this is called 'auto-commit' mode.
 Auto-commit mode is faster and uses less memory than transaction-safe mode.

 To use transaction-safe mode, special tabletypes must be used, which take more memory.
 It is not possible to use transaction-safe mode on tables that were not meant for it.
 It is possible to use auto-commit mode on tables that support transaction-safe mode.
 Mysql uses auto-commit mode by default,
   and it's default tabletype, MyISAM, does not support transaction-safe operation.

 Generally, retrieving and updating values of tables can be done as transaction,
   but changing layout of tables or databases can not.

 FRONTEND
 
 In addition to the database programs themselves, there are 'frontend' programs,
   for example an X interface with buttons, and scrollbars,
   and the name of the travelagent in golden letters.
 Mysql comes standard with
   - a shell interface that supports multiline input
   - librarys to make it accessible from C, C++, Perl, Python, PHP, Java, Tcl, Eiffel, and ODBC.
     (multithreaded apps can lock tables for read and/or write access).
 The documentation that comes with Mysql mainly describes the shell interface ;
   working via one of the programming language APIs works similar,
   because, after having connected to the database,
   programs send mysql queries as strings to the mysql server.

 =========================================================================================
 TUTORIAL :

 This tutorial tries to
   - tell you all you need to know for writing a simple application,
   - be useable as a fast reference (like a cheat-sheet),
   and therefore presents a fair amount of detail ;
 If you want to start easier but slower, read the html tutorial in /usr/share/doc/mysql-doc/ .
 If you want more detail, the real documentation is in info under topic mysql.

 -----------------------------------------------------------------------------------------
 Table of contents :

 Line	Length	Name

   181	  44	 Start
   225	  16	 Notes on syntaxdescriptions
   241	  52	 General remarks on the SQL language
   293	  38	 Database
   331	  49	 Records, fields, tables, rows, and columns
   380	  15	 Tables
   395	  91	 Fieldtypes
   486	  60	 Keys
   546	  19	 Tabletypes
   565	  38	 Syntax of 'create table'
   613	  10	 Deleting a table
   623	 120	 Manipulating table data
   743	  20	 Changing table layout
   763	 136	 Retrieving data
   879	  12	 'As' and presenting additional data
   891	 211	 Expression syntax
  1102	  74	 Regular expressions and relevance matching
  1176	 309	 Standard functions
  1485	  34	 Using multiple tables
  1519	   8	 Script file  

 -----------------------------------------------------------------------------------------
 START

 Mysql is in packages 'mysql-server', 'mysql-client', and 'mysql-doc' ;
   these must all be installed if you want to learn to use mysql.

 To use mysql, mysql server must be running ofcourse,
   if it is not, it can be started with 'mysqld' from commandline.
 I read that it may be necessary to have mysql-admin give privileges to user
   for use of a specific table.
   I did not notice this in practice.
   Am i mysql-admin ? Can anyone on localhost access all tables ?
   Are such things set in a .mysqlrc file or something like it ?

 When mysql daemon is running, a client application can connect to it ;
   from C this is done by
     - compiling the program with -I/usr/include/mysql -lmysqlclient -lz
     - #include <mysql.h>
     - getting a validly initialized connectionhandle with mysql_init( MYSQL * H);
     - connecting with mysql_real_connect(H)
   from commandline simply type 'mysql',
     which starts a commandline interface that automatically connects to server.

 It is also possible to use some utility programs provided by mysql package,
   for example 'mysqldump', which writes contents of a table to stdout.
 Another aproach is to use 'mysql -e <statement>',
   which executes mysql statement <statement> and writes output to stdout.
 
 Once connected to mysql (via a programlanguage api or through shell interface),
   sql queries can be sent to mysql server,
   from shell by typing them in, followed by a ; and <enter>,
   from C by using mysql_query( MYSQL * H, const char * query) .
 At end of session, connection must be terminated (in shell type 'Quit', in C 'mysql_close()' ).

 Mysql queries form language of interacting with mysql database server,
   and i describe usual queries hereafter.
   I indent queries by one tab, to distinguish them from normal text.
 On shell, a ';' must be added to each query, to indicate end-of-this-query,
   so that queries can be typed on multiple lines ;
   From API this is not necessary.
 These ';'s are not part of query, so they are not shown below.
   If from shell you forget to add them, you'll be prompted for more input.
 On shell, a command can be cancelled by typing '\c<enter>' .

 -----------------------------------------------------------------------------------------
 NOTES ON SYNTAXDESCRIPTIONS

 In syntax descriptions,
   text in <>s describes what to put there, <>s should not appear in any real query.
     For example <number> means to put some number there.
   text in []s denotes an optional item
   ... means optional repetition(s) of the previous item
   ,,, means optional repetition(s) of previous item, separated by commas.
   | separates alternatives
   ()s are used to define a group for repetition or alternatives
     note that ()s are also used in mysql itself. where ambiguous it will be noted.
 For example :
   [-]<digit>...[.<digit>...][(E|e)[-]<digit>...]
   describes the syntax of a number in standard notation, like 1 or 3.1 or 1.2E-3 .

 -----------------------------------------------------------------------------------------
 GENERAL REMARKS ON THE SQL LANGUAGE

* Names and keywords

 Keywords in queries are not case-sensitive ;
   they are often written in uppercase to distinguish them from names (of tables etc.) .

 Names can contain all letters and digits plus _ and $ , but not the space character
   (most other characters have special meanings as math operator, string delimiter, etc.).
 Names can start with digits, but can not consist of only digits.
 Names consisting of digits and letters a-f can be mistaken for hex numbers, so dont use them.
 Names can be maximum 64 characters long.
 Default locale is ISO8859-1 (Latin alphabet, with the usual sort order).
 Some names (namely those of databases and tables) are case-sensitive on Linux, but you must
   - treat all names as case-insensitive
       (i.e. dont use different cases of same name for different things), and
   - stick to one caseform for referring to each named thing
       (i.e. dont use different cases of same name for same thing).

* Whitespace

 Extra whitespace (spaces, tabs or newlines) is allowed everywhere,
   except in the middle of keywords or names ofcourse.

* Comments

 Comments can be put in mysql language in Perl-style or C-style,
   C style comments are like: /* this is a comment */
     and can be placed at every place whitespace can be put.
     They can contain newlines, but, unlike in C,  not ';' or unmatched single- or double-quotes ;
       these would be interpreted as terminating a mysql statement or starting a string.
   Perl style comments are like: # this is a comment
     and continue to end of line.
     They can be placed at end of every line.
 Comments are usefull in scriptfiles
   (scriptfiles contain sequences of mysql queries that have to be executed repeatedly,
    so you don't need to type them every time).

* Reserved Words

 Mysql has 112 reserved words that are not allowed as names of tables, databases, fields, etc.,
   you can however use them by putting them in quotes (keywords are never quoted),
   but that is clumsy.
 Most of these are keywords in mysql queries.
 They include common words like: string, local, password, process, file, end.
 Learning them by heart is not a nice idea,
   and the only way to detect them is by mysql complaining about syntax errors.
   so if you get a syntax error that you don't understand
   in a line where you used a variablename for the first time,
   try quoting that name (or look in info for list of reserved words).

 -----------------------------------------------------------------------------------------
 DATABASE

 A database is a set of tables that can refer to eachother.
 A server can (and usually does) manage multiple databases.

 First thing to do after having established a connection to server
   is specify which database you want to access,
   this can be an existing database or a new database.

 To create a new database :
	create database <databasename>
 Now new database has been created, and you access it just like a pre-existing database.

 Databases can be deleted from server by :
	drop database [if exists] <databasename>
 This is a very DANGEROUS command, as all data in database will be deleted.

 To see which databases exist :
	show databases

 To use a database :
	use database <databasemae>
 If access is denied to you for this database,
   then as mysqladmin do :
	grant all on <databasename>.* to <your mysql name>
 To find your mysql name :
	select user()
   which reports <username>@<hostname> .

 All databases can really be used simultaneously,
   but in that case tablenames have to be given as <databasename>.<tablename>,
   and fields as <databasename>.<tablename>.<fieldname>.
 'use database' says which databasename is default.

 To see which database is currently default database :
	select database()

 --------------------------------------------------------------------------------------------------
 RECORDS, FIELDS, TABLES, ROWS, and COLUMNS, and a note on description syntax.

 Data in a database is stored in form of 'records',
   every record consists of a set of 'fields' which have a name, a type and a value
     (usual types are string, integer and date).
   a record thus also has a type, but this is not explicitly used in mysql.
 Records of same type can be collected in a table,
   where each row of that table is a record.
 Mysql docs seldom speak of 'record's, usually they are called 'row's,
   because 'table' is central concept in mysql ;
 In mysql, records only exist as rows in tables.

 A row in a table consists of fields, and each field has a name and a type.
 A table that has multiple rows thus consists of columns,
   each column containing fields of same name and type from all rows.
 Mysql docs speak of a 'columnname', which is same as fieldname.
 I will call these 'fieldname' in rest of this text, because that is shorter than 'columnname',
   but will use 'column' in constructs like 'all columns',
   as 'all fields' means something different.

 On many occasions, a list of rows or columns can be supplied as an argument to a command.
 These are usualy formed as
	<rowname> [,<rowname>]... 
   ie a comma-separated list in which extra whitespace is allowed.
 To make description more easily readable, i show such lists in form
	<rownames>
 This is true for any word in <>s that is plural, unless noted otherwise.

 --------------------------------------------------------------------------------------------------
 TABLES

 Next thing you need to do is determine which table(s) you want to use.
 This can be an existing table or a new table.

 To see which tables exist in current database :
	show tables

 To rename a table :
	rename table <tablename> to <new tablename>

 To delete a whole table :
	truncate <tablename>

 To create a new table :
	create table <tablename> ( <fieldspecs> )
   where a <fieldspec> can be
	<fieldname> <fieldtype>
   there are also some other fieldspecs possible (see furtheron).

 It is not strictly necessary to specify all fieldnames and types of table when creating a table,
   fields can be added to an existing table lateron. (see furtheron).
   At least one field must be specified at creation time however.
 Syntax of fieldspecs will be described lateron,
   basic fieldtypes are string, date, and number ;
   For now i just show an example :
	create table pet ( name varchar(20), birthdate date, numberoflegs int )

 A temporary table can be created by adding keyword 'temporary' between 'create' and 'table' ;
   such table disappears when connection to server is closed.

 Before more completely describing syntax of 'create table',
   i need to tell you about fieldtypes, keys, and tabletypes.

 --------------------------------------------------------------------------------------------------
 FIELDTYPES

 Fields have a type, which determines which kind of data you can put in it.
 Basic datatypes are : string, date, and number.

 Because small numbers can be stored in less memory than large number,
   and the same applies to short and long strings,
   several datatypes are available for each basic datatype.
 They are :

 * numeric types :
	tinyint		 8 bit integer
	smallint	16 bit integer
	mediumint	24 bit integer
	int		32 bit integer
	bigint		64 bit integer
	float		24 bit float with 8 bit exponent
			(9 digits after decimaldot)
	double		48 bit float with 16 bit exponent
			(17 digits after decimaldot)
	decimal(a,b)	string of digits, stored as characters
			a is countof chars, including sign and decimaldot,
			b is countof digits after decimaldot.

 * date and time types :
	these can be assigned to and used  as strings and as numbers.
	date		date in YYYY-MM-DD format,
	time		time in HH:MM:SS format,
	datetime	combination of date and time,
			in format YYYY-MM-DD HH:MM:SS
	timestamp	datetime with time of last operation
			shorter formats are possible (see info)
	year		year in 4-digit format (by default)

 * string types :
	char(<int>)	string of <int> characters
			<int> must be smaller than 256
			value is auto-padded with spaces or truncated to fit.
	bit,bool,char	synonyms of char(1)
	varchar(<int>)	variable length string, int argument specifies maximum length,
			  maximum length can not be more than 255.
			trailing spaces are auto-removed.
	tinytext	variable length string of max           255 chars
	text		variable length string of max        65.535 chars
	mediumtext	variable length string of max    16.777.215 chars
	longtext	variable length string of max 4.294.967.295 chars
	tinyblob, blob, mediumblob, longblob
			same as *text, except comparisons are case-insensitive
	enum		enumeration : a string that can only hold one of
			a number (max 256) of specified values.
	set		a string that can hold any number of values from
			a user-specified list of (max 64) possible values.

 Difference between varchar and tinytext/blob is that
   memoryblocklets for values of text/blob fields are allocated individually.

 "Values of enum and set must be specified when table is created".
   (maybe when column is created or altered ?)
 Trying to in a set or enum store a value that is not in specified list,
   result is value "" (empty string) being stored.
 Empty string is a special error value that is considered to be
   in specified list at position 0 (first element of enum is at position 1).
 In numeric context,
   an enum evaluates to index of current value in specified list, and
   a set evaluates to bitmask of values in set taken as an unsigned integer,
     presence of first value in list sets least significant bit, etcetera.
 A set can be assigned to by a commaseparated list of allowed strings,
   the whole list must be in quotes, individual elements don't need quotes.

 NULL :

 Fields can also contain special value null, which often means 'value is not set yet'.
 Setting a date field to null really sets it to current date.
 Null values are treated specially by math and logical operators, see furtheron for that.
 Fields can be specified as 'not null' in a 'create table' or 'change table' statement,
   then:
   it is an error to assign null to such a field,
   if default value would otherwise be null, it is now something else (often 0),
   such fields can be used for fast searches.

 USE IN CREATE TABLE :

 Type of a field is specified in a 'create table' statement as a fieldspecifier of form
	<fieldname> <fieldtype> [<fieldtypeoption>] ...
   where numeric    fields can have options 'unsigned' and 'zerofill',
   char and varchar fields can have option 'binary', and
   other fieldtypes have no options.
 Unsigned fields can only contain non-negative numbers,
 Zerofill fields are automatically filled with 0 when table is created,
 Binary   fields are case-sensitive in comparisons.

 --------------------------------------------------------------------------------------------------
 KEYS

 Professional databases need to be able to quickly locate data.
 Speeding up lookups in a table can be done by
   creating hashvalues of all fields that are used as a thing to search for.
 These hashvalues cost memory, and
   any combination of parts of fields in a database can be used to specify desired rows,
   so these hashvalues are not computed by default.
 If you want fast searches, you need to tell mysql that a specific (set of) field(s)
   will be frequently used for searching on,
   and you do this by adding a 'key' clause to a 'create table' statement
     (or to a 'change table' statement, see furtheron for that).
 'key' is the thing whose value is looked for when searching a table.
 'index' is a synonym of 'key'.

 There are 5 kinds of keys :
   simple keys, unique keys, primary keys, and fulltext keys.

 Simple keys can be anything, as just described,
 Unique keys add requirement that
   data in table to search does not contain identical values of that key ;
   trying to add to a table a row that would have an existing value for a unique key
   is refused, an errormessage is printed instead.
 Primary keys are unique keys of which each table can have no more than one,
   and with additional restriction that fields used in key must be
   fields whose data can not be null
     (null values are handled differently from normal values, see furtheron for that) ;
   such fields must be declared 'not null' in a 'create table' or 'change table' statement.
 Fulltext keys are a special type of index that is good for searching (large) pieces of text ;
   they can only be used for fieldtypes *text and varchar,
   and are only available in tables of type MyISAM (which is default, see furtheron).
 Keys, whether fulltext or not, on fields of type blob or text
   are only possible when using MyISAM tables,
   for fulltext keys whole piece of text is used for indexing,
   for other keys you _must_ specify maximum length (in characters)
     of textpiece to use for indexing (for syntax see next paragraph).

 Keys must be declared, so mysql can precompute their hashvalues.
 A simple  key is declared by adding a fieldspec of form
	 'key [<keyname>] (<fieldname>[(<length>)],,,)'.
   where optional <length> says that only first <length> characters of fieldvalue
     need to be considered when computing hash value.
 A unique  key is declared similarly, only 'key' is replaced by 'unique'.
 A primary key is declared similarly, only 'key' is replaced by 'primary key'.
 These fieldspecs are to be added in a 'create table' or 'alter table' statement.

 All fields of a primary key must be declared as 'not null',
   which is done by for such fields using a fieldspec of form '<fieldname> <fieldtype> not null'.

 Keys can also be created by 'create index' queries,
   but these are all aliases for 'alter table' queries (see furtheron for 'alter table'),
   so i do not show these.

 To see which fields, fieldtypes and attributes a table has :
	describe <tablename>
 If you are only interested in some fields, use :
	describe <tablename> <fieldname>
   where <fieldname> can contain sql wildcard characters % and _ (see furtheron).

 -----------------------------------------------------------------------------------------
 TABLETYPES

 It is also possible to specify type of table to create in a 'create table' statement.
 Default type is MyISAM, and it is best choice for tables that don't need to be transaction-safe,
   ie when you don't use transactions.
 For tabletypes to use for transaction-safe mode, see info.
 For temporary tables, using HEAP tables is advantageous, as these are stored in RAM,
   and are thus very fast.
 There are some restrictions on using HEAP tables :
   (new words will be explained furtheron)
   Heap tables can not have blob, text or auto_increment columns.
   Heap tables can not have a key on a 'null' column.
   Selecting from a heap table can not have an 'order by' on a key.
   Do use max_rows to limit RAM usage when using HEAP tables.
   HEAP tables are not recommended for permanent tables
     because a crash will cause loss of all data of this kind of table.
   Dont forget to drop table when done with it, to free memory.

 -----------------------------------------------------------------------------------------
 FULL SYNTAX OF A CREATE TABLE STATEMENT

	 create [temporary] table [if not exists] <tablename>
	   [<fieldspecs>] [<option>...] [<select statement>]

 'if not exists' prevents an error in case a table with that name already exists.

 A fieldspec can be one of :
   - <fieldname> <fieldtype> [<typeoption>]... [null|not null] [default <value>] [auto_increment]
   - <keyspecification> /* as shown in section 'keys' */

 Fieldname, fieldtype and typeoptions are as described in section 'fieldtypes'.
 Default says which value to put in a field if no data has been put in it yet,
   if no default is specified, then default is null,
   unless field is 'not null', in which case it is zero or empty string.
 Specifying auto_increment will set all fields in that column to
   highest value in any of those fields plus 1,
   whenever a query tries to store one of values null or 0 in one of these fields.
   TODO: that's what info says, test it!
   There are some restrictions on use of auto_increment ; see info.

 There are about a dozen possible options, i only mention two of them here :
   type=<tabletype>			  /* eg MyISAM, HEAP, OBDB */
   max_rows=<max number of rows in table> /* attempting to add more rows results in an error */

 Select statement is a general mysql statement that, as will be described furtheron,
   is used for selecting data to present from a table ;
   In 'create table' it has a different meaning in that it does not copy data but fieldspecs
     effect of it is that
     - selected fields are added to new table, and
     - data in selected fields in other table is copied to new table
     - keys of other table are not copied.
   Eg	create table newtable (a int) select b from othertable
     copies fieldspecification of field b from table othertable into specification of newtable
     and copies data from column othertable.b into column newtable.b .

   If table can be used by multiple clients,
     lock table that is to be copied from for read access before copying, by
	lock tables <tablenames> read
     when after copying it is no longer needed to ascertain copy to be a copy, unlock tables with
	unlock tables <tablenames>


 Example:
	create temporary table tmptable name varchar not null type=heap max_rows=10


 -----------------------------------------------------------------------------------------
 DELETING A TABLE

 To delete a whole table, you can
   * delete all rows of it (as described in next section), or
   * create a new empty table, and use it instead of old table, by
	truncate <tablename>
 Truncate is much faster, and gives possibility of recreating table after deletion,
   but it implies an immediate commit, an therefore is not transaction-safe.

 -----------------------------------------------------------------------------------------
 MANIPULATING TABLE DATA

 To be able to use the data in a table, that data has to have been put in that table first.
 This can be done by typing it in, or by reading it from a file,
   or by getting it from another table (that will be described later, under 'retrieving data').

 To add a row of data to a table by typing it in :
	insert into <tablename> values ('<field1value>', '<field2value>', ... )...
   where values in ()s are for 1 row,
   and fieldvalue null is represented as NULL .
 If not all fields need to be filled in, or they must be filled in in a different order, use :
	insert into <tablename> set <fieldname>=<expression>, <fieldname>=<expression>, ...

 Fieldvalues can be expressions that can reference values in tables in database.
 Optionally 'insert' can be followed by keyword 'delayed', which makes it non-blocking
   (so it will update table when it is not in use by another thread).

 Names and dates must be quoted with either single or double quotes,
   ints do not need to be quoted.
 Dates must be in format yyyy-mm-dd , but any leading zeros can be left away.


 To read data from a file and add it as rows to a table,
   file must conform to syntax mysql uses for reading files,
   or parameters of that syntax must be specified in 'load data' statement.
 Default syntax consists of :
   * successive fieldvalues are separated by a tab
   * successive rows are separated by a newline
   * NULL values are represented as \N .
   If there are tabs and/or newlines in fieldvalues, they must be either quoted or escaped.
 What to use for field- and row-separators can be specified

 Syntax of query to issue for loading data from a file into a table is (all on one line) :
	load data [low_priority|concurrent] [local] infile <filename> [replace|ignore]
	into table <tablename>
	[fields [terminated by <string>] [enclosed by <string>] [escaped by <character>]]

 Low_priority waits until no other clients are reading from table before adding data from file.
 Concurrent says that it's ok for other threads to read while data is being loaded.
 Local says that file is on client's computer ; else it be searched for on server's host.
 Filename can be a filename or a pipe (so you can read output of gunzip of compressed files eg),
   but documentation does not say how this should be specified ;
   maybe 'infile "gunzip <filename> |"' ?
   There is also utility program mysqlimport that can be used from shell ;
     it does not have a manpage, but 'mysqlimport --help' is informative.
   There is also possibility to from shell use
	mysql -e "LOAD DATA INFILE filename INTO TABLE tablename" filename
 Replace and Ignore are for deciding what to do when
   a table has a unique key and one of rows to be added has an existing value for that key.
   Old row with that value will be replaced, or new row will be ignored.
   Default is: for local files: ignore ; for non-local files: error.
 Fields clause is for using non-default field- and row-separators,
   and can also be used for removing quotes from fieldvalues in file.
   String in 'terminated by' is a real string
     (can be empty, in which case rowterminator is fieldterminator),
   String in 'enclosed by' can be a single character or an empty string,
     empty string is interpreted as meaning quotes around empty string.
 Info has some additional notes on consecutive quotes in quoted strings,
   stupid settings in 'fields' clause, and representation of null value.

 When a record in file has less fields than table, rest of fields get default value.
 When there are more fields in file than in table, extra ones are ignored.


 To write data to a file :
	<select clause> to outfile <filename>
 Select will be described furtheron.
 Field- and record-separators to use can be specified in this command,
   in same way as was described for loading from file ; see info for full syntax.
 Files written are written as the user that runs mysql ;
   in order for mysql to be able to read these files, you must not run as root.


 To put selected data from a table into another table,
   a select clause is needed, which will be described furtheron in section 'retrieving data' ;
 Here i show syntax (without explaining select) :
	insert into <tablename> select <rest of select clause>
 Select clause should result in as many fieldvalues as there are fields in target table
   (otherewise they probably get default values).


 To change a field (or a column of fields) in a table :
	update <tablename> set <fieldname> to <new value> where <criterion>
 Where 'where' clause is optional,
   it is often used to uniquely identify row to change,
   eg 'name = <rowname>' .
 <new value> can be any expression, and can refer to old value as <fieldname> ;
   if expression is a commaseparated list of expressions,
   then it is evaluated from left to right,
     and "old" value is value after execution of last subexpression.
 The update command evaluates to the number of rows that it changed.
 I found a slightly different syntax in info :
	update <tablename> set <fieldname>=<expression> [,<fieldname>=<expression>]... where <..>


 To replace a row of a table, there is 'replace' statement ;
   it has 3 different syntaci, which are unclearly documented.
 I note what i think they mean (TODO try this out) :
	replace <tablename> [<fieldnames>] values <expresssions>
	replace <tablename> [<fieldnames>] select ...
	replace <tablename> set <fieldname>=<expression>[,<fieldname>=<expression>]...
 1st one replaces (some fields of) rows by specified values,
 2nd one replaces (some fields of) rows by a using value(s) from row(s),
 3rd one does same as 1st one, just has a different syntax.
 I expect that a where clause can be used to affect only some rows.


 To delete a row from a table :
	delete from <tablename> [where <condition>]
 This deletes all rows for which <condition> is satisfied.
 Delete evaluates to countof rows deleted,
   unless no where clause is present (so whole table is deleted),
   in which case it evaluates to 0 currently (but this will change in version 4).
 Deleting a whole table can be done much faster by 'truncate <tablename>'.
 Delete statement removes rows from the table,
   but does not free memory previously used by these rows ;
   use myisamchk() or 'optimize table' for that.


 -----------------------------------------------------------------------------------------
 CHANGING TABLE LAYOUT :


 To add columns or keys to a table :
	alter table <tablename> add [column|key|unique|primary key] <fieldspecs>
 To change name of existing columns :
	alter table <tablename> change [column] <oldfieldname> <newfieldname> <fieldtype>
 To change type of existing columns :
	alter table <tablename> modify <fieldname> <oldtype> <newtype>
 To remove existing keys or columns : 
	alter table <tablename> drop (key <keyname>|primary key|[column] <fieldname>)

 Less usual qualities of a table can also be altered with 'alter table',
   but have slightly different syntax ; see info for that.
 Multiple 'add', 'change', 'modify', and 'drop' clauses can be in an 'alter table' statement.
 Shortening (part of) a string field that is used as a key is not possible.
 It is not possible to remove all columns of a table ; use 'drop table' for that.


 --------------------------------------------------------------------------------------------------
 RETRIEVING DATA :

 To get data from a table, you can get everything that is in the table,
   or you can select part of table that you want.
 Selecting can be done by rows and/or columns,
   you can select rows/columns that satisfy certain criteria,
   and you can specify a list of rows or columns by separating row- or field-names by commas.
 When using API, selection does not immediately retrieve results from server,
   instead they can be used as target of a subsequent selection,
     to select a subset of first selection,
   and can thereafter be retrieved explicitly.
 When using shell, results are shown immediately,
   but you can give results a name,
   in order to be able to specify them as target of a subsequent selection.

 Usual syntax to get data is :
	select <fieldnames> from <tablenames>
 <fieldnames> can be :
   - a comma-separated list of column names,
     where optional whitespace is allowed
       as long it is not in the middle of a name or keyword, or
   - '*' , which stands for 'all columns'.
 If same fieldname exists in multiple tables in <tablenames>,
   unambiguous syntax <tablename>.<fieldname> must be used.
 <tablenames> is optional when no columnnames are used.

 Additional optional clauses can be added on end of select statement,
   eg:	select <fieldnames> from <tablename> where <criterion>
 The result of this is a number of rows,
   namely those that satisfy the criterion,
   each containing specified fields.
 Syntax of select showing nearly all optional clauses and the order they must be in :
	select [distinct|distinctrow|all] <expression> [into <file>] from <tables>
	  where     <condition>	/* conditional to select rows			 */
	  group by  <formula>	/* combine groups of rows into single rows	 */
	  having    <condition>	/* unselect groups				 */
	  order by  <formula>	/* specify order of rows of result		 */
	  limit     <limit>	/* max number of rows to retrieve		 */
	  procedure <procedure>	/* run (custom) c++ function on rows/groups before send to client */
 Clauses must be in this order,
   and conceptually this is also order in which operations caused by these clauses are done.
   For example, 'group by' groups (parts of) rows that are result of select and where clauses,
     and 'having' filters result of 'group by', etcetera.

 DISTINCT :
 Keyword 'distinct' causes that if result of select <expression> contains identical resultrows,
   all but the first of these are discarded.
 I assume that keyword 'distinctrow' is similar, but only discards if whole rows are identical,
   not just parts of row that are used by select.
 I assume that keyword 'all' does not discard any of results, and is default.

 INTO FILE :
 Can only write into files on server's host. For other files use 'mysql -e' from shell.
 Works like inverse of 'load data'. For full syntax see info::mysql .

 WHERE :
 Condition in where clause (and in having clause) must be
   a mysql expression that evaluates to a boolean.
 Expression syntax will be treated in next section.
 Where clause causes only those rows to be selected for which expression is true.

 GROUP BY :
 Rows in resultset can be combined into groups by
   using 'group by <fieldnamelist>' at end of select statement.
 Then resulting table has a row for every unique value in column <fieldname>.
 'Group by' can be thought of as producing a series of tables (each of 1 row),
   each of which is produced by a 'select <unique value of fieldname> (etc.)',
   and those tables being brought together into one result-table.
 'Group by' can use several functions, which are completely different from functions
   that can be used in condition of 'where' clause.
   Names of these functions are: count(), count(distinct()), avg(), min(), max(), sum(),
     std(), stddev(), bit_or(), bit_and().

 HAVING :
 'Having' clause is like 'where' clause, but operates on output of 'group by' clause.
 Argument is a boolean expression that if false causes (grouped) rows to be deselected.

 ORDER BY :
 To have result rows of a select presented ordered alphanumerically, use 'order by' clause :
	 'ORDER BY <rowname> [DESC] [,<rowname> [DESC]]...'
 To sort in reverse alphanumeric order, use 'DESC', which stands for 'descending'.

 LIMIT :
 Takes an integer as argument, which becomes maximum number of rows to retrieve.
 Usefull for searches that can return very large numbers of rows.

 PROCEDURE :
 I didnt try this. Should run a custom function at 'send to client' time.


 EXAMPLES :

 To get whole table :
	select * from <tablename>

 To get a list of all values of field <fieldname> in table <tablename> :
 	select <fieldname> from <tablename>

 To get all rows from table 'pet' for pets that have name 'grizzly' :
	select * from pet where name="grizzly"

 To get a list of all different values of field <fieldname> in table <tablename> :
	select <fieldname> from <tablename> group by <fieldname>
   or	select distinct <fieldname> from <tablename>


 DEBUG :

 To get a row of information about each table in a select statement,
   including way tables are "joined" and keys that are used and available,
   (eg if a complicated select doesnt do what you expect or is slow) :
	explain select <rest of select statement>
 See info:mysql:problems:queryissues:nomatchingrows:explain
 "Joining" means several tables are used as a whole.

 -----------------------------------------------------------------------------------------
 'AS' and PRESENTING ADDITIONAL DATA :

 Columnnames of result output can be changed by 'as' clauses in <expression>, for example :
	select <expression> [as <name>] [, <expression> [as <name>]]... [from <tablename>]

 <expression> does not need to refer to any table.
 This way a select can be used to evaluate any expression, for example :
	select 1 + 2 as sum ;
 Result will be presented in a (1 row) column with columnheader 'sum'.
 If 'as' clause is left away, columnheader will be '1 + 2'.

 -------------------------------------------------------------------------------------------
 EXPRESSION SYNTAX :


 Expressions can be used in 'select', 'where', and 'having' clauses.
 Eg	select (1+2)*3

 Expressions yield a value from a combination of one or more values ;
   values to use can be values of fields, variables, literals, or keys.
 Literals are values that you write in expression,
   fieldnames and numbers are written plainly, but strings must be put in quotes.

 Values are combined by
   operators,
   conditional expressions 'if' and 'case',
   regular expression matchions of simple and slightly less simple type,
   full text searches, and
   some standard functions (most of which take a fieldvalue as an argument).


 DATATYPES

 Fundamental datatypes of the language are strings, numbers, and dates.
 There is also special value 'null', which usually means 'unknown' ;
   variables of all datatypes can have value null.

 Strings can be specified as single-quoted (in '') or double-quoted (in ""),
   unlike in most programming languages, these notations behave identical.
 Character \ is used as escape character in quoted string,
   it causes next character to be interpreted differently :
   \0 means character whose ascii code is zero (only works with digit 0)
   \' means a singlequote character (not a quotedstring terminator)
   \" means a doublequote character
   \b means backspace
   \n means newline
   \r means carriage return
   \t means a tab
   \z means ascii character 26 (^z) that is used as EndOfFile indicator by windows.
   \\ means a backslash (\) character
 \ can also be used to remove special meaning of special characters in regular expressions,
   as will be noted in section 'regular expressions'.
 Strings can also be specified as
   a sequence of bytes representing indexes of characters in current locale's characterset
   by specifying them as a hexadecimal number, least significant byte specifying last character,
   for example 0x5061756c is equivalent to "Paul" in iso8859-1 locale .

 Numbers are written as a series of digits,
   optionally starting with character '-' to indicate a negative value,
   optionally including '.' character as separator of integer and fractional part,
   optionally followed by character 'E' or 'e' followed by a whole number without an E or e,
     specifying power of 10 to multiply with.
   For example -31.4E-1
 Whole numbers can also be specified in hexidecimal notation, for example 0x123456789ABCDEF0 .


 CONTEXT SENSITIVITY

 How a value is interpreted can depend on context it is used in,
   contexts are places where a certain datatype is called for.
 For example in an addition a number is called for (this is called 'numeric context'),
   and when you supply a string instead, mysql tries to interpret it as a number :
   If that string starts with a sequence of digits, these are converted to a number,
   If no interpretation as a number could be found, it is interpreted as having value null.
 Likewise, in a string concatenation a string is called for ('string context'),
   and if a number is supplied instead, mysql tries to interpret it as a string
   by using the digitstring representing that number in standard decimal notation.

 Floating point numbers used in contexts where a whole number ('integer') is needed
   are truncated (ie fractional part is discarded toward 0, so eg -1.5 becomes -1).
 Integers used in floating point context are simply replaced by floats with the same value.

 Dates are normally written like "2004-12-31", and format of a time can be like "24:59:59" ;
   in string context they are used as shown,
   in numeric context, separators '-' and ':' are discarded and result is interpreted as a number.
 Numbers and strings used in date- and time- context are transformed the other way around.
 There is also type 'datetime' which is concatenation of a date, a space, and a time.
 Note that if they are to be used as strings, they MUST have quotes around them,
   2004-12-31 is not a valid date, but "2004-12-31" is, and 20041231 is valid too.
 Numbers and strings used in date context should have correct number of digits ;
   otherwise mysql will try to interpret it as one of it's less usual date representations.

 Boolean operators evaluate to 'true' or 'false' ;
   boolean 'true' evaluates to 1 in math context, and 'false' to 0 .
   0 and null are taken as false in boolean context, anything else is taken as true.
 I assume booleans are a special case of numbers.


 VARIABLES

 Mysql supports variables,but they are a bit superfluous, unless threading is used.
 Variables need not be predeclared,
 They can be set by 'SET @<var1>=<val1>[, @<var1>=<var2>]...'
   or, inside an expression, by the  '@<variablename>:=<value>' syntax.
 They can contain a number or a string.
 Variables are thread-specific.
 Variables are a bit superfluous
   because an (intermediate) result can be given a name (called an alias) with an 'as' clause,
   as will be shown furtheron.
 Variables in the select part of a select statement do not work as one would expect.


 OPERATORS

 List and precedence operators :
   Operator :	Effect :
   ,		several subexpressions can be combined by separating them with commas ;
		subexpressions are evaluated from left to right.
		result equals result of last expression.
   (a)		causes expression a to be evaluated first, eg: 3 * (4 + 5) yields 27 .

 Math operators (a and b are numbers or can be interpreted as such) :
   Operator :	Evaluates to same value as :
   a + b	add b to a
   a - b	subtract b from a
   - a		change sign of a (unary operator)
   a * b	multiply a by b
   a / b	divide a by b. division by zero produces null.
   a % b	a modulo b (remainder of a/b).
   a | b	bitwise or
   a & b	bitwise and
   a << b	shift bigint (longlong, 64 bits) a left  b bits.
   a >> b	shift bigint (longlong, 64 bits) a right b bits.
   ~ a		invert all bits of a.
 If either argument is float, math operation is done as float,
   if both are int, operation is done as bigint,
   except for division, which is only done as bigint in context where
     result is immediately converted to int.

 Comparison operators :
 Same comparison operators are used for numbers as for strings.
 These produce a value that is 0 (false), 1 (true), or null (undefined).
 If one of arguments is null, result is null (except for <=> operator).
   Comparing a fieldvalue against NULL is done with syntax '<value> is [not] null'.
 Result depends on whether arguments are strings or numbers,
   eg "123" is sorted before "13", but 123 is not smaller then 13.
 If both arguments are strings, then they are compared as strings,
   otherwise they are compared as (float) numbers.
 Stringcomparisons are not case-sensitive, unless they have typequalifier 'binary',
   which can be specified in a 'create table' and by case-sensitivity operator 'binary'.
 Comparisons of case-sensitive strings ignore trailing spaces (except in 'like' regexp comparisons).
 Operator :		Effect :
   >			larger than
   >=			larger or equal
   =			equal
   <=>			equal (can compare nulls)
   <=			smaller or equal
   <			smaller
   <>   		not equal
   !=			not equal
   <value> is null
   <value> is not null
   <expression> between <minimum> and <maximum>
   <expression> in <values>
   <expression> not in <values>

 Logical operators :
 Results of comparison operators can be operated on by logical operators.
 They can also take number or null arguments,
   0 is taken as false, other numbers as true, null as false.
 Strings that start with numbers are treated as that number,
   strings which do not are treated as null.
 They return 1 (true), 0 (false), or null (unknown).
 NOTE: they have same precedence as math operators !
 They have two ways to write them, i show these as comma separated :
 Operator :		Effect :
   ! , not		not
   ||, or		or
   &&, and		and

 Case-sensitivity operators :
 Operator :		Effect :
 binary	<string>	this unary operator casts <string> to binary type,
			  binary type strings are compared case-sensitively.
 To force case-insensitive comparison, convert both arguments to uppercase.


 CONDITIONAL EXPRESSIONS

 Besides evaluating to a result that depends on a condition,
   these can also be used as control flow statements,
   like if-then-else in programming languages.

 if( <expression1>,<expression2>,<expression3>)
	Expression1 must yield an integer value !
	If expression1 is true, then returns value of expression2,
	  else it returns value of expression3.

 ifnull( <expression1>,<expression2>)
	If expression1 is null, then returns value of expression2,
	  else it returns value of expression1.

 nullif( <expression1>,<expression2>)
	Return null if expressions have equal value, else return value of expression1

 case <value>
   when <value1> then <resultvalue1>
  [when <value2> then <resultvalue2>] ...
  [else <defaultresultvalue>]
   end

 case
   when <condition1> then <resultvalue1>
  [when <condition2> then <resultvalue2>] ...
  [else <defaultresultvalue>]
   end
	 Case expressions end evaluation as soon as
	   a comparevalue equals value, or condition is true
	 If no true condition or comparison is found, it returns defaultresultvalue,
	   unless there is no 'else' clause, then it returns null.
	 Type of returned value is same as type of 1st returnable value !

 ----------------------------------------------------------------------------------------------
 REGULAR EXPRESSIONS and RELEVANCE MATCHING :


 Boolean expressions can be regexp matchions.
 Mysql supports 2 separate regexp syntaxes,
   simple   regexps, like those of grep , and
   extended regexps, like those of egrep.

 SIMPLE regexps (like those of grep), aka 'SQL patterns' :
 Operators are 'like' and 'not like'.
 _ matches any single character,
 % matches any non-negative number of characters.
 Are these matches greedy ?
 Matching is case-insensitive (by default?).
 Arguments to matchion operators can be expressions.
 Whole value of argument must be matched.
 Example:	select * from <table> where <fieldname> like "%b%" ;
   returns all rows whose fieldname contains a 'b'.
 Full syntax:	<expression> [not] like <pattern> [escape <escape-character>]
   evaluates to 1 or 0, default escapechar is '\'.
 Note: to match a newline, which appears as \n in strings, pattern must contain \\n .

 EXTENDED regexps (like egrep) :
 Operators are 'rlike' and 'not rlike'
   or their synonyms 'regexp' and 'not regexp'.
 They take a left argument that is a stringvalue,
   and a right argument that is a pattern.
 Pattern is specified in double quotes.
 Pattern syntax is :
 . matches any single character,
 A 'character class' (set of characters) can be specified as a list of characters in []s ,
   a 'character class' matches any single character that is member of that set.
   a continuous range of characters can be specified <firstchar>-<lastchar> in a character class.
   To include a literal -, put it first or last.
   To include a literal ], put it first.
 * is a postfix modifier that specifies matching 0 or more times,
   it can be applied after a character, (escaped character), character class, or character-group.
 Characters can be grouped by placing them in round braces ;
   this has effect that postfix modifiers then apply to whole embraced group.
 To match a literal '(' or ')', put a \ before it ; this is called 'escaping a character' ;
 To match a literal \ , specify it as '\\' .
 {<int>} is also a postfix modifier;
   it matches exactly <int> occurrences of previous pattern-element.
 ^ matches empty string at start of value.
 $ matches empty string at end of value.
 Extended regexp matching is case-insensitive,
   it can be made case-sensitive by including keyword 'binary'
   (which is a prefix string qualifier keyword that make a string be treated as a binary string).
 Example:	select * from <table> where <fieldname> regexp binary "a[na]*" ;
   returns rows whose fieldnames are ananas, banana, pear, etc.
 An extended regexp matches if it matches part of the string.
 Full syntax:	<expression> [not] (rlike|regexp) <pattern>


 FULLTEXT SEARCHES :

 Mysql supports indexing of large pieces of text if they are in 'varchar' or 'text' columns.
 Creating the index is much faster after all rows have been added to table,
   so is best done by adding key 'fulltext' to a field in an 'alter table' statement.
 Full text searches are specified with the 'match' 'against' syntax,
   which returns a bool and can be used in a 'where' clause :
	select * from <table> where match( <fieldsfrom1table> ) against( <constantstring> )
 Result of this query is a set of rows that contain matches,
   sorted by 'relevance', which is computed from numberof matchin words,
   numberof unique matching words, number of columns that match, and
   numberof rows that contain a matching word.
 Relevance can be presented as additional data by specifying 2 identical matches,
   then mysql optimizer will cause fulltext search to be done only once.
 A 'word' is any sequence of letters, ', and _ .
 If relevance is too low (not necessarily zero) then row is not shown,
   so 'match' does not show words that occur in most rows !
 'Match against' is a specialized and advanced search type, like a key.

 --------------------------------------------------------------------------------------------------
 STANDARD FUNCTIONS :

 Functions must not have whitespace between functionname and '(' (opening round brace).

 Functions used in tutorial :

 year(      <date> )		evaluates to numeric value of year of date
 month(     <date> )		evaluates to numeric value of month of date, not to substring !
 dayofmonth(<date> )		evaluates to numeric value of day of month of date.
 date_add(  <date>, interval <int> month )
				evaluates to a later date
 right(<string>,<int>)		evaluates to last <int> characters of string ;
				  can be used to get date-of-year from a date by using <int> 5 .
 substring(<fieldname>,<int>,<int2>)
				evaluates to substring starting at position <int>,
				  and 
 mod( <int>, <int2> )		evaluates to <int> modulo <int2> .
 count( <fieldnames> )		evaluates to countof rows returned by current select (sub)statement,
				eg 'select name, count(*) from pet group by name ;'
				returns a table with a row for every unique value of field 'name',
				  returned table having a column 'count(*)'
				  containing countof rows that have that value of 'name'.
 max( <fieldname> )		evalutes to maximum value for that field in whole column.
 concat( <fieldnames> )		evaluates to concatenation of all these fields's values.

 Other functions found in info :

 coalesce( <values> )		returns first non-null value in list, or null.
 interval( <value>, <values> )	<values> must be in ascending order.
				returns index in <values> (counting from 0) for which
				  <value> is less value at that index.
 STRING FUNCTIONS :

 ascii(<string>)		returns bytevalue of first byte of string,
				  this often is index of first char of string in ascii code table
				if string is "" returns 0, if string is null returns null,
 ord(  <string>)		same as ascii(), except for multibyte characters it returns
				  value of all bytes of character as an integer,
				  with first byte most significant.
 conv(<num1>,<int2>,<int3>)
				if num1 is a digitstring, it is interpreted as being in base int2,
				  if it is a number, then it simply is that number.
				  returns representation of num1 in base int3 as a digitstring.
				  works with 64bit precision and can handle floats
				  bases (int2 and int3) can be in range [2-36].
 bin(<num>)			Returns digitstring of value of num in base  2 (binary notation).
 oct(<num>)			Returns digitstring of value of num in base  8 (octal  notation).
 hex(<num>)			Returns digitstring of value of num in base 16 (hex    notation).
 concat( <strings> )		Returns concatenation of all strings,
				  or null if any of strings is null.
				  Numeric arguments are converted to strings.
 concat_ws( <separator>, <strings> )
				"concat with separator".
				  separator specifies itemseparator in <strings>.
				  for rest it is identical to concat()

 length(           <string> )	these all return length of string <string>
 octet_length(     <string> )
 char_length(      <string> )	char*_length counts a multibyte characters as 1.
 character_length( <string> )

 following functions are all multibyte-safe :

 locate(  <substr> ,  <string>)	return 0 if <substring> is not a substring of <string>,
 position(<substr> in <string>)	else return position of 1st char of substring in string,
 instr(   <string> ,  <substr>)	  counting from 1 at first character.

 lpad( <string>,<len>,<pad> )	returns rightmost <len> characters of concatenation <pad><string>
 rpad( <string>,<len>,<pad> )	returns leftmost  <len> characters of concatenation <string><pad>
 left( <string>,<len> )		returns leftmost  <len> characters of <string>, or whole <string>
 right(<string>,<len> )		returns rightmost <len> characters of <string>, or whole <string>

 substring(<string> from <pos> for <len>)
 substring(<string>,<pos>,<len>)
 mid(      <string>,<pos>,<len>)
				these return that substring of <string> that
				  starts at position <pos> (counted from 1) and has length <len>.
 substring(<string>,<pos>)
 substring(<string> from <pos>)	return rightmost part of <string> that starts at position <pos>.

 substring_index(<string>,<delim>,<count>)
				if count is negative, returns largest rightmost part of string
				  that contains at most 1 - <count> occurrences of <delimiter>.
				if count is positive, returns largest leftmost part of string
				  that contains at most <count> - 1 occurrences of <delimiter>.

 ltrim(<string>)		remove any spaces from left  side of <string>
 rtrim(<string>)		remove any spaces from right side of <string>

 trim([[both|leading|trailing] [<cut>] from] <string>)
				can cut all occurrences of string <cut> from leading and trailing
				  ends of string <string>. default is cut spaces from both ends.
 soundex(<string>)		is supposed to return identical 4 character strings for
				  words that sound the same.
 space(<int>)			evaluates to string of <int> space characters. float args truncate.

 replace(<string>,<cut>,<put>)	returns string <string> with all occurrences of substring <cut>
				  replaced by string <put>. (except where substrings overlap).
 repeat(<string>,<count>)	returns null if any argument is null,
				  returns empty string if count is nonpositive,
				  returns concatenation of <count> strings <string>.
 reverse(<string>)		returns <string> backward.
 insert(<string>,<pos>,<len>,<insert>)
				cut substring at position <pos> with length <len>
				  and insert string <insert> on that position.
 elt(<int>,<strings>)		evaluates to <int>th string in <strings>
				  if no string can be selected, it returns null.
 field(<string>,<strings>)	if <string> is in list <strings>, returns it's index, else returns 0.
 find_in_set(<string>,<set>)	a set is a string that consists of substrings separated by commas.
				  eg "ab,cd,ef".
				returns index of <string> in set if it is there, else returns 0,
				  unless an argument is null, then it returns null.
				This function is intended for use with fields of type 'set'.
				First argument must not contain a "," !
 make_set(<mask>,<strings>)	evaluates to a set of all substrings of <set> for whom a bitflag
				  is 1 in integer <mask>. leftmost string has least significant bit.
 export_set(<mask>,<on>,<off>[,<separator>,[<bitcount>]])
				returns a string that consists of substrings <on> and <off>,
				  substrings are separated by <separator> (default ",").
				  for every 1 in <mask> an <on>, for every 0 an <off>,
				  no more than <bitcount> bits of <mask> are used (default 64).
				  i assume leastsignificant bits are used first.
 lcase( <string> )		return <string> with all uppercase characters replaced by their
 lower( <string> )		  lowercase versions, or vice versa.
 ucase( <string> )		depends on current characterset mapping (default iso-8859-1).
 upper( <string> )
 load_file( <fqfilename> )	returns filecontents as a string, on failure returns null.
				  file must be on server and readable by all,
				  and you must have *FILE* permission.
				  <fqfilename> is a string containing fully qualified filename.
 strcmp( <string1>,<string2> )	returns -1 if string1 sorts before string2,
				returns  0 if strings are equal,
				returns  1 if string1 sorts after string2.
				sort order depends on locale, default is iso8859-1 .
 match <fields> against <expression>
				For full-text search of fields.
				A *FULLTEXT* index must exist (see 'create table').
				Returns a float that represents
				  "similarity between texts of fields and expression".
 least(    <strings> )		returns first-in-sort-order string of strings
 greatest( <strings> )		returns last-in-sort-order  string of strings
				  see also under math functions.

 MATHEMATICAL FUNCTIONS :

 abs(  <number> )	returns absolute value of number <number>
 sign( <number> )	returns -1, 0, or 1, for negative, zero, or positive numbers.
 mod( <num1>,<num2> )	returns num1 modulo num2. identical to % operator.
 bit_count( <num> )	returns countof bits set in number <num>
 floor(   <num> )	largest  integer value not greater than <num>
 ceiling( <num> )	smallest integer value not less    than <num>
 least(    <numbers> )	returns smallest number from <numbers>.
 greatest( <numbers> )	returns largest  number from <numbers>.
 round(   <num> )	rounds to nearest integer.
			beviour for halves (eg 1.5) depends on C library in use.
			  [s: 1.5 -> 2 ; -1.5 -> -2 here, which is correct.]
			for portability use trunate() or floor() instead.
 round( <num>,<num2> )	round number <num> to a precision of <num2> digits after decimaldot.
 truncate( <n1>,<n2> )	returns number n1 truncated to a precision of
			  n2 digits after decimaldot.
			truncating always rounds toward zero.
			can fail for integer values specified as float
			  (eg 3.0 stored as 2.99999..)
 exp(   <num> )		returns e (2.78..) to the power <num>
 log(   <num> )		returns natural logarithm of <num>.
			if num is non-positive returns null.
 log10( <num> )		returns 10log of num.
 pow(   <num1>,<num2> )	returns num1 to the power num2
 power( <num1>,<num2> ) idem
 sqrt(  <num> )		square root
 pi()			pi (3.141593..) ;
			  mysql uses double precision value of pi, but only shows first 5 decimals.
 degrees(  <number> )	returns value in degrees of an angle specified as argument in radians.
 radians(  <number> )	returns value in radians of an angle specified as argument in degrees.
 sin(  <num> )		sine    of num, where num is taken to be in radians.
 cos(  <num> )		cosine  of num, where num is taken to be in radians.
 tan(  <num> )		tangens of num, where num is taken to be in radians.
 cot(  <num> )		cotangens of num (cos/sin), where num is taken to be in radians.
 acos( <num> )		returns arc-cosine  of num in radians, or null if num outside of [-1;1].
 asin( <num> )		returns arc-sine    of num in radians, or null if num outside of [-1;1].
 atan( <num> )		returns arc-tangens of num in radians, or null if num outside of [-1;1].
 atan( <num1>,<num2> )	same as atan( <num1>/<num2> ).
 atan2(<num1>,<num2> )	same as atan( <num1>/<num2> ).
 rand( [<int>] )	returns random float in range [0;1], if <int> specified, uses it as seed.
			for each seed, same random number is always produced.
			impossible to 'order by' a rand() field, it be evaluated multiple times.

 DATE AND TIME FUNCTIONS :

 to_days(    <date> )	returns daynumber (countof days since year 0) of date.
			is inaccurate for dates before 1582 (gregorian calendar was changed then).
 from_days(  <number> )	inverse of to_days().
 dayofweek(  <date> )	returns index of day in week of <date>. 1=sunday, 7=saturday. (ODBC)
 weekday(    <date> )	returns index of day in week of <date>. 0=monday, 6=sunday.
 dayofmonth( <data> )	returns day of month, range of result is 1 to 31 .
 dayofyear(  <date> )	returns day of year,  range of result is 1 to 366 .
 month(      <date> )	returns index of month in year. range of result is 1 to 12.
 dayname(    <date> )	returns capitalized full english name of weekday of <date>.
 monthname(  <date> )	returns capitalized full english name of mont    of <date>.
 quarter(    <date> )	returns index of quarter in year. range of result is 1 to 4 .
 week(<date>[,<first>]) returns weeknumber in year of date. range of result is 0 to 53.
			53 for first part of a week at end of year, 0 for remainder of a week 52/53.
			by default calculates with weeks that start with sunday.
			if locally week starts with another day, specify <first> : 0=sunday, etc.
 year((      <date> )	returns year of date. result is in range 1000 to 9999 .
 yearweek(<date>[,<first>])
			returns concatenation of year and weeknumber of start of week that
			  contains date.
 hour(   <time> )	returns numeric hour   of time. result is in range 0 to 23 .
 minute( <time> )	returns numeric minute of time. result is in range 0 to 59 .
 second( <time> )	returns numeric second of time. result is in range 0 to 59 .

 period_add( <yearmonth>,<countofmonths>)
			returns numeric yearmonth that is countofmonths later than <yearmonth>.
			yearmonth format can be YYMM or YYYYMM, returnformat is YYYYMM .
 period_diff( <yearmonth1>,<yearmonth2>)
			returns <yearmonth1> - <yearmonth2> , which is an integer countof months.

 date_add(<date>, interval <expression> <type> )
 date_sub(<date>, interval <expression> <type> )
 adddate( <date>, interval <expression> <type> )
 subdate( <date>, interval <expression> <type> )
			returns a datetime that is <expression><type> later or earlier than <date>.
			<date> can be a date or a datetime.
			<expression> is a string that can start with '-' to indicate negative.
			<type> keyword specifies interpretation of expression. valid values are:
			    second, minute, hour, day, month, year,
			    minute_second, hour_minute, day_hour, year_month,
			    hour_second, day_minute, day_second.
			Result is usually a datetime,
			  but it is a date if <date> is a date and time of result was not computed.
			Eg: select date_add( 19991231235900, interval 1 minute);
			->  2000-01-01 00:00:00
 extract( <type> from <date> )
			returns <type> part of date. type is like type in date_add().
 date_format( <date>,<format> )
			returns date formatted as specified by string <format>, like printf() in C.
			valid conversion specifiers are :
			%Y 4digit year			%X 4digit yearof1stday(sunday)ofweek
			%y 2digit year			%x 4digit yearof1stday(monday)ofweek
			%M monthname			%b 3letter monthname
			%c monthnumber			%m 3digit monthnumber
			%U week0-53 (sunday first)	%u week0-53 (monday first)
			%V week1-53 (sunday first)	%v week1-53 (monday first)
			%j 3digit dayofyear
			%d dayofmonth			%e 2digit dayofmonth
			%D dayofmonth as (1st,2nd etc)
			%w dayofweek (0=Sunday)
			%W weekdayname			%a 3letter weekdayname
			%k 24hour			%H 2digit 24hour			
			%r 12hour			%I 2digit 12hour
			%T 24time     			%r 12time(AM/PM)	%p AM or PM
			%S seconds			%s 2digit seconds
			%% literal %
 time_format(<time>,<format>)
			returns time formatted as specified by string <format>.
			  conversionspecifiers are noted at date_format().
 curdate()		these return today's date in format depending on context.
 current_date()		  in string context: YYYY-MM-DD. in numeric context: YYYYMMDD.
 curtime()		these return now time in format depending on context.
 current_time()		  in string context: HH:MM:SS. in numeric context: HHMMSS.
 now()			these return current datatime in format depending on context.
 sysdate()		  in string  context: YYYY-MM-DD HH:MM:SS .
 current_timestamp()	  in numeric context: YYYYMMDDHHMMSS .
 unix_timestamp([<datetimestamp>])
			returns unix timestamp (ie seconds since 1970jan1 GMT).
			  <datetimestamp> can be a date, datetime, timestamp, or
			  a number in format YYMMDD or YYYYMMDD in local time.
			  wrong or outofrange arguments make it return 0 .
 from_unixtime(<unixtimestamp>,[<format>])
			inverse of unix_timestamp();
			  by default format depends on context like now().
			  format can be specified as string <format> like date_format().
 sec_to_time(<seconds>)	returns (integer) argument converted to time.
			  returned format depends on context like curtime().
 time_to_sec(<time>)	returns time argument converted to seconds (integer).

 DATABASE FUNCTIONS :

 isnull( <expression> )		returns true when <expression> is null, false otherwise.
 database()			returns name of current database.
 user()				these three return current mysql user name,
 system_user()			  which is usually <local username>@<hostname>,
 session_user()			  where hostname is 'localhost' for local host.
 password(<password>)		one-way encrypts string <password> for use in mysql grants table.
 encrypt( <password>[,<salt>] )	uses libc's crypt() to encrypt <string> (one-way presumably)
				  this DES encryption is only good for passwords.
 encode(<string>,<password>)	encrypts string using password as key. (uses libc's encrypt()?)
 decode(<string>,<password>)	inverse of encode().
 md5(<string>)			returns 32 character hex string representing md5sum of <string>.
 last_insert_id([,<expression>]) returns last value autoinserted into an autoincrement field.
				next value to use for autoinsertion can be given as <expression>.
 format(<number>,<int>)		returns <number> in format with <int> digits after decimaldot.
 version()			returns mysql server version.
 connection_id()		returns id for this connection to server.
 get_lock(<string>,<int>)	tries to get a lock named <string>; times out after <int> seconds.
				max 1 lock per thread ! new get_lock() releases previous lock !
				returns 1:success, 0:failed, null:error.
 release_lock(<string>)		releases lock named <string>.
				returns 1:success, 0:wasntlocked, null:lockdoesntexist.
 benchmark(<int>,<expression>)	evaluates <expression> <int> times. for use with 'mysql' client,
				  (shell) which reports query execution (client wallclock) time.
 inet_ntoa(<number>)		returns 4byte <number> formatted as dotted quad (network address).
				result is in network-byte-order (most significant byte first).
 master_pos_wait(<logname>,<logpos>)
				blocks until slave reaches specified position in master log
				  during replication. for replication or master-slave synchronize.
	
 --------------------------------------------------------------------------------------------------
 USING MULTIPLE TABLES

 When data from multiple tables is used in a single statement,
   you need to disambiguate which table a field is in.
   This is done by using fieldnames of form '<tablename>.<fieldname>'.
 A from clause in a select statement can take a list of tablenames.

 A statement that uses multiple tables is said to 'join' tables.

 To select on basis of a combination of rows in a single table,
   it must be pretended to be two (or more) identical tables,
   eg	select <something> from <tablename> as t1, <tablename> as t2 where <condition>

 A 'references' clause can be added to a fieldspec in a 'create table' or 'alter table' statement ;
 It causes values in that column to be gotten from primary key of referenced table.
 Type of column must match type of column of primary key of referenced table,
   but this is not necessary for all attributes of type,
   i saw 'auto-increment' in original and not present in reference.
 Example	create table <tname> ( <name> <type> not null references <othertablename> )

 Temporary tables are usefull for storing intermediate results,
   and can be used to join results of multiple queries by adding them to the temporary table.

 Usual syntax for retrieving data from multiple tables is
	select <fields> from <table1>, <table2>, ...
   and this is called joining,
   but joining really makes tables dependant on each other,
   and forces order of evaluation of tables,
     dependent tables being evaluated after tables they depend upon.
 There is explicit join syntax described in info,
   with possibility to specify which keys to use and not use for combined table,
   but idea of joining is not described.

 --------------------------------------------------------------------------------------------------
 SCRIPT FILE

 When a repetitive task is done with mysql, it is not needed to write a program using one of APIs,
   it can be done by creating a scriptfile containing mysql statements to execute,
   and running it from shell by invoking mysql as
	mysql < <scriptfilename>
 Output from running mysql in batch mode like this has same data, but lacks ascii-art of tables.