mysql客户端---SQLyog

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