When we have SQL database ready - let’s start learning language itself
SQL, as mentioned earlier, stands for Structured Query Language
It looks a bit like a sentence in English
Now, we need to learn actual syntax and how to use it So, what will we do today?
- What is query and how to execute them in phpMyAdmin
- Different query types and their syntax
Let’s go
Running queries
Before everything - what is a query?
Query is a “sentence” in SQL, that tells database to interact (Get, edit or delete) with records.
But, how do we execute that query in phpMyAdmin?
When we select table there is a little box above records:
When we click Edit
or Edit inline
we can run our own queries on the table
Let’s learn some basic queries now
Queries
Don’t get shocked - it’s the best and very common practice to write SQL keywords in uppercase (with caps lock)
So, when we write a query
- values defined by us, like table/column names - lowercase
- SQL keywords (functionalities built-in language) - uppercase
When we choose the table, this one query is executed.
SELECT * FROM `users`;
What is it?
SELECT
SELECT
allows us to get every row that meets specific conditions
For example: we want to retrieve value from table users
from columns named id
and username
where email
value equals test@test.com
Then, we’d write query like this
SELECT `id`, `username` FROM `users` WHERE `email` = "test@test.com"
Remember - conditions can be stacked using AND
keyword
SELECT `id`, `username` FROM `users` WHERE `email` = "test@test.com" AND `password` = "Some hashed Password"
You can place as many AND
as you want
So, formula for SELECT
query looks like this
SELECT [Columns] FROM [table] WHERE [conditions]
If we want to select values from every column for each record we use *
symbol
SELECT * FROM `users` WHERE `email` = "test@test.com"
That’s pretty much it - we’ll get used to it when we’ll be actually working with SQL
INSERT
Let’s say we want to add some record to the table - that’s what INSERT
is for
So, to add a new user to our users
table, just run
INSERT INTO `users` VALUES(NULL, "user1", "$2y$10$7uKWbeYe7X/oZyQT/fxxfOp8ichcShxejqSXAOSYbMFiNCwkpe70.")
Last value is bcrypt
hash of test123
string
Try it - click Edit
or Edit inline
(eventually SQL
in top nav) and paste that query
After execution, we should see one record
Why is first value NULL
? That’s why we set up Auto increment
for this column - now when we pass null it replaces it with numerical value of previous record and adds 1
So, if we run this query couple of times - then we get ids equal to 2
, 3
, 4
and so on
Here is the formula for INSERT
query
INSERT INTO [table] VALUES([Values separated by comma])
Where number of values passed has to match number of columns
DELETE
query
Our user doesn’t really like his new task - he wants to get rid of it
That’s where DELETE
comes in handy.
According to this scenario - let’s see a quick example
DELETE FROM `tasks` WHERE `task_id` = 1
1
here is some random value - in reality it would be one passed by PHP
Whole “recipe” for this query looks like this
DELETE FROM [table] WHERE [conditions]
UPDATE
query
But if a user made a typo in it’s title? Will we get whole contents of that record, then delete it and insert new?
That’s additional 3 queries. Multiplied by 1 million users it gives us 3 million queries - too much
That’s why we have UPDATE
UPDATE `tasks` SET `title` = "Something new" WHERE `task_id` = 1
It will set title
to Something new
in the record with task_id
equal to 1 from tasks
table
And basic syntax looks like this
UPDATE [table] SET [updated values] WHERE [conditions]
Dangers in UPDATE
and DELETE
While working with DELETE
and UPDATE
we need to be sure, that WHERE
conditions are valid - otherwise we might up with whole table looking identical or with no table at all
Unless we are specifically sure, that we want to update every record in table or delete whole content of the table, don’t forget valid WHERE
clause
Order of results
So, we have a bunch of posts but if we want to show them - it will display the oldest ones first (as that’s the order in database)
Do we have to reverse that table in PHP itself? No - just use ORDER BY
For example with tasks
SELECT * FROM `tasks` WHERE `creator` = "johnny" ORDER BY `task_id` DESC
DESC
will order the results alphabetically reversed (So here, from the greatest to lowest id)
And just ORDER BY
(or ORDER BY [column] ASC
) will return results in alphabetical order
LIKE
keyword
SQL LIKE
keyword allows us to search for specific pattern in a column
So, I’ll give you an example
Let’s say we create a shop and we have a database of cities
We want to see every city that starts with the letter L
So - we write this query
SELECT * FROM `Cities` WHERE `name` LIKE `L%`
From SQL to english
select all contents from
Cities
table wherename
begins withL
%
is so-called wildcard and it means any number of characters or even no at all
There is also anotherone useful wildcard - _
It represents a single character
So if in DB we’d have these 2 values
Lon
Lo
With %
, both of those meet condition
But is we use _
- only the second one will get returned
Conclusion
That’s pretty much it - I hope you have learnt something, as I’ll require that knowledge in the next part
I thought, this part will have a delay (as I was working on something) but luckily it was short so it got posted on time
What I was working on? On my own blog
So yeah, most of the articles will get posted there - especially non-coding ones This series will be there as well
If you want to have access to this course earlier - check my blog, as I’ll post those articles as soon as I finish writing them
Also, check out my other articles and another parts of this course - and see you in the next ones