MySQL 命令
MySQL 常用命令速查，查询、备份、授权、索引分析全覆盖，复制即用。
广告位（百度联盟）
| 命令 | 说明 | 示例 | |
|---|---|---|---|
| mysql -u root -p | 本地登录数据库 | mysql -u root -p | |
| mysql -h | 远程连接指定端口 | mysql -h 10.0.0.5 -P 3306 -u app -p | |
| mysql -e | 免交互执行单条 SQL | mysql -e "SHOW PROCESSLIST;" -u root -p | |
| show databases; | 列出所有数据库 | show databases; | |
| use app; | 切换当前数据库 | use shop; | |
| show tables; | 列出当前库表 | show tables; | |
| describe t; | 查看表结构 | describe user; | |
| show create table | 查看建表语句 | show create table order; | |
| select ... limit | 条件查询示例 | select id,name from user where status=1 limit 20; | |
| explain | 分析 SQL 执行计划 | explain select * from order where user_id=10; | |
| insert into | 插入一条数据 | insert into user(name,email) values("tom","t@it586.cn"); | |
| update ... set | 更新数据 | update user set status=0 where id=10; | |
| delete from | 删除数据 | delete from session where expire<now(); | |
| create database | 创建数据库（UTF8MB4） | create database shop default charset utf8mb4; | |
| create user | 创建数据库用户 | create user "app"@"%" identified by "P@ssw0rd"; | |
| grant all | 授权用户访问库 | grant all privileges on shop.* to "app"@"%"; | |
| flush privileges; | 刷新权限 | flush privileges; | |
| alter table | 修改表结构 | alter table user add column age int; | |
| show processlist; | 查看连接进程 | show processlist; | |
| show index from | 查看表索引 | show index from order; | |
| source | 执行 SQL 脚本文件 | source /tmp/init.sql; | |
| set password | 修改用户密码 | alter user "root"@"localhost" identified by "new"; | |
| mysqldump | 备份整个数据库 | mysqldump -u root -p --single-transaction shop > shop.sql | |
| mysql < file | 从文件恢复数据库 | mysql -u root -p shop < shop.sql | |
| mysqldump -d | 仅导出表结构 | mysqldump -u root -p -d shop > shop.schema.sql |