# 常用命令

# 超时时间设置

查看mysql server超时时间:

msyql> show global variables like '%timeout%';

设置mysql server超时时间(以秒为单位):

msyql> set global wait_timeout=10;
msyql> set global interactive_timeout=10;

# 添加,更新,删除表结构字段

alter table table_name add column column_name tinyint(1) not null default '0';
alter table table_name modify(change) column column_name varchar(39) not null;
alter table table_name drop column column_name;

# 索引,自增等

CREATE INDEX index_name ON table_name(column_name); //创建普通索引
alter table table_name auto_increment = x ;
alter table table_name add primary key (column_name);
alter table table_name modify priceid int(11) auto_increment;

# 复制表结构

CREATE TABLE new_table LIKE old_table;

# 导入数据库

方法一:

mysql>use db_name;//选择数据库
mysql>set names utf8;//设置数据库编码
mysql>source /tmp/abc.sql;//导入数据(注意sql文件的路径)

方法二:

mysql -uroot -p'pwd' dbname < dbname.sql

# 导出结构和数据

mysqldump -uroot -p --default-character-set=utf8 dbname tablename > bak.sql
//只导结构 -d

# 查看用户权限

show grants;//查看当前用户(自己)权限
show grants for user@localhost;//查看其他 MySQL 用户权限

# 授权用户权限

grant all on *.* to 'root'@'%' [identified by 'yourpassword'];
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.1.1' IDENTIFIED BY 'newpwd' WITH GRANT OPTION;

# 新建utf8库

CREATE DATABASE `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

# 新建用户并授权库

create user 'test'@'%' identified by '123456';
CREATE USER 'root123'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root123';

# 刷新权限

flush privileges;