Selecting data using SELECT statement
The SELECT statement is used to retrieve data from one or more tables from the database.
Basic SELECT Syntax
The structure of an SQL statement.
SELECT select_list FROM table_list WHERE predicates GROUP BY columns HAVING condition ORDER BY sort_key_columns
Select all products from the products tablespoon
SELECT * FROM products
Use the wildcard character *, we can retrieve all the rows and all the columns from the products tables.
Select a single column
SELECT product_name FROM products
By only choosing one column, we will retrieve only rows with the specified column from the database.
Retrieving multiple columns
SELECT prod_id, prod_name, prod_price FROM products
Whitespace is ignored when writing sql statements. We could have puts the whole sql statement in one line and would have worked the same. Also the sql statements are case insensitive,
selectare treated the same.
Retrieving distinct rows
We can use the
DISTINCT keyword to remove duplicates from the returned rows.
SELECT DISTINCT vend_id FROM products
The query will return distinct vendor ids from the the products table;
The DISTINCT keyword applies to all rows not only the one column it precedes. If you specify two columns, the DISTINCT will apply to both columns not to an individual column, so all rows will be returned.
THE LIMIT keyword is used to limit results returned from the database. We can specify a number or a percentage on the number of rows to return. The statement below will return the first 5 rows from the products table.
SELECT TOP 5 * FROM products
We can also specify a percentage to return, e.g. to return the top 10 % from the products table we can use the following sql statement.
SELECT TOP 20% FROM products
Comments are used to document your sql statements. These statements are ignored and not processed, they are mainly for yourself.
Single line comment
The single line comment starts with –, two dashes, anything after the dashes is ignored.
-- This is a single line comment, any statements after the two -- are ignored SELECT * FROM products
The multiline comment starts with with /* and ends with */ characters. Anything in between the two character combination will be ignored.
/* This is a multiple line comment anything between here and the last character will be ignored. */ SELECT * FROM products