MySQL View

List all view in the database

SHOW TABLES

Delete or drop view table

 DROP VIEW view_name

Student table

mysql> SELECT * FROM student;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Jone Mark |    63 | 
|  2 | Bob Maat  |    80 | 
|  3 | Paul Chao |    85 | 
|  4 | Marry     |    74 | 
|  5 | Christ    |    63 | 
+----+-----------+-------+
5 rows in set (0.00 sec)

student_status table

mysql> SELECT * FROM student_status;
+-------+------------+--------+
| ss_id | student_id | status |
+-------+------------+--------+
|     1 |          1 | fail   | 
|     2 |          2 | pass   | 
|     3 |          3 | pass   | 
|     4 |          4 | fail   | 
|     5 |          5 | fail   | 
+-------+------------+--------+
5 rows in set (0.00 sec)

create view in mysql

This view will return only the student who has score higher or equal to 80

mysql> CREATE VIEW student_pass_score
    -> AS SELECT * FROM student WHERE score >=80;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM student_pass_score;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  2 | Bob Maat  |    80 | 
|  3 | Paul Chao |    85 | 
+----+-----------+-------+
2 rows in set (0.01 sec)

create view for student who is failed

mysql> CREATE VIEW student_fail_score
    -> AS SELECT * FROM student WHERE score < 80;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM student_fail_score;
+----+-----------+-------+
| id | name      | score |
+----+-----------+-------+
|  1 | Jone Mark |    63 | 
|  4 | Marry     |    74 | 
|  5 | Christ    |    63 | 
+----+-----------+-------+
3 rows in set (0.00 sec)

mysql show create view

mysql> SHOW CREATE VIEW student_fail_score\G
*************************** 1. row ***************************
       View: student_fail_score
Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` 
SQL SECURITY DEFINER VIEW `student_fail_score` AS 
select 
`student`.`id` AS `id`,
`student`.`name` AS `name`,
`student`.`score` AS `score` from `student` where (`student`.`score` < 80)
1 row in set (0.00 sec)

mysql create view from multiple tables

mysql> CREATE VIEW student_information AS
    ->       SELECT 
    ->            t.*, p.status 
    ->       FROM 
    ->            student t, 
    ->            student_status p
    ->       WHERE t.id= p.student_id ;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM student_information;
+----+-----------+-------+--------+
| id | name      | score | status |
+----+-----------+-------+--------+
|  1 | Jone Mark |    63 | fail   | 
|  2 | Bob Maat  |    80 | pass   | 
|  3 | Paul Chao |    85 | pass   | 
|  4 | Marry     |    74 | fail   | 
|  5 | Christ    |    63 | fail   | 
+----+-----------+-------+--------+
5 rows in set (0.00 sec)

mysql update view

Update the view table will also update the original table.

mysql> UPDATE student_information SET name='Christina' WHERE id=5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM student_information;
+----+-----------+-------+--------+
| id | name      | score | status |
+----+-----------+-------+--------+
|  1 | Jone Mark |    63 | fail   | 
|  2 | Bob Maat  |    80 | pass   | 
|  3 | Paul Chao |    85 | pass   | 
|  4 | Marry     |    74 | fail   | 
|  5 | Christina |    63 | fail   | 
+----+-----------+-------+--------+
5 rows in set (0.00 sec)