/********************* Character Set (character) and proofing Rules (collation) ****************************/
Description: The character set is used to define how MySQL stores strings, and proofing rules are used to define how strings are compared; MySQL supports different sets of characters for various servers, different databases, different tables, different fields; one character set can correspond to multiple proofing rules
Specifies the character set when the object is created, and the proofing rules can view the Help Craete object;
View all available character sets:
Show character set;
Desc information_schema.character_sets
To view the system's proofing rules:
Show collation Like ' * * * *
Desc information_schema.collations;
Server character set, proofing rules (the default character set is used if no character set is specified, and no proofing rules are specified using the default collation rules of the character set)
Query server character set: show variables like ' character_set_server ';
Query server proofing rules: Show variables like ' collation_server ';
Database character set, proofing rules
Query database character set: Show variables like ' character_set_database ';
Query database proofing rules: Show variables like ' collation_database ';
Table character set, proofing rules
Show CREATE TABLE TableName \g;
/********************** Index *******************************/
Creating an index: Create [Unique|fulltext|spatial]index index_name
[Using Index_type]
On table_name (INDEX_COL_NAME)
Index_col_name:
col_name[(length)][asc| DESC]
Delete index: Drop index_name on table_name;
/************************ View *******************************/
Views: A table of virtual existence
To create a view:
create [or Replace] [algorithm={undefined|merge|temptable}]
View view_name[(column_list)]
As Select_statement
[With[cascaded|local]check option]
To modify an illustration:
alter [algorithm={undefined|merge|temptable}]
View view_name[(column_list)]
As Select_statement
[With[cascaded|local]check option]
To delete a view:
Drop View[if exit] view_name[,view_name] ... [Restrict|caseade]
To view the view:
Show tables;
Show table Status LIKE ' * * * *;
Show CREATE View view_name;
Desc information_schema.views;
/************************ stored procedures and functions ****************************/
Stored procedures and functions: a collection of SQL statements, except that the function must have a return value, the argument can only be in type, and the stored procedure does not return a value, the parameter may be in,out,inout
Create:
CREATE PROCEDURE Sp_name ([proc_parameter][....])
[Characteristic ...] Routine_body
Create function Sp_name ([func_parameter][...])
Returns type
[Characteristic ...] Routine_body
Proc_parameter:
[In|out|inout]param_name Type
Func_parameter:
Para_name type
Type
Any valid MySQL data type
Modify:
alter {procedure|function} Sp_name [characteristic ...]
Call:
Call Sp_name ([parameter][....])
Delete:
Drop {procedure|function}[if exit] Sp_name
View:
Show {procedure|function} status [like ' * * * ']
Show create {procedure|function} sp_name
The use of/****************** variables ***********************/
Defined:
Declare var_name[,...] Type [default value];
Assignment value:
Set VAR_NAME=EXP[,VAR_NAME=EPX] ...
Select col_name[,...] into var_name[,...] TABLE_EXPR//Query Assignment
/******************** defining conditions and handling **********************/
Define conditions:
DECLARE condition_name condition for condition_value;
Condition_value:
Sqlstate[value]sqlstate_value|mysql_error_code
Define conditional processing:
Declare Handler_type handler for condition_value[,...] sp_statement;
Hadler_type:
continue| Exit| UNDO
Condition_value:
/***************** use of the cursor *************************/
Declaration cursor:
DECLARE cursor_name cursor for select_name
Open cursor:
Open Cuesor_name
Operation Cursor:
Fetch cuesor_name into Var_value [, Var_name] ...
To close the cursor:
Close cursor_name
/****************** Process Control ***********************/
If Search_condition then statement_list
[ElseIf search_condition then statement_list] ...
[Else Statement_list]
endif
/*****************/
Case Case_value
When When_value then Statement_list
[When Search_condition then Statement_list]
[Else Statement_list]
End case
/*****************/
Case
When Search_condition then Statement_list
[When Search_condition then Statement_list]
[Else Sattement_list]
End case
/*****************/
[Begin_label:]loop
Statement_list
End Loop[end_label]
/*****************/
Exit Loop Leave/iterate
/*****************/
[Begin_label:]repeat
Statement_list
Until Search_condition
End Repeat[end_label]
/*****************/
[Begin_label:]while search_condition do
Statement_list
End While[end_label]
/****************** Event Scheduler *****************/
Description: Triggers an action by a custom time period
Create Event Event_Name
On schedule at Current_timestamp+interval 1 hour
Do
/************** Trigger **********************/
Create:
Create TRIGGER Trigger_name Trigger_event
On table_name for each TRIGGER_STMT
Delete:
Drop Trigger[schema_name]trigger_name
View:
Show triggers \g;
Character set, index, view, stored procedure function, variable, condition, cursor, flow, event, trigger
