우리는 데이터베이스와 SQL 언어를 분석합니다. (3부) - "Java 프로젝트 A부터 Z까지"

Java 프로젝트 생성에 관한 시리즈 기사입니다(다른 자료에 대한 링크는 끝에 있습니다). 그 목표는 핵심 기술을 분석하는 것이고, 그 결과는 텔레그램 봇을 작성하는 것입니다.

• 숙제 확인 중
  • 기본 키 추가
  • 몰도바 추가
  • 도시 테이블 만들기
  • 도시에 외래 키 추가
  • 보너스 부분. 테스트
• SELECT 문
  • WHERE 매개변수
  • ORDER BY 매개변수
  • GROUP BY 매개변수
• 숙제
• 결론

안녕하세요, 신사 숙녀 여러분, 데이터베이스, SQL 및 기타 사항에 대해 계속해서 이야기해 보겠습니다. 오늘의 자료에는 이론 부분과 실습 부분이 포함됩니다. 지난번에 모든 것을 설정하는 방법, 데이터베이스와 테이블을 만들고 그로부터 데이터를 가져오는 방법에 대해 이야기했음을 상기 시켜 드리겠습니다 . 이제 원격 감지로 문제가 해결되었는지 확인할 시간입니다. 제 생각에는 절반은 이전 기사를 바탕으로만 수행할 수 있었습니다. 애플리케이션을 적절하게 조립하고 모든 것을 다소 아름답게 만들려면 데이터베이스에 대해 이야기해야 하며 데이터베이스에 대해 이야기하려면 많은 시간을 소비해야 한다는 것이 밝혀졌습니다.

숙제 확인 중

임무를 성공적으로 완수한 모든 분들께 큰 존경을 표합니다. 이것은 당신에게만 이것이 필요하고 그것이 당신에게만 도움이 된다는 것을 이해한다는 것을 의미합니다. 내 임무를 등한시한 분들을 위해 다음 조건을 상기시켜 드리겠습니다.

1. ID 필드의 기본 키(PRIMARY KEY)를 국가 테이블 스키마에 추가해야 합니다.
2. 국가 테이블에 다른 국가인 몰도바를 추가합니다.
3. 이전 기사의 구성표에 따라 설명된 모든 필드를 포함하는 테이블 도시를 만듭니다. 필드 이름은 id, name, country_id, 인구입니다.
4. 도시 테이블에 기본 키를 추가합니다.
5. city ​​테이블에 외래 키를 추가합니다.

시작하려면 이전 기사의 첫 번째 부분을 사용하여 데이터베이스 터미널로 이동해 보겠습니다.

기본 키 추가

두 가지 방법으로 기본 키(PRIMARY KEY)를 추가할 수 있습니다. 즉, 테이블을 생성할 때 즉시 추가하거나, 생성 후에 ALTER TABLE을 사용하여 추가할 수 있습니다.

테이블 생성 중 기본 키

이미 테이블을 생성했고 이를 삭제하지 않으면 이 데이터베이스 내에서 이 접근 방식을 보여줄 수 없으므로 모든 작업을 수행할 임시 테스트 데이터베이스를 생성하기만 하면 됩니다. 다음 명령을 입력해 보겠습니다.

• 새 데이터베이스를 생성합니다:

  $CREATE 데이터베이스 테스트;

• 기본 키를 추가하는 테이블을 만듭니다.

  $ CREATE TABLE 국가(id INT, 이름 VARCHAR(30), PRIMARY KEY(id));

일반적으로 복잡한 것은 없습니다. 변수를 선언한 후 다음 부분 PRIMARY KEY(id)가 추가되는데 , 여기에 기본 키가 될 필드의 이름이 괄호 안에 전달됩니다. 그리고 테이블 스키마가 어떻게 변경되었는지 살펴보겠습니다. $ DESC country; 보시다시피 PRI 값이 id 항목의 키 필드에 표시되었습니다 .

테이블 생성 후 기본 키

앞서 말했듯이 테이블 생성 후 첫 번째 키는 ALTER TABLE을 사용하여 할당할 수 있습니다 . 이 예제를 도시 데이터베이스 에서 실행하겠습니다 .

• 테스트 데이터베이스에서 데이터베이스로 이동해 보겠습니다.

  $USE 도시;

• 우리가 확실히 데이터베이스에 있는지 확인해 보겠습니다(거기에는 인구라는 또 다른 필드가 있어야 합니다). 이를 위해 다음과 같이 작성합니다.

  $ DESC 인구;

모든 것이 정확합니다. 테이블은 우리 것입니다. 다음을 작성해 보겠습니다.

$ ALTER TABLE 국가 기본 키 추가(id);

• 다음 명령으로 즉시 확인하십시오.

  $DESC 국가;

그림에서 볼 수 있듯이 모든 것이 정확하고 PRI 값이 정확히 있어야 할 위치에 있습니다. 그건 그렇고, 우리는 테스트 데이터베이스로 작업했습니다. 이제 삭제해야 합니다. 왜 서버를 복잡하게 만들어야 합니까? 이를 위해 우리는 꽤 잘 알려진 명령인 $ DROP DATABASE test;를 사용합니다.

몰도바 추가

먼저 무엇을 녹음할지 결정해야 합니다. 다음 ID는 4입니다. 이름은 Moldova이고 인구는 3550900입니다. 따라서 이미 알고 있는 INSERT INTO 명령을 실행합니다. $ INSERT INTO country VALUES (4, 'Moldova', 3550900); 그리고 이 값이 데이터베이스에 정확하게 있는지 확인합니다. $ SELECT * FROM country WHERE id = 4; 데이터 요청에서 어떤 필드를 검색할지 즉시 결정했기 때문에 필요한 레코드 하나만 얻었습니다.

도시 테이블 만들기

데이터베이스에 관한 첫 번째 기사 의 다이어그램을 사용하여 테이블에 대해 필요한 정보를 얻습니다. 여기에는 다음 필드가 포함됩니다.

• id - 고유 식별자입니다.
• 이름 — 도시 이름;
• country_id — 국가 외래 키;
• 인구 - 도시의 인구.

매번 고유 아이디를 작성하는 건 좀 스트레스가 되지 않나요? 나는 이것을 MySQL 당국 에 맡기고 싶습니다 . 그리고 그런 방법이 있습니다 - AUTO INCREMENT . 이것을 디지털 필드에 추가해야 하며, 값을 명시적으로 전달하지 않으면 MySQL 자체가 이전 값에 비해 ID를 1씩 증가시킵니다. 따라서 테이블 생성은 다음과 같습니다: $ CREATE TABLE city ( id INT AUTO_INCREMENT, name VARCHAR(30), country_id INT, 인구 INT, PRIMARY KEY (id)); 모든 것이 올바르게 수행되었는지 확인하기 위해 테이블 ​​다이어그램을 살펴보겠습니다. $ DESC city; 테이블 다이어그램에서 볼 수 있듯이 id 필드에 대한 새로운 설명인 auto_increment가 있습니다. 그래서 우리는 모든 일을 올바르게 했습니다. 완전히 구성된 테이블의 데이터를 확인해 보겠습니다. 이를 위해 작업의 마지막 부분인 외래 키를 수행합니다.

도시에 외래 키 추가

외래 키의 경우 다음 명령이 있습니다. $ ALTER TABLE city ADD FOREIGN KEY (country_id) REFERENCES country(id); 그리고 테이블 스키마에 어떤 문제가 있는지 즉시 확인해 보겠습니다. 한 시간 동안 변경되었나요? $DESC 도시;

보너스 부분. 테스트

작업에 추가하는 것을 잊어버렸습니다. 첫 번째 부분의 스크린샷에 있던 데이터를 입력하세요. 깜빡했으니까 이제 제가 직접 할게요. 관심이 있으신 분들은 저 없이도 직접 해보실 수 있으며 확인하겠습니다. ;) Kharkov, Kyiv, Minsk, Odessa, Voronezh가 있었고 키시나우도 추가하겠습니다. 하지만 이번에는 ID를 전송하지 않고 건너뜁니다. $ INSERT INTO city (name, country_id, 인구) VALUES ('Kharkov', 1, 1443000), ('Kyiv', 1, 3703100), ('Minsk' , 3, 2545500), ('Odessa', 1, 1017699), ('Voronezh', 2, 1058261), ('Kishinev', 4, 695400); 보시다시피 하나의 INSERT INTO 명령을 사용하여 여러 항목을 동시에 만들 수 있습니다. 편리한 점을 기억하세요.) 즉시 테이블에 무엇이 있는지 살펴보겠습니다. $ SELECT * FROM city; AUTO_INCREMENT - 우리가 원하는 대로 정확하게 작동했습니다. 신분증 파일은 제출하지 않았는데도 모두 작성되어 있습니다. 외래 키는 종속적인 것입니다. 제대로 작동하는지 확인하려면 외래 테이블에 존재하지 않는 외래 키를 작성해 보면 됩니다. id = 5가 카자흐스탄이라고 결정했다고 가정해 보겠습니다. 그러나 실제로는 국가 목록에 없습니다. 데이터베이스가 맹세하는지 확인하려면 도시를 추가하세요. Astana: $ INSERT INTO city (name, country_id, 인구) VALUES ('Astana', 5, 1136156); 그리고 자연스럽게 다음과 같은 오류가 발생합니다. 이제 외래 키를 사용하면 데이터베이스에 없는 도시에 국가를 할당하지 않도록 할 수 있습니다. 숙제의 이 부분은 완료된 것으로 간주될 수 있습니다. 새 숙제로 넘어갑니다 :)

SELECT 문

이제 모든 것이 더 이상 무섭지 않은 것 같죠? Java 개발자에게는 데이터베이스에 대한 지식이 필수라는 점을 다시 한 번 말씀드리고 싶습니다. 데이터베이스가 없으면 아무데도 갈 수 없습니다. 예, 이미 신청서 작성을 시작하고 싶습니다. 동의합니다. 그러나 그것은 필요합니다. 그래서 우리는 이 방법을 계속할 것입니다. SELECT 문을 사용하여 데이터베이스에서 데이터를 검색합니다. 즉, 이것은 일반적인 DML 작업입니다(이미 그것이 무엇인지 잊어버렸습니까?...))) 전에 기사를 다시 읽으십시오). 관계형 데이터베이스의 이점은 무엇입니까? 데이터를 집계하고 검색하는 데 뛰어난 기능을 가지고 있습니다. 이것이 SELECT 문이 사용되는 이유입니다. 복잡한 일은 없을 것 같죠? 하지만 아직 이해해야 할 부분이 많은 것으로 드러났습니다.) 기반을 다질 수 있는 기본 사항을 이해하는 것이 중요합니다. SELECT 문을 사용하는 가장 간단한 쿼리는 한 테이블에서 모든 데이터를 선택하는 것입니다. SELECT 쿼리에서 연산자가 정확히 어떤 순서로 들어가야 하는지에 대한 위키의 설명이 정말 마음에 들었기 때문에 뻔뻔스럽게 여기에 복사하겠습니다.

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

여기서는 GROUP BY 연산자를 먼저 배치한 다음 WHERE 연산자를 배치할 수 없음을 알 수 있습니다. 나중에 어디서 왔는지 명확하지 않은 실수에 대해 분노하지 않도록 이것을 기억해야합니다. $SELECT * 도시에서; 그러나 모든 데이터를 긁어내는 것은 분명히 우리에게 재미가 없습니다. 현미경으로 못을 망치려는 경우에도 마찬가지입니다 [1] , [2] . 데이터베이스는 필터링, 정렬, 집계 작업을 Java 코드보다 훨씬 빠르게 수행하므로 이 문제는 데이터베이스에 맡기는 것이 좋습니다. 따라서 작업을 복잡하게 하여 새로운 기능을 열게 됩니다.

WHERE 매개변수

선택 항목을 필터링하려면 WHERE 단어가 사용됩니다 . 이는 다음과 같이 해석되어야 합니다: SELECT * FROM tablename(tablename 테이블에서 모든 필드 선택) WHERE talbe_row = 1(여기서 레코드에서 table_row 필드는 1과 같습니다). 쿼리에서 키워드의 순서가 중요하다는 점에 유의하는 것이 중요합니다. WHERE a =1 FROM table_name SELECT *를 쓸 수 없습니다. 러시아어의 경우 이는 괜찮고 일부에게는 그다지 나쁘지 않은 것처럼 보일 수 있지만 SQL의 경우 이는 허용되지 않습니다. 다음 쿼리를 작성합니다. $ SELECT * FROM city WHERE country_id = 1; 그리고 우리는 우크라이나 도시를 선택했습니다. 나쁘지 않죠? 우크라이나어뿐만 아니라 벨로루시어도 원한다면 어떻게 될까요? 이를 위해 필드가 취할 수 있는 값 모음을 나열할 수 있습니다. $SELECT * FROM city WHERE country_id IN(1, 3); 그리고 우리는 이미 이에 대응하는 두 나라의 도시를 갖고 있습니다. 필터링할 조건이 여러 개 있으면 어떻게 되나요? 인구가 2백만이 넘는 도시를 원한다고 가정해 볼까요? 이렇게 하려면 OR 및 AND 단어를 사용합니다 . $ SELECT * FROM city WHERE country_id IN (1, 3) AND 인구 > 2000000; 좋습니다. 하지만 정규식을 통해 이름을 검색하는 조건을 하나 더 추가해야 하는 경우에는 어떻게 해야 합니까 ? 예를 들어, 도시 철자를 어떻게 쓰는지 기억하지만 완전히는 아닙니다... 이렇게 하려면 필터링 표현식에 LIKE 키워드를 추가하면 됩니다 . $ SELECT * FROM city WHERE country_id IN (1, 3) AND 인구 > 2000000 OR "%hark%"와 같은 이름; 그리고 이런 식으로 우리는 Kharkov도 얻었습니다. 그 결과, 우리의 검색 결과가 매우 좋았다고 말할 수 있습니다. 그런데 ID가 아닌 인구순으로 정렬하고 싶은데 어떻게 해야 할까요? 응 아주 간단해...

ORDER BY 매개변수

ORDER BY를 사용하면 특정 필드별로 받은 레코드를 정렬할 수 있습니다. 숫자와 문자열을 모두 정렬합니다. 이전 쿼리를 확장하여 인구별로 정렬하고 ORDER BY 인구를 추가해 보겠습니다. $ SELECT * FROM city WHERE country_id IN (1, 3) AND 인구 > 2000000 OR name LIKE “%hark%” ORDER BY 인구; 보시다시피 정렬은 자연순, 즉 오름차순으로 이루어졌습니다. 그 반대를 원한다면 어떻게 될까요? 이렇게 하려면 DESC라는 단어를 추가해야 합니다. $ SELECT * FROM city WHERE country_id IN (1, 3) AND Population > 2000000 OR name LIKE “%hark%” ORDER BY Population DESC; 이제 정렬은 인구 감소를 기반으로 합니다. 그리고 데이터베이스는 이 작업을 매우 빠르게 수행합니다. Collections.sort를 비교할 수 없습니다. 이제 행별로, 이름별로 역순으로 정렬해 보겠습니다. $ SELECT * FROM city WHERE country_id IN (1, 3) AND 인구 > 2000000 OR name LIKE “%hark%” ORDER BY name DESC;

GROUP BY 매개변수

특정 필드별로 레코드를 그룹화하는 데 사용됩니다. 이는 일반적으로 집계 함수를 사용하는 데 필요합니다... 집계 함수란 무엇입니까?)) 서로 다른 레코드에 대해 동일한 경우 일부 필드를 기준으로 그룹화하는 것이 합리적입니다. 예제를 사용하여 이것이 무엇을 의미하는지 살펴보겠습니다. 도시에 외래 키(국가 ID)가 있다고 가정해 보겠습니다. 따라서 같은 국가의 도시에 대해서는 ID가 동일합니다. 따라서 다음과 같이 레코드를 가져와 그룹화할 수 있습니다. $ SELECT country_id, COUNT(*) FROM city GROUP BY country_id; 하지만 집계 기능이 없으면 약간 밋밋해 보인다는 점을 인정해야 합니다. 따라서 가장 일반적인 몇 가지 기능을 살펴보겠습니다.

• COUNT - 레코드 수, 그룹화하지 않고 사용할 수 있으며 COUNT(*) 로 사용됩니다 . 일부 필드를 기준으로 그룹화하는 경우 - COUNT(groupped_field);
• MAX - 특정 필드의 최대값을 찾습니다.
• MIN - 특정 필드의 최소값을 찾습니다.
• SUM - 특정 필드의 합계를 찾습니다.
• AVG - 평균값을 찾습니다.

일반적으로 이러한 기능은 그룹화 없이 사용할 수 있으며, 그 경우에는 하나의 필드만 표시됩니다. 우리 도시 인구에 대해 시도해 보겠습니다. $ SELECT COUNT(*) FROM city; 그들이 구한 것은 그들이 얻은 것이었습니다. 단지 레코드 수입니다. 때로는 이것이 유용합니다. 예를 들어, 특정 저자의 기사 수를 알아야 하는 경우입니다. 데이터베이스에서 그것들을 긁어내고 계산할 필요가 없습니다. 간단하게 COUNT()를 사용할 수 있습니다. $ SELECT AVG(인구) FROM 도시; $ SELECT MIN(인구) FROM 도시; 그리고 여기서 그룹화가 적용됩니다. 예를 들어, 임무는 해당 국가에서 가장 작은 도시를 얻는 것입니다. 이 작업을 수행하는 방법을 이미 알고 계십니까? 직접 시도해 보고 다음을 시청하세요. $ SELECT country_id as Country, MIN(population) FROM city WHERE GROUP BY country_id; 지금까지는 국가의 ID만 볼 수 있지만 중요하지 않습니다. 다음번에는 모든 작업을 수행하겠습니다. 따라서 이미 결과가 있으며 우리가 원하는 것을 얻었습니다. ID = 1인 국가에서 가장 작은 도시입니다. 나머지 기능은 동일합니다. 그룹화 및 집계를 사용할 때 *를 통해 모든 필드를 긁어내는 것은 작동하지 않는다는 점에 유의하는 것이 중요합니다! 생각해 보세요 ;)

숙제

이전 기사의 결과를 보면 숙제가 진행되고 있는 것이 분명하므로 계속하자)) 네, 숙제를 하는 모든 사람은 계속해서 댓글에 "+"를 넣을 것입니다. 숙제라는 주제가 당신에게 흥미로워서 앞으로도 계속 숙제를 하는 것이 중요합니다. 네, 정기적으로 귀하의 의견을 읽고 있습니다. 물론 나는 덜 자주 대답합니다. 더 어려운 SQL 문제를 내달라고 요청하는 것을 봤습니다. 조인을 배울 때까지 흥미로운 문제는 없을 것이므로 추가 자료에 필요한 문제가 있을 것입니다.

작업:

HAVING 연산자를 이해 하고 예제의 테이블에 대한 예제 쿼리를 작성하세요. 좀 더 명확하게 하기 위해 일부 필드나 더 많은 값을 추가해야 하는 경우 해당 항목을 추가하세요. 누구든지 원한다면 댓글에 예제 솔루션을 적어주세요. 이렇게 하면 시간이 있으면 확인할 수도 있습니다.
1. UI를 통해 데이터베이스를 사용하려면 MySQL Workbench를 설치하세요. 우리는 이미 콘솔에서 작업하는 연습을 충분히 했다고 생각합니다. 데이터베이스에 연결합니다. 데이터베이스 작업을 위해 다른 것을 사용하는 경우 이 작업을 건너뛰어도 됩니다. 여기서는 MySQL Workbench만 사용하겠습니다.
2. 데이터를 사용하여 수신 요청을 작성합니다.
   1. 가장 작은/가장 인구가 많은 국가;
   2. 해당 국가의 평균 주민 수;
   3. 이름이 "a"로 끝나는 국가의 평균 주민 수;
   4. 인구가 400만 명이 넘는 국가의 수;
   5. 주민 수를 줄여 국가를 정렬합니다.
   6. 국가를 이름별로 자연순으로 정렬합니다.

결론

오늘 우리는 지난 수업의 숙제에 대해 자세히 논의했습니다. 더욱이 나는 그것을 하지 않은 사람과 한 사람 모두에게 이것이 중요하다고 생각합니다. 전자의 경우 이는 답을 찾을 수 있는 기회이고, 후자의 경우 이를 결과와 비교할 수 있는 기회입니다. 프로젝트 변경 사항에 대한 최신 정보를 얻으려면 내 GitHub 계정을 구독하세요 . 나는 거기에서 전체 코드 베이스를 유지 관리할 것입니다. 모든 일은 이 조직 에서 이루어집니다 . 다음으로 SELECT 문에 대해 논의했습니다. 그는 우리에게 가장 중요합니다. 이를 통해 데이터에 대한 모든 요청이 이루어지며 우리는 이를 이해해야 합니다. 가장 중요한 것은 매개변수가 추가되는 순서(WHERE, ORDER BY, GROUP BY 등)를 기억하는 것입니다. 네, 가능한 모든 것을 말하지는 않았지만 그런 목표를 스스로 설정하지는 않았습니다. 예, 귀하가 이미 지원서를 작성하고 싶어한다는 것을 알고 있습니다. 인내심을 가지십시오. 이것이 필요한 전부입니다. 프로젝트와 귀하의 전문적인 성장을 위해. 기다리는 동안 Git이 이미 친숙한지 확인하세요. 잘 알려진 도구로 기본적으로 사용하겠습니다. 읽어주신 모든 분들께 감사드립니다. 다음 글에서는 데이터베이스 연결과 조인에 대해 이야기하겠습니다. 그것이 멋진 작업이 될 곳입니다))

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