MySQL Show command collection

A.ShowTables orShowTables from database_name; --Displays the names of all tables in the current database
B.ShowDatabases --DisplayMySQLName of all databases in
C.ShowColumns from table_name to database_name; OrShowColumns from Database_name.table_name; --Show the column names in the table
D. Show grants for USER_NAME; --Displays a user's permissions, displaying results similar to the grant command
E.Showindex from TABLE_NAME; --Display the index of the table
F.ShowStatus --Displays information about some system-specific resources, such as the number of threads running
G.ShowVariables --Displays the name and value of the system variable
H.ShowProcesslist; --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.ShowTable 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.Showprivileges; --Displays the different permissions supported by the server
K.ShowCREATE DATABASE database_name; --Show whether the CREATE DATABASE statement can create the specified databases
L.ShowCREATE TABLE table_name; --Show whether the CREATE DATABASE statement can create the specified databases
M.ShowEngies; --Displays the storage engine and the default engine that are available after installation.
N.ShowInnoDB status; --Displays the status of the InnoDB storage engine
O.ShowLogs --Displays logs of the BDB storage engine
P.ShowWarnings --Displays errors, warnings, and notifications resulting from the last statement executed
Q.ShowErrors --Displays only the errors generated by the last execution statement
R.Show[Storage] engines; --Displays the available storage engine and the default engine after installation
S.ShowProcedure status-Displays basic information about all stored procedures in the database, including the owning database, stored
Process name, creation time, etc.
T.ShowCREATE PROCEDURE Sp_name--show details of 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:

CREATE PROCEDURE procedure_name () SELECT * FROM table_name;

2, call:

Call procedure_name;

3, view:

There are three ways to view the contents of a procedure:
1, show create PROCEDURE procedure_name;
2, show procedure status like ' procedure_name '
3, select * from Mysql.proc where name= ' procedure_name '

4, Delete:

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

The declaration 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 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 assigning 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

MySQL Show command collection