MySQL Show command collection

Tag:databases   status   process    Database    tables    

A. show tables or show tables from database_name; --  displays the names of all tables in the current database b.  show databases; --  displays the names of all databases in MySQL c. show columns from table_name  from database_name;  or show columns from database_name.table_name; --  Display the column names in the table d. show grants for user_name; --  displays a user's permissions, and displays the results similar to the grant  command e.  show index from table_name; --  the index of the display table f. show status; --  Displays information about some system-specific resources, such as the number of running threads g. show variables; --  displays the name and value of the system variable h. 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 table j. show privileges; --  displays the different permissions supported by the server K. show create database  database_name; --  displays whether the create database  statement can create the specified database l. show create table table_name; --  displays whether the create database  statement can create the specified database m.  show engies; --  Displays the storage engine and the default engine that are available after installation. n. show innodb status; --  Displays the status of the InnoDB storage engine o. show logs; --  Displays logs for the BDB storage engine  p. show warnings; --  displays errors, warnings, and notifications resulting from the last statement executed q. show errors;  --  displays only errors resulting from the last execution statement  r. show [storage] engines; --displays the available storage engines and the default engine s after installation.  show procedure status --displays basic information about all stored procedures in the database, including the owning database, stored                                                      name, creation time etc T. show create procedure sp_name  --Show details for a stored procedure

Two things were studied in one afternoon: triggers and processes.

A trigger is an action that is performed (before, after) when an operation (INSERT, UPDATE, delete) is performed. This feature can be fully implemented in C and simpler. The use of triggers in MySQL also involves variables and the like, 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 set-mandate executive-Dream CREATE PROCEDURE procedure_name () SELECT * FROM table_name;

2, call:

MySQL Show command set-mandate executive-Dream call procedure_name;

3, view:

MySQL Show command Collection-mandate executive-dream there are three ways to view the contents of the process:

MySQL Show command set-mandate executive-Dream 1,show CREATE PROCEDURE procedure_name;

MySQL Show command set-mandate executive-Dream 2,show procedure status like ' procedure_name '

MySQL Show command set-mandate executive-Dream 3,select * from Mysql.proc where name= ' procedure_name '

4, Delete:

MySQL Show command set-mandate executive-Dream drop procedure procedure_name;

Some examples of stored procedures and functions

Delimiter |

Define procedure

CREATE PROCEDURE P2_test1 (out para1 int)

Begin

Select SUM (val) into Para1 from Test1;

End

|

Call procedure

Call P2_test1 (@sum) |

Select @sum |

Alter procedure did not study it.

Defining functions

Create function F_test1 (name char) returns CHAR (20)//Parameter no in,out, all in

Begin

Return concat (' Hello ', name, '! ');

End

|

Calling functions

Select F_test1 (' Hongjian ') |

Usage of three different show

Show procedure Status

Show function status

Show triggers//note, no status

Grammar:

CREATE PROCEDURE sp_name ([in | Out | INOUT] param type,...)

Begin

Routine_body

End

CREATE FUNCTION Sp_name (para,...) RETURNS type

Begin

Routing_body

End

MYSQL5 Supported stored Procedure statements

Declaration description

Create PROCEDURE a stored procedure that holds a table stored in the 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 a if-then-else-end if declaration.

Case ... When the structure of a case declaration

Loop a simple looping structure; You can use the Le***e statement to exit.

The le***e is used to exit if,case,loop,repeat and while statements.

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

Build a stored procedure

Create PROCEDURE P1 (AppID varchar (50))

Begin

Select APPNAME from APPINFO where id=appid;

End

Create or Replace procedure P1 () is not supported in MYSQ1

To delete a stored procedure

drop procedure P1;

To view all stored procedures that already exist in the current database, enter: Show procedure status;

To view a stored procedure, enter: Show CREATE PROCEDURE proc_name;

Using cursors

CREATE PROCEDURE P3 ()

Begin

You can put variables of the same type together to declare

DECLARE Sum,t,no_more_record Integer;

DECLARE cursor_total cursor FOR select total from Pnumber;

DECLARE CONTINUE HANDLER for not FOUND SET no_more_record=1;//must be declared here first No_more_record

You must declare all the variables before you can assign a value to the variable

Set sum=0,t=0,no_more_record=0;//can assign values to multiple variables on the same line

Open cursor_total;

Repeat

Fetch cursor_total into t;//mysql when you remove a record, if it is empty, the No_more_record is assigned 1, but its record is the last record

if (no_more_record!=1) then

Set sum=sum+t;

End If;

Until No_more_record

End repeat;//There must be a semicolon here

Close cursor_total;

Set no_more_record=0;

End

This article is from the "Give Me Three moles" blog, please be sure to keep this source http://wushuangjay.blog.51cto.com/3125450/1576176

MySQL Show command collection