Getting data into the tables in our databases, and updating and deleting that data when necessary, is where we will spend a good portion of our time when working with MariaDB. To learn how to do this, we will be covering the following commands in this chapter:
- Using
INSERT - Using
UPDATE - Using
DELETE
To put data into our database, we use the INSERT command. The basic syntax is as follows:
INSERT [INTO] <table_name> [(<column_name>[, <column_name>,...])] {VALUES | VALUE} ({<expression>|DEFAULT},...)[,(...),...];
As with the CREATE TABLE command in the previous chapter, the parts of the syntax example within the angle brackets (<>) are what we'll replace with our own values. The parts between the square brackets ([]) are optional and the pipe character (|) means or. The curly brackets ({}) specify a mandatory section where there is a choice of the key word which you can use. For example, the INTO keyword is optional but makes the INSERT line more readable and we can use the keyword VALUE or VALUES depending on whether we are inserting a single column of information or multiple columns, but we must use one of them. Three dots (...) represent the part where the previous part can be repeated.
The expression part is the value that we want to put in a column. It could be a calculated value (such as today's date + four days), a static value (such as John) or it could be the default value assigned to the column (if it has one). Default values are assigned using the key word DEFAULT, without any quotes.
As shown in the syntax example, specifying the columns we want to put the data into is optional. For example, to insert a single row into the employees table that we created in the previous chapter, without specifying any column names, we would do the following:
INSERT INTO employees VALUES (NULL, "Perry", "Lowell Tom", "Tom", "1988-08-05");
The downside to not specifying the columns is that we must specify a value for every column in our table, in the order in which they are in the table definition.
For auto-incremented columns, such as the id column in our employees table, we have to put something but we can't put in our own value because MariaDB handles that. So, we use NULL as a placeholder to let MariaDB know that we are not providing a value and then MariaDB provides its own value. The keyword NULL, means no value. Some columns, based on their definition, may not allow us to use NULL.
We can use NULL for any other column we don't want to insert values into, as long as the column definition allows it. For example, if all we wanted to enter were the surname and given names for a couple of employees, we could do the following:
INSERT INTO employees VALUES (NULL, "Pratt","Parley", NULL, NULL), (NULL, "Snow","Eliza", NULL, NULL);
Each row is wrapped in parentheses and multiple rows are separated by commas.
As mentioned previously, the downside to not specifying the columns as a part of an insert statement is that we need to provide something for each column in each row that we are inserting. This can get cumbersome, especially if we have many columns.
More importantly though, in addition to providing something for each column, we also need to list the columns in the exact order that they appear in the database. This presents a safety concern because the order in which the columns appear in our table could change, and if it does, then the values we enter will be put into the wrong columns. For example, our inserts might fail because we're doing something like putting names into our birthday column. A worse error would be if the order of the surname and the givenname columns were switched. Because the data types for those two are the same, no failure will happen if they are in the wrong order in our INSERT statement, but will lead to invisible errors and names in the wrong place.
A better and safer method is to always specify the columns that we are inserting data into, even if it is every column in the table.
Often when we are inserting data into a table we only want to insert a few columns, for example, the surname and givenname columns in our employees database. To insert a partial row like this, we specify the columns we want to provide values for, as follows:
INSERT INTO employees (surname,givenname) VALUES ("Taylor","John"), ("Woodruff","Wilford"), ("Snow","Lorenzo");
Another benefit that we get from specifying specific columns is that we can change the order in which we provide our data (the order in the database will be unchanged). For example, we could list the pref_name column as follows:
INSERT INTO employees (pref_name,givenname,surname,birthday) VALUES ("George","George Albert","Smith","1970-04-04");
We can even just specify a single column (in which case we use VALUE instead of VALUES):
INSERT employees (surname) VALUE ("McKay");
Sometimes, the data we want to insert into a table already exists in another table in our database. To handle this kind of a situation, there is a special form of the INSERT command that we can use. The syntax is as follows:
INSERT [INTO] <table_1> [(<column_name>[, <column_name>,...])] SELECT <column_name>[, <column_name>,...] FROM <table_2>;
For example, suppose we have a table called names in our database, containing employee information from a different company that our company just merged with. Assuming that the data types are compatible with our employees database; the birthdays are stored as dates, the names are compatible with the varchar datatype that our table uses, and so on; we can add the data in that table to our employee table using something similar to the following:
INSERT INTO employees (surname, givenname, birthday) SELECT lastname, firstname, bday FROM names;
When doing an insert like this using the data from another table, we should be careful about trying to insert any auto-incremented columns, such as the id column in the employees database. If the table we are reading from has one of these columns, we either need to verify that there are no values which overlap, or omit it from the data we read as we did in the example.
Don't worry about the SELECT part of the example. The next chapter is all about that command.
Another way to insert data into a table is to read it from a file. One of the most common formats that such files come in is as tab delimited files, where a tab character separates the columns of information. MariaDB has a built-in command that can read these files and insert the data into a table. The basic format is as follows:
LOAD DATA [LOCAL] INFILE '<filename>' INTO TABLE <tablename> [(<column_name>[, <column_name>,...]];
If the LOCAL option is used, MariaDB will look for the file on the filesystem that we are running our client on; otherwise it will look in its own filesystem. If we are running the client on the same computer that is running MariaDB, then the LOCAL option does nothing. In either case, it is recommended to give the full path to the file.
If the columns are not specified as part of the command, MariaDB will expect the data in the file to contain every column that the table we are inserting into has, and that they are in the same order. By specifying the columns, we are telling MariaDB what each of the columns in the file are and where they go in our table.
As an example of loading a file, suppose we have a file named new_employees, which has three columns which correspond to the birthday, surname, and givenname columns in our employees table. Look at the following example:
1971-08-09 Anderson Neil 1985-01-24 Christofferson Todd
To load this file into our employees table, we would do something similar to the following:
LOAD DATA INFILE '/tmp/new_employees' INTO TABLE employees (birthday, surname, givenname);
There are many more options for this command, including such things as skipping the first few lines of a file, changing the separator for the columns from tab to something else, and so on.