Australia photo & PC memo

The column alias could be used in the GROUP BY, HAVING or ORDER BY clause, but not in the WHERE clause.

It's possible to perform a sort using an expression result:
   SELECT name, MONTH(birthday) m FROM t ORDER BY MONTH(birthday);
   SELECT name, MONTH(birthday) m FROM t ORDER BY m;

A query might run faster if the ORDER BY uses an indexed column.

ORDER BY can be used with DELETE or UPDATE to force rows to be deleted or updated in certain order.

Non-binary strings sort in the order defined by their collation. If the collation is case-insensitive, the sort result is also case-insensitive.
If the collation is binary, the numeric value determines the sort order.
The sort order for members of an ENUM or SET column is based on their internal numeric values, which means, for ENUM, it follows the defined order in the column definition, for SET, it follows the sum values of internal numeric values.

You can change these behaviours by using CAST():
   SELECT mon FROM t ORDER BY CAST(mon AS CHAR);

Limiting a Selection Using LIMIT:
   ... LIMIT row_count
   ... LIMIT skip_count, row_count

When ORDER BY and LIMIT are used together, MySQL applies ORDER BY first and then LIMIT.

DISTINCT result may be different by the column collations.
A difference between DISTINCT and GROUP BY is that DISTINCT doesn't cause row sorting (in MySQL, GROUP BY does cause sorting).
DISTINCT can be used with the COUNT() funtion to count how many distinct values a column contains. In this case, NULL values are ignored.
   SELECT COUNT(DISTINCT col1) FROM t;

Using metadata example:
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'db_name' AND TABLE_NAME = 'table_name';

SHOW TABLES FROM db_name LIKE '%aaa%'; (no WHERE)

SHOW CREATE TABLE table_name;
* Display the CREATE TABLE statement including its indexes.

DESCRIBE table_name;
SHOW COLUMNS FROM table_name;
SHOW FIELDS FROM table_name;

SHOW INDEX FROM table_name;
* If the index is PK only and the PK consists of 2 columns, the output is 2 rows.

A PRIMARY KEY cannot contain NULL values.
A UNIQUE index can be allowed to contain NULL values.
A UNIQUE index that can't contain NULL is functionally equivalent to a PRIMARY KEY.

A FULLTEXT index is specially designed for text searching.
A SPATIAL index applies only to columns that have spatial data types.

CREATE TABLE t
(
id INT NOT NULL PRIMARY KEY,
name CHAR(30) NOT NULL
);

CREATE TABLE t
(
id INT NOT NULL,
name CHAR(30) NOT NULL,
primary key (id, name)
);

For all index types other than PRIMARY KEY, you can name an index by including the name just before the column list.
Index names are displayed by the SHOW CREATE TABLE or SHOW INDEX statement.


ALTER TABLE t ADD PRIMARY KEY (id), ADD INDEX [index_name] (firstname, lastname);
CREATE UNIQUE INDEX index_name ON t (id);
CREATE INDEX index_name ON t (firstname, lastname);
Note that with CREATE INDEX, it's necessary to provide an index name.
Only ALTER TABLE supports the use of PRIMARY KEY.


MEMORY tables use HASH index by default. HASH index is usable only for comparisons that use the = or <=> operator. Also, changing non-unique indexes is relatively slow.
If a MEMORY table contains non-unique indexes and there will be many duplicate values and will be used with many type of comparison operators, BTEE indexes is better.

ALTER TABLE t ADD INDEX USING BTREE (id);


ALTER TABLE t DROP PRIMARY KEY;
ALTER TABLE t DROP index_name, DROP index_name2;
To drop index, you must specify its name.

DROP INDEX `PRIMARY` ON t;
To drop a PRIMARY KEY with DROP INDEX, use the index name PRIMARY with a quoted identifier because the work 'PRIMARY' is a reserve word.

Add, delete and modify a column:

ALTER TABLE my_table ADD new_date_column DATE NOT NULL [FIRST | AFTER existing_column];
ALTER TABLE my_table DROP dropping_column;
ALTER TABLE my_table MODIFY modifying_column DATETIME NOT NULL;
ALTER TABLE my_table CHANGE original_column new_name CHAR(40) NOT NULL;
ALTER TABLE my_table CHANGE original_column new_name CHAR(40) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY(new_name);

* You should specify the all column attributes when you use MODIFY. If you want to disallow NULL in the column, the column definition provided for MODIFY must include the NOT NULL attribute.


Renaming a table:

ALTER TABLE my_table RENAME TO my_renamed_table;
RENAME TABLE my_table1 TO tmp, my_table2 TO my_table1, tmp TO my_table2;

* For TEMPORARY tables, RENAME TABLE does not work.

A TEMPORARY table is visible only to the client that created it and may be used only by that client.
Different clients can create TEMPORARY tables that have the same name and no conflict occurs.
The server drops a TEMPORARY table automatically when the client connection ends if the client has not dropped it.
A TEMPORARY table may have the same name as a non-TEMPORARY table. The non-TEMPORARY table becomes hidden to the client that created the TEMPORARY table as long as the TEMPORARY table exists.
A TEMPORARY table can be renamed only with ALTER TABLE. You can't use RENAME TABLE.

* A MEMORY table is temporary in the sense that it's contents are lost if you restart the server, but the table definition continues to exists in its database.

(A) CREATE TABLE ... SELECT ... creates a table and populates it from the result set of the SELECT statement.
(B) CREATE TABLE ... LIKE ... creates an empty table using the definition of another existing table.

The difference is (A) statement copies the column name and data type from the original table, but does not retain the column attribute information. Also the new table sues the default storage engine.

Some table attributes are not copied, even when issuing (B).
If the original table is a MyISAM table for which the DATA DIRECTORY or INDEX DIRECTORY table options are specified, those options are not copied to the new table.
Foreign Key definitions in the original table are not copied to the new table.

To see which storage engines your server supports, use the SHOW ENGINES;.

CREATE TABLE [db name].[table name] (...) ENGINE = InnoDB;
ALTER TABLE [db name].[table name] ENGINE = MyISAM;

The build-in default value of the storage_engine system variable is MyISAM.
The default storage engine can be specified at server startup with the --default-storage-engine option.
Or
SET GLOBAL storage_engine = [engine name]; if you have the SUPER privilege.
SET SESSION storage_engine = [engine name];
SET storage_engine = [engine name];