MySQL常用命令

1. 系统命令

功能语法
连接mysql -h 主机地址 -u 用户名 -p 密码 (当mysql为本地服务时 -h可以省略)
退出exitquit

2. 数据库操作

功能语法
创建库create database 库名
删除库drop database 库名
显示库show databases
使用库use 库名
查看当前使用的库select database()

3. 表操作

功能语法
显示表show tables
使用表use 表名
删除表drop table 表名
显示表结构describe 表名 可简写为 desc 表名,或者 **show columns from 表名 **
更改表名rename table 原表名 to 新表名
创建表create table 表名 ( 字段名 类型 [,…字段名n 类型n] )
表中增加字段alter table 表名 add 字段名 类型 其他
表中删除字段alter table 表名 drop column 字段名
表中修改字段类型alter table 表名 modify 字段名 类型 其他
表中修改字段alter table 表名 change 旧字段名 新字段名 类型 其他 (change与modify相比,可以修改字段名)

4. 表SQL

功能语法
插入数据insert into 表名 [( 字段名[,…字段名n ])] values ( 值 [,…值n )] (当values前面的字段名省略时,表示插入所有字段)
删除数据delete from 表名 where 表达式
修改数据update 表名 set 字段=新值,… where 条件
查询数据select 字段,字段,… from 表名 where 表达式
分页查limit 起始位,分页大小

5. 分析查询语句

explain select语句describe select语句(可简写为desc)

查询结果值含义
id编号
select_type类型:SIMPLE简单查询,其中不包括连接查询和子查询;PRIMARY表示主查询,或者是最外层的查询语句;UNION表示连接查询的第二个或后面的查询语句
table查询的表
type表的连接类型:
const表示表中有多条记录,但只从表中查询一条记录;
eq_ref表示多表连接时,后面的表使用了UNIQUE或者PRIMARY KEY;
ref表示多表查询时,后面的表使用了普通索引;
unique_ subquery表示子查询中使用了UNIQUE或者PRIMARY KEY;
index_ subquery表示子查询中使用了普通索引;
range表示查询语句中给出了查询范围;
index表示对表中的索引进行了完整的扫描;
all表示此次查询进行了全表扫描
possible_keys表示查询中可能使用的索引,如果备选的数量大于3那说明已经太多了,因为太多会导致选择索引而损耗性能, 所以建表时字段最好精简,同时也要建立联合索引,避免无效的单列索引
key表示查询使用到的索引
key_len表示索引字段的长度
ref表示使用哪个列或常数与索引一起来查询记录
rows表示查询的行数(估值,越小越好)
Extra表示查询过程的附件信息

6. 索引

索引类型描述
INDEX普通索引:可重复
UNIQUE唯一索引:唯一、可为NULL
PROMARY KEY主键索引:唯一、不为空
FULLTEXT全文索引:可以针对值中的某个单词,但效率确实不敢恭维
功能语法
修改表结构的方式创建索引alter table 表名 add 索引类型 [索引名] (字段名 [...,字段名])
直接创建索引create 索引类型 索引名 on 表名(字段名 […,字段名])
创建表的同时创建索引
CREATE TABLE `al_test` (
  `al_id` int(11) NOT NULL COMMENT '主键',
  `al_no` varchar(64) DEFAULT NULL,
  `al_type` varchar(255) DEFAULT NULL,
  `al_desc` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`al_id`),
  UNIQUE KEY `uk_no` (`al_no`),
  KEY `idx_type` (`al_type`),
  FULLTEXT KEY `ft_desc` (`al_desc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
功能语法
修改表结构的方式删除索引alter table 表名 drop index 索引名
直接删除索引drop index 索引名 on 表名
查看表的索引show indexes from 表名show keys from 表名
修改索引MySQL索引没有修改,可以先删除,再添加

7. 导入、导出(备份)

导出 SQL 格式的数据语法
导出所有mysqldump -u 用户名 -p --all-databases > > 导出的文件名.sql
导出指定数据库mysqldump -u 用户名 -p 数据库名 [表名] > 导出的文件名.sql (当不指明表名时导出整个数据库)
仅导出结构mysqldump -u 用户名 -p -d 数据库名 [表名] > 导出的文件名.sql (-d代表不导出数据)
导入数据库**source 文件名.sql ** (需要先登录mysql并且指定数据库)
导出查询出来的数据语法
导出查询结果到文件查询语句 into outfile ‘文件名’
如:select * from al_test into outfile ‘/al_test.txt’

常见异常: The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
表示导出的文件目录错误(没有权限导出到此目录)
可以执行show variables like "secure_file_priv" 查看允许的目录
如果value值为null,则为禁止所有目录;如果为空,则不限制目录;如果有文件夹目录,则只允许改目录下文件。
可以进入mysql配置文件配置修改 secure_file_priv =
修改之后需要重启mysql生效。