Kita nganalisa database lan basa SQL. (Part 3) - "Proyek Jawa saka A nganti Z"

Artikel saka seri babagan nggawe proyek Jawa (pranala menyang materi liyane ana ing pungkasan). Tujuane kanggo nganalisa teknologi kunci, asile nulis bot telegram.

• Priksa PR
  • Nambahake kunci utama
  • Nambahake Moldova
  • Nggawe tabel kutha
  • Tambah kunci asing menyang kutha
  • bagean bonus. Testing
• PILIH statement
  • Parameter WHERE
  • ORDER BY parameter
  • GROUP BY parameter
• Peer
• Kesimpulan

Halo, Bapak-bapak, ayo padha ngrembug babagan database, SQL lan liya-liyane. Materi dina iki bakal ngemot teori bagean lan praktik. Ayo kula ngelingake sampeyan yen pungkasan kita ngomong babagan carane nyetel kabeh, carane nggawe database, tabel lan entuk data saka iku. Iku wektu kanggo ndeleng apa ana sing bisa digunakake karo remote sensing. Miturut pendapatku, separo bisa ditindakake mung adhedhasar artikel sadurunge. Ternyata supaya bisa ngumpulake aplikasi kanthi bener lan nggawe kabeh luwih apik utawa luwih apik, sampeyan kudu ngomong babagan database, lan kanggo ngomong babagan sampeyan kudu nglampahi akeh wektu.

Priksa PR

Ngaturaken agunging panuwun dhumateng sedaya ingkang sampun kasil ngrampungaken tugas. Iki tegese sampeyan ngerti yen sampeyan mung butuh iki lan mung mbantu sampeyan. Kanggo sing wis nglirwakake tugasku, keparenga kula ngelingake sampeyan babagan syarat:

1. Sampeyan kudu nambah kunci utama (KEY PRIMARY) saka kolom ID menyang skema tabel negara.
2. Tambah negara liyane menyang meja negara - Moldova.
3. Miturut skema artikel sadurunge, nggawe kutha meja, sing bakal ngemot kabeh lapangan sing diterangake. Jeneng lapangan bakal kaya ing ngisor iki: id, jeneng, id_negara, populasi.
4. Tambah kunci utami ing meja kutha.
5. Tambah tombol manca kanggo meja kutha.

Kanggo miwiti, ayo gunakake bagean pisanan saka artikel sadurunge lan pindhah menyang terminal database.

Nambahake kunci utama

Sampeyan bisa nambah kunci utama (PRIMER KEY) kanthi rong cara: langsung nalika nggawe tabel, utawa sawise nggawe, nggunakake ALTER TABLE.

Tombol utama sajrone nggawe tabel

Amarga kita wis nggawe tabel, lan tanpa mbusak, kita ora bakal bisa nuduhake pendekatan iki ing database iki, kita mung bakal nggawe database test sementara sing bakal ditindakake kabeh. Ayo ketik printah ing ngisor iki:

• nggawe database anyar:

  tes $CREATE DATABASE;

• nggawe tabel nambah kunci utama:

  $ Gawe tabel negara(id INT, jeneng VARCHAR(30), KUNCI UTAMA (id));

Umumé, ora ana sing rumit. Sawise ngumumake variabel, bagean ing ngisor iki KUNCI UTAMA (id) ditambahake , ing ngendi jeneng kolom sing bakal dadi kunci utama diterusake ing kurung. Lan ayo kang ndeleng carane skema tabel wis diganti: $ DESC negara; Kaya sing sampeyan ngerteni, nilai PRI wis katon ing kolom Key kanggo rekaman id .

Tombol utama sawise nggawe tabel

Kaya sing dakkandhakake sadurunge, tombol pisanan sawise nggawe tabel bisa ditugasake nggunakake ALTER TABLE . Kita bakal mbukak conto iki ing database kutha kita :

• ayo menyang database kita saka test siji:

  $USE kutha;

• Priksa manawa kita mesthi ana ing database kita (mesti ana lapangan liyane ing kana - populasi). Kanggo nindakake iki, kita nulis:

  $ populasi DESC;

kabeh iku bener, meja iku kita. Ayo nulis ing ngisor iki:

$ ALTER TABLE negara ADD PRIMARY KEY (id);

• lan priksa langsung nganggo printah:

  $DESC negara;

Nalika sampeyan bisa ndeleng saka gambar, kabeh iku bener, Nilai PRI persis ngendi iku kudu. Miturut cara, kita makarya karo database test. Saiki kita kudu mbusak: kenapa kita kudu clutter server, ta? Kanggo nindakake iki, kita nggunakake printah cukup kondhang: $ DROP DATABASE test;

Nambahake Moldova

Kaping pisanan, kita kudu mutusake apa sing bakal direkam. ID sabanjure yaiku 4. Jenenge Moldova, lan populasi 3550900. Mula, kita nglakokake perintah INSERT INTO sing wis kita kenal: $ INSERT INTO NILAI negara (4, 'Moldova', 3550900); Lan kita mriksa apa nilai iki persis ing database: $ SELECT * FROM negara WHERE id = 4; Ing panyuwunan data, aku langsung nemtokake lapangan sing bakal digoleki, mula mung entuk siji rekaman, yaiku sing dibutuhake.

Nggawe tabel kutha

Nggunakake diagram saka artikel pisanan babagan database, kita entuk informasi sing dibutuhake babagan tabel. Bakal ngemot kolom ing ngisor iki:

• id - pengenal unik;
• jeneng - jeneng kutha;
• country_id — kunci asing negara;
• populasi — populasi kutha.

Iku rada ngepenakke kanggo nulis ID unik saben wektu, ora mikir? Aku pengin ninggalake iki kanggo panguwasa MySQL . Lan ana cara kasebut - AUTO INCREMENT . Kita kudu nambahake iki menyang lapangan digital, lan yen kita ora ngetrapake nilai kasebut kanthi jelas, MySQL dhewe bakal nambah ID siji dibandhingake sadurunge. Mulane, nggawe tabel bakal katon kaya iki: $ CREATE TABLE kutha (id INT AUTO_INCREMENT, jeneng VARCHAR(30), country_id INT, populasi INT, PRIMARY KEY (id)); Ayo katon ing diagram meja kanggo ndeleng apa kabeh wis rampung bener: $ DESC kutha; Nalika sampeyan bisa ndeleng saka diagram meja, kita duwe gambaran anyar kanggo lapangan id - auto_increment. Dadi kita nindakake kabeh kanthi bener. Ayo dipriksa data ing tabel sing wis dikonfigurasi kanthi lengkap. Kanggo nindakake iki, kita bakal nindakake bagean pungkasan saka tugas - tombol manca.

Tambah kunci asing menyang kutha

Kanggo kunci manca bakal ana prentah iki: $ ALTER TABLE kutha TAMBAHKAN KUNCI ASING (country_id) REFERENSI negara(id); Lan ayo langsung mriksa apa sing salah karo skema meja: wis diganti sajrone jam? $DESC kutha;

bagean bonus. Testing

Aku kelalen kanggo nambah menyang tugas - isi data sing ana ing gambar saka bagean pisanan. Aku lali, mula saiki aku bakal nindakake dhewe. Lan kanggo wong-wong sing kasengsem, sampeyan bisa nindakake dhewe tanpa kula lan banjur kita bakal mriksa;) Ana Kharkov, Kyiv, Minsk, Odessa, Voronezh, lan kita uga bakal nambah Chisinau. Nanging wektu iki kita ora ngirim ID, kita bakal ngliwati: $ INSERT INTO kutha (jeneng, country_id, populasi) NILAI ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Nalika sampeyan bisa ndeleng, sampeyan bisa nggawe sawetara entri bebarengan nggunakake siji INSERT INTO printah. A bab Handy, elinga) Lan langsung ayo kang ndeleng apa ing meja: $ SELECT * FROM kutha; AUTO_INCREMENT - kerjane persis kaya sing dikarepake. File ID wis diisi kabeh, sanajan kita ora ngirim. Kunci asing minangka barang sing gumantung. Kanggo mriksa apa kerjane kanthi bener, sampeyan bisa nyoba nulis kunci manca sing ora ana ing tabel manca. Ayo kita mutusake yen id = 5 yaiku Kazakhstan. Nanging nyatane ora ana ing tabel negara. Lan kanggo mriksa yen database bakal sumpah, nambah kutha - Astana: $ INSERT INTO kutha (jeneng, country_id, populasi) VALUES ('Astana', 5, 1136156); Lan kita alami kesalahan: Saiki kunci asing nggawe manawa kita ora nyoba nemtokake negara menyang kutha sing ora ana ing database kita. Bagean peer iki bisa dianggep rampung - maju menyang sing anyar :)

PILIH statement

Ya, kabeh ora katon medeni maneh, ta? Aku kaya kanggo Wigati sepisan maneh sing kanggo pangembang Jawa, kawruh saka database iku kudu duwe. Tanpa database sampeyan ora bisa menyang ngendi wae. Ya, aku wis arep miwiti nulis lamaran, aku setuju. Nanging perlu. Dadi kita bakal terus kanthi cara iki. Nggunakake statement SELECT, kita njupuk data saka database. Yaiku, iki minangka operasi DML sing khas (apa sampeyan wis lali apa iku?...))) Waca maneh artikel sadurunge). Apa keuntungan saka database relasional? Dheweke duwe fungsi sing apik kanggo nglumpukake lan njupuk data. Iki digunakake kanggo statement SELECT. Kayane ora ana sing rumit, ta? Nanging ternyata isih akeh sing kudu dimangerteni) Penting kanggo kita ngerti dhasar sing bisa kita bangun. Pitakonan paling gampang karo statement SELECT yaiku milih kabeh data saka siji tabel. Aku seneng banget karo katrangan saka wiki babagan persis apa sing kudu ditindakake operator ing pitakon PILIH, mula aku bakal nyalin ing kene:

SELECT
  [DISTINCT | DISTINCTROW | ALL]
  select_expression,...
FROM table_references
[WHERE where_definition]
[GROUP BY {unsigned_integer | col_name | formula}]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC], ...]

Ing kene sampeyan bisa ndeleng manawa sampeyan ora bisa nyelehake operator GROUP BY dhisik banjur operator WHERE. Iki perlu dieling-eling supaya mengko ora ana rasa gela marang kesalahan sing ora jelas asale saka ngendi. $SELECT * FROM kutha; Nanging scraping metu kabeh data cetha ora nyenengake kanggo kita. Iki persis padha yen kita pengin palu kuku nganggo mikroskop [1] , [2] . Wiwit basis data nindakake operasi nyaring, ngurutake lan agregasi luwih cepet tinimbang kode Jawa, luwih becik ninggalake perkara iki menyang database. Mulane, kanthi nggawe rumit tugas, kita bakal mbukak fungsi anyar.

Parameter WHERE

Kanggo nyaring pilihan, tembung WHERE digunakake . Iki kudu diinterpretasikake kaya ing ngisor iki: SELECT * FROM tablename (milih kabeh kolom saka tablename table) WHERE talbe_row = 1 (ngendi ing cathetan kolom table_row padha karo 1). Wigati dicathet yen urutan tembung kunci ing pitakon penting. Sampeyan ora bisa nulis WHERE a = 1 FROM table_name SELECT *. Kanggo basa Rusia iki ora apa-apa, lan kanggo sawetara bisa uga ora katon ala, nanging kanggo SQL iki ora bisa ditampa. Kita nulis pitakon ing ngisor iki: $ SELECT * FROM city WHERE country_id = 1; Lan kita milih kutha Ukrainia. Ora ala, ta? Apa yen kita pengin ora mung Ukrainia, nanging uga Belarusian? Kanggo tujuan iki, kita bisa dhaptar koleksi nilai sing bisa dijupuk ing lapangan: $SELECT * FROM city WHERE country_id IN(1, 3); Lan kita wis duwe kutha saka rong negara kanggo nanggepi. Apa yen ana sawetara kahanan kanggo nyaring? Apa kita pengin kutha kanthi populasi luwih saka rong yuta? Kanggo nindakake iki, gunakake tembung UTAWA lan AND : $ SELECT * FROM city WHERE country_id IN (1, 3) LAN populasi > 2000000; Apik, nanging yen kita kudu nambah siji kondisi liyane - kanggo nelusuri jeneng liwat expression biasa (Aku ora bakal njlèntrèhaké ekspresi reguler kene: kene wong sing nindakake iki "sedhela" ing 4 bagean )? Contone, kita ngelingi carane ngeja kutha, nanging ora rampung ... Kanggo nindakake iki, sampeyan bisa nambah tembung kunci LIKE menyang ekspresi nyaring : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR jeneng LIKE "%hark%"; Lan kanthi cara iki kita uga entuk Kharkov. Akibaté, kita bisa ngomong yen panelusuran kita dadi apik banget. Nanging aku ora pengin ngurutake miturut ID, nanging miturut populasi, nanging kepiye? Ya simple banget...

ORDER BY parameter

Nggunakake ORDER BY, kita bisa ngurutake cathetan sing ditampa miturut lapangan tartamtu. Iku ngurutake nomer lan senar. Ayo nggedhekake pitakon sadurunge, urut miturut populasi, nambah ORDER BY populasi: $ SELECT * FROM kutha WHERE country_id IN (1, 3) LAN populasi > 2000000 UTAWA jeneng kaya “%hark%” ORDER BY populasi; Minangka kita bisa ndeleng, ngurutake dumadi ing urutan alam, yaiku, ing urutan munggah. Apa yen kita pengin ngelawan? Kanggo nindakake iki, sampeyan kudu nambah tembung DESC: $ SELECT * FROM kutha WHERE country_id IN (1, 3) LAN populasi > 2000000 UTAWA jeneng kaya "% hark%" ORDER BY populasi DESC; Saiki ngurutake adhedhasar pengurangan populasi. Lan database nindakake iki cepet banget: ora ana Collections.sort ana bisa dibandhingake. Saiki ayo urutake miturut baris, miturut jeneng kanthi urutan mbalikke: $ SELECT * FROM kutha WHERE country_id IN (1, 3) LAN populasi > 2000000 UTAWA jeneng kaya "%hark%" ORDER BY jeneng DESC;

GROUP BY parameter

Digunakake kanggo nglumpukake cathetan miturut kolom tartamtu. Iki biasane dibutuhake kanggo nggunakake fungsi agregat ... Apa fungsi agregat?)) Iku ndadekake pangertèn kanggo klompok dening sawetara kothak yen padha kanggo cathetan beda. Ayo goleki apa tegese nggunakake conto kita. Ayo ngomong yen kutha duwe kunci asing - ID negara. Dadi, ID padha kanggo kutha saka negara sing padha. Mulane, sampeyan bisa njupuk lan nglumpukake cathetan dening wong-wong mau: $ SELECT country_id, COUNT(*) FROM kutha GROUP BY country_id; Nanging tanpa fungsi agregasi katon rada kurang, sampeyan kudu ngakoni. Dadi, ayo goleki sawetara fungsi sing paling umum:

• COUNT - jumlah rekaman, bisa digunakake tanpa ngelompokake, digunakake minangka COUNT(*) . Ing kasus klompok miturut sawetara lapangan - COUNT(groupped_field);
• MAX - nemokake nilai maksimum kanggo lapangan tartamtu;
• MIN - nemokake nilai minimal kanggo lapangan tartamtu;
• SUM - nemokake jumlah kanggo lapangan tartamtu;
• AVG - nemokake nilai rata-rata.

Umumé, fungsi kasebut bisa digunakake tanpa nglumpukake, mung banjur mung siji lapangan sing bakal ditampilake. Ayo padha nyoba kanggo populasi kutha kita: $ PILIH COUNT(*) Saka kutha; Sing dijaluk kuwi sing ditakoni. Mung nomer cathetan. Kadhangkala iki migunani. Contone, yen kita kudu ngerteni jumlah artikel saka penulis tartamtu. Ora perlu rake metu saka database lan ngetung. Sampeyan mung bisa nggunakake COUNT (). $ PILIH AVG(populasi) Saka kutha; $ SELECT MIN(populasi) FROM kutha; Lan iki ngendi klompok dadi pasukan. Contone, tugas kanggo njaluk kutha paling cilik ing negara. Wis ngerti carane nindakake iki? Coba dhewe, banjur tonton: $ SELECT country_id as Country, MIN(populasi) FROM city WHERE GROUP BY country_id; Nganti saiki kita mung ndeleng ID negara, nanging ora masalah - sabanjure kita bakal nindakake kabeh. Lan wis ana asil, lan kita entuk apa sing dikarepake - kutha paling cilik ing negara kanthi ID = 1. Fungsi liyane bakal padha. Wigati dimangerteni manawa raking metu kabeh lapangan liwat * nalika nggunakake klompok lan agregasi ora bakal bisa! Mikir bab iku ;)

Peer

Adhedhasar asil artikel sadurunge, jelas yen PR lagi ditindakake, mula ayo nerusake)) Ya, saben wong sing nindakake PR bakal terus menehi "+" ing komentar. Penting kanggo kula yen topik PR menarik kanggo sampeyan, supaya aku terus nindakake ing mangsa ngarep. Ya, aku maca komentar sampeyan kanthi rutin. Mesthi, aku mangsuli kurang asring. Aku weruh sing padha takon kanggo menehi masalah SQL luwih angel. Nganti kita sinau gabung, ora bakal ana masalah sing menarik, mula ana sing dibutuhake kanggo materi luwih lanjut.

Tugas:

Ngerti operator HAVING lan nulis conto query kanggo tabel saka conto kita. Yen sampeyan kudu nambah sawetara kolom utawa luwih akeh nilai supaya luwih jelas, tambahake. Yen ana sing pengin, tulisake solusi conto sampeyan ing komentar: kanthi cara iki aku uga bisa mriksa yen aku duwe wektu.
1. Instal MySQL Workbench kanggo nggarap database liwat UI. Aku wis cukup latihan karo nggarap console. Nyambung menyang database. Yen sampeyan nggunakake liya kanggo nggarap basis data, aja lali nglewati tugas iki. Ing kene lan luwih aku bakal nggunakake mung MySQL Workbench.
2. Tulis panjalukan kanggo nampa nggunakake data kita:
   1. negara paling cilik / paling padhet;
   2. jumlah rata-rata pedunung ing negara;
   3. jumlah rata-rata pedunung ing negara sing jenenge diakhiri karo "a";
   4. jumlah negara kanthi populasi luwih saka patang yuta;
   5. ngurutake negara kanthi ngurangi jumlah penduduk;
   6. urut negara miturut jeneng ing urutan alam.

Kesimpulan

Dina iki kita rembugan kanthi rinci babagan peer saka pawulangan pungkasan. Kajaba iku, aku nganggep iki penting kanggo wong sing ora nindakake lan kanggo wong sing nindakake. Kanggo sing sadurunge, iki minangka kesempatan kanggo nemokake jawaban, lan kanggo sing terakhir, mbandhingake karo asil sampeyan. Langganan akun GitHub kanggo tetep nganyari babagan owah-owahan ing proyek kasebut. Aku bakal njaga kabeh basis kode ana. Kabeh bakal kelakon ing organisasi iki . Sabanjure, kita ngrembug statement SELECT. Dheweke sing paling penting kanggo kita. Iku liwat iku kabeh panjalukan kanggo data bakal liwat, lan kita kudu ngerti. Sing paling penting yaiku ngelingi urutan paramèter sing ditambahake (WHERE, ORDER BY, GROUP BY, lan liya-liyane). Ya, aku ora ngandhani kabeh sing bisa ditindakake, nanging aku ora nemtokake tujuan kasebut kanggo aku. Ya, aku ngerti yen sampeyan wis sregep nulis aplikasi. Sabar, iki kabeh sing dibutuhake. Loro-lorone kanggo proyek kasebut lan kanggo pertumbuhan profesional sampeyan. Nalika ngenteni, priksa manawa Git wis kenal karo sampeyan. Aku bakal nggunakake minangka standar, minangka alat kondhang. Matur nuwun kanggo kabeh sing wis maca. Ing artikel sabanjure kita bakal ngomong babagan sambungan database lan gabung. Ing kono bakal dadi tugas sing keren))

Dhaptar kabeh materi ing seri kasebut ana ing wiwitan artikel iki.