MySQL study Note 3 index, stored procedure _ MySQL

MySQL study Note 3 index, stored procedure bitsCN.com

MySQL index classification: two storage types of indexes: B-tree index and HASH index. B-tree is the default index method. MySQL indexes include general indexes, Unique Indexes, full-text indexes, single-column indexes, multi-column indexes, and spatial indexes.

Note: only MyISAM data tables support full-text FULLTEXT indexing, while other data tables do not support full-text indexing. When you create a full-text index, the ERROR "ERROR 1283 (HY000): Column 'number' cannot be part of FULLTEXT index" is returned, this means that the current data table operated by the user does not support full-text indexing, that is, a data table not of the MyISAM type.

Only MyISAM tables support spatial indexes. In addition, the index field must have non-null constraints. Create an index when creating a data table

The syntax structure is as follows:

Create table table_name (

Attribute name data type [constraints],

Attribute name data type [constraints],

^ ......

Attribute name data type

[UNIQUE | FULLTEXT | SPATIAL] INDEX} KEY

[Alias] (attribute name 1 [(length)] [ASC | DESC]) create multi-column indexes

The condition for triggering a multi-column index is that you must use the first field of the index. if you do not use the first field, the index does not play any role. to optimize the query speed, you can apply this type of index. The basic command structure for creating an index in a created data table is as follows:
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

ON table_name (attribute [(length)] [ASC | DESC]); modify the data table structure and add the basic index structure as follows:

Alter table table_name ADD [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name (attribute name [(length)] [ASC | DESC]); note:

In terms of functions, modifying the data table structure to add indexes is basically the same as creating an index in an existing data table. Both are used to add or create a new index in an existing data table. The basic commands for deleting an index are as follows:

Drop index index_name ON table_name; MySQL stored procedure creation stored procedure and stored function creation stored procedure

Basic format: create proceduer sp_name ([proc_parameter [,...]) [characteristic...] routine_body

Stored Procedure call method: call the name of the stored procedure. note: The default statement terminator in MySQL is a semicolon. the SQL statement in the stored procedure must end with a semicolon. to avoid conflicts, first, use "DELIMITER //" to set the MySQL Terminator //. Finally, use "DELIMITER;" to restore the ending point. This is the same as the trigger creation. Create a storage function

Basic format: create function sp_name ([func_parameter [,...])

RETURNS types

[Characteristic...] application of the routine_body variable

MySQL Stored Procedure parameters include local parameters and session parameters, which can also be called local variables and session variables. The local variable is only used to define the begin… of the local variable ...... Valid in the end range, and session variables are valid throughout the stored procedure.

Partial variables are declared with the keyword declare, followed by the variable name and variable type. for example, declare a int

You can also use the keyword default to specify the default value for the variable. for example, the session variable in declare a int default 10MySQL can be used without declaration. the session variable is valid throughout the process, the session variable name starts with the character. Use the DECLARE keyword to define the variable for variable assignment: DECLARE var_name [,...] type [default value] use the SET keyword to assign a value to the variable: SET var_name = expr [, var_name = expr]... SELETC col_name [,...] INTO var_name [,...] FROM table_name where condition

Example: select tel into customer_tel from studentinfo where name = 'leonsk'; note: The preceding value assignment statement must exist in the stored procedure and be placed in BEGIN ...... Between ends. If the variable is out of this range, it cannot be used or assigned a value. Use of the cursor

Declare cursor

The cursor must be declared before the handler and after the variables and conditions.

Syntax: DECLARE cursor_name cursor for select_statement

The select clause cannot contain the INTO clause, and the cursor can only be used in stored procedures or stored functions. Open cursor

Syntax: OPEN info_of_student use cursor

Use the FETCH... INTO statement to read data. The syntax is as follows:

FETCH cursor_name INTO var_name [, var_name]... close the cursor

Syntax: CLOSE curso_name

For a closed cursor, FETCH cannot be used after it is closed. The cursor must be closed after use. View stored procedures and show status statements

SHOW {PROCEDUER | FUNCTION} STATUS [LIKE 'pattern'] show create statement

Show create {PROCEDUER | FUNCTION} sp_name; the show status statement can only view the database objects operated by stored procedures or functions, for example, the name, type, definer, modification time, and other information of a stored procedure or function cannot be queried. To view the detailed definition, use the show create statement. Modify the stored procedure and function syntax as follows:

ALTER {PROCEDUER | FUNCTION} sp_name [characteristic...]

Characteristic:

{Contains SQL | no SQL | reads SQL data | modifies SQL DATA}

| SQL SECURITY {DEFINER | INVOKER}

| COMMENT 'string'

BitsCN.com