0.查看所有的库
SHOW DATABASES;
1.设置客户端连接数据库的字符集
SET NAMES 'utf8';
如果数据子的字符集是utf8，而客户端连接数据库的字符集是latin1，数据库中的中文就会出现乱码，类似?????
2.查看当前库下的所有表
show tables;
SHOW FULL TABLES FROM `abc` WHERE table_type = 'BASE TABLE';
3.查看当前库下的所有视图
SELECT `TABLE_NAME` FROM `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'abc' AND `TABLE_TYPE` = 'VIEW';
4.查看当前库下的所有存储过程
SELECT `SPECIFIC_NAME` FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'abc' AND ROUTINE_TYPE = 'PROCEDURE';
5.查看当前库下的所有函数
SELECT `SPECIFIC_NAME` FROM `INFORMATION_SCHEMA`.`ROUTINES` WHERE `ROUTINE_SCHEMA` = 'abc' AND ROUTINE_TYPE = 'FUNCTION';
6.查看当前库下的所有触发器
SELECT `TRIGGER_NAME` FROM `INFORMATION_SCHEMA`.`TRIGGERS` WHERE `TRIGGER_SCHEMA` = 'abc';
7.查看表结构
DESC `abc`.`test`;
8.查看索引
SHOW INDEX FROM `abc`.`test`;
9.查看表信息
SHOW TABLE STATUS FROM `test1`;
10.显示当前的数据库
SELECT database();
11.更换数据库引擎
ALTER TABLE `abc`.`test` ENGINE = INNODB;
12.DELAYED
使用insert delayed能够将插入项置于本地队列中直到锁定解除，不会让客户端等待插入完成。同理replace。
INSERT [ LOW_PRIORITY | DELAYED ] [ IGNORE ]
[ INTO ] tbl_name [ (col_name,...) ]
VALUES (expression,...),(...),...
自动补全
Ctrl + Enter：列出所有的tag
Ctrl + Space：列出匹配的tag
Tab
命令模板
1.表改名
RENAME TABLE `abc`.`test` to `abc`.`test1`
2.锁表
LOCK TABLES test1 READ
添加读锁，其他的事务只能读test1表，不能写test1表
LOCK TABLES test1 WRITE
添加写锁，其他的事务堵塞，既不能读test1表，也不能写test1表
UNLOCK TABLES 释放锁
当连接断开时，也会释放此连接拥有的锁
FLUSH刷新操作--清除缓存（必须拥有reload权限）
FLUSH LOGS;
FLUSH HOSTS;
FLUSH PRIVILEGES;刷新用户权限，当直接操作mysql.user表时，需要手工刷新用户权限，否则，无法访问。
update mysql.user set password=PASSWORD('123') where User='root';
FLUSH TABLES;
FLUSH STATUS;
FLUSH DES_KEY_FILE;
FLUSH QUERY CACHE;用来清理查询缓存碎片以提高内存使用率。
查询缓存：针对select 语句完全相同，并且数据/数据表未更改；因此insert, update, delete, truncate等都会使cache失效。
RESET QUERY CACHE;则是从查询缓存中移出所有查询。FLUSH TABLES也执行相同的工作。
FLUSH USER_RESOURCES;
FLUSH TABLES WITH READ LOCK;
全局读锁，执行后数据库所有表都被锁定只读。一般用在数据库联机备份，此时表的写操作被阻塞。
LOCK TABLES XXX READ;
表级读锁,可以锁定某个表。
UNLOCK TABLES;
解锁，全局/表级都可以解
SQLyog快捷键
Connection
Ctrl+M Create A New Connection
Ctrl+N Create A New Connection With Current Connection Properties
Ctrl+F4/Ctrl+W Disconnect The Current Connection
Ctrl+Tab Switch to Next Connection
Ctrl+Shift+Tab Switch to Previous Connection
Ctrl+1...8 Select Connection (1 to 8)
Ctrl+9 Select Last Connection
Object Browser
F5 Refresh Object Browser (Default)
Ctrl+B Set Focus On Object Browser
SQL Window
Ctrl+T New Query Editor
Ctrl+E Set Focus On SQL Window
Ctrl+Y Redo
Ctrl+Z Undo
Ctrl+X Cut
Ctrl+V Paste
Ctrl+H Replace
Ctrl+G Go to
Ctrl+O Open File
Ctrl+Shift+U Make Selection Uppercase
Ctrl+Shift+L Make Selection Lowercase
Ctrl+Shift+C Comment Selection In SQL Window
Ctrl+Shift+R Remove Comment From Selection
Ctrl+Shift+T Insert Templates
Ctrl+Enter List All Tags
Ctrl+Space List Matching Tags
Query Execution
F8 Execute Current Query And Edit Result
F9 Execute Current Query (Default)
Ctrl+F9 Execute Selected Query(s) (Default)
Shift+F9 Execute All Query(s) (Default)
SQL Formatter
F12 Format Current Query
Ctrl+F12 Format Selected Query
Shift+F12 Format All Queries
Paste SQL Statements
Alt+Shift+I Insert Statement
Alt+Shift+U Update Statement
Alt+Shift+D Delete Statement
Alt+Shift+S Select Statement
Result
F11 Insert Update Dialog
Ctrl+R Set Focus To The Active Tab In Result Pane
Ctrl+L Switch Result Window/Table Data Between Grid/Text Mode
Ctrl+Alt+C Export Table Data As CSV, Excel, SQL etc...
Ctrl+Alt+E Backup Data As SQL Dump
Ctrl+Shift+M Import Data From CSV
Ctrl+Shift+E Export Resultset
Alt+1...n Select Nth Tab In Result Window
Show/Hide
Ctrl+Shift+1 Show/Hide Object Browser
Ctrl+Shift+2 Show/Hide Result Pane
Ctrl+Shift+3 Show/Hide Query Window
Database/Table
Ctrl+D Create Database
F6 Alter Database/Table Structure/Index/View/Store Proc/Function/Trigger/Event
F2 Rename Table/View/Trigger/Event
Shift+Del Truncate Database/Table
F7 Manage Index Window
F10 Relationships/Foreign Keys
Ctrl+Alt+R Reorder Column(s)
Ctrl+Alt+T Table Diagnostics
Ctrl+Alt+F Flush Dialog
Ctrl+Shift+Alt+S Create Schema For Database In HTML
Ctrl+Shift+Q Restore From SQL Dump
PowerTools
Ctrl+Alt+W Database Synchronization Wizard
Ctrl+Q Schema Synchronization Tool
Ctrl+Alt+O Import External Data Wizard
Ctrl+Alt+N Notification Services Wizard
Ctrl+Alt+S Scheduled Backup Wizard
Ctrl+K Query Builder
Ctrl+Alt+D Schema Designer
User Manager
Ctrl+U Add A New User
Ctrl+Alt+U Edit User
Ctrl+Shift+W Manage Privileges
Favorites
Ctrl+Shift+F Add Current SQL To Favorites
Other
F1 Help
Ctrl+C Copy
Ctrl+A Select All
Ctrl+F Find (SQL Window/Result Window And Table Data In Text Mode/Blob Viewer)
F3 Find Next (SQL Window/Result Window And Table Data In Text Mode)
Ctrl+S Save
Ctrl+PgUp Switch To Previous Tab
Ctrl+PgDown Switch To Next Tab
DEL Delete Selected
Alt+L Close Tab
Alt+F4 Exit
