1. 系统命令
功能 | 语法 |
---|
连接 | mysql -h 主机地址 -u 用户名 -p 密码 (当mysql为本地服务时 -h可以省略) |
退出 | exit 或 quit |
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生效。