The use of show in MySQL collection

A good memory is better than a random writing .... The following collection of the MySQL show in the use of skills .... If you need a friend, you can have a look.

A. Show tables or show tables from database_name;//displays the names of all tables in the current databaseB. show databases;//displays the names of all databases in MySQLC. Show columns from table_name to database_name; or show columns from Database_name.table_name;//display column names in a tableD. Show grants for[Email protected];//Displays the permissions of a user, displaying results similar to the grant commandE. Show index from TABLE_NAME;//index of the display tableF. Show status [like"'];//displays information about some system-specific resources, such as the number of threads that are runningG. Show [Session/global] variables [like"'];//display the name and value of a system variableh. Show Processlist;//displays all the processes that are running in the system, that is, the query that is currently executing. Most users can view their own processes, but if they have process permissions, they can see all processes, including passwords. I. Show table status;//displays information about each table in the database that is currently being used or specified. Information includes the table type and the last updated time of the tableJ. Show Privileges;//show the different permissions supported by the serverK. Show CREATE DATABASE database_name;//displays whether the CREATE DATABASE statement can create the specified databasesL. show CREATE TABLE table_name;//displays whether the CREATE DATABASE statement can create the specified databasesM. show engies BDB logs;//Displays the storage engine and the default engine that are available after installation. N. show InnoDB status;//Displays the status of the InnoDB storage engineO. Show logs;//displaying logs for the BDB storage engineP. Show warnings;//displays errors, warnings, and notifications resulting from the last statement executedQ. Show errors;//Show only errors resulting from the last execution statementR. show create TABLE tablename;//Show details of the build table and syntax and engineS. Show table status [from database];//Show details of all tables in this database-----------------------------------------------MySQL stored procedure: Show CREATE PROCEDURE Sp_name--Show details of a stored procedure two things were studied in the afternoon: triggers and procedures. A trigger is an action that takes place (before, after) when performing an operation (insert, UPDATE, delete). This feature can be implemented entirely in C and is simpler. The use of triggers in MySQL is also related to variables and so on, rather than using C to achieve simple, so here is not introduced. The procedure (procedure) is equivalent to a child function in the C language:1, create: MySQL show command CREATE PROCEDURE procedure_name ()Select*from table_name;2, called: MySQL show command call procedure_name;3, view: MySQL show command to view the contents of the process there are three ways: MySQL show command1, show create PROCEDURE procedure_name; The show command for MySQL2, show procedure status like'procedure_name'the show command for MySQL3,Select* FROM Mysql.proc where name='procedure_name'4, delete: MySQL show command drop procedure procedure_name; some examples of stored procedures and functions delimiter|define Procedurecreate procedure P2_test1 (out Para1int) BeginSelect sum(Val) into Para1 from Test1;end|call Procedurecall p2_test1 (@sum)|Select@sum |ALTER procedure did not work out to define the function createfunctionF_test1 (nameChar( -)) returnsChar( -)//parameter is not in,out, all inBeginreturn concat ('Hello', Name,' !'); end|calling FunctionsSelectF_test1 ('Hongjian')|three different show usage show procedure Statusshowfunctionstatusshow triggers//Note that there is no statussyntax: CREATE PROCEDURE sp_name ([ in| Out |inout] param type,...) Beginroutine_bodyendcreate FUNCTION Sp_name (para,...) RETURNS TYPEBEGINROUTING_BODYENDMYSQL5 supports a stored procedure statement that describes the create PROCEDURE stored procedure that establishes a table stored in a MySQL database. reate functions Create a user-defined function, especially a stored procedure that returns data. Alter PROCEDURE changes a pre-specified stored procedure established with create PROCEDURE, which does not affect the associated stored procedure or storage functionality. Alter function changes the pre-specified stored procedure established with the CREATE function, which does not affect the associated stored procedure or storage functionality. Drop PROCEDURE deletes one or more stored procedures from the MySQL table. Drop function deletes one or more stored functions from the MySQL table. SHOW Create PROCEDURE Returns the text of a pre-specified stored procedure that was established using create PROCEDURE. This statement is a MySQL extension of the SQL:2003 specification. The SHOW create function returns the text of a pre-specified stored procedure that was established using the CREATE function. This statement is a MySQL extension of the SQL:2003 specification. SHOW PROCEDURE STATUS Returns the attributes of a pre-specified stored procedure, including the name, type, creator, date of establishment, and date of change. This statement is a MySQL extension of the SQL:2003 specification. The SHOW function STATUS returns the attributes of a pre-specified storage function, including the name, type, creator, date of establishment, and date of change. This statement is a MySQL extension of the SQL:2003 specification. Call invokes a pre-specified stored procedure that was established using create procedure. BEGIN ... END contains a set of multiple declarations that are executed. The DECLARE is used to specify local variables, environments, processors, and pointers. SET is used to change the values of local and global server variables. SELECT ... Into is used to store columns that display variables. Open opens a pointer. FETCH uses a specific pointer to get the next column. Close is used for closing and opening pointers. IF an if- Then-Else-endifdeclaration. Case ... When a case is declared structure loop a simple loop structure; You can use the Le***E statement to exit. LE***E is used to exit the if,case,loop,repeat and the while statement. The iterate is used to start the loop again. REPEAT the loop at the end of the test. While the loop is tested at the beginning. RETURNS returns the value of a stored procedure to build a stored procedure create procedure P1 (AppID varchar ( -) BeginSelectAPPNAME from APPINFO where id=Appid;end in MYSQ1 does not support create or replace procedure p1 () Delete stored procedure drop procedure P1; To view all existing stored procedures in the current database, enter: Show procedure status; To view a stored procedure, enter: Show create procedure proc_name;//using CursorsCREATE PROCEDURE p3 () BEGIN//you can put variables of the same type together to declareDeclaresum, T,no_more_record integer;declare cursor_total cursor forSelectTotal from pnumber;declare CONTINUE HANDLER for not FOUND SET No_more_record=1;//You must declare No_more_record here first//You must declare all the variables before you can assign a value to the variableSetsum=0, t=0, no_more_record=0;//You can assign values to multiple variables on the same lineopen Cursor_total;repeatfetch cursor_total into T;//when MySQL takes down a record, if it is empty, it assigns no_more_record to 1, but its record is the last recordif(no_more_record!=1) ThenSetsum=sum+T;endif;untilNo_more_recordend repeat;//there must be a semicolon hereClose Cursor_total;set No_more_record=0; end

The use of show in MySQL collection