Creating Database Views in MySQL

MySQLMySQLBeginner
Practice Now

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

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("`MySQL`")) -.-> mysql/BasicKeywordsandStatementsGroup(["`Basic Keywords and Statements`"]) sql(("`SQL`")) -.-> sql/BasicSQLCommandsGroup(["`Basic SQL Commands`"]) sql(("`SQL`")) -.-> sql/DataManipulationandQueryingGroup(["`Data Manipulation and Querying`"]) mysql(("`MySQL`")) -.-> mysql/AdvancedFeaturesGroup(["`Advanced Features`"]) sql(("`SQL`")) -.-> sql/DataDefinitionandIntegrityGroup(["`Data Definition and Integrity`"]) sql(("`SQL`")) -.-> sql/AdvancedDataOperationsGroup(["`Advanced Data Operations`"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/source("`External Code Execution`") sql/BasicSQLCommandsGroup -.-> sql/select("`SELECT statements`") sql/DataManipulationandQueryingGroup -.-> sql/in("`IN clause`") mysql/BasicKeywordsandStatementsGroup -.-> mysql/select("`Data Retrieval`") mysql/AdvancedFeaturesGroup -.-> mysql/views("`View Management`") sql/DataDefinitionandIntegrityGroup -.-> sql/data_types("`Data Types`") sql/DataDefinitionandIntegrityGroup -.-> sql/constraints("`Constraints`") sql/AdvancedDataOperationsGroup -.-> sql/views("`Views`") subgraph Lab Skills mysql/source -.-> lab-301416{{"`Creating Database Views in MySQL`"}} sql/select -.-> lab-301416{{"`Creating Database Views in MySQL`"}} sql/in -.-> lab-301416{{"`Creating Database Views in MySQL`"}} mysql/select -.-> lab-301416{{"`Creating Database Views in MySQL`"}} mysql/views -.-> lab-301416{{"`Creating Database Views in MySQL`"}} sql/data_types -.-> lab-301416{{"`Creating Database Views in MySQL`"}} sql/constraints -.-> lab-301416{{"`Creating Database Views in MySQL`"}} sql/views -.-> lab-301416{{"`Creating Database Views in MySQL`"}} end

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)
âœĻ Check Solution and Practice

Summary

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

Other MySQL Tutorials you may like