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