在Debian 7上使用MySQL视图

・17 分钟阅读

介绍

MySQL有一个很好的特性叫做"视图",视图是存储的查询,将它们视为长时间查询的别名。本指南会向你展示如何使用视图更有效地组织数据。

前提条件

  • 加载Debian 7的SSD云服务器,
  • 有管理员(root )权限的用户,
  • 基本知识Linux,命令行和SSH ,

步骤1-安装MySQL服务器

在Debian 7.x上安装MySQL非常简单,首先,我们需要通过执行以下命令来确保我们的源被更新:

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

步骤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

会出现一个提示,询问你的root密码,这是你在步骤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

输入你在上一节中设置的root密码。

检查新创建的员工数据库的数据库列表。

 
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;

Huangzhongbang profile image