去重

select distinct a_id from a;

統(tǒng)計一張表內(nèi)字段值相等的記錄

select count(*) as c,a,b from mytable group by a,b having c > 1;

內(nèi)聯(lián)

select b.* from a,b where a.id=b.id and a.type="c"\G

按照心跳時間排序坐搔，找前10位

select * from my_table where sex="***" order by heartbeat_timestamp desc limit 10\G

按test分組婉徘，找出前10個最多的

select test,count(*) from my_table group by test order by count(*) desc limit 10;

授權(quán)遠程訪問

root使用123456從任何主機連接到mysql服務(wù)器漠嵌。

mysql>GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;
mysql>flush privileges;

允許用戶jack從ip為10.10.50.127的主機連接到mysql服務(wù)器，并使用654321作為密碼

mysql>GRANT ALL PRIVILEGES ON *.* TO 'jack'@’10.10.50.127’ IDENTIFIED BY '654321' WITH GRANT OPTION;
mysql>flush privileges;

查看編碼

First query:

 mysql> show variables like 'char%';
The output should look like:

 +--------------------------+---------------------------------+
 | Variable_name            | Value                           |
 +--------------------------+---------------------------------+
 | character_set_client     | utf8                            |
 | character_set_connection | utf8                            |
 | character_set_database   | utf8                            |
 | character_set_filesystem | binary                          |
 | character_set_results    | utf8                            |
 | character_set_server     | utf8                            |
 | character_set_system     | utf8                            |
 | character_sets_dir       | /usr/local/mysql/share/charsets/|
 +--------------------------+---------------------------------+
Second query:

 mysql> show variables like 'collation%';
And the query output is:

 +----------------------+-----------------+
 | Variable_name        | Value           |
 +----------------------+-----------------+
 | collation_connection | utf8_general_ci |
 | collation_database   | utf8_unicode_ci |
 | collation_server     | utf8_unicode_ci |
 +----------------------+-----------------+