Grundwissenspunktreserve für MySQL-Datenbanken (organisierte Zusammenfassung)

Dieser Artikel vermittelt Ihnen relevantes Wissen über die MySQL-Datenbank. Er organisiert hauptsächlich einige grundlegende Wissenspunkte der Datenbank, einschließlich Indizierung, Syntaxreihenfolge, Ausführungsreihenfolge, gespeicherte Prozeduren und andere verwandte Themen.

Empfohlenes Lernen: MySQL-Tutorial

1. Datenbank

1.1 Transaktionen

1.1.1 Vier Merkmale von Transaktionen (ACID)

• Atomizität (Atomizität )Atomicity）
  整个事务中的所要操作要么全部提交成功，要么全部失败回滚。
• 一致性（Consistency）
  保证数据库中的数据操作之前和操作之后的一致性。（比如用户多个账户之间的转账，但是用户的总金额是不变的）
• 隔离性（Isolation）
  隔离性要求一个事务对数据库中数据的修改，在未提交完成前对于其它事务是不可见的。（即事务之间要串行执行）
• 持久性（Durability）
  持久性是指一个事务一旦被提交了，那么对数据库中的数据改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。

SQL标准定义了四种隔离性：（下面隔离性是由低到高，并发性由高到低）

• 未提交读。
  最低的隔离等级，允许其他事务看到没有提交的数据，会导致脏读。
• 已提交读。
  由于数据库是读写分离，事务读取的时候获取读锁，但是在读完之后立即释放，释放读锁之后，就可能被其他事务修改数据，再进行读是就发现前后读取数据的结果不同，造成不可重复读。（读锁不需要事务提交后释放，而写锁需要事务提交后释放。）
• 可重复读。
  所有被select Alle erforderlichen Vorgänge in der gesamten Transaktion müssen entweder erfolgreich übermittelt werden oder alle fehlschlagen und zurückgesetzt werden.
• Konsistenz (Konsistenz)
  stellt die Konsistenz der Daten in der Datenbank vor und nach dem Vorgang sicher. (Zum Beispiel Überweisungen zwischen mehreren Konten eines Benutzers, aber der Gesamtbetrag des Benutzers bleibt unverändert)

Isolation (Isolation)

Isolation erfordert, dass eine Transaktion die Daten in der Datenbank ändert und nicht Ändern Sie es in Zukunft. Es ist für andere Transaktionen nicht sichtbar, bis der Commit abgeschlossen ist. (Das heißt, Transaktionen müssen seriell ausgeführt werden)

Dauerhaftigkeit (Durability)
Haltbarkeit bedeutet, dass die Änderungen an den Daten in der Datenbank dauerhaft sind, sobald eine Transaktion übermittelt wird, auch wenn dies der Fall ist Wenn das Datenbanksystem auf einen Fehler stößt, geht der Vorgang zum Festschreiben der Transaktion nicht verloren.


Der SQL-Standard definiert vier Arten der Isolation: (Die folgende Isolation reicht von niedrig nach hoch und die Parallelität von hoch nach niedrig.)

Nicht festgeschriebener Lesevorgang.

Die niedrigste Isolationsstufe, die es anderen Transaktionen ermöglicht, nicht festgeschriebene Daten zu sehen, was zu fehlerhaften Lesevorgängen führt. 🎜🎜Zum Lesen eingereicht. 🎜 Da die Datenbank lese- und schreibgetrennt ist, wird die Lesesperre beim Lesen der Transaktion erworben, sie wird jedoch sofort nach dem Aufheben der Lesesperre freigegeben. Beim erneuten Lesen können die Daten geändert werden Die Art und Weise, wie die Daten vorher und nachher gelesen werden, ist unterschiedlich, was zu nicht wiederholbaren Lesevorgängen führt. (Lesesperren müssen nach dem Festschreiben der Transaktion nicht freigegeben werden, während Schreibsperren nach dem Festschreiben der Transaktion freigegeben werden müssen.) 🎜🎜Wiederholbares Lesen. 🎜 Alle von select erhaltenen Daten können nicht geändert werden, um ein inkonsistentes Lesen vor und nach einer Transaktion zu vermeiden. Es gibt jedoch keine Möglichkeit, das Phantomlesen zu steuern, da andere Transaktionen die ausgewählten Daten derzeit nicht ändern, aber Daten hinzufügen können 🎜🎜Serialisierbar. 🎜 Alle Transaktionen werden nacheinander ausgeführt, um Phantom-Lesevorgänge zu vermeiden. Bei Datenbanken, die eine auf Sperren basierende Parallelitätskontrolle implementieren, muss bei der Ausführung von Bereichsabfragen eine Bereichssperre eingerichtet werden Wenn eine Transaktion erkannt wird, die gegen serielle Vorgänge verstößt, muss die Transaktion zurückgesetzt werden. 🎜🎜🎜Zusammenfassung: Die vier Ebenen werden schrittweise verbessert und jede Ebene löst das Problem. Je höher die Transaktionsebene, desto schlechter ist die Leistung.脏 脏 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜🎜 🎜 🎜 🎜 🎜 🎜 🎜 eingereicht werden (Community lesen) ist möglicherweise nicht möglich (serialisierbar)   unmöglich    unmöglich🎜🎜🎜Zusammenfassung: Nicht festgeschriebene Lesevorgänge führen zu fehlerhaften Lesevorgängen -> Festgeschriebene Lesevorgänge lösen fehlerhafte Lesevorgänge, verursachen jedoch nicht wiederholbare Lesevorgänge -> Wiederholbare Lesevorgänge lösen das Problem des Lesens der Ergebnisse vor und nach der Inkonsistenz, verursachen jedoch Phantom-Lesevorgänge (nicht vorher, sondern jetzt) ​​-> Die Serialisierbarkeit löst Phantom-Lesevorgänge, fügt jedoch viele Bereichssperren hinzu, die zu Zeitüberschreitungen bei Sperren führen können

1.1.2 Dirty Read, nicht wiederholbarer Read und Phantom Read

• Dirty Read (für Rollback-Vorgang): Transaktion T1 hat den Inhalt einer Zeile von Datensätzen aktualisiert, die Änderung jedoch nicht festgeschrieben, Transaktion T2 hat Get gelesen die aktualisierte Zeile, und dann führt T1 einen Rollback-Vorgang durch und bricht die gerade vorgenommene Änderung ab. Jetzt ist die Anzahl der von T2 gelesenen Zeilen ungültig (eine Transaktion liest eine andere Transaktion);
• Nicht wiederholbares Lesen (für geänderte Vorgänge): Transaktion T1 liest eine Zeile mit Datensätzen, und dann ändert T2 den Datensatz, den T1 gerade gelesen hat von Datensätzen, und dann las T1 diese Zeile von Datensätzen erneut und stellte fest, dass sich die Ergebnisse von den gerade gelesenen Ergebnissen unterschieden.
• Phantom-Lesen (für Aktualisierungsvorgänge): Transaktion T1 liest die Ergebnismenge, die von einer angegebenen WHERE-Klausel zurückgegeben wird, und dann fügt Transaktion T2 eine neue Zeile mit Datensätzen ein, was zufällig die von T1 verwendeten Abfragebedingungen erfüllt. Dann ruft T1 die Tabelle erneut ab, sieht aber erneut die von T2 eingefügten Daten. (Ich habe es beim ersten Mal nicht gesehen, aber beim zweiten Mal)

2. Index

2.1 Indexfunktionen

1. können den Datenbankabruf beschleunigen
2. können nur für Tabellen erstellt werden, keine Ansichten;
3. Kann direkt oder indirekt erstellt werden;
4. Verwenden Sie den Abfrageprozessor, um SQL-Anweisungen gleichzeitig auszuführen.

2.1.1 Indexvorteile

Erstellen Sie einen eindeutigen Index, um die Eindeutigkeit jeder Datenzeile in der Datenbanktabelle sicherzustellen.

1. Beschleunigen Sie den Datenabruf erheblich, was der Hauptgrund für die Erstellung eines Index ist
Beschleunigen Sie die Datenbank. Verknüpfungen zwischen Tabellen sind besonders wichtig, um die referenzielle Integrität der Datenbank zu erreichen.
2. Beim Abrufen mithilfe von Gruppierungs- und Sortierklauseln kann die Zeit für das Gruppieren und Sortieren in Abfragen ebenfalls erheblich reduziert werden.
3. Durch die Verwendung von Indizes Hider können in Abfragen verwendet werden, um die Systemleistung zu verbessern.
5. Zusätzlich zum von der Datentabelle belegten Datenraum belegt jeder Index auch eine bestimmte Menge an physischem Speicherplatz. Wenn ein Clustered-Index erstellt wird, ist der erforderliche Speicherplatz größer.

Beim Hinzufügen, Löschen und Ändern von Daten in der Tabelle. Der Index muss ebenfalls gepflegt werden, wodurch die Geschwindigkeit der Datenpflege verringert wird.

2.2 Indexklassifizierung
2. (1) Gewöhnlicher Index (es gibt keine Einschränkungen.)
(2) Eindeutiger Index (der Wert der Indexspalte). muss eindeutig sein, Nullwerte sind jedoch zulässig.)
3. (3) Primärschlüsselindex (ein spezieller eindeutiger Index, der keine Nullwerte zulässt. Der Primärschlüsselindex wird normalerweise gleichzeitig mit der Erstellung der Tabelle erstellt.)
(4) Kombinierter Index

(5) Der Clustered-Index erstellt einen B+-Baum basierend auf dem Primärschlüssel jeder Tabelle, und die Zeilendatensatzdaten der gesamten Tabelle werden in den Blattknoten gespeichert, also auch in den Blattknoten des Clustered-Index werden zu Datenseiten.

(6) Nicht gruppierter Index (Hilfsindex) (der Seitenknoten speichert nicht eine ganze Zeile von Datensätzen). 2.3 Indexfehler beginnt mit %, wie zum Beispiel SELECT * FROM mytable WHEREt Name like'%admin'; (3) Wenn der Spaltentyp eine Zeichenfolge ist, muss er in der Bedingung in Anführungszeichen gesetzt werden, andernfalls wird der Index nicht verwendet;

2.4 Jede Engine unterstützt Indizes



Vergleich der Engine-Typen:
Index   MyISAM-Index   InnoDB-Index  Speicherindex   B-Tree. Index  Unterstützung       Unterstützt H Ash-Index  Nicht unterstützt     Hash-Index  Nicht unterstützt     Unterstützt  R-Tree-Index  Unterstützung         Nicht unterstützt   Volltextindex Nicht unterstützt. Nicht unterstützt. Nicht unterstützt. Daher kann die Höhe des B-Baums durch die Verwendung einer Mehrweg-Baumstruktur durch verschiedene Operationen am B-Baum niedrig gehalten werden.

B-Baum wird auch als ausgeglichener Mehrpfad-Suchbaum bezeichnet. Die Eigenschaften eines B-Baums m-Ordnung sind wie folgt:

• 1. Jeder Knoten im Baum enthält höchstens m Kinder (m>=2); wobei ceil(x) eine Funktion ist, die die Obergrenze annimmt); Die Punkte erscheinen alle in derselben Ebene und die Blattknoten enthalten keine Schlüsselwortinformationen (kann als externe Knoten oder Knoten betrachtet werden, bei denen die Abfrage fehlgeschlagen ist und die Zeiger, die auf diese Knoten zeigen, null sind); einfach nicht Kinder und Zeiger auf Kinder, diese Knoten existieren auch, und es gibt Elemente (ähnlich wie beim rot-schwarzen Baum wird jeder Nullzeiger als Blattknoten betrachtet, aber nicht gezeichnet)
• B+ Baum
• 
  Wo ist es? Unter welchen Umständen ist es geeignet, einen Index zu erstellen? Ergebnismengenfelder von Vereinigungs- und anderen Mengenoperationen und Erstellen eines Index. Der Zweck ist derselbe wie oben.
  (3) Erstellen Sie Indizes für Felder, die häufig für die Abfrageauswahl verwendet werden. (4) Erstellen Sie Indizes für häufig verwendete Attribute als Tabellenverknüpfungen; (5) Erwägen Sie die Verwendung einer Indexabdeckung, die nur wenige Daten erfordert. Wenn der Benutzer häufig nur einige Felder der aktualisierten Tabelle abfragt, können Sie erwägen, Indizes für diese Felder zu erstellen, um den Tabellenscan in einen Indexscan umzuwandeln.
  
  3. MySQL-Syntaxreihenfolge
  
  Das heißt, wenn die folgenden Schlüsselwörter in SQL vorhanden sind, müssen sie diese Reihenfolge beibehalten:
  
  
  select[distinct], from, join (z. B. left join), on, where, group
by, have, union, order by, limit;

4. MySQL-Ausführungssequenz

Das heißt, bei der Ausführung wird SQL in der folgenden Reihenfolge ausgeführt:

von, an, beitreten, wo, gruppieren nach, Haben, Auswählen, Unterscheiden, Vereinigen, Sortieren nach

Gruppieren nach sollte zusammen mit Aggregatfunktionen verwendet werden,

Zum Beispiel:

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

Implementieren einer Abfrage mit mehreren Tabellen (Inner Join)

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

Verwenden von Auswählen von wo kann auch verwendet werden. Implementieren Sie <br><pre class="brush:php;toolbar:false">select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;</pre>

5. Gespeicherte Prozedur

delimiter $$
create procedure procedure_bill()
comment '查询所有销售情况'
begin
select billid, tx_time, amt from lm_bill;
end $$
delimiter ;

Rufen Sie die gespeicherte Prozedur auf

call procedure_bill();

Sehen Sie sich die gespeicherte Prozedur an

show procedure status like 'procedure_bill';

6. Stellen Sie eine Viele-zu-Viele-Datentabellenbeziehung her select from where同样可以实现

/**学生表*/
CREATE TABLE Student (
stu_id INT AUTO_INCREMENT,
NAME VARCHAR(30),
age INT ,
class VARCHAR(50),
address VARCHAR(100),
PRIMARY KEY(stu_id)
)
/*学生课程表*/
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)
)

五、存储过程

/*添加外键约束*/
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(cour_id) REFERENCES Course(cour_id);

调用存储过程

rrreee

查看存储过程

rrreee

六、建立多对多数据表关系

在数据库中，如果两个表的之间的关系为多对多的关系，如：“学生表和课程表”，一个学生可以选多门课，一门课也可以被多个学生选;根据数据库的设计原则，应当形成第三张关联表。
步骤1:创建三张数据表Student ,Course,Stu_Cour

rrreee

第二步:为Stu_Cour关联表添加外键

rrreee

完成创建！

• 注:为已经添加好的数据表添加外键:
  -语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

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

七、数据库引擎（存储引擎）

当你访问数据库时，不管是手工访问，还是程序访问，都不是直接读写数据库文件，而是通过数据库引擎去访问数据库文件。

以关系型数据库为例，发SQL语句给数据库引擎，数据库引擎解释SQL语句，提取出你需要的数据返回给你。因此，对访问者来说，数据库引擎就是SQL语句的解释器。

7.1 MYISAM和InnoDB引擎的区别

主要区别：

• MYISAM 是非事务安全型的，而InnoDB是事务安全型；
• NYISAM锁的粒度是表级锁，而InnoDB支持行级锁；
• MYISAM支持全文本索引，而InnoDB不支持全文索引
• MYISAM相对简单，所以在效率上要优于InnoDB,小型应用可以考虑使用MYISAM;
• MYISAM表是保存成文件的形式，在跨平台的数据转移中使用MYISAM存储会省去不少的麻烦;
• （6）InnoDB表比MYISAM
In der Datenbank Wenn es sich bei der Beziehung zwischen zwei Tabellen um mehrere Paare handelt, z. B. „Studententabelle und Kursplan“, kann ein Student mehrere Kurse auswählen, und ein Kurs kann auch von mehreren Studenten gemäß den Entwurfsprinzipien der Datenbank ausgewählt werden , sollte eine dritte Assoziationstabelle gebildet werden.

Schritt 1: Erstellen Sie drei Datentabellen Student, Course, Stu_Cour

rrreee

Schritt 2: Fügen Sie Fremdschlüssel zur Stu_Cour-bezogenen Tabelle hinzu🎜rrreee🎜Schließen Sie die Erstellung ab! 🎜🎜🎜Hinweis: Fügen Sie Fremdschlüssel zur bereits hinzugefügten Datentabelle hinzu:🎜 - Syntax: Tabelle ändern, Tabellenname hinzufügen, Einschränkung hinzufügen FK_ID Fremdschlüssel (Ihr Fremdschlüsselfeldname) REFERENZEN Fremdtabellenname (Primärschlüssel der entsprechenden Tabelle) Feldname);🎜🎜🎜Beispiel: Alter Table tb_active Add Constraint FK_ID Foreign Key(user_id) REFERENCES tb_user(id);🎜🎜Sieben. Datenbank-Engine (Speicher-Engine)🎜🎜Wann Wenn Sie auf die Datenbank zugreifen, unabhängig davon, ob es sich um einen manuellen Zugriff oder einen Programmzugriff handelt, lesen und schreiben Sie die Datenbankdatei nicht direkt, sondern greifen über die Datenbank-Engine auf die Datenbankdatei zu. 🎜🎜Nehmen Sie eine relationale Datenbank als Beispiel. Senden Sie eine SQL-Anweisung an die Datenbank-Engine. Die Datenbank-Engine interpretiert die SQL-Anweisung, extrahiert die benötigten Daten und gibt sie an Sie zurück. Für Besucher ist die Datenbank-Engine daher der Interpreter von SQL-Anweisungen. 🎜

🎜7.1 Der Unterschied zwischen MYISAM- und InnoDB-Engines🎜

🎜Hauptunterschied: 🎜🎜🎜MYISAM ist nicht transaktional sicher, während InnoDB transaktional ist sicherer Typ; 🎜🎜NYISAM Sperrgranularität ist Sperren auf Tabellenebene, während InnoDB Sperren auf Zeilenebene unterstützt; 🎜🎜MYISAM unterstützt Volltext index, während InnoDB keine Volltextindizierung unterstützt🎜🎜MYISAM ist relativ einfach und daher hinsichtlich der Effizienz besser als InnoDB. Kleine Anwendungen können die Verwendung von MYISAM in Betracht ziehen; 🎜🎜MYISAM-Tabelle wird als Datei gespeichert. Durch die Verwendung von MYISAM für die plattformübergreifende Datenübertragung wird viel gespart 🎜🎜 (6) InnoDB-Tabellen sind sicherer als MYISAM-Tabellen. Sie können nicht-transaktionale Tabellen in transaktionale Tabellen umwandeln, ohne Daten zu verlieren : 🎜

• MYISAM verwaltet nicht-transaktionale Tabellen. Es bietet Hochgeschwindigkeitsspeicherung und -abruf sowie Volltextsuchfunktionen, wenn die Anwendung eine große Anzahl von Auswahlvorgängen durchführen muss. code>-Abfragen, dann ist <code>MYISAM die bessere Wahl. <code>MYISAM管理非事务表，它提供高速存储和检索，以及全文搜索能力，如果应用中需要执行大量的select查询，那么MYISAM是更好的选择。
• InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的insert或update操作，则应该使用innodb
InnoDB wird für Transaktionsverarbeitungsanwendungen verwendet und verfügt über viele Funktionen, einschließlich ACID-Transaktionsunterstützung. Wenn Ihre Anwendung eine große Anzahl von insert- oder update-Vorgängen ausführen muss, sollten Sie innodb verwenden, was die Leistung von Mehrbenutzern verbessern kann gleichzeitige Operationen.

8. Datenbankparadigma

Derzeit gibt es 6 Paradigmen in relationalen Datenbanken: erste Normalform {1NF}, zweite Normalform {2NF}, dritte Normalform {3NF}, Bass-Codd-Normalform {BCNF}, vierte Normalform Form Normalform {4NF}, fünfte Normalform {5NF, auch als perfekte Normalform bekannt}. Das Paradigma, das die Mindestanforderungen erfüllt, ist die erste Normalform. Basierend auf der ersten Normalform wird diejenige, die mehr Standardanforderungen erfüllt, als zweite Normalform {2NF} bezeichnet, und die anderen Paradigmen folgen diesem Beispiel. Im Allgemeinen muss die Datenbank nur die dritte Normalform (3NF) erfüllen. .

Paradigma:

• 1NF: Stellen Sie sicher, dass jede Spalte atomar bleibt;
• 2NF: Stellen Sie sicher, dass jede Spalte in der Tabelle mit dem Primärschlüssel verknüpft ist (gemeinsamer Primärschlüssel);
• 3NF: Stellen Sie sicher, dass jede Spalte in der Tabelle atomar ist steht in direktem Zusammenhang mit dem Primärschlüssel (Fremdschlüssel);
• BCNF: Auf Basis von 1NF kann kein nicht-primäres Attribut von der Primärschlüssel-Teilmenge abhängen (auf Basis von 3NF entfällt die Abhängigkeit von der Primärschlüssel-Teilmenge). ;
• 4NF: Es ist erforderlich, mehrere Paare in derselben Tabelle zu kombinieren.
• 5NF: Stellen Sie die ursprüngliche Struktur aus der endgültigen Struktur wieder her.

MySQL-Video-Tutorial

Das obige ist der detaillierte Inhalt vonGrundwissenspunktreserve für MySQL-Datenbanken (organisierte Zusammenfassung). Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!