Posted on June 18, 2013
After almost a year of immersing myself in computer science, I’ve come to a point where I can confidently say that I’ve grasped the basics of database administration via SQL. I have a new found appreciation for what Salesforce.com has built, as they’ve made this same process into one that someone without a degree in computer science can not only do, but do incredibly well and put in place a system that their whole organization can benefit from.
But for those of us with aspirations of software development (as a hobby or a career) will probably come across a need for SQL database administration at some point.
What is a SQL Database? Structured Query Language (SQL) databases are not as scary as they sound. Anyone with (a) patience, and (b) a basic-to-intermediate understanding of database management (in Excel, Access, or a CRM like Salesforce or Microsoft Dynamics) will find SQL databases to be incredibly helpful in managing, manipulating, and analyzing data.
SQL databases are essentially an Excel spreadsheet without the ability to view all of the data at a glance. Instead, SQL databases rely on written queries, that pull back data in the database that meets the criteria specified in those queries. For this tutorial, we’ll use a ‘lite’ SQL language called SQLite3. If you use a Macbook, it’s most likely already installed. If not, you can install SQLite3 from sqlite.org. The goal of this post is to give you an overview on SQLite3 (which is general enough that it can be applied to learning other SQL languages) but also allows you to get started with some simple database management.
Creating a Database and a Table To create a test database, open your command terminal and enter the following:
>> sqlite3 test.db
For this example, we’re going to create a simple database to store the names, release dates and directors of our favorite films. To create this table:
sqlite> CREATE TABLE films (id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(30), year SMALLINT, director VARCHAR(30))
The first two pieces of this command, CREATE TABLE, tells SQLite3 that we’re creating a new table. Within the parenthesis, we’ll be creating columns and formatting these columns to accept certain types of data (integers or text). There are three pieces of the id column that are interesting: INTEGER tells SQLite3 that the values in this column will be whole numbers and will not include any text, special characters, or floating point integers.
PRIMARY KEY tells SQLite3 that each of these values will need to be unique. This is sometimes referred to as the Unique Key and gives us the ability to refer to each record individually, if needed.
AUTOINCREMENT tells SQLite3 that when a new record is created, the value should be incremented by 1 from the value of the last record.
So the first record that is entered into the database will have a value of 1, the next record will have a value of 2, and so on. Now that we’ve got our test database set up, let’s add some data.
Let’s get started by adding our first movie to the database.
sqlite> INSERT INTO films(title,year,director) VALUES('Boogie Nights',1996,'Paul Thomas Anderson');
Oops…I just realized that Boogie Nights came out in 1997, and not 1996 like I had originally entered when inserting the data.
sqlite> UPDATE films SET year=1996 WHERE title='Boogie Nights';
Viewing Your Data
Now that I’ve added some of my favorite films, I want to view my database.
sqlite> select * from films; 1|Boogie Nights|1996|Paul Thomas Anderson 2|There Will Be Blood|2007|Paul Thomas Anderson 3|Star Wars Episode IV: A New Hope|1977|George Lucas 4|Star Wars Episode V: The Empire Strikes Back|1980|Irvin Kershner 5|Star Wars Episode VI: Return of the Jedi|1983|Richard Marquand 5|Magnolia|1999|Paul Thomas Anderson
Viewing the Database Schema
Let’s say I want to add some more information to the database, but I forgot what information I’m collecting and the types of data that each column holds. At this point, I’d refer to the database schema. A database schema is how the database is structured.
This means two things. First, the database schema shows what information is being collected – in our example, the film title, the release year, and the director. Second, the schema refers to the how the data is being collected and how that data is organized. The title is a VARCHAR field that accepts 30 characters. The year column is a SMALLINT, which means that we can store a small integer. To view the schema in SQLite3, use the .schema command and specify the table.
sqlite> .schema films CREATE TABLE films (id INTEGER PRIMARY KEY AUTOINCREMENT, title VARCHAR(30), year SMALLINT, director VARCHAR(30));
I’m thinking that I want to make this database family friendly. Boogie Nights is clearly not family friendly, so first I’ll remove that individual film.
sqlite> DELETE FROM films WHERE title='Boogie Nights';
Well, all Paul Thomas Anderson movies would be inappropriate for young families, so I’ll remove all of his films.
sqlite> DELETE FROM films WHERE director = 'Paul Thomas Anderson';
Now we can check to make sure our changes were made:
sqlite> select * from films; 3|Star Wars Episode IV: A New Hope|1977|George Lucas 4|Star Wars Episode V: The Empire Strikes Back|1980|Irvin Kershner 5|Star Wars Episode VI: Return of the Jedi|1983|Richard Marquand
That’s the very basic overview of SQL database administration – hopefully it's enough to get you started!