New terms from treehouse – Database Foundations Part 2 – Manipulating Schema with SQL, Joining Relational Data Between Tables in SQL, SQL Calculating, Aggregating and Other Functions, and Securing and Maintaining a MySQL Database

Manipulating Schema with SQL

Altering Tables

To rename a table, type RENAME TABLE, then the table name, the TO keyword, then the new name. Remember, you can use more than one statement, so long as you have a semi colon at the end of each.

Screen Shot 2014-12-28 at 4.23.11 PM

However, you can write that in one statement by comma separating them.

Screen Shot 2014-12-28 at 4.24.10 PM

To delete a table, use the DROP keyword, then TABLE, then its name. You can optionally add IF EXISTS to make it so that if the table doesn’t exist it just throws a warning rather than an error.

Screen Shot 2014-12-28 at 4.27.20 PM

To remove a table without deleting it forever, you can use the TRUNCATE keyword, then the table name (you don’t need the TABLE keyword). Note that this differs from DML, it’s still DDL. It actually deletes the table, then makes a new one with the same name but no rows in it.

Screen Shot 2014-12-28 at 4.29.46 PM

Altering Columns

To add a column, write ALTER TABLE, then the table name, then ADD, then the optional keyword COLUMN (to specify what we’re adding), then its name and data type.

Screen Shot 2014-12-28 at 4.34.06 PM

To add multiple columns, simply wrap them in parentheses and comma separate them.

Screen Shot 2014-12-28 at 4.35.08 PM

To change the names of preexisting columns, replace ADD with CHANGE. Once again COLUMN is optional. Then, write it’s old name, a space, then its new one, then its data type definition. Unlike ADD, CHANGE cannot accept multiple columns. You’d need a second statement.

Screen Shot 2014-12-28 at 4.38.04 PM

Now, for our year column in the movies table, year is actually a MySQL keyword, so its better to rename it to something else.

Screen Shot 2014-12-28 at 4.39.21 PM

To remove a column, you’d just use DROP.

Screen Shot 2014-12-28 at 4.40.33 PM

Removing Databases

You used to be able to rename a db, but they removed it because it was dangerous. It’s better to back one up, then move that data into a new one.

Deleting a db deletes the schema and the data.  To delete a db, you’d write DROP DATABASE (or SCHEMA), an optional IF EXISTS, then the name of the db you want to delete.

Screen Shot 2014-12-28 at 4.46.07 PM

Joining Relational Data Between Tables in SQL


SQL_SAFE_UPDATES is there to make sure we’ve modeled our data in a safe way.

Screen Shot 2014-12-28 at 4.53.19 PM

So, we’d make a separate table with the different movie genres listed, each with an id, that we could then reference in another table.

Screen Shot 2014-12-28 at 4.54.22 PM

Keys and Auto-Incrementing Values

There are different kinds of keys.

Screen Shot 2014-12-28 at 4.55.55 PMScreen Shot 2014-12-28 at 4.56.25 PMScreen Shot 2014-12-28 at 4.56.45 PM

We’ll now use the treehouse db, and create a new table called genres, with the needed column definitions. We’ll add PRIMARY KEY for the id one, to specify it as such. Before that we’ll add AUTO_INCREMENT, which increases the value by one with each new row. The name is a unique key, so we’ll add UNIQUE, with an optional KEY. Since unique keys can be null, but we don’t want that, we’ll add NOT NULL, where specified by the documentation.

Screen Shot 2014-12-28 at 5.04.30 PM

Now, lets add a genre to our table. If we try adding that same one a second time, it will give us an error, due to that fact that that column is for unique keys.

Screen Shot 2014-12-28 at 5.06.58 PM

Screen Shot 2014-12-28 at 5.09.10 PM

Next we’ll add a id column to our movies table, which will auto increment and be a primary key. We’ll also make it the first column in our table by adding FIRST at the end.

Screen Shot 2014-12-28 at 5.12.20 PM Screen Shot 2014-12-28 at 5.12.12 PM

Now we’ll add an genre_id, which we don’t want to increment (since we’ll use the same one for multiple rows), nor be primary or the first column. It can be null so we’ll add NULL. We want a constraint so only certain values can be put into this column, in this case the ids that are listed in the genres table. To do that we write ADD CONSTRAINT, then FOREIGN KEY, then the column name to be affected by it in parentheses, then REFERENCES, then the table name, and its column in brackets. Note that even though I broke it onto different lines it’s still one statement.

Screen Shot 2014-12-28 at 5.21.19 PM

Screen Shot 2014-12-28 at 5.22.10 PM

Now we’ll add the genre id of sci fi to the aliens movies. In our genres table, sci fi is 1.

Screen Shot 2014-12-28 at 5.24.08 PM Screen Shot 2014-12-28 at 5.24.03 PM

Joining Tables and Aliasing

To join our two tables, we’ll do SELECT * FROM, then JOIN between their names, then ON for the condition where we set two columns equal to each other, in this case the foreign key on the movies table, genre_id, with the primary key on the genres table, id.

Screen Shot 2014-12-28 at 5.49.43 PM

The JOIN here is known as and can also be written as an INNER JOIN, which is the data between the two that overlaps in both. If you want to pull in data outside of the overlapping data, write LEFT OUTER instead.

Screen Shot 2014-12-28 at 5.51.56 PM

The left here means that we get all the info from the left join, the movies table, plus the inner join columns. If we wrote RIGHT we’d get this.

Screen Shot 2014-12-28 at 5.53.13 PM

Let’s say we just wanted to return the title from the movie table and the genre from the genre table. As we’ve seen, to select a column in a table you write table_name.column_name. So, we can replace the star with the needed columns.

Screen Shot 2014-12-28 at 5.55.54 PM

But for the name column, it’s not very clear that it’s the genre name. So, we can use aliasing, which lets us rename that. To do that, after the column name simply add AS, then the new name.

Screen Shot 2014-12-28 at 5.57.27 PM

Note that these alias’s can’t be queried on later in the string, you’d have to the table_name.column_name format.

Screen Shot 2014-12-28 at 6.00.02 PM

If the column name is unique, and only in one table, you can just write its name, but if it’s not and is in both tables, like id, it won’t work.

SQL Calculating, Aggregating and Other Functions

Numeric and Aggregate Functions

We have a new table for reviews, containing columns for id, movie_id (a foreign key) and a score. We’ll check out our table where movie_id = 1, which if we check our movies table we can see is the movie alien.

Screen Shot 2014-12-28 at 6.14.38 PM

To see how many reviews there are, use the COUNT function, which takes an argument, in this case * for all the columns.

Screen Shot 2014-12-28 at 6.15.58 PM

Once again, we can use an alias to make the column name more readable.

Screen Shot 2014-12-28 at 6.16.40 PM

To check for the minimum value, we can use MIN, and pass it the column we want to check, in this case score. MAX works the same way.

Screen Shot 2014-12-28 at 6.19.09 PM

To get the average we could use SUM() and divide it by the COUNT(), but it’s better to use the AVG() function.

Screen Shot 2014-12-28 at 6.22.12 PM

Keep in mind that at this time these values are not being stored anywhere.

Grouping, Joining and Cleaning Up

If we were to remove our WHERE clause, it would average for all movies. Even if we selected the column for, it would still only return one row.

Screen Shot 2014-12-28 at 6.32.11 PM

So, we can add GROUP BY at the end, then what we want to group the returned values by, in this case movie_id.

Screen Shot 2014-12-28 at 6.33.21 PM

But just returning the id doesn’t make much sense, so we’ll use a INNER JOIN, which will return the subset of info that is common between the two.

Screen Shot 2014-12-28 at 6.36.54 PM

But what if a movie doesn’t have any reviews yet? We can use a LEFT OUTER JOIN and see Tron has no reviews.

Screen Shot 2014-12-28 at 6.38.41 PM

We can use IFNULL, which takes two arguments, the thing to check for, and what to put if it’s null.

Screen Shot 2014-12-28 at 6.40.12 PM

To filter for results with an average above 3, use the keyword HAVING, then the condition. You use HAVING after grouping and aggregating, and since we used a GROUP BY, we’ll use that here. If we wanted to use a WHERE clause we’d have to do so before it the GROUP BY.

Screen Shot 2014-12-28 at 6.41.48 PM

Screen Shot 2014-12-28 at 6.43.51 PM

String Functions

Here we have our users table.

Screen Shot 2014-12-28 at 6.50.55 PM

Note that the email column has some upperspace characters.  We can use the LOWER function to lowercase all of them. UPPER makes thing uppercase.

Screen Shot 2014-12-28 at 6.52.34 PM

LENGTH() gives you the length of a string. Lets find people with a username less than 19 characters.

Screen Shot 2014-12-28 at 6.54.28 PM

CONCAT() lets you join strings together. If you want a space, make sure to add one.

Screen Shot 2014-12-28 at 6.57.10 PM

The SUBSTRING() method takes three arguments, the string to truncate, the the character to start from, and the one to end from. Here, we shorten the emails. Note that in SQL, they don’t start with 0, but with 1. We can also use CONCAT to add some dots to the end.

Screen Shot 2014-12-28 at 7.00.52 PM

There are lots of functions. Learn more about them in the documentation.

Securing and Maintaining a MySQL Database

Indexing Columns in MySQL

If you’re filtering over a certain column frequently, you should add an index to improve performance. It’s like having an address book for your data. Without one, the db would have to search through the whole column for a name, but with one it can make things much faster.

Screen Shot 2014-12-30 at 2.50.46 PMScreen Shot 2014-12-30 at 2.51.25 PM

Keep in mind though that having an index means that when you write to a db, it also has to write to an index.

Let’s look through our users table for people with the last name Chalkley. When we run the command, the db is going through each row and checking if the last_name column equals Chalkley.

Screen Shot 2014-12-30 at 2.55.40 PM

We can use the keyword EXPLAIN at the start of our statement to get some ideas on whats happening when we run this command. Under type, we see that it’s running against all rows, under rows we can see its running through all three rows, and under Extra we can see its using the WHERE clause.

Screen Shot 2014-12-30 at 2.58.24 PM

We’ll make an index for last name to improve this search. Start with CREATE INDEX, then the name you want to give it, then ON, then the name of the table, with the name of the column you want it on in that table in parentheses.

Screen Shot 2014-12-30 at 3.01.14 PM

Let’s look at our explained query again. We can see now that the type is ref, and the keys it’s looking on is the last_name_idx. For rows, it’s just going to one, and under Extra we can see that it’s using the index condition.

Screen Shot 2014-12-30 at 3.03.00 PM

We’ll now add a new user to the database with the same last name.

Screen Shot 2014-12-30 at 3.05.59 PM

If we explain our search again, we’ll see that it now brings up two rows, so Henry was added to the index.

Screen Shot 2014-12-30 at 3.07.32 PM

If we make it more specific and search for a first name as well, we can see that it still searches for 2 rows based on the index, but is also using the WHERE clause.

Screen Shot 2014-12-30 at 3.08.47 PM

Setting up Multiple Users

It’s good to think about what permissions you want to give users, to keep them from messing with your database in ways you don’t want them to. To make a user, start with the GRANT keyword, then the permissions you want to give them. The first user will only have read permissions, which we’d write as SELECT. Then we’d write ON, then the name of the db we’re referring to. Following the db name with a .* means it includes all the tables in the db.

Then, you write TO, then the name of the user, then @’%’, which means the user is at a particular host name address. So, a user with this username and the password we specify can connect. If this were for a certain website, you may want to instead specify your web server. Finally, you write IDENTIFIED BY, then the password in single quotes.

Screen Shot 2014-12-30 at 3.27.26 PM

To be able to connect as user1 though, you must first do FLUSH PRIVILEGES, which resets and reloads all the privileges of all the users for the database.

Screen Shot 2014-12-30 at 3.29.36 PM

Now, at this point you’re supposed to be able to create a new connection for that user, using their username and password, but this didn’t work for me at all and unsurprisingly treehouse provided no help.

Signed in as the user, you’ll find you can SELECT * FROM to view tables, but cannot edit them in any way. You’ll get an error if you try.

Screen Shot 2014-12-30 at 3.41.43 PM

To make a user with read and write permissions, you’d simply add more keywords – INSERT, UPDATE and DELETE. Make sure to flush the privileges when you’re done.

Screen Shot 2014-12-30 at 3.44.18 PM

If you create a connection for this user and use it, you’ll find you have access to all the DML CRUD operations. However, if you try to drop a table, a DDL command, it will be denied.

To create a user with DDL permissions, use the keywords ALTER, CREATE, and DROP.

Screen Shot 2014-12-30 at 3.49.55 PM

Backing Up and Restoring Databases

If you load up your connection and choose management on the left hand nav, you’ll see the Data Export option. You can choose what db you want to back up, and even which tables within it. You can then choose whether to export into separate files, or all into one. Choose your file path and make sure you use a .sql extension, and click Start Export.

Screen Shot 2014-12-30 at 3.59.23 PM

If there’s a discrepancy between versions, you may get an error. To fix this, go to Preferences > Admin > Path to MySQL dump tool > /usr/local/mysql/bin/mysqldump.

Screen Shot 2014-12-30 at 4.02.27 PM

So, keep in mind that the GUI interface is just running commands for you. If you typed it yourself in a command line, it would start with the path to the dump, then some parameters for the hostname, user and path to the db.

Once you have the file, you can open it with a text editor to view it.

Screen Shot 2014-12-30 at 4.06.33 PM

Notice it starts by creating the db, if it doesn’t already exist, then the next line is to USE it. Then it creates the tables, first by dropping them if there’s already one with that name, then creating one and inserting the values. If you wanted, you could modify this and rename the db to something like treehouse_movie_db_clone.

Screen Shot 2014-12-30 at 4.09.21 PM

To restore a db, select the Data Import/Restore option from the left nav > Import from Self-Contained file > choose path to file. Under Default Target Schema you can choose which one you’d like to use for the queries to be run in. However, this is only used if the dump file doesn’t contain its schema (our first two lines to create and use), and is otherwise ignored. So, it will actually be ignored here. Click Start Import > Ok.

Screen Shot 2014-12-30 at 4.13.05 PM

Now after refreshing our schemas, we see our clone. If we hadn’t changed the name, it would have overwritten our previous db.

Screen Shot 2014-12-30 at 4.14.55 PM

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s