MySQL常见操作(不断更新)

MySQL创建GBK字符集数据库:

1
2
3
GBK: create database `test` DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
UTF8: CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

MySQL修改root密码

  • 用SET PASSWORD命令

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('mynewpassword');

  • 用mysqladmin

    mysqladmin -u root password "mynewpassword"

如果root已经设置过密码,采用如下方法

`mysqladmin -u root password oldpass "mynewpassword"`
  • 用UPDATE直接编辑user表

    1
    2
    3
    4
    5
    mysql> use mysql;
    mysql> UPDATE user SET Password = PASSWORD('mynewpassword') WHERE user = 'root';
    mysql> FLUSH PRIVILEGES;
  • 如果root密码丢失,可以这样:
    使用下面的方法,重启mysql

    mysqld_safe --skip-grant-tables&

    然后登录mysql

    mysql -u root mysql    
    mysql> UPDATE user SET password=PASSWORD("mynewpassword") WHERE user='root';
    mysql> FLUSH PRIVILEGES;
    

MySQL中取Group By后的Top N问题
比如取group by后每个分组的Top 3

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT
mac_group,
mac,
dur
FROM
(
SELECT
mac_group,
sum(sum_dur) as dur,
mac,
@rn := IF(@prev = mac_group and @prev_mac = mac, @rn + 1, 1) AS rn,
@prev := mac_group,
@prev_mac := mac
FROM mac_customer_stats
JOIN (SELECT @prev := NULL, @prev_mac :=NULL, @rn := 0) AS vars
where mac_group in (1,2,3)
group by mac_group, mac, day
ORDER BY mac_group, mac, dur DESC
) AS T1
WHERE rn <= 3;