The main goals of MySQL are speed and robustness. Adding transactions would incur a significant speed and complexity penalty. There is however currently work underway to give similar functionality in a different way. This will probably be done by allowing an atomic multi-table update.
The base upon which MySQL is built is a set of routines that have been used in a highly demanding production environment for many years. While MySQL is currently still in development it already offers a rich and highly useful function set.
SYNOPSIS:
alter_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_nameDESCRIPTION:
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.
SYNOPSIS:
Where create_definition takes the following form:
create_definition: column_name type NOT NULL [DEFAULT default_value] [ PRIMARY KEY ] or column_name type [NULL] [ PRIMARY KEY ] or PRIMARY (KEY|INDEX) [key_name] ( column_name,... ) or (KEY|INDEX) [key_name] ( column_name[length],...) or INDEX [key_name] ( column_name[length],...) or UNIQUE ( column_name[length],...) or FOREIGN (KEY|INDEX) [key_name] ( column_name[length],...) REFERENCES table_name [ ON DELETE (RESTRICT | CASCADE | SET NULL) ]DESCRIPTION:
The FOREIGN syntax is only for compatibility. The REFERENCE keyword is also non functional at this time.
The MySQL CREATE TABLE command does not support the SQL CHECK keyword.
You must have create privileges to create a table.
Things to know:
If you wish to start at a value other than zero simply insert the desired starting value with the first record you insert into a given table. There is currently no other way to do this.
NOTE: If you use AUTO_INCREMENT you may use it on only one field in a table. Note also that this field must be declared as the primary key, and must be numeric.
EXAMPLE:
INT(5) ZEROFILL ; value 5 is retrieved as "00005"
Fields must be of one of the following data types:
BIGINT [(length)] [UNSIGNED] [ZEROFILL] | 8 byte integer (if compiler supports longlong) |
BLOB | Binary object with a maximum length of 65535 |
CHAR(NUM) | Fixed width string (1 <= NUM <= 255). See also VARCHAR |
DATE | Store date information. Uses the "YYYY-MM-DD" syntax.
May be updated with either a string or a number, though
you should probably use a string context as times and
dates with leading zeroes will not be dealt with correctly
currently.
The MySQL DATE type understands at least the following syntaxes.
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.
The DATETIME type is eight bytes long. |
DECIMAL (length,dec) | An unpacked floating point number. |
DOUBLE [(length,dec)] | double (4 or 8 bytes) A double precision number with a max length and a fixed number of decimals. Length and decimals are for formating and the calculation of max column width. |
FLOAT [(precision)] | A floating point number. FLOAT(4) and FLOAT are single precision. FLOAT(8) is double precision. |
FLOAT [(length,decimals)] | A single precision number with a max length and a fixed number of decimals. (4 bytes) |
INT [(length)] [UNSIGNED] [ZEROFILL] | 4 byte integer |
INTEGER [(length)] [UNSIGNED] [ZEROFILL] | 4 byte integer |
LONGBLOB | Binary object with a maximum length of 2**32 |
MEDIUMBLOB | Binary object with a maximum length of 16777216 |
MEDIUMINT [(length)] [UNSIGNED] [ZEROFILL] | 3 byte integer |
REAL [(length,dec)] | Identical to DOUBLE (8 bytes) |
SMALLINT [(length)] [UNSIGNED] [ZEROFILL] | 2 byte integer |
TINYBLOB | Binary object with a maximum length of 255 |
TINYINT [(length)] [UNSIGNED] [ZEROFILL] | 1 byte integer |
VARCHAR(NUM) | Variable length string (1 <= NUM <= 255) |
TIME | Store time information. Uses the "HH:MM:SS" syntax.
May be updated with either a string or number. The MySQL
TIME type understands at least the following syntaxes.
|
TIMESTAMP(NUM) | Changes automatically on insert/update (YYMMDDHHMMSS)
or (YYYYMMDDHHMMSS) The length determines how the output is
formatted. You may optionally update a TIMESTAMP field when
doing an INSERT. This is only useful when you want to set an
arbitrary date/time for the record. During updates you should
either not specify a value for your TIMESTAMP field, or specify
NULL as the value to insert. Otherwise you'll likely end up with
an invalid value for this field. When using mysql with ODBC and Access you should use a value of 14 for NUM, as this causes MySQL to always use four digit years. A value of 12 would cause MySQL to use a two digit year. The default is 14. Note that in the case of tables with multiple TIMESTAMP fields only the fist such field will be updated automatically. |
The length field specifies how many total digits the number can have, while the dec field specifies how many of these digits will be after the decimal place. These values are only used for formating and the calculation of maximum column width.
A MySQL table may have up to sixteen keys, each of which may consist of up to fifteen fields. The maximum supported key length in the binary distribution is 120. You can increase the key length by changing N_MAX_KEY_LENGTH in the file nisam.h and recompiling. Note that longer key lengths can lead to lower performance.
Keys may optionally be given names. In the case of the primary key, the name will always be PRIMARY. If no key name is given during table creation, the default key name is the first column name with an optional suffix (_2, _3, etc.) to make it unique. The key name can be used with the ALTER TABLE command to drop the key.
When creating a key you may optionally specify that only the first N places of the field will be used. For instance, if you want to create a unique key on a field in which you only care if the first 40 characters are unique, you could do something like the following.
CREATE TABLE SomeTable (composite CHAR(200), INDEX comp_idx (composite(40))) ;
It's also a good idea to use this option on non unique fields, as it will greatly decrease the size of your index, and generally lead to very little degradation in performance.
Note that his options is only available on CHAR and VARCHAR fields.
You may have one primary key per table. If a field is declared to be the PRIMARY KEY field an index is generated. There is no need to define a normal key as well. Furthermore, specifying additional indexes that contain the PRIMARY key will do you no good as the PRIMARY key will cause the index to be useless.
In general multi-field keys should be used to optimize specific queries. IE, all fields in the WHERE clause of a query should appear in the multi-field key.
Because of the way MySQL uses B-Tree's internally you do not need to declare keys that are a prefix of another key. The optimizer will find any usable prefix of a key and use it to perform the search. For example, if you declare the following key:
INDEX (first, second, third, fourth)
You also have also implicitly created the following keys:
(first, second, third)
(first, second)
(first)
Declaring unnecessary keys will only take up extra space and slow down your queries.
Keys must either be created at the time the table is defined, or by use of the ALTER TABLE. command.
A BLOB is a "Binary Large OBject".
As noted above, MySQL supports four BLOB types.
tinyblob (0-255 chars) blob (0-65535 chars) mediumblob (0-16777216 chars) longblob (0-2147483648 chars)Note that there may be some constraints because of the message buffer size. The message buffer is dynamically allocated. You do have to be aware of what 'max_allowed_packet' has been set to in the server and client. By default this is 64K for the server and 512K for the client. You are also constrained by available memory.
You can change the buffer length when starting mysqld by use of the -O option. But remember that this space will be alloced by each thread.
EXAMPLE:
mysqld -O max_allowed_packet=max_blob_length
The MySQL WIN95 ODBC driver defines BLOB:s as LONGVARCHAR.
If you wish to insert binary data into a blob you must escape the following characters:
\0
\\
'
or "
SYNOPSIS:
SYNOPSIS:
Where where_definition takes the following form:
where_definition: where_expr or where_expr [ AND | OR ] where_exprAnd 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:
You must have delete privileges to delete records.
SYNOPSIS:
SYNOPSIS:
If you just want to delete everything in a table and keep the definition you can use the DELETE command.
BEWARE! DROP TABLE will completely remove the named table(s) from your system. There is no going back. (Unless you have backups of course.)
You must have delete privileges to use DROP.
SYNOPSIS:
DESCRIPTION:
DROP INDEX is provided for compatibility reasons. It fools many clients into thinking they have actually gotten what they asked for. Primarily this comes up in conjunction with the ODBC driver.
SYNOPSIS:
GRANT (ALL PRIVILEGES | (SELECT, INSERT, UPDATE, DELETE, REFERENCES (column list), USAGE)) ON table TO user,... [WITH GRANT OPTION]DESCRIPTION:
SYNOPSIS:
SELECT [STRAIGHT_JOIN] [DISTINCT | ALL] select_expression,... [ FROM tables... [WHERE where_definition ] [GROUP BY column,...] [ ORDER BY column [ASC | DESC] ,..] HAVING full_where_definition [LIMIT [offset,] rows] [PROCEDURE procedure_name]] [INTO OUTFILE 'file_name' ...]
Where where_definition is:
where_definition: where_expr or where_expr [ AND | OR ] where_exprAnd 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:
http://w3.one.net/~jhoffman/sqltut.htm#Basics of the SELECT Statement
In MySQL versions prior to 3.21.x the WHERE clause is very limited. The HAVING clause will generally work where the WHERE clause does not. Some examples of things that do not work in the WHERE clause are REGEXP and the ! operator. Basically you cannot use functions with WHERE, but you can with HAVING.
HAVING is essentially a WHERE that is applied to the results. It is used mainly to narrow the scope of data returned by the query.
You must have select privileges to use SELECT.
The select_expression can contain the following logic functions/operators.
+ - * / | Basic math stuff. |
% | Modulo (like in C) |
| & | Bit functions. (48 bits in use) |
- | Sign. |
( ) | Parenthesis. |
BETWEEN(A,B,C) | Is the same as (A >= B AND A <= C). |
BIT_COUNT() | The number of bits. |
ELT(N,a,b,c,d) | Return a if N == 1, b if N == 2, etc. a,b,c,d are strings. EXAMPLE:
ELT(3,"First","Second","Third","Fourth")
|
FIELD(Z,a,b,c) | Return a if Z == a, b if Z == b, etc. a,b,c,d are strings. EXAMPLE:
FIELD("Second","First","Second","Third","Fourth")
|
IF(A,B,C) | If A is true (!= 0 and != NULL) then return B, else return C. |
IFNULL(A,B) | If A is not null return A, else return B. |
ISNULL(A) | Returns 1 if A is NULL else 0. Same as '( A == NULL '). |
NOT ! | NOT, returns TRUE (1) or FALSE (0). |
OR, AND | Returns TRUE (1) or FALSE (0). |
SIGN() | Returns -1, 0 or 1 (sign of argument). |
SUM() | Return SUM of column. |
= <> <= < >= > | Returns TRUE (1) or FALSE (0). |
expr LIKE expr | Returns TRUE (1) or FALSE (0). |
expr NOT LIKE expr | Returns TRUE (1) or FALSE (0). |
expr REGEXP expr | Check string against extended regular expr. |
expr NOT REGEXP expr | Check string against extended regular expr. |
The select_expression can also contain one or more of the following math functions.
ABS() | Absolute value. |
CEILING() | () |
EXP() | () |
FLOOR() | () |
FORMAT(nr,NUM) | Format number to format '#,###,###.##' with NUM decimals. |
LOG() | Return the log of a number. |
LOG10() | () |
MIN(),MAX() | Min or max value of argument. Variable arg count. Must have two or more arguments, else this is a group function. |
MOD() | Modulo (same as %). |
POW() | () |
ROUND() | Round to the nearest whole number. |
RAND([integer_expr]) | Returns a random float, 0 <= x <= 1.0, using integer_expr as the seed value. |
SQRT() | Square root of argument |
The select_expression can also contain one or more of the following string functions.
CONCAT() | Concatenate strings. Variable arg count. |
INTERVAL(A,a,b,c,d) | Return 1 if A == a, 2 if A == b... If no match return 0. A,a,b,c,d... are strings. |
INSERT(org,strt,len,new) | Replace substring org[strt...len(gth)] with new. First position in string=1. |
LCASE(A) | Change A to lower case. |
LEFT() | Get a string counting from the left. |
LENGTH() | Get the length of string. |
LOCATE(A,B) | Return position of B substring in A. |
LOCATE(A,B,C) | Return position of B substring in A starting at C. |
LTRIM(str) | Remove any leading spaces from str. |
REPLACE(A,B,C) | Replace all occurrences of B in A with C. |
RIGHT() | Get string counting from right. |
RTRIM(str) | Remove any trailing spaces from str. |
STRCMP() | Returns 0 if the strings are the same. |
SUBSTRING(A,B,C) | Get substring from A starting at B with C chars. |
UCASE(A) | Change A to upper case. |
The select_expression can also contain one or more of the following miscellaneous functions.
CURDATE() | Return the current date. |
DATABASE() | Return the name of the currently selected database. |
FROM_DAYS() | Change a day number to a DATE. |
NOW() | Return the current time. In format YYYYMMDDHHMMSS or "YYYY-MM-DD HH:MM:SS" depending on whether NOW() is used in a number or string context. |
PASSWORD() | Calculate a password string. |
PERIOD_ADD(P:N) | Add N months to period P (of type YYMM). |
PERIOD_DIFF(A,B) | Returns months between A,B. Note that PERIOD_DIFF only works with dates in the form of YYMM or YYYMM. |
TO_DAYS() | Change a DATE (YYMMDD) to a day number. |
UNIX_TIMESTAMP([date]) | Returns a unix timestamp if called without a date. (Seconds
since GMT 1970.01.01 00:00:00.) When called with a
TIMESTAMP column the UNIX_TIMESTAMP function returns the TIMESTAMP. date may also be a DATE string, a DATETIME string or a number in the form YYMMDD or YYYMMDD. |
USER() | Return the current user. |
WEEKDAY() | Get weekday for date. (0 = Monday, 1 = Tuesday...) |
The following functions are supported in the GROUP clause:
Select group functions:
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!).
For example:
"a" < "b" ; String compare "a" < 0 ; String compare 0 < "a" ; numerical compare a < 5 ; If field is a CHAR type then the compare is by strings, else by numeric.
NOTE: You can't currently write:
SELECT user,MAX(salary) FROM users GROUP BY users HAVING max(salary)>10
Instead, use something like the following: (This is also a good example of using a column alias.)
SELECT user,MAX(salary) AS sum FROM users GROUP BY users
HAVING sum > 10
This is also useful when you have used a function in your SELECT.
EXAMPLE:
SELECT Widget_Table.widget_id, Widget_Table.widget_name, Purchase_Order_Item.widget_id, sum(Purchase_Order_Item.quantity) FROM Widget_Table, Purchase_Order_Item WHERE Widget_Table.widget_id = Purchase_Order_Item.widget_id GROUP BY Widget_Table.widget_name ORDER BY 4;
The SQL join feature gives one the ability to define relationships between tables and retrieve information based on these relationships.
Relationships are listed in the FROM clause of a SELECT query. Each relationship is separated by a comma.
EXAMPLE:
$ mysql mysql Welcome to the mysql monitor. Commands ends with ; or \g. Type 'help' for help. mysql> SELECT db.user, db.delete_priv, user.user, user.delete_priv -> FROM db,user WHERE db.user = user.user; |
+------+-------------+------+-------------+ | user | delete_priv | user | delete_priv | +------+-------------+------+-------------+ |mke | N | mke | N | +------+-------------+------+-------------+The first two fields are actually db.user and db.delete_priv, while the last two are user.user and user.delete_priv.
Note that we use the table names in our query to specify exactly which fields we are referring to.
You may link up to fifteen tables by way of a single join.
MySQL won't use keys effectively to join tables by way of fields that are not of identical type. This means you should always use the same types for fields that are intended to be used in joins.
Aliases can also be used to make the identity of column names clearer. See the next section for details.
The MySQL database engine also supports the concept of aliases both on tables and fields.
Table aliases are a standard part of the SQL language. Let's look at an example.
EXAMPLE:
SELECT A.user,A.select_priv,A.insert_priv,A.update_priv FROM user A
The above is an example of using a table alias to shorten your query, By declaring an alias that is shorter than the table name. You use the alias in the first part of the select, and define it in the FROM by specifying the real table name, a space and the alias. If you have more than one table you wish to alias, simply add a comma after each table name/alias pair.
If you are using aliases with a query that will have a WHERE clause you must use the alias in the WHERE clause rather than the real table name.
Field aliases are a MySQL specific extension. Here's an example.
EXAMPLE:
SELECT user.user AS "User Name", user.delete_priv AS "Delete" FROM user;
One nice thing about field aliases is that they allow you to specify
a more user friendly label for your output. The result of the
above query might end up looking something like this:
+-----------+--------+ | User Name | Delete | +-----------+--------+ | root | Y | | mke | N | | dummy | N | | admin | N | +-----------+--------+It's a good idea to quote your aliases, as in the above example "Delete" would have caused a parse error without quotes. (This is because DELETE is a SQL keyword.)
SYNOPSIS:
@result{Records: 220 Duplicates: 1 Warnings: 1}
INSERT INTO Customer (customer_name,customer_contact) VALUES ("Joes Wholesale","Joe Smith")
You can also use SELECT to copy entries from one table to another. MySQL supports a limited form of sub queries to get this done. See the syntax in the SYNOPSIS section above for more information.
SYNOPSIS:
LOAD DATA INFILE 'customer.tab' [REPLACE | IGNORE] INTO TABLE Customer
[fields [terminated by ',' [optionally] enclosed by '"' escaped by '\\' ]]
[lines terminated by '\n'] [(field list)]
"fields terminated by" and "lines terminated by" may be more than 1 character.
If "fields terminated by" and "fields enclosed by" are both empty strings (") you end up
with a fixed row size. IE, your are reading a fixed field size non delimited file.
With a fixed row size NULL values are output-ed as a empty strings.
If you specify "optionally" in "enclosed by" and you don't use the the fixed
row size, only strings will be enclosed with the given character by the
SELECT ... INTO statement.
If "escaped by" is not empty then the following characters will be prefixed
with the escape character: "escaped by", ASCII 0, and the first character in
any of "fields terminated by", "fields enclosed by" and "lines terminated
by".
If REPLACE is used the new row will replace all rows which have a same unique
key. If IGNORE is used rows will be skipped if there already exists a record
with an identical unique key. If none of the above options is used an error
will be issued and the rest of the textfile will be ignored if a duplicate
key is found.
Some scenarios that are not supported by LOAD DATA INFILE:
All rows are read into the table. If a row has too few fields the rest of the
fields are set to default values.
For security reasons the textfile must either reside in the database directory
or be readable by all.
If "FIELDS ENCLOSED BY" is not empty then NULL is read as a NULL value. If
"FIELDS ESCAPED" is not empty then \N is also read as a NULL value. Note
that this is capitol N, not lower case.
When the LOAD DATA query is done you can get the following info string by
using the C API function mysql_info().
@result{Records: 1 Deleted: 0 Skiped: 0 Warnings: 0}
The Warnings value is incremented for each column that can't be stored without
loss of precision, for each column that didn't get a value from the read text
line (This happens if a line is too short) and for each line which has more
data than can fit into the given columns.
You must have select and insert privileges in the user table to
use this command.
SYNOPSIS:
SYNOPSIS:
UPDATE Widget_Table SET widgets_on_hand=widgets_on_hand - 300 where widget_id=3;
DELETE FROM Purchase_Order_Item WHERE purchase_order = 456
You would also want to delete the entry in Purchase_Order for purchase_order
number four hundred and fifty six. It's important to be sure that when
you do delete information, you get rid of all references to that information.
You're going to end up with a corrupted database if you don't.
You must have update privileges to use this command.
SYNOPSIS:
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";
Hyphens, spaces, hashes and other special characters can not be used
as they would make the table or column impossible to use in a
SELECT statement.
"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'.
SET OPTION
SET OPTION SQL_VALUE_OPTON=value, ...
DESCRIPTION:Change or set the value of a MySQL option. Options stay in effect
for the current session only.
MySQL supports the following options(s):
SQL_SELECT_LIMIT=value
The maximum number of records to return from any SELECT. If a SELECT
has a LIMIT clause it overrides this statement.
UPDATE
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:
SHOW
SHOW DATABASES [LIKE wild]
DESCRIPTION:
SHOW KEYS FROM table_name
SHOW TABLES [FROM database] [LIKE wild]
SHOW [COLUMNS|FIELDS] FROM table [FROM database] [LIKE wild]
Display information about a MySQL database. "wild" is a SQL LIKE
style regular expression.
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:
\0
ASCII zero. Note this is 5C 30, not 5C 00!
\n
newline
\t
tab
\r
return
\b
backspace
\'
'
\"
"
\\
\
\%
% (This is used in wild-card strings to search for '%')
\_
_ (This is used in wild-card strings to search for '_')
A ' inside a string may be written as ''
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
About Table And Column Names