* 顯示所有的資料庫名稱
mysql root@localhost:sakila> show databases; ++| Database |++| information_schema || chain_dev || jpa || mysql || performance_schema || sakila || sys || test |++* 顯示資料庫創建語句
mysql root@localhost:sakila> show create database sakila; +++| Database | Create Database |+++| sakila | CREATE DATABASE `sakila` /*!40100 DEFAULT CHARACTER SET utf8 */ |+++1 row in set* 顯示當前資料庫中所有表的名稱
mysql root@localhost:sakila> show tables; +---+| Tables_in_sakila |+---+| actor || actor_info || address || category || city || country || customer || customer_list || film || film_actor || film_category || film_list || film_text || inventory || language || nicer_but_slower_film_list || payment || rental || sales_by_film_category || sales_by_store || staff || staff_list || store |+---+* 顯示某個資料庫的所有表名稱(未切換到某個具體的數據時使用)
mysql> show tables from sakila;+---+| Tables_in_sakila |+---+| actor || actor_info || address || category || city || country || customer || customer_list || film || film_actor || film_category || film_list || film_text || inventory || language || nicer_but_slower_film_list || payment || rental || sales_by_film_category || sales_by_store || staff || staff_list || store |+---+* 資料庫各個表(含視圖)的狀態
mysql root@localhost:sakila> show table status from sakila \G; ***************************[ 1. row ]***************************Name | actorEngine | InnoDBVersion | 10Row_format | DynamicRows | 200Avg_row_length | 81Data_length | 16384Max_data_length | 0Index_length | 16384Data_free | 0Auto_increment | 201Create_time | 2020-11-26 16:08:11Update_time | 2020-11-26 16:12:22Check_time | <null>Collation | utf8mb4_general_ciChecksum | <null>Create_options | Comment |
後面還有很多表...如果只是想看某個表的狀態，則可以添加過濾條件
mysql root@localhost:sakila> show table status from sakila where name = 'actor' \G; ***************************[ 1. row ]***************************Name | actorEngine | InnoDBVersion | 10Row_format | DynamicRows | 200Avg_row_length | 81Data_length | 16384Max_data_length | 0Index_length | 16384Data_free | 0Auto_increment | 201Create_time | 2020-11-26 16:08:11Update_time | 2020-11-26 16:12:22Check_time | <null>Collation | utf8mb4_general_ciChecksum | <null>Create_options | Comment |
1 row in setTime: 0.004s查看視圖類型
mysql root@localhost:sakila> show table status from sakila where comment = 'view' \G; ***************************[ 1. row ]***************************Name | actor_infoEngine | <null>Version | <null>Row_format | <null>Rows | <null>Avg_row_length | <null>Data_length | <null>Max_data_length | <null>Index_length | <null>Data_free | <null>Auto_increment | <null>Create_time | <null>Update_time | <null>Check_time | <null>Collation | <null>Checksum | <null>Create_options | <null>Comment | VIEW* 顯示函數的基本信息
mysql root@localhost:sakila> show FUNCTION STATUS \G; ***************************[ 1. row ]***************************Db | sakilaName | get_customer_balanceType | FUNCTIONDefiner | root@localhostModified | 2020-11-26 16:08:12Created | 2020-11-26 16:08:12Security_type | DEFINERComment | character_set_client | utf8mb4collation_connection | utf8mb4_general_ciDatabase Collation | utf8_general_ci還有很多信息...* 過濾某個函數的信息
mysql root@localhost:sakila> show FUNCTION STATUS where name like '%inv%'; +---+---++-+-+-++----+--+--++| Db | Name | Type | Definer | Modified | Created | Security_type | Comment | character_set_client | collation_connection | Database Collation |+---+---++-+-+-++----+--+--++| sakila | inventory_held_by_customer | FUNCTION | root@localhost | 2020-11-26 16:08:12 | 2020-11-26 16:08:12 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci || sakila | inventory_in_stock | FUNCTION | root@localhost | 2020-11-26 16:08:12 | 2020-11-26 16:08:12 | DEFINER | | utf8mb4 | utf8mb4_general_ci | utf8_general_ci |+---+---++-+-+-++----+--+--++
2 rows in setTime: 0.016s* 顯示某個表的信息
mysql root@localhost:sakila> desc actor; +---+--+-++----+----+| Field | Type | Null | Key | Default | Extra |+---+--+-++----+----+| actor_id | smallint(5) unsigned | NO | PRI | <null> | auto_increment || first_name | varchar(45) | NO | | <null> | || last_name | varchar(45) | NO | MUL | <null> | || last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+---+--+-++----+----+
4 rows in setTime: 0.008smysql root@localhost:sakila> describe actor; +---+--+-++----+----+| Field | Type | Null | Key | Default | Extra |+---+--+-++----+----+| actor_id | smallint(5) unsigned | NO | PRI | <null> | auto_increment || first_name | varchar(45) | NO | | <null> | || last_name | varchar(45) | NO | MUL | <null> | || last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+---+--+-++----+----+
4 rows in setTime: 0.010s* 顯示某個表的創建語句
mysql root@localhost:sakila> show create table actor \G; ***************************[ 1. row ]***************************Table | actorCreate Table | CREATE TABLE `actor` ( `actor_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `first_name` varchar(45) NOT NULL, `last_name` varchar(45) NOT NULL, `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`actor_id`), KEY `idx_actor_last_name` (`last_name`)) ENGINE=InnoDB AUTO_INCREMENT=201 DEFAULT CHARSET=utf8mb4
1 row in setTime: 0.001s類似的還有查看視圖創建、查看觸發器創建、查看存儲過程創建、用戶創建、函數創建等
mysql root@localhost:sakila> show create view actor_info \G; ***************************[ 1. row ]***************************View | actor_infoCreate View | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY INVOKER VIEW `actor_info` AS select `a`.`actor_id` AS `actor_id`,`a`.`first_name` AS `first_name`,`a`.`last_name` AS `last_name`,group_concat(distinct concat(`c`.`name`,': ',(select group_concat(`f`.`title` order by `f`.`title` ASC separator ', ') from ((`film` `f` join `film_category` `fc` on((`f`.`film_id` = `fc`.`film_id`))) join `film_actor` `fa` on((`f`.`film_id` = `fa`.`film_id`))) where ((`fc`.`category_id` = `c`.`category_id`) and (`fa`.`actor_id` = `a`.`actor_id`)))) order by `c`.`name` ASC separator '; ') AS `film_info` from (((`actor` `a` left join `film_actor` `fa` on((`a`.`actor_id` = `fa`.`actor_id`))) left join `film_category` `fc` on((`fa`.`film_id` = `fc`.`film_id`))) left join `category` `c` on((`fc`.`category_id` = `c`.`category_id`))) group by `a`.`actor_id`,`a`.`first_name`,`a`.`last_name`character_set_client | utf8mb4collation_connection | utf8mb4_general_ci
1 row in setTime: 0.002s
mysql root@localhost:sakila> show create trigger ins_film \G; ***************************[ 1. row ]***************************Trigger | ins_filmsql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONSQL Original Statement | CREATE DEFINER=`root`@`localhost` TRIGGER `ins_film` AFTER INSERT ON `film` FOR EACH ROW BEGIN INSERT INTO film_text (film_id, title, description) VALUES (new.film_id, new.title, new.description); ENDcharacter_set_client | utf8mb4collation_connection | utf8mb4_general_ciDatabase Collation | utf8_general_ciCreated | 2020-11-26 16:08:12.040000
1 row in setTime: 0.004s
mysql root@localhost:sakila> show create procedure rewards_report \G; ***************************[ 1. row ]***************************Procedure | rewards_reportsql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONCreate Procedure | CREATE DEFINER=`root`@`localhost` PROCEDURE `rewards_report`( IN min_monthly_purchases TINYINT UNSIGNED , IN min_dollar_amount_purchased DECIMAL(10,2) , OUT count_rewardees INT) READS SQL DATA COMMENT 'Provides a customizable report on best customers'proc: BEGIN
DECLARE last_month_start DATE; DECLARE last_month_end DATE;
/* Some sanity checks... */ IF min_monthly_purchases = 0 THEN SELECT 'Minimum monthly purchases parameter must be > 0'; LEAVE proc; END IF; IF min_dollar_amount_purchased = 0.00 THEN SELECT 'Minimum monthly dollar amount purchased parameter must be > $0.00'; LEAVE proc; END IF;
/* Determine start and end time periods */ SET last_month_start = DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH); SET last_month_start = STR_TO_DATE(CONCAT(YEAR(last_month_start),'-',MONTH(last_month_start),'-01'),'%Y-%m-%d'); SET last_month_end = LAST_DAY(last_month_start);
/* Create a temporary storage area for Customer IDs. */ CREATE TEMPORARY TABLE tmpCustomer (customer_id SMALLINT UNSIGNED NOT NULL PRIMARY KEY);
/* Find all customers meeting the monthly purchase requirements */ INSERT INTO tmpCustomer (customer_id) SELECT p.customer_id FROM payment AS p WHERE DATE(p.payment_date) BETWEEN last_month_start AND last_month_end GROUP BY customer_id HAVING SUM(p.amount) > min_dollar_amount_purchased AND COUNT(customer_id) > min_monthly_purchases;
/* Populate OUT parameter with count of found customers */ SELECT COUNT(*) FROM tmpCustomer INTO count_rewardees;
/* Output ALL customer information of matching rewardees. Customize output as needed. */ SELECT c.* FROM tmpCustomer AS t INNER JOIN customer AS c ON t.customer_id = c.customer_id;
/* Clean up */ DROP TABLE tmpCustomer;ENDcharacter_set_client | utf8mb4collation_connection | utf8mb4_general_ciDatabase Collation | utf8_general_ci
1 row in setTime: 0.001s
mysql root@localhost:sakila> show create user 'root'@'localhost' \G; ***************************[ 1. row ]***************************CREATE USER for root@localhost | CREATE USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in setTime: 0.001s
mysql root@localhost:sakila> show create function inventory_held_by_customer \G; ***************************[ 1. row ]***************************Function | inventory_held_by_customersql_mode | STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONCreate Function | CREATE DEFINER=`root`@`localhost` FUNCTION `inventory_held_by_customer`(p_inventory_id INT) RETURNS int(11) READS SQL DATABEGIN DECLARE v_customer_id INT; DECLARE EXIT HANDLER FOR NOT FOUND RETURN NULL;
SELECT customer_id INTO v_customer_id FROM rental WHERE return_date IS NULL AND inventory_id = p_inventory_id;
RETURN v_customer_id;ENDcharacter_set_client | utf8mb4collation_connection | utf8mb4_general_ciDatabase Collation | utf8_general_ci
1 row in setTime: 0.002s* 查看某個表的欄位信息
mysql root@localhost:sakila> show columns from actor; +---+--+-++----+----+| Field | Type | Null | Key | Default | Extra |+---+--+-++----+----+| actor_id | smallint(5) unsigned | NO | PRI | <null> | auto_increment || first_name | varchar(45) | NO | | <null> | || last_name | varchar(45) | NO | MUL | <null> | || last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+---+--+-++----+----+
4 rows in setTime: 0.010s沒有切換到具體資料庫的時候需要關聯資料庫
mysql root@localhost:(none)> show columns from actor from sakila; +---+--+-++----+----+| Field | Type | Null | Key | Default | Extra |+---+--+-++----+----+| actor_id | smallint(5) unsigned | NO | PRI | <null> | auto_increment || first_name | varchar(45) | NO | | <null> | || last_name | varchar(45) | NO | MUL | <null> | || last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |+---+--+-++----+----+
4 rows in setTime: 0.015s* 顯示系統中正在運行的所有進程，也就是當前正在執行的查詢
mysql root@localhost:(none)> show processlist; +----+-+--+---+----+--++---+| Id | User | Host | db | Command | Time | State | Info |+----+-+--+---+----+--++---+| 21 | root | localhost:61384 | sys | Sleep | 11486 | | <null> || 22 | root | localhost:61387 | sys | Sleep | 11486 | | <null> || 32 | root | localhost:63007 | <null> | Query | 0 | starting | show processlist |+----+-+--+---+----+--++---+
3 rows in setTime: 0.010s* 顯示一些系統特定資源的信息
mysql root@localhost:(none)> show status \G; ***************************[ 1. row ]***************************Variable_name | Aborted_clientsValue | 20***************************[ 2. row ]***************************Variable_name | Aborted_connectsValue | 5***************************[ 3. row ]***************************Variable_name | Binlog_cache_disk_useValue | 0***************************[ 4. row ]***************************Variable_name | Binlog_cache_useValue | 0***************************[ 5. row ]***************************Variable_name | Binlog_stmt_cache_disk_useValue | 0
還有很多信息....* 顯示系統變量和名稱
mysql root@localhost:(none)> show variables \G; ***************************[ 1. row ]***************************Variable_name | auto_generate_certsValue | ON***************************[ 2. row ]***************************Variable_name | auto_increment_incrementValue | 1***************************[ 3. row ]***************************Variable_name | auto_increment_offsetValue | 1***************************[ 4. row ]***************************Variable_name | autocommitValue | ON***************************[ 5. row ]***************************Variable_name | automatic_sp_privilegesValue | ON還有很多信息...
// 如果只想看某個變量的信息，可以過濾mysql root@localhost:(none)> show variables like '%auto_generate%'; ++--+| Variable_name | Value |++--+| auto_generate_certs | ON || sha256_password_auto_generate_rsa_keys | ON |++--+2 rows in setTime: 0.011s* 顯示伺服器支持的不同權限
mysql root@localhost:(none)> show privileges; ++----++| Privilege | Context | Comment |++----++| Alter | Tables | To alter the table || Alter routine | Functions,Procedures | To alter or drop stored functions/procedures || Create | Databases,Tables,Indexes | To create new databases and tables || Create routine | Databases | To use CREATE FUNCTION/PROCEDURE || Create temporary tables | Databases | To use CREATE TEMPORARY TABLE || Create view | Tables | To create new views || Create user | Server Admin | To create new users || Delete | Tables | To delete existing rows || Drop | Databases,Tables | To drop databases, tables, and views || Event | Server Admin | To create, alter, drop and execute events || Execute | Functions,Procedures | To execute stored routines || File | File access on server | To read and write files on the server || Grant option | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess || Index | Tables | To create or drop indexes || Insert | Tables | To insert data into tables || Lock tables | Databases | To use LOCK TABLES (together with SELECT privilege) || Process | Server Admin | To view the plain text of currently executing queries || Proxy | Server Admin | To make proxy user possible || References | Databases,Tables | To have references on tables || Reload | Server Admin | To reload or refresh tables, logs and privileges || Replication client | Server Admin | To ask where the slave or master servers are || Replication slave | Server Admin | To read binary log events from the master || Select | Tables | To retrieve rows from table || Show databases | Server Admin | To see all databases with SHOW DATABASES || Show view | Tables | To see views with SHOW CREATE VIEW || Shutdown | Server Admin | To shut down the server || Super | Server Admin | To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. || Trigger | Tables | To use triggers || Create tablespace | Server Admin | To create/alter/drop tablespaces || Update | Tables | To update existing rows || Usage | Server Admin | No privileges - allow connect only |++----++31 rows in setTime: 0.010s* 顯示伺服器的引摯
mysql root@localhost:(none)> show engines; ++----+----+----+---+--+| Engine | Support | Comment | Transactions | XA | Savepoints |++----+----+----+---+--+| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES || MRG_MYISAM | YES | Collection of identical MyISAM tables | 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 || MyISAM | YES | MyISAM storage engine | NO | NO | NO || CSV | YES | CSV storage engine | 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 setTime: 0.016s* 顯示最後一個執行的語句所產生的錯誤、警告和通知
mysql root@localhost:sakila> show warnings; +--+-++| Level | Code | Message |+--+-++| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wa' at line 1 |+--+-++
1 row in setTime: 0.011s* 顯示最後一個執行語句所產生的錯誤
mysql root@localhost:sakila> show errors; +--+-++| Level | Code | Message |+--+-++| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'wa' at line 1 |+--+-++
1 row in setTime: 0.009s* 顯示伺服器的編碼列表
mysql root@localhost:sakila> show CHARACTER SET; ++---+-+---+| Charset | Description | Default collation | Maxlen |++---+-+---+| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || dec8 | DEC West European | dec8_swedish_ci | 1 || cp850 | DOS West European | cp850_general_ci | 1 || hp8 | HP West European | hp8_english_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish_ci | 1 || latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 || swe7 | 7bit Swedish | swe7_swedish_ci | 1 || ascii | US ASCII | ascii_general_ci | 1 || ujis | EUC-JP Japanese | ujis_japanese_ci | 3 || sjis | Shift-JIS Japanese | sjis_japanese_ci | 2 || hebrew | ISO 8859-8 Hebrew | hebrew_general_ci | 1 || tis620 | TIS620 Thai | tis620_thai_ci | 1 || euckr | EUC-KR Korean | euckr_korean_ci | 2 || koi8u | KOI8-U Ukrainian | koi8u_general_ci | 1 || gb2312 | GB2312 Simplified Chinese | gb2312_chinese_ci | 2 || greek | ISO 8859-7 Greek | greek_general_ci | 1 || cp1250 | Windows Central European | cp1250_general_ci | 1 || gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 || latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 || armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 || utf8 | UTF-8 Unicode | utf8_general_ci | 3 || ucs2 | UCS-2 Unicode | ucs2_general_ci | 2 || cp866 | DOS Russian | cp866_general_ci | 1 || keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2_general_ci | 1 || macce | Mac Central European | macce_general_ci | 1 || macroman | Mac West European | macroman_general_ci | 1 || cp852 | DOS Central European | cp852_general_ci | 1 || latin7 | ISO 8859-13 Baltic | latin7_general_ci | 1 || utf8mb4 | UTF-8 Unicode | utf8mb4_general_ci | 4 || cp1251 | Windows Cyrillic | cp1251_general_ci | 1 || utf16 | UTF-16 Unicode | utf16_general_ci | 4 || utf16le | UTF-16LE Unicode | utf16le_general_ci | 4 || cp1256 | Windows Arabic | cp1256_general_ci | 1 || cp1257 | Windows Baltic | cp1257_general_ci | 1 || utf32 | UTF-32 Unicode | utf32_general_ci | 4 || binary | Binary pseudo charset | binary | 1 || geostd8 | GEOSTD8 Georgian | geostd8_general_ci | 1 || cp932 | SJIS for Windows Japanese | cp932_japanese_ci | 2 || eucjpms | UJIS for Windows Japanese | eucjpms_japanese_ci | 3 || gb18030 | China National Standard GB18030 | gb18030_chinese_ci | 4 |++---+-+---+
41 rows in setTime: 0.015s