Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief talk about mysql index classification, transaction characteristics and view views. You can look up the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here, so let's go straight to the topic. I hope that this article on mysql index classification, transaction characteristics and view views can bring you some practical help.

The establishment of MySQL index is very important for the efficient operation of MySQL. Index can greatly improve the retrieval speed of MySQL. Database index is like a comprehensive catalog of books. Indexes are divided into five types of general indexes, which is the most basic type of index, and it has no restrictions such as uniqueness. Unique index, which is basically the same as the previous "normal index", but with one difference: all values of the index column can only appear once, that is, they must be unique. Primary key index, a primary key is a unique index that requires each value in the primary key to be unique and is not allowed to be null. Full-text index, index type is FULLTEXT, full-text index can be created on columns of type CHAR, VARCHAR, or TEXT. Single-column indexes and multi-column indexes, indexes can be created on a single column or on multiple columns. A multi-column index can distinguish between rows in which one column may have the same value. Create index method 1. To create a general index, the command format is: CREATE INDEX ON tablename (list of columns); 2. To create a unique index, the command format is: CREATE UNIQUE INDEX ON tablename (list of columns); 3. There are two ways to create a primary key index, one is to create the primary key while creating the table, the primary key index is created automatically, and the command format is as follows: CREATE TABLE tablename ([..], PRIMARY KEY (column list)); the other is that the table has been created, no primary key is specified, and then the table is modified to add the primary key, and the primary key index is created automatically, and the command format is as follows: ALTER TABLE tablename ADD PRIMARY KEY (list of columns); 4. Create a full-text index that specifies or modifies a table. The command format is as follows: CREATE TABLE table name (column name TEXT,FULLTEXT (column name)); 5. To create a multi-column index, the command format is as follows: CREATE INDEX multi-column index name ON column name _ column name; 6. Query index: SHOW INDEX FROM table name\ G

Key_name corresponds to the index name. The Non_unique corresponding value is 1, which means it is not a unique index, and the corresponding value is 0, which means it is a unique index.

7. Delete index: DROP INDEX index name from table name; transaction: a set of operations are performed together or not, and the results are consistent. The four characteristics of a transaction: atomicity: indivisibility, as a whole. Consistency: the results are consistent. Isolation: all concurrent transactions that modify data are isolated from each other, indicating that transactions are independent and should not depend on or affect other transactions in any way. Persistence: once the execution is successful, it is irreversible and the data changes permanently. Using commands to control transactions in Mysql requires three commands: begin: indicates the start of a transaction, followed by the execution of multiple database operation statements. Commit: means to commit a transaction, corresponding to the previous begin operation, and the database operation statements between them are completed together. Rollback: means to roll back a transaction. Between begin and commit, if an error occurs in a database operation statement, rollback rollback is performed, and the database returns to the state before begin. View: a virtual table in a database in which data in one or more tables provide access to users with different permissions. 1. Create a view of the query results: CREATE VIEW view name AS SELECT * FROM table name condition (where score > 80); 2. View view: SELECT * FROM view name; 3. Delete a view: DROP VIEW IF EXISTS view name

The IF EXISTS parameter determines whether the view exists, executes if it exists, and does not execute if it does not exist.

Mysql index classification, transaction characteristics and view will first tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.