Commands are entered into a multi-line buffer, terminated with the delimiter character ( default ;
) or
\g
aka go
and  and then sent to the server.
Commands are saved in history, which is retained across invocations of MySQL, and can be recalled see readline.
AUTOCOMMIT
is enabled by default.
MyISAM storage engine (default) does not support transactions!
rollback
will report: Warning (Code 1196): Some non-transactional changed tables couldn't be rolled back
SHOW ENGINES; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | MyISAM | DEFAULT | MyISAM storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+For transaction-safe tables
SET AUTOCOMMIT=0;
commit(MySQL) will permit ROLLBACK
.
source \ | . file |
The last 50 previous lines of the buffer can be recalled into current buffer (even across invocations of MySQL).
[^]
or [F5]
scroll to previous buffer line (repeatedly)
[F7]
Present prior buffer lines for insertion into current buffer
N.B. If line inserted contains delimiter, buffer is sent, sounds pretty dangerous to me.
[F9]
copy prior buffer line nn into current buffer.[F2]
copy buffer to char [F4]
delete buffer to char
Data ManipulationClauses must be presented in the order shown
VALUES(column,…)
RESET |SET
DO
Information |
Data Types | ||||||||
DATE TIME DATETIME YEAR
TIMESTAMP |
ENUM DECIMAL(prec,scale) (fixed Point) ex:DECIMAL(5,2) ok for 999.99 (aka NUMERIC) FLOAT DOUBLE |
INT TinyINT SmallINT MediumINT BigINT
attribute: BOOLEAN† |
CHAR
VARCHAR
TEXT† |
BLOB TinyBLOB
MediumBLOB
BINARY |
Data Definition | ||||||||
ALTER [IGNORE] TABLE table
|
CREATE
| TABLE tablename table_options
(
Change the value of the
Inserting a NULL (as with an insert without specifying the column) † into an indexed
To retrieve the value after inserting a row, use information function (without specifying a table ) | |||||||
TABLE VIEW
|
GRANT |
INDEX: CREATE| DROP
|
Function and Operator Reference | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
String Functions FUNC(arg No space permitted after (
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
CONCAT('abc','def',7, …) numerics converted to string
| CONCAT_WS('sep',str1,str2 …) with inserting sep arator
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
FORMAT(n, d) a number with d decimal places using #,###,###.##
|
ABS() | absolute value | |||||||||||||||||||||||||||||||||||||||||
CEIL() | ||||||||||||||||||||||||||||||||||||||||||
CEILING() | smallest integer value not less than the argument | |||||||||||||||||||||||||||||||||||||||||
FLOOR() | largest integer value not greater than the argument | |||||||||||||||||||||||||||||||||||||||||
ROUND() | Round | |||||||||||||||||||||||||||||||||||||||||
TRUNCATE(x, d) | Truncate x to specified number of decimal places. 0=> integer negative numbers set low digits to zero, example TRUNCATE (122,-2) returns 100
| |||||||||||||||||||||||||||||||||||||||||
RAND() | random floating-point value | |||||||||||||||||||||||||||||||||||||||||
SIGN() | sign | |||||||||||||||||||||||||||||||||||||||||
|
MySQL Control Flow | |||||||||
CASE value WHEN [compare_value] THEN result |
AdministrationCREATE USER user IDENTIFIED BY PASSWORD 'password' GRANT SELECT UPDATE ON TABLE tbl_nameRENAME USER REVOKE SET PASSWORD ANALYZE CHECK* CHECKSUM CONSTRAINT
FLUSH QUERY CACHE SHOW CREATE VIEW |
Error codes N.B. Empty set
i.e. no rows found is NOT an error!!
See My SQL admin for addtional details.
show variables like 'AUTO%';
set autocommit =off;
N.B. If the connection to the server is lost (perhaps due to inactivity) and reconnected as indicated by: ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 6098700 autocommit is reset!
|
show variables like 'sql%'
$q ="insert …| SELECT …" $execute = $connect->query($q); $ar=$execute->affectedrows($q); $nr=$execute->numrows(); $nf=$execute->numfields();keep fetching rows as long as there are rows to fetch.
while(@results = $execute->fetchrow() ){ $r0=$results[0]; … }
Geographic featuresGEOMETRY GEOMETRY HIERARCHY SPATIAL |
RESET SLAVE
SHOW SLAVE STATUS
START/stop SLAVE
SET GLOBAL SQL_SLAVE_SKIP_COUNTER | ||||||||
Transactions Topics:HANDLER, ISOLATION, LOCK, SAVEPOINT, START TRANSACTION |
lock
).
If you really want to use them as column names, quote them.There are more (see the link)
case character default div from key keys limit order check ignore option signal call grant group dec references
condition interval purge lines load separator
char constraint
verbs:
alter analyze change collate continue convert create cross declare delete desc describe double drop exit explain fetch force get
index insert iterate join kill leave lock
loop match mod modifies natural not null numeric optimize
outfile partition precision primary procedure range read write
release rename repeat replace require resignal restrict return revoke
select sensitive set show
spatial specific starting table terminated trailing trigger true undo union
unique unlock unsigned update usage use
using values varying
then to when where while with
adjectives:
long real accessible float enclosed escaped exists dual each distinct decimal delayed both by
having if for before between out left right
on integer into int inner insensitive foreign cursor
like linear
optionally or
false outer
asc