MySQL常用指令

mysql5.6授权

USE mysql;
GRANT ALL PRIVILEGES ON `database`.* TO 'username'@'host' IDENTIFIED BY 'password';
FLUSH   PRIVILEGES;

mysql5.7授权

USE mysql;
select User,Host,plugin,authentication_string from user;
create user 'username'@'%';
GRANT ALL PRIVILEGES ON `database`.* TO 'username'@'host';
update user set authentication_string=password("password") where user='root';
update user set plugin="mysql_native_password";
grant process on *.* to dbuser;
FLUSH   PRIVILEGES;

MariaDB 10.5.15修改账号密码

SET PASSWORD [FOR user] =
    {
        PASSWORD('some password')
      | OLD_PASSWORD('some password')
      | 'encrypted password'
    }

创建表

DROP TABLE IF EXISTS `app_users`;
CREATE TABLE `app_users` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '用户编号',
  `username` varchar(64) NOT NULL DEFAULT '' COMMENT '用户名',
  `password` varchar(256) NOT NULL DEFAULT '' COMMENT '密码',
  `status` tinyint(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '状态：0-禁用；1-启用',
  `update_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=10000 DEFAULT CHARSET=utf8mb4 COMMENT '用户信息表';

修改表-新增列

ALTER TABLE `app_users` ADD COLUMN `register_type` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '注册类型：0-无;1-Android;2-IOS' AFTER `status`;

修改表-修改列

• modify 和 change
• modify 修改列属性
• change 修改列属性和列的重命名

ALTER TABLE `app_users` MODIFY `register_type` TINYINT(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '注册类型：0-无;1-Android;2-IOS' AFTER `status`;

插入数据

INSERT INTO `app_users` (username, password) VALUES ('username', '123456');

添加索引

ALTER TABLE `app_users` ADD INDEX status (`status`);

数据库导出并压缩

• --no-data 仅备份数据表结构，不备份数据。

mysqldump -h主机名 -P端口 -u用户名 -p密码 --databases 数据库名 | gzip > bak.sql.gz

sql语句分析

explain select * from app_users;