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.
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.
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.
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:
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.
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();
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
->. 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.
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.
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.
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!
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
birthday. The piece of text after each of these provided column names refers to the data type that column should hold.
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
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:
text(for long blocks of text)
For a full list, see the MySQL Data Type documentation.
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;
To see a list of all the tables in the database we're currently connected to, we can use this command:
> SHOW 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.
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;
contactsrefers to the table we're updating.
favorite_coloris the name of the new column we're adding to it.
TEXTis the data type of that new column.
We can also remove a column from a table with this command:
> ALTER TABLE contacts DROP favorite_color;
contactsis the table we're updating.
favorite_coloris the column we're removing from it.
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;
contactsis the table we're updating.
idis the name of the new column we're adding.
serialis a data type referring to an auto-incrementing integer;
PRIMARY KEYtells MySQL that this column will contain the special primary key identifiers used to retrieve objects.
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');
contactsis 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.
VALUEStells 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
datetimes, but not around
ints. Also, remember that with SQL, we need to use single quotes - double quotes won't work.
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 INTO contacts (name, age, birthday) VALUES (‘Tanner’, 26, ‘1988-12-09’);
> SELECT LAST_INSERT_ID();
Now that we have data in our database, let's get it out! Reading data from a database is called querying.
Here's a query we can try:
> SELECT name FROM contacts;
This gives us a list of all the names in our
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
A WHERE clauses accepts the following operators:
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');
LIKE operator, the
% is a wildcard, meaning it can stand for any number of any characters.
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;
Now, let's delete a record:
> DELETE FROM contacts WHERE id = 1;
We're done playing around with this database now, so let's drop it.
> DROP DATABASE test_database;
Finally, to quit MySQL just run:
Also, we can always see a list of MySQL commands by typing the following into the MySQL shell:
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.
text(for long strings of text)
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: