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

Normalization

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 movie.id, 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

Advertisements

New terms from treehouse – Database Foundations Part 1 – Introduction to Data, Databases and SQL, Installing MySQL Server and MySQL Workbench, Reading Data from Databases with SQL, and Creating Tables and Manipulating Data with SQL

NOTE: If you’re looking for the instructions on installing/using the new version of MySQL Workbench on OS X, click here. 

Introduction to Data, Databases and SQL

Introduction to Data, Databases and SQL

A database is something that lets you store data, and then create, read, update and delete that data. It acts as the memory for a website. Ruby and other languages can abstract the finer details, but this course we’ll cover them as they are often the key to solving problems and speeding up performance. There are two main components to databases. The first is schema, which is the organization and relationships within the database. It is the different sections, and how they should store data. This is also known as modeling. A model is a plan/blueprint for building a database. This involves choosing what you’re going to store for each thing. If you were storing movies, you may choose the title and release year, and not include the director.

The second main component is data, whose formatting is described by the schema. There are different kinds of databases out there, but in this case we’ll be covering relational ones, which include MySQL, SQLite and Postgres, mainly MySQL. Databases contain collections of info called tables, which are spreadsheet like structures with rows (sometimes called tuples) and columns (which is a property of the entity you want to store). Depending on each database there is usually a naming convention for them.

Screen Shot 2014-12-23 at 2.19.33 PM

To get data in and out of the database we use Structured Query Language (SQL). It is written in keywords and phrases to make a sentence, which is sometimes known as a statement. Here’s an example. select is a keyword, and the * means every column in the table. From is the clause to say where it should be retrieved from, and movies is the table.

Screen Shot 2014-12-23 at 2.22.28 PM


Introduction to Data Types

Columns can have data types, which are like a type of formatting strictly enforced for that column. The three main kinds are strings, numeric and date/time types. There are two kinds of strings in MySQL. varchar contain short strings of characters, ideal for things like product names and movie titles. text are for longer strings like descriptions.

For numeric, there’s integers (whole numbers), fixed point (where the number of decimal places is fixed, like for currency), and float point (where the number of decimals can vary). How they are stored by the db (database) can vary though, if you put in 2.3 it may be stored as 2.2999.

For date and time types, which can contain a date, a time, or a combo of both, which is known as datetime. The types you choose are important, especially when it comes to sorting the columns later. Strings take up more space than integers, so it’d be silly to store a whole number in one.


Creating Tables

A basic SQL statement to create a table would look like this:

Screen Shot 2014-12-23 at 2.33.21 PM

The CREATE keyword tells the db what to do, and the TABLE keyword tells it what to create. Then, you put the table name (actors). Then, in the parentheses you have the column definitions, in this case we only have one. You put the column name, then the data type, and in this case we’re setting the character length with 50. To add more columns, simply separate them with commas.

Screen Shot 2014-12-23 at 2.35.31 PM


Inserting Data

Here’s a basic SQL statement for inserting some info into a table.

Screen Shot 2014-12-23 at 2.37.52 PM

INSERT INTO says you want to add info to a table. The VALUES keyword sets up the values, which appear in quotes. Make sure to enter your info here in the same order as your columns.


Installing MySQL Server and MySQL Workbench

Introduction to MySQL Server and MySQL Workbench

The client server model is when you use a GUI interface or programming language to interacting with a database on a server. For this lesson, the client will be the MySQL workbench, and the server will be the MySQL community server.


Installing MySQL Server and MySQL Workbench on OSX

NOTE: TREEHOUSE’S LESSON ON THIS IS EXTREMELY OUT OF DATE, FOLLOW THE DIRECTIONS BELOW ALONG WITH THE VIDEO AND FOLLOW MINE WHERE THEY DIFFER.

1. Go to MySQL.com > downloads > community > MySQL community server > download the file for your version of OSX. There’s two options, compressed TAR and DMG, select DMG. > No thanks, just start my download.

2. Download and Install it.

3. Download the MySQL Workbench GUI tool under MySQL.com > downloads > community > MySQL Workbench > download the one for OSX. Note that in this lesson he’s using an older version of this so some things may look/work differently.

4. Download and install it. Under System Pref > MySQL > Start MySQL Server

5. MySQL requires a special config file in a folder called ETC. Open Terminal > enter this: sudo cp /usr/local/mysql/support-files/my-default.cnf /etc/my.cnf

6. Open MySQL workbench. Click the little + next to MySQL Connection. For connection name use localhost, for connection method standard TCP/IP, and for hostname localhost. Port should be 3306 and username should be root. Click Configure Server Management in the lower left hand corner.

Screen Shot 2014-12-23 at 3.35.41 PM

7. Click Continue. It should now test your connection. If you’re getting an error, make sure that under System Preferences > MySQL you have the server running. Click Continue.

Screen Shot 2014-12-23 at 3.37.10 PM

8. For Operating System is should be MacOS X and for the MySQL Installation type it should be MacOS X(MySQL Package). Click Continue.

Screen Shot 2014-12-23 at 3.39.15 PM

9. Now it can test the connection, and if it passes should look like this. Click Continue, then Continue again.

Screen Shot 2014-12-23 at 3.39.36 PM

10. You can now start and stop the server by clicking on it’s panel in the main window.

Screen Shot 2014-12-23 at 3.41.27 PM

11. If you click Server Status on the left you can confirm its running.

Screen Shot 2014-12-23 at 3.43.07 PM


Running a Script and Using MySQL Workbench

He gave us a file to download. Open the connection then go to File > Open SQL Script > select it > Open. You should now see the scripts for the db. Click the lightning at the top to run the script. You’ll see a series of action outputs at the bottom, if there are green check marks it’s all good.

Screen Shot 2014-12-23 at 3.49.28 PM

On the left, you’ll now see a section called schemas. Right click > Refresh all and you should now see one called treehouse_movie_db. In there, open tables to see the names of each, each of which can be expanded to show the columns. So, this GUI lets us see a graphical representation of the db. Click on the treehouse_movie_db to select it, and it will turn bold.

Screen Shot 2014-12-23 at 3.52.21 PM

Close the movie_db import script tab, and in the query 1 tab type SELECT * FROM movies and you’ll see it returns the data. If you type movie you’ll get an error because that table doesn’t exist.

Screen Shot 2014-12-23 at 3.56.05 PM


Reading Data from Databases with SQL

Introduction to CRUD

CRUD stands for the four operations you’ll want to perform in the db – Create Read Update and Delete. Each has a SQL keyword associated with it. For Read, it’s SELECT.


Retrieving a Result Set

NOTE: the shortcut for execute is Command + Return. Before we had SELECT * FROM movies; for all movies. But, you can also write SELECT movies.title, movies.year FROM movies;.

Screen Shot 2014-12-23 at 4.14.38 PM

You can change the order of the columns returns by switching the column names. You can also write it without the movies before each column name as comma separated values. Note you don’t need a period here.

Screen Shot 2014-12-23 at 4.16.46 PM

If you just want data from only one column, write just that columns name.

Screen Shot 2014-12-23 at 4.17.47 PM


Querying Tables

clause lets you filter a result set. The where clause makes it so it only returns the data matching that condition. You right it at the end. Here, we test for movies where the year column equals 1999. Note that = is not an assignment operator here, but rather a comparison one.

Screen Shot 2014-12-23 at 4.21.29 PM

Use != to find the ones that aren’t from 1999.

Screen Shot 2014-12-23 at 4.22.36 PM

You can also use >, <, >=, <= as well. You can combine conditions using AND and OR. Here, we look for movies from 1999 with the title the matrix.

Screen Shot 2014-12-23 at 4.25.00 PM

To search between values, write the column name, then BETWEEN, then the number to start, the keyword AND, and then the ending number.

Screen Shot 2014-12-23 at 4.27.16 PM

You can search a string using the keyword LIKE with a combo of wildcards. If we were to search for “godfather” we’d get no results since the movie starts with “the”. The wildcard is a percent sign. Placing that before the g makes it wildcard, so anything that starts with something and then has godfather in it will come up. Note that LIKE is case insensitive.

Screen Shot 2014-12-23 at 4.30.04 PM

If we place a wildcard at the end of it as well, then it will bring up any godfather matches that also have something after them.

Screen Shot 2014-12-23 at 4.31.25 PM


Ordering the Result Set

Use the keyword combo ORDER BY to order results. You add it at the end, then what you want to order things by. See that is gives you an ascending order, and that null values come at the top.

Screen Shot 2014-12-23 at 4.35.49 PM

To have them descend, ass the keyword DESC at the end. If you needed to explicitly ascend them, add the keyword ASC to the end.

Screen Shot 2014-12-23 at 4.37.07 PM

You can chain ordering together by comma separating them. The first one listed is what the table sorts by, then the second sorts within the first column specified. So, here we have the table sorting by date, then for each year the titles sorting descending.

Screen Shot 2014-12-23 at 4.39.34 PM


Limiting the Result Set

If you don’t need all the results from the table, you can use the LIMIT keyword followed by the number of elements you want returned.

Screen Shot 2014-12-23 at 4.42.47 PM

To still pull in 10 but start later in your database, use OFFSET after that and a number, which is the number of rows you want to offset by.

Screen Shot 2014-12-23 at 4.43.49 PM

If you type OFFSET 0, it will start from the first row, so like an array the database starts counting the rows at 0. So, if you wanted to start at the 11th row, you’d offset by ten. You can also write this with just LIMIT, then two comma separated numbers, the first being the offset, and the second being the limit number.

Screen Shot 2014-12-23 at 4.46.22 PM

Note that if you don’t specify a limit, it will auto specify one for you at 0, 1000. You’ll find that some clients add one. If you were using a language like php or a command line tool though, these limits would not be there.

Screen Shot 2014-12-23 at 4.47.11 PM


Dealing with NULL

NULL is a value used to indicate data isn’t present in the database. To find the movies where the year is null, you’d simply write WHERE year IS null; Note that you can’t use = here, you need to use the IS keyword.

Screen Shot 2014-12-23 at 4.55.02 PM

We can combine this with our ORDER BY keyword to sort our movies and not include ones with a NULL year. In this case we’ll use IS NOT.

Screen Shot 2014-12-23 at 4.56.05 PM


Manipulating Data with SQL

Manipulating Schema and Data

The SQL syntax is split into two parts: DDL (Data Definition Language), which has to do with the keywords that create and modify tables and databases, aka schema, and DML (Data Manipulation Language), which deals with the CRUD operations in a database, aka the data.


Creating a Database

Think of a database as a container that keeps groups of tables separate from other groups of tables. To make a new one, in your workbench click the little icon that looks like a cylinder cut into three pieces.

Screen Shot 2014-12-28 at 2.53.58 PM

This makes a new schema, which is a database. Give it a name, and ignore default collection for now > Apply. Then, a screen will pop up showing you the keywords you’d use to do this by typing > apply > close > you’ll now see it on the left under schemas.

Screen Shot 2014-12-28 at 2.55.26 PM

Screen Shot 2014-12-28 at 2.56.21 PM

To select our new db from the schema list on the left, rather than clicking on it we’ll type USE movie_db_1;

Screen Shot 2014-12-28 at 2.59.04 PM

Note that if we try to make a new db with the same name we’ll get an error. The documentation on creating databases gives us more info. First it has the keyword needed, CREATE, then in curly braces it has the database and schema separated by a pipe, which means or. The words in the square brackets are optional.

Screen Shot 2014-12-28 at 3.02.34 PM

If we add the IF NOT EXISTS to our statement, we now get a warning rather than an error. This is good because when a script errors, it stops running, but with a warning it will continue on. Whether or not you’d want it to stop or not depends on the situation.

Screen Shot 2014-12-28 at 3.03.36 PM

Note that in the documentation database and schema are interchangeable, so we can use that to make a db as well. Make sure to right click > refresh your schema list after to see it there.

Screen Shot 2014-12-28 at 3.06.53 PM

You have the option to set your character set when making your db. To see the options, use the GUI to add a new schema, and select one from Default Collection. UTF-8 is the standard for the www. When creating it, it shows the nomenclature, which is DEFAULT CHARACTER SET utf-8;

Screen Shot 2014-12-28 at 3.09.38 PM

Screen Shot 2014-12-28 at 3.10.44 PM

However, the documentation says the default is optional, and that we can add an equals sign. This isn’t necessary, but shows we can modify it.

Screen Shot 2014-12-28 at 3.11.59 PM


Creating a Table

Here’s how we’d make some tables. CREATE TABLE, then the name, then the columns, commas separated in parentheses.

Screen Shot 2014-12-28 at 3.14.30 PM

The documentation on creating tables shows us how to do that. We have the option to make a temporary table, which is visible only in the current session, and is dropped automatically when closed. You can add IF NOT EXISTS like before. Then, you can create definitions, which would be the column name and its definition, which is its data type. You see that for the data type you have the option between not null, which means there has to be something set for it, or null, which means it can be empty. This would be good for our actor name, so we’ll add that in, and run the command to make the table.

Screen Shot 2014-12-28 at 3.22.24 PM

Now we’ll create one for movies. We always want the title, and don’t always need the year, so we’ll add not null and null respectively. Note that if we didn’t specify not null or null, it would default to null, so we don’t need to write it for year.

Screen Shot 2014-12-28 at 3.25.51 PM

Another way to make a table is to add table option, in this case ENGINE, which is a specific way to store data in the db. If we open a new tab, and type SHOW ENGINES, it will show us the engines available. InnoDB is the default and the one you’ll like want to use, but to specify one, simply add ENGINE then the name of the one you want at the end.

Screen Shot 2014-12-28 at 3.30.20 PM


Inserting, Updating and Deleting Rows

So all of that was DDL, this will cover the DML, aka the CRUD stuff. Here’s a table of the keywords you need for each part.

Screen Shot 2014-12-28 at 3.33.28 PM

Here we’ll insert a movie. Start with INSERT INTO, then the table you want to use, then VALUES, then in parentheses the values for the corresponding tables.

Screen Shot 2014-12-28 at 3.36.12 PM

If you didn’t know the ordering of the columns, you could list the column names between the table name and VALUES, and the values would correspond to how you wrote them.

Screen Shot 2014-12-28 at 3.37.42 PM

Another way to write this is to insert multiple rows at once, by adding a section set of values in a second set of parentheses.

Screen Shot 2014-12-28 at 3.39.08 PM

You can also use the SET keyword, then the column name equal to the value, separated by commas for each.

Screen Shot 2014-12-28 at 3.40.39 PM

If we SELECT * FROM movies, we can see the rows we’ve created.

Screen Shot 2014-12-28 at 3.41.17 PM

To update, use the UPDATE keyword, then with SET and a clause to only do this for movies where the title equals Avatar 2 (if we didn’t do this it would set it for all movies). Note that for this to work you have to turn off safe updates under Preferences > SQL Editor > uncheck Safe Updates, or you can write SET SQL_SAFE_UPDATES = 0; in a new tab.

Screen Shot 2014-12-28 at 3.45.17 PM

To update multiple values, simply comma separate them.

Screen Shot 2014-12-28 at 3.48.18 PM

To delete, us the DELETE FROM keywords, and a WHERE clause to ensure you don’t delete everything in the table.

Screen Shot 2014-12-28 at 3.50.07 PM