Copyright 2021 – Prof. Jennifer Wong-Ma This content is protected and may not be shared uploaded or distributed CS122A / EECS116 MORE SQL 1 Instructor:JenniferWong-Ma jwongma@uci.edu Copyright 2021 – Prof. Jennifer Wong-Ma This content is protected and may not be shared uploaded or distributed VIEWS - RECAP • A view is a conceptual schema of the underlying physical schema • Physical schema defines the files and indexes under the covers • Conceptual scheme defines the logical structure of the DB • View describes how the user “sees” the data • “virtual” table through SQL queries – name query • Views can be defined on other views! • Used for • Logical data independence • Simplified view (users/groups) • Authorization (for access control) 2 • Views can • Rename/Reorder columns • Change units/representation of columns • Select/Project/join/etc tables Copyright 2021 – Prof. Jennifer Wong-Ma This content is protected and may not be shared uploaded or distributed VIEW MYSQL SYNTAX CREATE [OR REPLACE] VIEW view_name [(column_list)] AS select-statement [WITH CHECK OPTION]; 3 SELECT * FROM view_name; DROP VIEW [IF EXISTS] view_name; Copyright 2021 – Prof. Jennifer Wong-Ma This content is protected and may not be shared uploaded or distributed VIEWS - CHANGES? • Update/Delete are only allowed on Views which are • projection or selection from a single table and • do not have aggregate values • More than 1 table? • Not clear how to handle the change/how to split data into the multiple tables • Aggregate value? • Impossible to update the value itself – no physical place to put • Insert? • Values not in the view are not defined • WITH CHECK OPTION – only permits insert or update on rows which would appear within view 4
















































学霸联盟