Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what are the basic knowledge points of MySQL database". In daily operation, I believe many people have doubts about what are the basic knowledge points of MySQL database. Xiaobian consulted all kinds of information and sorted out simple and easy operation methods. I hope to help you answer the doubts about "what are the basic knowledge points of MySQL database"! Next, please follow the small series to learn together!

Database 1.1 Transaction 1.1.1 Transaction Four Characteristics (ACID)

atomicity (Atomicity)

All operations in the transaction either commit successfully or roll back after failure.

Consistency (Consistency)

Ensure consistency before and after data operations in the database. (For example, transfers between multiple accounts of the user, but the total amount of the user is unchanged)

isolation (Isolation)

Isolation requires that modifications made by one transaction to data in the database be invisible to other transactions until committed. (i.e. serial execution between transactions)

Durability (Durability)

Persistence means that once a transaction is committed, the changes to the data in the database are permanent, and the committed transaction is not lost even in the case of a database system failure.

The SQL standard defines four types of isolation: (isolation from low to high, concurrency from high to low)

Not submitted for reading.

The lowest isolation level, allowing other transactions to see uncommitted data, results in dirty reads.

Submitted for reading.

Because the database is read and write separated, the transaction acquires the read lock when reading, but immediately releases it after reading. After releasing the read lock, the data may be modified by other transactions. If you read it again, you will find that the results of reading the data before and after are different, resulting in non-repeatable reading. (Read locks do not require transaction commit release, while write locks do.)

It can be read repeatedly.

All data retrieved by select cannot be modified, so that inconsistent reads before and after a transaction can be avoided. But there is no way to control phantom reading, because at this time other transactions cannot change the selected data, but can add data;

Serializable.

All transactions are executed one after another, which can avoid phantom reading. For databases that implement concurrency control based on locks, serialization requires obtaining range locks when executing range queries. If the database is not based on concurrency control based on locks, it is necessary to roll back the transactions when detecting transactions that violate serial operations.

Summary: Four progressive levels, each solving problems, with higher transaction levels resulting in poorer performance.

isolation level            Dirty reading, unrepeatable reading, phantom reading.    

Read uncommitted may be possible        may

read committed impossible impossible        may

Repeatable read impossible impossible       may

Serializable (serializable)   no way, no way      impossible

Summary: Uncommitted reads will cause dirty reads-> Committed reads solve dirty reads, but will cause non-repeatable reads-> Repeatable reads solve inconsistent reading results, but cause phantom reads (not before, but now)-> Serializable solves phantom reads, but adds a lot of range locks, which may cause lock timeout;

1.1.2 Dirty, Irrepeatable, and Illusion Reading

Dirty read (operation against rollback): Transaction T1 updates the contents of a row of records without committing the changes, transaction T2 reads the updated row, and then T1 performs a rollback operation that cancels the changes just made. Now the number of rows read by T2 is invalid (one transaction reads another);

Non-repeatable read (operation for modification): Transaction T1 reads a row of records, T2 modifies the row of records that T1 just read, and T1 reads the row of records again and finds that the result is different from the one just read.

Phantom reads (operations on updates): Transaction T1 reads the result set returned by a specified where clause, and then transaction T2 inserts a new row that exactly satisfies the query condition used by T1. T1 then retrieves the table again, but sees the data inserted by T2 again. (I didn't see it the first time, but I saw it the second time)

Index 2.1 Index characteristics

It can speed up database retrieval;

Can only be created on a table, not on a view;

It can be created directly or indirectly.

Indexing can be used in optimizing hiding;

Using the query processor to execute sql statements, only one index can be used at a time on a table.

2.1.1 Advantages of indexing

Create a unique index to ensure the uniqueness of each row of data in the database table;

Greatly speed up data retrieval, which is the main reason for creating indexes;

Speeding up links between database tables, especially in achieving database referential integrity, is particularly interesting;

When using grouping and sorting clauses for retrieval, it can also significantly reduce the time for grouping and sorting in queries;

By using indexes, you can use optimization hiders in queries to improve system performance;

2.1.2 Index weaknesses

Creating and maintaining indexes is time consuming, and this time increases with the number of indexes;

Indexes need to occupy physical space. In addition to the data space occupied by the data table, each index also occupies a certain amount of physical space. If a clustered index is established, the required space will be larger.

When adding, deleting and modifying data in the table, the index also needs to be maintained, reducing the speed of data maintenance;

2.2 reference classification

(1) General index (it has no restrictions.)

(2) Uniqueness index (The value of index column must be unique, but null value is allowed.)

(3) Primary key index (a special unique index that does not allow null values. The primary key index is usually created at the same time as the table is being built.)

(4) Combined index

(5) The clustered index constructs a B+ tree according to the primary key of each table, and the leaf nodes store the row record data of the whole table, so the leaf nodes of the clustered index are also data pages.

(6) Non-clustered index (secondary index)(page node does not store an entire row of records).

2.3 index failure

(1) If there is or in the condition, even if there is a conditional index, it will not be used (or is used as little as possible);

(2) Like queries start with %, e.g. SELECT * FROM mytable WHEREt Name like'% admin';

(3) If the column type is a string, be sure to use quotation marks in the condition, otherwise the index will not be used;

2.4 Each engine supports indexing

MyISAM, InnoDB,Memonry Comparison of three common MySQL engine types:

index   MyISAM index   InnoDB index   Memonry index

B-tree index support        support      support

Hash index   not support     not support     support

R-Tree index support       not support     not support

Full-text index not supported     does not support     not support

2.5 Index structure in database

Because when using binary tree, I/O reads and writes are too frequent due to the excessive depth of binary tree, which leads to low query efficiency. Therefore, by adopting the multi-path tree structure, various operations of the B-tree can keep the height of the B-tree low.

B-trees are also called balanced multi-path search trees. The characteristics of a B-tree of order m are as follows:

1. Each node in the tree contains at most m children (m>=2);

2. Every node except root and leaf nodes has at least (ceiling (m/2)) children (where ceiling (x) is an upper bound function);

The root node has at least two children (unless the B-tree contains only one node: the root node).

4. All leaf nodes appear in the same layer, leaf nodes do not contain any keyword information (can be regarded as external nodes or query failed nodes, pointers to these nodes are null);(Note: leaf nodes only have no children and pointers to children, these nodes also exist, there are elements, similar to red and black trees, each null pointer is regarded as a leaf node, but not drawn)

B+ tree

Under what circumstances is indexing appropriate?

(1) index fields that often appear after the keywords order by, group by, distinct;

(2) Establish indexes on the result set fields of union and other set operations, and the purpose of establishing indexes is the same as above;

(3) indexing fields that are frequently used as query selections;

(4) Indexing attributes that are often used as table links;

(5) Consider using index coverage. For tables where data is rarely updated, if users often query only a few fields, you can consider establishing indexes on these fields, thus changing the scan of the table to the scan of the index.

Third, Mysql syntax order

That is, when the following keywords exist in sql, they should maintain this order:

select[distinct], from, join (e.g. left join), on, where, group

by、having、union、order by、limit;

IV. Execution order of Mysql

That is, sql is executed in the following order:

from、on、join、where、group by、having、select、distinct、union、order by

 group by To use with aggregate functions,

For example:

select a.Customer,sum(a.OrderPrice) from orders a where a.Customer='Bush' or a.Customer = 'Adams' group by a.Customer;

Implementation of multi-table query (inner join)

select u.uname,a.addr from lm_user u inner join lm_addr a on u.uid = a.uid;

Select from where can also be done.

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid; V. Stored procedure elimiter $$create procedure_bill()comment 'Query all sales' begin select bild, tx_time, amt from lm_bill;end $$elimiter;

call a stored procedure

call procedure_bill();

View Stored Procedures

show procedure status like 'procedure_bill'; VI. Establish many-to-many data table relationships

In the database, if the relationship between two tables is many-to-many, such as "student table and curriculum table," one student can choose multiple courses, and one course can also be selected by multiple students; according to the design principle of the database, a third association table should be formed.

Step 1: Create three data tables Student ,Course,Stu_Cour

/** Student table */CREATE TABLE Student (stu_id INT AUTO_INCREMENT,NAME VARCHAR(30),age INT ,class VARCHAR(50),address VARCHAR(100),PRIMARY KEY(stu_id))/* Student course table */CREATE TABLE Course(cour_id INT AUTO_INCREMENT,NAME VARCHAR(50),CODE VARCHAR(30),PRIMARY KEY(cour_id))/** */CREATE TABLE Stu_Cour(sc_id INT AUTO_INCREMENT,stu_id INT ,cour_id INT,PRIMARY KEY(sc_id))

Step 2: Add foreign keys to Stu_Cour association table

/* Add foreign key constraint bundle */ALTER TABLE Stu_Cour ADD CONSTRAINT stu_FK1 FOREIGN KEY(stu_id) REFERENCES Student(stu_id);ALTER TABLE Stu_Cour ADD CONSTRAINT cour_FK2 FOREIGN KEY(cur_id) REFERENCES Course(cur_id);

Creation complete!

Note: Add foreign keys to already added data tables:

- syntax:alter table name add constraint FK_ID foreign key(your foreign key field name) REFERENCES outer table name (the primary key field name of the corresponding table);

Example: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);

VII. Database engine (storage engine)

When you access a database, whether manually or programmatically, you do not read or write database files directly, but access database files through the database engine.

Take a relational database as an example, send SQL statements to the database engine, and the database engine interprets the SQL statements and extracts the data you need to return to you. Thus, to the visitor, the database engine is the interpreter of SQL statements.

7.1 Difference between MYISAM and InnoDB engines

Main differences:

MYISAM is non-transaction-safe, while InnoDB is transaction-safe;

NYISAM lock granularity is table-level lock, while InnoDB supports row-level lock;

MYISAM supports full-text indexing, while InnoDB does not.

MYISAM is relatively simple, so it is better than InnoDB in efficiency. Small applications can consider using MYISAM;

MYISAM tables are saved as files, and using MYISAM storage in cross-platform data transfer saves a lot of trouble;

(6) InnoDB tables are more secure than MYISAM tables, and can switch from non-transaction tables to transaction tables without losing data;

Application scenarios:

MYISAM manages non-transactional tables, provides high-speed storage and retrieval, as well as full-text search capabilities, and is a better choice if the application needs to perform a large number of select queries.

InnoDB is used for transaction processing applications and has numerous features, including ACID transaction support. If your application needs to perform a large number of insert or update operations, you should use innodb, which can improve the performance of multi-user concurrent operations.

VIII. Database Paradigm

At present, there are six normal forms of relational database: the first normal form {1NF}, the second normal form {2NF}, the third normal form {3NF}, the Bas-Cod normal form {BCNF}, the fourth normal form {4NF}, and the fifth normal form {5NF, also known as perfect normal form}. The paradigm that meets the minimum requirements is the first paradigm. On the basis of the first normal form, the database that further meets more specification requirements is called the second normal form {2NF}, and the rest of the normal forms are analogous. Generally speaking, the database only needs to meet the third normal form (3NF) to be OK.

Paradigm:

1NF: Ensure that each column remains atomic;

2NF: Ensure that every column in the table is related to the primary key (joint primary key);

3NF: Ensure that every column in the table is directly related to the primary key (foreign key);

BCNF: On the basis of 1NF, no non-primary attribute can depend on the subset of primary keys (on the basis of 3NF, the dependence on the subset of primary keys is eliminated);

4NF: requires the deletion of many-to-many relationships within the same table;

5NF: Reconstruct the original structure from the final structure;

At this point, the study of "MySQL database basics" is over, hoping to solve everyone's doubts. Theory and practice can better match to help you learn, go and try it! If you want to continue learning more relevant knowledge, please continue to pay attention to the website, Xiaobian will continue to strive to bring more practical articles for everyone!

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.