Chúng tôi phân tích cơ sở dữ liệu và ngôn ngữ SQL. (Phần 6 - Kiểm tra bài tập cuối cùng) - "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. Phần này chứa phân tích về nhiệm vụ cuối cùng trên cơ sở dữ liệu. Xin chào các độc giả thân mến. Hôm nay chúng ta sẽ phân tích nhiệm vụ từ bài viết trước về cơ sở dữ liệu. Thật thú vị vì nó dành cho các cuộc phỏng vấn ở cấp Trung cấp. Nghĩa là, sau nhiệm vụ này, bạn đã có thể đi phỏng vấn và bạn sẽ vượt qua thành công ít nhất một phần những gì liên quan đến cơ sở dữ liệu quan hệ. Tôi biết bài viết này có thể cần thiết như thế nào và do đó tôi sẽ dồn tất cả kinh nghiệm của mình để làm cho nó trở nên hữu ích và thú vị. Và nếu bạn không ngủ quên giữa bài viết thì có nghĩa là tôi đã đạt được mục tiêu của mình. Tôi sẽ không lặp lại toàn bộ nhiệm vụ: Tôi sẽ trích dẫn nó trước khi hoàn thành mỗi nhiệm vụ, gạch chân nó bằng chữ in nghiêng. Tôi hy vọng rằng tất cả những ai đọc bài viết này sẽ chạy tất cả các truy vấn trong cơ sở dữ liệu của họ và nhận được kết quả tương tự. Điều này sẽ mang lại lợi ích tối đa cho doanh nghiệp. Và tôi sẽ hạnh phúc hơn một chút khi nghĩ rằng mình đã giúp được ai đó trong nhiệm vụ khó khăn của mình)

Bài tập 1

Viết script SQL tạo bảng “Student” với các trường sau: id (khóa chính), name, Last_name, e_mail (duy nhất). Chúng tôi đã làm việc này rồi nên sẽ không có vấn đề gì cả. Trong tập lệnh, bạn cần chỉ định khóa chính và trường duy nhất khác với trường chính. Đầu tiên, hãy tạo một cơ sở dữ liệu mới cho nhiệm vụ này: $ CREATE DATABASE Final_task; Và hãy sử dụng cơ sở dữ liệu này: $USE Final_task; Khi môi trường đã được thiết lập và sẵn sàng để chạy công việc, chúng ta có thể viết đoạn script sau: $ CREATE TABLE sinh viên ( id INT AUTO_INCREMENT, name VARCHAR(40), Last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), ĐỘC ĐÁO (email ) ); Cho đến nay, không có gì mới so với những gì chúng ta đã trải qua. Mọi ý kiến ​​đều không cần thiết, hãy tiếp tục.

Nhiệm vụ 2-3

Viết script SQL tạo bảng “Book” với các trường sau: id, title (id + title = khóa chính). Liên kết 'Sinh viên' và 'Sách' với mối quan hệ một-nhiều 'Sách' 'Học sinh'. Hãy kết hợp hai nhiệm vụ thành một để thực hiện nhanh hơn và thuận tiện hơn. Tôi đã thảo luận về cách thêm khóa ngoại riêng trong các bài viết trước. Ngoài ra, chúng ta cần nhớ cách chúng ta tạo ra các kết nối và thông qua những gì. Bài viết trước sẽ giúp bạn, còn đây là script: $ CREATE TABLE book ( id INT, title VARCHAR(100), sinh viên_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) TÀI LIỆU THAM KHẢO sinh viên (id ) ); Theo cách đơn giản này, chúng tôi đã thêm một khóa tổng hợp cho bảng PRIMARY KEY (id, title) của mình , bây giờ khóa sẽ chính xác là cặp. Điều này có nghĩa là có thể có nhiều hơn một giá trị trường id giống hệt nhau trong bảng. Và chính xác như vậy đối với tiêu đề.

Nhiệm vụ 4

Viết script SQL tạo bảng “Teacher” với các trường sau: id (khóa chính), name, Last_name, e_mail (duy nhất), chủ đề. Chúng ta tiếp tục chuẩn bị cơ sở dữ liệu cho các truy vấn, tạo bảng giáo viên: $ CREATE TABLE Teacher( id INT AUTO_INCREMENT, name VARCHAR(30), Last_name VARCHAR(30), email VARCHAR(100), topic VARCHAR(40), PRIMARY KEY ( id), ĐỘC ĐÁO (email)); Cho đến nay nó không khó khăn, phải không? Ba nhiệm vụ đã kết thúc!

Nhiệm vụ 5

Liên kết 'Học sinh' và 'Giáo viên' với mối quan hệ 'Học sinh' nhiều-nhiều Giáo viên'. Bây giờ điều này thú vị hơn! Chúng ta vừa nói về chuyện này lần trước. Hãy để tôi nhắc bạn những điều cần thiết để đạt được điều này: bạn cần tạo một bảng trung gian để lưu trữ các cặp học sinh-giáo viên. Với sự trợ giúp của nó, sẽ có thể tạo ra mối quan hệ nhiều-nhiều. Vì vậy, hãy tạo một bảng Students_x_techers . Cách đặt tên là mở và cũng có thể là: sinh_giáo . $ TẠO BẢNG sinh viên_x_teachers ( sinh viên_id INT KHÔNG NULL, giáo viên_id INT KHÔNG NULL, KHÓA CHÍNH (student_id, giáo viên_id), KHÓA NGOÀI (student_id) TÀI LIỆU THAM KHẢO sinh viên(id), KHÓA NGOẠI (teacher_id) TÀI LIỆU THAM KHẢO giáo viên(id) ); Như bạn có thể thấy, mọi thứ đều được thực hiện rõ ràng và mạch lạc. Chúng tôi có khóa tổng hợp cho hai khóa ngoại: sinh viên_id và giáo viên_id. Tại sao cũng là khóa ngoại? Để chúng tôi chắc chắn rằng các bản ghi trong bảng học sinh và giáo viên tồn tại đối với các cặp được ghi.

Nhiệm vụ 6

Chọn 'Sinh viên' có 'oro' trong họ của họ, ví dụ 'Sidorov', 'Voronovsky'. Để làm cho nó thú vị và trực quan đối với chúng tôi, tôi khuyên bạn nên thêm một số học sinh trước để một số trong số họ phù hợp với yêu cầu này, còn một số thì không. Do đó, hãy viết ra những người nên được đưa vào do yêu cầu: $ INSERT INTO sinh viên (tên, họ_tên, email) GIÁ TRỊ ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ CHÈN VÀO sinh viên (tên, họ, email) GIÁ TRỊ ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); Và những người không nên vào: $ INSERT INTO sinh viên (tên, họ, email) GIÁ TRỊ ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ CHÈN VÀO sinh viên (tên, họ, email) GIÁ TRỊ('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); Hãy kiểm tra kết quả, nhìn vào danh sách dữ liệu trong bảng sinh viên: $ SELECT * FROM sinh viên; và chúng tôi nhận được: Tổng cộng có bốn bản ghi, hai trong số đó phù hợp và hai bản ghi không phù hợp. Sau khi chuẩn bị tất cả dữ liệu cho yêu cầu, chúng ta có thể đưa ra yêu cầu cho chính nhiệm vụ đó: $ SELECT * FROM sinh viên WHERE họ_BỘ THÍCH '%oro%'; Kết quả là Ivan và Nikolai đã xem qua danh sách.

Nhiệm vụ 7

Nhiệm vụ tiếp theo, chúng ta đọc: Chọn từ bảng 'Học sinh' tất cả các họ ('last_name') và số lần lặp lại của chúng. Hãy xem xét rằng có những cái tên trùng tên trong cơ sở dữ liệu. Sắp xếp theo số lượng giảm dần. Nó sẽ giống như thế này:

họ	Số lượng
Petrov	15
Ivanov	12
Sidorov	3

Để rõ ràng, bạn cần thêm nhiều dữ liệu hơn. Không chần chừ gì nữa, hãy thêm Petrovs, Ivanovs và Sidorovs, những người không biết mối quan hệ họ hàng của họ ;) Tôi sẽ không tạo ra địa chỉ email, tôi sẽ chỉ loại trừ nó khỏi các mục mới. Hãy chạy lệnh sau 12 lần: $ INSERT INTO sinh viên (tên, họ) GIÁ TRỊ ('Ivan', 'Ivanov'); Hãy thêm 15 Petrov: $ INSERT INTO sinh viên (tên, họ) GIÁ TRỊ ('Petr', 'Petrov'); Và hai Sidorov (chúng tôi đã có một))): $ INSERT INTO sinh viên (tên, họ) GIÁ TRỊ ('Sidorov', 'Sidorov'); Bây giờ dữ liệu đã sẵn sàng. Để có được những dữ liệu như vậy, bạn cần thực hiện việc nhóm; để thực hiện việc nhóm, bạn cần sử dụng toán tử Group By và bạn cần thực hiện việc này theo trường Last_name. Bạn cũng có thể nhận thấy rằng số lần lặp lại được chỉ định là số lượng và ở đây bạn cũng cần nhớ cách tạo bí danh trong SQL: $ SELECT Last_name, COUNT(*) as số lượng FROM sinh viên GROUP BY Last_name ORDER BY COUNT(*) DESC ; Vì vậy, tôi đã dùng quá nhiều với Petrovs - hóa ra là 16))

Nhiệm vụ 8

Điều kiện: Chọn 3 cái tên được lặp lại nhiều nhất từ ​​'Học sinh'. Sắp xếp theo số lượng giảm dần. Nó sẽ giống như thế này:

tên	Số lượng
Alexander	27
Serge	10
Peter	7

Ồ, vì mục đích này chúng ta đã có Ivans, Peters và Sidors. Vì vậy không cần thiết phải thêm chúng. Chúng ta đã biết cách sắp xếp. Điều duy nhất chúng ta không nói đến hôm nay là cách chọn một số lượng bản ghi nhất định. Điều này đã xuất hiện trong các giải pháp trước đây cho các vấn đề về cơ sở dữ liệu. Ai chưa đọc thì đọc nhé. Đối với phần còn lại, hãy đi thẳng vào vấn đề: $ SELECT name, COUNT(*) là số lượng TỪ NHÓM sinh viên THEO tên ĐẶT HÀNG THEO COUNT(*) DESC LIMIT 3; Như có thể thấy từ truy vấn, nếu bạn biết thứ tự các toán tử trong truy vấn SELECT thì sẽ không có vấn đề gì khi thực hiện truy vấn đó. Và nhiệm vụ này vẫn thuộc về chúng ta. Và những kiến ​​thức được trình bày trước đó là khá đủ để giải quyết vấn đề này.

Nhiệm vụ 9

Điều kiện nhiệm vụ: Chọn 'Học sinh' có số lượng 'Sách' lớn nhất và được liên kết với 'Giáo viên'. Sắp xếp theo số lượng giảm dần. Nó sẽ giống như thế này:

Họ_của giáo viên	Họ_tên của học sinh	Số lượng sách
Petrov	Sidorov	7
Ivanov	thợ rèn	5
Petrov	Kankava	2

Như vậy, nhiệm vụ này rõ ràng là khó khăn hơn nhiệm vụ trước phải không? Không có gì lạ: nó có vẻ giống như tham gia... và nhiều hơn một) Đầu tiên, chúng ta cần hiểu phải làm gì... Rõ ràng là số lượng của Sách cần phải được nhóm lại. Nhưng cái gì? Và tại sao chúng ta nên nhóm chúng lại? Truy vấn bao gồm ba bảng, nhóm và sắp xếp. Đánh giá bằng thực tế là các bản ghi không có sách không được hiển thị, điều đó có nghĩa là bạn cần phải thực hiện INNER JOIN. Chúng tôi cũng sẽ đưa ra yêu cầu LEFT JOIN để không gặp vấn đề gì với việc này. Và có một số lựa chọn. Điều đầu tiên chúng ta làm là nối ba bảng thành một bản ghi. Tiếp theo, chúng tôi nhóm theo học sinh và thêm tên giáo viên vào đó. Chúng ta sẽ chọn gì? Tên giáo viên, học sinh và số sách. Hãy thêm dữ liệu cho yêu cầu:

• ba giáo viên;
• mười cuốn sách;
• kết nối hai học sinh với ba giáo viên.

Ba giáo viên

$ CHÈN VÀO giáo viên(last_name) GIÁ TRỊ ('Matvienko'); $ CHÈN VÀO giáo viên(last_name) GIÁ TRỊ ('Shevchenko'); $ CHÈN VÀO giáo viên(last_name) GIÁ TRỊ ('Vasilenko');

10 cuốn sách

Tôi sẽ lấy ID của học sinh thứ nhất và thứ hai. Tôi sẽ đính kèm sách với họ. Vì AUTO_INCREMENT chưa được đặt nên để không viết ID mới mỗi lần, bạn cần thực hiện như sau: $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; Tiếp theo, thêm sách cho học sinh đầu tiên: $ INSERT INTO book (title, SV_id) VALUES('book1', 1); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book2', 1); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book3', 1); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book4', 1); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book5', 1); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book6', 1); Và sách dành cho học sinh thứ 2: $ INSERT INTO book (title, SV_id) VALUES('book7', 2); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book8', 2); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book9', 2); $ CHÈN VÀO sách (tiêu đề, sinh viên_id) GIÁ TRỊ('book10', 2);

Kết nối giáo viên-học sinh

Để thực hiện việc này, hãy thêm sinh viên_x_teachers vào bảng: $ INSERT INTO sinh viên_x_teachers GIÁ TRỊ (1,1); $CHÈN VÀO sinh viên_x_giáo viên GIÁ TRỊ(1,2); $CHÈN VÀO sinh viên_x_giáo viên GIÁ TRỊ(2,3);

Hãy thực hiện yêu cầu

Chúng tôi thực hiện giai đoạn đầu tiên - chúng tôi liên kết ba bảng thành một bản ghi: $ SELECT * FROM giáo viên tch INNER JOIN sinh viên_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN sinh viên st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; Tất nhiên, chúng ta chưa có bất kỳ bản ghi nào, tuy nhiên, chúng ta có thể thấy rằng chúng ta đã kết nối thành công ba bảng. Bây giờ chúng ta thêm nhóm sách, sắp xếp và các trường mà chúng ta cần: $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM sinh viên st INNER THAM GIA sách b ON st.id = b.student_id INNER THAM GIA sinh viên_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER THAM GIA giáo viên tch ON tch.id = st_x_tch.teacher_id NHÓM THEO st.id ĐẶT HÀNG THEO sách DESC; Nhưng chúng tôi gặp lỗi trong SQL và câu trả lời sau: Mã lỗi: 1055. Biểu thức số 1 của danh sách SELECT không có trong mệnh đề GROUP BY và chứa cột không tổng hợp 'final_task.tch.last_name' không phụ thuộc về mặt chức năng vào các cột trong GROUP BY mệnh đề Nó không hoạt động khi lấy những phần tử này vì có mối quan hệ nhiều-nhiều giữa giáo viên và học sinh. Và đó là sự thật: chúng ta không thể chỉ có một giáo viên cho mỗi học sinh. Vậy hãy đi theo con đường khác. Hãy sử dụng thứ gọi là View SQL. Ý tưởng là gì: chúng tôi tạo một chế độ xem riêng, đó là một bảng mới, đã có sẵn nhóm mà chúng tôi cần. Và vào bảng này, chúng tôi sẽ thêm tên giáo viên cần thiết. Nhưng chúng tôi tính đến thực tế là có thể có nhiều giáo viên nên các mục sẽ được lặp lại. Tạo chế độ xem: $ TẠO XEM sinh viênBooks dưới dạng CHỌN st.last_name,st.id,COUNT(*) dưới dạng sách TỪ sinh viên st INNER THAM GIA sách b ON st.id=b.student_id NHÓM THEO st.id ĐẶT HÀNG THEO sách DESC; Tiếp theo, chúng tôi làm việc với dạng xem này dưới dạng một bảng đơn giản có ba trường: họ_tên, id_student và số lượng sách. Theo ID của học sinh, chúng ta cũng có thể thêm giáo viên thông qua hai phép nối: $ SELECT tch.last_name as 'Teacher', sbw.last_name 'Student', sbw.books as 'Books' from schoolbook sbw INNER JOIN Students_x_teachers stch ON sbw. id = stch.student_id INNER THAM GIA giáo viên tch ON tch.id = stch.teacher_id; Và bây giờ kết quả sẽ là: Huh! Đây là một yêu cầu phải không?) Kết quả đúng như mong đợi: một học sinh có id=1 có sáu cuốn sách và hai giáo viên, và một học sinh có id=2 có bốn cuốn sách và một giáo viên.

Nhiệm vụ 10

Điều kiện: Chọn 'Giáo viên' có số lượng 'Sách' lớn nhất trong số tất cả 'Học sinh' của mình. Sắp xếp theo số lượng giảm dần. Nó sẽ giống như thế này:

Họ_của giáo viên	Số lượng sách
Petrov	9
Ivanov	5

Ở đây chúng ta có thể sử dụng một yêu cầu làm sẵn từ nhiệm vụ trước. Chúng ta cần thay đổi điều gì về nó? Chúng ta đã có dữ liệu này rồi, chúng ta chỉ cần thêm một nhóm khác và xóa tên học sinh khỏi dữ liệu đầu ra. Nhưng trước tiên, hãy thêm một học sinh nữa vào giáo viên để kết quả trở nên thú vị hơn. Để làm điều này, chúng ta viết: $ INSERT INTO Students_x_teachers GIÁ TRỊ (2, 1); Và chính truy vấn đó: $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' từ sách học sinh sbw INNER JOIN sinh viên_x_teachers stch ON sbw.id = stch.student_id INNER JOIN giáo viên tch ON tch.id = stch . Teacher_id NHÓM THEO tch.id; Kết quả ta được: giáo viên Vasilenko có 10 cuốn sách và Shevchenko có 6...)

Nhiệm vụ 11

Điều kiện: Chọn một 'Giáo viên' có số lượng 'Sách' cho tất cả 'Học sinh' của mình là từ 7 đến 11. Sắp xếp theo số lượng giảm dần. Nó sẽ giống như thế này:

Họ_của giáo viên	Số lượng sách
Petrov	mười một
Sidorov	9
Ivanov	7

Đây là nơi chúng ta sẽ sử dụng HAVING. Chúng tôi đã nói chuyện về anh ấy. Yêu cầu sẽ giống hệt như trước, chỉ có điều bạn cần thêm điều kiện là số lượng sách sẽ nằm trong một phạm vi nhất định. Và như tôi đã nói trong các bài viết trước, khi chúng ta cần lọc trong khi nhóm và/hoặc trên các hàm tổng hợp, chúng ta cần sử dụng HAVING : $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from sinh viên sbw INNER JOIN sinh viên_x_teachers stch ON sbw.id = stch.student_id INNER JOIN giáo viên tch ON tch.id = stch.teacher_id NHÓM THEO tch.id CÓ SUM(sbw.books) > 6 VÀ SUM(sbw.books) < 12; Tôi đã đánh dấu phần tôi đã thêm. Và trên thực tế, kết quả như mong đợi: Chỉ có Vasilenko vượt qua vòng này))

Nhiệm vụ 12

Điều kiện: In tất cả 'last_name' và 'name' của tất cả 'Giáo viên' và 'Học sinh' với trường 'loại' (học sinh hoặc giáo viên). Sắp xếp theo thứ tự bảng chữ cái theo 'last_name'. Nó sẽ giống như thế này:

họ	kiểu
Ivanov	học sinh
Kankava	giáo viên
thợ rèn	học sinh
Sidorov	giáo viên
Petrov	giáo viên

Nghĩa là, chúng ta cần kết hợp hai đầu ra và đây chính xác là mục đích của UNION. Nói cách khác, chúng ta sẽ lấy các bản ghi từ học sinh và từ giáo viên rồi in cùng nhau: $ SELECT Last_name, 'teacher' làm loại từ giáo viên UNION ALL chọn Last_name, 'student' làm loại từ học sinh ORDER BY Last_name; Và sẽ có cả giáo viên và học sinh. Có vẻ như mọi thứ đều đơn giản, nhưng đây là lúc chúng ta nhìn vào kết quả. Và vì vậy bạn cần phải đoán hai điều.

Nhiệm vụ 13

Điều kiện: Thêm cột 'tỷ lệ' vào bảng 'Sinh viên' hiện có, cột này sẽ lưu trữ khóa học mà sinh viên hiện đang tham gia (giá trị số từ 1 đến 6). THAY ĐỔI BẢNG sinh viên THÊM Ràng buộc check_rate KIỂM TRA (tỷ lệ > 0 VÀ tỷ lệ < 7); Ở đây chúng tôi đang thêm một trường thông qua ALTER TABLE và CHECK để đặt giới hạn cho trường này từ 1 đến 6.

Nhiệm vụ 14

Điều kiện: Mục này không bắt buộc nhưng sẽ là một điểm cộng. Viết hàm sẽ duyệt qua tất cả 'Sách' và xuất ra tất cả 'tiêu đề' được phân tách bằng dấu phẩy. Ở đây bạn chỉ cần trả về một dòng do yêu cầu, dòng này sẽ chứa tất cả tên sách. Ở đây một lần nữa tôi phải google. Có một chức năng như vậy - GROUP_CONCAT , việc này được thực hiện rất đơn giản: $ SELECT GROUP_CONCAT(title) from book; Và thế là xong...)) Tất cả 14 nhiệm vụ đã sẵn sàng.

kết luận

Uuhhh... Thật không dễ dàng chút nào. Nó rất thú vị. Tôi chắc chắn rằng những nhiệm vụ đó rất xứng đáng. Trong khi thực hiện những nhiệm vụ này, chúng tôi đã điểm qua nhiều điều mà trước đây chúng tôi chưa biết:

• XEM SQL
• NHÓM_CONCAT
• LIÊN HIỆP

và như thế. Cảm ơn tất cả những người đã có sức mạnh để đọc và lặp lại những gì tôi đã làm. Ai biết cách đưa ra yêu cầu tốt hơn - hãy viết bình luận, tôi chắc chắn sẽ đọc chúng)

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.