Database
Reset Syntax in MySQL
RESET reset_option [, reset_option] …
The RESET statement is used to clear the state of various server operations. It also acts as a
stronger version of the FLUSH statement. See Section 6.5.4.2, “FLUSH Syntax.”
To execute RESET, you must have the RELOAD privilege.
reset_option can be any of the following:
MASTER
Deletes all binary logs listed in the index file, resets […]
LOAD INDEX INTO CACHE Syntax in MySQL
LOAD INDEX INTO CACHE
tbl_index_list [, tbl_index_list] …
tbl_index_list:
tbl_name
[[INDEX] (index_name[, index_name] …)]
[IGNORE LEAVES]
The LOAD INDEX INTO CACHE statement preloads a table index into the key cache to which it
has been assigned by an explicit CACHE INDEX statement, or into the default key cache otherwise.
LOAD INDEX INTO CACHE is used only for MyISAM tables.
The IGNORE LEAVES modifier causes […]
SHOW TABLES Syntax in MySQL
SHOW [OPEN] TABLES [FROM db_name] [LIKE ‘pattern’]
SHOW TABLES lists the non-TEMPORARY tables in a given database. You can also get this list
using the mysqlshow db_name command.
Note: If you have no privileges for a table, the table will not show up in the output from
SHOW TABLES or mysqlshow db_name.
SHOW OPEN TABLES lists the tables that are […]
Show Status in MySQL
SHOW STATUS [LIKE ‘pattern’]
SHOW STATUS provides server status information. This information also can be obtained using
the mysqladmin extended-status command.
Partial output is shown here. The list of variables and their values may be different for your
server. The meaning of each variable is given in the MySQL Administrator’s Guide.
mysql> SHOW STATUS;
+————————–+————+
| Variable_name | Value |
+————————–+————+
| Aborted_clients | […]
Show Errors in MySQL
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS
This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings,
and notes, it displays only errors. SHOW ERRORS is available as of MySQL 4.1.0.
The LIMIT clause has the same syntax as for the SELECT statement. See Section 6.1.7, “SELECT
Syntax.”
The SHOW COUNT(*) ERRORS statement displays the number […]
SHOW CREATE DATABASE in MySQL
SHOW CREATE DATABASE db_name
Shows a CREATE DATABASE statement that will create the given database. It was added in
MySQL 4.1.
mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
Database: test
Create Database: CREATE DATABASE `test`
/*!40100 DEFAULT CHARACTER SET latin1 */
6.5.3.6 SHOW CREATE TABLE Syntax
SHOW CREATE TABLE tbl_name
Shows a CREATE TABLE statement that will create the given table. It was […]
Show Column Syntax in MySQL
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE ‘pattern’]
SHOW COLUMNS lists the columns in a given table. If the column types differ from what you
expect them to be based on your CREATE TABLE statement, note that MySQL sometimes
changes column types when you create or alter a table.
The FULL keyword can be used from MySQL 3.23.32 […]
Show Collation Syntax in MySQL
SHOW COLLATION [LIKE ‘pattern’]
The output from SHOW COLLATION includes all available character sets. It takes an optional
LIKE clause that indicates which collation names to match.
For example:
mysql> SHOW COLLATION LIKE ‘latin1%’;
+——————-+———+—-+———+———-+———+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+——————-+———+—-+———+———-+———+
| latin1_german1_ci | latin1 | 5 | | | 0 |
| latin1_swedish_ci | latin1 […]
Create Database Syntax for MySQL
CREATE DATABASE [IF NOT EXISTS] db_name
[create_specification [, create_specification] …]
create_specification:
[DEFAULT] CHARACTER SET charset_name
| [DEFAULT] COLLATE collation_name
CREATE DATABASE creates a database with the given name. To use CREATE DATABASE, you need
the CREATE privilege on the database.
Rules for allowable database names are “Database, Table, Index,
Column, and Alias Names.” An error occurs if the database already exists and you […]
ALTER TABLE Syntax for MySQL
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] …
alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,…)
| ADD INDEX [index_name] [index_type] (index_col_name,…)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,…)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,…)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,…)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,…)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] […]