Sử dụng chế độ xem MySQL trên Debian 7

• Giới thiệu
• Điều kiện tiên quyết
• Bước một - Cài đặt máy chủ MySQL
• Bước hai - Cài đặt cơ sở dữ liệu mẫu
• Bước ba - Tạo, sử dụng và xóa chế độ xem

Giới thiệu

MySQL có một tính năng tuyệt vời được gọi là "lượt xem". Lượt xem được lưu trữ truy vấn. Hãy nghĩ về họ như một bí danh cho một truy vấn dài khác. Trong hướng dẫn này, tôi sẽ chỉ cho bạn cách sử dụng chế độ xem để sắp xếp dữ liệu hiệu quả hơn.

Điều kiện tiên quyết

• Một máy chủ đám mây SSD Vultr được tải với Debian 7.
• Người dùng có quyền quản trị viên (root).
• Kiến thức cơ bản về Linux, dòng lệnh và SSH.

Bước một - Cài đặt máy chủ MySQL

Cài đặt MySQL trên Debian 7.x rất đơn giản. Trước tiên, chúng tôi cần đảm bảo rằng các nguồn của chúng tôi được cập nhật bằng cách thực hiện:

sudo apt-get update

Tiếp theo, chúng ta có thể cài đặt máy chủ MySQL:

sudo apt-get install -y mysql-server

Một hộp thoại sẽ được hiển thị nhắc bạn tạo mật khẩu cho người dùng "root". Hãy chắc chắn rằng bạn nhớ mật khẩu này.

Hãy tăng cường bảo mật cài đặt của chúng tôi bằng cách thực hiện:

sudo mysql_secure_installation

Sau khi thực hiện, bạn sẽ được trình bày với một loạt các lời nhắc. Mỗi câu trả lời mà bạn nên chọn được hiển thị bên dưới.

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

Bước hai - Cài đặt cơ sở dữ liệu mẫu

Tại thời điểm này, chúng tôi không có dữ liệu trên máy chủ để thử nghiệm. Đối với hướng dẫn này, chúng tôi sẽ sử dụng cơ sở dữ liệu nhân viên vì nó dễ làm việc và có sẵn miễn phí từ trang web của MySQL.

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

Chúng ta cần cài đặt bzip2để có thể giải nén tập tin.

sudo apt-get install bzip2

Trích xuất cơ sở dữ liệu. Các tập tin khá lớn, vì vậy có thể mất một vài phút.

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

Khi tệp đã được giải nén, bạn sẽ có một thư mục có tiêu đề employees_db. Chúng ta cần điều hướng vào thư mục này để cài đặt cơ sở dữ liệu.

cd employees_db 
ls -l

Đầu ra sẽ như thế này:

-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

Thực hiện lệnh sau để kết nối với máy chủ MySQL, tạo cơ sở dữ liệu và nhập dữ liệu:

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

Một dấu nhắc sẽ xuất hiện yêu cầu mật khẩu root của bạn. Đây là mật khẩu mà bạn đặt ở bước một.

Do cơ sở dữ liệu khá lớn, có thể sẽ mất từ ​​1-3 phút để nhập dữ liệu đầy đủ. Nếu mọi thứ đã được thực hiện chính xác, bạn sẽ thấy đầu ra sau đây.

+-----------------------------+
| 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 |
+------------------+

Bây giờ, chúng ta có thể đăng nhập vào MySQL và xem dữ liệu đã nhập.

sudo mysql -h localhost -u root -p

Nhập mật khẩu gốc mà bạn đã đặt trong phần trước.

Kiểm tra danh sách cơ sở dữ liệu cho cơ sở dữ liệu nhân viên mới được tạo của chúng tôi .

show databases;

Đầu ra sẽ như thế này:

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

Hãy sử dụng cơ sở dữ liệu nhân viên .

use employees;

Kiểm tra các bảng trong đó.

show tables;

Điều này sẽ xuất ra:

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

Bước ba - Tạo, sử dụng và xóa chế độ xem

Trong bước này, bạn sẽ học cách tạo và sử dụng các khung nhìn. Tôi đã chia bước này thành các phần nhỏ hơn để khớp dữ liệu và kết hợp dữ liệu cho tổ chức. Đã đến lúc bắt đầu tương tác với dữ liệu thử nghiệm của chúng tôi.

Hợp nhất / khớp dữ liệu

Dưới đây, tôi có một truy vấn hiển thị tất cả các nhân viên có mức lương hàng năm bằng hoặc lớn hơn 50.000 đô la.

select * from salaries where salary >= 50000;

Đầu ra (cắt ngắn):

+--------+--------+------------+------------+
| 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 |
(...)

Như bạn có thể thấy, điều này chỉ hiển thị số nhân viên. Nó có thể gây phiền toái khi cố gắng xác định nhanh chóng một nhân viên. May mắn thay, chúng ta có thể tạo một chế độ xem sẽ lưu trữ một truy vấn khá dài có thể khớp số nhân viên với tên nhân viên bằng cách kéo và khớp dữ liệu từ nhiều bảng. Các truy vấn được hiển thị dưới đây.

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;

Lưu ý cách tôi đã bỏ qua >= 50000từ truy vấn. Chúng tôi sẽ sử dụng giá trị này sau khi chế độ xem của chúng tôi được tạo.

Để tạo chế độ xem, chúng tôi chỉ cần thêm create view view_name asvào truy vấn. Trong trường hợp này, tôi sẽ tạo ra một khung nhìn có tên là_s_saries .

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;

Chúng tôi hiển thị dữ liệu từ một chế độ xem theo cùng một cách mà chúng tôi hiển thị dữ liệu từ một bảng.

select * from named_salaries

Nếu chế độ xem được tạo đúng, thì bạn sẽ thấy đầu ra sau (dữ liệu đã bị cắt ngắn):

+------------+-----------+--------+--------+------------+------------+
| 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 |
(...)

Vì chúng ta có thể tương tác với các khung nhìn theo cùng một cách mà chúng ta có thể tương tác với một bảng, nên có thể lấy >= 50000từ truy vấn ban đầu và áp dụng nó vào dạng xem.

select * from named_salaries where salary >= 50000;

Đầu ra (cắt ngắn):

+------------+-----------+--------+--------+------------+------------+
| 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 |
(...)
+------------+-----------+--------+--------+------------+------------+

Như bạn có thể thấy, truy vấn đã xử lý khung nhìn giống như một bảng truyền thống.

Hãy sử dụng một khung nhìn trong một ví dụ khác. Dưới đây, tôi có một truy vấn khá dài liệt kê các quản lý bộ phận, tên / họ, số nhân viên, tên bộ phận của họ và số bộ phận. Truy vấn kết hợp dữ liệu từ nhiều bảng khác nhau.

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;

Đầu ra (cắt ngắn):

+-------------+--------------+--------+------------+------------+--------------------+---------+
| 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    |
(...)

Như bạn có thể thấy, sẽ hơi bất tiện khi nhập truy vấn đó mỗi khi bạn cần tìm nạp danh sách người quản lý bộ phận. Hãy tạo một khung nhìn để làm cho nó dễ dàng hơn. Tôi sẽ gọi quan điểm là "quản lý".

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;

Bây giờ, chúng ta có thể chỉ cần gõ select * from management;để lấy cùng một dữ liệu. Tất nhiên, chúng ta cũng có thể áp dụng các tham số bổ sung cho điều đó - giống như một bảng truyền thống. Ví dụ: giả sử chúng tôi chỉ muốn hiển thị cho người quản lý bộ phận cho "Dịch vụ khách hàng".

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

Đầu ra:

+------------+-------------+--------+------------+------------+------------------+---------+
| 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    |
+------------+-------------+--------+------------+------------+------------------+---------+

Hoặc có thể chúng tôi muốn "Dịch vụ khách hàng" và "Nhân sự":

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

Đầu ra:

+------------+--------------+--------+------------+------------+------------------+---------+
| 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    |
+------------+--------------+--------+------------+------------+------------------+---------+

Xóa chế độ xem

Xóa một cái nhìn là rất đơn giản. Tương tự như loại bỏ một bảng, bạn sẽ gõ drop view view_name;. Ví dụ: nếu chúng tôi muốn xóa chế độ xem tên_salaries , lệnh sẽ là : drop view named_salaries;.