Rizab titik pengetahuan asas pangkalan data MySQL (ringkasan tersusun)

Artikel ini membawakan anda pengetahuan yang berkaitan tentang pangkalan data mysql Ia terutamanya mengatur beberapa titik pengetahuan asas pangkalan data, termasuk pengindeksan, susunan sintaks, perintah pelaksanaan, prosedur tersimpan dan isu lain yang berkaitan semua orang.

Pembelajaran yang disyorkan: tutorial mysql

1. Pangkalan Data

1.1 Transaksi

1.1.1 Empat ciri urus niaga (ACID)

• Atomicity (Atomicity)
  Semua operasi yang diperlukan dalam keseluruhan transaksi mesti berjaya diserahkan , atau semuanya gagal dan berguling kembali.
• Ketekalan (Consistency)
  Memastikan ketekalan data dalam pangkalan data sebelum dan selepas operasi. (Sebagai contoh, pemindahan antara berbilang akaun pengguna, tetapi jumlah keseluruhan pengguna kekal tidak berubah)
• Pengasingan (Isolation)
  Pengasingan memerlukan transaksi untuk mengubah suai data dalam pangkalan data tidak kelihatan kepada transaksi lain sehingga komit selesai. (Iaitu, urus niaga mesti dilaksanakan secara bersiri)
• Kegigihan (Durability)
  Ketahanan bermaksud sebaik sahaja transaksi diserahkan, perubahan kepada data dalam pangkalan data adalah kekal, walaupun operasi melakukan transaksi tidak akan hilang walaupun sistem pangkalan data menghadapi kegagalan.

SQLStandard mentakrifkan empat jenis pengasingan: (Pengasingan berikut adalah dari rendah ke tinggi, dan konkurensi adalah dari tinggi ke rendah)

• Bacaan tanpa komitmen.
  Tahap pengasingan paling rendah, membolehkan transaksi lain melihat data tidak komited, yang akan membawa kepada bacaan kotor.
• Diserahkan untuk dibaca.
  Memandangkan pangkalan data dibaca dan tulis dipisahkan, kunci baca diperoleh apabila transaksi dibaca, tetapi ia dikeluarkan serta-merta selepas kunci baca dikeluarkan, data mungkin diubah suai oleh transaksi lain , anda akan mendapati bahawa data telah dibaca sebelum dan selepas Keputusan adalah berbeza, menyebabkan bacaan tidak boleh diulang. (Kunci baca tidak perlu dilepaskan selepas transaksi dilakukan, manakala kunci tulis perlu dilepaskan selepas transaksi dilakukan.)
• Bacaan berulang.
  Semua data yang diperolehi oleh select tidak boleh diubah suai, sekali gus mengelakkan bacaan tidak konsisten sebelum dan selepas transaksi. Tetapi tiada cara untuk mengawal bacaan hantu, kerana urus niaga lain tidak boleh menukar data yang dipilih pada masa ini, tetapi mereka boleh menambah data
• boleh bersiri.
  Semua urus niaga dilaksanakan satu demi satu untuk mengelakkan bacaan hantu Untuk pangkalan data yang melaksanakan kawalan serentak berdasarkan kunci, siri memerlukan apabila melaksanakan pertanyaan julat, kunci julat perlu diperolehi Jika konkurensi tidak dilaksanakan berdasarkan kunci Terkawal pangkalan data, apabila transaksi yang melanggar operasi bersiri dikesan, urus niaga itu perlu ditarik balik.

Ringkasan: Empat tahap dipertingkatkan secara beransur-ansur, dan setiap tahap menyelesaikan masalah Semakin tinggi tahap transaksi, semakin teruk prestasinya. Tahap pengasingan yang tidak dapat dibaca dengan bacaan yang tidak dapat dikembalikan masalah hasil bacaan yang tidak konsisten, tetapi menyebabkan bacaan hantu (bukan sebelum ini, tetapi sekarang) -> Serializable menyelesaikan bacaan hantu, tetapi menambah banyak kunci julat , boleh menyebabkan kunci tamat masa;

1.1.2 Bacaan kotor, bacaan tidak boleh berulang dan bacaan hantu

• Bacaan kotor (untuk operasi rollback): Transaksi T1 mengemas kini deretan kandungan rekod , tetapi pengubahsuaian tidak dilakukan Transaksi T2 membaca baris yang dikemas kini, dan kemudian T1 melakukan operasi rollback dan membatalkan pengubahsuaian yang baru dibuat. Kini bilangan baris yang dibaca oleh T2 adalah tidak sah (satu transaksi membaca satu lagi transaksi); rekod yang baru dibaca T1, dan kemudian T1 membaca baris rekod ini sekali lagi, dan mendapati keputusannya berbeza daripada keputusan yang baru dibaca.
• Baca hantu (untuk operasi kemas kini): Transaksi T1 membaca set hasil yang dikembalikan oleh klausa tempat yang ditentukan, dan kemudian transaksi T2 memasukkan baris rekod baharu, yang betul-betul boleh memenuhi pertanyaan yang digunakan oleh keadaan T1. Kemudian T1 mendapatkan semula jadual, tetapi melihat data yang dimasukkan oleh T2 sekali lagi. (Saya tidak melihatnya pada kali pertama, tetapi saya melihatnya kali kedua)
• 2. 🎜>

boleh mempercepatkan pengambilan pangkalan data; pengoptimuman Gunakan indeks dalam;

Gunakan pemproses pertanyaan untuk melaksanakan pernyataan sql Pada jadual, hanya satu indeks boleh digunakan pada satu masa.

2.1.1 Kelebihan indeks

2. Buat indeks unik untuk memastikan keunikan setiap baris data dalam jadual pangkalan data;
Mempercepatkan perolehan data dengan ketara, yang merupakan sebab utama untuk mencipta indeks;
3. Mempercepatkan pautan antara jadual pangkalan data, terutamanya dalam mencapai integriti rujukan pangkalan data; untuk mendapatkan semula, masa untuk mengumpulkan dan menyusun dalam pertanyaan juga boleh dikurangkan dengan ketara;
4. Dengan menggunakan indeks, penyembunyi pengoptimuman boleh digunakan dalam pertanyaan untuk meningkatkan prestasi sistem; >2.1.2 Kelemahan Indeks

Mencipta dan menyelenggara indeks mengambil masa, dan kali ini meningkat dengan pertambahan bilangan Pengindeksan memerlukan Menduduki ruang fizikal, sebagai tambahan kepada ruang data yang diduduki oleh jadual data, setiap indeks juga menduduki jumlah ruang fizikal tertentu Jika indeks berkelompok diwujudkan, ruang yang diperlukan akan menjadi lebih besar; memadam dan mengubah suai, indeks juga perlu dikekalkan, yang mengurangkan kelajuan penyelenggaraan data; ia Tiada sekatan )

(2) Indeks unik (nilai lajur indeks mestilah unik, tetapi nilai nol dibenarkan.)

(3) Indeks kunci utama (indeks unik khas yang tidak membenarkan nilai nol) . Secara amnya, indeks kunci utama dicipta pada masa yang sama semasa mencipta jadual )
1. (4) Indeks gabungan
(5) Indeks berkelompok membina pokok B mengikut. kunci utama setiap jadual, dan nod daun menyimpan keseluruhan jadual. Baris jadual rekod data, jadi nod daun indeks berkelompok menjadi halaman data.
2. (6) Indeks tidak berkelompok (indeks tambahan) (nod halaman tidak menyimpan keseluruhan baris rekod).
3. 2.3 Ketaksahan Indeks
5. (1) Jika ada atau dalam keadaan, walaupun ada syarat dengan indeks, ia tidak akan digunakan (guna atau sesedikit mungkin);
(2) Pertanyaan suka bermula dengan %, contohnya, PILIH * DARI jadual saya WHEREt Nama seperti'�min';

(3) Jika jenis lajur ialah rentetan, ia mesti disertakan dalam tanda petikan dalam keadaan, jika tidak, ia tidak akan menggunakan indeks; > Perbandingan tiga jenis enjin

yang biasa digunakan: Indeks myISAM Indeks Innodb Indeks memori Indeks B-Tree menyokong sokongan

Indeks hash tidak menyokong sokongan

Sokongan indeks R-Tree tidak sokongan
1. Indeks TEKS penuh tidak menyokong sementara Tidak disokong    Tidak disokong
2. 2.5 Struktur indeks dalam pangkalan data

Kerana apabila menggunakan pokok binari, I/O membaca dan menulis terlalu kerap disebabkan kedalaman pepohon binari yang berlebihan, yang membawa kepada kecekapan pertanyaan yang rendah. Oleh itu, menggunakan struktur pokok pelbagai hala, pelbagai operasi pada pokok B boleh mengekalkan ketinggian pokok B yang rendah.

B-tree juga dipanggil pepohon carian berbilang laluan seimbang Ciri-ciri pokok B-tertib-m adalah seperti berikut:

• 1. Setiap nod dalam pokok mengandungi paling banyak m anak (m>=2);
• 2. Kecuali nod akar dan nod daun, setiap nod mempunyai sekurang-kurangnya (ceil (m/2)) kanak-kanak (dengan siling (x) ialah fungsi yang mengambil had atas);
• 3. Nod akar);
• 4. Semua nod daun muncul dalam lapisan yang sama, dan nod daun tidak mengandungi sebarang maklumat kata kunci (boleh dianggap sebagai nod luaran atau nod di mana pertanyaan gagal, menunjuk ke nod ini. daripada semua titik adalah nol); (Nota: Nod daun hanya tidak mempunyai anak dan penunjuk kepada kanak-kanak. Nod ini juga wujud dan mempunyai unsur. Sama seperti pokok merah-hitam, setiap penunjuk nol dianggap sebagai nod daun, tetapi terdapat tidak Lukis sahaja)
  
  B tree
  
  Dalam keadaan apakah ia sesuai untuk membina indeks?
  (1) Untuk kata kunci yang sering muncul dalam Buat indeks pada medan mengikut susunan mengikut, kumpulan mengikut, berbeza
  (2) Buat indeks pada medan set hasil kesatuan dan operasi set lain adalah sama seperti di atas;
  (3) sering digunakan Tanya medan yang dipilih dan buat indeks
  (4) Buat indeks pada atribut yang sering digunakan sebagai pautan jadual; data jarang dikemas kini Jika pengguna sering Untuk bertanya hanya beberapa medan, anda boleh mempertimbangkan untuk membuat indeks pada medan ini untuk menukar imbasan jadual kepada imbasan indeks.
  

3. Urutan sintaks Mysql

Iaitu, apabila kata kunci berikut wujud dalam sql, mereka mesti mengekalkan susunan ini:

pilih[distinct ] , dari, sertai (seperti kiri sertai), pada, di mana, kumpulan

oleh, mempunyai, kesatuan, perintah mengikut, had; Iaitu, apabila melaksanakan, sql dilaksanakan dalam susunan berikut:

dari, pada, sertai, di mana, kumpulan mengikut, mempunyai, pilih, berbeza, kesatuan, susunan mengikut

kumpulan mengikut dan Fungsi pengagregatan digunakan bersama,

Contohnya:

untuk melaksanakan pertanyaan berbilang jadual (cantuman dalam)

Menggunakan

juga boleh mencapai

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

5 Prosedur tersimpan

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

select from wherePanggil prosedur tersimpan

select u.uname,a.addr from lm_user u, lm_addr a where u.uid = a.uid;

Lihat prosedur tersimpan

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

6. . Wujudkan hubungan jadual data many-to-many

call procedure_bill();

Dalam pangkalan data, jika hubungan antara dua jadual ialah hubungan banyak-ke-banyak, seperti: "jadual jadual pelajar dan jadual kursus", pelajar boleh memilih berbilang kursus, satu kursus Ia juga boleh dipilih oleh berbilang pelajar mengikut prinsip reka bentuk pangkalan data, jadual persatuan ketiga harus dibentuk.

Langkah 1: Cipta tiga jadual data Pelajar, Kursus, Stu_Cour

show procedure status like 'procedure_bill';

Langkah 2: Tambahkan kunci asing pada jadual berkaitan Stu_Cour

Selesaikan penciptaan!

/**学生表*/
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)
)

Nota: Tambahkan kunci asing pada jadual data yang ditambahkan:

- Sintaks:

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

;

Contoh:
• 
  alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名) 7. Enjin pangkalan data (enjin storan)

Apabila anda mengakses pangkalan data, sama ada capaian manual atau capaian program, anda tidak terus membaca dan menulis fail pangkalan data, tetapi mengakses fail pangkalan data melalui enjin pangkalan data.

alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id);Ambil pangkalan data hubungan sebagai contoh Hantar pernyataan SQL kepada enjin pangkalan data Enjin pangkalan data mentafsir pernyataan SQL, mengekstrak data yang anda perlukan dan mengembalikannya kepada anda. Oleh itu, kepada pelawat, enjin pangkalan data adalah penterjemah pernyataan SQL.

7.1 Perbezaan antara enjin MYISAM dan InnoDB

Perbezaan utama:

tidak selamat dari segi transaksi, manakala

Ia selamat untuk transaksi;

butiran kunci ialah penguncian peringkat jadual, manakala
• menyokong penguncian peringkat baris; MYISAM tidak Menyokong indeks teks penuh InnoDB
• agak mudah, jadi ia lebih baik daripada NYISAM dari segi kecekapan aplikasi kecil boleh mempertimbangkan untuk menggunakan InnoDB
• jadual disimpan sebagai fail Borang, menggunakan storan MYISAM dalam pemindahan data merentas platform akan menjimatkan banyak masalah; (6) Jadual InnoDB lebih selamat daripada
jadual dan boleh memastikan bahawa data tidak hilang Dalam kes ini, tukar jadual bukan transaksi kepada jadual urus niaga
• MYISAMInnoDBSenario aplikasi: MYISAM.
  • MYISAM mengurus jadual bukan urus niaga Ia menyediakan storan dan pengambilan semula berkelajuan tinggi, serta keupayaan carian teks penuh Jika aplikasi perlu melakukan sejumlah besar pertanyaan select, maka MYISAM ialah pilihan yang lebih baik.
  • InnoDB untuk aplikasi pemprosesan transaksi dengan pelbagai ciri termasuk ACID sokongan transaksi. Jika aplikasi anda perlu melaksanakan sejumlah besar operasi insert atau update, anda harus menggunakan innodb, yang boleh meningkatkan prestasi operasi serentak berbilang pengguna.

  8. Paradigma pangkalan data

  Pada masa ini terdapat 6 paradigma dalam pangkalan data hubungan: bentuk normal pertama {1NF}, bentuk normal kedua {2NF}, bentuk normal ketiga {3NF}, Bath —Kod bentuk normal {BCNF}, bentuk normal keempat {4NF}, bentuk normal kelima {5NF, juga dikenali sebagai bentuk normal sempurna}. Paradigma yang memenuhi keperluan minimum ialah bentuk normal pertama. Berdasarkan bentuk normal pertama, bentuk yang memenuhi lebih banyak keperluan standard dipanggil bentuk normal kedua {2NF}, dan paradigma lain mengikutinya Secara umumnya, pangkalan data hanya perlu memenuhi bentuk normal ketiga (3NF). .
  Bentuk biasa:

  • 1NF: Pastikan setiap lajur kekal atom
  • 2NF: Pastikan setiap lajur dalam jadual berkaitan dengan kunci primer (kunci primer bersama) ;
  • 3NF: Pastikan setiap lajur dalam jadual berkaitan secara langsung dengan kunci utama (kunci asing); subset kunci utama (hapuskan pergantungan pada subset kunci primer berdasarkan 3NF) Pergantungan pada subset kod utama
  • 4NF: memerlukan pemadaman hubungan banyak-ke-banyak dalam yang sama); jadual;
  • 5NF: menetapkan semula struktur asal daripada struktur akhir;

Atas ialah kandungan terperinci Rizab titik pengetahuan asas pangkalan data MySQL (ringkasan tersusun). Untuk maklumat lanjut, sila ikut artikel berkaitan lain di laman web China PHP!