ใช้ MySQL Views บน Debian 7

• บทนำ
• ข้อกำหนดเบื้องต้น
• ขั้นตอนที่หนึ่ง - ติดตั้งเซิร์ฟเวอร์ MySQL
• ขั้นตอนที่สอง - ติดตั้งฐานข้อมูลตัวอย่าง
• ขั้นตอนที่สาม - การสร้างใช้และลบมุมมอง

บทนำ

MySQL มีคุณสมบัติที่ยอดเยี่ยมที่เรียกว่า "มุมมอง" จำนวนการดูคือแบบสอบถามที่เก็บไว้ คิดว่าพวกเขาเป็นนามแฝงสำหรับแบบสอบถามยาว ๆ ในคู่มือนี้ฉันจะแสดงวิธีใช้มุมมองเพื่อจัดระเบียบข้อมูลอย่างมีประสิทธิภาพยิ่งขึ้น

ข้อกำหนดเบื้องต้น

• เซิร์ฟเวอร์คลาวด์ Vultr SSD ที่โหลดด้วย Debian 7
• ผู้ใช้ที่มีสิทธิ์ผู้ดูแลระบบ (รูท)
• ความรู้พื้นฐานของ Linux, บรรทัดคำสั่งและ SSH

ขั้นตอนที่หนึ่ง - ติดตั้งเซิร์ฟเวอร์ MySQL

การติดตั้ง MySQL บน Debian 7.x นั้นตรงไปตรงมามาก อันดับแรกเราต้องตรวจสอบให้แน่ใจว่าแหล่งที่มาของเราได้รับการอัปเดตโดยดำเนินการ:

sudo apt-get update

ต่อไปเราสามารถติดตั้งเซิร์ฟเวอร์ MySQL:

sudo apt-get install -y mysql-server

กล่องโต้ตอบจะปรากฏขึ้นเพื่อให้คุณสร้างรหัสผ่านสำหรับผู้ใช้ "root" ต้องแน่ใจว่าคุณจำรหัสผ่านนี้ได้

มาเสริมความปลอดภัยของการติดตั้งของเราให้แข็งแกร่งขึ้นโดยการดำเนินการ

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

ขั้นตอนที่สอง - ติดตั้งฐานข้อมูลตัวอย่าง

ณ จุดนี้เราไม่มีข้อมูลบนเซิร์ฟเวอร์ที่จะทำการทดลอง สำหรับบทช่วยสอนนี้เราจะใช้ฐานข้อมูลพนักงานเนื่องจากง่ายต่อการใช้งานและสามารถใช้ได้อย่างอิสระจากเว็บไซต์ของ 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-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)

ขั้นตอนที่สาม - การสร้างใช้และลบมุมมอง

ในขั้นตอนนี้คุณจะได้เรียนรู้วิธีสร้างและใช้มุมมอง ฉันแบ่งขั้นตอนนี้เป็นส่วนย่อย ๆ เพื่อจับคู่ข้อมูลและรวมข้อมูลสำหรับองค์กร ได้เวลาโต้ตอบกับข้อมูลการทดสอบของเราแล้ว

การผสาน / จับคู่ข้อมูล

ด้านล่างนี้ฉันมีแบบสอบถามที่แสดงพนักงานทุกคนที่มีเงินเดือนประจำปีเท่ากับหรือมากกว่า $ 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_salariesdrop view named_salaries;มุมมองของคำสั่งจะเป็น: