I made a mistake in the table structure. How to fix?

Question

MySQL

There is an InnoDB table that stores student progress.
Table structure

CREATE TABLE `performance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pupil_id` int(11) NOT NULL,
  `date` date NOT NULL,
  `read` tinyint(4) NOT NULL,
  `write` tinyint(4) NOT NULL,
  `listening` tinyint(4) NOT NULL,
  `translate` tinyint(4) NOT NULL,
  `late` tinyint(1) NOT NULL,
  `nothw` tinyint(1) NOT NULL,
  `skip` tinyint(1) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Where assessment was not carried out, 0 is set. Subsequently, these data are used to build a graph of progress. Naturally, you do not need to take a zero value for display on the chart. The graph is built in accordance with the values ​​stored in the read, write, listening, translate fields - along the Y axis. The date is displayed along the X axis.
To get the scores for each activity without counting zeros, you would have to do a SELECT for each column. The request looks like this:

SELECT performance.date, performance.read FROM performance WHERE performance.read <> 0;

Same queries for other columns.
Table example, the columns are in order a little off:

id	date	pup_id	read	write	listening	translate	skip	late	nothing
one	01.02.2013	7	ten	ten	0	4	0	0	0
2	02.02.2013	7	eight	eleven	4	4	0	0	0
3	03.02.2013	7	0	0	0	0	one	0	0

How to fix the structure so that you can get the data with one request?

Answer 1

Create a relationship like this: [id, dt, pupil_id, disc_id, value]. For each student and subject on the desired date, an assessment is made. Then you get all the necessary data and draw a graph based on it. Another advantage of this form of storage is that you can build a general graph of academic performance without taking into account students, and other analytical queries.

Answer 2

I didn’t really understand what prevents me from adding conditions to WHERE through AND? Could you please clarify if I misunderstood the problem?

Answer 3

And is it not an option to do the processing and cleaning of data before giving it to the chart? Even if you had NULL or any other value there, you would not have managed with one request.
There is nothing wrong with four simple queries. Set up the indexes.
Or split the data into two tables, but you will have to write a migration script.

Answer 4

Why not just get all the data and just draw dates with zeros at the level of program logic?
You yourself at least imagine in what form you want to get the resulting table?

Answer 5

Make a temporary table (CREATE TEMPORARY TABLE), put INSERT (...) SELECT… WHERE>0 data there several times, and then do SELECT… GROUP BY.