Data manipulation is done through access, modify, insert and delete of the content. There are 4 basic commands that allow performing these operations.
SELECT allows you to retrieve data from a database; it is a simple command. You can use the
* star to select all the content or specify the column(s) you are interested in. So you can select several columns and several tables at the same time, separated by a comma. To retrieve the table name, we use
FROM at the end of the query.
The basket corresponds to the table, the fruits correspond to the columns. So we see a hand retrieving one fruit, several fruits, corresponding to the specific
SELECT. We get all the fruits in the last comic strip, which corresponds to the global
1-- SELECT column 2-- FROM table; 3 4SELECT * 5FROM humanresources.employee; 6 7SELECT jobtitle 8FROM humanresources.employee; 9 10SELECT jobtitle, gender 11FROM humanresources.employee;
By default duplicates are returned with
ALL. If you want to delete them you have to use
In this comic, there are fruits that are duplicates. In the second panel of the comic strip, we can get all the duplicates, this corresponds to the default selection, without the
DISTINCT, but otherwise, we can choose a particular fruit by selecting its duplicate, which corresponds to the use of the
1SELECT DISTINCT jobtitle, gender 2FROM humanresources.employee;
|1||Production Technician - WC60||M||...|
It is possible to retrieve the sorted data with
ORDER BY. The cause must be placed at the end of the query (after the
FROM) and is used only once. It can be accompanied by
ASC (smallest to largest) or
DESC (largest to smallest). However, it is recommended that you always use it with
WHERE as it requires a lot of performance.
In this comic book, the goal is to collect the fruits precisely, by price, and therefore from the cheapest to the most expensive. The fruits of the last column are recovered, the banana first which is at 2 dollars then the strawberry which is at 3 dollars.
1SELECT DISTINCT jobtitle 2FROM humanresources.employee ORDER BY jobtitle; 3 4SELECT unitprice 5FROM purchasing.purchaseorderdetail 6ORDER BY unitprice DESC; 7
You can limit the number of entries with
LIMIT. For example, return only the first 10 elements of the result. You can shift the result with
OFFSET and sort with
DESC to get the last rows.
Here the goal is to get fruits, but with a limited number, here are the fruits of the first line. So we limit to 1, line one, then specify the fruits, for example, the banana or the strawberry.
1SELECT jobtitle 2FROM humanresources.employee LIMIT 2; 3 4SELECT jobtitle 5FROM humanresources.employee LIMIT 10 OFFSET 5;
|1||Chief Executive Officer||...|
|2||Vice President of Engineering||...|
The alias is useful when performing a query with several tables. It improves readability and creates consistency in the query.
1SELECT jobtitle 2FROM humanresources.employee AS HR;
You can decide to select only a part of the rows thanks to
WHERE. This limits a query that could be too costly in terms of performance.
Here we specify the characteristic which interests us, for example, the color of the fruit, corresponding to the
WHERE, i.e., we recover a banana whose (« where ») color is green.
1SELECT jobtitle 2FROM humanresources.employee where maritalstatus = 'S';
|1||Chief Executive Officer||S||...|
|2||Vice President of Engineering||S||...|
Data manipulation in SQL is done using
WHERE. The simplest order is
SELECT. You can combine several operations to refine your query and optimize performance, especially with
Did you find this content useful?
Your feedback helps us improve our content.