Wednesday 27 July 2016

VIEWS



View is a virtual table based on the result-set of an SQL statement.

How to create view
CREATE VIEW view_name AS (SELECT something FROM table_name);

How to see the records from view
SELECT * FROM view_name;

Drop view
DROP VIEW view_name;

I am going to use following sample data.

CREATE TABLE employee(
  id int PRIMARY KEY,
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  salary DECIMAL(10, 2)
);

INSERT INTO employee VALUES (1, "Hari Krishna", "Gurram", 12345.67);
INSERT INTO employee VALUES (2, "Rama Devi", "Gurram", 1234578.67);
INSERT INTO employee VALUES (3, "Lakshmana", "Rao", 9876543.67);
INSERT INTO employee VALUES (4, "Rama", "Krishna", 1234587.67);
INSERT INTO employee VALUES (5, "Sowmya", "asd", 1238745.67);
INSERT INTO employee VALUES (6, "Jyotsna", "PS", 76543.67);
INSERT INTO employee VALUES (7, "Gireesh", "Amara", 87698);
INSERT INTO employee VALUES (8, "Sravani", "Nidamanuri", 987654);
INSERT INTO employee VALUES (9, "Saranya", "Amara", 1987654);
INSERT INTO employee VALUES (10, "Sudhir", "Sami", 876543);
INSERT INTO employee VALUES (11, "Kiran", "Darsi", 76549);

mysql> SELECT * FROM employee;
+----+--------------+------------+------------+
| id | firstName    | lastName   | salary     |
+----+--------------+------------+------------+
|  1 | Hari Krishna | Gurram     |   12345.67 |
|  2 | Rama Devi    | Gurram     | 1234578.67 |
|  3 | Lakshmana    | Rao        | 9876543.67 |
|  4 | Rama         | Krishna    | 1234587.67 |
|  5 | Sowmya       | asd        | 1238745.67 |
|  6 | Jyotsna      | PS         |   76543.67 |
|  7 | Gireesh      | Amara      |   87698.00 |
|  8 | Sravani      | Nidamanuri |  987654.00 |
|  9 | Saranya      | Amara      | 1987654.00 |
| 10 | Sudhir       | Sami       |  876543.00 |
| 11 | Kiran        | Darsi      |   76549.00 |
+----+--------------+------------+------------+
11 rows in set (0.00 sec)


Create a view that has only firstName and lastName.

mysql> CREATE VIEW emp_name as (SELECT firstName, lastName FROM employee);
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> SELECT * FROM emp_name;
+--------------+------------+
| firstName    | lastName   |
+--------------+------------+
| Hari Krishna | Gurram     |
| Rama Devi    | Gurram     |
| Lakshmana    | Rao        |
| Rama         | Krishna    |
| Sowmya       | asd        |
| Jyotsna      | PS         |
| Gireesh      | Amara      |
| Sravani      | Nidamanuri |
| Saranya      | Amara      |
| Sudhir       | Sami       |
| Kiran        | Darsi      |
+--------------+------------+
11 rows in set (0.00 sec)


Now when you call ‘SHOW TABLES’ command, it display views also.

mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| blog             |
| emp_name         |
| employee         |
| employee_info    |
| post_info        |
+------------------+
5 rows in set (0.00 sec)


Insert data into tables through views
INSERT INTO view_name VALUES(data);

mysql> SELECT * FROM employee;
+----+--------------+------------+------------+
| id | firstName    | lastName   | salary     |
+----+--------------+------------+------------+
|  1 | Hari Krishna | Gurram     |   12345.67 |
|  2 | Rama Devi    | Gurram     | 1234578.67 |
|  3 | Lakshmana    | Rao        | 9876543.67 |
|  4 | Rama         | Krishna    | 1234587.67 |
|  5 | Sowmya       | asd        | 1238745.67 |
|  6 | Jyotsna      | PS         |   76543.67 |
|  7 | Gireesh      | Amara      |   87698.00 |
|  8 | Sravani      | Nidamanuri |  987654.00 |
|  9 | Saranya      | Amara      | 1987654.00 |
| 10 | Sudhir       | Sami       |  876543.00 |
| 11 | Kiran        | Darsi      |   76549.00 |
+----+--------------+------------+------------+
11 rows in set (0.00 sec)


mysql> CREATE VIEW emp_name as (SELECT firstName, lastName FROM employee);
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM emp_name;
+--------------+------------+
| firstName    | lastName   |
+--------------+------------+
| Hari Krishna | Gurram     |
| Rama Devi    | Gurram     |
| Lakshmana    | Rao        |
| Rama         | Krishna    |
| Sowmya       | asd        |
| Jyotsna      | PS         |
| Gireesh      | Amara      |
| Sravani      | Nidamanuri |
| Saranya      | Amara      |
| Sudhir       | Sami       |
| Kiran        | Darsi      |
+--------------+------------+
11 rows in set (0.00 sec)

mysql> SHOW TABLES;
+------------------+
| Tables_in_sample |
+------------------+
| blog             |
| emp_name         |
| employee         |
| employee_info    |
| post_info        |
+------------------+
5 rows in set (0.00 sec)

mysql> SELECT  *
    -> FROM    sys.objects
    -> WHERE   type = 'V'
    -> ;
ERROR 1146 (42S02): Table 'sys.objects' doesn't exist
mysql> SELECT * FROM sys.views
    -> ;
ERROR 1146 (42S02): Table 'sys.views' doesn't exist
mysql> INSERT INTO emp_name VALUES("ABCD", "EFGH");
ERROR 1423 (HY000): Field of view 'sample.emp_name' underlying table doesn't have a default value
mysql>
mysql> DESC employee;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| id        | int(11)       | NO   | PRI | NULL    |       |
| firstName | varchar(20)   | YES  |     | NULL    |       |
| lastName  | varchar(20)   | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>
mysql> CREATE VIEW  employee_summary AS (SELECT id, firstName FROM employee);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM employee_summary;
+----+--------------+
| id | firstName    |
+----+--------------+
|  1 | Hari Krishna |
|  2 | Rama Devi    |
|  3 | Lakshmana    |
|  4 | Rama         |
|  5 | Sowmya       |
|  6 | Jyotsna      |
|  7 | Gireesh      |
|  8 | Sravani      |
|  9 | Saranya      |
| 10 | Sudhir       |
| 11 | Kiran        |
+----+--------------+
11 rows in set (0.00 sec)

mysql>
mysql> INSERT INTO employee_summary VALUES (1234, "Anand");
Query OK, 1 row affected (0.00 sec)

mysql>
mysql> SELECT * FROM employee;
+------+--------------+------------+------------+
| id   | firstName    | lastName   | salary     |
+------+--------------+------------+------------+
|    1 | Hari Krishna | Gurram     |   12345.67 |
|    2 | Rama Devi    | Gurram     | 1234578.67 |
|    3 | Lakshmana    | Rao        | 9876543.67 |
|    4 | Rama         | Krishna    | 1234587.67 |
|    5 | Sowmya       | asd        | 1238745.67 |
|    6 | Jyotsna      | PS         |   76543.67 |
|    7 | Gireesh      | Amara      |   87698.00 |
|    8 | Sravani      | Nidamanuri |  987654.00 |
|    9 | Saranya      | Amara      | 1987654.00 |
|   10 | Sudhir       | Sami       |  876543.00 |
|   11 | Kiran        | Darsi      |   76549.00 |
| 1234 | Anand        | NULL       |       NULL |
+------+--------------+------------+------------+
12 rows in set (0.00 sec)


Update records using views
UPDATE view_name SET col1 = val1, col2 = val2...colN=valN [WHERE condition];

mysql> SELECT * FROM employee_summary;
+------+--------------+
| id   | firstName    |
+------+--------------+
|    1 | Hari Krishna |
|    2 | Rama Devi    |
|    3 | Lakshmana    |
|    4 | Rama         |
|    5 | Sowmya       |
|    6 | Jyotsna      |
|    7 | Gireesh      |
|    8 | Sravani      |
|    9 | Saranya      |
|   10 | Sudhir       |
|   11 | Kiran        |
| 1234 | Anand        |
+------+--------------+
12 rows in set (0.00 sec)

mysql>
mysql> UPDATE employee_summary SET firstName="Tyrian Lanister" WHERE id=1234;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM employee_summary;
+------+-----------------+
| id   | firstName       |
+------+-----------------+
|    1 | Hari Krishna    |
|    2 | Rama Devi       |
|    3 | Lakshmana       |
|    4 | Rama            |
|    5 | Sowmya          |
|    6 | Jyotsna         |
|    7 | Gireesh         |
|    8 | Sravani         |
|    9 | Saranya         |
|   10 | Sudhir          |
|   11 | Kiran           |
| 1234 | Tyrian Lanister |
+------+-----------------+
12 rows in set (0.00 sec)

mysql>
mysql> SELECT * FROM employee;
+------+-----------------+------------+------------+
| id   | firstName       | lastName   | salary     |
+------+-----------------+------------+------------+
|    1 | Hari Krishna    | Gurram     |   12345.67 |
|    2 | Rama Devi       | Gurram     | 1234578.67 |
|    3 | Lakshmana       | Rao        | 9876543.67 |
|    4 | Rama            | Krishna    | 1234587.67 |
|    5 | Sowmya          | asd        | 1238745.67 |
|    6 | Jyotsna         | PS         |   76543.67 |
|    7 | Gireesh         | Amara      |   87698.00 |
|    8 | Sravani         | Nidamanuri |  987654.00 |
|    9 | Saranya         | Amara      | 1987654.00 |
|   10 | Sudhir          | Sami       |  876543.00 |
|   11 | Kiran           | Darsi      |   76549.00 |
| 1234 | Tyrian Lanister | NULL       |       NULL |
+------+-----------------+------------+------------+
12 rows in set (0.00 sec)






Previous                                                 Next                                                 Home

No comments:

Post a Comment