MySQL includes some extensions that
you probably will not find in other SQL databases. Be warned that if you use
them, your code will not be portable to other SQL servers. In some cases, you
can write code that includes MySQL extensions, but is still portable, by using
comments of the form /*! ... */. In this case, MySQL will parse and execute the
code within the comment as it would any other MySQL statement, but other SQL
servers will ignore the extensions. For example:
SELECT /*! STRAIGHT_JOIN */ col_name
FROM table1,table2 WHERE ...
If you add a version number after the
'!', the syntax will only be executed if the MySQL version is equal to or newer
than the used version number:
CREATE /*!32302 TEMPORARY */ TABLE (a
int);
The above means that if you have
Version 3.23.02 or newer, then MySQL will use the TEMPORARY keyword.
MySQL extensions are listed below:
The field types MEDIUMINT, SET, ENUM,
and the different BLOB and TEXT types.
The field attributes AUTO_INCREMENT,
BINARY, NULL, UNSIGNED, and ZEROFILL.
All string comparisons are case
insensitive by default, with sort ordering determined by the current character
set (ISO-8859-1 Latin1 by default). If you don't like this, you should declare
your columns with the BINARY attribute or use the BINARY cast, which causes
comparisons to be done according to the ASCII order used on the MySQL server
host.
MySQL maps each database to a
directory under the MySQL data directory, and tables within a database to
filenames in the database directory. This has a few implications:
Database names and table names are
case sensitive in MySQL on operating systems that have case-sensitive filenames
(like most Unix systems).
Database, table, index, column, or
alias names may begin with a digit (but may not consist solely of digits).
You can use standard system commands
to backup, rename, move, delete, and copy tables. For example, to rename a
table, rename the `.MYD', `.MYI', and `.frm' files to which the table
corresponds.
In SQL statements, you can access
tables from different databases with the db_name.tbl_name syntax. Some SQL
servers provide the same functionality but call this User space. MySQL doesn't
support tablespaces as in: create table ralph.my_table...IN my_tablespace.
LIKE is allowed on numeric columns.
Use of INTO OUTFILE and STRAIGHT_JOIN
in a SELECT statement.
The SQL_SMALL_RESULT option in a
SELECT statement.
EXPLAIN SELECT to get a description on
how tables are joined.
Use of index names, indexes on a
prefix of a field, and use of INDEX or KEY in a CREATE TABLE statement.
Use of TEMPORARY or IF NOT EXISTS with
CREATE TABLE.
Use of COUNT(DISTINCT list) where
'list' is more than one element.
Use of CHANGE col_name, DROP col_name,
or DROP INDEX, IGNORE or RENAME in an ALTER TABLE statement.
Use of RENAME TABLE.
Use of multiple ADD, ALTER, DROP, or
CHANGE clauses in an ALTER TABLE statement.
Use of DROP TABLE with the keywords IF
EXISTS.
You can drop multiple tables with a
single DROP TABLE statement.
The LIMIT clause of the DELETE statement.
The DELAYED clause of the INSERT and
REPLACE statements.
The LOW_PRIORITY clause of the INSERT,
REPLACE, DELETE, and UPDATE statements.
Use of LOAD DATA INFILE. In many
cases, this syntax is compatible with Oracle's LOAD DATA INFILE.
The ANALYZE TABLE, CHECK TABLE,
OPTIMIZE TABLE, and REPAIR TABLE statements.
The SHOW statement.
Strings may be enclosed by either
`"' or `'', not just by `''.
Use of the escape `\' character.
The SET OPTION statement.
You don't need to name all selected
columns in the GROUP BY part. This gives better performance for some very
specific, but quite normal queries.
One can specify ASC and DESC with
GROUP BY.
To make it easier for users who come
from other SQL environments, MySQL supports aliases for many functions. For
example, all string functions support both ANSI SQL syntax and ODBC syntax.
MySQL understands the || and
&& operators to mean logical OR and AND, as in the C programming
language. In MySQL, || and OR are synonyms, as are && and AND. Because
of this nice syntax, MySQL doesn't support the ANSI SQL || operator for string
concatenation; use CONCAT() instead. Because CONCAT() takes any number of
arguments, it's easy to convert use of the || operator to MySQL.
CREATE DATABASE or DROP DATABASE.
The % operator is a synonym for MOD().
That is, N % M is equivalent to MOD(N,M). % is supported for C programmers and
for compatibility with PostgreSQL.
The =, <>, <= ,<,
>=,>, <<, >>, <=>, AND, OR, or LIKE operators may be
used in column comparisons to the left of the FROM in SELECT statements. For
example:
mysql> SELECT col1=1 AND col2=2
FROM tbl_name;
The LAST_INSERT_ID() function.
The REGEXP and NOT REGEXP extended
regular expression operators.
CONCAT() or CHAR() with one argument
or more than two arguments. (In MySQL, these functions can take any number of
arguments.)
The BIT_COUNT(), CASE, ELT(),
FROM_DAYS(), FORMAT(), IF(), PASSWORD(), ENCRYPT(), md5(), ENCODE(), DECODE(),
PERIOD_ADD(), PERIOD_DIFF(), TO_DAYS(), or WEEKDAY() functions.
Use of TRIM() to trim substrings. ANSI
SQL only supports removal of single characters.
The GROUP BY functions STD(),
BIT_OR(), and BIT_AND().
Use of REPLACE instead of DELETE +
INSERT.
The FLUSH flush_option statement.
The possiblity to set variables in a
statement with :=:
SELECT @a:=SUM(total),@b=COUNT(*),@a/@b
AS avg FROM test_table;
SELECT
@t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
No comments:
Post a Comment