우리는 데이터베이스와 SQL 언어를 분석합니다. (6부 - 최종과제 확인하기) - "Java 프로젝트 A부터 Z까지"

Java 프로젝트 생성에 관한 시리즈 기사입니다(다른 자료에 대한 링크는 끝에 있습니다). 그 목표는 핵심 기술을 분석하는 것이고, 그 결과는 텔레그램 봇을 작성하는 것입니다. 이 부분에는 데이터베이스의 최종 작업에 대한 분석이 포함되어 있습니다. 안녕하세요, 독자 여러분. 오늘 우리는 데이터베이스의 마지막 기사에서 작업을 분석할 것입니다. 중급 면접용이라 흥미롭습니다. 즉, 이 작업 후에는 이미 인터뷰에 갈 수 있으며 관계형 데이터베이스와 관련된 내용 중 적어도 일부를 성공적으로 통과하게 됩니다. 나는 이 기사가 얼마나 필요한지 알고 있으므로 이 기사를 유용하고 흥미롭게 만드는 데 내 모든 경험을 쏟아부을 것입니다. 그리고 기사 중간에 잠들지 않는다면, 내가 목표를 달성했다는 의미가 됩니다. 나는 전체 작업을 반복하지 않을 것입니다. 각 작업을 완료하기 전에 인용하고 이탤릭체로 밑줄을 긋겠습니다. 나는 이 기사를 읽는 모든 사람이 자신의 데이터베이스에서 모든 쿼리를 실행하고 동일한 결과를 얻을 것으로 기대합니다. 이는 비즈니스에 최대의 이익을 가져올 것입니다. 그리고 우리의 어려운 일을 누군가에게 도움을 주었다는 생각에 조금 더 행복해질 것입니다)

연습 1

id(기본 키), 이름, last_name, e_mail(고유) 필드가 포함된 'Student' 테이블을 생성하는 SQL 스크립트를 작성하세요. 우리는 이미 이 작업을 수행했으므로 아무런 문제가 없을 것입니다. 스크립트에서는 기본 키와 기본 키와 다른 고유 필드를 지정해야 합니다. 먼저 이 작업을 위한 새 데이터베이스를 생성해 보겠습니다. $ CREATE DATABASE final_task; 그리고 다음 데이터베이스를 사용해 보겠습니다. $ USE final_task; 환경이 설정되고 작업을 실행할 준비가 되면 다음 스크립트를 작성할 수 있습니다. $ CREATE TABLE Student ( id INT AUTO_INCREMENT, name VARCHAR(40), last_name VARCHAR(50), email VARCHAR(100), PRIMARY KEY ( id), UNIQUE (이메일 ) ); 지금까지 우리가 이미 겪은 일에 비하면 새로운 것은 없습니다. 어떤 코멘트도 불필요합니다. 계속 진행하겠습니다.

작업 2-3

id, title(id + title = 기본 키) 필드가 포함된 'Book' 테이블을 생성하는 SQL 스크립트를 작성하세요. '학생'과 '도서'를 '학생' 일대다 '도서' 관계로 연결합니다. 두 가지 작업을 하나로 합쳐서 더 빠르고 편리하게 만들어 보겠습니다. 이전 기사에서 별도의 외래 키를 추가하는 방법에 대해 이미 설명했습니다. 덧붙이자면, 우리가 어떻게, 무엇을 통해 연결하는지 기억해야 합니다. 이전 기사가 도움이 될 것이며 스크립트는 다음과 같습니다. $ CREATE TABLE book ( id INT, title VARCHAR(100), Student_id INT DEFAULT NULL, PRIMARY KEY (id, title), FOREIGN KEY (student_id) REFERENCES Student (id) ) ); 이 간단한 방법으로 테이블 PRIMARY KEY (id, title) 에 대한 복합 키를 추가했습니다 . 이제 키는 정확히 쌍이 됩니다. 이는 테이블에 동일한 ID 필드 값이 두 개 이상 있을 수 있음을 의미합니다. 그리고 제목도 똑같습니다.

작업 4

id(기본 키), 이름, last_name, e_mail(고유), 제목 필드가 포함된 'Teacher' 테이블을 생성하는 SQL 스크립트를 작성하세요. 쿼리를 위해 데이터베이스를 계속 준비하고 교사 테이블을 만듭니다. $ CREATE TABLE Teacher( id INT AUTO_INCREMENT, name VARCHAR(30), last_name VARCHAR(30), email VARCHAR(100), subject VARCHAR(40), PRIMARY KEY ( id), UNIQUE (이메일) ); 여기까지는 어렵지 않죠? 벌써 세 가지 작업이 끝났습니다!

작업 5

'학생'과 '교사'를 '학생' 다대다 교사 관계로 연결합니다. 이제 이것이 더 흥미로워졌습니다! 우리는 지난번에 이것에 대해 이야기했습니다. 이를 달성하는 데 필요한 것이 무엇인지 상기시켜 드리겠습니다. 학생-교사 쌍을 저장할 중간 테이블을 만들어야 합니다. 그것의 도움으로 다대다 관계를 만드는 것이 가능할 것입니다. 그러므로 Students_x_techers 테이블을 만들어 보겠습니다 . 명명 방식은 개방적이며 다음과 같을 수도 있습니다: Student_teacher . $ CREATE TABLE Students_x_teachers ( Student_id INT NOT NULL, Teacher_id INT NOT NULL, PRIMARY KEY (student_id, Teacher_id), FOREIGN KEY (student_id) REFERENCES 학생(id), FOREIGN KEY (teacher_id) REFERENCES Teacher(id) ); 보시다시피 모든 것이 명확하고 일관되게 수행됩니다. 우리는 Student_id와 Teacher_id라는 두 개의 외래 키에 대한 복합 키를 가지고 있습니다. 왜 외래 키도 필요합니까? 기록되는 쌍에 대해 학생 및 교사 테이블의 기록이 존재하는지 확인하기 위해.

작업 6

성에 'oro'가 있는 '학생'을 선택하세요(예: 'Sidorov', 'Voronovsky'). 우리에게 흥미롭고 시각적인 내용을 제공하기 위해 먼저 여러 명의 학생을 추가하여 그 중 일부는 이 요청에 적합하고 일부는 그렇지 않도록 하는 것이 좋습니다. 따라서 요청 결과에 포함되어야 하는 사람을 적어 보겠습니다 . $ INSERT INTO 학생 (이름, 성, 이메일) VALUES ('Ivan', 'Sidorov', 'ivan.sidorov@gmail.com'); $ INSERT INTO 학생(이름, 성, 이메일) VALUES ('Nikolay', 'Voronovsky', 'nikolay.voronovsky@gmail.com'); 들어가지 말아야 할 사람: $ INSERT INTO 학생 (이름, 성, 이메일) VALUES ('Roman', 'Fortny', 'roman.fortny@gmail.com'); $ INSERT INTO 학생(이름, 성, 이메일) VALUES('Kostya', 'Petrov', 'kostya.petrov@gmail.com'); 결과를 확인해 보겠습니다. 학생 테이블의 데이터 목록을 살펴보겠습니다. $ SELECT * FROM Student; 그리고 우리는 총 4개의 레코드가 있는데 그 중 2개는 적합해야 하고 2개는 적합하지 않아야 한다는 결과를 얻습니다. 요청에 대한 모든 데이터를 준비한 후 작업 자체에 대한 요청을 만들 수 있습니다. $ SELECT * FROM Student WHERE last_name LIKE '%oro%'; 결과적으로 Ivan과 Nikolai가 목록을 검토했습니다.

작업 7

다음 작업은 다음과 같습니다. 'Student' 테이블에서 모든 성('last_name')과 반복 횟수를 선택합니다. 데이터베이스에 이름이 같은 것이 있다는 것을 고려하십시오. 수량별로 내림차순으로 정렬합니다. 다음과 같아야 합니다.

성	수량
페트로프	15
이바노프	12
시도로프	삼

명확성을 위해서는 더 많은 데이터를 추가해야 합니다. 더 이상 고민하지 않고 친족관계를 모르는 Petrovs, Ivanovs 및 Sidorovs를 추가해 보겠습니다. ;) 이메일 주소를 만들지 않고 새 항목에서 제외하겠습니다. 다음 명령을 12번 실행해 보겠습니다. $ INSERT INTO Student (name, last_name) VALUES ('Ivan', 'Ivanov'); 15개의 Petrov를 추가해 보겠습니다. $ INSERT INTO Student (name, last_name) VALUES ('Petr', 'Petrov'); 그리고 두 개의 Sidorov(이미 하나가 있습니다))): $ INSERT INTO 학생 (이름, 성) VALUES ('Sidor', 'Sidorov'); 이제 데이터가 준비되었습니다. 이러한 데이터를 얻으려면 그룹화를 수행해야 하며, 그룹화를 수행하려면 Group By 연산자를 사용해야 하며 이를 last_name 필드로 수행해야 합니다. 또한 반복 횟수가 수량 으로 지정되어 있음을 알 수 있으며 여기에서 SQL에서 별칭을 만드는 방법도 기억해야 합니다. $ SELECT last_name, COUNT(*) as amount FROM Student GROUP BY last_name ORDER BY COUNT(*) DESC ; 그래서 나는 Petrovs로 그것을 과장했습니다. 그것은 16으로 밝혀졌습니다))

작업 8

조건: '학생'에서 가장 많이 반복되는 이름 상위 3개를 선택하세요. 수량별로 내림차순으로 정렬합니다. 다음과 같아야 합니다.

이름	수량
알렉산더	27
세르게이	10
베드로	7

아, 이 목적을 위해 우리는 이미 Ivans, Peters 및 Sidors를 가지고 있습니다. 따라서 추가할 필요가 없습니다. 우리는 이미 정렬하는 방법을 알고 있습니다. 오늘 우리가 이야기하지 않은 유일한 것은 특정 수의 레코드를 선택하는 방법입니다. 이는 데이터베이스 문제에 대한 이전 솔루션에서 이미 나타났습니다. 안 읽어보신 분들은 꼭 읽어보세요. 나머지 부분은 바로 요점으로 들어가겠습니다. $ SELECT name, COUNT(*) as amount FROM 학생 GROUP BY name ORDER BY COUNT(*) DESC LIMIT 3; 쿼리에서 알 수 있듯이 SELECT 쿼리에서 연산자의 순서를 알면 해당 쿼리를 실행하는 데 문제가 없습니다. 그리고 이 임무는 여전히 우리에게 달려있습니다. 그리고 앞서 제시된 지식은 이 문제를 해결하기에 충분합니다.

작업 9

작업조건 : '도서'와 연계된 '선생님' 수가 가장 많은 '학생'을 선택합니다. 수량별로 내림차순으로 정렬합니다. 다음과 같아야 합니다.

선생님의 성	학생의 성	도서 수량
페트로프	시도로프	7
이바노프	스미스	5
페트로프	칸카바	2

그렇다면 이번 작업은 확실히 이전 작업보다 더 어려운 작업이겠죠? 당연한 일입니다. 조인 냄새가 납니다... 하나 이상) 먼저 무엇을 해야 할지 이해해야 합니다... Book의 수량을 그룹화해야 한다는 것은 분명합니다. 근데 뭐? 그리고 왜 그들을 그룹화해야 할까요? 쿼리에는 세 개의 테이블, 그룹화 및 정렬이 포함됩니다. 책이 없는 레코드가 표시되지 않는다는 점으로 판단하면 INNER JOIN을 수행해야 함을 의미합니다. 이에 대해 문제가 발생하지 않도록 LEFT JOIN도 요청해 보겠습니다. 그리고 몇 가지 옵션이 있습니다. 가장 먼저 할 일은 세 개의 테이블을 하나의 레코드로 결합하는 것입니다. 다음으로 학생별로 그룹화하고 교사의 이름을 추가합니다. 우리는 무엇을 선택할 것인가? 선생님의 이름, 학생의 이름, 책의 수. 요청에 대한 데이터를 추가해 보겠습니다.

• 세 명의 교사;
• 10권의 책;
• 두 명의 학생과 세 명의 교사를 연결합니다.

선생님 3명

$ INSERT INTO 교사(성) VALUES ('Matvienko'); $ INSERT INTO 교사(성) VALUES ('셰브첸코'); $ INSERT INTO 교사(성) VALUES ('Vasilenko');

10권

1, 2학년 학생증을 가져갈게요. 나는 그들에게 책을 첨부할 것이다. AUTO_INCREMENT가 설정되지 않았기 때문에 매번 새로운 ID를 쓰지 않으려면 다음과 같이 해야 합니다. $ ALTER TABLE book MODIFY id INT NOT NULL AUTO_INCREMENT; 다음으로, 첫 번째 학생을 위한 책을 추가합니다: $ INSERT INTO book (title, Student_id) VALUES('book1', 1); $ INSERT INTO 책(제목, 학생 ID) VALUES('book2', 1); $ INSERT INTO 도서(제목, 학생 ID) VALUES('book3', 1); $ INSERT INTO 도서(제목, 학생 ID) VALUES('book4', 1); $ INSERT INTO 도서(제목, 학생 ID) VALUES('book5', 1); $ INSERT INTO 도서(제목, 학생 ID) VALUES('book6', 1); 그리고 두 번째 학생을 위한 책: $ INSERT INTO book (title, Student_id) VALUES('book7', 2); $ INSERT INTO 도서(제목, 학생 ID) VALUES('book8', 2); $ INSERT INTO 도서(제목, 학생 ID) VALUES('book9', 2); $ INSERT INTO 도서(제목, 학생 ID) VALUES('book10', 2);

교사-학생 연결

이렇게 하려면 테이블에 Students_x_teachers를 추가하십시오: $ INSERT INTO Students_x_teachers VALUES (1,1); $INSERT INTO Students_x_teachers VALUES(1,2); $INSERT INTO Students_x_teachers VALUES(2,3);

요청을 구현해보자

첫 번째 단계를 수행합니다. 세 개의 테이블을 하나의 레코드로 연결합니다. $ SELECT * FROM Teacher tch INNER JOIN Students_x_teachers st_x_tch ON tch.id = st_x_tch.teacher_id INNER JOIN Student st ON st_x_tch.student_id = st.id INNER JOIN book b ON st .id = b.student_id; 물론 아직 아무런 기록도 없지만, 3개의 테이블을 성공적으로 연결한 것을 확인할 수 있습니다. 이제 책 그룹화, 정렬 및 필요한 필드를 추가합니다. $ SELECT tch.last_name, st.last_name, st.id, COUNT(*) as books FROM Student st INNER JOIN book b ON st.id = b.student_id INNER JOIN Students_x_teachers st_x_tch ON st.id = st_x_tch.student_id INNER JOIN 교사 tch ON tch.id = st_x_tch.teacher_id GROUP BY st.id ORDER BY books DESC; 그러나 SQL에서 다음과 같은 오류가 발생합니다. 오류 코드: 1055. SELECT 목록의 표현식 #1은 GROUP BY 절에 없으며 GROUP BY의 열에 기능적으로 종속되지 않는 집계되지 않은 열 'final_task.tch.last_name'을 포함합니다. 절 교사와 학생 사이에 다대다 관계가 있기 때문에 이러한 요소를 취하는 것은 작동하지 않습니다. 그리고 그것은 사실입니다. 학생 한 명당 교사를 한 명만 둘 수는 없습니다. 그럼 다른 방향으로 가봅시다. View SQL이라는 것을 사용해 보겠습니다. 아이디어는 무엇입니까? 필요한 그룹화가 이미 포함된 새 테이블인 별도의 뷰를 만듭니다. 그리고 이 표에 필요한 교사 이름을 추가하겠습니다. 그러나 우리는 교사가 한 명 이상일 수 있다는 사실을 고려하므로 항목이 반복됩니다. 보기 만들기: $ CREATE VIEW StudentBooks as SELECT st.last_name,st.id,COUNT(*) as books FROM Student st INNER JOIN book b ON st.id=b.student_id GROUP BY st.id ORDER BY books DESC; 다음으로, 이 뷰를 학생 성, 학생 ID, 도서 수라는 세 가지 필드가 있는 간단한 테이블로 작업합니다. 학생 ID에 따라 두 가지 조인을 통해 교사를 추가할 수도 있습니다. $ 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 JOIN 교사 tch ON tch.id = stch.teacher_id; 이제 결과는 다음과 같습니다. 허! 요청이겠죠?) 예상한 대로 id=1인 학생은 6권의 책과 2명의 교사가 있고, id=2인 학생은 4권의 책과 1명의 교사가 있습니다.

작업 10

조건: '학생' 중 '책' 수가 가장 많은 '선생님'을 선택하세요. 수량별로 내림차순으로 정렬합니다. 다음과 같아야 합니다.

선생님의 성	도서 수량
페트로프	9
이바노프	5

여기서는 이전 작업에서 이미 만들어진 요청을 사용할 수 있습니다. 이에 대해 우리는 무엇을 바꿔야 합니까? 이 데이터는 이미 있으므로 다른 그룹을 추가하고 출력 데이터에서 학생 이름을 제거하면 됩니다. 하지만 먼저 교사에 학생을 한 명 더 추가하여 결과를 더 흥미롭게 만들어 보겠습니다. 이를 위해 다음과 같이 작성합니다: $ INSERT INTO Students_x_teachers VALUES (2, 1); 그리고 쿼리 자체는 다음과 같습니다. $ SELECT tch.last_name as 'Teacher', SUM(sbw.books) as 'Books' from Studentbook sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id INNER JOIN Teacher tch ON tch.id = stch .teacher_id GROUP BY tch.id; 결과적으로 우리는 Vasilenko 선생님이 10권의 책을 가지고 있고 Shevchenko가 6권의 책을 가지고 있다는 것을 알게 됩니다...)

작업 11

조건: 모든 '학생'에 대한 '도서' 수가 7~11권인 '선생님'을 선택하세요. 수량별로 내림차순으로 정렬합니다. 다음과 같아야 합니다.

선생님의 성	도서 수량
페트로프	열하나
시도로프	9
이바노프	7

여기서는 HAVING을 사용할 것입니다. 우리는 그에 대해 이야기했습니다. 요청은 이전과 동일하며, 책의 수가 특정 범위에 있어야 한다는 조건만 추가하면 됩니다. 그리고 이전 기사에서 말했듯이 그룹화 및/또는 집계 함수에서 필터링을 수행해야 하는 경우 HAVING을 사용해야 합니다 . $ SELECT tch.last_name을 'Teacher'로, SUM(sbw.books)을 'Books'로 Studentbook sbw INNER JOIN Students_x_teachers stch ON sbw.id = stch.student_id 내부 JOIN 교사 tch ON tch.id = stch.teacher_id GROUP BY tch.id HAVING SUM(sbw.books) > 6 AND SUM(sbw.books) < 12; 제가 추가한 부분을 강조해 두었습니다. 그리고 실제로 예상되는 결과는 Vasilenko만이 이번 라운드를 통과한 것입니다.))

작업 12

조건: 'type' 필드(학생 또는 교사)와 함께 모든 'Teacher' 및 'Student'의 모든 'last_name' 및 'name'을 인쇄합니다. 'last_name'을 기준으로 알파벳순으로 정렬합니다. 다음과 같아야 합니다.

성	유형
이바노프	학생
칸카바	선생님
스미스	학생
시도로프	선생님
페트로프	선생님

즉, 두 개의 출력을 결합해야 하는데 이것이 바로 UNION의 목적입니다. 즉, 학생과 교사로부터 기록을 가져와 함께 인쇄합니다. $ SELECT last_name, 'teacher' as type from Teacher UNION ALL select last_name, 'student' as type from Student ORDER BY last_name; 그리고 교사와 학생이 모두있을 것입니다. 모든 것이 단순 해 보이지만 이것은 이미 결과를 볼 때입니다. 따라서 두 가지를 추측해야 합니다.

작업 13

조건: 기존 'Student' 테이블에 'rate' 열을 추가합니다. 이 열은 학생이 현재 수강 중인 강좌(1~6의 숫자 값)를 저장합니다. ALTER TABLE 학생 ADD CONSTRAINT check_rate CHECK(비율 > 0 AND 비율 < 7); 여기서는 ALTER TABLE 및 CHECK를 통해 필드를 추가하여 이 필드에 대한 제한을 1에서 6까지 설정합니다.

작업 14

조건: 이 항목은 필수는 아니지만 플러스가 될 것입니다. 모든 '책'을 살펴보고 모든 '제목'을 쉼표로 구분하여 출력하는 함수를 작성하세요. 여기에서는 요청 결과로 책의 모든 제목이 포함된 줄을 반환하기만 하면 됩니다. 여기서 다시 구글링을 해야 했습니다. 매우 간단하게 수행되는 GROUP_CONCAT 함수가 있습니다 . $ SELECT GROUP_CONCAT(title) from book; 그게 다입니다...)) 14개의 작업이 모두 준비되었습니다.

결론

윽... 쉽지 않더군요. 그것은 흥미로웠다. 작업은 그만한 가치가 있었고 확신합니다. 이러한 작업을 수행하는 동안 우리는 이전에 알려지지 않았던 많은 사항을 검토했습니다.

• SQL 보기
• GROUP_CONCAT
• 노동 조합

등등. 제가 한 일을 읽고 반복할 수 있는 힘을 주신 모든 분들께 감사드립니다. 요청을 더 잘 만드는 방법을 아는 사람-댓글을 작성하면 확실히 읽을 것입니다)

시리즈의 모든 자료 목록은 이 기사의 시작 부분에 있습니다.