Select with the day of the week in Portuguese

You can use the weekday (date) function to know the day of the week and the solution will look similar to what you did for the month.

It considers the day of the week as an integer from 0 - 6 starting from Monday through Sunday.

Here's a solution:

Select *,id, data, mes,date_format('data','%d/%m/%Y') as 'data_formatada' FROM (SELECT DAYNAME(data) AS dia, year(data) AS ano,    
                    (CASE month(data) 
                       when 1 then 'Janeiro'
                       when 2 then 'Fevereiro'
                       when 3 then 'Março'
                       when 4 then 'Abril'
                       when 5 then 'Maio'
                       when 6 then 'Junho'
                       when 7 then 'Julho'
                       when 8 then 'Agosto'
                       when 9 then 'Setembro'
                       when 10 then 'Outubro'
                       when 11 then 'Novembro'
                       when 12 then 'Dezembro'
                       END) AS mes,
 (CASE WEEKDAY(data) 
                       when 0 then 'Segunda-feira'
                       when 1 then 'Terça-feira'
                       when 2 then 'Quarta-feira'
                       when 3 then 'Quinta-feira'
                       when 4 then 'Sexta-feira'
                       when 5 then 'Sábado'
                       when 6 then 'Domingo'                 
                       END) AS DiaDaSemana,
                       id, 
                       data,hora,evento,participante FROM agenda WHERE not (data is null)) as agenda

Just use lc_time_names = 'pt_PT';

SET lc_time_names = 'pt_PT';

Select  DAYNAME(NOW()) AS dia, year(NOW()) AS ano,    
    (CASE month(NOW()) 
       when 1 then 'Janeiro'
       when 2 then 'Fevereiro'
       when 3 then 'Março'
       when 4 then 'Abril'
       when 5 then 'Maio'
       when 6 then 'Junho'
       when 7 then 'Julho'
       when 8 then 'Agosto'
       when 9 then 'Setembro'
       when 10 then 'Outubro'
       when 11 then 'Novembro'
       when 12 then 'Dezembro'
       END) AS mes

In short, you just really need it.

SET lc_time_names = 'pt_PT';

Select  DAYNAME(NOW()) AS dia, year(NOW()) AS ano,     MONTHNAME(NOW()) AS mes

Much simpler.