Preparing Data

In relational databases, the most common operation is querying.

To facilitate explanation and practice, we have prepared a students table and a classes table, structured as follows:

The students table stores student information:

id	class_id	name	gender	score
1	1	Xiaoming	M	90
2	1	Xiaohong	F	95
3	1	Xiaojun	M	88
4	1	Xiaomi	F	73
5	2	Xiaobai	F	81
6	2	Xiaobing	M	55
7	2	Xiaolin	M	85
8	3	Xiaoxin	F	91
9	3	Xiaowang	M	89
10	3	Xiaoli	F	85

The classes table stores class information:

id	name
1	Class One
2	Class Two
3	Class Three
4	Class Four

Please note that unlike MySQL's persistent storage, the data in these two tables is imported into memory when the page loads using the AlaSQL in-memory database and only exists in the browser's memory. Therefore, refreshing the page will reset the data to the initial values above.

MySQL

If you want to practice with MySQL, you can copy this SQL script :

sql

-- Create the test database if it does not exist:
CREATE DATABASE IF NOT EXISTS test;

-- Switch to the test database
USE test;

-- Drop the classes and students tables if they exist:
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;

-- Create the classes table:
CREATE TABLE classes (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Create the students table:
CREATE TABLE students (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(1) NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Insert records into the classes table:
INSERT INTO classes(id, name) VALUES (1, 'Class One');
INSERT INTO classes(id, name) VALUES (2, 'Class Two');
INSERT INTO classes(id, name) VALUES (3, 'Class Three');
INSERT INTO classes(id, name) VALUES (4, 'Class Four');

-- Insert records into the students table:
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, 'Xiaoming', 'M', 90);
INSERT INTO students (id, class_id, name, gender, score) VALUES (2, 1, 'Xiaohong', 'F', 95);
INSERT INTO students (id, class_id, name, gender, score) VALUES (3, 1, 'Xiaojun', 'M', 88);
INSERT INTO students (id, class_id, name, gender, score) VALUES (4, 1, 'Xiaomi', 'F', 73);
INSERT INTO students (id, class_id, name, gender, score) VALUES (5, 2, 'Xiaobai', 'F', 81);
INSERT INTO students (id, class_id, name, gender, score) VALUES (6, 2, 'Xiaobing', 'M', 55);
INSERT INTO students (id, class_id, name, gender, score) VALUES (7, 2, 'Xiaolin', 'M', 85);
INSERT INTO students (id, class_id, name, gender, score) VALUES (8, 3, 'Xiaoxin', 'F', 91);
INSERT INTO students (id, class_id, name, gender, score) VALUES (9, 3, 'Xiaowang', 'M', 89);
INSERT INTO students (id, class_id, name, gender, score) VALUES (10, 3, 'Xiaoli', 'F', 85);

-- OK:
SELECT 'ok' as 'result:';

and run it in the command line:

$ mysql -u root -p < init-test-data.sql

This will automatically create the test database and create the students and classes tables under the test database, along with the necessary initialization data.

Unlike in-memory databases, all modifications made to the MySQL database will be saved. If you wish to restore to the initial state, you can run the script again.

Preparing Data ​

MySQL ​

Preparing Data

MySQL