We'll cover how to create and delete database tables in SQLite as well as how to add columns to an existing table.
CREATE TABLEkeyword to create a new table with columns, including the
.helpcommand to get a complete list of SQL commands
.tablescommand to list all the tables in a database
.schemacommand to look at the structure of a database
ALTER TABLEkeywords to add columns to a table
DROP TABLEkeywords to delete a table
Relational Databases like SQLite store data in a structure we refer to as a table. You can think of a table in a database a lot like you would a spreadsheet. We define specific columns in our table, and then we store any number of what we refer to as 'records' as rows in our database. A record is just information referring to one specific entity. For instance, if you had a table called "People" you could imagine a structure like this:
Each column has a name, and each row contains the corresponding information about a person.
When we name columns in our database, there are a couple of conventions we will follow. The first is that we will always use lowercase letters when referring to columns in our database. SQLite isn't case sensitive about its commands or column names, but it is generally best practice for us to stick to lowercase for our column names.
The second convention we want to follow is more important. That is, when we have multiple words in a column name, we link them together using underscores rather than spaces. We call this convention "snake_case". So, for instance, if we wanted to be more specific with our email column above, we can name it email_address. If we wanted to split up name to first and last we might have columns called first_name and last_name.
In the following sections, we'll cover how to create, alter, and delete database tables. This reading is accompanied by a code along exercise that you can do in your terminal. You don't need to fork this repository, and there are no tests to pass. Follow along with the reading and code along instructions.
When we create a new database, it comes like a sort of blank slate.
Once you create a database (which you can do with the
sqlite3 database_name.db command), we create a table using the following statement:
CREATE TABLE table_name;
But before we're able to store any actual data in a table, we'll need to define the columns in the table as well as the specific type of data each column will store.
Let's give it a shot. For the purposes of this code along, you'll be typing these commands into your terminal.
sqlite> CREATE TABLE cats;
You should see the following error:
sqlite> CREATE TABLE cats; Error: near ";": syntax error
SQLite expects us to include at least some definition of the structure of this table as well. In other words, when we create database tables, we need to specify some column names, along with the type of data we are planning to store in each column. More on data types later.
Let's try that table statement again:
sqlite> CREATE TABLE cats ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER );
Let's break down the above code:
CREATE TABLEcommand to create a new table called "cats"
TEXTmeans we'll be storing plain old text,
INTEGERmeans we'll store a number. Note that the use of capitalization is arbitrary, but it is a convention to help separate the SQL commands from the names we make up for our tables and columns.
Okay, let's check and make sure that we successfully created that table. To do this we'll be using SQL commands. To get a complete list of commands, you can type
.help into the sqlite prompt.
sqlite> .help .backup ?DB? FILE Backup DB (default "main") to FILE .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo ON|OFF Turn command echo on or off .exit Exit this program .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. .header(s) ON|OFF Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE. .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns. (See .width) html HTML <table> code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator TEXT tabs Tab-separated values tcl TCL list elements .nullvalue TEXT Print TEXT in place of NULL values .output FILENAME Send output to FILENAME .output stdout Send output to the screen .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILENAME Execute SQL in FILENAME .restore ?DB? FILE Restore content of DB (default "main") from FILE .schema ?TABLE? Show the CREATE statements If TABLE specified, only show tables matching LIKE pattern TABLE. .separator TEXT Change separator used by output mode and .import .show Show the current values for various settings .stats ON|OFF Turn stats on or off .tables ?TABLE? List names of tables If TABLE specified, only list tables matching LIKE pattern TABLE. .timeout MS Try opening locked tables for MS milliseconds .vfsname ?AUX? Print the name of the VFS stack .width NUM1 NUM2 ... Set column widths for "column" mode .timer ON|OFF Turn the CPU timer measurement on or off
Woah, that's a lot. Don't worry too much about all of these different commands right now. Just know that you can always use
.help to check out the available options.
Okay, let's check out our new table. To list all the tables in the database we'll use the .tables command. Type it into the sqlite prompt and hit enter, you should get:
sqlite> .tables cats
We can look at the structure, or "schema", of our database (i.e. the tables and their columns + column data types) with the
sqlite> .schema CREATE TABLE cats( id INTEGER PRIMARY KEY, name TEXT, age INTEGER );
Let's move on to altering our table.
Let's say that, after creating a database and creating a table to live inside that database, we decide we want to add or remove a column. We can do so with the
ALTER TABLE statement.
breed, to our
sqlite> ALTER TABLE cats ADD COLUMN breed TEXT;
Let's check out our schema now:
sqlite> .schema CREATE TABLE cats( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, breed TEXT );
Notice that the
ALTER statement isn't here, but instead SQLite has updated our original CREATE statement. The schema reflects the current structure of the database, which is reflected as the CREATE statement necessary to create that structure.
Fortunately, SQLite still supports most of what we'll need to use it for one way or another. For now, if you need to change a column name, it's best to simply delete the table and re-create it.
Lastly, we'll discuss how to delete a table from a database with the
DROP TABLE statement.
Deleting a table is very simple:
sqlite> DROP TABLE cats;
And that's it! You can exit out of the sqlite prompt with the
View Database Basics on Learn.co and start learning to code for free.
View SQL Database Basics on Learn.co and start learning to code for free.
View SQL Database Basics on Learn.co and start learning to code for free.