mysql常用命令

linux中mysql启停命令

sudo /etc/init.d/mysql.server -P 3306 start
sudo /etc/init.d/mysql.server -P 3306 stop

将查询结果导入到文件

mysql>tee /home/a.txt
mysql>select * from rank;
mysql>exit

设置字符集

mysql>set names utf8;

查看所有用户及权限

SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user;

属性增删改

ALTER TABLE table_name ADD COLUMN `add_column_name` VARCHAR(100) NOT NULL DEFAULT '' COMMENT 'some comment' AFTER `some column name`;
ALTER TABLE table_name delete COLUMN `delete_column_name`;
ALTER TABLE table_name MODIFY `modify_column_name` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'some comment';

若修改列名用change：

ALTER TABLE table_name CHANGE `old_column_name` `new_column_name` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'some comment';

索引增删

ALTER TABLE 方式增加索引：

alter table table_name add index index_name (column_list) ;
alter table table_name add unique (column_list) ;
alter table table_name add primary key (column_list) ;

CREATE INDEX 方式增加索引：

create index index_name on table_name (column_list) ;
create unique index index_name on table_name (column_list) ;

删除索引：

drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;