Lesson Weekend

As a temporary work around, use the following connection command: '/Applications/MAMP/Library/bin/mysql --host=localhost -uroot -proot` if the mysql command is not working.

Now that we have a basic understanding of databases, let's start experimenting with SQL commands in the command line. The database management system that we will be using is MySQL.

Connecting to MySQL from the Command Line

At Epicodus

To launch MySQL servers:

  • Launch the MAMP application installed in the previous lesson.

  • Select the Start Servers option.

  • Access MySQL executing the command mysql -uroot -proot in the terminal.

At Home

To log into MySQL via the terminal on your personal machine, you may need to enter a different command. In most cases the command mysql -uroot -proot should work.

Note: Windows users need to include MySQL in the System Environment Path Variable. By default, the string is C:\MAMP\bin\mysql\bin. This may be different if you choose a different installation location for MAMP. If you're unsure how to edit Environment Variables on your machine, refer back to Windows MAMP installation instructions in the previous lesson.

Working with the Database Prompt

After following instructions above, we are now logged into our local MySQL server with the username root and password root. We should see a prompt like this:

mysql>

The MySQL shell ends its prompt with the > symbol. As such, MySQL shell commands in our curriculum will be preceded by > too. This is simply to depict that commands are meant to run in MySQL shell. You do not need to literally type the > before your own command.

Navigating to a Database

MySQL can manage many different databases at once. To make changes to a particular database we must navigate into that database from the MySQL shell, similar to the manner we navigate between project directories in the terminal.

We can determine which database we are connected to with this command:

> SELECT DATABASE();

Creating a Database

We should see that the current database is NULL. This is just because we haven’t created any databases yet. Let's change that! We can tell MySQL to make a new database with this command:

> CREATE DATABASE test_database;

As you can see, many SQL commands are quite straightforward. The command to create a database, as seen above, is literally CREATE DATABASE accompanied by the name of the new database.

Note that SQL commands aren't actually case sensitive, but it's common practice to type them in ALL CAPS, as seen here. Also, all SQL statements must always end with a semicolon.

If we press Enter after a command like the one above, and nothing happens, check to see if the prompt has changed from MySQL> to ->. If so, that means we forgot a semicolon at the end of our statement. We can just type one and press Enter again to resolve the issue, and execute the SQL command.

Listing all Databases

Let's list out all of the databases in our MySQL server:

> SHOW DATABASES;

Among other pre-existing databases that come with MySQL by default, we'll see our new database called test_database in this list.

Important Note

In the pre-existing databases listed with the command above, we'll see one called information_schema. This is a database that holds metadata for all our other databases. MySQL requires it. Don't ever, ever, ever delete it.

In fact, it's a good rule of thumb to never alter a database you didn't create. If it came with a tool by default, that tool likely requires it to run. Altering or deleting the database will only break it.

Connecting to a Database

To create tables and columns in our database, we need to connect to it:

> USE test_database;

Now if we run the following command again...

> SELECT DATABASE();

...we'll see that we're connected to the test_database we just created. Great!

Creating Tables in a Database

Now let's create a table in our database:

> CREATE TABLE contacts (name VARCHAR (255), age INT, birthday DATETIME);

This creates a table called contacts with columns for name, age, and birthday. The piece of text after each of these provided column names refers to the data type that column should hold.

Data Types

Just like object properties and variables in C#, SQL columns also have specified data types. Some of them just differ from data types used in C#.

  • In the command above, we give name the data type varchar. This is a varying number of characters (essentially the SQL equivalent of a string).

  • age is an int. Similar to ints in C#, this just refers to a number value.

  • birthday is a datetime, which includes a date and time.

Some data types have restrictions and requirements. For instance, varchar requires a maximum length, which we've set to 255 characters. 255 is a good default length for varchar data types.

SQL has many data types, but here are the most common ones you'll use:

  • int
  • float
  • varchar
  • text (for long blocks of text)
  • timestamp
  • datetime
  • boolean.

For a full list, see the MySQL Data Type documentation.

Listing Columns

After creating our new columns, let's check them out. To list columns in a specific table that resides in the same database we're currently connected to, we can run the following:

> DESCRIBE contacts;

Listing Tables

To see a list of all the tables in the database we're currently connected to, we can use this command:

> SHOW TABLES;

Deleting Tables

If we mess something up and need to delete a table, we can do so like this:

> DROP TABLE table_name;

table_name is the name of the table we'd like to delete. But again never delete a database you didn't personally create.

Adding Columns

Sometimes we'll want to add a new column to a database table we've already created. We can do that at any time with this command:

> ALTER TABLE contacts ADD favorite_color TEXT;
  • contacts refers to the table we're updating.
  • favorite_color is the name of the new column we're adding to it.
  • TEXT is the data type of that new column.

Dropping Columns

We can also remove a column from a table with this command:

> ALTER TABLE contacts DROP favorite_color;
  • contacts is the table we're updating.
  • favorite_color is the column we're removing from it.

Adding Primary Keys

It's standard practice to include a unique, automatically-incrementing ID number for each record in our database. (Like the id column we saw on the sample databases in the Introduction to Databases lesson.

To do this, we use the same command to add a new column, but tell MySQL that this column is a special primary key, like this:

> ALTER TABLE contacts ADD id serial PRIMARY KEY;
  • contacts is the table we're updating.
  • id is the name of the new column we're adding.
  • serial is a data type referring to an auto-incrementing integer;
  • and PRIMARY KEY tells MySQL that this column will contain the special primary key identifiers used to retrieve objects.

Inserting Data

Now that we have tables with columns, let's put some data in them. We can manually add new rows into our database with commands like this:

> INSERT INTO contacts (name, age, birthday) VALUES ('Wes', 43, '1969-05-01');
  • contacts is the table we're adding an entry to.
  • (name, age, birthday) tells MySQL which columns on the table we are providing information for in this command.
  • VALUES tells MySQL that the next set of data is the values that match the columns we just listed.
  • ('Wes', 43, '1969-05-01') is the data we're adding into this new entry;

Notice how we need to put quotes around varchars and datetimes, but not around ints. Also, remember that with SQL, we need to use single quotes - double quotes won't work.

Retrieving IDs

We'll often want MySQL to tell us the special primary key id it has assigned to an entry we just added to a table, so we know how to later find that row. Here's how to find the ID from our most recent INSERT statement:

> INSERT INTO contacts (name, age, birthday) VALUES (‘Tanner’, 26, ‘1988-12-09’);
> SELECT LAST_INSERT_ID();

Querying

Now that we have data in our database, let's get it out! Reading data from a database is called querying.

SELECT

Here's a query we can try:

> SELECT name FROM contacts;

This gives us a list of all the names in our contacts table.

We can select multiple columns from a table, too:

> SELECT name, birthday FROM contacts;

If we want to select all columns there's even a shortcut:

> SELECT * FROM contacts;

WE can limit our SELECTs to only return rows that match certain criteria:

> SELECT * FROM contacts WHERE age >= 18;

This gives us all of the adults in contacts.

WHERE

A WHERE clauses accepts the following operators: =, !=, >, <, >=, <=, BETWEEN, LIKE, and IN. You can also prepend any operator with NOT. Here are examples of the last couple operators:

> SELECT * FROM contacts WHERE age BETWEEN 13 AND 17;  
> SELECT * FROM contacts WHERE name BETWEEN 'Judith' AND 'Wilma';
> SELECT * FROM contacts WHERE name LIKE 'We%';
> SELECT * FROM contacts WHERE birthday IN ('1969-01-01', '1999-01-01');

For the LIKE operator, the % is a wildcard, meaning it can stand for any number of any characters.

Updating Records

What if we need to change data in our database? There are a lot of fancy ways to do this, but let's focus on the simplest and most common: select a record by its primary key, and update its data:

> UPDATE contacts SET name = 'Wes Anderson' WHERE id = 1;

Deleting Records

Now, let's delete a record:

> DELETE FROM contacts WHERE id = 1;

Dropping Databases

We're done playing around with this database now, so let's drop it.

> DROP DATABASE test_database;

Quitting MySQL

Finally, to quit MySQL just run:

> \q

More Commands

Also, we can always see a list of MySQL commands by typing the following into the MySQL shell:

 \?

Common SQL Commands


CREATE DATABASE database_name; # From the $USER database.
CREATE TABLE table_name (id serial PRIMARY KEY, some_column varchar, another_column int, yet_another_column timestamp);
ALTER TABLE table_name ADD column_name boolean;
ALTER TABLE table_name DROP column_name;
INSERT INTO contacts (name, age, birthday) VALUES ('Wes', 43, '1969-05-01') RETURNING id;
SELECT * FROM table_name WHERE age >= 18;
SELECT * FROM contacts WHERE NOT age >= 18;
UPDATE contacts SET name = 'Wes Anderson' WHERE id = 1;
DELETE FROM contacts WHERE id = 1;
DROP TABLE table_name; # From the database that holds the table.
DROP DATABASE test_database; # From the $USER database.

Common Column Types


  • int
  • float
  • varchar
  • text (for long strings of text)
  • timestamp
  • boolean

Operators for WHERE clauses


  • =
  • !=
  • >
  • <
  • >=
  • <=
  • BETWEEN
  • LIKE
  • IN

Review

Remember, browsers request pages by making a request for a URL using a specific HTTP method, such as GET, POST, PATCH and DELETE. Each method is a request to perform an operation on a resource.

In modern web applications, we typically use the same URL and different HTTP methods to convey what we're trying to do. Using the same URL patterns with different HTTP methods or actions is part of a widely-accepted approach for designing web applications called REST, or REpresentational State Transfer. We've been creating RESTful routes in our applications all along.

For reference, here's a table listing all of the CRUD functionalities, the corresponding SQL keywords to execute each functionality in the database, and the HTTP request often associated with each action:

crud-sql-http