This is a crash course in SQL. This assumes you've already set up your database and have access to it. If you have everything set up, log into it and go to a sql window. We will be hand typing this code, today. Today we will learn the basics enough to get you started with development or database administration. We will discuss how to create tables, search for information in those tables, join tables, perform easy calculations, and use real world examples to get you started in mySQL. Whether you're using a web-based sql management app like phpMyAdmin or one on your computer like SequelPro, you'll want to go to the area where you manually type in SQL.
Summary of what this tutorial covers
- Creating tables
- Inserting data into those tables
- Querying data
- Updating data
- Deleting data
You've been hired at AwesomeCo to create a database to manage all employees, their position, their yearly salaries, and their vacation days. Your boss has given you a spreadsheet with the following information:
|Employee Name||Position||Salary||Vacation Days Allowed||Vacation Days Taken|
Step 1: Creating TablesFirst, you need to create some tables to manage all of that information. You could create one table with all of that information, and have it resemble the spreadsheet, but if you notice, position is replecated. Knowing what we know about companies, we know that this company will grow and have more employees and will have more and more duplicates. So what we can do is keep that in it's own table. Let's start with that one, let's create a table that stores all of the possible positions:
CREATE TABLE `positions` ( `position_id` INT, `position_desc` VARCHAR(20) );
So what's going on here? Let's break this command up:
- CREATE TABLE - This part tells the database what we want to do. We're basically telling it that we want to create a table.
- `positions` - this is the name of the table. We told it we wanted to create a table, now we're telling it what we want to call that table.
- The part in parentheses - This is a list of fields (or columns), separated by the comma that will go in the database. We first give it the name, then tell it what type, then give it more attributes, such as making it automatically increment or making it a primary key.
- `position_id` - Here, we're telling it that the column should be named "position_id," and that it should be an integer.
- `position_desc` VARCHAR(20) - Here, we are telling it that the field will be called "position_desc" and that it will be a varchar, which is basically characters. The number behind "varchar" in the parentheses is the maximum length of the field.
And that's it! We have our first table. Now, let's add a table for employees. This one will be a bit more complex:
CREATE TABLE `employees` ( `employee_id` INT AUTO_INCREMENT PRIMARY KEY, `name` VARCHAR(150), `position_id` INT, `salary` DECIMAL(10,2), `vacation_allowed` INT, `vacation_taken` INT );
So we already know what most of this query does, but if you look, there's a lot more going on near "employee_id." We know that INT means it's an integer, but what about the two new things after it?
- AUTO_INCREMENT - This creates a field that will automatically increment the number each time you add data. We don't control the content of this field, mySQL does.
- PRIMARY KEY - This makes this field a unique identifier. This field will never be duplicated in any other row.
Also, you may have noticed that "position_id" is an INT instead of varchar. That is because we're going to store the ID of the position, and not the full name of the position for each employee. This is done to save space in the database. It's why we have two tables instead of one.
Step 2: Inserting Data
So, now it's time to put some data in those tables. Let's start with positions. Going back to that spreadsheet your boss gave you, we can see that there are three positions that we need to remember. Even though there are five records, two of the positions are duplicated, we only need that in the database once.
INSERT INTO `positions` (`position_id`,`position_desc`) VALUES ('1','Administrator'), ('2','Sales'), ('3','Developer')
Let's break this one up, too:
- INSERT INTO `positions` - This is just us telling the database that we want to insert some data, and we want to put it into the "positions" table.
- (`position_id`,`position_desc`) - This is just a list of which fields we'll be inserting into. You won't always want to insert data into every field. Sometimes you may be storing optional data that you won't be inserting at this point.
- VALUES ('1','Administrator'),... - This is the actual data. Notice that we gave each position an id of 1,2, or 3. We did this manually for the sake of learning. If we had set this field up as AUTO_INCREMENT, we wouldn't have to do this.
Now for the employees table.
INSERT INTO `employees` (`name`,`position_id`,`salary`,`vacation_allowed`,`vacation_taken`) VALUES ('Daenerys Targaryen','1','95000','15','7'), ('Petyr Baelish','2','75000','15','1'), ('Arya Stark','2','70000','10','2'), ('Jon Snow','3','85000','15','5'), ('Gregor Clegane','3','65000','10','4')
You'll notice that here, we didn't have an insert every field, employee_id was left off. This is because it is an AUTO_INCREMENT field, and a number was automatically inserted for each one.
Step 3: Querying Data
Now we need to see our data! Querying the table is easy. Let's start with a basic query:
SELECT * FROM `employees`
Let's put those people in alphabetical order:
SELECT * FROM `employees` ORDER BY `name`
You can reverse that order by adding "DESC" to the sort order (ORDER BY `name` DESC).
This query should give you a list of everything in the employees table. But what if you don't want to see everything? What if you only want to see the data for someone named "Arya Stark?" Let's put some filters on there:
SELECT * FROM `employees` WHERE `name` = 'Arya Stark'
This shows us only the information for Arya Stark. But it shows all of the information, what if we only want to see her name and salary?
SELECT `name`,`salary` FROM `employees` WHERE `name` = 'Arya Stark'
We need to join that table to the table that gives the information about the positions:
SELECT `name`,`salary`,`position_desc` FROM `employees`,`positions` WHERE `employees`.`position_id` = `positions`.`position_id` AND `name` = 'Arya Stark'
So what's going on here, is that we added the field to the select part, the table to the list of tables, and in the WHERE section, we told it that the "position_id" field in the employees table is the same as the "position_id" field in the positions table. That's how it knows to connect those two pieces of data.
Now, lets say the boss needs some information from you out of your database. With what we know, now, we can give that information. Here are some examples:"I need a list of everyone who has taken more than three days off this year"
SELECT `name`,`vacation_taken` FROM `employees` WHERE `vacation_taken` > 3 ORDER BY `name`"I need a list of all salesmen and their salaries"
SELECT `name`,`salary` FROM `employees` WHERE `position_id`= 2 ORDER BY `name`"I need a list of everyone who makes $75,000 or more and their positions"
SELECT `name`,`salary`,`position_desc` FROM `employees`,`positions` WHERE `employees`.`position_id` = `positions`.`position_id` AND `salary` >= 75000
There are even calculations you can do with the data. Try some of these"What is the average salary for the company?"
SELECT AVG(`salary`) FROM `employees`"What is the total number of vacation days used so far?"
SELECT SUM(`vacation_taken`) FROM `employees`"Who is the highest paid employee in the company?"
SELECT `name`,MAX(`salary`) FROM `employees`
And you can limit the number of results, as well, using LIMIT.What three people have used the most amount of vacation?
SELECT `name`,`vacation_taken` FROM `employees` ORDER BY `vacation_taken` DESC LIMIT 3
And those are just some examples, there are many more calculations you can do.
Step 4: Updating Data
Updating data is easy. You just need to know the ID and which fields you want to update. Let's say someone hits their 5 year anniversary and now gets more vacation days:
UPDATE `employees` SET `vacation_allowed` = 15 WHERE `employee_id` = 5
That just changed Sandor's allowed vacations from 10 to 15. Check it by doing a SELECT statement right now if you want. You can also do calculations in updates, as well. Let's say Arya just took another day off:
UPDATE `employees` SET `vacation_taken` = `vacation_taken` + 1 WHERE `id` = 3
Step 4: Deleting Data
Now what happens when someone quits? We need to remove them from the database. To delete data, do it like this:
DELETE FROM `employees` WHERE `id` = 3