MySQL·MySQL from first query to production

Views and stored procedures

Package reusable queries in views and business operations in procedures.

Beginner 14 min read Lesson 8 of 300 Challenge included

What you will be able to do

Use views to simplify repeated joins.
Views do not store data by default.
Use procedures for multi-step operations.
Keep business logic in the app unless the database is the right boundary.

In plain English

A view is a saved query that behaves like a table. A stored procedure is a named program that runs inside the database. Use views for reporting convenience and procedures for operations that must be atomic and close to the data.

view.sqlSQL

CREATE VIEW course_totals AS
SELECT c.slug, c.title, COUNT(l.id) AS lessons, COALESCE(SUM(l.minutes), 0) AS minutes
FROM courses c
LEFT JOIN lessons l ON l.course_slug = c.slug
GROUP BY c.id, c.slug, c.title;

SELECT * FROM course_totals ORDER BY minutes DESC;

Your turn

Create a simple view

Medium

Create a view named long_lessons for lessons of 20 minutes or more.

SQL · starter

CREATE VIEW long_lessons AS

Check your understanding before moving on.

Knowledge checkQuestion 1

Does a standard view store its result rows?

Select an answer to continue

Study tools

Make it yours.

Keep a private note for this lesson, then turn the key ideas into flashcards you can revisit.

Sign in to save notes and flashcards.Both are private to your account and stay with your progress.

Finished reading?

Lock in what you just learned.

Mark this lesson complete to save your place and move one step closer to the certificate.

Next: Backups and restore

Lesson 8 of 300 · 0% of course