This article will cover retrieval of information.
Selection
The SELECT
keyword is used to obtain all relevant information based on a query.
For instance, running a SELECT
query on a table with no conditions would look like:
SELECT * FROM `mydb`.`mytable`
This will select all (symbolised by the *, which is the Unix symbol for all) from the table called mytable
which is found within the mydb
database.
Note the dot between the database name and the table name is used for contatenation of a database and table, this simply means that the table exists within the database. This is crucial if the database has not already been selected as shown in the previous article.
The following table is an example table from some database:
ID | First_name | Second_name | Country |
---|---|---|---|
0 | Jack | Adams | US |
1 | Calum | Costner | UK |
2 | James | Maclean | UK |
3 | Link | Williams | UK |
Running the previous query on the following table will return the whole table, and there will be no modifications made to the database.
Selection can also be made to be more specific, that is essentially selecting only what is needed and removing all of the rest. This can be done by providing columns to view.
The following query returns only the ID and First_name fields:
SELECT ID, First_name FROM `mydb`.`mytable`
ID | First_name |
---|---|
0 | Jack |
1 | Calum |
2 | James |
3 | Link |
Selecting a database
Running SQL commands on a single database can be made more efficient if the database is selected first. This also shortens queries by removing the need to reference the database each time.
To select a database, the USE
keyword is used. Assuming the database is called
mydb
, the following would select that database:
USE `mydb`
As a result, the SELECT
query from above can be simplified to:
SELECT ID, First_name FROM `mytable`
Selecting unique data
SQL also provides a DISTINCT
keyword that forces only single instances of
a column in the results. For instance, say you wanted count all of the individual locations of people so you can
figure out how many countries have participated in a survey, you could simply run the following:
SELECT DISTINCT Country FROM `mytable`
From the above table, this will return:
Country |
---|
US |
UK |
Binding tables to variables
As well as being able to simply select a table, tables can be named or bound to variables, so that fields can then be accessed through the table directly and to prevent confusion:
SELECT ID, First_name FROM 'mydb'.'countries' c
Null
SQL also includes a value known as a special null value. Null represents a value of nothing. It is not the same as an empty field, a 0 value or a false value. It represents nothing at all. Sometimes a selection will return null in some fields, meaning nothing has been returned.