Simple MySQL knowledge and simple MySQL knowledge

Simple MySQL knowledge and simple MySQL knowledge

I recently read a book on MySQL, which only describes some basic knowledge, but also covers all MySQL knowledge points. The rest of the more advanced ones are only extended or optimized on the basis. After reading this book, I am also a little confused, so I know some friends to help solve it.

1. MySQL engine Classification

InnoDB, MyISAM, and MEMORY engines.

InnoDB is a reliable transaction processing engine, but it does not support full text search.

MEMORY functions are similar to MyISAM, but because the data is stored in the MEMORY, the speed is very fast.

MyISAM is a highly personalized engine that supports full text search, but does not support transaction processing.

2. Let's talk about the full text search of MyISAM.

Unlike the like syntax used in InnoDB, full text search of MyISAM does not search for a certain column, but all text can be displayed if any matching Column exists. The basic format is as follows:

Match (matching column name) Against (search expression)

(1) Basic Syntax:

SELECT columnName

FROM TABLENAME

Where match (QUERY column) Against ('anvils' with query expansion) (this is the QUERY extension)

Because the entire text is searched, as long as one word meets the requirements, it will be displayed, so the general result is not unique. They will be arranged in a certain order, mainly based on the priority of the query and the location between the keywords you search. The closer the location, the higher the priority.

(2) Boolean text search

Example:

Select note_text

From product

Where Match (note_text) Against ('heive' in boolean mode)

Heavy indicates the search keyword. In boolean mode is a BOOLEAN text flag.

Against ('heavy-rope * 'in boolean mode) indicates to remove rows containing the rope from the search results.

Against ('+ rabbit + bait' in boolean mode) displays rows containing rabbit and bait

Against ('rabbit + bait' in boolean mode) displays any row containing rabbit and bait

Against ('rabbit bait' in boolean mode) displays the row of the rabbit bait phrase.

Here I raised a question: why does InnoDB not support full text search? This involves internal engine problems. I hope you can reply to me.

3. Regular Expression REGXEP

I have studied SQL SERVER databases before reading this book. However, no regular expressions were learned at the time. I learned about it only a few times in my life.

Regexp is a regular expression. Therefore, learning English is essential for a programmer. Sometimes we can know what this is through a short name, and I will have my own experience.

Here I want to talk about regular expressions matching special characters. When a regular expression matches special characters, the escape character "\" must be added as the leading character.

? Is to match any character before the current 0 or 1 appear.

4. View

The view is a virtual table. It is mainly used for data retrieval. It encapsulates select statements to protect basic data of some users. However, I still do not know enough about the use of views.

5. CURSOR)

Procedure:

Define -- declare -- use -- disable

Create procedure c ()

BEGIN

Declare cursor name CURSOR

FOR

SELECT _ FROM _;

END

The above is the process of creating a cursor

Open cursor and close cursor are the operations to OPEN and close cursor.

What I have learned recently is this. I hope you can answer the questions marked in red. These basic knowledge is very important for us who want to know MySQL, and there are some things we don't know. We will continue to learn more. Waiting for my subsequent database knowledge, let's grow together.