Kami menganalisis pangkalan data dan bahasa SQL. (Bahagian 3) - "Projek Java dari A hingga Z"

Artikel daripada siri tentang mencipta projek Java (pautan ke bahan lain ada di penghujung). Matlamatnya adalah untuk menganalisis teknologi utama, hasilnya menulis bot telegram.

• Menyemak kerja rumah
  • Menambah kunci utama
  • Menambah Moldova
  • Cipta jadual bandar
  • Tambahkan kunci asing ke bandar
  • Bahagian bonus. Menguji
• SELECT kenyataan
  • Parameter WHERE
  • PESANAN MENGIKUT parameter
  • KUMPULAN MENGIKUT parameter
• Kerja rumah
• Kesimpulan

Hello, tuan-tuan dan puan-puan, mari kita terus bercakap tentang pangkalan data, SQL dan lain-lain. Bahan hari ini akan mengandungi sebahagian teori dan sebahagian amalan. Izinkan saya mengingatkan anda bahawa kali terakhir kita bercakap tentang cara menyediakan segala-galanya, cara membuat pangkalan data, jadual dan mendapatkan data daripadanya. Sudah tiba masanya untuk melihat sama ada apa-apa berhasil dengan penderiaan jauh. Pada pendapat saya, separuh daripadanya boleh dilakukan hanya berdasarkan artikel sebelum ini. Ternyata untuk memasang aplikasi dengan betul dan membuat segala-galanya lebih kurang cantik, anda perlu bercakap tentang pangkalan data, dan untuk bercakap tentangnya anda perlu menghabiskan banyak masa.

Menyemak kerja rumah

Setinggi-tinggi penghormatan kepada semua yang berjaya menyelesaikan tugasan. Ini bermakna anda memahami bahawa hanya anda yang memerlukan ini dan ia hanya membantu anda. Bagi mereka yang telah mengabaikan tugas saya, izinkan saya mengingatkan anda tentang syarat:

1. Anda perlu menambah kunci utama (KUNCI UTAMA) daripada medan ID kepada skema jadual negara.
2. Tambah negara lain ke jadual negara - Moldova.
3. Mengikut skema artikel sebelumnya, buat bandar jadual, yang akan mengandungi semua medan yang diterangkan. Nama medan adalah seperti berikut: id, nama, country_id, populasi.
4. Tambahkan kunci utama pada jadual bandar.
5. Tambahkan kunci asing pada jadual bandar.

Untuk bermula, mari gunakan bahagian pertama artikel sebelumnya dan pergi ke terminal pangkalan data.

Menambah kunci utama

Anda boleh menambah kunci utama (KUNCI UTAMA) dalam dua cara: serta-merta apabila mencipta jadual, atau selepas penciptaan, menggunakan ALTER TABLE.

Kunci utama semasa pembuatan jadual

Memandangkan kami telah pun mencipta jadual, dan tanpa memadamkannya kami tidak akan dapat menunjukkan pendekatan ini dalam pangkalan data ini, kami hanya akan mencipta pangkalan data ujian sementara di mana kami akan melakukan segala-galanya. Mari masukkan arahan berikut:

• buat pangkalan data baharu:

  ujian $CREATE DATABASE;

• buat jadual dengan menambah kunci utama:

  $ CREATE TABLE country(id INT, name VARCHAR(30), PRIMARY KEY (id));

Secara umum, tidak ada yang rumit. Selepas mengisytiharkan pembolehubah, bahagian berikut PRIMARY KEY (id) ditambah , di mana nama medan yang akan menjadi kunci utama dihantar dalam kurungan. Dan mari kita lihat bagaimana skema jadual telah berubah: $ DESC negara; Seperti yang anda lihat, nilai PRI telah muncul dalam medan Kunci untuk entri id .

Kunci utama selepas penciptaan jadual

Seperti yang saya katakan sebelum ini, kunci pertama selepas mencipta jadual boleh diberikan menggunakan ALTER TABLE . Kami akan menjalankan contoh ini dalam pangkalan data bandar kami :

• mari pergi ke pangkalan data kami dari ujian satu:

  bandar $USE;

• Mari periksa sama ada kami pasti berada dalam pangkalan data kami (perlu ada bidang lain di sana - populasi). Untuk melakukan ini kami menulis:

  $ penduduk DESC;

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

$ ALTER JADUAL negara TAMBAHKAN KUNCI UTAMA (id);

• dan semak dengan segera dengan arahan:

  negara $DESC;

Seperti yang anda lihat dari gambar, semuanya betul, nilai PRI adalah tepat di mana ia sepatutnya. By the way, kami bekerja dengan pangkalan data ujian. Sekarang kita perlu memadamnya: mengapa kita perlu mengacaukan pelayan, bukan? Untuk melakukan ini, kami menggunakan arahan yang agak terkenal: $ DROP DATABASE ujian;

Menambah Moldova

Mula-mula kita perlu memutuskan apa yang akan kita rekod. ID kami yang seterusnya ialah 4. Namanya ialah Moldova, dan penduduknya ialah 3550900. Oleh itu, kami melaksanakan perintah INSERT INTO yang sudah kami ketahui: $ INSERT INTO country NILAI (4, 'Moldova', 3550900); Dan kami menyemak sama ada nilai ini betul-betul dalam pangkalan data: $ SELECT * FROM country WHERE id = 4; Dalam permintaan data, saya segera menentukan medan mana yang akan dicari, jadi kami hanya mendapat satu rekod, iaitu apa yang kami perlukan.

Cipta jadual bandar

Menggunakan gambar rajah dari artikel pertama tentang pangkalan data, kami mendapat maklumat yang diperlukan tentang jadual. Ia akan mengandungi medan berikut:

• id - pengecam unik;
• nama - nama bandar;
• country_id — kunci asing negara;
• populasi - penduduk bandar.

Agak tertekan untuk menulis ID unik setiap kali, bukan? Saya ingin menyerahkan perkara ini kepada pihak berkuasa MySQL . Dan ada cara sedemikian - AUTO INCREMENT . Kita perlu menambah ini pada medan digital, dan jika kita tidak melepasi nilai secara eksplisit, MySQL sendiri akan meningkatkan ID sebanyak satu berbanding dengan yang sebelumnya. Oleh itu, mencipta jadual akan kelihatan seperti ini: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, population INT, PRIMARY KEY (id)); Mari lihat rajah jadual untuk melihat sama ada semuanya telah dilakukan dengan betul: $ DESC city; Seperti yang anda boleh lihat daripada rajah jadual, kami mempunyai penerangan baharu untuk medan id - auto_increment. Jadi kami melakukan semuanya dengan betul. Mari kita semak data pada jadual yang dikonfigurasikan sepenuhnya. Untuk melakukan ini, kami akan melakukan bahagian terakhir tugas - kunci asing.

Tambahkan kunci asing ke bandar

Untuk kunci asing akan ada arahan ini: $ ALTER TABLE city TAMBAH KUNCI ASING (country_id) RUJUKAN country(id); Dan mari kita semak dengan segera apa yang salah dengan skema jadual: adakah ia telah berubah dalam tempoh sejam? bandar $DESC;

Bahagian bonus. Menguji

Saya terlupa untuk menambahkannya pada tugas - isikan data yang terdapat dalam tangkapan skrin bahagian pertama. Saya terlupa, jadi sekarang saya akan melakukannya sendiri. Dan bagi mereka yang berminat, anda boleh melakukannya sendiri tanpa saya dan kemudian kami akan menyemak ;) Terdapat Kharkov, Kyiv, Minsk, Odessa, Voronezh, dan kami juga akan menambah Chisinau. Tetapi kali ini kami tidak akan menghantar ID, kami akan melangkaunya: $ INSERT INTO city (name, country_id, population) NILAI ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Seperti yang anda lihat, anda boleh membuat beberapa entri serentak menggunakan satu arahan INSERT INTO. Perkara yang berguna, ingat) Dan segera mari lihat apa yang ada dalam jadual: $ PILIH * DARI bandar; AUTO_INCREMENT - berfungsi tepat seperti yang kami mahu. Fail ID semuanya telah diisi, walaupun kami tidak menyerahkannya. Kunci asing adalah perkara yang bergantung. Untuk menyemak sama ada ia berfungsi dengan betul, anda boleh cuba menulis kunci asing yang tidak wujud dalam jadual asing. Katakan kita memutuskan bahawa id = 5 ialah Kazakhstan. Tetapi pada hakikatnya ia tidak ada dalam jadual negara. Dan untuk memastikan pangkalan data akan bersumpah, tambahkan bandar - Astana: $ INSERT INTO city (nama, country_id, population) NILAI ('Astana', 5, 1136156); Dan kami secara semula jadi mendapat ralat: Sekarang kunci asing memastikan bahawa kami tidak cuba untuk menetapkan negara kepada bandar yang tiada dalam pangkalan data kami. Bahagian kerja rumah ini boleh dianggap selesai - maju ke yang baru :)

SELECT kenyataan

Nah, semuanya tidak kelihatan begitu menakutkan lagi, bukan? Saya ingin ambil perhatian sekali lagi bahawa untuk pembangun Java, pengetahuan tentang pangkalan data adalah mesti ada. Tanpa pangkalan data anda tidak boleh ke mana-mana. Ya, saya sudah mahu mula menulis permohonan, saya setuju. Tetapi ia perlu. Jadi kita akan teruskan cara ini. Menggunakan pernyataan SELECT, kami mendapatkan semula data daripada pangkalan data. Iaitu, ini adalah operasi DML biasa (adakah anda sudah lupa apa itu?...))) Baca semula artikel SEBELUM). Apakah faedah pangkalan data hubungan? Mereka mempunyai fungsi yang hebat untuk mengagregat dan mendapatkan semula data. Inilah yang digunakan pernyataan SELECT. Nampaknya tidak ada yang rumit mengenainya, bukan? Tetapi ternyata masih banyak yang perlu difahami) Adalah penting untuk kita memahami asas yang boleh kita bina. Pertanyaan paling mudah dengan pernyataan SELECT adalah untuk memilih semua data dari satu jadual. Saya sangat menyukai huraian daripada wiki tentang susunan yang sepatutnya diberikan oleh pengendali dalam pertanyaan SELECT, jadi saya akan menyalinnya dengan berani 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 boleh melihat bahawa anda tidak boleh meletakkan operator GROUP BY dahulu dan kemudian operator WHERE. Perkara ini perlu diingat supaya tidak timbul rasa dendam terhadap kesilapan yang tidak jelas dari mana datangnya. $SELECT * DARI bandar; Tetapi mengikis semua data jelas tidak menyeronokkan bagi kami. Ini betul-betul sama jika kita ingin menukul paku dengan mikroskop [1] , [2] . Memandangkan pangkalan data menjalankan operasi penapisan, pengisihan dan pengagregatan lebih cepat daripada kod Java, adalah lebih baik untuk menyerahkan perkara ini kepada pangkalan data. Oleh itu, dengan merumitkan tugas kami akan membuka fungsi baharu.

Parameter WHERE

Untuk menapis pilihan, perkataan WHERE digunakan . Ini harus ditafsirkan seperti berikut: PILIH * DARI nama jadual (pilih semua medan daripada nama jadual jadual) WHERE talbe_row = 1 (di mana dalam rekod medan table_row adalah sama dengan 1). Adalah penting untuk ambil perhatian bahawa susunan kata kunci dalam pertanyaan adalah penting. Anda tidak boleh menulis WHERE a =1 FROM table_name SELECT *. Untuk bahasa Rusia ini ok, dan bagi sesetengah orang ia mungkin kelihatan tidak begitu buruk, tetapi untuk SQL ini tidak boleh diterima. Kami menulis pertanyaan berikut: $ SELECT * FROM city WHERE country_id = 1; Dan kami memilih bandar-bandar Ukraine. Tidak buruk, bukan? Bagaimana jika kita mahu bukan sahaja Ukraine, tetapi juga Belarusia? Untuk tujuan ini, kita boleh menyenaraikan koleksi nilai yang boleh diambil oleh medan: $SELECT * FROM city WHERE country_id IN(1, 3); Dan kami sudah mempunyai bandar dari dua negara sebagai tindak balas. Bagaimana jika terdapat beberapa syarat untuk ditapis? Katakan kita mahu bandar yang mempunyai populasi lebih daripada dua juta? Untuk melakukan ini, gunakan perkataan OR dan AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000; Hebat, tetapi bagaimana jika kita perlu menambah satu lagi syarat - untuk mencari nama melalui ungkapan biasa (saya tidak akan menerangkan ungkapan biasa di sini: inilah orang yang melakukan ini "secara ringkas" dalam 4 bahagian )? Sebagai contoh, kami ingat cara mengeja bandar, tetapi tidak sepenuhnya... Untuk melakukan ini, anda boleh menambah kata kunci SUKA pada ungkapan penapisan : $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR nama SEPERTI “%hark%”; Dan dengan cara ini kami juga mendapat Kharkov. Akibatnya, kami boleh mengatakan bahawa carian kami ternyata sangat baik. Tetapi saya ingin mengisih bukan mengikut ID, tetapi mengikut populasi, tetapi bagaimana? Ya sangat mudah...

PESANAN MENGIKUT parameter

Menggunakan ORDER BY, kami boleh mengisih rekod yang kami terima mengikut medan tertentu. Ia menyusun kedua-dua nombor dan rentetan. Mari kembangkan pertanyaan sebelumnya, mengisih mengikut populasi, menambah ORDER BY population: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 ATAU nama LIKE “%hark%” ORDER BY population; Seperti yang dapat kita lihat, pengasingan berlaku dalam susunan semula jadi, iaitu, dalam susunan menaik. Bagaimana jika kita mahu sebaliknya? Untuk melakukan ini, anda perlu menambah perkataan DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 ATAU nama LIKE “%hark%” ORDER BY population DESC; Sekarang pengisihan adalah berdasarkan pengurangan populasi. Dan pangkalan data melakukan ini dengan cepat: tiada Collections.sort di sana boleh dibandingkan. Sekarang mari kita mengisih mengikut baris, mengikut nama dalam susunan terbalik: $ SELECT * FROM city WHERE country_id IN (1, 3) DAN populasi > 2000000 ATAU nama SEPERTI “%hark%” ORDER MENGIKUT nama DESC;

KUMPULAN MENGIKUT parameter

Digunakan untuk mengumpulkan rekod mengikut medan tertentu. Ini biasanya diperlukan untuk menggunakan fungsi agregat... Apakah fungsi agregat?)) Adalah masuk akal untuk dikumpulkan mengikut beberapa medan jika ia adalah sama untuk rekod yang berbeza. Mari lihat apa maksudnya menggunakan contoh kami. Katakan bandar mempunyai kunci asing - ID negara. Jadi, ID adalah sama untuk bandar dari negara yang sama. Oleh itu, anda boleh mengambil dan mengumpulkan rekod oleh mereka: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; Tetapi tanpa fungsi agregasi ia kelihatan agak hambar, anda mesti mengakui. Oleh itu, mari kita lihat beberapa fungsi yang paling biasa:

• COUNT - bilangan rekod, boleh digunakan tanpa pengumpulan, digunakan sebagai COUNT(*) . Dalam kes pengumpulan mengikut beberapa medan - COUNT(groupped_field);
• MAX - mencari nilai maksimum untuk medan tertentu;
• MIN - mencari nilai minimum untuk medan tertentu;
• SUM - mencari jumlah untuk medan tertentu;
• AVG - mencari nilai purata.

Secara umum, fungsi ini boleh digunakan tanpa pengelompokan, hanya kemudian hanya satu medan akan dipaparkan. Mari cuba mereka untuk penduduk bandar kita: $ SELECT COUNT(*) FROM city; Apa yang mereka minta adalah apa yang mereka dapat. Hanya bilangan rekod. Kadang-kadang ini berguna. Sebagai contoh, jika kita perlu mengetahui jumlah artikel penulis tertentu. Tidak perlu mengeluarkannya daripada pangkalan data dan mengiranya. Anda hanya boleh menggunakan COUNT(). $ PILIH AVG(populasi) DARI bandar; $ PILIH MIN(penduduk) DARI bandar; Dan di sinilah perkumpulan mula berkuat kuasa. Sebagai contoh, tugasnya adalah untuk mendapatkan bandar terkecil di negara ini. Sudah tahu bagaimana untuk melakukan ini? Cuba sendiri, kemudian tonton: $ SELECT country_id as Country, MIN(populasi) FROM city WHERE GROUP BY country_id; Setakat ini kami hanya melihat nombor ID negara, tetapi tidak mengapa - lain kali kami akan melakukan segala-galanya. Jadi sudah ada keputusan, dan kami mendapat apa yang kami mahu - bandar terkecil di negara ini dengan ID = 1. Selebihnya fungsi akan sama. Adalah penting untuk ambil perhatian bahawa mengumpul semua medan melalui * apabila menggunakan kumpulan dan pengagregatan tidak akan berfungsi! Cuba pertimbangkan ;)

Kerja rumah

Berdasarkan hasil artikel sebelum ini, jelas bahawa kerja rumah sedang dilakukan, jadi mari kita teruskan)) Ya, semua orang yang melakukan kerja rumah akan terus meletakkan "+" dalam komen. Adalah penting bagi saya bahawa topik kerja rumah menarik kepada anda, supaya saya terus melakukannya pada masa hadapan. Ya, saya membaca komen anda dengan kerap. Sudah tentu, saya kurang kerap menjawab. Saya melihat bahawa mereka meminta untuk memberikan masalah SQL yang lebih sukar. Sehingga kita belajar bergabung, tidak akan ada masalah yang menarik, jadi akan ada yang saya perlukan untuk bahan selanjutnya.

Tugasan:

Fahami operator HAVING dan tulis contoh pertanyaan untuk jadual daripada contoh kami. Jika anda perlu menambah beberapa medan atau lebih banyak nilai untuk menjadikannya lebih jelas, tambahkannya. Jika sesiapa mahu, tulis penyelesaian contoh anda dalam ulasan: dengan cara ini saya juga boleh menyemaknya jika saya mempunyai masa.
1. Pasang MySQL Workbench untuk bekerja dengan pangkalan data melalui UI. Saya fikir kita sudah mempunyai latihan yang mencukupi dengan bekerja dari konsol. Sambung ke pangkalan data. Jika anda menggunakan sesuatu yang lain untuk bekerja dengan pangkalan data, jangan ragu untuk melangkau tugas ini. Di sini dan seterusnya saya hanya akan menggunakan MySQL Workbench.
2. Tulis permintaan untuk menerima menggunakan data kami:
   1. negara terkecil/paling ramai penduduk;
   2. purata bilangan penduduk di negara ini;
   3. purata bilangan penduduk di negara yang namanya berakhir dengan “a”;
   4. bilangan negara dengan penduduk lebih daripada empat juta;
   5. menyusun negara dengan mengurangkan bilangan penduduk;
   6. menyusun negara mengikut nama mengikut susunan semula jadi.

Kesimpulan

Hari ini kami membincangkan secara terperinci kerja rumah dari pelajaran lepas. Lebih-lebih lagi, saya menganggap ini penting bagi mereka yang tidak melakukannya dan bagi mereka yang melakukannya. Untuk yang pertama, ini adalah peluang untuk mengetahui jawapannya, dan untuk yang terakhir, membandingkannya dengan keputusan anda. Langgan akaun GitHub saya untuk kekal dikemas kini tentang perubahan pada projek. Saya akan mengekalkan keseluruhan pangkalan kod di sana. Semuanya akan berlaku dalam organisasi ini . Seterusnya, kami membincangkan pernyataan SELECT. Dia yang paling penting bagi kita. Melaluinya semua permintaan untuk data akan dilalui, dan kita mesti memahaminya. Perkara yang paling penting ialah mengingati susunan parameter yang ditambahkan (WHERE, ORDER BY, GROUP BY, dan sebagainya). Ya, saya tidak memberitahu semua yang mungkin, tetapi saya tidak menetapkan matlamat sedemikian untuk diri saya sendiri. Ya, saya tahu bahawa anda sudah tidak sabar-sabar untuk menulis permohonan. Sabar, ini sahaja yang anda perlukan. Kedua-dua untuk projek dan untuk pertumbuhan profesional anda. Semasa anda menunggu, pastikan Git sudah biasa dengan anda. Saya akan menggunakannya secara lalai, sebagai alat yang terkenal. Terima kasih semua kerana membaca. Dalam artikel seterusnya kita akan bercakap tentang sambungan pangkalan data dan gabungan. Di situlah tugas-tugas yang hebat akan berlaku))

Senarai semua bahan dalam siri ini adalah pada permulaan artikel ini.