SQL CRUD Basics: Part 1 – Create

In Introduction to SQL CRUD Basics, I listed out the 4 elements of CRUD. Create is the first and the subject of this post. Create relates to the SQL INSERT statement, which is used to introduce new rows of data into a database table. Continue reading to learn basic usage of this first CRUD element.

Photo by Nikhil Mitra on Unsplash

Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.

OS and DB used:
  • OpenSuse Leap 15.1
  • MySQL 8.0.18

Self-Promotion:

If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!

It is difficult to comprehend (for me) the amount of time that’s passed since I wrote, Populating a MySQL table with the INSERT statement – Beginner Series. I feel I have grown as a SQL Developer and technical writer, therefore revisiting this topic makes sense to me. And by that, I hope to further provide a fantastic post, with solid fundamental examples, for anyone wanting to learn about the INSERT statement.

If you visit the on-line MySQL 8 INSERT documentation, it’s plain to see that INSERT is quite a complex command, as it should be. Loading new rows of data into a table is important, to say the least. Else, all you have got is an empty table.

Foregoing the majority of the many available options, this post will instead, concentrate on a basic INSERT syntax in the form of:

1
2
INSERT INTO some_table(column(s) to populate)
VALUES(column(s) values to store);

Imagine we have a simple ‘friends’ table with columns: first_name, last_name, phone_num, birthday, and age. To be honest, the ‘age’ column is perhaps not a value you even need to store, as it can be easily calculated using the ‘birthday’ column values and any of the available system date components that are a part of any SQL flavor. However, I included it in the ‘friends’ table to represent a more complete data set.

Here is the ‘friends’ CREATE TABLE statement:

1
2
3
4
5
6
7
8
9
10
11
mysql> SHOW CREATE TABLE friends\G
*************************** 1. row ***************************
       Table: friends
Create Table: CREATE TABLE `friends` (
  `first_name` varchar(25) NOT NULL,
  `last_name` varchar(25) NOT NULL,
  `phone_num` char(12) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

A simple and basic INSERT can take the form of:

1
2
3
mysql> INSERT INTO friends(first_name, last_name, phone_num, birthday, age)
    -> VALUES('Jim', 'Dandy', '476-111-1122', '1977-07-19', 42);
Query OK, 1 row affected (0.10 sec)

Let’s look at each individual part of the overall statement:

  • INSERT INTO friends – The target table for the INSERT.
  • (first_name, last_name, phone_num, birthday, age) – The target table columns to be populated.
  • VALUES('Jim', 'Dandy', '476-111-1122', '1977-07-19', 42) – A list of supplied values to populate surrounded in the parentheses of the VALUES() clause (list).

So long as there is a value provided for each of the table columns, the column list itself can be completely omitted from the INSERT statement:

1
2
3
mysql> INSERT INTO friends
    -> VALUES ('Tara', 'Runner', '777-767-9900', '1980-01-23', 39);
Query OK, 1 row affected (0.25 sec)

Interlude – Recommended Reading

The example ‘friends’ table in this form does not have a designated PRIMARY KEY. Oftentimes, a PRIMARY KEY column is implemented using some form of an auto-incrementing INTEGER value. While this is not a hard and fast rule, it is quite prevalent in database table design. In MySQL, the AUTO_INCREMENT column attribute enables this functionality. All that being said, INSERT‘s on a column that have this attribute are handled a bit differently and I wrote all about it in the post, The AUTO_INCREMENT column attribute in MySQL – A beginning perspective. Be sure and read it for more details and information.

We learned in the previous example, that so long as a value is supplied for each column, the column list is optional. But, what if all the values are not present in the VALUES clause when there is no column list? Let’s exclude the ‘phone_num’ value and see what happens:

1
2
3
mysql> INSERT INTO friends
    -> VALUES ('Max', 'Maxer', '1975-01-23', 44);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

Since no columns list is present, INSERT expects a value for each one.

Yet, if you do include the column list, you can exclude the ‘phone_num’ column and matching value with no issue as shown by the successful INSERT below:

1
2
3
mysql> INSERT INTO friends(first_name, last_name, birthday, age)
    -> VALUES ('Max', 'Maxer', '1975-01-23', 44);
Query OK, 1 row affected (0.23 sec)

To understand why the previous INSERT worked, let’s look at the ‘friends’ table description:

1
2
3
4
5
6
7
8
9
10
11
mysql> DESC friends;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(25) | NO   |     | NULL    |       |
| last_name  | varchar(25) | NO   |     | NULL    |       |
| phone_num  | char(12)    | YES  |     | NULL    |       |
| birthday   | date        | YES  |     | NULL    |       |
| age        | int(11)     | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

You’re likely wondering what value was inserted?

Let’s see the present values and find out:

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM friends;
+------------+-----------+--------------+------------+------+
| first_name | last_name | phone_num    | birthday   | age  |
+------------+-----------+--------------+------------+------+
| Jim        | Dandy     | 476-111-1122 | 1977-07-19 |   42 |
| Tara       | Runner    | 777-767-9900 | 1980-01-23 |   39 |
| Max        | Maxer     | NULL         | 1975-01-23 |   44 |
+------------+-----------+--------------+------------+------+
3 rows in set (0.00 sec)

The NULL marker was inserted into column ‘phone_num’, as that is the DEFAULT value.

Anytime you want the DEFAULT value inserted, simply list it for that particular column in the VALUES clause:

1
2
3
mysql> INSERT INTO friends
    -> VALUES ('Mary', 'Moore', DEFAULT, '1978-09-23', 41);
Query OK, 1 row affected (0.27 sec)

Again, we can see the DEFAULT value inserted for the ‘phone_num’ column is the NULL marker:

1
2
3
4
5
6
7
8
9
mysql> SELECT * FROM friends;
+------------+-----------+--------------+------------+------+
| first_name | last_name | phone_num    | birthday   | age  |
+------------+-----------+--------------+------------+------+
| Jim        | Dandy     | 476-111-1122 | 1977-07-19 |   42 |
| Tara       | Runner    | 777-767-9900 | 1980-01-23 |   39 |
| Max        | Maxer     | NULL         | 1975-01-23 |   44 |
| Mary       | Moore     | NULL         | 1978-09-23 |   41 |
+------------+-----------+--------------+------------+------+

The reason NULL is acceptable – and inserted – is because of the DEFAULT NULL column definition as shown in the SHOW CREATE TABLE output in the top portion of the post.

Can you just omit any of the columns and values, knowing that NULL (potentially) will be inserted?
Let’s find out.

I’ll attempt an INSERT without the ‘first_name’ and ‘last_name’ columns:

1
2
3
mysql> INSERT INTO friends(first_name, last_name, phone_num, birthday, age)
    -> VALUES('319-223-8907','1979-08-15',40);
ERROR 1136 (21S01): Column count doesn't match value count at row 1

What about specifying DEFAULT for the column values?

1
2
3
mysql> INSERT INTO friends(first_name, last_name, phone_num, birthday, age)
    -> VALUES (DEFAULT, DEFAULT,'319-223-8907','1979-08-15',40);
ERROR 1364 (HY000): Field 'first_name' doesn't have a default value

What if we try NULL?

1
2
3
mysql> INSERT INTO friends(first_name, last_name, phone_num, birthday, age)
    -> VALUES (NULL, NULL,'319-223-8907','1979-08-15',40);
ERROR 1048 (23000): Column 'first_name' cannot be null

Recall from the SHOW CREATE TABLE friends; command, columns ‘first_name’ and ‘last_name’ both have the NOT NULL definition and cannot contain NULL. Therefore, the INSERT fails.

Lastly, to INSERT multiple rows in one command, the VALUES clause accepts each individual row’s worth of column values, surrounded by a matching set of parenthesis and separated by commas. See in the example below where 3 new rows are added in one INSERT:

1
2
3
4
5
mysql> INSERT INTO friends(first_name, last_name, phone_num, birthday, age)
     > VALUES('Charlie','Charles','888-767-2323','1971-08-22',48),
     > ('Humpty','Dumpty','118-257-7344','1971-11-22',48),
     >('Roger','Dodger','234-767-3983','1975-08-22',44);
Query OK, 3 rows affected (0.24 sec)

A Bit on SQL Modes

Specific to MySQL, much of INSERT‘s behavior is dictated by the current SQL Mode. Particularly, strict SQL mode affects how DEFAULT column values are stored. Visit the 3 links below for more information:

Congratulations! You know how to add new rows of data into a table using the INSERT command. Now, you need to read that data right? But how?
In part 2 of CRUD SQL Basics, we will learn all about the Read aspect by way of the SQL SELECT clause. If you are not already, be sure and subscribe to the blog (via the Subscribe button in the sidebar) and receive a notification when new posts are published.

Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!

A Call To Action!

Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.

Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.

Have I mentioned how much I love a cup of coffee?!?!

To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)

Be sure and visit the “Best Of” page for a collection of my best blog posts.

Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.

Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, is performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.

6 thoughts on “SQL CRUD Basics: Part 1 – Create”

  1. Pingback: SQL CRUD Compilation - Digital Owl's Prose
  2. Pingback: MySQL Shell CRUD with Python: Create – with examples. | Digital Owl's Prose
  3. Pingback: Pyodbc SQL CRUD – Create: Examples with MySQL | Digital Owl's Prose
  4. Pingback: SQL CRUD Basics Part 4 – Delete. | Digital Owl's Prose
  5. Pingback: SQL CRUD Basics Part 3 – Update. | Digital Owl's Prose
  6. Pingback: SQL CRUD Basics Part 2 – Read | Digital Owl's Prose

Hey thanks for commenting! Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.