Detailed MySQL transactions, indexes, views

Introduction to Indexes

An index is a decentralized storage structure created to speed up the retrieval of rows of data in a table. Indexes are built on tables and are made up of index pages other than data pages, and the rows in each index page contain logical pointers to speed up the retrieval of physical data.

Index function

1. After setting the appropriate index, the database can greatly speed up the query rate by using all kinds of fast location technology.
2, especially when the table is large, or query design to multiple tables, using the index can make the query more than a thousand
3, can reduce the database IO (input and output) cost, and the index can also reduce the sorting cost of the database, pay attention to read the time will consume memory
4. Ensure uniqueness of data table data by creating a unique index
5, can speed up the connection between the table and the table, note that the related fields can be used as an index
6, when using the sorting of grouping, can greatly reduce the grouping and sorting time

Classification of Indexes General index

This is the most basic type of index, and it has no limitations such as uniqueness
Uniqueness Index
This index is basically the same as the previous "normal index", but there is one difference: all values of an indexed column can only occur once, that is, must be unique, allowed to be empty, only one value is empty

Primary key Index

This is a unique index, but it must be specified as "PRIMARY KEY"

Full-Text Indexing

Version MySQL3.23.23 supports full-text indexing and full-text retrieval. In MySQL, the index type of the full-text index is fulltext, and the full-text index can be created on a varchar or text-type column

Single-column and multicolumn indexes

The index can be an index created on a single column, or it can be an index created on multiple columns

Business Introduction

A transaction is a mechanism, a sequence of operations, a set of database operations commands, and all of the commands as a whole to the system to commit or revoke an operation request, that is, the set of database commands are either executed or not executed. Transaction is an inseparable unit of work, when performing concurrent operations on the database system, the transaction is the smallest control unit, suitable for multi-user simultaneous operation of the database system scenarios, such as banks, insurance companies and securities trading system, etc., through the integrity of the transaction to ensure data consistency.

Transactional features

1. Atomic Nature
A transaction is a complete operation, the elements of a thing are not divided, all elements in a transaction must be committed or rolled back as a whole, and if any element in the transaction fails, the entire transaction fails
2. Consistency
When the transaction completes, the data must be in a consistent state: the data stored in the database is in a consistent state before the transaction begins, the data may be in an inconsistent state in the ongoing transaction, and when the transaction completes successfully, the data must return to the known consistency again, noting that the results remain consistent.
3, the isolation of
All concurrent transactions that modify the data are isolated from each other, indicating that the transaction must be independent and that it should not be dependent on or affect other transactions in any way. Transactions that modify data can access the data before another transaction that uses the same data is started, or after the end of another transaction that uses the same data, noting that the transactions are isolated from each other
4. Persistence
The persistence of a transaction regardless of whether the system has failed, the result of the transaction is permanent, once the transaction is committed, the effect of the transaction will be persisted in the database, that is, written to the system disk

Introduction to Views

A view is a virtual table in a database whose contents are defined by a query. As with a real table, a view contains a series of column and row data with names. However, the view does not exist in the database as a stored set of data values. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced. Can be understood as soft links

View action

1, for users to centralize data, simplify the user's data query and processing.
2, shielding the complexity of the database, users do not need to understand the complexity of the database.
3, simplify the management of user rights, only give users permission to use the view.
4, easy to share data, multiple users do not have to define the required data.
5. Data can be re-organized to be linked to other applications.

Command Step one, index 1, normal index

Mysql> CREATE TABLE Student
(id int (3),
Name varchar, #姓名
Score Decimal (5,2), #分数
adrress varchar (+), #地址
Hobbyid Int (3) #关联hobby (hobby sheet)
); #创建一张student表

Mysql> CREATE index Id_index on student (ID); #将student表中id字段创建成普通索引

Mysql> Show index from STUDENT\G; #查看索引

Mysql> DROP index id_index on student; #删除索引

2. Primary KEY index

Mysql> ALTER TABLE Student add primary key (ID); #使用alter命令修改表结构, change the ID to the primary key
Mysql> Show index from STUDENT\G; #查看索引

mysql> ALTER TABLE student drop PRIMARY key; #删除student表主键

Mysql> CREATE TABLE info (ID int not NULL PRIMARY key Auto_increment,name varchar (10)); #通过创建表的时候创建主键列对应的也是主键索引

Mysql> Show index from INFO\G;

mysql> ALTER TABLE info drop PRIMARY key; #删除student表主键

mysql> drop table info; #删除info表

3. Unique index

Mysql> Create unique index name_index on student (name); #将student表中name字段创建成唯一索引
Mysql> Show index from STUDENT\G;

Mysql> DROP index name_index on student; # #删除唯一索引

Mysql> CREATE TABLE info (id int not null,name varchar () unique); #通过创建表的时候创建唯一键对应的也是唯一索引

Mysql> Show index from INFO\G;

mysql> ALTER TABLE info DROP INDEX name; #删除索引

Mysql> Show index from INFO\G; #删除info表

4. Full-Text Indexing

Mysql> ALTER TABLE student add column descript TEXT; #向student表中添加descript字段, type is text
mysql> desc Student;

Mysql> ALTER TABLE Student ADD Fulltext index Desc_index (descript); #将student表中descript字段创建成全文索引
Mysql> Show index from STUDENT\G;

Mysql> DROP index desc_index on student; #删除student表全文索引

Mysql> CREATE TABLE info (descript text,fulltext (descript)); #通过创建表的时候创建全文索引

mysql> ALTER TABLE info DROP INDEX descript; #删除info表全文索引

mysql> drop table info; #删除info表

5, single-row index, multi-column index

For a single-column index, the above example is a single-column index, slightly ...
Mysql> CREATE index Duo_index on student (Id,hobbyid); #将student表中id, Hobbyid Two fields are created as multi-column indexes

Mysql> DROP index duo_index on student; #删除student表多列索引

Mysql> CREATE TABLE info (ID int,name varchar (), index Duo_index (id,name)); #通过创建表的时候将id, the Name field is set to multi-column index

Mysql> Show index from INFO\G;

Mysql> DROP index duo_index on student; #删除student表多列索引

Mysql> DROP index duo_index on info; #删除多列索引

mysql> drop table info; #删除info表

Second, transaction 1, commit the transaction

Mysql> select * from student;

Mysql> begin; #开启事务
mysql> INSERT into student values (1, ' Zhangsan ', +, ' gz ', 1, ' good ');
mysql> INSERT into student values (2, ' Lisi ', +, ' zz ', 1, ' good ');
mysql> INSERT into student values (3, ' Wangwu ', +, ' zz ', 1, ' good ');
Mysql> commit; #提交事务

Mysql> select * from student;

2. Rolling back the transaction

Mysql> begin;
mysql> INSERT into student values (4, ' Zhaoliu ', +, ' gz ', 1, ' good ');
mysql> INSERT into student values (5, ' Tianqi ', +, ' gz ', 1, ' good ');
mysql> INSERT into student values (6, ' Qianba ', +, ' gz ', 1, ' good ');
mysql> rollback; #回滚事务

Mysql> select * from student;

3, specify the rollback point

Mysql> begin;
mysql> INSERT into student values (4, ' t01 ', +, ' gz ', 1, ' good ');
mysql> savepoint S1;
mysql> INSERT into student values (5, ' t02 ', +, ' gz ', 1, ' good ');
mysql> savepoint S2;
mysql> INSERT into student values (6, ' t03 ', +, ' gz ', 1, ' good ');
Mysql> savepoint S3;
mysql> rollback to savepoint S2; #将事务回滚至s2标记下
Mysql> commit; #提交

Mysql> select * from student;

Third, view 1, create a view

Mysql> CREATE VIEW Stu_view as SELECT * from student where score > 30;

2. View detail Information

Mysql> Show CREATE View stu_view\g;

3. View data information

Mysql> select * from Stu_view;

4. Modify the View

Mysql> ALTER VIEW Stu_view as SELECT from student where score > 40;
Mysql> select from Stu_view;

5. Modify the data

mysql> Update Stu_view set score=100 where id=1; #修改视图, score is 100
Mysql> select * from Stu_view;

Mysql> select * from student;

6. Delete View

mysql> drop View Stu_view;

Detailed MySQL transactions, indexes, views