MySQL has a great feature known as “views”. Views are stored queries. Think of them as an alias for an otherwise long query. In this guide, I will be showing you how to use views to organize data more efficiently.<\/p>\n
Installing MySQL on Debian 7.x is very straightforward. First, we need to ensure that our sources are updated by executing:<\/p>\n
sudo apt-get update\r\n<\/code><\/pre>\nNext, we can install the MySQL server:<\/p>\n
sudo apt-get install -y mysql-server\r\n<\/code><\/pre>\nA dialog box will be displayed prompting you to create a password for the “root” user. Be sure that you remember this password.<\/p>\n
Let’s harden the security of our installation by executing:<\/p>\n
sudo mysql_secure_installation\r\n<\/code><\/pre>\nAfter execution, you will be presented with a series of prompts. Each of the answers that you should select are displayed below.<\/p>\n
...\r\nEnter current password for root (enter for none):\r\nOK, successfully used password, moving on...\r\n...\r\nSet root password? [Y\/n] y\r\nNew password:\r\nRe-enter new password:\r\nPassword updated successfully!\r\nReloading privilege tables..\r\n ... Success!\r\n...\r\nRemove anonymous users? [Y\/n] y\r\n ... Success!\r\n...\r\nDisallow root login remotely? [Y\/n] y\r\n ... Success!\r\nRemove test database and access to it? [Y\/n] y\r\n - Dropping test database...\r\n ... Success!\r\n...\r\nReload privilege tables now? [Y\/n] y\r\n ... Success!\r\nCleaning up...\r\n<\/code><\/pre>\nStep two – Install sample database<\/h3>\n
At this point, we have no data on the server to experiment with. For this tutorial, we will be using the\u00a0employees<\/strong>\u00a0database as it is easy to work with and freely available from MySQL’s website.<\/p>\nsudo wget https:\/\/launchpad.net\/test-db\/employees-db-1\/1.0.6\/+download\/employees_db-full-1.0.6.tar.bz2\r\n<\/code><\/pre>\nWe need to install\u00a0bzip2<\/code>\u00a0so that we can extract the file.<\/p>\nsudo apt-get install bzip2\r\n<\/code><\/pre>\nExtract the database. The file is fairly large, so it may take a few moments.<\/p>\n
sudo bzip2 -dfv employees_db-full-1.0.6.tar.bz2\r\nsudo tar -xf employees_db-full-1.0.6.tar\r\n<\/code><\/pre>\nOnce the file has been extracted, you will have a folder titled\u00a0employees_db<\/code>. We need to navigate into this directory to install the database.<\/p>\ncd employees_db \r\nls -l\r\n<\/code><\/pre>\nThe output will look like this:<\/p>\n
-rw-r--r--. 1 501 games 752 Mar 30 2009 Changelog\r\n-rw-r--r--. 1 501 games 6460 Oct 9 2008 employees_partitioned2.sql\r\n-rw-r--r--. 1 501 games 7624 Feb 6 2009 employees_partitioned3.sql\r\n-rw-r--r--. 1 501 games 5660 Feb 6 2009 employees_partitioned.sql\r\n-rw-r--r--. 1 501 games 3861 Nov 28 2008 employees.sql\r\n-rw-r--r--. 1 501 games 241 Jul 30 2008 load_departments.dump\r\n-rw-r--r--. 1 501 games 13828291 Mar 30 2009 load_dept_emp.dump\r\n-rw-r--r--. 1 501 games 1043 Jul 30 2008 load_dept_manager.dump\r\n-rw-r--r--. 1 501 games 17422825 Jul 30 2008 load_employees.dump\r\n-rw-r--r--. 1 501 games 115848997 Jul 30 2008 load_salaries.dump\r\n-rw-r--r--. 1 501 games 21265449 Jul 30 2008 load_titles.dump\r\n-rw-r--r--. 1 501 games 3889 Mar 30 2009 objects.sql\r\n-rw-r--r--. 1 501 games 2211 Jul 30 2008 README\r\n-rw-r--r--. 1 501 games 4455 Mar 30 2009 test_employees_md5.sql\r\n-rw-r--r--. 1 501 games 4450 Mar 30 2009 test_employees_sha.sql\r\n<\/code><\/pre>\nExecute the following command to connect to the MySQL server, create the database, and import the data:<\/p>\n
sudo mysql -h localhost -u root -p -t < employees.sql\r\n<\/code><\/pre>\nA prompt will appear asking for your root password. This is the password that you set in step one.<\/p>\n
Being that the database is quite large, it will probably take anywhere from 1-3 minutes to fully import the data. If everything was done correctly, you will see the following output.<\/p>\n
+-----------------------------+\r\n| INFO |\r\n+-----------------------------+\r\n| CREATING DATABASE STRUCTURE |\r\n+-----------------------------+\r\n+------------------------+\r\n| INFO |\r\n+------------------------+\r\n| storage engine: InnoDB |\r\n+------------------------+\r\n+---------------------+\r\n| INFO |\r\n+---------------------+\r\n| LOADING departments |\r\n+---------------------+\r\n+-------------------+\r\n| INFO |\r\n+-------------------+\r\n| LOADING employees |\r\n+-------------------+\r\n+------------------+\r\n| INFO |\r\n+------------------+\r\n| LOADING dept_emp |\r\n+------------------+\r\n+----------------------+\r\n| INFO |\r\n+----------------------+\r\n| LOADING dept_manager |\r\n+----------------------+\r\n+----------------+\r\n| INFO |\r\n+----------------+\r\n| LOADING titles |\r\n+----------------+\r\n+------------------+\r\n| INFO |\r\n+------------------+\r\n| LOADING salaries |\r\n+------------------+\r\n<\/code><\/pre>\nNow, we can log into MySQL and view the imported data.<\/p>\n
sudo mysql -h localhost -u root -p\r\n<\/code><\/pre>\nEnter the\u00a0root<\/strong>\u00a0password that you set in the previous section.<\/p>\nCheck the list of databases for our newly created\u00a0employees<\/strong>\u00a0database.<\/p>\nshow databases;\r\n<\/code><\/pre>\nThe output will look like this:<\/p>\n
+--------------------+\r\n| Database |\r\n+--------------------+\r\n| information_schema |\r\n| employees |\r\n| mysql |\r\n| performance_schema |\r\n+--------------------+\r\n4 rows in set (0.01 sec)\r\n<\/code><\/pre>\nLet’s use the\u00a0employees<\/strong>\u00a0database.<\/p>\nuse employees;\r\n<\/code><\/pre>\nCheck the tables within it.<\/p>\n
show tables;\r\n<\/code><\/pre>\nThis will output:<\/p>\n
+---------------------+\r\n| Tables_in_employees |\r\n+---------------------+\r\n| departments |\r\n| dept_emp |\r\n| dept_manager |\r\n| employees |\r\n| salaries |\r\n| titles |\r\n+---------------------+\r\n6 rows in set (0.01 sec)\r\n<\/code><\/pre>\nStep three – Creating, using, and removing views<\/h3>\n
In this step, you will learn to create and use views. I have broken up this step into smaller sections for matching data, and combining data for organization. It’s time to start interacting with our test data.<\/p>\n
Merging\/matching data<\/h4>\n
Below, I have a query which displays all employees who have a yearly salary equal to, or greater than $50,000.<\/p>\n
select * from salaries where salary >= 50000;\r\n<\/code><\/pre>\nOutput (truncated):<\/p>\n
+--------+--------+------------+------------+\r\n| emp_no | salary | from_date | to_date |\r\n+--------+--------+------------+------------+\r\n| 10001 | 60117 | 1986-06-26 | 1987-06-26 |\r\n| 10001 | 62102 | 1987-06-26 | 1988-06-25 |\r\n| 10001 | 66074 | 1988-06-25 | 1989-06-25 |\r\n| 10001 | 66596 | 1989-06-25 | 1990-06-25 |\r\n| 10001 | 66961 | 1990-06-25 | 1991-06-25 |\r\n(...)\r\n<\/code><\/pre>\nAs you can see, this only displays employee numbers. It could be a nuisance when trying to identify an employee quickly. Luckily, we can create a view that will store a fairly lengthy query that can match employee numbers to employee names by pulling and matching data from multiple tables. The query is shown below.<\/p>\n
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;\r\n<\/code><\/pre>\nNotice how I have omitted\u00a0>= 50000<\/code>\u00a0from the query. We will be using this value after our view has been created.<\/p>\nTo create the view, we simply append\u00a0create view view_name as<\/code>\u00a0to the query. In this case, I will be creating a view called\u00a0named_salaries<\/strong>.<\/p>\ncreate 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;\r\n<\/code><\/pre>\nWe display data from a view in the same fashion that we display data from a table.<\/p>\n
select * from named_salaries\r\n<\/code><\/pre>\nIf the view has been created properly, then you will see the following output (data has been truncated):<\/p>\n
+------------+-----------+--------+--------+------------+------------+\r\n| first_name | last_name | emp_no | salary | to_date | from_date |\r\n+------------+-----------+--------+--------+------------+------------+\r\n| Georgi | Facello | 10001 | 60117 | 1987-06-26 | 1986-06-26 |\r\n| Georgi | Facello | 10001 | 62102 | 1988-06-25 | 1987-06-26 |\r\n| Georgi | Facello | 10001 | 66074 | 1989-06-25 | 1988-06-25 |\r\n| Georgi | Facello | 10001 | 66596 | 1990-06-25 | 1989-06-25 |\r\n| Georgi | Facello | 10001 | 66961 | 1991-06-25 | 1990-06-25 |\r\n| Georgi | Facello | 10001 | 71046 | 8 1992-06-24 | 1991-06-25 |\r\n(...)\r\n<\/code><\/pre>\nSince we can interact with views in the same way that we can interact with a table, it is possible to take the\u00a0>= 50000<\/code>\u00a0from the original query and apply it to the view.<\/p>\nselect * from named_salaries where salary >= 50000;\r\n<\/code><\/pre>\nOutput (truncated):<\/p>\n
+------------+-----------+--------+--------+------------+------------+\r\n| first_name | last_name | emp_no | salary | to_date | from_date |\r\n+------------+-----------+--------+--------+------------+------------+\r\n| Georgi | Facello | 10001 | 60117 | 1987-06-26 | 1986-06-26 |\r\n(...)\r\n| Bezalel | Simmel | 10002 | 65828 | 1997-08-03 | 1996-08-03 |\r\n(...)\r\n| Chirstian | Koblick | 10004 | 50594 | 1992-11-29 | 1991-11-30 |\r\n(...)\r\n| Kyoichi | Maliniak | 10005 | 78228 | 1990-09-12 | 1989-09-12 |\r\n(...)\r\n| Anneke | Preusig | 10006 | 53747 | 1998-08-03 | 1997-08-03 |\r\n(...)\r\n+------------+-----------+--------+--------+------------+------------+\r\n<\/code><\/pre>\nAs you can see, the query has treated the view just like a traditional table.<\/p>\n
Let’s use a view in another example. Below, I have a fairly lengthy query that lists the department managers, their first\/last names, employee numbers, their department names, and the department numbers. The query pulls together data from several different tables.<\/p>\n
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;\r\n<\/code><\/pre>\nOutput (truncated):<\/p>\n
+-------------+--------------+--------+------------+------------+--------------------+---------+\r\n| first_name | last_name | emp_no | to_date | from_date | dept_name | dept_no |\r\n+-------------+--------------+--------+------------+------------+--------------------+---------+\r\n| Tonny | Butterworth | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009 |\r\n| Marjo | Giarratana | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009 |\r\n| Xiaobin | Spinelli | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009 |\r\n| Yuchang | Weedman | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009 |\r\n| DeForest | Hagimont | 110511 | 1992-04-25 | 1985-01-01 | Development | d005 |\r\n| Leon | DasSarma | 110567 | 9999-01-01 | 1992-04-25 | Development | d005 |\r\n(...)\r\n<\/code><\/pre>\nAs you can see, it would be somewhat inconvenient to type in that query every time you need to fetch a list of department managers. Let’s create a view to make it easier. I’m going to call the view “management”.<\/p>\n
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;\r\n<\/code><\/pre>\nNow, we can simply type\u00a0select * from management;<\/code>\u00a0to retrieve the same data. Of course, we can also apply additional parameters to that – just like a traditional table. For example, say we wanted to only show the departments managers for “Customer Service”.<\/p>\nselect * from management where dept_name = 'Customer Service';\r\n<\/code><\/pre>\nOutput:<\/p>\n
+------------+-------------+--------+------------+------------+------------------+---------+\r\n| first_name | last_name | emp_no | to_date | from_date | dept_name | dept_no |\r\n+------------+-------------+--------+------------+------------+------------------+---------+\r\n| Tonny | Butterworth | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009 |\r\n| Marjo | Giarratana | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009 |\r\n| Xiaobin | Spinelli | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009 |\r\n| Yuchang | Weedman | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009 |\r\n+------------+-------------+--------+------------+------------+------------------+---------+\r\n<\/code><\/pre>\nOr maybe we want “Customer Service” and “Human Resources”:<\/p>\n
select * from management where dept_name = 'Customer Service' OR dept_name = 'Human Resources';\r\n<\/code><\/pre>\nOutput:<\/p>\n
+------------+--------------+--------+------------+------------+------------------+---------+\r\n| first_name | last_name | emp_no | to_date | from_date | dept_name | dept_no |\r\n+------------+--------------+--------+------------+------------+------------------+---------+\r\n| Tonny | Butterworth | 111692 | 1988-10-17 | 1985-01-01 | Customer Service | d009 |\r\n| Marjo | Giarratana | 111784 | 1992-09-08 | 1988-10-17 | Customer Service | d009 |\r\n| Xiaobin | Spinelli | 111877 | 1996-01-03 | 1992-09-08 | Customer Service | d009 |\r\n| Yuchang | Weedman | 111939 | 9999-01-01 | 1996-01-03 | Customer Service | d009 |\r\n| Shirish | Ossenbruggen | 110183 | 1992-03-21 | 1985-01-01 | Human Resources | d003 |\r\n| Karsten | Sigstam | 110228 | 9999-01-01 | 1992-03-21 | Human Resources | d003 |\r\n+------------+--------------+--------+------------+------------+------------------+---------+\r\n<\/code><\/pre>\nRemoving a view<\/h4>\n
Deleting a view is very straightforward. Similar to removing a table, you would type\u00a0drop view view_name;<\/code>. For example, if we wanted to delete the\u00a0named_salaries<\/strong>\u00a0view, the command would be:\u00a0drop view named_salaries;<\/code>.<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","format":"standard","manualknowledgebasecat":[110,233,242],"manual_kb_tag":[484],"_links":{"self":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2981"}],"collection":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb"}],"about":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/types\/manual_kb"}],"author":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/comments?post=2981"}],"version-history":[{"count":2,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2981\/revisions"}],"predecessor-version":[{"id":2985,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb\/2981\/revisions\/2985"}],"wp:attachment":[{"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/media?parent=2981"}],"wp:term":[{"taxonomy":"manualknowledgebasecat","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manualknowledgebasecat?post=2981"},{"taxonomy":"manual_kb_tag","embeddable":true,"href":"https:\/\/support.aklwebhost.com\/wp-json\/wp\/v2\/manual_kb_tag?post=2981"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}