Mysql常用命令

1. 用户

​ 连接数据库

mysql -u root -p

​ 修改用户密码

update user set password=password(”密码″) where user=’root’;

​ 重启 mysql 服务

service mysqld restart;

​ 清空所有日志

reset master; 

​ 关闭更新时的安全模式

SET SQL_SAFE_UPDATES=0;

​ 忘记密码

​ 在 my.cnf 或 my.ini 文件的 mysqld 配置段添加 skip-grant-tables ，然后重新启动 mysql 即可登录修改 root密码

（1）显示

​ 显示当前用户

select user();

​ 显示当前 mysql 版本和当前日期

select version(),current_date;

（2）查看

​ 查看参数值

show variables like "%log%";

​ 查看最后一个bin日志

show master status;

​ 查看安装的 plugin

show plugins; 

​ 查看大小写是否敏感 mysql中控制数据库名和表名的大小写敏感由参数lower_case_table_names控制，为0时表示区分大小写，为1时，表示将名字转化为小写后存储，不区分大小写并且以_ci（大小写不敏感）、_cs（大小写敏感）或_bin 大小写敏感

SHOW VARIABLES LIKE '%case%';

​ 查看存储引擎

show engines;

​ 关闭外键 约束

SELECT @@FOREIGN_KEY_CHECKS; 
SET FOREIGN_KEY_CHECKS=1; // 开启外键约束 
SET FOREIGN_KEY_CHECKS=0; // 关闭外键约束 

​ MySQL的一些配置，比如查看MySQL的数据库文件存放在那个目录就可以用下面的命令

show variables where Variable_name ='datadir';

​ 查看当前数据库有哪些用户可以访问

select user,host,password from mysql.user;

​ 查看用户权限

show grants for user_name@localhost;

​ 查看错误日志的存放位置

show variables like '%log_error%';

​ 查看 MySQL 加载配置文件的顺序，后面的配置会覆盖前面相同的配置项

mysqld --help --verbose | grep -A 1 'Default options'

2. 数据库

​ 显示所有的数据库

show databases;

​ 查看某个库的具体情况

show table status from db_name;

​ 创建数据库

create database name;

​ 创建数据库, utf8_bin 区分大小写，utf8_general_ci 不区分大小写

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_bin;

​ 打开指定的数据库

use 数据库名;

​ 显示use的数据库名

select database();

​ 删除指定的数据库

drop database name;
drop database if exists 库名  //如果存在该数据库，则删除

​ 删除数据库前，有提示

mysqladmin drop 库名

3. 数据表

​ 显示当前数据库的所有数据表

show tables;

​ 展开表信息

describe tablename;

​ 查询出数据库中所有的表信息

select table_name from information_schema.tables where table_schema='数据库名' and table_type='base table'; select * from information_schema.tables where table_schema='数据库名' and table_type='base table';

​ 查询 最后 10 条数据（ id 自增长）

select * from wy_user order by id desc limit 10;

​ 按某一列的值的长度查找 // UTF8 编码中文长度为 3

SELECT * FROM `bs_member` WHERE city like '%北京%' and length(city) > 7

​ 建立数据表

create table 表名 (字段名 varchar(20), 字段名 char(1));

​ 给表添加注释

alter table 表名 comment '注释的内容';

​ 查看某个表的注释

select table_name,table_comment FROM information_schema.tables where table_name='表名'

​ 删除数据表

drop table 表名;

​ 删除一张表里所有的数据

truncate 表名;

​ 查看创建表的 sql 语句

show create table 表名;

​ 排序

select * from '表名' order by colname desc, colname asc

​ 将字符型的 数字（ID_）转成数字来排序 +0 或 *1

select * from '表名' order by ID_+0 asc;

​ 分组

select 'colname',count(*) as total from '表名' group by colname

​ 往表中插入记录

insert into 表名 values()

​ 更新表中数据

update 表名 set 字段名1='a',字段名2='b' where 字段名3='c';

​ 用文本方式将数据装入数据表中

load data local infile "文件路径" into table 表名;

​ 去除重复字段

select 中加上distinct;

​ 重命名表

alter table 旧表名 rename 新表名;

4. 索引

​ 添加主键索引

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

​ 添加唯一索引

ALTER TABLE `table_name` ADD UNIQUE ( `column` )

​ 添加普通索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 
ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

​ 添加全文索引

ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

​ 删除索引

DROP INDEX index_name ON table_name

​ 查看

​ 查看表的索引信息

show index from 表名

​ 查询时禁止使用主键索引

select * from tableName ignore index(PRI)

​ 查询时禁止使用某些索引

select * from tableName ignore index(PRI, indexName)

​ 查询时强制使用主键索引

select * from tableName force index(PRI)

​ 查询时强制使用某些索引

select * from tableName force index(PRI, indexName)

5. grant

​ 创建一个可以从任何地方连接服务器的一个完全的超级用户，但是必须使用一个口令something做这个

grant all privileges on *.* to user@localhost identified by ’something’ with
grant all on *.* to user1@192.168.67.1 identified by "pawd123";

​ 增加新用户

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”
GRANT ALL PRIVILEGES ON *.* TO monty@localhost IDENTIFIED BY ’something’ WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO monty@”%” IDENTIFIED BY ’something’ WITH GRANT OPTION;

​ 删除授权

revoke all privileges on *.* from root@”%”;
delete from user where user=”root” and host=”%”;
flush privileges;

6. 数据库的导入与导出

​ 备份数据库

mysqldump -h host -u root -p dbname >dbname_backup.sql

​ 导出整个数据库（文件默认存在 mysql\bin 目录下）

mysqldump -u 用户名 -p 数据库名 > 导出文件名
mysqldump -u user_name -p database_name table_name > outfile_name.sql

​ 恢复数据库

mysqladmin -h myhost -u root -p create dbname
mysqldump -h host -u root -p dbname < dbname_backup.sql

 将 **CSV** 文件导入 **Mysq**l 中

LOAD DATA LOCAL INFILE '/Users/fangcailiang/Downloads/dbs/db-friend/aff11.csv' into table user1 FIELDS TERMINATED BY ',' lines terminated by '\n' ignore 1 lines (pwsid,email,country,sex,birthday,state,zip,ip);

​ 刷新数据库

flush privileges;