mysql命令

mysql命令

常用代码

// 设置自增字段
alter table 数据表名 modify 字段名 int auto_increment;
alter table demo modify id int auto_increment;
// 修改自增 起始值
alter table 数据表名 auto_increment = 1001;
alter table demo auto_increment = 1000;

linux终端

//登录mysql(有密码请输入密码)
mysql -u root -p
//打开数据库
show database;
//设置密码
set password = password('123456');
//退出
exit;
//使用 mysql 数据库
use mysql;
// 查询MySQL账户
select user,host from user;
//更改访问限制 
update user set host=’%’where user=’用户名’@'本地账户';
//示例:
update user set host=’%’where user=’root’@'localhost';
//新增远程访问账号 
grant all privileges on 数据库.数据表 to 用户名 @"%" identified by "密码";
//示例:
grant all privileges on *.* to remoteUser @"%" identified by "123456";
//更新配置
flush privileges;
//创建用户
CREATE USER '远程用户'@'%' IDENTIFIED BY '密码';
//示例:
CREATE USER 'remoteUser'@'%' IDENTIFIED BY '123456';
修改密码
ALTER  user '用户名'@'本地账户'IDENTIFIED BY '密码';
//示例:
ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
// 用户分配权限
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `数据库名称`.* TO '远程用户'@'%';
//示例:
GRANT SELECT, INSERT, UPDATE, REFERENCES, DELETE, CREATE, DROP, ALTER, INDEX, TRIGGER, CREATE VIEW, SHOW VIEW, EXECUTE, ALTER ROUTINE, CREATE ROUTINE, CREATE TEMPORARY TABLES, LOCK TABLES, EVENT ON `mysql`.* TO 'remoteUser'@'%';
// 分配指定表
GRANT GRANT OPTION ON `数据库名称`.* TO '远程用户'@'%';
GRANT GRANT OPTION ON `mysql`.* TO 'remoteUser'@'%';