SQL of tables with multiple relationships

The structure you have at the moment allows you to answer your two questions:

• "store the total number of videos of a genre so that it is possible to take, for example, all user 1 movies that are of genre 3?"

Number of films for each genre:

select g.nome, count(rel.id_filme)
from genero g
left join rel_filme_genero rel
   on rel.id_genero = g.id
group by g.nome

All movies from user 1 that are of genre 3:

select f.nome
from filmes f
inner join rel_filme_usuario relfu
  on relfu.id_filme = f.id
inner join rel_filme_genero relfg
  on relfg.id_filme = f.id
where relfu.id_usuario = 1
  and relgf.id_genero = 3

"Select movies of a given genre (genre is passed by name and not by id)":

select g.nome, f.nome, f.duracao
from genero g
inner join rel_filme_genero relfg
   on relfg.id_genero = g.id
inner join filmes f
   on f.id = relfg.id_filme
where g.nome = 'Comedia'

Insert the movie and then the genre information. I would execute this in a transaction:

start transaction

insert into filme (nome, duracao) 
values ('Titanic', 120);
set @filme_id = LAST_INSERT_ID();
insert into rel_filme_genero (id_filme, id_genero) 
select @filme_id, g.id 
from generos g
where g.nome in ('comedia', 'romance'); 

commit

I only suggest that in your process flow you guarantee that the contents of the Gender table are controlled by you and do not allow the indiscriminate insertion of values. Even because the universe of values for Gender is not very big. This way I believe you can keep the current structure of tables.

Diagram

What I understood from your problem:

Itwouldbecorrecttosaythat

• 1usercanhave1orNmovies
• 1moviecanhave1orNgenres

Youcanreadbackwardsaswell

• 1genrecanbeinNmovies
• 1moviemaybewithNusers.Infact,Ithinkyoumeantthat1moviecanonlyhave1user,inthiscaseyourbusinessrulewillhavetoguaranteethisstatus.

AskingQuestionsfortheDataModel

Howtocountthenumberofmoviesinagivengenre?

SELECTg.nome,(SELECTCOUNT(*)FROMrel_filme_generoWHEREid_genero=g.id_genero)ASquantidadeFROMgenerogWHEREg.id_genero=1ORg.nome="nomeDoGenero"

How to count the number of user movies of a given genre?

SELECT usuario.nome, genero.nome, COUNT(*) AS quantidade FROM usuario 
LEFT JOIN rel_usuario_filme ON usuario.id_usuario = rel_usuario_filme.id_usuario 
LEFT JOIN filme ON rel_usuario_filme.id_filme = filme.id_filme 
LEFT JOIN rel_filme_genero ON filme.id_filme = rel_filme_genero.id_filme 
LEFT JOIN genero ON rel_filme_genero.id_genero = genero.id_genero 
WHERE usuario.id_usuario = 1 AND genero.nome = "Acao" 
GROUP BY usuario.id_usuario, genero.id_genero

How do I find out the number of movies per genre?

Within the various possible forms, follow one:

SELECT nome, (SELECT COUNT(*) FROM rel_filme_genero WHERE id_genero = g.id_genero) AS quantidade 
FROM genero g

You can use this simple data model I did in SQL Fiddle to test

Taking advantage of this, you can change LEFT (which will always give preference to the leftmost registers) poin INNER to only return lines of result home there are records from both sides at the time of the join. In this question, you have more details on the types of JOIN's

To correct the relationship problem, I will pass your script with the necessary changes, have a comment explaining why the

DROP TABLE IF EXISTS usuario;
CREATE TABLE usuario(
id INTEGER NOT NULL AUTO_INCREMENT,
email VARCHAR(50) NOT NULL UNIQUE,
nome VARCHAR(40) NOT NULL,
data_nascimento DATE NOT NULL,
PRIMARY KEY(id));

DROP TABLE IF EXISTS filme;
CREATE TABLE filme(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
duracao INT NOT NULL,
PRIMARY KEY(id));

DROP TABLE IF EXISTS genero;
CREATE TABLE genero(
id INTEGER NOT NULL AUTO_INCREMENT,
nome VARCHAR(40) NOT NULL,
PRIMARY KEY(id));

DROP TABLE IF EXISTS rel_usuario_filme;
CREATE TABLE rel_usuario_filme( 
id_usuario INTEGER NOT NULL, 
id_filme INTEGER NOT NULL, 
PRIMARY KEY(id_usuario, id_filme), 
CONSTRAINT fk_usuario FOREIGN KEY (id_usuario) REFERENCES usuario(id), 
CONSTRAINT fk_filme FOREIGN KEY (id_filme) REFERENCES filme(id));

DROP TABLE IF EXISTS rel_filme_genero;
CREATE TABLE rel_filme_genero(
id_filme INTEGER NOT NULL,
id_genero INTEGER NOT NULL,
PRIMARY KEY(id_filme, id_genero),
CONSTRAINT fk_rel_fg_genero FOREIGN KEY (id_genero) REFERENCES genero(id),
CONSTRAINT fk_rel_fg_filme FOREIGN KEY (id_filme) REFERENCES filme(id));

ALTER TABLE 'test'.'rel_filme_genero' 
DROP FOREIGN KEY 'fk_rel_fg_filme';
ALTER TABLE 'test'.'rel_filme_genero' 
ADD COLUMN 'id' INT NOT NULL AUTO_INCREMENT COMMENT 'id_filme e id_genero nao pode ser chave composta pois 1 filme pode ser comedia romantica por exemplo, entao as duas sao apenas chaves estrangeiras aqui' FIRST,
DROP PRIMARY KEY,
ADD PRIMARY KEY ('id'),
ADD INDEX 'fk_rel_fg_filme_idx' ('id_filme' ASC);
ALTER TABLE 'test'.'rel_filme_genero' 
DROP FOREIGN KEY 'fk_rel_fg_genero';
ALTER TABLE 'test'.'rel_filme_genero' ADD CONSTRAINT 'fk_rel_fg_genero'
  FOREIGN KEY ('id_genero')
  REFERENCES 'test'.'genero' ('id')
  ON DELETE NO ACTION
  ON UPDATE NO ACTION,
ADD CONSTRAINT 'fk_rel_fg_filme'
  FOREIGN KEY ('id_filme')
  REFERENCES 'test'.'filme' ('id')
  ON DELETE NO ACTION
  ON UPDATE NO ACTION;

For your question about the total of films of user 1 that is of genre 3, the rule of normal form says not to store information that can be obtained by calculation and / or query I remember the number of the rule and possibly not with these words) then the correct one would be a query that makes that total for you

SELECT u.nome,
    (select sum(1) from rel_usuario_filme ruf, rel_filme_genero rfg where ruf.id_usuario=u.id and ruf.id_filme=rfg.id_filme and rfg.id_genero=3) as total_filmes
FROM usuario u
where 
    -- u.nome = 'joao' ou pelo id abaixo
    u.id = 1;