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)
No comments:
Post a Comment