SQL is the language used to manipulate relational databases. In this tutorial, we are laying down the foundations of working with SQL using one of its implementations MYSQL. MYSQL is an open source database system used by millions of systems out there. While working with your database, you usually want to do basic stuff like creating or dropping(deleting) databases, adding tables to the database, dropping tables form the database, or just routine CRUD operations on your tables.
You need a mysql environment to be able to run your SQL queries. There are tons of guides on installing mysql on your favorite system online. I ll assume you have one handy. Next you need some kind of mysql client in which to type your queries. I’ll be using the command line mysql client and a built in client inside phpstorm on windows in this tutorial. Feel free to use whatever works for you.
Creating a database
We want to create a database named “students”:
CREATE DATABASE students;
The command above creates your database. Make sure your database is created by issuing the command
show DATABASES ;
you should see the database “students” in the returned list.
To be able to operate on the database, you need to select it as the current database. The command for that is
It is just USE followed by the name of the database.
Create your table with the command :
CREATE TABLE basic_info ( id INT NOT NULL AUTO_INCREMENT , firstName VARCHAR(255), lastName VARCHAR(255), email VARCHAR(255), address VARCHAR(255), province VARCHAR(255), age INT, PRIMARY KEY (id) );
This table of ours is used to store basic information about students. It has 7 fields namely, firstName, lastName, email, address, province and age. There is a command to see a more friendly representation of the table:
This shows the table in the form below:
We can now clearly see the field names, types for these fields, whether or not the field can be null, whether of not it is a primary key, the default value and if the field supports auto_increment.
Insert data into the table
The command is :
INSERT INTO basic_info( firstName, lastName, email, address, province, age) VALUES ('Mpyisi','Yohani','firstname.lastname@example.org','Kigali 000 St #46','Kigali City','21');
We tried to split the command into separate lines for clarity but you can just put that into one big line and it’ll work. If you run this into your mysql client, your first table entry will be created. Notice that we did not insert the id field. That’s because it is set to auto_increment and the system takes care of putting it in place for us. To really see that the data was inserted, issue the command
SELECT * FROM basic_info;
This basically selects everything from your table .You should see something like shown below
You can insert multiple entries at once like this :
INSERT INTO basic_info( firstName, lastName, email, address, province, age) VALUES ('Mpyisi','Yohani','email@example.com','Kigali 000 St #46','Kigali City','21'), ('Giti','Samweli','firstname.lastname@example.org','Butaro 4634 St #256','North Province','30'), ('Mpazi','Jackson','email@example.com','Huye 45 St #368','South Province','27'), ('Mashimo','Diane','firstname.lastname@example.org','Nyaruguru 4545 St #988','South Province','23'), ('Ndiyo','Dionisiyo','email@example.com','Kigali 232 St #23','Kigali City','21'), ('Sita','Makasi','firstname.lastname@example.org','Rwamagana 36 St #46','East Province','25'), ('Zuba','Fiona','email@example.com','Kigali 3636 St #384','Kigali City','21'), ('Riba','Jean Luc','firstname.lastname@example.org','Kigali 34534 St #34','Kigali City','20');
and the result would be something like shown below:
Updating the table
Let say that our student with id=5 wants to change her email from “dianama@blikoon” to “email@example.com” for some reason. The command to do that is :
UPDATE basic_info SET email = "firstname.lastname@example.org" WHERE id = 5;
When you run it and show out table again, you see something like this :
If you wanted to update multiple fields at once , you could also do that. For example, notice that the students with id=1 and id = 2 are completely the same. To have a different student at id = 2 we can issue the command below :
UPDATE basic_info SET firstName = "Girinka", lastName="Tunga",email = "email@example.com", address = "Rusizi 46464St #635",province ="West Province",age= "22" WHERE id = 2;
and now our table would look as shown below :
Now lets say that the student with id = 9 just graduated and is no longer in the list of our current students. We need to delete him from our basic_info table. We do this with the query below :
DELETE FROM basic_info WHERE id = 9;
Run the command and you should see the id = 9 student deleted.
Altering the table
Altering the table is basically changing something about the structure of the table, like adding or removing columns or event changing the data type of some column. First we see how to add a new column.
Adding new columns
Let say that we want to add a column to store the sex of the student. This should be a BOOLEAN type with 0 (ZERO) representing MALE and 1(ONE) representing FEMALE. The command to do that is as shown below :
ALTER TABLE basic_info ADD sex BOOL;
Show your table and it should look something like :
You can see that our column is added. One problem though, our values are all null. This is because we created our student records before the column was added. We could have specified a default value for the column but I wanted you to see this problem. For now just run the update command
UPDATE basic_info SET sex = "1" WHERE id = 8;
to update each row with the sex you want to have. You should have a table looking something similar to :
Changing Field Data Type
Sometimes you want to modify the data type for your fields for some reason. For example, take a look at the structure of our table reproduced below :
The age is being stored in an Integer with a maximum width of 11 digits! 3 would have been enough here. To change that, issue the query below :
ALTER TABLE basic_info MODIFY age INT(3);
and show your table to see the changes
You can see that the age is now stored in a tree digit max integer. Ah ha! See that our sex field is being stored as a tinyint(1) ? This sometimes happens as the system is helping you store your data more efficiently and we’ ll just leave it like this. Don’t be alarmed that you did something wrong here.
When it happens that you want to delete a column from your table, let say that we don’t care about the province field anymore and we want to get rid of it. We do it with the query command below :
ALTER TABLE basic_info DROP COLUMN province;
and our table structure now looks like :
Notice that the province field is completely gone. If you even show the data, you see that the data we had for province field is gone
This confirms that the command completely wipes out metadata about the field and actual data.
We touched briefly on the select query command but did not really dig a bit more. We have been using the command :
SELECT * FROM basic_info;
to show our tables like the one below showing the current state of our table
The command just selects everything from the table and returns to you into a nicely formatted table. The ‘*’ character means everything.
Select with WHERE clause
To have more fine tuned data returned, you need to filter you queries using the where clause.For example if we wanted to just select the users with age greater that 25 I would issue the command :
SELECT * FROM basic_info WHERE age > 25;
and I get the result :
You can also specify which fields are returned to you. For example let say you want to have the lastName and firstName of your students with ages greater than 25
SELECT firstName,lastName FROM basic_info WHERE age > 25;
We get the result shown below :
You can also select as specific student. Let say we want all the information about the student with the id = 5. Run the command
SELECT * FROM basic_info WHERE id = 1;
and one row with the student should be returned.
Order you results
Sometimes you want to have control over the order in which your results are returned in. If you wanted your students to be returned in ascending order by age for example, you would issue the command :
SELECT * FROM basic_info ORDER BY age;
and you would get your results ordered;
If you wanted to have the results in the descending order, just put a DESC at the end
SELECT * FROM basic_info ORDER BY age DESC;
and your results are
Distinct values from results
Sometimes you don’t want to have duplicates in your results. For example , let say we want to know the age ranges for our students but aren’t really interested in knowing who is how much old. We would issue the command :
SELECT DISTINCT age FROM basic_info;
and we get just the ages :
SQL has different types of operators, we have Arithmetic operators, Logical Operators and Comparison Operators.
I have listed then mixed here and I will show some examples of how you would use them.
If you wanted to select the students with ages between(inclusive) 22 and 25 for example , you would do so like
SELECT * FROM basic_info WHERE age BETWEEN 22 AND 25;
and the results are
Like is usually used in search queries where you want to match for a particular string. For example in our table, if we wanted to retrieve who live in Kigali, we would filter the address field for the Kigali string as shown :
SELECT * FROM basic_info WHERE address LIKE 'Kigali%';
The result is shown below :
We have effectively selected all the students whose address start with Kigali and end then after that really anything. If one student had something like “Kigali is great” in her address, this filter would pick it up. You should keep that in mind and do more checks in your applications. Note that you can also do NOT LIKE as shown below
SELECT * FROM basic_info WHERE address NOT LIKE 'Kigali%';
and get students whose address doesn’t start with Kigali.
“IN” help you filter out fields that fulfill a condition defined by a set. An example would make more sense. Let say we want to select the students who are “21”, “23” or “27”, we can easily do that with the query command :
SELECT * FROM basic_info where age IN ('21','23','27');
and we get the result :
Indexes are added to columns to speed up searches and make them more efficient. You only put then on columns that are searched against more often and you shouldn’t put them just everywhere.
In our basic_info table, let say that we will be frequently searching using emails and we want an index on the email field. Add an index with the query :
CREATE INDEX EIndex ON basic_info(email);
Show your table,
Nothing seemingly changes except for the field header now having a greenish color to the right of the icon. This my specific SQL client way to tell me that field in question has an index attached to it. From now on, searches you make against this field will be efficient.
NOTE : Notice that our index has a name EIndex, be mindful how you name your indexes as index name collision can cause obscure errors that are hard to debug.
Removing indexes is easy, just run a query like
DROP INDEX EIndex on basic_info;
and your index is gone. We use the name of the index to specify which index to drop. Keep good track of them.
Notice that the yellowish color is gone now. The index is really gone.
Relationships and Foreign keys
To learn about table relationships, we need to add a few tables that work side by side with basic_info. Create a courses table as shown below :
CREATE TABLE courses( id INT NOT NULL AUTO_INCREMENT, courseName VARCHAR(255), grade INT(3), maximumGrade INT(3), PRIMARY KEY (id) );
this table holds information about courses students may enroll in. We create another table that is going to link basic_info and courses together.
CREATE TABLE books( id INT NOT NULL AUTO_INCREMENT, bookname VARCHAR(255), studentId INT, courseId INT, leaseTime DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), FOREIGN KEY (studentId) REFERENCES basic_info(id), FOREIGN KEY (courseId) REFERENCES courses(id) );
This tables relationship to others is defined through foreign keys. I simple terms, we specify that studentId should be an Id of the basic_info table and courseId should be an Id of the courses table. The database system enforces this in that when you pass in an Id from another table it reports an error or if you try to delete the table with some other tables still referencing it through foreign keys, the system gives you an error and helps in keeping the integrity of your database design.
Joins are used to combine two or more columns from different tables depending on a common field between them. To experiment with joins, let’s first populate our tables.
INSERT INTO courses( courseName, grade, maximumGrade ) VALUES ('Course 1','112','200'), ('Course 2','78','100'), ('Course 3','92','100'), ('Course 4','40','60');
We now populate the books table with some data
INSERT INTO books (bookname, studentId, courseId) VALUES ('Descriptive Geometry','2','4'), ('Planet Earth',7,1), ('Trees of Africa',6,2), ('Temple of heaven',5,1), ('Kenya Mountains',4,3), ('Chemistry distilled',3,3), ('Physics decoupled',1,1);
Looking at the book table, you don’t really have useful information about which student or course it is associated to. Joins help perform a single query that matches data from different tables and columns internal to give you more meaningful results.Consider the query below
SELECT basic_info.firstName,basic_info.lastName,books.bookname FROM basic_info INNER JOIN books ON basic_info.id = books.studentId ORDER BY basic_info.firstName;
which gives the results below
Now you can see that this result is friendly to the human who can directly see that the Descriptive Geometry book is currently held by Girinka Tunga. Now lets suppose you want to perform a query that touches on all the tables, for that you will need to have multiple joins:
SELECT books.bookname,courses.courseName,basic_info.firstName,basic_info.lastName FROM books INNER JOIN courses ON books.courseId = courses.id INNER JOIN basic_info ON books.studentId = basic_info.id ORDER BY basic_info.firstName;
which yields the result:
This mixes and matches table data behind the scenes to give us the info that we really care about. Hopefully you can see now that JOINS can save you lots of queries to sort out table relationships yourself.
We have only touched on INNER JOINS, but be aware that there are also LEFT JOINS, RIGHT JOINS and FULL joins. We won’t be giving examples on these, you can explore those yourself for now.
Aliases allow us to stick temporary names to column headers to have more readable results. Consider the query below :
SELECT firstName AS 'First Name', lastName AS 'Last Name' FROM basic_info;
which gives us the result :
This comes in handy when you need it.You can also concatenate columns as one unified column to have more compact results.Consider the query below :
SELECT CONCAT(firstName,' ',lastName) AS 'Name',address,email FROM basic_info;
which gives us the result below :
Now we don’t need to deal with lastname and firstname separately , we just have one field called name. This can add a layer of security for your application as the internal structure of your database tables is not exposed to external users.
SQL Aggregate Functions
The average function helps you compute the average for a given row. Consider the query below
SELECT AVG(age) FROM basic_info;
yielding the result
The count function gives you the count of certain entries in a column. The query
SELECT COUNT(age) FROM basic_info;
returns the number of students with an age entry in our basic_info table
This computes the maximun entry in your column.
SELECT MAX(age) FROM basic_info;
There is also a MIN() function you can try out
GROUP BY() modifier
Consider the query
SELECT age , COUNT(age) FROM basic_info WHERE age > 20;
which gives the result
It returns the first age meeting the WHERE clause filter criteria and cramps all the ages count into one row. If you wanted to know how many students are of a particular age , you would add a group by modifier as shown below
SELECT age , COUNT(age) FROM basic_info WHERE age > 20 GROUP BY age;
Much better huh!
If you wanted to even filter more based on the returned COUNT(age) , you would do something like
SELECT age , COUNT(age) FROM basic_info WHERE age > 20 GROUP BY age HAVING COUNT(age) >=2;
which would just return the 21 age
Sure there are more aggregate functions than we covered here and you can do to the docs and explore more. This gives a good set of tricks and tips you can mix and match to make your application’s databases as efficient as you can. Do shoot me in the comments below if you have any feedback or problem. I hope this has been informative to you and thanks for reading.