ccsunnyWhat is a table view in SQL?
here are some examples of how a view can be created and used in MySQL:
- Create a simple view that selects all columns from the "customers" table:
CREATE VIEW customer_view AS
SELECT *
FROM customers;
- Create a view that aggregates data from the "orders" table by customer ID:
CREATE VIEW order_summary AS
SELECT customer_id, COUNT(*) as order_count, SUM(total_amount) as total_spent
FROM orders
GROUP BY customer_id;
- Create a view that joins two tables and selects specific columns:
CREATE VIEW sales_report AS
SELECT customers.first_name, customers.last_name, orders.order_date, order_items.unit_price, order_items.quantity
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id;
These views can then be used like a regular table in a query. For example, you can query the customer_view created in example 1 like this:
SELECT * FROM customer_view;
I hope this helps! Let me know if you have any more questions.
show full tables in <your_database_name> where TABLE_type like 'VIEW';
