Kami menganalisis database dan bahasa SQL. (Bagian 3) - "Proyek Java dari A sampai Z"

Artikel dari seri tentang membuat proyek Java (tautan ke materi lain ada di akhir). Tujuannya adalah menganalisis teknologi utama, dan hasilnya adalah menulis bot telegram.

• Memeriksa pekerjaan rumah
  • Menambahkan kunci utama
  • Menambahkan Moldova
  • Buat tabel kota
  • Tambahkan kunci asing ke kota
  • Bagian bonusnya. Pengujian
• pernyataan PILIH
  • parameter DIMANA
  • PESANAN BERDASARKAN parameter
  • KELOMPOK BERDASARKAN parameter
• Pekerjaan rumah
• Kesimpulan

Halo bapak dan ibu sekalian, mari kita lanjutkan membahas tentang database, SQL dan lain-lain. Materi hari ini akan berisi sebagian teori dan sebagian praktik. Izinkan saya mengingatkan Anda bahwa terakhir kali kita berbicara tentang cara mengatur semuanya, cara membuat database, tabel, dan mendapatkan data darinya. Saatnya untuk melihat apakah ada yang berhasil dengan penginderaan jauh. Menurut saya, separuhnya bisa dilakukan hanya berdasarkan artikel sebelumnya. Ternyata untuk merakit aplikasi dengan benar dan membuat semuanya menjadi lebih atau kurang indah, Anda perlu berbicara tentang database, dan untuk membicarakannya Anda perlu menghabiskan banyak waktu.

Memeriksa pekerjaan rumah

Rasa hormat yang sebesar-besarnya kepada semua orang yang berhasil menyelesaikan tugas. Ini berarti Anda memahami bahwa hanya Anda yang membutuhkan ini dan itu hanya membantu Anda. Bagi yang melalaikan tugas saya, izinkan saya mengingatkan Anda tentang syarat:

1. Anda perlu menambahkan kunci utama (PRIMARY KEY) dari bidang ID ke skema tabel negara.
2. Tambahkan negara lain ke tabel negara - Moldova.
3. Berdasarkan skema artikel sebelumnya, buatlah tabel kota, yang akan berisi semua bidang yang dijelaskan. Nama fieldnya adalah sebagai berikut: id, nama, country_id, populasi.
4. Tambahkan kunci utama ke tabel kota.
5. Tambahkan kunci asing ke tabel kota.

Untuk memulai, mari gunakan bagian pertama artikel sebelumnya dan buka terminal database.

Menambahkan kunci utama

Anda dapat menambahkan kunci utama (PRIMARY KEY) dengan dua cara: segera saat membuat tabel, atau setelah pembuatan, menggunakan ALTER TABLE.

Kunci utama selama pembuatan tabel

Karena kita telah membuat tabel, dan tanpa menghapusnya kita tidak akan dapat menampilkan pendekatan ini dalam database ini, kita cukup membuat database pengujian sementara di mana kita akan melakukan semuanya. Mari masukkan perintah berikut:

• membuat basis data baru:

  tes $CREATE DATABASE;

• buat tabel dengan menambahkan kunci utama:

  $ CREATE TABLE negara(id INT, nama VARCHAR(30), PRIMARY KEY (id));

Secara umum, tidak ada yang rumit. Setelah mendeklarasikan variabel, bagian berikut PRIMARY KEY (id) ditambahkan , di mana nama field yang akan menjadi kunci utama dilewatkan dalam tanda kurung. Dan mari kita lihat bagaimana skema tabel berubah: $ DESC negara; Seperti yang Anda lihat, nilai PRI telah muncul di bidang Key untuk entri id .

Kunci utama setelah pembuatan tabel

Seperti yang saya katakan sebelumnya, kunci pertama setelah membuat tabel dapat ditetapkan menggunakan ALTER TABLE . Kami akan menjalankan contoh ini di database kota kami :

• mari kita pergi ke database kita dari yang tes:

  $USE kota;

• Mari kita periksa apakah kita sudah pasti ada di database kita (harus ada kolom lain di sana - populasi). Untuk melakukan ini kami menulis:

  $DESC populasi;

semuanya benar, meja itu milik kita. Mari kita tulis yang berikut ini:

$ ALTER TABLE negara TAMBAHKAN KUNCI UTAMA (id);

• dan segera periksa dengan perintah:

  $DESC negara;

Terlihat dari gambar, semuanya sudah benar, nilai PRI sudah sesuai dengan yang seharusnya. Omong-omong, kami bekerja dengan database pengujian. Sekarang kita perlu menghapusnya: mengapa kita perlu mengacaukan server, bukan? Untuk melakukan ini, kami menggunakan perintah yang cukup terkenal: $ DROP DATABASE test;

Menambahkan Moldova

Pertama kita perlu memutuskan apa yang akan kita rekam. ID kita berikutnya adalah 4. Namanya adalah Moldova, dan populasinya adalah 3550900. Oleh karena itu, kita jalankan perintah INSERT INTO yang sudah kita ketahui: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); Dan kami memeriksa apakah nilai ini persis ada di database: $ SELECT * FROM country WHERE id = 4; Dalam permintaan data saya langsung menentukan field mana yang akan dicari, sehingga kita hanya mendapat satu record saja yang kita butuhkan.

Buat tabel kota

Dengan menggunakan diagram dari artikel pertama tentang database, kami memperoleh informasi yang diperlukan tentang tabel. Ini akan berisi bidang-bidang berikut:

• id - pengidentifikasi unik;
• nama — nama kota;
• country_id — kunci asing negara;
• populasi — populasi kota.

Agak menegangkan untuk selalu menulis ID unik, bukan? Saya ingin menyerahkan hal ini kepada pihak berwenang MySQL . Dan ada caranya - PENINGKATAN OTOMATIS . Kita perlu menambahkan ini ke bidang digital, dan jika kita tidak meneruskan nilainya secara eksplisit, MySQL sendiri akan menambah ID sebanyak satu dibandingkan dengan yang sebelumnya. Oleh karena itu, pembuatan tabel akan terlihat seperti ini: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, Population INT, PRIMARY KEY (id)); Mari kita lihat diagram tabel untuk melihat apakah semuanya telah dilakukan dengan benar: $ DESC city; Seperti yang Anda lihat dari diagram tabel, kami memiliki deskripsi baru untuk bidang id - kenaikan_otomatis. Jadi kami melakukan segalanya dengan benar. Mari kita periksa data pada tabel yang dikonfigurasi sepenuhnya. Untuk melakukan ini, kita akan melakukan bagian terakhir dari tugas - kunci asing.

Tambahkan kunci asing ke kota

Untuk kunci asing akan ada perintah ini: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); Dan mari kita segera periksa apa yang salah dengan skema tabel: apakah sudah berubah dalam waktu satu jam? $DESC kota;

Bagian bonusnya. Pengujian

Saya lupa menambahkannya ke tugas - mengisi data yang ada di tangkapan layar bagian pertama. Saya lupa, jadi sekarang saya akan melakukannya sendiri. Dan bagi yang berminat bisa melakukannya sendiri tanpa saya lalu kita periksa ;) Ada Kharkov, Kyiv, Minsk, Odessa, Voronezh, dan kami juga akan menambahkan Chisinau. Tapi kali ini kami tidak akan mengirimkan ID, kami akan melewatkannya: $ INSERT INTO city (nama, country_id, populasi) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Seperti yang Anda lihat, Anda dapat membuat beberapa entri secara bersamaan menggunakan satu perintah INSERT INTO. Hal yang berguna, ingat) Dan segera mari kita lihat apa yang ada di tabel: $ SELECT * FROM city; AUTO_INCREMENT - bekerja persis seperti yang kami inginkan. Berkas ID sudah terisi semua, padahal kami tidak menyerahkannya. Kunci asing adalah hal yang bergantung. Untuk memeriksa apakah berfungsi dengan benar, Anda dapat mencoba menulis kunci asing yang tidak ada di tabel asing. Katakanlah kita memutuskan bahwa id = 5 adalah Kazakhstan. Namun pada kenyataannya hal ini tidak ada dalam tabel negara. Dan untuk memeriksa apakah database akan bersumpah, tambahkan kota - Astana: $ INSERT INTO city (nama, country_id, populasi) VALUES ('Astana', 5, 1136156); Dan tentu saja kita mendapatkan kesalahan: Sekarang kunci asing memastikan bahwa kita tidak mencoba menetapkan negara ke kota yang tidak ada dalam database kita. Bagian pekerjaan rumah ini dapat dianggap selesai - lanjutkan ke yang baru :)

pernyataan PILIH

Ya, semuanya tidak terasa menakutkan lagi, bukan? Saya ingin mencatat sekali lagi bahwa bagi pengembang Java, pengetahuan tentang database adalah suatu keharusan. Tanpa database Anda tidak bisa kemana-mana. Ya, saya sudah ingin mulai menulis lamaran, saya setuju. Tapi itu perlu. Jadi kami akan terus seperti ini. Menggunakan pernyataan SELECT, kami mengambil data dari database. Artinya, ini adalah operasi DML yang khas (apakah Anda lupa apa itu?...))) Baca kembali artikel SEBELUM). Apa manfaat dari database relasional? Mereka memiliki fungsionalitas hebat untuk mengumpulkan dan mengambil data. Untuk inilah pernyataan SELECT digunakan. Tampaknya tidak ada yang rumit dalam hal ini, bukan? Tapi ternyata masih banyak yang harus dipahami) Penting bagi kita untuk memahami dasar-dasar yang bisa kita gunakan untuk membangun. Kueri paling sederhana dengan pernyataan SELECT adalah memilih semua data dari satu tabel. Saya sangat menyukai deskripsi dari wiki tentang urutan apa yang harus dilakukan operator dalam kueri SELECT, jadi saya akan dengan berani menyalinnya di sini:

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], ...]

Di sini Anda dapat melihat bahwa Anda tidak dapat menempatkan operator GROUP BY terlebih dahulu dan kemudian operator WHERE. Hal ini perlu diingat agar nantinya tidak ada rasa dendam atas kesalahan yang tidak jelas asalnya. $PILIH * DARI kota; Namun menghapus semua data jelas tidak menyenangkan bagi kami. Hal yang sama terjadi jika kita ingin memalu paku dengan mikroskop [1] , [2] . Karena database melakukan operasi pemfilteran, pengurutan, dan agregasi jauh lebih cepat daripada kode Java, lebih baik menyerahkan masalah ini ke database. Oleh karena itu, dengan memperumit tugas, kami akan membuka fungsionalitas baru.

parameter DIMANA

Untuk memfilter pilihan, kata WHERE digunakan . Ini harus ditafsirkan sebagai berikut: SELECT * FROM tablename (pilih semua field dari table tablename) WHERE talbe_row = 1 (di mana dalam catatan field table_row sama dengan 1). Penting untuk dicatat bahwa urutan kata kunci dalam kueri itu penting. Anda tidak dapat menulis WHERE a =1 FROM table_name SELECT *. Untuk bahasa Rusia hal ini tidak masalah, dan bagi sebagian orang hal ini mungkin tidak terlalu buruk, tetapi untuk SQL hal ini tidak dapat diterima. Kami menulis query berikut: $ SELECT * FROM city WHERE country_id = 1; Dan kami memilih kota-kota Ukraina. Tidak buruk, bukan? Bagaimana jika kita tidak hanya menginginkan bahasa Ukraina, tetapi juga Belarusia? Untuk tujuan ini, kita dapat membuat daftar kumpulan nilai yang dapat diambil oleh bidang tersebut: $SELECT * FROM city WHERE country_id IN(1, 3); Dan kami sudah memiliki kota-kota dari dua negara sebagai tanggapannya. Bagaimana jika ada beberapa kondisi yang perlu difilter? Katakanlah kita menginginkan kota dengan jumlah penduduk lebih dari dua juta? Untuk melakukan ini, gunakan kata OR dan AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND populasi > 2000000; Bagus, tapi bagaimana jika kita perlu menambahkan satu syarat lagi - untuk mencari nama melalui ekspresi reguler (saya tidak akan menjelaskan ekspresi reguler di sini: inilah orang yang melakukan ini "secara singkat" dalam 4 bagian )? Misalnya, kita ingat cara mengeja sebuah kota, tetapi tidak sepenuhnya... Untuk melakukan ini, Anda dapat menambahkan kata kunci LIKE ke ekspresi pemfilteran : $ SELECT * FROM city WHERE country_id IN (1, 3) AND populasi > 2000000 ATAU nama SEPERTI “%hark%”; Dan dengan cara ini kami juga mendapatkan Kharkov. Hasilnya, kami dapat mengatakan bahwa pencarian kami ternyata sangat bagus. Tapi saya ingin mengurutkan bukan berdasarkan ID, tapi berdasarkan populasi, tapi bagaimana caranya? Ya, sangat sederhana...

PESANAN BERDASARKAN parameter

Dengan menggunakan ORDER BY, kita dapat mengurutkan catatan yang kita terima berdasarkan bidang tertentu. Ini mengurutkan angka dan string. Mari kita perluas kueri sebelumnya, urutkan berdasarkan populasi, tambahkan ORDER BY populasi: $ SELECT * FROM city WHERE country_id IN (1, 3) AND populasi > 2000000 ATAU nama SEPERTI “%hark%” ORDER BY populasi; Seperti yang bisa kita lihat, pengurutan terjadi secara alami, yaitu dalam urutan menaik. Bagaimana jika kita menginginkan yang sebaliknya? Untuk melakukan ini, Anda perlu menambahkan kata DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) AND populasi > 2000000 ATAU nama SEPERTI “%hark%” ORDER BY populasi DESC; Kini pemilahannya didasarkan pada pengurangan populasi. Dan database melakukan ini dengan sangat cepat: tidak ada Collections.sort yang dapat dibandingkan. Sekarang mari kita urutkan berdasarkan baris, berdasarkan nama dalam urutan terbalik: $ SELECT * FROM city WHERE country_id IN (1, 3) AND Population > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

KELOMPOK BERDASARKAN parameter

Digunakan untuk mengelompokkan catatan berdasarkan bidang tertentu. Ini biasanya diperlukan untuk menggunakan fungsi agregat... Apa itu fungsi agregat?)) Masuk akal untuk mengelompokkan berdasarkan beberapa bidang jika bidang tersebut sama untuk catatan yang berbeda. Mari kita lihat apa artinya menggunakan contoh kita. Misalkan kota mempunyai kunci asing - ID negara. Jadi, ID-nya sama untuk kota-kota dari negara yang sama. Oleh karena itu, Anda dapat mengambil dan mengelompokkan catatan berdasarkan: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; Namun tanpa menggabungkan fungsi-fungsinya, Anda harus mengakuinya terlihat agak kurang bagus. Oleh karena itu, mari kita lihat beberapa fungsi yang paling umum:

• COUNT — jumlah record, dapat digunakan tanpa pengelompokan, digunakan sebagai COUNT(*) . Dalam hal pengelompokan berdasarkan beberapa bidang - COUNT(groupped_field);
• MAX - menemukan nilai maksimum untuk bidang tertentu;
• MIN - menemukan nilai minimum untuk bidang tertentu;
• SUM - menemukan jumlah untuk bidang tertentu;
• AVG - menemukan nilai rata-rata.

Secara umum fungsi-fungsi ini dapat digunakan tanpa pengelompokan, hanya dengan satu field saja yang akan ditampilkan. Mari kita coba untuk populasi kota kita: $ SELECT COUNT(*) FROM city; Apa yang mereka minta adalah apa yang mereka dapatkan. Hanya jumlah catatan. Terkadang ini berguna. Misalnya saja jika kita ingin mengetahui jumlah artikel dari penulis tertentu. Tidak perlu mengeluarkannya dari database dan menghitungnya. Anda cukup menggunakan COUNT(). $ PILIH AVG(populasi) DARI kota; $ PILIH MIN(populasi) DARI kota; Dan di sinilah pengelompokan mulai berlaku. Misalnya, tugasnya adalah mendapatkan kota terkecil di negara tersebut. Sudah tahu cara melakukan ini? Cobalah sendiri, lalu tonton: $ SELECT country_id as Country, MIN(population) FROM city WHERE GROUP BY country_id; Sejauh ini kami hanya melihat nomor ID negaranya, tapi itu tidak masalah - lain kali kami akan melakukan semuanya. Jadi sudah ada hasilnya, dan kami mendapatkan apa yang kami inginkan - kota terkecil di negara ini dengan ID = 1. Fungsi lainnya akan sama. Penting untuk dicatat bahwa menyapu semua bidang melalui * saat menggunakan pengelompokan dan agregasi tidak akan berhasil! Pikirkan tentang itu ;)

Pekerjaan rumah

Berdasarkan hasil artikel sebelumnya sudah terlihat PR-nya sudah selesai, jadi ayo lanjutkan)) Ya, semua yang mengerjakan PR akan terus memberi tanda “+” di kolom komentar. Penting bagi saya bahwa topik pekerjaan rumah menarik bagi Anda sehingga saya terus mengerjakannya di masa depan. Ya, saya membaca komentar Anda secara teratur. Tentu saja, saya lebih jarang menjawab. Saya melihat mereka meminta untuk memberikan masalah SQL yang lebih sulit. Sampai kita belajar join, tidak akan ada soal yang menarik, jadi ada saja yang saya perlukan untuk materi selanjutnya.

Tugas:

Pahami operator HAVING dan tulis contoh kueri untuk tabel dari contoh kita. Jika Anda perlu menambahkan beberapa bidang atau lebih banyak nilai agar lebih jelas, tambahkan saja. Jika ada yang mau, tulis contoh solusi Anda di komentar: dengan cara ini saya juga bisa memeriksanya jika saya punya waktu.
1. Instal MySQL Workbench untuk bekerja dengan database melalui UI. Saya rasa kami sudah cukup berlatih bekerja dari konsol. Hubungkan ke basis data. Jika Anda menggunakan sesuatu yang lain untuk bekerja dengan database, silakan lewati tugas ini. Di sini dan selanjutnya saya hanya akan menggunakan MySQL Workbench.
2. Tulis permintaan untuk menerima menggunakan data kami:
   1. negara terkecil/terpadat;
   2. jumlah rata-rata penduduk di negara tersebut;
   3. jumlah rata-rata penduduk di negara-negara yang namanya diakhiri dengan “a”;
   4. jumlah negara dengan jumlah penduduk lebih dari empat juta;
   5. mengurutkan negara berdasarkan penurunan jumlah penduduk;
   6. mengurutkan negara berdasarkan nama dalam urutan alami.

Kesimpulan

Hari ini kita membahas secara rinci pekerjaan rumah dari pelajaran terakhir. Selain itu, saya menganggap hal ini penting baik bagi mereka yang tidak melakukannya maupun bagi mereka yang melakukannya. Bagi yang pertama, ini adalah kesempatan untuk menemukan jawabannya, dan bagi yang kedua, untuk membandingkannya dengan hasil Anda. Berlangganan ke akun GitHub saya untuk terus mendapatkan informasi terbaru tentang perubahan proyek. Saya akan mempertahankan seluruh basis kode di sana. Semuanya akan terjadi di organisasi ini . Selanjutnya, kita membahas pernyataan SELECT. Dia yang paling penting bagi kami. Melalui itulah semua permintaan data akan dilalui, dan kita harus memahaminya. Yang paling penting adalah mengingat urutan penambahan parameter (WHERE, ORDER BY, GROUP BY, dan seterusnya). Ya, saya tidak memberi tahu semua yang mungkin, tetapi saya tidak menetapkan tujuan seperti itu untuk diri saya sendiri. Ya, saya tahu Anda sudah bersemangat untuk menulis lamaran. Bersabarlah, hanya ini yang Anda butuhkan. Baik untuk proyek maupun untuk pertumbuhan profesional Anda. Sementara Anda menunggu, pastikan Git sudah Anda kenal. Saya akan menggunakannya secara default, sebagai alat yang terkenal. Terima kasih semuanya telah membaca. Pada artikel selanjutnya kita akan membahas tentang koneksi dan penggabungan database. Di situlah tugas-tugas kerennya))

Daftar semua materi dalam seri ini ada di awal artikel ini.