데비안 7에서 MySQL 뷰 사용하기

• 소개
• 전제 조건
• 1 단계-MySQL 서버 설치
• 2 단계-샘플 데이터베이스 설치
• 3 단계-보기 작성, 사용 및 제거

소개

MySQL에는 "보기"라는 훌륭한 기능이 있습니다. 뷰는 저장된 쿼리입니다. 그것들을 그렇지 않으면 긴 쿼리의 별칭으로 생각하십시오. 이 가이드에서는 뷰를 사용하여 데이터를보다 효율적으로 구성하는 방법을 보여줍니다.

전제 조건

• 데비안 7이 탑재 된 Vultr SSD 클라우드 서버
• 관리자 (루트) 권한이있는 사용자
• Linux, 명령 행 및 SSH에 대한 기본 지식

1 단계-MySQL 서버 설치

데비안 7.x에 MySQL을 설치하는 것은 매우 간단합니다. 먼저 다음을 실행하여 소스를 업데이트해야합니다.

sudo apt-get update

다음으로 MySQL 서버를 설치할 수 있습니다 :

sudo apt-get install -y mysql-server

"루트"사용자의 비밀번호를 작성하라는 대화 상자가 표시됩니다. 이 암호를 기억하십시오.

다음을 실행하여 설치 보안을 강화하십시오.

sudo mysql_secure_installation

실행 후 일련의 프롬프트가 표시됩니다. 선택해야 할 각 답변이 아래에 표시됩니다.

...
Enter current password for root (enter for none):
OK, successfully used password, moving on...
...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
...
Remove anonymous users? [Y/n] y
 ... Success!
...
Disallow root login remotely? [Y/n] y
 ... Success!
Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
...
Reload privilege tables now? [Y/n] y
 ... Success!
Cleaning up...

2 단계-샘플 데이터베이스 설치

이 시점에서 실험 할 서버에 데이터가 없습니다. 이 튜토리얼에서는 직원들의 데이터베이스를 사용 하기 쉬우 며 MySQL 웹 사이트에서 자유롭게 작업 할 수 있습니다.

sudo wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2

bzip2파일을 추출 할 수 있도록 설치해야 합니다.

sudo apt-get install bzip2

데이터베이스를 추출하십시오. 파일이 상당히 커서 잠시 시간이 걸릴 수 있습니다.

sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2
sudo tar -xf employees_db-full-1.0.6.tar

파일이 추출되면이라는 제목의 폴더가 생성 employees_db됩니다. 데이터베이스를 설치하려면이 디렉토리로 이동해야합니다.

cd employees_db 
ls -l

결과는 다음과 같습니다.

-rw-r--r--. 1 501 games       752 Mar 30  2009 Changelog
-rw-r--r--. 1 501 games      6460 Oct  9  2008 employees_partitioned2.sql
-rw-r--r--. 1 501 games      7624 Feb  6  2009 employees_partitioned3.sql
-rw-r--r--. 1 501 games      5660 Feb  6  2009 employees_partitioned.sql
-rw-r--r--. 1 501 games      3861 Nov 28  2008 employees.sql
-rw-r--r--. 1 501 games       241 Jul 30  2008 load_departments.dump
-rw-r--r--. 1 501 games  13828291 Mar 30  2009 load_dept_emp.dump
-rw-r--r--. 1 501 games      1043 Jul 30  2008 load_dept_manager.dump
-rw-r--r--. 1 501 games  17422825 Jul 30  2008 load_employees.dump
-rw-r--r--. 1 501 games 115848997 Jul 30  2008 load_salaries.dump
-rw-r--r--. 1 501 games  21265449 Jul 30  2008 load_titles.dump
-rw-r--r--. 1 501 games      3889 Mar 30  2009 objects.sql
-rw-r--r--. 1 501 games      2211 Jul 30  2008 README
-rw-r--r--. 1 501 games      4455 Mar 30  2009 test_employees_md5.sql
-rw-r--r--. 1 501 games      4450 Mar 30  2009 test_employees_sha.sql

다음 명령을 실행하여 MySQL 서버에 연결하고 데이터베이스를 작성하고 데이터를 가져 오십시오.

sudo mysql -h localhost -u root -p -t < employees.sql

루트 암호를 묻는 프롬프트가 나타납니다. 1 단계에서 설정 한 비밀번호입니다.

데이터베이스가 상당히 크기 때문에 데이터를 완전히 가져 오는 데 1-3 분 정도 걸립니다. 모든 것이 올바르게 완료되면 다음과 같은 결과가 나타납니다.

+-----------------------------+
| INFO                        |
+-----------------------------+
| CREATING DATABASE STRUCTURE |
+-----------------------------+
+------------------------+
| INFO                   |
+------------------------+
| storage engine: InnoDB |
+------------------------+
+---------------------+
| INFO                |
+---------------------+
| LOADING departments |
+---------------------+
+-------------------+
| INFO              |
+-------------------+
| LOADING employees |
+-------------------+
+------------------+
| INFO             |
+------------------+
| LOADING dept_emp |
+------------------+
+----------------------+
| INFO                 |
+----------------------+
| LOADING dept_manager |
+----------------------+
+----------------+
| INFO           |
+----------------+
| LOADING titles |
+----------------+
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+

이제 MySQL에 로그인하여 가져온 데이터를 볼 수 있습니다.

sudo mysql -h localhost -u root -p

이전 섹션에서 설정 한 루트 비밀번호를 입력 하십시오.

새로 작성된 직원 데이터베이스 의 데이터베이스 목록을 확인하십시오 .

show databases;

결과는 다음과 같습니다.

+--------------------+
| Database           |
+--------------------+
| information_schema |
| employees          |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.01 sec)

직원 데이터베이스를 사용합시다 .

use employees;

그 안의 테이블을 확인하십시오.

show tables;

출력됩니다 :

+---------------------+
| Tables_in_employees |
+---------------------+
| departments         |
| dept_emp            |
| dept_manager        |
| employees           |
| salaries            |
| titles              |
+---------------------+
6 rows in set (0.01 sec)

3 단계-보기 작성, 사용 및 제거

이 단계에서는 뷰를 작성하고 사용하는 방법을 배웁니다. 데이터를 일치시키고 조직을 위해 데이터를 결합하기 위해이 단계를 더 작은 섹션으로 나누었습니다. 테스트 데이터와 상호 작용을 시작할 차례입니다.

데이터 병합 / 매칭

아래에는 연간 급여가 $ 50,000 이상인 모든 직원을 표시하는 쿼리가 있습니다.

select * from salaries where salary >= 50000;

출력 (잘림) :

+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  10001 |  62102 | 1987-06-26 | 1988-06-25 |
|  10001 |  66074 | 1988-06-25 | 1989-06-25 |
|  10001 |  66596 | 1989-06-25 | 1990-06-25 |
|  10001 |  66961 | 1990-06-25 | 1991-06-25 |
(...)

보시다시피 직원 번호 만 표시됩니다. 직원을 빨리 식별하려고하면 성 가실 수 있습니다. 운 좋게도 여러 테이블에서 데이터를 가져 와서 일치시켜 직원 번호를 직원 이름과 일치시킬 수있는 상당히 긴 쿼리를 저장할 뷰를 만들 수 있습니다. 쿼리는 아래와 같습니다.

select employees.first_name,employees.last_name,employees.emp_no,salaries.salary,salaries.to_date,salaries.from_date from employees, salaries where employees.emp_no = salaries.emp_no;

>= 50000쿼리에서 어떻게 생략했는지 주목 하십시오. 뷰를 만든 후에이 값을 사용합니다.

뷰를 만들려면 create view view_name as쿼리에 추가 하면됩니다. 이 경우 named_salaries 라는 뷰를 작성합니다 .

create view named_salaries as select employees.first_name,employees.last_name,employees.emp_no,salaries.salary,salaries.to_date,salaries.from_date from employees, salaries where employees.emp_no = salaries.emp_no;

테이블의 데이터를 표시하는 것과 같은 방식으로 뷰의 데이터를 표시합니다.

select * from named_salaries

보기가 올바르게 작성된 경우 다음 출력이 표시됩니다 (데이터가 잘림).

+------------+-----------+--------+--------+------------+------------+
| first_name | last_name | emp_no | salary | to_date    | from_date  |
+------------+-----------+--------+--------+------------+------------+
| Georgi     | Facello   |  10001 |  60117 | 1987-06-26 | 1986-06-26 |
| Georgi     | Facello   |  10001 |  62102 | 1988-06-25 | 1987-06-26 |
| Georgi     | Facello   |  10001 |  66074 | 1989-06-25 | 1988-06-25 |
| Georgi     | Facello   |  10001 |  66596 | 1990-06-25 | 1989-06-25 |
| Georgi     | Facello   |  10001 |  66961 | 1991-06-25 | 1990-06-25 |
| Georgi     | Facello   |  10001 |  71046 | 8  1992-06-24 | 1991-06-25 |
(...)

테이블과 상호 작용할 수있는 것과 같은 방식으로 뷰와 상호 작용할 수 있으므로 >= 50000원래 쿼리에서 가져 와서 뷰에 적용 할 수 있습니다.

select * from named_salaries where salary >= 50000;

출력 (잘림) :

+------------+-----------+--------+--------+------------+------------+
| first_name | last_name | emp_no | salary | to_date    | from_date  |
+------------+-----------+--------+--------+------------+------------+
| Georgi     | Facello   |  10001 |  60117 | 1987-06-26 | 1986-06-26 |
(...)
| Bezalel    | Simmel    |  10002 |  65828 | 1997-08-03 | 1996-08-03 |
(...)
| Chirstian  | Koblick   |  10004 |  50594 | 1992-11-29 | 1991-11-30 |
(...)
| Kyoichi    | Maliniak  |  10005 |  78228 | 1990-09-12 | 1989-09-12 |
(...)
| Anneke     | Preusig   |  10006 |  53747 | 1998-08-03 | 1997-08-03 |
(...)
+------------+-----------+--------+--------+------------+------------+

보다시피, 쿼리는 뷰를 전통적인 테이블처럼 취급했습니다.

다른 예에서 뷰를 사용하겠습니다. 아래에는 부서 관리자, 이름 / 성, 직원 번호, 부서 이름 및 부서 번호를 나열하는 상당히 긴 쿼리가 있습니다. 쿼리는 여러 다른 테이블에서 데이터를 가져옵니다.

select employees.first_name,employees.last_name,employees.emp_no,dept_manager.to_date,dept_manager.from_date,departments.dept_name,departments.dept_no from employees, dept_manager, departments where employees.emp_no = dept_manager.emp_no AND departments.dept_no = dept_manager.dept_no;

출력 (잘림) :

+-------------+--------------+--------+------------+------------+--------------------+---------+
| first_name  | last_name    | emp_no | to_date    | from_date  | dept_name          | dept_no |
+-------------+--------------+--------+------------+------------+--------------------+---------+
| Tonny       | Butterworth  | 111692 | 1988-10-17 | 1985-01-01 | Customer Service   | d009    |
| Marjo       | Giarratana   | 111784 | 1992-09-08 | 1988-10-17 | Customer Service   | d009    |
| Xiaobin     | Spinelli     | 111877 | 1996-01-03 | 1992-09-08 | Customer Service   | d009    |
| Yuchang     | Weedman      | 111939 | 9999-01-01 | 1996-01-03 | Customer Service   | d009    |
| DeForest    | Hagimont     | 110511 | 1992-04-25 | 1985-01-01 | Development        | d005    |
| Leon        | DasSarma     | 110567 | 9999-01-01 | 1992-04-25 | Development        | d005    |
(...)

보시다시피 부서 관리자 목록을 가져올 때마다 해당 쿼리를 입력하는 것이 다소 불편합니다. 보기 편하게 만들어 봅시다. 뷰를 "관리"라고 부를 것입니다.

create view management as select employees.first_name,employees.last_name,employees.emp_no,dept_manager.to_date,dept_manager.from_date,departments.dept_name,departments.dept_no from employees, dept_manager, departments where employees.emp_no = dept_manager.emp_no AND departments.dept_no = dept_manager.dept_no;

이제 select * from management;동일한 데이터를 검색하기 위해 간단히 입력 할 수 있습니다 . 물론 기존 테이블과 마찬가지로 추가 매개 변수를 적용 할 수도 있습니다. 예를 들어 "고객 서비스"에 대한 부서 관리자 만 표시하려고한다고 가정하십시오.

select * from management where dept_name = 'Customer Service';

산출:

+------------+-------------+--------+------------+------------+------------------+---------+
| first_name | last_name   | emp_no | to_date    | from_date  | dept_name        | dept_no |
+------------+-------------+--------+------------+------------+------------------+---------+
| Tonny      | Butterworth | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009    |
| Marjo      | Giarratana  | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009    |
| Xiaobin    | Spinelli    | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009    |
| Yuchang    | Weedman     | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009    |
+------------+-------------+--------+------------+------------+------------------+---------+

또는 "고객 서비스"및 "인적 자원"을 원할 수도 있습니다.

select * from management where dept_name = 'Customer Service' OR dept_name = 'Human Resources';

산출:

+------------+--------------+--------+------------+------------+------------------+---------+
| first_name | last_name    | emp_no | to_date    | from_date  | dept_name        | dept_no |
+------------+--------------+--------+------------+------------+------------------+---------+
| Tonny      | Butterworth  | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009    |
| Marjo      | Giarratana   | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009    |
| Xiaobin    | Spinelli     | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009    |
| Yuchang    | Weedman      | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009    |
| Shirish    | Ossenbruggen | 110183 | 1992-03-21 | 1985-01-01 | Human Resources  | d003    |
| Karsten    | Sigstam      | 110228 | 9999-01-01 | 1992-03-21 | Human Resources  | d003    |
+------------+--------------+--------+------------+------------+------------------+---------+

보기 제거

뷰를 삭제하는 것은 매우 간단합니다. 테이블을 제거하는 것과 유사하게을 입력 drop view view_name;합니다. 예를 들어 named_salaries 보기 를 삭제하려는 경우 명령은 다음과 같습니다 drop view named_salaries;.