Chúng tôi phân tích cơ sở dữ liệu và ngôn ngữ SQL. (Phần 3) - "Dự án Java từ A đến Z"

Một bài viết trong loạt bài viết về cách tạo một dự án Java (có liên kết đến các tài liệu khác ở cuối). Mục tiêu của nó là phân tích các công nghệ chủ chốt, kết quả là viết một bot điện tín.

• Kiểm tra bài tập về nhà
  • Thêm khóa chính
  • Thêm Moldova
  • Tạo bảng thành phố
  • Thêm khóa ngoại cho thành phố
  • Phần thưởng. Kiểm tra
• câu lệnh CHỌN
  • tham số WHERE
  • ĐẶT HÀNG THEO tham số
  • tham số NHÓM THEO
• Bài tập về nhà
• Phần kết luận

Xin chào quý vị và các bạn, chúng ta hãy tiếp tục nói về cơ sở dữ liệu, SQL và những thứ khác. Nội dung hôm nay sẽ có một phần lý thuyết và một phần thực hành. Hãy để tôi nhắc bạn rằng lần trước chúng ta đã nói về cách thiết lập mọi thứ, cách tạo cơ sở dữ liệu, bảng và lấy dữ liệu từ đó. Đã đến lúc xem liệu có điều gì xảy ra với viễn thám không. Theo tôi, chỉ dựa vào bài viết trước đã có thể thực hiện được một nửa. Hóa ra để lắp ráp một ứng dụng đúng cách và làm cho mọi thứ trở nên đẹp đẽ hơn hoặc ít hơn, bạn cần phải nói về cơ sở dữ liệu, và để nói về chúng, bạn cần phải dành rất nhiều thời gian.

Kiểm tra bài tập về nhà

Rất tôn trọng tất cả những người đã hoàn thành xuất sắc nhiệm vụ. Điều này có nghĩa là bạn hiểu rằng chỉ có bạn cần điều này và nó chỉ giúp ích cho bạn. Đối với những người đã bỏ bê nhiệm vụ của tôi, hãy để tôi nhắc bạn về điều kiện:

1. Bạn cần thêm khóa chính (KHÓA CHÍNH) từ trường ID vào lược đồ bảng quốc gia.
2. Thêm một quốc gia khác vào bảng quốc gia - Moldova.
3. Theo sơ đồ của bài viết trước, hãy tạo một thành phố dạng bảng, trong đó sẽ chứa tất cả các trường được mô tả. Tên trường sẽ như sau: id, name, country_id, dân số.
4. Thêm khóa chính vào bảng thành phố.
5. Thêm khóa ngoại vào bảng thành phố.

Để bắt đầu, hãy sử dụng phần đầu tiên của bài viết trước và đi đến thiết bị đầu cuối cơ sở dữ liệu.

Thêm khóa chính

Bạn có thể thêm khóa chính (PRIMARY KEY) theo hai cách: ngay khi tạo bảng hoặc sau khi tạo, sử dụng ALTER TABLE.

Khóa chính trong quá trình tạo bảng

Vì chúng tôi đã tạo một bảng và nếu không xóa nó, chúng tôi sẽ không thể hiển thị cách tiếp cận này trong cơ sở dữ liệu này, nên chúng tôi sẽ chỉ tạo một cơ sở dữ liệu thử nghiệm tạm thời trong đó chúng tôi sẽ thực hiện mọi thứ. Hãy nhập các lệnh sau:

• tạo cơ sở dữ liệu mới:

  bài kiểm tra $CREATE DATABASE;

• tạo một bảng thêm khóa chính:

  $ TẠO BẢNG quốc gia (id INT, tên VARCHAR(30), PRIMARY KEY (id));

Nói chung là không có gì phức tạp. Sau khi khai báo các biến, phần PRIMARY KEY (id) sau đây được thêm vào , trong đó tên của trường sẽ là khóa chính được chuyển trong ngoặc đơn. Và hãy xem lược đồ bảng đã thay đổi như thế nào: $ DESC country; Như bạn có thể thấy, giá trị PRI đã xuất hiện trong trường Key cho mục nhập id .

Khóa chính sau khi tạo bảng

Như tôi đã nói trước đó, khóa đầu tiên sau khi tạo bảng có thể được gán bằng ALTER TABLE . Chúng tôi sẽ chạy ví dụ này trong cơ sở dữ liệu thành phố của chúng tôi :

• hãy truy cập cơ sở dữ liệu của chúng tôi từ cơ sở dữ liệu thử nghiệm:

  $USE thành phố;

• Hãy kiểm tra xem chúng tôi chắc chắn có trong cơ sở dữ liệu của mình hay không (cần có một trường khác ở đó - dân số). Để làm điều này chúng tôi viết:

  $ dân số DESC;

mọi thứ đều chính xác, cái bàn là của chúng tôi. Hãy viết như sau:

$ THAY ĐỔI BẢNG quốc gia THÊM KHÓA CHÍNH (id);

• và kiểm tra ngay bằng lệnh:

  quốc gia $DESC;

Như bạn có thể thấy trong hình, mọi thứ đều chính xác, giá trị PRI chính xác ở vị trí cần có. Nhân tiện, chúng tôi đã làm việc với cơ sở dữ liệu thử nghiệm. Bây giờ chúng ta cần xóa nó đi: tại sao chúng ta lại phải làm lộn xộn máy chủ nhỉ? Để làm điều này, chúng tôi sử dụng một lệnh khá nổi tiếng: $ DROP DATABASE test;

Thêm Moldova

Đầu tiên chúng ta cần quyết định những gì chúng ta sẽ ghi lại. ID tiếp theo của chúng tôi sẽ là 4. Tên sẽ là Moldova và dân số của nó là 3550900. Do đó, chúng tôi thực thi lệnh INSERT INTO mà chúng tôi đã biết: $ INSERT INTO country VALUES (4, 'Moldova', 3550900); Và chúng tôi kiểm tra xem giá trị này có chính xác trong cơ sở dữ liệu hay không: $ SELECT * FROM country WHERE id = 4; Trong yêu cầu dữ liệu, tôi xác định ngay trường nào sẽ được tìm kiếm nên chúng tôi chỉ nhận được một bản ghi, đó là thứ chúng tôi cần.

Tạo bảng thành phố

Sử dụng sơ đồ từ bài viết đầu tiên về cơ sở dữ liệu, chúng ta có được thông tin cần thiết về bảng. Nó sẽ chứa các trường sau:

• id - mã định danh duy nhất;
• tên - tên thành phố;
• country_id - khóa ngoại quốc gia;
• dân số - dân số của thành phố.

Bạn có thấy hơi căng thẳng khi phải viết một ID duy nhất mỗi lần không? Tôi muốn để việc này cho chính quyền MySQL . Và có một cách như vậy - AUTO INCREMENT . Chúng ta cần thêm giá trị này vào trường kỹ thuật số và nếu chúng ta không chuyển các giá trị một cách rõ ràng, chính MySQL sẽ tăng ID lên một so với giá trị trước đó. Do đó, việc tạo một bảng sẽ như sau: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, dân số INT, PRIMARY KEY (id)); Hãy nhìn vào sơ đồ bảng để xem mọi thứ đã được thực hiện đúng chưa: $ DESC city; Như bạn có thể thấy từ sơ đồ bảng, chúng tôi có mô tả mới cho trường id - auto_increment. Vì vậy, chúng tôi đã làm mọi thứ đúng. Hãy kiểm tra dữ liệu trên một bảng được cấu hình đầy đủ. Để làm điều này, chúng ta sẽ thực hiện phần cuối cùng của nhiệm vụ - khóa ngoại.

Thêm khóa ngoại cho thành phố

Đối với khóa ngoại sẽ có lệnh này: $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); Và hãy kiểm tra ngay xem có vấn đề gì với lược đồ bảng: nó có thay đổi trong suốt một giờ không? $DESC thành phố;

Phần thưởng. Kiểm tra

Tôi quên thêm nó vào nhiệm vụ - điền dữ liệu có trong ảnh chụp màn hình của phần đầu tiên. Mình quên mất nên bây giờ mình sẽ tự làm. Và đối với những ai quan tâm, bạn có thể tự làm mà không cần tôi và sau đó chúng tôi sẽ kiểm tra;) Có Kharkov, Kyiv, Minsk, Odessa, Voronezh và chúng tôi cũng sẽ thêm Chisinau. Nhưng lần này chúng tôi sẽ không truyền ID, chúng tôi sẽ bỏ qua chúng: $ INSERT VÀO thành phố (tên, country_id, dân số) GIÁ TRỊ ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); Như bạn có thể thấy, bạn có thể tạo nhiều mục cùng lúc bằng cách sử dụng một lệnh INSERT INTO. Hãy nhớ rằng, một điều hữu ích) Và ngay lập tức chúng ta hãy xem những gì trong bảng: $ SELECT * FROM city; AUTO_INCREMENT - hoạt động chính xác như chúng tôi mong muốn. Các tệp ID đều đã được điền đầy đủ, mặc dù chúng tôi không gửi chúng. Khóa ngoại là một thứ phụ thuộc. Để kiểm tra xem nó có hoạt động chính xác hay không, bạn có thể thử viết khóa ngoại không tồn tại trong bảng ngoại. Giả sử chúng ta quyết định rằng id = 5 là Kazakhstan. Nhưng trên thực tế nó không có trong bảng các nước. Và để kiểm tra xem cơ sở dữ liệu có chửi thề hay không, hãy thêm thành phố - Astana: $ INSERT INTO city (name, country_id, dân số) VALUES ('Astana', 5, 1136156); Và tất nhiên chúng tôi gặp lỗi: Bây giờ, khóa ngoại đảm bảo rằng chúng tôi không cố gắng gán một quốc gia cho thành phố không có trong cơ sở dữ liệu của chúng tôi. Phần bài tập về nhà này có thể được coi là đã hoàn thành - chuyển sang phần mới :)

câu lệnh CHỌN

Chà, mọi thứ dường như không còn đáng sợ nữa phải không? Tôi muốn lưu ý một lần nữa rằng đối với các nhà phát triển Java, kiến ​​thức về cơ sở dữ liệu là điều bắt buộc. Không có cơ sở dữ liệu bạn không thể đi đâu cả. Có, tôi đã muốn bắt đầu viết đơn đăng ký rồi, tôi đồng ý. Nhưng nó là cần thiết. Vì vậy chúng ta sẽ tiếp tục theo cách này. Sử dụng câu lệnh SELECT, chúng ta lấy dữ liệu từ cơ sở dữ liệu. Tức là, đây là một hoạt động DML điển hình (bạn đã quên nó là gì rồi à?...))) Đọc lại các bài viết TRƯỚC KHI). Lợi ích của cơ sở dữ liệu quan hệ là gì? Chúng có chức năng tuyệt vời để tổng hợp và truy xuất dữ liệu. Đây là mục đích của câu lệnh SELECT. Có vẻ như không có gì phức tạp về nó, phải không? Nhưng hóa ra vẫn còn nhiều điều cần hiểu) Điều quan trọng là chúng ta phải hiểu những kiến ​​thức cơ bản để từ đó chúng ta có thể phát triển. Truy vấn đơn giản nhất với câu lệnh SELECT là chọn tất cả dữ liệu từ một bảng. Tôi thực sự thích mô tả từ wiki về chính xác thứ tự các toán tử nên thực hiện trong truy vấn CHỌN, vì vậy tôi sẽ mạnh dạn sao chép nó ở đây:

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

Ở đây bạn có thể thấy rằng bạn không thể đặt toán tử GROUP BY trước rồi đến toán tử WHERE. Điều này cần phải ghi nhớ để sau này không còn oán hận những sai lầm không rõ nguồn gốc từ đâu. $CHỌN * TỪ thành phố; Nhưng việc loại bỏ tất cả dữ liệu rõ ràng không phải là điều thú vị đối với chúng tôi. Điều này hoàn toàn giống nếu chúng ta muốn đóng đinh bằng kính hiển vi [1] , [2] . Vì cơ sở dữ liệu thực hiện các hoạt động lọc, sắp xếp và tổng hợp nhanh hơn nhiều so với mã Java, nên tốt hơn hết hãy để vấn đề này cho cơ sở dữ liệu. Do đó, bằng cách làm phức tạp các nhiệm vụ, chúng tôi sẽ mở ra chức năng mới.

tham số WHERE

Để lọc một lựa chọn, từ WHERE được sử dụng . Điều này nên được hiểu như sau: SELECT * FROM tablename (chọn tất cả các trường từ tên bảng) WHERE talbe_row = 1 (trong đó trong các bản ghi, trường table_row bằng 1). Điều quan trọng cần lưu ý là thứ tự của các từ khóa trong truy vấn rất quan trọng. Bạn không thể viết WHERE a =1 FROM table_name SELECT *. Đối với tiếng Nga thì điều này là ổn, và đối với một số người thì điều đó có vẻ không quá tệ, nhưng đối với SQL thì điều này là không thể chấp nhận được. Chúng tôi viết truy vấn sau: $ SELECT * FROM city WHERE country_id = 1; Và chúng tôi đã chọn các thành phố của Ukraina. Không tệ, phải không? Điều gì sẽ xảy ra nếu chúng ta không chỉ muốn tiếng Ukraina mà còn cả tiếng Belarus? Với mục đích này, chúng ta có thể liệt kê tập hợp các giá trị mà trường có thể lấy: $SELECT * FROM city WHERE country_id IN(1, 3); Và chúng tôi đã có các thành phố từ hai quốc gia đáp lại. Nếu có nhiều điều kiện để lọc thì sao? Giả sử chúng ta muốn các thành phố có dân số hơn hai triệu người? Để thực hiện việc này, hãy sử dụng các từ OR và AND : $ SELECT * FROM city WHERE country_id IN (1, 3) AND dân số > 2000000; Tuyệt vời, nhưng điều gì sẽ xảy ra nếu chúng ta cần thêm một điều kiện nữa - tìm kiếm tên thông qua một biểu thức chính quy (tôi sẽ không mô tả các biểu thức chính quy ở đây: đây là người đã thực hiện điều này “một thời gian ngắn” trong 4 phần )? Ví dụ: chúng tôi nhớ cách đánh vần một thành phố, nhưng không hoàn toàn... Để làm điều này, bạn có thể thêm từ khóa THÍCH vào biểu thức lọc : $ SELECT * FROM city WHERE country_id IN (1, 3) AND dân số > 2000000 OR tên THÍCH “%hark%”; Và bằng cách này, chúng tôi cũng có được Kharkov. Kết quả là, chúng tôi có thể nói rằng tìm kiếm của chúng tôi hóa ra rất tốt. Nhưng tôi muốn sắp xếp không phải theo ID mà theo dân số, nhưng bằng cách nào? Vâng rất đơn giản...

ĐẶT HÀNG THEO tham số

При помощи ORDER BY мы можем отсортировать записи, которые получor по определенному полю. Сортирует он How числа, так и строки. Расширим предыдущий request, отсортируем по населению, добавив ORDER BY population: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population;Как мы видим, sorting произошла в натуральном порядке, то есть по возрастанию. А если мы хотим наоборот? Для этого нужно добавить слово DESC: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY population DESC;Теперь sorting идет по уменьшению населения. И делает это БД очень быстро: ниHowие там Collections.sort не идут в сравнение. Теперь отсортируем по строкам, по имени в обратном порядке: $ SELECT * FROM city WHERE country_id IN (1, 3) AND population > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

Параметр GROUP BY

Используется для группировки записей по определенным полям. Обычно это нужно, чтобы применять агрегатные функции… А что такое агрегатные функции?)) Есть смысл группировать по Howим-то полям, если они одинаковые для разных записей. Разберем, что имеется в виду, на нашем примере. Скажем, в городах есть внешние ключи — ID-шники стран. Так вот, ID одинаков для городов из одной страны. Поэтому можно взять и сгруппировать записи по ним: $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id;Но без агрегирующих функций это выглядит How-то блекло, согласитесь. Поэтому рассмотрим несколько самых распространенных функций:

• COUNT — количество записей, можно без группировки, используют How COUNT(*). В случае же группировки по Howому-то полю — COUNT(groupped_field);
• MAX — находит максимальное meaning по определенному полю;
• MIN — находит минимальное meaning по определенному полю;
• SUM — находит сумму по определенному полю;
• AVG — находит среднее meaning.

Nói chung, các hàm này có thể được sử dụng mà không cần nhóm, chỉ khi đó chỉ có một trường được hiển thị. Hãy thử chúng với dân số thành phố của chúng ta: $ SELECT COUNT(*) FROM city; Những gì họ yêu cầu là những gì họ nhận được. Chỉ cần số lượng hồ sơ. Đôi khi điều này rất hữu ích. Ví dụ: nếu chúng ta cần tìm hiểu số lượng bài viết của một tác giả nào đó. Không cần phải lấy chúng ra khỏi cơ sở dữ liệu và đếm chúng. Bạn chỉ có thể sử dụng COUNT(). $ CHỌN AVG (dân số) TỪ thành phố; $ CHỌN MIN(dân số) TỪ thành phố; Và đây là lúc việc phân nhóm có hiệu lực. Ví dụ, nhiệm vụ là lấy được thành phố nhỏ nhất trong cả nước. Bạn đã biết cách thực hiện việc này chưa? Hãy tự mình thử, sau đó xem: $ SELECT country_id làm Quốc gia, MIN(dân số) TỪ thành phố WHERE GROUP BY country_id; Cho đến nay chúng tôi chỉ thấy ID của quốc gia, nhưng điều đó không thành vấn đề - lần sau chúng tôi sẽ làm mọi thứ. Và thế là đã có kết quả và chúng tôi đã đạt được điều mình mong muốn - thành phố nhỏ nhất cả nước có ID = 1. Các chức năng còn lại sẽ giống nhau. Điều quan trọng cần lưu ý là việc loại bỏ tất cả các trường thông qua * khi sử dụng tính năng nhóm và tổng hợp sẽ không hoạt động! Hãy suy nghĩ về nó;)

Bài tập về nhà

Dựa vào kết quả của các bài trước thì rõ ràng bài tập đã làm xong rồi, tiếp tục nhé)) Đúng rồi, mọi người làm bài tập tiếp tục cho dấu “+” vào phần bình luận. Điều quan trọng đối với tôi là chủ đề bài tập về nhà khiến bạn thấy thú vị để tôi tiếp tục thực hiện nó trong tương lai. Có, tôi thường xuyên đọc bình luận của bạn. Tất nhiên, tôi trả lời ít thường xuyên hơn. Tôi thấy họ yêu cầu đưa ra những vấn đề SQL khó hơn. Cho đến khi chúng ta học về phép nối, sẽ không có vấn đề gì thú vị, vì vậy sẽ có những vấn đề mà tôi cần để có thêm tài liệu.

Nhiệm vụ:

Hiểu toán tử HAVING và viết một truy vấn mẫu cho các bảng từ ví dụ của chúng tôi. Nếu bạn cần thêm một số trường hoặc nhiều giá trị để làm rõ hơn, hãy thêm chúng. Nếu có ai muốn, hãy viết giải pháp ví dụ của bạn vào phần bình luận: bằng cách này, tôi cũng có thể kiểm tra nó nếu có thời gian.
1. Cài đặt MySQL Workbench để làm việc với cơ sở dữ liệu thông qua giao diện người dùng. Tôi nghĩ chúng ta đã thực hành đủ cách làm việc từ bảng điều khiển. Kết nối với cơ sở dữ liệu. Nếu bạn sử dụng cái gì khác để làm việc với cơ sở dữ liệu, vui lòng bỏ qua tác vụ này. Ở đây và hơn thế nữa tôi sẽ chỉ sử dụng MySQL Workbench.
2. Viết yêu cầu nhận bằng cách sử dụng dữ liệu của chúng tôi:
   1. quốc gia nhỏ nhất/đông dân nhất;
   2. số dân trung bình trong cả nước;
   3. số dân trung bình ở các quốc gia có tên kết thúc bằng “a”;
   4. số quốc gia có dân số trên 4 triệu người;
   5. sắp xếp các quốc gia theo số lượng dân số giảm dần;
   6. sắp xếp các quốc gia theo tên theo thứ tự tự nhiên.

Phần kết luận

Hôm nay chúng ta đã thảo luận chi tiết về bài tập về nhà của bài học trước. Hơn nữa, tôi coi điều này là quan trọng đối với cả những người không làm và những người đã làm. Đối với người trước, đây là cơ hội để tìm ra câu trả lời và đối với người sau, hãy so sánh nó với kết quả của bạn. Đăng ký tài khoản GitHub của tôi để cập nhật những thay đổi của dự án. Tôi sẽ duy trì toàn bộ cơ sở mã ở đó. Mọi thứ sẽ diễn ra trong tổ chức này . Tiếp theo, chúng ta thảo luận về câu lệnh SELECT. Anh ấy là người quan trọng nhất đối với chúng tôi. Thông qua nó, mọi yêu cầu về dữ liệu sẽ được thực hiện và chúng ta phải hiểu nó. Điều quan trọng nhất là phải nhớ thứ tự các tham số được thêm vào (WHERE, ORDER BY, GROUP BY, v.v.). Đúng, tôi đã không nói ra tất cả những gì có thể xảy ra, nhưng tôi cũng không đặt mục tiêu như vậy cho bản thân. Vâng, tôi biết rằng bạn đang rất muốn viết đơn đăng ký. Hãy kiên nhẫn, đây là tất cả những gì bạn cần. Cả cho dự án và cho sự phát triển nghề nghiệp của bạn. Trong khi chờ đợi, hãy đảm bảo rằng Git đã quen thuộc với bạn. Tôi sẽ sử dụng nó theo mặc định, như một công cụ nổi tiếng. Cảm ơn mọi người đã đọc. Trong bài viết tiếp theo chúng ta sẽ nói về các kết nối và liên kết cơ sở dữ liệu. Đó là nơi sẽ có những nhiệm vụ thú vị))

Danh sách tất cả các tài liệu trong loạt bài này nằm ở đầu bài viết này.