Home / code / sql

Select data in SQL

With the following instruction, you get a set of records as result.

SELECT [predikt] {*|table.*| [table.]field1 [AS alias1] [, [table.]field2 [AS alias2] [, ...]]} FROM tablename [, ...] [IN externdatabase] [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT ...]

I know it seams like a lot, but it's all gone be clear later.

predikt: With a predikt you can limit the number of records in the result. You can use the following ALL, DISTINCT or TOP. ALL is standart, TOP is not supported by all relational databases.

*: Select all fields in the table.

table.: The name of the table that has the requested fields.

field1, field2: The names of the selected fields. If you select more then 1 field, they are retrieved in the given order.

alias1, alias2: The names you want to uses for column heads instead original column heads.

tablename: Name of the table or tables where the data is stored.

externdatabase: The name of the database that has the tables, if the tables are not in the current database.

Requesting a list

Ok, now we are going to do it step by step.

The absolute minimum for a select instruction is:
SELECT * FROM biers
This select all fields from the table biers

Now when can select only one column:
SELECT name FROM biers
This instruction select the field name in the table biers

Or more columns
SELECT name, place FROM biers
Here we get the records of the fields name and place from the table biers.

SELECT place FROM biers
Now we have selected the field place from the table biers
But there can be more biers on 1 place, so to get every place only 1 time you can use DISTINCT.
SELECT DISTINCT place FROM biers

 

Selection with WHERE

With the WHERE you can add conditions to your selection.

example:

SELECT field1 FROM table WHERE field2 < 5
Here you get all the results of field1 where the value of field2 is smaller then 5.

There are more operators then <
<, >, <=, >=, !=, =, like, between ... and ..., in (...)

You can also compare strings(words):

SELECT field1 FROM table WHERE field2='theword'
The result must be exactly as theword.

If you just want to compare part of a word:

SELECT field FROM table WHERE field LIKE '%be%'
The results are all values that contain be.

Below is a list of all possible combinations.

Nature of the selection Patrons Values that fit in the patron Values that do not fit in the patron
Multiple characters a%a aa,aBa,abbbbbba abc
%ab% abc,AABB,xab aZb,bac
Special character a[@]a a@a aaa
1 character a_a aaa,a3a,aBa aBBBBBa
1 figure a#a a0a,a1a aaa,a10a
Character must be in the array [a-z] f,p,j 2,&
Combination a[b-m]# ab9, Af0 aac,a90,za,Az5

You also have between:
Select field FROM table WHERE field BETWEEN 5 AND 7
This gives all the values that are between 5 and 7.

SELECT field FROM table WHERE field IN(0, 5, 8)
Gives all the values that are 0, 5 or 8.

The same can be done for strings:
SELECT field1 FROM table WHERE field2 IN('this','that','andthis')

You can also select the values where nothing is entered:
SELECT field1 FROM table WHERE field2 IS Null

If you want to select the values where there is something entered:
SELECT field FROM table WHERE field IS NOT Null

Requesting a ordered list

SELECT field1 FROM table ORDER BY field2
Now the result is ordered alphabetic, the following conditions has the same result: SELECT field1 FROM table ORDER BY field2 ASC

With DESC you have the results in reverse order: SELECT field1 FROM table ORDER BY field2 DESC

And of course you can make combinations.
SELECT field1, field2 FROM table ORDER BY field2 DESC, field1 ASC

Grouping data

You can group data. For example you want the city with the biggest population for every country.
SELECT city, country, max(population) AS population FROM table GROUP BY country

Having

You use having when the selection is based on a Grouping.
SELECT city, country, max(population) AS population FROM table GROUP BY country having max(population)>1000000
This only shows the biggest city for every country if the population of that city is greater the 1 000 000.

LIMIT

The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants. With one argument, the value specifies the number of rows to return from the beginning of the result set. With two arguments, the first specifies the offset of the first row to return, the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1): To be compatible with PostgreSQL MySQL also supports the syntax: LIMIT row_count OFFSET offset.

SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
To retrieve all rows from a certain offset up to the end of the result set, you can use -1 for the second parameter:
SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
If one argument is given, it indicates the maximum number of rows to return:
SELECT * FROM table LIMIT 5; # Retrieve first 5 rows
In other words, LIMIT n is equivalent to LIMIT 0,n.

 

TOP

Latest script:

 

Books: