SQL queries allow us to talk to the server to execute the functions of a database. Unlike Excel we don’t directly view the data in tables, nor edit the data stored in the database. In SQL everything happens via queries, which can be thought of as commands from the user to the server. The server receives the commands and executes them, perhaps making changes to the data stored or returning results to the user.
Even when we are using phpmyadmin to view data on the server we are actually executing queries and showing results. You may have noticed that when you click to the
Browse tab on phpmyadmin above the data table displayed is a grey box showing something like this:
SELECT * from objects
So phpmyadmin has executed a query for us, received the results, and wrapped html around them to show them to us. Similarly if we “sort” the table by a column (say the
name column in the objects table in the objects database) we see:
SELECT * FROM colors ORDER BY colors.name ASC
When we use the
Insert tab the server actually executes an
INSERT query. Lets say that we want to add a new pen to the objects database. We click to the
Insert tab and fill the boxes out (
When we click
Go the next screen at the top shows us the query that was run:
INSERT INTO colors (id, name) VALUES ('34', 'grey pen');
So even things that feel like directly interacting with the server (via phpmyadmin) are actually executing queries. We’re going to learn how to write these ourselves.
SQL queries have a specific syntax. The server, unfortunately, can only work when we write our queries as it is expecting them. Unlike people, who are skilled in understanding context and filling in gaps in understanding, computers are extremely literal. So we’ll all get error messages back from the server as we learn to write our queries. Stick with it :)
To make queries easier to read we are going to write the mysql keywords (special words) in capital letters. That makes it easier to distinguish between, for example, keywords, the names of tables and columns, and little pieces of data in queries.
We will write our queries in the Atom editor, into a file we save with the .mysql ending. That will enable Atom to apply syntax coloring, as in the boxes above, to help us distinguish even more.
At the start of the semester we discussed the four functions of databases, using the acronym
CRUD, each of the functions maps to a type of query in SQL:
C: Create. INSERT R: Read. SELECT U: Update. UPDATE D: Delete. DELETE
The four query types share much of their syntax, so we’ll spend most of our time on
SELECT queries before circling back to briefly talk about