Updated at: May 12, 2017
Oracle aquire Sun which had acquired MySQLAB
MariaDB # open source
> mysql -h host -u user -p
Enter password: ********
> mysql -h host -u user -p dbname
Enter password: ********
> mysql -h host -u user -p(no space here!!!)passwd dbname
Enter password: ********
batch mode: Executing SQL Statements from a Text File
> mysql -h host -u user -p < batch-file
--force continue next when error
-t display output like interactive
-v display SQL
mysql> source batch-file
mysql> \. batch-file
mysql> select version(),current_date;
+-------------------------+--------------+
| version() | current_date |
+-------------------------+--------------+
| 5.7.17-0ubuntu0.16.04.1 | 2017-03-19 |
+-------------------------+--------------+
mysql> select user(),now();
+----------------+---------------------+
| user() | now() |
+----------------+---------------------+
| root@localhost | 2017-03-19 12:53:56 |
+----------------+---------------------+
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| debian-sys-maint |
| guest |
| mysql.sys |
| root |
+------------------+
mysql> show grants for guest@localhost;
+---------------------------------------------------------+
| Grants for guest@localhost |
+---------------------------------------------------------+
| GRANT USAGE ON *.* TO 'guest'@'localhost' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'guest'@'localhost' |
+---------------------------------------------------------+
\hmysql> \h
For information about MySQL products and services, visit:
http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
https://shop.mysql.com/
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
? (\?) Synonym for `help'.
clear (\c) Clear the current input statement.
connect (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit (\e) Edit command with $EDITOR.
ego (\G) Send command to mysql server, display result vertically.
exit (\q) Exit mysql. Same as quit.
go (\g) Send command to mysql server.
help (\h) Display this help.
nopager (\n) Disable pager, print to stdout.
notee (\t) Don't write into outfile.
pager (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print (\p) Print current command.
prompt (\R) Change your mysql prompt.
quit (\q) Quit mysql.
rehash (\#) Rebuild completion hash.
source (\.) Execute an SQL script file. Takes a file name as an argument.
status (\s) Get status information from the server.
system (\!) Execute a system shell command.
tee (\T) Set outfile [to_outfile]. Append everything into given outfile.
use (\u) Use another database. Takes database name as argument.
charset (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.
For server side help, type 'help contents'
mysql> help show;
Name: 'SHOW'
Description:
SHOW has many forms that provide information about databases, tables,
columns, or status information about the server. This section describes
those following:
SHOW {BINARY | MASTER} LOGS
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE EVENT event_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW CREATE TRIGGER trigger_name
SHOW CREATE VIEW view_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {STATUS | MUTEX}
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW EVENTS
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW MASTER STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PLUGINS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW RELAYLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
SHOW SLAVE HOSTS
SHOW SLAVE STATUS [NONBLOCKING]
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW [FULL] TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]
like_or_where:
LIKE 'pattern'
| WHERE expr
If the syntax for a given SHOW statement includes a LIKE 'pattern'
part, 'pattern' is a string that can contain the SQL "%" and "_"
wildcard characters. The pattern is useful for restricting statement
output to matching values.
Several SHOW statements also accept a WHERE clause that provides more
flexibility in specifying which rows to display. See
http://dev.mysql.com/doc/refman/5.7/en/extended-show.html.
URL: http://dev.mysql.com/doc/refman/5.7/en/show.html
mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
-> LINES TERMINATED BY '\r\n';
| Name | Description |
|---|---|
| AVG() | Return the average value of the argument |
| BIT_AND() | Return bitwise AND |
| BIT_OR() | Return bitwise OR |
| BIT_XOR() | Return bitwise XOR |
| COUNT() | Return a count of the number of rows returned |
| COUNT(DISTINCT) | Return the count of a number of different values |
| GROUP_CONCAT() | Return a concatenated string |
| MAX() | Return the maximum value |
| MIN() | Return the minimum value |
| STD() | Return the population standard deviation, 总体标准差, 根号内除以n |
| STDDEV() | Return the population standard deviation, This function is provided for compatibility with Oracle. |
| STDDEV_POP() | Return the population standard deviation, You can also use STD() or STDDEV(), which are equivalent but not standard SQL. |
| STDDEV_SAMP() | Return the sample standard deviation, 样本标准差, 根号内除以(n-1) |
| SUM() | Return the sum |
| VAR_POP() | Return the population standard variance, You can also use VARIANCE(), which is equivalent but is not standard SQL. |
| VAR_SAMP() | Return the sample variance, square of STDDEV_SAMP() |
| VARIANCE() | Return the population standard variance |
A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. For example:
SELECT * FROM t1
WHERE column1 = ANY (SELECT column1 FROM t2
WHERE t2.column2 = t1.column2);
Foreign Key, references table_name( col_name )
MySQL’s implementation of foreign keys differ from SQL standard; InnoDB tables support checking, while others not. You can define but there is no actual effect, like a reminder/comment.
mysql> CREATE TABLE people( last_name varchar(50) not null, first_name varchar(50) not null, dob date not null, gender enum('m','f') not null, KEY idx_name_dob (last_name,first_name,dob) );
mysql> show index from people;
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people | 1 | idx_name_dob | 1 | last_name | A | 0 | NULL | NULL | | BTREE | | |
| people | 1 | idx_name_dob | 2 | first_name | A | 0 | NULL | NULL | | BTREE | | |
| people | 1 | idx_name_dob | 3 | dob | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> CREATE TABLE testhash( fname varchar(50), lname varchar(50), KEY idx_hash USING HASH(fname)) ENGINE = MEMORY;
mysql> show index from testhash;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| testhash | 1 | idx_hash | 1 | fname | NULL | 0 | NULL | NULL | YES | HASH | | |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
CREATE TABLE `json_test` (
`row_number` int(11) NOT NULL AUTO_INCREMENT,
`data` json NOT NULL,
PRIMARY KEY (`row_number`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
mysql> select data->'$**."股东"' from json_test;
+------------------------------------------+
| data->'$**."股东"' |
+------------------------------------------+
| ["宫婷", "饶敢", "饶敢", "宫婷"] |
+------------------------------------------+