Svarbu

Vertimas yra bendruomenės pastangos, prie kurių jūs galite prisijungti. Šis puslapis šiuo metu išverstas 27.78%.

15.5. Pamoka: vaizdai

Kai jūs rašote užklausą, jūs sugaištate daug laiko ir pastangų ją formuluodami. Naudodami vaizdus jūs galite įsirašyti SQL užklausos apibrėžimą į perpanaudojamą „virtualią lentelę“.

Šios pamokos tikslas: įrašyti užklausą kaip vaizdą.

15.5.1. Vaizdo sukūrimas

You can treat a view just like a table, but its data is sourced from a query. Let’s make a simple view based on the above:

create view roads_count_v as
  select count(people.name), streets.name
  from people, streets where people.street_id=streets.id
  group by people.street_id, streets.name;

As you can see the only change is the create view roads_count_v as part at the beginning. We can now select data from that view:

select * from roads_count_v;

Result:

 count |    name
-------+-------------
     1 | Main Road
     2 | High street
     1 | Low Street
(3 rows)

15.5.2. Modifying a View

A view is not fixed, and it contains no ‚real data‘. This means you can easily change it without impacting on any data in your database:

CREATE OR REPLACE VIEW roads_count_v AS
  SELECT count(people.name), streets.name
  FROM people, streets WHERE people.street_id=streets.id
  GROUP BY people.street_id, streets.name
  ORDER BY streets.name;

(This example also shows the best practice convention of using UPPER CASE for all SQL keywords.)

You will see that we have added an ORDER BY clause so that our view rows are nicely sorted:

select * from roads_count_v;

 count |    name
-------+-------------
     2 | High street
     1 | Low Street
     1 | Main Road
(3 rows)

15.5.3. Dropping a View

If you no longer need a view, you can delete it like this:

drop view roads_count_v;

15.5.4. In Conclusion

Using views, you can save a query and access its results as if it were a table.

15.5.5. What’s Next?

Sometimes, when changing data, you want your changes to have effects elsewhere in the database. The next lesson will show you how to do this.