Fifth MySQL transaction, view, index, backup and restore

Fifth. mysql transaction, view, index, backup and restore I. Transaction 1. What is a transaction

A transaction is a mechanism, a sequence of operations, that contains a set of database operations commands and submits or revokes an operation request to the system as a whole. Either they are executed, or none are executed.

Transactions are used as the smallest control unit, especially with database systems that operate concurrently with multiple users.

2. Why transactions are required

A transaction (transaction) is a unified management of a series of data operations bundled into a whole, and if a transaction executes successfully, all data in that transaction commits and becomes a permanent part of the database. If the transaction execution encounters an error and must be canceled or rolled back, the data is all restored to the state before the operation and all changes to the data are cleared.

3. Characteristics of the transaction

A transaction is a series of operations performed as a single unit of work, and a logical unit of work must have four properties, namely: ACID

1) atomicity (atomicity). A transaction is a complete operation, and the individual elements of the transaction are non-divided (atomic). all elements in a transaction must be committed or rolled back as a whole.

2) Consistency (consistency). When the transaction is complete, the data must be in a consistent state. that is, before the transaction begins, the data stored in the database is in a consistent state, and in the ongoing transaction, the data may be in an inconsistent state, as the data may be partially modified. However, when the transaction is complete, the data must return to the known consistent state again.

3) Isolation (lsolation). All concurrent transactions that are modified on the database are isolated from one another, and when one transaction is performing a modification, another transaction cannot be modified at the same time, and must wait until the first transaction is complete before it can operate, thus indicating that the transaction must be independent.

4) Persistence (Durability ). The persistence of a transaction means that the result of the transaction is permanent regardless of whether the system has failed.

4. How to perform a transaction

MySQL provides a variety of storage engine support transactions, and the storage engine that supports transactions has InnoDB and BDB. InnoDB Storage engine transactions are primarily through the undo log and the redo log, and the MyISAM storage engine does not support transactions.

Any kind of database, there are a variety of logs, used to record the operation of the database, daily operations, error messages and so on.

A) Undo log: Copies the data before the transaction executes, for the transaction to roll back the data when an exception occurs.

B) Redo log: Records transactions in execution, each update to the database operation, when the transaction is committed, the content will be flushed to disk.

Syntax for executing a transaction:

(1) start a transaction: begin; or start transaction;

(2) COMMIT TRANSACTION: Commit;

(3) Rollback (undo) transaction: rollback;

example of executing a transaction:

                   /* Start Transaction * / insert a batch of student grades into the student table

BEGIN;

INSERT into ' result ' VALUES

(100000,2,now (), 100),

(100001,1,now (), 90),

(100002,2,now (), 80);

SELECT * from ' result '; #如果用SQLyog会显示已经在表里面更新了数据, the database does not actually update the data

COMMIT; #提交

SELECT * from ' result '; #提交过后数据库里面会更新数据

BEGIN;

INSERT into ' result ' VALUES

(100003,2,now (), 110),

(100004,1,now (), 900);

ROLLBACK; #回滚

SELECT * from ' result '; #执行回滚后查询数据没有任何变化

5. Principles to be followed in the use of transactions

1. The transaction is as brief as possible. in multi-user systems, large transactions can consume a large amount of resources, which can affect the performance of the software and even cause the system to crash.
2. The amount of data accessed in a transaction is minimized. when concurrent execution, the smaller the amount of data that is manipulated by the data, the less contention is to the data between transactions.
3. try not to use transactions when querying data. the query operation does not update the database, but it wastes system resources.
4. try not to have an operation waiting for user input during the transaction process. If you need to wait for the user to enter data during the execution of a transaction, the transaction's President event consumes system resources and can cause system congestion.

Two. Views 1. Why a view is required

In the actual work, different identities of users are concerned about the database data may be different, the people of different positions on the database query data range permissions are also different.

Database management system provides a view of such database objects, can be reused complex query results saved to a view, you can also set different access scope view, different positions of employees according to their own permissions to view the relevant data to ensure the security of data access.

2. What is a view

A view is another way to view data in one or more tables in a database, which is a virtual table that is typically created as a subset of the rows or columns of one or more tables.

A) The four operations that the view is typically used for:

1. Filter rows in a table
2. Prevent unauthorized users from accessing sensitive data tables
3. Abstract multiple physical data tables into a single logical data table
4. Reduce the complexity of your data

B) Benefits of the view to the user:

1. The results are much easier to understand. When you create a view, you can change the column name to a more meaningful column name without affecting the physical database data.
2. Getting data is much easier. You can view the data directly from the view without having to write your own query statements.

C) The benefits of the view for developers:

1. Restricting data retrieval is easier. Can effectively hide some sensitive data
2. More convenient to maintain your application. Debugging a view is easier than debugging a query.

3. Use of views 1. Create a view using SQL statements

Syntax: CREATE View view name

As

<select Statement >;

In the SQL statement naming specification, views are typically named in the View_xxx or v_xxx style.

2. Delete a view using SQL statements

Syntax: DROP view [IF EXISTS] views name;

3. Viewing view data using SQL statements

Syntax: SELECT field 1, Field 2 ..... From view_name (view name);

4. Considerations for Using Views

1) Multiple tables can be used in each view.

2) Similar to a query, a view can nest another view, preferably not more than three layers.

3) Adding the view data, the update and delete operations directly refer to the data in the table.

4) When view data comes from multiple tables, adding and deleting data is not allowed, which affects the integrity of the data in the table.

5. Special Considerations for using views

1.) Using views to modify data can have a number of limitations and relationships, typically used in real-world views as queries only.

2.) View the syntax for all views:

SELECT * from Views\g;

Three. Index 1. What is an index

An index is a way of effectively combining data that can be quickly found to a specified record, similar to a catalog in a book.

2. Role of the Index

1) greatly improve the retrieval speed of the database.

2) Improve the performance of the database.

3. Index classification

The storage type of the index can be divided into B-Tree index (BTREE) and hash table (hash). The InnoDB and MyISAM storage engines support B-tree indexing.

1) normal index; the most basic index type, allowing duplicate and null values to be inserted in the column that defines the index. Its only task is to speed up access to the database. Therefore, you should only create indexes for the data columns in the most frequently occurring query conditions (WHERE) or sort criteria (order by).

2) unique index; a unique index does not allow two rows to have the same index value, allowing for a null value.

If a unique constraint is created, a unique index is automatically created. Although a unique index can help find information, it is still recommended to use primary key constraints for best performance.

3) primary key index; The primary key index requires that each value in the primary key is non-null and unique, allowing fast access to the data.

Defining a primary key for a table in a data diagram automatically creates a primary key index, which is a special type of unique index.

4) composite index; Merge multiple columns as indexes, which are called composite indexes.

It is important to note that the index is used only when the leftmost field of the composite index is used in the query, that is, the first field as a set of prefixes.

5) Full-text indexing; the role of full-text indexing is to support lookup of values, allowing null and duplicate values to occur. Full-text indexes can be created on Char,varchar or text-type columns, and full-text indexing is much more efficient than the "like" keyword. Only the MyISAM storage engine in MySQL5.5 supports full-text indexing.

6) Spatial index, a spatial index is an index to a column of a spatial data type, such as Geometry,point, and so on, creating a spatial index column must declare it as not NULL, and only the MyISAM storage engine supports spatial indexes in MySQL5.5.

4. Create an index

Grammar:

CREATE [UNIQUE | Fulltext | SPATIAL] INDEX index_name

On table_name (Column_name[length] .......);

Where: Unique represents a unique index, Fulltext represents a full-text index, spatial represents a spatial index, and is an optional parameter.

Index_name represents the index name;

TABLE_NAME represents the name of the table that created the index;

COLUMN_NAME Specifies the column to create the index;

length specifies the size of the index, an optional parameter, and only the string type to specify the length of the index.

5. Deleting an index

Grammar:

DROP INDEX index_name on table_name;

6. View Index

Grammar:

SHOW INDEX from table_name;

7. Principles for Using indexes

Because the index itself also needs to be maintained and consumes a certain amount of resources, it is not necessary to add an index to each column, the following conditions can be added to the index;

1) Frequently searched columns

2) columns that are frequently used as queries

3) frequently sorted, grouped columns

4) frequently used as a connection column (primary key, foreign key)

Do not create an index for the following columns:

1. Columns that contain only a few different values
2. The package contains only a few lines
3. Creating indexes for small tables is less used because the events that are spent searching for data in the index take longer than the time-per-row search in the table.

Four. mysql backup and recovery 1. Backing Up the database

syntax: in DOS operating in the environment

Mysqldump–u username –h host -PPassword

Dbname[tbname1,tabnem2....]>filename.sql

Cases:

/* Export Data * /

Mysqldump-u Root-proot;

MySchool Student result>d:\bf\myschool.sql;

2. Recovering a Database

Syntax: Mysql–u username-p [dbname]<filename.sql; in DOS operating in the environment

OR: source filename;

Cases:

/* Import Data * /

CREATE DATABASE ' Newmyschool '; the database being imported must exist

Mysql-u Root-proot newmyschool<d:\bf\myschool.sql;

3. Data sheet Export to a text file

Syntax: SELECT columnlist from TableName

[WHERE Contion]

into OUTFILE ' filename ' [OPTION];

Cases:

# export records for account tables and save As. txt file

SELECT * from ' subject '

Into OUTFILE ' d:/beifen/subject.txt ';

4. Pour the text file into the data sheet

Syntax: LOAD DATA INFILE filename into TABLE tablename[option];

Cases:

DELETE from ' subject '; Delete the data guarantee table in the chart of accounts is empty

LOAD DATA INFILE ' d:/beifen/subject.txt ' into TABLE ' subject ';

Fifth MySQL transaction, view, index, backup and restore