MySQL常⽤SQL语句之SHOW语句详解SQL语句之SHOW语句
SHOW DATABASES – 显⽰当前所有数据库的名称
mysql> SHOW DATABASES;
SHOW TABLES – 显⽰当前数据库中所有表的名称(需要设置默认数据库use DATABASE_NAME)
Mysql> SHOW TABLES;
SHOW TABLES FROM db_name – 显⽰数据库中的所有表
Mysql> SHOW TABLES FROM db_name;
SHOW ENGINES - 显⽰MySQL当前⽀持哪些存储引擎和默认存储引擎
mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
SHOW CHARACTER SET – 显⽰MySQL当前⽀持哪些字符集
mysql> SHOW CHARACTER SET;
+----------+-----------------------------+---------------------+--------+
| Charset | Description | Default collation | Maxlen |
+----------+-----------------------------+---------------------+--------+
| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 |
| dec8 | DEC West European | dec8_swedish_ci | 1 |
-------------------------------------------------------------------------
mysql> SHOW CHARACTER SET LIKE '%utf%';
+---------+------------------+--------------------+--------+
| Charset | Description | Default collation | Maxlen |
+---------+------------------+--------------------+--------+
| utf8 | UTF-8 Unicode | utf8_general_ci | 3 |
| utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 |
| utf16 | UTF-16 Unicode | utf16_general_ci | 4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 |
| utf32 | UTF-32 Unicode | utf32_general_ci | 4 |
+---------+------------------+--------------------+--------+
5 rows in set (0.00 sec)
SHOW COLLATION – 显⽰MySQL⽀持字符集的排序规则
mysql> SHOW COLLATION;
+--------------------------+----------+-----+---------+----------+---------+
| Collation | Charset | Id | Default | Compiled | Sortlen |
+--------------------------+----------+-----+---------+----------+---------+
| big5_chinese_ci | big5 | 1 | Yes | Yes | 1 |
| big5_bin | big5 | 84 | | Yes | 1 |
SHOW BINARY | MASTER – 显⽰⼆进制⽂件以及⽂件⼤⼩（需要开启⼆进制⽇志记录功能）
mysql> SHOW BINARY LOGS;
mysql> SHOW MASTER LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 143 |
| mysql-bin.000002 | 143 |
SHOW BINLOG EVENTS – 显⽰⼆进制⽂件的执⾏过程
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.27-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Stop | 1 | 143 | |
+------------------+-----+-------------+-----------+-------------+---------------------------------------+
2 rows in set (0.00 sec)
mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001' FROM 120;
+------------------+-----+------------+-----------+-------------+------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+------------+-----------+-------------+------+
| mysql-bin.000001 | 120 | Stop | 1 | 143 | |
+------------------+-----+------------+-----------+-------------+------+
负数的补码是反码加一是什么意思1 row in set (0.01 sec)
SHOW COLUMNS – 显⽰表的列信息（等同于DESC，需要先创建表）
mysql> SHOW COLUMNS FROM blog.info;
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| NAME | char(8) | NO | | NULL | |
| AGE | int(11) | NO | | NULL | |
| ADDRESS | varchar(20) | NO | | NULL | |
| SALARY | decimal(10,2) | NO | | NULL | |
+---------+---------------+------+-----+---------+----------------+
5 rows in set (0.0
6 sec)
SHOW CREATE DATABASES – 显⽰已经创建的库，创建时的语句
mysql> SHOW CREATE DATABASE blog;
+----------+-----------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
android studio模拟器运行不出来1 row in set (0.00 sec)
SHOW CREATE TABLE – 显⽰已经创建的表，创建时的语句
mysql> SHOW CREATE TABLE info\G
*************************** 1. row ***************************
Table: info
Create Table: CREATE TABLE `info` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` char(8) NOT NULL,
`AGE` int(11) NOT NULL,
`ADDRESS` varchar(20) NOT NULL,
`SALARY` decimal(10,2) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
SHOW CREATE FUNTCION – 显⽰已经创建的函数，创建时的语句SHOW CREATE PROCEDURE – 显⽰已经创建的存储过程，创建时的语句
当绝缘材料发生击穿放电mysql> SHOW CREATE PROCEDURE test.simpleproc\G
*************************** 1. row ***************************
Procedure: simpleproc - 存储过程的名字
sql_mode:
Create Procedure: CREATE PROCEDURE `simpleproc`(OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t;
END
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW CREATE TRIGGER - 显⽰已经创建的触发器，创建时的语句
mysql> SHOW CREATE TRIGGER ins_sum\G
*************************** 1. row ***************************
Trigger: ins_sum
sql_mode: NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`me`@`localhost` TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
SHOW CREATE VIEW – 显⽰已经创建的视图，创建时的语句
mysql> SHOW CREATE VIEW v\G
*************************** 1. row ***************************
View: v
Create View: CREATE ALGORITHM=UNDEFINED
DEFINER=`bob`@`localhost`
SQL SECURITY DEFINER VIEW
`v` AS select1 AS `a`,2 AS `b`
character_set_client: latin1
collation_connection: latin1_swedish_ci
SHOW CREATE EVENTS – 显⽰已经创建的事件，创建时的语句
mysql> SHOW CREATE EVENT test.e_daily\G
*************************** 1. row ***************************
Event: e_daily
sql_mode:
time_zone: SYSTEM
Create Event: CREATE EVENT `e_daily`
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 6 HOUR ON COMPLETION NOT PRESERVE
ENABLE
COMMENT 'Saves total number of sessions then
clears the table each day'
DO BEGIN
INSERT INTO als (time, total)
SELECT CURRENT_TIMESTAMP, COUNT(*)
FROM site_activity.sessions;
DELETE FROM site_activity.sessions;
END
character_set_client: latin1
网页设计与制作教程西北工业大学collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
SHOW ENGINE – 显⽰存储引擎的详细信息
mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
2015-11-2219:05:05 7f6b6c780700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 61 seconds ..................
SHOW WARNINGS – 显⽰最后⼀个执⾏语句所产⽣的警告信息
mysql> SHOW WARNINGS\G
mysql> SELECT @@warning_count;
+-----------------+
| @@warning_count |
+-----------------+
| 1 |
+-----------------+
1 row in set (0.01 sec)
SHOW ERRORS – 显⽰最后⼀个执⾏语句所产⽣的错误信息mysql> SHOW ERRORS\G
SHOW EVENTS – 显⽰事件信息
mysql> SHOW EVENTS;
Empty set (0.02 sec)
SHOW GRANTS – 显⽰⼀个⽤户所拥有的权限
mysql> SHOW GRANTS;
mysql> SHOW GRANTS FOR CURRENT_USER();
mysql> SHOW GRANTS FOR 'root'@'localhost';
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |c语言入门经典图片
+---------------------------------------------------------------------+
SHOW PROCESSLIST – 显⽰系统中正在运⾏的所有进程，普通⽤户只能查看⾃⼰的进⾏信息
mysql> SHOW PROCESSLIST;
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+-------+------------------+
| 6 | root | localhost | blog | Query | 0 | init | SHOW PROCESSLIST |
+----+------+-----------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)
SHOW PRIVILEGES – 显⽰MySQL所⽀持的所有权限，及权限可操作的对象
mysql> SHOW PRIVILEGES;
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege | Context | Comment |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter | Tables | To alter the table |
| Alter routine | Functions,Procedures | To alter or drop stored functions/procedures | SHOW PLUGINS – 显⽰MySQL插件信息
mysql> SHOW PLUGINS;
+----------------------------+----------+--------------------+---------+---------+
| Name | Status | Type | Library | License |
+----------------------------+----------+--------------------+---------+---------+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| mysql_old_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| CSV | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL |
| MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL |
| InnoDB | ACTIVE | STORAGE ENGINE | NULL | GPL | ...................
SHOW MASTER STATUS – 显⽰Master当前正在使⽤的⼆进制信息
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
mysql查看所有存储过程| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000010 | 7040 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
SHOW TABLE STATUS – 显⽰表属性信息
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论