This is part two of a series on databases and structured query language (SQL).
There are four primary operations to modify table data in SQL databases. Keep in mind that the following are only operations for modifying data, and that there are many more operations used to modify table structure, modify database structure, user privileges, and interrelational (between tables) operations.
- Create for making rows (tuples) in tables (relations).
- Read for retrieving information from tables.
- Update for changing one or more attributes of one or more rows in a table.
- Delete for removing one or more rows of a table.
In SQL, commands are issued to a database. Each command is ended with a semicolon. The database can return a success signal, an error, or a set of data. The following paragraphs demonstrate how to formulate commands that perform the above 4 operations.
The create operation is implemented through the insert keyword. Specifically, use the following:
insert into table-name (attribute 1, attribute 2, ..) values (value 1, value 2, ..);
Attribute names are always words, and must correspond to the name of a column in the table. If attribute names contain special characters like spaces, surround them with back-ticks (`). The back-tick key is the same as the tilde (~) key and is usually below the Escape key.
Values can be numbers like 4 or 26.5 or strings (sequences of characters) like Mr. Jones. Strings can be surrounded with double-quotes (“), single-quotes(‘), or back-ticks (`). Some database implementations do not support single-quotes.
insert into classes (teacher,biography) values (`Mr. Jones`,`A native of Southern California, Mr. Jones enjoys off-roading with his family.`);
Be careful of using double-quotes to surround strings that themselves contain quotes, such as I always say “don’t look back”. They can be surround with back-ticks instead of double-quotes, or they can be escaped.
Escape characters refer to special sequences of characters that are replaced by the database system with characters that usually can’t be typed. For example:
- \” is replaced with a double-quote.
- \’ is replaced with a single-quote.
- \n is replaced with a new-line character.
- \\ is replaced with a literal backslash character.
In the example query above, the id field is neither in the attributes list nor the values list. In that case, the database decides what value to give it.
The id is a primary key, so the database gives it an automatically incremented value based on the previous rows of the database. Varchar fields can have a default value set to them. Some fields will be set to null, which is an absence of a value. Some database systems allow a field to be configured to return an error if it defaults to null, known as the not null property of a field.
The next part (part 3) in this series will cover the second of the 4 basic database operations: create, read, update, and delete (CRUD for short).