PARTIII PART PART in Software

Printer QR Code in Software PARTIII PART PART

Records from the view can be filtered using a WHERE clause, as with any other table Consider the next example, which displays round-trip routes with distances greater than 3,000 kilometers:

mysql> SELECT vRouteID, vFrom, vTo, vDistance -> FROM v_round_trip_routes AS v -> WHERE vDistance > 3000; +---------+------+-----+----------+ | RouteID | From | To | Distance | +---------+------+-----+----------+ | 1142 | 201 | 126 | 3913 | | 1141 | 126 | 201 | 3913 | | 1192 | 92 | 201 | 10310 | | 1193 | 201 | 92 | 10310 | +---------+------+-----+----------+ 4 rows in set (001 sec)

The key thing to note about a view is that it automatically reflects changes in its underlying tables Consider, for example, what happens when a new round-trip route is added:

mysql> INSERT INTO route -> (RouteID, `From`, `To`, Distance, Duration, Status) -> VALUES -> (1016, 129, 132, 1235, 150, 1), -> (1017, 132, 129, 1235, 150, 1); Query OK, 2 rows affected (002 sec) Records: 2 Duplicates: 0 Warnings: 0

Part I:

The view automatically reflects the change in the underlying table:

mysql> SELECT vRouteID, vFrom, vTo, vDistance -> FROM v_round_trip_routes AS v; +---------+------+-----+----------+ | RouteID | From | To | Distance | +---------+------+-----+----------+ | 1175 | 132 | 56 | 1267 | | 1176 | 56 | 132 | 1267 | | 1142 | 201 | 126 | 3913 | | 1141 | 126 | 201 | 3913 | | 1192 | 92 | 201 | 10310 | | 1140 | 87 | 83 | 2474 | | 1139 | 83 | 87 | 2474 | | 1193 | 201 | 92 | 10310 | | 1017 | 132 | 129 | 1235 | | 1016 | 129 | 132 | 1235 | +---------+------+-----+----------+ 10 rows in set (016 sec)

It s also possible to join the fields in a view to other tables, as in this next example, which joins the airport table to retrieve airport names for each round-trip route:

mysql> SELECT vRouteID, aAirportName AS FromAirport -> FROM v_round_trip_routes AS v, airport AS a -> WHERE vFrom = aAirportID; +---------+--------------------------------------------+ | RouteID | FromAirport | +---------+--------------------------------------------+ | 1175 | Barajas Airport | | 1176 | Heathrow Airport | | 1142 | Changi Airport | | 1141 | Chhatrapati Shivaji International Airport | | 1192 | Zurich Airport | | 1140 | Budapest Ferihegy International Airport | | 1139 | Lisbon Airport | | 1193 | Changi Airport | | 1017 | Barajas Airport | | 1016 | Bristol International Airport | +---------+--------------------------------------------+ 10 rows in set (001 sec)

A view only allows access to the fields listed in its SELECT statement; any attempt to access other fields, even if they exist in the underlying table, will generate an error Consider what happens when you try accessing the routeStatus field, which is not part of the view definition, through the view:

4:

mysql> SELECT vRouteID, aAirportName AS FromAirport, -> vStatus FROM v_round_trip_routes AS v, -> airport AS a WHERE vFrom = aAirportID; ERROR 1054 (42S22): Unknown column 'vStatus' in 'field list'

that contains only the allowed fields while restricting access to the underlying table MySQL s privilege system, which is the key to defining these access rules, is discussed in 11 Views are listed in the output of the SHOW TABLES command, as shown:

mysql> SHOW TABLES; +---------------------+ | Tables_in_db1 | +---------------------+ | aircraft | | aircrafttype | | airport | | user | | v_round_trip_routes | +---------------------+ 13 rows in set (000 sec)

It s a good idea to prefix your view names with a character or label, such as v, v_, or view_, so that you can identify them easily in the output of the SHOW TABLES command However, you can t use the DROP TABLE command to remove a view; instead, use the DROP VIEW command with the view name as an argument It s worth noting, however, that dropping a table does not automatically remove any views that depend on it

mysql> DROP VIEW v_timetable; Query OK, 0 rows affected (003 sec)

To view (pardon the pun) the SELECT statement used for a particular view, use the SHOW CREATE VIEW command with the view name as an argument Here s an example:

mysql> SHOW CREATE VIEW v_round_trip_routes\G *************************** 1 row *************************** View: v_round_trip_routes Create View: CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_round_trip_routes` AS SELECT `r1``RouteID` AS `RouteID `,`r1``From` AS `From`,`r1``To` AS `To`,`r1``Distance` AS `Distance` FROM (`route` `r1` JOIN `route` `r2`) WHERE ((`r1``From` = `r2``To`)

Part I: