Basic CRUD

The basic database operations - CRUD (Create, Read, Update, Delete) - can be demonstrated directly in MySQL Workbench. Enter SQL statements into the query editor and click the lightning-bolt icon to execute them; the icon to its left saves the statements into a .sql script.

Create

Consider maintaining the records of the students in a school. After creating a couple of two-dimensional tables, values can be inserted into them, as shown in the schema below.

Two tables are created:

The Students table with four fields:
- The student id, a fixed-width string. This field is a PRIMARY KEY, meaning the values must be unique.
- The fullname, with a maximum length of 128 characters.
- The dob (date of birth) of the student.
- The class, ie. the classroom of the student.
The Tests table with five fields:
- The test_id, a random fixed-width string.
- The student_id, the id of the student taking the test. This field is declared as a FOREIGN KEY that must match the id field of the Students table.
- The subj (subject) of the test.
- The dt, the date the test is taken.
- The score of the student.

ch01-crud-schema.sql:

-- #DROP DATABASE Moon_Primary_School; # resets the database

CREATE DATABASE Moon_Primary_School;

USE Moon_Primary_School;

CREATE TABLE Students (
   id CHAR(8) PRIMARY KEY,
   fullname VARCHAR(128),
   dob DATE,
   class CHAR(2)
);

CREATE TABLE Tests (
   test_id CHAR(8),
   student_id CHAR(8),
   subj VARCHAR(16),
   dt DATE,
   score INT,
   FOREIGN KEY (student_id) REFERENCES Students(id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
);

INSERT INTO Students VALUES
   ('hh09hhhj', 'Alex Phil', '2010-12-11', '2A'),
   ('g98fdhh8', 'Mike Tudor', '2011-05-28', '1C'),
   ('fsh8sd8f', 'Ivy Hugh', '2010-10-23', '2D');

INSERT INTO Tests VALUES
   ('H_sdjksd', 'g98fdhh8', 'History', '2023-05-18', 99),
   ('M_sasgkj', 'hh09hhhj', 'Maths', '2023-01-05', 87),
   ('M_sasgkj', 'fsh8sd8f', 'Maths', '2023-01-05', 45),
   ('M_lgjg88', 'hh09hhhj', 'Maths', '2023-03-21', 53),
   ('M_lgjg88', 'fsh8sd8f', 'Maths', '2023-03-21', 45),
   ('M_qas9ij', 'hh09hhhj', 'Maths', '2023-05-13', 57),
   ('M_qas9ij', 'fsh8sd8f', 'Maths', '2023-05-13', 79);

-- #DROP DATABASE Moon_Primary_School; # resets the database

CREATE DATABASE Moon_Primary_School;

USE Moon_Primary_School;

CREATE TABLE Students (
   id CHAR(8) PRIMARY KEY,
   fullname VARCHAR(128),
   dob DATE,
   class CHAR(2)
);

CREATE TABLE Tests (
   test_id CHAR(8),
   student_id CHAR(8),
   subj VARCHAR(16),
   dt DATE,
   score INT,
   FOREIGN KEY (student_id) REFERENCES Students(id)
      ON DELETE CASCADE
      ON UPDATE RESTRICT
);

INSERT INTO Students VALUES
   ('hh09hhhj', 'Alex Phil', '2010-12-11', '2A'),
   ('g98fdhh8', 'Mike Tudor', '2011-05-28', '1C'),
   ('fsh8sd8f', 'Ivy Hugh', '2010-10-23', '2D');

INSERT INTO Tests VALUES
   ('H_sdjksd', 'g98fdhh8', 'History', '2023-05-18', 99),
   ('M_sasgkj', 'hh09hhhj', 'Maths', '2023-01-05', 87),
   ('M_sasgkj', 'fsh8sd8f', 'Maths', '2023-01-05', 45),
   ('M_lgjg88', 'hh09hhhj', 'Maths', '2023-03-21', 53),
   ('M_lgjg88', 'fsh8sd8f', 'Maths', '2023-03-21', 45),
   ('M_qas9ij', 'hh09hhhj', 'Maths', '2023-05-13', 57),
   ('M_qas9ij', 'fsh8sd8f', 'Maths', '2023-05-13', 79);

As the Tests table is populated, notice that some data is repeated and redundant - for example the test id, the subject, and the test date - resulting in inefficient storage.

In real-world scenarios, a better design would split Tests into two separate tables:

CREATE TABLE IF NOT EXISTS Tests (
  test_id CHAR(8),
  subj VARCHAR(16),
  dt DATE
);
CREATE TABLE IF NOT EXISTS Scores (
  test_id CHAR(8),
  student_id CHAR(8),
  score INT
);

Splitting the repeated test metadata (subject, date) out of Tests and into a separate Scores table, joining them together when necessary.

For the sake of simplicity, the remainder of this chapter sticks to the two original tables shown in the runnable schema above.

Read

With the tables populated, data can be queried. This retrieves the id, fullname, and class columns for every row in Students:

SELECT id, fullname, class FROM Students;

SELECT id, fullname, class FROM Students;

id	fullname	class
hh09hhhj	Alex Phil	2A
g98fdhh8	Mike Tudor	1C
fsh8sd8f	Ivy Hugh	2D

Update

To modify an existing record - changing the class of the student whose id is 'hh09hhhj' to '2B':

UPDATE Students SET class='2B' WHERE id='hh09hhhj';
SELECT id, fullname, class FROM Students;

UPDATE Students SET class='2B' WHERE id='hh09hhhj';
SELECT id, fullname, class FROM Students;

Query OK, 1 row affected

id	fullname	class
hh09hhhj	Alex Phil	2B
g98fdhh8	Mike Tudor	1C
fsh8sd8f	Ivy Hugh	2D

Delete

To delete a record - removing the row in Students whose id is 'hh09hhhj'. Because Tests.student_id references Students.id with ON DELETE CASCADE, matching rows in Tests are removed too:

DELETE FROM Students WHERE id='hh09hhhj';
SELECT id, fullname, class FROM Students;
SELECT test_id, student_id, subj FROM Tests;

DELETE FROM Students WHERE id='hh09hhhj';
SELECT id, fullname, class FROM Students;
SELECT test_id, student_id, subj FROM Tests;