Mysql语句

去重

select distinct a_id from a;

统计一张表内字段值相等的记录

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

内联

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;

授权远程访问

root使用123456从任何主机连接到mysql服务器。

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

允许用户jack从ip为10.10.50.127的主机连接到mysql服务器，并使用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 |
 +----------------------+-----------------+