Creating Database Views in MySQL

Introduction

In this project, you will learn how to create a database view based on the student table in the edusys database. The view will include the ID, name, and dept_name columns from the student table, providing a simplified and focused view of the student data.

👀 Preview

MySQL [edusys]> DESC student_view;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| ID         | int(11)     | NO   |     | NULL    |       |
| name       | varchar(20) | NO   |     | NULL    |       |
| dept_name  | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.002 sec)

🎯 Tasks

In this project, you will learn:

• How to start the MySQL server and log into the MySQL terminal
• How to import data from an SQL script into the MySQL database
• How to create a database view using the CREATE VIEW statement
• How to verify the structure of the created view using the DESC statement

🏆 Achievements

After completing this project, you will be able to:

• Understand the process of setting up a MySQL database environment
• Create custom database views to simplify data access and presentation
• Apply SQL statements to manage and manipulate database objects

Start MySQL and Import the Data

In this step, you will learn how to start the MySQL server and import the data from the edusys.sql script into the MySQL database.

1. Open a terminal and start the MySQL service:

   sudo /etc/init.d/mysql start
   

2. Log into the MySQL terminal:

   mysql -uroot
   

3. Import the data from the edusys.sql script:

   SOURCE ~/project/edusys.sql
   

Create the student_view View

In this step, you will create a view named student_view that includes the ID, name, and dept_name columns from the student table.

1. Create a new file named studentView.sql in the ~/project directory.

2. In the file studentView.sql add the code area to create the student_view view:

   USE edusys;
   CREATE VIEW student_view AS
   SELECT ID, name, dept_name
   FROM student;
   

3. Run the SQL script in the MySQL terminal:

   SOURCE /home/labex/project/studentView.sql
   

4. Verify the structure of the student_view view:

   DESC student_view;
   

   The output should look like this:

   +------------+-------------+------+-----+---------+-------+
   | Field      | Type        | Null | Key | Default | Extra |
   +------------+-------------+------+-----+---------+-------+
   | ID         | int(11)     | NO   |     | NULL    |       |
   | name       | varchar(20) | NO   |     | NULL    |       |
   | dept_name  | varchar(20) | YES  |     | NULL    |       |
   +------------+-------------+------+-----+---------+-------+
   3 rows in set (0.002 sec)
   

Summary

Congratulations! You have completed this project. You can practice more labs in LabEx to improve your skills.