The equivalent of Common Table Expressions (CTEs) in MySQL
In this particular case the CTE could be replaced with a normal subselect (a derived table):

SELECT
  tbl_notifications.module_name,
  tbl_notifications.message,
  tbl_notifications.staff_username,
  tbl_notifications.date_time 
FROM
  (
    SELECT
      tbl_modules.module_code
    FROM
      tbl_student
      INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
    WHERE
      tbl_student.student_number = '1002'
  ) AS CTE
  INNER JOIN tbl_notifications ON cte.module_code = tbl_notifications.module_code
ORDER BY
  tbl_notifications.date_time DESC
;


And that query would work both in SQL Server and MySQL (in any major SQL product, in fact).

Moreover, nesting is unnecessary here at all, and so you could have the same result with the following:

SELECT
  tbl_notifications.module_name,
  tbl_notifications.message,
  tbl_notifications.staff_username,
  tbl_notifications.date_time 
FROM
  tbl_student
  INNER JOIN tbl_modules ON tbl_student.registration_year = tbl_modules.registration_year
  INNER JOIN tbl_notifications ON tbl_modules.module_code = tbl_notifications.module_code
WHERE
  tbl_student.student_number = '1002'
ORDER BY
  tbl_notifications.date_time DESC
;


And by the way, please consider using short table aliases in your queries. Here is what the same query looks like with short aliases:

SELECT
  n.module_name,
  n.message,
  n.staff_username,
  n.date_time 
FROM
  tbl_student AS s
  INNER JOIN tbl_modules AS m ON s.registration_year = m.registration_year
  INNER JOIN tbl_notifications AS n ON m.module_code = n.module_code
WHERE
  s.student_number = '1002'
ORDER BY
  n.date_time DESC
;


It may be subjective sometimes, but I do believe that in this case readability improves greatly.