MENU
Information
Comments within an SQL statement are denoted from '#' to the end of line, from '-- ' (ending with a whitespace character) to the end of line, and from '/*' to '*/'. The last form of comment syntax can span multiple lines.| CREATE TABLE tbl( a INT, # this is a comment b INT, -- this is another comment c INT /* this is a multiline comment */ ); |
<!DOCTYPE html><html><head></head><body><pre>
<?php
$S=new mysqli("localhost","root","password","testDB");
tabulate($S->query("HELP 'contents'")); // the contents page
tabulate($S->query("HELP 'Data Types'")); // a topic
tabulate($S->query("HELP 'REPLACE'")); // a command
// ****** A useful, general-purpose result printer
function tabulate($result){
$fInfo = $result->fetch_fields();
echo "<table border='1'><tr>";
foreach($fInfo as $col){
echo "<td>".$col->name."</td>";
}
while ($row = $result->fetch_row()){
echo "</tr><tr>";
foreach ($row as $val){
echo "<td>".$val."</td>";
}
}
echo "</tr></table><br/>";
}
?>
</pre></body></html>| source_category_name | name | is_it_category |
| Contents | Account Manager | Y |
| Contents | Administration | Y |
| Contents | Compound Satements | Y |
| Contents | Data Definition | Y |
| Contents | Data Manipulation | Y |
| Contents | Data Types | Y |
| Contents | Functions | Y |
| Contents | Functions and Modifiers for use with GROUP BY | Y |
| Contents | Geographic Features | Y |
| Contents | Help Meatadat | Y |
| Contents | Language Structure | Y |
| Contents | Plugins | Y |
| Contents | Procedures | Y |
| Contents | Storage Engines | Y |
| Contents | Table Maintenance | Y |
| Contents | Transactions | Y |
| Contents | User-Defined Functions | Y |
| Contents | Utility | Y |
| Nsource_category_name | name | is_it_category |
| Data Types | AUTO_INCREAMENT | N |
| Data Types | BIGINT | N |
| Data Types | BINARY | N |
| Data Types | BIT | N |
| Data Types | BLOB | N |
| Data Types | BLOB DATA TYPE | N |
| Data Types | BOOLEAN | N |
| Data Types | CHAR | N |
| Data Types | CHAR BYTE | N |
| Data Types | DATE | N |
| Data Types | DATETIME | N |
| Data Types | DEC | N |
| Data Types | DECIMAL | N |
| Data Types | DOUBLE | N |
| Data Types | DOUBLE PRECISION | N |
| Data Types | ENUM | N |
| Data Types | FLOAT | N |
| Data Types | INT | N |
| Data Types | INTEGER | N |
| Data Types | LONGBLOB | N |
| Data Types | LONGTEXT | N |
| Data Types | MEDIUMBLOB | N |
| Data Types | MEDIUMINT | N |
| Data Types | MEDIUMTEXT | N |
| Data Types | SET DATA TYPE | N |
| Data Types | SMALLINT | N |
| Data Types | TEXT | N |
| Data Types | TIME | N |
| Data Types | TIMESTAMP | N |
| Data Types | TINYBLOB | N |
| Data Types | TINYINT | N |
| Data Types | TINYTEXT | N |
| Data Types | VARBINARY | N |
| Data Types | VARCHAR | N |
| Data Types | YEAR DATA TYPE | N |
| name | description | example |
| REPLACE | Syntax: REPLACE [LOW_PRIORITY | DELAYED] [INTO] table_name [PARTITION (partition_name,.......)] or: REPLACE [LOW_PRIORITY | DELAYED] INTO] table_name [PARTITION (partition_name,.......)] SET col_name={expr | DEFAULT},................ or: REPLACE [LOW_PRIORITY | DELAYED] [INTO] table_name [PARTITION (partition_name,.......)] [(col_name,.......)] SELECT ............... REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is delated before the new row is inserted. see [HELP INSERT0. REPLACE is MySQL extenstion to the SQL standard. It either inserts, or deletes and inserts. For another MYSQL extension to standard SQL --- that either inserts or updates---see http://dev.mysql.com/doc/refman/5.6/ en/insert-on-duplicate.html. Note that unless the table has a PRIMARY KEY or UNIQUE index, using a REPLACE statement makes no sense. It becomes equivalent to INSERT, because there is no index to be used to determine whether a new row duplicate another. Values for all column are taken from the values specified in the REPLACE statement. Any missing columns are set to their default values, just as happens for INSERT. You cannot refer to value form the current row and use them in the new. If you use an assignment such as SET col_name=col_name+1, the reference to the column name on the right hand side is treated as DEFAUT(col_name), so the assignment is equivalent set col_name=DEFAULT(col_name) + 1. To use REPLACE, you must have both the INSERT and DELETE privileges for the table. Beginning with MySQL 5.6.2, REPLACE supports explicit partions selection using the PARTITION option with a comma-seperated list of names of partitions, the REPLACE statement fails with the error Found a row not matching the given partition set. see http://dev.mysql.com/doc/refman/5.6/partitioning-selection.html, for more information. URL: http://dev.mysql.com/doc/refman/5.6/en/replace.html |
| EXPLAIN, DESCRIBE, or DESC can be used to obtain information about a specific table or statement. |
| {EXPLAIN | DESCRIBE | DESC} tbl_name [col_name | wild] {EXPLAIN | DESCRIBE | DESC} [explain_type] {explainable_stmt | FOR CONNECTION connection_id} explain_type: { EXTENDED | PARTITIONS | FORMAT = format_name } format_name: { TRADITIONAL | JSON } explainable_stmt: { SELECT statement | DELETE statement | INSERT statement | REPLACE statement | UPDATE statement } |
<!DOCTYPE html><html><head></head><body><pre>
<?php
$S=new mysqli("localhost","root","password","testDB");
$S->query("DROP TABLE IF EXISTS tbl;");
$S->query("
CREATE TABLE tbl(
a INT PRIMARY KEY,
b INT DEFAULT 5,
c INT AUTO_INCREMENT UNIQUE
)");
tabulate($S->query("EXPLAIN tbl"));
tabulate($S->query("EXPLAIN SELECT * FROM tbl"));
// ****** A useful, general-purpose result printer
function tabulate($result){
$fInfo = $result->fetch_fields();
echo "<table border='1'><tr>";
foreach($fInfo as $col){
echo "<td>".$col->name."</td>";
}
while ($row = $result->fetch_row()){
echo "</tr><tr>";
foreach ($row as $val){
echo "<td>".$val."</td>";
}
}
echo "</tr></table><br/>";
}
?>
</pre></body></html>| Field | Type | Null | Key | Default | Extra |
| a | int(11) | NO | PRI | ||
| b | int(11) | YES | 5 | ||
| c | int(11) | NO | UNI | auto_increment |
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
| 1 | SAMPLE | tbl | ALL |