Watch the 7.3 Webinar On-Demand
This new release brings updates to Universal Storage, query
optimization, and usability that you won’t want to miss.
Create an unmaterialized view.
CREATE
[DEFINER = { <user> | CURRENT_USER }]
[SCHEMA_BINDING = { ON | OFF }]
VIEW <view_name>
AS <select_statement>
INSERT
, UPDATE
, or DELETE
data in a view will result in an error. Instead, use ALTER VIEW
or manipulate data directly in the underlying tables from which the view is constructed.ALGORITHM = MERGE
option as part of CREATE VIEW
. When a merge is not possible, SingleStore DB creates an internal temporary table.CREATE OR REPLACE VIEW
syntax. To modify an existing view, use ALTER VIEW
.CREATE
, ALTER
, or SELECT
from the view to the user who created the view and to SUPER
users. In MySQL terms, this is the equivalent of including DEFINER = CURRENT_USER
and SQL SECURITY DEFINER
in the CREATE VIEW
statement.DROP TABLE
on a table referenced by a view will give an error. Running ALTER TABLE DROP COLUMN col
will give an error when col
is referenced by a view. Columns not referenced by any view can be dropped.CREATE VIEW view_name AS SELECT * FROM table_name ...
will replace *
with the column names at the time of CREATE VIEW
execution rather than during execution of SELECT ... FROM view_name
. It is important to consider this distinction if table_name
is altered between the execution of CREATE VIEW
and SELECT ... FROM view_name
.CURRENT_USER()
and NOW()
) used in a view are evaluated during execution of SELECT ... FROM view_name
, not when the view is created.DEFINER
clause tells SingleStore DB which user should be used for security checks when a view is referenced by a query. The default value is CURRENT_USER.SCHEMA_BINDING
is set to ON
, objects referenced by the view cannot be dropped if the view exists; you would need to drop the view before dropping these objects. By default, SCHEMA_BINDING
is set to OFF
.table_name_case_sensitivity
is set to OFF
: After you create a view, you cannot create another view having the same view name with a different case. Refer to the Database Object Case Sensitivity topic for more information.The following examples create views that reference a table in the same database as the view.
CREATE VIEW person_view AS SELECT first_name, last_name
FROM table_name WHERE user_id = 'real_person';
CREATE VIEW active_items_view AS SELECT name
FROM items WHERE status = 'active';
CREATE VIEW discounted_items_view AS SELECT name
FROM active_items_view WHERE discount = 1;
The following example creates a view that references two tables (orders
and customers
) that are located in different databases (orders_db
and customers_db
).
CREATE VIEW customer_orders AS SELECT o.id, c.last_name, c.first_name
FROM orders_db.orders o, customers_db.customers c WHERE o.customer_id = c.id;
Related Topics