mysql===== theory (indexes, views, stored procedures, transactions)

The basic concept of MySQL

Question 1

What is a database?

A database is an organized, shared, centrally managed collection of data that is stored in a computer for long periods of time. It is a data structure to store and manage

Computer software system. A database contains two levels of meaning, a "warehouse" of data, and methods and techniques for managing data.

Gathering all of the data together cannot be called a database, but only with the ability to leverage information.

Question 2

What are the characteristics of the database?

Realize data sharing, reduce data redundancy, adopt specific data types, have high data independence, and have unified data control function.

Question 3

What is a relational database?

relational database, which is based on the relational model, is used to deal with the data in the database by means of mathematical concepts and methods such as set algebra.

Question 4

A common relational database?

Oracle, SQL Server, Sybase, DB2, Access, MySQL

A link to a non-relational database

http://blog.csdn.net/qq_22101509/article/details/49794303

Question 5

The composition of the database system?

Database: Where data is stored

Database management system: The software used to store the database

Database application: A software supplement to the management database used to improve the processing power of the database system

Database is not equal to the database management system, the database is only a place with the ability to provide extraction information, and the real deletion and retrieval is done by the database management system (DBMS)

Question 6

What parts of the SQL language can be divided into?

Data definition Language (DDL): Drop create alter

Data manipulation Language (DML): Insert Delete Update Select

Data Control Language (DCL): Grant revoke commit rollback

Question 7

What are the advantages of MySQL?

Consider the direction: speed, price, support for different systems (Windows Linux UNIX Mac OS), query-enabled languages (SQL statements), rich interfaces (PHP c c + + Python ...). ）

Open source

Question 8

What is an index?

An index is a separate database structure, stored on disk, that contains reference pointers to all records in the data table.

Question 9

The classification of the index?

Normal index and unique index

Single-column indexes and combined indexes

Full-Text Indexing

Spatial index

Question 10

The storage engine used in the query table

Mysql> show create TABLE score;

+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- ----------------------+

| Table | Create Table                               &NBS P                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp                          ,         &NB Sp          |

+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- ----------------------+

| Score | CREATE TABLE ' score ' (

' id ' int (ten) is not NULL,

' stu_id ' int (ten) is DEFAULT NULL,

' c_name ' varchar DEFAULT NULL,

' Grade ' int (ten) is DEFAULT NULL,

PRIMARY KEY (' id '),

UNIQUE KEY ' id ' (' ID ')

) engine=InnoDB DEFAULT Charset=utf8 |

+-------+------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------- ----------------------+

1 row in Set (0.16 sec)

What are the advantages of indexing? What are the disadvantages of indexing?

Advantages: Convenient Query

Cons: Too many indexes also consume disk space

Design principles for indexes?

Not more indexes are better.

Avoid too many indexes on frequently updated tables, and as few columns as possible in the index.

A table with a small amount of data is best not to use an index.

Build a search on columns that are often used in conditional expressions with more different values, and do not index on columns with fewer values.

Specifies a unique index when uniqueness is a feature of the data itself.

Indexing on columns that are frequently sorted or grouped (that is, a group by or order by operation)

Question 11

What is a stored procedure?

Seq language is a non-program language

Programming language means that the processing process is written through multiple commands for the purpose of achieving a goal. Instead of programming languages, processes cannot be written.

Definition: The use of common or very complex work, pre-written SQL statements and stored with a specified name, then to call the database to provide the functions of the defined stored procedures with the same functionality, just call execute, you can automatically complete the command.

In short, a collection of SQL commands stored in a database

How the query works

All queries need to be interpreted, parsed, optimized, compiled and executed before they are executed.

Explanation: Check the syntax of the statement is correct

Parse: Check that the object name appears in the statement is valid, while checking all permissions of the object

Optimize: Check whether indexes can be used and decide on federated policies

Compiling: Translating the query into an executable table

Execution: Submits and processes query requests for the compilation process

What are the advantages of stored procedures?

Stored procedures are resolved in advance, compiled stored in the database, the execution can reduce the burden on the database, improve execution performance

Question 12

What is a view?

A view is a virtual table that is a table from one or more tables in a database. Views can also be defined on the basis of a view that already exists.

The role of the view?

Simplicity of

Security

Logical Data Independence

Question 13

Understanding of the business?

A transaction is a rigorous set of operations in an application, and all operations must be completed successfully, or all changes made in each operation will be undone. That is, transactions are atomic, and a series of operations in a transaction are either successful or not.

The end of a transaction has two types, and when all the steps in the transaction are executed successfully, the transaction commits. If one of the steps fails, a rollback operation occurs, and the undo action is undone until the start of the transaction.

Two ACID of the transaction

A transaction has four characteristics: atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability). These four properties are referred to as ACID properties.

1. Atomic Nature

A transaction is a logical unit of work for a database, and each operation contained in a transaction is either done or not

2. Consistency

The result of the transaction execution must be to change the database from one consistent state to another. Therefore, when the database contains only the results of successful transaction commits, the database is said to be in a consistent state. If a database system fails in operation and some transactions have not yet been completed, some of the modifications made to the database have been written to the physical database, and the database is in an incorrect state, or an inconsistent state.

3, the isolation of

Execution of one transaction cannot interfere with other transactions. That is, the operations within one transaction and the data used are isolated from other concurrent transactions, and the transactions performed concurrently cannot interfere with each other.

4, the continuity of

Also known as permanence, when a transaction is committed, its changes to the data in the database should be permanent. The next operation or failure should not have any effect on its execution results.

The database system must maintain the following characteristics of the transaction (for short, ACID):

Atomicity (atomicity)

Consistency (consistency)

Isolation (Isolation)

Persistence (Durability)

Atomicity (atomicity)

All operations in a transaction are either fully executed or not executed;

If a transaction is not guaranteed to be atomic, the database may be in an inconsistent state in the event of a system failure

This article is from the "It Life" blog, so be sure to keep this source http://dingxue.blog.51cto.com/12290895/1973682

mysql===== theory (indexes, views, stored procedures, transactions)