In the previous chapter, we learned not only how to create a database but how to do it well. Well-considered table design, column constraints, and relationships will really shine once we start putting data into the tables. With our strong table design and the well-thought-out relationships between them, we will be able to join efficiently and easily. When a piece of data needs to be changed (e.g., a customer address), we only need to change it in one place rather than several. When a bad piece of data comes in, hopefully we have set enough sensible constraints to prevent it from entering the database.
In this chapter, we will learn how to INSERT
, DELETE
, and UPDATE
records. Fortunately, writing operations like these is much simpler than writing SELECT
statements.
These SQL write operations do not have to be done by a human. Software (written in Java, Python, or other coding languages) will often generate and execute SQL statements to read and write data the same way a human would, but much more efficiently. Although this is beyond the scope of this book, coding languages will be touched on in the next chapter if that is pertinent to you.
In a relational database, data only exists if the database first receives records. The INSERT
statement does just that and inserts a record into the database. You can pick what fields to populate in the record, and the rest of the fields will be null or use a default value.
First, we will INSERT
an ATTENDEE
record into the SurgeTech database we created in the last chapter. An INSERT
to add yourself to the database should look something like this. Execute the following statement with your name:
INSERT
INTO
ATTENDEE
(
FIRST_NAME
,
LAST_NAME
)
VALUES
(
'Thomas'
,
'Nield'
)
Let’s break this statement down:
INSERT
INTO
ATTENDEE
(
FIRST_NAME
,
LAST_NAME
)
VALUES
(
'Thomas'
,
'Nield'
)
To start, we declare that we are inserting a record into the ATTENDEE
table, and the fields we are choosing to populate are FIRST_NAME
and LAST_NAME
:
INSERT
INTO
ATTENDEE
(
FIRST_NAME
,
LAST_NAME
)
VALUES
(
'Thomas'
,
'Nield'
)
Then we specify the values for each of these fields. Note that we specify the values in the same order we declared the fields in: 'Thomas'
corresponds to FIRST_NAME
, and 'Nield'
to LAST_NAME
.
Run SELECT * FROM ATTENDEE
to see if our INSERT
made it in. Sure enough, the record now exists (Figure 10-1).
There are a number of observations to make here. We did not populate all the columns in our INSERT
, but due to the rules we created in the previous chapter, some of the columns were assigned a default value.
The ATTENDEE_ID
gave itself a value of 1 due to our PRIMARY KEY
and AUTOINCREMENT
rule. If we were to INSERT
another record, it would automatically be assigned an ATTENDEE_ID
of 2, then 3, and so on. On an INSERT
, you should avoid populating the ATTENDEE_ID
field yourself and let it assign its own ID.
Again, the AUTOINCREMENT
constraint in SQLite is actually not necessary. It is needed for MySQL and other platforms, though, hence why we are doing it for practice. In SQLite, simply making a column of type INTEGER
a primary key will automatically assign IDs to new records.
PHONE
and EMAIL
were not specified in our INSERT
, so they were left null. If either of these columns had a NOT NULL
constraint and no default value policy, our INSERT
would have failed. But in our design, we have allowed these two fields to be null in case our attendees prefer to be off the grid.
The VIP
status was not specified in our INSERT
either, but we gave this field a default value of false (0). So instead of making it null, SQLite resorted to using the default value we specified.
Hopefully by now, you are already appreciating that the design is working efficiently. Because of the policies we set, the columns resorted to default values when they were not provided with any.
If you have a lot of records to INSERT
, you do not have to do it one at a time. You can specify multiple records in a single INSERT
command. Simply repeat the clause following VALUES
and separate each entry with a comma:
INSERT
INTO
ATTENDEE
(
FIRST_NAME
,
LAST_NAME
,
PHONE
,
,
VIP
)
VALUES
(
'Jon'
,
'Skeeter'
,
4802185842
,
'[email protected]'
,
1
),
(
'Sam'
,
'Scala'
,
2156783401
,
'[email protected]'
,
0
),
(
'Brittany'
,
'Fisher'
,
5932857296
,
'[email protected]'
,
0
)
Doing multiple inserts in this manner is far more efficient, especially if you have thousands of records. If a process written in Java or Python is populating a table, it should use this syntax to insert large amounts of records rather than executing one INSERT
at a time. Otherwise, the process can run very slowly.
You can also INSERT
records using the results from a SELECT
query. This is helpful if you need to migrate data from one table to another. Just make sure the SELECT
fields line up with the INSERT
fields and have the same order and data types:
INSERT
INTO
ATTENDEE
(
FIRST_NAME
,
LAST_NAME
,
PHONE
,
)
SELECT
FIRST_NAME
,
LAST_NAME
,
PHONE
,
FROM
SOME_OTHER_TABLE
Let’s take an opportunity to witness another policy of our design at work: the foreign keys.
Right now, we should only have four attendees with ATTENDEE_ID
assignments of 1, 2, 3, and 4. But test this functionality by inserting a COMPANY
record with a PRIMARY_CONTACT_ID
value of 5:
INSERT
INTO
COMPANY
(
NAME
,
DESCRIPTION
,
PRIMARY_CONTACT_ID
)
VALUES
(
'RexApp Solutions'
,
'A mobile app delivery service'
,
5
)
This query should have failed and an error should be displayed at the bottom of the window (Figure 10-2).
This is good because it means our foreign key constraint has worked: it kept an orphan record out. The INSERT
needs to have a PRIMARY_CONTACT_ID
that is existent. So, if we change it to 3 (Sam Scala), the INSERT
should now work correctly:
INSERT
INTO
COMPANY
(
NAME
,
DESCRIPTION
,
PRIMARY_CONTACT_ID
)
VALUES
(
'RexApp Solutions'
,
'A mobile app delivery service'
,
3
)
The DELETE
statement is dangerously simple. It deletes all records in a table:
DELETE
FROM
ATTENDEE
However, you can conditionally delete records with a WHERE
statement. If we wanted to remove all records that have no contact information, we could filter to records where PHONE
and EMAIL
are null:
DELETE
FROM
ATTENDEE
WHERE
PHONE
IS
NULL
AND
IS
NULL
Because it is perilously easy to make mistakes with a DELETE
statement, it is a good practice to replace the DELETE
with a SELECT *
first. Executing that query gives us a preview of what records will be deleted:
SELECT
*
FROM
ATTENDEE
WHERE
PHONE
IS
NULL
AND
IS
NULL
In the previous section, we looked at a means to delete all records from a table:
DELETE
FROM
ATTENDEE
Although not used in SQLite, on some database platforms (like MySQL) the preferred way to delete all records from a table is to use TRUNCATE TABLE
:
TRUNCATE
TABLE
ATTENDEE
Using this command will allow the database engine to reset the autoincrements for any primary keys as well as any other constraint behaviors. It also allows it to make some optimizations behind the scenes to reset the table.
While SQLite does not support TRUNCATE TABLE
, it does allow some similar optimizations when you run a DELETE
without a WHERE
.
Finally, we come to the UPDATE
command. The UPDATE
modifies existing records. If we wanted to update the EMAIL
values for all records to be uppercase, we could do that with this statement using the UPPER()
function:
UPDATE
ATTENDEE
SET
=
UPPER
(
)
We can also update multiple fields at once. Just separate each expression after the SET
keyword with a comma. To update both the FIRST_NAME
and LAST_NAME
fields to uppercase, run this command:
UPDATE
ATTENDEE
SET
FIRST_NAME
=
UPPER
(
FIRST_NAME
),
LAST_NAME
=
UPPER
(
LAST_NAME
)
Like with DELETE
, we can use a WHERE
to conditionally apply updates to records. Execute the following query to set the VIP
field to true where the ATTENDEE_ID
is 3 or 4:
UPDATE
ATTENDEE
SET
VIP
=
1
WHERE
ATTENDEE_ID
IN
(
3
,
4
)
There may be times where you want to remove a table altogether from the database. Just type DROP TABLE
followed by the name of the table you want to delete (this is a dangerous statement as well because it deletes the table permanently, so be careful and certain about what you are doing):
DROP
TABLE
MY_UNWANTED_TABLE
At this point, you have the tools you need to go out and create your own database and manage its data. You may have questions on how to do all of this efficiently or provide practical means for users to input and update data, because chances are you cannot teach all of them SQL and they will want a graphical user interface. Or maybe you want to know how to pump large amounts of data into a database automatically or synchronize it with another data source. This will be lightly addressed in the final chapter.
18.224.64.89