Storage of front-end learning databases: storage of learning Databases

Storage of front-end learning databases: storage of learning Databases
* Directory [1] stored procedure [2] before the storage engine

We often insert, delete, update, and search data tables, that is, CURD. In fact, when we enter the command, the mysql engine will follow the operation

If we omit the analysis and compilation steps, the execution efficiency will be greatly improved. This requires the storage described below to implement

 

Stored Procedure

A stored procedure is a set of pre-compiled SQL statements and control statements. It is stored in a name and processed as a unit. Stored procedures are stored in databases and can be called and executed by applications, allowing users to clearly define variables and control processes. Stored Procedures can receive parameters (input parameters and output parameters) and can return multiple values. Therefore, the execution efficiency of stored procedures is higher than that of a single SQL command.

Advantages

1. Enhanced Functions and flexibility of SQL statements

2. Fast execution speed. When the client calls the stored procedure for the first time, the MySQL engine performs Syntactic Analysis, compilation, and other operations on the stored procedure, and then stores the compilation results in the memory. Therefore, the efficiency is the same for the first time as before, however, the compilation results in the memory will be called directly in the future to improve the efficiency.

3. reduce network traffic. A single SQL statement has a large number of characters. by calling a stored procedure, you only need to pass the name and related parameters of the stored procedure. The data submitted to the server is relatively small.

Syntax structure

CREATE[DEFINER = { user | CURRENT_USER }]  PROCEDURE sp_name([proc_parameter[,...]])  [characteristic ...] routine_body

proc_parameter:[ IN | OUT | INOUT ] param_name type 

IN indicates that the value of this parameter must be specified when the stored procedure is called and cannot be returned.

OUT indicates that the value of this parameter can be changed by the stored procedure and can be returned.

INOUT indicates that this parameter is specified during the call and can be changed and returned.

Characteristic (feature)

COMMENT 'string'|{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY {DEFINER | INVOKER}

COMMENT: COMMENT

Contains SQL: a statement that contains SQL statements but does not contain read or write data.

No SQL: does not contain SQL statements

Reads SQL DATA: statement containing read DATA

Modifies SQL DATA: statement containing DATA writing

SQL SECURITY {DEFINER | INVOKER}: specifies who has the permission to execute

Process body

1. The process body consists of legal SQL statements;

2. The process body can be an "arbitrary" SQL statement (here, any mainly refers to adding, deleting, modifying, and querying records, and connecting multiple tables );

3. If the process body is in a composite structure, use the BEGIN... END statement;

4. The composite structure can contain declarations, loops, and control structures.

Create a stored procedure without Parameters

CREATE PROCEDURE sp1() SELECT VERSION();

Call Stored Procedure

Method 1: CALL sp_name ([parameter [,...]) if the Stored Procedure contains parameters, parentheses must exist.

Method 2: CALL sp_name [()] if the stored procedure does not contain parameters, parentheses are optional.

Modify Stored Procedure

ALTER PROCEDURE sp_name [characteristic ...]COMMENT 'string'|{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }| SQL SECURITY { DEFINER | INVOKER }

You can only modify the annotations and types of the current content in stored procedures, but cannot modify the process bodies. To modify the process body, you must first Delete the stored procedure and then recreate it.

Delete stored procedure

DROP PROCEDURE [IF EXISTS] sp_name

Create a stored procedure with the IN Type

DELIMITER //CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;END//DELIMITER;

The following describes how to call a stored procedure.

Create a stored procedure with IN and OUT parameters

DELIMITER //CREATE PROCEDURE removeAndReturnUsersNums(IN p_id INT UNSIGNED,OUT userNums INT UNSIGNED)BEGINDELETE FROM users WHERE id = p_id;SELECT count(id) FROM users INTO userNums;END//DELIMITER ;

Mysql Variable Classification

1. User variable: Start with "@", in the form of "@ variable name"

The user variable is bound to the mysql client. The set variable takes effect only for the client currently in use.

SET @i = 7;

2. GLOBAL variables: The global variables are defined in the following two forms: set GLOBAL variable name or set @ global. variable name.

Valid for all clients. You can set global variables only when you have super permissions.

3. session variable: valid only for connected clients

4. Local variable: The scope of the variable is between the in and end statement blocks. Variable set in the statement Block

The declare statement is used to define local variables. The set statement is used to set different types of variables, including session variables and global variables.

The following describes how to call a stored procedure.

CALL removeAndReturnUsersNums(1,@nums);

Create a stored procedure with multiple OUT parameters

DELIMITER //CREATE PROCEDURE removeUserByAgeAndReturnInfos(IN p_age SMALLINT UNSIGNED,OUT deleteUsers SMALLINT UNSIGNED, OUT userCounts SAMLLINT UNSIGNED)BEGINDELETE FROM users WHERE age = p_age;SELECT ROW_COUNT() INTO deleteUsers;SELECT COUNT(id) FROM users INTO userCounts;END//DELIMITER ;

[Note] the ROW_COUNT () function is used to obtain the total number of affected records inserted, deleted, and updated.

The following describes how to call a stored procedure.

CALL removeUserByAgeAndReturnInfos(20,@a,@b);

[Note] @ a indicates the number of deleted records, and @ B indicates the number of remaining records.

SELECT @a,@b;

Differences between stored procedures and user-defined functions

1. The functions implemented by stored procedures must be more complex, while functions are more targeted.

2. stored procedures can return multiple values. A function can return only one value.

3. stored procedures are generally executed independently. functions are mainly used as components of other SQL statements.

 

Storage Engine

MySQL can store data in files (memory) using different technologies, which are called storage engines. Each storage engine uses different storage mechanisms, indexing techniques, and locking levels to ultimately provide a wide range of different functions.

In relational databases, data is stored as tables. Therefore, the storage engine can also be called a table type. Therefore, in fact, the storage engine is a technology that stores and queries data.

MySQL supports the following storage engines: MyISAM, InnoDB, Memory, CSV, and Archive.

Concurrency Control　

Concurrency control ensures data consistency and integrity when multiple connections modify records.

For example, if two users log on to and operate the database at the same time, one of the users deletes a record, and the other user reads the record, this requires concurrency control; otherwise, an error is reported or invalid information is returned.

When processing concurrent 'read' or 'write' operations, MySQL uses the lock system to implement concurrency control, including the shared lock and exclusive lock.

-Shared lock (read lock): multiple users can read the same resource within the same period of time, and the data will not change during the read process.

-Exclusive lock (write lock): at any time, only one user can write data to the resource. When writing the lock, other read or write locks will be blocked.

Lock particles (also known as lock strength) refer to the unit of lock timing. You only need to precisely lock the modified data, instead of locking all resources.

Locking increases system overhead. Therefore, you must use the locking policy to balance the lock overhead with system security. Mysql locks include table locks and row locks.

-Table lock is a lock policy with the minimum overhead.

-Row lock is a lock policy with the largest overhead.

Transaction Processing

Transactions are an important feature that distinguishes databases from file systems. transactions are mainly used to ensure database integrity.

Transaction Features include: Atomicity, Consistency, Isolation, and Durability, abbreviated as ACID

Index

An index is a structure that sorts values of one or more columns in a data table. You can use an index to quickly access specific information of a data table. Indexing is a way to quickly locate records, similar to the directory of a book.

Indexes include common indexes, unique indexes, full-text indexes, B-tree indexes, and hash indexes.

Features of various storage engines

In addition to the above storage engines, there are also the following types of less common Engines

The CSV storage engine does not support indexing. Values Separated by commas (,) (Comma-Separated Values, CSV, and sometimes called character-Separated Values, because the delimiter can also be a Comma ), its files store table data (numbers and text) in plain text format ). Plain text means that the file is a character sequence and does not contain data that must be interpreted as a binary number. A csv file consists of any number of records separated by a line break.

BlackHole is also called the Black Hole engine. The written data disappears and is generally used for data replication relay.

The MyISAM engine is suitable for situations where transactions are not processed much.

Modify storage engine

1. By modifying the MySQL configuration file

default-storage-engine = engine

2. Run the create data table command.

CREATE TABLE table_name(  ...) ENGINE = engine;

3. Run the modify data table command.

ALTER TABLE table_name ENGINE [=] engine_name;