Where can I read about views for MySQL?

Question

I really don't know nothing about SQL.. I know how to insert and select data from database using PHP but besides that, nothing.

I found this view And I am sure that 5 minutes for you to answer this question will give me a lot more knowledge.

I understand the fact the the view is created and given the name film_list and the AS defines what this view is going to retrieve.

Then we select from the Schema or Database film the field film_id and define it AS FID.

Q1. Why do we do that? What is the main purpose of this definition?

... The statement continues until GROUP_CONCAT.

Q2. What is the difference between CONCAT, that already combines the two fields to one element, and GROUP_CONCAT ?

.. Then the FROM syntax. Now we choose the schemas and tables to select from. After reading on w3schools, I understand the INNER JOIN, FULL JOIN, LEFT JOIN and RIGHT JOIN statements, but not the JOINonly statement.

Q3. Which requirements does the JOIN statement have?

.. Okay now.. This one I haven't been able to find anything about.

Q4. What is the ON statement, and what is it good for?

The GROUP BY statement I understand.. The SQL simply groups everything together related to the film_id

CREATE VIEW film_list 
AS 
SELECT film.film_id AS FID,
film.title AS title,
film.description AS description,
category.name AS category,
film.rental_rate AS price,
film.length AS length,
film.rating AS rating,
GROUP_CONCAT(CONCAT(actor.first_name, _utf8' ', actor.last_name) SEPARATOR ', ') AS actors
FROM category LEFT JOIN film_category ON category.category_id = film_category.category_id
LEFT JOIN film ON film_category.film_id = film.film_id
JOIN film_actor ON film.film_id = film_actor.film_id 
JOIN actor ON film_actor.actor_id = actor.actor_id 
GROUP BY film.film_id

---

Q5. What would the correct way to display returned data be?

I assume that the view would create some kind of table during the SELECT statement. Usually I would be able to simply echo a row in PHP. Is that still possible, and if so - how would I have to do? Because usually I echo like this echo $row['field_name'] but how would I know which field to call? Is that why we defined the elements with FID, title, desciption etc.?

In my MySQL database I have 14 different tables each one containing a little bit information about a cooking recipe.

Q6. Would a view be ideal for me to use when having that amount of tables to search in?

I Really hope that some of you please would take your time to answer these questions. It would help me a lot. And then a final Question:

Q7. Which websites or books are the best to teach me how to work with SQL?

Answer 1

A1. You don't have to do that, it's pretty arbitrary. The column alias just renames the id column.

A2. CONCAT() and GROUP_CONCAT are very different -- CONCAT('a','b','c') is just a function that would evaluate to 'abc' GROUP_CONCAT() is an aggregate function that works with GROUP BY; for each group of rows that you "roll up" into a group, GROUP_CONCAT() will create a string based on the value for each row in that group.

A3. JOIN is just shorthand for INNER JOIN

A4. ON defines how the tables are joined. Each set of possible combination rows are examined, and each combination where the ON clause evaluates to true, then you get a match. Visual Join Explanation

A5. I can't find your actual question here.

A6. No, a view doesn't sound like the right approach. If you're searching for the same thing in many tables, then all that stuff should probably be in one tables. But that's a generalization, you haven't described nearly enough about your actual project to give a clear answer.

A7. This question is answered other places.